• 数据库报告存档


    前段时间把数据库学完了,顺便做了大三学长的数据库报告,现在存一下,自己大三的时候就不用写了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;
    
    
  • 相关阅读:
    使页面左右无法滑动(手机端)
    git使用简易指南(转)
    sql2012笔记
    C#的应用
    细谈HTML解析模块
    poj2299解题报告(归并排序求逆序数)
    poj2388解题报告(排序)
    poj3080解题报告(暴力、最大公共子串)
    poj1068解题报告(模拟类)
    poj3295解题报告(构造、算术表达式运算)
  • 原文地址:https://www.cnblogs.com/kylinbalck/p/15030370.html
Copyright © 2020-2023  润新知