點(diǎn)贊，收藏，慢慢看。
<一>實(shí)驗(yàn)一
CREATE DATABASE STUDENTSDB;
USE STUDENTSDB;
CREATE TABLE STUDENT_INFO(
學(xué)號 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),
學(xué)分 INT
);
CREATE TABLE GRADE(
學(xué)號 CHAR(4) NOT NULL,
課程編號 CHAR(4) NOT NULL,
分?jǐn)?shù) INT,
PRIMARY KEY(學(xué)號,課程編號)
);
INSERT INTO STUDENT_INFO VALUES('0001','張青平','男','2000-10-01','衡陽市東風(fēng)路77號');
INSERT INTO STUDENT_INFO VALUES('0002','劉東陽','男','1998-12-09','衡陽市東風(fēng)路77號');
INSERT INTO STUDENT_INFO VALUES('0003','馬曉夏','女','1995-05-12','衡陽市東風(fēng)路77號');
INSERT INTO STUDENT_INFO VALUES('0004','錢忠理','男','1994-09-23','衡陽市東風(fēng)路77號');
INSERT INTO STUDENT_INFO VALUES('0005','孫海洋','男','1995-04-03','衡陽市東風(fēng)路77號');
INSERT INTO STUDENT_INFO VALUES('0006','郭小斌','男','1997-11-10','衡陽市東風(fēng)路77號');
INSERT INTO STUDENT_INFO VALUES('0007','肖月玲','女','1996-12-07','衡陽市東風(fēng)路77號');
INSERT INTO STUDENT_INFO VALUES('0008','張玲瓏','女','1997-12-24','衡陽市東風(fēng)路77號');
INSERT INTO CURRICULUM VALUES('0001','計算機(jī)應(yīng)用基礎(chǔ)',2);
INSERT INTO CURRICULUM VALUES('0002','C語言課程設(shè)計',2);
INSERT INTO CURRICULUM VALUES('0003','數(shù)據(jù)庫原理及應(yīng)用',2);
INSERT INTO CURRICULUM VALUES('0004','英語',4);
INSERT INTO CURRICULUM VALUES('0005','高等數(shù)學(xué)',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 分?jǐn)?shù) 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 學(xué)號 ='0004';
UPDATE STU SET 家庭住址='濱江市新建路96號'WHERE 學(xué)號='0002';
ALTER TABLE STU
DROP COLUMN 備注;
DROP TABLE STU;
DROP DATABASE STUDB;
實(shí)驗(yàn)二
USE STUDENTSDB;
SELECT 學(xué)號,姓名,出生日期 FROM STUDENT_INFO;
SELECT 姓名,家庭住址 FROM STUDENT_INFO WHERE 學(xué)號 ='0002';
SELECT 姓名,出生日期 FROM STUDENT_INFO
WHERE 出生日期>='1996-01-01' AND 性別='女';
SELECT * FROM GRADE WHERE 分?jǐn)?shù) BETWEEN 70 AND 80;
SELECT AVG(分?jǐn)?shù))平均分 FROM GRADE WHERE 課程編號 ='0002';
SELECT COUNT(*)選課人數(shù),COUNT(分?jǐn)?shù)) 有成績?nèi)藬?shù) FROM GRADE
WHERE 課程編號 ='0003';
SELECT 姓名,出生日期 FROM STUDENT_INFO ORDER BY 出生日期 DESC;
SELECT 學(xué)號,姓名 FROM STUDENT_INFO WHERE 姓名 LIKE '張%';
SELECT 學(xué)號,姓名,性別,出生日期,家庭住址 FROM STUDENT_INFO
ORDER BY 性別 ASC,學(xué)號 DESC;
SELECT 學(xué)號,AVG(分?jǐn)?shù)) 平均成績 FROM GRADE GROUP BY 學(xué)號;
SELECT 學(xué)號,姓名 FROM STUDENT_INFO WHERE 姓名 LIKE '劉%'
UNION SELECT 學(xué)號,姓名 FROM STUDENT_INFO WHERE 姓名 LIKE '張%';
SELECT 姓名,出生日期 FROM STUDENT_INFO
WHERE 性別=(SELECT 性別 FROM STUDENT_INFO WHERE 姓名 ='劉東陽');
SELECT 學(xué)號,姓名,性別 FROM STUDENT_INFO
WHERE 學(xué)號 IN(SELECT 學(xué)號 FROM GRADE
WHERE 課程編號 IN('0002','0005'));
SELECT 課程編號,分?jǐn)?shù) FROM GRADE
WHERE 學(xué)號='0001'AND 分?jǐn)?shù)> ANY(SELECT 分?jǐn)?shù) FROM GRADE
WHERE 學(xué)號 ='0002');
SELECT 課程編號,分?jǐn)?shù) FROM GRADE
WHERE 學(xué)號 ='0001'AND 分?jǐn)?shù)> ALL(SELECT 分?jǐn)?shù) FROM GRADE
WHERE 學(xué)號 ='0002');
SELECT S.學(xué)號,姓名,分?jǐn)?shù) FROM STUDENT_INFO S,GRADE G
WHERE S.學(xué)號=G.學(xué)號 AND 分?jǐn)?shù) BETWEEN 80 AND 90;
SELECT S.學(xué)號,姓名,分?jǐn)?shù) FROM STUDENT_INFO S INNER JOIN GRADE G
ON S.學(xué)號 =G.學(xué)號 INNER JOIN CURRICULUM C ON G.課程編號 =C.課程編號
WHERE 課程名稱 ='數(shù)據(jù)庫原理及應(yīng)用';
SELECT S.學(xué)號,姓名,MAX(分?jǐn)?shù)) 最高成績
FROM STUDENT_INFO S,GRADE G
WHERE S.學(xué)號 =G.學(xué)號
GROUP BY S.學(xué)號;
SELECT S.學(xué)號,姓名,SUM(分?jǐn)?shù)) 總成績
FROM STUDENT_INFO S LEFT OUTER JOIN GRADE G ON S.學(xué)號 =G.學(xué)號
GROUP BY S.學(xué)號;
INSERT INTO GRADE VALUES('0004','0006',76);
SELECT G.課程編號,課程名稱,COUNT(*) 選修人數(shù)
FROM CURRICULUM C RIGHT OUTER JOIN GRADE G ON G.課程編號 =C.課程編號
GROUP BY G.課程編號;
實(shí)驗(yàn)三
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(分?jǐn)?shù));
CREATE INDEX GRADE_SID_CID_IDX ON GRADE(學(xué)號,課程編號);
SHOW INDEX FROM GRADE;
DROP INDEX GRADE_IDX ON GRADE;
SHOW INDEX FROM GRADE;
CREATE VIEW V_STU_C
AS
SELECT S.學(xué)號,姓名,課程編號 FROM STUDENT_INFO S,GRADE G
WHERE S.學(xué)號=G.學(xué)號;
SELECT*FROM V_STU_C
WHERE 學(xué)號='0003';
CREATE VIEW V_STU_G
AS
SELECT S.學(xué)號,姓名,課程名稱,分?jǐn)?shù)
FROM STUDENT_INFO S,GRADE G,CURRICULUM C
WHERE S.學(xué)號=G.學(xué)號 AND G.課程編號 =C.課程編號;
SELECT AVG(分?jǐn)?shù)) FROM V_STU_G WHERE 學(xué)號='0001';
ALTER VIEW V_STU_G
AS
SELECT 學(xué)號,姓名,性別 FROM STUDENT_INFO;
INSERT INTO V_STU_G(學(xué)號,姓名,性別)
VALUES('0010','陳婷婷','女');
DELETE FROM V_STU_G WHERE 學(xué)號='0010';
UPDATE GRADE SET 分?jǐn)?shù) =87
WHERE 學(xué)號=(SELECT 學(xué)號 FROM V_STU_G WHERE 姓名='張青平')AND
課程編號=(SELECT 課程編號 FROM CURRICULUM WHERE 課程名稱='高等數(shù)學(xué)');
DROP VIEW V_STU_C,V_STU_G;
實(shí)驗(yàn)四
CREATE DATABASE STUDENTS;
USE STUDENTS;
CREATE TABLE STU(
學(xué)號 CHAR(4) NOT NULL PRIMARY KEY,
姓名 CHAR(8),
性別 CHAR(2),
出生日期 DATE
);
CREATE TABLE SC(
學(xué)號 CHAR(4) NOT NULL,
課號 CHAR(4) NOT NULL,
成績 DECIMAL(5,2) CHECK(成績 BETWEEN 0 AND 100 ),
PRIMARY KEY(學(xué)號,課號),
CONSTRAINT FK_SNO FOREIGN KEY(學(xué)號) REFERENCES STU(學(xué)號)
);
CREATE TABLE COURSE(
課號 CHAR(4) NOT NULL,
課名 CHAR(20),
學(xué)分 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;
實(shí)驗(yàn)五
delimiter @@
create procedure stu_grade()
begin
select 姓名,課程名稱,分?jǐn)?shù) from student_info s,grade g,curriculum c
where s.學(xué)號=g.學(xué)號 and g.課程編號 =c.課程編號 and s.學(xué)號='0001';
end @@
delimiter;
call stu_grade();
delimiter @@
create procedure stu_name(in name char(8))
begin
select 姓名,max(分?jǐn)?shù)) 最高分,min(分?jǐn)?shù)) 最低分,avg(分?jǐn)?shù)) 平均分
from student_info s,grade g,curriculum c
where s.學(xué)號 =g.學(xué)號 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語言程序設(shè)計');
delimiter @@
create function avg_func(cname varchar(50))
returns decimal
begin
declare v_avg decimal;
declare avg_cur cursor for select avg(分?jǐn)?shù)) 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語言程序設(shè)計') 課程平均分;
drop function avg_func;
實(shí)驗(yàn)六
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;
實(shí)驗(yàn)七文章來源：http://www.zghlxwxcb.cn/news/detail-443051.html
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;
實(shí)驗(yàn)八文章來源地址http://www.zghlxwxcb.cn/news/detail-443051.html
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); /*#項(xiàng)請給出具體值，后面同*/
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;
到了這里，關(guān)于數(shù)據(jù)庫原理及應(yīng)用（MySQL版）MySQL實(shí)驗(yàn)指導(dǎo)參考答案（實(shí)驗(yàn)一到實(shí)驗(yàn)八）的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
