mysql常用方法、语句_笔记

转自csdn

1. 时间日期相关关

1.1 mysql毫秒值 转为 天/时/分/秒

set @time = 671218437/1000; -- 毫秒转换为天时分秒(671218437为毫秒)
SELECT 
CONCAT(convert(TRUNCATE(@time/(24*3600), 0), char), '天') LAST_DAY,
CONCAT(convert(TRUNCATE(@time%(24*3600)/3600, 0), char), '小时') LAST_HOUR,
CONCAT(convert(TRUNCATE(@time%3600/60, 0), char), '分') LAST_MINUTE,
CONCAT(convert(TRUNCATE((@time)%60, 0), char), '秒') LAST_SECOND

1.2 sql时间日期转换

Select CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 1): 05/16/06
Select CONVERT(varchar(100), GETDATE(), 2): 06.05.16
Select CONVERT(varchar(100), GETDATE(), 3): 16/05/06
Select CONVERT(varchar(100), GETDATE(), 4): 16.05.06
Select CONVERT(varchar(100), GETDATE(), 5): 16-05-06
Select CONVERT(varchar(100), GETDATE(), 6): 16 05 06
Select CONVERT(varchar(100), GETDATE(), 7): 05 16, 06
Select CONVERT(varchar(100), GETDATE(), 8): 10:57:46
Select CONVERT(varchar(100), GETDATE(), 9): 05 16 2006 10:57:46:827AM
Select CONVERT(varchar(100), GETDATE(), 10): 05-16-06
Select CONVERT(varchar(100), GETDATE(), 11): 06/05/16
Select CONVERT(varchar(100), GETDATE(), 12): 060516
Select CONVERT(varchar(100), GETDATE(), 13): 16 05 2006 10:57:46:937
Select CONVERT(varchar(100), GETDATE(), 14): 10:57:46:967
Select CONVERT(varchar(100), GETDATE(), 20): 2006-05-16 10:57:47
Select CONVERT(varchar(100), GETDATE(), 21): 2006-05-16 10:57:47.157
Select CONVERT(varchar(100), GETDATE(), 22): 05/16/06 10:57:47 AM
Select CONVERT(varchar(100), GETDATE(), 23): 2006-05-16
Select CONVERT(varchar(100), GETDATE(), 24): 10:57:47
Select CONVERT(varchar(100), GETDATE(), 25): 2006-05-16 10:57:47.250
Select CONVERT(varchar(100), GETDATE(), 100): 05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 101): 05/16/2006
Select CONVERT(varchar(100), GETDATE(), 102): 2006.05.16
Select CONVERT(varchar(100), GETDATE(), 103): 16/05/2006
Select CONVERT(varchar(100), GETDATE(), 104): 16.05.2006
Select CONVERT(varchar(100), GETDATE(), 105): 16-05-2006
Select CONVERT(varchar(100), GETDATE(), 106): 16 05 2006
Select CONVERT(varchar(100), GETDATE(), 107): 05 16, 2006
Select CONVERT(varchar(100), GETDATE(), 108): 10:57:49
Select CONVERT(varchar(100), GETDATE(), 109): 05 16 2006 10:57:49:437AM
Select CONVERT(varchar(100), GETDATE(), 110): 05-16-2006
Select CONVERT(varchar(100), GETDATE(), 111): 2006/05/16
Select CONVERT(varchar(100), GETDATE(), 112): 20060516
Select CONVERT(varchar(100), GETDATE(), 113): 16 05 2006 10:57:49:513
Select CONVERT(varchar(100), GETDATE(), 114): 10:57:49:547
Select CONVERT(varchar(100), GETDATE(), 120): 2006-05-16 10:57:49
Select CONVERT(varchar(100), GETDATE(), 121): 2006-05-16 10:57:49.700
Select CONVERT(varchar(100), GETDATE(), 126): 2006-05-16T10:57:49.827

1.2 时间差

1.Mysql如何计算两个时间字段的差值？可用函数 TIMESTAMPDIFF()
-----------------------------

# TIMESTAMPDIFF函数，有参数设置，可以精确到天(DAY)、小时(HOUR)，分钟(MINUTE)和秒(SECOND)，
# 使用起来比datediff函数更加灵活。对于比较的两个时间，时间小的放在前面，时间大的放在后面。

--相差1天

select TIMESTAMPDIFF(DAY, '2018-03-20 23:59:00', '2015-03-22 00:00:00');

--相差49小时

select TIMESTAMPDIFF(HOUR, '2018-03-20 09:00:00', '2018-03-22 10:00:00');

--相差2940分钟

select TIMESTAMPDIFF(MINUTE, '2018-03-20 09:00:00', '2018-03-22 10:00:00');

--相差176400秒

select TIMESTAMPDIFF(SECOND, '2018-03-20 09:00:00', '2018-03-22 10:00:00');

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

2.时间推算

--14个月前

SELECT DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -14 MONTH), '%Y/%m/%d')

日期转换计算

日期转换计算：
select date('2022-02-02 23:33:33'); => 2022-02-02

-- 当前月的最后一天
select last_day(now());

-- 下月第一天
select date_add(last_day(now()), interval 1 day);

-- 当天为当月的第几天
select day(now());

-- 当月第一天
select date_add(now(), interval 1-(day(curdate())) day);

--当前日期的上一个月
DATE_SUB(now(),INTERVAL 1 MONTH)

--获取年，月，日
year(now())
month(now())
day(now())

2. 操作数据库（表）相关

2.1 删除表数据

先说总结：

1、当你不再需要该表时， 用 drop;
2、当你仍要保留该表，但要删除所有数据表记录时， 用 truncate;
3、当你要删除部分记录或者有可能会后悔的话， 用 delete。

清空表数据命令： truncate

truncate table 表名

1. 不能与where一起使用。
2. truncate删除数据后是不可以rollback的。
3. .truncate删除数据后会重置Identity（标识列、自增字段），相当于自增列会被置为初始值，又重新从1开始记录，而不是接着原来的ID数。
4. truncate删除数据后不写服务器log，整体删除速度快。
5. truncate删除数据后不激活trigger(触发器)。

删除表命令：drop

drop table 表名;
drop table if exists 表名 ;

truncate只会清除表数据，drop不光清除表数据还要删除表结构。

清空数据表内容命令： delete

delete from 表名 where id = '1';
delete from 表名;

delete含义：你要删除哪张表的数据 ?你要删掉哪些行 ?
delete可以删除一行，也可以删除多行；
如果不加where条件，则是删除表所有的数据，这是很危险的！不建议这样做！

2.2 DDL

1 ALTER TABLE：添加，修改，删除表的列，约束等表的定义。     
 2 查看列：desc 表名;  
 3 修改表名：alter table t_book rename to bbb;  
 4 添加列：alter table 表名 add column 列名 varchar(30);  
 5 删除列：alter table 表名 drop column 列名;  
 6 修改列名MySQL： alter table bbb change nnnnn hh int;  
 7 修改列名SQLServer：exec sp_rename't_student.name','nn','column';  
 8 修改列名Oracle：lter table bbb rename column nnnnn to hh int;  
 9 修改列属性：alter table t_book modify name varchar(22);  
10 sp_rename：SQLServer 内置的存储过程，用与修改表的定义。 

# 建表
CREATE TABLE IF NOT EXISTS wf.${targetTable} LIKE wf.${sourceTable};
  #  添加字段  
 1 alter table user add COLUMN new1 VARCHAR(20) DEFAULT NULL; //增加一个字段，默认为空  
 2 alter table user add COLUMN new2 VARCHAR(20) NOT NULL;  //增加一个字段，默认不能为空

# MySQL 查看约束，添加约束，删除约束 添加列，修改列，删除列  
 1 查看表的字段信息：desc 表名;  
 2 查看表的所有信息：show create table 表名;  
 3 添加主键约束：alter table 表名 add constraint 主键 （形如：PK_表名） primary key 表名(主键字段);  
 4 添加外键约束：alter table 从表 add constraint 外键（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);  
 5 删除主键约束：alter table 表名 drop primary key;  
 6 删除外键约束：alter table 表名 drop foreign key 外键（区分大小写）;  
 7 修改表名：alter table t_book rename to bbb;  
 8 添加列：alter table 表名 add column 列名 varchar(30);  
 9 删除列：alter table 表名 drop column 列名;  
10 修改列名MySQL： alter table bbb change nnnnn hh int;  
11 修改列名SQLServer：exec sp_rename't_student.name','nn','column';  
12 修改列名Oracle：alter table bbb rename column nnnnn to hh int;  
13 修改列属性：alter table t_book modify name varchar(22);  
14 sp_rename：SQLServer 内置的存储过程，用与修改表的定义。

3. Sql速查表