数据库1（MYSQL）大题

（一）实验目的： 

（二）实验内容：把创建数据库mySPJ，并建立S，P，J，和SPJ四个基本表的命令写在作业中。

（三）实验结果：可以是运行结果截图或其他形式的结果展示

（四）问题及解决：实验中遇到的问题及解决方法。

（五）回答思考题提出的问题。

CREATE DATABASE myspj CHARSET utf8;

Use myspj;

CREATE TABLE S (

   SNO VARCHAR(8) NOT NULL PRIMARY KEY,

   SNAME NVARCHAR(20) UNIQUE,

   STATUS INT ,

   CITY NVARCHAR(20)

) CHARSET utf8;

CREATE TABLE P (

   PNO VARCHAR(8) NOT NULL PRIMARY KEY,

   PNAME NVARCHAR(20),

   COLOR NVARCHAR(10),

   WEIGHT INT

)CHARSET utf8;

CREATE TABLE J(

   JNO VARCHAR(8) NOT NULL PRIMARY KEY,

   JNAME NVARCHAR(30),

   CITY NVARCHAR(20)

)CHARSET utf8;

CREATE TABLE SPJ (

   SNO VARCHAR(8),

   PNO VARCHAR(8),

   JNO VARCHAR(8),

   QTY INT ,

   PRIMARY KEY (SNO,PNO,JNO),

   FOREIGN KEY (SNO) REFERENCES S(SNO),

   FOREIGN KEY (PNO) REFERENCES P(PNO),

   FOREIGN KEY (JNO) REFERENCES J(JNO)

)CHARSET utf8;

（三）按照实验一中mySPJ数据库中列出的表1-4、1-5、1-6、1-7的基本表结构分别建立各表，并分别使用插入、删除、修改的方式更新基本表中的数据。

1、创建mySPJ数据库

2、分别输入创建S，P，J，和SPJ表命令

3、利用Insert 语句表1-4、1-5、1-6、1-7给出的数据记录插入各表。

4.利用Update更新表中的记录：

①将p表中的所有红色零件的重量增加5。

②将spj表中所有天津供应商的QTY属性值减少10。用子查询。

5.利用Delete语句删除p表中的所有红色零件的记录。

作业答案：

CREATE DATABASE myspj CHARSET utf8;

Use myspj;

CREATE TABLE S (

   SNO VARCHAR(8) NOT NULL PRIMARY KEY,

   SNAME NVARCHAR(20) UNIQUE,

   STATUS INT ,

   CITY NVARCHAR(20)

) CHARSET utf8;

CREATE TABLE P (

   PNO VARCHAR(8) NOT NULL PRIMARY KEY,

   PNAME NVARCHAR(20),

   COLOR NVARCHAR(10),

   WEIGHT INT

)CHARSET utf8;

CREATE TABLE J(

   JNO VARCHAR(8) NOT NULL PRIMARY KEY,

   JNAME NVARCHAR(30),

   CITY NVARCHAR(20)

)CHARSET utf8;

CREATE TABLE SPJ (

   SNO VARCHAR(8),

   PNO VARCHAR(8),

   JNO VARCHAR(8),

   QTY INT ,

   PRIMARY KEY (SNO,PNO,JNO),

   FOREIGN KEY (SNO) REFERENCES S(SNO),

   FOREIGN KEY (PNO) REFERENCES P(PNO),

   FOREIGN KEY (JNO) REFERENCES J(JNO)

)CHARSET utf8;

–S供应商表；

INSERT INTO s VALUES(‘S1′,’精益’,20,’天津’);

INSERT INTO s VALUES(‘S2′,’盛锡’,10,’北京’);

INSERT INTO s VALUES(‘S3′,’东方红’,30,’北京’);

INSERT INTO s VALUES(‘S4′,’丰泰盛’,20,’天津’);

INSERT INTO s VALUES(‘S5′,’为民’,30,’上海’);

–P零件表：

INSERT INTO P VALUES(‘P1′,’螺母’,’红’,12);

INSERT INTO P VALUES(‘P2′,’螺丝’,’绿’,17);

INSERT INTO P VALUES(‘P3′,’螺丝刀’,’蓝’,14);

INSERT INTO P VALUES(‘P4′,’螺丝刀’,’红’,14);

INSERT INTO P VALUES(‘P5′,’凸轮’,’蓝’,40);

INSERT INTO P VALUES(‘P6′,’齿轮’,’红’,30);

–J工程项目表：

INSERT INTO J VALUES(‘J1′,’三建’,’北京’);

INSERT INTO J VALUES(‘J2′,’一汽’,’长春’);

INSERT INTO J VALUES(‘J3′,’弹 簧 厂’,’天津’);

INSERT INTO J VALUES(‘J4′,’造 船 厂’,’天津’);

INSERT INTO J VALUES(‘J5′,’机 车 厂’,’唐山’);

INSERT INTO J VALUES(‘J6′,’无线电厂’,’常州’);

INSERT INTO J VALUES(‘J7′,’半导体厂’,’南京‘);

–SPJ供应情况表：

INSERT INTO SPJ VALUES(‘S1′,’P1′,’J1’,200);

INSERT INTO SPJ VALUES(‘S1′,’P1′,’J3’,100);

INSERT INTO SPJ VALUES(‘S1′,’P1′,’J4’,700);

INSERT INTO SPJ VALUES(‘S1′,’P2′,’J2’,100);

INSERT INTO SPJ VALUES(‘S2′,’P3′,’J1’,400);

INSERT INTO SPJ VALUES(‘S2′,’P3′,’J2’,200);

INSERT INTO SPJ VALUES(‘S2′,’P3′,’J4’,500);

INSERT INTO SPJ VALUES(‘S2′,’P3′,’J5’,400);

INSERT INTO SPJ VALUES(‘S2′,’P5′,’J1’,400);

INSERT INTO SPJ VALUES(‘S2′,’P5′,’J2’,100);

INSERT INTO SPJ VALUES(‘S3′,’P1′,’J1’,200);

INSERT INTO SPJ VALUES(‘S3′,’P3′,’J1’,200);

INSERT INTO SPJ VALUES(‘S4′,’P5′,’J1’,100);

INSERT INTO SPJ VALUES(‘S4′,’P6′,’J3’,300);

INSERT INTO SPJ VALUES(‘S4′,’P6′,’J4’,200);

INSERT INTO SPJ VALUES(‘S5′,’P2′,’J4’,100);

INSERT INTO SPJ VALUES(‘S5′,’P3′,’J1’,200);

INSERT INTO SPJ VALUES(‘S5′,’P6′,’J2’,200);

INSERT INTO SPJ VALUES(‘S5′,’P6′,’J4’,500);

INSERT INTO SPJ VALUES(‘S1′,’P1′,’J2’,5000);

4.利用Update更新表中的记录：

①将p表中的所有红色零件的重量增加5。

UPDATE p SET WEIGHT=WEIGHT+5 WHERE color=’红’

②将spj表中所有天津供应商的QTY属性值减少10。用子查询。

UPDATE spj SET  qty=qty-10

WHERE sno IN

( SELECT sno FROM s WHERE city=’天津’)

利用Delete语句删除p表中的所有红色零件的记录。
       DELETE FROM  p WHERE  color=’红’

    但是受外码约束，改删除语句会被拒绝，因为spj表中有用到这些零件

（三）在实验二建立的mySPJ数据库及数据基础上完成如下简单查询。

（1）查询所有“天津”的供应商明细；

SELECT *

FROM s

WHERE city=’天津’;

（2）查询所有“红色”的14公斤以上的零件。

SELECT *

FROM p

WHERE color=’红’ AND weight>14;

（3）查询工程名称中含有“厂”字的工程明细。

SELECT *

FROM j

WHERE jname LIKE ‘%厂’;

（四）在实验二建立的mySPJ数据库及数据基础上完成如下连接查询。

（1）等值连接：求s表和j表的相同城市的等值连接。

SELECT s.*,j.*

FROM s,j

WHERE s.`city`=j.`city`;

（2）自然连接：查询所有的供应明细，要求显示供应商、零件和工程的名称，并按照供应工 SELECT sname,jname,pname

FROM S,J,P,SPJ

WHERE SPJ.`sno`=S.`sno` AND SPJ.`jno`=j.`jno` AND spj.`pno`=p.`pno`

ORDER BY j.jno,p.pno

(3)笛卡尔积：求s和p表的笛卡尔积

 SELECT *

FROM s,p;

(4)左连接：求j表和spj表的左连接。

SELECT j.jno,jname,city,sno,pno,qty

FROM j

 LEFT JOIN spj ON(j.`jno`=spj.`jno`);

(5)右连接：求spj表和j表的右连接。

SELECT j.jno,jname,city,sno,pno,qty

FROM spj

 RIGHT JOIN j ON(j.`jno`=spj.`jno`);

（三）在实验二的建立的mySPJ数据库及数据基础上完成如下分组查询。

1.求各种颜色零件的平均重量。

SELECT COLOR,AVG(WEIGHT)

FROM p

GROUP BY color

2.求北京供应商和天津供应商的总个数。

SELECT city,COUNT(city) 供应商个数

FROM  s

WHERE city IN (‘天津’,’北京’)

GROUP BY city

求各供应商供应的零件总数。
SELECT s.SNO,SNAME,SUM(QTY) 零件总数

FROM  s,spj

WHERE s.sno=spj.sno

GROUP BY s.SNO,SNAME

求各供应商供应给各工程的零件总数。
SELECT s.SNO,j.jno,SUM(QTY) 零件总数

FROM  s,spj,j

WHERE s.sno=spj.sno   AND spj.jno=j.jno

GROUP BY s.SNO,j.jno

求使用了100个以上P1零件的工程名称。
SELECT j.jname

FROM  spj,j

WHERE  spj.jno=j.jno AND QTY>100 AND spj.PNO=’p1′

求各工程使用的各城市供应的零件总数
SELECT j.jname,s.city,SUM(qty)

FROM  spj,j,s

WHERE spj.jno=j.jno AND s.sno=spj.sno

GROUP BY j.jname,s.city

（四）在实验二的建立的mySPJ数据库及数据基础上完成如下嵌套查询。

1、in连接谓词查询：

查询没有使用天津供应商供应的红色零件的工程名称。
     

SELECT jname

FROM j

WHERE jno NOT IN(

   SELECT jno

   FROM spj

   WHERE sno IN(

     SELECT sno

     FROM s

     WHERE s.city=’天津’

   ) AND pno IN (

      SELECT pno

      FROM p

      WHERE color=’红’

   )

 )

查询供应了1000个以上零件的供应商名称。(having)
SELECT sname

FROM s

WHERE sno IN

(

   SELECT sno

   FROM spj

   GROUP BY sno

   HAVING SUM(qty)>1000

)

2、比较运算符：求重量大于所有零件平均重量的零件名称。

SELECT pname

FROM p

WHERE WEIGHT>ALL

 (

  SELECT AVG(weight)

  FROM p

 )

3、Exists连接谓词：

 查询供应J1的所有的零件都是红色的供应商名称。
SELECT sno, sname

FROM s

WHERE NOT EXISTS

(    SELECT *

     FROM spj

     WHERE jno=’J1′ AND spj.sno=s.sno  AND   EXISTS (  SELECT * FROM p WHERE  spj.pno=p.pno  AND color!=’红’      )

)

至少用了供应商S1所供应的全部零件的工程号JNO。
SELECT  DISTINCT jno

FROM spj spjx

WHERE NOT EXISTS

(

   SELECT *

   FROM spj spjy

   WHERE sno=’S1′ AND NOT EXISTS(

       SELECT *

       FROM spj spjZ

       WHERE spjZ.sno=spjx.sno AND spjx.jno=spjZ.jno AND spjZ.pno=spjy.pno

  

   )

)

（二）在实验二的建立的mySPJ数据库及数据基础上完成如下视图的创建和查询并分析结果。

      （1）查询北京的供应商的编号、名称和城市。

  CREATE VIEW  Bei_jing_s

 AS

 SELECT sno,sname,city

 FROM s

 WHERE city LIKE ‘北京’;

   （2）查询S1供应商的所有供应明细。

 CREATE VIEW spj_s1

AS

SELECT *

FROM spj

WHERE  sno=’S1′;

   （3）查询各工程名称使用的各种颜色零件的个数。

    CREATE VIEW p_j_spj_sum

 AS

SELECT j.jname,p.color, SUM(qty)

FROM  spj,j,p

WHERE spj.jno=j.jno AND spj.pno=p.pno

GROUP BY j.jname,p.color

（1）、创建数据库创建表

CREATE DATABASE hospital CHARACTER SET utf8;

USE hospital;

CREATE TABLE doctor(

           doc_id INT AUTO_INCREMENT,

           doc_name VARCHAR(20),

           doc_sex VARCHAR(20),

           doc_age INT,

           doc_dep VARCHAR(20),

           PRIMARY KEY(doc_id)

           )CHARACTER SET utf8;

（2）、输入数据：

INSERT INTO doctor VALUES (NULL,’aa’,’male’,35,’aaaa’),

                            (NULL,’bb‘,’female’,32,’bbbb‘),

                            (NULL,’cc‘,’male’,42,’cccc‘),

                            (NULL,’dd’,’female’,41,’dddd’);

（3）、创建数据表 department。

CREATE TABLE department

              (dep_id INT AUTO_INCREMENT PRIMARY KEY,

              dep_name VARCHAR(20),

              dep_addr VARCHAR(20) )CHARACTER SET utf8;

（4）、输入数据：

INSERT INTO department VALUES (NULL,’aaaa’,’floor1′),

                                (NULL,’bbbb’,’floor2′),

                                (NULL,’cccc‘,’floor3’),

                                (NULL,’dddd’,’floor4′);

（5）、在 doctor 表上创建一个名为 doc_view 的视图，视图只包括 doc_id 和 doc_name 两个字段，并给这两个字段取名为 doid 和 doname；创建完后，查看该视图。

CREATE VIEW doc_view(doid,doname)

 AS

 SELECT doc_id,doc_name

 FROM doctor

(6) 创建视图 dd_view，视图包括医生工号、医生姓名、医生所在科室和科室的地址；创建完后，查看该视图。

CREATE VIEW dd_view

AS

SELECT doc_id,doc_name,doc_dep,dep_addr

FROM Doctor,Department

WHERE Doctor.`doc_dep` = Department.`dep_name`

(7)

a) 在 doctor 表上创建一个名为 doc_view2 的视图，要求只显示女性医生的信息；向该视图中插入数据 doc_id=5,doc_name=’ee’，观察能否插入成功并展示执行后视图 doc_view2 和表 doctor 内的数据；

CREATE VIEW doc_view2

AS

SELECT *

FROM Doctor

WHERE doc_sex = ‘female’;

INSERT INTO doc_view2(doc_id,doc_name) VALUES(5,’ee’);

b) 若在创建视图时加上 with check option 的约束呢？还能成功插入吗？请说明原因（说明：新建视图 doc_view3，插入数据改为 doc_id=6,doc_name=’ff’，其余不变）。

CREATE OR REPLACE VIEW doc_view3

AS

SELECT *

FROM Doctor

WHERE doc_sex = ‘female’

WITH CHECK OPTION;

INSERT INTO doc_view3(doc_id,doc_name) VALUES(6,’ff’);

(8) 删除所有视图。

DROP VIEW dd_view;

DROP VIEW doc_view;

DROP VIEW doc_view2;

DROP VIEW doc_view3;

实验六
参考答案：

（1）对于mySPJ数据库进行如下数据控制

①使用GRANT把对S表查询的权利授予WangLi。

GRANT SELECT ON `myspj`.`s` TO Wangli@’localhost‘;

②使用GRANT把对P表查询、插入、修改、删除的权利授予LiMing。

GRANT ALL PRIVILEGES ON `myspj`.* TO Liming@’localhost‘;

③使用REVOKE把LiMing对P表插入、删除的权利回收。

REVOKE SELECT ON `myspj`.`p` FROM Liming@’localhost‘;

REVOKE DELETE ON `myspj`.`p` FROM Liming@’localhost‘;

(2)创建下面医院数据库，按要求进行权限设置操作。

（1）、创建数据库创建表

CREATE DATABASE hospital CHARACTER SET utf8;

USE hospital;

CREATE TABLE doctor(

           doc_id INT AUTO_INCREMENT,

           doc_name VARCHAR(20),

           doc_sex VARCHAR(20),

           doc_age INT,

           doc_dep VARCHAR(20),

           PRIMARY KEY(doc_id)

           )CHARACTER SET utf8;

（2）、输入数据：

INSERT INTO doctor VALUES (NULL,’aa’,’male’,35,’aaaa’),

                            (NULL,’bb’,’female’,32,’bbbb’),

                            (NULL,’cc‘,’male’,42,’cccc’),

                            (NULL,’dd’,’female’,41,’dddd’);

（3）、创建数据表 department。

CREATE TABLE department

              (dep_id INT AUTO_INCREMENT PRIMARY KEY,

              dep_name VARCHAR(20),

              dep_addr VARCHAR(20) )CHARACTER SET utf8;

（4）、输入数据：

INSERT INTO department VALUES (NULL,’aaaa’,’floor1′),

                                (NULL,’bbbb’,’floor2′),

                                (NULL,’cccc’,’floor3′),

                                (NULL,’dddd’,’floor4′);

(5) 创建用户 user1，密码为’12345’。

CREATE USER ‘user1’@’localhost‘ IDENTIFIED BY ‘12345’;

(6) 授予用户 user1 对 doctor 表的查询权限，并进行验证。

GRANT SELECT

ON TABLE Doctor

TO ‘user1’@’localhost‘;

(7) 收回用户 user1 对 doctor 表的查询权限，并进行验证。

REVOKE SELECT

ON TABLE Doctor

FROM ‘user1’@’localhost‘

(8)

a) 创建用户 user2，密码为’12345’，授予其对 doctor 表的查询权限和修改字段

doc_name 的权限，并允许将此权限授予其他用户；验证 user2 对 doctor 表的查

询和更新权限（将 doc_id 为 1 的医生姓名更新为’xxx’）；

GRANT SELECT, UPDATE(doc_name)

ON Doctor

TO ‘user2’@’localhost‘

WITH GRANT OPTION;

User2登陆数据库

SELECT * FROM doctor;

UPDATE doctor SET doc_name=’XXX’ WHERE doc_id=1;

b) 创建用户 user3，密码为’12345’，授予其对 doctor 表的查询权限，并允许将此

权限授予其他用户；验证 user3 对 doctor 表的查询权限；

CREATE USER ‘user3’@’localhost’ IDENTIFIED BY ‘12345’;

GRANT SELECT

ON TABLE Doctor

TO ‘user3’@’localhost’

WITH GRANT OPTION;

User3登陆数据库

SELECT * FROM doctor;

c) 创建用户 user4，密码为’12345’，验证 user4 对 doctor 表的查询权限；

CREATE USER ‘user4’@’localhost’ IDENTIFIED BY ‘12345’;

User4登陆数据库

d) 用户 user2 将 doctor 表的查询权限授予 user4，验证 user4 对 doctor 表的查询权

限；

GRANT SELECT

ON doctor

TO ‘user4’@’localhost’;

SELECT * FROM doctor;

e) 用户 user3 将 doctor 表的查询权限授予 user4，验证 user4 对 doctor 表的查询权

限；

User3登录

GRANT SELECT

ON doctor

TO ‘user4’@’localhost’;

User4登录：

SELECT * FROM doctor

f) 用户 user3 撤销 user4 对 doctor 表的查询权限，验证 user4 对 doctor 表是否还有

查询权限；

REVOKE SELECT

ON doctor

FROM ‘user4’@’localhost’ IDENTIFIED BY ‘12345’;

User4无法查hostipal数据库

g) 用户 user2 撤销 user4 对 doctor 表的查询权限，验证 user4 对 doctor 表是否还有

查询权限。

User2登录：

REVOKE SELECT

ON doctor

FROM ‘user4’@’localhost’

INSERT INTO expert(exp_id, exp_name, exp_skill, exp_tel, exp_age)

VALUES (‘1’, ‘zhangsan’, ‘数据分析‘, ‘5888888’, ’35’);

INSERT INTO expert(exp_id, exp_name, exp_skill, exp_tel, exp_age)

VALUES (‘2’, ‘lisi’, ‘地形判断‘, ‘5888887’, 53),

(‘3′,’wangwu’,’外科’,’5888885′,24),

(‘4’,’xiaoming’,’决策’,’5888889′,19);

CREATE TABLE events_table

(

 event_id     INT ,

event_name VARCHAR(50),

event_desc   VARCHAR(50));

ALTER TABLE events_table

ADD CONSTRAINT events_pk PRIMARY KEY(event_id);

INSERT INTO events_table VALUES(1,’地震’,’发生地震’),

(2,’台风’,’多省有台风’),

(3,’干旱’,’发生干旱’),

(4,’火灾‘,’发生火灾‘);

CREATE TABLE contribution

(

   exp_id INT,  

   event_id INT,      

   contri VARCHAR(50),

   PRIMARY KEY(exp_id,event_id),

   CONSTRAINT fkey1 FOREIGN KEY (exp_id) REFERENCES expert(exp_id),

   CONSTRAINT fkey2 FOREIGN KEY (event_id) REFERENCES events_table(event_id)

);

INSERT INTO contribution VALUES

(1,4,’分析火灾损失‘),

(2,4,’判断火灾原因’),

(2,3,’判断干旱的地形’),

(4,1,’预测地震发生’)

INSERT INTO contribution VALUES

(5,4,’决策人员‘)

报错如下：

INSERT INTO contribution VALUES

(5,4,’决策人员‘)

(9) 直接将events_table 表删除会出现什么情况，为什么？（不需要删除该表）

(10) 将expert 表1 号专家的年龄改为102 岁，会出现什么情况，为什么？

UPDATE expert SET exp_age=102 WHERE exp_id=’1′

(11) 在expert 表中插入如下数据会发生什么情况？

INSERT INTO expert VALUES(6,’zs’,’分析’,’5888888′,95);

(13) 用命令方式删除contribution 表上的所有外键。

mysql删除外键方法：

1、不支持直接删除约束：alter table t drop constraint 外键名；

2、只支持分步删除：

a、先删除外键：alter table t drop foreign key 外键名；

b、再删除索引：alter table t drop index 外键名；

ALTER TABLE contribution

DROP FOREIGN KEY fkey1;

ALTER TABLE contribution

DROP FOREIGN KEY fkey2;

ALTER TABLE contribution DROP INDEX fkey2;

(14) a、在contribution 表上创建触发器函数，若contribution 表中event_id 被修改，相

应的events_table 表中的event_id 也被修改。

b、 创建AFTER 触发器，在contribution 表中更新数据时启动。

— 创建触发器

DELIMITER ;;

CREATE TRIGGER trg_tb_contribution_UPDATE_check AFTER UPDATE

ON contribution FOR EACH ROW

BEGIN

    UPDATE expert SET exp_id=new.exp_id WHERE exp_id=old.exp_id;

END;

;;

DELIMITER ;

(15) 在contribution 表中将exp_id=4 的event_id 改成5。展示contribution 表和events_table 表的情况。

(16) 删除expert 表上的触发器。

DROP TRIGGER  IF EXISTS trg_tb_ expert _insert_check;

DROP TRIGGER  IF EXISTS trg_tb_expert_update_check;

CREATE DATABASE Emergency CHARSET=utf8

CREATE TABLE expert(

exp_id   INT,

exp_name    VARCHAR(50) UNIQUE,

exp_skill      VARCHAR(50),

exp_tel  VARCHAR(30) UNIQUE,

exp_age INT,

CONSTRAINT expert_pk PRIMARY KEY(exp_id  )

) CHARSET=utf8;

— 创建触发器

DELIMITER ;;

CREATE TRIGGER trg_tb_expert_insert_check BEFORE INSERT

ON expert FOR EACH ROW

BEGIN

       DECLARE msg VARCHAR(100);

       IF NEW.exp_age <= 0 OR NEW.exp_age >= 100

       THEN

              SET msg = CONCAT(‘您输入的年龄值：’,NEW.exp_age,’ 为无效的年龄，请输入0到100以内的有效数字。’);

              SIGNAL SQLSTATE ‘HY000’ SET MESSAGE_TEXT = msg;

       END IF;

END;

;;

DELIMITER ;

— 创建触发器

DELIMITER ;;

CREATE TRIGGER trg_tb_expert_UPDATE_check BEFORE UPDATE

ON expert FOR EACH ROW

BEGIN

       DECLARE msg VARCHAR(100);

       IF NEW.exp_age <= 0 OR NEW.exp_age >= 100

       THEN

              SET msg = CONCAT(‘您输入的年龄值：’,NEW.exp_age,’ 为无效的年龄，请输入0到100以内的有效数字。’);

              SIGNAL SQLSTATE ‘HY000’ SET MESSAGE_TEXT = msg;

       END IF;

END;

;;

DELIMITER ;

完成实验六的如下作业，以文档形式提交。

（1）对于mySPJ数据库进行如下数据控制

①创建WangLi和LiMing用户

②使用GRANT把对S表查询的权利授予WangLi。

③使用GRANT把对P表查询、插入、修改、删除的权利授予LiMing。

④使用REVOKE把LiMing对P表插入、删除的权利回收。

(2)创建下面医院数据库，按要求进行权限设置操作。

医院数据库包括医生表 doctor，医生表包含医生工号（doc_id），医生姓名

（doc_name），医生性别（doc_sex），医生年龄（doc_age），医生所在科室

（doc_dep）；科室表 department，科室表包含科室编号（dep_id），科室名称

（dep_name），科室地址（dep_addr）。

(1) 创建数据表 doctor。

表6-1 doctor表结构

属性

类型

长度

是否为主键

doc_id

Int（自增）

是

doc_name

varchar

20

否

doc_sex

varchar

20

否

doc_age

int

否

doc_dep

varchar

20

否

这次doc_id设置为自增字段，它的语法规则是:属性名 属性类型  auto_increment

(2) 向 doctor 表中插入数据。

表6-2 doctor表的数据

doc_id

doc_name

doc_sex

doc_age

doc_dep

1

aa

male

35

aaaa

2

bb

female

32

bbbb

3

cc

male

42

cccc

4

dd

female

41

dddd

对自增字段数据插入时，对应位置填上null，那么该字段自动编号。

例如：

Insert into doctor values（null，‘aa’，‘male’，35，‘aaa’）；

(3) 创建数据表 department。

表6-3 department表结构

属性

类型

长度

是否为主键

dep_id

int

是

dep_name

varchar

20

否

dep_addr

varchar

20

否

(4) 向 department 表中插入数据。

表6-4 department表数据

dep_id

dep_name

dep_addr

1

aaaa

floor1

2

bbbb

floor2

3

cccc

floor2

4

dddd

floor3

(5) 创建用户 user1，密码为’12345’。

(6) 授予用户 user1 对 doctor 表的查询权限，并进行验证。

(7) 收回用户 user1 对 doctor 表的查询权限，并进行验证。

(8)

a) 创建用户 user2，密码为’12345’，授予其对 doctor 表的查询权限和修改字段doc_name 的权限，并允许将此权限授予其他用户；验证 user2 对 doctor 表的查询和更新权限（将 doc_id 为 1 的医生姓名更新为’xxx’）；

b) 创建用户 user3，密码为’12345’，授予其对 doctor 表的查询权限，并允许将此

权限授予其他用户；验证 user3 对 doctor 表的查询权限；

c) 创建用户 user4，密码为’12345’，验证 user4 对 doctor 表的查询权限；

d) 用户 user2 将 doctor 表的查询权限授予 user4，验证 user4 对 doctor 表的查询权限；

e) 用户 user3 将 doctor 表的查询权限授予 user4，验证 user4 对 doctor 表的查询权限；

f) 用户 user3 撤销 user4 对 doctor 表的查询权限，验证 user4 对 doctor 表是否还有查询权限；

g) 用户 user2 撤销 user4 对 doctor 表的查询权限，验证 user4 对 doctor 表是否还有查询权限。

（1）对于mySPJ数据库进行如下数据控制

①创建WangLi和LiMing用户

CREATE USER ‘WangLi’@’localhost’

CREATE USER ‘LiMing’@’localhost’

②使用GRANT把对S表查询的权利授予WangLi。

GRANT SELECT ON s TO ‘WangLi’@’localhost’

③使用GRANT把对P表查询、插入、修改、删除的权利授予LiMing。

GRANT SELECT,INSERT,UPDATE,DELETE ON p TO ‘LiMing’@’localhost’

④使用REVOKE把LiMing对P表插入、删除的权利回收。

REVOKE INSERT,DELETE ON p FROM ‘LiMing’@’localhost’

(2)创建下面医院数据库，按要求进行权限设置操作。

创建用户 user1，密码为’12345’。
CREATE USER user1@’localhost’ IDENTIFIED BY ‘12345’

授予用户 user1 对 doctor 表的查询权限，并进行验证。
GRANT SELECT ON doctor TO user1@’localhost’

(7) 收回用户 user1 对 doctor 表的查询权限，并进行验证。

REVOKE SELECT ON doctor FROM user1@’localhost’

创建用户 user2，密码为’12345’，授予其对 doctor 表的查询权限和修改字段doc_name 的权限，并允许将此权限授予其他用户；验证 user2 对 doctor 表的查询和更新权限（将 doc_id 为 1 的医生姓名更新为’xxx’）；
CREATE USER user2 IDENTIFIED WITH mysql_native_password BY ‘12345’

GRANT SELECT,UPDATE(doc_name) ON doctor TO user2 WITH GRANT OPTION

b) 创建用户 user3，密码为’12345’，授予其对 doctor 表的查询权限，并允许将此

权限授予其他用户；验证 user3 对 doctor 表的查询权限；

CREATE USER user3 IDENTIFIED WITH mysql_native_password BY ‘12345’

GRANT SELECT ON doctor TO user3 WITH GRANT OPTION

创建用户 user4，密码为’12345’，验证 user4 对 doctor 表的查询权限；
CREATE USER user4  IDENTIFIED WITH mysql_native_password BY ‘12345’

用户 user2 将 doctor 表的查询权限授予 user4，验证 user4 对 doctor 表的查询权限；
GRANT user2@’localhost’

TO user4@’localhost’;

用户 user3 将 doctor 表的查询权限授予 user4，验证 user4 对 doctor 表的查询权限；
GRANT user3@’localhost’

TO user4@’localhost’;

用户 user3 撤销 user4 对 doctor 表的查询权限，验证 user4 对 doctor 表是否还有查询权限；
 有，user2给的还有命令

REVOKE user3@’localhost’

FROM user4@’localhost’

g) 用户 user2 撤销 user4 对 doctor 表的查询权限，验证 user4 对 doctor 表是否还有查询权限。

REVOKE user2@’localhost’

FROM user4@’localhost’

无，给的命令都撤销了

1、写一个带参数的函数fun1，计算1+2+3+…+n。

2、编写一个存储过程sum_add，其功能是完成两个整数相加。

3、使用SQL语句在Mystudent“学生选课”数据库中创建一个名为xuesheng_cxbyid的带一个in类型参数的存储过程。该存储过程根据参数的值（学号），返回“学生”表中的对应学号的学生记录。

4、编写一个存储过程p_deleteStudent(in sno char(9),out msg char(8)),其功能是删除指定学号sno的学生记录，如果sc表中有该学生的选课记录，要级联删除该学生的选课记录，并根据删除情况返回msg信息（未找到学生，已经删除学生信息，已经删除学生信息及其选课信息）等。

5、编写一个存储存过程 set_cj(IN xh CHAR(9),IN kh CHAR(4))，其功能是设定Mystudent数据库的sc表中指定学号xh，指定课程号kh的学生成绩为空。

6、对Mystudent数据库中的表结构改造为如下图所示结构：

1、写一个带参数的函数fun1，计算1+2+3+…+n。

参考代码：

DROP FUNCTION IF EXISTS fun1;

DELIMITER $$

CREATE FUNCTION fun1(n INT) RETURNS INT

BEGIN

   DECLARE i INT DEFAULT 0;

   DECLARE SUM INT DEFAULT 0;

   sum_loop:LOOP

           SET i=i+1;

           IF i>n THEN LEAVE sum_loop;

           END IF;

            SET SUM=SUM+i;

    END LOOP sum_loop;

    RETURN SUM;

END$$

DELIMITER ;

SELECT fun1(100);

2、编写一个存储过程sum_add，其功能是完成两个整数相加。

参考代码：

DELIMITER //

create procedure sum_add(in x int,in y int)

begin

declare z int default 0;

set z=x+y;

select z;

end //

delimiter ;

3、使用SQL语句在Mystudent“学生选课”数据库中创建一个名为xuesheng_cxbyid的带一个in类型参数的存储过程。该存储过程根据参数的值（学号），返回“学生”表中的对应学号的学生记录。

参考代码：

DROP PROCEDURE IF EXISTS xuesheng_cxbyid;

DELIMITER $$

CREATE PROCEDURE xuesheng_cxbyid(IN uSno CHAR(9))

BEGIN

      SELECT * FROM student WHERE Sno=uSno;

END$$

DELIMITER ;  

USE mystudent;

CALL xuesheng_cxbyid(‘201215122’)

4、编写一个存储过程p_deleteStudent(in sno char(9),out msg char(8)),其功能是删除指定学号sno的学生记录，如果sc表中有该学生的选课记录，要级联删除该学生的选课记录，并根据删除情况返回msg信息（未找到学生，已经删除学生信息，已经删除学生信息及其选课信息）等。

参考代码：

DELIMITER ##

CREATE PROCEDURE p_deleteStudent(IN sno CHAR(9),OUT msg CHAR(30))

BEGIN

        DECLARE cs INT DEFAULT 0;/*学生的数量*/

        DECLARE cc INT DEFAULT 0;/*记录学生选课数量*/

        SELECT COUNT(*)  INTO cs FROM student WHERE student.sno=sno;

        SELECT COUNT(*) INTO cc FROM sc WHERE sc.sno=sno;

          IF cs=0 THEN

             SET msg=’未找到学生’;

          ELSEIF cc=0 THEN

              DELETE FROM student WHERE student.sno=sno;

              SET msg=’已经删除学生信息’;

           ELSE

              DELETE FROM sc WHERE sc.sno=sno;

               DELETE FROM student WHERE student.sno=sno;

              SET msg=’已经删除学生信息及其选课信息’;

            END IF;

 END ##

DELIMITER ;

SET @msg=”;

CALL p_deleteStudent(‘201215121’,@msg);

SELECT @msg;

5、编写一个存储存过程 set_cj(IN xh CHAR(9),IN kh CHAR(4))，其功能是设定Mystudent数据库的sc表中指定学号xh，指定课程号kh的学生成绩为空。

参考代码：

DELIMITER ##

CREATE PROCEDURE set_cj(IN xh CHAR(9),IN kh CHAR(4))

BEGIN

UPDATE sc SET grade=NULL WHERE sno=xh AND cno=kh;

END ##

DELIMITER ;

调用存储过程：

USE mystudent;

CALL set_cj(‘201215121’,1)

6、编写一个存储过程set_xy_cj(in xy char(9),in kh char(4))，该存储过程能够把给定学院xy和给定课程号kh的所有学生成绩设置为null。要求使用游标和调用第5题的set_cj存储过程实现。

参考代码：

DELIMITER ##

CREATE PROCEDURE set_xy_cj(IN xy CHAR(9),IN kh CHAR(4))

BEGIN

        DECLARE stsno CHAR(9) ;

        DECLARE done INT DEFAULT FALSE;

DECLARE cur CURSOR FOR SELECT sno FROM student,dept

WHERE student.`sdept`=dept.`Deptno` AND dname=xy;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;

FETCH cur INTO stsno;

WHILE(NOT done) DO

CALL set_cj (stsno,kh);

FETCH cur INTO stsno;

END WHILE;

CLOSE cur;

END ##

DELIMITER ;

USE mystudent;

CALL set_xy_cj(‘软件学院‘,1);

1、设教学数据库中有3 个关系：

学生关系S(SNO,SNAME,AGE,SEX)

学习关系SC(SNO,CNO,GRADE)

课程关系C(CNO,CNAME,TEACHER)

下面用关系代数表达式表达每个查询语句。

（1）检索学习课程号为C2的学生学号与成绩。

（2）检索学习课程号为C2的学生学号与姓名

（3）检索选修课程名为MATHS的学生学号与姓名。

（4）检索选修课程号为C2或C4的学生学号。

（5）检索至少选修课程号为C2和C4的学生学号。

（6）检索不学C2 课的学生姓名与年龄。

（7）检索学习全部课程的学生姓名。

（8）检索所学课程包含S3所学课程的学生学号。

(1) 检索学习课程号为C2的学生学号与成绩。

πSNO，GRADE(σ CNO=’C2′(SC))

(2) 检索学习课程号为C2的学生学号与姓名

πSNO，SNAME(σ CNO=’C2′(S  SC))

由于这个查询涉及到两个关系S 和SC，因此先对这两个关系进行自然连接，同一位学生

的有关的信息，然后再执行选择投影操作。

此查询亦可等价地写成：

πSNO，SNAME（ S） （ πSNO(σ CNO=’C2′(SC))）

这个表达式中自然连接的右分量为”学了C2课的学生学号的集合“。这个表达式比前一个

表达式优化，执行起来要省时间，省空间。

（3）检索选修课程名为MATHS的学生学号与姓名。

πSNO，SANME(σ CNAME=’MATHS'(S  SC   C))

（4）检索选修课程号为C2或C4的学生学号。

πSNO(σ CNO=’C2’∨CNO=’C4′(SC))

（5）检索至少选修课程号为C2或C4的学生学号。

π 1(σ1=4∧ 2=’C2’∧ 5=’C4’（SC×SC）)

这里（ SC×SC）表示关系SC自身相乘的乘积操作，其中数字1，2，4，5 都为它的结果

关系中的属性序号。

比较这一题与上一题的差别。

（6）检索不学C2 课的学生姓名与年龄。

πSNAME，AGE（ S）－ πSNAME，AGE(σ CNO=’C2’（ S     SC）)

这个表达式用了差运算，差运算的左分量为”全体学生的姓名和年龄” ，右分量为” 学了C2

课的学生姓名与年龄”。

（7）检索学习全部课程的学生姓名。

编写这个查询语句的关系代数过程如下：

(a) 学生选课情况可用πSNO,CNO(SC) 表示；

(b) 全部课程可用πCNO(C)表示；

(c) 学了全部课程的学生学号可用除法操作表示。

操作结果为学号SNO的集合， 该集合中每个学生（对应SNO)与C中任一门课程号CNO

配在一起都在πSCO，CNO（ SC）中出现（即SC中出现），所以结果中每个学生都学了全部

的课程（这是” 除法”操作的含义） ：

πSNO,CNO(SC) ÷πCNO(C)

(d) 从SNO求学生姓名SNAME，可以用自然连结和投影操作组合而成：

πSNAME(S      (πSNO,CNO(SC÷) πCNO(C)))

这就是最后得到的关系代数表达式。

（8）检索所学课程包含S3所学课程的学生学号。

注意：学生S3 可能学多门课程，所以要用到除法操作来表达此查询语句。

学生选课情况可用操作π SNO,CNO(SC) 表示；

所学课程包含学生S3所学课程的学生学号，可以用除法操作求得：

πSNO,CNO(SC) ÷ πCNO(σ SNO=’S3′(SC) ）

设有一个SPJ数据库，包括S，P， J，SPJ 四个关系模式：

S( SNO，SNAME ，STATUS ，CITY) ；

P(PNO，PNAME ，COLOR ，WEIGHT) ；

J(JNO， JNAME ， CITY) ；

SPJ(SNO，PNO，JNO，QTY) ；

供应商表S 由供应商代码（ SNO）、供应商姓名（ SNAME ）、供应商状态（ STATUS）、供应商所在城

市（ CITY ）组成；零件表P 由零件代码（ PNO）、零件名（ PNAME ）、颜色（ COLOR ）、重量（ WEIGHT ）

组成；工程项目表J 由工程项目代码（ JNO）、工程项目名（ JNAME ）、工程项目所在城市（ CITY ）组成；

供应情况表SPJ 由供应商代码（SNO）、零件代码（PNO）、工程项目代码（JNO）、供应数量（QTY ）组成，

表示某供应商供应某种零件给某工程项目的数量为QTY 。

试用关系代数完成如下查询：

（1） 求供应工程J1 零件的供应商号码SNO；

（2） 求供应工程J1 零件P1 的供应商号码SNO；

（3） 求供应工程J1 零件为红色的供应商号码SNO；

（4） 求没有使用天津供应商生产的红色零件的工程号JNO；

（5） 求至少用了供应商S1 所供应的全部零件的工程号JNO。

假设有一个数据库包含以下关系模式：

Teacher(Tno, Tname, Tage, Tsex)

Department(Dno, Dname, Tno)

Work(Tno, Dno,Year, Salary)

教师表Teacher 由教师代码Tno、教师名字Tname、教师年龄Tage、教师性别Tsex组成。

系表Department 由系代码Dno、系名Dname、系主任代码Tno 组成

工作表Work 由教师代码Tno、系代码Dno、入职年份Year、工资Salary 组成

使用关系代数表示每个查询

（1） 列出工资超过5000 的教师的不同年龄；

（2） 查找不在计算机系工作的教师代码；

（3） 系主任T1 管辖范围内的所有教师姓名

按照实验9的实验作业要求，完成作业，写出实验报告，并把实验报告以提交到下面输入框内：

六、实验作业要求：

1、参照本次实验的内容，编写两个存储过程分别实现对课程信息的查询与维护。

2、参照本次实验的内容，在Kcxxb（课程信息表）上增加mcsx（课程名称的拼音缩写），并编写两个触发器实现对该字段的维护。

1、

DELIMITER $$

USE `xkgl`$$

DROP PROCEDURE IF EXISTS `XsxxCW`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `XsxxCW`(kcid INT,kcdm VARCHAR(10),kcmc VARCHAR(50),xf DOUBLE,mcsx VARCHAR(10))

BEGIN

   IF kcid=0 THEN

     INSERT INTO kcxxb VALUES(kcid,kcdm,kcmc,xf,mcsx);

   ELSEIF kcid<0 THEN

     DELETE FROM kcxxb WHERE kcxxb.`kcid`=kcid;

   ELSE

     UPDATE kcxxb SET kcxxb.`kcid`=kcid,kcxxb.`kcdm`=kcdm,kcxxb.`kcmc`=kcmc,kcxxb.`xf`=xf,

     kcxxb.`mcsx`=mcsx

      WHERE kcxxb.`kcid`=kcid;

   END IF;

    END$$

DELIMITER ;

CALL `XsxxCx`(0,’真’);

CALL `XsxxCx`(1,’李四’);

CALL `XsxxCx`(2,’六’);

DELIMITER $$

CREATE PROCEDURE `xkgl`.`XsxxCx`(cxlb INT,cxnr VARCHAR(50))

    BEGIN

   IF cxlb=0 THEN

     SELECT * FROM xsxxb;

   ELSEIF cxlb=1 THEN

     SELECT * FROM xsxxb WHERE xm=cxnr;

   ELSE

    SELECT * FROM xsxxb WHERE xm LIKE CONCAT(‘%’,cxnr,’%’) OR xmsx LIKE CONCAT(‘%’,cxnr,’%’);

   END IF;

    END$$

DELIMITER ;

CALL `XsxxCW`(0,006,’体育’,9,’ty’);

CALL `XsxxCW`(6,006,’语言‘,10,’yy’);

2、

DELIMITER $$

USE `xkgl`$$

DROP TRIGGER /*!50032 IF EXISTS */ `update_kcxxb_insert`$$

CREATE

    /*!50017 DEFINER = ‘root‘@’localhost’ */

    TRIGGER `update_kcxxb_insert` BEFORE INSERT ON `kcxxb`

    FOR EACH ROW BEGIN

   SET new.mcsx=PysxCx(new.kcmc);

    END;

$$

DELIMITER ;

DELIMITER $$

USE `xkgl`$$

DROP TRIGGER /*!50032 IF EXISTS */ `update_kcxxb_update`$$

CREATE

    /*!50017 DEFINER = ‘root‘@’localhost’ */

    TRIGGER `update_kcxxb_update` BEFORE UPDATE ON `kcxxb`

    FOR EACH ROW BEGIN

   SET new.mcsx=PysxCx(new.kcmc);

    END;

$$

DELIMITER ;

INSERT INTO `kcxxb` VALUE(7,’007′,’大学英语’,9,NULL);

UPDATE `kcxxb` SET kcmc=’程序设计‘ WHERE kcmc=’语言‘;

三、实验结果

**

1、

2、

…

1、简述事务的概念及事务的4个特性。

2、为什么事务非正常结束时，会影响数据库数据的正确性？

3、事务的4种隔离级别分别是什么？

实验作业：

2、设计一个存储过程，两条渔船之间转储渔获（记录从一条船将指定数量的渔获转移到另一条渔船上）的功能。在同一个渔获表中的两个账户之间转移，转出量不能超出渔船的现存渔获量，转入、转出渔船编号不存在的拒绝转移动。（假设只有一种渔获）

渔获登记表（catchtable）的结构：渔船编号(id)，渔船名(shipname)，渔获量(weight)。数据类型、码等自定

过程名：pro1，第一参数转移渔获重量：e，第二参数转出渔船编号：idSource，第三参数转入渔船编号：idTarget

注意：要提交存储过程代码和调用存储过程结果画面截图。

3、在course表上添加一个读锁。然后开启两个会话，验证读锁。

4、在course表上添加一个写锁。然后开启两个会话，验证写锁。

查看事务的隔离级别。
SHOW VARIABLES LIKE ‘%isolation%’;

2、设计一个存储过程，两条渔船之间转储渔获（记录从一条船将指定数量的渔获转移到另一条渔船上）的功能。在同一个渔获表中的两个账户之间转移，转出量不能超出渔船的现存渔获量，转入、转出渔船编号不存在的拒绝转移动。（假设只有一种渔获）

渔获登记表（catchtable）的结构：渔船编号(id)，渔船名(shipname)，渔获量(weight)。数据类型、码等自定

过程名：pro1，第一参数转移渔获重量：e，第二参数转出渔船编号：idSource，第三参数转入渔船编号：idTarget

注意：要提交存储过程代码和调用存储过程结果画面截图。

DELIMITER $$

CREATE PROCEDURE pro1(e INT,idSource INT,idTarget INT)

BEGIN

       DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;

       START TRANSACTION;

       UPDATE catchtable SET weight=weight+e WHERE id=idTarget;

       UPDATE catchtable SET weight=weight-e WHERE id=idSource;

       COMMIT;

END

$$

DELIMITER ;

在course表上添加一个读锁。然后开启两个会话，验证读锁。
LOCK TABLES course READ;

4、在course表上添加一个写锁。然后开启两个会话，验证写锁。

lock tables course write;