MySql 之多表查询

1. 多表查询分类:

• 合并结果集(了解)
• 连接查询
• 子查询

2. 合并结果集

• 要求被合并的表中, 列的类型和列数相同.
• UNION : 去除重复行.
• UNION ALL : 不去除重复行.

// 合并 ab 表和 cd 表的查询结果集
SELECT * FROM ab
UNION ALL
SELECT * FROM cd;

3. 连接查询(一次查询多张表)

1. 分类:

• 内连接
• 外连接(左外连接, 右外连接, 全外连接(MySQL 不支持))
• 自然连接(属于一种简化形式)

3.1 内连接

// 连接查询, 即一次查询多张表, 查询的结果为笛卡尔积形式
// 例如, 有两个表(a,b,c)  (1,2)
//       查询结果: (a1,a2,b1,b2,c1,c2); 其中有较多垃圾信息, 可以使用条件去除
    SELECT * FROM emp, dept;

// 内连接查询出的所有记录都满足条件.
// 方言
    SELECT * FROM 表1 别名1, 表2 别名2 WHERE 别名1.xx==别名2.xx;
// 标准 (建议使用)
    SELECT * FROM 表1 别名1 INNER JOIN 表2 别名2 ON 别名1.xx=别名2.xx;
// 自然
    SELECT * FROM 表1 别名1 NATURAL JOIN 表2 别名2;  (自动添加 where 条件, 可读性查)

3.2 外连接

• 外连接有一主一次, 左外即左表为主!

// 左外
    SELECT * FROM 表1 别名1 LEFT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx;
    左表(表1)记录无论是否满足条件都会查询出来, 而右表只有满足条件才能查询出来, 左表中不满足条件的记录,
    右表部分都为 NULL.

// 左外自然
    SELECT * FROM 表1 别名1 NATURAL LEFT OUTER JOIN 表2 别名2;

// 右外
    SELECT * FROM 表1 别名1 RIGHT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx;
    右表(表2)记录无论是否满足条件都会查询出来, 而左表只有满足条件才能查询出来, 右表中不满足条件的记录,
    左表部分都为 NULL

// 右外自然:
    SELECT * FROM 表1 别名1 NATURAL RIGHT OUTER JOIN 表2 别名2;

// 全连接: 可以使用 UNION 来连接左连接和右连接, 形成全连接.

4. 子查询

1. 查询中有查询(可以通过查看 SELECT 关键字的个数确定!!)
2. 出现的位置:
   • WHERE 后作为条件存在;
   • FROM 后作为表存在(多行多列)
3. 条件:
   • 单行单列作为查询条件
     SELECT * FROM 表1 别名1 WHERE 列1 [=, >, <, >=, <=, !=] (SELECT 列 FROM 表2 别名2 WHERE 条件);
   • 多行单列作为查询条件
     SELECT * FROM 表1 别名1 WHERE 列1 [IN,ALL,ANY] (SELECT 列 FROM 表2 别名2 WHERE 条件);
   • 单行多列作为查询条件
     SELECT * FROM 表1 别名1 WHERE (列1, 列2) IN (SELECT 列1, 列2 FROM 表2 别名2 WHERE 条件);
   • 多行多列
     SELECT * FROM 表1 别名1, (SELECT ...) 别名2 WHERE 条件;

// 示例:
    // 查询本公司工资最高的员工的详细信息
    SELECT * FROM emp WHERE sal=(SELECT MAX(sal) FROM emp);
    // 先从 emp 中查询出最高工资,结果为单行单列. 然后在从 emp 中查询出 "sal=最高工资" 的详细信息

    // 查询工作和部门与张三完全相同的员工 (查询条件为单行多列)
    SELECT * FROM emp WHERE (job,deptno) IN (SELECT job,deptno FROM emp WHERE ename='张三');

    // 查询本公司在财务部的员工姓名和工资 (查询条件为多行多列)
    SELECT e.name,e.sal FROM (SELECT * FROM emp WHERE deptno=30) e;

练习: 查出年份, 利润, 年度增长比

源表:

结果表:

// 查询语句:
SELECT y1.*, CONCAT(y1.zz-y2.zz)/y2.zz/100, '%') 增长比
FROM tb_year y1 LEFT OUTER JOIN tb_year y2
ON y1.year=y2.year+1;

参考资料:

• JavaWeb 视频教程