mysql建立修改表存储过程_MySQL数据库创建、表的创建、存储过程、触发器

一、基本内容

(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;