连接查询

1.全连接

  1. select distinct kc.课程名,xs_kc.课程号
  2.     from kc,xs_kc
  3.     where kc.课程号=xs_kc.课程号;
  4. #通过where语句中的“=”将两个表的属性相通,得到的是一个新表

例子：查询与雨落同学在同一个专业学习的学生的姓名

  1. SELECT b.姓名 FROM xs as a, xs as b  #将同一个表看做两个表来进行连接查询
  2. WHERE a.专业名=b.专业名
  3. AND a.姓名='雨落' and b.姓名 <>'雨落';

2.内连接 INNER JOIN

使用 INNER JOIN 连接语法能够确保不会忘记连接条件，而且 WHERE 子句在某些时候会影响查询的性能。

  1. select distinct 课程名,xs_kc.课程号
  2.     from kc inner join xs_kc
  3.     on(kc.课程号=xs_kc.课程号);  #on(XXX=XXX)可以替换为 using(XXX);
  4. #根据on关键字后面的连接条件，合并两个表，返回满足条件的行

ON和WHERE同时使用

  1. select 姓名,成绩
  2.     from xs join xs_kc 
  3.     on xs.学号=xs_kc.学号  #连接条件
  4.     where 课程号='206' and 成绩>=80; #查询条件

多表连接：

  1. select xs.学号,姓名,score.成绩,xk.课程名
  2.     from xs join score on xs.学号=score.学号
  3.     join xk on score.课程号=xk.课程号
  4.     where 成绩>=60;
  6. +--------+------+------+--------+
  7. | 学号   | 姓名 | 成绩 | 课程名 |
  8. +--------+------+------+--------+
  9. | 023130 | 骆瑜 |  100 | 数据库 |
  10. | 023130 | 骆瑜 |   98 | Java   |
  11. | 081211 | 刘华 |   66 | 数据库 |
  12. | 085531 | 张三 |   87 | Java   |
  13. +--------+------+------+--------+
  14. 4 rows in set (0.27 sec)

3.外连接查询（左右）

外连接先将连接的表分为基表和参考表，再以基表为依据返回满足和不满足条件的记录。

按照连接表的顺序，可以分为左外连接和右外连接

在左外连接的结果集中，除了匹配的行之外，还包括左表中有但在右表中不匹配的行，对于这样的行，从右表中选择的列的值被设置为 NULL，即左外连接的结果集中的 NULL 值表示右表中没有找到与左表相符的记录。

  1. #左连接查询
  2. mysql> SELECT name,dept_name
  3.     -> FROM tb_students_info s
  4.     -> LEFT OUTER JOIN tb_departments d
  5.     -> ON s.dept_id = d.dept_id;
  6. +--------+-----------+
  7. | name   | dept_name |
  8. +--------+-----------+
  9. | Dany   | Computer  |
  10. | Tom    | Economy   |
  11. | Lily   | NULL      |  #Lily NULL 指右表中没有匹配的行
  12. +--------+-----------+
  13. 10 rows in set (0.03 sec)
  1. #右连接查询
  2. mysql> SELECT name,dept_name
  3.     -> FROM tb_students_info s
  4.     -> RIGHT OUTER JOIN tb_departments d
  5.     -> ON s.dept_id = d.dept_id;
  6. +--------+-----------+
  7. | name   | dept_name |
  8. +--------+-----------+
  9. | Dany   | Computer  |
  10. | Green  | Chinese   |
  11. | Thomas | Chinese   |
  12. | Tom    | Economy   |
  13. | NULL   | History   | #NULL History指在左表中没有学生属于History
  14. +--------+-----------+
  15. 10 rows in set (0.00 sec)

子查询/嵌套查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从 MySQL 4.1 开始引入，在 SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。

常用操作符：

ANY（SOME）、ALL、IN 和 EXISTS。

子查询可以添加到 SELECT、UPDATE 和 DELETE 语句中，而且可以进行多层嵌套。子查询也可以使用比较运算符，如“<”、“<=”、“>”、“>=”、“！=”等。

  1. #IN
  2. select 学号,姓名
  3.     from xs
  4.     where 学号 in
  5.     (
  6.         select 学号
  7.         from score
  8.         where 课程号='1'
  9.         );
  10. #嵌套查询/子查询，查询选修了课程号为1的学生的学号姓名
  11. 先再score表中查询课程号=1的所有学号，再将学号与xs表的学号对应调出姓名
  12. +--------+------+
  13. | 学号   | 姓名 |
  14. +--------+------+
  15. | 023130 | 骆瑜 |
  16. | 081211 | 刘华 |
  17. | 085531 | 张三 |
  18. +--------+------+
  1. #运算符
  2. select 学号,姓名,专业名
  3.     from xs
  4.     where 出生日期 < all (
  5.         select 出生日期
  6.         from xs
  7.         where 专业名='电子商务'
  8.     );
  9. #查找出生日期比电子商务所有同学都早的学生
  1. #EXISTS
  2. #[NOT] EXISTS （select语句） 若子查询结果为空表则返回false
  4. > select 姓名
  5.     -> from xs
  6.     -> where exists
  7.     -> (
  8.     -> select * from score
  9.     -> where 学号=xs.学号 and 课程号='3'
  10.     -> );
  11. Empty set (0.00 sec)

例子

查询xs表中所有女同学和学号为081101的年龄差距

select 学号,姓名,year(出生日期)-year(
          (    select 出生日期
                    from xs
                    where 学号='081101')) as 年龄差距
            from xs
            where 性别='0';

查询 学号性别 都与081101一样的学生信息

select 学号,性别
    from xs
    where (性别,总学分)=( select 性别,总学分
                                from xs
                                where 学号='081101'
                                );