SQL server 数据库查询语句的基本实现

桃扇骨 2022-05-25 01:14 407阅读 0赞

** 这篇博文的均是以 SQL server 数据库为基础的基本查询语句的实现。涉及到学生表选课表和课程表，基本操作都附加在查询语句后，还请读者留意。**

![20180505115338363][]

![20180505115359465][]

## **查询分析器下的语句实现：** ##

create database scs;
    use SCS;
    create table student
    (
       sno char(7)  primary key,
       sname char(10)  not null,
       ssex char(2)  ,
       sage tinyint,
       sdept char(20)
    
    );
    create table course 
    (
        cno char(6) primary key,
        cname char(20) not null,
        credit tinyint ,
        semster tinyint
    );
    
    create table sc
    (
        sno char(7)  ,
        cno char(6)  ,
        grade smallint,
        primary key (sno,cno),      ---主键为两个的时候，不能用列级约束
        foreign key(sno) references student(sno),     ---此时约束应该为表级约束，且foreign key（sno/cno），（sno/cno）在列级约束的时候可以省略，在表级约束不行
        foreign key(cno) references course(cno)
    );
    select *from student
    insert into student
    values('1512101','李勇','男',19,'计算机系'),
    ('1512102','刘晨','男',20,'计算机系'),
    ('1512103','王敏','女',18,'计算机系'),
    ('1512104','李小玲','女',19,'计算机系'),
    ('1521101','张立','男',22,'信息系'),
    ('1521102','吴宾','女',21,'信息系'),
    ('1521103','张海','男',20,'信息系'),
    ('1531101','钱小平','女',18,'数学系'),
    ('1531102','王大力','男',19,'数学系')
    
    select * from course
    insert into course
    values('c001','计算机文化学',3,1),
    ('c002','高等数学',6,1),
    ('c003','高等数学',3,2),
    ('c004','大学英语',6,2),
    ('c005','java',2,3),
    ('c006','程序设计',3,3),
    ('c007','数据结构',5,4),
    ('c008','操作系统',4,4),
    ('c009','数据库基础',4,5)
    
    select *from sc
    insert into sc
    values ('1512101','c001',90),
    ('1512101','c002',86),
    ('1512101','c003',92),
    ('1512101','c005',88),
    ('1512101','c006',null),
    ('1512102','c001',76),
    ('1512102','c002',78),
    ('1512102','c005',66),
    ('1512104','c002',66),
    ('1512104','c005',78),
    ('1512104','c008',66),
    ('1521102','c001',82),
    ('1521102','c005',75),
    ('1521102','c007',92),
    ('1521102','c009',50),
    ('1521103','c002',68),
    ('1521103','c006',null),
    ('1521103','c007',null),
    ('1521103','c008',78),
    ('1531101','c001',80),
    ('1531101','c005',50),
    ('1531101','c007',45),
    ('1531102','c001',80),
    ('1531102','c002',75),
    ('1531102','c005',85),
    ('1531102','c009',88)
    
    select sno as 学号,cno as 课程号,grade as 成绩 from sc     ---查询学生选课表的所有信息
    
    select sname as 姓名,sage as 年龄 ,sdept as 系名  from student where sdept='计算机系'    --查询计算机系的学生的姓名和年龄
    
    select sno as 学号,cno as 课程号, grade as 成绩 from sc where grade between 70 and 80    --查询成绩在70-80之间的学生的学号课程号和成绩
    
    select sname as 姓名,sage as 年龄,ssex as 性别, sdept as 系名 from student where sdept='计算机系' and ssex='男' and sage between 18 and 20    ---查询计算机系年龄在18-20岁之间的且性别为男的学生的姓名和年龄
    
    select MAX(grade) as 最高分数 from sc where cno='c001'   --查询课程号c001的课程的最高分数
    select MAX(sage) as 最大年龄,MIN(sage) as 最小年龄 from student where sdept='计算机系'    ---查询计算机系学生的最大年龄和最小年龄
    
    select sdept as 系名,count(*) as 人数 from student 
    group by sdept                   --统计每个系的学生人数
    
    select cno as 课程号, COUNT(*) as 选课人数,MAX(grade) as 考试最高分 from sc
    group by cno              ---统计每门课程的选课人数和考试的最高分
    
    select sno as 学号, COUNT(*)as 选课门数,sum(grade) as 考试总成绩 from sc 
    group by sno
    order by 选课门数                   ---统计每个学生的选课门数和考试的总成绩，并按选课门数的升序显示结果
    
    select sno as 学号, grade =SUM(grade)from sc 
    group by sno   having SUM(grade)>200               ---10、查询总成绩超过200分的学生，要求列出学号，总成绩
    
    
    select sname as 姓名,sdept as 所在系 from student join sc  on student.sno=sc.sno where cno='c002'          --查询选修了c002号课程的学生的姓名和所在系
    select sname as 姓名,cno as 课程,grade as 成绩 from student join sc on student.sno=sc.sno where grade>80
    order by grade desc           ---查询成绩在80分以上的学生的姓名、课程号、成绩，并按成绩的降序排列结果
    select sname as 姓名,student.sno as 学号,sdept as 所在系 from student join sc on student.sno=sc.sno join course on sc.cno=course.cno
    where course.cno=null            ---查询哪些学生没有选课，要求列出其学号，姓名和所在系
    
    select  cname as 课程名,semster as 开课学期 from course where semster =
    (
       select semster from course where cname='java'     ---查询与java在同一学期开设的课程的课程名和开课学期
    )
    
    
     select sname as 姓名,sdept as 所在系,sage as 年龄 from student where sage=
     (select sage from student where  sname='李勇')      ---查询和李勇年龄相同的学生的学号姓名和所在系
      
     select  sname as 姓名,sdept as 所在系 from student join sc on student.sno=sc.sno join course on sc.cno=course.cno
     where course.cno='c001'             ---查询选修了c001课程的学生的姓名和所在系   ,这是连接查询
     select  sname as 姓名,sdept as 所在系 from student where sno=where (select cno from sc)
    
    
    ----实验三实验四内容部分
    select sname as 姓名,sdept as 所在系 from student  where sno in
    (
      select sno from sc where cno='c001'      -----查询选修了“c001”号课程的学生的姓名和所在系
    )
    
    ---或者用到exists
    select sname ,sdept from student  where exists
    (select *from sc
    where sno=student.sno
    and cno='c001'
    )
    
    
    
    select  student.sno ,sname ,cno,grade from student join sc on student.sno=sc.sno  where grade>80                    
                                                                      
     and sc.sno in (select sno from student where sdept='数学系')-----查询数学系成绩80分以上的学生的学号、姓名、课程号和成绩。
    
    select sname from student where sno in
    ( 
         select sno from sc where grade=(select MAX(grade) from sc)      ----查询计算机系考试成绩最高的学生的姓名
    )and sdept='计算机系'     
    
       
    
    select sname ,sdept from student where sno in 
    
    (
    
     select sno from sc  join course on sc.cno=course.cno where grade=(select MAX(grade) from sc) and cname='数据结构'
                               ------查询数据结构考试成绩最高的学生的姓名和所在系。 子查询和连接查询套用
    )
    
    
    
    select sname ,sdept from student where sno in
    (
       select sno from sc where cno in 
       (
       
         select cno from course where cname='数据结构'              ------查询数据结构考试成绩最高的学生的姓名和所在系
       
       ) and grade= (select MAX(grade) from sc)
    
    ）

----1、建立一个存储过程proc_ student，要求该存储过程实现：查询某一位学生是否选修某门课程，
    --若没有选修，则该生对这门课程进行选修（即在SC表中插入相应的学生学号和课程号）；若已选修，则显示学生学号、课程名称及成绩。
    --执行存储过程proc_ student。
    ---、删除所有新建的存储过程。
    
    if exists( select * from sysobjects where name='proc_student'  and type='p' )
    drop proc proc_student
    
    
    go 
    create proc proc_student (@no char(7),@no1 char(6))
    as 
    begin
    	if exists (select * from student join sc on sc.sno=student.sno where sc.sno=@no and cno=@no1)
    
    		select * from sc where sno=@no and cno =@no1
    	else 
    
    		insert into sc(sno,cno)
    	    values(@no,@no1)
    end
      exec proc_student '1512101','c004'
      select *from sc
      drop proc proc_student

所有作品均为作者原创，正处在学习数据库的道路上，希望学习数据库的你们，可以从我的

分享中有所收获。个人能力毕竟有限，如果大家有的地方可以进行查询语句的优化，欢迎大家留言指正。

**更多精彩内容请关注公众号：干货分享录**

![20190413174801384.png][]

[20180505115338363]: /images/20220525/866c37d6386c4df19663a1f50345282f.png
[20180505115359465]: /images/20220525/f61f840219d744cfae2f686ff1e6bdee.png
[20190413174801384.png]: /images/20220525/142c64ceee594806abfdc1750c50ba4c.png