前段时间把数据库学完了,顺便做了大三学长的数据库报告,现在存一下,自己大三的时候就不用写了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;