1.创建数据库：
create database DB002 create table Student ( stu_no char(50) not null unique check(len(stu_no)=8), stu_name char(50) not null unique, stu_major char(50) , stu_sex char(50) check(stu_sex='男' or stu_sex='女'), stu_birthday date ) create table [Subject] ( sub_no char(50) not null unique check(len(sub_no)=2), sub_name char(50) not null unique, sub_credit int ) create table StudentSubject ( stu_no char(50), sub_no char(50), grade int check(grade>=0 or grade <= 100) )
2.修改表
//增加约束 alter table Student add constraint stu_sex check(stu_sex='男' or stu_sex='女') //增加约束 alter table Student add constraint stu_no check(len(stu_no)=8) //增加约束 alter table [subject] add constraint sub_no check(len(sub_no)=2) //修改字段数据类型 alter table studentsubject alter column grade int //修改字段数据类型 alter table [subject] alter column sub_credit int //增加约束 alter table studentsubject add constraint grade check(grade>=0 or grade <= 100) //删除约束 alter table Student drop constraint stu_no //删除约束 alter table [subject] drop constraint sub_no
3.插入数据
insert into dbo.student(stu_no,stu_name,stu_major,stu_sex,stu_birthday)
values('20220001','张雪','土木工程','女','2003-04-23 00:00:00:000'),
('20220002','李学周','计算机','男','2004-01-15 00:00:00:000'),
('20220003','王欢','软件工程','男','2002-03-03 00:00:00:000'),
('20220004','赵有才','土木工程','男','2005-01-16 00:00:00:000'),
('20220005','关晓莹','电子','女','2004-07-11 00:00:00:000'),
('20220006','李世友','计算机','男','2005-11-09 00:00:00:000'),
('20220007','叶舒凡','软件工程','女','2003-02-03 00:00:00:000'),
('20220008','刘雪涛','土木工程','男','2005-08-13 00:00:00:000'),
('20220009','吴天放','软件工程','男','2004-12-01 00:00:00:000'),
('20220010','胡雪赫','计算机','男','2004-06-01 00:00:00:000')
select * from dbo.student
insert into dbo.[subject](sub_no,sub_name,sub_credit)
values('01','计算机基础','5'),
('02','C语言','4'),
('03','数据结构','2'),
('04','计算机网络','3'),
('05','离散数学','2'),
('06','英语','1'),
('07','软件工程','5')
select * from dbo.[subject]
insert into dbo.StudentSubject(stu_no,sub_no,grade)
values('20220001','01','70'),
('20220002','02','79'),
('20220003','01','98'),
('20220004','03','87'),
('20220005','02','69'),
('20220006','06','78'),
('20220007','01','88'),
('20220008','02','92'),
('20220009','01','81'),
('20220010','03','77'),
('20220001','03','80'),
('20220002','03','78'),
('20220003','04','85'),
('20220004','04','63'),
('20220005','04','72'),
('20220006','06','89'),
('20220007','04','93'),
('20220008','03','67'),
('20220009','03','89'),
('20220010','04','94'),
('20220001','07','66'),
('20220002','04','72'),
('20220003','05','76'),
('20220004','07','83'),
('20220005','05','79'),
('20220006','04','62'),
('20220007','06','77'),
('20220008','04','82'),
('20220009','05','73'),
('20220010','05','89'),
('20220001','04','97'),
('20220002','06','87'),
('20220003','06','77'),
('20220004','06','91'),
('20220005','07','90'),
('20220006','02','77'),
('20220007','07','63'),
('20220008','05','76'),
('20220009','06','83'),
('20220010','07','76')
select * from dbo.studentsubject
4.联表查询：
用sql语句统计每个人的所选学科学分和成绩学分（成绩/100*学分），按照所选学分从高到低显示，输出：学号、姓名、所选学分和成绩学分
select stu.stu_no,stu.stu_name,sum(sub_credit) as credit , sum(ss.grade*sub_credit/100) as grades from dbo.student as stu left join dbo.StudentSubject as ss on stu.stu_no = ss.stu_no left join dbo.[subject] as sub on sub.sub_no = ss.sub_no group by stu.stu_no,stu_name
5.找出所选学分高于8分的和成绩学分、所选学分对比不低于60%的人，输出：学号、姓名、所选学分和成绩学分
select Student.stu_no,Student.stu_name,sum(sub_credit)credit,sum(grade*sub_credit/100)grade_credit from Student left join StudentSubject on Student.stu_no = StudentSubject.stu_no left join Subject on Subject.sub_no = StudentSubject.sub_no group by Student.stu_no,Student.stu_name having(sum(grade*sub_credit/100)>=sum(sub_credit)*0.6)
6.按照姓名、各种学科对应的成绩显示，如果没有成绩显示空，（动态二维表，都没有成绩的学科就不显示）
//第一种方法
//查询出要实现的数据
select names = stuff((select ',['+ sub_name +']'
from StudentSubject left join Subject on
Subject.sub_no = StudentSubject.sub_no
group by sub_name for xml path('')), 1, 1, '')
//根据上面的结果可动态实现
select t.stu_no,stu_name ,[C语言],[计算机基础],[计算机网络],[离散数学],[软件工程],[数据结构],[英语]
from (
select Student.stu_no,Student.stu_name,sub_name,grade
from Student
left join StudentSubject on Student.stu_no = StudentSubject.stu_no
left join Subject on Subject.sub_no = StudentSubject.sub_no
)t1
pivot (
sum(grade)
for sub_name in
([C语言],[计算机基础],[计算机网络],[离散数学],[软件工程],[数据结构],[英语])
) as t
//第二种方法(容易理解，但是写法麻烦无法动态实现)
select stu_no,stu_name ,sum(C语言)as 'C语言',sum(计算机基础)as '计算机基础',sum(计算机网络)as '计算机网络',sum(离散数学)as '离散数学',sum(软件工程)as '软件工程',sum(数据结构)as '数据结构',sum(英语)as '英语' from (
select Student.stu_no,Student.stu_name ,(case when Subject.sub_name = 'C语言' then StudentSubject.grade end) as 'C语言'
,(case when Subject.sub_name = '计算机基础' then StudentSubject.grade end) as '计算机基础'
,(case when Subject.sub_name = '计算机网络' then StudentSubject.grade end) as '计算机网络'
,(case when Subject.sub_name = '离散数学' then StudentSubject.grade end) as '离散数学'
,(case when Subject.sub_name = '软件工程' then StudentSubject.grade end) as '软件工程'
,(case when Subject.sub_name = '数据结构' then StudentSubject.grade end) as '数据结构'
,(case when Subject.sub_name = '英语' then StudentSubject.grade end) as '英语'
from Student
left join StudentSubject on Student.stu_no = StudentSubject.stu_no
left join Subject on Subject.sub_no = StudentSubject.sub_no
)as t
group by stu_no,stu_name
7.时间递归
declare @start date = '2015-01-01' declare @end date = '2022-01-01'; with cte as( select @start hs_date union all select dateadd(month,1,hs_date) from cte where hs_date < @end ) select * from cte
8.按照姓名、成绩（计算机基础：60，英语：50，C语言：60，C语言：60）输出报表
with cte as (
select Student.stu_no,Student.stu_name,sub_name+':'+cast(grade as nvarchar(10)) sub_name
from Student
left join StudentSubject on Student.stu_no = StudentSubject.stu_no
left join Subject on Subject.sub_no = StudentSubject.sub_no)
select stu_no,stu_name,names = stuff((
select distinct '，'+ sub_name from cte temp where temp.stu_no = cte.stu_no
and temp.stu_name = cte.stu_name
for xml path ('')),1,1,'') from cte
group by stu_no,stu_name
9.分页
select top 4 stu_no from student where stu_no not in (select top 2 stu_no from student) select stu_no from student order by stu_no OFFSET 2 rows fetch next 4 rows ONLY
