博客

目录

一、常用sql函数

1.concat（）:字符串拼接

2.ifnull(str1,str2):若str1为null,返回str2;否则，返回str1;

4.特别提示：+号与concat()

5.in

6.is / is not

7.安全等于：<=>

8.LENGTH(str)：字符串长度；upper：转大写；lower：转小写；

9.substr(str,a，b)：在str中从索引a开始,截取b个字符

10.示例：substr、upper、lower、concat

11.instr(str1，str2)

12.trim：去除前后空格或其它指定字符

13.lapd、rapd

14.replace(str1,str2,str3):使用str3替换str1中的str2

15、round、ceil、floor、truncate、mod、rand

16、now()、curdate()、str_to_date()、date_format()、datediff

17、流程控制函数：if、case

18：聚合函数：sum、、max 、min 、count

一、常用sql函数

1.concat（）:字符串拼接

特别注意：NULL和任何的数据concat拼接,结果都是NULL；

#将last_name、first_name拼接中间以“，”间隔；
SELECT concat(last_name,',',first_name) as 姓名
from employees;

2.ifnull(str1,str2):若str1为null,返回str2;否则，返回str1;

#若commission_pct为null,返回0，否则返回commission_pct
SELECT IFNULL(commission_pct,0) as 奖金率；

3.根据上面两个函数，示例：拼接可能为null的数据字段值，应该使用ifnull()+concat()

#为了防止commission_pct为null,导致concat（）拼接结果为null，所以使用ifnull（）判断
SELECT CONCAT(first_name,',',last_name,',',job_id,',',IFNULL(commission_pct,0))as OUT_PUT
FROM employees;

4.

特别提示

：+号与concat()

#+号，在sql中，如果拼接字段是字符串数据，它会转换为数据类型，作算术运算；
#如果不能转换为数据，就为0；+号和concat()函数不同；+号虽然在java或python中可以拼接字符，
#但在sql中，是向算术倾向的；

5.in

SELECT last_name,job_id FROM employees WHERE job_id IN ('IT_PORT','AD_VP','AD_PRES');

6.is / is not

特别注意：=号不能判断一个值是null;需要使用：IS;但是注意，is、is not只能用来判断字段值null

SELECT last_name,commission_pct FROM employees WHERE commission_pct is NULL;
#而下面这句则不行
#SELECT last_name,commission_pct FROM employees WHERE commission_pct = NULL;

7.安全等于：<=>

特别注意：<=>这个符号可以判断任何字段值是否等于，包括上面的值为null的情况；

SELECT last_name,commission_pct FROM employees WHERE commission_pct <=> NULL;

8.LENGTH(str)：字符串长度；upper：转大写；lower：转小写；

9.substr(str,a，b)：在str中从索引a开始,截取b个字符

特别注意：sql的索引从1开始

SELECT SUBSTR(last_name,2,3) FROM employees;

10.示例：substr、upper、lower、concat

#姓名首字母大写，其他字母小写，下划线连接
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),LOWER(SUBSTR(last_name,2)),"__",LOWER(first_name)) as 姓名
FROM employees;

11.instr(str1，str2)

返回str2字符串在str1中的起始索引,如果没有就返回0
SELECT INSTR("杨不悔爱上了殷六侠","殷六侠") AS out_put;#返回：7

12.trim：去除前后空格或其它指定字符

SELECT TRIM( '    刘德华          ') AS output;
#刘德华
SELECT TRIM('a' FROM "aaa刘aa德aa华") AS 输出;
#刘aa德aa华

13.lapd、rapd

#lapd:用指定的字符实现左填充指定长度，如果字符长度大于指定长度，则从左保留指定长度字符
SELECT lpad('影视计算机',20,"*") AS 输出;
SELECT lpad('影视计算机',2,"*") AS 输出;
SELECT lpad('计算机',4,"影视") AS 输出;#如果指定填充的字符填充后，大于指定长度，那么之后在指定用于填充字符截取部分进行填充；
#rpad用指定的字符实现右填充指定长度，如果字符长度大于指定长度，则从从保留指定长度字符
SELECT rpad('影视计算机',20,"*") AS 输出;
SELECT rpad('影视计算机',2,"*") AS 输出;
SELECT rpad('计算机',4,"影视") AS 输出;#如果指定填充的字符填充后，大于指定长度，那么之后在指定用于填充字符截取部分进行填充；

14.replace(str1,str2,str3):使用str3替换str1中的str2

SELECT REPLACE('周芷若爱上了张无忌张无忌喜欢周芷若',"周芷若","赵敏")AS out_put;

15、round、ceil、floor、truncate、mod、rand

#数学函数,注意数学函数中的第二个参数一般都是保留几位小数
#round：四舍五入；
SELECT ROUND(-1.65);
SELECT round(1.5678,3);#保留3位小数
#ceil:向上取整,返回>=该参数的最小整数
SELECT ceil(-1.02);
SELECT ceil(1.02);
#floor 向下取整，返回<=该参数的最大整数
SELECT FLOOR(-1.02);
SELECT FLOOR(1.02);
#truncate 截断,参数2表示保留小数位数
SELECT TRUNCATE(1.69999,2);
#mod 取余
SELECT MOD(10,3);
#rand()
#获取随机数，返回0-1之间的小数

16、now()、curdate()、str_to_date()、date_format()、datediff

#日期函数
#now 返回当前系统日期+时间
SELECT NOW();
#curdate 返回当前系统日期,不带时间
SELECT CURDATE();
#curtime 返回当前系统时间,不带日期
SELECT curtime();
#可以指定显示：年、月、日、小时、分钟、秒（year、month、day、hour、minute、second）
SELECT YEAR(NOW()) 年;
SELECT SECOND(NOW()) 秒;
#str_to_date:将日期格式的字符转换成指定格式的日期
SELECT STR_TO_DATE('9-9-2020','%d-%m-%Y') as 年月日;
#date_format:将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日') as 年月日;
#datediff：返回两个日期相差的天数
select DATEDIFF('2020-01-02','2020-01-01');#返回：1

17、流程控制函数：if、case

#if(表达式1，表达式2，表达式3)：1成立返回2，否则返回3 
SELECT if(10<5,"对","错");
SELECT last_name,commission_pct,IF(commission_pct IS NULL,"没奖金，哈哈","有奖金，吃肉")FROM employees;
#case
SELECT salary 合同工资, department_id,
CASE department_id
	WHEN 30 THEN salary*1.1
	WHEN 40 THEN salary*1.2
	WHEN 50 THEN salary*1.3
	ELSE 0
END as 新工资
FROM employees;

18：聚合函数：sum、、max 、min 、count

特别注意：聚合函数都忽略null值，即：遇到null直接跳过；

#sum:求和、avg 平均值、max 最大值、min 最小值、count 计算个数
SELECT SUM(salary) from employees;
SELECT SUM(salary) 求和,avg(salary) 平均,MIN(salary) 最小,MAX(salary) 最大,COUNT(salary) 个数 FROM employees;
#注意：
#1.聚合函数中sum、avg一般用于处理数值型；max、min、count可以处理任何类型；
#2.NULL类型不参与聚合函数运算,即：在执行这几种方法时，null被忽略跳过;
#3.可以和关键字distinct搭配，实现去重的聚合
SELECT sum(DISTINCT salary),sum(salary) FROM employees; #可以看出加了distinct的要小很多
#count()函数最常用：下面两种均是统计表中所有行数：*或常量
SELECT COUNT(*) FROM employees;
SELECT COUNT(1) from employees;#在count()参数添加常量