2021-04-14 SQL基础知识-单表查询（二）_聚合键写在where中更合适-优快云博客

本文链接：https://blog.youkuaiyun.com/mlxgccc/article/details/115711561

第一部分数据过滤

数据过滤——where
根据指定条件，过滤数据结果，不能单独使用，属于非必需子句。
    注意事项：
        1. FROM之后，ORDER BY之前
        2. 属于文本格式的数值，大小比较
        3. NULL的处理（空值会遗漏） IFNULL（expr1，expr2）

   SELECT m.* FROM milk_tea AS m WHERE IFNULL(sale_price,15 ) < 15;

其中空值用15做筛选，但是不是确定的数值15

括号里也可以进行运算，比如IFNULL（sale_price * 0.9 , 15）与 IFNULL（sale_price , 15）*0.9

知识点：

数值过滤——between..and..
    指定数值在某两个值之间：where [列] between [前端值] and [后端值]
        适用场景：简化取值范围的书写，两端均包含，between 3 and 8


数值过滤——is null
    判断列为空值
        适用场景：异常值处理


数值过滤——is not null
    判断值不为空值：where [列] is not null
        适用场景：异常值处理


LIKE：模糊查询，非已知，不明确
通配符——下划线(_)
    单个、任何字符：WHERE [列] LIKE '... _ ...'
        适用场景：知道字符数目、知道字符位置；


通配符——百分号(%)
    任意数目（包括0个）、任何字符：WHERE [列] LIKE'...%...';
    适用场景：不知道字符数目，知道字符数目

注意：
    1. 通配符开头，那么效率较低，所以一般不把通配符放在开头位置处
    2. 转义字符（\）的使用，示例中的（SELECT * FROM pet WHERE owner LIKE 'Gr\%_en'；）其中，\%表示转义，%就是个符号

补充：在SQL的注释用两个短杠(--)表示
过滤条件（且与或）——条件不仅局限于一列
    且与或的操作：先且与先或，最好用括号隔开
    且——AND
        两个或多个条件，同时满足：WHERE [条件1] AND [条件2] AND...；
            适用场景：不止一个过滤条件，条件需要同时满足


     或——OR
        两个或多个条件，至少满足一个：WHERE [条件1] OR [条件2] OR...；
            适用场景：不止一个过滤条件，条件只需要满足一个即可


    组合操作——AND..OR..
        多个条件，不同要求：WHERE [条件1] AND [条件2] OR[条件3]...
            适用场景：复杂情况



数值过滤
    取值限制——IN
        明确而不连续的取值：WHERE [字段] IN （值1，值2...）
            适合场景；过滤值明确，不是连续范围


    否定条件——NOT
        否定过滤条件：WHERE NOT [条件1]；
            适合场景：否定一个或多个过滤条件，不能单独使用，类似补集的概念（只否定紧跟着的那个条件）

在这里插入图片描述
关于单表查询中的数据类型

**数值型**
01-INTEGER(INT)
    整数型、整型，只能储存整数
        示例：int（10）
02-DECIMAL
    定点型，储存固定位数的小数
        示例：decimal（2,2）——0.16   /总长度+小数点后多少位
        用于精确计算，比如金额。多出位数四舍五入，默认参数为Decimal（10,0）


03-FLOAT/DOUBT/REAL
    浮点型，存储不固定位数的小数
        示例：0.16，float（5,3）
        多出位数五舍六入，举例10.0015，float（5,3）则是10.001


04-函数使用（对列进行输入/输出）
    绝对值函数——ABS
        ABS(-3)=3

    平方根函数——SQRT
        SQRT(4)=2

    指数函数——EXP
        EXP(4)=e^4

    四舍五入函数——ROUND
        ROUND(1.234,2)=1.23

    圆周率函数——PI
        PI( )=π




文本型
CHAR（查找比较方便，例如手机号、身份证号）
    定长字符串
    举例：'home'、'数据类型'——4个字符
    用法：char（10），不管写了多少字符，例如 'home' 用了char（10），用了4个字符，剩下6个字符用空位填满
VARCHAR（比较省空间）
    变长字符串
    举例：'home'、'数据类型'、'SQL'
    用法：Varchar（100）

文本中区分大小写，char型不足位，补空位
长度单位为字符个数
    UTF-8中，1个中文字符占3个字节，1个数字或英文占1个字节

文本型处理函数
    字符长度——CHAR_LENGTH
        示例：CHAR_LENGTH('数据')=2

    字节数——LENGTH
        示例：LENGTH('数据')=6

    去除右边空值——RTRIM
        示例：RTRIM('home ')='home'

    去除左边空值——LTRIM
        示例：RTRIM(' home')='home'

    大写字母转换——UPPER
        示例：UPPER('HOME')='home'

    小写字母转换——LOWER
        示例：LOWER('home')='HOME'





TIME
    定义：时间型H:i:s
    举例：'14:55:01'
    用法：TIME

DATETIME
    日期型 Y-m-d H:i:s
    举例：'2021-04-04 14:56:00'
    用法：DATETIME

日期型处理函数
    获取具体日期段——YEAR/M/D
        示例：YEAR('2021-04-14') = '2021'；

    获取具体时间段——HOUR/M/S
        示例：HOUR(' 14:02:01')='14'

    获取月份名称——MONTHNAME
        示例：MONTHNAME('2019-1-2')='JAN'

    获取当前日期时间——NOW
        示例：NOW()=systime

    获取当前日期——CURDATE
        示例：CURDATE()='2021-4-14'

    获取当前时间——CURTIME
        示例：CURTIME()='15:30:21'

    时间增加——DATE_ADD
        示例：DATE_ADD('20210414',INTERVAL 1 MONTH)

    时间减少——DATE_SUB
        示例：DATE_SUB('20210414',INTERVAL 1 YEAR)

补充:不同的DBMS会有不同，一定先测试再使用

第三部分聚合函数

//输入（列），输出（一行），不实际检索数据，而是汇总处理数据
1. 计数函数-COUNT（x列有多少行）
    COUNT()/(1)   确定表中行的数目或符合特定条件的行的数目，唯一一个**能用的函数,空值行也计入
    COUNT(col)   只作用非NULL行
    COUNT(DISTINCT col)   对行去重计数
2. 求和函数-SUM
    SUM(col)
    具体用法：区别于直接四则运算；只对数值型作用；可以操作多个列；可以作用于计算表达式
    建议在实际运算前先做非空值处理：IFNULL(XX,0)

3. 均值函数-AVG
    AVG(col)
    具体用法：分母不对NULL计数、可以同时操作多个列

4. 最大值函数-MAX
    max(col)
    具体用法：适用于数值型/文本型/日期型，要求该列可以排序，忽略null，distinct无意义
    注意：文本类返回以及中文字符的处理

5. 最小值函数-MIN
    min(col)
    具体用法：适用于数值型/文本型/日期型，要求该列可以排序，忽略null，distinct无意义
    注意：文本类返回以及中文字符的处理


数据分组
    GROUP BY +聚合键
    SELECT组成： 聚合键/分组列：GROUP BY 指定列；聚合函数；常数
    注意事项：
        1. 结果中一行数据即代表一个分组；
        2. select展示列必须与分组对应，那么就只能是聚合键；NULL值单独成为一个分组；聚合键尽量不要出现别名
        示例：select m.net_w,sum(m.sale_price) from milk_tea as m WHERE m.net_w in ('100g','150g')group by m.net_w


分组过滤
    将聚合结果再次筛选，得到所需分组，一定要在GROUP BY之后,执行结果仍是多行数据表
    关键字：
        必需子句：SELECT...FROM...
        过滤子句：WHERE  
        分组子句：GROUP BY  #分组列其实就算是聚合键
        分组筛选子句：HAVING
        对结果进行排序：ORDER BY..

    WHERE过滤的是行，HAVING筛选的是组，ORDER BY 用在末尾处。
    聚合键写在where中更合适，先把不满足要求的行用where过滤掉,GROUP BY 尽量不要使用别名
    示例:
    执行顺序：FROM... >> WHERE.. >> GROUP BY.. >> HAVING.. >> SELECT.. >> ORDER BY..

代码示例：
1、各类别中，产品销售价在 10 块以上的分别有多少？

select p.class, COUNT(1) from prod_info as p where p.sale_price > 10 GROUP BY p.class;

2、各类别中，产品数量在 5 件以上的分别有哪些？

SELECT p.class, count(1) FROM prod_info as p GROUP BY p.class HAVING count(1)>5;

3、购买商品超过 5 件的用户有哪些？

SELECT cust_id,sum(ifnull(quantity,0)) FROM order_list AS o group by cust_id having sum(ifnull(quantity,0)) > 5;

结合猴子的live重新再把单表过一遍，明天简单复习后开复杂查询。