MySQL一些花样招式

他说，这里似乎很多玩的，有没有数据库语句好玩的呢？然而随手写了几条，我觉得呢看看书再实践实践应该很不错的。

CASE WHEN

条件判断表达式

# 字段的值使用代号，节省内存
mysql> SELECT `name`,`age`,`sex` FROM `admin`;
+----------+-----+-----+
| name     | age | sex |
+----------+-----+-----+
| AlicFeng |  21 |   1 |
| Alice    |  20 |   0 |
+----------+-----+-----+

# 通过CASE WHEN条件判断获取目标值
mysql> SELECT `name`,`age`,CASE `sex` WHEN 1 THEN '男' WHEN 0 THEN '女' ELSE '不公开' END AS `sex` FROM `admin`;
+----------+-----+-----+
| name     | age | sex |
+----------+-----+-----+
| AlicFeng |  21 | 男  |
| Alice    |  20 | 女  |
+----------+-----+-----+

IFNULL

判断是否为空并处理，判断空值的处理，有允许空值的列是会自动移除索引的，因此不推荐使用。知道就好！

# 直接获取字段值，有些为空值
mysql> SELECT `name`,`message` FROM `admin`;
+----------+--------------------------------------------+
| name     | message                                    |
+----------+--------------------------------------------+
| AlicFeng | 价值源于技术，贡献源于分享。               |
| Alice    | NULL                                       |
+----------+--------------------------------------------+

# 查询数据，处理空值
mysql> SELECT `name`,IFNULL(`message`,"这人很懒,没有留下任何东西。") AS `message` FROM `admin`;
+----------+--------------------------------------------+
| name     | message                                    |
+----------+--------------------------------------------+
| AlicFeng | 价值源于技术，贡献源于分享。               |
| Alice    | 这人很懒,没有留下任何东西。                |
+----------+--------------------------------------------+

IF(EXP1,VALUE1,VALUE2)

IF条件判断，EXP表达式成立的话值为VALUE1，否则为VALUE2

# default SELECT
mysql> SELECT `name`,`age` FROM `admin`;
+----------+-----+
| name     | age |
+----------+-----+
| AlicFeng |  21 |
| Alice    |  20 |
+----------+-----+

# 我要根据年龄来判断这个家伙是不是小屁孩
mysql> SELECT `name`,IF(`age`>20,"这个很很黑","小屁孩一个") AS `who` FROM `admin`;
+----------+-----------------+
| name     | who             |
+----------+-----------------+
| AlicFeng | 这个很很黑      |
| Alice    | 小屁孩一个      |
+----------+-----------------+

UNION 以及 UNION ALL

就是合并，这个没什么好玩的。但是注意查询的字段名必须相同，不管来自哪一个表， ALL的话就是不消除相同行。使用是尽量使用括号括起来，不然有LIMIT、ORDER BY会提示错误。

# UNION 
mysql> (SELECT `name` FROM `admin`) UNION (SELECT `name` FROM `user`);
+----------+
| name     |
+----------+
| AlicFeng |
| Alice    |
| 大傻     |
+----------+

# UNION ALL
mysql> (SELECT `name` FROM `admin`) UNION (ALL SELECT `name` FROM `user`);
+----------+
| name     |
+----------+
| AlicFeng |
| Alice    |
| 大傻     |
| AlicFeng |
+----------+

GROUP BY+SUM+COUNT+ORDER BY

这个没什么说的，经常会用到就熟悉了。分组+求和+计数+排序

mysql> SELECT `age`,COUNT(*) AS `number` FROM `admin` GROUP BY `age` ORDER BY `number` DESC;
+-----+--------+
| age | number |
+-----+--------+
|  21 |      2 |
|  20 |      1 |
+-----+--------+

WHERE和HAVING联合使用

说明having的话肯定有ORDER BY，分组后再帅选

mysql> SELECT `age`,COUNT(*) FROM `admin` WHERE `age`>18 GROUP BY `age` HAVING COUNT(*) >=1;
+-----+----------+
| age | COUNT(*) |
+-----+----------+
|  20 |        1 |
|  21 |        2 |
+-----+----------+

FROM

这里说的是FROM子查询。举个例子好了，现在我要查询小学小孩子有两科以及以上不及格的同学的平均分成绩和名字。一步一步来！

# 首先我要查询出有两个科目不及格的同学
mysql> SELECT `name`,COUNT(*) AS `number_class` FROM `user` WHERE `score`<60 GROUP BY `name` HAVING `number_class`>=2;
+----------+--------------+
| name     | number_class |
+----------+--------------+
| AlicFeng |            2 |
| 大傻     |            2 |
+----------+--------------+

# 既然可以查询出名字了，我们只获取名字即可
mysql> SELECT `name` FROM (SELECT `name`,COUNT(*) AS `number_class` FROM `user` WHERE `score`<60 GROUP BY `name` HAVING `number_class`>=2) AS `user_t`;
+----------+
| name     |
+----------+
| AlicFeng |
| 大傻     |
+----------+

# 名字有了，那么就可以获得最后的结果了
mysql> SELECT `name`,AVG(`score`) FROM `user`  WHERE `name` IN (SELECT `name` FROM (SELECT `name`,COUNT(*) AS `number_class` FROM `user` WHERE `score`<60 GROUP BY `name` HAVING `number_class`>=2) AS `user_t`) GROUP BY `name`;
+----------+--------------+
| name     | AVG(`score`) |
+----------+--------------+
| AlicFeng |      59.6667 |
| 大傻     |      61.6667 |
+----------+--------------+

# PS：这里只是举个例子，正确做法根据学号，这里的数据表结构是这样的(冗余、数据类型不严谨)。
mysql> desc user;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | NO   |     | NULL    |                |
| sub   | varchar(10) | NO   |     | NULL    |                |
| score | int(11)     | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

WHERE

WHERE子查询，一句话，内层查询的结果供外层使用。不多话，举个例子！

# 查询一个user表效绩最高的人在另一个表的名言
mysql> SELECT `name`,`message` FROM `admin` WHERE `name`=(SELECT `name` FROM (SELECT `name`,max(`score`) FROM `user`) AS `temp_t`);
+--------+---------+
| name   | message |
+--------+---------+
| 大傻   | 追求    |
+--------+---------+
# PS：可以使用连表查询，还可以分组查找。THIS IS DEMO

EXISTS

简单说明：外层查询的结果拿到内层查询，是否成立。

mysql> SELECT `name`,`message` FROM `admin` WHERE EXISTS(SELECT `name` FROM `user` WHERE `user`.`name`=`admin`.`name`);
+----------+--------------------------------------------+
| name     | message                                    |
+----------+--------------------------------------------+
| AlicFeng | 价值源于技术，贡献源于分享。               |
| 大傻     | 追求                                       |
+----------+--------------------------------------------+

一下操作，我们先打印一下数据表数据，便于观察

mysql> SELECT * FROM `admin`;
+----+----------+-----+-----+--------------------------------------------+
| id | name     | age | sex | message                                    |
+----+----------+-----+-----+--------------------------------------------+
|  1 | AlicFeng |  21 |   1 | 价值源于技术，贡献源于分享。               |
|  2 | Alice    |  20 |   0 | NULL                                       |
|  3 | 大傻     |  21 |   1 | 追求                                       |
+----+----------+-----+-----+--------------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM `user`;
+--------+----------+---------+-------+
| id     | name     | sub     | score |
+--------+----------+---------+-------+
|      1 | 大傻     | chinese |    48 |
|      2 | AlicFeng | math    |    28 |
|      3 | 大傻     | math    |    38 |
|      4 | AlicFeng | english |    98 |
|      5 | AlicFeng | math    |    53 |
|      6 | 大傻     | english |    99 |
|      7 | 世界     | math    |    88 |
+--------+----------+---------+-------+

简单连表查询

在简单的两个表连接查询中，使用WHERE和JOIN ON查询结果是等效的。

# JOIN ON
mysql> SELECT `admin`.`name`,`admin`.`message`,`user`.`score` FROM `admin` JOIN `user` ON `admin`.`name`=`user`.`name`;
+----------+--------------------------------------------+-------+
| name     | message                                    | score |
+----------+--------------------------------------------+-------+
| 大傻     | 追求                                       |    48 |
| AlicFeng | 价值源于技术，贡献源于分享。               |    28 |
| 大傻     | 追求                                       |    38 |
| AlicFeng | 价值源于技术，贡献源于分享。               |    98 |
| AlicFeng | 价值源于技术，贡献源于分享。               |    53 |
| 大傻     | 追求                                       |    99 |
+----------+--------------------------------------------+-------+

# WHERE
mysql> SELECT `admin`.`name`,`admin`.`message`,`user`.`score` FROM `admin`, `user` WHERE `admin`.`name`=`user`.`name`;
+----------+--------------------------------------------+-------+
| name     | message                                    | score |
+----------+--------------------------------------------+-------+
| 大傻     | 追求                                       |    48 |
| AlicFeng | 价值源于技术，贡献源于分享。               |    28 |
| 大傻     | 追求                                       |    38 |
| AlicFeng | 价值源于技术，贡献源于分享。               |    98 |
| AlicFeng | 价值源于技术，贡献源于分享。               |    53 |
| 大傻     | 追求                                       |    99 |
+----------+--------------------------------------------+-------+

左连接，推荐

特点：以左表为准，去右表找数据，如果没有匹配的数据，则以null补空位，输出结果数>=左表原元祖数

mysql> SELECT `admin`.`name`,`admin`.`message` FROM `admin` LEFT JOIN `user` ON `admin`.`name`=`user`.`name`;
+----------+--------------------------------------------+
| name     | message                                    |
+----------+--------------------------------------------+
| AlicFeng | 价值源于技术，贡献源于分享。               |
| AlicFeng | 价值源于技术，贡献源于分享。               |
| AlicFeng | 价值源于技术，贡献源于分享。               |
| Alice    | NULL                                       |
| 大傻     | 追求                                       |
| 大傻     | 追求                                       |
| 大傻     | 追求                                       |
+----------+--------------------------------------------+

右连接

特点：以右表为准，去左表找数据，如果没有匹配的数据，则以null补空位，输出结果数>=右表原元祖数

mysql> SELECT `admin`.`name`,`admin`.`message` FROM `admin` RIGHT JOIN `user` ON `admin`.`name`=`user`.`name`;
+----------+--------------------------------------------+
| name     | message                                    |
+----------+--------------------------------------------+
| 大傻     | 追求                                       |
| AlicFeng | 价值源于技术，贡献源于分享。               |
| 大傻     | 追求                                       |
| AlicFeng | 价值源于技术，贡献源于分享。               |
| AlicFeng | 价值源于技术，贡献源于分享。               |
| 大傻     | 追求                                       |
| NULL     | NULL                                       |
+----------+--------------------------------------------+

内连接

特点：左右连接的交集。其实与WHERE、JOIN ON的查询结果一样。

mysql> SELECT `admin`.`name`,`admin`.`message` FROM `admin` INNER JOIN `user` ON `admin`.`name`=`user`.`name`;
+----------+--------------------------------------------+
| name     | message                                    |
+----------+--------------------------------------------+
| 大傻     | 追求                                       |
| AlicFeng | 价值源于技术，贡献源于分享。               |
| 大傻     | 追求                                       |
| AlicFeng | 价值源于技术，贡献源于分享。               |
| AlicFeng | 价值源于技术，贡献源于分享。               |
| 大傻     | 追求                                       |
+----------+--------------------------------------------+

玩玩效率的查询

玩这个肯定需要海量的数据了，好了～搞一个sheel脚本插入海量的数据。

# 先来设置MySQL的最大连接数
mysql> set global max_connections=100000;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'max_connections';
+-----------------+--------+
| Variable_name   | Value  |
+-----------------+--------+
| max_connections | 100000 |
+-----------------+--------+
1 row in set (0.00 sec)

shell

#!/bin/bash  
date
for i in `seq 1 100000`
do
{
    mysql -usamego -p!@#$%^&123 demo -e "insert into user(name,score,sub)   values('ALicFeng',78,'math');"  
    sleep 0.01
} &
done
wait
date
exit 0

这里的数据还是很少的，最后很多了，执行这个sh时，我眼睛时刻盯着系统温度等信息、手已经放在CTRL+C上面了，O(∩_∩)O哈哈~

待续... ...

最后编辑于：2017.12.08 01:22:28

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 199,175评论 5赞 466
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 83,674评论 2赞 376
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 146,151评论 0赞 328
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 53,597评论 1赞 269
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 62,505评论 5赞 359
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 47,969评论 1赞 275
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,455评论 3赞 390
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,118评论 0赞 254
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,227评论 1赞 294
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,213评论 2赞 317
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,214评论 1赞 328
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 32,928评论 3赞 316
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,512评论 3赞 302
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,616评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 30,848评论 1赞 255
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 42,228评论 2赞 344
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 41,772评论 2赞 339

MySQL一些花样招式

推荐阅读更多精彩内容