数据库原理及应用（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 GRADE
MODIFY 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_INFO
WHERE 出生日期>='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_INFO
ORDER 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 G
WHERE 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_C
AS
    SELECT S.学号,姓名,课程编号 FROM STUDENT_INFO S,GRADE G
WHERE S.学号=G.学号;

SELECT*FROM V_STU_C
WHERE 学号='0003';

CREATE VIEW V_STU_G
AS
    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_G
AS
SELECT 学号,姓名,性别 FROM STUDENT_INFO;

INSERT INTO V_STU_G(学号,姓名,性别)
    VALUES('0010','陈婷婷','女');

DELETE FROM V_STU_G WHERE 学号='0010';

UPDATE GRADE SET 分数 =87
WHERE 学号=(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 COURSE
ADD PRIMARY KEY(课号);

ALTER TABLE SC
ADD CONSTRAINT  FK_CNO FOREIGN KEY (课号) REFERENCES COURSE(课号)
ON UPDATE CASCADE;

ALTER TABLE STU
ADD CONSTRAINT UP_SNAME UNIQUE (姓名);

ALTER TABLE SC
DROP FOREIGN KEY  FK_CNO;
ALTER TABLE SC
DROP 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 int
begin
    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 decimal
begin
    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_INFO
TO 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;
DELETE
FROM GRADE;

USE STUDENTSDB;
SELECT *
FROM CURRICULUM
INTO 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;
DELETE
FROM 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;