mysql语句学习（一）

1.EXISTS 和 IN

SELECT COUNT(ca.aaa)
FROM xx ca
WHERE EXISTS(
    SELECT label.*
    FROM xxx label
    WHERE label.aaa = ca.aaa
)

SELECT COUNT(ca.aaa)
FROM xx ca
WHERE ca.aaa 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
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;

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的列名