正则表达式搜索

使用正则表达式

基本字符匹配

字符串匹配

# 检索prod_name包含文本1000的所有行
MariaDB [fake_order]> SELECT prod_name
    -> FROM products
    -> WHERE prod_name REGEXP '1000'
    -> ORDER BY prod_name;
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
+--------------+
1 row in set (0.109 sec)

# 使用 "."特殊字符匹配
MariaDB [fake_order]> SELECT prod_name
    -> FROM products
    -> WHERE prod_name REGEXP .000'
    -> ORDER BY prod_name;
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.019 sec)

# 使用 LIKE 匹配
MariaDB [fake_order]> SELECT prod_name
    -> FROM products
    -> WHERE prod_name LIKE '1000'
    -> ORDER BY prod_name;
Empty set (0.024 sec)

# LIKE 匹配一个完整的列。如果匹配的文本存在于列的值中，LIKE 不会找到并该行不会返回，如果使用 REGEXP 则会返回该行
# LIKE 使用^和$锚点是可以的

执行OR匹配

为了搜索两个字符串中其中任意一个，使用 "|"

MariaDB [fake_order]> SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000' ORDER BY prod_name;
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.074 sec)

匹配多个字符中的一个

"." 匹配任何单个字符，'[]'括起来的字符集可以用来匹配特定字符，"[123] Ton"是"[1|2|3] Ton"的缩写。

MariaDB [fake_order]> SELECT prod_name
    -> FROM products
    -> WHERE prod_name REGEXP '[123] Ton'
    -> ORDER BY prod_name;
+-------------+
| prod_name   |
+-------------+
| 1 ton anvil |
| 2 ton anvil |
+-------------+
2 rows in set (0.038 sec)

匹配范围

集合可以定义一个或多个字符进行匹配，如数组0～9，可以使用 "-" 来定义范围。也可以 [a-z]匹配任何字母字符。

MariaDB [fake_order]> SELECT prod_name
    -> FROM products
    -> WHERE prod_name REGEXP '[1-5] Ton'
    -> ORDER BY prod_name;
+--------------+
| prod_name    |
+--------------+
| .5 ton anvil |
| 1 ton anvil  |
| 2 ton anvil  |
+--------------+
3 rows in set (0.020 sec)

特殊字符

使用 "\" 来匹配特殊字符，如"\."匹配"."，这个过程称为转义。

MariaDB [fake_order]> SELECT vend_name
    -> FROM vendors
    -> WHERE vend_name REGEXP '\\.'
    -> ORDER BY vend_name;
+--------------+
| vend_name    |
+--------------+
| Furball Inc. |
+--------------+
1 row in set (0.016 sec)

匹配字符类

为了简化工作，可以使用预定义的字符集，即字符类。

• [:alnum:] 任何字母或数字

匹配多个实例

• *：匹配0个或多个
• +：匹配1个或多个
• ？：匹配0个或1个
• {n}：匹配指定的次数
• {n, }：匹配至少n次
• {m, n}：匹配一个范围，m不超过255

锚点

为了匹配指定位置的文本，需要使用锚点

• ^: 文本开始
• $: 文本结尾
• [[:<:]]: 单词开始
• [[:>:]]: 单词结尾

创建计算字段

拼接

Concat()

# Concat() 指定一个或多个值，每个值用逗号隔开，拼接了以下4个元素
# vend_name、空格和开括号的字符串、vend_country、闭括号的字符串

MariaDB [fake_order]> SELECT Concat(vend_name, ' (', vend_country, ')')
    -> FROM vendors
    -> ORDER BY vend_name;
+--------------------------------------------+
| Concat(vend_name, ' (', vend_country, ')') |
+--------------------------------------------+
| ACME (USA)                                 |
| Anvils R Us (USA)                          |
| Furball Inc. (USA)                         |
| Jet Set (England)                          |
| Jouets Et Ours (France)                    |
| LT Supplies (USA)                          |
+--------------------------------------------+
6 rows in set (0.029 sec)

使用别名 AS

MariaDB [fake_order]> SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title FROM vendors ORDER BY vend_title;
+-------------------------+
| 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.077 sec)

执行算数运算

+-*/

MariaDB [fake_order]> SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM orderitems WHERE order_num = 20005;
+---------+----------+------------+----------------+
| prod_id | quantity | item_price | expanded_price |
+---------+----------+------------+----------------+
| ANV01   |       10 |       5.99 |          59.90 |
| ANV02   |        3 |       9.99 |          29.97 |
| TNT2    |        5 |      10.00 |          50.00 |
| FB      |        1 |      10.00 |          10.00 |
+---------+----------+------------+----------------+
4 rows in set (0.104 sec)

操作函数

文本操作函数

• RTrim()
• Upper()
• Sounddex() 匹配发音类似

日期操作函数

• AddDate() 添加到一个日期（日、周等）
• AddTime() 添加到一个时间（时、分等）

不详列了。

MariaDB [fake_order]> SELECT cust_id, order_num
    -> FROM orders
    -> WHERE Date(order_date) = '2011-09-01';
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
+---------+-----------+
1 row in set (0.034 sec)

数值操作函数

• Abs 返回一个数的绝对值
• Cos() 返回一个角度的余弦

聚合函数

• AVG() 返回一列的平均值
• COUNT() 返回对应于一列的函数
• MAX() 返回一列的最大值
• MIN() 返回一列的最小值
• SUM() 返回一列值的总和

# AVG()
MariaDB [fake_order]> SELECT AVG(prod_price) AS avg_price
    -> FROM products;
+-----------+
| avg_price |
+-----------+
| 16.133571 |
+-----------+
1 row in set (0.042 sec)

# COUNT()
MariaDB [fake_order]> SELECT COUNT(*) AS num_cust FROM customers;
+----------+
| num_cust |
+----------+
|        5 |
+----------+
1 row in set (0.010 sec)

# DISTINCT
MariaDB [fake_order]> SELECT AVG(DISTINCT prod_price) AS avg_price
    -> FROM products
    -> WHERE vend_id = 1003;
+-----------+
| avg_price |
+-----------+
| 15.998000 |
+-----------+
1 row in set (0.019 sec)

#组合聚合函数
MariaDB [fake_order]> SELECT COUNT(*) AS nums_items,
    -> MIN(prod_price) AS price_min,
    -> MAX(prod_price) AS price_max,
    -> AVG(prod_price) AS price_avg
    -> FROM products;
+------------+-----------+-----------+-----------+
| nums_items | price_min | price_max | price_avg |
+------------+-----------+-----------+-----------+
|         14 |      2.50 |     55.00 | 16.133571 |
+------------+-----------+-----------+-----------+
1 row in set (0.012 sec)

数据分组

分组（GROUP BY）

MariaDB [fake_order]> SELECT vend_id, COUNT(*) AS num_prods
    -> FROM products
    -> GROUP BY vend_id;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
+---------+-----------+
4 rows in set (0.015 sec)

过滤分组（HAVING）

WHERE 能用的方法 HAVING 都能用，只不过 WHERE 是针对行，而 HAVING 是针对组的。

MariaDB [fake_order]> 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.167 sec)

子查询

通过子查询过滤

多个查询语句合并成一个查询，每个查询都是基于前一个查询的结果。子查询总是从最内层的SELECT语句开始往外处理。

# 想要所有订单包含物品TNT2的订单号
# 1）检索所有包含物品TNT2的订单号
MariaDB [fake_order]> SELECT order_num
    -> FROM orderitems
    -> WHERE prod_id = 'TNT2';
+-----------+
| order_num |
+-----------+
|     20005 |
|     20007 |
+-----------+
2 rows in set (0.040 sec)
# 2）检索订购了上一个步骤中返回的订单的所有客户的客户ID
MariaDB [fake_order]> SELECT cust_id
    -> FROM orders
    -> WHERE order_num IN(20005, 20007);
+---------+
| cust_id |
+---------+
|   10001 |
|   10004 |
+---------+
2 rows in set (0.052 sec)
# 3）检索从上一个步骤中获得得到所有客户ID的客户信息
MariaDB [fake_order]> SELECT cust_name, cust_contact
    -> FROM customers
    -> WHERE cust_id IN (10001, 10004);
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+
2 rows in set (0.020 sec)

# 子查询，注意格式化
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'));

多表连接

创建连接必须包含所有指定的表，并且说明它们是如何关联起来的。

# FROM 后面跟了两个表，而且 vend_id 前面都完全限定列明。
SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;

笛卡尔乘积

没有使用连接条件的表关系的返回结果。返回的行数是第一个表的行数乘以第二个表的行数。

SELECT vend_name, prod_name, prod_price
FROM vendors, products
ORDER BY vend_name, prod_name;

交叉连接

这种返回笛卡尔乘积的连接类型，这就是交叉连接。

内连接

目前为止，之前使用过的连接被称为等值连接——一个基于测试两表相等的连接。这种连接也被称为内连接。

事实上还可以用一种完全不同的语法使用这些连接：显式指定连接的类型

# 同上面的例子返回结果一样
SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id

连接多个表

SQL没有明确限制SELECT语句中表连接的数量。创建连接的基本规则仍然相同。首先列出所有的表，然后定义表关系。

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;

创建高级连接

表别名

SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')')
				AS vend_title
FROM vendors
ORDER BY vend_name;

不同类型的连接

自连接

# 子查询
# 内层 SELECT 语句做了一个简单的索引来返回生产DTNTR产品的vend_id
# 这个 ID 用在 WHERE 子句中查询该供应商生产的其他产品
SELECT prod_id, prod_name
FROM products
WHERE vend_id = (SELECT vend_id
								FROM products
								WHERE prod_id = 'DTNTR');

# 自连接
# 这个查询需要的两个表实际上是相同的表，因此products在 FROM 中出现了两次，尽管完全合法，但任何对products表的引用都会产生歧义。
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*）和其他表使用列的明确子集。

MariaDB [fake_order]> 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';
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+
| cust_id | cust_name   | cust_address   | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email      | order_num | order_date          | prod_id | quantity | item_price |
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+
|   10001 | Coyote Inc. | 200 Maple Lane | Detroit   | MI         | 44444    | USA          | Y Lee        | ylee@coyote.com |     20005 | 2011-09-01 00:00:00 | FB      |        1 |      10.00 |
|   10001 | Coyote Inc. | 200 Maple Lane | Detroit   | MI         | 44444    | USA          | Y Lee        | ylee@coyote.com |     20009 | 2011-10-08 00:00:00 | FB      |        1 |      10.00 |
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+
2 rows in set (0.109 sec)

外连接

许多连接将一个表的行余其他表的行连接起来，然而有时候，你需要包含没有关联关系的行。例如：

计算每个客户有多少订单，包括没有订单的客户。

在这个例子中，与本表相关联的其他表没有关系的行也包含在连接中，这种类型的连接叫做外连接。

# 内连接
MariaDB [fake_order]> 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 |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
|   10001 |     20009 |
+---------+-----------+
5 rows in set (0.069 sec)

# 外连接与使用 OUTER JOIN 指定连接类型，外连接也包含不想关联的行，当使用OUTER JOIN语法时，你必须用 RIGHT 或 LEFT关键字指定包含所有行的表（RIGHT 对应OUTER JOIN 的右侧， LEFT 对应 LEFT JOIN 的左侧。）
# LEFT OUTER JOIN 来选择 FROM 子句中左边的所有行（即customers表）
MariaDB [fake_order]> SELECT customers.cust_id, orders.order_num
    -> FROM customers RIGHT OUTER JOIN orders
    -> ON orders.cust_id = customers.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
|   10001 |     20009 |
+---------+-----------+
5 rows in set (0.025 sec)

MariaDB [fake_order]> SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders ON orders.cust_id = customers.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10002 |      NULL |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+
6 rows in set (0.015 sec)

带聚合函数的连接

MariaDB [fake_order]> 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.099 sec)

# 外连接聚合查询
MariaDB [fake_order]> 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;
+----------------+---------+---------+
| cust_name      | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc.    |   10001 |       2 |
| Mouse House    |   10002 |       0 |
| Wascals        |   10003 |       1 |
| Yosemite Place |   10004 |       1 |
| E Fudd         |   10005 |       1 |
+----------------+---------+---------+
5 rows in set (0.470 sec)

MariaDB [fake_order]> SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS num_ord FROM customers RIGHT  OUTER 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.015 sec)