常用的数据库基础函数
函数是数据库提供的用来处理数据的
函数由3部分组成
1、函数名
函数是一个运算过程
2、参数列表,参数就是需要被函数进行运算的依据,
写在函数名后面的()里面,参数的个数可以是0,1,多个
参数可以是表中的字段名,也可以是一个直接量
3、返回值
返回值是函数根据参数运算后返回的结果
函数的调用
函数可以使用 select 关键字 进行调用
如果函数中的参数是某张表中的字段,需要跟上 from 表名
如果函数中的参数是一个直接量,不需要跟 from 表名
数学函数
use test_index;
– 测试函数,使用直接量,不使用字段
– -5是直接量
– ABS() 返回绝对值
SELECT ABS(-5);
– 参数是直接量,函数只会执行一次,只返回一个运算结果
– MOD() 返回余数,取模
SELECT MOD(15,7);
– 参数是字段名,后面必须跟表名,函数执行的次数与该字段对应的行数相等,
SELECT MOD(age,3) FROM user;
– CEILING() 向上取整
SELECT CEIL(10.2),CEIL(10.5),CEIL(10.8);
SELECT CEILING(10.2),CEILING(10.5),CEILING(10.8);
– FLOOR()向下取整
SELECT FLOOR(10.2),FLOOR(10.5),FLOOR(10.8);
– ROUND() 四舍五入
SELECT ROUND(10.2),ROUND(10.5),ROUND(10.8);
字符串函数
CONCAT(str1,str2,…) 拼接字符串,参数的个数不固定
SELECT CONCAT('江苏','南京','玄武');
SELECT CONCAT('姓名:',name,' 性别:',sex,' 年龄:',age) FROM user where id=999999;
-- 查询的结果有多少行,这个函数就会执行多少次
SELECT CONCAT('姓名:',name,' 性别:',sex,' 年龄:',age) FROM user;
– LENGTH(str) 长度,LENGTH返回的是字符的的字节长度
– 测试用户1000003 字符长度是11,但字节长度是4*3+7=19
– 在一个字符串里面,
– 有的字符占据一个字节:例如英文的标点符号,数字0-9,英文字母
– 有的字符占据两个字节,
– 有的字符占据三个字节,例如中文汉字,中文标点符号
SELECT name,LENGTH(name) FROM user;
SELECT LENGTH('ABCDEFG'),LENGTH(1234567),LENGTH('江苏南京玄武区'),LENGTH(',。;()《》');
– LOCATE(substr,str) 返回参数1在参数2中第一次出现的位置
SELECT LOCATE('南京','江苏省南京市');
– INSTR(substr,str) 返回参数2在参数1中第一次出现的位置
SELECT INSTR('江苏省南京市','南京');
– LEFT() 从左边截取字符串长度,截取的长度由第二个参数决定
SELECT LEFT('江苏省南京市',3);
– RIGHT() 从右边截取字符串长度,截取的长度由第二个参数决定
SELECT RIGHT('江苏省南京市',3);
– SUBSTRING() 参数1是被截取的字符串,参数2是开始截取的位置,参数3可有有无,若有则从为截取长度,无则截取到字符串最后
SELECT SUBSTRING('江苏省南京市玄武区',4)
SELECT SUBSTRING('江苏省南京市玄武区',4,3)
– LTRIM(str) 去除字符串左边的空格
SELECT LTRIM(' ab cd ');
– RTRIM(str) 去除字符串右边的空格
SELECT RTRIM(' ab cd ');
– TRIM(str) 去除字符串两边的空格
SELECT TRIM(' ab cd ');
– REPLACE(str,from_str,to_str) 用参数3替换掉参数1中所有的参数2
SELECT REPLACE('刷牙牙刷','牙','鞋')
SELECT REPLACE('南京南邮','南','北')
– REVERSE(str) 翻转
SELECT REVERSE('江苏省南京市玄武区');
– INSERT() 在指定位置插入或替代字符串,此INSERT()非彼INSERT()
SELECT INSERT('ABCDE',3,0,'HHH'),INSERT('ABCDE',3,2,'HHH');
– 日期和时间函数
这三个参数都是 无参,直接返回结果
– NOW()返回当前系统的日期时间,格式是 ‘YYYY-MM-DD hh:mm:ss’,对应的数据类型是datetime
– CURDATE()当前日期,格式是 ‘YYYY-MM-DD’,对应的数据类型是date
– CURTIME()当前时间,格式是 ‘hh:mm:ss’,对应的数据类型是time
SELECT NOW(),CURDATE(),CURTIME();
– DAYOFWEEK() 返回今天是本周的第几天(大1)
– 1-星期天 2-星期一 3-星期二 4-星期三 5-星期四 6-星期五 7-星期六
– WEEKDAY() 返回今天是星期几(小1)
– 0-星期一 1-星期二 2-星期三 3-星期四 4-星期五 5-星期六 6-星期天
SELECT DAYOFWEEK('2022-1-13'),WEEKDAY('2022-1-13');
– DAYOFMONTH() 返回当前日期中该月的第几天
SELECT DAYOFMONTH('2022-1-13');
– DAYOFYEAR() 返回当前日期中该年的第几天
SELECT DAYOFYEAR('2022-2-13');
– MONTH() 返回当前日期中第几个月
SELECT MONTH('2022-1-13');
– QUARTER() 返回当前日期的季度
SELECT QUARTER('2022-6-13');
– YEAR() 返回当前日期中的年份
SELECT YEAR('2022-1-13');
– ``反引号
– 加反引号 意思为关键字
– 所有的关键字显示为蓝色,非关键字显示为黑色
– 官方建议加反引号YEAR
的原因,是防止函数名与关键字冲突
– ORDER BY 关键字
– order 这个单词还有一个意思是订单
– 创建一个订单表,表名为order
CREATE TABLE order(
id int PRIMARY KEY auto_increment
);
– 我们希望order在这个语句中不是以关键字被解析的,而是一个普通的表名,加反引号即可
CREATE TABLE `order`(
id int PRIMARY KEY auto_increment
);
– 如果一个名字(表名、字段名、函数名…)与关键字冲突了,那么必须加反引号
– 没有冲突,可加可不加,但官方建议加
– WEEK() | WEEKOFYEAR() 返回当前日期是一年中的第几周
SELECT WEEK('2021-1-13'),WEEKOFYEAR('2021-1-13');
– HOUR() 返回当前时间的第几小时
SELECT HOUR('10:20:22');
SELECT HOUR('2021-1-13 10:20:22');
SELECT HOUR(NOW()),HOUR(CURTIME());
– CURDATE()没有小时不可用
– MINUTE() 返回分钟
SELECT MINUTE(NOW()),MINUTE(CURDATE()),MINUTE(CURTIME());
– SECOND() 返回秒
SELECT SECOND(NOW()),SECOND(CURDATE()),SECOND(CURTIME());
– 日期的加减
– 日期的加减不能直接使用加减运算符,只是数字上的加减
– 应该使用DATE_ADD()函数和DATE_SUB()函数
SELECT CURDATE(),CURDATE()+20,DATE_ADD(CURDATE(),INTERVAL 20 DAY);
– DATE_ADD 在指定日期上加,
– 参数1是指定日期,参数2是指定加减的数字和单位 INTERVAL 加减数字 加减单位
– YEAR MONTH WEEK DAY HOUR MINUTE SECOND
– DATE_SUB() 在指定日期上减
SELECT DATE_ADD(NOW(),INTERVAL 20 YEAR),
DATE_ADD(NOW(),INTERVAL 20 MONTH),
DATE_ADD(NOW(),INTERVAL 20 WEEK),
DATE_ADD(NOW(),INTERVAL 20 DAY),
DATE_ADD(NOW(),INTERVAL 20 HOUR),
DATE_ADD(NOW(),INTERVAL 20 MINUTE),
DATE_ADD(NOW(),INTERVAL 20 SECOND);
SELECT DATE_SUB(NOW(),INTERVAL 20 YEAR),
DATE_SUB(NOW(),INTERVAL 20 MONTH),
DATE_SUB(NOW(),INTERVAL 20 WEEK),
DATE_SUB(NOW(),INTERVAL 20 DAY),
DATE_SUB(NOW(),INTERVAL 20 HOUR),
DATE_SUB(NOW(),INTERVAL 20 MINUTE),
DATE_SUB(NOW(),INTERVAL 20 SECOND);
– 注意 YEAR与YEAR()的区别
– 没括号的是用于加减日期时,指定加减单位的关键字
– 有括号的是用于获取指定日期中某一部分的函数名
– 一个函数的返回值可以作为另一个函数的参数
– TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
– 用来获取两个日期之间相差的天数,参数1是单位,参数2是小日期,参数3是大日期
– 获取日期时间差时,不满一年/月/周/日/时/分/秒 的 都不会算
SELECT NOW(),
TIMESTAMPDIFF(YEAR,‘2021-1-13’,NOW()),
TIMESTAMPDIFF(MONTH,‘2021-12-1’,NOW()),
TIMESTAMPDIFF(WEEK,‘2021-12-1’,NOW()),
TIMESTAMPDIFF(DAY,‘2021-12-1’,NOW()),
TIMESTAMPDIFF(HOUR,‘2021-12-1’,‘2021-12-1 00:00:59’),
TIMESTAMPDIFF(MINUTE,‘2021-12-1’,NOW()),
TIMESTAMPDIFF(SECOND,‘2021-12-1’,NOW());
– 查询7日内快要到期的会员,提醒用户还有多久到期
SELECT u.id,u.name,TIMESTAMPDIFF(DAY,CURDATE(),DATE_ADD(buy_date,INTERVAL v.day_count DAY))
FROM user u INNER JOIN vip_record v
ON u.id=v.u_id
WHERE state=0 AND TIMESTAMPDIFF(DAY,CURDATE(),DATE_ADD(buy_date,INTERVAL v.day_count DAY))<=7;
– 统计函数/聚合函数/分组函数
COUNT() 统计数据行数
– 统计贷款记录有多少行
SELECT COUNT(*) FROM loan_record;
SELECT COUNT(id) FROM loan_record;
– SUM() 求和,该列累加后返回
– AVG() 求平均数,该列累加后求均值返回
– MAX() 求最大值
– MIN() 求最小值
SELECT COUNT(money),SUM(money),AVG(money),MAX(money),MIN(money)
FROM loan_record;
– 统计函数的高级用法-分组统计
求每一个员工办理的贷款金额之和
– 分组统计的关键字是 GROUP BY
SELECT e_id, COUNT(money),SUM(money),AVG(money),MAX(money),MIN(money)
FROM loan_record
GROUP BY e_id;
– 对统计后的数据进行筛选
– 查询平均每笔贷款金额在30W以上的
– WHERE子句是在分组统计之前执行的
– 所以筛选条件应该用HAVING语句,该语句在分组之后执行的
– 官方的设定是分组查询,必须要指定一个分组字段,根据分组字段将数据进行分组
– 统计结束后每一个组只会有一行统计数据
– 因此被分组的字段可以被查询,不是以该字段进行分组的字段不能直接查询,只能用统计函数统计
SELECT e_id, AVG(money)
FROM loan_record
GROUP BY e_id
HAVING AVG(money)>=300000;
– 我们可以给统计函数的运算结果取别名,只用算一次平均值
– 好处可以复用统计结果,减少统计次数
SELECT e_id, AVG(money) avg
FROM loan_record
GROUP BY e_id
HAVING avg>=300000;
– 知道这个员工的其他信息 连表查询
SELECT e_id,e.name,e.job,e.salary,AVG(money) avg
FROM loan_record l INNER JOIN emp e
ON e_id=e.id
GROUP BY e_id
HAVING avg>=300000;
– 数据库系统的执行器会将这句指令拆分为几个任务
– 任务一:查loan_record表 分组 GROUP BY e_id
– 任务二:统计 AVG(money) avg
– 任务三:筛选统计结果 HAVING avg>=300000
– 任务四:连表 做笛卡尔积
– 任务五:剔除连接错误的数据 ON e_id=e.id
– 任务六:查询所需数据 SELECT e_id,e.name,e.job,e.salary,AVG(money) avg
– 统计所有用户贷款的次数,并按次数进行排序
SELECT u_id,u.name,COUNT(u_id) ct
FROM loan_record l INNER JOIN user u
ON u_id = u.id
GROUP BY u_id
HAVING ct >= 3
ORDER BY ct DESC;
– 关键字DISTINCT 去除重复列
– 可以单字段,也可以是多字段联合去重
– 可以用于普通字段
SELECT job
FROM emp;
查询一共有几种岗位
SELECT DISTINCT job
FROM emp;
– 加了其他列DISTINCT就失效了
SELECT DISTINCT job,id,name
FROM emp;
– 对两列去重
SELECT DISTINCT job,salary
FROM emp;
– 也可以用于统计函数去重
SELECT COUNT(DISTINCT job)
FROM emp;
– 统计每个贷款专员手上有几个客户
SELECT e_id,e.name,COUNT(DISTINCT u_id)
FROM emp e INNER JOIN loan_record l
ON e.id=l.e_id
GROUP BY e_id
– 关键字LIMIT 截取指定行
– 查询最近的三条贷款记录
SELECT *
FROM loan_record
ORDER BY loan_date DESC
LIMIT 0,3
– 办理的贷款平均金额最高的员工信息
– 分组 平均
– 降序,取第一个
SELECT e_id,e.name,e.dept,AVG(money) avg
FROM loan_record l INNER JOIN emp e
ON e_id = e.id
GROUP BY e_id
ORDER BY avg DESC
LIMIT 0,1;
– 找出每个贷款专员办理过的最高贷款金额的贷款记录
SELECT e_id,e.name,e.dept,MAX(money) max
FROM loan_record l INNER JOIN emp e
ON e_id = e.id
GROUP BY e_id
– LIMIT不行,没有办法用
– 练习:找出2021年度的销冠,可以用LIMIT
SELECT e_id,e.name,e.dept,SUM(money) sum
FROM loan_record l INNER JOIN emp e
ON e_id = e.id
WHERE YEAR(loan_date)='2021'
GROUP BY e_id
ORDER BY sum DESC
LIMIT 0,1;
– 练习:统计月度销售额,并排序
SELECT MONTH(loan_date) mon,SUM(money) sum
FROM loan_record
GROUP BY mon
ORDER BY sum DESC;
– 查询每笔贷款记录是哪个员工为哪个客户在什么时候办的
– 要求:列出员工姓名/客户姓名/贷款时间/贷款金额
SELECT l.id,e.name,u.name,l.loan_date,l.money
FROM loan_record l INNER JOIN emp e
ON e_id=e.id
INNER JOIN user u
ON u_id=u.id
ORDER BY l.id;
– 练习:查询本月过生日的员工
SELECT name,birthday
FROM emp e
WHERE MONTH(birthday)=MONTH(CURDATE());
– 查询入职满5年的员工
SELECT name,joinday
FROM emp
WHERE TIMESTAMPDIFF(YEAR,joinday,CURDATE())>=5;
– 如果员工薪资 = 月薪salary + 月度销售额的百分之二,计算4月份,每个员工的实际薪资
SELECT e_id 工号,e.name 姓名,e.salary 底薪,SUM(money)/100*2 提成,salary+SUM(money)/100*2 实际薪资
FROM emp e LEFT OUTER JOIN loan_record l
ON e_id=e.id
WHERE MONTH(loan_date)=4
GROUP BY e_id;
ORDER BY e_id DESC
– 2021年每位员工操作的用户用来买房且业绩总额小于200万的业绩 降序排列
SELECT e_id,SUM(money) sum
FROM user u INNER JOIN loan_record l
ON u.id=u_id
INNER JOIN emp e
ON e.id=e_id
WHERE YEAR(l.loan_date)='2021' AND l.explain='买房'
GROUP BY e_id
HAVING sum < 2000000
ORDER BY sum DESC