MySQL数据库-2

一、连接查询

1、基本概念

• 当查询结果来源于多张表时，需要将多张表连接成一个大的数据集，再选择 合适的结果返回；
• 连接查询可以通过连接运算符（连接条件）可以实现多个表查询；
• 内连接：查询的结果只显示两个表中满足连接条件的部分；

左连接：查询的结果为两个表匹配到的数据加左表特有的数据，对于右表中

不存在的数据使用 null 填充；

右连接：查询的结果为两个表匹配到的数据加右表特有的数据，对于左表中 不存在的数据使用 null 填充；

2、内连接

语法一：
select * from 表 1
inner join 表 2 on 表 1.字段=表 2.字段

两个表内连接的例子

/* 创建表 a */
drop table if exists a;
create table a (
id int , /*id 号,*/
name varchar(10) /*姓名*/
);
/* 向表 a 插入数据 */
insert into a values
('1', '张三'),
('2', '李四'),
('3', '王五');

/* 创建表 b */
drop table if exists b;
create table b (
id int , /*id 号,*/
score int /*成绩*/
);
/* 向表 b 插入数据 */
insert into b values
('1', '30'), 
('1', '40'), 
('2', '50'), 
('2', '60');

表 a 和表 b 通过字段 id 内连接查询后的结果
执行如下查询语句:
select * from a INNER JOIN b on a.id = b.id;

查询结果列说明:

表 a 和表 b 的列都会出现在查询结果中，由于表 a 和表 b 都有列名叫 id， 为了区分，表 b 的 id 列名显示为 id1。

查询结果行说明:

记录只保留表 a 和表 b 中 id 字段相同的记录。

students 表与 scores 表数据说明

例 1：查询学生信息和学生的成绩，只显示有成绩的学生
查询的结果只显示 students 和 scores 两个表中 studentNo 相同的部分；
select * from students
inner join scores on students.studentNo = scores.studentNo;

语法二：
select * from 表 1,表 2
where 表 1.字段=表 2.字段

语法二的例子：查询学生信息和成绩，只显示有成绩的学生
select * from students, scores
where students.studentNo = scores.studentNo;

选择部分字段

例 3：查询学生信息，要求只显示姓名、课程号、成绩
select name, courseNo, score from students
inner join scores on students.studentNo = scores.studentNo;

表的别名

例 4：查询学生信息，要求只显示姓名、课程号、成绩，其中表 students 别名为 stu，表 scores 别名为 sc
select name, courseNo, score from students stu
inner join scores sc on stu.studentNo = sc.studentNo;

带有 where 条件的内连接

例 5：查询王昭君的信息，要求只显示姓名、课程号、成绩
select name, courseNo, score
from students stu inner join scores sc
on stu.studentNo = sc.studentNo
where stu.name = '王昭君';

带有 and 逻辑运算符的内连接查询

例 6：查询姓名为’王昭君’,并且成绩小于 90 的信息，要求只显示姓名、成绩
select name, score
from students stu inner join scores sc
on stu.studentNo = sc.studentNo
where stu.name = '王昭君' and sc.score < 90;

多表内连接查询

例 7：查询学生信息和成绩以及成绩对应的课程名称
select * from students stu
inner join scores sc on stu.studentNo = sc.studentNo
inner join courses cs on sc.courseNo = cs.courseNo;

带有 order by 排序的内连接查询

例 8：查询成绩最高的男生信息，要求显示姓名、课程名、成绩
select name, coursename, score from students stu
inner join scores sc on stu.studentNo = sc.studentNo
inner join courses cs on sc.courseNo = cs.courseNo
where stu.sex = '男'
order by sc.score desc limit 1;

3、左连接

语法：
select * from 表 1
left join 表 2 on 表 1.字段=表 2.字段

例 1：查询所有学生的信息以及成绩，包括没有成绩的学生
students 表中有些学生在 score 表中没有成绩；
左连接查询的结果为 students 和 scores 两个表匹配到的数据加 students 表特
有的数据，对于 scores 表中不存在的数据使用 null 填充；
select * from students stu
left join scores sc on stu.studentNo = sc.studentNo;

4、右连接

语法：
select * from 表 1
right join 表 2 on 表 1.字段=表 2.字段

例 1：查询所有课程的信息，包括没有成绩的课程
courses 表中 courseNo 为 7 的课程 python 在 score 表中没有对应的成绩；
右连接查询的结果为 scores 和 courses 两个表匹配到的数据加 courses 表特有
的数据，对于 scores 表中不存在的数据使用 null 填充；
select * from scores sc
right join courses cs on sc.courseNo = cs.courseNo;

二、自关联

1、设计省信息的表结构 provinces：

• proid：省编号；
• pname：省名称；

设计市信息的表结构 citys：

• cityid：市编号；
• cname：市名称；
• proid：市所属的省编号；

citys 表的 proid 表示城市所属的省，对应着 provinces 表的 proid 值；

思考：观察两张表发现，citys 表比 provinces 表多一个字段 proid，其它字段的类型都是一样的； 意义：存储的都是地区信息，而且每种信息的数据量有限，没必要增加一个 新表，或者将来还要存储区、乡镇信息，都增加新表的开销太大；

答案：定义表 areas，结构如下：

• id；
• name；
• pid；

表中的一条记录，可以记录省，也可以记录市

记录为省的时候，字段说明：

• id：代表省 id；
• name：代表省名称；
• pid：因为省没有所属的省份，所以 pid 写为 null。

记录为市的时候，字段说明：

• id：代表市 id；
• name：代表市名称；
• pid：代表市所属省 id。

这就是自关联，表中的某一字段，关联了这个表中的另外一字段，但是它们 的业务逻辑含义是不一样的，城市信息的 pid 引用的是省信息的 id；

在这个表中，结构不变，以后还可以扩充区县、乡镇街道、村社区等信息。

2、创建区域表 areas,向表插入数据

例 1：查询一共有多少个省
select count(*) from areas where pid is null;

例 2：查询广东省的所有城市
select * from areas as p
inner join areas as c on p.id = c.pid
where p.name='广东省';

三、子查询

1、定义

在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句。

2、主查询

外层的第一条 select 语句为主查询

3、主查询和子查询的关系

• 子查询是嵌入到主查询中；
• 子查询是辅助主查询的, 要么充当条件, 要么充当数据源；
• 子查询是可以独立存在的语句, 是一条完整的 select 语句；

标量子查询：例 1 格式二中, 子查询返回的结果只有一个值（一行一列）， 这种称之为标量子查询；

例 1：查询大于平均年龄的学生记录
格式一: 用两条 SELECT 语句实现
第一步：查询平均年龄
select avg(age) from students;
第二步：根据第一步的结果，查询大于平均年龄的学生记录
select * from students where age > 30.1667;
格式二: 用子查询实现
select * from students where age > (select avg(age) from students);

列子查询: 例 2 格式二中, 子查询返回的结果是一列（一列多行），这种称 之为列子查询。

例 2：查询 30 岁的学生的成绩
格式一: 用两条 SELECT 语句实现
第一步：查询 30 岁的学号
select studentNo from students where age=30;
第二步：根据学号查询成绩
select score from scores where studentNo in ('001','003','011');

格式二: 用子查询实现
select score from scores where studentNo in (select studentNo from students where
age=30);

表级子查询: 例 3 中, 子查询返回的结果是多行多列（一个表），这种称之 为表级子查询。

例 3：用子查询，查询所有女生的信息和成绩
select * from (select * from students where sex='女') stu
INNER JOIN scores sc ON stu.studentNo = sc.studentNo;