第八节 查找命令select

-- 查询所有字段
    SELECT * FROM t_student;

-- 查询指定字段
    SELECT name,id FROM t_student;

--使用as给字段起别名
    SELECT name AS 姓名, age AS年龄 FROM t_student;
    SELECT t_student .name, t_student .age FROM t_student;
    SELECT s.name, s.age FROM t_student AS s;
-- SELECT t_student .name, t_student .age FROM t_student AS s; 错误语句

-- 去重
    SELECT DISTINCT gender FROM t_student;

-- 条件查询
    -- 普通条件查询
        SELECT * FROM t_studentWHERE age>18;
    -- 比较运算符查询
        SELECT * FROM  t_student WHERE not (age>18 and age <30);
        SELECT * FROM  t_student WHERE not age>18 and age <30;
    -- 模糊查询
        -- like，% 替换一个或者多个，_替换一个
            -- 查询姓名中以‘小开头的’名字
            SELECT * FROM t_student WHERE name LIKE '小%';
            -- 查询姓名中有‘小的’所有名字
            SELECT * FROM t_student WHERE name LIKE '%小%';
            -- 查询两个字的名字
            SELECT * FROM t_student WHERE name LIKE '__';
        -- rlike 正则
            -- 以周开头的名字
            SELECT * FROM t_student WHERE name RLIKE '^周.*';
            -- 以周开头以伦结尾的名字
            SELECT * FROM t_student WHERE name RLIKE '^周.*伦$';

-- 范围查询
    -- in
        -- 查询年纪为18、34的姓名
        SELECT * FROM t_student WHERE age IN (18,34);
    -- not in
        -- 查询年龄不为18、34的姓名
        SELECT * FROM t_student WHERE age NOT IN (18,64);
    -- between...and...
        -- 查询年龄在18到34岁的姓名
        SELECT * FROM t_student WHERE age BETWEEN 18 AND 34;
    -- not between...and...
        -- 查询年龄不在18和34之间的姓名
        SELECT * FROM t_student WHERE age NOT BETWEEN 18 AND 34;
        ---- not between...and...是一种固定语句，不是between...and...取反的意思。因此NOT (BETWEEN 18 AND 34)是错误的
        SELECT * FROM t_student WHERE NOT age BETWEEN 18 AND 34;
        -- 这才是取反操作

-- 空判断
    -- 判空 IS NULL
    SELECT * FROM t_student WHERE height IS NULL;
    -- 判非空 IS NOT NULL
    SELECT * FROM t_student WHERE height IS NOT NULL;

-- 排序
    --ORDER BY 字段
        -- ASC从小到大排列，升序
            -- 查询年龄18-34之间的男性，并年龄升序排列
            SELECT * FROM t_student WHERE age BETWEEN 18 AND 34 AND gender=1 ORDER BY age ASC;
        -- DESC从大到小排列，降序
            -- 查询年龄在18-34之间的女性，身高降序
            SELECT * FROM t_student WHERE age BETWEEN 18 AND 34 AND gender=2 ORDER BY height DESC;
        -- ORDER BY 多个字段
            SELECT * FROM t_student WHERE age BETWEEN 18 AND 34 AND gender=2 ORDER BY height ASC, id DESC;

-- 聚合函数
    -- 总数，count
        -- 查询男性有多少人，女性有多少人
        SELECT COUNT(name) FROM t_student WHERE gender=1;
        SELECT COUNT(*) AS 女性人数, avg(age) AS 平均年龄 FROM t_student WHERE gender=1;
    -- 最大值 max
        --查询最大的年龄
        SELECT MAX(age) FROM t_student ;
    -- 最小值 min
        -- 查询女性的最高身高
        SELECT MAX(height) FROM t_student WHERE gender=2;
    -- 平均值 avg
        -- 计算平均年龄
        SELECT AVG(age) FROM t_student ;
    -- 四舍五入 round(123.23, 1)保留一位小数
        -- 计算所有人的平均年龄，保留两位小数
        SELECT ROUND(AVG(age), 2) FROM t_student ;

-- 分组
    -- gruop by
        -- 按照性别分组，查询所有性别
        SELECT gender FROM t_student GROUP BY gender;
        -- 计算每种性别中的人数
        SELECT gender,COUNT(NAME) FROM t_student GROUP BY gender;
        -- 计算男性的人数
        SELECT gender,COUNT(NAME) FROM t_student WHERE gender=1 GROUP BY gender;
    -- group_concat(...)
        --查询同种性别中的姓名
        SELECT gender,group_concat(name) FROM t_student WHERE gender=1 GROUP BY gender;
        SELECT gender,group_concat(name, id, age) FROM t_student WHERE gender=1 GROUP BY gender;
        SELECT gender,group_concat(name,' ',id, '', age) FROM t_student WHERE gender=1 GROUP BY gender;
    -- having
        -- 查询平均年龄超过30岁的性别、姓名
        SELECT gender, group_concat(NAME), AVG(age) FROM t_student GROUP BY gender having AVG(age)>30;
        -- where是对原表进行过滤，having是对分组后每个组的数据进行判断
        -- 查询每种性别中的人数多于两个的信息
        SELECT gender, group_concat(NAME) FROM t_student GROUP BY gender having COUNT(NAME)>2;

-- 分页
    -- limit start，count
        -- start表示从第几个开始取（0是第一个），count是取几个
        SELECT * FROM t_student LIMIT 1, 5;
    -- 限制查询出来的个数
    SELECT * FROM t_student WHERE gender=1 LIMIT 2;
    -- 查询前5个数据
    SELECT * FROM t_student LIMIT 0, 5;
    -- 查询 ID 6-10（包含）的数据
    SELECT * FROM t_student LIMIT 5,5;
    -- 每页显示2个，第1个页面
    SELECT * FROM t_student LIMIT 0, 2;
    -- 每页显示2个，第2个页面
    SELECT * FROM t_student LIMIT 2, 2;
    -- 每页显示2个，第3个页面
    SELECT * FROM t_student LIMIT 4, 2;
    -- 每页显示2个，第4个页面
    SELECT * FROM t_student LIMIT 6, 2;
    -- 每页显示2个，显示第6页信息，按照年龄从小到大排序
    SELECT * FROM t_student LIMIT 10,2 ORDER BY age;
    -- LIMIT 只能放在最后
-- 连接查询
    -- inner join ...on 显示两者共有，即交集
     SELECT S.*,C.NAME FROM t_student AS S INNER JOIN CLASSES AS C ON S.cls_id = C.id;
    -- left join ...on 谁在左边以谁为主，如果左边在右边没有对应的值，就显示null
     SELECT * FROM t_student AS S LEFT JOIN CLASSES AS C ON S.cls_id=C.id;
    -- right join ...on 谁在右边以谁为主，如果右边在左边没有对应的值，就显示null
    SELECT * FROM t_student AS S RIGHT JOIN CLASSES AS C ON S.cls_id=C.id;
    -- 查询没有对应班级的学生
    SELECT * FROM t_student AS S left JOIN CLASSES AS C ON S.cls_id=C.id having c.id is null;
     -- 此处having改成where也行，但不建议，原则上原表过滤用where，查询结果过滤用having
 
-- 自关联

 -- 子查询（自关联的表也可以用子查询得到相同的结果）
     select * from areas where pid = (select aid from areas where atitle = '河北省')