Mysql常用技巧汇总

约定不等于承诺〃 2022-06-11 01:20 274阅读 0赞

Mysql使用技巧  
**1、在表有外键关联时不能直接删除数据，可以先将外键检查关闭，删除数据后再打开**  
\#关闭外键检查  
SET FOREIGN\_KEY\_CHECKS=0;  
\#打开外键检查  
SET FOREIGN\_KEY\_CHECKS=1;  
**2、时间字段取，前一天或后一天的函数date\_sub(curdate(),INTERVAL expr type)**  
\#select date\_sub(curdate(),INTERVAL expr type) from dual;  
正数是时间往前推，type 是day表示按天，month表示按月，year表示按年  
SELECT CURDATE(),DATE\_SUB(CURDATE(),INTERVAL 1 DAY) FROM DUAL;  
result: 2017-07-26 2017-07-25  
负数是时间往后推  
SELECT CURDATE(),DATE\_SUB(CURDATE(),INTERVAL -1 DAY) FROM DUAL;  
result: 2017-07-26 2017-07-27  
SELECT CURDATE(),DATE\_SUB(CURDATE(),INTERVAL -1 MONTH) FROM DUAL;  
result: 2017-07-26 2017-08-26  
SELECT CURDATE(),DATE\_SUB(CURDATE(),INTERVAL -1 YEAR) FROM DUAL;  
result: 2017-07-26 2018-07-26  
**3、字符串和时间的转换**  
date\_format(date, format) 函数，MySQL日期格式化函数date\_format()  
str\_to\_date(str, format) 函数  
unix\_timestamp(),unix\_timestamp(date) 函数 \#字符串和date转化为时间戳  
from\_unixtime(unix\_timestamp),from\_unixtime(unix\_timestamp, format) 函数，\#时间戳转化为时间或字符串  
\#date转化为字符串  
SELECT CURDATE(),DATE\_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),DATE\_FORMAT(CURDATE(),'%Y-%M-%d'),DATE\_FORMAT(CURDATE(),'%y-%m-%d'),DATE\_FORMAT(CURDATE(),'%Y-%m-%D') FROM DUAL;  
result:2017-07-27 2017-07-27 00:00:00 2017-July-27 17-07-27 2017-07-27th  
\#字符串转化为date  
SELECT '2017-07-27',STR\_TO\_DATE('2017-07-27','%Y-%m-%d %H:%I:%S') FROM DUAL;  
result:2017-07-27 2017-07-27 00:00:00  
\#时间和字符串转化为时间戳  
SELECT CURDATE(),UNIX\_TIMESTAMP(),UNIX\_TIMESTAMP(CURDATE()),UNIX\_TIMESTAMP('2017-07-27 14:22:33') FROM DUAL;  
result:2017-07-27 1501136577 1501084800 1501136553  
\#时间戳转化为date和字符串  
SELECT NOW(),CURDATE(),FROM\_UNIXTIME(UNIX\_TIMESTAMP()),FROM\_UNIXTIME(150930294),FROM\_UNIXTIME(150930294,'%Y-%m-%d') FROM DUAL;

result:2017-07-27 14:35:252017-07-272017-07-27 14:35:251974-10-14 05:04:541974-10-14

**4、同一张表两个字段值互换**  
表test包括id,column1和column2，3个字段，id是主键，将column1和column2互换值。  
不能使用update test set column1=column2,column2=column1;  
因为更新是分步骤来的第一步column1=column2后，这时候column1的值和column2是相同的，再column2=column1就没用了。  
所以需要使用下面这种方法  
update test a,test b  
set a.column1=b.column2,  
    a.column2=b.column1

where a.id=b.id;

**5、Sqlyog查看表字段及表定义**  
desc table\_name;  
show create table table\_name;

**6、字符串转数字**  
1)转换为整数  
select CAST('123' AS SIGNED) FROM DUAL;  
2)转换为指定小数位的小数  
select CAST('123.343' AS DECIMAL(9，2)) FROM DUAL;

**7、delete语句使用**

DELETE FROM table\_name \[WHERE Clause\]  
\#delete语句表使用别名会报错  
DELETE FROM \`zy\_mobile\_gelder\` t WHERE t.brand = '';  
\#You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where t.brand = ''' at line 1  
\#去掉别名就没有问题了  
DELETE FROM \`zy\_mobile\_gelder\`  WHERE brand = '';  
**8、根据分隔符截取字符串**  
SELECT SUBSTRING\_INDEX('a-b-c','-',1),SUBSTRING\_INDEX('a-b-c','-',2),SUBSTRING\_INDEX('a-b-c','-',-1) FROM DUAL;  
result:aa-b c

**9、查询一个表（t1）的某个字段记录不在另一个表（t2）中**  
SELECT t1.\* FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;

**10、截取字符串**  
截取字符串的函数:left(), right(), substring(), substring\_index(),mid(), substr()。mid(), substr() 等价于 substring()。  
1)字符串截取：left(str, length),从左往右截取指定长度字符串  
SELECT LEFT('23432534534',2) FROM DUAL;  
\#23  
2)字符串截取：right(str, length)，从右往左截取指定长度字符串  
SELECT RIGHT('23432534534',2) FROM DUAL;  
\#34  
3)字符串截取：substring(str, pos); substring(str, pos, len),从指定位置截取指定字符串，当pos为负数时，从右向左截取指定长度字符串。  
SELECT SUBSTRING('23432534534',3,3) FROM DUAL;  
\#432  
SELECT SUBSTRING('23432534534',-3,3) FROM DUAL;  
\#534

**11、查询字符串长度**  
LENGTH(STR),计算字符串的长度，一个汉字算3个字符，一个数字或字母算一个字符。  
CHAR\_LENGTH(str)=CHARACTER\_LENGTH(str),返回字符串str的长度，长度的单位为字符，一个多字节字符算作一个单字符。

**12、将查询结果列通过逗号连接起来**  
select group\_concat(t.column\_name) from test t;  
**13、查询当前月的第一个自然周、第二个自然周。。。**  
date\_format(date,'%u');\#周一是一周的第一天  
date\_format(date,'%U');\#周日是一周的第一天  
\#取9月的第一个自然周，因为9月1日是周五，所以9月第一个自然周的周一是8月28，所以如果日期是8月28也属于9月第一周  
SELECT DATE\_FORMAT(STR\_TO\_DATE('20170827','%Y%m%d'),'%u') FROM DUAL;  
SELECT DATE\_FORMAT(STR\_TO\_DATE('20170901','%Y%m%d'),'%u') FROM DUAL;  
\#结果是1，表示9月第一周  
SELECT DATE\_FORMAT(STR\_TO\_DATE('20170901','%Y%m%d'),'%u')-DATE\_FORMAT(STR\_TO\_DATE('20170901','%Y%m%d'),'%u')+1 FROM DUAL;  
\#结果是2，表示9月第二周  
第一个时间如果不是固定的，后面可以不加2，算出来的就是根据指定日期的周  
SELECT DATE\_FORMAT(STR\_TO\_DATE('20170901','%Y%m%d'),'%u')-DATE\_FORMAT(STR\_TO\_DATE('20170901','%Y%m%d'),'%u')+2 FROM DUAL;

**14、强制结束正在执行的查询等任务**  
查看正在执行的任务的进程id：show full processlist  
结束指定的进程：kill id  
**15、**  
\#下月1号的1点  
SELECT DATE\_ADD(DATE\_ADD(DATE\_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 1 MONTH),INTERVAL 1 HOUR) FROM DUAL;  
\#下月  
SELECT DATE\_ADD(DATE\_ADD(DATE\_SUB(CURDATE(),INTERVAL DAY(CURDATE())-9 DAY), INTERVAL 1 MONTH),INTERVAL 1 HOUR) FROM DUAL;

**16、insert用法**  
1）insert into table\_name(columnName1,columnName2,columnName3...) values(value1,value2,value3...);  
2）插入数据来源于查询结果  
 INSERT INTO t1(columnName1,columnName2) SELECT field1,field2 FROM t2;  
\#如果每一列都插入数据，则表的列名可以省略  
 INSERT INTO t1 SELECT columnName1,columnName2 FROM t2;  
\#下面这种在查询结果时如果报错，就加一个别名  
 INSERT INTO t1(columnName1,columnName2) SELECT \* FROM(SELECT columnName1,columnName2 FROM t2 JOIN t3) AS alias

**17、update用法**  
单表更新  
update t set t.column1=value1,t.column2=value2  
where t.column3=value3;  
多表关联更新  
1)update t1,t2  
set t1.column1=t2.value1,t1.column2=t2.value2  
where t1.column3=t2.column3  
and ...;  
2)update t1  
inner join t2  
on t1.column3=t2.column3  
set t1.column1=t2.value1,t1.column2=t2.value2  
where t1.column3=t2.column3;

**18、查询表的数据大小**  
1)查询所以用户占用的总大小  
SELECT CONCAT(ROUND(SUM(data\_length/1024/1024),2),'M')  
FROM information\_schema.tables t;  
2)查询指定数据库占用存储空间  
SELECT CONCAT(ROUND(SUM(data\_length/1024/1024),2),'M')  
FROM information\_schema.tables t  
WHERE 1=1  
AND t.TABLE\_SCHEMA='数据库名';  
3)查询指定数据库的指定表占用存储空间  
SELECT CONCAT(ROUND(SUM(data\_length/1024/1024),2),'M')  
FROM information\_schema.tables t  
WHERE 1=1  
AND t.TABLE\_SCHEMA='数据库名'  
AND t.TABLE\_NAME='表名';