Chapter 3 —— SQL语言（4）

数据插入 INSERT

· 首先我们之前已经知道了，插入操作是以元组为单位进行的：

INSERT INTO `表名` VALUES(常量1, 常量2 ...);

· 当然，可以指定列，没指定的列会用默认值或NULL代替：

INSERT INTO `表名`(`列名1`, `列名2`, ...) VALUES(常量1, 常量2 ...);

· 在插入之前，DBMS会检查插入的元组有没有违反三个完整性（主键不空、外键存在或空、自己定义的UNIQUE，NOT NULL等）。

插入时主键冲突

· 如果插入数据的时候主键冲突了，我们有三种优雅的解决方案：

① 有重复的话就忽略本次插入，没有重复则继续插入：IGNORE

INSERT IGNORE INTO ... VALUES ...

② 有重复的话就把旧的删掉，用新的替换：REPLACE

REPLACE INTO ... VALUES ...

③ 有重复则执行更新操作，没有重复则直接插入：ON DUPLICATE KEY

INSERT INTO ... VALUES ...
ON DUPLICATE KEY # 在重复的时候，执行下面的UPDATE语句
UPDATE <表达式>

注意，ON DUPLICATE KEY已经算是一个条件了，因此不能再加WHERE了！举个例子：

INSERT INTO `Student`
VALUES ("123", "HQR", "Male", 21) # 此处表中已存在123号学生，因此冲突
ON DUPLICATE KEY UPDAET `Sname` = "重复了！"; # 会直接更新123号学生的Sanme，不需要再用WHERE查询

插入子查询的结果

· 顾名思义，把子查询的结果插入到表中，用这个可以实现一次性插入多个元组。

· 需要注意的是，子查询返回到结果要和INSERT的属性的域一一对应！

· 举个例子：

INSERT INTO `Dept_age`(`Sdept`, `avg_age`)
SELECT `Sdept`, AVG(`Sage`)
FROM `Student`
GROUP BY `Sdept`;

数据更新 UPDATE

· 顾名思义，更新表中的数据。数据更新的操作是以值为单位进行的。

· 举个例子：

# 把所有学生的年龄都增加一岁，院系都改为CS
UPDATE `Student`
SET `Sage` = `Sage` + 1, `Sdept` = "CS";

· 当然，我们也可以有目标的去修改某一些值：

# 只设置学号为123的学生的年龄
UPDATE `Student`
SET `Sage` = 22
WHERE `Sno` = "123";

# 设置所有CS系的学生的年龄
UPDATE `Student`
SET `Sage` = 22
WHERE `Sdept` = "CS";

数据删除 DELETE

· 数据的删除是以元组为单位进行的。

· 如果有WHERE子句则删除满足条件的元组，如果没有则清空表。

· 注意和删除表区分，删除表是DROP TABLE！

· 举个例子：

DELETE
FROM `Student`
WHERE `Sno` = "1234";

视图 View

创建视图

· 首先，我们之前所操作的对象都是基本表，即真实存在的，存放在硬盘上的表。

· 而视图，是一个虚拟表，它是从一个或几个基本表（或其他视图）中导出的表。

· 其次，视图存放的只有定义，而不存放真实数据。因此，基表中的数据发生变化，视图查询出的数据也会发生变化。

· 创建视图：

CREATE VIEW `视图名`(`视图列名1`, `列名2` ...)
AS
SELECT `对应列名1` `对应列名2` ...
FROM `表`
...

· 这条语句在干什么呢？

· 其实就是创建了一个视图，命名，并将AS后面的子查询存入视图，但是并不执行查询！

· 再举两个例子：

# 创建CS系学生的（学号，年龄）视图，命名为student_cs
CREATE VIEW `student_cs`(`Sno`, `Sage`)
AS
SELECT `Sno`, `Sage`
FROM `Student`
WHERE `Sdept` = "CS";

# 创建选修了课程号为001的课的学生的（学号，姓名，成绩）视图，命名为course_001
CREATE VIEW `course_001`(`Sno`, `Sname`, `Score`)
AS
SELECT `Student`.`Sno`, `Student`.`Sname`, `SC`.`Score`
FROM `Student`
INNER JOIN `SC`
ON `Student`.`Sno` = `SC`.`Sno`
WHERE `Cno` = "001";

视图的聚集与分组

· 当然，视图也是可以创建聚集结果的列的：

CREATE VIEW `born_year`(`Sno`, `Sname`, `born_year`)
AS
SELECT `Sno`, `Sname`, 2023 - `Sage` # 这是一个运算表达式
FROM `Student`;

我们称这种通过运算得到的列为“虚拟列”，也称“派生列”。

· 在创建一张基本表的时候，我们可以定义某一列为虚拟列（MySQL 5.7+），它的好处就是不占内存，可用于显示表内的一些计算结果，甚至可以创建索引。

· 但虚拟列也有限制，比如要创建在被引用的列后，不支持子查询。

· 回到视图，既然能把聚集存在视图里，那分组也必然是可以的：

CREATE VIEW `AVG_SCORE`
AS
SELECT `Cno`, AVG(`Score`)
FROM `SC`
GROUP BY `Cno`;

· 我们称存储了分组的视图为分组视图。

· 接下来看一个特殊的例子，我们创建了一个视图：

CREATE VIEW `F_Student`
AS
SELECT *
FROM `Student`
WHERE `Ssex` = “F”;

如果Student表发生变化，多了一列`Sage`，那视图F_Student是不会变化的，需要先删除原视图，再创建新视图。可以理解为创建视图的时候同时保存了查询语句与要查询的对应的列。

视图的删除

· 和删除基本表一样，因为删除的是一个对象，因此要使用DROP：

DROP VIEW `视图名`;

· 如果视图之上还有视图，这就涉及到了一个删除设定的问题。

· 在定义视图的时候，可以设置检查选项，有两个选项，分别是CASCADE和LOCAL。

视图的查询

· 视图的查询和表的查询类似，只是FROM后面跟的是视图名而不是表名。

· 对于用户来说，查询视图和查询表是一样的；对于DBMS来说，会在检查视图以及涉及的表是否存在之后，利用视图的定义，和查询语句结合，转换为对基本表的查询，这个过程称为视图消解。

· 举个例子：

# 先创建了一个每个课程的平均分的视图
CREATE VIEW `course_avg`(`Cno`, `avg_score`)
AS
SELECT `Cno`, AVG(`Score`)
FROM `SC`
GROUP BY `Cno`;

# 然后要查询视图中平均分大于80分的课程号
SELECT `Cno`
FROM `course_avg`
WHERE `avg_score` > 80;

# 其实本质上，执行的视图消解是：
SELECT `Cno`
FROM `SC`
GROUP BY `Cno`
HAVING AVG(`Score`) > 80;

· 我们再思考的更深层次一点，还是上面的题，但是这次要同时同时查询高于80分的课程号和课程名：

# 先创建一个所有大于80分课程的视图
CREATE VIEW `course_avg`
AS
SELECT `Cno`, AVG(`Score`)
FROM `SC`
GROUP BY `Cno`
HAVING AVG(`Score`) > 80;

# 然后进行嵌套查询
SELECT `Cno`, `Cname`
FROM `Course`
WHERE `Cno` IN (
    SELECT `course_avg`.`Cno`
    FROM `course_avg`
);

· 然后我们来看视图消解的形式：

SELECT `Cno`, `Cname`
FROM `Course`
WHERE `Cno` IN (
    SELECT `SC`.`Cno`
    FROM `SC`
    GROUP BY `SC`.`Cno`
    HAVING AVG(`Score`) > 80
);

· 基于视图的查询和视图消解的查询都是嵌套查询，但他们存在本质上的不同；普通的嵌套查询是”阅后即焚“的，而视图会把定义保存下来！

视图的更新

· 既然可以用视图对基本表进行查询，那是否能通过视图来更新基本表呢？有的可以，有的不行。

· 我们使用WITH CHECK OPTION可以在对视图进行增删改时自动加上子查询涉及到WHERE条件，举个例子：

# 首先我们创建一个视图
CREATE VIEW `student_cs`
AS
SELECT `Sno`, `Sname`, `Ssex`
FROM `Student`
WHERE `Sdept` = "CS"
WITH CHECK OPTION; # 加上了这句话

# 然后我们尝试给视图增加一条元组，可以正常运行
INSERT INTO `student_cs`
VALUES ("123456", "raymond_hqr", "M");

# 因为加上了WITH CHECK OPTION，真正执行INSERT的时候其实是执行了：
INSERT INTO `student_cs`
VALUES ("123456", "raymond_hqr", "M", "CS"); # 自动把CS补上了

· 我们知道，视图是不会保存数据的，对视图的更新，最终是反映到它的基本表的更新上的。

· 接下来我们看另一个例子：

# 首先我们创建一个视图
CREATE VIEW `course_avg_score`(`Sno`, `avg_score`)
AS
SELECT `Cno`, AVG(`Score`)
FROM `SC`
GROUP BY `Cno`;

# 然后我们希望把001课程的平均分改成90分，于是进行了如下操作：
UPDATE `course_avg_score`
SET `avg_score` = 90
WHERE `Cno` = "001";

· 上面的更新操作是无法正确执行的，要时刻牢记，视图不存储数据！创建视图的时候进行分组聚集的结果是虚拟列，也不是真实的数据，无法通过视图修改。

· 但是，如果是进行删除操作是可以的，因为本质上来说就是定义上修改：

DELETE
FROM `course_avg_score`
WHERE `Cno` = "001";

· 总的来说，以下几种情况都不允许更新视图

① 由两个或以上的基本表导出的视图。

② 定义的SELECT子句里有聚集函数，虚拟列（计算结果）。

③ 定义的SELECT子句里有DISTINCT，UNIQUE等限定词。

④ 定义的SELECT子句里有GROUP BY。

⑤ 单表导出的视图里不包含主键。