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
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值