上面说了语法，下面说命令
select: 投影：select name,age from stu2; select name as 姓名,age as 年龄 from stu2; 选择：select * from stu2 where id>3;
去掉重复行： select DISTINCT name,age sex from stu2;
select 简单用法 select * from stu2 where name='lisi'; select * from stu2 where name='lisi' and id>3; select * from stu2 where name='lisi' or id>3; select * from stu2 where id in(3,5,6) ; select * from stu2 where id in(select id from stu2 where sex='M'); select * from stu2 where sex is null or birth is null; select * from stu2 where sex is not null; select * from stu2 where id between 2 and 5; 聚合函数avg count max min等 select count(*) from dep; select max(age) from emp; select min(age) from emp; select avg(age) from emp; 分组group by select dep_id,count(dep_id) from emp group by dep_id; //分组之后再汇总WITH ROLLUP select dep_id,count(dep_id) from emp group by dep_id WITH ROLLUP; select dep_id,count(dep_id) from emp group by dep_id having count(dep_id)>1;
排序order by asc/desc select * from emp order by age asc; select * from emp order by age desc; select * from emp order by sex asc; select * from emp order by sex asc,dep_id asc;
limit [offset ] line,limit还是很有用处的，特别是当数据库东西很多的时候，你不可能全输出来，那你就不用干其他的事情了 select * from emp order by sex asc,dep_id asc limit 3; select * from emp order by sex asc,dep_id asc limit 2,3;
insert 几个特别用法 //从stu2表随机的取出数据插入stu2表中 insert into stu2(name,age,sex,birth) select name,age,sex,birth from stu2 order by rand();
//前提stu1 和stu2 结构一样，比如create table stu1 like stu2;,rand随机函数，范围0-1 insert into stu1 select * from stu2 where rand() > 0.3; 其实还有一种创建表的方式 create table stu1 select * from stu2;//创建表同时插入数据 多表查询： select e.id,d.name as dname,e.name as ename ,e.age,e.sex from dep d,emp e where d.id=e.dep_id;
左外联 select e.id,d.name as dname,e.name as ename ,e.age,e.sex FROM dep d LEFT JOIN emp e ON d.id=e.dep_id; 右外联 select e.id,d.name as dname,e.name as ename ,e.age,e.sex from emp e RIGTH JOIN dep d ON d.id=e.dep_id; 内联 select e.id,d.name as dname,e.name as ename ,e.age,e.sex from emp e INNER JOIN dep d ON d.id=e.dep_id;
