SQL高级点的查询和聚合函数

-- 所有查询方法
    -- 基本查询查
        -- 查询所有字段
            select * from 表名;
        -- 指定条件查询
            select * from 表名 where 条件;  -- 条件：id>6或者id<9或者 "name" = "xxx"或者 "age" ="男"
        -- 查询指定字段
            select id,name from 表名;     -- 只查询 id和 name字段
            select 表名.id,表名.name from 表名;   -- 查询哪个表的id和哪个表的name
        -- 查询指定字段，并给字段取别名
            select id as 身份证,name as 姓名 from 表名;
        -- 查询指定字段，并给表和字段取别名
            select f.id as 身份证,f.name as 姓名 from 表名 as f;
        -- 查询的显示顺序
            select name as 姓名,id as 身份证 from 表名;        -- name 在前面所以显示在前面
        -- 去重：多个重复的值，只显示第一个
            select distinct 字段 from 表名;


    -- 条件查询
        -- 比较运算符：>,<,=,>=,<=,<>,!=
            -- 列一： 大于18岁的信息
                select * from 表名 where age>18;

        -- 逻辑运算符：and,or,not
            -- 列一： 年龄不是小于或者等于18 并且是女性
                select * from 表名 where (not age<18) and gender=2;


    -- 模糊查询
        -- like
        -- % 等于0个或者1个或者多个
        -- _ 等于1个
            -- 列一： 查询姓名中 以 '小' 开头的名字
                select * from 表名 where name like "小%";
            -- 列二： 查询两个字的名字
                select * from 表名 where name like "__";
            -- 列三： 查询至少两个字的名字
                select * from 表名 where name like "__%";

        -- rlie 正则  （正则表达式查询）
            -- 列一： 查询以周开头的
                select * from 表名 where name rlike "^周.*伦$";


    -- 范围查询
        -- 非连续
            -- in (1,3,8)表示在一个非连续的范围内
                -- 列一：查询年龄为18,34的姓名
                    select name,age from 表名 where age in (18,34);
            -- not in (1,3,8) 不非连续的范围内
                -- 列一：查询年龄不为18,34的姓名
                select name,age from 表名 where age not in (18,34);

        -- 连续
            -- between 多少 and 多少;
                -- 列一：查询年龄在18到34这间的
                    select name,age from 表名 where age between 18 and 34;
            -- not between 多少 and 多少;
                -- 年龄不在多少到多少这间,


    -- 判断空
        -- 判空 is null
            -- 列一：查询身高为空的信息
                select name as 姓名,height as 身高 from 表名 where height is null;

        -- 判非空 not is null;
            -- is 前面加个 not 结果就是不为空的信息


-- 排序
    -- order by 字段名 asc或desc
    -- asc 从小到大排列，即升序
    -- desc 从大到小排序，即降序
        -- 列一：查询年龄18到34这间的男性，按照年龄从小到大排序
            select * from students where (age between 18 and 34) and gender =1 order by age desc;
        -- 列二：查询年龄在18到34这间的女性，身高从高到矮排序
            select * from students where (age between 18 and 34) and gender='女' order by height desc;

    -- order by 多个字段
        -- 如果第一个条件有相同的值，相同的值就会按照主键从小到大排
        -- 给他加多个字段（条件）
            -- 列一：查询年龄在18到34这间的女性，身高从高到矮排序，如果有身高相同的情况下按照年龄从小到大排
                select * from 表名 where age between 18 and 34 and gender=2 order by height desc,age asc;
            -- 列二：如果列一的结果年龄还有相同，按照id从大到小排
                select * from 表名 where age between 18 and 34 and gender=2 order by height desc,age asc,id desc;
            -- 列三：按照年龄从小到大、身高从高到矮
                select * from 表名 order by age asc,height desc;


----------------------------------------------------------------------------------------------------------------
-- 聚合函数
    -- 总数
    -- count 计算个数
        -- 列一：查询男性有多少人，女性有多少人
            select count(*) as '男性人数' from 表名 where gender='男';
            select count(*) as '女性人数' from students where gender=2;

    -- 最大值
    -- max
        -- 列一：查询最大年龄
            select max(age) as '最大年龄' from 表名;
        -- 列二：查询女性的最高身高
            select max(height) as '女性最高身高' from 表名 where gender=2;

    -- 最小值
    -- min
        -- 和最大值一样用

    -- 求和
    -- sum
        -- 列一：计算所有人的年龄总和
            select sum(age) from 表名;

    -- 平均值
    -- avg
        -- 列一：计算平均年龄
            select avg(age) from 表名;
        -- 列二：计算平均年龄2
            select sum(age)/count(*) from 表名;


    -- 四舍五入
    -- round(123.45,2) 保留两位小数
        -- 列一：计算出平均年龄，保留两位小数
            select round(avg(age),2) from 表名;
        -- 列二：计算男性的平均身高，保留一位小数
            select round(avg(height),1) from 表名 where gender=1;


-- 分组（分组之后才能和聚合函数一起使用），不能用姓名和id这些分组，要用性别或者部门这类的
    -- 不能这样使用，字段名不能和聚合函数一起使用，要先分组才能使用
    -- select name,round(avg(height),2) from 表名 where gender=1      -- name字段名不能直接和round聚合函数放一起
    -- 按照某个东西来分组
    -- group by
        -- 列一：按照性别分组
            select gender from students group by gender;        -- 理解为：按照性别分为四个表
        -- 分组后，计算每个性别的人数
            select gender,count(*) from students group by gender;       -- count查询这四个表的个数

    -- 查询分组后，每个组有谁
    -- group_concat(...)
        -- 列一：查询分组后，每个组有谁
            select gender,group_concat(name) from students group by gender;
        -- 列二：计算男性组的人数
            select gender,count(*) from students where gender=1 group by gender;
        -- 列三：同时查询组多个信息
            select gender,group_concat(name,'_',age,'岁')from students group by gender;

    -- 分组条件显示
    -- having
        -- 列一：查询平均年龄超过30岁的分组，以及姓名
            select gender,group_concat(name),avg(age) as '平均年龄' from students group by gender having avg(age)>30;


-- 分页
-- limit start,count
    -- 列一：查询前5条数据
        select * from students limit 5;
    -- 列二：从第3条开始查询7条数据
        select * from students limit 3,7;
    -- 列三：查询所有女性的信息并且按照身高从高到矮排序，然后只显示两条信息
        select * from students where gender=2 order by height desc limit 0,2;


----------------------------------------------------------------------------------------------------------------
-- 数据表连接查询
    --------------------------------------------内连接：取交集--------------------------------------------
        -- ineer join 另一个表名 on
            -- students 连接 classes ，on 条件（按照什么连接这两个表）：按照students表里的cls_id字段的内容 = classes里的 id一样的话，就把classes里对应的行连接到students行里
                select * from students inner join classes on students.cls_id=classes.id;

        -- 显示指定字段信息：姓名，班级
            select students.name as '姓名',classes.name as '班级' from students inner join classes on students.cls_id=classes.id;
        -- 一个表所有东西，另一个表值取一个字段
            select s.*,c.name as '班级' from students as s inner join classes as c on s.cls_id=c.id;
        -- 排序
            select c.name as '班级',s.* from students as s inner join classes as c on s.cls_id=c.id order by c.name desc;


    -----------------------------------左连接---------------------------------------
                    select s.*,c.name as '班级' from students as s left join classes as c on s.cls_id=c.id;


    -- 取完后查询指定条件的值
        -- 取c表id字段为null的行
        select s.*,c.name as '班级' from students as s left join classes as c on s.cls_id=c.id having c.id is null ;
        select s.*,c.name as '班级' from students as s left join classes as c on s.cls_id=c.id where c.id is null ;


-- 自关联
-- 比如你选择广东省就会显示出这个省的市，