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


作者:true2over

相关推荐

使用.Net Core编写命令行工具(CLI)的方法

使用.Net Core编写命令行工具(CLI)的方法

Vue中el-form标签中的自定义el-select下拉框标签功能

javascript设计模式 – 命令模式原理与用法实例分析

oracle基本查询操作子查询用法实例分析