MYSQL多表查询

• 多表查询

  由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

1. 一对多 ( 多对一 )
2. 多对多
3. 一对一

select * from 表一，表二......

注：无效连接或者漏写连接条件，会出现笛卡尔乘积现象

• 等值连接查询

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

--查询每个分类的所有商品信息

SELECT category.name,goods.name FROM goods,category WHERE goods.category_no = category.no;

        2. 表的别名 :

    ① . tableA as 别名 1 , tableB as 别名 2 ;

    ② . tableA 别名 1 , tableB 别名 2 ;

    注意：一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名来指定字段。

• 自连接查询

#查询当前公司员工和所属上级员工的信息
select
    e1.emp_no as 员工编号,
    e1.e_name as 员工姓名,
    e2.emp_no as 领导编号,
    e2.e_name as 领导姓名
from
    emp as e1,
    emp as e2
where
e1.mgr =e2.emp_no

• 内连接查询

内连接查询使用 inner join 关键字实现， inner 可以省略。内连接查询时，条件用 on 连接，多个条件使用 () 将其括起来.

1. 内连接查询的是两张表交集部分的数据。

--查询每个种类的所有商品
select c.name, g.name from goods g join category c on c.no = g.category_no; 

    

        2. 和等值查询差不多

• 外连接

       外连接分为左外连接（ left outer join ） 和右外连接（ right outer join ）其值 outer 可以省略。外连接查询时，条件用 on 连接，多个条件使用 () 将其括起来.左外连接表示以左表为主表，右外连接表示以右表为主表。查询时将主表信息在从表中进行匹配

1. 左外连接

SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;    

       2.右外连接

SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;

注;左外连接和右外连接是可以相互替换的，只需要调整在连接查询时SQL中，表结构的先后顺序就可以了。而我们在日常开发使用时，更偏向于左外连接。

• 子查询

1. 单行子查询

-- 查询零食种类的所有商品信息
select * from goods where category_no = (select no from category where name = '零食');
-- 查询部门名称是 ‘ ACCOUNTING’ 的所有员工信息
select * from emp where deptno = (select deptno from dept where dname = 'ACCOUNTING');

     2.多列子查询 

-- 查询公司中和员工***相同薪水和奖金的员工
select * from emp e1 where (e1.sal,e1.comm) = (select e2.sal,e2.comm from emp e2 where e2.ename = '张青')

      3.多行子查询

     IN : 在指定的集合范围之内，多选一

    NOT IN : 不在指定的集合范围之内

    ANY : 子查询返回列表中，有任意一个满足即可

    ALL : 子查询返回列表的所有值都必须满足

-- 统计所有的员工分布在那些部门的信息
select * from category where category.no in (select category_no from goods);

-- 查询公司中比任意一个员工的工资高的所有员工
select * from emp e1 where e1.sal > any (select e1.sal from emp e2);

-- 查询公司中比所有的 Stock Clerk 工资高但不在 33部门 的员工
select * from emp e1 where e1.sal > all(select e2.sal from e2.empwhere w2.joblike '%助理');

>any 表示大于子查询中的任意一个值，即大于最小值

>all 表示大于子查询中的所有值，即大于最大的值

• 关联子查询

关联子查询与外部查询（主查询）之间存在联系，并且内部子查询的结果依赖于外部查询的值。换句话说，内部子查询的执行取决于外部查询的每一行

SELECT student_name,
(SELECT score FROM scores WHERE subject = 'Math' AND students.student_id = scores.student_id) AS math_score FROM students;

• 综合查询

-- 统计每个部门入职日期在 1980-1-1 日之后的人数，按照人数降序展示，展示第一页，每页显示5条数据

SELECT DISTINCT emp.deptno 

FROM emp JOIN dept ON emp.deptno =dept.deptno 

WHERE HIREDATE >= '1980-01-01' 

GROUP BY emp.deptno

HAVING count(*) >= 2 ORDER BY count(*) DESC LIMIT 0, 5;

-- 统计每个部门入职日期在 1980-1-1 日之后的人数，按照人数降序展示，展示第一页，每页显示5条数据
SELECT DISTINCT emp.deptno 
FROM emp JOIN dept ON emp.deptno =dept.deptno 
WHERE HIREDATE >= '1980-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

• IFNULL的用法

MySQL IFNULL 函数是 MySQL 控制流函数之一，它接受两个参数，如果不是 NULL ，则返回第一个参数。 否则， IFNULL 函数返回第二个参数。

SELECT IFNULL(num, '暂无') FROm cart;
# 如果行数据num为null，查询结果中显示‘暂无’，如果不是null，显示num的值

注： 应避免在 WHERE 子句中使用 IFNULL 函数，因为它会降低查询的性能。如果要检查值是否为 NULL ，则可以在 WHERE 子句中使用 IS NULL 或 IS NOT NULL 。

• CASE语句

CASE 语句遍历条件并在满足第一个条件时返回一个值（如 IF-THEN-ELSE 语句）。因此，一旦条件为真，它将停止读取并返回结果。如果没有条件为真，它将返回 ELSE 子句中的值。如果没有ELSE部分且没有条件为真，则返回NULL。

CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END
# condition1, condition2, ...conditionN 条件
# result1, result2, ...resultN 条件为真时返回的值
# END 表示结束
SELECT CustomerName, City, Country
FROM Customers
ORDER BY
(CASE
WHEN City IS NULL THEN Country
ELSE City
END);
# 如果city是NULL 按照country排序
-- 按照成绩输出等级
SELECT id, score, CASE
WHEN score < 60 THEN 'D'
WHEN score < 70 THEN 'C'
WHEN score < 80 THEN 'B'
END '成绩等级' FROM score;