详解Mysql——第一篇/连接查询

ゞ浴缸里的玫瑰 2024-03-23 20:55 22阅读 0赞

**mysql的连接查询，相必在网上都能找到很多的教程，博主今天不做老话常谈，不走重复路线**

1.建表

–1.学生表 
    Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别 
    –2.课程表 
    Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号 
    –3.教师表 
    Teacher(t_id,t_name) –教师编号,教师姓名 
    –4.成绩表 
    Score(s_id,c_id,s_score) –学生编号,课程编号,分数

2.准备测试数据

--建表
    --学生表
    CREATE TABLE `Student`(
    `s_id` VARCHAR(20),
    `s_name` VARCHAR(20) NOT NULL DEFAULT '',
    `s_birth` VARCHAR(20) NOT NULL DEFAULT '',
    `s_sex` VARCHAR(10) NOT NULL DEFAULT '',
    PRIMARY KEY(`s_id`)
    );
    --课程表
    CREATE TABLE `Course`(
    `c_id` VARCHAR(20),
    `c_name` VARCHAR(20) NOT NULL DEFAULT '',
    `t_id` VARCHAR(20) NOT NULL,
    PRIMARY KEY(`c_id`)
    );
    --教师表
    CREATE TABLE `Teacher`(
    `t_id` VARCHAR(20),
    `t_name` VARCHAR(20) NOT NULL DEFAULT '',
    PRIMARY KEY(`t_id`)
    );
    --成绩表
    CREATE TABLE `Score`(
    `s_id` VARCHAR(20),
    `c_id` VARCHAR(20),
    `s_score` INT(3),
    PRIMARY KEY(`s_id`,`c_id`)
    );
    --插入学生表测试数据
    insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
    insert into Student values('02' , '钱电' , '1990-12-21' , '男');
    insert into Student values('03' , '孙风' , '1990-05-20' , '男');
    insert into Student values('04' , '李云' , '1990-08-06' , '男');
    insert into Student values('05' , '周梅' , '1991-12-01' , '女');
    insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
    insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
    insert into Student values('08' , '王菊' , '1990-01-20' , '女');
    --课程表测试数据
    insert into Course values('01' , '语文' , '02');
    insert into Course values('02' , '数学' , '01');
    insert into Course values('03' , '英语' , '03');
    
    --教师表测试数据
    insert into Teacher values('01' , '张三');
    insert into Teacher values('02' , '李四');
    insert into Teacher values('03' , '王五');
    
    --成绩表测试数据
    insert into Score values('01' , '01' , 80);
    insert into Score values('01' , '02' , 90);
    insert into Score values('01' , '03' , 99);
    insert into Score values('02' , '01' , 70);
    insert into Score values('02' , '02' , 60);
    insert into Score values('02' , '03' , 80);
    insert into Score values('03' , '01' , 80);
    insert into Score values('03' , '02' , 80);
    insert into Score values('03' , '03' , 80);
    insert into Score values('04' , '01' , 50);
    insert into Score values('04' , '02' , 30);
    insert into Score values('04' , '03' , 20);
    insert into Score values('05' , '01' , 76);
    insert into Score values('05' , '02' , 87);
    insert into Score values('06' , '01' , 31);
    insert into Score values('06' , '03' , 34);
    insert into Score values('07' , '02' , 89);
    insert into Score values('07' , '03' , 98);

3.注意事项

-- 准备条件,去掉 sql_mode 的 ONLY_FULL_GROUP_BY 否则此种情况下会报错:
    -- Expression #1 of select list is not in group by clause and contains nonaggregated column 'userinfo.
    -- 原因：
    -- MySQL 5.7.5和up实现了对功能依赖的检测。如果启用了only_full_group_by SQL模式(在默认情况下是这样)，
    -- 那么MySQL就会拒绝选择列表、条件或顺序列表引用的查询，这些查询将引用组中未命名的非聚合列，而不是在功能上依赖于它们。
    -- (在5.7.5之前，MySQL没有检测到功能依赖项，only_full_group_by在默认情况下是不启用的。关于前5.7.5行为的描述，请参阅MySQL 5.6参考手册。)
    -- 执行以下个命令，可以查看 sql_mode 的内容。
    SHOW SESSION VARIABLES;
    SHOW GLOBAL VARIABLES;
    select @@sql_mode;
    -- 更改
    set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
    set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

1.  闲话少说，打开一下话匣子吧  
    连接查询有很多，今天博主侧重点分析左外联查。小伙伴们把左外联查彻底搞明白了，其它查询也是同样的道理

先看一组例子  
（1）左表是student，右表是score，连接条件仅限于等号

> select st.*,sc.* from student st left join score sc  
> on sc.s\_id=st.s\_id

执行结果：  
![在这里插入图片描述][e95ae56a24ae4e8bb58b3af364265fc7.png]  
（2）左表是student，右表是score，连接条件包括and

> select st.*,sc.* from student st left join score sc  
> on sc.s\_id=st.s\_id and sc.s\_id is not null

![在这里插入图片描述][9f07453cdd654f39b25ddb241f1ef662.png]

(3) 左表是score，右表是student，连接条件包括and

> select st.*,sc.* from score sc left join student st  
> on sc.s\_id=st.s\_id and sc.s\_id is not null

执行结果：  
![在这里插入图片描述][bbc933563ff248b4a2e783f6eb2d89fa.png]  
（3）左表是student，右表是score，连接条件仅限于等号，但是存在where筛选条件

> select st.*,sc.* from student st left join score sc  
> on sc.s\_id=st.s\_id where sc.s\_id is not NULL

![在这里插入图片描述][73b035eaa50a4978a0864fc877574d6a.png]

（4）左表是student，右表是score  
select st.*,sc.* from student st  
left join score sc on sc.s\_id=st.s\_id and st.s\_name in (‘赵雷’,‘钱电’)  
![在这里插入图片描述][5cc99c15d3fd42899acbc5807e9c381a.png]  
（5）左表是student，右表是score  
select st.*,sc.* from student st  
left join score sc on sc.s\_id=st.s\_id  
where st.s\_name in (‘赵雷’,‘钱电’)  
![在这里插入图片描述][564995aeb8bf4c459b49f322d6a39d83.png]

（6）左表是student，右表是score  
select st.*,sc.* from student st  
left join score sc on sc.s\_id=st.s\_id and sc.s\_id in(‘01’,‘02’)  
![在这里插入图片描述][34706de9b4ea42a187608e75eade8c85.png]  
select st.*,sc.* from student st  
left join score sc on sc.s\_id=st.s\_id and sc.s\_id in(‘01’,‘03’)  
![在这里插入图片描述][903224746f6a4bf79260365bf65edc2d.png]

（7）左表是student，右表是score  
select st.*,sc.* from student st  
left join score sc on sc.s\_id=st.s\_id and sc.s\_id in(‘01’)  
![在这里插入图片描述][c1892a9e81514006a415fb9ec39239f7.png]

（8）左表是student，右表是score  
select st.*,sc.* from student st  
left join score sc on sc.s\_id=st.s\_id and st.s\_name =‘钱电’ and sc.s\_id in(‘01’)  
![在这里插入图片描述][6cca40fadf6942fc940e93137c86b5f5.png]

（9）左表是student，右表是score  
select st.*,sc.* from student st  
left join score sc on sc.s\_id=st.s\_id and sc.s\_id in(‘01’,‘02’,‘03’,‘04’,‘05’,‘06’,‘07’,‘08’)![在这里插入图片描述][524976496bef424bb7852c4b80c36b79.png]

小结：  
（1）左外联查，以左表作为主表，右表数据根据连接条件往左表拼凑数据，最终形成一张完整的临时表  
（2）连接条件分为连接中筛选与连接后筛选，其中on后面的条件做的都是连接中的筛选，where后面的连接条件做的都是连接后的筛选  
（3）拓展一下，on后面的筛选条件可以像where后面的筛选条件组合使用，比如and, or, >,>=,=,<=,<,in等等  
（4）on后面的筛选条件以左表的筛选条件为主，

> 1- 当左表连接条件与右表连接条件同时存在时，由左右两个表共同的筛选条件决定着最终形成的临时表的数据条数。即左右两表的筛选条件是相同的地位（同时成立的关系）。  
> 2- 当只有左表筛选条件时，左表筛选条件决定着最终形成的临时表的数据条数。  
> 3- 当只有右表筛选条件时，右表筛选条件决定着最终形成的临时表的数据条数。

第二章节：多表联查（在这里我们以三表联查举例）

select st.*,sc.*,c.\* from student st  
left join score sc on sc.s\_id=st.s\_id  
left join course c on c.c\_id=sc.c\_id  
![在这里插入图片描述][009546bcb64f4190a9faf217bf5a8222.png]  
select st.*,sc.*,c.\* from student st  
left join score sc on sc.s\_id=st.s\_id and st.s\_name =‘赵雷’  
left join course c on c.c\_id=sc.c\_id  
解析：由于在连接查询时需要满足st.s\_name =‘赵雷’，所以根据赵雷这个人对应的s\_id=01,去和score，course表做连接匹配  
![在这里插入图片描述][3ed16e56dcf445e789a494cf69990db6.png]  
select st.*,sc.*,c.\* from student st  
left join score sc on sc.s\_id=st.s\_id and st.s\_name =‘赵雷’ or sc.s\_score >90  
left join course c on c.c\_id=sc.c\_id  
解析：student，score，course三个表做连接查询，其中穿插条件st.s\_name =‘赵雷’ or sc.s\_score >90  
条件st.s\_name =‘赵雷’ 表示在三表连接后形成的新行必须要包含st\_name=赵雷’  
条件sc.s\_score >90 表示在三表连接后形成的新行必须满足score>90，并且它是这样的逻辑，拿sc表的s\_score>90的所有s\_score值去依次和student表里的数据做匹配，最终是拼接的结果是两行钱电，两行孙风，两行李云等等。　  
![在这里插入图片描述][8f86aaa523ae47758f8011e919197c85.png]  
select st.*,sc.*,c.\* from student st  
left join score sc on sc.s\_id=st.s\_id  
left join course c on c.c\_id=sc.c\_id and c.c\_name=‘语文’  
![在这里插入图片描述][9bcb5587aeaf47e89ee15ed6e4ea6745.png]

select st.*,sc.*,c.\* from student st  
left join score sc on sc.s\_id=st.s\_id  
left join course c on c.c\_id=sc.c\_id  
where st.s\_name=‘赵雷’ or sc.s\_score >90  
![在这里插入图片描述][65bcf025043143abbfd143dc5f6ca83d.png]  
select c.c\_name,count(\*) as ‘总数’ from student st  
left join score sc on sc.s\_id=st.s\_id  
left join course c on c.c\_id=sc.c\_id  
group by c.c\_name  
![在这里插入图片描述][9d189f067a244f69ac76ad490d34eeb5.png]

小结：  
（1）多表联查时，多个连接条件需要同时成立去拼接最终临时表的数据，  
（2）连接查询是通过一个个条件去做匹配连接的，在连接中根据条件一行行的做拼接  
(3) 同两张表连接一样，多表联查的where关键字也起着在查询后做筛选的作用

[e95ae56a24ae4e8bb58b3af364265fc7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/d9b9a076b1394d5aaef72c3c17be9151.png
[9f07453cdd654f39b25ddb241f1ef662.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/b63462e288984bedbc1add9309e3cfb1.png
[bbc933563ff248b4a2e783f6eb2d89fa.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/70316964d68041dcb5ff92bb4bae534c.png
[73b035eaa50a4978a0864fc877574d6a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/68d3795771604c92a37082adead6549f.png
[5cc99c15d3fd42899acbc5807e9c381a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/5ff51ac8e31c4cd88c4c1298291aa0d3.png
[564995aeb8bf4c459b49f322d6a39d83.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/25bed9332f96413da2256ae9536503a3.png
[34706de9b4ea42a187608e75eade8c85.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/52f3acc19f4f408a8a1c77aca7a313c8.png
[903224746f6a4bf79260365bf65edc2d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/c81f7cc35a7b4b5fab077efb6858ceac.png
[c1892a9e81514006a415fb9ec39239f7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/4927f8972cea41328227fd80881f24d4.png
[6cca40fadf6942fc940e93137c86b5f5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/14f3ca20d757403dbdf6838d666ef173.png
[524976496bef424bb7852c4b80c36b79.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/336eb43925e74bd48a16d4f6930857e8.png
[009546bcb64f4190a9faf217bf5a8222.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/56924e65c3014d2ab84383a206ddcf25.png
[3ed16e56dcf445e789a494cf69990db6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/d2c6f57f7b0d49ebb5dc3dd09bae618c.png
[8f86aaa523ae47758f8011e919197c85.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/7a9fd4c299c14ef8a8d8e4417cd7c529.png
[9bcb5587aeaf47e89ee15ed6e4ea6745.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/f1a48930930a43c9bcb88e81e18dc731.png
[65bcf025043143abbfd143dc5f6ca83d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/dd8cb9768dbe496991b53343de981624.png
[9d189f067a244f69ac76ad490d34eeb5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/69e1a754f8d34982967ced43a1cc04e0.png