数据准备
创建用户表
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 R R和 S S S均为 n n n元(列)关系,且满足相容性条件(列数相同且两表对应列的属性都相同),那么 R R R和 S S S的并的元组就由两表元组的集合组成,字段仍是组合前的 n n n元字段。
用关系代数记为:
R
∪
S
R\cup S
R∪S
MySQL的例子请看第一道例题。
笛卡尔积
笛卡尔积就是把 k 1 k_1 k1行(个元组) r r r列(元)的关系 R R R和 k 2 k_2 k2行(个元组) s s s列(元)的关系 S S S拼接起来成一张表, R R R的每行都要和 S S S的每一行左右拼接。故这张新表有 r + s r+s r+s列(元), k 1 × k 2 k_1\times k_2 k1×k2行(个元组)。
用关系代数笛卡尔积记作:
R × S R\times S R×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
⋈
A
=
B
S
R\underset{A=B}\Join S
RA=B⋈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
R⟗S
左外连接
查询左表的全部数据,和左右两张表有交集部分的数据。
左外连接用关系代数记为:
R
⟕
S
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
R⟖S
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
)
R(A,B,C)
R(A,B,C)和
S
(
C
,
D
)
S(C,D)
S(C,D),下列哪一个关系代数表达式与SQL语句select A, B, D from R, S where R.C = S.C
等价
- σ R . C = S . C ( Π A , B , D ( R × S ) ) \sigma_{R.C=S.C}(\Pi_{A,B,D}(R\times S)) σR.C=S.C(ΠA,B,D(R×S))
- Π A , B , D ( σ R . C = S . C ( R × S ) ) \Pi_{A,B,D}(\sigma_{R.C=S.C}(R\times S)) ΠA,B,D(σR.C=S.C(R×S)) (正确)
- σ R . C = S . C ( σ A , B R × Π D S ) \sigma_{R.C=S.C}(\sigma_{A,B}R\times \Pi_DS) σR.C=S.C(σA,BR×ΠDS)
- σ R . C = S . C ( Π D ( Π A , B R ) × S ) \sigma_{R.C=S.C}(\Pi_D(\Pi_{A,B}R)\times S) σR.C=S.C(ΠD(ΠA,BR)×S)