步步为营关系代数练习与三种连接

公众号guangcity

发布于 2019-09-20 17:31:21

7840

发布于 2019-09-20 17:31:21

文章被收录于专栏：光城(guangcity)光城(guangcity)

MySQL学习仓库Up-Up-MySQL，这是一个学习MySQL从入门实战到理论完善，再到精通的一个仓库，后面会把MySQL的学习资料上传上去！欢迎大家star与fork起来！

仓库地址：

https://github.com/Light-City/Up-Up-MySQL

也可以点击阅读原文！

今天上手第六弹，关系代数练习与三种连接!

1.笛卡尔积、等值连接、自然连接三者之间区别

笛卡尔积对两个关系R和S进行操作，产生的关系中元组个数为两个关系中元组个数之积。等值联接则是在笛卡尔积的结果上再进行选择操作，挑选关系第i个分量与第(r+j)个分量值相等的元组；自然连接则是在等值联接(以公共属性值相等为条件)的基础上再行投影操作，去掉S中的公共属性列，当两个关系没有公共属性时，自然连接就转化成笛卡尔积。

1）等值连接必须要有等值的条件，当条件不同时连接的结果也不相同，两个关系可以没有相同的属性列

2）自然连接必须要有相同的属性列才能进行

下面演示上述理论：

现有S与R两张表，数据如下：

mysql> select * from S;
+------+------+
| A    | B    |
+------+------+
|    1 |    2 |
|    3 |    3 |
|    5 |    9 |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from R;
+------+------+
| B    | C    |
+------+------+
|    2 |    1 |
|    7 |    2 |
|    3 |    5 |
+------+------+
3 rows in set (0.00 sec)

笛卡尔积操作

mysql> select * from S,R;
+------+------+------+------+
| A    | B    | B    | C    |
+------+------+------+------+
|    1 |    2 |    2 |    1 |
|    3 |    3 |    2 |    1 |
|    5 |    9 |    2 |    1 |
|    1 |    2 |    7 |    2 |
|    3 |    3 |    7 |    2 |
|    5 |    9 |    7 |    2 |
|    1 |    2 |    3 |    5 |
|    3 |    3 |    3 |    5 |
|    5 |    9 |    3 |    5 |
+------+------+------+------+
9 rows in set (0.00 sec)

等值连接

没有相同属性列的S.A=R.C等值连接：

mysql> select * from S,R where S.A=R.C;
+------+------+------+------+
| A    | B    | B    | C    |
+------+------+------+------+
|    1 |    2 |    2 |    1 |
|    5 |    9 |    3 |    5 |
+------+------+------+------+
2 rows in set (0.00 sec)

有相同属性列的S.B=R.B等值连接：

mysql> select * from S,R where S.B=R.B;
+------+------+------+------+
| A    | B    | B    | C    |
+------+------+------+------+
|    1 |    2 |    2 |    1 |
|    3 |    3 |    3 |    5 |
+------+------+------+------+
2 rows in set (0.00 sec)

自然连接

mysql> select * from S,R where S.B=R.B;
+------+------+------+------+
| A    | B    | B    | C    |
+------+------+------+------+
|    1 |    2 |    2 |    1 |
|    3 |    3 |    3 |    5 |
+------+------+------+------+
2 rows in set (0.00 sec)

2.关系代数

进入http://dbis-uibk.github.io/relax/calc.htm#网站，选择Wikipedia - Relational algebra (en)数据库进行练习！

查询练习应包含π σ ÷ ⨝ ⟕ 等运算符。

2.1 字段描述

Employee  雇员
    Name string 
    EmpId number
    DeptName string
Dept 部门
    DeptName string
    Manager string
Completed 已完成
    Student string
    Task string
    DBProject
    Task string
Car 汽车
    CarModel string
    CarPrice number
Boat 船
    BoatModel string
    BoatPrice number

2.2关系代数

自然连接练习

π Name, DeptName σ DeptName = 'Sales' (Employee ⨝ Dept)

除练习

Completed表

Completed.Student	Completed.Task
Fred	Database1
Fred	Database2
Fred	Compiler1
Eugene	Database1
Eugene	Compiler1
Sarah	Database1
Sarah	Database2

DBProject表：

DBProject.Task
Database1
Database2

( Completed ÷ DBProject ) 结果：

Completed.Student
Fred
Sarah

Employee表：

Employee.Name	Employee.EmpId	Employee.DeptName
Harry	3415	Finance
Sally	2241	Sales
George	3401	Finance
Harriet	2202	Sales
Tim	1123	Executive

自然连接：

(Completed ÷ DBProject) ⨝ Employee

Completed.Student	Employee.Name	Employee.EmpId	Employee.DeptName
Fred	Harry	3415	Finance
Fred	Sally	2241	Sales
Fred	George	3401	Finance
Fred	Harriet	2202	Sales
Fred	Tim	1123	Executive
Sarah	Harry	3415	Finance
Sarah	Sally	2241	Sales
Sarah	George	3401	Finance
Sarah	Harriet	2202	Sales
Sarah	Tim	1123	Executive

选择结果：

Employee.Name	Employee.DeptName
Harry	Finance
Sally	Sales
George	Finance
Harriet	Sales
Tim	Executive

综上的关系代数为：

π Name, DeptName ( ( Completed ÷ DBProject ) ⨝ Employee )

并练习

( Completed ÷ DBProject ) 结果：

Completed.Student
Fred
Sarah

Employee表投影出Name列结果：

Employee.Name
Harry
Sally
George
Harriet
Tim

并：

π Name (Employee) ∪ (Completed ÷ DBProject)

Employee.Name
Harry
Sally
George
Harriet
Tim
Fred
Sarah

差练习

对Employee投影出DeptName：

π DeptName (Employee)

Employee.DeptName
Finance
Sales
Executive

对Dept投影出DeptName：

π DeptName (Dept)

Dept.DeptName
Sales
Production

两张表的DeptName做差运算：

π DeptName (Employee) - π DeptName (Dept)

Employee.DeptName
Finance
Executive

左外连接

Employee表：

Employee.Name	Employee.EmpId	Employee.DeptName
Harry	3415	Finance
Sally	2241	Sales
George	3401	Finance
Harriet	2202	Sales
Tim	1123	Executive

Dept表：

Dept.DeptName	Dept.Manager
Sales	Harriet
Production	Charles

Employee left join Dept结果：

Employee ⟕ Dept

Employee.Name	Employee.EmpId	Employee.DeptName	Dept.Manager
Harry	3415	Finance	null
Sally	2241	Sales	Harriet
George	3401	Finance	null
Harriet	2202	Sales	Harriet
Tim	1123	Executive	null

投影选择列：

π Name, DeptName, Manager (Employee ⟕ Dept)

Employee.Name	Employee.DeptName	Dept.Manager
Harry	Finance	null
Sally	Sales	Harriet
George	Finance	null
Harriet	Sales	Harriet
Tim	Executive	null

选择练习

Car表：

Car.CarModel	Car.CarPrice
CarA	20000
CarB	30000
CarC	50000

Boat表：

Boat.BoatModel	Boat.BoatPrice
Boat1	10000
Boat2	40000
Boat3	60000

自然连接：

Car ⨝ Boat

Car.CarModel	Car.CarPrice	Boat.BoatModel	Boat.BoatPrice
CarA	20000	Boat1	10000
CarA	20000	Boat2	40000
CarA	20000	Boat3	60000
CarB	30000	Boat1	10000
CarB	30000	Boat2	40000
CarB	30000	Boat3	60000
CarC	50000	Boat1	10000
CarC	50000	Boat2	40000
CarC	50000	Boat3	60000