MySQL实验指导设计性实验参考答案

MySQL实验指导设计性实验参考答案实验一（1）设计能够表示出生产厂商和产品关系的数据模型。其中生产厂商包括厂商名称、地址、电话；产品包括品
牌、型号、价格；生产厂商生产某产品的数量和日期。① 确定产品实体和生产厂商实体的属性和码。生产厂商：厂商名称、地址、电话，码为厂商
名称 产品：品牌、型号、价格，码为品牌和型号② 确定产品和生产厂商之间的联系，给联系命名并指出联系的类型。一个生产厂商可以生产多种
产品，一种产品也可以有多个生产厂商生产，所以产品和生产厂商间是多对多关系，即m：n，联系名称：生产。③ 确定联系本身的属性。联系“
生产”的属性有数量和日期。④ 画出产品与生产厂商关系的E-R图。厂商名称yxkg号地址电话生产 产品价格品牌日期数量 型号 （2）
设计能够表现出车队、车辆和司机关系的数据模型。① 确定车队、车辆和司机实体的属性和码。车队：车队号、车队名，码为车队号?车辆：车牌
号、厂家，出厂日期，码为车牌号?司机：司机编号、姓名、电话，码为司机编号 ② 确定实体之间的联系，给联系命名并指出联系的类型。每个
车队可聘用若干司机，但每个司机只能应聘于一个车队，车队与司机联系类型是1：n，联系名称：聘用；每个车队可以拥有若干车辆，但每辆车只
能属于一个车队，车队与车辆联系类型是1：n，联系名称：拥有；每个司机可以使用多辆车，每辆车可被多个司机使用，车辆与司机联系类型是m
:n，联系名称为：使用。③ 确定联系本身的属性。联系“聘用”的属性有“聘用开始时间”和“聘期”两个属性，联系“使用“有“使用日期”
和“公里数”两个属性。④ 画出E-R图。电话厂家使用车辆司机编号牌照号司机出厂日期姓名车队号车队名拥有 聘用 nmn1n聘用开始
时间聘期使用日期公里数实验三1. CREATE DATABASE studentsdb; 2. USE studentsdb;3.
CREATE TABLE student_info( 学号 char(4) NOT NULL PRIMARY KEY, 姓名
char(8) NOT NULL, 性别 char(2), 出生日期 date, 家庭住址 varchar(50));AL
TER TABLE student_info MODIFY 姓名 char(8) NULL;ALTER TABLE student
_info CHANGE 家庭住址 地址 varchar(50);ALTER TABLE student_info ADD 备注
varchar(50);ALTER TABLE student_info DROP 出生日期;CREATE TABLE stu S
ELECT FROM student_info;DROP TABLE stu,student_info;DROP DATABA
SE studentsdb;实验四1. INSERT INTO salary VALUES(''7369'',8000,1320),(
''7499'',12000,1463),(''7521'',11500,1463),(''7566'',13000,1566);ALTER
TABLE salary ADD sal FLOAT;3. SET SQL_SAFE_UPDATES=0; UPDATE sala
ry SET sal=income-outcome;4. DELETE FROM salary WHERE income>=800
0 AND income<=10000;5. DROP TABLE salary;6. DROP DATABASE yggl;实验
五SELECT COUNT(DISTINCT 学号) 选课人数 FROM grade;SELECT 姓名,出生日期 FROM s
tudent_infoWHERE 学号 IN(''0001'',''0002'',''0003'',''0004'');3. INSERT INT
O grade(学号,课程编号) VALUES(''0004'',''0001'');4. SELECT 学号,课程编号 FROM gr
ade WHERE 分数 IS NULL;5. SET SQL_SAFE_UPDATES=0;DELETE FROM grade
WHERE 分数 IS NULL;SELECT FROM grade WHERE 分数>=90 and (课程编号=''0001
'' or 课程编号=''0002'');7. SELECT 学号,SUM(分数) 总分 FROM grade GROUP BY 学号
HAVING SUM(分数)>400 ORDER BY 总分;8. SELECT MAX(分数) 最高分,MIN(分数) 最低分
,MAX(分数)-MIN(分数) 分差 FROM grade WHERE 课程编号=''0001'';9. SELECT 学号,COU
NT() 课程数 FROM grade WHERE 分数>70GROUP BY 学号 HAVING 课程数>=3;10. SEL
ECT 课程编号,AVG(分数) 平均分 FROM grade GROUP BY 课程编号 HAVING 平均分 BETWEEN
80 AND 90;实验六1. SELECT s.学号,姓名,分数 FROM student_info s,curriculum
c,grade gWHERE s.学号=g.学号 AND g.课程编号=c.课程编号 AND 课程名称=''C语言程序设计'' AN
D 分数>(SELECT avg(分数) FROM grade g,curriculum c WHERE g.课程编号=c.课程编
号 AND 课程名称=''C语言程序设计'');2. SELECT 姓名,课程名称,分数 FROM student_info s LE
FT OUTER JOIN grade g ON s.学号=g.学号LEFT OUTER JOIN curriculum c ON
g.课程编号=c.课程编号;3. SELECT 课程名称 FROM curriculum WHERE 课程编号 IN(SELEC
T 课程编号 FROM grade WHERE 学号=(SELECT 学号 FROM student_info WHERE 姓名
=''张青平''));4. SELECT COUNT() FROM grade WHERE 课程编号=''0001'' AND 分数=
(SELECT MAX(分数) FROM grade WHERE 课程编号=''0001'');5. SELECT 姓名 FROM s
tudent_info s WHERE 学号 IN (SELECT 学号 FROM grade WHERE 课程编号=''0001''
) ORDER BY (SELECT 分数 FROM grade g WHERE 课程编号=''0001'' AND s.学号=g.学
号) DESC;实验七CREATE DATABASE job;USE job;CREATE TABLE user( userid
INT NOT NULL, username VARCHAR(20) NOT NULL, passwd VARCHAR(20) N
OT NULL, info TEXT, UNIQUE INDEX index_uid(userid DESC), INDEX in
dex_user(username,passwd), FULLTEXT INDEX index_info(info) );3. C
REATE TABLE information( id INT NOT NULL, name VARCHAR(20) NOT NU
LL,sex VARCHAR(4) NOT NULL, birthday DATE, address VARCHAR(50), t
el VARCHAR(20), pic BLOB);4. CREATE INDEX index_name ON informati
on(name);5. CREATE INDEX index_bir ON information(birthday,addres
s);6. ALTER TABLE information ADD INDEX index_id(id ASC);7. DROP
INDEX index_user ON user;8. ALTER TABLE information DROP INDEX in
dex_id;实验八1. USE studentsdb;CREATE VIEW v_student AS SELECT 学号,姓名
,出生日期,家庭住址 FROM student_info WHERE 姓名 LIKE ''张%'' AND 出生日期>=''2000/0
1/01'';SELECT FROM v_student;2. CREATE VIEW v_cnt AS SELECT 课程编
号,COUNT() 人数 FROM grade WHERE 分数>=90 GROUP BY 课程编号; SELECT FRO
M v_cnt;3. CREATE VIEW v_grade AS SELECT 课程名称,MAX(分数) 最高分,MIN(分数)
最低分,AVG(分数) 平均分 FROM grade g INNER JOIN curriculum c ON g.课程编号=
c.课程编号 GROUP BY 课程名称;SELECT FROM v_grade;4. INSERT INTO v_stude
nt(学号,姓名,出生日期) VALUES(''0010'',''张三丰'',''2000-03-23'');SELECT FROM v_
student;SELECT FROM student_info;5. UPDATE v_student SET 家庭住址=''
广州市中山路3号'' WHERE 学号=''0010'';6. INSERT INTO v_student(学号,姓名,出生日期) VA
LUES(''0011'',''赵海棠'',''2001-11-12'');SELECT FROM v_student;SELECT
FROM student_info;7. ALTER VIEW v_student AS SELECT 学号,姓名,出生日期,家庭
住址 FROM student_info WHERE 姓名 LIKE ''张%'' AND 出生日期>=''2000/01/01'' WI
TH CHECK OPTION;8. INSERT INTO v_student(学号,姓名,出生日期) VALUES(''001
2'',''李春桃'',''2000-1-12''); 插入失败，因为所插入的姓名与创建视图的条件不符。9. DELETE FROM v_s
tudent WHERE 学号=''0010'';10. DELETE FROM student_info WHERE 学号=''001
1''; 不能通过视图v_student删除0011的记录。实验九1. USE studentsdb;CREATE TABLE c
AS SELECT FROM curriculum;CREATE TABLE g AS SELECT FROM grade
;2. ALTER TABLE c ADD PRIMARY KEY(课程名称);3. INSERT INTO c(课程编号,课程名
称) VALUES(''0006'',''计算机应用基础'');4. ALTER TABLE C DROP PRIMARY KEY;5.
ALTER TABLE c ADD PRIMARY KEY(课程编号);ALTER TABLE g ADD FOREIGN KEY
(课程编号) REFERENCES c(课程编号) ON DELETE CASCADE;6. DELETE FROM c WHER
E 课程编号=''0001'';SELECT FROM g;ALTER TABLE g ADD CHECK(分数>=0);INSE
RT INTO g VALUES(''0004'',''0001'',-80);ALTER TABLE c ADD CONSTRAINT
uq_name UNIQUE(课程名称);DROP TABLE c,g;实验十1. SELECT 姓名,year(current_
date())-year(出生日期) 年龄 FROM student_info;2. SELECT 姓名,CASE WHEN 分
数>=90 THEN ''优秀'' WHEN 分数>=80 THEN ''良好'' WHEN 分数>=70 THEN ''中'' WHEN 分
数>=60 THEN ''及格'' ELSE ''不及格'' END AS 成绩等级FROM student_info s,grade g
WHERE s.学号=g.学号 and 课程编号=''0001'';3. DELIMITER @@CREATE FUNCTION n
um_func(cname VARCHAR(50)) RETURNS INT BEGIN DECLARE num INT; SEL
ECT COUNT() INTO num FROM grade g,curriculum c WHERE g.课程编号=c.课程
编号 and 课程名称=cname; RETURN num; END @@ SELECT num_func(''C语言程序设计'')
;4.DELIMITER @@CREATE FUNCTION f_fac(n int) RETURNS INT BEGIN DEC
LARE i,s INT; SET i=1; SET s=1; WHILE i<=n DO SET s=si; SET i=i+
1; END WHILE; RETURN s; END@@ DELIMITER ; SELECT f_fac(4);5. DEL
IMITER @@ CREATE FUNCTION f_stu(sid char(4),type int) RETURNS VA
RCHAR(50) BEGIN CASE type WHEN 1 THEN RETURN(SELECT 姓名 FROM stude
nt_info WHERE 学号=sid); WHEN 2 THEN RETURN(SELECT 家庭住址 FROM studen
t_info WHERE 学号=sid); ELSE RETURN(''ERROR''); END CASE; END@@ DELIM
ITER ;SELECT f_stu(''0001'',1); DROP FUNCTION f_stu;实验十一1.（1）DELIMI
TER @@CREATE PROCEDURE c_name(IN name varCHAR(50)) BEGIN SELECT 课
程名称,count() 不及格人数 FROM grade g,curriculum c WHERE g.课程编号=c.课程编号
and 课程名称=name and 分数>=90; END @@（2）DELIMITER ;CALL c_name(''C语言程序设
计'');2.（1）DELIMITER @@CREATE PROCEDURE c_proc(IN cno CHAR(4),OUT c
name varchar(50),OUT cxf int) BEGIN SELECT 课程名称,学分 INTO cname,cx
f FROM curriculum WHERE 课程编号=cno; END@@（2）DELIMITER ;CALL c_proc(
''0002'',@name,@xf);（3）SELECT @name,@xf;3.DELIMITER @@CREATE PROCED
URE currAdd(IN cid char(4),IN cname VARCHAR(50),IN credit int) BE
GIN INSERT INTO curriculum VALUES(cid,cname,credit); END@@ DELIM
ITER ;CALL currAdd(''0006'',''Java程序设计'',''3'');SELECT FROM curriculu
m WHERE 课程编号=''0006'';4.DELIMITER @@CREATE PROCEDURE comp(IN cid1 c
har(4),IN cid2 char(4),OUT result int) BEGIN DECLARE max1,max2 in
t; SET max1=(SELECT MAX(分数) FROM grade WHERE 课程编号=cid1); SET max2
=(SELECT MAX(分数) FROM grade WHERE 课程编号=cid2); IF max1>max2 THEN S
ET result=0; ELSE SET result=1; END IF; END@@ CALL comp(''0001'',''
0002'',@result); SELECT @result;实验十二2.DELIMITER @@CREATE FUNCTION
count_sch()RETURNS INTBEGIN RETURN (SELECT COUNT() FROM sch);END
@@DELIMITER ;SELECT count_sch();3.DELIMITER @@CREATE PROCEDURE ad
d_id(OUT count INT,OUT sum INT)BEGIN DECLARE i INT; DECLARE it
mp INT; DECLARE cur_id CURSOR FOR SELECT id FROM sch; SELECT co
unt_sch() INTO count; SET i=1; SET sum=0; OPEN cur_id; WHILE
i<=count DO FETCH cur_id INTO itmp; SET sum=sum+itmp; SET i=
i+1; END WHILE;CLOSE cur_id;END @@CALL add_id(@x,@y);select @x,@
y;4. DELIMITER @@CREATE PROCEDURE cal(IN cid CHAR(4),OUT result F
LOAT) BEGIN DECLARE i FLOAT DEFAULT 0; DECLARE s FLOAT DEFAULT 0
; DECLARE score INT; DECLARE flag BOOLEAN DEFAULT TRUE; DECLARE s
_cur CURSOR FOR SELECT 分数 FROM grade WHERE 课程编号=cid; DECLARE CONT
INUE HANDLER FOR NOT FOUND SET flag=FALSE; OPEN s_cur; FETCH s_cu
r INTO score; WHILE flag DO SET s=s+1; IF score>=90 THEN SET i=i
+1; END IF; FETCH s_cur INTO score; END WHILE; SET result=i/s; EN
D@@ CALL cal(''0002'',@result);SELECT @result;实验十三1.DELIMITER @@CRE
ATE TRIGGER cno_tri AFTER UPDATE ON course FOR EACH ROW BEGIN UPD
ATE sc SET 课程编号=new.课程编号 WHERE 课程编号=old.课程编号; END@@2.DELIMITER ;U
PDATE course SET 课程编号=''0008'' WHERE 课程编号=''0002'';SELECT FROM sc
WHERE 课程编号=''0008'';3.CREATE TABLE del_course( 课程编号 char(4), 课程名称 v
archar(50));CREATE TRIGGER course_tri AFTER DELETE ON course FOR
EACH ROW INSERT INTO del_course VALUES(old.课程编号,old.课程名称); DELETE
FROM course WHERE 课程编号=''0008'';SELECT FROM del_course;4.DELIMIT
ER @@CREATE PROCEDURE tran_save() BEGIN START TRANSACTION; INSERT
INTO course VALUES(''0010'',''Python数据分析'',3); SAVEPOINT sp01; DELET
E FROM course WHERE 课程编号=''0010''; ROLLBACK TO SAVEPOINT sp01; SELE
CT FROM course WHERE 课程编号=''0010''; END@@ DELIMITER ; CALL tran_
save();5. LOCK TABLES course READ;6. LOCK TABLES sc WRITE;7. UNLO
CK TABLES;实验十四1.use mysql;2.CREATE USER newAdmin@localhost IDENTI
FIED BY ''pw1'';3.GRANT SELECT, UPDATE(分数) ON studentsdb.grade TO
''newAdmin''@''localhost'' ;4.select from studentsdb.grade;5.REVOK
E SELECT, UPDATE ON studentsdb.grade FROM ''newAdmin''@''localhost'';
6.DROP USER ''newAdmin''@''localhost'';实验十六1.CREATE DATABASE Book;2.U
SE Book;CREATE TABLE book( bno char(4) not null primary key, bnam
e char(20), author char(10), publish char(20), pubdate datetime);
INSERT INTO book VALUES(''0001'',''数据库原理'',''张小海'',''人民邮电出版社'',''2020-10-0
1''),(''0002'',''软件工程'',''李妙莎'',''高等教育出版社'',''2020-08-09''),(''0003'',''操作系统'',''
钱东升'',''人民邮电出版社'',''2021-03-06''),(''0004'',''数据结构'',''鲁明浩'',''清华大学出版社'',''2021
-05-28''),(''0005'',''编译原理'',''张悦'',''高等教育出版社'',''2020-10-30'');CREATE TABLE
reader( rno char(4) not null primary key, rname char(10));INSERT
INTO reader VALUES(''0001'',''全志忠''),(''0002'',''孙佳佳''),(''0003'',''司马静'');C
REATE TABLE borrow( borrowno int not null primary key, bno char(4
), rno char(4), borrowdate datetime);INSERT INTO borrow VALUES(1,
''0001'',''0001'',''2021-11-15''),(2,''0002'',''0001'',''2021-11-20''),(3,''00
02'',''0002'',''2021-11-30''),(4,''0003'',''0002'',''2021-12-05''),(5,''0003''
,''0001'',''2021-12-12''),(6,''0004'',''0001'',''2021-12-21'');3.alter tabl
e borrow add constraint FK_BNO foreign key(bno) references book(b
no);alter table borrow add constraint FK_RNO foreign key(rno) ref
erences reader(rno);4.select pubdate from book where author=''张小海''
and bname=''数据库原理'';5.select from borrow where bno in(select bno
from book where bname=''软件工程'');6.select from book order by publ
ish desc,pubdate asc;7.select rno 读者编号,count() 借书次数 from borrow
group by rno;8.select bno,bname,author from book where bno not in
(select bno from borrow);9.create view bookview as select bname,a
uthor,pubdate from book where publish=''人民邮电出版社'';select from boo
kview;10.create index pubdateindex on book(pubdate desc);11.creat
e table tbook( bno char(4) not null primary key, bname char(20),
author char(10), publish char(20), pubdate datetime);insert into tbook select from book where publish=''人民邮电出版社'';12.drop table tbook;13.delimiter @@create procedure bookproc(in au char(10)) begin select bname,publish,pubdate from book where author=au; end@@delimiter ;call bookproc(''张小海'');14.alter table borrow drop constraint FK_rno;15.create trigger newtrigger after delete on reader for each row delete from borrow where rno=old.rno;set sql_safe_updates=0;delete from reader where rno=''0001'';select from borrow;16.delimiter @@create function borrowcount(rid char(4)) returns int begin declare bcnt int; if exists(select from borrow where rno=rid) then set bcnt=(select count() from borrow where rno=rid); else set bcnt=-1; end if; return bcnt; end@@ delimiter ;select borrowcount(''0002'');17.