【Sql Server】Sql语句整理

use Person

<--添加约束-->
Alter table Student
    alter column  Sno char(5) not null;
Alter table Student
    Add constraint uq_sno unique(Sno);
Alter table Student 
    Add constraint C_sex check(Ssex in('男','女'));
Alter table Student 
    Add constraint df_Sage Default 20 for Sage;
Alter table Student 
    Add constraint PK_Sno primary key(Sno);
<--要把字段设置为非空，sqlserver不允许空值列建立主键约束-->
Alter table Course
    alter column Cno char(3) not null
Alter table Course 
    Add constraint PK_Cno primary key(Cno),constraint FK_Cpno_ foreign key(Cpno) references Course(Cno);
Alter table Course
    Drop constraint FK_Cpno_;

Alter table SC
    alter column Cno char(3) not null

Alter table SC
    alter column Sno char(5) not null
Alter table SC
    Add constraint PK_sc primary key(Sno,Cno),constraint FK_Sno foreign key(Sno) references Student(Sno),constraint FK_Cno foreign key(Cno) references Course(Cno)

    
<--插入数据-->
insert into Student(Sno,Sname,Ssex,Sage,Sdept) values('08001','张力','男','18','cs');
select * from Course    
select Getdate()



create table Student
(
    Sno char(5),//学号
    Sname char(20),//姓名
    Ssex char(2),//性别
    Sage Smallint,//年龄
    Sdept  char(15)//系别
); 
create table Course
(
    Cno char(3),//课程号
    Cname char(30),//课程名
    Cpno char(3),//先修课号
    Ccredit Smallint//学分
);

create table SC
(
    Sno char(5),//学号
    Cno char(3),//课程号
    Grade  int//分数
)

                                        <--查询语句-->

select Sno,Grade from SC where Cno='002' order by Grade desc;

select * from SC ORDER BY Cno,Sno desc;

select count(distinct Sno) from Student

select AVG(Grade) from SC where Cno='002';
select max(grade) from SC where Cno='001'

select Sdept,count(Sno) from Student Group by Sdept;

select Cno,avg(Grade) from SC group by Cno;

select Sno,avg(Grade) from SC group by Sno;
<--查询选修了3门课程以上的学生的学号-->
select Sno,Count(Cno) from SC group by Sno having count(cno)>3 ;
<-查询所有成绩为优秀的学生的学号->
select Sno,min(grade) 
from SC 
where Sno 
    not in(select Sno from SC where Grade is null) 
    Group by Sno 
    having min(Grade)>=90;

<--等值连接:查询每个学生及其选修课程的情况-->
select Student.*,Sc.* 
from Student,Sc 
where Student.Sno=Sc.Sno;

select Sno,Cno from Student,Course;
<--自身连接查询:查询每门课程的见解先修课-->
select First.Cno,Second.Cpno 
from Course as First,Course as Second 
where First.Cpno=Second.Cno;

<--自身连接查询:查询和刘晨在同一个系的学生-->
Select S2.* from Student S1,Student S2 
where S1.Sname='刘晨' And S1.Sdept=S2.Sdept;

<--外连接查询->
Select Student.*,Sc.* from Student Left Join Sc On Student.Sno=Sc.Sno;

<--复合连接查询:查询选修了002课程号且成绩大于90的学生情况-->
select student.* from Student,Sc 
where Student.Sno=SC.Sno And Sc.Cno='002' And Grade>'90';

<--复合连接查询:查询选修了课程的学生姓名、课程名、和成绩-->
Select Sname,Cname,Grade 
from Student,SC,Course 
where Student.Sno=SC.Sno And 
      Sc.Cno=Course.Cno;

<--查询所有成绩为优秀的学生姓名-->
Select Sname 
from Student,Sc 
where Student.Sno=SC.Sno And 
      Student.Sno Not in(Select Sno from SC where Grade is null) 
      group by Sname 
      having min(Grade)>80;

<--子查询:查询未被学生选修的课程信息--->
select * from Course where Course.Cno not in (select Distinct Cno from Sc)

<--子查询:查询选修了课程名为数据库的课程的学生信息-->

select Cno from Couse where Cname='数据库'

select Sno from Sc 
where Cno 
in(select Cno from Couse where Cname='数据库')

select * from Student 
where Sno in(select Sno from Sc where Cno in(select Cno from Course where Cname='数据库'));

select Student.* from Student,SC,Course 
where Student.Sno=SC.Sno 
      And Sc.Cno=Course.Cno 
      and Course.Cname='数据库';
      
<--子查询:查找其它系中比IS某一系学生年龄小的学生信息-->
select * from Student 
where Sage< Any(select Sage from Student where Sdept='is') 
      And Sdept<>'is' Order by Sage Desc;

<--相关子查询：查询比本系平均年龄大的的学生信息-->
select * 
from Student S1
where Sage>
        (select avg(Sage)
        from Student S2 where S1.Sdept=S2.Sdept)

<--Exists子查询；查询所有选修了001号课程的学生姓名-->
select Sname
from Student
where Exists(select *
            from Sc where Sno=Student.Sno And Cno='001');
                    


                                                <--集合查询-->
<--UNION运算符：查询计算机系的学生以及年龄不大于19的学生-->
Select * from Student where Sdept='CS'
Union
Select * from Student where Sage<=19;

Select * 
from Student 
where Sdept='Cs' Or Sage<=19;

<--集合的交操作 Intersect:查询选修了课程001而且002的学生学号-->
select Sno from Sc 
where Cno='001' And Sno In
                    (Select Sno From Sc where Cno='002');    

Select A.Sno from Sc A,Sc B
where A.Cno='001' And B.Cno='002' And A.Sno=B.Sno;            
                                    
                                                <--插入数据-->
<--创建新表Deptage，保存每一个系的学生平均年龄-->
Create table Deptage
(
    Sdept Char(15),
    Avgage Smallint
)
<--对Student表按系别进行分组，求平均年龄，然后存入表Deptage-->
Insert into 
Deptage(Sdept,Avgage) select Sdept,avg(Sage) 
from Student group by Sdept;




                                                <--修改数据-->
<--把选修了课程名为'数据库'的课程的学生的成绩改为0-->
Update Sc set Grade=0 where Cno in(Select cno 
        from Course 
        where Cname='数据库')
        
        
                                                <--删除数据-->
<--删除所有学生的选课记录-->
delete from sc;
<--删除计算机系(cs)所有学生的选课记录-->
delete 
from SC 
where Sno in(select Sno 
                    from Student 
                    where Sdept='CS')
                    
delete 
from SC 
where 'CS'=(select Sdept
                    from Student
                    where Student.Sno=Sc.Sno)