数据库报告存档

前段时间把数据库学完了，顺便做了大三学长的数据库报告，现在存一下，自己大三的时候就不用写了qwq

用的MYSQL

-- 1.创建S
CREATE TABLE S (
	sclass INT,
	sno INT,
	sname VARCHAR(64) UNIQUE,
	ssex VARCHAR(32),
	sage INT,
	Sdept VARCHAR(32),
	PRIMARY KEY(sclass, sno)
);


INSERT INTO S (sclass, sno, sname, ssex, sage, Sdept) VALUES (1, 1, '李勇', '男', 20, 'IA');
INSERT INTO S (sclass, sno, sname, ssex, sage, Sdept) VALUES (1, 2, '刘晨', '女', 19, 'IA');
INSERT INTO S (sclass, sno, sname, ssex, sage, Sdept) VALUES (1, 3, "刘朋", "男", 20, 'IA');
INSERT INTO S (sclass, sno, sname, ssex, sage, Sdept) VALUES (2, 1, '王敏', '女', 18, 'MA');
INSERT INTO S (sclass, sno, sname, ssex, sage, Sdept) VALUES (2, 2, '张峰', '男', 19, 'MA');
INSERT INTO S (sclass, sno, sname, ssex, sage, Sdept) VALUES (2, 3, '李敏', '男', 20, 'MA');

SELECT * FROM S;

DROP TABLE S;


-- 2.创建C
CREATE TABLE C (
	cno INT PRIMARY KEY,
	cname VARCHAR(64) NOT NULL,
	cpno INT,
	ccredit INT
);

INSERT INTO C (cno, cname, cpno, ccredit) VALUES (1, '数据库', 5, 4);
INSERT INTO C (cno, cname, ccredit) VALUES (2, '数学', 2);
INSERT INTO C (cno, cname, cpno, ccredit) VALUES (3, '信息系统', 1, 4);
INSERT INTO C (cno, cname, cpno, ccredit) VALUES (4, '操作系统', 6, 3);
INSERT INTO C (cno, cname, cpno, ccredit) VALUES (5, '数据结构', 7, 4);
INSERT INTO C (cno, cname, ccredit) VALUES (6, '数据处理', 2);
INSERT INTO C (cno, cname, cpno, ccredit) VALUES (7, 'PASCAL语言', 6, 4);

SELECT * FROM C;

DROP TABLE C;


-- 3.创建SC
CREATE TABLE SC (
	sclass INT,
	sno INT,
	cno INT,
	grade INT,
	PRIMARY KEY(sclass, sno, cno),
	FOREIGN KEY(sclass, sno) REFERENCES S(sclass, sno),
	FOREIGN KEY(cno) REFERENCES C(cno)
);

INSERT INTO Sc (sclass, sno, cno, grade) VALUES (1, 1, 1, 92);
INSERT INTO Sc (sclass, sno, cno, grade) VALUES (1, 1, 2, 85);
INSERT INTO Sc (sclass, sno, cno, grade) VALUES (1, 1, 3, 88);
INSERT INTO Sc (sclass, sno, cno, grade) VALUES (1, 2, 2, 90);
INSERT INTO Sc (sclass, sno, cno, grade) VALUES (1, 2, 3, 80);
INSERT INTO Sc (sclass, sno, cno, grade) VALUES (2, 1, 1, 75);
INSERT INTO Sc (sclass, sno, cno, grade) VALUES (2, 1, 2, 92);
INSERT INTO Sc (sclass, sno, cno, grade) VALUES (2, 2, 2, 87);
INSERT INTO Sc (sclass, sno, cno, grade) VALUES (2, 2, 3, 89);
INSERT INTO Sc (sclass, sno, cno, grade) VALUES (2, 3, 1, 90);

SELECT * FROM SC;

DROP TABLE SC;

-- （一）

-- 4. 查询所有信息

-- 隐式内连接查询

SELECT s.sclass, s.sno, sname, ssex, sage, sdept, c.cno, cname, cpno, ccredit, grade  
FROM S
INNER JOIN SC ON S.sclass = SC.sclass AND S.sno = SC.sno
INNER JOIN C ON C.cno = SC.cno; 

-- 5.查询1班学生学号及姓名

SELECT sno, sname 
FROM S 
WHERE sclass = 1;

-- 6.查询 刘晨 出生年

SELECT 2021 - sage 
FROM S 
WHERE sname = '刘晨';

-- 7.查询姓刘的学生的详细情况（包括学生表，选课表及课程表的全部信息）

SELECT s.sclass, s.sno, sname, ssex, sage, sdept, c.cno, cname, cpno, ccredit, grade
FROM S 
LEFT OUTER JOIN SC ON S.sclass = SC.sclass AND S.sno = SC.sno
LEFT OUTER JOIN C ON C.cno = SC.cno
WHERE sname LIKE '刘%';

SELECT s.sclass, s.sno, sname, ssex, sage, sdept, c.cno, cname, cpno, ccredit, grade
FROM S 
INNER JOIN SC ON S.sclass = SC.sclass AND S.sno = SC.sno
INNER JOIN C ON C.cno = SC.cno
WHERE sname LIKE '刘%';

 -- 8.查询选修了1号课的学生姓名、性别、成绩
 
 SELECT sname, ssex, grade 
 FROM S, SC 
 WHERE S.sclass = SC.sclass AND S.sno = SC.sno AND SC.cno = 1;
 
 -- 9.查询没有先行课课程的课程号和课程名
 
 SELECT cno, cname 
 FROM C 
 WHERE cpno IS NULL;
 
 -- 10.查询2班的所有女生情况
 
 SELECT * 
 FROM S 
 WHERE sclass = 2 AND ssex = '女';
 
 -- 11.查询学分2~3之间的课程号及课程名
 
 SELECT cno, cname 
 FROM C 
 WHERE ccredit BETWEEN 2 AND 3;
 
 -- 12.查询选修1号课的学生的班号，学号，姓名，课程名，及成绩，要求按照成绩递减排序输出
 
 SELECT S.sclass, S.sno, sname, C.cname, grade 
 FROM S, C, SC 
 WHERE S.sclass = SC.sclass AND S.sno = SC.sno AND C.cno = SC.cno AND C.cno = 1 
 ORDER BY SC.grade DESC;
 
 -- 13.查询2班至少选修一门其先行课为1号课的学生的班号，学号，姓名，性别，系，课程号及成绩 
 
 SELECT S.sclass, S.sno, S.sname, S.ssex, S.Sdept, C.cname, SC.grade 
 FROM S
 INNER JOIN SC ON sc.sclass = s.sclass AND sc.sno = s.sno
 INNER JOIN c ON c.cno = sc.cno
 WHERE s.sclass = 2 AND s.sno IN (SELECT sno
				FROM sc
				INNER JOIN c ON c.cno = sc.cno
				WHERE sclass = 2 AND cpno = 1);
 
 -- 14.查询2号课成绩最高的学生班号，学号，姓名
 
 SELECT S.sclass, S.sno, sname 
 FROM S, SC 
 WHERE S.sclass = SC.sclass AND S.sno = SC.sno AND SC.cno = 2 
 ORDER BY grade DESC LIMIT 1;
 
 -- 15.查询1班2号课成绩最低的学生班号，学号
 
 SELECT sclass, sno 
 FROM SC 
 WHERE sclass = 1 AND cno = 2 
 ORDER BY grade ASC 
 LIMIT 1;
 
 -- 16.查询选修2号课且成绩不是最低的同学班号，学号
 
 SELECT sclass, sno 
 FROM SC 
 WHERE SC.cno = 2 AND grade > (SELECT MIN(grade) 
				FROM SC 
				WHERE SC.cno = 2);
 
 -- 17.	查询包含2班1号同学所选全部课程的同学的班号、学号
 
 SELECT sclass, sno
 FROM s scx
 WHERE NOT EXISTS(SELECT sclass, sno
		  FROM sc scy
		  WHERE sclass = 2 AND sno = 1 AND NOT EXISTS (SELECT sclass, sno
							       FROM sc scz
							       WHERE scx.sclass = scz.sclass AND scx.sno = scz.sno AND scy.cno = scz.cno));


-- 18.查询选修每门课程的课程号及人数

SELECT cno, COUNT(cno) 
FROM SC 
GROUP BY cno;

-- 19.查询选修三门课的同学班号、学号、姓名、课程名及成绩
-- 两层select嵌套

SELECT S.sclass, S.sno, S.sname, cname, grade 
FROM C, S, SC, (SELECT S.sname AS sname, COUNT(cno) AS num 
		FROM S, SC 
		WHERE S.sclass = SC.sclass AND S.sno = SC.sno 
		GROUP BY S.sname) AS N 
WHERE num = 3 AND N.sname = S.sname AND S.sno = SC.sno AND SC.sclass = S.sclass AND SC.cno = C.cno;

-- 20.实现上述数据库的备份和恢复功能


-- 21.练习SQL SERVER 数据库备份与恢复技术方法。抓屏显示数据库备份和恢复的步骤过程。


-- (二)


-- 1.查询选了1号课且选了2号课的学生的班号、学号

SELECT sc1.sclass, sc1.sno
FROM sc sc1, sc sc2
WHERE sc1.sclass = sc2.sclass AND sc1.sno = sc2.sno AND sc1.cno = 1 AND sc2.cno = 2;

-- 2.查询选了1号课但不选2号课的学生的班号、学号

SELECT sclass, sno
FROM sc
WHERE cno = 1 AND (sclass, sno) NOT IN (SELECT sclass, sno
					FROM sc
					WHERE cno = 2);

-- 3.查询1班平均分在85分以上的同学班号、学号、姓名、性别、系、各科课程号及成绩

SELECT S.sclass, S.sno, S.sname, ssex, Sdept, SC.cno, grade
FROM S, SC, (SELECT sname, AVG(grade) _avg 
		FROM S, SC 
		WHERE S.sclass = SC.sclass AND S.sno = SC.sno 
		GROUP BY sname) average
WHERE S.sclass = SC.sclass AND S.sno = SC.sno AND S.sname = average.sname AND _avg > 85 AND S.sclass = 1; 

-- 4.查询至少选了1班2号同学所选课的所有班号、学号及同学姓名


 SELECT sclass, sno
 FROM s scx
 WHERE NOT EXISTS(SELECT sclass, sno
		  FROM sc scy
		  WHERE sclass = 2 AND sno = 1 AND NOT EXISTS (SELECT sclass, sno
							       FROM sc scz
							       WHERE scx.sclass = scz.sclass AND scx.sno = scz.sno AND scy.cno = scz.cno));

-- 5.查询不选1号课的学生班号及学号

SELECT S.sclass, S.sno
FROM S 
WHERE NOT EXISTS (SELECT sname
		FROM SC
		WHERE S.sclass = SC.sclass AND S.sno = SC.sno AND cno = 1);


-- 6.查询选2号课的学生名字及相应2号课成绩，按成绩从高到低排序

SELECT sname, grade 
FROM S, SC
WHERE cno = 2 AND S.sclass = SC.sclass AND S.sno = SC.sno
ORDER BY grade DESC;

-- 7.统计学生选修课程的班号、学号及总学分

SELECT S.sclass, S.sno, SUM(ccredit)
FROM SC, C, S
WHERE SC.cno = C.cno AND S.sclass = SC.sclass AND S.sno = SC.sno
GROUP BY sname;

-- 8.计1班选修3号课的学号及平均分

SELECT S.sno, AVG(grade)
FROM S, SC, (SELECT sname
		FROM S, SC
		WHERE S.sclass = 1 AND S.sclass = SC.sclass AND S.sno = SC.sno AND cno = 3) N
WHERE S.sclass = SC.sclass AND S.sno = SC.sno AND S.sname = N.sname
GROUP BY S.sname;

-- 9.把个人信息及选课信息插入到Student和SC 表及新增加一门“无机化学”课程信息

CREATE TABLE stu(
	sclass INT,
	sno INT,
	sname VARCHAR(64),
	ssex VARCHAR(32),
	sage INT,
	sdept VARCHAR(32),
	cno INT,
	grade INT,
	PRIMARY KEY(sclass, sno, cno)
);

SELECT * FROM stu;

INSERT INTO stu
SELECT s.*, sc.cno, sc.grade
FROM s, sc
WHERE s.sclass = sc.sclass AND s.sno = sc.sno;

INSERT INTO c VALUES(8, '无机化学', NULL, 2);

-- 10.删除选修3号课的所有选课信息并显示删除后的结果

DELETE FROM stu WHERE cno = 3;

-- 11.把选修1号课的所有男同学年龄增加1岁并显示最终学生Student信息

UPDATE stu SET sage = sage + 1 WHERE ssex = '男';

-- 12.把每个选课人的学号、班号及平均成绩插入到一个新表中。

CREATE TABLE stu_new(
	sclass INT,
	sno INT,
	avg_grade INT,
	PRIMARY KEY(sclass, sno)
);

SELECT * FROM stu_new;

INSERT INTO stu_new
SELECT sclass, sno, AVG(grade)
FROM stu
GROUP BY sname;



-- （二）视图SQL语言功能


-- 1.使用企业管理器创建视图：在ST库中以“student”表为基础，建立信息系学生的视图V_IS_Student

DROP VIEW V_IS_Student;

CREATE VIEW V_IS_Student AS
SELECT *
FROM stu
WHERE sdept = 'IA';

SELECT * FROM v_IS_Student;

-- 2.使用SQL语句创建视图：

-- ①建立一个每个学生的学号、班号、姓名、选修的课名及成绩的视图     S_C_GRADE；

CREATE VIEW s_c_crade AS
SELECT sclass, sno, sname, cno, grade
FROM stu;

SELECT * FROM s_c_crade;

-- ②建立信息系建立信息系选修了1号课程且成绩在90分以上的学生的视 图V_IS_Score

DROP VIEW v_ia_score;

CREATE VIEW v_ia_score AS
SELECT * 
FROM stu
WHERE sname IN (SELECT sname 
		FROM stu
		WHERE cno = 1 AND grade > 90) AND sdept = 'IA';
		
SELECT * FROM v_ia_score;

-- ③将各系学生人数，平均年龄定义为视图V_NUM_AVG。

DROP VIEW v_num_avg;

CREATE VIEW v_num_avg AS
SELECT COUNT(sname), AVG(sage)
FROM s
GROUP BY sdept;

SELECT * FROM v_num_avg;

-- 3.查询以上所建的视图结果

SELECT * FROM v_IS_Student;
SELECT * FROM s_c_crade;
SELECT * FROM v_ia_score;
SELECT * FROM v_num_avg;

-- 4.查询选修了1号课程的信息系学生

SELECT sclass, sno, sname
FROM stu
WHERE cno = 1 AND sdept = 'IA';

-- 5.在信息系学生的视图中找出年龄小于20岁的学生

SELECT sclass, sno, sname FROM v_is_student WHERE sage < 20 GROUP BY sname;

-- 6.将信息系学生视图V_IS_Student中学号一班2号的学生姓名改为“刘辰”

UPDATE v_is_student 
SET sname = '刘辰'
WHERE sclass = 1 AND sno = 2;

 -- 7.用SQL语句删除视图S_C_GRADE

DROP VIEW s_c_crade;