一、基本內容

(1) 使用SQL語句創(chuàng)建數(shù)據(jù)庫；
(2) 為數(shù)據(jù)庫分配管理權限；
(3) 定義表和數(shù)據(jù)庫的完整性，student（學生表），course（課程表）和 sc（學生選課表），
    并設置各個表之間的聯(lián)系，設置表的主鍵和每個表對應的外鍵約束；
(4) 查看三個表的完整性，錄入三個表的元組數(shù)據(jù)，觀察設置外鍵的作用；
(5) 使用 select * from student... 等SQL語句進行數(shù)據(jù)的查詢，觀察實驗結果
(6) 為eduTest數(shù)據(jù)庫的基本表創(chuàng)建視圖、索引，實現(xiàn)數(shù)據(jù)庫的完整性操作；
(7) eduTest數(shù)據(jù)庫，分配用戶權限；

二、SQL語句

1.數(shù)據(jù)庫的創(chuàng)建：

(1) 數(shù)據(jù)庫創(chuàng)建代碼：

CREATE DATABASE eduTest DEFAULT CHARACTER SET utf8;

(2)查詢數(shù)據(jù)庫：

SHOW DATABASES;

image.png

(3) 修改數(shù)據(jù)庫的默認字符集：

ALTER DATABASE eduTest DEFAULT CHARACTER SET utf8;

image.png

ALTER DATABASE eduTest DEFAULT CHARACTER SET gbk;

image.png

2.數(shù)據(jù)庫中表建立的代碼：

（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 不存在時，當前值是不能插入到數(shù)據(jù)庫當中去的。

4.在數(shù)據(jù)庫eduTest中創(chuàng)建視圖便于對數(shù)據(jù)的保護：

(1)將student表中的某些字段單獨提取出來作為給數(shù)據(jù)庫用戶使用的基本 表，便于對重要字段的素具進行保密：

Cs系的student_sc視圖：

/*為每一個系的學生分別創(chuàng)建視圖*/
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)試圖能夠簡化用戶的操作；效果：視圖機制使用戶可以把主要的精力花費在數(shù)據(jù)上，而不是直接來自哪個基本表。
(2)視圖能使用戶從多種角度去分析數(shù)據(jù)的作用；
效果：在許多用戶同時訪問同一數(shù)據(jù)的時候比較重要。
(3)試圖對重構的數(shù)據(jù)庫提供了一定程度的邏輯性；
效果：可以增加表的字段，也可以在創(chuàng)建視圖的時候摘要自己需要的數(shù)據(jù)項。
(4)試圖能夠對機密數(shù)據(jù)進行安全性保護；
效果：可以將機密的字段進行保護，可以不用顯示給用戶。
(5)適當?shù)倪\用視圖可以更清晰的表達查詢；

6.創(chuàng)建數(shù)據(jù)庫更新操作的日志表

/創(chuàng)建日志表信息對表操作完成后觸發(fā)寫進日志表/

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.創(chuàng)建存儲過程

/創(chuàng)建批插入的存儲過程，觸發(fā)插入操作對應的觸發(fā)器/

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 
$

/創(chuàng)建批刪除的存儲過程，觸發(fā)刪除操作對應的觸發(fā)器/

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.觸發(fā)器的創(chuàng)建：
/創(chuàng)建student表插入的觸發(fā)器/

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','插入');

/刪除觸發(fā)器pro_insert_student/

DROP TRIGGER trigger_insert_student;

/創(chuàng)建student表刪除的觸發(fā)器/

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','刪除');

/創(chuàng)建student_test表刪除的觸發(fā)器/

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;

/創(chuàng)建student_test表插入的觸發(fā)器/

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','插入');

/刪除觸發(fā)器pro_insert_student_test/

DROP TRIGGER trigger_insert_student_test;
SELECT * FROM text_log;