常用SQL语句

1、创建数据表create table tablename(col1 type1 [not null] [primary key], col2 type [not null],...)2、删除数据表drop table tablename3、插入记录insert into tablename(field1,field2) values(value1,value2)4、查询记录(1)从指定的数据表中查询并返回字段field1大于value1的那些记录的所有字段select from tablename where field1>value1

(2)模糊查询，返回字段field1中包含字符串value1的那些记录的3个字段

select field1,field2,field3 from tablename where field1 like '%value1%'

(3)查询并返回字段field1的值介于value1和value2之间的那些记录的所有字段

select from tablename where field1 between value1 and value2(4)查询并返回所有记录所有字段，按字段field1升序，field2降序排列select from tablename order by field1,field2 desc

(5)查询并返回数据表中所有记录总数

select count() as totalcount from tablename(6)对数据表中指定字段field1的值进行求和select sum(field1) as sumvalue from tablename(7)对数据表中指定字段的field1的值求平均数select avg(field1) as avgvalue from tablename(8)对数据表中指定字段field1的值求最大值、最小值select max(field1) as maxvalue from tablenameselect min(field1) as maxvalue from tablename(9)查询并返回数据表中符合条件的前10条记录select top 10 * from tablename where field1 like '%value1%' order by field15、更新记录update tablename set field1=value1 where field2=value26、删除记录delete from tablename where field1=value1