MySQL学习仓库Up-Up-MySQL,这是一个学习MySQL从入门实战到理论完善,再到精通的一个仓库,后面会把MySQL的学习资料上传上去!欢迎大家star与fork起来!
仓库地址:
https://github.com/Light-City/Up-Up-MySQL
也可以点击阅读原文!
今天上手第六弹,关系代数练习与三种连接!
笛卡尔积对两个关系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)
进入http://dbis-uibk.github.io/relax/calc.htm#网站,选择Wikipedia - Relational algebra (en)数据库进行练习!
查询练习应包含π σ ÷ ⨝ ⟕ 等运算符。
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
π 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 |
选择:
σ CarPrice > BoatPrice (Car ⨝ Boat)
Car.CarModel | Car.CarPrice | Boat.BoatModel | Boat.BoatPrice |
---|---|---|---|
CarA | 20000 | Boat1 | 10000 |
CarB | 30000 | Boat1 | 10000 |
CarC | 50000 | Boat1 | 10000 |
CarC | 50000 | Boat2 | 40000 |