-- ########## 01、查询的排序 ##########
-- 需求：对班级的所有男生的年龄进行排序
-- 思路：
-- 思路1、对全部的数据先排序，再进行筛选
-- 思路2、对全部的数据线筛选，再进行排序
-- 显然，思路2这种形式效率比较高，语义上和实现上都符合要求，因为排序的数据越多，耗时越多，所以先通过筛选减少需要排序的数据量再进行排序

-- 次序：升序（顺序）ASC 和 降序（逆序）DESC
-- 特点：
-- A：没有指明使用哪个字段作为排序顺序时，默认的显示次序是按照表的主键的字段来进行排序（升序）的
-- B：升序时，NULL值得顺序排在非空值之前
-- C：不显式指明次序，默认的顺序为升序（顺序）ASC

DESC student;

SELECT * FROM student;

-- 1、单一字段的排序
SELECT * FROM student ORDER BY phone ASC;
SELECT * FROM student ORDER BY phone DESC;
SELECT * FROM student ORDER BY phone;

-- 2、多个字段的排序
INSERT INTO student VALUES(NULL, ‘郭嘉‘, ‘男‘, ‘666‘), (NULL, ‘郭嘉‘, ‘男‘, ‘111‘);
-- 需求：按studentname升序，同时按phone降序
SELECT * FROM student ORDER BY studentname ASC, phone DESC;
INSERT INTO student VALUES(NULL, ‘郭嘉‘, ‘男‘, ‘222‘);

-- 注意：下句也涉及到多个字段的排序：先按studentname升序，再按主键studentid升序
--         即首先考虑ORDER BY子句中显式指明的排序次序，其次考虑隐式的主键升序
SELECT * FROM student ORDER BY studentname ASC;

-- ########## 02、基于列的逻辑 ##########

-- 商品表
CREATE TABLE product
(
    -- 商品编号
    productid INT AUTO_INCREMENT PRIMARY KEY,
    -- 商品名称
    productname VARCHAR(20),
    -- 分类编码
    categorycode ENUM(‘F‘, ‘C‘)
);

INSERT INTO product VALUES(NULL, ‘苹果‘, ‘F‘), (NULL, ‘梨子‘, ‘F‘), (NULL, ‘香蕉‘, ‘F‘),
(NULL, ‘Nike‘, ‘C‘), (NULL, ‘Kappa‘, ‘C‘);

SELECT * FROM product;

-- 需求：分类编码categorycode显示的F、C不够清晰，在查询结果中希望能够看到清晰的含义
-- 分析：这就是对于列的逻辑，对列的内容如果是F就显示为水果，如果是C就显示为衣服
--          使用关键字CASE、WHEN、THEN、ELSE、END的结合使用

-- case表达式的格式1：
-- select
-- case 字段或表达式
-- when 值1 then 结果1
-- WHEN 值2 THEN 结果2
-- ...
-- else 默认结果
-- end

SELECT 
    productid AS 商品编号,
    productname AS 商品名称,
    categorycode AS 分类编码（不清晰）,
    CASE categorycode
    WHEN ‘F‘ THEN ‘水果‘
    WHEN ‘C‘ THEN ‘衣服‘
    ELSE ‘其他‘
    END AS 分类编码（清晰）
FROM product;

-- case表达式的格式2：
-- select
-- case
-- when 条件1 then 结果1
-- WHEN 条件2 THEN 结果2
-- ...
-- else 默认结果
-- end

SELECT
    productid AS 商品编号,
    productname AS 商品名称,
    categorycode AS 分类编码（不清晰）,
    CASE
    WHEN categorycode = ‘F‘ THEN ‘水果‘
    WHEN categorycode = ‘C‘ THEN ‘衣服‘
    ELSE ‘其他‘
    END AS 分类编码（清晰）
FROM product;

-- ########## 03、基于行的逻辑 ##########
SELECT * FROM student;

DESC student;

-- 1、使用WHERE子句，对集合进行条件筛选
SELECT * FROM student WHERE gender = ‘女‘;
SELECT * FROM student WHERE gender = ‘男‘;
-- 错误代码： 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘LIMIT 0, 1000‘ at line 1
SELECT * FROM student WHERE gender = DEFAULT;

-- 2、在WHERE子句中使用AND，表示逻辑与的关系（筛选出WHERE子句中所有条件满足的结果）
SELECT * FROM student WHERE gender = ‘女‘ AND phone = ‘123‘;
SELECT * FROM student WHERE gender = ‘女‘ AND phone = ‘114‘;

-- 3、在WHERE子句中使用OR，表示逻辑或的关系（WHERE子句中只要有条件满足的结果就筛选出来）
SELECT * FROM student WHERE gender = ‘女‘ OR phone = ‘123‘;
SELECT * FROM student WHERE gender = ‘女‘ OR phone = ‘114‘;
SELECT * FROM student WHERE gender = ‘女‘ OR phone = ‘119‘;

-- 4、在WHERE子句中使用!= 或者 <>，表示不等于的关系
SELECT * FROM student WHERE gender != ‘女‘;
SELECT * FROM student WHERE gender <> ‘女‘;
-- 错误代码： 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘>< ‘女‘ LIMIT 0, 1000‘ at line 1
SELECT * FROM student WHERE gender >< ‘女‘;

-- 5、* 通配符：星号通配符，用来代替所有的字段名称
SELECT * FROM student;

-- 6、_ 通配符：下划线通配符（占位通配符），结合LIKE关键字使用
-- 注意：_ 通配符占一个位置，这个位置上可以是任意字符，但是一定有一个字符
INSERT INTO student VALUES(NULL, ‘曹丕‘, ‘男‘, ‘110‘), (NULL, ‘曹植‘, ‘男‘, ‘120‘), (NULL, ‘曹孟德‘, ‘男‘, ‘130‘), (NULL, ‘曹‘, ‘男‘, ‘140‘);

-- 需求：查询以‘曹‘开头的，后面接一个任意字符的记录
SELECT * FROM student WHERE studentname LIKE ‘曹_‘;

-- 需求：查询以‘曹‘开头的，后面接两个任意字符的记录
SELECT * FROM student WHERE studentname LIKE ‘曹__‘;

-- 需求：查询以一个任意字符开头，中间有一个‘孟‘字，后面接一个任意字符的记录
SELECT * FROM student WHERE studentname LIKE ‘_孟_‘;

-- 需求：查询以一个任意字符开头，中间有一个‘曹‘字，后面接一个任意字符的记录
SELECT * FROM student WHERE studentname LIKE ‘_曹_‘;
-- 注意：体会占位的含义，下句插入空格+曹彰，会被查出
INSERT INTO student VALUES(NULL, ‘ 曹彰‘, ‘男‘, 150);
SELECT * FROM student WHERE studentname LIKE ‘_曹_‘;

-- 7、% 通配符：百分号通配符（任意匹配通配符），结合LIKE关键字使用

-- 任意匹配的含义：不论匹配一个字符，还是匹配多个字符，甚至没有字符，均匹配
SELECT * FROM student WHERE studentname LIKE ‘曹%‘;

-- 下句中的‘曹‘字符后有两个%百分号的效果 和 有一个%百分号的效果是一致的，都是在‘曹‘字符后任意匹配
SELECT * FROM student WHERE studentname LIKE ‘曹%%‘;

-- 下句表示在‘曹‘字符前后任意匹配
SELECT * FROM student WHERE studentname LIKE ‘%曹%‘;

-- 匹配所有的记录（非空记录）
SELECT * FROM student WHERE studentname LIKE ‘%‘;

-- 匹配所有联系电话以1开头的记录
SELECT * FROM student WHERE phone LIKE ‘1%‘;

-- 匹配所有联系电话非空的记录
SELECT * FROM student WHERE phone LIKE ‘%‘;

-- 8、使用LIKE进行模糊查询时，取反操作NOT LIKE，不会对NULL值进行匹配的
SELECT * FROM student WHERE phone NOT LIKE ‘1%‘;

-- 9、在WHERE子句中使用IN关键字描述处于某一个小的范围的集合
-- IN对应的小范围集合的值在表中均有的情况：匹配值的记录被取出
SELECT * FROM student WHERE phone IN (‘110‘, ‘120‘, ‘666‘);

-- IN对应的小范围集合的值在表中部分有的情况：匹配值的记录被取出
SELECT * FROM student WHERE phone IN (‘110‘, ‘120‘, ‘777‘);

-- 注意：上述IN的使用可以理解为：
SELECT * FROM student WHERE phone = ‘110‘ OR phone = ‘120‘ OR phone = ‘777‘;

-- 10、在WHERE子句中使用NOT IN关键字描述不处于某一个小的范围的集合
SELECT * FROM student WHERE phone NOT IN (‘110‘, ‘120‘, ‘666‘);

SELECT * FROM student WHERE phone NOT IN (‘110‘, ‘120‘, ‘777‘);

-- 注意：IN 某一个小的范围 + NOT IN 某一个小的范围     不等于  全部范围，因为忽略了NULL值

-- 11、使用IS NULL筛选字段的内容为NULL的记录
SELECT * FROM student WHERE phone IS NULL;

-- 12、使用IS NOT NULL筛选字段的内容不为NULL的记录
SELECT * FROM student WHERE phone IS NOT NULL;

-- 注意：IS NULL 筛选出来的记录  +  IS NOT NULL 筛选出来的记录   等于  所有记录

-- 13、使用大于、小于、大于等于、小于等于符号进行范围筛选（同样不包含NULL值对应的记录）
SELECT * FROM student WHERE phone > ‘150‘;        -- 2条记录
SELECT * FROM student WHERE phone < ‘150‘;        -- 7条记录
SELECT * FROM student WHERE phone >= ‘150‘;        -- 3条记录
SELECT * FROM student WHERE phone <= ‘150‘;        -- 8条记录

-- 14、使用BETWEEN...AND...做范围筛选（考虑边界值是否包含？答：两个边界值均包含在内）
SELECT * FROM student WHERE studentid BETWEEN 3 AND 8;

-- 下句查询的结果：没有满足条件的记录
SELECT * FROM student WHERE studentid BETWEEN 8 AND 3;

-- 注意：BETWEEN 值1 AND 值2  相当于  大于等于 值1  且 小于等于 值2
-- 198行可以理解为下句：
SELECT * FROM student WHERE studentid >= 3 AND studentid <= 8;
-- 201行可以理解为下句：
SELECT * FROM student WHERE studentid >= 8 AND studentid <= 3;

-- ########## 04、汇总数据 ##########

-- 1、使用DISTINCT关键字去除重复的值
-- 需求：获取学生的性别
SELECT gender FROM student;
-- 上句从语法上看没有问题，但是从语义上，会取出很多重复的数据，而这些重复的数据无意义

SELECT DISTINCT gender FROM student;

-- *****************************************************
-- 聚合函数：SUM()、AVG()、MAX()、MIN()、COUNT()

SELECT * FROM student;

-- 2、单独使用聚合函数
-- COUNT(*)统计的记录针对非空字段进行的统计
SELECT COUNT(*) AS 记录条数 FROM student;

-- 对字段的内容中没有NULL值的字段（主键字段）进行COUNT()，结果和COUNT(*)的记录数一致
SELECT COUNT(studentid) AS 记录条数 FROM student;

-- 对字段的内容中没有NULL值的字段（非空字段）进行COUNT()，结果和COUNT(*)的记录数一致
SELECT COUNT(studentname) AS 记录条数 FROM student;

-- 对字段的内容中有NULL值的字段（可空字段）进行COUNT()，结果和COUNT(*)的记录数相差了NULL值的个数
SELECT COUNT(phone) AS 非空记录条数 FROM student;

-- 注意：字段内容中的NULL值不会被COUNT()这个聚合函数统计在内
--         COUNT(*)函数中的* 并不是匹配所有的字段，而是那些非空的字段

SELECT MAX(studentid) AS 最大学号 FROM student;
SELECT MIN(studentid) AS 最小学号 FROM student;

SELECT MAX(phone) AS 最大联系号码 FROM student;
SELECT MIN(phone) AS 最小联系号码 FROM student;

-- 注意：字段内容中的NULL值不会被MAX()、MIN()这两个聚合函数统计在内

ALTER TABLE student ADD score INT AFTER phone;

DESC student;
SELECT * FROM student;

-- 此时，新增了score成绩字段（可空），默认值均为NULL，对其进行SUM() 和 AVG()，均为NULL值
SELECT SUM(score) AS 总分 FROM student;        -- NULL
SELECT AVG(score) AS 平均分 FROM student;    -- NULL

-- 更新随机分数，测试一下
SELECT ROUND(RAND() * 100);
SELECT SUBSTRING(RAND() * 100, 1, 2); -- 这种形式可能会出现个位数字加小数点点号的结果

-- 更新表中的数据
UPDATE student SET score = ROUND(RAND() * 100) WHERE studentid IN (1, 2, 8);

SELECT SUM(score) AS 总分 FROM student;
SELECT AVG(score) AS 平均分 FROM student;

-- 注意：字段内容中的NULL值不会被SUM()、AVG()这两个聚合函数统计在内

-- AVG(某个字段) = SUM(某个字段) / COUNT(某个字段)
SELECT SUM(score) / COUNT(score) FROM student;

-- 聚合函数小结：聚合函数均无视NULL值