脑图有助于回顾知识点
#常见函数
/*
调用:
select 函数名(实参列表) 【from】;
分类:
1、1单行函数(做处理)
concat, length, ifnull等
2、分组函数(统计函数、聚合函数、组函数)
*/
#单行函数
#一、字符函数
SHOW VARIABLES LIKE '%char%'; #utf8中汉字占3个字节
#length 返回参数的字节数
LENGTH('hhh');
LENGTH('潘花花hhh');
#conccat 拼接
SELECT CONCAT('1','+','1','=','2') AS 表达式;
#upper、lower转换大小写
SELECT UPPER ('aaa');
SELECT LOWER ('AAA');
#substr、substring
SELECT SUBSTR('和冰冰吃火锅',2) AS 1; #索引从1开始
SELECT SUBSTR('和冰冰吃火锅',2,2) AS 谁; #指定字符长度截取
#instr 返回子字符在这个字符串第一次出现的位置,如果没有,返回0
SELECT INSTR ('天天喝奶茶吃冰淇淋还不长胖','不长胖') AS 在哪;
#trim 去头尾空格或指定字符
SELECT TRIM(' 喝奶茶 ') AS 干啥;
SELECT TRIM('a' FROM 'aaaaaaa喝aa奶茶aaa') AS 干啥;
#lpad、rpad 左、右边填充指定个数的指定字符
SELECT LPAD('薯片',5,'吃');
SELECT LPAD('其实没必要',3,'*'); #超过指定字符个数则做左截取
#replace 全部替换
SELECT REPLACE('最爱是新欢','新欢','旧爱');
#数学函数
#round 四舍五入
SELECT ROUND(-1.55); #默认取整
SELECT ROUND(-1.55,1); #可选保留小数位数
#ceil 向上取整,返回>=参数的最小值
SELECT CEIL(1.01);
SELECT CEIL(1.00);
#floor 向下取整,返回<=该参数的最小值
SELECT FLOOR(9.99);
#truncate 截断
SELECT TRUNCATE(1.69,1);
#mod 取余 相当于%
SELECT MOD(10,-3);
SELECT -10%3;
#三、日期函数
#now 返回当前年月日时分秒
SELECT NOW();
#curdate 返回当前年月日
SELECT CURDATE();
#curtime 返回当前时分秒
SELECT CURTIME();
#获取指定部分的年、月、日、时、分、秒
#1、year 参数可以是函数,字符,列名
SELECT YEAR(NOW()) 年;
SELECT YEAR('1996-11-20');
#2、month
SELECT MONTH('1996-11-20');
SELECT MONTHNAME('1996-11-20'); #英文显示
#3、....
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());
#str_to_date 将日期格式的字符转换成指定格式的日期(用指定格式解析已有字符型日期)
SELECT STR_TO_DATE('1996-11-20','%Y_%c_%d'); #有bug,尝试解决没成功
-- select @@global.sql_mode;
-- set @@global.sql_mode='strict_trans_tables';
#date_format 将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日');
SELECT DATE_FORMAT(NOW(),'%c月/%d日-%y年');
#其他函数
SELECT VERSION();
SELECT DATABASE();
SELECT USER();
#流程控制函数
#1、if函数, if else效果 做判断并返回指定结果
SELECT IF(10>2,'是','否');
#2、case函数
/*
语法:用法一:等值判断
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;#如果是值 不加分号,如果是表达式 要加分号
...
else 要显示的值N或语句N;
end
*/
SELECT *, #逗号不要忘记了
CASE `place`
WHEN '法国' THEN price*0.9
WHEN '澳大利亚'THEN price*0.5
ELSE price
END AS 内部价
FROM`query1`;
/*
语法:用法二:区间值判断
case
when 条件1 then 要显示的值1或语句1;#如果是值 不加分号,如果是表达式 要加分号
...
else 要显示的值N或语句N;
end
*/
SELECT
*,
CASE
WHEN price > 90
THEN 'A级'
WHEN price > 80
THEN 'B级'
WHEN price > 50
THEN 'c级'
ELSE 'D级'
END AS 等级
FROM
`query1` ;
#二、分组函数
/*
功能:统计使用,又称聚合函数或统计函数或组函数
分类:
sum、avg、max、min、count
支持的参数类型:
sum、avg 支持数值类型
min、max、count 支持任何类---count只计算不为null的值
是否忽略null:
sum、avg、count、min、max、 都绝对忽略null
搭配distinct去重
注意:
和分组函数一起查询的必须是group by之后的字段(只能一对一)
*/
SELECT SUM(`price`) FROM `query1`;
SELECT MIN(`price`) AS 最小, #多个函数一起查
MAX(`price`) 最大
FROM `query1`;
#搭配distinct去重
SELECT SUM(DISTINCT price) FROM `query1`;
SELECT COUNT(DISTINCT place) FROM `query1`;
#count 详细介绍
SELECT COUNT(id)FROM `query1`; #统计字段不为零的行数
SELECT COUNT(*) FROM `query1`; #统计所有行数
SELECT COUNT(1) FROM `query1`; #统计所有行数
#分组查询
/*
语法:
select 分组函数,列(要求出现在group by的后面)
from 表
group by 分组的列表
【order by】;
注意:
查询列表必须特殊,要求是分组函数和group by后出现的字段
特点:
1、筛选条件:
分组前筛选----原表里可以看到---where----在from之后
分组后筛选----原表里看不到----having----在group by之后
A、分组函数做条件肯定是放在having之后
B、能能用分组前筛选优先使用分组前筛选
*/
#简单分组查询
SELECT AVG(price) AS 平均,place
FROM `query1`
GROUP BY `place`
ORDER BY 平均 DESC;
#筛选条件分组查询---where:分组前筛选
SELECT AVG(price) AS 平均,place
FROM `query1`
WHERE `place` LIKE '%国%'
GROUP BY place
ORDER BY 平均;
#复杂查询之根据查询结果筛选再查询---having:分组后筛选
SELECT COUNT(*),place
FROM `query1` # 先查询
GROUP BY place #----------------
HAVING COUNT(*)>=2; #根据结果筛选