mysql临时笔记

删除所有查询语句

SELECT concat('KILL ',id,';') FROM information_schema.processlist  WHERE db = 'paedb_main'

获取所有表名

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'mnw-log' and LOCATE('t_api_req',table_name) > 0

获取所有字段名

SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE table_name = 'table_name';

获取所有字段名并合并字符串

SELECT group_concat(COLUMN_NAME) FROM information_schema.COLUMNS WHERE table_name = 'table_name';

表不存在就拷贝table1表结构并创建

create table if not exists 'tableName' like table1;

拷贝表结构和数据

create table t2 select * from t1

只拷贝表结构

create table t2 like t1

create table t2 select * from t1 where 1 = 0

只拷贝表数据

insert into t2 select * from t1

剔除重复数据（参考：http://www.cnblogs.com/nzbbody/p/4470638.html）

delete from t_all_ips where id not in(select minid from (select min(id) as minid from t_all_ips GROUP BY ip)t);

导出数据到txt文件

SELECT ip FROM t_all_ips into outfile '/home/mnw_log/outfile/ips.txt' lines terminated by '\r\n' ;

根据条件判断记录是否存在（使用limit 1提高性能）

select EXISTS(select 1 from table where field1= 'val1' limit 1)

使用explain extended与show warnings来分析sql优化处理

explain extended select count(*) from t_user;

show warnings;

select count(0) AS `count(*)` from `xxdb`.`t_user`

count(1)、count(*)和count(column)区别？

count(*) 统计所有行数

count(column) 统计column不为null的行数

count(1) 同count(*) 

参考：http://stackoverflow.com/questions/3003457/count-vs-countcolumn-name-which-is-more-correct

创建用户并赋权限

create user 'abcd@%' identified by '123456';

grant all privileges on *.* to abcd@'%' identified by '123456';

聚合函数添加筛选条件

select count(f_status > 0 or null) from table  统计f_status>0的记录数【方法一】

select count(if(`f_status` < 0, 1, null))  from table 统计f_status>0的记录数【方法二】

随机生成中文姓名

SELECT CONCAT(SUBSTRING('赵钱孙李周吴郑王冯陈诸卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤滕殷罗毕郝邬安常乐于时傅皮齐康伍余元卜顾孟平黄和穆萧尹姚邵堪汪祁毛禹狄米贝明臧计伏成戴谈宋茅庞熊纪舒屈项祝董粱杜阮蓝闵席季麻强贾路娄危江童颜郭梅盛林刁钟徐邱骆高夏蔡田樊胡凌霍虞万支柯咎管卢莫经房裘干解应宗丁宣贲邓郁单杭洪包诸左石崔吉钮龚',FLOOR(1+190*RAND()),1),SUBSTRING('明国华建文平志伟东海强晓生光林小民永杰军金健一忠洪江福祥中正振勇耀春大宁亮宇兴宝少剑云学仁涛瑞飞鹏安亚泽世汉达卫利胜敏群波成荣新峰刚家龙德庆斌辉良玉俊立浩天宏子松克清长嘉红山贤阳乐锋智青跃元武广思雄锦威启昌铭维义宗英凯鸿森超坚旭政传康继翔栋仲权奇礼楠炜友年震鑫雷兵万星骏伦绍麟雨行才希彦兆贵源有景升惠臣慧开章润高佳虎根远力进泉茂毅富博霖顺信凡豪树和恩向道川彬柏磊敬书鸣芳培全炳基冠晖京欣廷哲保秋君劲轩帆若连勋祖锡吉崇钧田石奕发洲彪钢运伯满庭申湘皓承梓雪孟其潮冰怀鲁裕翰征谦航士尧标洁城寿枫革纯风化逸腾岳银鹤琳显焕来心凤睿勤延凌昊西羽百捷定琦圣佩麒虹如靖日咏会久昕黎桂玮燕可越彤雁孝宪萌颖艺夏桐月瑜沛诚夫声冬奎扬双坤镇楚水铁喜之迪泰方同滨邦先聪朝善非恒晋汝丹为晨乃秀岩辰洋然厚灿卓杨钰兰怡灵淇美琪亦晶舒菁真涵爽雅爱依静棋宜男蔚芝菲露娜珊雯淑曼萍珠诗璇琴素梅玲蕾艳紫珍丽仪梦倩伊茜妍碧芬儿岚婷菊妮媛莲娟一',FLOOR(1+400*RAND()),1),SUBSTRING('明国华建文平志伟东海强晓生光林小民永杰军金健一忠洪江福祥中正振勇耀春大宁亮宇兴宝少剑云学仁涛瑞飞鹏安亚泽世汉达卫利胜敏群波成荣新峰刚家龙德庆斌辉良玉俊立浩天宏子松克清长嘉红山贤阳乐锋智青跃元武广思雄锦威启昌铭维义宗英凯鸿森超坚旭政传康继翔栋仲权奇礼楠炜友年震鑫雷兵万星骏伦绍麟雨行才希彦兆贵源有景升惠臣慧开章润高佳虎根远力进泉茂毅富博霖顺信凡豪树和恩向道川彬柏磊敬书鸣芳培全炳基冠晖京欣廷哲保秋君劲轩帆若连勋祖锡吉崇钧田石奕发洲彪钢运伯满庭申湘皓承梓雪孟其潮冰怀鲁裕翰征谦航士尧标洁城寿枫革纯风化逸腾岳银鹤琳显焕来心凤睿勤延凌昊西羽百捷定琦圣佩麒虹如靖日咏会久昕黎桂玮燕可越彤雁孝宪萌颖艺夏桐月瑜沛诚夫声冬奎扬双坤镇楚水铁喜之迪泰方同滨邦先聪朝善非恒晋汝丹为晨乃秀岩辰洋然厚灿卓杨钰兰怡灵淇美琪亦晶舒菁真涵爽雅爱依静棋宜男蔚芝菲露娜珊雯淑曼萍珠诗璇琴素梅玲蕾艳紫珍丽仪梦倩伊茜妍碧芬儿岚婷菊妮媛莲娟一',FLOOR(1+400*RAND()),1))

分类统计排名前N的筛选

员工表：Employee
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
+----+-------+--------+--------------+

部门表：Department
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+

求每个部门下面工资最高的前三名员工，预期结果是：

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+

方法一（笛卡尔积）：

SELECT t3.Name AS 'Department',t1.Name AS 'Empoloyee',t1.Salary FROM Employee t1,Department t3
WHERE (SELECT COUNT(1) FROM Employee t2 WHERE t1.DepartmentId = t2.`DepartmentId` AND t2.`Salary` >= t1.Salary) <= 3
AND t1.DepartmentId = t3.id
ORDER BY t1.DepartmentId,t1.Salary DESC

方法二（SQL/ORACLE）：

select t2.Id as 'Department',t1.Name as 'Employee',t2.Salary from
(select DepartmentId,Name,Salary,row_number() as 'rn' over (partition by DepartmentId
order by Salary desc) from Employee) t1, Department t2
where t1.rn<3 and t1.DepartmentId = t2.Id;