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

累行的博客 2020.09.09 06:01:49 阅读数：434

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

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

#常用函数

#字符串函数

#字符串连接CONCAT

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

转大小写UPPER,LOWER

复制收展SQLSELECT ename,UPPER(ename) FROM tb_emp;
 
SELECT ename,LOWER(ename) FROM tb_emp;1
2
3

#字符串长度

SELECT LENGTH("luo");

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

复制收展SQLSELECT SUBSTR('luolei',2,4);
SELECT SUBSTR('luolei',4);1
2

#返回当前时间

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