数据库tiku

最新推荐文章于 2024-11-08 18:30:00 发布

远道244

最新推荐文章于 2024-11-08 18:30:00 发布

阅读量514

点赞数 5

文章标签：数据库 sql java

本文链接：https://blog.csdn.net/weixin_60838732/article/details/138866924

版权

把创建数据库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;

实验三数据查询-简单查询和连接查询

把实验步骤（三）中对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;

--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数据库相关的（三）和（四）的内容写在实验报告中。

作业答案：

（三）在实验二建立的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数据库相关的（三）和（四）的内容写在实验报告中。

作业参考答案：

（三）在实验二的建立的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='红'

)

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

SELECT sno,sname,city

FROM s

WHERE city LIKE '北京';

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

CREATE VIEW spj_s1

SELECT *

FROM spj

WHERE sno='S1';

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

CREATE VIEW p_j_spj_sum

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)

SELECT doc_id,doc_name

FROM doctor

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

CREATE VIEW dd_view

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

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

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数据库进行如下数据控制

①创建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

否

doc_sex

varchar

否

doc_age

int

否

doc_dep

varchar

否

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

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

表6-2 doctor表的数据

doc_id

doc_name

doc_sex

doc_age

doc_dep

male

aaaa

female

bbbb

male

cccc

female

dddd

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

例如：

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

(3) 创建数据表 department。

表6-3 department表结构

属性

类型

长度

是否为主键

dep_id

int

是

dep_name

varchar

否

dep_addr

varchar

否

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

表6-4 department表数据

dep_id

dep_name

dep_addr

aaaa

floor1

bbbb

floor2

cccc

floor2

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 `myspj`.`s` TO Wangli@'localhost';

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

GRANT SELECT,INSERT,UPDATE,DELETE 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'

实验七 mysql编程基础

实验作业
（一）对于mystudent数据库完成下面操作。

1、创建一个存储过程，根据给定的学生学号返回该学生的姓名。

2、创建一个存储过程，根据课程号，统计选修该课程的人数，并将人数以输出。

（二）在SQLyog下创建Xkgl（选课管理）数据库，包括Xsxxb（学生信息表）、Kcxxb（课程信息表）、Xkjlb（选课记录表）三个基本表,一个辅助表Hzpyb（汉字拼音表），导入Hzpyb（汉字拼音表，以脚本文件的形式提供给学生）。

1、创建数据库

CREATE DATABASE IF NOT EXISTS Xkgl;

2、学生信息表Xsxxb

创建学生信息表Xsxxb，表的结构如下：

表7-1 Xsxxb结构

3、课程信息表Kcxxb

创建课程信息表Kcxxb，表的结构如下：

表7-2 kcxxb结构

4、选课记录表Xkjlb

创建课程信息表Xkjlb，表的结构如下：

表7-3 xkjlb结构

5、辅助表汉字拼音表Hzpyb

创建汉字拼音表Hzpyb，表的结构如下：

序号

列名

数据类型

键

自增

索引

说明

Char(2)

汉字

Char(1)

简拼

Varchar(10)

拼音

输入一些学生、课程、选课数据。

（三）编写存储过程XsxxWh(学生信息查询) 和XsxxWh(学生信息维护) 实现对学生信息的查询和维护

DROP PROCEDURE IF EXISTS `XsxxCx`;

DELIMITER $$

CREATE PROCEDURE `XsxxCx`(cxlb int,cxnr varchar(50))

BEGIN

IF cxlb=0 then /* 查询所有学生信息*/

SELECT * FROM Xsxxb;

ELSEIF cxlb=1 then /* 查询指定学号的学生信息*/

SELECT * FROM Xsxxb WHERE xh=cxnr;

ELSE /* 按姓名或姓名缩写模糊查询*/

SELECT * FROM Xsxxb WHERE xm LIKE concat(cxnr,'%') OR xmsx LIKE CONCAT(cxnr,'%'); /* CONCAT字符串拼接函数 */

END IF;

END$$

DELIMITER ;

该存储过程有两个参数，cxlb（查询类别）和cxnr（查询内容（条件））可以实现查询所有学生信息或指定学号的学生信息或按姓名或姓名的拼音缩写模糊查询。

DROP PROCEDURE IF EXISTS `Xsxxwh`;

DELIMITER $$

CREATE PROCEDURE `Xsxxwh`(xsid INT,xh VARCHAR(10),xm VARCHAR(50),xb CHAR(2),zydm INT,nj INT,bj VARCHAR(10),sjh VARCHAR(20))

BEGIN

IF xsid=0 THEN

INSERT Xsxxb (xh,xm,xb,zydm,nj,bj,sjh) VALUES (xh,xm,xb,zydm,nj,bj,sjh);

ELSEIF xsid<0 THEN

DELETE FROM xsxxb WHERE `xsxxb`.`xsid`=-xsid;

ELSE

UPDATE Xsxxb SET `xsxxb`.`xh`=xh, `xsxxb`.`xm`=xm, `xsxxb`.`xb`=xb, `xsxxb`.`zydm`=zydm, `xsxxb`.`nj`=nj, `xsxxb`.`bj`=bj, `xsxxb`.`sjh`=sjh WHERE `xsxxb`.`xsid`=xsid;

END IF;

END$$

DELIMITER ;

该存储过程可以实现对学生信息的增、删、改操作。

（三）编写函数PysxCx(拼音缩写查询) 实现汉字字符串的拼音缩写查询

DELIMITER $$

CREATE FUNCTION `pysxcx`(zw VARCHAR(50)) RETURNS VARCHAR(10) CHARSET utf8

BEGIN

SET @l=CHAR_LENGTH(zw);

SET @i=1;

SET @pysx='';

SET @jp='';

WHILE @i<=@l DO

SET @hz=SUBSTRING(zw,@i,1);

SELECT jp INTO @jp FROM hzpyb WHERE hz=@hz;

SET @pysx=CONCAT(@pysx,@jp);

SET @i=@i+1;

END WHILE;

RETURN @pysx;

END

DELIMITER ;

该函数中用到的一些MySQL内置函数说明：

CHAR_LENGTH（）：按照字符个数统计字符串长度；
SUBSTRING（参数1，参数2，参数3）：三个参数分别表示：参数1表示需要截取的字符串，参数2表示从字符串的那个位置开始截取（字符串下标从1开始），参数3表示要截取多少位，如果不写，表示截取从参数2指定的位置开始剩下的全部字符
CONCAT（）：用于将多个字符串连接成一个字符串
函数的使用1：

图7-1内置函数使用效果1

函数的使用2：

图7-2内置函数使用效果2

（四）在SQLyog下创建Update_xmsx_before_insert_Xsxxb，实现当在Xsxxb中插入记录时，自动维护该记录中的xmsx字段。

CREATE TRIGGER `Update_xmsx_before_insert_Xsxxb` BEFORE INSERT ON `xsxxb`

FOR EACH ROW BEGIN

SET new.xmsx=pysxcx(new.xm);

END;

验证该触发器：执行插入操作

图7-3验证该触发器页面1

查看操作结果

图7-4验证该触发器页面2

（五）在SQLyog下创建Update_xmsx_before_ update_xsxxb_xm，实现当在Xsxxb中更新姓名时，自动维护该记录中的xmsx字段。

CREATE TRIGGER `Update_xmsx_before_update_xsxxb_xm` BEFORE UPDATE ON `xsxxb`

FOR EACH ROW BEGIN

IF new.xm<>old.xm THEN

SET new.xmsx=pysxcx(new.xm);

END IF;

END;

验证该触发器：执行更新操作

图7-5验证该触发器页面3

查看操作结果

图7-6验证该触发器页面4

（六）参照前面学生信息的查询和维护内容，编写两个存储过程分别实现对课程信息表Kcxxb的查询与维护

（七）在Kcxxb（课程信息表）上增加mcsx（课程名称的拼音缩写），并编写两个触发器实现对该字段的维护。

实验八数据库的完整性

六、实验作业

作业

(1) 新建数据库Emergency，创建数据表expert，将主键约束命名为expert_pk，设置

exp_name 和exp_tel 取值唯一, 并保证exp_age 取值为0-100 岁（mysql8.0版本中可以用check实现，mysql5.X版本需要触发器实现）。

属性

类型

长度

exp_id

int

exp_name

varchar

exp_skill

varchar

exp_tel

varchar

exp_age

int

(2) 使用sql 语句，向expert 中插入如下数据：

exp_id

exp_name

exp_skill

exp_tel

exp_age

zhangsan

数据分析

5888888

lisi

地形判断

5888887

wangwu

外科

5888885

xiaoming

决策

5888889

(3) 创建数据表events_table，并设置event_name 非空。

属性

类型

长度

event_id

int

event_name

varchar

event_desc

varchar

创建完成后，向events_table 表中补充声明主键，将event_id 作为主键，命名为events_pk。

(4) 使用sql 语句，向events_table 中插入如下数据：

event_id

event_name

event_desc

地震

发生地震

台风

多省有台风

干旱

发生干旱

火灾

发生火灾

(5) 创建数据表contribution，并定义主键约束和外键约束，约束命名自定义（如果没约束名，后面无法单独删除）。

属性

类型

长度

exp_id

int

event_id

int

contri

varchar

(6) 使用sql 语句，向contribution 中插入如下数据：

exp_id

event_id

contri

分析火灾损失

判断火灾原因

判断干旱的地形

预测地震发生

(7) 向contribution 表中添加如下数据，会发生什么情况，为什么？

exp_id

event_id

contri

决策人员

(8) 将contribution 表中event_id 为1 的值改为5，会出现什么情况，为什么？

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

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

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

exp_id

exp_name

exp_skill

exp_tel

exp_age

分析

5888888

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

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

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

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

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

的情况。

删除expert 表上的触发器。
作业参考答案：

参考答案：

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 ;

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;

实验九事务处理与并发控制

作业：

设计一个存储过程，实现在同一个账户表中的两个账户之间转账的功能，转出账户余额不足或转出、转入账户不存在的拒绝转账。（用事务控制）
账户表（account）的结构：账号(id)，用户名(username)，余额(balance)。数据类型、码等自定

过程名：pro1，第一参数转账金额：e，第二参数转出账户：idSource，第三参数转入账户：idTarget

参考答案：

CREATE DATABASE banks

创建表

CREATE TABLE account(

id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

username VARCHAR(32) NOT NULL UNIQUE COMMENT '账户名',

balance DECIMAL(10,2) NOT NULL COMMENT '存款金额'

)ENGINE=INNODB CHARSET=utf8;

插入数据

INSERT INTO account VALUES(NULL,'张三',5000),(NULL,'李四',100)

定义存储过程

DELIMITER $$

CREATE

/*[DEFINER = { user | CURRENT_USER }]*/

PROCEDURE `banks`.`pro1`(IN e INT,IN idSource INT,IN idTarget INT)

/*LANGUAGE SQL

| [NOT] DETERMINISTIC

| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

| SQL SECURITY { DEFINER | INVOKER }

| COMMENT 'string'*/

BEGIN

DECLARE money1 INT DEFAULT -1;

DECLARE money2 INT DEFAULT -1;

SELECT balance INTO money1 FROM account WHERE id=idSource FOR UPDATE;

SELECT balance INTO money2 FROM account WHERE id=idTarget FOR UPDATE;

IF money1<e THEN

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient balance';

ELSE

UPDATE account SET balance = balance - e WHERE id = idSource;

UPDATE account SET balance=balance+e WHERE id=idTarget;

END IF;

COMMIT;

END;

END$$

DELIMITER ;

调用存储过程

CALL pro1(100,1,2)

CALL pro1(100,0,2)
————————————————

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

原文链接：https://blog.csdn.net/m0_74062024/article/details/135037047

远道244

关注

5
点赞
踩
6

收藏

觉得还不错? 一键收藏
1
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫