SQL查询练习篇

用到的表名和字段：

1. 学生表
   Student(s_id, s_name, s_birth, s_sex)
2. 课程表
   Course(c_id, c_name, t_id)
3. 教师表
   Teacher(t_id, t_name)
4. 成绩表
   Score(s_id, c_id, s_score)

学生表：

s_id	s_name	s_birth	s_sex
01	赵雷	1990-01-01	男
02	钱电	1990-12-21	男
03	赵雷	1990-05-20	男
04	赵雷	1990-08-06	男
05	赵雷	1990-12-01	女
06	赵雷	1990-03-01	女
07	赵雷	1990-07-01	女
08	赵雷	1990-01-20	女

课程表：

c_id	c_name	t_id
01	语文	02
02	数学	01
03	英语	03

教师表：

t_id	t_name
01	张三
02	李四
03	王五

成绩表：

s_id	c_id	s_score
01	01	80
01	02	90
01	03	99
02	01	70
02	02	60
02	03	80
03	01	80
03	02	80
03	03	80
04	01	50
04	02	30
04	03	20
05	01	76
05	02	87
06	01	31
06	03	34
07	02	89
07	03	98

"""
sql语句不区分大小写，而且缩写也可
"""
//查询表student的所有内容
select * from student;
"1.查询01课程比02课程成绩高的学生的信息及课程分数"
//方法一：自连接
select 
a.s_id,
a.s_score s01,
b.s_score s02
from
score a, score b
where
a.c_id="01"
and b.c_id="02"
and a.s_id=b.s_id
and a.s_score>b.s_score;

查询结果：

s_id	s01	s02
02	70	60
04	50	30

将 "a.s_id," 改为 "c.*"
from后加 ", student c" 语句
条件部分再加一个 "and c.s_id=a.s_id" 语句

查询结果：

s_id	s_name	s_birth	s_sex	s01	s02
02	钱电	1990-12-21	男	70	60
04	李云	1990-08-06	男	50	30

//方法一：长型数据变成宽型数据
select 
	s.*
	t.s01, t.s02
from
	(select
		a.s_id,
		max(case when a.c_id="01" then a.s_score end) s01,
		max(case when a.c_id="02" then a.s_score end) s02
		from score a
		group by
			a.s_id) t, Student s
where
	t.s01>t.s02
and t.s_id=s.s_id

表t部分的查询结果：

s_id	s01	s02
01	80	90
02	70	60
03	80	80
04	50	30
05	76	87
06	31	NULL
07	NULL	89

"2.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩"
# 方法一：子查询
select  
	a.s_id,
	(select s_name from student s where s.s_id=a.s_id) s_name,
	avg(a.s_score) avg_s
from
	score a
group by
	a.s_id
having
	avg(a.s_score)>=60
# 方法二：两个表连接
select  
	s.s_id,
	s.s_name,
	avg(a.s_score) avg_s
from
	score a, student s
where
	a.s_id=s.s_id
group by
	a.s_id
having
	avg(a.s_score)>=60
"2.1 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩"
# 为了将无数据的学生找出来，需要用外连接
select  
	s.s_id,
	s.s_name,
	//如果为空返回0
	if(avg(a.s_score), 0) avg_s
from
	score a
right join
	student s
on
	a.s_id=s.s_id
group by
	a.s_id
having
	ifnull(avg(a.s_score), 0) < 60
"3.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩"
select
	b.s_id,
	b.s_name,
	count(a.c_id) cnt_s,
	//如果值为null返回0
	ifnull(sum(a.s_score), 0) sum_s
from
	score_a
right join
	student b
on 
	a.s_id=b.s_id
group by
	b.s_id
	b.s_name
"4.查询李姓老师的数量"
select count(t_name) cnt_t from teacher a where a.t_name like "李%";
"5.查询学过张三老师授课的同学的信息"
select 
	c.*
from
	course a.score b.student c.teacher d
where
	d.t_id=a.t_id
and a.c_id=b.c_id
and b.s_id=c.s_id
and d.t_name="张三";
"6.查询没有学过张三老师授课的同学的信息"
select * from student where s_id not in (
	select 
		c.*
	from
		course a.score c.teacher d
	where
		d.t_id=a.t_id
	and a.c_id=b.c_id
	and d.t_name="张三";)

select * from student where s_id not exists (
	//1可以是任意字段
	select 1 from
		(select 
			c.*
		from
			course a.score c.teacher d
		where
			d.t_id=a.t_id
		and a.c_id=b.c_id
		and d.t_name="张三";) t
	where t.s_id=student.s_id);
"7.查询学过学过01和02课程的学生信息"
select 
from
	score.a score.b student c
where
	a.c_id="01"
and b.c_id="02"
and b.c_id=b.s_id
and a.s_id=b.s_id
and a.s_id=c.s_id
"8.查询学过学过01但没学过02课程的学生信息"
select s.* from
	(select 
		s_id,
		max(case when c_id="01" then s_score end) s01,
		max(case when c_id="02" then s_score end) s02,
	from
		score a
	group by
		a.s_id) t.student s
where 
	t.s_id=s.s_id
and t.s01 is not null
and t.s02 is null

更新中…