mysql轻快入门（2）
上面说了语法，下面说命令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';
selectfrom stu2 where name='lisi' and> selectfrom stu2 where name='lisi' or>
selectfrom stu2 where> selectfrom stu2 where>
selectfrom stu2 where sex is null or birth is null;
select from stu2 where sex is not null;
selectfrom 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;
selectfrom emp order by age desc;
select from emp order by sex asc;
selectfrom emp order by sex asc,dep_id asc;
limit line,limit还是很有用处的，特别是当数据库东西很多的时候，你不可能全输出来，那你就不用干其他的事情了
select from emp order by sex asc,dep_id asc limit 3;
selectfrom 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,birthfrom stu2 order by rand();
//前提stu1 和stu2 结构一样，比如create table stu1 like stu2;,rand随机函数，范围0-1
insert into stu1 select from stu2 whererand() > 0.3;
其实还有一种创建表的方式
create table stu1 selectfrom 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;
页:
[1]