Mysql多表查询

高级查询

去重查询

SELECT DISTINCT * FROM dept;只能去掉所有列都相同的一行数据

降序查询

SELECT * FROM dept order by deptno desc;

升序查询

SELECT * FROM dept order by deptno ASC;

SELECT * FROM dept order by deptno;

limit分页查询

select * from table_name limit (页码 - 1) * 每页数量, 每页数量;

聚合函数

Mysql中内置了 5 种聚合函数，分别是：SUM 、max、min、avg、count。

• sum： 求和

  select sum(列) from table_name [其他子句];
  

• max: 求最大值

  select max(列) from table_name [其他子句];
  

• min: 求最小值

  select min(列) from table_name [其他子句];
  

• avg： 求平均值

  select avg(列) from table_name [其他子句];
  

• count: 求数量

  select count(列) from table_name [其他子句];
  

group by

group by分组后的查询中，select的列不能出现除了group by 分组条件以及聚合函数外的其他列。

select 列1, 列2, (聚合函数) from table_name group by 列1, 列2;

having

having是对group by分组后的结果集进行筛选。

select 列1, 列2, (聚合函数) from table_name group by 列1, 列2 having 分组后条件;

注意：使用聚合函数之后比较大小用having；

SELECT DEPT_ID, COUNT(*) FROM emp GROUP BY dept_id having count(*) > 2;

综合查询

SELECT DISTINCT emp.deptno FROM emp JOIN dept ON emp.deptno = dept.deptno WHERE bridate >= '2000-01-01' GROUP BY emp.deptno HAVING count(*) >= 2 ORDER BY count(*) DESC  LIMIT 0, 5;

书写顺序是以上。

SQL语句的执行顺序

from --> on --> join --> where --> group by --> having --> select --> distinct-- > order by–> limit

连接查询

• 使用聚合函数，SELECT 查询的列就只能是 聚合函数或 GROUP BY 语句中出现的列 才合法
• 在 WHERE 条件中不能出现 聚合函数, 分组后才可以
• HAVING 对分组后的数据进行条件过滤
• 为了解决笛卡尔积现象，使用等值连接查询。

等值连接查询

通常是在存在主键外键关联关系的表之间的连接进行，使用"="连接相关的表

n个表进行等值连接查询，最少需要n-1个等值条件来约束

例：查询每个部门的所有员工

SELECT e.ename,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno;

内连接查询

多表连接查询，用关键字inner join实现，inner可以省略，条件用ON连接，多个条件用（）

例：查询每个部门的所有员工

SELECT e.ename, d.deptno, d.dname FROM emp e JOIN dept d ON d.deptno = e.deptno;

外连接查询

• 多表连接查询
• 分为左外连接（ left outer join ） 和右外连接（ right outer join ）
• 条件用ON连接
• 左外连接中关键字JOIN左侧的表叫做主表（左表），右侧的表叫做从表（右表）。右外连接相反。
• 主表的去匹配从表的数据，如果主表与从表的数据满足ON 的条件返回结果。如果不满足，主表数据返回结果集中，从表用NULL 填充结果。

例：查询每个部门的所有员工

SELECT d.deptno ,e.ename,d.dname FROM emp e LEFT JOIN dept d ON d.deptno=e.deptno;

SELECT d.deptno ,e.ename,d.dname FROM emp e RIGHT JOIN dept d ON d.deptno=e.deptno;

自连接查询

在一个表之间进行连接查询

例：查询当前公司员工和所属上级员工的信息

SELECT e1.ename as 员工名称,e1.mgr as 领导编号,e2.ename FROM emp e1,emp e2 WHERE e1.mgr=e2.empno;

子查询

• 单列子查询: 返回单行单列数据的子查询
• 单行子查询: 返回单行多列数据的子查询
• 多行子查询: 返回数据是多行单列的数据
• 多列子查询：返回数据是单行多列的数据
• 关联子查询: 子查询中如果使用了外部主SQL中的表或列，就说这个子查询跟外部SQL是相关的

单行单列子查询

• 可以成对比较，也可以把子查询当成一张虚拟表使用

单列子查询

查看 emp 表中与 SMITH 岗位相同的员工信息

SELECT * FROM emp e WHERE job = (SELECT e.job FROM emp e WHERE ename = 'SMITH');

1. 先尝试查看 SMITH 所从事的岗位

SELECT job,ename  FROM emp WHERE ename='SMITH';

2. 查询从事 clerk 工作的员工

SELECT ename,job FROM emp WHERE JOB ='CLERK';

单行子查询

查询 与 SMITH 在同一个部门且岗位相同的员工的信息

SELECT e.* FROM emp e,dept d WHERE d.deptno=(SELECT deptno FROM emp WHERE ename = 'SMITH') AND e.job=(SELECT job FROM emp WHERE ename = 'SMITH') and e.deptno= d.deptno;

1. 查询 SMITH 所在的部门和从事的岗位

 SELECT e.ename,d.deptno,d.dname,e.job FROM dept d ,emp e WHERE ename='SMITH' AND d.deptno=e.deptno;

2. 查询在 20 部门从事 clerk 岗位的员工信息\

SELECT e.* FROM emp e,dept d WHERE d.deptno=20 AND e.job = 'CLERK' AND d.deptno=e.deptno;

多行子查询

多行记录操作符：in , all , any(some)

• in 子查询中所有的记录
• >any 表示大于子查询中的任意一个值，即大于最小值
• >all 表示大于子查询中的所有值，即大于最大的值

例：统计所有的员工分布的部门信息(IN)

SELECT * FROM dept d WHERE deptno in (SELECT e.deptno FROM emp e );

查询公司中工资比任意员工高的所有员工信息

SELECT * FROM emp WHERE salary > ANY(SELECT salary FROM emp);

查询公司中比所有的CLERK工资高但不是CLETK的员工

SELECT empno,ename,job,salary FROM emp WHERE salary > ALL (SELECT salary FROM emp WHERE job='CLERK' );

查询emp表中与20部门员工岗位相同的员工信息

SELECT * FROM emp WHERE job IN (SELECT DISTINCT job FROM emp WHERE deptno=20);

1. 查询20部门的所有岗位

2. 剔除重复行

SELECT DISTINCT job,deptno FROM emp WHERE deptno = 20;

3. 根据20部门的岗位来查询emp表中的员工、

多列子查询

例：查询公司中和员工SMITH相同薪水和奖金的员工

SELECT * FROM emp WHERE (salary,comm) = (SELECT salary,comm FROM emp WHERE ename='SMITH')   

关联子查询

关键字：EXISTS / NOT EXISTS (满足子查询条件/不满足子查询条件)

例：查询哪些员工与SMITH不在同一个部门

SELECT * FROM emp e WHERE NOT EXISTS ( SELECT * FROM emp p WHERE p.ename = 'SMITH' AND p.deptno = e.deptno );

查询哪些员工与SMITH在同一个部门

SELECT * FROM emp e WHERE EXISTS ( SELECT * FROM emp p WHERE p.ename = 'SMITH' AND p.deptno = e.deptno );

拓展

在 DELETE / UPDATE 中可以使用子查询吗？有限制吗？限制是什么？

答：DELETE 和 UPDATE 语句可以使用子查询，以便基于子查询返回的结果来更新或删除表中的数据。但是，子查询必须返回单个值，否则会出现错误。例如，如果子查询返回多个值，DELETE 或 UPDATE 语句将无法确定要删除或更新哪些行。