数据库-SQL Sever(练习检测)

ゞ浴缸里的玫瑰 2022-12-24 00:41 178阅读 0赞

### 数据库.SQL Sever.练习检测.职业技能 ###

*  0. 目录
 *  2. SQL Sever Test
 *   *  2.1 测试题目
     *  2.2 试题解答

# 0. 目录 #

# 2. SQL Sever Test #

## 2.1 测试题目 ##

> 使用SQL进行创建数据库和表

1.  创建数据库Student
2.  创建数据表：  
    学生表studentinfo（学生id、学号、姓名、电话、性别、班级、年龄、创建日期）  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ3MzIyOA_size_16_color_FFFFFF_t_70_pic_center]
3.  插入学生表数据（单条/批量插入）  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ3MzIyOA_size_16_color_FFFFFF_t_70_pic_center 1]
4.  插入学生成绩数据  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ3MzIyOA_size_16_color_FFFFFF_t_70_pic_center 2]
5.  删除学号为005的学生和学生成绩
6.  更新学号为001语文的成绩为78
7.  查询一班男生的成绩
8.  查询一班语文科目成绩，并通过成绩降序
9.  通过班级和科目分组，查询平均分和最高分，按最高分排序（序号、班级、科目、最高分、平均分）【除了SQL外，在EXCEL中也要模拟出逻辑查询处理的步骤】
10. 查询学号001学生的各科目成绩在一行中显示  
    ![在这里插入图片描述][20201125212408716.png_pic_center]

## 2.2 试题解答 ##

*  创建数据库Student

CREATE DATABASE Student;

![在这里插入图片描述][20201125212423775.png_pic_center]

*  创建数据库表studentinfo、studentscore

CREATE TABLE studentinfo(
    	studentid UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
    	studentno NVARCHAR(20) NOT NULL,
    	studengtname NVARCHAR(20) NOT NULL,
    	mobile NVARCHAR(20) NOT NULL,
    	gender NVARCHAR(20) NOT NULL,
    	classname NVARCHAR(20) NOT NULL,
    	age INT NOT NULL,
    	createdon DATETIME NOT NULL
    );

CREATE TABLE studentscore(
    	studentscoreid UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
    	studentid UNIQUEIDENTIFIER NOT NULL FOREIGN KEY REFERENCES studentinfo(studentid),
    	subjectname NVARCHAR(20) NOT NULL,
    	score INT NOT NULL,
    	createdon DATETIME NOT NULL

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ3MzIyOA_size_16_color_FFFFFF_t_70_pic_center 3]  
![在这里插入图片描述][20201125212544390.png_pic_center]

*  studentinfo、studentscore表插入数据

1.  方法1

INSERT INTO studentinfo(studentid,studentno,studentname,mobile,gender,classname,age,createdon)
    SELECT '61ACAC24-5C09-47D2-A7E9-71A4FF9ECBAD','001','张静','13811920091','女','一班',20,'2020/4/1 10:30'
    UNION
    SELECT '959FA07B-AAE2-469B-A2BE-7BDE1048A02C','002','王伟','13811920092','男','二班',19,'2020/4/1 10:30'
    UNION
    SELECT '7BF5489C-CA9E-46CD-B095-4C6FC6797F15','003','张三','13811920093','男','二班',18,'2020/4/1 10:30'
    UNION
    SELECT 'C281947B-1ABB-4D26-B880-FF227105C8D4','004','王武','13811920094','男','一班',19,'2020/4/1 10:30'
    UNION
    SELECT '8AC42551-5E05-4DA1-8329-8A896BBD0ABE','005','谢文','13811920095','男','三班',21,'2020/4/1 10:30'
    UNION
    SELECT 'FEA2D7F4-BA8A-4574-AE64-46C20A5689E0','006','叶问','13811920096','男','一班',22,'2020/4/1 10:30'
    UNION
    SELECT '03FC4DF9-AABA-4B6A-9565-705275E322B0','007','张强','13811920097','男','三班',20,'2020/4/1 10:30'
    UNION
    SELECT 'B32F30B6-5477-4462-BBC8-9FE700AF4252','008','李丽','13811920098','女','四班',18,'2020/4/1 10:30'
    
    INSERT INTO studentscore(studentscoreid,studentid,subjectname,score,createdon)
    SELECT 'CF8811B3-E3D2-401C-96F7-54EDCC77B023','61ACAC24-5C09-47D2-A7E9-71A4FF9ECBAD','语文',70,'2020/4/1 10:30'
    UNION
    SELECT '51169677-5509-43C2-A798-DEC19EEA8767','959FA07B-AAE2-469B-A2BE-7BDE1048A02C','语文',84,'2020/4/1 10:30'
    UNION
    SELECT 'BFB715EA-49FC-4898-8525-5838EDD34A8E','7BF5489C-CA9E-46CD-B095-4C6FC6797F15','语文',88,'2020/4/1 10:30'
    UNION
    SELECT 'E1F3452F-1204-42F8-9C27-EF37AE341FFC','C281947B-1ABB-4D26-B880-FF227105C8D4','语文',92,'2020/4/1 10:30'
    UNION
    SELECT 'B4112535-7002-4E7B-8EA4-75A88CD68348','8AC42551-5E05-4DA1-8329-8A896BBD0ABE','语文',98,'2020/4/1 10:30'
    UNION
    SELECT 'DA67328A-FB51-4731-9A12-1D3F43E9B722','FEA2D7F4-BA8A-4574-AE64-46C20A5689E0','语文',86,'2020/4/1 10:30'
    UNION
    SELECT '46CDCAD8-B358-4D6C-B43F-C21FBA68875F','03FC4DF9-AABA-4B6A-9565-705275E322B0','语文',67,'2020/4/1 10:30'
    UNION
    SELECT '3C242280-2FA4-44D5-8FE4-A24FE30E1E17','B32F30B6-5477-4462-BBC8-9FE700AF4252','语文',89,'2020/4/1 10:30'
    UNION
    SELECT 'B2C8AA87-29BE-4C2A-99FA-207BF7D91202','61ACAC24-5C09-47D2-A7E9-71A4FF9ECBAD','数学',94,'2020/4/1 10:30'
    UNION
    SELECT '2E3A23D0-0F76-43A1-95DA-3356748FE3A9','959FA07B-AAE2-469B-A2BE-7BDE1048A02C','数学',93,'2020/4/1 10:30'
    UNION
    SELECT '2615B55C-513D-4846-A5E7-E8345E4AF1B1','7BF5489C-CA9E-46CD-B095-4C6FC6797F15','数学',78,'2020/4/1 10:30'
    UNION
    SELECT '6CF39B27-50EE-47A1-87EB-18137D73928B','C281947B-1ABB-4D26-B880-FF227105C8D4','数学',69,'2020/4/1 10:30'
    UNION
    SELECT 'F41DE0FD-E4AB-4708-9F6E-4D5C78BFCE3B','8AC42551-5E05-4DA1-8329-8A896BBD0ABE','数学',100,'2020/4/1 10:30'
    UNION
    SELECT '51EF0AD8-604E-4058-BD5B-FCC75F3EB492','FEA2D7F4-BA8A-4574-AE64-46C20A5689E0','数学',99,'2020/4/1 10:30'

1.  方法2

INSERT INTO studentinfo VALUES ('61ACAC24-5C09-47D2-A7E9-71A4FF9ECBAD','001','张静','13811920091','女','一班',20,'2020/4/1 10:30');
    INSERT INTO studentinfo VALUES ('959FA07B-AAE2-469B-A2BE-7BDE1048A02C','002','王伟','13811920092','男','二班',19,'2020/4/1 10:30');
    INSERT INTO studentinfo VALUES ('7BF5489C-CA9E-46CD-B095-4C6FC6797F15','003','张三','13811920093','男','二班',18,'2020/4/1 10:30');
    INSERT INTO studentinfo VALUES ('C281947B-1ABB-4D26-B880-FF227105C8D4','004','王武','13811920094','男','一班',19,'2020/4/1 10:30');
    INSERT INTO studentinfo VALUES ('8AC42551-5E05-4DA1-8329-8A896BBD0ABE','005','谢文','13811920095','男','三班',21,'2020/4/1 10:30');
    INSERT INTO studentinfo VALUES ('FEA2D7F4-BA8A-4574-AE64-46C20A5689E0','006','叶问','13811920096','男','一班',22,'2020/4/1 10:30');
    INSERT INTO studentinfo VALUES ('03FC4DF9-AABA-4B6A-9565-705275E322B0','007','张强','13811920097','男','三班',20,'2020/4/1 10:30');
    INSERT INTO studentinfo VALUES ('B32F30B6-5477-4462-BBC8-9FE700AF4252','008','李丽','13811920098','女','四班',18,'2020/4/1 10:30');
    
    INSERT INTO studentscore VALUES ( 'CF8811B3-E3D2-401C-96F7-54EDCC77B023','61ACAC24-5C09-47D2-A7E9-71A4FF9ECBAD','语文',70,'2020/4/1 10:30');
    INSERT INTO studentscore VALUES ( '51169677-5509-43C2-A798-DEC19EEA8767','959FA07B-AAE2-469B-A2BE-7BDE1048A02C','语文',84,'2020/4/1 10:30');
    INSERT INTO studentscore VALUES ( 'BFB715EA-49FC-4898-8525-5838EDD34A8E','7BF5489C-CA9E-46CD-B095-4C6FC6797F15','语文',88,'2020/4/1 10:30');
    INSERT INTO studentscore VALUES ( 'E1F3452F-1204-42F8-9C27-EF37AE341FFC','C281947B-1ABB-4D26-B880-FF227105C8D4','语文',92,'2020/4/1 10:30');
    INSERT INTO studentscore VALUES ( 'B4112535-7002-4E7B-8EA4-75A88CD68348','8AC42551-5E05-4DA1-8329-8A896BBD0ABE','语文',98,'2020/4/1 10:30');
    INSERT INTO studentscore VALUES ( 'DA67328A-FB51-4731-9A12-1D3F43E9B722','FEA2D7F4-BA8A-4574-AE64-46C20A5689E0','语文',86,'2020/4/1 10:30');
    INSERT INTO studentscore VALUES ( '46CDCAD8-B358-4D6C-B43F-C21FBA68875F','03FC4DF9-AABA-4B6A-9565-705275E322B0','语文',67,'2020/4/1 10:30');
    INSERT INTO studentscore VALUES ( '3C242280-2FA4-44D5-8FE4-A24FE30E1E17','B32F30B6-5477-4462-BBC8-9FE700AF4252','语文',89,'2020/4/1 10:30');
    INSERT INTO studentscore VALUES ( 'B2C8AA87-29BE-4C2A-99FA-207BF7D91202','61ACAC24-5C09-47D2-A7E9-71A4FF9ECBAD','数学',94,'2020/4/1 10:30');
    INSERT INTO studentscore VALUES ( '2E3A23D0-0F76-43A1-95DA-3356748FE3A9','959FA07B-AAE2-469B-A2BE-7BDE1048A02C','数学',93,'2020/4/1 10:30');
    INSERT INTO studentscore VALUES ( '2615B55C-513D-4846-A5E7-E8345E4AF1B1','7BF5489C-CA9E-46CD-B095-4C6FC6797F15','数学',78,'2020/4/1 10:30');
    INSERT INTO studentscore VALUES ( '6CF39B27-50EE-47A1-87EB-18137D73928B','C281947B-1ABB-4D26-B880-FF227105C8D4','数学',69,'2020/4/1 10:30');
    INSERT INTO studentscore VALUES ( 'F41DE0FD-E4AB-4708-9F6E-4D5C78BFCE3B','8AC42551-5E05-4DA1-8329-8A896BBD0ABE','数学',100,'2020/4/1 10:30');
    INSERT INTO studentscore VALUES ( '51EF0AD8-604E-4058-BD5B-FCC75F3EB492','FEA2D7F4-BA8A-4574-AE64-46C20A5689E0','数学',99,'2020/4/1 10:30');

![在这里插入图片描述][2020112521262740.png_pic_center]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ3MzIyOA_size_16_color_FFFFFF_t_70_pic_center 4]

*  删除学号为005的学生和学生成绩

DELETE FROM studentscore WHERE studentscore.studentid = (SELECT studentid FROM studentinfo WHERE studentinfo.studentno = '005');
    DELETE FROM studentinfo WHERE studentinfo.studentno = '005';

![在这里插入图片描述][20201125212648296.png_pic_center]

*  更新学号为001语文的成绩为78

UPDATE studentscore SET score = 78 WHERE studentscore.subjectname = '语文' AND studentscore.studentid = (SELECT studentid FROM studentinfo WHERE studentinfo.studentno = '001');

![在这里插入图片描述][20201125212652517.png_pic_center]

*  查询一班男生的成绩

SELECT studengtname,subjectname,score FROM studentscore sco
    	LEFT JOIN studentinfo info ON sco.studentid = info.studentid
    	WHERE info.classname = '一班' AND info.gender = '男'
    	ORDER BY info.studentno;

![在这里插入图片描述][20201125212803883.png_pic_center]

*  查询一班语文科目成绩，并通过成绩降序

SELECT studengtname,score FROM studentscore sco
    	LEFT JOIN studentinfo info ON sco.studentid = info.studentid
    	WHERE info.classname = '一班' AND sco.subjectname = '语文'
    	ORDER BY score DESC;

![在这里插入图片描述][20201125212813443.png_pic_center]

*  通过班级和科目分组，查询平均分和最高分，按最高分排序（序号、班级、科目、最高分、平均分）

SELECT classname,subjectname,MAX(score) AS '最高分',AVG(score) AS '平均分' FROM studentscore sco
    	LEFT JOIN studentinfo info ON sco.studentid = info.studentid
    	GROUP BY classname,subjectname
    	ORDER BY MAX(score);

![在这里插入图片描述][20201125212830749.png_pic_center]  
`VT1-VT5的计算方法`

--VT1
    SELECT * FROM studentscore INNER JOIN studentinfo ON 1 = 1;
    --VT2
    SELECT * FROM studentscore sco INNER JOIN studentinfo info ON sco.studentid = info.studentid;
    --VT3
    SELECT * FROM studentscore sco INNER JOIN studentinfo info ON sco.studentid = info.studentid GROUP BY classname,subjectname;
    --VT4
    SELECT classname,subjectname,MAX(score) AS '最高分',AVG(score) AS '平均分' FROM studentscore sco INNER JOIN studentinfo info ON sco.studentid = info.studentid GROUP BY classname,subjectname;
    --VT5
    SELECT classname,subjectname,MAX(score) AS '最高分',AVG(score) AS '平均分' FROM studentscore sco INNER JOIN studentinfo info ON sco.studentid = info.studentid GROUP BY classname,subjectname ORDER BY MAX(score);

![在这里插入图片描述][20201125212839525.png_pic_center]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ3MzIyOA_size_16_color_FFFFFF_t_70_pic_center 5]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ3MzIyOA_size_16_color_FFFFFF_t_70_pic_center 6]  
![在这里插入图片描述][20201125212859897.png_pic_center]![在这里插入图片描述][20201125212905156.png_pic_center]

*  查询学号001学生的各科目成绩在一行中显示  
    `聚合方法:`

SELECT classname,studentno,studentname,
    	MAX(CASE subjectname WHEN '语文' THEN score ELSE 0 END)语文,
    	MAX(CASE subjectname WHEN '数学' THEN score ELSE 0 END)数学
    	FROM studentscore sco
    	LEFT JOIN studentinfo info ON sco.studentid = info.studentid
    	WHERE studentno = '001'
    	GROUP BY info.classname,studentno,studentname;

`pivot方法1:`

WITH q AS (
    	SELECT classname,studentno,studentname,score,subjectname FROM studentinfo 
    	JOIN studentscore 
    	ON studentinfo.studentid=studentscore.studentid 
    	WHERE studentno='001') 
    	SELECT * FROM q
    	PIVOT (max(score) FOR [subjectname] in(数学,语文)) AS t;

`pivot方法2:`

SELECT * FROM (
    	SELECT info.studentno, info.classname,info.studentname,sco.subjectname,sco.score
    	FROM studentinfo info
    	INNER JOIN studentscore sco ON info.studentid = sco.studentid
    	WHERE studentno = '001'
    )tmp
    PIVOT
    (
    	SUM(tmp.score) FOR tmp.subjectname in ([语文],[数学])
    )sco;

![在这里插入图片描述][20201125212937579.png_pic_center]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ3MzIyOA_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/6778336f53ed4fe2a9ddb2d1253a21da.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ3MzIyOA_size_16_color_FFFFFF_t_70_pic_center 1]: https://img-blog.csdnimg.cn/20201125212351644.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ3MzIyOA==,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ3MzIyOA_size_16_color_FFFFFF_t_70_pic_center 2]: https://img-blog.csdnimg.cn/20201125212400431.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ3MzIyOA==,size_16,color_FFFFFF,t_70#pic_center
[20201125212408716.png_pic_center]: https://img-blog.csdnimg.cn/20201125212408716.png#pic_center
[20201125212423775.png_pic_center]: https://img-blog.csdnimg.cn/20201125212423775.png#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ3MzIyOA_size_16_color_FFFFFF_t_70_pic_center 3]: https://img-blog.csdnimg.cn/20201125212538745.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ3MzIyOA==,size_16,color_FFFFFF,t_70#pic_center
[20201125212544390.png_pic_center]: https://img-blog.csdnimg.cn/20201125212544390.png#pic_center
[2020112521262740.png_pic_center]: https://img-blog.csdnimg.cn/2020112521262740.png#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ3MzIyOA_size_16_color_FFFFFF_t_70_pic_center 4]: https://img-blog.csdnimg.cn/20201125212630996.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ3MzIyOA==,size_16,color_FFFFFF,t_70#pic_center
[20201125212648296.png_pic_center]: https://img-blog.csdnimg.cn/20201125212648296.png#pic_center
[20201125212652517.png_pic_center]: https://img-blog.csdnimg.cn/20201125212652517.png#pic_center
[20201125212803883.png_pic_center]: https://img-blog.csdnimg.cn/20201125212803883.png#pic_center
[20201125212813443.png_pic_center]: https://img-blog.csdnimg.cn/20201125212813443.png#pic_center
[20201125212830749.png_pic_center]: https://img-blog.csdnimg.cn/20201125212830749.png#pic_center
[20201125212839525.png_pic_center]: https://img-blog.csdnimg.cn/20201125212839525.png#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ3MzIyOA_size_16_color_FFFFFF_t_70_pic_center 5]: https://img-blog.csdnimg.cn/20201125212847291.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ3MzIyOA==,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ3MzIyOA_size_16_color_FFFFFF_t_70_pic_center 6]: https://img-blog.csdnimg.cn/20201125212852849.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ3MzIyOA==,size_16,color_FFFFFF,t_70#pic_center
[20201125212859897.png_pic_center]: https://img-blog.csdnimg.cn/20201125212859897.png#pic_center
[20201125212905156.png_pic_center]: https://img-blog.csdnimg.cn/20201125212905156.png#pic_center
[20201125212937579.png_pic_center]: https://img-blog.csdnimg.cn/20201125212937579.png#pic_center