二阶段day3

二阶段day3

MySql查询部分案例

trim()：修剪字符串左右两端的空格，对多空字符串进行处理

17查询没有录入籍贯的学生姓名(空值处理)

select  stu_name as 姓名
		,stu_addr as 籍贯
from tb_student
where stu_addr is null or stu_addr = '';
1
2
3
4

18查询录入了籍贯的学生姓名(空值处理)

select stu_name as 学生姓名
from tb_student
where stu_addr is not null and stu_addr <> '';
1
2
3

19查询学生选课的所有日期(去重)

select distinct sel_date as 选课日期
from tb_record;
1
2

20查询学生的籍贯(去重)

select distinct stu_addr as 籍贯
from tb_student;
1
2

21查询男学生的姓名和生日按年龄从大到小排列(排序)

首先年龄的计算：

select curdate();
select datediff(curdate(),'2018-12-2')/365;
-- 向下取整
select floor(datediff(curdate(),'2018-12-2')/365);
-- 向上取整
select ceil(datediff(curdate(),'2018-12-2')/365);
1
2
3
4
5
6

年龄无关月份，所以我直接年份相减

select stu_name,stu_birth from tb_student 
where stu_sex = 1 
order by year(now())-year(stu_birth); 
1
2
3

22补充：将上面的生日换算成年龄(日期函数、数值函数)

select stu_name,case stu_birth 
when stu_birth then year(now())-year(stu_birth) else '' end 
from tb_student
where stu_sex = 1 
order by year(now())-year(stu_birth);
1
2
3
4
5

23查询年龄最大的学生的出生日期(聚合函数)

select min(stu_birth )
from tb_student;
1
2

24查询年龄最小的学生的出生日期(聚合函数)

select max(stu_birth )
from tb_student;
1
2

25查询编号为1111的课程考试成绩的最高分(聚合函数)

select max(score)
from tb_record
where cou_id=1111;
1
2
3

26查询学号为1001的学生考试成绩的最低分(聚合函数)

select stu_id
from tb_record
where cou_id=1111 and score>90;
1
2
3

27查询学号为1001的学生考试成绩的平均分(聚合函数)

select avg(score)
from tb_record
where stu_id=1001;
1
2
3

28查询学号为1001的学生考试成绩的平均分，如果有null值，null值算0分(聚合函数)

-- 方法1
select avg(ifnull(score,0))
from tb_record
where stu_id=1001;
-- 注意了：因为ifnull仅仅可以mysql中使用，所以使用如下方法，coalesce返回第一个非null的值
select avg(coalesce(score,0))
from tb_record
where stu_id=1001;

-- 方法2
select sum(score) / count(*)
from tb_record
where stu_id=1001;
1
2
3
4
5
6
7
8
9
10
11
12
13

29查询学号为1001的学生考试成绩的标准差(聚合函数)

-- 样本方差
select stddev_samp(score)
from tb_record
where stu_id=1001;

-- 总体方差,stddev 和 stddev_pop一样
select stddev_pop(score)
from tb_record
where stu_id=1001;

-- 也可以是方差开方，结果和总体想、标准差一样
select sqrt(variance(score))
from tb_record
where stu_id=1001;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

30查询男女学生的人数(分组和聚合函数)

select count(stu_id) 
from tb_student
group by col_id;
1
2
3

31查询每个学院学生人数(分组和聚合函数)

select  stu_name as 姓名
from tb_student
where stu_name like '%不%' or stu_name like '%嫣%';

-- 或者,方法2
select  stu_name as 姓名
from tb_student
where stu_name like '%不%'
union
-- union并集的意思，mysql只支持并集

select  stu_name as 姓名
from tb_student
where stu_name like '%嫣%';
-- union all,不去掉重复元素
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

32查询每个学院男女学生人数(分组和聚合函数)

select stu_sex,count(stu_id) 
from tb_student
group by col_id,stu_sex
with rollup;
1
2
3
4

33查询每个学生的学号和平均成绩(分组和聚合函数)

select stu_id,avg(score)
from tb_record
group by stu_id;
1
2
3

34查询平均成绩大于等于90分的学生的学号和平均成绩

select stu_id,avg(score)
from tb_record
group by stu_id having avg(score) >=90;
1
2
3

35查询1111、2222、3333三门课程平均成绩大于等于90分的学生的学号

select distinct stu_id
from tb_record
where cou_id =1111 or cou_id =2222 or cou_id =3333
-- 分组前放在where里，分组后放在having里
group by stu_id 
having avg(score) >=90;
1
2
3
4
5
6

36查询年龄最大的学生的姓名(子查询/嵌套查询)

select stu_name 
from tb_student
where stu_birth in (select min(stu_birth) from tb_student);
1
2
3

37查询选了两门以上的课程的学生姓名(子查询/分组条件/集合运算)

select stu_name 
from tb_student
where stu_id in (select stu_id from tb_record group by stu_id having count(*)>2);

-- 或者
select stu_name 
from tb_student
where stu_id = any(select stu_id from tb_record group by stu_id having count(*)>2);
1
2
3
4
5
6
7
8

38查询学生的姓名、生日和所在学院名称

select stu_name
	  ,stu_birth
      ,col_name
from tb_student,tb_college
where tb_student.col_id = tb_college.col_id;

-- 方法2：
select stu_name
	  ,stu_birth
      ,col_name
from tb_student natural join tb_college;

-- 方法3：
select stu_name
	  ,stu_birth
      ,col_name
from tb_student inner join tb_college
on tb_student.col_id = tb_college.col_id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

39查询学生姓名、课程名称以及成绩(连接查询/联结查询)

select stu_name
,cou_name
,ifnull(score,0)
from tb_student a 
inner join tb_record c on a.stu_id =c.stu_id 
inner join tb_course b on b.cou_id = c.cou_id;

1
2
3
4
5
6
7

补充：上面的查询结果按课程和成绩排序取前5条数据(分页查询)

select stu_name
,cou_name
,ifnull(score,0)
from tb_student a 
inner join tb_record c on a.stu_id =c.stu_id 
inner join tb_course b on b.cou_id = c.cou_id
order by score desc,cou_name asc
limit 5;
1
2
3
4
5
6
7
8

补充：上面的查询结果按课程和成绩排序取第6-10条数据(分页查询)

select stu_name
,cou_name
,ifnull(score,0)
from tb_student a 
inner join tb_record c on a.stu_id =c.stu_id 
inner join tb_course b on b.cou_id = c.cou_id
order by score desc,cou_name asc
limit 6,10;

-- 方法2
select stu_name
,cou_name
,ifnull(score,0)
from tb_student a 
inner join tb_record c on a.stu_id =c.stu_id 
inner join tb_course b on b.cou_id = c.cou_id
order by score desc,cou_name asc
limit 5
offset 5;
-- 跳过5条，取5个
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

假设，当我想随机抽样

-- 先随机排序
select stu_name
,cou_name
,ifnull(score,0)
from tb_student a 
inner join tb_record c on a.stu_id =c.stu_id 
inner join tb_course b on b.cou_id = c.cou_id
order by rand(now())
-- 随机排序成功
-- 随机抽样
limit 5;
-- 随机抽样成功
1
2
3
4
5
6
7
8
9
10
11
12

40查询选课学生的姓名和平均成绩(子查询和连接查询)

select stu_name
,avg_s
from tb_student as a 
inner join (select stu_id
					,avg(score) as avg_s
			from tb_record group by stu_id) as b
on a.stu_id = b.stu_id;
1
2
3
4
5
6
7

41查询学生的姓名和选课的数量

select stu_name
,c_count
from tb_student
natural join 
(select stu_id
		,count(*) as c_count
        from tb_record group by stu_id) as c;
1
2
3
4
5
6
7

42查询每个学生的姓名和选课数量(左外连接和子查询)

select stu_name
		,coalesce(c_count,0) as 数量
		-- coalesce将空值处理为0
from tb_student as name_s
left join (select stu_id
		,count(*) as c_count
        from tb_record group by stu_id) as c
        on name_s.stu_id = c.stu_id;
1
2
3
4
5
6
7
8

43查询没有选课的学生的姓名

select stu_name
		,coalesce(c_count,0) as 数量
from tb_student as name_s
left join (select stu_id
		,count(*) as c_count
        from tb_record group by stu_id) as c
        on name_s.stu_id = c.stu_id
where c.stu_id is null;
1
2
3
4
5
6
7
8