多表查询：在查询为多张表的数据时，通常在from表和表之间使用逗号,作为间隔符号。

1.内连接inner join，相当于两个集合关键字段的全交叉记录。

• 1：select * from table_name1 inner join table_name2;
• 2：select * from table_name1,table_name2;

2.等值连接，将有等值条件筛选的称为等值连接，也就是相同外键约束的组合。

• 1：select * from table_name1 inner join table_name2  on table_name1.col_name=table_name2.col_name;
• 2：select * from table_name1,table_name2 where table_name1.col_name=table_name2.col_name;

3.外连接。

• 左连接left join：select * from table_name1 left jion table_name2 on table_name1.id=table_name2.id; 

 存在两张表，其中a表作为主表，b表作为副表，a表的记录数多于b表的话，那么当a表为主表显示时候，b表中的记录不够则自动用null去填充。

• 右连接right join：select * from a right join b on a.id=b.id;

同左连接思想一样，但是这个右连接的主表是b，副表是a，其他与左连接相似，当副表记录不够时用null填充。

4.全连接。

MySQL的全连接关键字是Union和Union all，而不是full join，full join关键字是Oracle中存有的。

• (select * from table_name1) union (select * from table_name2);

注意：

1.通过全连接union连接的两张表的列数必须相同。

2.合并的两张表中以第一张表作为主要表，也就是他们合并的记录取值的字段是第一张表的字段，而第二张表的字段消失。

3.通常来说，我们一般采用的全连接是union all进行合并，因为union耗时较长。

4.union连接的SQL字句，单个字句不写order by，因为不会存有排序的效果。

多表查询

在关系型数据库中，表与表之间是有练习的，所以在实际应用中，经常使用多表查询。多表查询就是同时查询两个或两个以上的表

在 MySQL 中，多表查询主要有 交叉连接，内连接，外连接，子查询

交叉连接

交叉连接 ( CROSS JOIN ) 一般用来返回连接表的笛卡尔积

语法格式如下：

# 多表交叉连接时，使用 `cross join` 或者 `,` 皆可，前者是官方建议的标准写法
select <字段名> from <tab_name> cross join <tab_name> [where子句]
select <字段名> from <tab_name>,<tab_name> [where子句]

当连接的表之间没有关系时，我们会省略掉 where子句，这时返回结果就是两个表的笛卡尔积，返回结果数量就是两个表的数据行相乘，如果每个表有 1000 行，那么返回数量就有 1000*1000=1000000 行，数据量是非常巨大的！

如果在交叉连接时使用 where子句，MySQL会先生成两个表的笛卡尔积，然后再选择满足 where条件的记录。因此，表的数量较多时，交叉连接会非常非常慢，一般情况下不建议使用交叉连接

笛卡尔积

笛卡尔积( Cartesian product ) 是指两个集合 x 和 y 的乘积

例如，有两个集合，它们值如下：

A = {1,2}
B = {3,4,5}

# 集合 A*B 的结果集为：
A×B={(1,3), (1,4), (1,5), (2,3), (2,4), (2,5) };

# 集合 B*A 的结果集为：
B×A={(3,1), (3,2), (4,1), (4,2), (5,1), (5,2) };

以上 AB 和 BA 的结果就叫做两个集合各自的笛卡尔积

内连接

内连接 ( inner join ) 使用 inner join 关键字连接两张表，并使用 on 子句来设置连接条件

语法格式如下：

select <字段名> from <tab_name> <tab_name> inner join <tab_name> [on子句]

多个表连接时，再 from 后边连续使用 inner join 或 join 即可

案例操作：

# 在 学生表 和 课程表中，查询 学生对应的课程
select s.name c.course_name from student as s inner join course as c on s.course_id = c.id

注意：当对多个表进行查询时，要在 select 语句后面指定字段是来源自哪一张表
语法为 表名.列名 ，如果表名较长，可以给表设置别名，这样就可以直接在 select 后写 表的别名.列名

外连接

内连接查询的结果都是符合连接条件的结果，为外连接会先将连接的表分为基表和参考表，再以基表为依据返回满足与不满足条件的记录

外连接区分为 左外连接 和 右外连接

左外连接

左外连接又称为左连接，使用 left outer join 关键字连接两个表，也可以简写为 left join，并使用 on 设置连接条件

语法格式如下：

select <字段名> from <tab1> left join <tab2> [on子句]

上述语法中，tab1 为基表，tab2 为参考表，左连接查询时，可以查询处 tab1 中的所有记录和 tab2 中匹配连接条件的记录。如果 tab1 的某行在 tab2 中没有匹配行，那么在返回结果中，tab2 的字段值均为空值 NULL

右外连接

右外连接又称为右连接，右连接是左连接的反向连接。使用 right outer join 关键字连接两个表，可以简化为 right join ，并使用 on 子句来设置连接条件

语法格式如下：

select <字段名> from <tab1> right outer <tab2> [on子句]

与左连接相反，右连接以 tab2 为基表，tab1 为参考表，可以查询出 tab2 中所有记录和 tab1 中匹配连接条件的记录。如果 tab2 的某行在 tab1 中没有匹配项，那么在返回结果中，tab1 的字段值均为 NULL

多个表 左/右 连接时，在 on 子句后连续使用 left/right join 即可

子查询

子查询是 MySQL 中比较常用的查询方法，通过子查询可以实现多表查询。子查询指将一个查询语句嵌套在另一个查询语句中。子查询可以在 select,update 和 delete 语句中使用，而且可以进行多层嵌套，实际开发中，子查询经常出现在 where 语句中

语法格式如下：

select <字段> from <tab_name>
    where <字段><操作符>(子查询)
    );

其中，操作符可以是 in，not in，exists，not exists 等关键字

案例：

# 查询学习 python 课程的学生
select name from student s
    where s.course_id = (select id from course c where c.name = "Python")

# 查询学习 python 和 html 的学生
SELECT name FROM student s WHERE s.course_id in
    (SELECT id FROM course c WHERE c.name="Python" OR c.name="Html");

进阶用法：

子查询语句可以嵌套在 SQL 语句中任何表达式出现的位置

select (子查询) from 表名；

select * from (子查询) as 别名；

一、表架构

• student(sid,sname,sage,ssex) 学生表
• course(cid,cname,tid) 课程表
• sc(sid,cid,score) 成绩表
• teacher(tid,tname) 教师表

二 、建表sql语句

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `cid` int(11) NOT NULL COMMENT '课程号',
  `cname` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '课程名',
  `tid` int(11) NULL DEFAULT NULL COMMENT '老师号',
  PRIMARY KEY (`cid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES (3001, '英语', 1);
INSERT INTO `course` VALUES (3002, '数学', 2);
INSERT INTO `course` VALUES (3003, '物理', 3);
INSERT INTO `course` VALUES (3004, '语文', 4);

-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc`  (
  `sid` int(11) NOT NULL COMMENT '学生号',
  `cid` int(11) NOT NULL COMMENT '课程号',
  `score` int(11) NULL DEFAULT NULL COMMENT '成绩'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES (101, 3001, 90);
INSERT INTO `sc` VALUES (102, 3001, 85);
INSERT INTO `sc` VALUES (103, 3001, 76);
INSERT INTO `sc` VALUES (104, 3002, 87);
INSERT INTO `sc` VALUES (105, 3001, 92);
INSERT INTO `sc` VALUES (101, 3002, 81);
INSERT INTO `sc` VALUES (102, 3002, 93);
INSERT INTO `sc` VALUES (103, 3002, 73);
INSERT INTO `sc` VALUES (104, 3002, 65);
INSERT INTO `sc` VALUES (105, 3002, 96);
INSERT INTO `sc` VALUES (101, 3003, 85);
INSERT INTO `sc` VALUES (102, 3003, 76);
INSERT INTO `sc` VALUES (103, 3003, 63);
INSERT INTO `sc` VALUES (104, 3003, 59);
INSERT INTO `sc` VALUES (105, 3003, 56);
INSERT INTO `sc` VALUES (101, 3004, 100);
INSERT INTO `sc` VALUES (102, 3004, 83);
INSERT INTO `sc` VALUES (103, 3004, 75);
INSERT INTO `sc` VALUES (104, 3004, 69);
INSERT INTO `sc` VALUES (105, 3004, 50);
INSERT INTO `sc` VALUES (106, 3001, 60);
INSERT INTO `sc` VALUES (106, 3001, 60);

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `sid` int(11) NOT NULL COMMENT '学生号',
  `sname` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '姓名',
  `sage` int(11) NULL DEFAULT NULL COMMENT '年龄',
  `ssex` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '性别',
  PRIMARY KEY (`sid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (101, '王明', 18, '女');
INSERT INTO `student` VALUES (102, '王天', 19, '男');
INSERT INTO `student` VALUES (103, '张三', 18, '男');
INSERT INTO `student` VALUES (104, '李四', 19, '女');
INSERT INTO `student` VALUES (105, '王五', 20, '男');
INSERT INTO `student` VALUES (107, '万源', 17, '男');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `tid` int(11) NOT NULL COMMENT '老师号',
  `tname` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '老师名称',
  PRIMARY KEY (`tid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES (1, '王老师');
INSERT INTO `teacher` VALUES (2, '李老师');
INSERT INTO `teacher` VALUES (3, '赵老师');
INSERT INTO `teacher` VALUES (4, '宋老师');

SET FOREIGN_KEY_CHECKS = 1;

三、查询

1、多表查询语法

select  字段1,字段2... from 表1,表2... [where 条件]

• 查询学生表和成绩表所有信息

select * from student,sc where student.sid = sc.sid

注意: 多表查询时,一定要找到两个表中相互关联的字段,并且作为条件使用

2、多表链接查询

多表连接查询语法(重点)
SELECT 字段列表
    FROM 表1  INNER|LEFT|RIGHT JOIN  表2
ON 表1.字段 = 表2.字段;

①内连接查询 (只显示符合条件的数据)

• 查询学生表和成绩表所有信息

select * from student inner join sc  on student.sid = sc.sid;

内连接查询与多表联合查询的效果是一样的.

②左外连接查询 (左边表中的数据优先全部显示)

• 查询学生表和成绩表所有信息

select * from student left join sc  on student.sid = sc.sid;

学生表中的数据全部都显示,而成绩表中的数据符合条件的才会显示,不符合条件的会以 null 进行填充


③右外连接查询 (右边表中的数据优先全部显示)

• 查询学生表和成绩表所有信息

select * from student right join sc  on student.sid = sc.sid;

正好与[左外连接相反]

④全连接查询(显示左右表中全部数据)

全连接查询：是在内连接的基础上增加 左右两边没有显示的数据
注意: mysql并不支持全连接 full JOIN 关键字
注意: 但是mysql 提供了 UNION 关键字.使用 UNION 可以间接实现 full JOIN 功能

select * from student left join sc on student.sid = sc.sid
UNION
select * from student right join sc on student.sid = sc.sid;

注意: UNION 和 UNION ALL 的区别:UNION 会去掉重复的数据,而 UNION ALL 则直接显示结果

3、三表查询

①查询所有人的英语成绩

语法：
select 表1.字段,表2.字段,表3.字段 from 表1 join 表2 on 表1.关联字段 = 表2.关联字段 join 表3 on 表2.关联字段 = 表3.关联字段 where....;

select student.sname,course.cname,sc.score from student join sc on student.sid = sc.sid join course on sc.cid = course.cid where course.cname = "英语";

4、其它查询

1、查询“3001”课程比“3003”课程成绩高的所有学生的学号与分数；

SELECT a.sid,a.score FROM (SELECT sid,score FROM sc WHERE cid="3001") a,

      (SELECT sid,score FROM sc WHERE cid="3003") b

     WHERE a.score>b.score AND a.sid=b.sid

2、查询平均成绩大于60分的同学的学号和平均成绩

SELECT sid,AVG(score)

FROM sc

GROUP BY sid HAVING AVG(score)>60

3、查询所有同学的学号、姓名、选课数、总成绩

select s.sid as 学号,s.sname as 姓名,count(sc.cid) as 选课数,SUM(sc.score) as 总成绩
from student s INNER JOIN sc sc
on s.sid=sc.sid
GROUP BY s.sid

4、查询姓“李”的老师的个数；

select count(distinct(Tname))
  from teacher
  where tname like '李%';

5、查询学过“王老师”课的同学的学号、姓名

SELECT s.sid AS 学号,s.sname AS 姓名 
FROM student s, sc sc, course c, teacher t 
WHERE s.sid = sc.sid AND sc.cid = c.cid AND c.tid = t.tid AND tname = "王老师"

6、查询没有学过“王老师”课的同学的学号、姓名

SELECT s.sid, s.sname
FROM student s
WHERE s.sid NOT IN (
SELECT s.sid
FROM student s, sc sc, course c, teacher t
WHERE s.sid=sc.sid AND c.cid=sc.cid AND t.tid=c.tid AND t.tname="王老师")