老杜MySQL学习记录 -- 查询
完整的DQL语句：select ... from ... where ... group by ... having ... order by ... limit ...
执行顺序 5 1 2 3 4 6 7
1. 普通查询语句
基础语法： select 字段1，字段2，字段3,... from 表名；
select ename from emp;
select ename, sal*12 as '年薪' from emp;//创建别名‘年薪’，单引号，as可省略
select * from emp; 查询所有字段
1.1 条件查询
运算符：
---------------------------------------------------------------
= 等于
<>或!= 不等于
<或<=或>或>= 大于、小于
between...and... 两个值之间，左小右大，等同>=and<=。还可以用在字符串：between 'A' and 'C',[A,C),开区间
is null或is not null 为不为空
and 并且第二个条件
or 或者第二个条件
in 包含，相当于多个or，in(100,1000)指是否等于100或1000这两个值，而不是100到1000的范围
not 取非，主要用在is或者in中，is not, not in
like 模糊查询，%代表任意多个字符，_代表任意单个字符。%A%代表包含A的子串，_A%代表第二个字符是A的。
若要查询包含'%'或'_',需要转义'\%'，'\_'
--------------------------------------------------------------------------
语法： select 字段1，字段2，字段3,... from 表名 where 条件；
执行顺序： 先from，然后where，最后select
select sal from emp where ename = 'SMITH';//VARCHAR类型字符串需要加‘’
select ename, sal from emp where sal>=1000 and sal <= 3000;//查询工资在1000和3000之间
select ename, sal from emp where sal between 1000 and 3000; //功能同上，between..and是闭区间[1000,3000],左小右大
select ename, sal from emp where sal <> 3000; // !=和<>都可以表示不等于
select ename, sal from emp where sal > 1000 and (deptno = 20 or deptno = 30);//and优先级比or高，不确定就加括号
select ename, sal from emp where sal > 1000 and deptno in (20, 30);//功能同上，in等同or
select ename from emp where ename like '%A%';//找出名字中含有A字符的
Note: NULL在数据库中不是一个值，做赋值运算结果总是NULL：300+NULL=NULL，使用is null或is not null
1.2 数据排序
语法： select 字段1，字段2，...from 表名 order by 规则
asc表示升序，desc表示降序。
select ename, sal from emp order by sal;//默认按工资升序排列
select ename, sal from emp order by sal asc;//同上，升序
select ename, sal from emp order by sal desc;//降序排
select ename, sal from emp order by sal desc, ename asc;//按工资降序，工资相同按名字升序
多个条件排序，优先按前面规则，后面规则可能用不上
组合： select 字段 from 表名 where 条件 order by xxx;
执行顺序： from最先，where第二，select第三，order by最后
例子： select ename, sal*12 as '年薪' from emp where job = 'salesman' order by '年薪' desc;
例子中select语句先定好年薪，order by是按select定的别名排列
1.3 单行处理函数（输入一行输出一行）
ifnull() 空处理函数
用法： ifnull(可能为NULL的数据，被当做什么)
select ename,(sal+ifnull(comm, 0))*12 from emp;//列出所有人的年薪（工资加补贴）
1.4 分组函数(多行处理函数) 一般和group by联合使用，在group by之后执行
count 计数
sum 求和
avg 平均值
max 最大值
min 最小值
所有的分组函数都是对‘某一组’数据进行操作的
select sum(sal) from emp;//求和
select max(sal) from emp;//找最大值
select count(ename) from emp;//找出ename的总人数
select count(*) from emp;//找出总人数
NOTE：分组函数自动忽略NULL
select ename, sal from emp where sal > avg(sal);//语法错误！分组函数不能直接出现在where语句中
select ename, sal from emp where sal > (select avg(sal) from emp);//子查询，查询工资高于平均工资的员工
count(*)和count(字段)区别：
count(*): 不是统计某个字段个数，是统计总条数（和某个字段无关）
count(字段)： 统计某个字段中不为NULL的总条数
1.5 group by和having
group by： 按照某个字段或某些字段进行分组
having: 对分组后的数据进行再次过滤，只能在group by后联合使用
找出每个工作岗位的最高薪资：先分组再查询
select max(sal),job from emp group by job;
NOTE： 当查询语句中有group by时候，select之后只能跟分组函数和被分组的字段！！
select ename,max(sal),job from emp group by job;//错误！ ename没参加分组，此时不能加入查询
多个字段联合分组：（找出每个部门不同工作岗位的最高薪资）
select max(sal), job, deptno from emp group by job, deptno;
找出每个工作岗位的最高薪资，要求显示薪资大于2500的。
select max(sal), job from emp group by job having max(sal)>2500;//效率低
select max(sal), job from emp where sal>2500 group by job;//where先过滤，减少数据查询，效率高
能使用where过滤的不要使用having，除非where不能用。
比如：找出每个岗位的平均薪资，要求显示薪资大于2000的岗位。
select avg(sal), job from emp group by job having avg(sal)>2000;//此处where后不能跟分组函数，只能having
1.6 查询结果去重 distinct
select distinct job from emp;
Note: distinct只能出现在所有字段最前面，代表所有字段联合起来去重
select distinct deptno,job from emp;
select count(distinct job) from emp;//统计岗位的数量
2. 连接查询（多张表联合查询）
根据表的连接方式划分：
内连接 假设表A和表B连接，把A和B能够匹配的记录查询出来，AB两张表没有主副之分
等值连接
非等值连接
自连接
外链接 假设表A和表B连接，AB中有一张是主表，一张副表，主要查询主表数据，捎带查询附表。
假如副表中数据没有和主表匹配上，副表自动模拟出NULL与之匹配。
左外连接（左连接）：左边表是主表
右外连接（右连接）：右边表是主表
全连接 （很少用）
连接查询，一般用别名，来区分两张表内相同名字的字段(类似类中成员变量)
select e.ename,d.dname from emp as e, dept as d;
2.1 笛卡尔乘积现象：如果连接两张表联合查询没有条件限制，查询结果就变成了两个表乘起来
避免了笛卡尔乘积现象，不能减少匹配次数，只是显示的结果是有效记录
select e.ename,d.dname from emp e, dept d where e.deptno = d.deptno;//SQL92, 老语法
2.2 内连接--等值连接：最大特点是，条件是等量关系
SQL99： join...on...语法
语法： ...from A表 (inner 可以省略)join B表 on 连接条件 where ... （表连接和where条件分离，SQL99语法更清晰）
select e.ename, d.dname from emp e inner join dept d on e.deptno=d.deptno;
2.3 内连接--非等值连接：最大特点是，条件是非等量关系
//找出员工工资所在的等级，员工表+工资等级表
select e.ename, e.sal, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
2.4 内连接--自连接： 最大特点是，一张表看做两张表，自己连自己
//找出每个员工的上级领导，要求显示员工名和对应领导名：
//可以分成两个表，一个员工表，一个领导表。关键：员工的领导编号，等于领导的员工编号
select
a.ename as '员工名', b.ename as '领导名'
from
emp as a
join
emp as b
on
a.mgr = b.empno;
2.5 外连接：比内连接使用多
//找出每个员工的上级领导，要求显示员工名和对应领导名，King没有领导，也要查出来：
select
a.ename as '员工名', b.ename as '领导名'
from
emp as a
left join //左外连接， outer可以省略
emp as b
on
a.mgr = b.empno;
//找出没有员工的部门
select
d.*
from
emp as e
right join
dept as d
on
e.deptno=d.deptno
where
e.ename is null;
NOTE：外连接最重要特点是：主表数据无条件查询出来
2.6 三张表联合查询
A join B on ... join C on ...表示A先和B连接，连接之后再和C连接
//找出每一个员工的部门名称和工资等级
select
e.ename, d.dname, s.grade
from
emp as e
join
dept as d
on
e.deptno = d.deptno
join
salgrade as s
on
e.sal between s.losal and s.hisal;
//找出每一个员工的部门名称和工资等级以及上级领导
select
e.ename '员工', d.dname, s.grade, m.ename '领导'
from
emp as e
join
dept as d
on
e.deptno = d.deptno
join
salgrade as s
on
e.sal between s.losal and s.hisal
left join
emp as m
on
e.mgr = m.empno;
3. 子查询
select语句当中嵌套select语句，被嵌套的select语句是子查询，可以出现在select/from/where之后
3.1 where子语句中使用子查询
select ename,sal from emp where sal > (select avg(sal) from emp);//找出工资比平均工资高的
3.2 from后面嵌套子查询
//找出每个部门平均薪水的薪资等级
select d.deptno, s.grade from
1. select avg(e.sal) from emp as e;//找出所有人平均工资
2. select deptno, avg(sal) as average from emp group by deptno;//找出每个部门平均工资
3. 把上一步查询结果当做一个表temp
select temp.*, s.grade
from temp
join salgrade as s
on temp.average between s.losal and hisal;
也就是
select temp.*, s.grade
from (select deptno, avg(sal) as average from emp group by deptno) temp
//right join dept as d
//on temp.deptno=d.deptno
join salgrade as s
on temp.average between s.losal and hisal;
//找出每个部门薪资的平均等级
select e.deptno, s.grade from emp as e join salgrade as s on e.sal between s.losal and s.hisal;
//select temp.deptno, avg(temp.s.grade) from temp group by temp.e.deptno;不用当做临时表。。
select
e.deptno, avg(s.grade)
from
emp as e
join
salgrade as s
on
e.sal between s.losal and s.hisal
group by
e.deptno;
3.3 在select后面嵌套子查询
//找出每个员工所在的部门名，显示员工名和部门名
原来的方法：
select e.ename, d.dname from emp join dept on e.deptno = d.deptno;
新方法：
select
e.ename, (select dname from dept as d where e.deptno=d.deptno)
from
emp as e;
4. union查询, 拼接查询结果
第一个查询列的数量和第二个查询列的数量要一致！
//找出工作岗位是salesman和manager的员工
方法1： select ename, job from emp where job='salesman' or job='manager';
方法2： select ename, job from emp where job in ('salesman','manager');
方法3： select ename, job from emp where job = 'salesman'
union
select ename, job from emp where job = 'manager';
5. limit（分页查询）
5.1 limit是mysql特有的，不通用。Oracle中有个类似的rownum
5.2 limit取结果集中的部分数据
5.3 语法机制
limit startIndex，length 从起始位置取几个，startIndex不写默认从0
例子： 取出工资前5名的员工（先降序，再取）
select ename, sal from emp order by dest limit 0, 5;
5.4 通用的分页sql
每页显示pagesize条记录，第n页的内容是 (n-1)*pagesize, pagesize
