mysql轻快入门（2）

sher

　　上面说了语法，下面说命令
　　select:
　　投影：select name,age from stu2;
　　select name as 姓名,age as 年龄 from stu2;

　　选择：select * from stu2 where>　　去掉重复行：
　　select DISTINCT name,age sex from stu2;
　　select 简单用法
　　select from stu2 where name='lisi';

　　select  from stu2 where name='lisi' and>　　select  from stu2 where name='lisi' or>

　　select  from stu2 where>　　select  from stu2 where>
　　select  from stu2 where sex is null or birth is null;
　　select from stu2 where sex is not null;

　　select  from stu2 where>　　聚合函数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;