MySQL工作总结

1.在表中具体某个位置上新增一列

(1).在某列后面新增一列

ALTER TABLE '表名' ADD COLUMN '增加列名' VARCHAR(20) NOT NULL COMMENT '注释' AFTER '列名';

(2).新增一列指定为表中的第一列

ALTER TABLE '表名' ADD COLUMN '增加列名' VARCHAR(20) NOT NULL COMMENT '注释' FIRST;

2.FORMAT 格式化

#保留4位小数
SELECT FORMAT('2013.123456',4);

运行结果：

 3.IFNULL 为空判断

#如果为空，默认为"0"
SELECT IFNULL(NULL,"0");

#如果为空，默认为"晓"
SELECT IFNULL(NULL,"晓");

运行结果：

 

  4.DATE_FORMAT 格式化日期形式

SET @batchDate = "2020-12-28";
SELECT DATE_SUB(@batchDate,INTERVAL WEEKDAY(@batchDate) +0 DAY) AS "本周一",DATE_SUB(@batchDate,INTERVAL WEEKDAY(@batchDate) -6 DAY) AS "本周日";

运行结果：

5.获取本周第一天和最后一天、上周第一天和最后一天

SET @batchDate = "2020-12-28";
SELECT DATE_SUB(@batchDate,INTERVAL WEEKDAY(@batchDate) +0 DAY) AS "本周一",DATE_SUB(@batchDate,INTERVAL WEEKDAY(@batchDate) -6 DAY) AS "本周日";

运行结果：

SET @batchDate = "2020-12-28";
SELECT DATE_SUB(@batchDate,INTERVAL WEEKDAY(@batchDate) +7 DAY) AS "上周一",DATE_SUB(@batchDate,INTERVAL WEEKDAY(@batchDate) +1 DAY) AS "上周日";

运行结果：

6.获取当月的第一天和最后一天、上月的第一天和最后一天

#获取日期"20201228"这个日期当前月的第一天和最后一天日期
SELECT DATE_ADD("20201228",INTERVAL -DAY("20201228")+1 DAY) AS '当月的第一天日期',LAST_DAY("20201228") AS '当月的最后一天日期';

运行结果：

#获取日期"2020-12-28"上一个月的第一天和最后一天日期
SELECT ADDDATE(LAST_DAY(SUBDATE("2020-12-28",INTERVAL 2 MONTH)),1) AS "上个月的第一天",LAST_DAY(SUBDATE("2022-12-28",INTERVAL 1 MONTH)) AS "上个月的最后一天";

 运行结果：

7.获取上一年当月的第一天和最后一天

SET @batchDate = "2020-12-28";
SELECT DATE_ADD(DATE_ADD(@batchDate,INTERVAL -DAY(@batchDate)+1 DAY),INTERVAL -12 MONTH) AS '去年当月第一天',DATE_ADD(LAST_DAY(@batchDate),INTERVAL -12 MONTH) AS '去年当月最后一天';

运行结果：

 

8.获取日期中的year、month、day

#获取日期中的year、month、day
set @dateTemp = "20201228";
SELECT YEAR(@dateTemp),MONTH(@dateTemp),DAY(@dateTemp);

运行结果：

9.获取当前日期月份的天数

#计算当前日期月份的天数的
SELECT DAY(LAST_DAY(CURDATE())) AS "方式一天数",TIMESTAMPDIFF(DAY,CURDATE(),(DATE_ADD(CURDATE(),INTERVAL 1 MONTH))) AS "方式二天数";

 运行结果：

10.获取当年的第一天和最后一天

#获取当年的第一天和最后一天
SELECT CONCAT(YEAR("2020-12-28"),"-01-01") AS "当年的第一天", CONCAT(YEAR("2020-12-28"),"-12-31") AS "当年的最后一天";

运行结果：

 

11.获取去年的第一天和最后一天

SET @batchDate = "2020-12-28";
SELECT CONCAT(YEAR(@batchDate)-1,"-01-01") AS '去年第一天',CONCAT(YEAR(@batchDate)-1,"-12-31") AS '去年最后一天';

运行结果：

 

12.计算两个日期之间相差天数

#计算date1和date2相差天数
SELECT DATEDIFF("2020-12-01","2020-11-01") AS "相差天数";

运行结果：

 

13.判断一个字符串是否包含某个字符

LOCATE(s,str):如果包含s字符就会返回这个字符在str字符串中的索引；否则返回0。

#字符串是否包含某个字段
SET @str = "ASGB";
SELECT LOCATE("S",@str) AS "是否包含S",LOCATE("B",@str) AS "是否包含B",LOCATE("W",@str) AS "是否包含W",LOCATE("T",NULL) AS "是否包含T";

 运行结果：

14.TIMESTAMPDIFF

(1).计算两个日期间隔的年数，精确到天

#计算两个日期间隔的年数，精确到天
SELECT TIMESTAMPDIFF(YEAR,'2020-01-08','2022-01-07') AS "YEAR";

(2).根据身份证号计算年龄，精确到天

#根据身份证计算年龄，精确到天
SELECT TIMESTAMPDIFF(YEAR,SUBSTRING('410425199112216013',7,8),'2021-12-21') AS "AGE";

15.更换主键操作

(1).删除主键

1）新建表

#新建表
CREATE TABLE temp_table(
app_no VARCHAR(30) PRIMARY KEY COMMENT '授信号',
id_no VARCHAR(10) DEFAULT NULL COMMENT '身份证号'
);

2）删除主键

#删除主键
ALTER TABLE temp_table DROP PRIMARY KEY;

删除前：

删除后：

(2).更换主键

        有时候系统会要求表中必须有主键，这时候如果变更主键，需要将删除主键和新增主键归为一条语句，同时进行，否则会报错。

1）新建表

#新建表
CREATE TABLE temp_table(
app_no VARCHAR(30) PRIMARY KEY COMMENT '授信号',
id_no VARCHAR(10) DEFAULT NULL COMMENT '身份证号'
);

2）更换主键

#更换主键
ALTER TABLE temp_table DROP PRIMARY KEY,ADD id BIGINT(19) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'id' FIRST;

变更前：

变更后：

16.获取几个值中的最大值

#获取几个值中的最大值
SELECT GREATEST(45,55,12) AS "最大的值";

运行结果：

17.数据库内存满了，使用delete删除数据表后内存没有减少，反而增加了

原因：

delete 删除数据时，其实对应的数据行并不是真正的删除，仅仅是将其标记成可复用的状态，所以表空间不会变小。并且delete语句每次删除一行，会在事务日志中为所删除的每行记录一项。所以就会出现使用delete清除数据空间后，不但空间没有变小，反而增大的原因。

解决办法：

RENAME TABLE yak_key_context to yak_key_context_hst;
CREATE TABLE yak_key_context LIKE yak_key_context_hst;
DROP TABLE yak_key_context_hst;

参考：

(1)MySQL基础系列之 DDL、DML和DCL的区别与理解_通往架构师的路上的博客-CSDN博客

(2)Mysql数据库执行delete命令删除表数据后，主机磁盘空间为什么还被一直占用着呢？_不太想事贼佛系的CV码农的博客-CSDN博客_mysql 一直执行 delete数据 (3)MySQL中Delete和Truncate区别_mysql truncate和delete_Decade102的博客-CSDN博客

18.查询数据库中表的内存

(1)查询某个数据库中每个表所占内存：

-- 查询mysql数据库中每个表所占空间，倒序排序
-- 方法一：
SELECT TABLE_NAME AS '表名',CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024),2),"MB") AS 'data'
FROM information_schema.`TABLES`
WHERE TABLE_SCHEMA = 'mysql' -- 数据库
GROUP BY TABLE_NAME         
ORDER BY DATA_LENGTH DESC;

-- 方法二：
SELECT TABLE_NAME AS '表名',TRUNCATE(DATA_LENGTH/1024/1024,2) AS 'data'
FROM information_schema.`TABLES`
WHERE TABLE_SCHEMA = 'mysql' -- 数据库
GROUP BY TABLE_NAME         
ORDER BY DATA_LENGTH DESC;

方法一运行结果：

方法二运行结果：

(2)查询某个数据库中某个表所占内存：

-- 查询mysql数据库中具体某个表的所占空间
-- 方法一：
SELECT  TABLE_NAME AS '表名',CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024),2),"MB") AS 'data'
FROM information_schema.`TABLES`
WHERE TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'help_topic';

-- 方法二:
SELECT  TABLE_NAME AS '表名',TRUNCATE(DATA_LENGTH/1024/1024,2) AS 'data'
FROM information_schema.`TABLES`
WHERE TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'help_topic';

方法一运行结果：

方法二运行结果：

19.查看当前安装mysql数据库的版本