Sql server DML操作数据库（增删改查）

--通过命令行和图形界面向关系表添加数据  insert into Student19377056(Sno,Sname,Sgender,Sdept,Syear) values  ('S001','宋杰','男','IS',2017), ('S002','武潭','男','IS',2017), ('S003','李丽','女','IS',2018), ('S101','王彤','男','CS',2018), ('S102','谢明','男','CS',2017), ('S103','宋静','女','CS',2017), ('S201','凯林','男','MA',2018), ('S202','张梅','女','MA',2019), ('S203','杜宇','男','CS',2017),
('S301','李瑾','女','IS',2019),
('S302','王梅','女','MA',2017),
('S303','康文','男','MA',2017),
('S401','张凯','男','IS',2019),
('S402','李婷','女','MA',2019),
('S403','李选','女','IS',2017),
('S501','唐丽','女','MA',2017),
('S502','张瑞','男','IS',2018),
('S503','李倩','女','IS',2017)

insert into Sc19377056(Sno,Cno,Grade) values
('S001',8003,90),
('S002',8001,80),
('S002',8002,70),
('S003',8003,76),
('S003',9001,86),
('S101',8002,96),
('S101',8003,66),
('S101',8001,40),
('S201',8003,66),
('S201',8002,59),
('S202',1001,86),
('S202',1002,55),
('S202',1003,59),
('S203',8003,77),
('S203',8002,86),
('S203',9002,90),
('S301',8003,56),
('S301',8001,86),
('S302',8003,96),
('S401',9002,92),
('S401',9003,87),
('S401',9001,87),
('S402',9003,87),
('S402',9001,97),
('S403',8003,57),
('S403',9003,87),
('S403',9001,77),
('S403',9002,57),
('S501',8003,87),
('S501',9003,93),
('S501',1002,78),
('S501',9001,88),
('S501',1003,93),
('S502',1003,87),
('S502',9003,96),
('S502',8003,94),
('S502',9002,87),
('S503',9001,99),
('S503',9003,79),
('S503',8003,77),
('S503',8001,87)

insert into Course19377056(Cno,Cname,Cpno,Ccredit) values
(8001,'A',8002,2.5),
(8002,'B',9001,3),
(8003,'C',9002,4),
(8004,'D',8001,2),
(9001,'E',8001,2),
(9002,'F',9001,3),
(9003,'G',8002,5),
(1001,'H',8003,2.5),
(1002,'I',8002,2),
(1003,'J',8001,6)

--查询具有不同特征的学生信息，特征要求自行定义

--查询所有信息系统专业的男生的姓名，学号
select Sno,Sname from Student19377056 where Sdept='IS' and Sgender='男'

--查询所有姓李的2017级学生
select Sno,Sname from Student19377056 where Sname like '李%' and Syear = 2017

--查询所有选修8003课程的2019级学生
select Student19377056.Sno,Sname from Student19377056,Sc19377056 
where Student19377056.Sno = Sc19377056.Sno and Cno=8003 and Syear=2017

--查询所有选择2学分课程的同学成绩的前5名
select top 5 Student19377056.Sno,Sname,Sc19377056.Cno,Cname,Grade from Student19377056,Course19377056,Sc19377056
where Student19377056.Sno=Sc19377056.Sno and Sc19377056.Cno=Course19377056.Cno and 
Ccredit=2 order by Grade desc 

--每门课的选课人数和平均成绩
select  Cno,count(Cno) people_number,avg(Grade) avg_grade from Sc19377056 group by Cno

--有不及格课程学生的姓名、学号、专业
select distinct Student19377056.Sno,Sname,Sdept from Student19377056,Sc19377056 
where Student19377056.Sno=Sc19377056.Sno and grade<60

--将2017年入学，必修课成绩依然有不及格状态的同学，将其学籍状态标记为"暂缓毕业"
update Student19377056 set Sroll = '暂缓毕业' where Sno in 
(select Student19377056.Sno from Student19377056,Sc19377056 where Student19377056.Sno=Sc19377056.Sno and Syear=2017 and grade<60)

--定义视图：提供每门课的学生平均分
create view grade_student(Cno,avg_grade) as select Cno,avg(Grade) from Sc19377056 group by Cno
select * from grade_student

--删除从来没有人选修的课程信息
delete from Course19377056 where Cno not in (select Course19377056.Cno from Sc19377056,Course19377056 where Sc19377056.Cno=Course19377056.Cno)

--8004课程被去掉