mysql语句大全-工作中常用整理（欢迎大家在评论区继续补充）
1.NOT EXISTS 和 NOT IN
SELECT COUNT(ca.aaa)
FROM xx ca
WHERE NOT EXISTS(
SELECT label.*
FROM xxx label
WHERE label.aaa = ca.aaa
)
SELECT COUNT(ca.aaa)
FROM xx ca
WHERE ca.aaa NOT IN(
SELECT label.aaa
FROM xxx label
WHERE label.aaa = ca.aaa
)
2.查询表结构
-- 查询指定表的字段结构
select (@i:=@i+1) i,COLUMN_NAME,COLUMN_TYPE,IS_NULLABLE,COLUMN_COMMENT
from information_schema.columns,(SELECT @i:=0) as i where table_schema = 'xxx' #表所在数据库
and table_name = 'xxx' ; #你要查的表
-- 查询所有表结构
select table_name,COLUMN_NAME,COLUMN_TYPE,IS_NULLABLE,COLUMN_COMMENT,IF(columns.COLUMN_KEY='PRI','是',NULL) AS IS_KEY
from information_schema.columns columns where table_schema = 'xxx'; #表所在数据库
-- 查询所有表名
show tables;
-- 查询所有表名和记录总数
SELECT TABLE_NAME,TABLE_COMMENT,TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='xxx';
-- 查询有url字段的表
SELECT table_name, (@i:=@i+1) i,COLUMN_NAME,COLUMN_TYPE,IS_NULLABLE,COLUMN_COMMENT
FROM information_schema.columns,(SELECT @i:=0) as i
WHERE table_schema = 'xxx'
AND (COLUMN_NAME LIKE '%url%' OR COLUMN_NAME LIKE '%path%' OR COLUMN_NAME LIKE '%file%');
3.按照指定字段分组，有着重复字段的多条记录中，取修改时间最新的那条记录
SELECT ca.*
FROM (select aaa, MAX(change_time) as change_time from xxx group by aaa) AS temp
LEFT JOIN xxx ca ON temp.aaa = ca.aaa AND temp.change_time = ca.change_time
4.数据库锁、线程
（1）数据库锁等待超时
java中报错：java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
先查看当前库的所有线程
show full processlist;
然后查看innodb（存储引擎）的事务表innodb_trx，看下里面是否有正在锁定的事务线程
select * from information_schema.innodb_trx;
有一个正在锁定的事务线程 trx_state：RUNNING trx_mysql_thread_id：527491，
看看ID是否在show full processlist里面的sleep线程中：
如果是，就证明这个sleep的线程事务一直没有commit或者rollback被卡住了，我们需要手动kill掉。
最后杀掉该线程
kill 527491;
information_schema数据库 INNODB_LOCKS、INNODB_TRX
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout'
（2）死锁：
Deadlock found when trying to get lock; try restarting transaction
找到引起死锁的语句，然后针对语句进行优化
show engine innodb status;
5.IF函数
SELECT IF(lo.aaa=1,lo.bbb*12,lo.bbb) as rate
WHERE IF(ld.aaa!=0, lo.`bbb`=10, ld.ccc=0)
6.排序
-- DESC 降序时候默认null值排在后面、ASC升序时默认null值排在前面，可使用 IS NULL处理
null值放在最后
ORDER BY sort_num is null, sort_num
7.存放数字的字符串字段排序
SELECT data_value
FROM xxx
ORDER BY data_value + 0 DESC;
SELECT aaa
FROM xxx
GROUP BY aaa
ORDER BY STR_TO_DATE(aaa, '%Y年%m月') DESC;
8.FIELD函数
对查询结果集进行指定顺序排序
ORDER BY field(p.aaa, 'ccc','bbb')
9.FIND_IN_SET函数
查询 某元素 是否存在于 集合类型的字符串
SELECT *
FROM xxx
WHERE FIND_IN_SET('auditJob', job) -- auditJob存放值：job,test
10.case...when
CASE [col_name]
WHEN [value1] THEN [result1]
WHEN [value1] THEN [result1]
ELSE [default]
END
SELECT aaa as 测试,
CASE bbb
WHEN 0 THEN '开发'
WHEN 1 THEN '产品'
ELSE '项目'
END as 运维,
11.查询结果集的序号
SELECT (@i:=@i+1) as 序号
FROM xxx, (select @i:=0) t
12.distinct
去重的统计
SELECT count(DISTINCT(aaa))
from xxx
WHERE `status`=1
13.REPLACE函数
url去掉第一个字符，并把/改成_
UPDATE xxx
SET aaa = REPLACE(SUBSTRING(aaa,2), '/', '_')
UPDATE xxx
SET aaa = REPLACE(aaa,'.png','-short.png');
14.GROUP_CONCAT()函数
分组后数据值的拼接（多个用,隔开，也可以指定分隔符）
SELECT GROUP_CONCAT(bbb)
FROM xxx
GROUP BY aaa
15.TRUNCATE TABLE
truncate删除整表数据（或navicat工具中：右击-截断表）
与delete不同的是：
删除后将重新水平线和索引(id从零开始)
16.设置数据包的大小
数据库对大量数据插入或者更新有限制，可以自己设置
show global variables like 'max_allowed_packet';
set global max_allowed_packet=157286400;
mysql中，in语句中参数个数是不限制的，不过对整段sql语句的长度有了限制max_allowed_packet
17.upper(str)转大写、lower(str)转小写
18.字符串连接concat函数
19.MAX、MIN函数
查询最大id，用MAX(id)
查询最小id，用MIN(id)
20.删除或更新的表和条件用的表如果一样
则条件表需起别名，删除表不能有别名
delete from xxx where id in(
select u.id from xxx as u where u.status = 1
)
UPDATE xxx SET mobile = '110'
WHERE id in(
SELECT temp.id
FROM (
SELECT u.id
FROM xxx AS u
where u.status = 1
) AS temp
);
更新 SET 自己表
UPDATE xxx t1
INNER JOIN (select * from xxx as t where t.grant_num = 1) t2
ON t1.ccc = t2.ccc
SET t1.aaa = t2.bbb;
21.COUNT函数
SELECT COUNT(vo.id)
FROM(
SELECT ca.id
FROM xxx ca
LEFT JOIN xx ld ON ld.user_id = ca.user_id
WHERE ca.status=1 AND ld.id is NULL
GROUP BY ca.aaa
) vo
22.insert into select from
INSERT INTO `xxx`(`aaa`, `bbb`)
SELECT 1, '测试'
FROM `xx`
WHERE status= 0
LIMIT 1;
23.查询重复数据
SELECT GROUP_CONCAT(id), GROUP_CONCAT(mobile)
FROM xxx
GROUP BY mobile HAVING count(mobile)>1;
24.时间查询
近三年： date(sentiment_time) BETWEEN CURDATE() - INTERVAL 3 YEAR AND CURDATE()
近一年： date(sentiment_time) BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 YEAR) AND CURDATE()
近一周： date(sentiment_time) BETWEEN DATE_SUB(CURDATE(),INTERVAL 7 DAY) AND CURDATE()
近三天： date(sentiment_time) BETWEEN CURDATE() - INTERVAL 3 DAY AND CURDATE()
今日： date(sentiment_time) = CURDATE()
25.with as
26.联合查询
union 的查询结果不会有重复项，因为它的内部使用了一次 distinct，
而 union all 的查询结果包含重复项。
SELECT 'all' `bankTypeName`, COUNT(*) total
FROM xxx a
WHERE a.platform = 'xxx'
UNION ALL
SELECT 'bank' `bankTypeName`, COUNT(*) total
FROM xxx a
WHERE a.platform = 'xxx'
AND b.valid=1
注意：参与联合查询的各查询结果的列数必须相同，对应项的数据类型也必须相同
同时查询多张没有关联的表数据
(SELECT UNIT_TYPE, UNICODE,"NAME",'xxx' AS label from xxx)
union all
(SELECT UNIT_TYPE, UNICODE,"NAME",'xxx' AS label from xxx)
union all
(SELECT UNIT_TYPE, UNICODE,"NAME",'xxx' AS label from xxx)
列：相同数量、相似的数据类型、顺序相同。返回第一个select的列名
27.评论区
