MySQL多表关联查询+子查询_R

MySQL多表关联查询+子查询

-- **************关联查询（多表查询）****************
-- 多表查询规则：1）确定查询哪些表。 2）确定哪些字段。 3）表与表之间连接关系(规律：连接条件数量=表数量-1)。
-- ***1.交叉连接查询（笛卡尔乘积，查询条数=表1数据条数*表2数据条数。基本不会用）=cross join
SELECT empName AS '姓名', deptName AS '部门名称' FROM emp, dept;
-- 需求：查询员工及其所在部门（显示员工姓名，部门名称）
-- ***2.内连接查询（只有满足条件的结果才会显示。使用最频繁）
-- 2.1语法一，隐式内连接
SELECT empName AS '姓名', deptName AS '部门名称'  -- 2）确定哪些字段
FROM emp, dept       -- 1) 先确定查询哪些表
WHERE emp.deptId=dept.id;-- 3) 表与表之间连接关系
-- 2.2语法二  显示内连接
SELECT empName AS '姓名', deptName AS '部门名称' 
FROM emp 
INNER JOIN dept
ON emp.deptId=dept.id;  -- 注意这里用on
-- 使用别名
SELECT e.empName AS '姓名', d.deptName AS '部门名称' 
FROM emp e
INNER JOIN dept d  -- inner join 等价于 join
ON e.deptId=d.id;
-- 需求：查询每个部门的员工
-- ***3.左(外)链接查询（使用左边表的数据去匹配右边表的数据，如果符合连接条件的结果则显示，如果不符合连接则显示null）
-- 注意：左外连接：左边的数据一定会完整显示。
SELECT d.deptName "部门", e.empName "员工"
FROM dept d 
LEFT OUTER JOIN  emp e  -- left outer join 等价于 left join 
ON d.id=e.deptId;
-- ***4.右(外)链接查询（使用右边表的数据去匹配左边表的数据，如果符合连接条件的结果则显示，如果不符合连接则显示null）
SELECT d.deptName "部门", e.empName "员工"
FROM emp e 
RIGHT OUTER JOIN  dept d  -- left outer join 等价于 left join 
ON d.id=e.deptId;
-- *****************修改表数据*******************
ALTER TABLE emp ADD bossId INT;
UPDATE emp SET bossId=1 WHERE id = 2;
UPDATE emp SET bossId=2 WHERE id = 3;
UPDATE emp SET bossId=3 WHERE id = 4;
UPDATE emp SET bossId=4 WHERE id = 5;
-- ***5.自连接查询
-- 需求：查询员工及其上司
SELECT e.empName "员工", s.empName "上司"
FROM emp e
LEFT OUTER JOIN emp s
ON e.bossId = s.id;
-- ***6.子查询（嵌套查询）
-- 需求：员工id=1所在部门的所有员工信息
-- 1.查询员工id=1所在的部门
SELECT deptId FROM emp WHERE emp.id=1;
-- 2.查询该部门所有员工信息
SELECT * FROM dept WHERE dept.id=(SELECT deptId FROM emp WHERE emp.id=1);

重点：子查询

  注意：

   1) 子查询作为中间表时，必须给中间表取一个别名。

    2)当子查询查询的结果有多个时，作为条件使用时用 in

    3）注意当子查询的结果作为中间表，当做条件使用时，最好用 in ，因为不确定中间表的结果数据条数，用in比较保险。

注意1证明：

 

注意2证明： 需求4：查询“html”课程考试成绩为60-80分的学生名单

①一个单表查询

 

②用①中查出的数据，作为子查询的条件数据。

③用②中查出的数据，作为子查询的中间表。

注意：上面子查询结果作为中间表使用，必须取别名，否则报错：错误码: 1248 Every derived table must have its own alias

 死

④用③中查出的数据，作为子查询的条件数据。完成查询

SELECT * 
FROM student s
WHERE s.sId IN (SELECT sId FROM(SELECT se.sId, se.score FROM studentExam se WHERE se.geId=(SELECT ge.geId FROM gradeExam ge WHERE ge.eName="html课程")) m WHERE score BETWEEN 60 AND 80);