注:仅作为对自己学习SQL的一些记录,由浅及深,后续会继续补充,本文主要是在SQL Server中实践
** 本文练习的数据是创建了四张表,Student,Score,Course,Teacher,方便做表的交叉查询,表间通过主外键链接,也希望对入门SQL的同学有一定参考价值
本文在题目的筛选上也参考了知乎用户:[猴子聊数据分析](https://zhuanlan.zhihu.com/p/38390980?utm_source=wechat_session&utm_medium=social&utm_oi=40863849775104),感谢大神给了我练习的一些思路
1.建表过程,比较简单,这里注意的是,每个定义列的数据类型,以及定义主键
## 创建 Student 表
Create table Student(
s_id varchar(50) ,
s_name varchar(20) not null ,
s_birth varchar(20) not null,
s_sex varchar(10) not null,
primary key (s_id)
);
## 创建Course 表
Create table Course(
c_id varchar(50) primary key,
c_name varchar(20) not null ,
t_id varchar(50) not null,
);
## 创建Teacher表
Create table Teacher(
t_id varchar(50) primary key,
t_name varchar(20) not null ,
);
## 创建Score 表
Create table Score(
s_id varchar(50) ,
c_id varchar(50) not null ,
s_score float not null,
);
2.建好表,就要添加数据啦,也很简单,主要是insert into 的用法
## 在Student表插入数据
insert into Student values('01','小王','1995-01-01','女')
insert into Student values('02','小郑','1995-01-02','男')
insert into Student values('03','小陈','1994-03-01','女')
insert into Student values('04','小树','1992-06-01','女')
insert into Student values('05','小新','1996-02-01','男')
insert into Student values('06','小李','1994-05-01','女')
## 在Course表插入数据
insert into Course values('01' , '语文' , '02')
insert into Course values('02' , '数学' , '01')
insert into Course values('03' , '英语' , '03')
## 在Teacher表插入数据
insert into Teacher values('01' , '陈真')
insert into Teacher values('02' , '王九')
insert into Teacher values('03' , '谢逊')
## 在Score表插入数据
insert into Score values('01' , '01' , '70')
insert into Score values('01' , '02' , '75')
insert into Score values('01' , '03' , '85')
insert into Score values('02' , '01' , '65')
insert into Score values('02' , '02' , '80')
insert into Score values('02' , '03' , '95')
insert into Score values('03' , '01' , '95')
insert into Score values('03' , '02' , '100')
insert into Score values('03' , '03' , '89')
insert into Score values('04' , '01' , '76')
insert into Score values('04' , '02' , '88')
insert into Score values('04' , '03' , '90')
insert into Score values('05' , '01' , '56')
insert into Score values('05' , '02' , '44')
insert into Score values('05' , '03' , '59')
insert into Score values('06' , '01' , '89')
insert into Score values('06' , '02' , '56')
insert into Score values('06' , '03' , '77')
3.开始进入正题,想几个题目,接下来就是看题解题的过程
1、选出三门成绩都大于85分的同学
# 1、第一种解题思路:通过将成绩大于85分的转化为1,其他转化为0,如果都大于85,则结果为3
select s_name
from
Student where s_id in(
select s_id
from Score
group by s_id --切记要使用group by 因为下面用到聚合
having count(s_score) = sum(
case
when s_score >85 then 1
when s_score <=85 then 0
end
)
)
# 2、第二种思路:通过在每个学生的,三门成绩中,只要最低分的那一门成绩大于85,则三科成绩一定大于85
select s_name
from
Student where s_id in(
select s_id
from Score
group by s_id --切记要使用group by 因为下面用到聚合
having min(s_score) >85
)
#3、第三种思路:思路转化,求三门成绩大于85,那么只要一门低于85的学生,则不是我们的目标,group by 不能用where,要用having
select s_name
from
Student where s_id not in(
select s_id
from Score --切记要使用group by 因为下面用到聚合
where s_score <85
)
2、查询01课程分数大于02课程的学生名字、性别和成绩
## 解题思路:首先将选出科目01 的成绩新建一列,然后挑出科目为02 的一列,然后将这两列的数据进行合并成一个表,这样就能对比这两列的数据了,这里比较关键的一步是对两个select语句进行left join ,当然两列的都有成绩时可以进行比较,当一列成绩为空的时候,也是我们要考虑的
select Score.s_id ,s_name ,c_id,s_sex,s_score
from Score , Student
where Score.s_id = Student.s_id
and Score.s_id in
(
select a.s_id from
(select s_id,
s_score as a_score
from Score
where c_id = 01) a
left join
(select s_id,
s_score as b_score
from Score
where c_id = 02 ) b
on b.s_id = a.s_id
where a_score > b_score or a_score is not null and b_score is null
)
## 如果是求课程01 小于课程02 ,的学生信息,则是用right join
select Score.s_id ,s_name ,c_id,s_sex,s_score
from Score , Student
where Score.s_id = Student.s_id
and Score.s_id in
(
select a.s_id from
(select s_id,
s_score as a_score
from Score
where c_id = 01) a
left join
(select s_id,
s_score as b_score
from Score
where c_id = 02 ) b
on b.s_id = a.s_id
where a_score < b_score or b_score is not null and a_score is null
)
3、求平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
# 1、解题思路:先找出平均成绩低于60分的同学的编号,再进行连接查询
select Score.s_id ,s_name ,
avg(s_score) as avg_score
from Score,Student
where Score.s_id = Student.s_id
and Score.s_id in
(
select s_id from
Score
group by s_id
having AVG(s_score) > 60
)
group by Score.s_id,s_name
# 2、另一种方法:进行聚合的时候切记要进行group by
select Score.s_id ,s_name ,
avg(s_score) as avg_score
from Score,Student
where Score.s_id = Student.s_id
group by Score.s_id,s_name
having avg(s_score) > 60
#3、第三种方法:left join
select Score.s_id ,s_name ,
avg(s_score) as avg_score
from Student
left join
Score
on Score.s_id = Student.s_id
group by s_name,Score.s_id
having avg(s_score) > 60
4、查询成绩和学生所有信息,并根据s_id 排序
select Score.s_id ,s_name ,
avg(s_score) as avg_score,
count(s_score) as cnt_score
from Student
left join
Score
on Score.s_id = Student.s_id
group by Score.s_id,s_name
order by s_id
5、查询名字中带有王的学生的信息,包括各科成绩
select a.s_id,c_id,s_name,s_sex,s_birth,c_id,s_score
from
((select *
from Student) a
left join
(select *
from Score) b
on a.s_id = b.s_id)
where s_name like '%王'
6、根据课程总分对学生由高分到低分排名,添加排名列
select Student.s_id,s_name,s_sex,s_birth,
sum(s_score) as sum_score,
RANK() over (order by sum(s_score) ) as rank1
from
Student,Score
Where Student.s_id = Score.s_id
group by Student.s_id,s_name,s_sex,s_birth
order by sum_score desc