SQL Sever 学习小案例

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

创建数据库Student

创建数据表

--学生表

名称	学生id	学号	姓名	电话	性别	所在班级	年龄	创建时间
字段名称	studentid	studentno	name	mobile	gender	classname	age	createdon
字段类型	uniqueidentifier	nvarchar(20)	nvarchar(20)	nvarchar(20)	nvarchar(20)	nvarchar(20)	int	datetime

?

1 2 3 4 5 6 7 8 9 10

CREATE TABLE studentinfo(     studentid UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),     studentno NVARCHAR(20),     name NVARCHAR(20),     mobile NVARCHAR(20),     gender NVARCHAR(20),     classname NVARCHAR(20),     age int,     createdon datetime DEFAULT(getdate()) );

--成绩表studentscore

名称	学生成绩表id	学生id	科目	分数	创建时间
字段名称	studentscoreid	studentid	subjectname	score	createdon
字段类型	uniqueidentifier	uniqueidentifier	nvarchar(20)	int	datetime

?

1 2 3 4 5 6 7

CREATE TABLE studentscore(     studentscoreid UNIQUEIDENTIFIER DEFAULT NEWID(),     studentid UNIQUEIDENTIFIER CONSTRAINT s_id FOREIGN KEY(studentid) REFERENCES studentinfo(studentid),     subjectname NVARCHAR(20),     score int,     createdon datetime DEFAULT(getdate())     );

--插入学生表数据　

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

insert into studentinfo(studentid,studentno,name,mobile,gender,classname,age,createdon) values('61ACAC24-5C09-47D2-A7E9-71A4FF9ECBAD ','001','张静','13811920091','女','一班',20,'2020/04/01 10:30')   insert into studentinfo(studentid,studentno,name,mobile,gender,classname,age,createdon) values ('959FA07B-AAE2-469B-A2BE-7BDE1048A02C ','002','王伟','13811920092','男','二班',19,'2020/04/01 10:30'), ('7BF5489C-CA9E-46CD-B095-4C6FC6797F15 ','003','张三','13811920093','男','二班',18,'2020/04/01 10:30'), ('C281947B-1ABB-4D26-B880-FF227105C8D4 ','004','王武','13811920094','男','一班',19,'2020/04/01 10:30'), ('8AC42551-5E05-4DA1-8329-8A896BBD0ABE ','005','谢文','13811920095','男','三班',21,'2020/04/01 10:30'), ('FEA2D7F4-BA8A-4574-AE64-46C20A5689E0 ','006','叶问','13811920096','男','一班',22,'2020/04/01 10:30'), ('03FC4DF9-AABA-4B6A-9565-705275E322B0 ','007','张强','13811920097','男','三班',20,'2020/04/01 10:30'), ('B32F30B6-5477-4462-BBC8-9FE700AF4252 ','008','李丽','13811920098','女','四班',18,'2020/04/01 10:30')

注：这是完全仿照图片标准，上面进行建表已经使用newid()，自动生成id，自行思考。

--插入学生成绩数据

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

insert into studentscore(studentscoreid,studentid,subjectname,score,createdon) values ('CF8811B3-E3D2-401C-96F7-54EDCC77B023 ','61ACAC24-5C09-47D2-A7E9-71A4FF9ECBAD','语文',70,'2020/04/01 10:30'), ('51169677-5509-43C2-A798-DEC19EEA8767 ','959FA07B-AAE2-469B-A2BE-7BDE1048A02C','语文',84,'2020/04/01 10:30'), ('BFB715EA-49FC-4898-8525-5838EDD34A8E ','7BF5489C-CA9E-46CD-B095-4C6FC6797F15','语文',88,'2020/04/01 10:30'), ('E1F3452F-1204-42F8-9C27-EF37AE341FFC ','C281947B-1ABB-4D26-B880-FF227105C8D4','语文',92,'2020/04/01 10:30'), ('B4112535-7002-4E7B-8EA4-75A88CD68348 ','8AC42551-5E05-4DA1-8329-8A896BBD0ABE','语文',98,'2020/04/01 10:30'), ('DA67328A-FB51-4731-9A12-1D3F43E9B722 ','FEA2D7F4-BA8A-4574-AE64-46C20A5689E0','语文',86,'2020/04/01 10:30'), ('46CDCAD8-B358-4D6C-B43F-C21FBA68875F ','03FC4DF9-AABA-4B6A-9565-705275E322B0','语文',67,'2020/04/01 10:30'), ('3C242280-2FA4-44D5-8FE4-A24FE30E1E17 ','B32F30B6-5477-4462-BBC8-9FE700AF4252','语文',89,'2020/04/01 10:30'), ('B2C8AA87-29BE-4C2A-99FA-207BF7D91202 ','61ACAC24-5C09-47D2-A7E9-71A4FF9ECBAD','数学',94,'2020/04/01 10:30'), ('2E3A23D0-0F76-43A1-95DA-3356748FE3A9 ','959FA07B-AAE2-469B-A2BE-7BDE1048A02C','数学',93,'2020/04/01 10:30'), ('2615B55C-513D-4846-A5E7-E8345E4AF1B1 ','7BF5489C-CA9E-46CD-B095-4C6FC6797F15','数学',78,'2020/04/01 10:30'), ('6CF39B27-50EE-47A1-87EB-18137D73928B ','C281947B-1ABB-4D26-B880-FF227105C8D4','数学',69,'2020/04/01 10:30'), ('F41DE0FD-E4AB-4708-9F6E-4D5C78BFCE3B ','8AC42551-5E05-4DA1-8329-8A896BBD0ABE','数学',100,'2020/04/01 10:30'), ('51EF0AD8-604E-4058-BD5B-FCC75F3EB492 ','FEA2D7F4-BA8A-4574-AE64-46C20A5689E0','数学',99,'2020/04/01 10:30')

注：同学生表插入数据一样，是否可以更简洁。

实现一些简单的操作：

--删除学号为05的学生和学生成绩

方式一：

?

1 2	DELETE from studentscore  WHERE studentid = (select studentid   FROM studentinfo WHERE studentno = '005'); DELETE  FROM studentinfo WHERE studentno = '005';

方式二：

?

1 2 3 4

declare @result_studentid as varchar(100); select @result_studentid =studentid from studentinfo where studentno='005'; DELETE from studentscore  WHERE studentid =@result_studentid; DELETE  FROM studentinfo WHERE studentno = '005';

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

?

1	update studentscore set score = 78 where studentid in (select studentid from studentinfo where studentno = 001) and subjectname = '语文';

--查询一班男生的成绩

?

1 2 3 4

select name,subjectname,score from studentscore sco     LEFT JOIN studentinfo info ON sco.studentid = info.studentid     where info.classname = '一班' AND info.gender = '男'     order by info.studentno;

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

?

1 2 3 4

select name,score from studentscore sco     LEFT JOIN studentinfo info ON sco.studentid = info.studentid     where info.classname = '一班' AND sco.subjectname = '语文'     order by score desc;

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

?

1 2 3 4

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);

--查询学号为001学生的各科目成绩在一行中显示。　

?

1 2 3 4 5 6 7

select classname,studentno,name,     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,name;

　　

 

存储过程、自定义函数学习：

--新建一个存储过程，用于获取该学生的成绩

?

1 2 3 4 5 6 7 8 9 10 11 12 13

create proc proc_stuscore @sno nvarchar(20) output as begin     select classname,studentno,name,     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 = @sno     group by info.classname,studentno,name; end; exec proc_stuscore '001';

　　

--新建一个标量函数，用于格式化日期格式：输入日期类型，返回字符串类型的值

?

1 2 3 4 5 6 7 8 9 10 11 12 13

create function func_get_date(@date_input date) returns nvarchar(20) as begin     declare @year varchar(10)         set @year =datename(year,@date_input)+'年'     declare @month varchar(10)         set @month =datename(month,@date_input)+'月'     declare @day varchar(10)         set @day =datename(day,@date_input)+'日'     return @year+@month+@day end; select dbo.func_get_date('2020-01-20') as '转换后的日期'

　　

--新建一个表值函数，根据班级返回所有学生的成绩

?

1 2 3 4 5 6 7 8 9 10 11

create function func_get_table(@cname  nvarchar(20)) returns table as return  select classname,studentno,name,     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 classname = @cname     group by info.classname,studentno,name select * from func_get_table('一班');