前言
其實一直感覺自己的MySQL十分弱逼惊来，因為用的Laravel义图，同時也因為自己沒怎么看過MySQL基礎方面的東西栖忠。所以，趁現(xiàn)在有一點時間设凹，就找了這本MySQL-Cookbook來看舰讹。這是O`Reilly的第二版，最新的是第三版闪朱，但是沒有找到資源月匣。
現(xiàn)在將書中我認為作用大的示例語句摘出來，如果對這些語句有疑問奋姿，可以查閱Cookbook锄开。
正文
注意
- 聚類函數(shù)（COUNT等等）會忽略NULL，如果一張表里面總共有7條記錄称诗，而這里面包含3條有字段為NULL的記錄萍悴，那么使用聚類函數(shù)的時候只會計算4條記錄。COUNT()函數(shù)對待空值與其他聚類函數(shù)略有不同粪狼，COUNT(*)計算時會包含空值退腥，而COUNT(column_name)時不會計算NULL任岸。
- 語句分號結(jié)尾再榄！分號結(jié)尾！分號結(jié)尾享潜！或者也可以用“\c”困鸥。
- 當面對在某個表中尋找與另一個表不匹配（即另一個表所缺少）的值的問題時，你應該養(yǎng)成如此的思維習慣剑按，“啊疾就，這是一個LEFT JOIN問題”。
- USING和HAVING艺蝴。USING用于表連接時給定連接條件猬腰，必須是兩個表是通過同名字段連接才能使用USING（書中沒有明確說明必須是這樣）。
SELECT * FROM table1 JOIN table2 ON table1.id = table2.id等于SELECT * FROM table1 JOIN table2 USING(id)猜敢。引入HAVING是因為WHERE不能和統(tǒng)計函數(shù)一起使用姑荷，SELECT customer, SUM(price) FROM order GROUP BY customer HAVING SUM(price)<2000盒延。
第三章：從表中查找數(shù)據(jù)
SELECT * FROM mail
查詢表中所有數(shù)據(jù)
SELECT t, srcuser, srchost, dstuser, dsthost FROM mail
查詢結(jié)果只輸出特定列（這樣可以給結(jié)果排序，并且可以精簡查詢結(jié)果）
SELECT t, srcuser, srchost FROM mail WHERE srchost = 'venus'
特定條件查詢
SELECT * FROM mail WHERE srcuser LIKE 's%'
模糊條件查詢
SELECT * FROM mail WHERE srcuser = 'barb' AND dstuser = 'tricia'
多重條件查詢
SELECT CONCAT(MONTHNAME(t), '', DAYOFMONTH(t), ', ', YEAR(t)), srcuser, size FROM mail
CONCAT鼠冕，串連接
SELECT DATE_FORMAT(t, '%M %e, %Y'), srcuser, size FROM mail
DATE_FORMAT函數(shù)
SELECT '1+1+1' AS 'The expression, 1+1+1 AS 'The result'
AS添寺，別名
SELECT DATE_FORMAT(t, '%M %e, %Y') AS Date_sent, CONCAT(srcuser, '@', srchost) AS Sender, CONCAT(dstuser, '@', dsthost) AS Recipient, size FROM mail
CONCAT和AS的聯(lián)合使用
SELECT t, srcuser, dstuser, size/1024 AS Kilobytes FROM mail WHERE size/1024 > 500
WHERE子句中不能使用別名，應使用原始別名（此例中即應使用size/1024懈费，而不能使用Kilobytes）
SELECT srcuser, srcuser < 'c', size, size > 5000 FROM mail
SELECT DISTINCT srcuser FROM mail
DISTINCT计露，查詢唯一化
SELECT DISTINCT YEAR(t), MONTH(t), DAYOFMONTH(t) FROM mail
對函數(shù)處理后的數(shù)據(jù)進行唯一化
SELECT COUNT(DISTINCT srcuser) FROM mail
COUNT+DISTINCT
SELECT * FROM taxpayer WHERE id IS NOT NULL
NULL值的處理
SELECT name, IF(id IS NULL, 'Unknow', id) AS 'Id' FROM taxpayer
SELECT name, IFNULL(id, 'Unknow') AS 'Id' FROM taxpayer
IFNULL
SELECT * FROM mail WHERE size > 100000 ORDER BY size
ORDER BY
SELECT * FROM mail WHERE dstuser = 'reicia' ORDER BY srcuser, srchost
WHERE+ORDER BY
SELECT * FROM mail WHERE size > 50000 ORDER BY size DESC
ORDER BY DESC
CREATE VIEW view_mail AS SELECT DATE_FORMAT(t, '%M %e, %Y') AS Date_sent, CONCAT(srcuser, '@', srchost) AS Sender, CONCAT(dstuser, '@', dsthost) AS Recipient, size FROM mail
SELECT * FROM view_mail
視圖，CREATE VIEW
SELECT id, name, service, contact_name FROM profile INNER JOIN profile_contact ON id = profile_id
初見INNER JOIN
SELECT * FROM profile_contact WHERE profile_id = (SELECT id FROM profile WHERE name = 'Mort')
WHERE子句中使用子句
SELECT * FROM profile LIMIT 5
初見LIMIT
SELECT * FROM profile ORDER BY birth DESC LIMIT 1
LIMIT放在ORDER BY后面
SELECT * FROM profile LIMIT 4,2
從原始結(jié)果集跳過4條記錄憎乙，取隨后的2條
SELECT * FROM al_winner WHERE wins > 15 ORDER BY wins DESC LIMIT 5
LIMIT的混合使用
SELECT name, wins FROM al_winner WHERE wins >= (SELECT DISTINCT wins FROM al_winner ORDER BY wins DESC, name LIMIT 3,1) ORDER BY wins DESC, name
查詢生日的最后4個票罐，并要求結(jié)果集按生日升序排列
SELECT * FROM (SELECT name, birth FROM profile ORDER BY birth DESC LIMIT 4) AS t ORDER BY birth
選出最新的4個，再將他們升序排列（其實也可以計算出總長度寨闹，升序排列后取出最后4個）
CREATE TABLE mail2 LIKE mail
克隆表
INSERT INTO mail2 SELECT * FROM mail WHERE srcuser = 'barb'
SELECT thing, UPPER(thing), LOWER(thing) FROM limbs
字符串大小寫轉(zhuǎn)換
SELECT * FROM driver_log ORDER BY name, trav_date
ORDER BY
SELECT t, srcuser, FLOOR((size + 1023) / 1024) AS Kilobytes FROM mail WHERE size > 50000 ORDER BY Kilobytes
AS+ORDER BY
SELECT name, jersey_num FROM roster ORDER BY jersey_num + 0
這里的jersey_num是一個數(shù)字胶坠，也可能是一個 寫作了字串的數(shù)字，所以在排序的時候“+ 0”將其變成數(shù)字
SELECT t, CONCAT(srcuser, '@', srchost) AS Sender, size FROM mail WHERE size > 50000 ORDER BY srchost, srcuser
WHERE + ORDER BY
SELECT DAYNAME(date) AS Day, date, description FROM event ORDER BY DAYOFWEEK(date)
按周歷排序繁堡，以Sunday為第一天
SELECT DAYNAME(date), date, description FROM event ORDER BY MOD(DAYOFWEEK(date) + 5, 7)
按周歷排序沈善，以Monday為第一天
SELECT id, MID(id, 4, 5) AS Serial, LEFT(id, 3) AS Category, RIGHT(id, 2) AS Country FROM housewares
函數(shù)：MID + LEFT + RIGHT
SELECT id, LEFT(SUBSTRING(id, 4), CHAR_LENGTH(SUBSTRING(id, 4) - 2)) FROM housewares2
函數(shù)：CHAR_LENGTH
SELECT name, SUBSTRING_INDEX(SUBSTRING_INDEX(name, '.', -3), '.', 1) AS Leftmost, SUBSTRING_INDEX(SUBSTRING_INDEX(name, '.', -2), '.', 1) AS Middle, SUBSTRING_INDEX(name, '.', -1) AS Rightmost FROM hostname
函數(shù)：SUBSTRING_INDEX，網(wǎng)址按域名排序（www.baidu.com椭蹄，不精準）
SELECT name, SUBSTRING_UNDEX(SUBSTRING_INDEX(CONCAT('.', name), '.', -3), '.', 1) AS Leftmost, SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT('.', name), '.', -2), '.', 1) AS Middle, SUBSTRING_INDEX(name, '.', -1) AS Rightmost FROM hostname
網(wǎng)址按域名排序闻牡，較精準
SELECT t, srcuser, dstuser, size FROM mail GROUP BY FIELD (name, 'Henry', 'Suzi', 'Ben')
用戶自定義排序
SELECT TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'cookbook' AND TABLE_NAME = 'states'
從INFORMATION_SHCEMA表中查詢cookbook庫下面的states表一共有多少行
SELECT COUNT(*) FROM states WHERE statehood < '1900-01-01'
20世紀初，美國有多少個州
SELECT COUNT(*) FROM states WHERE statehood BETWEEN '1800-01-01' AND '1899-12-31'
美國有多少個州是19世紀加入聯(lián)邦的
SELECT COUNT(IF(DAYOFWEEK(trav_date) IN (1, 7), 1, NULL)) AS 'Weekend trips', COUNT(IF(DAYOFWEEK(trav_date) IN (1, 7), NULL, 1)) AS 'Weekday trips' FROM driver_logs
計算周末和周中行程的對比
SELECT = MIN(t) AS Earliest, MAX(t) AS Latest, MIN(size) AS Smallest, MAX(size) AS Largest FROM mail
函數(shù)：MIN + MAX + SUM + AVG
SELECT DISTINCT name FROM driver_log ORDER BY name
一共有多少位司機
SELECT DISTINCT HOUR(t) AS Hour FROM mail ORDER BY Hour
函數(shù)+DISTINCT
SET @max = (SELECT MAX(pop) FROM states) SELECT pop AS 'Highest population', name FROM states WHERE pop = @max
查詢?nèi)丝谧疃嗟闹?/p>
SELECT pop AS 'Highest population', name FROM states WHERE pop = (SELECT MAX(pop) FROM states)
查詢?nèi)丝谧疃嗟闹?/p>
SELECT srcuser, srchost, COUNT(srcuser) FROM mail GROUP BY srcuser, srchost
COUNT是對GROUP BY后的子群進行的計算
SELECT srcuser, MAX(size), MAX(t) FROM mail GROUP BY srcuser
MAX都是對GROUP BY后的子群進行的計算
SELECT srcuser, dstuser, MAX(size) FROM mail GROUP BY srcuser, dstuser
查找mail列表中每一對發(fā)送者和接收者之間發(fā)送的最大的信息
SELECT name, trav_date, MAX(miles) AS 'Longest trip' FROM driver_log GROUP BY name
錯誤方式示例：查詢每個司機最長的旅程绳矩，及其發(fā)生的日期罩润。下面是正確方法
CREATE TABLE t SELECT name, MAX(miles) AS miles FROM driver_log GROUP BY name; SELECT d.name, d.trav_date, d.miles AS 'Longest trip' FROM driver_log AS d INNER JOIN t USING (name, miles) ORDER BY name
SELECT trav_date, COUNT(trav_date) FROM driver_log GROUP BY trav_date HAVING COUNT(trav_date) = 1
哪一天僅有一個司機當班
SELECT CHAR_LENGTH(name), COUNT(*) FROM states GROUP BY CHAR_LENGTH(name)
州名稱長度的分布
SELECT * FROM artist, painting
兩個表的全連接，笛卡爾積
SELECT * FROM artist, painting WHERE artist.a_id = painting.a_id
SELECT * FROM artist INNER JOIN painting ON artist.a_id = painting.a_id
SELECT * FROM artist INNER JOIN painting USING(a_id)
上面三句表達的意思是一樣的
SELECT * FROM artist INNER JOIN painting USING(a_id) WHERE painting.state = 'KY'
查詢在肯塔基州購買的畫作翼馆。根據(jù)經(jīng)驗規(guī)則割以，通常使用ON或USING來指定如何連接表，而使用WHERE子句限定選擇哪些已連接的行
SELECT artist.name, painting.title, states.name, painting.price FROM artist INNER JOIN painting INNER JOIN states ON artist.a_id = painting.a_id AND painting.state = states.abbrev
三個表的連接查詢
SELECT artist.name, COUNT(*) AS 'Number of paintings' FROM artist INNER JOIN painting ON artist.a_id = painting.a_id GROUP BY artist.name
對于每個畫家各收藏了多少作品
SELECT artist.name, COUNT(*) AS 'Number of paintings', SUM(painting.price) AS 'Total price', AVG(painting.price) AS 'Average price' FROM artist INNER JOIN painting ON artist.a_id = painting.a_id GROUP BY artist.name
函數(shù)+INNER JOIN
SELECT * FROM artist LEFT JOIN painting ON artist.a_id = painting.a_id
初見外連接
SELECT artist.* FROM artist LEFT JOIN painting ON artist.a_id = painting.a_id WHERE painting.a_id IS NUL
只顯示在artist表中应媚，卻不被painting表所擁有的值
SELECT artist.name, IF(COUNT(painting.a_id) > 0, 'Yes', 'No') AS 'In collection' FROM painting RIGHT JOIN artist ON artist.a_id = painting.a_id GROUP BY artist.name
SELECT p2.title FROM painting AS p1 INNER JOIN painting AS p2 ON p1.a_id = p2.a_id WHERE p1.title = 'The Potato Eaters'
一張表與自身連接
