MySQL表的增删改查(进阶)

MySQL表的增删改查(进阶)

一、关系数据库

1. 关系数据结构及形式化定义

1.1 关系

• 关系可表示为一张二维表

1. 域(domain)：域是一组具有相同数据类型的值的集合
2. 笛卡尔积：笛卡尔积可表示为一张二维表。表中的每一行对应一个元组，表中的每一列的值对应一个域。
   

• 若关系中的某一属性组的值能唯一的标识一个元组，而其子集不能，则称该属性组为候选码
• 若一个关系中有多个候选码，则选定其中一个作为主码(primary key)
• 候选码的诸属性称为主属性，不包含在任何候选码中的属性称为非主属性

1.2 关系操作

• 基本关系操作：选择、投影、并、差、笛卡尔积

2. 关系的完整性

2.1 实体完整性

• 若属性A是基本关系R(表R)的主属性，则A不能取空值

2.2 参照完整性

• 设F是基本关系R的一个或一组属性，但不是关系R的码，K是基本关系S的主码。如果F和K相对应，则F是R的外码(foreign key)，并称基本关系R为参照关系，基本关系S为被参照关系或目标关系。
• F的值必须为空值，或者是 S 中某个元组的主码值

2.3 用户定义完整性

• NOT NULL：某列不能存储NULL值
• UNIQUE：保证某列的某行必须有唯一的值
• DEFAULT：规定没有给列赋值时的默认值
• CHECK：保证列中的值符合指定条件

二、进阶

1. 数据查询

1.1 聚集函数

• COUNT(*)：统计元组的个数
• COUNT( [DISTINCT|ALL] <列名>)：统计一列中值的个数
• SUM( [DISTINCT|ALL] <列名>)：计算一列值的总和
• AVG( [DISTINCT|ALL] <列名>)：计算一列值的平均值
• MAX( [DISTINCT|ALL] <列名>)：计算一列值中的最大值
• MIN( [DISTINCT|ALL] <列名>)：计算一列值中的最小值

-- 查询学生总人数
SELECT COUNT(*)
FROM Student;

1.2 ORDER BY子句

• 用户可以用ORDER BY子句对查询结果按照一个或多个属性列的升序(ASC)或降序(DESC)排序，默认值为升序。
• 查询选修了号课程的学生的学号及成绩，查询结果按分数降序排列，代码如下：

SELECT Sno,Grade
FROM sc
WHERE Cno=3
ORDER BY Grade DESC;

1.3 GROUP BY子句

• 将查询结果按某一列或多列的值分组，值相等的为一组

-- 查询选修了三门以上课程的学生学号
SELECT sno
FROM sc
GROUP BY Sno
HAVING COUNT(*)>=3;

• 先用GROUP BY子句按Sno进行分组，再用聚集函数COUNT对每一组计数；HAVING短语给出了选择组的条件，只有满足条件的才会被选出。

1.4 连接查询

1.4.1 等值与非等值连接

• 连接查询的WHERE子句中用来连接两个表的条件称为连接条件或连接谓词
• 当连接使用=时称为等值连接，使用其他运算符称为非等值连接

-- 查询每个学生选课情况
SELECT Student.*,SC.*
FROM Student,SC
WHERE Student.Sno=SC.Sno;

• 将目标中的重复属性列去掉称为自然连接
• 一条SQL语句可以同时完成选择和连接查询，这事WHERE子句是由连接谓词和选择谓词组成的复合条件

-- 查询选修2号课程且成绩在90分以上的所有学生的学号和姓名
SELECT Student.Sno,Sname
FROM Student,SC
WHERE Student.sno=sc.sno AND
	SC.Cno=2 AND sc.Grade>=90;

1.4.2 自身连接

• 连接操作不仅可以是两个表之间进行，也可以一个表与其自己进行连接，称为表的自身连接

-- 求一门课的间接先修课(即先修课的先修课)
SELECT FIRST.Cno,SECOND.Cpno
FROM Course FIRST,Course SECOND		-- 起别名
WHERE FIRST.Cpno=second.Cno;

1.4.3 外连接

• 在通常的链接操作中，只有满足条件的元组才能作为结果输出。例如在学生表和选课表中，如果有的学生没有选课，则查询的结果就不会显式那一部分信息，若要以Student表为主体，需要将学生的所有信息都显式出来，这时候需要用到外连接

-- 左外连接
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno);

1.5 嵌套查询

• 一个SELECT-FROM-WHERE语句称为一个查询块,将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语中的查询称为嵌套查询

1.5.1 带有IN谓词的子查询

-- 查询和刘晨所在同一系的所有学生
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept IN
	(SELECT sdept
	 FROM Student
	 WHERE Sname='刘晨');

1.5.2 带有比较运算符的子查询

-- 查询每个学生超过自己选修课程平均成绩的课程号
SELECT Sno,Cno
FROM SC X
WHERE Grade>=(
	SELECT AVG(Grade)
	FROM SC Y
	WHERE y.Sno=x.Sno
);

1.6 集合查询

• 集合操作主要包括并操作UNION、交操作INTERSECT和差操作EXCEPT

-- 查询选修了课程1或课程2的学生
SELECT Sno
FROM SC
WHERE Cno=1
UNION
SELECT Sno
FROM sc
WHERE Cno=2;

2. 表的设计

2.1 一对一

2.2 一对多

2.3 多对多