这是 《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 子句，都应该使用圆括号明确地分组操作符。
- 不要过度使用通配符，通配符通常消耗更多的时间。
- 使用聚集函数来汇总数据，比你在自己的软件中计算要快得多，这些函数是高效设计的。
- 用逐渐子查询来建立查询