常用语句 - MySQL

##查询表
	show tables;	#查看所有表
	show tables like '%[条件]%'	#查看包含特定名称的表
	show tables where Tables_in_[数据名] not like '%[条件]%'	#查看不包含特定名称的表

##优化表
	optimize table <表名>	#优化表

##查看innodb内存情况
	show engine innodb status\G

##字段

#时间字段：
	`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
	`last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',

#删除某个字段：
	ALTER TABLE `tableName` drop COLUMN `columeName`;

#增加某个字段：
	ALTER TABLE `tableName` ADD COLUMN `columeName` varchar(255) NOT NULL DEFAULT '' COMMENT '备注信息' AFTER `anotherColumeName`;

#修改某个字段：
	ALTER TABLE `tableName` MODIFY COLUMN `columeName` DATETIME NOT NULL DEFAULT '0001-00-00 00:00:00' COMMENT '时间' AFTER `anotherColumeName`;

##日期格式化
	-- 日期补0
	select concat(YEAR(calltime),'-',Right(100 + MONTH(calltime),2))

-- 日期字符串转时间戳，10位，数据库存储的是13位
	select UNIX_TIMESTAMP('2019-09-01 00:00:00');

-- 时间戳转日期
	select FROM_UNIXTIME(1567267200,'%Y-%m-%d %H:%i:%s')

---
		DATE_FORMAT(date,format)
	-----------------------------------------------------------
	参数：
	%Y	年，4 位
	%m	月，数值(00-12)
	%d	月的天，数值(00-31)
	%H	小时 (00-23)
	%i	分钟，数值(00-59)
	%s	秒(00-59)
	-----------------------------------------------------------
	select date_format(now(),'%Y-%m-%d %H:%i:%s')
	2020-07-04 21:48:46

---
##获取当前日期本周几
	-------------------------------------------------------------
	#上周六
	select subdate(curdate(),date_format(curdate(),'%w')+1);
	#上周日
	select subdate(curdate(),date_format(curdate(),'%w'));
	#本周一
	select subdate(curdate(),date_format(curdate(),'%w')-1);
	------------------------------------------------------------
	以下如此类推
	------------------------------------------------------------

---
###获取当前时间加上一个月
	select DATE_ADD(NOW(), interval 1 MONTH)

NOW()此处必须为时间格式

date_add() 增加
	date_sub()减少

month 月份
	minute 分钟
	second 秒

---
##复制自表数据
	insert into <表名> select 0或null,<所需列> from <表名> where <条件>；

---
##添加增值id
####创建一个新的带有自增ID的临时表：

CREATE TABLE tmp_table (
	  id INT AUTO_INCREMENT PRIMARY KEY,
	  -- 其他列
	);
####将原始表中的数据插入到临时表中：

INSERT INTO tmp_table SELECT NULL, * FROM original_table;
	#这将为临时表中的每一行生成一个新的自增ID。

####删除原始表：

DROP TABLE original_table;
####将临时表重命名为原始表的名称：

RENAME TABLE tmp_table TO original_table;
####现在，您的原始表将具有自增ID。

---
##多行合并一行
	select GROUP_CONCAT(agentname Separator ',') from dixiao_key where cost='60s';
---
##一行数据拆分多行
	SELECT
	    a.cutname,
	    substring_index(
	        substring_index(
	            a.remoteips,
	            ',',
	            b.help_topic_id + 1
	        ),
	        ',' ,- 1
	    ) AS remoteips
	FROM
	    (select remoteips,substring_index(name,'_',1) cutname from e_gatewaymapping where remoteips != '' and locktype!=3 group by remoteips) a
	JOIN mysql.help_topic b ON b.help_topic_id < (
	    length(a.remoteips) - length(
	        REPLACE (a.remoteips, ',', '')
	    ) + 1
	)

##查看所有数据库各表容量大小
	select table_schema as '数据库',
	table_name as '表名',
	table_rows as '记录数',
	truncate(data_length/1024/1024, 2) as '数据容量(MB)',
	truncate(index_length/1024/1024, 2) as '索引容量(MB)' 
	from information_schema.tables order by data_length desc, index_length desc;
	
##查看指定数据库容量大小
	select table_schema as '数据库',
	sum(table_rows) as '记录数', sum(truncate(data_length/1024/1024/1024, 2)) as '数据容量(GB)',
	sum(truncate(index_length/1024/1024/1024, 2)) as '索引容量(GB)' 
	from information_schema.tables group by  table_schema;

##复制表数据（含索引等属性）
	create table table_name2 like table_name1

##复制表数据（不含索引等属性）
	复制表(含数据)： 
	create table table_name2 as select * from table_name1

复制表(不含数据)： 
	create table table_name2 as select * from table_name1 where 1=2

只复制表数据：
	insert into table_name2 select * from table_name1

##查看碎片率
####查看指定数据库
    SELECT TABLE_SCHEMA as '数据库',
          TABLE_NAME as '表名',
          ENGINE as '引擎',
          ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) AS '表大小mb',
          ROUND(DATA_FREE/1024/1024,2) AS '碎片大小mb',
          ROUND((DATA_FREE / (DATA_LENGTH + INDEX_LENGTH)) * 100, 2) AS '碎片率'
    FROM information_schema.TABLES 
    WHERE TABLE_SCHEMA = '<库名>'
    ORDER BY 碎片大小mb DESC;

####查看指定表
    SELECT TABLE_SCHEMA as '数据库',
          TABLE_NAME as '表名',
          ENGINE as '引擎',
          ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) AS '表大小mb',
          ROUND(DATA_FREE/1024/1024,2) AS '碎片大小mb',
          ROUND((DATA_FREE / (DATA_LENGTH + INDEX_LENGTH)) * 100, 2) AS '碎片率'
    FROM information_schema.TABLES 
    WHERE TABLE_NAME = '<表名>'
    ORDER BY 碎片大小mb DESC;