use school;
-- 查询所有学生信息
select * from tb_student;
select stu_id, stu_name, stu_sex, stu_birth, stu_addr, col_id 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;
-- 查询所有80后学生的姓名、性别和出生日期(筛选)
select
stu_name as 姓名,
case stu_sex when 1 then '男' else '女' end as 性别,
stu_birth as 生日
from tb_student
where stu_birth between '1980-1-1' and '1989-12-31';
-- SQL方言（因为其他的数据库可能没有if函数）
-- 例如：Oracle中做同样事情的函数叫做decode
select
stu_name as 姓名,
if(stu_sex, '男', '女') as 性别,
stu_birth as 生日
from tb_student
where stu_birth between '1980-1-1' and '1989-12-31';
-- 查询所有80后女学生的姓名和出生日期
select stu_name, stu_birth from tb_student
where stu_birth >= '1980-1-1' and stu_birth <= '1989-12-31' and stu_sex=0;
-- 查询所有女学生或80后的所有学生的姓名和出生日期
select stu_name, stu_birth from tb_student
where stu_birth >= '1980-1-1' and stu_birth <= '1989-12-31' or stu_sex=0;
-- 查询姓”杨“的学生姓名和性别(模糊)
-- 在SQL中通配符%可以匹配0个或任意多个字符
select stu_name, stu_sex from tb_student where stu_name like '杨%';
-- 查询姓”杨“名字两个字的学生姓名和性别(模糊)
-- 在SQL中通配符_可以精确匹配1个字符
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 stu_name like '%不%'
union
select stu_name from tb_student where stu_name like '%嫣%';
-- 并集的情况下保留重复的
select stu_name from tb_student where stu_name like '%不%'
union all
select stu_name from tb_student where stu_name like '%嫣%';
-- 正则表达式模糊查询
select stu_name, stu_sex from tb_student where stu_name regexp '杨.';
select stu_name, stu_sex from tb_student where stu_name regexp '^杨.$';
-- 查询没有录入家庭住址的学生姓名(空值)
-- null作任何运算结果也是产生null，null相当于是条件不成立
select stu_name from tb_student where stu_addr is null;
select stu_name from tb_student where stu_addr<=>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 where stu_addr is not null;
-- 查询男学生的姓名和生日按年龄从大到小排列(排序)
-- asc（默认，可以不写） - 升序（从小到大），desc - 降序（从大到小）
-- 如果有生日相同的可以在后面加逗号，相同的再以后面的条件排序
select stu_name, stu_birth from tb_student
where stu_sex=1 order by stu_birth asc;
select stu_name, stu_birth from tb_student
where stu_sex=1 order by stu_birth desc;
-- 查询年龄最大的学生的出生日期(聚合函数)
select now() from dual; -- 当前时间
select curdate() from dual; -- 当前日期
select
min(stu_birth) as 生日,
floor(datediff(curdate(), min(stu_birth))/365) as 年龄
from tb_student;
-- 查询年龄最小的学生的出生日期(聚合函数)
select
max(stu_birth) as 生日,
floor(datediff(curdate(), max(stu_birth))/365) as 年龄
from tb_student;
-- 查询所有考试的平均成绩
-- 聚合函数在遇到null值会做忽略的处理
-- 如果做计数操作，建议使用count(*)，这样才不会漏掉空值
select avg(score) from tb_record;
select sum(score) / count(score) from tb_record;
select sum(score) / count(*) from tb_record;
-- 查询男女学生的人数(分组和聚合函数)
-- SAC（Split - Aggregate - Combine)
select
if(stu_sex, '男', '女') as 性别,
count(*) as 人数
from tb_student group by stu_sex;
-- 统计每个学院男女学生人数
select
col_id as 学院,
if(stu_sex, '男', '女') as 性别,
count(*) as 人数
from tb_student group by col_id, stu_sex;
-- 查询课程编号为1111的课程的平均成绩(筛选和聚合函数)
select avg(score) from tb_record where cid=1111;
-- 查询学号为1001的学生所有课程的平均分(筛选和聚合函数)
select avg(score) from tb_record where sid=1001;
select count(distinct stu_addr) from tb_student where stu_addr is not null;
-- 查询每个学生的学号和平均成绩(分组和聚合函数)
select sid, round(avg(score), 2) from tb_record group by sid;
-- 查询平均成绩大于等于90分的学生的学号和平均成绩
-- 分组以前的数据筛选使用where子句，分组以后的数据筛选使用having子句
select
sid as 学号,
round(avg(score), 2) as 平均分
from tb_record
group by sid having 平均分>=90;