sql常用语句

常用sql语句
创建库creat database database-name
删除库drop database dbname
创建表create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],…)
删除表drop table tabname
添加列Alter table tabname add column col type
添加主键Alter table tabname add primary key(col)
创建视图create view viewname as select statement
删除视图drop view viewname
–查询：select * from table1 where 范围
–插入：insert into table1(field1,field2) values(value1,value2)
–删除：delete from table1 where 范围
–更新：update table1 set field1=value1 where 范围
–查找：select * from table1 where field1 like ’%value1%’ —like的语法
–排序：select * from table1 order by field1,field2 [desc]
–分组：select sex,sum(*) from table1 where age>20 group by sex
–总数：select count as totalcount from table1
–求和：select sum(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
–between用法：select * from table1 where time between time1 and time2
–in用法：select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)