MySQL笔记（三）常用系统函数

这是我在学习Mysql之路上做的笔记，今天将它粘出来。这一篇主要是Mysql常用系统函数。有错误的欢迎大家指出。。。

数据库函数的用法、数据库的创建参考我的另一篇博客 https://blog.csdn.net/csdnluolei/article/details/83409945

#常用函数

#字符串函数

#字符串连接CONCAT

SELECT CONCAT(empno,ename,job) FROM tb_emp;

#转大小写UPPER,LOWER

SELECT ename,UPPER(ename) FROM tb_emp;SELECT ename,LOWER(ename) FROM tb_emp;

#字符串长度

SELECT LENGTH("luo");

#截取字符串,是从1开始的计数

SELECT SUBSTR('luolei',2,4);SELECT SUBSTR('luolei',4);

#返回当前时间

SELECT NOW();

#查询入职时间是2018年8月的

SELECT *FROM tb_emp WHERE YEAR(hiredate)='2018' AND MONTH(hiredate)='8';

#插入日期

INSERT INTO tb_emp (empno,ename,job,mgr,hiredate,sal,dept_id)VALUES('7112','Clark','开发工程师','7103','2018-06-21','1600',40);INSERT INTO tb_emp (empno,ename,job,mgr,hiredate,sal,dept_id)VALUES('7113','Clark','开发工程师','7103',NOW(),'1600',40);

#逻辑函数

#函数case when then end

SELECT ename,sal,
CASE
WHEN comm IS NULL THEN 100
ELSE comm
END AS '奖金'
FROM tb_emp;

#IFNULL(expr1,expr2),不为空取第1个

SELECTcomm,IFNULL(comm, 50)
FROMtb_emp;

#IF(expr1,expr2,expr3),不为空取第2个，为空第3个

SELECTcomm,IF (comm, comm + 50, 50)
FROMtb_emp;

#常用聚合函数

#聚合函数是一对值进行运算，返回一个数，也叫组合函数

SELECTdept_id,AVG(sal),SUM(sal),SUM(comm),MAX(sal),MIN(sal)
FROMtb_emp
GROUP BYdept_id;

#COUNT不统计NULL值

SELECT COUNT(*) FROM tb_emp;

#将有奖金的人数统计出来了，不统计NULL值

SELECT COUNT(comm) FROM tb_emp;

#统计共有几种职位

SELECT COUNT(DISTINCT job) FROM tb_emp;

#AVG不统计NULL值

#假如10个人只有4个人有奖金就是 4个人奖金数/4的结果

SELECT AVG(comm) FROM tb_emp;SELECT AVG(IFNULL(comm,0)) FROM tb_emp;

#分组

#group by分组作用是与各聚合函数配合使用，他用来对查询出来的数据进行分组

#分组规则：查询的字段要么出现在"组合函数"，要么出现在"group by子句"中

#查询每个部门的平均工资

SELECTdept_id AS '部门编号',AVG(sal) AS '平均工资'
FROMtb_emp
GROUP BYdept_id;

#ORDER BY 排序（默认ASC升序，DESC降序）

SELECTsal
FROMtb_emp
ORDER BYsal ASC;

#分组后限定查询结果HAVING

#1、WHERE和HAVING都是限定查询，可以同时用，

#2、WHERE在group by 之前，HAVING只能用在GROUP BY后对分组后进行查询

#3、where后的条件表达式里不允许使用聚合函数，而having可以

#4、where 是对数据集进行筛选，group by 是对结果集进行聚集

#查询平均工资大于2000的部门编号，并降序显示

SELECTdept_id AS '部门编号',AVG(sal) AS '平均工资'
FROMtb_emp
#WHERE AVG(sal)>2000;
GROUP BY dept_id
HAVINGAVG(sal) > 2000
ORDER BYAVG(sal) DESC;

#查询平均工资大于2000的部门名称

SELECT dname FROM tb_dept WHERE deptno IN (
SELECTdept_id
FROMtb_emp
GROUP BYdept_id
HAVING AVG(sal)>2000);

#LIMIT指定查询几条数据，常用来分页

SELECT *FROM tb_emp LIMIT 5;

#第2条开始，显示3条

SELECT *FROM tb_emp LIMIT 2,3;

#查询平均工资最高的部门名称

SELECT dname FROM tb_dept WHERE deptno =(
SELECTdept_id
FROMtb_emp
GROUP BYdept_id
ORDER BY AVG(sal) DESC
LIMIT 1
);

#select基本语法顺序

SELECT [* | column|MAX(column)]

FROM tbname

[JOIN ON]

[WHERE]

[GROUP BY]

[HAVING]

[ORDER BY]

[LIMIT]

 

-- 获取当前系统日期时间：

SELECT SYSDATE() AS 系统日期时间;

-- 获取当前系统年月日：

SELECT CURRENT_DATE AS 年月日;

-- 分别获取时间

SELECTSYSDATE(),YEAR (CURRENT_DATE) AS 年,MONTH (CURRENT_DATE) AS 月,DAY (CURRENT_DATE) AS 日,TIME(SYSDATE()) AS 系统时间,HOUR(SYSDATE()) AS 系统小时,MINUTE(SYSDATE()) AS 系统分钟,SECOND(SYSDATE()) AS 系统秒,MICROSECOND(SYSDATE()) AS 系统毫秒;SELECT DATE_FORMAT(NOW(),'%Y')SELECT DATE_FORMAT(NOW(),'%Y%m%d')