DQL | Maturest

# DQL

# 查询所有学生的信息
select `id`,`name`,`phone`,`gender`,`birthday`,`address`,`college_id` from student;

# 查询学生的学号，姓名和籍贯
select `id` as '学号',`name` as '姓名',`address` as '籍贯' from student;

# 查询所有课程的名称以及学分
select `name` as '课程',`credit` as '学分' from course;

# 查询所有女生的姓名和出生日期
select `name` as '姓名',`birthday` as '出生日期' from student where gender = 0;

# 查询籍贯为四川成都的女学生的姓名和出生日期
select `name` as '姓名',`birthday` as '出生日期' from student where gender = 0 and address = '四川成都';

# 查询籍贯为四川成都或者性别是女的学生
select `name` as '姓名',`birthday` as '出生日期' from student where gender = 0 or address = '四川成都';

# 查询所有80后学生的姓名，性别和出生日期
select `name` as '姓名',`gender` as '性别',`birthday` as '出生日期' from student where birthday between '1980-01-01' and '1989-12-31';

# 查询学分大于2的可成的名称和学分
select `name`,`credit` from course where `credit` > 2;

# 查询学分是奇数的课程名称和学分
select `name`,`credit` from course where `credit` mod 2 != 0;

# 查询选择了1111的课程，考试成绩并且在90分以上的学生学号
select student_id from record where course_id = 1111 and score > 90;

# 查询名字叫杨过的学生的姓名和性别
select `name` as '姓名', case `gender` when 1 then '男' else '女' end as '性别' from student where `name` = '杨过';

select `name` as '姓名', if(gender,'男','女') as '性别' from student where `name` = '杨过';

# 查询姓杨的学生姓名和性别（模糊匹配）
select `name` as '姓名', case `gender` when 1 then '男' else '女' end as '性别' from `student` where `name` like '杨%';

# 查询学号最后一位是3的学生的学号和姓名
select `id` as '学号',`name` as '姓名' from `student` where id like '%3';

# 查询名字中有不或者嫣字的学生的学号和姓名
select `id` as '学号',`name` as '姓名' from `student` where `name` like '%不%' or `name` like '%嫣%';

select `id` as '学号',`name` as '姓名' from `student` where `name` like '%不%' union select `id` as '学号',`name` as '姓名' from `student` where `name` like '%嫣%';

# 查询姓杨或者姓林的名字三个字的学号和姓名
select `id` as '学号',`name` as '姓名' from `student` where `name` like '杨__' or `name` like '林__';

select `id` as '学号',`name` as '姓名' from `student` where `name` regexp '[杨林][\\u4e00-\\u9fa5]{2}';

# 查询没有录入籍贯的学生姓名
select `name` as '姓名' from `student` where trim(`address`) = '' or `address` is null;

# 查询录入了籍贯的学生姓名
select `name` as '姓名' from `student` where trim(`address`) != '' and `address` is not null;

# 查询学生选课的所有日期
select distinct `created_at` from  `record`;

# 查询学生的籍贯
select distinct `address` from student where trim(`address`) != '' and `address` is not null;

# 查询男学生的姓名和生日按年龄从大到小排列
select `name` as '姓名',`birthday` as '生日' from `student` where gender = 1 order by `birthday` asc;

# 查询男学生的姓名和年龄按年龄从大到小排列
select `name` as '姓名', floor(datediff(curdate(),`birthday`) / 365)  as '年龄' from `student` where gender = 1 order by `birthday` asc;

# 查询年龄最大的学生的出生日期
select min(`birthday`) from `student`;

# 查询年龄最小的学生的出生日期
select max(`birthday`) from `student`;

# 查询编号为1111的课程考试成绩的最高分
select max(`score`) from `record` where `course_id` = 1111;

# 查询学号为1001的学生考试成绩的最低分、最高分、平均分、标准差、方差(聚合函数)
select min(`score`) as '最低分', max(`score`) as '最高分',round(avg(`score`),1) as '平均分',stddev(`score`) as '标准差',variance(`score`) as '方差' from `record` where `student_id` = 1001;

# 查询学号为1001的学生考试成绩平均分，如果又null值,null值算0分
select round(sum(`score`) / count(*),1) as '平均分' from record where `student_id` = 1001;

# 查询男女学生的人数
select case gender when 1 then '男' else '女' end as '性别',count(*) as '人数' from `student` group by gender;

# 查询每个学院学生人数
select `college_id` as '学院编号',count(*) as '人数' from `student` group by `college_id` with rollup;

# 查询每个学院的男女学生的人数
select `college_id` as '学院编号',case gender when 1 then '男' else '女' end as '性别',count(*) as '人数' from `student` group by `college_id`,`gender`;

# 查询每个学生的学号和平均成绩
select `student_id` as '学号',round(avg(`score`),1) as '平均分' from `record` group by `student_id`;

# 查询平均成绩大于等于90分的学生的学号和平均成绩
select `student_id` as '学号',round(avg(`score`),1) as 'avg_score' from `record` group by `student_id` having avg_score >= 90;

# 查询1111,2222,3333三门课程平均成绩大于等于90分的学生的学号和平均成绩
select `student_id` as '学号',round(avg(`score`),1) as 'avg_score' from `record` where course_id in (1111,2222,3333) group by `student_id` having avg_score >= 90 order by avg_score asc;

# 查询年龄最大的学生的姓名(子查询)
select `name` as '姓名' from `student` where birthday = (select min(birthday) from `student`);

select `name` as '姓名' from `student` order by birthday asc limit 1;

# 查询选了两门以上课程的学生姓名(子查询和集合运算)
select `name` as '姓名' from `student` where id in (select `student_id` from `record` group by student_id having count(*)  > 2);

# 查询学生的姓名、生日、和所在学院名称
select s.name as '姓名',`birthday` as '生日',c.name as '学院名称' from `student` as s,`college` as c where s.college_id = c.id;

select s.name as '姓名',`birthday` as '生日',c.name as '学院名称' from `student` as s inner join `college` as c on s.college_id = c.id;

select s.name as '姓名',`birthday` as '生日',c.name as '学院名称' from `student` as s left join `college` as c on s.college_id = c.id;


# 查询学生姓名，课程名称，以及成绩(表连接)

select s.name as '姓名', c.name as '课程名称',r.score as '成绩' from `student` as s,`course` as c,`record` as r where s.id = r.student_id and c.id = r.course_id and score is not null; # 隐式的内连接

# 与上面相同，但更容易理解
SELECT s.name AS '姓名', c.name AS '课程名称', r.score AS '成绩'  
FROM `student` AS s  
INNER JOIN `record` AS r ON s.id = r.student_id  
INNER JOIN `course` AS c ON r.course_id = c.id  
WHERE r.score IS NOT NULL;
介个连接：
NATURAL JOIN 基于两个表共有的列名自动进行连接，不推荐使用。
INNER JOIN 只返回两个表中匹配的记录。
LEFT JOIN 保证左表的所有记录都出现在结果中，如果右表中没有匹配则显示NULL。
RIGHT JOIN 保证右表的所有记录都出现在结果中，如果左表中没有匹配则显示NULL。
Cross Join（笛卡尔积）是SQL中一种特殊的连接类型，它不需要任何连接条件来匹配两个表中的行。在执行Cross Join时，数据库会将第一个表中的每一行与第二个表中的每一行进行组合，生成的结果集行数等于两个表行数的乘积。
← DML DCL →