数据库讲解---(数据更新、视图、数据控制)【MySQL版本】

2024-04-17 07:06:10

前言

数据库讲解（MySQL版）（超详细）【第一章】-****博客

数据库-ER图教程_er图怎么讲解-****博客

数据库讲解（MySQL版）（超详细）【第二章】【上】-****博客

数据库讲解---(SQL语句--表的使用)【MySQL版本】-****博客

数据库讲解---(数据查询)【MySQL版本】_数据库怎么查询选修课程名称的学号和姓名-****博客

数据库讲解---(SQL语句--练习题讲解)【MySQL版本】-****博客

一.数据更新

1.1插入数据

1.1.1插入单个元组

插入语句的一般格式为：

INSERT
INTO <表名> [(属性列1)、(属性列2).....]
VALUES [(常量1)、(常量2)、(常量3)];

如果某些属性列在INTO字句中没有出现，那么新纪录将在这些列上自动取空值，但如果表定义时某些列规定了“NOT NULL”约束，那么将会出错！

1.1.2将一个新学生记录(学号:091530,姓名:夏雨,性别:男,籍:海南,出生年份:1999,学院:计算机)插入到学生表中

INSERT
INTO 学生
VALUES ("091530","夏雨"，"男","海南","1999","计算机");
或者
INSERT
INTO 学生 (学号,姓名,性别,籍贯,出生年份,学院)
VALUES ("091530","夏雨"，"男","海南","1999","计算机");

如果全部属性都有值，那么可以“省略属性列”的书写

1.1.3插入子查询结果

我们可以将一个查询的结果作为子句，并将这个子句作为一个记录插入到表中

子查询插入的一般格式：

INSERT
INTO <表名> ([属性列1]、[属性列2])
子查询;

1.1.4有一个表“DEPT”(SDEPT CHAR(20),AVG_AGE SMALLINT)表示每个学院的学生的平均年龄，根据学生表中的数据求得结果后存入表中

INSERT
INTO DEPT
SELECT 学院,AVG(year(now() - 出生年龄))
FROM 学生
GROUP BY 学院;

1.2删除数据

1.2.1删除语句

删除语句的一般格式为：

DELETE
FROM <表名>
(WHERE <条件>);

而删除可以分为下面三种：

删除某个(某些元组)的值，WHERE子句给出删除条件

删除全部元组,忽略WHERE子句

带子查询的删除语句

1.2.2删除学号为092021的学生记录

DELETE
FROM 学生
WHERE 学号 = '092021';

1.2.3删除计算机学院所有学生的选课记录

DELETE
FROM 学习
WHERE '计算机' = (
    SELECT 学院
    FROM 学生
    WHERE 学生.学号 = 学习.学号
);

1.3修改数据

1.3.1修改语句

修改语句的一般格式：

UPDATE <表名>
SET <列名> = <表达式> [列名 = <表达式>]
[WHERE <条件>];

功能是修改指定表中满足WHERE子句的元组

SET子句用于指定修改方法，即用表达式的值取代列名中原先的属性值

如果没有WHERE子句默认修改表中所有记录

1.3.2将091611号学生的籍贯改为江苏

UPDATE 学生
SET 籍贯 = '江苏'
WHERE 学号 = '091611';

1.3.3将180101号课程的成绩增加一分

UPDATE 学习
SET 成绩 = 成绩 + 1
WHERE 课程号 = '180101';

1.3.4将计算机学院学生的成绩清零

UPDATE 学习
SET 成绩 = 0
WHERE '计算机' = (
    SELECT 学院
    FROM 学生
    WHERE 学生.学号 = 学习.学号
);
或者
UPDATE 学习
SET 成绩 = 0
WHERE 学号 IN (
    SELECT 学号
    FROM 学生
    WHERE 学院 = '计算机'
);

二.视图

2.1视图介绍

视图是从一个表或几个基本表(视图)中导出的表，因此是一种非标准的子模式概念。

我们修改图上的属性，并不会影响表中的属性，因此可以理解为视图为表的一个副本

使用视图查询的效率远大于使用原表查询的效率

2.2建立视图

建立视图的一般格式为：

CREATE VIEW <视图名> ([列名1][列名2]....)
AS <子查询>
(WITH CHECK OPTION);

DBMS执行“CREATE VIEW”语句时只是把视图的定义存入数据字典，并不执行其中的子查询语句，所以数据中只存在视图的定义，而不存放对应的数据！！

(PS：构成视图的属性列或者全部省略、或者全部给出，没有其他情况)

(但如果全部省略，那么构成视图的属性就是子查询中的属性)

但是以下三种情况，必须明确指定组成视图的所有列名：

某个目标是集函数或表达式

多表连接时选出了几个同名列作为视图的字段

需要在视图中为某个列启用新的合适的名字

WITH CHECK OPTION表示对视图进行插入、删除、更新操作时要保证发生变动的行满足视图定义中的谓词条件(即子查询中的条件表达式)

2.2.1建立计算机学院学生的视图

CREATE VIEW CS_VIEW
AS SELECT *
FROM 学生
WHERE 学院 = '计算机';

2.2.2建立计算机学院选修了“数据库原理”这门课的学生的视图

CREATE VIEW DB_S1
AS SELECT 学生.学号,姓名,性别,籍贯,学院,成绩
FROM 学生,学习,课程
WHERE 学生.学号 = 学习.学号 AND 学习.课程号 = 课程.课程号 AND 学院 = '计算机';

2.2.3建立计算机学院选修“数据库原理”课程且成绩在90分以上的学生视图

视图可以建立在另一个视图之上

CREATE VIEW DB_S2
AS SELECT *
FROM DB_S1
WHERE 成绩 >= 90;

2.3删除视图

删除视图语句：

DROP VIEW <视图名>;

值得注意的是，如果有其它视图建立在这个视图之上，那么该视图被删除后，其他视图会失效，因此也需要使用DROP将它们一一删除

2.4查询视图

2.4.1查询使用

将对视图的查询转换为对基本表的查询的过程称为“视图的消解”

2.4.2在计算机学院学生的视图中找出年龄小于20岁的学生

SELECT *
FROM CS_VIEW
WHERE year(now()) - 出生年份 < 20;

2.5更新视图(略过)

更新视图包括：“INSERT插入”、“DELETE删除”、“UPDATE修改”三种

并非所有的视图都允许更新，允许更新的视图称为“可更新视图”

以下七种情况下，视图不允许更新：

视图属性来自属性表达式或常数

视图属性来自库函数

视图定义中有GROUP BY子句

视图定义中有DISTINCT选项

视图定义中有嵌套查询且嵌套查询的FROM子句涉及该视图的参照表

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

在一个不允许更新的视图上再定义一个视图

三.数据控制

3.1授权

授权使用“GRANT”语句，格式如下：

GRANT <权限1> [,<权限2>....]
[ON <对象类型> <对象名>]
[TO <对象类型> <对象名>]
[WITH GRANT OPTION];

语义为：“将指定操作对象的指定操作权限授权给某个用户”

常见的操作权限如下：

如果指定了“WTH GRANT OPTION”子句，则获得权限的用户还可以将这个权限再授予别的用户！

3.1.1把学生表的查询权限授予用户USER1

GRANT SELECT ON TABLE 学生 TO USER1;

3.1.2把学生表和课程表的全部权限授予用户USER2和USER3

GRANT ALL PRIVILEGES ON TABLE 学生,课程 TO USER2，USER3;

3.1.3把学习表的查询权限授予全部用户

GRANT SELECT ON TABLE 学习 TO PUBLIC;

3.1.4把查询学习表和修改成绩的权限授予用户USER4

GRANT UPDATE(成绩),SELECT ON TABLE 学习 TO USER4;

3.1.5把学生表的INSERT权限授予USER5用户，并允许他将权限再授予别人

GRANT INSERT ON TABLE 学生 TO USER5 WITH GRANT OPTION;

3.2收回权限

收回权限使用“REMOVE”语句收回，格式如下：

REMOVE <权限1> <权限2> ...
[ON <对象类型> <对象名>]
[FROM <用户>];

3.2.1把用户USER4修改成绩的权限收回

REMOVE UPDATE(成绩) ON TBALE FROM USER4;

3.2.2把用户USER5对学生表的INSERT权限收回

REMOVE INSERT ON TABLE 学生 FROM USER5;

收回USER5权限的同时，如果有其他用户从USER5中获得了该权限，那么也会被收回

码农公寓

目录

前言