操作mysql方法总结_Mysql常用技巧汇总

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(),INTERVAL -1 DAY) FROM DUAL;

result: 2017-07-26 2017-07-27

SELECT CURDATE(),INTERVAL -1 MONTH) FROM DUAL;

result: 2017-07-26 2017-08-26

SELECT 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'),'%Y-%M-%d'),'%y-%m-%d'),'%Y-%m-%D') FROM DUAL;

result:2017-07-27 2017-07-27 00:00:002017-July-2717-07-272017-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 150113657715010848001501136553

#时间戳转化为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:25 2017-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),-1) FROM DUAL;

result:a a-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','%u') FROM DUAL;

#结果是1，表示9月第一周

SELECT DATE_FORMAT(STR_TO_DATE('20170901','%u')-DATE_FORMAT(STR_TO_DATE('20170901','%u')+1 FROM DUAL;

#结果是2，表示9月第二周

第一个时间如果不是固定的，后面可以不加2，算出来的就是根据指定日期的周

SELECT DATE_FORMAT(STR_TO_DATE('20170901','%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 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),'M')

FROM information_schema.tables t;

2)查询指定数据库占用存储空间

SELECT CONCAT(ROUND(SUM(data_length/1024/1024),'M')

FROM information_schema.tables t

WHERE 1=1

AND t.TABLE_SCHEMA='数据库名';

3)查询指定数据库的指定表占用存储空间

SELECT CONCAT(ROUND(SUM(data_length/1024/1024),'M')

FROM information_schema.tables t

WHERE 1=1

AND t.TABLE_SCHEMA='数据库名'

AND t.TABLE_NAME='表名';