SQL实验：数据控制

一、实验准备

实验环境：Windows11、MySQL Workbench 8.0 CE

查看自己当前在数据库中的登录身份：

SELECT CURRENT_USER();
SELECT USER();
SHOW PROCESSLIST;

这三句都可以起到查询的效果：CURRENT_USER() 函数返回当前 MySQL 会话的用户和主机名；USER() 函数也返回当前 MySQL 会话的用户和主机名。但是，它显示的是用户登录时提供的用户名，不包括权限提升或代理用户的情况。SHOW PROCESSLIST 命令显示当前所有连接到 MySQL 服务器的会话列表。输出包括连接ID、用户、主机、数据库、状态、执行的时间以及当前正在执行的查询等信息。

我们使用第一条，结果如下：

可以看到 root@localhost 作为结果，说明当前是以 root 用户在本地（localhost）连接到 MySQL 服务器，能够满足本次实验的需求。

搭建实验所用数据库：

show databases;
use course3;

-- 创建 Student 表
CREATE TABLE Student (
    Sno INT PRIMARY KEY,
    Sname VARCHAR(50) NOT NULL,
    Ssex CHAR(1) CHECK (Ssex IN ('M', 'F')),
    Sage INT,
    Sdept VARCHAR(100)
);

-- 创建 Course 表
CREATE TABLE Course (
    Cno INT PRIMARY KEY,
    Cname VARCHAR(100) NOT NULL,
    Cpno INT,
    Ccredit INT CHECK (Ccredit > 0)
);

-- 创建 SC 表 (学生选课表)
CREATE TABLE SC (
    Sno INT,
    Cno INT,
    Grade CHAR(1) CHECK (Grade IN ('A', 'B', 'C', 'D', 'F')),
    PRIMARY KEY (Sno, Cno),
    FOREIGN KEY (Sno) REFERENCES Student(Sno),
    FOREIGN KEY (Cno) REFERENCES Course(Cno)
);

-- 插入 Student 表的示例数据
INSERT INTO Student (Sno, Sname, Ssex, Sage, Sdept)
VALUES 
(1, 'John Doe', 'M', 20, 'Computer Science'),
(2, 'Jane Smith', 'F', 21, 'Mathematics'),
(3, 'Jim Brown', 'M', 19, 'Physics'),
(4, 'Anna Taylor', 'F', 22, 'Chemistry'),
(5, 'Robert Johnson', 'M', 23, 'Biology'),
(6, 'Emily Davis', 'F', 20, 'English Literature'),
(7, 'Michael Wilson', 'M', 21, 'History'),
(8, 'Sarah Moore', 'F', 19, 'Geography'),
(9, 'David Clark', 'M', 22, 'Philosophy'),
(10, 'Laura Martinez', 'F', 21, 'Art');

-- 插入 Course 表的示例数据
INSERT INTO Course (Cno, Cname, Cpno, Ccredit)
VALUES 
(101, 'Mathematics', NULL, 3),
(102, 'English Literature', NULL, 4),
(103, 'Computer Science', NULL, 5),
(104, 'Physics', NULL, 4),
(105, 'Chemistry', NULL, 3),
(106, 'Biology', NULL, 4),
(107, 'History', NULL, 3),
(108, 'Geography', NULL, 3),
(109, 'Philosophy', NULL, 2),
(110, 'Art', NULL, 2);

-- 插入 SC 表的示例数据
INSERT INTO SC (Sno, Cno, Grade)
VALUES 
(1, 101, 'A'),
(2, 102, 'B'),
(3, 103, 'C'),
(4, 104, 'A'),
(5, 105, 'B'),
(6, 106, 'C'),
(7, 107, 'A'),
(8, 108, 'B'),
(9, 109, 'C'),
(10, 110, 'A'),
(1, 103, 'B'),
(2, 101, 'A'),
(3, 102, 'B'),
(4, 103, 'C'),
(5, 104, 'A'),
(6, 105, 'B'),
(7, 106, 'C'),
(8, 107, 'A'),
(9, 108, 'B'),
(10, 109, 'C');

结果如下，可以看到成功搭建：

二、安全性部分

在这部分中，实验要求是：

在数据库中由DBA创建若干用户，权限全部选择为CONNECT（SQL Server中的db_accessadmin角色）
仿照教材4.2.4 [例1]~[例10]，在DBA与这些用户之间进行授权和回收，并查看效果（4用例）
注意SQL Server中登录名与用户的区别

因为我使用MySQL，所以就登录名与用户的区别上，有一些自己的理解如下：

在 SQL Server 中，登录名（Login）和用户（User）是分开的概念，登录名是在服务器级别创建的，而用户是在数据库级别创建的，用户与登录名关联。在 MySQL 中，用户（User）和权限（Privileges）都是在服务器级别管理的，但权限可以在数据库、表、列等不同级别进行分配。MySQL 中没有像 SQL Server 那样明确区分登录名和用户，但有一点同样值得注意，MySQL 用户是以 user@host 的形式定义的，即用户和主机一起决定了用户的身份。因此，user1@localhost 和 user1@'%' 是两个不同的用户。

1.创建用户

CREATE USER 'user1'@'localhost' IDENTIFIED BY 'Password1!';
CREATE USER 'user2'@'localhost' IDENTIFIED BY 'Password2!';

2.分配CONNECT权限

GRANT CONNECT ON course3.* TO 'user1'@'localhost';
GRANT CONNECT ON course3.* TO 'user2'@'localhost';

上述语句在MySQL中发生报错如下：

经查阅，我了解到在 MySQL 中，没有单独的 CONNECT 权限，像在 SQL Server 中那样明确。相反，MySQL 使用的 USAGE 权限和基础连接机制来允许用户连接到数据库服务器。因此，在MySQL中，我们通常通过创建用户并授予他们最低权限（如 USAGE 权限）来允许用户连接到数据库服务器。

GRANT USAGE ON *.* TO 'user1'@'localhost';
GRANT USAGE ON *.* TO 'user2'@'localhost';
-- 因为USAGE仅仅是让用户连接到服务器但不授予任何具体权限，所以*.*不指定数据库也可以

3.授权与回收示例

授予 user1 对 Student 表的 SELECT 权限，并回收

-- 授予 user1 对 Student 表的 SELECT 权限
GRANT SELECT ON course3.Student TO 'user1'@'localhost';
-- 验证 user1 的权限
SHOW GRANTS FOR 'user1'@'localhost';

-- 回收 user1 对 Student 表的 SELECT 权限
REVOKE SELECT ON course3.Student FROM 'user1'@'localhost';
-- 验证 user1 的权限
SHOW GRANTS FOR 'user1'@'localhost';

授予 user2 对 Course 表的 INSERT 权限，并回收

-- 授予 user2 对 Course 表的 INSERT 权限
GRANT INSERT ON course3.Course TO 'user2'@'localhost';
-- 验证 user2 的权限
SHOW GRANTS FOR 'user2'@'localhost';

-- 回收 user2 对 Course 表的 INSERT 权限
REVOKE INSERT ON course3.Course FROM 'user2'@'localhost';
-- 验证 user2 的权限
SHOW GRANTS FOR 'user2'@'localhost';

授予 user1 对 course3 数据库的所有表的 DELETE 权限，并回收

-- 授予 user1 对 course3 数据库的所有表的 DELETE 权限
GRANT DELETE ON course3.* TO 'user1'@'localhost';
-- 验证 user1 的权限
SHOW GRANTS FOR 'user1'@'localhost';

-- 回收 user1 对 course3 数据库的所有表的 DELETE 权限
REVOKE DELETE ON course3.* FROM 'user1'@'localhost';
-- 验证 user1 的权限
SHOW GRANTS FOR 'user1'@'localhost';

三、完整性部分

在这部分中，实验要求是：

用SQL对数据进行完整性控制，并用实验证实，当操作违反了完整性约束条件时，系统是如何处理的

实体完整性(仿照[例1]、[例2] ) x1
参照完整性(仿照[例3]) x4
用户定义完整性(仿照[例5]、 [例6]) x1
CHECK短语(仿照[例7]或 [例8] 、[例9]) x1
CONSTRAINT子句(仿照[例10]、 [例13]) x1

1.实体完整性

实验开始，创建数据库时就已经进行了实体完整性控制如下：

-- 创建 Student 表
CREATE TABLE Student (
    Sno INT PRIMARY KEY,
    Sname VARCHAR(50) NOT NULL,
    Ssex CHAR(1) CHECK (Ssex IN ('M', 'F')),
    Sage INT,
    Sdept VARCHAR(100)
);

-- 创建 Course 表
CREATE TABLE Course (
    Cno INT PRIMARY KEY,
    Cname VARCHAR(100) NOT NULL,
    Cpno INT,
    Ccredit INT CHECK (Ccredit > 0)
);

-- 创建 SC 表 (学生选课表)
CREATE TABLE SC (
    Sno INT,
    Cno INT,
    Grade CHAR(1) CHECK (Grade IN ('A', 'B', 'C', 'D', 'F')),
    PRIMARY KEY (Sno, Cno),
    FOREIGN KEY (Sno) REFERENCES Student(Sno),
    FOREIGN KEY (Cno) REFERENCES Course(Cno)
);

插入重复的主键值

-- 插入重复主键值到 Student 表中
INSERT INTO Student (Sno, Sname, Ssex, Sage, Sdept)
VALUES (1, 'Duplicate John', 'M', 21, 'Physics');

发生如下报错：

由于 Sno 已经存在，插入操作将失败，MySQL 将抛出错误。

0	46	22:50:07	INSERT INTO Student (Sno, Sname, Ssex, Sage, Sdept)
 VALUES (1, 'Duplicate John', 'M', 21, 'Physics')	Error Code: 1062. Duplicate entry '1' for key 'PRIMARY'	0.000 sec

插入空主键值

-- 尝试插入空主键值到 Student 表中
INSERT INTO Student (Sno, Sname, Ssex, Sage, Sdept)
VALUES (NULL, 'No Key', 'F', 20, 'Math');

发生如下报错：

由于 Sno 不能为空，插入操作将失败，MySQL 将抛出错误。

0	47	22:51:43	INSERT INTO Student (Sno, Sname, Ssex, Sage, Sdept)
 VALUES (NULL, 'No Key', 'F', 20, 'Math')	Error Code: 1048. Column 'Sno' cannot be null	0.000 sec

2.参照完整性

在这之前的实验中已经完整展示并介绍了数据库建表等操作。为了能让每个实验相对独立，有足够多的样例使得实验更加清晰，在这一步之后table中的数据我都使用了AI快速生成，望老师理解。

根据例三创建表并定义参照完整性如下：

-- 创建数据库并切换到该数据库
CREATE DATABASE course4;
USE course4;

-- 创建 Student 表
CREATE TABLE Student (
    Sno CHAR(9) PRIMARY KEY,
    Sname CHAR(20) NOT NULL,
    Ssex CHAR(2),
    Sage SMALLINT,
    Sdept CHAR(20)
);

-- 创建 Course 表
CREATE TABLE Course (
    Cno CHAR(4) PRIMARY KEY,
    Cname CHAR(100) NOT NULL,
    Cpno CHAR(4),
    Ccredit INT CHECK (Ccredit > 0)
);

-- 创建 SC 表，并定义参照完整性
CREATE TABLE SC (
    Sno CHAR(9) NOT NULL,
    Cno CHAR(4) NOT NULL,
    Grade SMALLINT,
    PRIMARY KEY (Sno, Cno),
    FOREIGN KEY (Sno) REFERENCES Student(Sno),
    FOREIGN KEY (Cno) REFERENCES Course(Cno)
);

-- 插入 Student 表的示例数据
INSERT INTO Student (Sno, Sname, Ssex, Sage, Sdept)
VALUES 
('S001', 'John Doe', 'M', 20, 'Computer Science'),
('S002', 'Jane Smith', 'F', 21, 'Mathematics'),
('S003', 'Jim Brown', 'M', 19, 'Physics'),
('S004', 'Anna Taylor', 'F', 22, 'Chemistry'),
('S005', 'Robert Johnson', 'M', 23, 'Biology'),
('S006', 'Emily Davis', 'F', 20, 'English Literature'),
('S007', 'Michael Wilson', 'M', 21, 'History'),
('S008', 'Sarah Moore', 'F', 19, 'Geography'),
('S009', 'David Clark', 'M', 22, 'Philosophy'),
('S010', 'Laura Martinez', 'F', 21, 'Art');

-- 插入 Course 表的示例数据
INSERT INTO Course (Cno, Cname, Cpno, Ccredit)
VALUES 
('101', 'Mathematics', NULL, 3),
('102', 'English Literature', NULL, 4),
('103', 'Computer Science', NULL, 5),
('104', 'Physics', NULL, 4),
('105', 'Chemistry', NULL, 3),
('106', 'Biology', NULL, 4),
('107', 'History', NULL, 3),
('108', 'Geography', NULL, 3),
('109', 'Philosophy', NULL, 2),
('110', 'Art', NULL, 2);

-- 插入 SC 表的示例数据
INSERT INTO SC (Sno, Cno, Grade)
VALUES 
('S001', '101', 90),
('S002', '102', 85),
('S003', '103', 80),
('S004', '104', 75),
('S005', '105', 70),
('S006', '106', 65),
('S007', '107', 60),
('S008', '108', 55),
('S009', '109', 50),
('S010', '110', 95);

插入不存在的学生编号到 SC 表
```
-- 尝试插入不存在的学生编号到 SC 表中
INSERT INTO SC (Sno, Cno, Grade)
VALUES ('S999', '101', 85);
```
由于 Sno 不存在于 Student 表中，插入操作将失败，MySQL 将抛出错误。

惊奇的发现，居然可以插入成功，外键约束失效！

发现无论插入何值只要符合实体完整性都能插入成功。经查阅，发现是默认情况下，我们的MySQL都是引擎都是MyISAM，这个引擎是不支持的外键的，我们要把引擎改成InnoDB才可以正常使用外键。

步骤：
1. 开启MySQL的InnoDB引擎，要不然当你使用“alter table 表名 type=InnoDB”时会提示错误。
  
  修改MySQL的配置文件my.ini：
  
  #default-storage-engine=INNODB ----去掉前面的“#”
  
  skip-innodb -----注释掉这句话，在前面加“#”
2. 修改表的type属性，如果你的表不是InnoDB引擎的话。
  
  alert table 表名 type=InnoDB
3. 外键还是没起作用，开启外键检测。
  
  set foreign_key_checks=1;
插入不存在的课程编号到 SC 表
```
-- 尝试插入不存在的课程编号到 SC 表中
INSERT INTO SC (Sno, Cno, Grade)
VALUES ('S001', 'C999', 90);
```
由于 Cno 不存在于 Course 表中，插入操作将失败，MySQL 将抛出错误。

删除被引用的学生记录

-- 首先插入有效的数据
INSERT INTO Student (Sno, Sname, Ssex, Sage, Sdept)
VALUES ('S001', 'John Doe', 'M', 20, 'Computer Science');

INSERT INTO Course (Cno, Cname, Cpno, Ccredit)
VALUES ('101', 'Mathematics', NULL, 3);

INSERT INTO SC (Sno, Cno, Grade)
VALUES ('S001', '101', 90);

-- 尝试删除被引用的学生记录
DELETE FROM Student WHERE Sno = 'S001';

由于 Sno 被 SC 表引用，删除操作将失败，MySQL 将抛出错误。

删除被引用的课程记录
```
-- 尝试删除被引用的课程记录
DELETE FROM Course WHERE Cno = '101';
```
由于 Cno 被 SC 表引用，删除操作将失败，MySQL 将抛出错误。

3.用户定义完整性

CREATE DATABASE course5;
USE course5;

-- 创建 SC 表，定义 NOT NULL 约束
CREATE TABLE SC (
    Sno CHAR(9) NOT NULL,
    Cno CHAR(4) NOT NULL,
    Grade SMALLINT NOT NULL,
    PRIMARY KEY (Sno, Cno),
    FOREIGN KEY (Sno) REFERENCES Student(Sno),
    FOREIGN KEY (Cno) REFERENCES Course(Cno)
) ENGINE=InnoDB;
-- 这个ENGINE=InnoDB前面已经提到过为什么了

验证 SC 表的 NOT NULL 约束：

-- 尝试插入 NULL 值到 SC 表中
INSERT INTO SC (Sno, Cno, Grade)
VALUES ('S001', '101', NULL);

产生报错，提示lumnGrade'cannot be null

4.CHECK短语

CREATE DATABASE course5;
USE course5;

-- 创建 Student 表，定义 CHECK 约束
CREATE TABLE Student (
    Sno CHAR(9) PRIMARY KEY,
    Sname CHAR(8) NOT NULL,
    Ssex CHAR(2) CHECK (Ssex IN ('男', '女')), -- 性别属性 Ssex 只允许取 '男' 或 '女'
    Sage SMALLINT,
    Sdept CHAR(20)
) ENGINE=InnoDB;

插入符合 CHECK 约束的值

-- 插入符合 CHECK 约束的值
INSERT INTO Student (Sno, Sname, Ssex, Sage, Sdept)
VALUES ('S001', 'John Doe', '男', 20, 'Computer Science');

INSERT INTO Student (Sno, Sname, Ssex, Sage, Sdept)
VALUES ('S002', 'Jane Smith', '女', 21, 'Mathematics');

插入操作成功，因为 Ssex 列的值符合 CHECK 约束。

插入不符合 CHECK 约束的值

-- 尝试插入不符合 CHECK 约束的值
INSERT INTO Student (Sno, Sname, Ssex, Sage, Sdept)
VALUES ('S003', 'Jim Brown', 'M', 19, 'Physics');

由于 Ssex 列的值 'M' 不符合 CHECK 约束，插入操作将失败，MySQL 将抛出错误。

5.CONSTRAINT子句

CREATE DATABASE course8;
USE course8;

-- 创建 Student 表，定义 CONSTRAINT 约束
CREATE TABLE Student (
    Sno NUMERIC(6),
    Sname CHAR(20),
    Sage NUMERIC(3),
    Ssex CHAR(2),
    CONSTRAINT C1 CHECK (Sno BETWEEN 90000 AND 99999),
    CONSTRAINT C2 NOT NULL (Sname),
    CONSTRAINT C3 CHECK (Sage < 30),
    CONSTRAINT C4 CHECK (Ssex IN ('男', '女')),
    CONSTRAINT StudentKey PRIMARY KEY (Sno)
) ENGINE=InnoDB;

插入符合所有 CONSTRAINT 约束的值

-- 插入符合所有 CONSTRAINT 约束的值
INSERT INTO Student (Sno, Sname, Ssex, Sage)
VALUES (90001, 'John Doe', '男', 20);

插入操作成功，因为所有列的值符合 CONSTRAINT 约束。

插入不符合 CONSTRAINT C1 的值（学号不在 90000-99999 范围内）
```
-- 尝试插入不符合 CONSTRAINT C1 的值
INSERT INTO Student (Sno, Sname, Ssex, Sage)
VALUES (80001, 'Jane Smith', '女', 21);
```
由于 Sno 列的值不符合 CONSTRAINT C1 约束，插入操作将失败，MySQL 将抛出错误。

四、备份和恢复

在这部分中，实验要求是：

用Transact-SQL完成以下操作

(1) 创建一个备份设备。

(2) 完整备份实验数据库到备份设备。

(3) 向实验数据库中某个表插入若干条记录。

(4)备份数据库事务日志到备份设备，并查看日志格式。

(5)利用第2步所得的完整备份，恢复到插入记录前的状态。

(6)利用第4步所得的事务日志，恢复到插入记录后的状态。

在这部分实验中，我将使用 mysqldump 工具来完成数据库备份和恢复操作。

1.创建一个备份设备

在windows的cmd窗口中使用mysqldump命令首先需要配置环境变量：

在计算机中找到MySQL的安装位置，找到MySQL Workbench，将路径添加到环境变量中。
打开cmd，输入 mysqldump -uroot -p 数据库表 > 地址就可以备份MySQL中某个表的数据了。

在 MySQL 中，我们不需要显式地创建备份设备。我们可以直接使用 mysqldump 工具将备份保存到文件中。

2.完整备份实验数据库到备份设备

C:\Users\67093>mysqldump -u root -p course3 > C:\Users\67093\Desktop\course3_backup.sql

这个命令会将 course3 数据库的完整备份保存到 course3_backup.sql 文件中。

发生报错如下：

这个错误是由于 mysqldump 在执行过程中尝试访问 information_schema.COLUMN_STATISTICS 表，但该表在您的 MySQL 版本中不存在或未启用。这是 MySQL 8.0 中引入的一个新特性，用于统计列的直方图。在某些情况下，可能会遇到这种问题。

解决如下：

成功拿到备份文件

3.向实验数据库中某个表插入若干条记录

USE course3;

INSERT INTO Student (Sno, Sname, Ssex, Sage)
VALUES (90005, 'Emily Davis', '女', 19),
       (90006, 'Michael Wilson', '男', 22);

4.备份数据库事务日志到备份设备，并查看日志格式

通过 mysqlbinlog 工具进行备份,在 my.cnf 文件中添加以下内容，并重启 MySQL 服务：

[mysqld]
log-bin=mysql-bin

备份二进制日志：

mysqlbinlog C:\Users\67093\Desktop\mysql-bin.000001 > C:\Users\67093\Desktop\mysql-bin-backup.sql

将二进制日志文件保存到了 mysql-bin-backup.sql 文件中。

5.利用第2步所得的完整备份，恢复到插入记录前的状态

首先，删除当前数据库，以确保我们从备份中恢复：

DROP DATABASE course3;

然后，通过以下命令恢复数据库：

mysql -u root -p course3 < C:\Users\67093\Desktop\course3_backup.sql

可以看到恢复成功！

6.利用第4步所得的事务日志，恢复到插入记录后的状态

应用二进制日志:

mysql -u root -p < C:\Users\67093\Desktop\mysql-bin-backup.sql

这个命令将应用二进制日志文件中的所有事务，恢复到插入记录后的状态。