一、基本内容
(1) 使用SQL语句创建数据库；
(2) 为数据库分配管理权限；
(3) 定义表和数据库的完整性，student（学生表），course（课程表）和 sc（学生选课表），
并设置各个表之间的联系，设置表的主键和每个表对应的外键约束；
(4) 查看三个表的完整性，录入三个表的元组数据，观察设置外键的作用；
(5) 使用 select * from student... 等SQL语句进行数据的查询，观察实验结果
(6) 为eduTest数据库的基本表创建视图、索引，实现数据库的完整性操作；
(7) eduTest数据库，分配用户权限；
二、SQL语句
1.数据库的创建：
(1) 数据库创建代码：
CREATE DATABASE eduTest DEFAULT CHARACTER SET utf8;
(2)查询数据库：
SHOW DATABASES;
image.png
(3) 修改数据库的默认字符集：
ALTER DATABASE eduTest DEFAULT CHARACTER SET utf8;
image.png
ALTER DATABASE eduTest DEFAULT CHARACTER SET gbk;
image.png
2.数据库中表建立的代码：
（1）course表：
CREATE TABLE course(
id INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20) NOT NULL UNIQUE,
presourse VARCHAR(20) NOT NULL,
remark VARCHAR(40)
);
image.png
（2）student表：
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
sno INT(8) ZEROFILL NOT NULL,
sname VARCHAR(20) NOT NULL,
sId INT ,
CONSTRAINT student_course_fk FOREIGN KEY(sId) REFERENCES course(id)
);
image.png
（3）sc表
CREATE TABLE sc(
scId INT PRIMARY KEY AUTO_INCREMENT,
sno INT(8) ZEROFILL NOT NULL,
grade INT NOT NULL,
courseId INT NOT NULL,
remark VARCHAR(20),
CONSTRAINT sc_course_fk FOREIGN KEY(courseId) REFERENCES course(id)
);
image.png
3. 外键的约束
image.png
由于courseId作为外键参考于course表的id，当添加的courseId的值在course表中id 不存在时，当前值是不能插入到数据库当中去的。
4.在数据库eduTest中创建视图便于对数据的保护：
(1)将student表中的某些字段单独提取出来作为给数据库用户使用的基本 表，便于对重要字段的素具进行保密：
Cs系的student_sc视图：
/*为每一个系的学生分别创建视图*/
CREATE VIEW student_sc AS SELECT student.* FROM student WHERE student.sdept='cs';
image.png
Is系的student_is视图：
CREATE VIEW student_is AS SELECT student.* FROM student WHERE student.sdept='is';
image.png
Net系的student_net视图：
CREATE VIEW student_net AS SELECT student.* FROM student WHERE student.sdept='net';
image.png
Soft系的student_soft视图：
CREATE VIEW student_soft AS SELECT student.* FROM student WHERE student.sdept='soft';
image.png
5.视图的作用
(1)试图能够简化用户的操作；效果：视图机制使用户可以把主要的精力花费在数据上，而不是直接来自哪个基本表。
(2)视图能使用户从多种角度去分析数据的作用；
效果：在许多用户同时访问同一数据的时候比较重要。
(3)试图对重构的数据库提供了一定程度的逻辑性；
效果：可以增加表的字段，也可以在创建视图的时候摘要自己需要的数据项。
(4)试图能够对机密数据进行安全性保护；
效果：可以将机密的字段进行保护，可以不用显示给用户。
(5)适当的运用视图可以更清晰的表达查询；
6.创建数据库更新操作的日志表
/创建日志表信息对表操作完成后触发写进日志表/
CREATE TABLE text_log(
id INT PRIMARY KEY AUTO_INCREMENT,
opra_table VARCHAR(20) NOT NULL,
opra_type VARCHAR(20) NOT NULL,
opra_remark VARCHAR(20)
);
7.创建存储过程
/创建批插入的存储过程，触发插入操作对应的触发器/
DELIMITER $
CREATE PROCEDURE insert_student(IN sno_start INT, IN sno_end INT)
BEGIN
DECLARE i INT DEFAULT 0;
SET i=sno_start;
WHILE i<=sno_end DO
INSERT INTO student(sno,sname,sage,ssex,sdept)
VALUE(i,'安雨轩',18+i-sno_start,'女','net');
SET i=i+1;
END WHILE;
END
$
/创建批删除的存储过程，触发删除操作对应的触发器/
DELIMITER $
CREATE PROCEDURE delete_student(IN sno_start INT, IN sno_end INT)
BEGIN
DECLARE i INT DEFAULT 0;
SET i=sno_start;
WHILE i<=sno_end DO
DELETE FROM student WHERE student.sno=i;
SET i=i+1;
END WHILE;
END
$
8.触发器的创建：
/创建student表插入的触发器/
CREATE TRIGGER trigger_insert_student AFTER INSERT ON student FOR EACH ROW
INSERT INTO text_log(opra_table,opra_type,opra_remark) VALUE('student','insert','插入');
/删除触发器pro_insert_student/
DROP TRIGGER trigger_insert_student;
/创建student表删除的触发器/
CREATE TRIGGER trigger_delete_student AFTER DELETE ON student FOR EACH ROW
INSERT INTO text_log(opra_table,opra_type,opra_remark) VALUE('student','delete','删除');
/创建student_test表删除的触发器/
CREATE TRIGGER trigger_delete_student_test AFTER DELETE ON student_test FOR EACH ROW
INSERT INTOtext_log(opra_table,opra_type,opra_remark) VALUE('student','delete','删除');
DELETE FROM student_test WHERE student_test.sno=04151079;
/创建student_test表插入的触发器/
CREATE TRIGGER trigger_insert_student_test AFTER INSERT ON student_test FOR EACH ROW
INSERT INTO text_log(opra_table,opra_type,opra_remark) VALUE('student','insert','插入');
/删除触发器pro_insert_student_test/
DROP TRIGGER trigger_insert_student_test;
SELECT * FROM text_log;
网友评论