MySQL常用命令

MySQL常用命令

日期 / 时间函数

-- 当前日期时间
	select now()
	select sysdate()
	select current_timestamp()
-- 当前日期
	select current_date()
-- 当前时间
	select current_time()
-- 获取日期部分
	select date('yyyy-mm-dd hh:ii:ss')
-- 获取时间部分
	select time('yyyy-mm-dd hh:ii:ss')
-- 格式化时间
	select date_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j')
-- 获得unix时间戳
	select unix_timestamp()
-- 从时间戳获得时间
	select from_unixtime()

日期时间计算函数

-- 获取日期的天
	select right(date_format(now(),'%Y-%m-%d'), 2)
-- 获取日期的月
	select mid(date_format(now(),'%Y-%m-%d'), 6, 2)
-- 获取日期的年	
	select left(date_format(now(),'%Y-%m-%d'), 4)
-- 获取星期几
	select dayofweek(now())-1
-- 查询多少时间前（历史时间） year、month、day、hour、minite、second
	select date_sub(sysdate(),interval 10 year)	-- 10年前
	select date_sub(sysdate(),interval 5 day)	-- 5天前
	select date_sub(sysdate(),interval 3 month)	-- 3月前
	
	select subdate(sysdate(), interval 1 day) -- 一天前
	select subdate(sysdate(), interval -1 day) -- 一天后
-- 查询多少时间后（未来时间）：date_add、adddate
	select date_add(sysdate(),interval 10 year)	-- 10年后
	select date_add(sysdate(),interval 5 day)	-- 5天后
	select date_add(sysdate(),interval 3 month)	-- 3月后
	
	select adddate(sysdate(), interval 1 day) -- 一天后
	select adddate(sysdate(), interval -1 day) -- 一天前
-- 日期、时间相减函数：datediff(date1,date2), timediff(time1,time2)	
  select datediff('2020-08-08', '2020-08-01'); -- 7
  select datediff('2020-08-01', '2020-08-08'); -- -7
  
  select timediff('2020-08-08 08:08:08', '2020-08-08 00:00:00'); -- 08:08:08
	select timediff('08:08:08', '00:00:00'); -- 08:08:08
-- 时间戳（timestamp）增、减函数：	
  timestamp(dt,time) -- dt + time
  timestampadd(unit,interval,datetime_expr)
  timestampdiff(unit,datetime_expr1,datetime_expr2)

日期时间转换函数

-- （日期/时间转换为字符串）函数：date_format(date,format)，time_format(time,format)
	select date_format('2020-08-08 22:23:01', '%Y%m%d%H%i%s');
--  （字符串转换为日期）函数：str_to_date(str, format)
  select str_to_date('03/13/2020', '%m/%d/%Y'); -- 2020-03-13
  select str_to_date('03/13/20' , '%m/%d/%y'); -- 2020-03-13
  select str_to_date('03.13.2020', '%m.%d.%Y'); -- 2020-03-13
  select str_to_date('08:09:30', '%h:%i:%s'); -- 08:09:30
  select str_to_date('03.13.2020 08:09:30', '%m.%d.%Y %h:%i:%s'); -- 2020-03-13 08:09:30
-- （日期、天数）转换函数：to_days(date), from_days(days)
	select to_days('0000-00-00'); -- 0
	select to_days('2020-03-13'); -- 737862
	
	SELECT from_days(737862) -- 2020-03-13
-- （时间、秒）转换函数：time_to_sec(time), sec_to_time(seconds)	
	select time_to_sec('01:00:05'); -- 3605
	select sec_to_time(3605); -- '01:00:05'
-- （拼凑日期、时间）函数：makdedate(year,dayofyear), maketime(hour,minute,second)
	select makedate(2020,31); -- '2020-01-31'
  select makedate(2020,32); -- '2020-02-01'
  select maketime(12,15,30); -- '12:15:30'
-- 时间戳（timestamp）转换函数：timestamp(date)
	select timestamp(now()) -- date to timestamp 
-- 时区（timezone）转换函数 convert_tz(dt,from_tz,to_tz)
  select convert_tz('2020-08-08 12:00:00', '+08:00', '+00:00'); -- 2020-08-08 04:00:00

format时间格式

%S, %s  两位数字形式的秒（ 00,01, ..., 59）
%I, %i  两位数字形式的分（ 00,01, ..., 59）
%H  		两位数字形式的小时，24 小时（00,01, ..., 23）
%h  		两位数字形式的小时，12 小时（01,02, ..., 12）
%k  		数字形式的小时，24 小时（0,1, ..., 23）
%l  		数字形式的小时，12 小时（1, 2, ..., 12）
%T  		24小时的时间形式（hh:mm:ss）
%r  		12小时的时间形式（hh:mm:ss AM 或hh:mm:ss PM）
%p  		AM或PM
%W  		一周中每一天的名称（Sunday, Monday, ..., Saturday）
%a  		一周中每一天名称的缩写（Sun, Mon, ..., Sat）
%d  		两位数字表示月中的天数（00, 01,..., 31）
%e  		数字形式表示月中的天数（1, 2， ..., 31）
%D  		英文后缀表示月中的天数（1st, 2nd, 3rd,...）
%w  		以数字形式表示周中的天数（ 0 = Sunday, 1=Monday, ..., 6=Saturday）
%j  		以三位数字表示年中的天数（ 001, 002, ..., 366）
%U  		周（0, 1, 52），其中Sunday 为周中的第一天
%u  		周（0, 1, 52），其中Monday 为周中的第一天
%M  		月名（January, February, ..., December）
%b  		缩写的月名（ January, February,...., December）
%m  		两位数字表示的月份（01, 02, ..., 12）
%c  		数字表示的月份（1, 2, ...., 12）
%Y  		四位数字表示的年份
%y  		两位数字表示的年份
%%  		直接值“%”

设置查看 / 修改

timestamp列自动更新将来会被弃用（explicit_defaults_for_timestamp）

​ explicit_defaults_for_timestamp 变量会直接影响表结构，也就是说explicit_defaults_for_timestamp的作用时间是在表定义的时候；你的update | insert 想通过它去改变行为已经太晚了！

# explicit_defaults_for_timestamp=off 时表结构
CREATE TABLE `t` (
  `x` int(11) DEFAULT NULL,
  `y` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- `y` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

-- ---------------------------------------------------------------- --

# explicit_defaults_for_timestamp=on 时表结构
CREATE TABLE `t6` (
  `x` int(11) DEFAULT NULL,
  `y` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- `y` timestamp NULL DEFAULT NULL

大小写敏感设置（lower_case_table_names）

lower_case_table_names： 此参数不可以动态修改，必须重启数据库
lower_case_table_names = 1 表名存储在磁盘是小写的，但是比较的时候是不区分大小写
lower_case_table_names=0 表名存储为给定的大小和比较是区分大小写的
lower_case_table_names=2, 表名存储为给定的大小写但是比较的时候是小写的

# 查看mysql大小写设置
show variables like 'lower%';

# 修改mysql大小写设置（修改后需要重启mysql）
	修改 /etc/my.cnf 中 [mysqld] 设置 lower_case_table_names = 1

常用语法

ALTER 表结构修改

-- 增加列
alter table [表名] add column [新列名] [属性] default [默认值] comment [注释] after [列名]
-- 删除列
alter table [表名] drop column [列名]

WITH AS 公用表表达式 CTE（mysql 8.0+）

with as初级用法

-- 格式： 
	with [虚拟表名] as ( [虚拟表语句] )
	
-- 简单例子（with as）
	with xxx_name as ( select current_timestamp )
	select * from xxx_name
-- 复杂例子：递归调用（with recursive as）
	with recursive derived(n)
	as ( 
    		select 1 
    	union all 
    		select n + 1 from derived where n < 5 
  )
  select * from derived;

with as高级用法

假设要执行分层数据遍历，以便为每个员工生成一个组织结构图（即从CEO到每个员工的路径），也可以使用递归CTE

• 创建带有manager_id的测试表

mysql> create table employees.employees_mgr ( 
    -> id int primary key not null,
    -> name varchar(100) not null,
    -> manager_id int null,
    -> index (manager_id),
    -> foreign key (manager_id) references employees_mgr (id)
    -> );
Query OK, 0 rows affected (0.03 sec)

• 插入示例数据

mysql> insert into employees.employees_mgr values 
    -> (333, "Yasmina", null), /* Yasmina is the CEO (manager_id is null) */
    -> (198, "John", 333), /* John has id 198 and reports to 333 (Yasmina) */
    -> (692, "Tarek", 333),
    -> (29, "Pedro ", 198),
    -> (4610, "Sarah", 29),
    -> (72, "Pierre", 29),
    -> (123, "Adil", 692);
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select id, name, manager_id from employees.employees_mgr;
+------+---------+------------+
| id   | name    | manager_id |
+------+---------+------------+
|   29 | Pedro   |        198 |
|   72 | Pierre  |         29 |
|  123 | Adil    |        692 |
|  198 | John    |        333 |
|  333 | Yasmina |       NULL |
|  692 | Tarek   |        333 |
| 4610 | Sarah   |         29 |
+------+---------+------------+
7 rows in set (0.00 sec)

• 执行递归CTE

mysql> with recursive employee_paths (id, name, path) as (
    -> select id, name, cast(id as char(200))
    -> from employees.employees_mgr
    -> where manager_id is null
    -> union all
    -> select e.id, e.name, concat(ep.path, '->', e.id)
    -> from employee_paths as ep
    -> join employees.employees_mgr as e
    -> on ep.id = e.manager_id
    -> )
    -> select * from employee_paths;
+------+---------+--------------------+
| id   | name    | path               |
+------+---------+--------------------+
|  333 | Yasmina | 333                |
|  198 | John    | 333->198           |
|  692 | Tarek   | 333->692           |
|   29 | Pedro   | 333->198->29       |
|  123 | Adil    | 333->692->123      |
|   72 | Pierre  | 333->198->29->72   |
| 4610 | Sarah   | 333->198->29->4610 |
+------+---------+--------------------+
7 rows in set (0.00 sec)

with recursive employee_paths (id, name, path) as是CTE的名称，列是(id, name, path)。
  select id, name, cast(id as char(200)) from employees.employees_mgr where manager_id is null是查询CEO的seed查询（ 没有在CEO之上的管理者）。
  select e.id, e.name, concat(ep.path, '->', e.id) from employee_paths as ep join employees.employees_mgr as e on ep.id = e.manager_id是递归查询。
  递归查询生成的每一行，会查找直接向前一行生成的员工做汇报的所有员工。对于每个员工，该行的信息包括员工ID、 姓名和员工管理链，该链是在最后添加了员工ID的管理链`

踩坑

sysdate()、now()、current_timestamp()的区别

​ sysdate() 日期时间函数跟 now() 类似，不同之处在于：now() 在执行开始时值就得到了，并且未重新赋值不会改变；sysdate() 在函数执行时动态得到值；sysdate() 日期时间函数，一般情况下很少用到

mysql> select now(), sleep(3), now();

+---------------------+----------+---------------------+
| now() | sleep(3) | now() |
+---------------------+----------+---------------------+
| 2020-08-08 22:28:21 | 0 | 2020-08-08 22:28:21 |
+---------------------+----------+---------------------+

mysql> select sysdate(), sleep(3), sysdate();

+---------------------+----------+---------------------+
| sysdate() | sleep(3) | sysdate() |
+---------------------+----------+---------------------+
| 2020-08-08 22:28:21 | 0 | 2020-08-08 22:28:24 |
+---------------------+----------+---------------------+

mysql> select current_timestamp(), sleep(3), current_timestamp();

+---------------------+----------+---------------------+
| current_timestamp() | sleep(3) | current_timestamp() |
+---------------------+----------+---------------------+
| 2020-08-08 22:28:21 | 0 | 2020-08-08 22:28:21 |
+---------------------+----------+---------------------+

timediff(time1,time2) 函数的两个参数类型必须相同

MySQL timestampdiff() 函数就比 datediff() 功能强多了，datediff() 只能计算两个日期（date）之间相差的天数

select timestamp('2020-08-08'); -- 2020-08-08 00:00:00
select timestamp('2020-08-08 08:00:00', '01:01:01'); -- 2020-08-08 09:01:01
select timestamp('2020-08-08 08:00:00', '10 01:01:01'); -- 2020-08-18 09:01:01

select timestampadd(day, 1, '2020-08-08 08:00:00'); -- 2020-08-09 08:00:00
select date_add('2020-08-08 08:00:00', interval 1 day); -- 2020-08-09 08:00:00

MySQL timestampadd() 函数类似于 date_add()。
select timestampdiff(year,'2002-05-01','2001-01-01'); -- -1
select timestampdiff(day ,'2002-05-01','2001-01-01'); -- -485
select timestampdiff(hour,'2020-08-08 12:00:00','2020-08-08 00:00:00'); -- -12

select datediff('2020-08-08 12:00:00', '2020-08-01 00:00:00'); -- 7