MySQL 多表操作

数据准备

创建用户表

create table user (
    id int primary key auto_increment,
    name varchar(20),
    age int
);

insert into user values
                     (1, '张三', 23),
                     (2, '李四', 24),
                     (3, '王五', 25),
                     (4, '赵六', 26);

id	name	age
1	张三	23
2	李四	24
3	王五	25
4	赵六	26

创建订单表

create table orderlist (
    id int primary key auto_increment,
    number varchar(30),
    uid int,
    constraint ou_fk1 foreign key (uid) references user(id)
);

insert into orderlist values
                          (1, 'hm001', 1),
                          (2, 'hm002', 1),
                          (3, 'hm003', 2),
                          (4, 'hm004', 2),
                          (5, 'hm005', 3),
                          (6, 'hm006', 3),
                          (7, 'hm007', NULL);

id	number	uid
1	hm001	1
2	hm002	1
3	hm003	2
4	hm004	2
5	hm005	3
6	hm006	3
7	hm007	null

并

$R$和$S$均为$n$元（列）关系，且满足相容性条件（列数相同且两表对应列的属性都相同），那么$R$和$S$的并的元组就由两表元组的集合组成，字段仍是组合前的$n$元字段。

用关系代数记为：
$$R\cup S$$
MySQL的例子请看第一道例题。

笛卡尔积

笛卡尔积就是把$k_1$行（个元组）$r$列（元）的关系$R$和$k_2$行（个元组）$s$列（元）的关系$S$拼接起来成一张表，$R$的每行都要和$S$的每一行左右拼接。故这张新表有$r+s$列（元），$k_1\times k_2$行（个元组）。

用关系代数笛卡尔积记作：

$$R\times S$$

在MySQL中，可以直接用以下方式求两张表的笛卡尔积：

select * from user, orderlist;

id	name	age	id	number	uid
4	赵六	26	1	hm001	1
3	王五	25	1	hm001	1
2	李四	24	1	hm001	1
1	张三	23	1	hm001	1
4	赵六	26	2	hm002	1
3	王五	25	2	hm002	1
2	李四	24	2	hm002	1
1	张三	23	2	hm002	1
4	赵六	26	3	hm003	2
3	王五	25	3	hm003	2
2	李四	24	3	hm003	2
1	张三	23	3	hm003	2
4	赵六	26	4	hm004	2
3	王五	25	4	hm004	2
2	李四	24	4	hm004	2
1	张三	23	4	hm004	2
4	赵六	26	5	hm005	3
3	王五	25	5	hm005	3
2	李四	24	5	hm005	3
1	张三	23	5	hm005	3
4	赵六	26	6	hm006	3
3	王五	25	6	hm006	3
2	李四	24	6	hm006	3
1	张三	23	6	hm006	3
4	赵六	26	7	hm007	null
3	王五	25	7	hm007	null
2	李四	24	7	hm007	null
1	张三	23	7	hm007	null

内连接

内连接就是等值连接，就是从两个关系（两张表）的笛卡尔积中选取两张表间含有一个或者多个共有属性的元组。用关系代数记为：
$$R\underset{A=B}{\bowtie }S$$
语法如下：

select 列名 from 表1 [inner] join 表2 on 条件;

inner可以不写，这种写法称为显示内连接。

比如以下示例：

查询用户信息和对应的订单信息

将user表和orderlist表按照用户id拼成一张表。

select * from user inner join orderlist on user.id = orderlist.uid;

查询结果：

id	name	age	id	number	uid
1	张三	23	1	hm001	1
1	张三	23	2	hm002	1
2	李四	24	3	hm003	2
2	李四	24	4	hm004	2
3	王五	25	5	hm005	3
3	王五	25	6	hm006	3

查询用户信息和对应的订单信息，起别名

可以给每一张表取别名，简化查询语句的书写。

select * from user u inner join orderlist o on u.id = o.uid;

查询的结果也是一样的：

id	name	age	id	number	uid
1	张三	23	1	hm001	1
1	张三	23	2	hm002	1
2	李四	24	3	hm003	2
2	李四	24	4	hm004	2
3	王五	25	5	hm005	3
3	王五	25	6	hm006	3

查询用户姓名、年龄和订单编号

select
    u.name,
    u.age,
    o.number
from
    user u
    inner join orderlist o
    on u.id = o.uid;

name	age	number
张三	23	hm001
张三	23	hm002
李四	24	hm003
李四	24	hm004
王五	25	hm005
王五	25	hm006

也可以直接从内连接的定义入手，直接从两张表笛卡尔积生成的新表中，选取指定两列字段相同的元组。

select
    u.name,
    u.age,
    o.number
from
    user u,
    orderlist o
where
    o.uid = u.id;

name	age	number
张三	23	hm001
张三	23	hm002
李四	24	hm003
李四	24	hm004
王五	25	hm005
王五	25	hm006

可以发现结果也是相同的，这种查询方式被称为隐式内连接。

外连接

外连接分为左外连接和右外连接。在内连接中，会丢弃掉相同属性集上没有匹配值的元组（某一行指定的两列若不相等，该行就会被舍弃）。

如果想让两张表中某一张的全部元组保留，就可以使用外连接，查询到的结果为内连接的元组加上某一张表剩余没有匹配的元组，这些元组在另一张表上的值设为null。

外连接用关系代数记为：
$$R⟗S$$

左外连接

查询左表的全部数据，和左右两张表有交集部分的数据。

左外连接用关系代数记为：
$$R⟕S$$

查询所有用户信息，以及用户对应的订单信息

select
    u.*,
    o.number
from
    user u
left outer join
    orderlist o
on u.id = o.uid;

id	name	age	number
1	张三	23	hm001
1	张三	23	hm002
2	李四	24	hm003
2	李四	24	hm004
3	王五	25	hm005
3	王五	25	hm006
4	赵六	26	null

右外连接

查询右表的全部数据，和左右两张表有交集部分的数据。

右外连接用关系代数记为：
$$R⟖S$$

查询所有订单信息，以及订单所属的用户信息

select
    o.*,
    u.name
from
    user u
right outer join
    orderlist o
on u.id = o.uid;

id	number	uid	name
1	hm001	1	张三
2	hm002	1	张三
3	hm003	2	李四
4	hm004	2	李四
5	hm005	3	王五
6	hm006	3	王五
7	hm007	null	null

子查询

子查询就是查询语句中嵌套了查询语句

查询年龄最高的用户姓名（结果是单行单列）

select name, age
from user
where age = (
    select max(age) from user
    );

name	age
赵六	26

查询张三和李四的订单信息（查询结果是多行单列）

select *
from orderlist
where uid in (
    select id from user
    where name in ('张三', '李四')
    );

id	number	uid
1	hm001	1
2	hm002	1
3	hm003	2
4	hm004	2

查询订单表中id大于4的订单信息和所属用户信息（结果是多行多列的）

select
    u.name,
    o.number
from
    user u,
    (select * from orderlist where id > 4) o
where
    o.uid = u.id;

name	number
王五	hm005
王五	hm006

习题

SQL25 查找山东大学或者性别为男生的信息

题目链接：
https://www.nowcoder.com/practice/979b1a5a16d44afaba5191b22152f64a

实现代码：

select device_id, gender, age, gpa
from user_profile
where university = '山东大学'
union all
select device_id, gender, age, gpa
from user_profile
where gender = 'male'

使用union all就是不对两表的相同元组去重，使用union就是要去重。

SQL21 浙江大学用户题目回答情况
题目链接：https://www.nowcoder.com/practice/55f3d94c3f4d47b69833b335867c06c1

实现代码：

select
    q.device_id,
    question_id,
    result
from
    question_practice_detail q
    inner join user_profile u
on q.device_id = u.device_id
where university = '浙江大学'
order by question_id asc;

SQL22 统计每个学校的答过题的用户的平均答题数

题目链接：https://www.nowcoder.com/practice/88aa923a9a674253b861a8fa56bac8e5
实现代码：

select
    university,
    count(*) / count(distinct u.device_id)
from
    user_profile u
    inner join question_practice_detail q
on u.device_id = q.device_id
group by university
order by university asc;

SQL23 统计每个学校各难度的用户平均刷题数

题目链接：
https://www.nowcoder.com/practice/5400df085a034f88b2e17941ab338ee8

实现代码：

select
    university,
    difficult_level,
    round(count(*) / count(distinct qpd.device_id), 4) as avg_answer_cnt
from 
    user_profile up
    inner join question_practice_detail qpd
    on up.device_id = qpd.device_id

    inner join question_detail qd
    on qpd.question_id = qd.question_id
group by university, difficult_level

SQL24 统计每个用户的平均刷题数

题目链接：
https://www.nowcoder.com/practice/f4714f7529404679b7f8909c96299ac4

实现代码：

select 
    university,
    difficult_level,
    count(up.device_id) / count(distinct up.device_id)
from
    user_profile up
    inner join question_practice_detail qpd
    on up.device_id = qpd.device_id

    inner join question_detail qd
    on qpd.question_id = qd.question_id
group by university, difficult_level
having university = '山东大学'

设有关系$R(A,B,C)$和$S(C,D)$，下列哪一个关系代数表达式与SQL语句select A, B, D from R, S where R.C = S.C等价

• ${\sigma }_{R.C=S.C}({\Pi }_{A,B,D}(R\times S))$
• ${\Pi }_{A,B,D}({\sigma }_{R.C=S.C}(R\times S))$ （正确）
• ${\sigma }_{R.C=S.C}({\sigma }_{A,B}R\times {\Pi }_{D}S)$
• ${\sigma }_{R.C=S.C}({\Pi }_D({\Pi }_{A,B}R)\times S)$