【数据库】sql server 中的sql语句--增删改查

create database 学生
go
use 学生
go
CREATE TABLE Student (
    Sno char (7) PRIMARY KEY  ,
    Sname char (10)  NOT NULL ,
    Ssex char (2) check(ssex in('男','女')) ,
    Sage tinyint  ,
    Sdept char (20) 
)

CREATE TABLE Course (
    Cno char (10) PRIMARY KEY ,
    Cname char (20)  NOT NULL ,
    Ccredit tinyint  ,
    Semester tinyint  ,
)

CREATE TABLE sc (
    sno char (7) NOT NULL ,
    cno char (10) NOT NULL ,
    grade smallint  ,
    XKLB char (6)  ,
     PRIMARY KEY(sno,cno),
     FOREIGN KEY 
    (
        cno
    ) REFERENCES Course (
        Cno
    ),
     FOREIGN KEY 
    (
        sno
    ) REFERENCES Student (
        Sno
    ),
     CHECK (grade >= 0 and grade <= 100)
) 

go

insert student values('9512101','李勇','男',19,'计算机系')
insert student values('9512102','刘晨','男',20,'计算机系')
insert student values('9512103','王敏','女',20,'计算机系')
insert student values('9521101','张立','男',22,'信息系')
insert student values('9521102','吴宾','女',21,'信息系')
insert student values('9521103','张海','男',20,'信息系')
insert student values('9531101','钱小平','女',18,'数学系')     
insert student values('9531102','王大力','男',19,'数学系')    

insert course values('c01','计算机文化学',3,1)    
insert course values('c02','VB',2,3)
insert course values('c03','计算机网络',4,7)
insert course values('c04','数据库基础',6,6)
insert course values('c05','高等数学',8,2)
insert course values('c06','数据结构',5,4)

insert sc values('9512101','c01',90,'必修')
insert sc values('9512101','c02',86,'选修')
insert sc values('9512101','c06',NULL,'必修')
insert sc values('9512102','c02',78,'选修')
insert sc values('9512102','c04',66,'必修')
insert sc values('9521102','c01',82,'选修')
insert sc values('9521102','c02',75,'选修')
insert sc values('9521102','c04',92,'必修')
insert sc values('9521102','c05',50,'必修')
insert sc values('9521103','c02',68,'选修')
insert sc values('9521103','c06',NULL,'必修')
insert sc values('9531101','c01',80,'选修')
insert sc values('9531101','c05',95,'必修')
insert sc values('9531102','c05',85,'必修')
go

--【数据查询】
--查询所有姓‘刘’的学生。
select * from Student where Sname like '刘%'


--查询所有年龄小于20的男学生。
select * from Student where Sage<20 and Ssex='男'


--查询所有姓名第二个字是‘小’的学生。
select * from Student where Sname like '%小%'



--使用BETWEEN子句查询所有年龄在20到21岁的学生。
select * from Student where Sage between 20 and 21


--使用IN子句查询所有姓‘信息系’和‘数学系’的学生
select * from Student where Sdept in('信息系','数学系')



--查询所有学生的出生年份。
select Sname,year(getdate())-Sage as 出生年份 from Student



--统计共有多少学生。
select count(Sno)as 学生总人数 from Student


--查询各门课的课程号和考试平均成绩及选课人数。
select cno,avg(grade) as 考试平均成绩,count(sno) as 选课人数 from sc group by cno



--查询平均成绩大于80的学生的学号。
select sno from sc group by sno having avg(grade)>80 


--查询全体学生的选课情况
select Student.Sno,Sname,Cname,grade from Student join sc on Student.sno=sc.sno join Course on Course.Cno=sc.cno


--查询计算机系学生的修课情况，要求列出学生的名字、所修课的课程号和成绩。
select Sname,cno,grade from Student left join sc on Student.sno=sc.sno where Student.Sdept='计算机系'

--查询信息系修了VB课程的学生的修课成绩，要求列出学生姓名、课程名和成绩。
select Sname,Cname,grade from Student  join sc on Student.sno=sc.sno join Course on sc.cno=Course.Cno  where Cname='VB'


--查询所有修了VB课程的学生的修课情况，要求列出学生姓名和所在的系。
select Sname,Sdept from Student  join sc on Student.sno=sc.sno join Course on sc.cno=Course.Cno  where Cname='VB'


--查询与张海在同一个系学习的学生的姓名和所在的系。
select Sname,Sdept from Student where Sdept in(select Sdept from Student where Sname='张海'); 




--查询学生的修课情况，包括修了课程的学生和没有修课的学生。
select Student.Sno,Sname,Cname,grade from Student left join sc on Student.sno=sc.sno left join Course on Course.Cno=sc.cno


--用子查询查询与钱小平在同一个系的学生。
select Sname from Student where Sdept in(select Sdept from Student where Sname='钱小平'); 





--用子查询找出平均成绩为大于80分的学生的学号、姓名、系别。
select Sno,Sname,Sdept from Student where sno in(select sno from sc group by sno having avg(grade)>80)






-- 用子查询查询选修了“数据库基础”课程的学生的学号、姓名。
select Sno,Sname from Student where sno in(select sno from sc where cno in(select cno from Course where Cname='数据库基础'))


--用子查询查询修了‘c02’课程且成绩高于此课程的平均成绩的学生的学号和成绩。
select Sno,grade from sc where grade >(select avg(grade) from sc where cno='c02') and cno='c02'







-- 用子查询查询选修了‘c01’号课程的学生姓名。
select Sname from Student where sno  in(select sno from sc where cno='c01')


-- 用子查询查询没有选修‘c01’号课程的学生姓名和所在系。
select Sname from Student where sno not in(select sno from sc where cno='c01')


-- 用子查询找出‘李勇’所选课程的学分之和。
select sum(Ccredit) as 学分之和 from Course where cno in(select cno from sc where sno in(select sno from Student where Sname='李勇'))


-- 用子查询找出所有学生中年龄最大的学生的姓名、年龄。
select Sname,Sage from Student where Sage=(select max(Sage) from student)



-- 用子查询找出所有没有成绩的学生的姓名及系别。
select Sname,Sdept from Student where sno in(select sno from sc where grade is null)





--Q25. 用子查询计算‘数学系’的学生的平均成绩。
select avg(grade) from sc group by sno having sno in(select sno from Student where Sdept='数学系')



--【数据更新】
--1.    将新生（学号：9512110；姓名：TOM；性别：男；年龄：22；所在系：数学系；SEMAIL：[email protected]）的记录插入到学生表中，
---并增加相应的选课记录（9512110，c01，80，必修）。
alter table Student add SEMAIL varchar(40)
insert into Student values('9512110','TOM','男','22','数学系','[email protected]')
insert into sc values('9512110','c01','80','必修')
select *from sc


--2.    假设过了一个学年，将所有学生的年龄增加1岁。
update Student set Sage=Sage+1

--3.    事后发现学生李勇考试作弊，将其所有成绩改为0分。
update sc set grade=0 from sc  join student on sc.sno=student.sno where sname='李勇'
select *from sc




--4.    学号为“9512102”的学生平时表现优异，将其“c04”成绩增加10分。
update sc set grade=grade+10 where cno='c04'and sno='9512102'



--5.    由于无人选修“c04”课程，因此学校决定暂不开课，删除所有“c04”的选课记录。
delete from sc where cno='c04'
select *from sc


--6.    学号为“9512102”的学生由于退学，删除该学生及其选课记录。
delete from Student where sno='9512102'
delete from sc where sno='9512102'
select *from student