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 |
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 |
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()) );
--插入学生表数据
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，自行思考。
--插入学生成绩数据
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的学生和学生成绩
方式一：
DELETE from studentscore WHERE studentid = (select studentid FROM studentinfo WHERE studentno = '005'); DELETE FROM studentinfo WHERE studentno = '005';
方式二：
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
update studentscore set score = 78 where studentid in (select studentid from studentinfo where studentno = 001) and subjectname = '语文';
--查询一班男生的成绩
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;
--查询一班语文科目成绩，并通过成绩降序
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;
--通过班级和科目分组，查询平均分和最高分，按最高分排序（序号、班级、科目最高分、平均分）
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学生的各科目成绩在一行中显示。
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;
存储过程、自定义函数学习：
--新建一个存储过程，用于获取该学生的成绩
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';
--新建一个标量函数，用于格式化日期格式：输入日期类型，返回字符串类型的值
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 '转换后的日期'
--新建一个表值函数，根据班级返回所有学生的成绩
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('一班');
浙公网安备 33010602011771号