【MySQL】SQL常用函数总结

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: 工资等级表创建语句如下:

补充:来自oracle 9i的经典测试表


表1:emp员工表


员工表创建语句如下:


DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',
  `ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',
  `job` varchar(9) DEFAULT NULL COMMENT '雇员职位',
  `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',
  `hiredate` datetime DEFAULT NULL COMMENT '雇佣时间',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',
  `comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',
  `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
);


接下来向员工表中插入一些数据:


insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, 'TURNER', 'SALESMAN', 7698,'1981-09-08', 1500, 0, 30);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);


结果显示:

453c2fe146f449c9b8f10b09e3fa3f88.png


表2:dept部门表


部门表创建语句如下:


DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `deptno` int(2) unsigned zerofill NOT NULL COMMENT '部门编号',
  `dname` varchar(14) DEFAULT NULL COMMENT '部门名称',
  `loc` varchar(13) DEFAULT NULL COMMENT '部门所在地点'
);


部门表数据插入:


insert into dept (deptno, dname, loc)
values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept (deptno, dname, loc)
values (20, 'RESEARCH', 'DALLAS');
insert into dept (deptno, dname, loc)
values (30, 'SALES', 'CHICAGO');
insert into dept (deptno, dname, loc)
values (40, 'OPERATIONS', 'BOSTON');


结果显示:


fb4f7e507ffa4c06b4bd73f589ca614d.png


表3:salgrade工资等级表


工资等级表创建语句如下:


DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
  `grade` int(11) DEFAULT NULL COMMENT '等级',
  `losal` int(11) DEFAULT NULL COMMENT '此等级最低工资',
  `hisal` int(11) DEFAULT NULL COMMENT '此等级最高工资'
);


数据插入:


insert into salgrade (grade, losal, hisal) values (1, 700, 1200);
insert into salgrade (grade, losal, hisal) values (2, 1201, 1400);
insert into salgrade (grade, losal, hisal) values (3, 1401, 2000);
insert into salgrade (grade, losal, hisal) values (4, 2001, 3000);
insert into salgrade (grade, losal, hisal) values (5, 3001, 9999);


结果显示:


307e5f4c6a7f479588986f9912b1c3ac.png


一. 聚合函数


1. 函数介绍


聚合函数又叫组函数,通常是对表中的数据进行统计和计算,一般结合分组(group by)来使用,用于统计和计算分组数据。


函数特点


每个组函数只能接收一个参数(字段名或者表达式) 统计结果中默认忽略字段为NULL的记录,要想列值为NULL的行也参与组函数的计算,必须使用IFNULL函数对NULL值做转换。

不允许嵌套使用组函数,比如sum(max(xx))。

可以在参数前加上DISTINCT先进行数据去重,然后在执行函数。

函数概览


函数 说明

COUNT([DISTINCT] expr) 返回查询到的数据的 数量

SUM([DISTINCT] expr) 返回查询到的数据的 总和,不是数字没有意义

AVG([DISTINCT] expr) 返回查询到的数据的 平均值,不是数字没有意义

MAX([DISTINCT] expr) 返回查询到的数据的 最大值,不是数字没有意义

MIN([DISTINCT] expr) 返回查询到的数据的 最小值,不是数字没有意义


2. 使用举例


为了方便举例,在这里新建一张学生成绩表,它包括如下5个字段:


id:学生的学号,设为自增长主键。

name:学生姓名,不允许为空。

chinese:语文成绩,可以为空,默认0.0分。

math:数学成绩,可以为空,默认0.0分。

english:英语成绩,可以为空,默认0.0分。

mysql> create table if not exists TestScores(
    -> id int unsigned primary key auto_increment,
    -> name varchar(20) not null,
    -> chinese float default 0.0,
    -> math float default 0.0,
    -> english float default 0.0
    -> );
Query OK, 0 rows affected (0.02 sec)
mysql> desc TestScores;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20)      | NO   |     | NULL    |                |
| chinese | float            | YES  |     | 0       |                |
| math    | float            | YES  |     | 0       |                |
| english | float            | YES  |     | 0       |                |
+---------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)


接下来向该表中插入一些数据:


mysql> insert into TestScores(name, chinese, math, english) values
    -> ('曹操', 67, 98, 56),
    -> ('孙权', 87, 78, 77),
    -> ('孙策', 88, 98, 90),
    -> ('刘备', 82, 84, 67),
    -> ('程咬金', 55, 85, 45),
    -> ('孙尚香', 70, 73, 78),
    -> ('诸葛亮', 75, 65, 30);
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0


1、统计班级共有多少同学


使用 * 做统计:


mysql> select * from TestScores;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 曹操      |      67 |   98 |      56 |
|  2 | 孙权      |      87 |   78 |      77 |
|  3 | 孙策      |      88 |   98 |      90 |
|  4 | 刘备      |      82 |   84 |      67 |
|  5 | 程咬金    |      55 |   85 |      45 |
|  6 | 孙尚香    |      70 |   73 |      78 |
|  7 | 诸葛亮    |      75 |   65 |      30 |
+----+-----------+---------+------+---------+
7 rows in set (0.00 sec)
mysql> select count(*) from TestScores;
+----------+
| count(*) |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)



当然我们也可以把统计出来的结果重命名:


mysql> select count(*) as count_num from TestScores;
+-----------+
| count_num |
+-----------+
|         7 |
+-----------+
1 row in set (0.00 sec)


2、统计本次考试的数学成绩分数个数


以math字段为参数做统计,如果有NULL的话不会计入结果:


mysql> select * from TestScores;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 曹操      |      67 |   98 |      56 |
|  2 | 孙权      |      87 |   78 |      77 |
|  3 | 孙策      |      88 |   98 |      90 |
|  4 | 刘备      |      82 |   84 |      67 |
|  5 | 程咬金    |      55 |   85 |      45 |
|  6 | 孙尚香    |      70 |   73 |      78 |
|  7 | 诸葛亮    |      75 |   65 |      30 |
+----+-----------+---------+------+---------+
7 rows in set (0.00 sec)
mysql> select count(math) from TestScores;
+-------------+
| count(math) |
+-------------+
|           7 |
+-------------+
1 row in set (0.00 sec)



此外,我们还可以使用DISTINCT先去重再统计,得到的是去重后的成绩数量:


mysql> select count(distinct math) from TestScores;
+----------------------+
| count(distinct math) |
+----------------------+
|                    6 |
+----------------------+
1 row in set (0.00 sec)


3、统计数学成绩总分


使用sum聚合函数来计算数据的和。


mysql> select sum(math) from TestScores;
+-----------+
| sum(math) |
+-----------+
|       581 |
+-----------+
1 row in set (0.00 sec)


4、统计总平均分


使用avg聚合函数来计算平均值。


mysql> select avg(chinese+math+english) from TestScores;
+---------------------------+
| avg(chinese+math+english) |
+---------------------------+
|        221.14285714285714 |
+---------------------------+
1 row in set (0.00 sec)


5、返回英语最高分


使用max聚合函数来统计数据中的最大值。


mysql> select max(english) from TestScores;
+--------------+
| max(english) |
+--------------+
|           90 |
+--------------+
1 row in set (0.00 sec)


PS:对应的还有个min聚合函数用来统计数据中的最小值。


3. group by子句的使用


在select中使用group by子句可以对指定列进行分组查询,聚合函数常常需要结合group by子句一起使用。


举例:使用文章最开始的oracle 9i经典测试表


1、显示每个部门的平均工资和最高工资


// 1、查看emp表的数据
mysql> select * from emp;
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)
// 2、按照部门号对所有员工分组,然后以组为单位计算组内所有员工的平均工资和最高工资
mysql> select deptno, avg(sal), max(sal) from emp group by deptno;
+--------+-------------+----------+
| deptno | avg(sal)    | max(sal) |
+--------+-------------+----------+
|     10 | 2916.666667 |  5000.00 |
|     20 | 2175.000000 |  3000.00 |
|     30 | 1566.666667 |  2850.00 |
+--------+-------------+----------+
3 rows in set (0.01 sec)



PS:这里必须使用group by字句进行分组然后才能计算,因为我们最开始还查询了部门号deptno。


// error:没有分组的话聚合函数默认统计所有员工的数据,但是这里select又查询了部门号deptno
// 这样同时显示出来会有歧义,所以要使用group by进行分组
mysql> select deptno, avg(sal), max(sal) from emp;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'scott.emp.deptno'; this is incompatible with sql_mode=only_full_group_by
// 不查询部门号deptno的话就没什么问题,这时统计的是所有员工的数据
mysql> select avg(sal), max(sal) from emp;
+-------------+----------+
| avg(sal)    | max(sal) |
+-------------+----------+
| 2073.214286 |  5000.00 |
+-------------+----------+
1 row in set (0.00 sec)


2、显示每个部门的每种岗位的平均工资和最低工资


2a6c9fec52664791b4e81797ed9b73fb.png

使用group by先对部门分组然后内部进行岗位分组,最后使用聚合函数计算结果:


// 1、查询emp中表的数据
mysql> select * from emp;
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)
// 2、group by后面跟需要分组的字段,有先后顺序
mysql> select avg(sal) 平均工资, min(sal) 最低工资, job, deptno from emp group by deptno, job;
+--------------+--------------+-----------+--------+
| 平均工资     | 最低工资     | job       | deptno |
+--------------+--------------+-----------+--------+
|  1300.000000 |      1300.00 | CLERK     |     10 |
|  2450.000000 |      2450.00 | MANAGER   |     10 |
|  5000.000000 |      5000.00 | PRESIDENT |     10 |
|  3000.000000 |      3000.00 | ANALYST   |     20 |
|   950.000000 |       800.00 | CLERK     |     20 |
|  2975.000000 |      2975.00 | MANAGER   |     20 |
|   950.000000 |       950.00 | CLERK     |     30 |
|  2850.000000 |      2850.00 | MANAGER   |     30 |
|  1400.000000 |      1250.00 | SALESMAN  |     30 |
+--------------+--------------+-----------+--------+
9 rows in set (0.00 sec)



PS:在select后面的列名称(除聚合函数外),如果后续我们要进行group by分组,那么凡是在select中出现的原表中的列名称,也必须在group by中出现。


// error:查询了select字段,但是没有以它进行分组
mysql> select avg(sal) 平均工资, min(sal) 最低工资, job, deptno from emp group by deptno;
ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'scott.emp.job' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
// succeed:使用了聚合函数,查询的字段必须都分组
mysql> select avg(sal) 平均工资, min(sal) 最低工资, job, deptno from emp group by deptno, job;
+--------------+--------------+-----------+--------+
| 平均工资     | 最低工资     | job       | deptno |
+--------------+--------------+-----------+--------+
|  1300.000000 |      1300.00 | CLERK     |     10 |
|  2450.000000 |      2450.00 | MANAGER   |     10 |
|  5000.000000 |      5000.00 | PRESIDENT |     10 |
|  3000.000000 |      3000.00 | ANALYST   |     20 |
|   950.000000 |       800.00 | CLERK     |     20 |
|  2975.000000 |      2975.00 | MANAGER   |     20 |
|   950.000000 |       950.00 | CLERK     |     30 |
|  2850.000000 |      2850.00 | MANAGER   |     30 |
|  1400.000000 |      1250.00 | SALESMAN  |     30 |
+--------------+--------------+-----------+--------+
9 rows in set (0.00 sec)



3、显示平均工资低于2000的部门和它的平均工资


// 1、以部门为单位分组,统计每个部门的平均工资
mysql> select avg(sal), deptno from emp group by deptno;
+-------------+--------+
| avg(sal)    | deptno |
+-------------+--------+
| 2916.666667 |     10 |
| 2175.000000 |     20 |
| 1566.666667 |     30 |
+-------------+--------+
3 rows in set (0.00 sec)
// error:分组后的数据不能使用where进行筛选
mysql> select avg(sal), deptno from emp group by deptno where avg(sal)<2000;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where avg(sal)<2000' at line 1
// succeed:分组后的数据只能用having字句进行筛选
mysql> select avg(sal), deptno from emp group by deptno having avg(sal)<2000;
+-------------+--------+
| avg(sal)    | deptno |
+-------------+--------+
| 1566.666667 |     30 |
+-------------+--------+
1 row in set (0.00 sec)



知识点补充


where适用于原表数据的筛选;having适用于对分组后的表进行筛选。

select时各种SQL字句的执行顺序:

f538a5d4063d413b8fae0922afc57f23.png


4. 聚合函数使用注意事项


聚合函数是做统计用的,必须严格匹配格式:select 聚合函数(字段) from 表名使用,而不能单独作为表达式去使用,除非有进行分组。


举例1:显示工资最高的员工的名字和工作岗位



// 错误用法,聚合函数不能单独作为表达式去使用
mysql> select ename, max(sal) from emp;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'scott.emp.ename'; this is incompatible with sql_mode=only_full_group_by
// 错误用法:
mysql> select ename, sal from emp where sal=max(sal);
ERROR 1111 (HY000): Invalid use of group function
// 正确用法
mysql> select ename, sal from emp where sal=(select max(sal) from emp);
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
+-------+---------+
1 row in set (0.02 sec)



举例二:显示每个部门的平均工资和最高工资


mysql> select deptno, avg(sal), max(sal) from emp group by deptno;
+--------+-------------+----------+
| deptno | avg(sal)    | max(sal) |
+--------+-------------+----------+
|     10 | 2916.666667 |  5000.00 |
|     20 | 2175.000000 |  3000.00 |
|     30 | 1566.666667 |  2850.00 |
+--------+-------------+----------+
3 rows in set (0.01 sec)


二. 日期函数


1. 函数概览


函数名称 描述

current_date() 当前日期

current_time() 当前时间

current_timestamp 当前日期时间

date_add(data, interval, d_value_type) 在date中添加日期或时间

interval后的数值单位可以是:year day minute second

date_sub(date, interval d_value_type) 在date中减去日期或时间

interval后的数值单位可以是:year day minute second

datediff(date1, date2) 两个日期的差,单位是天

now() 当前日期时间

date(datetime) 只显示日期部分

time 只显示时间部分


2. 使用举例


1、获得当前日期


// 使用函数current_date()函数
mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2022-06-30     |
+----------------+
1 row in set (0.00 sec)


2、获得当前时间


// 使用current_time()函数
mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 12:57:46       |
+----------------+
1 row in set (0.00 sec)


3、获得当前时间日期


// 方法一:使用current_timestamp()函数
mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2022-06-30 12:58:54 |
+---------------------+
1 row in set (0.00 sec)
// 方法二:使用now()函数
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2022-06-30 12:59:48 |
+---------------------+
1 row in set (0.00 sec)



4、date_add()的使用


// 正整数的话就执行加操作
mysql> select date_add('2001-07-10', interval 3 day);
+----------------------------------------+
| date_add('2001-07-10', interval 3 day) |
+----------------------------------------+
| 2001-07-13                             |
+----------------------------------------+
1 row in set (0.02 sec)
// 负整数的话就是执行减操作
mysql> select date_add('2001-07-10', interval -3 day);
+-----------------------------------------+
| date_add('2001-07-10', interval -3 day) |
+-----------------------------------------+
| 2001-07-07                              |
+-----------------------------------------+
1 row in set (0.00 sec)
// interval后面数据的单位可以是year day minute second
mysql> select date_add('2001-07-10', interval 3 second);
+-------------------------------------------+
| date_add('2001-07-10', interval 3 second) |
+-------------------------------------------+
| 2001-07-10 00:00:03                       |
+-------------------------------------------+
1 row in set (0.00 sec)


5、date_sub()的使用


mysql> select date_sub('2001-07-10', interval 3 day);
+----------------------------------------+
| date_sub('2001-07-10', interval 3 day) |
+----------------------------------------+
| 2001-07-07                             |
+----------------------------------------+
1 row in set (0.00 sec)
mysql> select date_sub('2001-07-10', interval -3 day);
+-----------------------------------------+
| date_sub('2001-07-10', interval -3 day) |
+-----------------------------------------+
| 2001-07-13                              |
+-----------------------------------------+
1 row in set (0.00 sec)
mysql> select date_sub('2001-07-10', interval 3 second);
+-------------------------------------------+
| date_sub('2001-07-10', interval 3 second) |
+-------------------------------------------+
| 2001-07-09 23:59:57                       |
+-------------------------------------------+
1 row in set (0.00 sec)


6、计算两个日期之间相差多少天


// 左边的日期 - 右边的日期
mysql> select datediff('2001-07-29', '2001-07-10');
+--------------------------------------+
| datediff('2001-07-29', '2001-07-10') |
+--------------------------------------+
|                                   19 |
+--------------------------------------+
1 row in set (0.00 sec)


7、只显示日期或只显示时间


// 1、date函数只显示日期部分,如果参数没有日期就显示NULL
mysql> select date('2001-07-09 12:59:48');
+-----------------------------+
| date('2001-07-09 12:59:48') |
+-----------------------------+
| 2001-07-09                  |
+-----------------------------+
1 row in set (0.00 sec)
mysql> select date('12:59:48');
+------------------+
| date('12:59:48') |
+------------------+
| NULL             |
+------------------+
1 row in set, 1 warning (0.00 sec)
// 2、time函数只显示时间部分,如果没有时间的话显示的数据不确定(可能为空,也能为其他时间)
mysql> select time('2001-07-09 12:59:48');
+-----------------------------+
| time('2001-07-09 12:59:48') |
+-----------------------------+
| 12:59:48                    |
+-----------------------------+
1 row in set (0.00 sec)



8、设计一个留言表


表的内容包括以下三个字段:


id:用户的id,设为自增长主键。
content:用户的留言内容,不能为空。
sendtime:留言日期时间,可以为空,默认是当前的。
mysql> create table if no exists msg(
    -> id int primary key auto_increment comment '用户id',
    -> content varchar(30) not null comment '留言内容',
    -> sendtime datetime default now() comment '留言日期时间'
    -> );
Query OK, 0 rows affected (0.03 sec)


向留言表中间隔一分钟插入一条数据:


mysql> insert into msg (content) values ('张三我爱你!!!');
Query OK, 1 row affected (0.00 sec)
mysql> insert into msg (content) values ('祝李四生日快乐');
Query OK, 1 row affected (0.01 sec)
mysql> insert into msg (content) values ('祝妈妈母亲节快乐');
Query OK, 1 row affected (0.00 sec)
mysql> insert into msg (content) values ('熊猫太可爱了');
Query OK, 1 row affected (0.01 sec)
mysql> select * from msg;
+----+--------------------------+---------------------+
| id | content                  | sendtime            |
+----+--------------------------+---------------------+
|  1 | 张三我爱你!!!            | 2022-06-30 13:50:48 |
|  2 | 祝李四生日快乐           | 2022-06-30 13:51:32 |
|  3 | 祝妈妈母亲节快乐         | 2022-06-30 13:52:55 |
|  4 | 熊猫太可爱了             | 2022-06-30 13:53:46 |
+----+--------------------------+---------------------+
4 rows in set (0.00 sec)


显示所有留言信息,发布日期只显示日期,不用显示时间

mysql> select content, date(sendtime) from msg;
+--------------------------+----------------+
| content                  | date(sendtime) |
+--------------------------+----------------+
| 张三我爱你!!!            | 2022-06-30     |
| 祝李四生日快乐           | 2022-06-30     |
| 祝妈妈母亲节快乐         | 2022-06-30     |
| 熊猫太可爱了             | 2022-06-30     |
+--------------------------+----------------+
4 rows in set (0.00 sec)


查询在2分钟内发布的帖子

// 1、查询留言板的数据
mysql> select * from msg;
+----+--------------------------+---------------------+
| id | content                  | sendtime            |
+----+--------------------------+---------------------+
|  1 | 张三我爱你!!!            | 2022-06-30 13:50:48 |
|  2 | 祝李四生日快乐           | 2022-06-30 13:51:32 |
|  3 | 祝妈妈母亲节快乐         | 2022-06-30 13:52:55 |
|  4 | 熊猫太可爱了             | 2022-06-30 13:53:46 |
+----+--------------------------+---------------------+
4 rows in set (0.00 sec)
// 2、显示当前时间
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2022-06-30 13:54:26 |
+---------------------+
1 row in set (0.00 sec)
// 3、查询在2分钟内发布的帖子
mysql> select * from msg where date_add(sendtime, interval 2 minute) > now();
+----+--------------------------+---------------------+
| id | content                  | sendtime            |
+----+--------------------------+---------------------+
|  3 | 祝妈妈母亲节快乐           | 2022-06-30 13:52:55 |
|  4 | 熊猫太可爱了              | 2022-06-30 13:53:46 |
+----+--------------------------+---------------------+
4 rows in set (0.00 sec)


三. 字符串函数


1. 函数概览


函数名称 描述

charset(str) 返回字符串的字符集类型

concat(string [, …]) 拼接字符串

instr(string, substring) 返回substring在string中出现的位置,没有返回0

ucase(string) 把string中的字符都转换成大写

lcase(string) 把string中的字符都转换成小写

left(string, length) 从string中的左边起取length个字符

length(string) 返回string所占的空间大小,单位是字节

replace(str, search_str, replace_str) 在str中用replace_str替换search_str

strcmp(string1, string2) 逐字符比较两字符串大小

substring(str, position [, length]) 从str的position开始,取length个字符

ltrim(string) rtrim(string) trim(string) 去除前空格或后空格


2. 使用举例


下面使用oracle 9i的emp表举例,该表数据如下:


fb3323ce31d34676add0ab5d1e293b1b.png

获取emp表中ename字段的字符集

mysql> select ename, charset(ename) from emp;
+--------+----------------+
| ename  | charset(ename) |
+--------+----------------+
| SMITH  | utf8           |
| ALLEN  | utf8           |
| WARD   | utf8           |
| JONES  | utf8           |
| MARTIN | utf8           |
| BLAKE  | utf8           |
| CLARK  | utf8           |
| SCOTT  | utf8           |
| KING   | utf8           |
| TURNER | utf8           |
| ADAMS  | utf8           |
| JAMES  | utf8           |
| FORD   | utf8           |
| MILLER | utf8           |
+--------+----------------+
14 rows in set (0.00 sec)



要求显示emp表中的信息,显示格式:“XXX的工号是XXX,岗位是XXX”

mysql> select concat(ename, '的工号是', empno, ',岗位是', job) from emp;
+-----------------------------------------------------------+
| concat(ename, '的工号是', empno, ',岗位是', job)         |
+-----------------------------------------------------------+
| SMITH的工号是007369,岗位是CLERK                          |
| ALLEN的工号是007499,岗位是SALESMAN                       |
| WARD的工号是007521,岗位是SALESMAN                        |
| JONES的工号是007566,岗位是MANAGER                        |
| MARTIN的工号是007654,岗位是SALESMAN                      |
| BLAKE的工号是007698,岗位是MANAGER                        |
| CLARK的工号是007782,岗位是MANAGER                        |
| SCOTT的工号是007788,岗位是ANALYST                        |
| KING的工号是007839,岗位是PRESIDENT                       |
| TURNER的工号是007844,岗位是SALESMAN                      |
| ADAMS的工号是007876,岗位是CLERK                          |
| JAMES的工号是007900,岗位是CLERK                          |
| FORD的工号是007902,岗位是ANALYST                         |
| MILLER的工号是007934,岗位是CLERK                         |
+-----------------------------------------------------------+
14 rows in set (0.00 sec)


求emp表中员工姓名占用的字节数

mysql> select ename, length(ename) from emp;
+--------+---------------+
| ename  | length(ename) |
+--------+---------------+
| SMITH  |             5 |
| ALLEN  |             5 |
| WARD   |             4 |
| JONES  |             5 |
| MARTIN |             6 |
| BLAKE  |             5 |
| CLARK  |             5 |
| SCOTT  |             5 |
| KING   |             4 |
| TURNER |             6 |
| ADAMS  |             5 |
| JAMES  |             5 |
| FORD   |             4 |
| MILLER |             6 |
+--------+---------------+
14 rows in set (0.00 sec)


将EMP表中所有员工名字中带有S的替换成’上海’

mysql> select ename, replace(ename, 'S', '上海') from emp;
+--------+-------------------------------+
| ename  | replace(ename, 'S', '上海')   |
+--------+-------------------------------+
| SMITH  | 上海MITH                      |
| ALLEN  | ALLEN                         |
| WARD   | WARD                          |
| JONES  | JONE上海                      |
| MARTIN | MARTIN                        |
| BLAKE  | BLAKE                         |
| CLARK  | CLARK                         |
| SCOTT  | 上海COTT                      |
| KING   | KING                          |
| TURNER | TURNER                        |
| ADAMS  | ADAM上海                      |
| JAMES  | JAME上海                      |
| FORD   | FORD                          |
| MILLER | MILLER                        |
+--------+-------------------------------+
14 rows in set (0.00 sec)


截取EMP表中ename字段的第二个到第三个字符

mysql> select ename, substring(ename, 2, 2) from emp;
+--------+------------------------+
| ename  | substring(ename, 2, 2) |
+--------+------------------------+
| SMITH  | MI                     |
| ALLEN  | LL                     |
| WARD   | AR                     |
| JONES  | ON                     |
| MARTIN | AR                     |
| BLAKE  | LA                     |
| CLARK  | LA                     |
| SCOTT  | CO                     |
| KING   | IN                     |
| TURNER | UR                     |
| ADAMS  | DA                     |
| JAMES  | AM                     |
| FORD   | OR                     |
| MILLER | IL                     |
+--------+------------------------+
14 rows in set (0.00 sec)


以首字母小写的方式显示所有员工的姓名

mysql> select concat(lcase(substring(ename, 1, 1)), substring(ename, 2)) from emp;
+------------------------------------------------------------+
| concat(lcase(substring(ename, 1, 1)), substring(ename, 2)) |
+------------------------------------------------------------+
| sMITH                                                      |
| aLLEN                                                      |
| wARD                                                       |
| jONES                                                      |
| mARTIN                                                     |
| bLAKE                                                      |
| cLARK                                                      |
| sCOTT                                                      |
| kING                                                       |
| tURNER                                                     |
| aDAMS                                                      |
| jAMES                                                      |
| fORD                                                       |
| mILLER                                                     |
+------------------------------------------------------------+
14 rows in set (0.00 sec)



四. 数学函数


1. 函数概览


函数名称 描述

abs(number) 绝对值函数

bin(decimal_number) 把十进制数字转换成二进制

hex(decimal_number) 把十进制数字转换成十六进制

conv(number, from_base, to_base) 指定进制转换

ceiling(number) 向上取整

floor(number) 向下取整

format(number, decimal_places) 四舍五入,decimal_places表示保留的小数位数

rand() 返回随机浮点数,范围[0.0, 1.0]

mod(number, denominator) 取模,求余


2. 使用举例


计算绝对值

mysql> select abs(-3.14);
+------------+
| abs(-3.14) |
+------------+
|       3.14 |
+------------+
1 row in set (0.00 sec)


向上取整(往大的数方向上取整)

mysql> select ceiling(3.14), ceiling(-3.14), ceiling(3);
+---------------+----------------+------------+
| ceiling(3.14) | ceiling(-3.14) | ceiling(3) |
+---------------+----------------+------------+
|             4 |             -3 |          3 |
+---------------+----------------+------------+
1 row in set (0.00 sec)


向下取整(往小的数方向上取整)

mysql> select floor(3.14), floor(-3.14), floor(3);
+-------------+--------------+----------+
| floor(3.14) | floor(-3.14) | floor(3) |
+-------------+--------------+----------+
|           3 |           -4 |        3 |
+-------------+--------------+----------+
1 row in set (0.00 sec)


四舍五入(需要指定小数位数)

mysql> select format(3.5, 0), format(-3.5, 0), format(3.4, 0), format(-3.4, 0);
+----------------+-----------------+----------------+-----------------+
| format(3.5, 0) | format(-3.5, 0) | format(3.4, 0) | format(-3.4, 0) |
+----------------+-----------------+----------------+-----------------+
| 4              | -4              | 3              | -3              |
+----------------+-----------------+----------------+-----------------+
1 row in set (0.00 sec)


产生1~100之间的随机数

// 因为rand()函数得到的是随机浮点数,范围[0.0, 1.0]
// rnad()再乘上100后向下取整得到范围[0, 99]
// 最后再加上1得到范围[1, 100]
mysql> select floor(rand()*100)+1;
+---------------------+
| floor(rand()*100)+1 |
+---------------------+
|                  95 |
+---------------------+
1 row in set (0.00 sec)
mysql> select floor(rand()*100)+1;
+---------------------+
| floor(rand()*100)+1 |
+---------------------+
|                  24 |
+---------------------+
1 row in set (0.00 sec)
mysql> select floor(rand()*100)+1;
+---------------------+
| floor(rand()*100)+1 |
+---------------------+
|                  34 |
+---------------------+
1 row in set (0.00 sec)


十进制数字转为二进制和十六进制

// 转为二进制
mysql> select bin(10);
+---------+
| bin(10) |
+---------+
| 1010    |
+---------+
1 row in set (0.00 sec)
// 转为十六进制
mysql> select hex(10);
+---------+
| hex(10) |
+---------+
| A       |
+---------+
1 row in set (0.00 sec)


执行取模运算

// 发现取模后结果的正负号取决于被除数
mysql> select mod(10, 3);
+------------+
| mod(10, 3) |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)
mysql> select mod(-10, -3);
+--------------+
| mod(-10, -3) |
+--------------+
|           -1 |
+--------------+
1 row in set (0.00 sec)
mysql> select mod(-10, 3);
+-------------+
| mod(-10, 3) |
+-------------+
|          -1 |
+-------------+
1 row in set (0.00 sec)
mysql> select mod(10, -3);
+-------------+
| mod(10, -3) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)



五. 其它函数


1、user() 查询当前用户


mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)


2、database()显示当前正在使用的数据库


// 1、查看当前正在使用的数据库
mysql> select database();
+------------+
| database() |
+------------+
| ForTest    |
+------------+
1 row in set (0.00 sec)
// 2、切换数据库
mysql> use scott;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
// 3、重新查看当前正在使用的数据库
mysql> select database();
+------------+
| database() |
+------------+
| scott      |
+------------+
1 row in set (0.00 sec)



3、md5(str)对一个字符串进行md5摘要,摘要后得到一个32位字符串


// 不论字符串长度有多长,摘要后得到的都是一个32位字符串
mysql> select md5('5201314');
+----------------------------------+
| md5('5201314')                   |
+----------------------------------+
| 723d505516e0c197e42a6be3c0af910e |
+----------------------------------+
1 row in set (0.02 sec)
mysql> select md5('admin');
+----------------------------------+
| md5('admin')                     |
+----------------------------------+
| 21232f297a57a5a743894a0e4a801fc3 |
+----------------------------------+
1 row in set (0.00 sec)



4、ifnull(val1, val2)如果val1不为null,返回val1,否则返回val2的值


PS:val可以是任意类型的数据。


// ifnull的作用类似于C/C++中的三目运算符
mysql> select ifnull('abc', '123');
+----------------------+
| ifnull('abc', '123') |
+----------------------+
| abc                  |
+----------------------+
1 row in set (0.00 sec)
mysql> select ifnull(null, '123');
+---------------------+
| ifnull(null, '123') |
+---------------------+
| 123                 |
+---------------------+
1 row in set (0.00 sec)


相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
1天前
|
SQL 存储 关系型数据库
MySQL进阶突击系列(01)一条简单SQL搞懂MySQL架构原理 | 含实用命令参数集
本文从MySQL的架构原理出发,详细介绍其SQL查询的全过程,涵盖客户端发起SQL查询、服务端SQL接口、解析器、优化器、存储引擎及日志数据等内容。同时提供了MySQL常用的管理命令参数集,帮助读者深入了解MySQL的技术细节和优化方法。
|
2天前
|
SQL Oracle 关系型数据库
SQL(MySQL)
SQL语言是指结构化查询语言,是一门ANSI的标准计算机语言,用来访问和操作数据库。 数据库包括SQL server,MySQL和Oracle。(语法大致相同) 创建数据库指令:CRATE DATABASE websecurity; 查看数据库:show datebase; 切换数据库:USE websecurity; 删除数据库:DROP DATABASE websecurity;
|
26天前
|
关系型数据库 MySQL Serverless
MySQL函数
最常用的MySQL函数,包括聚合函数,字符串函数,日期时间函数,控制流函数等
|
2月前
|
SQL Oracle 关系型数据库
SQL优化-使用联合索引和函数索引
在一次例行巡检中,发现一条使用 `to_char` 函数将日期转换为字符串的 SQL 语句 CPU 利用率很高。为了优化该语句,首先分析了 where 条件中各列的选择性,并创建了不同类型的索引,包括普通索引、函数索引和虚拟列索引。通过对比不同索引的执行计划,最终确定了使用复合索引(包含函数表达式)能够显著降低查询成本,提高执行效率。
|
2月前
|
SQL 运维 关系型数据库
MySQL 运维 SQL 备忘
MySQL 运维 SQL 备忘录
48 1
|
1月前
|
SQL NoSQL 关系型数据库
|
1月前
|
SQL 关系型数据库 MySQL
MySql5.6版本开启慢SQL功能-本次采用永久生效方式
MySql5.6版本开启慢SQL功能-本次采用永久生效方式
38 0
|
1月前
|
SQL 关系型数据库 MySQL
mysql编写sql脚本:要求表没有主键,但是想查询没有相同值的时候才进行插入
mysql编写sql脚本:要求表没有主键,但是想查询没有相同值的时候才进行插入
31 0
|
2月前
|
关系型数据库 MySQL 数据库
mysql中tonumber函数使用要注意什么
在处理这类转换操作时,考虑周全,利用提供的高性能云服务器资源,可以进一步提升数据库处理效率,确保数据操作的稳定性和安全性,尤其是在处理大量数据转换和运算密集型应用时。
102 0
|
SQL 存储 关系型数据库
MySQL下使用SQL命令进行表结构与数据复制实践
MySQL下使用SQL命令进行表结构与数据复制实践
157 0