点赞，收藏，慢慢看。
<一>实验一
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;
