MySQL必知必会-上

补充：

mysql> SELECT * FROM table LIMIT 5,10;  // 检索记录行 6-15
//为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1： 
mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.
//如果只给定一个参数，它表示返回最大的记录行数目： 
mysql> SELECT * FROM table LIMIT 5;     //检索前 5 个记录行
//换句话说，LIMIT n 等价于 LIMIT 0,n。

3. 使用MySQL

SHOW COLUMNS FROM from_names;
= DESCRIBE from_names;显示表列
SHOW STATUS;用于显示广泛的服务器状态信息
SHOW CREATE DATABASE;和SHOW CREATE TABLES;分别用来显示创建特定数据库或表的MySQL语句
SHOW GRANTS;用来显示授予用户
SHOW ERRORS;和SHOW WARNING;

4. SELECT语句

SELECT prod_id,prod_name,prod_price FROM products;检索多个列
SELECT * FROM products;检索所有列
SELECT DISTINCT ven_id FROM products;检索不同的行
SELECT prod_name FROM products LIMIT 5;返回结果的前5行
SELECT prod_name FROM products LIMIT 5,5;返回从行5开始的5行

有时候必须使用完全限定的表名

5. 排序检索数据

SELECT prod_id, prod_price, prod_name
FROM products 
ORDER BY prod_price, prod_name;
检索三个列，并按其中两个列对结果进行排序。

SELECT prod_id, prod_price, prod_name
FROM products
ORDER by prod_price DESC
按价格以降序排序产品
注：如果想在多个列上进行降序排序，必须对每个列指定DESC关键字。

SELECT prod_id, prod_price, prod_name
FROM products 
ORDER by prod_price DESC 
LIMIT 1
使用ORDER和LIMIT的组合，可以找到一个列中的最高或最低值。

6. 过滤数据

只检索所需数据需要制定搜索条件(search criteria)，搜索条件也称为过滤条件(filter condition)。

WHERE字句操作符

操作符	说明
<>	不等于
!=	不等于
BETWEEN	在指定的两个值之间

MySQL在匹配时不区分大小写

SELECT prod_name,prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10
范围匹配查找

7. 数据过滤

OR操作符与AND操作符不同，它指示MySQL检索匹配任一条件的行
SQL（像多数语言一样）在处理OR操作符之前，优先处理AND操作符，所以说任何时候使用具有AND和OR操作符的WHERE字句，都应该使用圆括号明确地分组操作符。

SELECT prod_name, prod_price
FROM students
WHERE vend_id IN (1002,1003)
ORDER BY prod_name
IN操作符用来指定条件范围

为什么要使用IN操作符？

• 在使用长的合法选项清单时， IN操作符的语法更清楚且更直观。
• 在使用IN时，计算的次序更容易管理（因为使用的操作符更少）。
• IN操作符一般比OR操作符清单执行更快。
• IN的最大优点是可以包含其他SELECT语句，使得能够更动态地建
  立WHERE子句。

NOT操作符用于否定它之后跟的任何条件

8. 使用通配符进行过滤

SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE 'jet%'
% 表示任何字符出现任意次数(0个，1个或多个)
_ 只匹配单个字符

注意NULL:虽然%通配符可以匹配任何东西，但有个例外，就是不能匹配NULL。

通配符的技巧：

• 不要过度使用通配符
• 在确实需要使用通配符时，除非有绝对必要，否则不要把他们用在搜索模式的开始处。因为这样是最慢的
• 注意通配符的位置

9. 用正则表达式进行搜索

REGEXP后所跟的东西作为正则表达式处理

SELECT prod_name
FROM products
WHERE prod_name REGEXP '[1-5] Ton'
ORDER BY prod_name

LIKE与REGEXP

在LIKE与REGEXP之间有一个重要的差别

SELECT prod_name
FROM products
WHERE prod_name LIKE '1000'
ORDER BY prod_name;

SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000'
ORDER BY prod_name;

如果执行上面两条语句，会发现第一条语句不返回数据，而第二条数据返回一行。为什么呢？
LIKE匹配整个列。如果被匹配的文本在列值中出现， LIKE将不会找到它，相应的行也不被返回（除非使用通配符）。而REGEXP在列值内进行匹配，如果被匹配的文本在列值中出现， REGEXP将会找到它，相应的行将被返回。这是一个非常重要的差别。

mysql> SELECT prod_name
    -> FROM products
    -> WHERE prod_name regexp '\\([0-9] sticks?\\)'
    -> ORDER BY prod_name;
+----------------+
| prod_name      |
+----------------+
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+

• 匹配任何单一字符：[]
• MySQL中使用\\代表转义
• \\. 代表.

匹配多个实例

元字符	说明
*	0个或多个匹配
+	1个或多个匹配（等于{1，}）
?	0个或1个匹配（等于{0,1}）
{n}	指定数目的匹配
{n,}	不少于指定数目的匹配
{n,m}	匹配数目的范围（m不超过255）

定位元字符
| 元字符 | 说明 |
| ——- | —– |
| ^ | 文本的开始 |
| $ | 文本的结尾 |
| [[:<:]] | 词的开始 |
| [[:>:]] | 词的结尾 |

10. 创建计算字段

拼接(concatenate)：将值联结到一起构成单个值

在MySQL的SELECT语句中，可使用Concat()函数来拼接两个列
SELECT Concat(vend_name, '(', vend_country, ')')
FROM vendors
ORDER BY vend_name;

RTrim()：删除右侧多余的空格来整理数据
SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')')
FROM vendors
ORDER BY vend_name;

别名(alias)是一个字段或值的替换名，用AS关键字赋予
SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') AS vend_title
FROM vendors
ORDER BY vend_name;

SELECT prod_id,
       quantity,
       item_price, 
       quantity*item_price AS expanded_price
FROM orderitems
WHERE order_num = 20005;
创建一个新的列expanded_price，来存放计算的值

Concat()拼接串，即把多个串连接起来形成一个较长的串，参数之间用逗号分隔。

11. 使用数据处理函数

常用的文本处理函数：
| 函数 | 说明 |
| ———– | ———— |
| Left() | 返回串左边的字符 |
| Length() | 返回串的长度 |
| Locate() | 找出串的一个子串 |
| Lower() | 将串转换成小写 |
| LTrim() | 去掉串左边的空格 |
| Right() | 返回串右边的字符 |
| RTtim() | 去掉串右边的空格 |
| Soundex() | 返回串的SOUNDEX值 |
| SubString() | 返回子串的字符 |
| Upper() | 将串转换为大写 |

SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。

SELECT cust_name, cust_contact
FROM customers
WHERE Soundex(cust_contact) = Soundex("Y. Lie")
匹配所有发音类似于Y. Lie的联系名

SELECT cust_id, order_num
FROM orders
WHERE Date(order_date) = '2005-09-01'
Date(order_date)指示MySQL仅提取列的日期部分

SELECT cust_id, order_num
FROM orders
WHERE Year(order_date) = 2005 and Month(order_date) = 9

12. 汇总数据(important)

SQL聚集函数
| 函数 | 说明 |
| ——- | ——– |
| AVG() | 返回某列的平均值 |
| COUNT() | 返回某列的行数 |
| MAX() | 返回某列的最大值 |
| MIN() | 返回某列的最小值 |
| SUM() | 返回某列之和 |

COUNT()函数有两种使用方式

• 使用COUNT(*)对表中行的数目进行计数，NULL也计算在内
• 使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值

13. 分组数据

分组允许把数据分为多个逻辑组，以便能够对每个组进行聚集计算。

SELECT vend_id,COUNT(*) AS num_prods
FROM products
GTOUP BY vend_id;

+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
+---------+-----------+
4 rows in set (0.00 sec)

上面的SELECT语句指定了两个列，vend_id包含产品供应商的ID，num_prods为计算字段（用COUNT(*)函数建立）。 GROUP BY子句指示MySQL按vend_id排序并分组数据。这导致对每个vend_id而不是整个表计算num_prods一次。

注意事项：

• GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。
• 如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
• GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
• 除聚集计算语句外， SELECT语句中的每个列都必须在GROUP BY子句中给出。
• 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
• GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

SELECT cust_id,COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;

+---------+--------+
| cust_id | orders |
+---------+--------+
|   10001 |      2 |
+---------+--------+
1 row in set (0.01 sec)

HAVING和WHERE的差别：
WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。

例子：列出具有2个以上，价格为10以上的产品的供应商。

SELECT vend_id,COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >= 2;

+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1003 |         4 |
|    1005 |         2 |
+---------+-----------+
2 rows in set (0.00 sec)

SELECT子句及其顺序
| 子句 | 说明 | 是否必须使用 |
| ——– | ——— | ———– |
| SELECT | 要返回的列或表达式 | 是 |
| FROM | 从中检索数据的表 | 仅在从表选择数据时使用 |
| WHERE | 行级过滤 | 否 |
| GROUP BY | 分组说明 | 仅在按组计算聚集时使用 |
| HAVING | 组级过滤 | 否 |
| ORDER BY | 输出排序顺序 | 否 |
| LIMIT | 要检索的行数 | 否 |

检索总计订单价格大于等于50的订单的订单号和总订单价格，最後按总订单价格排序输出：

mysql> SELECT order_num, SUM(quantity*item_price) AS ordertotal
    -> FROM orderitems
    -> GROUP BY order_num
    -> HAVING SUM(quantity*item_price) >= 50
    -> ORDER BY ordertotal;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
|     20006 |      55.00 |
|     20008 |     125.00 |
|     20005 |     149.87 |
|     20007 |    1000.00 |
+-----------+------------+
4 rows in set (0.00 sec)

14. 使用子查询

SQL允许创建子查询，即嵌套在其他查询中的查询。
相关子查询(correlated)：涉及每部查询的子查询。

在SELECT语句中，子查询总是从内向外处理。

15. 联结表(join)

外键(foreign)：外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。
如果数据存储在多个表中，怎样使用单条SELECT语句检索出数据？
答案是使用联结，联结是一种机制，用来在一条SELECT语句中关联表。

mysql> SELECT vend_name, prod_name, prod_price
    -> FROM vendors, products
    -> WHERE vendors.vend_id = products.vend_id
    -> ORDER BY vend_name, prod_name;
+-------------+----------------+------------+
| vend_name   | prod_name      | prod_price |
+-------------+----------------+------------+
| ACME        | Bird seed      |      10.00 |
| ACME        | Carrots        |       2.50 |
| ACME        | Detonator      |      13.00 |
| ACME        | Safe           |      50.00 |
| ACME        | Sling          |       4.49 |
| ACME        | TNT (1 stick)  |       2.50 |
| ACME        | TNT (5 sticks) |      10.00 |
| Anvils R Us | .5 ton anvil   |       5.99 |
| Anvils R Us | 1 ton anvil    |       9.99 |
| Anvils R Us | 2 ton anvil    |      14.99 |
| Jet Set     | JetPack 1000   |      35.00 |
| Jet Set     | JetPack 2000   |      55.00 |
| LT Supplies | Fuses          |       3.42 |
| LT Supplies | Oil can        |       8.99 |
+-------------+----------------+------------+
14 rows in set (0.00 sec)

笛卡尔积(cartesian product)由没有联结条件的表返回的结果为笛卡尔积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

16. 创建高级联结

使用表别名

mysql> SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') AS
    -> vend_title
    -> FROM vendors
    -> ORDER BY vend_name;
+------------------------+
| vend_title             |
+------------------------+
| ACME(USA)              |
| Anvils R Us(USA)       |
| Furball Inc.(USA)      |
| Jet Set(England)       |
| Jouets Et Ours(France) |
| LT Supplies(USA)       |
+------------------------+
6 rows in set (0.00 sec)

使用不同类型的联结

• 等值联结
• 自联结
• 自然联结
  • 自然联结排除多次出现，使每个列只返回一次
• 外部联结
  • 联结包含了那些在相关表中没有关联行的行。称为外部联结

自联结:

SELECT prod_id, prod_name
FROM products
WHERE vend_id = (SELECT vend_id
                FROM products
                WHERE prod_id = 'DTNTR');
                
使用联结的相同查询：
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';

假如你发现某物品（其ID为DTNTR）存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。此查询要求首先找到生产ID为DTNTR的物品的供应商，然后找出这个供应商生产的其他物品。

自然联结：

自然联结排除多次出现，使每个列只返回一次。

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 LEFT OUTER JOIN orders
    -> ON customers.cust_id = orders.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10002 |      NULL |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+
6 rows in set (0.00 sec)
//下面看一下若如果换成内联结的结果：
mysql> SELECT customers.cust_id, orders.order_num
    -> FROM customers INNER JOIN orders
    -> ON customers.cust_id = orders.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+
5 rows in set (0.00 sec)

检索所有客户即每个客户所下的订单数

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;
+----------------+---------+---------+
| cust_name      | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc.    |   10001 |       2 |
| Wascals        |   10003 |       1 |
| Yosemite Place |   10004 |       1 |
| E Fudd         |   10005 |       1 |
+----------------+---------+---------+
4 rows in set (0.00 sec)

使用联结和联结条件：

• 注意所使用的联结类型。一般而言我们使用内部联结，但使用外部联结也是有效的
• 保证使用正确的联结条件，否则将返回不正确的数据
• 应该总是提供联结条件，否则会得出笛卡尔积
• 在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。

17. 组合查询

组合查询的使用条件：

• 在单个查询中从不同的表返回类似结构的数据
• 对单个表执行多个查询，按单个查询返回数据

UNION规则：

• UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔
• UNION中的每个查询必须包含相同的列、表达式或聚集函数
• 列数据必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型。

注：UNION从查询结果集中自动去除了重复的行，这是UNION的默认行为。如果想返回所有匹配行，可使用UNION ALL

18. 全文本搜索

启用全文本搜索：为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断地重新索引。

CREATE TABLE语句接受FULLTEXT()子句，它给出被索引列的一个逗号分隔的列表

进行全文本搜索

Match()指定被搜索的列，传递给Match()的值必须与FULLTEXT()定义中相同。如果指定多个列，则必须列出他们（而且次序正确）

Against()指定要使用的搜索表达式

mysql> SELECT note_text
    -> FROM productnotes
    -> WHERE Match(note_text) Against('rabbit');
+----------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                            |
+----------------------------------------------------------------------------------------------------------------------+
| Customer complaint: rabbit has been able to detect trap, food apparently less effective now.                         |
| Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait. |
+----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

使用查询扩展

查询扩展用来放宽所返回的全文本搜索结果的范围

SELECT note_text
FROM productnotes
WHERE Math(note_text) Against('anvils' WITH QUERY EXCEPITON);