mysql dql（数据查询）-爱代码爱编程

数据查询语言，用来查询数据库中表的记录(数据)

查询语句的子句执行顺序

1.词法分析与优化，读取SQL语句

2.from，选择数据来源

3.where，筛选出符合条件的记录

4.group by, 对where筛选出的记录分组

5.select，选择输出内容

6.order by，对结果排序

7.limit，规定结果集中出现的数量

条件查询

where子句中，条件执行的顺序是从左到右。我们应该把索引条件放在最左侧，其次是筛选记录最多的条件，最后是普通条件，这样会使查询速度最快。

where语句中的条件运算会用到四种运算符：

• 数学运算符：+ - * / %

• 比较运算符：> < = ! != in(包含) is null(为空) is not null(不为空) between…and…(范围) like(模糊查询) regexp(正则表达式)

• 逻辑运算符：and(与) or(或) not(非) xor(异或)

• 按位运算符：&(位与) |(位或) ~(位取反) ^(位异或) <<(左移) >>(右移)

  null与数学运算符进行运算，结果都是null；

  异或：age>18 xor sex=“男” 左边右边都是false，才返回false，否则都是true；

#查询10，20部门里工资大于2000的员工
select empno,ename,sal
from t_emp
where (deptno=10 or deptno=20) and sal>=2000;

#查询10部门里年收入超过15000，且工龄大于20年
#ifnull(comm,0)如果comm字段为null则返回数字0
#datediff(now(),hiredate)返回两个日期相差多少天，now()获取当前时间
select empno,ename,sal,hiredate
from t_emp
where deptno=10 and (sal+ifnull(comm,0))*12>=15000
and datediff(now(),hiredate)/365>=20;

#查询10,20,30部门里在1985年以前入职，且不能是SALESMAN职位的员工
select 
   empno,ename,sal,deptno,hiredate
from t_emp
where deptno in(10,20,30) and job!="SALESMAN"
and hiredate<"1985-01-01";

#查找佣金为空,工资在2000-3000之间，名字含字母A的员工
select ename,comm,sal from t_emp 
where comm is null
and sal between 2000 and 3000
and ename like "%A%";

#查询部门不是10,20 和 工资大于2000的员工(结果包含10,20部门的员工)
select
  ename,deptno,sal
from t_emp
where not deptno in(10,20) xor sal>=2000;

聚合函数

聚合函数在数据的查询分析中，应用十分广泛。聚合函数可以对数据求和、求最大值和最小值、求平均值等等。

聚合函数不能出现在where子句中，这是一种语法错误，聚合函数执行需要一个数据范围，而在where子句执行时还未划出这个范围。

1. sum函数由于求和，只能用于数字类型，字符类型的统计结果为0，日期类型统计结果是毫秒数相加
2. max、min函数用于获得非空值的最大最小值。
3. avg函数用于获得非空值的平均值，非数字数据统计结果为0。
4. count(*)用于获得包含空值的记录数，count(列名)用于获得包含非空值的记录

#查询所有员工工资的平均值
select avg( sal+ifnull(comm,0) ) as avg from t_emp

#查询10,20部门员工的工资总和
select sum(sal) from t_emp where deptno in(10,20);

#查询10,20部门中，月收入最高的员工
select max(sal+ifnull(comm,0)) from t_emp where deptno in(10,20);

#查询员工名字最长的是几个字符
select max(length(ename)) from t_emp;

#查询员工表中所有记录的数量
select count(*) from t_emp;

#查询comm字段中非空的记录数量
select count(comm) from t_emp;

#查询10和20部门中，底薪超过2000元并且工龄超过15年的员工人数
select count(*) 
from t_emp where deptno in(10,20) 
and sal>=2000 
and datediff(now(),hiredate)/365>=15;

分组查询

默认情况下汇总函数是对全表范围内的数据做统计，group by子句的作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对每个小区域分别进行数据汇总处理。

数据库支持多列分组条件，执行的时候逐级分组。

查询语句中如果含有group by子句，那么select子句中的内容就必须要遵守规定 : select子句中只能包括聚合函数，或者group by子句的分组列，其余内容均不可以出现在select子句中。

with rollup 关键字：对汇总结果再次进行汇总计算。

group_concat 函数可以把分组查询中的某个字段拼接成一个字符串

#查询每个部门的平均底薪, round四舍五入到整数位
select deptno,round(avg(sal))
from t_emp group by deptno;

#逐级分组，查询每个部门里，每种职位的人员数量和平均底薪
select deptno,job,count(*),avg(sal) as _avg
from t_emp group by deptno,job
order by _avg desc;

#对分组结果集再次做汇总计算,with rollup关键字：对汇总结果再次进行汇总计算
select deptno,count(*),avg(sal),sum(sal),max(sal),min(sal)
from t_emp 
group by deptno with rollup;

#查询每个部门内底薪超过2000元的人数和员工姓名
select deptno,group_concat(ename),count(*)
from t_emp where sal>=2000
group by deptno;

having子句

having子句功能和where子句一样，实现数据的筛选，having子句只能放在group by后面。

having子句的特殊用法：按照数字1分组，MySQL会依据select子句中的列进行分组，having子句也可以正常使用。

#查询平均薪资超过2000的部门
select deptno
from t_emp
group by deptno having avg(sal)>=2000;

#查询每个部门中，1982年以后入职的员工超过2个人的部门编号
select deptno
from t_emp
where hiredate>="1982-01-01"
group by deptno having count(*)>=2;

#having子句的特殊用法
#1代表用select中第1个字段进行分组
select deptno,count(*) from t_emp
group by 1;

#可以用having替代where的内容，由于执行速度慢不建议这么写
select deptno,count(*) from t_emp
group by 1 having deptno in(10,20);

表连接查询

可以在多个表中查询数据。

表连接分为两种：内连接和外连接。内连接是结果集中只保留符合连接条件的记录，外连接是不管符不符合连接条件，记录都要保存到结果集中。

相同的数据表也可以做表连接。表连接查询速度比子查询高很多。

内连接：

内连接的数据表不一定有同名字段，只要字段之间符合逻辑关系就行。

#内连接的多种语法形式
#查询每名员工的部门信息
select e.empno,e.ename,d.dname
from t_emp e join t_dept d on e.deptno=d.deptno;

select e.empno,e.ename,d.dname
from t_emp e join t_dept d where e.deptno=d.deptno;

select e.empno,e.ename,d.dname
from t_emp e , t_dept d where e.deptno=d.deptno;

#查询每个员工的工号、姓名、部门名称、底薪、职位、工资等级
select e.empno,e.ename,d.dname,e.sal,e.job,s.grade
from t_emp e join t_dept d on e.deptno=d.deptno
join t_salgrade s on e.sal between s.losal and s.hisal;

#查询与SCOTT相同部门的员工都有谁
select e2.ename
from t_emp e1 join t_emp e2 on e1.deptno=e2.deptno
where e1.ename="SCOTT" and e2.ename!="SCOTT";

#查询底薪超过公司平均底薪的员工信息
select e.empno,e.ename,e.sal
from t_emp e join (select avg(sal) a from t_emp) t
on e.sal>t.a;

#查询RESEARCH部门的人数、最高底薪、最低底薪、平均底薪、平均工龄
select count(*),max(e.sal),min(e.sal),avg(e.sal),avg(datediff(now(),e.hiredate) /365)
from t_emp e join t_dept d on e.deptno=d.deptno
where d.dname="RESEARCH";

#查询每种职业的最高工资、最低工资、平均工资、最高工资等级和最低工资等级?
select
e.job,
max(e.sal+ifnull(e.comm,0)),
min(e.sal+ifnull(e.comm,0)),
avg(e.sal+ifnull(e.comm,0)),
max(s.grade),
min(s.grade)
from t_emp e join t_salgrade s on (e.sal+ifnull(e.comm,0)) between s.losal and s.hisal
group by e.job;

#查询每个底薪超过部门平均底薪的员工信息
select e.empno,e.ename,e.sal
from t_emp e join (select deptno,avg(sal) as a from t_emp group by deptno) t
on e.deptno=t.deptno and e.sal>t.a;

为什么要使用外连接？

内连接的连接条件是 e.deptno=d.deptno，如果一个新来的员工没有部门，查询时就会被漏掉，所以要引入外连接语法

外连接：

左外连接就是保留左表所有的记录，与右表做连接。如果右表有符合条件的记录就与左表连接。如果右表没有符合条件的记录，就用NULL与左表连接。右外连接也是如此。

使用外连接时要特别注意where与on子句的区别，where会筛选掉符合某条件的数据，而on不会，必须把筛选条件写在where中。

#外连接
select e.empno,e.ename,d.dname
from t_emp e left join t_dept d
on e.deptno=d.deptno;

#查询每个部门的名称和部门人数
select d.dname,count(e.deptno)
from t_emp e right join t_dept d
on e.deptno=d.deptno
group by d.deptno;

#查询每个部门的名称和部门人数，如果没有部门的员工，部门名称用null代替
(select d.dname,count(e.deptno)
from t_emp e right join t_dept d
on e.deptno=d.deptno
group by d.deptno) 
union
(select d.dname,count(*)
from t_emp e left join t_dept d
on e.deptno=d.deptno
group by d.deptno);

#查询每名员工的编号、姓名、部门、月薪、工资等级、工龄、上司编号、上司姓名、. 上司部门
select 
  e.empno,e.ename,d.dname,
  (e.sal+ifnull(e.comm,0)),s.grade,
  floor(datediff(now(),e.hiredate) /365),
  t.mgrno,t.mename,t.mdname
from t_emp e left join t_dept d on e.deptno=d.deptno
left join t_salgrade s on e.sal between s.losal and s.hisal
left join
(select 
  e1.empno as mgrno,e1.ename as mename,d1.dname as mdname
from t_emp e1 join t_dept d1 
on e1.deptno=d1.deptno) t
on e.mgr=t.mgrno;

子查询

子查询是一种查询中嵌套查询的语句。where子句在筛选数据时会执行很多次，不推荐在where中使用子查询。from子句在筛选数据事只会执行一次，只推荐使用from子句的子查询。

单行子查询的结果集只有一条记录，多行子查询结果集有多行记录，多行子查询只能出现在where子句和from子句中。

where子句中，可以使用in、all、any、exist关键字来处理多行表达式结果集的条件判断。

#查询FORD和MARTIN两个人的同事
select ename
from t_emp
where deptno in
(select deptno from t_emp where ename in("FORD","MARTIN") )
and ename not in("FORD","MARTIN");

#查询比FORD和MARTIN底薪都高的员工信息
select ename from t_emp
where sal >= all
(select sal from t_emp where ename in("FORD","MARTIN"))
and ename not in("FORD","MARTIN");

#查询公子等级是3级或者4级的员工信息
select empno,ename,sal
from t_emp
where exists(
select grade from t_salgrade
where sal between losal and hisal
and grade in(3,4)
);