MySQL操作之查询(DQL)

1. 投影、别名、筛选

-- MySQL 方言 if() 函数 ，处理上面的筛选
select 
    stu_name as 姓名,
    if(stu_sex,'男','女') as 性别,    # 建表时男默认1,女默认0；此处 if( ) 函数，if 真返回男, if 假返回女
    stu_birth as 生日
from tb_student
where stu_birth between '1980-1-1' and '1989-12-31';

2. 模糊查询   关键字 like 

通配符 wildcard ： % 匹配零个或任意多个

                                  _  精确匹配一个字符

-- 查询姓”杨“的学生姓名和性别(模糊)
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 '%嫣%';

并集：union  可以去重      union all 不去重

select stu_name from tb_student where stu_name like '%嫣%'
union
select stu_name from tb_student where stu_name like '%不%';

正则表达式查询 regular expression                                                                regexp 关键字

select stu_name from tb_student where stu_name regexp '[杨林].{2}';

3. 空值   null(表示恒不成立)

-- 查询没有录入家庭住址的学生姓名(空值)
select stu_name from tb_student where stu_addr is null or stu_addr='';
-- 查询录入了家庭住址的学生姓名(空值)
select stu_name from tb_student where stu_addr is not null and stu_addr<>'';

 4. 去重    distinct 关键字

-- 查询学生选课的所有日期(去重)
select distinct sel_date from tb_record ;
-- 查询学生的家庭住址(去重)
select distinct stu_addr from tb_student where stu_addr is not null;

5. 排序  

升序 ( ascending )          asc 关键字

降序 ( descending)        desc 关键字

                                           order by 关键字

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

年龄：curdate  获取当前日期     datediff  计算时间差 ( 以天为单位 )     floor / ceil  向下 / 向上取整
select
    stu_name,
    stu_birth,
    floor ( datediff ( curdate( ) , stu_birth ) / 365 ) as stu_age
from tb_student
where stu_sex=1
order by stu_age desc;

6. 聚合函数

-- 查询编号为1111的课程考试成绩最高分
select max(score) from tb_record where cou_id=1111;                                               max()
-- 查询学号为1001的学生考试成绩最低分
select min(score) from tb_record where stu_id=1001;                                                min()
-- 查询学号为1001的学生考试成绩平均分
select avg(score) from tb_record where stu_id=1001;                                                avg()
select sum(score) / count(score) from tb_record where stu_id=1001;                  sum() / count()
-- 查询学号为1001的学生考试成绩平均分,如果有null值,null值算0分
select sum(score) / count(*) from tb_record where stu_id=1001;
select sum(score) / count(stu_id) from tb_record where stu_id=1001;
select avg(ifnull(score,0)) from tb_record where stu_id=1001;
-- ifnull ---> 如果遇到空值，将其替换为指定的值，MySQL方言                              ifnull() 方言

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

7. 分组     group by

-- 查询男女学生的人数(分组)
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;

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

-- 分组以前的筛选使用where子句，分组以后的筛选使用having子句                     where / having
-- 查询1111,2222,3333三门课平均成绩大于90分的学生学号和成绩
select 
    stu_id as 学号,
    round(avg(score),2) as 平均分
from tb_record
where cou_id in (1111,2222,3333)
group by stu_id
having 平均分>=90;

8. 子查询（嵌套查询）

-- 嵌套查询：把一个select 的结果作为另一个select 的一部分来使用
-- 嵌套查询通常也称之为子查询,在查询语句中有两个或多个select
select stu_name from tb_student
    where stu_birth=(
        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(*)>2
);

9. 连接查询（联结查询）

-- 查询学生姓名、课程名称以及成绩(连接查询/联结查询)
select stu_name,cou_name,score
from tb_record as t1,tb_student as t2,tb_course as t3         # as 可以不写
where 
    t1.stu_id=t2.stu_id and 
    t1.cou_id=t3.cou_id and
    score is not null;

select stu_name,cou_name,score                                                            inner join...on...
from tb_student t1 inner join tb_record t2
on t1.stu_id=t2.stu_id inner join tb_course t3
on t2.cou_id=t3.cou_id where score is not null;

select stu_name,cou_name,score                                                            natural join
from tb_student natural join tb_course natural join tb_record
where score is not null

分页查询 ：                                                                                        limit 用于MySQL、HiveSQL

select stu_name,cou_name,score
from tb_student natural join tb_course natural join tb_record
where score is not null
order by score desc limit 5;

select stu_name,cou_name,score
from tb_student natural join tb_course natural join tb_record
where score is not null
order by score desc limit 5 offset 6;     # 跳过前6个，再取5个                          limit...offset...

select stu_name,cou_name,score
from tb_student natural join tb_course natural join tb_record
where score is not null
order by score desc limit 6,5;      # 跳过前6个，再取5个

10.子查询和连接查询

select stu_name,avg_score
from
    tb_student t1,
    (
    select stu_id,round(avg(score),1) as avg_score
    from tb_record group by stu_id
    ) t2
where t1.stu_id=t2.stu_id;

11.左外连接和子查询

内连接：查询左右两表满足连接条件的数据。
外连接：左外、右外、全外
左外连接：确保左表(现在join前面的表)中的所有记录都能查出来，不满足连接条件的补充null。
右外连接：确保右表(现在join后面的表)中的所有记录都能查出来，不满足连接条件的补充null。
全外连接：确保左表和右表中的所有记录都能查出来，不满足连接条件的补充null。

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

select stu_name, ifnull(total, 0) as total 
from tb_student t1 left outer join (
    select stu_id, count(*) as total from tb_record 
    group by stu_id
) t2 on t1.stu_id=t2.stu_id;

-- MySQL不支持全外连接
-- 可以通过左外连接与右外连接求并集运算得到全外连接的结果