有关连接查询、子查询和组合查询

高级查询

背景

如何使用sql查询两张及以上表格的数据
开端的语法是连接查询（又称多表查询，当查询的字段来自于多个表时，就会用到连接查询）
- 其意义在于，避免了无效连接后出现的笛卡尔乘积现象（表1 有m行，表2有n行，结果=m*n行）
接着是联合查询、子查询

案例代码（student、subject、score三张表）

CREATE DATABASE IF NOT EXISTS `student_examination_sys` DEFAULT CHARSET UTF8 COLLATE UTF8_GENERAL_CI ;
USE `student_examination_sys`;
DROP TABLE IF EXISTS `student`;
CREATE TABLE  `student`(
`id` VARCHAR(3) NOT NULL,
`name` VARCHAR(10) NOT NULL,
`age` INT NOT NULL,
`sex` VARCHAR(2) NOT NULL
)ENGINE=INNODB DEFAULT CHARSET = UTF8;

INSERT  INTO `student` VALUES
('001','张三',18,'男'),
('002','李四',20,'女'),
('003','王五',21,'男'),
('004','赵六',19,'男'),
('005','春喜',17,'女'),
('006','夏依',17,'女');
SELECT * FROM `student`;


DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`(
`id` VARCHAR(4) NOT NULL,
`subject` VARCHAR(10) NOT NULL,
`teacher` VARCHAR(10) NOT NULL,
`description` VARCHAR(20) NOT NULL
)ENGINE=INNODB DEFAULT CHARSET = UTF8;

INSERT INTO `subject` VALUES
('1001','语文','王老师','本次考试比较简单'),
('1002','数学','刘老师','本次考试比较难');
SELECT  * FROM `subject`;

DROP TABLE IF EXISTS `score`;
CREATE TABLE `score`(
`id` INT NOT NULL,
`student_id` VARCHAR(3) NOT NULL,
`subject_id` VARCHAR(4) NOT NULL,
`score` DOUBLE NOT NULL
);

INSERT INTO `score` VALUES
(1,'001','1001',90),
(2,'002','1001',60),
(3,'003','1001',70),
(4,'004','1001',88),
(5,'005','1001',62),
(6,'006','1001',59),
(7,'001','1002',70),
(8,'002','1002',60.5),
(9,'003','1002',76),
(10,'004','1002',82),
(11,'005','1002',54),
(12,'006','1002',89);
SELECT  * FROM ``score`;

score.png

subject.png

student.png

有关于笛卡尔查询的代码
查询score中每行数据的老师姓名
笛卡尔现象代码：

SELECT 
  s.id as 科目,
  sub.teacher as 老师
FROM
  score as s, 
  SUBJECT AS sub ;

建立有效连接后的代码

SELECT 
  sub.id,
  sub.teacher 
FROM
  score AS s,
  SUBJECT AS sub 
WHERE sub.id = s.subject_id ;

分类：

按年代分类：
sql92标准:仅仅支持内连接
sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接

按功能分类：

内连接：
- 等值连接
- 非等值连接
- 自连接
外连接：
- 左外连接
- 右外连接
- 全外连接
交叉连接

一、sql92标准

1、等值连接

① 多表等值连接的结果为多表的交集部分
②n表连接，至少需要n-1个连接条件
③ 多表的顺序没有要求
④一般需要为表起别名
⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

之前的案例，就是一个等值连接

SELECT 
  sub.id,
  sub.teacher 
FROM
  score AS s,
  SUBJECT AS sub 
WHERE sub.id = s.subject_id ;

为表起别名

①提高语句的简洁度
②区分多个重名的字段

注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定

可以使用AND关键字增加筛选

查询score表中的学生姓名，并且学生年龄要大于18

SELECT 
  score,
  stu.name,
  stu.age 
FROM
  score AS s,
  student AS stu 
WHERE s.student_id = stu.id 
  AND stu.age > 18 ;

可以使用分组查询、排序

查询每个科目保留两位小数的平均分，按照降序排列

SELECT 
  ROUND(AVG(score), 2) AS 平均分,
  sub.subject AS 科目 
FROM
  score AS s,
  SUBJECT AS sub 
WHERE s.subject_id = sub.id 
GROUP BY 科目
ORDER BY 平均分 DESC ;

可以三表连接

查询每个科目保留两位小数的平均分（要求：学生成绩及格的），按照降序排列

SELECT 
  ROUND(AVG(score), 2) AS 平均分,
  sub.subject AS 科目 
FROM
  score AS s,
  SUBJECT AS sub,
  student AS stu 
WHERE s.subject_id = sub.id 
  AND s.student_id = stu.id 
  AND s.score >= 60 
GROUP BY sub.subject 
ORDER BY 平均分 DESC ;

2、非等值连接

创建level表

DROP TABLE IF EXISTS `levels` ;

CREATE TABLE `levels` (
  low INT NOT NULL,
  high INT NOT NULL,
  LEVEL VARCHAR (1) NOT NULL
) ;

INSERT INTO `levels` 
VALUES
  (0, 60, 'D'),
  (61, 70, 'C'),
  (71, 80, 'B'),
  (81, 100, 'A') ;

SELECT 
  * 
    FROM
    `levels` ;

查询每个学生具体成绩的等级

SELECT 
  s.score,
  l.level 
FROM
  score AS s,
  levels AS l 
WHERE s.score BETWEEN l.low 
  AND l.high ;

自连接

挑选出语文成绩高于数学成绩的学生

SELECT 
  s1.student_id,
  s1.score AS 语文,
  s2.score AS 数学 
FROM
  score AS s1,
  score AS s2 
WHERE s1.student_id = s2.student_id 
  AND s1.score > s2.score ;

二、sql99语法

语法：

select 查询列表
from 表1 别名【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】

连接类型分类：

内连接（★）：inner
外连接
左外(★):left 【outer】
右外(★)：right 【outer】
交叉连接：cross

一）内连接

语法：

select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件;

分类：
等值
非等值
自连接

特点：
①添加排序、分组、筛选
②inner可以省略
③ 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集

1、等值连接

查询score中的老师

SELECT 
  sub.id,
  sub.teacher 
FROM
  score AS s 
  INNER JOIN `SUBJECT` AS sub 
    ON sub.id = s.subject_id ;

可以使用AND关键字增加筛选

查询score表中的学生姓名，并且学生年龄要大于18

SELECT 
  score,
  stu.name,
  stu.age 
FROM
  score AS s,
  student AS stu 
WHERE s.student_id = stu.id 
  AND stu.age > 18 ;

SELECT 
  score,
  stu.name,
  stu.age 
FROM
  score AS s 
  INNER JOIN student AS stu 
    ON s.student_id = stu.id 
WHERE stu.age > 18 ;

可以使用分组查询、排序

查询每个科目保留两位小数的平均分，按照降序排列

SELECT 
  ROUND(AVG(score), 2) AS 平均分,
  sub.subject AS 科目 
FROM
  score AS s,
  SUBJECT AS sub 
WHERE s.subject_id = sub.id 
GROUP BY sub.subject 
ORDER BY 平均分 DESC ;

SELECT 
  ROUND(AVG(score), 2) AS 平均分,
  sub.subject AS 科目 
FROM
  score AS s 
  INNER JOIN SUBJECT AS sub 
    ON s.subject_id = sub.id 
GROUP BY sub.subject 
ORDER BY 平均分 DESC ;

可以三表连接

查询每个科目保留两位小数的平均分（要求：学生成绩及格的），按照降序排列

SELECT 
  ROUND(AVG(score), 2) AS 平均分,
  sub.subject AS 科目 
FROM
  score AS s,
  SUBJECT AS sub,
  student AS stu 
WHERE s.subject_id = sub.id 
  AND s.student_id = stu.id 
  AND s.score >= 60 
GROUP BY sub.subject 
ORDER BY 平均分 DESC ;

SELECT 
  ROUND(AVG(score), 2) AS 平均分,
  sub.subject AS 科目 
FROM
  score AS s 
  INNER JOIN SUBJECT AS sub 
    ON s.subject_id = sub.id 
  INNER JOIN student AS stu 
    ON s.student_id = stu.id 
    where s.score >= 60 
GROUP BY sub.subject 
ORDER BY 平均分 DESC ;

2、非等值连接

DROP TABLE IF EXISTS `levels` ;

CREATE TABLE `levels` (
  low INT NOT NULL,
  high INT NOT NULL,
  LEVEL VARCHAR (1) NOT NULL
) ;

INSERT INTO `levels` 
VALUES
  (0, 60, 'D'),
  (61, 70, 'C'),
  (71, 80, 'B'),
  (81, 100, 'A') ;

SELECT 
  * 
  FROM
    `levels` ;

查询每个学生具体成绩的等级

SELECT 
  s.score,
  l.level 
FROM
  score AS s,
  levels AS l 
WHERE s.score BETWEEN l.low 
  AND l.high ;

SELECT 
  s.score,
  l.level 
FROM
  score AS s 
  INNER JOIN levels AS l 
    ON s.score BETWEEN l.low 
    AND l.high ;

自连接

挑选出语文成绩高于数学成绩的学生

SELECT 
  s1.student_id,
  s1.score AS 语文,
  s2.score AS 数学 
FROM
  score AS s1,
  score AS s2 
WHERE s1.student_id = s2.student_id 
  AND s1.score > s2.score ;

SELECT 
  s1.student_id,
  s1.score AS 语文,
  s2.score AS 数学 
FROM
  score AS s1 
  INNER JOIN score AS s2 
    ON s1.student_id = s2.student_id 
WHERE s1.score > s2.score ;

二、外连接

应用场景：用于查询一个表中有，另一个表没有的记录

特点：
1、外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接，left join左边的是主表
右外连接，right join右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果

修改表数据使连接存在null值

UPDATE 
  score 
SET
  student_id = '007' 
WHERE student_id = '001' ;

左外连接 score主表

SELECT 

  * 
    FROM
    score AS s 
    LEFT OUTER JOIN student AS stu 
    ON stu.id = s.student_id 
    WHERE score > 85 ;

右外连接 score主表

SELECT 
  * 
    FROM
    student AS stu 
    RIGHT OUTER JOIN score AS s 
    ON stu.id = s.student_id 
    WHERE score > 85 ;

左外连接 student主表

SELECT 
  * 
    FROM
    student AS stu 
    LEFT OUTER JOIN score AS s 
    ON stu.id = s.student_id 
    WHERE score > 85 ;

右外连接 student主表

SELECT 
  * 
    FROM
    score AS s 
    RIGHT OUTER JOIN student AS stu 
    ON stu.id = s.student_id 
    WHERE score > 85 ;

交叉连接

SELECT 
  * FROM
    score AS s 
    CROSS JOIN student AS stu ;

sql92和 sql99pk

功能：sql99支持的较多
可读性：sql99实现连接条件和筛选条件的分离，可读性较高

子查询

含义：
出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询

分类：
按子查询出现的位置：

select后面：
仅仅支持标量子查询
from后面：
- 支持表子查询
where或having后面：★
- 标量子查询（单行） √
- 列子查询（多行） √
- 行子查询

exists后面（相关子查询）
- 表子查询
按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）

一、where或having后面

标量子查询（单行子查询）
列子查询（多行子查询）
行子查询（多列多行）

特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用

> < >= <= = <>

列子查询，一般搭配着多行操作符使用
in、any/some、all

④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

1.标量子查询★

查询语文成绩比春喜高的学生清单
先找春喜的语文成绩

UPDATE 
  score 
SET
  student_id = '001' 
WHERE student_id = '007' ;

SELECT 
  score 
FROM
  score AS s 
  INNER JOIN student AS stu 
    ON s.student_id = stu.id 
WHERE stu.name = '春喜' 
  AND s.subject_id = '1001' ;

再找成绩比春喜高的学生

SELECT 
  score AS 语文,
  stu.name 
FROM
  score s 
  INNER JOIN student AS stu 
    ON s.student_id = stu.id 
WHERE s.subject_id = '1001' 
  AND score > 
  (SELECT 
    score 
  FROM
    score AS s 
    INNER JOIN student AS stu 
      ON s.student_id = stu.id 
  WHERE stu.name = '春喜' 
    AND s.subject_id = '1001') ;

支持分组及排序查询

查询学生两科目平均分低于整体平均分的人
先查询平均分

SELECT 
  AVG(score) 
FROM
  score AS s 
  INNER JOIN student AS stu 
    ON s.student_id = stu.id

再查询小于平均分的学生

SELECT 
  AVG(score) AS 平均分,
  stu.name 
FROM
  score AS s 
  INNER JOIN student AS stu 
    ON s.student_id = stu.id 
GROUP BY stu.name 
HAVING AVG(score) < 
  (SELECT 
    AVG(score) 
  FROM
    score )
    order by score desc;

2.列子查询（多行子查询）★

查询编号为学生为李四和王五的数学成绩

SELECT 
    stu.id, stu.name, score AS 数学
FROM
    score AS s
        INNER JOIN
    student AS stu ON s.student_id = stu.id
WHERE
    s.subject_id = '1002'
        AND stu.id IN (SELECT 
            id
        FROM
            student
        WHERE
            name IN ('李四' , '王五'));

3、行子查询（结果集一行多列或多行多列）

查询编号最大的最高成绩

UPDATE 
  score 
SET
  score = 100 
WHERE id = 12 ;

SELECT 
  * 
    FROM
    score ;

SELECT 
  score 
FROM
  score 
WHERE (id, score) = 
  (SELECT 
    MAX(id),
    MAX(score) 
  FROM
    score) ;

二、select后面

仅仅支持标量子查询

查询及格的学生总个数

SELECT 
  COUNT(*) 
FROM
  score AS s 
  INNER JOIN student AS stu 
    ON s.student_id = stu.id 
WHERE s.score >= 60 ;

SELECT 
  (SELECT 
    COUNT(*) 
  FROM
    score AS s 
    INNER JOIN student AS stu 
      ON s.student_id = stu.id 
  WHERE s.score >= 60) AS 学生及格总数 ;

三、from后面

将子查询结果充当一张表，要求必须起别名

查询每个老师下的及格学生个数

SELECT 
    COUNT(*) AS `mount`, sub.teacher, sub.id AS 科目
FROM
    score AS s
        INNER JOIN
    SUBJECT AS sub ON sub.id = s.subject_id
        INNER JOIN
    student AS stu ON s.student_id = stu.id
WHERE
    s.score >= 60
GROUP BY sub.id;

SELECT 
    sa.teacher AS 老师, sa.mount AS 及格数
FROM
    (SELECT 
        COUNT(*) AS `mount`, sub.teacher, sub.id AS 科目
    FROM
        score AS s
    INNER JOIN SUBJECT AS sub ON sub.id = s.subject_id
    INNER JOIN student AS stu ON s.student_id = stu.id
    WHERE
        s.score >= 60
    GROUP BY sub.id) AS sa;

四、exists后面（相关子查询）

语法：
exists(完整的查询语句)
结果：
1或0

查询是否存在不及格的学生

SELECT 
    EXISTS( SELECT 
            s.id
        FROM
            score AS s
        WHERE
            score < 60) AS 是否不及格;

联合查询

union 联合合并：将多条查询语句的结果合并成一个结果

语法：
查询语句1
union
查询语句2
union
...

应用场景：
要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时

特点：★
1、要求多条查询语句的查询列数是一致的！
2、要求多条查询语句的查询的每一列的类型和顺序最好一致
3、union关键字默认去重，如果使用union all 可以包含重复项

查询编号为001的学生姓名和编号为1001的科目

SELECT 
    *
FROM
    student AS stu
WHERE
    id = '001' 
UNION SELECT 
    *
FROM
    SUBJECT AS sub
WHERE
    id = '1001';

关于三者的异同

子查询：

子查询是多表查询的一种形式，是一种次要查询，返回的结果数据被用于主查询的条件中，它返回的结果可以是一行或多行
查询效率较低，性能差，因为底层数据是笛卡儿积，
可读性相对较高
代码较麻烦
当只需要唯一的列，用子查询

连接查询

连接查询是对两个以上表逐记录（一行一行地）进行相关查询
主要使用join关键字，配合inner （内连接）left outer （左外连接）right outer（右外连接）等使用
性能高
代码简洁
可读性较差
当需要连接多个表时，用联结查询更好，效率高

组合查询

union关键字
要求返回的列结构类似，列类型必须兼容
union默认去重，使用union all可以返回所有结果
用union查询时，只能使用一条ORDER BY 语句，且只能出现在最后一条SELECT语句之后

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 203,937评论 6赞 478
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,503评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 150,712评论 0赞 337
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,668评论 1赞 276
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,677评论 5赞 366
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,601评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,975评论 3赞 396
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,637评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,881评论 1赞 298
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,621评论 2赞 321
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,710评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,387评论 4赞 319
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,971评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,947评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,189评论 1赞 260
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 44,805评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,449评论 2赞 342

有关连接查询、子查询和组合查询

高级查询

背景

案例代码（student、subject、score三张表）

一、sql92标准

1、等值连接

为表起别名

可以使用AND关键字增加筛选

可以使用分组查询、排序

可以三表连接

2、非等值连接

自连接

二、sql99语法

语法：

连接类型分类：

一）内连接

1、等值连接

可以使用AND关键字增加筛选

可以使用分组查询、排序

可以三表连接

2、非等值连接

自连接

二、外连接

sql92和 sql99pk

子查询

一、where或having后面

1.标量子查询★

支持分组及排序查询

2.列子查询（多行子查询）★

3、行子查询（结果集一行多列或多行多列）

二、select后面

三、from后面

查询每个老师下的及格学生个数

四、exists后面（相关子查询）

联合查询

关于三者的异同

推荐阅读更多精彩内容