【查询】基本查询

建表:

CREATE TABLE Student 
  ( 
     S#    INT, 
     Sname nvarchar(32), 
     Sage  INT, 
     Ssex  nvarchar(8) 
  ) 

CREATE TABLE Course 
  ( 
     C#    INT, 
     Cname nvarchar(32), 
     T#    INT 
  ) 

CREATE TABLE Sc 
  ( 
     S#    INT, 
     C#    INT, 
     score INT 
  ) 

CREATE TABLE Teacher 
  ( 
     T#    INT, 
     Tname nvarchar(16) 
  )

插入数据:

insert into Student select 1,N'刘一',18,N'男' union all
 select 2,N'钱二',19,N'女' union all
 select 3,N'张三',17,N'男' union all
 select 4,N'李四',18,N'女' union all
 select 5,N'王五',17,N'男' union all
 select 6,N'赵六',19,N'女' 
 
 insert into Teacher select 1,N'叶平' union all
 select 2,N'贺高' union all
 select 3,N'杨艳' union all
 select 4,N'周磊'
 
 insert into Course select 1,N'语文',1 union all
 select 2,N'数学',2 union all
 select 3,N'英语',3 union all
 select 4,N'物理',4
 
 insert into SC 
 select 1,1,56 union all 
 select 1,2,78 union all 
 select 1,3,67 union all 
 select 1,4,58 union all 
 select 2,1,79 union all 
 select 2,2,81 union all 
 select 2,3,92 union all 
 select 2,4,68 union all 
 select 3,1,91 union all 
 select 3,2,47 union all 
 select 3,3,88 union all 
 select 3,4,56 union all 
 select 4,2,88 union all 
 select 4,3,90 union all 
 select 4,4,93 union all 
 select 5,1,46 union all 
 select 5,3,78 union all 
 select 5,4,53 union all 
 select 6,1,35 union all 
 select 6,2,68 union all 
 select 6,4,71

例子:

1. 查询“001”课程比“002”课程成绩高的所有学生的学号；

Select * from 
(select S#, score from Sc where C#='1') a, 
(select S#, score from Sc where C#='2') b 
where  a.S# = b.S# and b.Score < a.Score;

2. 查询平均成绩大于60分的同学的学号和平均成绩.

方法一:

Select * from 
(Select S#, avg(score) av from sc group by S#) as a
where a.av > 60;

方法二【having后面跟聚合函数】:

Select S#, avg(Score) as avgScore from sc group by S# having avg(Score) > 60;

3. 查询所有同学的学号、姓名、选课数、总成绩

select S.S#, S.Sname,Count(C.C#) as CourseCount, Sum(C.Score) as ScoreSum
from student S left join Sc C
on S.S# = C.S#
group by S.S#, s.Sname
order by S.S#;

4. 查询姓“李”的老师的个数

Select count(distinct Tname) as count from Teacher where Tname like '李%';

5.查询没学过“叶平”老师课的同学的学号、姓名；

Select S.S#, S.Sname
from student as S
where S.S# not in 
(select distinct(sc.S#) from SC sc,Course c,Teacher t where sc.C#=c.C# and c.T#=t.T# and t.Tname='叶平')

6. 查询学过“001”并且也学过编号“002”课程的同学的学号、姓名

方法一:

select s.S#,s.Sname
from Student s,SC sc
where s.S#=sc.S# and sc.C#='001' and exists
(
    select * from SC sc2 where sc.S#=sc2.S# and sc2.C#='002'
)

方法二:

select s.S#,s.Sname
from Student s,SC sc
where s.S#=sc.S# and sc.C#='001'
intersect
select s.S#,s.Sname
from Student s,SC sc
where s.S#=sc.S# and sc.C#='002'

EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False。那么，这里我们来看一下in和exists的区别：

　　①in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。

　　②一直以来认为exists比in效率高的说法是不准确的。

　-->如果查询的两个表大小相当，那么用in和exists差别不大。

　-->如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。

7. 查询学过“叶平”老师所教的所有课的同学的学号、姓名；

select s.S#,s.Sname 
from Student s
where s.S# in 
(
  select S# from Sc sc where sc.C# in 
  (
    select C# from Teacher t 
    inner join Course c on t.T#=c.T#
    where t.Tname='叶平'
  )
)

8.查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名

select s.S#,s.Sname from Student s,
(select sc1.S#,sc1.Score from SC sc1 where sc1.C#='002') a,
(select sc2.S#,sc2.Score from SC sc2 where sc2.C#='001') b
where s.S#=a.S# and s.S#=b.S# and a.S#=b.S# and a.Score<b.Score;

9.查询有课程成绩小于60分的同学的学号、姓名

select s.S#,s.Sname
from dbo.Student s
left join dbo.Sc sc 
on s.S#=sc.S#
where sc.score < 60
group by s.S#,s.Sname

10.查询没有学全所有课的同学的学号、姓名；（可以从学全的学生中取反）

select b.s#, b.Coutsecount,c.Coutsecount from
(select S#, count(C#) as Coutsecount from Sc group by S#) as b,
(select count(*) as Coutsecount from course) as c
where b.Coutsecount < c.Coutsecount;

View Code

11.查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；

select s.S#,s.Sname from dbo.Student s
where s.S# in
(
    select distinct(sc.S#) from dbo.Sc where sc.C# 
    in(select  distinct(sc.C#)  from dbo.Sc sc where sc.S# = '001')
)

12.把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩

update SC set Score=
(
    select AVG(score) from SC sc,Course c,Teacher t
    where sc.C#=c.C# and c.T#=t.T# and t.Tname='叶平' 
)
where C# in 
(
    select distinct(sc.C#) from SC sc,Course c,Teacher t
    where sc.C#=c.C# and c.T#=t.T# and t.Tname='叶平'
)

13.查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名

select s.S#,s.Sname 
from Student s
where s.S#!='002' and s.S# in 
(
    select distinct(S#) from SC
    where C# in (select C# from SC where S#='002')
    group by S#
    having COUNT(distinct C#)=
    (
        select COUNT(distinct C#) from SC
        where S#='002'
    )
)

View Code

14.删除学习“叶平”老师课的SC表记录

delete from SC where C# in
(
    select c.C# from Course c,Teacher t
    where c.T#=t.T# and t.Tname='叶平'
)

15.向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩

insert into SC
select s.S#,'002' C#,(select AVG(sc.score) from dbo.Sc sc where sc.C# = '002') score
from dbo.Student s where s.S# not in
(
    select distinct(sc.S#)  from dbo.Sc sc where sc.C# ='002'
)

16.按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示：学生ID,语文,数学,英语,有效课程数,有效平均分

select t.S# as '学生ID',
(select Score from SC sc where sc.S#=t.S# and sc.C#='002') as '语文',
(select Score from SC sc where sc.S#=t.S# and sc.C#='003') as '数学',
(select Score from SC sc where sc.S#=t.S# and sc.C#='004') as '英语',
COUNT(t.C#) as '有效课程数',
AVG(t.Score) as '有效平均分'
from SC t
group by t.S#
order by AVG(t.Score)

17.sql中自连接的使用

select sc2.S# as '学生ID',
(select Score from SC sc1 where sc1.S#=sc2.S# and sc1.C#='002') as '语文'
from SC sc2
group by sc2.S#

参考资料:

https://www.cnblogs.com/cnki/p/5257911.html

posted @ 2019-12-12 14:16 黑涩小豆阅读(162) 评论(0) 收藏举报

刷新页面返回顶部

黑涩小豆

【查询】基本查询

公告