入门级SQL常见函数

脑图有助于回顾知识点

#常见函数
/*
调用：
	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;   #根据结果筛选