数据库原理及应用（MySQL版）MySQL实验指导参考答案（实验一到实验八）

点赞，收藏，慢慢看。实验一CREATE DATABASE STUDENTSDB;USE STUDENTSDB;CREATE TABLE STUDENT_INFO(    学号 CHAR(4) NOT NULL PRIMARY KEY,    姓名 CHAR(8) NOT NULL,    性别 CHAR(2),    出生日期 DATE,    家庭住址 VARCHAR(50));CREATE TABLE CURRICULUM(    课程编号 CHAR(4) NOT NULL PRIMARY KEY,    课程名称 VARCHAR(50),    学分 INT);CREATE TABLE GRADE(    学号 CHAR(4) NOT NULL,    课程编号 CHAR(4) NOT NULL,    分数 INT,    PRIMARY KEY(学号,课程编号));INSERT INTO STUDENT_INFO VALUES('0001','张青平','男','2000-10-01','衡阳市东风路77号');INSERT INTO STUDENT_INFO VALUES('0002','刘东阳','男','1998-12-09','衡阳市东风路77号');INSERT INTO STUDENT_INFO VALUES('0003','马晓夏','女','1995-05-12','衡阳市东风路77号');INSERT INTO STUDENT_INFO VALUES('0004','钱忠理','男','1994-09-23','衡阳市东风路77号');INSERT INTO STUDENT_INFO VALUES('0005','孙海洋','男','1995-04-03','衡阳市东风路77号');INSERT INTO STUDENT_INFO VALUES('0006','郭小斌','男','1997-11-10','衡阳市东风路77号');INSERT INTO STUDENT_INFO VALUES('0007','肖月玲','女','1996-12-07','衡阳市东风路77号');INSERT INTO STUDENT_INFO VALUES('0008','张玲珑','女','1997-12-24','衡阳市东风路77号');INSERT INTO CURRICULUM VALUES('0001','计算机应用基础',2);INSERT INTO CURRICULUM VALUES('0002','C语言课程设计',2);INSERT INTO CURRICULUM VALUES('0003','数据库原理及应用',2);INSERT INTO CURRICULUM VALUES('0004','英语',4);INSERT INTO CURRICULUM VALUES('0005','高等数学',4);INSERT INTO GRADE VALUES('0001','0001',80);INSERT INTO GRADE VALUES('0001','0002',91);INSERT INTO GRADE VALUES('0001','0003',88);INSERT INTO GRADE VALUES('0001','0004',85);INSERT INTO GRADE VALUES('0001','0005',77);INSERT INTO GRADE VALUES('0002','0001',73);INSERT INTO GRADE VALUES('0002','0002',68);INSERT INTO GRADE VALUES('0002','0003',80);INSERT INTO GRADE VALUES('0002','0004',79);INSERT INTO GRADE VALUES('0002','0005',73);INSERT INTO GRADE VALUES('0003','0001',84);INSERT INTO GRADE VALUES('0003','0002',92);INSERT INTO GRADE VALUES('0003','0003',81);INSERT INTO GRADE VALUES('0003','0004',82);INSERT INTO GRADE VALUES('0003','0005',75);ALTER TABLE CURRICULUM    MODIFY COLUMN 课程名称 VARCHAR(50) NULL;ALTER TABLE GRADEMODIFY COLUMN 分数 DECIMAL(5,2);ALTER TABLE STUDENT_INFO    ADD 备注 VARCHAR(50);CREATE DATABASE STUDB;USE STUDB;CREATE TABLE STU    AS SELECT * FROM STUDENTSDB.STUDENT_INFO;SET SQL_SAFE_UPDATES=0;DELETE FROM STU WHERE 学号 ='0004';UPDATE STU SET 家庭住址='滨江市新建路96号'WHERE 学号='0002';ALTER TABLE STU    DROP COLUMN 备注;DROP TABLE STU;DROP DATABASE STUDB;

实验二

USE STUDENTSDB;SELECT 学号,姓名,出生日期 FROM STUDENT_INFO;SELECT 姓名,家庭住址 FROM STUDENT_INFO WHERE 学号 ='0002';SELECT 姓名,出生日期 FROM STUDENT_INFOWHERE 出生日期>='1996-01-01' AND 性别='女';SELECT * FROM GRADE WHERE 分数 BETWEEN 70 AND 80;SELECT AVG(分数)平均分 FROM GRADE WHERE 课程编号 ='0002';SELECT COUNT(*)选课人数,COUNT(分数) 有成绩人数 FROM GRADE    WHERE 课程编号 ='0003';SELECT 姓名,出生日期 FROM STUDENT_INFO ORDER BY 出生日期 DESC;SELECT 学号,姓名 FROM STUDENT_INFO WHERE 姓名 LIKE '张%';SELECT 学号,姓名,性别,出生日期,家庭住址 FROM STUDENT_INFOORDER BY 性别 ASC,学号 DESC;SELECT 学号,AVG(分数) 平均成绩 FROM GRADE GROUP BY  学号;SELECT 学号,姓名 FROM STUDENT_INFO WHERE 姓名 LIKE '刘%'UNION SELECT 学号,姓名 FROM STUDENT_INFO WHERE 姓名 LIKE '张%';SELECT 姓名,出生日期 FROM STUDENT_INFO    WHERE 性别=(SELECT 性别 FROM STUDENT_INFO WHERE 姓名 ='刘东阳');SELECT 学号,姓名,性别 FROM STUDENT_INFO    WHERE 学号 IN(SELECT 学号 FROM GRADE        WHERE 课程编号 IN('0002','0005'));SELECT 课程编号,分数 FROM GRADE    WHERE 学号='0001'AND 分数> ANY(SELECT 分数 FROM GRADE        WHERE 学号 ='0002');SELECT 课程编号,分数 FROM GRADE    WHERE 学号 ='0001'AND 分数> ALL(SELECT 分数 FROM GRADE        WHERE 学号 ='0002');SELECT S.学号,姓名,分数 FROM STUDENT_INFO S,GRADE G    WHERE S.学号=G.学号 AND 分数 BETWEEN  80 AND 90;SELECT S.学号,姓名,分数 FROM STUDENT_INFO S INNER JOIN GRADE G    ON S.学号 =G.学号 INNER JOIN CURRICULUM C ON G.课程编号 =C.课程编号WHERE 课程名称 ='数据库原理及应用';SELECT S.学号,姓名,MAX(分数) 最高成绩FROM STUDENT_INFO S,GRADE GWHERE S.学号 =G.学号GROUP BY S.学号;SELECT S.学号,姓名,SUM(分数) 总成绩    FROM STUDENT_INFO S LEFT OUTER JOIN GRADE G ON S.学号 =G.学号GROUP BY S.学号;INSERT INTO GRADE VALUES('0004','0006',76);SELECT G.课程编号,课程名称,COUNT(*) 选修人数    FROM CURRICULUM C RIGHT OUTER JOIN GRADE G ON G.课程编号 =C.课程编号GROUP BY G.课程编号;

实验三

USE STUDENTSDB;ALTER TABLE STUDENT_INFO DROP PRIMARY KEY;ALTER TABLE CURRICULUM DROP PRIMARY KEY;ALTER TABLE GRADE DROP PRIMARY KEY;CREATE UNIQUE INDEX CNO_IDX ON CURRICULUM(课程编号);CREATE INDEX GRADE_IDX ON GRADE(分数);CREATE INDEX GRADE_SID_CID_IDX ON GRADE(学号,课程编号);SHOW INDEX FROM GRADE;DROP INDEX GRADE_IDX ON GRADE;SHOW INDEX FROM GRADE;CREATE VIEW V_STU_CAS    SELECT S.学号,姓名,课程编号 FROM STUDENT_INFO S,GRADE GWHERE S.学号=G.学号;SELECT*FROM V_STU_CWHERE 学号='0003';CREATE VIEW V_STU_GAS    SELECT S.学号,姓名,课程名称,分数    FROM STUDENT_INFO S,GRADE G,CURRICULUM C    WHERE S.学号=G.学号 AND G.课程编号 =C.课程编号;SELECT AVG(分数) FROM V_STU_G WHERE 学号='0001';ALTER VIEW V_STU_GASSELECT 学号,姓名,性别 FROM STUDENT_INFO;INSERT INTO V_STU_G(学号,姓名,性别)    VALUES('0010','陈婷婷','女');DELETE FROM V_STU_G WHERE 学号='0010';UPDATE GRADE SET 分数 =87WHERE 学号=(SELECT 学号 FROM V_STU_G WHERE 姓名='张青平')AND      课程编号=(SELECT 课程编号 FROM CURRICULUM WHERE 课程名称='高等数学');DROP VIEW V_STU_C,V_STU_G;实验四

CREATE DATABASE STUDENTS;USE STUDENTS;CREATE TABLE STU(    学号 CHAR(4) NOT NULL PRIMARY KEY,    姓名 CHAR(8),    性别 CHAR(2),    出生日期 DATE);CREATE TABLE SC(    学号 CHAR(4) NOT NULL,    课号 CHAR(4) NOT NULL,    成绩 DECIMAL(5,2) CHECK(成绩 BETWEEN 0 AND 100 ),    PRIMARY KEY(学号,课号),    CONSTRAINT FK_SNO FOREIGN KEY(学号) REFERENCES STU(学号));CREATE TABLE COURSE(    课号 CHAR(4) NOT NULL,    课名 CHAR(20),    学分 INT,    CONSTRAINT  UP_CNAME UNIQUE(课名));ALTER TABLE COURSEADD PRIMARY KEY(课号);ALTER TABLE SCADD CONSTRAINT  FK_CNO FOREIGN KEY (课号) REFERENCES COURSE(课号)ON UPDATE CASCADE;ALTER TABLE STUADD CONSTRAINT UP_SNAME UNIQUE (姓名);ALTER TABLE SCDROP FOREIGN KEY  FK_CNO;ALTER TABLE SCDROP FOREIGN KEY FK_SNO;ALTER TABLE STU DROP PRIMARY KEY ;ALTER TABLE COURSE DROP INDEX UP_CNAME;CREATE TABLE TEST(    DATE_TIME VARCHAR(50));CREATE TRIGGER TEST_TRG    AFTER INSERT    ON STU    FOR EACH ROW    INSERT INTO TEST VALUES(SYSDATE());INSERT INTO STU VALUES('1','MARY','F','1995-10-13');SELECT * FROM TEST;CREATE TRIGGER DEL_TRIG    AFTER DELETE    ON COURSE    FOR EACH ROW    DELETE FROM SC WHERE 课号=OLD.课号;DELETE FROM COURSE  WHERE 课号='1';SELECT * FROM SC;

实验五

delimiter @@create procedure stu_grade()begin    select 姓名,课程名称,分数 from student_info s,grade g,curriculum c    where s.学号=g.学号 and g.课程编号 =c.课程编号 and s.学号='0001';end @@delimiter;call stu_grade();delimiter @@create procedure stu_name(in name char(8))begin    select 姓名,max(分数) 最高分,min(分数) 最低分,avg(分数) 平均分    from student_info s,grade g,curriculum c    where s.学号 =g.学号 and g.课程编号 =c.课程编号 and 姓名 =name;end; @@delimiter;call stu_name('张青平');drop procedure stu_name;delimiter @@create procedure stu_g_r(in cno char(4),out num int)begin    select count(*) into num from grade where 课程编号 =cno;end; @@delimiter;call stu_g_r('0002',@num);select @num;set global log_bin_trust_function_creators=1;delimiter @@create function num_func(cname varchar(50))returns intbegin    declare num int;    select count(*) into num from grade g,curriculum c    where g.课程编号=c.课程编号 and 课程名称=cname;    return num;end;@@select num_func('c语言程序设计');delimiter @@create function avg_func(cname varchar(50))returns decimalbegin    declare v_avg decimal;    declare avg_cur cursor for select avg(分数) from grade g,curriculum c    where g.课程编号=c.课程编号 and 课程名称 =cname;    open avg_cur;    fetch avg_cur into v_avg;    close avg_cur;    return v_avg;end;@@select avg_func('c语言程序设计') 课程平均分;drop function avg_func;

实验六

CREATE USER ST_01@LOCALHOST IDENTIFIED BY '123455';USE MYSQL;SELECT * FROM USER;SET PASSWORD FOR ST_01@LOCALHOST='111111';GRANT SELECT ON TABLE STUDENTSDB.STUDENT_INFO TO ST_01@LOCALHOST;GRANT UPDATE(家庭住址) ON TABLE STUDENTSDB.STUDENT_INFOTO ST_01@LOCALHOST;GRANT ALTER ON TABLE STUDENTSDB.STUDENT_INFO TO ST_01@LOCALHOST;DELIMITER@@CREATE PROCEDURE STUDENTSDB.CN_PROC()BEGIN    DECLARE N INT;SELECT COUNT(*) INTO N FROM STUDENTSDB.STUDENT_INFO;SELECT N;END@@DELIMITER;GRANT EXECUTE ON PROCEDURE STUDENTSDB.CN_RROC TO ST_01@LOCALHOST;CALL STUDENTSDB.CN_PROC();GRANT CREATE,SELECT,INSERT,DROP ON STUDENTSDB.* TO ST_01@LOCALHOST;CREATE TABLE STUDENTSDB.ST_COPY SELECT * FROM STUDENTSDB.STUDENT_INFO;DROP TABLE STUDENTSDB.ST_COPY;REVOKE CREATE,SELECT,INSERT,DROP ON STUDENTSDB. * FROM ST_01@LOCALHOST;CREATE ROLE 'STUDENT'@'LOCALHOST';GRANT SELECT ON TABLE STUDENTSDB.STUDENT_INFO TO 'STUDENT'@'LOCALHOST';CREATE USER STU_02@LOCALHOST IDENTIFIED BY '123';GRANT 'STUDENT'@'LOCALHOST'TO STU_02@LOCALHOST;SET GLOBAL ACTIVATE_ALL_ROLES_ON_LOGIN=ON;SELECT * FROM STUDENTSDB.STUDENT_INFO;REVOKE ALL PRIVILEGES,GRANT OPTION FROM 'STUDENT'@'LOCALHOST';DROP ROLE 'STUDENT'@'LOCALHOST';DROP USER ST_01@LOCALHOST,ST_02@LOCALHOST;

实验七

CREATE DATABASE STUDENT1;CREATE DATABASE STUDENT2;USE STUDENT1;SET SQL_SAFE_UPDATES = 0;DELETEFROM GRADE;USE STUDENTSDB;SELECT *FROM CURRICULUMINTO OUTFILE 'C:/PROGRAM DATA/MYSQL/MYSQL SERVER 8.0/UPLOADS/C.TXT'    FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '“'    LINES TERMINATED BY '\r\n';USE STUDENTSDB;SET SQL_SAFE_UPDATES = 0;DELETEFROM STUDENT_INFO;

实验八

CREATE TABLE BOOK(    BOOKID    VARCHAR(20) PRIMARY KEY,    TITLE     VARCHAR(50) NOT NULL,    AUTHOR    VARCHAR(50),    PUBLISHER VARCHAR(50),    PYEAR     CHAR(4),    LANGUAGE  CHAR(1) DEFAULT 'C',    STATE     CHAR(1) DEFAULT '0');CREATE TABLE STUDENT(    ID   CHAR(6) PRIMARY KEY,    NAME VARCHAR(20) NOT NULL,    DEPT VARCHAR(20) NOT NULL);CREATE TABLE ASSISTENT(    ID   CHAR(6) PRIMARY KEY,    NAME VARCHAR(20) NOT NULL);CREATE TABLE BBOOK(    BID   VARCHAR(20) NOT NULL,    STDID CHAR(6)     NOT NULL,    BDATE DATE        NOT NULL,    CONSTRAINT FK_BBOOK_BID        FOREIGN KEY (BID) REFERENCES BOOK (BOOKID),    CONSTRAINT FK_BBOOK_STDID        FOREIGN KEY (STDID) REFERENCES STUDENT (ID));CREATE TABLE RBOOK(    BOOKID VARCHAR(20) NOT NULL,    STDID  CHAR(6)     NOT NULL,    RDATE  DATE        NOT NULL,    CONSTRAINT FK_RBOOK_BOOKID        FOREIGN KEY (BOOKID) REFERENCES BOOK (BOOKID),    CONSTRAINT FK_RBOOK_STDID        FOREIGN KEY (STDID) REFERENCES STUDENT (ID));CREATE TABLE LEND(    STDID  CHAR(6)     NOT NULL,    ASTID  CHAR(6)     NOT NULL,    BOOKID VARCHAR(20) NOT NULL,    LDATE  DATE        NOT NULL,    CONSTRAINT FK_LEND_ASTID        FOREIGN KEY (STDID) REFERENCES STUDENT (ID),    CONSTRAINT FK_LEND_ASTID        FOREIGN KEY (ASTID) REFERENCES ASSISTENT (ID),    CONSTRAINT FK_LEND_BOOKID        FOREIGN KEY (BOOKID) REFERENCES BOOK (BOOKID));CREATE TABLE RETURNN(    STDID  CHAR(6)     NOT NULL,    ASTID  CHAR(6)     NOT NULL,    BOOKID VARCHAR(20) NOT NULL,    RDATE  DATE        NOT NULL,    CONSTRAINT FK_RETURN_STDID        FOREIGN KEY (STDID) REFERENCES STUDENT (ID),    CONSTRAINT FK_RETURN_ASTID        FOREIGN KEY (ASTID) REFERENCES ASSISTENT (ID),    CONSTRAINT FK_RETURN_BOOKID        FOREIGN KEY (BOOKID) REFERENCES BOOK (BOOKID));INSERT INTO STUDENT(ID, NAME, DEPT)VALUES (#STDID,#NAME,#DEPT);         /*#项请给出具体值，后面同*/           DELETE FROM STUDENT WHERE ID=#ID;                  UPDATE STUDENT SET NAME =#NAME,DEPT = #DEPT                  WHERE ID=#ID;                  INSERT INTO BOOK                  VALUES (#BOOKID,#TITLE,#AUTHOR,#PUBLISHER,#PYEAR,#LANGUAGE);                  DELETE FROM BOOK WHERE BOOKID = #BOOKID;                  UPDATE BOOK SET TITLE =#TILE,AUTHOR=#AUTHOR,                  PUBLISHER= #PUBLISHER,PYEAR=#PYEAR,LANGUAGE =#LANGUAGE                  WHERE BOOKID =#BOOKID;                  START TRANSACTION;                  INSERT INTO LEND(STDID, ASTID, BOOKID, LDATE)                  VALUES (#STDID,#ASTID,#BOOKID,#LDATE);                  UPDATE BOOK SET STATE = '2'                  WHERE BOOKID = #BOOKID;                  COMMIT;                  START TRANSACTION;                  INSERT INTO RETURN (STDID, ASTID, BOOKID, RDATE)                  VALUES (#STDID,#ASTID,#BOOKID,#RDATE);                  UPDATE BOOK SET STATE = '0'                  WHERE BOOKID =#BOOKID;                  COMMIT;