《MySQL 必知必会》读书总结
这是 《MySQL 必知必会》 的读书总结。也是自己整理的常用操作的参考手册。
使用 MySQL
- 连接到 MySQL
shell>mysql -u root -p Enter password:****** |
- 显示数据库
mysql>SHOW DATABASES; |
- 选择数据库
mysql>USE mytest; |
- 显示数据库中的表
mysql>SHOW TABLES; |
- 显示表列
mysql>SHOW COLUMNS FROM tmall_user; |
mysql>DESCRIBE tmall_user; |
第二种方法与第一种方法效果相同 |
- SHOW 的其他用法
mysql>SHOW STATUS; |
显示服务器的状态信息 |
mysql>SHOW CREATE DATABASE mytest; |
显示创建数据库的语句 |
mysql>SHOW CREATE TABLE tmall_user; |
显示创建表的语句 |
mysql>SHOW GRANTS; |
显示授权用户的安全权限 |
mysql>SHOW ERRORS; |
显示服务器错误 |
mysql>SHOW WARNINGS; |
显示服务器警告信息 |
基本查询（SELECT 语句）
- 查询某列
mysql>SELECT username FROM tmall_user; |
- 查询多列
mysql>SELECT username, password, time FROM tmall_user; |
- 查询所有列
mysql>SELECT * FROM tmall_user; |
- 查询不同行
mysql>SELECT DISTINCT password FROM tmall_user; |
注：DISTINCT 关键字应用于所有列。 |
- 限制结果
mysql>SELECT username FROM tmall_user LIMIT 5 |
返回5行 |
mysql>SELECT username FROM tmall_user LIMIT 5,5 |
返回从第5行开始的5行 |
注：查询结果的第一行为行0，因此，LIMIT 1,1 将检索出第二行 |
新语法：LIMIT 4 OFFSET 3 从行3开始取4行，就像 LIMIT 3,4 一样 |
- 使用完全限定的列名、表名
mysql>SELECT tmall_user.username FROM mytest.tmall_user; |
排序数据（ORDER BY 子句）
- 排列数据
mysql>SELECT username FROM tmall_user ->ORDER BY username; |
对 username 结构列以字母顺序排列数据 |
- 按多个列排序
mysql>SELECT prod_id, prod_price, prod_name FROM products ->ORDER BY prod_peice, prod_name; |
查询三个列，先按照价格排序，然后再按名称排序 |
- 指定排序方向
mysql>SELECT prod_id, prod_price, prod_name FROM products ->ORDER BY prod_price DESC |
默认的排序是升序排序，DESC 关键字指定降序排序（价格由高到低） |
- 多个列排序并指定方向
mysql>SELECT prod_id, prod_price, prod_name FROM products ->ORDER BY prod_price DESC, prod_name; |
以降序排序产品（价格从高到低），然后再对产品名排序。 |
注：DESC 关键字之应用到直接位于其前面的列名，如果想在多个列上进行降序排序，必须对每个列指定 DESC 关键字。 |
- 使用 ORDER BY 和 LIMIT 的组合，找出一个列中最高或最低的值
mysql>SELECT prod_price FROM products ->ORDER BY prod_price DESC ->LIMIT 1; |
查询价格最贵的那个商品 |
查询条件（WHERE 子句）
- 查询满足条件的行
mysql>SELECT prod_name, prod_price FROM products ->WHERE prod_price = 2.50 |
返回 prod_price 值为 2.50 的行 |
- WHERE 子句操作符
= | 等于 |
<> | 不等于 |
!= | 不等于 |
< | 小于 |
<= | 小于等于 |
> | 大于 |
>= | 大于等于 |
BETWEEN | 在指定的两个值之间 |
- 一些操作符的例子
mysql>SELECT prod_name, prod_price FROM products ->WHERE prod_price < 10; |
列出价格小于 10 的所有产品 |
mysql>SELECT vend_id, prod_name FROM products ->WHERE vend_id <> 1003; |
列出不是由供应商1003制造的所有产品 |
mysql>SELECT prod_name, prod_price FROM products ->WHERE prod_price BETWEEN 5 AND 10 |
查询价格在 5 和 10 之间的所有产品 |
mysql>SELECT prod_name FROM products ->WHERE prod_price IS NULL; |
查询价格列值为 NULL 的行。（空值=值为NULL） |
- 组合 WHERE 子句（逻辑操作符）
AND | 与 |
OR | 或 |
- IN 操作符
mysql>SELECT prod_name FROM products ->WHERE vend_id IN (1002, 1003) ->ORDER BY prod_name; |
查询供应商1002和1003制造的所有产品 |
IN WHERE 子句中用来指定要匹配值得清单的关键字，功能与 OR 相当 |
- NOT 操作符
mysql>SELECT prod_name FROM products ->WHERE vend_id NOT (1002, 1003) ->ORDER BY prod_name; |
查询除1002和1003之外的所用供应商制造的产品 |
NOT WHERE 子句中用来否定后跟条件的关键字，MySQL中的 NOT 支持使用 对 IN、BETWEEN 和 EXISTS 子句取反 |
- LIKE 操作符（通配符过滤）
% | 任何字符出现任意次数，不能匹配 NULL |
_ | 任何一个字符 |
mysql>SELECT prod_name FROM products ->WHERE prod_name LIKE 's%e'; | |
查询以s开头以e结尾的产品 | |
函数
- 文本处理函数
Left() | 返回串左边的字符 |
Length() | 返回串的长度 |
Locate() | 找出串的一个子串 |
Ltrim() | 去掉串左边的空格 |
Right() | 返回串右边的字符 |
Rtrim() | 去掉串右边的空格 |
Soundex() | 返回串SOUNDEX值（根据发音比较而不是字母比较） |
SubString() | 返回子串的字符 |
Upper() | 转换为大写 |
mysql>SELECT name FROM tmall_user ->WHERE SOUNDEX(name) = SOUNDEX('雷君') 返回：雷军 | |
- 时间和日期处理函数
AddDate() |
|
AddTime() |
|
CurDate() | 返回当前日期 |
CurTime() | 返回当前时间 |
Date() | 返回日期时间的日期部分 |
DateDiff() | 计算两个日期之差 |
Date_Add() | 高度灵活的日期运算函数 |
Date_Format | 返回一个格式化的日期或时间串 |
Day() | 返回日期的天数部分 |
DayOfWeek() | 返回日期对应的星期数 |
Hour() | 返回时间的小时部分 |
Minute() | 返回时间的分钟部分 |
Month() | 返回日期的月份部分 |
Now() | 返回当前日期和时间 |
Second() | 返回时间的秒数部分 |
Time() | 返回日期时间的时间部分 |
Year() | 返回日期的年份部分 |
mysql>SELECT username FROM tmall_user ->WHERE DATE(time) = '2016-09-21'; | |
使用日期比较过滤查询结果 | |
mysql>SELECT count(id) FROM tmall_user ->WHERE DATE(time) BETWEEN '2016-09-01' AND '2016-09-30'; | |
查询2016年9月注册的用户数量 | |
mysql>SELECT count(id) FROM tmall_user ->WHERE Year(time) = 2016 AND Month(time) = 9; | |
效果与上例相同 | |
- 数值处理函数
Abs() |
|
Cos() |
|
Mod() |
|
Pi() |
|
Rand() |
|
Sin() |
|
Sqrt() |
|
Tan() |
|
汇总数据（聚集函数）
- 聚集函数
AVG() | 返回某列的平均值 |
COUNT() | 返回某列的行数 |
MAX() | 返回某列的最大值 |
MIN() | 返回某列的最小值 |
SUM() | 返回某列值之和 |
- AVG() 函数
mysql>SELECT AVG(prod_price) FROM products; |
返回所有products表中产品的平均价格 |
mysql>SELECT AVG(prod_price) FROM products ->WHERE vend_id = 1003; |
返回特定供应商所提供产品的平均价格 |
AVG() 函数只应用于单个列，忽略列值为NULL的行 |
- COUNT() 函数
mysql>SELECT COUNT(*) FROM customers; |
对所有行进行计数，包括列值为NULL的行 |
mysql>SELECT COUNT(name) FROM tmall_user; |
只对有昵称的行进行计数，忽略列值为NULL的行 |
- MAX()函数和MIN()函数
mysql>SELECT MAX(prod_price) FROM products; |
返回最贵的物品的价格 |
MAX()函数一般用于找出最大的数值或日期值，但也可以返回任意列中的最大值。在用于文本数据时，按照相应顺序排序，返回最后一行。 |
MAX()函数忽略列值为NULL的行 |
MIN()函数与MAX()函数功能相反。 |
- SUM()函数
mysql>SELECT SUM(quantity) FROM orderitems ->WHERE order_num = 20005; |
查询所订购物品的总数（所有quantity之和） |
mysql>SELECT SUM(item_price*quantity) FROM orderitems ->WHERE order_num = 20005; |
查询所订购物品的总金额。 |
- 聚集不同值（DISTINCT）
mysql>SELECT AVG(DISTINCT prod_price) FROM products ->WHERE vend_id = 1003; |
返回特定供应商的产品的平均价格，平均值只考虑各个不同的价格。 |
- 组合聚集函数
mysql>SELECT COUNT(*), MIN(prod_price), MAX(prod_price), ->AVG(prod_price) FROM products; |
单条SELECT语句执行了4个聚集计算，返回4个值 |
分组数据（GROUP BY 子句和 HAVING 子句）
- 创建分组
mysql>SELECT vend_id, COUNT(*) FROM products ->GROUP BY vend_id; |
按vend_id排序并分组数据，COUNT()会对每个 vend_id 分组计算 num_prods 一次而不是对整个表计算。 |
- 过滤分组
HAVING 非常类似于 WHERE。HAVING 支持所有 WHERE 操作符。他们之间的差别是WHERE 过滤行，而HAVING 过滤分组。 另一种理解：WHERE 在数据分组前进行过滤，HAVING 在数据分组后进行过滤。 |
mysql>SELECT cust_id, COUNT(*) FROM orders ->GROUP BY cust_id ->HAVING COUNT(*) >= 2; |
过滤两个以上的订单的分组 |
mysql>SELECT vend_id, COUNT(*) FROM products ->WHERE prod_price >=10 ->GROUP BY vend_id ->HAVING COUNT(*) >= 2; |
列出具有两个以上，价格为10以上的产品的供应商 |
mysql>SELECT order_num, SUM(quantity*item_price) AS ordertotal ->FROM orderitems ->ORDER BY order_num ->HANVING SUM(quantity*item_price) >= 50 ->ORDER BY ordertoal; |
GROUP BY 子句按照订单号分组数据，以便SUM(*)返回总计订单价格。HAVING 子句过滤数据，使得返回总计订单价格>=50的订单。最后用ORDER BY排序输出。 |
- SELECT 子句顺序
SELECT | 要返回的列或表达式 |
FROM | 要查询的表 |
WHERE | 行过滤 |
GROUP BY | 分组 |
HAVING | 分组过滤 |
ORDER BY | 输出排序顺序 |
LIMIT | 行数 |
子查询
- 查询订购物品TNT2的所有客户
mysql>SELECT cust_name, cust_contact FROM customers ->WHERE cust_id IN (SELECT cust_id FROM orders -> WHERE order_num IN(SELECT order_num -> FROM orderitems -> WHERE prod_id = 'TNT2')); |
虽然子查询一般与IN操作符结合使用，但也可以用于测试等于=、不等于<>。 |
- 作为计算字段使用子查询
mysql>SELECT cust_name, cust_state, (SELECT COUNT(*)FROM orders -> WHERE orders.cust_id = customers.cust_id) AS orders ->FROM customers ->ORDER BY cust_name; |
查询customers表中每个客户的订单总数。订单与相应客户ID存储在orders表中 |
联结表
- 创建联结
mysql>SELECT vend_name, prod_name, prod_price ->FROM vendors, products ->WHERE vendors.vend_id = products.vend_id ->ORDER BY vend_name, prod_name; |
这两个表用WHERE子句联结，需要完全限定列名。 |
由没有联结条件的表关系返回的结果为笛卡尔积，检索出的行数=表1的行数×表2的行数。应该保证所有联结都有WHERE子句。 |
- 内部联结（等值联结）
mysql>SELECT vend_name, prod_name, prod_price ->FROM vendors INNER JOIN products ->ON vendors.vend_id = products.vend_id; |
两个表之间的关系以 INNER JOIN 指定，ON 的条件与上例 WHERE 条件相同 |
- 联结多个表（内部联结）
mysql>SELECT prod_name, vend_name, prod_price, quantity ->FROM orderitems, products, vendors ->WHERE products.vend_id = vendors.vend_id -> AND orderitems.prod_id = products.prod_id -> AND order_num = 20005; |
mysql>SELECT cust_name, cust_contact ->FROM customers, orders, orderitems ->WHERE customers.cust_id = orders.cust_id -> AND orderitems.order_num = orders.order_num -> AND prod_id = 'TNT2'; |
使用联结查询订购物品TNT2的所有客户 |
- 使用表别名
mysql>SELECT cust_name, cust_contact ->FROM customers AS c, orders AS o, orderitems AS oi ->WHERE c.cust_id = o.cust_id -> AND oi.order_num = o.order_num -> AND prod_id = 'TNT2'; |
- 自联结
mysql>SELECT prod_id, prod_name FROM products ->WHERE vend_id = (SELECT vend_id FROM products -> WHERE prod_id = 'DTNTR'); |
mysql>SELECT p1.prod_id, p1.prod_name ->FROM products AS p1, products AS p2 ->WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR' |
选择某物品的供应商所生产的其他物品，第一种方法使用子查询，第二种方法使用自联结，推荐用自联结而不用子查询。 |
使用表别名的主要原因是能在单条SELECT语句中不止一次引用相同的表 |
- 自然联结
自然联结排除多次出现，使每个列只返回一次。这一般是通过对第一个表使用SELECT *，对所有其他的表使用明确的子集来完成的。 |
mysql>SELECT c*, o.order_num, o.order_date, oi.prod_id, oi_quantity, ->oi.item_price ->FROM customers AS c, orders AS o, orderitems AS oi ->WHERE c.cust_id = o.cust_id ->AND oi.order_num = o.order_num ->AND prod_id = 'FB'; |
- 外部联结
mysql>SELECT customers.cust_id, orders.order_num ->FROM customers INNER LEFT OUTER JOIN orders ->ON customers.cust_id = orders.cust_id; |
查询所有客户及其订单，包括没有订单的客户, LEFT 指从左边的表选择所有行 RIGHT 指从右边的表选择所有行 |
- 带聚集函数的联结
mysql>SELECT customers.cust_name, customers.cust_id, -> COUNT(orders.order_num) AS num_ord ->FROM customers INNER JOIN orders -> ON customers.cust_id = orders.cust_id ->GROUP BY customers.cust_id; |
检索所有客户及每个客户所下的订单数 |
mysql>SELECT customers.cust_name, customers.cust_id, -> COUNT(orders.order_num) AS num_ord ->FROM customers LEFT OUTER JOIN orders -> ON customers.cust_id = orders.cust_id ->GROUP BY customers.cust_id; |
检索所有客户及每个客户所下的订单数（包括没有下订单的客户） |
- 使用联结的要点
|
组合查询（UNION 操作符）
全文本搜索
插入数据
更新和删除数据
- 更新数据（UPDATE 语句）
不要省略WHERE子句，再使用UPDATE时一定要注意细心。因为稍不注意，就会更新表中所有行。 |
UPDATE customers SET cust_email = 'elmer@fudd.com' WHERE cust_id = 10005; |
更新多个列 |
UPDATE customers SET cust_email = 'The Fudds', cust_email = 'elmer@fudd.com' WHERE cust_id = 10005; |
|
规范
- 对 SQL 关键字使用大写，而对所有列和表名使用小写，这样做使代码更易于阅读和调试。
- 任何时候使用具有 AND 和 OR 操作符的 WHERE 子句，都应该使用圆括号明确地分组操作符。
- 不要过度使用通配符，通配符通常消耗更多的时间。
- 使用聚集函数来汇总数据，比你在自己的软件中计算要快得多，这些函数是高效设计的。
- 用逐渐子查询来建立查询
