MySQL-Cookbook

前言

其实一直感觉自己的MySQL十分弱逼，因为用的Laravel，同时也因为自己没怎么看过MySQL基础方面的东西。所以，趁现在有一点时间，就找了这本MySQL-Cookbook来看。这是O`Reilly的第二版，最新的是第三版，但是没有找到资源。
现在将书中我认为作用大的示例语句摘出来，如果对这些语句有疑问，可以查阅Cookbook。

正文

注意

1. 聚类函数（COUNT等等）会忽略NULL，如果一张表里面总共有7条记录，而这里面包含3条有字段为NULL的记录，那么使用聚类函数的时候只会计算4条记录。COUNT()函数对待空值与其他聚类函数略有不同，COUNT(*)计算时会包含空值，而COUNT(column_name)时不会计算NULL。
2. 语句分号结尾！分号结尾！分号结尾！或者也可以用“\c”。
3. 当面对在某个表中寻找与另一个表不匹配（即另一个表所缺少）的值的问题时，你应该养成如此的思维习惯，“啊，这是一个LEFT JOIN问题”。
4. USING和HAVING。USING用于表连接时给定连接条件，必须是两个表是通过同名字段连接才能使用USING（书中没有明确说明必须是这样）。SELECT * FROM table1 JOIN table2 ON table1.id = table2.id等于SELECT * FROM table1 JOIN table2 USING(id)。引入HAVING是因为WHERE不能和统计函数一起使用，SELECT customer, SUM(price) FROM order GROUP BY customer HAVING SUM(price)<2000。

第三章：从表中查找数据

SELECT * FROM mail
查询表中所有数据

SELECT t, srcuser, srchost, dstuser, dsthost FROM mail
查询结果只输出特定列（这样可以给结果排序，并且可以精简查询结果）

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函数

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的联合使用

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
对函数处理后的数据进行唯一化

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
从原始结果集跳过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个，并要求结果集按生日升序排列

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
字符串大小写转换

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是一个数字，也可能是一个 写作了字串的数字，所以在排序的时候“+ 0”将其变成数字

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
函数：MID + LEFT + RIGHT

SELECT id, LEFT(SUBSTRING(id, 4), CHAR_LENGTH(SUBSTRING(id, 4) - 2)) FROM housewares2
函数：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
函数：SUBSTRING_INDEX，网址按域名排序（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
网址按域名排序，较精准

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世纪加入联邦的

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
函数：MIN + MAX + SUM + AVG

SELECT DISTINCT name FROM driver_log ORDER BY name
一共有多少位司机

SELECT DISTINCT HOUR(t) AS Hour FROM mail ORDER BY Hour
函数+DISTINCT

SET @max = (SELECT MAX(pop) FROM states) SELECT pop AS 'Highest population', name FROM states WHERE pop = @max
查询人口最多的州

SELECT pop AS 'Highest population', name FROM states WHERE pop = (SELECT MAX(pop) FROM states)
查询人口最多的州

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列表中每一对发送者和接收者之间发送的最大的信息

SELECT name, trav_date, MAX(miles) AS 'Longest trip' FROM driver_log GROUP BY name
错误方式示例：查询每个司机最长的旅程，及其发生的日期。下面是正确方法
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'
查询在肯塔基州购买的画作。根据经验规则，通常使用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
函数+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'
一张表与自身连接