数据库艰难求生之路(基础：增删改查)part2

一、数据库查询

由于这个点的东西实在是多的，我就和题目，知识点一起演示。

首先是创建数据库：

create database ExampleInfo  --创建数据库

use ExampleInfo   --使用数据库

其次是创建数据库表：

--创建学生表
create table StudentTable(
Sid int not null primary key identity,
Sname varchar(50),
Sage datetime,
Ssex nvarchar(10)
)
 --插入学生数据
insert into StudentTable(Sname,Sage,Ssex) 
values ('王昭君',18,'女'),
('貂蝉',18,'女'),('韩信',18,'男'),
('李白',20,'男'),('蔡文姬',19,'女'),
('后裔',19,'男'),('伽罗',19,'女')

--创建课程表
create table CourseTable(
Cid int not null primary key identity,
Cname varchar(50),
Tid int
)
--插入课程数据
insert into CourseTable(Cname,Tid)
 values ('语文',1),('数学',3),('英语',5),
('物理',4),('化学',6),('生物',5),('地理',2)

--创建教师表
create table TeacherTable(
Tid int not null primary key identity,
Tname varchar(50)
)
--插入教师数据
insert into TeacherTable(Tname)
 values ('诸葛亮'),('黄总'),('老夫子'),
('墨子'),('女娲'),('伏羲')

--创建一张学生课程表
create table SCTable(
Sid int,
Cid int,
Score int,
foreign Key (Sid) REFERENCES StudentTable(Sid),
foreign Key (Cid) REFERENCES CourseTable(Cid)
)
--插入学生课程数据
insert into SCTable (Sid,Cid,Score)
values (1,1,80),(1,2,89),(1,3,88),
(1,4,87),(1,5,78),(1,6,48),(1,7,87),
(2,1,55),(2,2,77),(2,4,99),(2,5,89),
(2,6,15),(3,1,88),(3,2,77),(3,3,78),
(3,4,75),(3,5,67),(3,6,89),(3,7,88),
(4,1,78),(4,4,98),(4,5,89),(4,6,78),
(4,7,79),(5,1,77),(5,2,85),(5,3,82),
(5,5,76),(5,6,95),(6,1,94),(6,4,48),
(7,1,58),(7,2,88),(7,4,75),(7,6,84),
(7,7,99)

 1.　查询至少选修两门课程的学生学号；

select Sid from scTable
group by sid
having count(sid) > 2

 1） 在数据库中的表名，字段名，数据库名是没有大小写区分的，所以，我在这里可以随便大小写（为了美观还是写的规整一点）

 2)  group by  这是一种分组方式，就是将数据分成一个个“小区域”，在这段语句中，从表SCTable中查出了Sid,然后根据Sid相同的进行分类，它会自动过滤相同的sid。

 3） 这里还用到了having，它和where 有些类似，都是会根据后面的条件过滤数据。它通常是与group by还有聚合函数一起使用。

 

 

 

 

 

 

聚合函数：（要记住，后面题目有用）

avg():求平均数

count()：计数（求个数）

MIN():求最小值

Max():求最大值

sum : 总和 （求总和）

 

 

 

 

 

 

 

 

2、查询1号同学的科目id为1课程的成绩；

select Score from scTable
where Sid = 1 and cid = 1

3、 查询平均成绩大于60分的同学的学号和平均成绩；

首先分析用到了几个表：这里只需要知道学号和平均成绩，所以只用到SCTable就可以了

select Sid,AVG(Score) as avgScore from ScTable 
group by Sid 
having avg(score)>60

as:英文意思中就有作为的意思，这里差不多，就是将平均成绩avg(score) 的列名作为avgScore

 

 

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

分析：有几个表，从题目中可以得知，一定有StudentTable表因为有姓名，还要用成绩所以也有SCTable。

总成绩：sum()函数，选课数：count()函数。

select s.Sid,s.Sname,count(cid) as CountCourse,sum(Score) as SumScore
from StudentTable s,ScTable sc
where sc.Sid = s.Sid
group by s.Sid,s.Sname

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

解法一：
select s.Sid,s.Sname from StudentTable s,ScTable sc
where sc.Sid = s.Sid
group by s.Sid,s.Sname,Score
having Score<60

解法二：
select s.Sid,s.Sname 
from StudentTable s
where s.Sid in
(
    select distinct(sc.Sid) from ScTable sc
    where s.Sid=sc.Sid and sc.Score<60
)

 

5、查询“1”课程比“2”课程成绩高的所有学生的学号；

分析：涉及一个表，但是却存在同一个列的比较，和根据条件查询出这两个列，然后直接比较

 select a.Sid from
 (select Sid,Score from Sctable where Cid=1) a,           
 (select Sid,Score from Sctable where Cid=2) b
 where a.Sid=b.Sid and a.Score>b.Score

6、查询姓“诸葛”的老师的个数；

select count(*) as TCount from TeacherTable where Tname like '诸葛%'

 值得注意的是：like 模糊查询，通常和'%'连用。通常有三种情况

1）XX%  表示的是左边确定，右边不确定
select count(*) as TCount from TeacherTable where Tname like '诸葛%'

2）%XX 表示的是左边不确定，右边确定

select count(*) as TCount from TeacherTable where Tname like '%亮'

 

3）%XX% 表示只是知道中间的某一部分

select count(*) as TCount from TeacherTable where Tname like '%葛%'

4）X%X 表示只是知道左右两边不知道中间部分。

select count(*) as TCount from TeacherTable where Tname like '诸%亮'

 7、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；

select s.Sid,s.Sname
from StudentTable s,ScTable sc
where s.Sid=sc.Sid and sc.Cid='1'
intersect
select s.Sid,s.Sname
from StudentTable s,ScTable sc
where s.Sid=sc.Sid and sc.Cid='2'

联合查询
    1) Union 对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序。
    2) Union all 对两个结果集进行并集操作，包括重复行，不进行排序。
    3) except  返回两个结果集的差
    4) intersect 返回两个结果集的并集
比如说：a = {1,2,3,4} b={2,3,4,5}
则 
 使用Union  结果：   {1，2，3，4，5}
 使用Union all 结果：{1，2，3，4，2，3，4，5}
 使用except  
  1.a execpt b  结果：{1，2}
  2.b execpt a  结果：{5}
 使用intersect 结果：{2,3,4}
一、查询学过“1”或者学过“2”课程的同学的学号、姓名；

select s.Sid,s.Sname
from StudentTable s,ScTable sc
where s.Sid=sc.Sid and sc.Cid='1'
Union
select s.Sid,s.Sname
from StudentTable s,ScTable sc
where s.Sid=sc.Sid and sc.Cid='2'

二、 用UNION all 结合上面结果

select s.Sid,s.Sname
from StudentTable s,ScTable sc
where s.Sid=sc.Sid and sc.Cid='1'
Union ALL
select s.Sid,s.Sname
from StudentTable s,ScTable sc
where s.Sid=sc.Sid and sc.Cid='2'

 

三、学过“1”但是没有学过“2”课程的同学的学号、姓名；

select s.Sid,s.Sname
from StudentTable s,ScTable sc
where s.Sid=sc.Sid and sc.Cid='1'
except
select s.Sid,s.Sname
from StudentTable s,ScTable sc
where s.Sid=sc.Sid and sc.Cid='2'

 8、查询学过“诸葛亮”老师所教的所有课的同学的学号、姓名；

select s.Sid,s.Sname from StudentTable s,SCTable sc
where sc.sid = S.Sid and Cid = (
select Cid from CourseTable C where c.Cid =(
select Tid from TeacherTable where Tname = '诸葛亮'
)
)

9、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；

select s.Sid,s.Sname from StudentTable s,
(select sc1.Sid,sc1.Score from ScTable sc1 where sc1.Cid = 2) a,
(select sc2.Sid,sc2.Score from ScTable sc2 where sc2.Cid = 1) b
where a.Sid = b.Sid and s.Sid = a.Sid and s.sid = b.sid and a.Score <b.Score

10、查询没有学全所有课的同学的学号、姓名；

方案一
select s.Sid,Sname from StudentTable s,scTable sc
where S.Sid = sc.Sid 
group by s.Sid,Sname
having  count(sc.cid) <( select count(cid) as Ccount from CourseTable)

方案二
select s.Sid,s.Sname
from StudentTable s
where s.Sid not in
(
    select sc.Sid from SCTable sc
    group by sc.Sid
    having COUNT(distinct sc.Cid)=
    (
        select COUNT(distinct c.Cid) from CourseTable c
    )
)

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

select sc.Sid  as '学生ID',
(select Score from Sctable where Sid = sc.Sid and Cid = (select Cid from CourseTable where Cname ='语文')
) as 语文,
(select Score from Sctable where Sid = sc.Sid and Cid = (select Cid from CourseTable where Cname ='数学')
) as 数学,
(select Score from Sctable where Sid = sc.Sid and Cid = (select Cid from CourseTable where Cname ='英语')
) as 英语,
(select Score from Sctable where Sid = sc.Sid and Cid = (select Cid from CourseTable where Cname ='物理')
) as 物理,
(select Score from Sctable where Sid = sc.Sid and Cid = (select Cid from CourseTable where Cname ='化学')
) as 化学,
(select Score from Sctable where Sid = sc.Sid and Cid = (select Cid from CourseTable where Cname ='生物')
) as 生物,
(select Score from Sctable where Sid = sc.Sid and Cid = (select Cid from CourseTable where Cname ='地理')
) as 地理,
count(sc.Cid) as '有效课程数',
avg(Score) as '有效平均分'
from scTable sc
group by sc.Sid

12、按各科平均成绩从低到高和及格率的百分数从高到低顺序；

select c.Cid,c.Cname,avg(Sc.Score) as avgScore,
100*SUM(case when isnull(sc.Score,0)>=60 then 1 else 0 end )/count(*) as 'Percent(%)'
from sctable sc,CourseTable c
where c.cid = sc.cid 
group by c.Cid,c.Cname
order by 'Percent(%)' desc

专题小训练：case when then else end:

Case具有两种格式。简单Case函数和Case搜索函数。

1.查询学生表中性别为男和性别为女的个数

 

--分析：首先性别为一列，然后是计算的性别的数量为一列，而计算数量可以用Count来计算
select Ssex, count(
    case ssex 
    when '女' then 1
    when '男' then 0
    else '无性别' end

) as sexCount from studentTable
group by Ssex

 

2.根据学生成绩判断学生的等级，90分以上为特别优秀，80分以上为优秀，70分以上为良好，60分以上为及格，60分一下为差。显示出学生的姓名，课程名，分数，等级。

select Sc.sid,s.Sname,sc.Score,c.Cname,(case 
                   when score between 90 and 100 then '特别优秀'
                   when score between 80 and 90 then '优秀'
                   when score between 70 and 80 then '良好'
                   when score between 60 and 70 then '及格'
            else '差' end)  as '等级'
            from sctable sc,studenttable s,coursetable c
            where sc.Sid = s.sid and c.Cid = sc.cid
            group by Sc.sid,s.Sname,sc.Score,c.Cname

3.统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-90],[80-90],[60-80],[ <60]

select sc.Cid,MAX(c.Cname) as 'CourseName',
SUM(CASE WHEN sc.Score BETWEEN 90 and 100 THEN 1 ELSE 0 END) as '[90-100]',
SUM(CASE WHEN sc.Score BETWEEN 80 and 90 THEN 1 ELSE 0 END) as '[80-90]',
SUM(CASE WHEN sc.Score BETWEEN 60 and 80 THEN 1 ELSE 0 END) as '[60-80]',
SUM(CASE WHEN sc.Score BETWEEN 0 and 60 THEN 1 ELSE 0 END) as '[<60]'
from SCtable sc,Coursetable c
where sc.Cid=c.Cid
group by sc.Cid

 

训练完成：https://www.cnblogs.com/prefect/p/5746624.html（这位大神的这一段讲的挺好）

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

  备注：这里的题目我是做完了一个大神博客的题目自己模仿写的。做完他的题目后深有感触。若果需要加大训练的请去

https://www.cnblogs.com/edisonchou/p/3878135.html