1、join多表连接查询

案例准备

  1. use school
  2. student: 学生表
  3. sno: 学号
  4. sname: 学生姓名
  5. sage: 学生年龄
  6. ssex： 学生性别
  8. teacher: 教师表
  9. tno: 教师编号
  10. tname: 教师名字
  12. course: 课程表
  13. cno: 课程编号
  14. cname: 课程名字
  15. tno: 教师编号
  17. sc: 成绩表
  18. sno: 学号
  19. cno: 课程编号
  20. score: 成绩
  22. # 项目构建
  23. drop database school;
  24. CREATE DATABASE school CHARSET utf8;
  25. USE school
  27. CREATE TABLE student(
  28. sno INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
  29. sname VARCHAR(20) NOT NULL COMMENT '姓名',
  30. sage TINYINT UNSIGNED  NOT NULL COMMENT '年龄',
  31. ssex  ENUM('f','m') NOT NULL DEFAULT 'm' COMMENT '性别'
  32. )ENGINE=INNODB CHARSET=utf8;
  34. CREATE TABLE course(
  35. cno INT NOT NULL PRIMARY KEY COMMENT '课程编号',
  36. cname VARCHAR(20) NOT NULL COMMENT '课程名字',
  37. tno INT NOT NULL  COMMENT '教师编号'
  38. )ENGINE=INNODB CHARSET utf8;
  40. CREATE TABLE sc (
  41. sno INT NOT NULL COMMENT '学号',
  42. cno INT NOT NULL COMMENT '课程编号',
  43. score INT  NOT NULL DEFAULT 0 COMMENT '成绩'
  44. )ENGINE=INNODB CHARSET=utf8;
  46. CREATE TABLE teacher(
  47. tno INT NOT NULL PRIMARY KEY COMMENT '教师编号',
  48. tname VARCHAR(20) NOT NULL COMMENT '教师名字'
  49. )ENGINE=INNODB CHARSET utf8;
  51. INSERT INTO student(sno,sname,sage,ssex)
  52. VALUES (1,'zhang3',18,'m');
  54. INSERT INTO student(sno,sname,sage,ssex)
  55. VALUES
  56. (2,'zhang4',18,'m'),
  57. (3,'li4',18,'m'),
  58. (4,'wang5',19,'f');
  60. INSERT INTO student
  61. VALUES
  62. (5,'zh4',18,'m'),
  63. (6,'zhao4',18,'m'),
  64. (7,'ma6',19,'f');
  66. INSERT INTO student(sname,sage,ssex)
  67. VALUES
  68. ('oldboy',20,'m'),
  69. ('oldgirl',20,'f'),
  70. ('oldp',25,'m');
  73. INSERT INTO teacher(tno,tname) VALUES
  74. (101,'oldboy'),
  75. (102,'hesw'),
  76. (103,'oldguo');
  78. DESC course;
  79. INSERT INTO course(cno,cname,tno)
  80. VALUES
  81. (1001,'linux',101),
  82. (1002,'python',102),
  83. (1003,'mysql',103);
  85. DESC sc;
  86. INSERT INTO sc(sno,cno,score)
  87. VALUES
  88. (1,1001,80),
  89. (1,1002,59),
  90. (2,1002,90),
  91. (2,1003,100),
  92. (3,1001,99),
  93. (3,1003,40),
  94. (4,1001,79),
  95. (4,1002,61),
  96. (4,1003,99),
  97. (5,1003,40),
  98. (6,1001,89),
  99. (6,1003,77),
  100. (7,1001,67),
  101. (7,1003,82),
  102. (8,1001,70),
  103. (9,1003,80),
  104. (10,1003,96);
  106. SELECT * FROM student;
  107. SELECT * FROM teacher;
  108. SELECT * FROM course;
  109. SELECT * FROM sc;

1.1、笛卡尔乘积

SELECT * FROM teacher , course;
或者:
SELECT * FROM teacher JOIN course;
或者:
SELECT * FROM teacher INNER JOIN course;

拿着 teacher表每行数据和 course表逐行进行组合, 显示两层for循环的实现逻辑, Simple-Nextloop (嵌套循环方式)得出的结果, 会有部分数据是无意义的
注意: 生产禁用这种方式, 会极大增加 mysql压力

1.2、内连接

参考链接
https://www.runoob.com/mysql/mysql-join.html

#内连接是取两会张表拼接, 有共同值的部分
SELECT * FROM teacher JOIN course ON teacher.tno=course.tno;
或者
SELECT * FROM teacher INNER JOIN course ON teacher.tno=course.tno;

#on 相当于 where条件过滤

+-----+--------+------+--------+-----+
| tno | tname  | cno  | cname  | tno |
+-----+--------+------+--------+-----+
| 101 | oldboy | 1001 | linux  | 101 |
| 102 | hesw   | 1002 | python | 102 |
| 103 | oldguo | 1003 | mysql  | 103 |
+-----+--------+------+--------+-----+

1.3、外连接

1.3.1、左外连接

#left join: 获取左表所有记录, 过滤右表没有对应匹配的记录
SELECT * FROM teacher LEFT JOIN course ON teacher.tno=course.tno;

+-----+--------+------+--------+------+
| tno | tname  | cno  | cname  | tno  |
+-----+--------+------+--------+------+
| 101 | oldboy | 1001 | linux  |  101 |
| 102 | hesw   | 1002 | python |  102 |
| 103 | oldguo | 1003 | mysql  |  103 |
| 104 | oldx   | NULL | NULL   | NULL |
| 105 | oldw   | NULL | NULL   | NULL |
+-----+--------+------+--------+------+

1.3.2、右外连接

#right join: 与left join相反, 获取右表所有记录, 过滤左表没有对应匹配的记录

SELECT * FROM teacher RIGHT JOIN course ON teacher.tno=course.tno;

+------+--------+------+--------+-----+
| tno  | tname  | cno  | cname  | tno |
+------+--------+------+--------+-----+
|  101 | oldboy | 1001 | linux  | 101 |
|  102 | hesw   | 1002 | python | 102 |
|  103 | oldguo | 1003 | mysql  | 103 |
| NULL | NULL   | 1004 | k8s    | 108 |
+------+--------+------+--------+-----+

1.3.3、导出连接查询的结果集

#目录需要有 mysql用户权限, 指定分隔符为(,) 导出csv文件
vim my.cnf
secure_file_priv=""

SELECT * FROM teacher RIGHT JOIN course ON teacher.tno=course.tno into outfile '/data/join.csv' fields terminated by ',';

1.3.4、连接查询性能影响

连接查询相当于 两层 for循环, 合成一张表, on 条件过滤 相当于 if 取条件判断
对于大表来说会极其影响性能
优化方案: 给两张表关联的字段添加索引

1.4、多表连接语法

1.4.1、a和b有直接关联关系

#a表的z 和 b表的z有关联字段

select a.x,b.y
from a
join b on a.z=b.z
where
group by
having
order by
limit;

1.4.2、a和b没有直接关联关系

假如: a 和 c 有关, b 和 c 有关
a join c on a.i = c.i join b on c.n=b.n

方法:
1. 根据题意将所有涉及到的表找出来 a  b 
2. 找到a和b直接或者间接的关联条件 
3. 用join on 语句把所有表连接到一起 (连接后当成一张表操作)
4. 罗列其他查询条件

注意:
MySQL中建议连接的表不要超过4个, 否则需要考虑重新设计表
相当于多一个表多一层 for循环

1.5、练习

1.5.1、查询每位老师所教课程名称

SELECT teacher.tname,course.cname
FROM teacher JOIN course
ON teacher.tno=course.tno;

步骤分析:
1. 找出需要查询的表和关联条件
FROM teacher JOIN course
ON teacher.tno=course.tno;

2. 找出需要查询的内容, 老师 和 课程名
SELECT teacher.tname,course.cname
FROM teacher JOIN course
ON teacher.tno=course.tno;

1.5.2、统计每个学员, 学习课程的门数

1. 分析: 需要的数据再student表、sc表,course表, 但是sc表记录了课程名, 使用 student表和 course表连接
SELECT * 
FROM student
INNER JOIN sc
ON student.sno=sc.sno;

2. 根据 sno进行分组
SELECT student.sname, count(*) 
FROM student 
INNER JOIN sc
ON student.sno=sc.sno
GROUP BY student.sno;

3. 优化: 增加姓名和学号显示
SELECT concat("姓名",student.sname,"学号:",student.sno), count(*) 
FROM student 
INNER JOIN sc
ON student.sno=sc.sno
GROUP BY student.sno;