MySQL常用命令

于秋 发布于 04/02 16:01
阅读 110
收藏 0

Serverless 架构就不要服务器了?>>>

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()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
日期时间计算函数
-- 获取日期的天
    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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
日期时间转换函数
-- (日期/时间转换为字符串)函数: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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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🇲🇲ss)
%r          12小时的时间形式(hh🇲🇲ss AM 或hh🇲🇲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          两位数字表示的年份
%%          直接值“%”
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
设置查看 / 修改
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
大小写敏感设置(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
1
2
3
4
5
常用语法
ALTER 表结构修改
-- 增加列
alter table [表名] add column [新列名] [属性] default [默认值] comment [注释] after [列名]
-- 删除列
alter table [表名] drop column [列名]
1
2
3
4
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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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)
1
2
3
4
5
6
7
8
插入示例数据
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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
执行递归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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
with recursive employee_paths (id, name, path) as是CTE的名称,列是(id, name, path)。郑州不孕不育医院哪家好:http://www.xbzztj.com/
  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 |
+---------------------+----------+---------------------+
1
2
3
4
5
6
7
mysql> select sysdate(), sleep(3), sysdate();

+---------------------+----------+---------------------+
| sysdate() | sleep(3) | sysdate() |
+---------------------+----------+---------------------+
| 2020-08-08 22:28:21 | 0 | 2020-08-08 22:28:24 |
+---------------------+----------+---------------------+
1
2
3
4
5
6
7
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 |
+---------------------+----------+---------------------+
1
2
3
4
5
6
7
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

加载中
返回顶部
顶部