简单的数据查询

单表查询：

1、基本查询：
    查询所有记录的所有字段：
        select * from 表名;
    查询指定的列：
        select 列名1,列名2,列名3,.... from 表名;
        select name,age,gender from student;
    去重：
        select distinct name from studnet;
    列运算：
        select `name`,`age`,`age`*10 from student;
        说明：如果列中有null值，null和任何类型做运算都为null。IFNULL。
    别名：出现在查询结果的临时表中（虚拟表中）
        select `id` `编号`,`name` `姓名`,`age` `年龄`,`gender` `性别` from student;
        select `id` as `编号`,`name` as `姓名`,`age` as `年龄`,`gender` as `性别` from student;    
    条件控制：
        select * from student where id = 3;
        select * from student where id in (1,3,5,7);
        select * from student where id >= 5;
        select * from student where id BETWEEN 7 AND 10;
        SELECT * from student where id = 5 or name = '王兴'
    模糊查询：
        select * from student where name LIKE '马%';
        %：代表任意个数的字符。
        _：代表一个字符。
        select * from student where name LIKE '%马%';
    排序：order by
        升序：
            select * from student order by age asc;
            order by实际上默认就是升序排列，asc可以省略。        
        降序：
            select * from student order by age desc;
        多列排序
            select * from student order by age desc,id desc;
        场景：开发中，几乎所有的表都有两个列，一个是新增数据的时间，另一个是修改数据的时间。
              查询数据的时候，这个表的数据就需要按照修改时间的降序排列。
2、聚合函数：
    count()：查询满足条件的记录数，后面可以跟where条件。如果列为null，不做统计。如果要统计真实的表的记录数，最好不要用可以为null的列。
        count(*)，count(主键)，count(1)
        select count(字段) from 表名; 
    max()：查询满足条件的记录中的最大值，后面可以跟where条件。
        select max(age) from student where gender = '男';
    min()：查询满足条件的记录中的最小值，后面可以跟where条件。
        select min(age) from student where gender = '男';
    sum()：查询满足条件的记录中的值的和，后面可以跟where条件。
        select sum(age) from student where gender = '男';
    avg()：查询满足条件的记录中的值的平均值，后面可以跟where条件。
        select avg(age) from student where gender = '男';
3、分组查询：group by。将原有数据进行分组统计。
        select 分组列名,聚合函数1,聚合函数2... from 表名 group by 该分组列名;
        说明：group by后面可以是一列，也可以是多个列，分组后查询的列只能是分组的列，或者是使用了聚合函数的其他的列，其他列不能单独查询。
        也可以使用where条件，先把满足条件的人分出来，再分组：
            select 分组列,聚合函数... from 表名 where 条件 group by 分组列名;
        也可以使用having条件，把组信息中满足条件的组再细分出来：
            select 分组列,聚合函数... from 表名 【where 条件】 group by 分组列 having 条件;
        select gender,avg(age) from student group by gender HAVING gender = '男'; 
        select gender,avg(age) from student WHERE gender = '男' group by gender; 
        select gender,avg(age) from student group by gender order by avg(age) desc;
4、分页查询：LIMIT子句。
        限定查询结果的起始行，以及总行数。它是mysql中特有的语法，通常用来做分页查询。
        select * from student limit 4,3;
        select * from student limit 3; ==> limit 0,3
同时出现关键字的顺序：
    where -> group by -> having -> order by -> limit
面试题：
    一、在分组查询中，where和having的区别？
        1、位置
        2、where先筛选，再分组。having是先分组，再筛选。
        3、where条件可以使用所有的列，having条件只能使用分组的列。    
    二、group by 和order by？
    三、模糊查询，_和%区别？

多表（关联）查询：（多表联查）

    1、笛卡尔积。select * from student,teacher;
        开发中，尽量避免出现笛卡尔积。
SQL92语法：
    select t.name tname,c.name cname 
    from 
        teacher t,course c
    where 
        c.t_id = t.id;
    （1）进行多表查询时，通常情况下，多表之间要有逻辑关系。
    （2）表的别名，一旦在多表查询中，设置了表的别名，涉及到使用表名的位置，就必须使用别名。
    （3）物理外键和逻辑外键。
SQL99语法：
    1、内连接：
        select t.name,c.name
        from teacher t
        inner join course c 
        on t.id = c.t_id
        where t.name = '老子';
    2、外连接：
        左外连接：
            select t.name,c.name
            from teacher t
            left join course c 
            on t.id = c.t_id;
        右外连接：
            select t.name,c.name
            from teacher t
            right join course c 
            on t.id = c.t_id;
        全外连接：
        mysql不支持。
        select t.name,c.name
        from teacher t
        full join course c 
        on t.id = c.t_id;
        
        union关键字：
        select t.name,c.name 
        from teacher t 
        left join course c 
        on t.id = c.t_id 

        UNION 

        select t.name,c.name
        from teacher t 
        right join course c 
        on t.id = c.t_id 
小知识：
    1、阿里规约中有一条【强制】规定，超过三个表禁止join（视图）。
    2、需要join的字段，数据类型要一致
    3、多表关联查询时，保证被关联的字段要有索引。
    4、即使两张表关联，最好也好加上索引。
        

子查询：
    1、标量子查询：结果集只有一行一列。
    2、列子查询：结果集有一列多行。
    3、行子查询：结果集有一行多列。
    4、表子查询：结果集有多行多列。
-- 查询比张一鸣年龄大的学生（标量子查询）
select * from student where age > (
    select age from student where name = "张一鸣"
);

-- 查询有一门学科分数大于85分的学生信息
select * from student where id in(
    select DISTINCT  s_id from scores where score > 85
)

-- 对排名数学成绩前五的学生，正序排列（表子查询）
select * from (
    select s.id,s.name sname,c.name cname,r.score 
    from student s
    left join scores r on s.id = r.s_id  
    left join course c on c.id = r.c_id 
    where c.name = "数学"
) t order by t.score desc limit 5;

-- 查询男生且年龄最大的学生信息
select * from student  where gender = "男" and age = (
    select max(age) from student group by gender HAVING gender ="男"
)

-- 查询每个老师的代课的数量
select t.id,t.name,count(c.id)
from teacher t 
left join course c on c.t_id = t.id 
group by t.id,t.name;

select t.id,t.name,(
    select count(*) from course c where c.t_id  = t.id
) as '代课数量' from teacher t ;

-- 查询有课的老师
select * from teacher t where EXISTS (
    select * from course c where c.t_id = t.id
)