第一部分 数据过滤
数据过滤——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重新再把单表过一遍,明天简单复习后开复杂查询。