【作业】2022.5.24 MySQL 查操作

use school;

-- 查询所有学生的所有信息
select * from tb_student;

-- 查询学生的学号、姓名和籍贯(投影)
select stu_id, stu_name, stu_addr from tb_student;

-- 查询所有课程的名称及学分(投影和别名)
select cou_name as 课程名称, cou_credit as 课程学分 from tb_course;

-- 查询所有女学生的姓名和出生日期(筛选)
select stu_name, stu_birth from tb_student where stu_sex=0;

-- 查询籍贯为“四川成都”的女学生的姓名和出生日期(筛选)
select stu_name, stu_birth from tb_student where stu_sex=0 and stu_addr='四川成都';

-- 查询籍贯为“四川成都”或者性别是女的学生
select * from tb_student where stu_sex=0 or stu_addr='四川成都';

-- 查询所有80后学生的姓名、性别和出生日期(筛选)
select stu_name, stu_sex, stu_birth from tb_student where stu_birth>='1980-01-01';

-- 查询学分大于2的课程的名称和学分(筛选)
select cou_name, cou_credit from tb_course where cou_credit>2;

-- 查询学分是奇数的课程的名称和学分(筛选)
select cou_name, cou_credit from tb_course where cou_credit % 2 = 1;

-- 查询选择选了1111的课程考试成绩在90分以上的学生学号(筛选)
select stu_id from tb_record where cou_id = 1111 and score > 90;

-- 查询名字叫“杨过”的学生的姓名和性别
select stu_name, stu_sex from tb_student where stu_name = '杨过';

-- 查询姓“杨”的学生姓名和性别(模糊)
select stu_name, stu_sex from tb_student where stu_name like '杨%';

-- 查询姓“杨”名字两个字的学生姓名和性别(模糊)
select stu_name, stu_sex from tb_student where stu_name like '杨_';

-- 查询姓“杨”名字三个字的学生姓名和性别(模糊)
select stu_name, stu_sex from tb_student where stu_name like '杨__';

-- 查询名字中有“不”字或“嫣”字的学生的姓名(模糊)
select stu_name from tb_student 
where stu_name like '%不%' or stu_name like '%嫣%';

-- 查询姓“杨”或姓“林”名字三个字的学生的姓名(正则表达式模糊查询)
select stu_name from tb_student where regexp_like (stu_name, '[杨林]..');

-- 查询没有录入籍贯的学生姓名(空值处理)
select stu_name from tb_student where stu_addr is null;

-- 查询录入了籍贯的学生姓名(空值处理)
select stu_name from tb_student where stu_addr is not null;

-- 查询学生选课的所有日期(去重)
select distinct sel_date from tb_record;

-- 查询学生的籍贯(去重)
select distinct stu_addr from tb_student;

-- 查询男学生的姓名和生日按年龄从大到小排列(排序)
select stu_name, stu_birth from tb_student 
where stu_sex = 1 
order by stu_birth;

-- 补充：将上面的生日换算成年龄(日期函数、数值函数)
select stu_name, timestampdiff(year, stu_birth, curdate()) from tb_student
where stu_sex = 1
order by stu_birth;

-- 查询年龄最大的学生的出生日期(聚合函数)
select min(stu_birth) from tb_student;

-- 查询年龄最小的学生的出生日期(聚合函数)
select max(stu_birth) from tb_student;

-- 查询编号为1111的课程考试成绩的最高分(聚合函数)
select max(score) from tb_record where cou_id = 1111;

-- 查询学号为1001的学生考试成绩的最低分(聚合函数)
select min(score) from tb_record where stu_id = 1001;

-- 查询学号为1001的学生考试成绩的平均分(聚合函数)
select avg(score) from tb_record where stu_id = 1001;

-- 查询学号为1001的学生考试成绩的平均分，如果有null值，null值算0分(聚合函数)
select avg(ifnull(score, 0)) from tb_record where stu_id = 1001;

-- 查询学号为1001的学生考试成绩的标准差(聚合函数)
select stddev(score) from tb_record where stu_id = 1001;

-- 查询男女学生的人数(分组和聚合函数)
select stu_sex as 性别, count(stu_sex) as 人数 
from tb_student group by stu_sex;

-- 查询每个学院学生人数(分组和聚合函数)
select col_id, count(col_id) 
from tb_student group by col_id;

-- 查询每个学院男女学生人数(分组和聚合函数)
select col_id, stu_sex, count(stu_sex) 
from tb_student group by col_id, stu_sex;

-- 查询每个学生的学号和平均成绩(分组和聚合函数)
select stu_id, avg(score) from tb_record group by stu_id;

-- 查询平均成绩大于等于90分的学生的学号和平均成绩
select stu_id, avg(score) from tb_record group by stu_id having avg(score) > 90;

-- 查询1111、2222、3333三门课程平均成绩大于等于90分的学生的学号和平均成绩
select stu_id, avg(score) from tb_record where cou_id in (1111, 2222, 3333)
group by stu_id having avg(score) > 90 and count(stu_id) = 3;

-- 查询年龄最大的学生的姓名(子查询/嵌套查询)
select stu_name from tb_student where stu_birth in 
(select min(stu_birth) from tb_student);

-- 查询选了两门以上的课程的学生姓名(子查询/分组条件/集合运算)
select stu_name from tb_student where stu_id in
(select stu_id from tb_record group by stu_id having count(stu_id) > 2);

-- 查询学生的姓名、生日和所在学院名称
select stu_name, stu_birth, col_name from tb_college 
join tb_student on tb_student.col_id=tb_college.col_id; 

-- 查询学生姓名、课程名称以及成绩(连接查询/联结查询)
select stu_name, cou_name, score from tb_record
join (tb_student, tb_course) on tb_student.stu_id=tb_record.stu_id and tb_course.cou_id=tb_record.cou_id;

-- 补充：上面的查询结果取前5条数据(分页查询)
select stu_name, cou_name, score from tb_record
join (tb_student, tb_course) on tb_student.stu_id=tb_record.stu_id and tb_course.cou_id=tb_record.cou_id
limit 5 offset 0;

-- 补充：上面的查询结果取第6-10条数据(分页查询)
select stu_name, cou_name, score from tb_record
join (tb_student, tb_course) on tb_student.stu_id=tb_record.stu_id and tb_course.cou_id=tb_record.cou_id
limit 5 offset 5;

-- 补充：上面的查询结果取第11-15条数据(分页查询)
select stu_name, cou_name, score from tb_record
join (tb_student, tb_course) on tb_student.stu_id=tb_record.stu_id and tb_course.cou_id=tb_record.cou_id
limit 5 offset 10;

-- 查询选课学生的姓名和平均成绩(子查询和连接查询)
select stu_name, avg(score) from tb_record 
join tb_student on tb_student.stu_id = tb_record.stu_id
group by tb_record.stu_id;

-- 查询学生的姓名和选课的数量
select stu_name, count(tb_record.stu_id) from tb_record 
join tb_student on tb_student.stu_id = tb_record.stu_id
group by tb_record.stu_id;

-- 查询每个学生的姓名和选课数量(左外连接和子查询)
 select stu_name, count(tb_record.stu_id) from tb_record 
join tb_student on tb_student.stu_id = tb_record.stu_id
group by tb_record.stu_id;

-- 补充练习
-- 01. 查询课程1111比课程2222成绩高的所有学生的学号

-- 02. 查询没有学过张三丰老师的课程的同学的学号和姓名
select stu_id, stu_name from tb_student where stu_id not in(
select tb_student.stu_id from tb_student 
join (tb_teacher, tb_record, tb_course) on 
tb_teacher.tea_id = tb_course.tea_id and 
tb_student.stu_id = tb_record.stu_id and
tb_course.cou_id = tb_record.cou_id
where tea_name = '张三丰');