经典sql语句

初始化表 truncate table [tablename] //删除表中的所有记录，主键从0开始递增 //区别于delete

使用旧表创建新表 create table tab_new like tab_old

增加列 alter table tablename add collum type //列添加后不能删除

添加主键 alter table tablename add primary key(collum)

删除主键 alter table tablename drop primary key(collum)

创建索引 create [unique] index indexname on tablename(col)

删除索引 drop index indexname //索引是不能修改的只能删除重建

选择 select * from tablename where 条件语句

插入 insert into tablename(col1,col2...) values(col1_val,col2_val...)

删除 delete from tablename where 条件语句

更新 update tablename set col=value where 条件语句

查找 select * from tablename where col1 like '%keywords%'

排序 select * from tablename order by col1 asc or desc

总数 select count * as totalcount from tablename

求和 select sum(col1) as sumvalue from tablename

平均 select avg(col1) as avgvalue from tablename

最大 select max(col1) as maxvalue from tablename

最小 select min(col1) as maxvalue from tablename

左连接 SELECT *

FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)

WHERE b.key=d.key;

左外连接 select a.a a.b a.c b.c b.d b.f from a left out join b on a.a = b.c

//左外连接返回所有匹配行和左连接表的所有行

显示所有数据库 show databases

显示数据库所有表 show tables