SQL 数据库基本语句

创建数据库:CREATE DATABASE dbname;

删除数据库:drop database dbname;

创建新表:create table tabname (col1 type1 [not null][primary key],col2 type2 [not null],..); www.2cto.com

使用旧表创建新表:select * into newtable from oldtable;

如果不想导记录,只想生成表结构:select*into newtable from oldtable where 1=2;

向新表中插入就表的数据:insert into newtable select * from oidtable where...;

删除新表:drop table tabname;

增加一个列:Alter table tabname add column col type;

添加主键:Alter table tabname add primary key(col);

删除主键:Alter table tabname drop primary key(col); --运行不了,貌似是针对特定版本的,05版用不了

创建索引:create [unique] index idxname on tabname(col….);

删除索引:drop index idxname on tabname;

选择:select* from table1 where 范围;

插入:insertinto table1(field1,field2) values(value1,value2);

删除:deletefrom table1 where 范围;

更新:update table1 set field1=value1 where 范围;

查找:select* from table1 where field1 like ’%value1%’ --传说中的检索

排序:select* from table1 order by field1,field2 [desc];

总数:selectcount * as totalcount from table1;

求和:selectsum(field1) as sumvalue from table1;

平均:select avg(field1) as avgvalue from table1;

最大:select max(field1) as maxvalue from table1;

最小:select min(field1) as minvalue from table1;