MySQL数据库实验三

本文承接前面的俩次实验基础上完成，不过实现的都是基础操作的练习

前言

实验目的

实验要求

实验内容及步骤

updata操作

delete操作

alter操作

添加列

删除列

修改列的数据类型

要求实现

实验结果

代码结果

注意事项

思考题

总结

前言

本文是MySQL数据库的简单操作的练习实验，掌握基本的sql练习。了解数据库的维护操作，包含数据的插入、检索、修改，掌握MySQL 中用户、角色及操作权限的管理方法，学会创建和使用规则、缺省和触发器。

实验目的

要求学生熟练使用和掌握数据库的维护操作，包含数据的插入、检索、修改，掌握MySQL 中用户、角色及操作权限的管理方法，学会创建和使用规则、缺省和触发器。

实验要求

1. 将B5的单价改为6.00元

2. 删去图书B2及的单价改为6.00元

3. 修改江苏新华书店的图书的数量为50

4. 为图书发行LBS表的QTY字段设计CHECK约束： 0<QTY<100

5. 将(B6,人工智能库论,4.5)加入图书B

6. 实现对LBS表的操作权限管理的使用。

实验内容及步骤

updata操作

UPDATE table_name  
SET column1 = value1, column2 = value2, ...  
WHERE condition;

delete操作

DELETE FROM table_name  
WHERE condition;

alter操作

添加列

ALTER TABLE students  
ADD email VARCHAR(255);
-- 例子
ALTER TABLE students  
ADD email VARCHAR(255);

删除列

ALTER TABLE table_name  
DROP COLUMN column_name;
-- 例子
ALTER TABLE students  
DROP COLUMN email;

修改列的数据类型

ALTER TABLE table_name  
MODIFY COLUMN column_name new_datatype;
-- 例子
ALTER TABLE students 
MODIFY COLUMN age SMALLINT;

这里只枚举了alter几个作用，alter实际上还有很多功能可以使用。

要求实现

1. 将B5的单价改为6.00元
只要直接在B表中使用UPDATE修改对应B5的价格即可

-- 将B5的单价改为6.00元
select * from b;
UPDATE B SET PRICE = 6.00 WHERE BNO = 'B5';

2. 删去图书B2及的单价改为6.00元

首先，删除图书B2的发行记录（因为可能存在外键约束，需要先删除外键表中的数据）,然后，删除图书B2的记录.

-- 删去图书B2及相应的发行记录
DELETE FROM LBS WHERE BNO = 'B2';
select *from lbs;
DELETE FROM B WHERE BNO = 'B2';
select *from b;

3. 修改江苏新华书店的图书的数量为50

由于图书数量只在LBS表中，而江苏新华书店只在S表中，我们在使用updata更改LBS中数量数据前，要用sno来使俩个表产生链接，找到对应需要修改的数据

-- 修改江苏新华书店的图书的数量为50
select * from S;
select * from LBS;
SELECT SNO FROM S WHERE SNAME = '江苏新华书店';
UPDATE LBS SET QTY = 50 WHERE SNO = (SELECT SNO FROM S WHERE SNAME = '江苏新华书店');

4. 为图书发行LBS表的QTY字段设计CHECK约束： 0<QTY<100
用alter可以调增LBS表，使用ADD CONSTRAINT添加约束
同时可以使用下述的代码查询是有在约束名中有新增添的约束

-- 为图书发行LBS表的QTY字段设计CHECK约束：  0<QTY<100
select * from LBS;
ALTER TABLE LBS ADD CONSTRAINT CHK_QTY CHECK (QTY > 0 AND QTY < 100);
-- 查找约束名
SELECT constraint_name
FROM information_schema.table_constraints
WHERE table_name = 'LBS' AND constraint_type = 'CHECK';

5. 将(B6,人工智能库论,4.5)加入图书B
主要使用insert进行数据的插入

-- 将(B6,人工智能库论,4.5)加入图书B
select * from b;
INSERT INTO B (BNO, BNAME, PRICE) VALUES ('B6', '人工智能库论', 4.50);

6. 实现对LBS表的操作权限管理的使用。

首先要创建用户，然后提供不同操作的权限，同时提供了查询用户权限和删除的操作

-- 实现对LBS表的操作权限管理的使用。
CREATE USER 'lbs_user'@'localhost' IDENTIFIED BY '123456';
-- 允许用户查询LBS表：
GRANT SELECT ON bookdistribution.LBS TO 'lbs_user'@'localhost';
-- 允许用户插入、更新和删除LBS表中的数据：
GRANT INSERT, UPDATE, DELETE ON bookdistribution.LBS TO 'lbs_user'@'localhost';
-- 允许用户执行所有操作（包括创建和删除表等）：并未执行
GRANT ALL PRIVILEGES ON bookdistribution.LBS TO 'lbs_user'@'localhost';
-- 查询用户
SHOW GRANTS FOR 'lbs_user'@'localhost';
-- 删除用户
DROP USER 'lbs_user'@'localhost';

实验结果

代码结果

1. 将B5的单价改为6.00元

2. 删去图书B2及的单价改为6.00元

3. 修改江苏新华书店的图书的数量为50

4. 为图书发行LBS表的QTY字段设计CHECK约束： 0<QTY<100

5. 将(B6,人工智能库论,4.5)加入图书B

6. 实现对LBS表的操作权限管理的使用。

注意事项

1、数据更改时要注意外码约束：在更改数据时，如果涉及到外键约束的表，需要先确保外键表中没有引用到这些数据，或者适当地更新外键表中的数据以维持数据的一致性和完整性。

2、数据删除时要注意外码约束：在删除数据时，如果涉及到外键约束的表，需要先删除外键表中的数据或者解除外键约束，然后再删除主表中的数据。

3、用户、角色和权限的职能，以及它们之间的关系：用户是访问数据库的主体，角色是一组权限的集合，用户可以被赋予一个或多个角色，从而继承角色的权限。这样，通过角色管理权限可以简化权限管理过程。

思考题

1、为什么不能随意删除被参考表中的主码：因为被参考表（通常称为父表或主表）的主码在子表（通常称为外键表）中被用作外键，用于维护两个表之间的关联关系。如果随意删除父表的主码数据，可能会导致子表中的外键数据失效或成为悬空引用，破坏数据的完整性和一致性。

2、数据库中一般不允许更改主码数据。如果需要更改主码数据时，怎样处理：如果确实需要更改主码数据，通常需要先删除原有的数据记录，然后插入新的数据记录，同时更新所有外键表中引用该主码的数据。但在实际操作中，这通常不是一个好的做法，因为可能会导致数据丢失或不一致。更好的做法是在设计数据库时尽量避免需要更改主码的情况。

3、两种SQL Server的安全认证模式及特点：SQL Server支持两种安全认证模式：Windows身份验证模式和混合身份验证模式。Windows身份验证模式使用Windows账户和密码进行身份验证，简化了管理并提供了更强的安全性。混合身份验证模式允许使用Windows账户和SQL Server账户进行身份验证，提供了更大的灵活性。

4、什么是触发器？主要功能是什么：触发器（Trigger）是一种特殊的存储过程，它会在指定的表上进行INSERT、UPDATE或DELETE操作时自动执行。触发器的主要功能包括数据完整性检查、数据审计、级联更新和删除等。通过触发器，可以在数据发生更改时自动执行一系列操作，以确保数据的完整性和一致性。