mysql轻快入门（2）

上面说了语法，下面说命令

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;

转载于:https://blog.51cto.com/13375232/2057533