MySQL一些花樣招式

他說偏友，這里似乎很多玩的裤翩，有沒有數(shù)據(jù)庫語句好玩的呢芥炭？然而隨手寫了幾條，我覺得呢看看書再實踐實踐應(yīng)該很不錯的羽峰。

  • CASE WHEN

    條件判斷表達式

# 字段的值使用代號趟咆，節(jié)省內(nèi)存
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 | 價值源于技術(shù)，貢獻源于分享惰聂。               |
| Alice    | NULL                                       |
+----------+--------------------------------------------+

# 查詢數(shù)據(jù)疆偿，處理空值
mysql> SELECT `name`,IFNULL(`message`,"這人很懶,沒有留下任何東西。") AS `message` FROM `admin`;
+----------+--------------------------------------------+
| name     | message                                    |
+----------+--------------------------------------------+
| AlicFeng | 價值源于技術(shù)搓幌，貢獻源于分享杆故。               |
| Alice    | 這人很懶,沒有留下任何東西。                |
+----------+--------------------------------------------+

  • IF(EXP1,VALUE1,VALUE2)

    IF條件判斷溉愁，EXP表達式成立的話值為VALUE1处铛，否則為VALUE2

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

# 我要根據(jù)年齡來判斷這個家伙是不是小屁孩
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

    這個沒什么說的，經(jīng)常會用到就熟悉了寝贡。分組+求和+計數(shù)+排序

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

  • WHERE和HAVING聯(lián)合使用

    說明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子查詢。舉個例子好了圃泡，現(xiàn)在我要查詢小學小孩子有兩科以及以上不及格的同學的平均分成績和名字碟案。一步一步來！

# 首先我要查詢出有兩個科目不及格的同學
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 |
| 大傻     |
+----------+

# 名字有了价说，那么就可以獲得最后的結(jié)果了
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：這里只是舉個例子辆亏，正確做法根據(jù)學號，這里的數(shù)據(jù)表結(jié)構(gòu)是這樣的(冗余鳖目、數(shù)據(jù)類型不嚴謹)扮叨。
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子查詢，一句話领迈，內(nèi)層查詢的結(jié)果供外層使用彻磁。不多話，舉個例子狸捅！

# 查詢一個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

    簡單說明：外層查詢的結(jié)果拿到內(nèi)層查詢尘喝，是否成立恍箭。

mysql> SELECT `name`,`message` FROM `admin` WHERE EXISTS(SELECT `name` FROM `user` WHERE `user`.`name`=`admin`.`name`);
+----------+--------------------------------------------+
| name     | message                                    |
+----------+--------------------------------------------+
| AlicFeng | 價值源于技術(shù)，貢獻源于分享瞧省。               |
| 大傻     | 追求                                       |
+----------+--------------------------------------------+

一下操作扯夭，我們先打印一下數(shù)據(jù)表數(shù)據(jù)，便于觀察

mysql> SELECT * FROM `admin`;
+----+----------+-----+-----+--------------------------------------------+
| id | name     | age | sex | message                                    |
+----+----------+-----+-----+--------------------------------------------+
|  1 | AlicFeng |  21 |   1 | 價值源于技術(shù)鞍匾，貢獻源于分享交洗。               |
|  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查詢結(jié)果是等效的橡淑。

# JOIN ON
mysql> SELECT `admin`.`name`,`admin`.`message`,`user`.`score` FROM `admin` JOIN `user` ON `admin`.`name`=`user`.`name`;
+----------+--------------------------------------------+-------+
| name     | message                                    | score |
+----------+--------------------------------------------+-------+
| 大傻     | 追求                                       |    48 |
| AlicFeng | 價值源于技術(shù)构拳，貢獻源于分享。               |    28 |
| 大傻     | 追求                                       |    38 |
| AlicFeng | 價值源于技術(shù)梁棠，貢獻源于分享置森。               |    98 |
| AlicFeng | 價值源于技術(shù)，貢獻源于分享符糊。               |    53 |
| 大傻     | 追求                                       |    99 |
+----------+--------------------------------------------+-------+

# WHERE
mysql> SELECT `admin`.`name`,`admin`.`message`,`user`.`score` FROM `admin`, `user` WHERE `admin`.`name`=`user`.`name`;
+----------+--------------------------------------------+-------+
| name     | message                                    | score |
+----------+--------------------------------------------+-------+
| 大傻     | 追求                                       |    48 |
| AlicFeng | 價值源于技術(shù)凫海，貢獻源于分享。               |    28 |
| 大傻     | 追求                                       |    38 |
| AlicFeng | 價值源于技術(shù)男娄，貢獻源于分享行贪。               |    98 |
| AlicFeng | 價值源于技術(shù)，貢獻源于分享模闲。               |    53 |
| 大傻     | 追求                                       |    99 |
+----------+--------------------------------------------+-------+

  • 左連接建瘫，推薦

    特點：以左表為準，去右表找數(shù)據(jù)尸折，如果沒有匹配的數(shù)據(jù)啰脚，則以null補空位，輸出結(jié)果數(shù)>=左表原元祖數(shù)

mysql> SELECT `admin`.`name`,`admin`.`message` FROM `admin` LEFT JOIN `user` ON `admin`.`name`=`user`.`name`;
+----------+--------------------------------------------+
| name     | message                                    |
+----------+--------------------------------------------+
| AlicFeng | 價值源于技術(shù)实夹，貢獻源于分享橄浓。               |
| AlicFeng | 價值源于技術(shù)晾咪，貢獻源于分享。               |
| AlicFeng | 價值源于技術(shù)贮配，貢獻源于分享谍倦。               |
| Alice    | NULL                                       |
| 大傻     | 追求                                       |
| 大傻     | 追求                                       |
| 大傻     | 追求                                       |
+----------+--------------------------------------------+

  • 右連接

    特點：以右表為準，去左表找數(shù)據(jù)泪勒，如果沒有匹配的數(shù)據(jù)昼蛀，則以null補空位，輸出結(jié)果數(shù)>=右表原元祖數(shù)

mysql> SELECT `admin`.`name`,`admin`.`message` FROM `admin` RIGHT JOIN `user` ON `admin`.`name`=`user`.`name`;
+----------+--------------------------------------------+
| name     | message                                    |
+----------+--------------------------------------------+
| 大傻     | 追求                                       |
| AlicFeng | 價值源于技術(shù)圆存，貢獻源于分享叼旋。               |
| 大傻     | 追求                                       |
| AlicFeng | 價值源于技術(shù)，貢獻源于分享沦辙。               |
| AlicFeng | 價值源于技術(shù)夫植，貢獻源于分享。               |
| 大傻     | 追求                                       |
| NULL     | NULL                                       |
+----------+--------------------------------------------+

  • 內(nèi)連接

    特點：左右連接的交集油讯。其實與WHERE详民、JOIN ON的查詢結(jié)果一樣。

mysql> SELECT `admin`.`name`,`admin`.`message` FROM `admin` INNER JOIN `user` ON `admin`.`name`=`user`.`name`;
+----------+--------------------------------------------+
| name     | message                                    |
+----------+--------------------------------------------+
| 大傻     | 追求                                       |
| AlicFeng | 價值源于技術(shù)陌兑，貢獻源于分享沈跨。               |
| 大傻     | 追求                                       |
| AlicFeng | 價值源于技術(shù)，貢獻源于分享兔综。               |
| AlicFeng | 價值源于技術(shù)饿凛，貢獻源于分享。               |
| 大傻     | 追求                                       |
+----------+--------------------------------------------+

  • 玩玩效率的查詢

    玩這個肯定需要海量的數(shù)據(jù)了软驰，好了～搞一個sheel腳本插入海量的數(shù)據(jù)涧窒。

# 先來設(shè)置MySQL的最大連接數(shù)
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ù)據(jù)還是很少的，最后很多了锭亏，執(zhí)行這個sh時纠吴，我眼睛時刻盯著系統(tǒng)溫度等信息、手已經(jīng)放在CTRL+C上面了贰镣，O(∩_∩)O哈哈~

待續(xù)... ...

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 人面猴
    序言：七十年代末呜象，一起剝皮案震驚了整個濱河市膳凝，隨后出現(xiàn)的幾起案子碑隆，更是在濱河造成了極大的恐慌，老刑警劉巖蹬音，帶你破解...
    沈念sama閱讀 218,386評論 6贊 506
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件上煤，死亡現(xiàn)場離奇詭異，居然都是意外死亡著淆，警方通過查閱死者的電腦和手機劫狠，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 93,142評論 3贊 394
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進店門拴疤，熙熙樓的掌柜王于貴愁眉苦臉地迎上來，“玉大人独泞，你說我怎么就攤上這事呐矾。” “怎么了懦砂？”我有些...
    開封第一講書人閱讀 164,704評論 0贊 353
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵蜒犯，是天一觀的道長。 經(jīng)常有香客問我荞膘，道長罚随，這世上最難降的妖魔是什么？ 我笑而不...
    開封第一講書人閱讀 58,702評論 1贊 294
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任羽资，我火速辦了婚禮淘菩，結(jié)果婚禮上，老公的妹妹穿的比我還像新娘屠升。我一直安慰自己潮改，他們只是感情好，可當我...
    茶點故事閱讀 67,716評論 6贊 392
  • 惡毒庶女頂嫁案：這布局不是一般人想出來的
    文/花漫 我一把揭開白布腹暖。 她就那樣靜靜地躺著进陡，像睡著了一般。 火紅的嫁衣襯著肌膚如雪微服。 梳的紋絲不亂的頭發(fā)上趾疚，一...
    開封第一講書人閱讀 51,573評論 1贊 305
  • 城市分裂傳說
    那天，我揣著相機與錄音以蕴，去河邊找鬼糙麦。 笑死，一個胖子當著我的面吹牛丛肮，可吹牛的內(nèi)容都是我干的赡磅。 我是一名探鬼主播，決...
    沈念sama閱讀 40,314評論 3贊 418
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開眼宝与，長吁一口氣：“原來是場噩夢啊……” “哼焚廊！你這毒婦竟也來了？” 一聲冷哼從身側(cè)響起习劫，我...
    開封第一講書人閱讀 39,230評論 0贊 276
  • 萬榮殺人案實錄
    序言：老撾萬榮一對情侶失蹤咆瘟，失蹤者是張志新（化名）和其女友劉穎，沒想到半個月后诽里，有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體袒餐，經(jīng)...
    沈念sama閱讀 45,680評論 1贊 314
  • ?護林員之死
    正文 獨居荒郊野嶺守林人離奇死亡，尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 37,873評論 3贊 336
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年，在試婚紗的時候發(fā)現(xiàn)自己被綠了灸眼。 大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片卧檐。...
    茶點故事閱讀 39,991評論 1贊 348
  • 活死人
    序言：一個原本活蹦亂跳的男人離奇死亡，死狀恐怖焰宣，靈堂內(nèi)的尸體忽然破棺而出霉囚，到底是詐尸還是另有隱情，我是刑警寧澤匕积，帶...
    沈念sama閱讀 35,706評論 5贊 346
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布佛嬉，位于F島的核電站，受9級特大地震影響闸天，放射性物質(zhì)發(fā)生泄漏暖呕。R本人自食惡果不足惜，卻給世界環(huán)境...
    茶點故事閱讀 41,329評論 3贊 330
  • 男人毒藥：我在死后第九天來索命
    文/蒙蒙 一苞氮、第九天 我趴在偏房一處隱蔽的房頂上張望湾揽。 院中可真熱鬧，春花似錦笼吟、人聲如沸库物。這莊子的主人今日做“春日...
    開封第一講書人閱讀 31,910評論 0贊 22
  • 一樁弒父案贷帮，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽戚揭。三九已至，卻和暖如春撵枢，著一層夾襖步出監(jiān)牢的瞬間民晒，已是汗流浹背。 一陣腳步聲響...
    開封第一講書人閱讀 33,038評論 1贊 270
  • 情欲美人皮
    我被黑心中介騙來泰國打工锄禽， 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留潜必，地道東北人。 一個月前我還...
    沈念sama閱讀 48,158評論 3贊 370
  • 代替公主和親
    正文 我出身青樓沃但，卻偏偏與公主長得像磁滚，于是被迫代替她去往敵國和親。 傳聞我的和親對象是個殘疾皇子宵晚，可洞房花燭夜當晚...
    茶點故事閱讀 44,941評論 2贊 355

推薦閱讀更多精彩內(nèi)容

  • 1. SQL 簡介 SQL 的目標 理想情況下垂攘，數(shù)據(jù)庫語言應(yīng)允許用戶： 建立數(shù)據(jù)庫和關(guān)系結(jié)構(gòu) 完成基本數(shù)據(jù)管理任務(wù)...
    板藍根plank閱讀 2,347評論 0贊 11
  • 關(guān)系型數(shù)據(jù)庫和SQL SQL語言的三個部分DML：Data Manipulation Language，數(shù)據(jù)操縱語...
    Awey閱讀 1,947評論 0贊 13
  • 50個常用的sql語句Student(S#,Sname,Sage,Ssex) 學生表Course(C#,Cname...
    哈哈海閱讀 1,231評論 0贊 7
  • 數(shù)據(jù)庫基礎(chǔ)知識 數(shù)據(jù)庫客戶端MySQL作為數(shù)據(jù)庫服務(wù)器來運行淤刃，任何滿足mysql通信規(guī)范的軟件都可以作為客戶端來連...
    littlexjing閱讀 906評論 0贊 2
  • 昨晚定的鬧鐘是6:42晒他，8:10開始大三的第一堂課，打算起來好好吃個早餐再去教室的钝凶，所以早點起床仪芒，但唁影，可能是...
    六月夏夏A閱讀 1,427評論 2贊 2