常用函数-单行函数
调用函数:
SELECT 函数名() 【FROM 表】;
特点:
- 叫什么(函数名)
- 干什么的(函数功能)
函数的分类:
- 单行函数
- concat
- lenght
- ifnull
- 等
- 分组函数
- 做统计使用,又称为统计函数,聚合函数
单行函数
1.字符函数
- 0.COUNT()统计
1.显示雇员的总数,以及获得补助的雇员数
思路:获得补助的雇员数,就是comm列为非null,就是count(列),如果该列的值为null,是不会统计
//不统计列有null的值 mysql> select count(*),count(dept_no) from dept_emp; +----------+----------------+ | count(*) | count(dept_no) | +----------+----------------+ | 331603 | 331603 | +----------+----------------+ 1 row in set (0.05 sec) //count统计某列有null值 mysql> select count(*),count(IFNULL(dept_no,1)) from dept_eemp; +----------+--------------------------+ | count(*) | count(IFNULL(dept_no,1)) | +----------+--------------------------+ | 331603 | 331603 | +----------+--------------------------+ 1 row in set (0.04 sec)
2.统计部门领导总人数,有些领导多次出现
mysql> select count(DISTINCT dept_no) from dept_manager; +-------------------------+ | count(DISTINCT dept_no) | +-------------------------+ | 9 | +-------------------------+ 1 row in set (0.00 sec)
- 1.lenght 获取参数的字节个数
SELECT LENGTH('john'); SELECT LENGTH('张三丰adadad');
#查看系统的编码,UTF-8中一个汉字等于3个字节
SHOW VARIABLES LIKE '%char%'
- 2.concat 拼接字符
SELECT CONCAT(last_name,'_',first_name) AS name FROM employess;
- 3.upper(大写),lower(小写)
SELECT UPPER('john'); SELECT LOWER('john');
#实例:将姓变大写,名变小写,然后拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM employees;
- 4.substr 截取指定字符长度
- 4.leet 从列的左边。去指定长度的字符
- 4.right 从列的有边,去指定长度的字符
select leet(name,2) from emp; select right(name,2) from emp;
#显示陆展元
mysql> select substr('李莫愁爱上了陆展元',7) AS out_put; +-----------+ | out_put | +-----------+ | 陆展元 | +-----------+ 1 row in set (0.01 sec)
#显示李莫愁
mysql> select substr('李莫愁爱上了陆展元',1,3) AS out_put; +-----------+ | out_put | +-----------+ | 李莫愁 | +-----------+ 1 row in set (0.00 sec)
练习1,姓名中首字符大写,其它字符小写然后用_拼接,显示出来
SELECT CONCAT(UPPER(SUBSTR(list_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) 姓名 FROM employees;
- 5.instr 用于返回子串在大的字符串中的起始位置
mysql> SELECT INSTR('杨不悔爱上了殷六侠','殷六侠') AS out_put; +---------+ | out_put | +---------+ | 7 | +---------+ 1 row in set (0.00 sec)
#如果找不到信息,返回0
mysql> SELECT INSTR('杨不悔爱上了殷六侠','殷ba侠') AS out_put; +---------+ | out_put | +---------+ | 0 | +---------+ 1 row in set (0.00 sec)
- 6.trim 默认去除前后的空格
- LTRIM 去除左边的空格
- RTRIM 去除右边的空格
select LTRIM(' xxxx') from xx; select RTRIM ('xxx ') from xx;
mysql> select length(' xxx '); +-------------------+ | length(' xxx ') | +-------------------+ | 7 | +-------------------+ 1 row in set (0.00 sec) mysql> select length(trim(' xxx ')); +-------------------------+ | length(trim(' xxx ')) | +-------------------------+ | 3 | +-------------------------+ 1 row in set (0.00 sec)
#还可以去除前后其它字符
mysql> select trim('a' from 'aaaa张aaaa翠aaaa山aaaaaaaaa');
+-------------------------------------------------+
| trim('a' from 'aaaa张aaaa翠aaaa山aaaaaaaaa') |
+-------------------------------------------------+
| 张aaaa翠aaaa山 |
+-------------------------------------------------+
1 row in set (0.00 sec)
- 7.lpad 左填充,指定总长度
- rpad 右边填充,指定总长度
mysql> select lpad('殷素素',10,'*');
+--------------------------+
| lpad('殷素素',10,'*') |
+--------------------------+
| *******殷素素 |
+--------------------------+
1 row in set (0.00 sec)
##rpad
mysql> select rpad('殷素素',10,'*');
+--------------------------+
| rpad('殷素素',10,'*') |
+--------------------------+
| 殷素素******* |
+--------------------------+
1 row in set (0.00 sec)
- 8.replace 替换
select enam,REPLACE (job,'MANAGER','XX') from emp;
mysql> select replace ('周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') AS out_put;
+--------------------------------------+
| out_put |
+--------------------------------------+
| 赵敏赵敏张无忌爱上了赵敏 |
+--------------------------------------+
1 row in set (0.00 sec)
2.数学函数
- ASB()绝对值
- BIN()十进制转二进制
- CONV()进制转换
//把数字8,认为是10进制,转换为2进制
mysql> select conv(8,10,2);
+--------------+
| conv(8,10,2) |
+--------------+
| 1000 |
+--------------+
1 row in set (0.00 sec)
mysql> select conv(8,10,10);
+---------------+
| conv(8,10,10) |
+---------------+
| 8 |
+---------------+
1 row in set (0.00 sec)
- HEX()转换为16进制
- 1.round ()四舍五入(默认小数点后一位)
mysql> select round(1.611); +--------------+ | round(1.611) | +--------------+ | 2 | +--------------+ 1 row in set (0.00 sec) mysql> select round(1.615,2); +----------------+ | round(1.615,2) | +----------------+ | 1.62 | +----------------+ 1 row in set (0.00 sec) mysql> select round(1.614,2); +----------------+ | round(1.614,2) | +----------------+ | 1.61 | +----------------+ 1 row in set (0.00 sec)
- 2.ceil 向上取整数(值等于自己不取)
mysql> select ceil('1.11'); +--------------+ | ceil('1.11') | +--------------+ | 2 | +--------------+ 1 row in set (0.00 sec) mysql> select ceil('1.0001'); +----------------+ | ceil('1.0001') | +----------------+ | 2 | +----------------+ 1 row in set (0.01 sec)
- 3.floor 向下取整(值等于自己时不变)
mysql> select floor('1.999'); +----------------+ | floor('1.999') | +----------------+ | 1 | +----------------+ 1 row in set (0.00 sec) mysql> select floor('-1.999'); +-----------------+ | floor('-1.999') | +-----------------+ | -2 | +-----------------+ 1 row in set (0.00 sec)
- 4.truncate 截断(默认从小数点开始)
mysql> select truncate(1.55,5); +------------------+ | truncate(1.55,5) | +------------------+ | 1.55000 | +------------------+ 1 row in set (0.00 sec) mysql> select truncate(1.55,1); +------------------+ | truncate(1.55,1) | +------------------+ | 1.5 | +------------------+ 1 row in set (0.00 sec)
- 5.mod 取余
mysql> select mod(10,3); +-----------+ | mod(10,3) | +-----------+ | 1 | +-----------+ 1 row in set (0.00 sec) mysql> select mod(10,2); +-----------+ | mod(10,2) | +-----------+ | 0 | +-----------+ 1 row in set (0.00 sec)
- 6.rand 随机
mysql> select rand(); +--------------------+ | rand() | +--------------------+ | 0.9024876457662206 | +--------------------+ 1 row in set (0.00 sec)
1-100以内随机值
mysql> select floor(1+rand()*99);
+--------------------+
| floor(1+rand()*99) |
+--------------------+
| 87 |
+--------------------+
1 row in set (0.00 sec)
mysql> select floor(1+rand()*99);
+--------------------+
| floor(1+rand()*99) |
+--------------------+
| 65 |
+--------------------+
1 row in set (0.00 sec)
mysql> select floor(1+rand()*99);
+--------------------+
| floor(1+rand()*99) |
+--------------------+
| 62 |
+--------------------+
1 row in set (0.00 sec)
3.日期函数
- DATE_ADD(DATE2,INTERVAL d_value d_type) 在date2中加上日期或时间
//查询在10分钟内发布的新闻 SELECT * FROM mes WHERE DATE_ADD(send_time, INTERVAL 10 MINUTE) >= NOW() MINUTE 分钟 YEAR 天
- DATE_SUN(date2,INTERVAL d_value d_type) 在date2上减去一个时间
- DATEDIFF(date1,date2) date1-date2两个日期差(结果天)
//请在mysql的sql语句中求出2011-11-11 和1990-1-1相差多少天 SELECT DATEDIFF('xxx','xxx') //如果你能活到100岁,球你还能活多少天 mysql> mysql> select DATEDIFF(DATE_ADD('1986-11-11',INTERVAL 100 YEAR), NOW()); +-----------------------------------------------------------+ | DATEDIFF(DATE_ADD('1986-11-11',INTERVAL 100 YEAR), NOW()) | +-----------------------------------------------------------+ | 23881 | +-----------------------------------------------------------+ 1 row in set (0.00 sec)
- TIMEDIFF('xx','xx') 单位时分秒
- YEAR|MONTH|DAY
SELECT YEAR(NOW()) FROM DULA; SELECT MONTH(NOW()) FROM DULA; SELECT DAY(NOW()) FROM DULA;
- UNIX_TIMESTAMP()返回1970-1-1到现在的秒数
- 1.now 返回当前select语句执行的时间
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2021-03-21 12:39:41 |
+---------------------+
1 row in set (0.00 sec)
now显示毫秒
mysql> select now(6);
+----------------------------+
| now(6) |
+----------------------------+
| 2021-06-10 23:39:41.474814 |
+----------------------------+
1 row in set (0.00 sec)
当前时间+1天
- hour 小时
- minute 分钟
mysql> select date_add(now(),interval 1 day);
+--------------------------------+
| date_add(now(),interval 1 day) |
+--------------------------------+
| 2021-06-11 23:47:36 |
+--------------------------------+
1 row in set (0.00 sec)
当前时间-1天
mysql> select date_add(now(),interval -1 day);
+---------------------------------+
| date_add(now(),interval -1 day) |
+---------------------------------+
| 2021-06-09 23:48:16 |
+---------------------------------+
1 row in set (0.00 sec)
- 1.sysdate();执行到这个韩函数的时间
mysql> select now(),sysdate(),sleep(3),now(),sysdate(); +---------------------+---------------------+----------+---------------------+---------------------+ | now() | sysdate() | sleep(3) | now() | sysdate() | +---------------------+---------------------+----------+---------------------+---------------------+ | 2021-06-10 23:44:13 | 2021-06-10 23:44:13 | 0 | 2021-06-10 23:44:13 | 2021-06-10 23:44:16 | +---------------------+---------------------+----------+---------------------+---------------------+ 1 row in set (3.00 sec)
- 2.curdate 返回当前系统日期,不包含时间
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2021-03-21 |
+------------+
1 row in set (0.05 sec)
- 3.curtime 返回当前时间,不包含日期
mysql> select curtime(); +-----------+ | curtime() | +-----------+ | 12:42:24 | +-----------+ 1 row in set (0.00 sec)
- 4.YEAR 年
- month 月
- day 日
mysql> select year(now()); +-------------+ | year(now()) | +-------------+ | 2021 | +-------------+ 1 row in set (0.00 sec) mysql> select year('1995-1-1'); +------------------+ | year('1995-1-1') | +------------------+ | 1995 | +------------------+ 1 row in set (0.00 sec)
还可以筛选指定表的列信息
mysql> select * from xx; +------+---------+ | id | name | +------+---------+ | 1 | Beijing | | 2 | TianT | | 3 | XianT | | 4 | WianT | | 4 | 2ianT | +------+---------+ 5 rows in set (0.01 sec) mysql> select year(id) from xx; +----------+ | year(id) | +----------+ | NULL | | NULL | | NULL | | NULL | | NULL | +----------+ 5 rows in set, 5 warnings (0.00 sec)
- 5.str_to_date 将日期格式的字符转换为指定格式的日期
mysql> select str_to_date('2000-1-3','%Y-%d-%m');
+------------------------------------+
| str_to_date('2000-1-3','%Y-%d-%m') |
+------------------------------------+
| 2000-03-01 |
+------------------------------------+
1 row in set (0.00 sec)c
查询入职日期为1992-4-3的员工信息
SELECT * FROM employees WHERE hiredate= STR_TO_DATE('4-3 1992','%c-%d %Y');
- 6.date_format 将日期转换成字符
mysql> select date_format('2018/5/5','%Y年%m月%d日');
+-------------------------------------------+
| date_format('2018/5/5','%Y年%m月%d日') |
+-------------------------------------------+
| 2018年05月05日 |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql> select date_format(now(),'%Y年%m月%d日') as out_put;
+-------------------+
| out_put |
+-------------------+
| 2021年03月21日 |
+-------------------+
1 row in set (0.00 sec)
练习1,查询有降序的员工名和入职日期(xx月/xx日 xx年)
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %Y年' 入职日期 FROM employees WHERE commission_pct IN NOT NULL ORDEY BY last_name ASC;
- 7.DATEIFF 算日期差值函数单位天
mysql> select datediff(now(),'1999-2-2'); +----------------------------+ | datediff(now(),'1999-2-2') | +----------------------------+ | 8102 | +----------------------------+ 1 row in set (0.00 sec)
mysql> select datediff('2021-4-9','2021-4-1'); +---------------------------------+ | datediff('2021-4-9','2021-4-1') | +---------------------------------+ | 8 | +---------------------------------+ 1 row in set (0.00 sec)
- 8.unix_timestamp 把当前直接转行为时间戳
mysql> select unix_timestamp(now());
+-----------------------+
| unix_timestamp(now()) |
+-----------------------+
| 1623338914 |
+-----------------------+
1 row in set (0.00 sec)
- 9.FROM_UNIXTIME 将时间戳转换为指定格式的时间
其它函数
1.查看系统版本
SELECT VERSION();
2.查看当前位置
SELECT DATABASE();
3.查看当前用户
SELECT USER();
4.对用户密码进行加密
MD5()
流程控制函数
- 1.if函数:if else的效果
mysql> select if(10>5,'大','肖');
+----------------------+
| if(10>5,'大','肖') |
+----------------------+
| 大 |
+----------------------+
1 row in set (0.00 sec)c
IFNULL(expr1,expr2) 如果expr1不为空NULL,则返回expr1,否则返回expr2
mysql> SELECT IFNULL(NULL,'chain') FROM DUAL;
+----------------------+
| IFNULL(NULL,'chain') |
+----------------------+
| chain |
+----------------------+
1 row in set (0.00 sec)
1.查询表emp,如果comm是null,则显示0.0
SELECT ename,IFNULL(COMM,0.0) FROM emp;
2.如果emp表的job是CLERK则显示职员,如果是MANAGER则显示经理,如果是SALESMAN则显示销售人员,其它正常显示
SELECT ename,(select case where job = 'CLERK' THEN '职员' where job = 'MANAGER' THEN '经理' where job = 'SALESMAN' THEN '销售人员' ELSE job END) AS job FROM emp;
- 2.case函数的
使用一: 函数 case的效果
查询员工的工资,要求部分号=30.,显示工资为1.1倍,部门号=40,显示员工资为1.2倍,部门号=50,显示员工资为1.3倍,其他部门,显示的工资为原工资
SELECT slary 原始工资,department_id, CASE department_id WHEN 30 THEN salary*1.1 WHEN 40 THEN salary*1.2 WHEN 50 THEN salary*1.3 ELSE salary END AS 新工资;
使用二: 类似于 多重if
实例一:查询员工的工资的情况
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000,显示C级别
否则,显示D级别
SELECT salary, CASE WHEN salary>20000 THEN 'A' WHEN salary>15000 THEN 'B' WHEN salary>10000 THEN 'C' ELSE ‘D' END AS 工资级别 FROM employees;
单行函数练习题
1.显示系统时间(注:日期+时间)
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2021-03-22 18:27:03 |
+---------------------+
1 row in set (0.00 sec)
2.查询员工号,姓名,工资,以及工资提高百分之20%后的结果(NEW salary)
SELECT employee_id,last_name,salary,salary*1.2 "new salary" FROM employees;
3.将员工的姓名按首字母排序,并写出姓名的长度(LENGTH)
SELECT LENTH(last_name),SUBSTR(last_name,1,1) 首字符 FROM xxx ORDER BY 首字符
4.做一个查询,产生下面的结果
<last_name> earns <salary> monthly but wants <salary*3> Dream Salary
King earns 24000 monthly but wants 72000
SELECT CONCAT(last_name,' earns ',salary,' monthly but wants ',salary*3) AS "Dream Salary" FROM employees;
5.使用CASE-WHEN,按照下面的条件:
job grade
AD_PRES A
ST_MAN B
IT_PROG C
SELECT job_id AS job, CASE job_id WHEN 'AD_PRES' THEN 'A' WHEN 'ST_MAN' THEN 'B' WHEN 'IT_PROG' THEN 'C' END AS grade FROM employees ;

评论