SQL(DCL、DDL、DML、DQL)
0. SQL 基础
- SQL 分类
- SQL 语法规则
1. DCL
- 1.1 查看权限
- 1.2 创建用户并赋予权限
- 1.3 回收权限
- 1.4 修改密码
- 1.5 删除账号
- 1.6 权限信息与生效规则
2. DDL
- 2.1 库对象
- 2.2 表对象
3. DML
- 3.1 插入数据:INSERT
- 3.2 修改数据:UPDATE
- 3.3 删除数据:DELETE/TRUNCATE
4. DQL
-
4.1 基础
- 字段控制
- 模糊查询
- 条件查询
- 排序
- LIMIT
- 聚合函数
-
4.2 条件判断查询
-
4.3 多表查询
- 合并结果集(UNION)
- 连接查询(JOIN ... ON)
- 子查询
- 自连接
-
4.4 函数
- 时间日期相关
- 字符串相关
- 数学计算相关
- 流程控制相关
0. SQL 基础
SQL 分类
DCL(Data Control Language):数据控制语句,主要做权限控制,比如创建账号、授权、修改密码。常用关键字有 grant、revoke、create user 等。
DDL (Data Definition Language):数据定义语句,主要针对数据库、表、字段等的定义操作。常用关键字有 create、drop、alter 等。
DML (Data Manipulation Language):数据操纵语句,主要做表数据的增删改操作。常用关键字有 insert、delete、update 等。
DQL (Data Query Language):数据查询语言,主要做表数据的查询操作。常用关键字有 select 等。
SQL 语法规则
- SQL 关键字不区分大小写(Oracle 区分)
- SQL 以 ;(分号)结尾,也可以以 \G 结尾(表示结构化输出结果)
- 使用 ' '(单引号)或 " "(双引号)来表示字符串(Oracle 只支持单引号)
- 使用 ` `(反引号)来表示数据库、数据表、字段(也可以不使用)
- 注释一行: -- 或 #
- 注解多行: /* */
1. DCL
DCL(Data Control Language):数据控制语句,主要做权限控制,比如创建账号、授权、修改密码。常用关键字有 grant,revoke,create user 等。
1.1 查看权限
创建完账号后,时间场了可能就会忘记分配的权限而需要查看账号权限,也有可能经过一段时间后需要更改以前的的账号权限。
查看用户
- host:允许用户登录所使用的 IP。
补充:mysql 用户表中多个 host 时的匹配规则
Mysql 数据库中 user 表的 host 字段,是用来控制用户访问数据库“权限”的。
- 可以使用“%”、""(空字符串)、“*”,表示所有的网段(但不包括 localhost);
- 也可以使用具体的 IP 地址,表示只有该 IP 的客户端才可以登录到 Mysql 服务器;
- 也可以使用“_”进行模糊匹配,表示匹配成功的客户端可以登录到 Mysql 服务器。
如果在 user 表中存在一个用户两条不同 host 值的记录,那么 Mysql 服务器该如何匹配该用户的权限呢?
Mysql 采用的策略是:当服务器读取 user 表时,它首先以最具体的 host 值排序(主机名和 IP 是最具体的) 。当有相同 host 值的条目时,首先以最具体的 user 匹配。
如下,有两条 root 用户数据,那么只有 localhost 的 root 客户端可以登录到 Mysql 服务器。
| root | localhost | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | | root | % | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
查看权限
可以通过如下命令进行查看权限:
show grants for 'user'@'host'; -- 可以不加引号,但加了可以避免歧义 show grants for user; --@host 也可以不写,默认为%(模糊匹配) show grants; --查看当前账号的权限
1.2 创建用户并赋予权限
Mysql 8.0 以前的版本可以使用 grant 在授权的时候隐式的创建用户,8.0 以后已经不支持,所以必须先创建用户,然后再授权。
示例 1:创建用户 test,权限为可以在所有数据库上执行所有权限,可以从任意 IP 进行连接。
--创建用户 create user 'test'@'%' identified [with mysql_native_password] by '123456'; --加密方式默认为caching_sha2_password --授权 grant all privileges on *.* to 'test'@'%';
示例 2:在示例 1 的基础上为用户增加 grant 权限。
grant all privileges on *.* to 'test'@'%' with grant option;
示例 3:创建用户 test2,只能从服务器本地进行连接,权限为对 test1 数据库里的所有表进行 select、update、insert 和 delete 操作,初始密码为“123”。
--创建用户 create user 'test2'@'localhost' identified by '123456'; --加密方式默认为caching_sha2_password --授权 grant select, insert, update, delete on test1.* to 'test2'@'localhost';
示例 4:只授予登录权限。
grant usage on *.* to 'test2'@'localhost';
示例 5:如果要让一个非 root 账号可以创建用户并授权,需要两步。
grant create user on *.* to 'test'@'%'; -- 授予 create user 权限 grant create user ON *.* TO 'test'@'%' with grant option; -- 授予 grant 权限
注意:账号只能授予自己本身拥有的权限。
1.3 回收权限
GRANT INSERT ON 'test'.* TO 'test1'@'%'; -- 授予INSERT权限 REVOKE INSERT ON 'test'.* FROM 'test1'@'%'; -- 回收INSERT权限
1.4 修改密码
-- 方式1:SET
set password for 'test'@'%'='123456';
-- 方式2:ALTER
alter user 'test'@'%' identified by '123456';
1.5 删除账号
--方式1:DROP drop user 'test'@'localhost'; drop user 'test'@'localhost', 'test2'@'localhost'; -- 删除多个用户 --方式2:修改user表 delete from mysql.user where user='test' and host='localhost'; flush privileges; -- 修改权限表后需要刷新权限才能及时生效(载入内存)
1.6 权限信息与生效规则
权限信息都存在哪里?
所有权限信息都存在 Mysql 库里的 user、db、tables_priv、columns_priv 这四张表中。
权限是如何生效?
一般 Mysql 登录有两个步骤:
- 连接数据库(网络可达);
- 验证密码、权限。
而 Mysql 服务器中的权限信息都会加载到内存中,以此保证验证速度。
GRANT、ALTER 等 DCL 操作修改权限信息时,会直接加载到内存中,即时生效。
当修改权限表信息时,则需要使用 flush privileges 重新加载权限信息到内存中。
2. DDL
DDL (Data Definition Language):数据定义语句,主要针对数据库、表、字段等的定义操作。常用关键字有 create、drop、alter 等。
2.1 库对象
1)创建数据库
create database mydb1; -- 如果 mydb1 存在则会报错 create database IF NOT EXISTS mydb2; -- 如果 mydb2 不存在才创建数据库(避免冲突报错) create database mydb3 character set gbk; create database mydb4 character set gbk COLLATE gbk_chinese_ci;
use mydb1; -- 进入 mydb1 数据库
COLLATE 代表的是数据库校对规则:
- utf8_bin:将字符串中的每一个字符用二进制数据存储,区分大小写。
- utf8_genera_ci:不区分大小写(ci 为 case insensitive 的缩写,即大小写不敏感)。
- utf8_general_cs:区分大小写(cs 为 case sensitive 的缩写,即大小写敏感)。
2)查询数据库信息
-- 查看所有数据库 show databases; -- 查看当前使用的数据库 select database(); -- 查看mydb2数据库的定义信息 show create database mydb2;
3)修改数据库信息
-- 把 mydb2 库的字符集修改为 utf8 alter database mydb2 character set utf8;
4)删除数据库
drop database mydb3;
2.2 表对象
1)创建表
create table [if not exists] 表名( 字段名 字段类型, 字段名 字段类型, ... 字段名 字段类型 ); -- 使用like创建表 create table new_table like old_table; -- 创建与 old_table 表结构一致的 new_table 表 insert into new_table SELECT * from old_table; -- 导入 old_table 的数据到 new_table 表
增加默认值、注释信息、主键、自增值、备注:
create table student( id int comment '学号ID' auto_increment primary key, -- 自增值无需指定默认值(因为已默认从1开始递增),且必须指定为主键 name varchar(30) not null default '' comment '学生姓名', age tinyint not null default 0 comment '学生年龄', phoneNo char(11) not null comment '学生手机号', addr varchar(100) not null comment '学生家庭住址' );
- not null:因为 null 是一个特殊的值,无法被索引,所以建议创建表的时候指定字段为 not null。
- 默认值:建议创建表时为字段指定默认值。
指定无符号的数值类型:
指定存储引擎、字符集、数据库校对规则:
COLLATE 代表的是数据库校对规则:
- utf8_bin:将字符串中的每一个字符用二进制数据存储,区分大小写。
- utf8_genera_ci:不区分大小写(ci 为 case insensitive 的缩写,即大小写不敏感)。
- utf8_general_cs:区分大小写(cs 为 case sensitive 的缩写,即大小写敏感)。
2)查看表信息
-- 当前数据库中的所有表 SHOW TABLES; -- 查看 student 的表结构 DESC student; -- 查看 student 表的创建语句 SHOW CREATE TABLE student;
3)修改表信息
-- 增加一个 lesson 列,其字段类型为 blob ALTER TABLE student ADD lesson blob not null default ''; -- 一次增加多个列 ALTER TABLE student ADD nick_name char(10) not null default '', ADD teacher char(10) not null default ''; -- 修改 addr 列,使其长度为 60 ALTER TABLE student MODIFY addr varchar(60); -- 列名 addr 修改为 address,并修改其字段属性 ALTER TABLE student CHANGE addr address varchar(100) not null default ''; -- 删除 lesson 列 ALTER TABLE student DROP image; -- 一次删除多列 ALTER TABLE student DROP phoneNo, DROP addr; -- 表名改为 new_student RENAME TABLE student TO new_student; -- 修改表的字符集为 utf8 ALTER TABLE new_student CHARACTER SET utf8;
自增字段达到了最大值,导致插入数据报错:
这时就需要把自增字段修改为存储范围更大的数值类型(如 bigint)。
4)删除表
DROP TABLE student; -- 删除 student 表
3. DML
DML (Data Manipulation Language):数据操纵语句,主要做表数据的增删改操作。常用关键字有 insert、delete、update 等。
3.1 插入数据:INSERT
语法:
INSERT INTO 表名(列名1,列名2 ...)VALUES(列值1,列值2...);
- 列名与列值的类型、个数、顺序要一一对应。
- 如果要插入空值,可使用 "" 或 null。
- 其它未提供的字段,采用 default(默认值)或自增值。
示例:
-- 创建表 create table tmp( id int, name varchar(100), gender varchar(10), birthday date, salary float(10, 2), entry_date date, resume text ); -- 插入单条数据 INSERT INTO tmp(id, name, gender, birthday, salary, entry_date, resume) VALUES(1, 'zhangsan', 'female', '1990-5-10', 10000, '2015-5-5', 'good girl'); -- 插入多条数据 INSERT INTO tmp(id, name, gender, birthday, salary, entry_date, resume) VALUES(2, 'lisi', 'male', '1995-5-10', 10000, '2015-5-5', 'good boy'), (3, '你好', 'male', '1995-5-10', 10000, '2015-5-5', 'good boy');
3.2 修改数据:UPDATE
语法:
UPDATE 表名 SET 列名1=列值1, 列名2=列值2, ... WHERE 列名=值
示例:
-- 全量修改:将所有员工薪水修改为5000元 UPDATE emp SET salary=5000; -- 带条件修改:将姓名为'zs'的员工薪水修改为3000元 UPDATE emp SET salary=3000 WHERE name='zhangsan'; -- 修改多个字段:将姓名为'aaa'的员工薪水修改为4000元,job改为ccc UPDATE emp SET salary=4000, gender='female' WHERE name='lisi'; -- 使用运算:将wu的薪水在原有基础上增加1000元 UPDATE emp SET salary=salary+1000 WHERE gender='male';
3.3 删除数据:DELETE/TRUNCATE
语法:
DELETE 表名 WHERE 列名=值; -- 删除指定数据 TRUNCATE TABLE 表名; -- 清空整个表数据(等于重新建表)
示例:
-- 删除表中名称为'zs'的记录 DELETE FROM tmp WHERE name='zs'; -- 删除表中所有记录 DELETE FROM tmp; -- 使用truncate删除表中记录 TRUNCATE TABLE tmp; -- 根据id值删除重复数据:每个分组保留最小的id值并删除其余数据 delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
DELETE 与 TRUNCATE 区别:
- DELETE:删除表中的数据,表结构还在;删除后的数据可以找回。
- TRUNCATE:等于把表直接 DROP 掉,然后再创建一个同样的新表。删除的数据不能找回,自增值重置为从 1 开始。执行速度比 DELETE 快。
4. DQL
DQL (Data Query Language):数据查询语言,主要做表数据的查询操作。常用关键字有 select 等。
数据库执行DQL语句不会对数据进行改变,而是让服务端发送结果集给客户端,即查询返回的结果集是一张虚拟表。
查询语句的书写顺序如上;而执行顺序如下:
from —> where —> group by —> having —> select —> order by —> limit
4.1 基础
字段控制
1)两列的类型都是数值类型,可以做加运算。如果其中有一个字段不是数值类型,那么会报错。
2)假如某列有很多记录的值为 NULL,因为任何东西与 NULL 相加结果还是 NULL ,所以结算结果可能会出现 NULL。下面使用了把 NULL 转换成数值 0 的函数 IFNULL():
SELECT *, sal+IFNULL(comm, 0) FROM emp;
3)可以给列名添加别名,并且给列起别名时,是可以省略 AS 关键字的。
模糊查询
当想查询姓名中包含 a 字母的学生时就需要使用模糊查询了。模糊查询需要使用关键字 LIKE。
通配符如下:
- _:任意一个字母。
- %:任意 0~n 个字母。
select * from student where name like '%a%';
条件查询
条件查询就是在查询时给出 WHERE 子句,在 WHERE 子句中可以使用如下运算符及关键字:
- =、!=、<>、<、<=、>、>=
- BETWEEN…AND
- IN
- IS NULL
- AND
- OR
- NOT
排序
-- 查询所有学生记录,按年龄升序排序 SELECT * FROM stu ORDER BY sage ASC; -- 默认升序 SELECT * FROM stu ORDER BY sage; -- 查询所有学生记录,按年龄降序排序 SELECT * FROM stu ORDER BY age DESC; -- 查询所有雇员,按月薪降序排序;如果月薪相同时,按编号升序排序 SELECT * FROM emp ORDER BY sal DESC, empno ASC;
LIMIT
LIMIT 用来限定查询结果的起始行以及总行数。
-- 起始行, 需要的行数 SELECT * FROM emp LIMIT 0, 5; -- 也可以只写行数 SELECT * FROM emp LIMIT 5; -- 起始行默认为0
聚合函数
- count
- count(*) :输出的是所有记录行数,包括全为 null 的行。
- count(列名) :会统计空字符,而不会统计 null。
- sum
- 均不统计空字符与 null。
- avg
- avg(value) = sum(value) / count(value)
- 对某列使用 avg 时,其处理空值和空字符时方式和 count 一样,会统计空字符而不会统计 null。
having 与 where 的区别
WHERE | HAVING | |
出现位置 | GROUP BY 前 | GROUP BY 后 |
作用对象 |
数据源 (是对分组前记录的筛选条件,如果某行记录没有满足 WHERE 子句的条件,那么这行记录不会参加分组) |
分组后的结果 (对分组后的数据进行过滤,是对分组后数据的约束) |
语句后能否使用聚合函数 | WHERE 后面不可以使用聚合函数 | HAVING 后面可以使用聚合函数 |
示例:
SELECT deptno, SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal) > 9000; -- 查询总薪资大于9000的部门编号
4.2 条件判断查询
4.3 多表查询
多表查询有如下几种:
- 合并结果集:UNION、UNION ALL
- 连接查询:JOIN ... ON
- 内连接:[INNER] JOIN ... ON
- 外连接:[OUTER] JOIN ... ON
- 左(外)连接:LEFT [OUTER] JOIN
- 右(外)连接:RIGHT [OUTER] JOIN
- 全外连接:(MySQL 不支持)FULL JOIN
- 自然连接:NATURAL JOIN
- 子查询
- 自连接
1)合并结果集(UNION)
作用:合并结果集就是把两个 select 语句的查询结果合并到一起。
要求:被合并的两张表的列数、列类型必须相同。
-- UNION:去除重复记录 SELECT * FROM t1 UNION SELECT * FROM t2;
-- UNION ALL:不去除重复记录 SELECT * FROM t1 UNION ALL SELECT * FROM t2;
2)连接查询(JOIN ... ON)
连接查询就是求出多个表的乘积,例如 t1 连接 t2,那么查询出的结果就是 t1*t2。
连接查询会产生笛卡尔积,假设集合 A={a,b},集合 B={0,1,2},则两个集合的笛卡尔积为 {(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。
多表查询产生这样的结果并不是我们想要的,那么怎样去除重复的、不想要的记录呢?当然是通过条件过滤。通常要查询的多个表之间都存在关联关系(主键),那么就可以通过关联关系(主键)去除笛卡尔积。
SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno; -- emp表和dept表 通过deptno主键字段 进行关联
内连接
INNER 可以省略,MySQL 默认的连接方式就是内连接。
内连接的特点:查询结果必须满足条件,即查询出同时满足左表和右表的条件的结果。
外连接(左/右连接)
外连接的特点:查询出的结果存在不满足条件的可能。
- 左连接是先查询出左表(即以左表为主),然后查询右表,右表中满足条件的显示出来,不满足条件的显示 NULL。
- 右连接就是先把右表中所有记录都查询出来,然后左表满足条件的显示,不满足显示 NULL。
连接查询总结
连接不限于两张表,连接查询也可以是三张、四张,甚至 N 张表的连接查询。通常连接查询不需要整个笛卡尔积,而只是需要其中一部分,那么这时就需要使用条件来去除不需要的记录。这个条件大多数情况下都是使用主外键关系去除。
两张表的连接查询一定有一个主外键关系,三张表的连接查询就一定有两个主外键关系,所以在大家不是很熟悉连接查询时,首先要学会去除无用笛卡尔积,那么就是用主外键关系作为条件来处理。如果是两张表的查询,那么至少得有一个主外键条件,三张表连接则至少得有两个主外键条件。
自然连接(NATURAL JOIN)
连接查询会产生无用笛卡尔积,我们通常使用主外键关系等式来去除它。而自然连接无需你去给出主外键等式,它会自动找到这一等式。
两张连接的表中名称和类型完全一致的列作为条件,例如 emp 和 dept 表都存在 deptno 列,并且类型一致,所以会被自然连接找到。
当然自然连接还有其他的查找条件的方式,但其他方式都可能存在问题。
SELECT * FROM emp NATURAL JOIN dept; --内连接 SELECT * FROM emp NATURAL LEFT JOIN dept; --左连接 SELECT * FROM emp NATURAL RIGHT JOIN dept; --右连接
3)子查询
子查询就是嵌套查询,即 SELECT 中包含 SELECT,如果一条语句中存在两个,或两个以上 SELECT,那么就是子查询语句。
子查询出现的位置:
- where 后:作为被查询的条件的一部分
- from 后:用作表
当子查询出现在 where 后作为条件时,还可以使用如下关键字:
- any
- all
子查询结果集的形式:
- 单行单列(用作条件)
- 单行多列(用作条件)
- 多行单列(用作条件)
- 多行多列(用作表)
示例:
-- 子查询作为条件:查询高于编号30部门所有人工资的员工信息 SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30); -- 子查询作为表 SELECT e.ename, e.sal, d.dname, d.loc FROM emp e, (SELECT dname,loc,deptno FROM dept) d WHERE e.deptno=d.deptno AND e.empno=7788;
4)自连接
即表对自身连接,将一张表当成两张或多张表使用,以别名来区分表。
-- 例:求7369员工编号、姓名、经理编号和经理姓名 SELECT e1.empno, e1.ename, e2.mgr, e2.ename FROM emp e1, emp e2 WHERE e1.mgr = e2.empno AND e1.empno = 7369;
4.4 函数
时间日期相关
1)获取当前时间
- CURRENT_DATE():当前日期
- CURRENT_TIME():当前时间
- CURRENT_TIMESTAMP():当前时间戳
- now():当前日期+时间
- sysdate():当前日期+时间
sysdate() 日期时间函数跟 now() 类似,不同之处在于:
- now() 在执行开始时值就得到了。
- sysdate() 在函数执行时动态得到值,一般情况下很少用到。
mysql> select now(), sleep(3), now(); +---------------------+----------+---------------------+ | now() | sleep(3) | now() | +---------------------+----------+---------------------+ | 2021-06-08 14:39:29 | 0 | 2021-06-08 14:39:29 | +---------------------+----------+---------------------+ 1 row in set (3.08 sec) mysql> select sysdate(), sleep(3), sysdate(); +---------------------+----------+---------------------+ | sysdate() | sleep(3) | sysdate() | +---------------------+----------+---------------------+ | 2021-06-08 14:40:03 | 0 | 2021-06-08 14:40:06 | +---------------------+----------+---------------------+ 1 row in set (3.06 sec)
2)INTERVAL
INTERVAL 代表的是时间间隔。
Mysql 中的时间间隔类型有如下几种:
利用 INTERVAL 做时间的加减法:
-- 加法 SELECT DATE '2018-11-01' + INTERVAL '10 11' DAY_HOUR; -- 结果:2018-11-11 11:00:00 -- 减法 select DATE '2018-11-11 11:00:00' - INTERVAL '10 11' DAY_HOUR; -- 结果:2018-11-01 00:00:00
3)时间计算
为日期增加一个时间间隔:date_add()
set @dt = now(); select date_add(@dt, interval 1 day); -- add 1 day select date_add(@dt, interval 1 hour); -- add 1 hour select date_add(@dt, interval 1 minute); select date_add(@dt, interval 1 second); select date_add(@dt, interval 1 microsecond); select date_add(@dt, interval 1 week); select date_add(@dt, interval 1 month); select date_add(@dt, interval 1 quarter); select date_add(@dt, interval 1 year); select date_add(@dt, interval -1 day); -- sub 1 day
adddate()、addtime() 函数,可以用 date_add() 来替代:
set @dt = '2008-08-09 12:12:33'; select date_add(@dt, interval '01:15:30' hour_second); -- 2008-08-09 13:28:03 select date_add(@dt, interval '1 01:15:30' day_second); -- 2008-08-10 13:28:03
为日期减去一个时间间隔:date_sub()
date_sub() 函数 和 date_add() 用法一致,不再赘述。
select date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second); -- 1997-12-30 22:58:59
日期、时间相减函数:datediff(date1, date2)、timediff(time1, time2)
-- datediff(date1,date2):两个日期相减 date1 - date2,返回天数 select datediff('2008-08-08', '2008-08-01'); -- 7 select datediff('2008-08-01', '2008-08-08'); -- -7 -- timediff(time1,time2):两个日期相减 time1 - time2,返回 time 差值 select timediff('2008-08-08 08:08:08', '2008-08-08 00:00:00'); -- 08:08:08 select timediff('08:08:08', '00:00:00'); -- 08:08:08
注意:timediff(time1, time2) 函数的两个参数类型必须相同。
时间戳(timestamp)转换、增、减函数
- timestamp(date) -- date to timestamp
- timestamp(dt, time) -- dt + time
- timestampadd(unit, interval, datetime_expr)
- timestampdiff(unit, datetime_expr1, datetime_expr2)
select timestamp('2008-08-08'); -- 2008-08-08 00:00:00 select timestamp('2008-08-08 08:00:00', '01:01:01'); -- 2008-08-08 09:01:01 select timestamp('2008-08-08 08:00:00', '10 01:01:01'); -- 2008-08-18 09:01:01 -- timestampadd() 函数类似于 date_add() select timestampadd(day, 1, '2008-08-08 08:00:00'); -- 2008-08-09 08:00:00 select date_add('2008-08-08 08:00:00', interval 1 day); -- 2008-08-09 08:00:00 -- timestampdiff() 函数就比 datediff() 功能强多了,datediff() 只能计算两个日期(date)之间相差的天数。 select timestampdiff(year, '2002-05-01', '2001-01-01'); -- -1 select timestampdiff(day , '2002-05-01', '2001-01-01'); -- -485 select timestampdiff(hour, '2008-08-08 12:00:00', '2008-08-08 00:00:00'); -- -12 select datediff('2008-08-08 12:00:00', '2008-08-01 00:00:00'); -- 7
4)格式转换
日期/时间转换成时间戳
-
timestamp(date[, time]):date + time,其中 time 部分默认为 00:00:00
select timestamp('2008-08-08'); -- 2008-08-08 00:00:00 select timestamp('2008-08-08 08:00:00', '01:01:01'); -- 2008-08-08 09:01:01 select timestamp('2008-08-08 08:00:00', '10 01:01:01'); -- 2008-08-18 09:01:01
日期/时间转换成字符串
- date_format(date, format)
- time_format(time, format)
select date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s'); -- 20080808222301
date_format()、time_format() 能够把一个日期/时间转换成各种各样的字符串格式。它是 str_to_date(str, format) 函数的一个逆转换。
字符串转换为日期
- str_to_date(str, format)
select str_to_date('08/09/2008', '%m/%d/%Y'); -- 2008-08-09 select str_to_date('08/09/08' , '%m/%d/%y'); -- 2008-08-09 select str_to_date('08.09.2008', '%m.%d.%Y'); -- 2008-08-09 select str_to_date('08:09:30', '%h:%i:%s'); -- 08:09:30 select str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s'); -- 2008-08-09 08:09:30
可以看到,str_to_date() 可以把一些杂乱无章的字符串转换为日期格式。另外,它也可以转换为时间。
日期、天数互换
mysql> select to_days('0000-01-01'); +-----------------------+ | to_days('0000-01-01') | +-----------------------+ | 1 | +-----------------------+ 1 row in set (0.00 sec) mysql> select to_days('2020-01-01'); +-----------------------+ | to_days('2020-01-01') | +-----------------------+ | 737790 | +-----------------------+ 1 row in set (0.00 sec) mysql> select from_days(737790); +-------------------+ | from_days(737790) | +-------------------+ | 2020-01-01 | +-------------------+ 1 row in set (0.00 sec)
时间、秒互换
- time_to_sec(time)
- sec_to_time(seconds)
select time_to_sec('01:00:05'); -- 3605 select sec_to_time(3605); -- '01:00:05'
拼接日期、时间
- makdate(year, dayofyear)
- maketime(hour, minute, second)
select makedate(2001, 31); -- '2001-01-31' select makedate(2001, 32); -- '2001-02-01' select maketime(12, 15, 30); -- '12:15:30'
Unix 时间戳、日期互换
- unix_timestamp()
- unix_timestamp(date)
- from_unixtime(unix_timestamp)
- from_unixtime(unix_timestamp, format)
select unix_timestamp(); -- 1218290027 select unix_timestamp('2008-08-08'); -- 1218124800 select unix_timestamp('2008-08-08 12:30:00'); -- 1218169800 select from_unixtime(1218290027); -- '2008-08-09 21:53:47' select from_unixtime(1218124800); -- '2008-08-08 00:00:00' select from_unixtime(1218169800); -- '2008-08-08 12:30:00' select from_unixtime(1218169800, '%Y %D %M %h:%i:%s %x'); -- '2008 8th August 12:30:00 2008'
时区(timezone)转换
- convert_tz(dt, from_tz, to_tz)
select convert_tz('2008-08-08 12:00:00', '+08:00', '+00:00'); -- 2008-08-08 04:00:00 -- 时区转换也可以通过 date_add, date_sub, timestampadd 来实现。 select date_add('2008-08-08 12:00:00', interval -8 hour); -- 2008-08-08 04:00:00 select date_sub('2008-08-08 12:00:00', interval 8 hour); -- 2008-08-08 04:00:00 select timestampadd(hour, -8, '2008-08-08 12:00:00'); -- 2008-08-08 04:00:00
5)提取
- date(datetime):返回 datetime 中的日期部分。
- year/month/day(datetime):返回 datime 中的年/月/日部分。
- extract():返回日期/时间的单独部分,比如年、月、日、小时、分钟等。
-- 语法 EXTRACT(unit FROM date) -- 示例 mysql> SELECT -> EXTRACT(YEAR FROM NOW()) AS years , -> EXTRACT(MONTH FROM NOW()) AS months, -> EXTRACT(DAY FROM NOW()) AS days; +-------+--------+------+ | years | months | days | +-------+--------+------+ | 2021 | 6 | 8 | +-------+--------+------+ 1 row in set (0.02 sec) mysql> SELECT NOW(), EXTRACT(DAY_SECOND FROM NOW()); +---------------------+--------------------------------+ | NOW() | EXTRACT(DAY_SECOND FROM NOW()) | +---------------------+--------------------------------+ | 2021-06-08 15:13:58 | 151358 | +---------------------+--------------------------------+ 1 row in set (0.00 sec)
unit 取值表:
复合函数实现查询上个月月末/月初的日期
-- 上个月月末 -- 1. 首先查询出来这个月过了几天 select EXTRACT(DAY FROM NOW()) AS DAY; -- 2. 用 DATE_SUB 减去这时间间隔 SELECT DATE_SUB(DATE_FORMAT(NOW(), '%y-%m-%d'), INTERVAL EXTRACT(DAY FROM NOW()) AS DAY); -- 上个月月初 -- 1. 算出这个月过了几天 select EXTRACT(DAY FROM NOW()) DAY; -- 假设今天时间为11月7日,这个月过了7天,如果11月7日减去6天的话,就是月初,所以6天就是距离月初的时间间隔 select EXTRACT(DAY FROM NOW()) - 1 DAY; -- 2. 用 date_sub 得到本月月初时间 SELECT DATE_SUB(DATE_FORMAT(NOW(), '%y-%m-%d'), INTERVAL EXTRACT(DAY FROM NOW()) - 1 DAY); -- 3. 继续用 date_sub 将上述结果,减去一个月 SELECT DATE_SUB(DATE_SUB(DATE_FORMAT(NOW(), '%y-%m-%d'), INTERVAL EXTRACT(DAY FROM NOW()) - 1 DAY), INTERVAL 1 MONTH);
字符串相关
语法 | 说明 |
CHARSET(str) | 返回字符串的字符集 |
CONCAT(str[, str2, str3, ...]) | 拼接字符串 |
INSTR(str, substr) | 返回 substr 在 str 中出现的位置,没有则返回 0 |
UCASE(str) / UPPER(STR) | 转换成大写 |
LCASE(str) / LOWER(STR) | 转换成小写 |
LEFT(str, len) | 从 str 的左边起取 len 个字符 |
SUBSTRING(str, position[, length]) | 从 str 的position 索引位开始取 length 个字符 |
LENGTH(str) | 返回 str 的长度 |
INSERT(str, x, y, instr) | 从字符串 str 的 x 位置开始,取 y 个字符替换为 instr |
REPLACE(str, search_str, replace_str) | 在 str 中用 replace_str 替换 search_str |
LTRIM(str)/RTRIM(str)/TRIM(str) | 去除字符串左/右/左右边的空格 |
LPAD(str, n, pad) | 从 str 左边开始用 pad 字符串进行填充,直到字符串长度为 n |
RPAD(str, n, pad) | 从 str 右边开始用 pad 字符串进行填充,直到字符串长度为 n |
REPEAT(str, n) | 将字符串 str 重复 n 次 |
示例:
mysql> select concat("hello", " ", "world"); +-------------------------------+ | concat("hello", " ", "world") | +-------------------------------+ | hello world | +-------------------------------+ 1 row in set (0.06 sec) mysql> select concat("hello", null, "world"); -- 只要含有null,则结果都为null +--------------------------------+ | concat("hello", null, "world") | +--------------------------------+ | NULL | +--------------------------------+ 1 row in set (0.00 sec) mysql> select INSTR("abcde", "bc"); -- 索引从1开始 +----------------------+ | INSTR("abcde", "bc") | +----------------------+ | 2 | +----------------------+ 1 row in set (0.04 sec) mysql> select insert("!!!!!", 2, 3, "world"); -- 索引从1开始 +--------------------------------+ | insert("!!!!!", 2, 3, "world") | +--------------------------------+ | !world! | +--------------------------------+ 1 row in set (0.00 sec) mysql> select lpad("hello", 15, "!"); +------------------------+ | lpad("hello", 15, "!") | +------------------------+ | !!!!!!!!!!hello | +------------------------+ 1 row in set (0.06 sec) mysql> select repeat("hello ", 3); +---------------------+ | repeat("hello ", 3) | +---------------------+ | hello hello hello | +---------------------+ 1 row in set (0.20 sec)
数学计算相关
语法 | 说明 |
ABS(num) | 绝对值 |
BIN(num) | 十进制转二进制 |
CEIL(num) / CEILING(num) | 向上取整 |
FLOOR(num) | 向下取整 |
CONV(num, from_base, to_base) | 进制转换 |
FORMAT(num, decimal_places) | 保留小数位数 |
HEX(num) | 十进制转十六进制 |
LEAST(num1[, num2, num3, ...]) | 取最小值 |
GREATEST(num1[, num2, num3, ...]) | 取最大值 |
MOD(num, denominator) | 取余 |
RAND([seed]) |
返回 [0, 1) 的随机数 (同样的种子得到同样的随机序列) |
ROUND(float, n) |
四舍五入,保留 n 位小数 |
TRUNCATE(float_num, n) |
将浮点数进行截断,保留 n 位小数 |
示例:
mysql> select ROUND(10.4999, 1); +-------------------+ | ROUND(10.4999, 1) | +-------------------+ | 10.5 | +-------------------+ 1 row in set (0.00 sec) mysql> select TRUNCATE(10.4999, 1); +----------------------+ | TRUNCATE(10.4999, 1) | +----------------------+ | 10.4 | +----------------------+ 1 row in set (0.00 sec) mysql> select ROUND(RAND()*100); +-------------------+ | ROUND(RAND()*100) | +-------------------+ | 9 | +-------------------+ 1 row in set (0.00 sec)
流程控制相关
语法 | 说明 |
IF(condition, t, f) | 如果 condition 为真,返回 t,否则返回 f |
IFNULL(values1, value2) | 如果 value1 不为 null,返回 value1;否则返回 value2 |
示例: