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.时间查询 timeType 1-近三年，2-近一年，3-近一周，4-近三天，5-今日 AND date(sentiment_time) BETWEEN CURDATE() - INTERVAL 3 YEAR AND CURDATE() AND date(sentiment_time) BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 YEAR) AND CURDATE() AND date(sentiment_time) BETWEEN DATE_SUB(CURDATE(),INTERVAL 7 DAY) AND CURDATE() AND date(sentiment_time) BETWEEN CURDATE() - INTERVAL 3 DAY AND CURDATE() AND 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.评论区
翻译
搜索
复制
标签：语句,aaa,ca,xxx,mysql,WHERE,id,SELECT,大全 From： https://www.cnblogs.com/muxisc/p/18280340