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

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

数据库的创建参考我的另一篇博客 https://blog.****.net/****luolei/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个

SELECT
   comm,
   IFNULL(comm, 50)
FROM
   tb_emp;

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

SELECT
    comm,
    IF (comm, comm + 50, 50)
FROM
    tb_emp;

#常用聚合函数

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

SELECT
  dept_id,
  AVG(sal),
  SUM(sal),
  SUM(comm),
  MAX(sal),
  MIN(sal)
FROM
  tb_emp
GROUP BY
  dept_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子句"中

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

SELECT
    dept_id AS '部门编号',
    AVG(sal) AS '平均工资'
FROM
    tb_emp
GROUP BY
    dept_id;

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

SELECT
    sal
FROM
    tb_emp
ORDER BY
    sal ASC;

#分组后限定查询结果HAVING

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

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

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

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

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

SELECT
    dept_id AS '部门编号',
    AVG(sal) AS '平均工资'
FROM
    tb_emp
#WHERE AVG(sal)>2000;
GROUP BY 
    dept_id
HAVING
    AVG(sal) > 2000
ORDER BY
    AVG(sal) DESC;

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

SELECT dname FROM tb_dept WHERE deptno IN (
SELECT
    dept_id
FROM
    tb_emp
GROUP BY
    dept_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 =(
SELECT
    dept_id
FROM
    tb_emp
GROUP BY
    dept_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 年月日;

-- 分别获取时间

SELECT

       SYSDATE(),

       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')