目录
展开
1.拼接字段(Concat)
拼接：将值(值可以来源于不同表不同列)联结到一起构成单个值
示例，生成一个格式为name(location)的供应商报表：
mysql> 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.00 sec)
mysql>注意：多数DBMS使用+或者||来实现拼接，MySQL则使用Concat()函数实现
删除右侧多余空格(RTrim()函数)来整理数据：
mysql> select Concat(rtrim(vend_name),'(',rtrim(vend_country),')') as new_data from vendors order by vend_name;
+------------------------+
| new_data |
+------------------------+
| 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)
mysql>MySQL除了支持RTrim()，还支持LTrim()（去掉字符串左边的空格）以及Trim()（去掉字符串左右两边的空格）
2.别名(AS)
一个未命名的列不能用于客户机应用中，因为客户机没有办法应用它。因此使用AS别名来解决这个问题
- 示例：
mysql> select Concat(vend_name,'(',vend_country,')') as vend_title from vendors order by vend_country desc; +------------------------+ | vend_title | +------------------------+ | Anvils R Us(USA) | | LT Supplies(USA) | | ACME(USA) | | Furball Inc.(USA) | | Jouets Et Ours(France) | | Jet Set(England) | +------------------------+ 6 rows in set (0.00 sec)
mysql>
☆导出列 别名有时也称为导出列（derived column），不管称为什么，它们所代表的都是相同的东西。
### 3.执行算数计算
* 示例，列出订单号为20005的所有物品信息：
```sql
mysql> select prod_id,quantity,item_price from orderitems where order_num=20005;
+---------+----------+------------+
| prod_id | quantity | item_price |
+---------+----------+------------+
| ANV01 | 10 | 5.99 |
| ANV02 | 3 | 9.99 |
| TNT2 | 5 | 10.00 |
| FB | 1 | 10.00 |
+---------+----------+------------+
4 rows in set (0.00 sec)
mysql>- 计算每项物品总价（数量x价格）：
mysql> select prod_id,quantity,item_price,quantity*item_price as total_price from orderitems where order_num=20005;
+---------+----------+------------+-------------+
| prod_id | quantity | item_price | total_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.00 sec)
mysql>Mysql算数运算符：
| 操作符 | 说明 |
|---|---|
+ |
加 |
- |
减 |
* |
乘 |
/ |
除 |
注意:圆括号优先级最高
- 如何测试计算：
虽然SELECT通常用来从表中检索数据，但可以省略FROM子句以便简单地访问和处理表达式。例如，SELECT 3*2;将返回6
SELECT Trim(‘abc’);将返回abc，而SELECT Now()利用Now()函数返回当前日期和时间。通过这些例子，可以明白如何根据需要使用SELECT进行试验。
4.函数
- 文本处理函数
Upper()：将文本转换为大写
mysql> select Trim(vend_name),Upper(vend_name) as vend_name_upper from vendors order by vend_name;
+-----------------+-----------------+
| Trim(vend_name) | vend_name_upper |
+-----------------+-----------------+
| ACME | ACME |
| Anvils R Us | ANVILS R US |
| Furball Inc. | FURBALL INC. |
| Jet Set | JET SET |
| Jouets Et Ours | JOUETS ET OURS |
| LT Supplies | LT SUPPLIES |
+-----------------+-----------------+
6 rows in set (0.00 sec)
mysql>- 常用的文本处理函数：
| 函数 | 说明 |
|---|---|
Left() |
返回串左边的字符 |
Length() |
返回串的长度 |
Locate() |
找出串的一个子串 |
Lower() |
将串转换为小写 |
LTrim() |
去掉左边的空格 |
Right() |
返回串右边的字符 |
RTrim() |
去掉右边的空格 |
Soundex() |
返回串的SOUNDEX值 |
Upper() |
将串转换为大写 |
- 常用日期和时间处理函数：
| 函数 | 说明 |
|---|---|
AddDate() |
增加一个日期(天、周等) |
AddTime() |
增加一个时间(时、分等) |
CurDate() |
返回当前日期 |
CurTime() |
返回当前时间 |
Date() |
返回日期时间的日期部分 |
DateDiff() |
计算两个日期时差 |
Date_Add() |
高度灵活的日期运算函数 |
Date_Format() |
返回一个格式化的日期或时间串 |
Day() |
返回一个日期的天数部分 |
DayOfWeek() |
返回一个日期对应的星期几 |
Hour() |
返回一个时间的小时部分 |
Minute() |
返回一个时间的分钟部分 |
Month() |
返回一个日期的月份部分 |
Now() |
返回当前日期和时间 |
Second() |
返回一个时间的秒部分 |
Time() |
返回一个日期时间的时间部分 |
Year() |
返回一个日期的年份部分 |
首选日期格式：yyyy-mm-dd
不管是插入或更新表值还是用WHERE子句过滤，日期格式必须为yyyy-mm-dd，虽然其他日期格式可能也行，但这是首选日期格式，因为它排除了歧义
☆应该总是使用四位数字的年份，支持两位数字的年份：
MySQL处理00-69为2000-2069，处理70-99为1097-1999，虽然他们可能是打算要的年份，但使用完整的4位数字年份更可靠，因为MySQL不必做出任何假定。
Date()函数与Time()函数：
mysql> select * from orders where order_date='2005-09-1';
+-----------+---------------------+---------+
| order_num | order_date | cust_id |
+-----------+---------------------+---------+
| 20005 | 2005-09-01 00:00:00 | 10001 |
+-----------+---------------------+---------+
1 row in set (0.00 sec)
mysql>
使用上述匹配其实并不可靠，因为order_date列的数据类型为datetiem，where之所以能匹配因为时间值为00:00:00，如果时间值为其他值，则上述语句匹配不到，所以如果只想匹配日期，可以用date()函数：
mysql> select * from orders where date(order_date)='2005-09-1';
+-----------+---------------------+---------+
| order_num | order_date | cust_id |
+-----------+---------------------+---------+
| 20005 | 2005-09-01 00:00:00 | 10001 |
+-----------+---------------------+---------+
1 row in set (0.00 sec)
mysql>如果只想要时间则用Time()函数：
mysql> select * from orders where time(order_date)='00:00:00' and cust_id regexp '3$';
+-----------+---------------------+---------+
| order_num | order_date | cust_id |
+-----------+---------------------+---------+
| 20006 | 2005-09-12 00:00:00 | 10003 |
+-----------+---------------------+---------+
1 row in set (0.00 sec)
mysql>- 匹配日期范围
比如，想列出2005年9月份的所有订单
方法一，使用between and操作符：
mysql> select * from orders where date(order_date) between '2005-9-1' and '2005-9-30';
+-----------+---------------------+---------+
| order_num | order_date | cust_id |
+-----------+---------------------+---------+
| 20005 | 2005-09-01 00:00:00 | 10001 |
| 20006 | 2005-09-12 00:00:00 | 10003 |
| 20007 | 2005-09-30 00:00:00 | 10004 |
+-----------+---------------------+---------+
3 rows in set (0.00 sec)
mysql>方法二，不需要知道每个月有多少天或不需要操作闰年2月的方法，使用Year()和Month()函数：
mysql> select * from orders where year(order_date)='2005' and month(order_date)='9';
+-----------+---------------------+---------+
| order_num | order_date | cust_id |
+-----------+---------------------+---------+
| 20005 | 2005-09-01 00:00:00 | 10001 |
| 20006 | 2005-09-12 00:00:00 | 10003 |
| 20007 | 2005-09-30 00:00:00 | 10004 |
+-----------+---------------------+---------+
3 rows in set (0.00 sec)
mysql>- 数值处理函数
数值函数仅处理数值数据，一般主要用于代数、三角、几何运算，所以使用没有日期函数频繁，但数值函数在主要DBMS中最统一。
常用数值处理函数请看官方文档，这里就不一一列出了。
5.数据汇总
- 1)聚集函数
聚集函数：运行在行组上，计算和返回单个值的函数
AVG()函数：通过对表中行数计数并计算特定列值之和的平均值。可以用来返回所有列的平均值，也可是指定列或行
示例：返回products表中所有产品的平均价格
mysql> select avg(prod_price) from products;
+-----------------+
| avg(prod_price) |
+-----------------+
| 16.133571 |
+-----------------+
1 row in set (0.00 sec)
mysql>示例2:返回vend_id=1003的产品平均价格
mysql> select avg(prod_price) as avg_price from products where vend_id=1003;
+-----------+
| avg_price |
+-----------+
| 13.212857 |
+-----------+
1 row in set (0.00 sec)
mysql>返回多个列的平均值(逗号分隔就行)：
mysql> select avg(prod_price) as avg_price,avg(vend_id) as avg_vend from products;
+-----------+-----------+
| avg_price | avg_vend |
+-----------+-----------+
| 16.133571 | 1002.7143 |
+-----------+-----------+
1 row in set (0.00 sec)
mysql>注意：AVG()函数忽略列值为NULL的行。
COUNT()函数：COUNT()函数进行计数。可利用COUNT()确定表中行的数目或符合特定条件的行的数目
两种用法：
☆使用COUNT(*)对表中行的数目进行计数，不管表行中包含的是空值(NULL)还是非空值
☆使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值
示例,返回customers的总行数(5行)：
mysql> select * from customers;
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+
| 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee | ylee@coyote.com |
| 10002 | Mouse House | 333 Fromage Lane | Columbus | OH | 43333 | USA | Jerry Mouse | NULL |
| 10003 | Wascals | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | rabbit@wascally.com |
| 10004 | Yosemite Place | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Y Sam | sam@yosemite.com |
| 10005 | E Fudd | 4545 53rd Street | Chicago | IL | 54545 | USA | E Fudd | NULL |
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+
5 rows in set (0.00 sec)
mysql>返回customer表中行进行计数：
mysql> select count(*) as total_man from customers;
+-----------+
| total_man |
+-----------+
| 5 |
+-----------+
1 row in set (0.00 sec)
mysql>对有电子邮件的客户(cust_emial的值非NULL)进行计数(相当于对cust_email列计数，但不包含NULL)：
mysql> select count(cust_email) as num_cust from customers;
+----------+
| num_cust |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)
mysql>MAX()函数：返回指定列中的最大值。需要指定列名。
示例，返回vend_id列的最大值以及prod_price的最大值：
mysql> select max(vend_id) as max_id,max(prod_price) as max_price from products;
+--------+-----------+
| max_id | max_price |
+--------+-----------+
| 1005 | 55.00 |
+--------+-----------+
1 row in set (0.00 sec)
mysql>返回最后一行：对非数值数据使用MAX()：MAX()函数一般用来找出最大数值或日期值，如果处理的是文本列，则返回的是列中的最后一行:
mysql> select prod_name from products;
+----------------+
| prod_name |
+----------------+
| 1 ton anvil |
| ... |
| Sling |
| TNT (1 stick) |
| TNT (5 sticks) |
+----------------+
14 rows in set (0.00 sec)
mysql> select max(prod_name) as end_line from products;
+----------------+
| end_line |
+----------------+
| TNT (5 sticks) |
+----------------+
1 row in set (0.00 sec)
mysql>☆同样的，MAX()函数忽略NULL值,MIN()函数则与MAX相反
☆同样的，MIN()函数忽略NULL值
SUM()函数：返回指定列值的和
示例，返回order_num为20005的物品总数量、总价格、平均值：
mysql> select sum(quantity) as item_quantity,sum(item_price) as total_price,sum(item_price*quantity)/sum(quantity) as avg_price from orderitems where order_num=20005;
+---------------+-------------+-----------+
| item_quantity | total_price | avg_price |
+---------------+-------------+-----------+
| 19 | 35.98 | 7.887895 |
+---------------+-------------+-----------+
1 row in set (0.00 sec)
mysql>☆同样的，SUN()函数忽略NULL值
- 2)聚集不同值(
DISTINCT)
以上五个聚集函数都可以如下使用：
对所有的行执行计算，指定ALL参数或不给参数(默认就为ALL行为)
对不同的值执行计算，指定DISTINCT参数即可
示例，对vend_id=1003的产品的不同价格取平均值：
mysql> select avg(distinct prod_price) as avg_price from products where vend_id=1003;
+-----------+
| avg_price |
+-----------+
| 15.998000 |
+-----------+
1 row in set (0.00 sec)
mysql>注意：如果指定列名，则DISTINCT只能用于COUNT()，不能用于COUNT(*)。类似的，DISTINCT必须使用列名，不能用于计算或表达式
6.分组数据
GROUP BY：指定对那个列进行排序并分组数据
示例，对products表的所有产品的vend_id进行分组排序：
mysql> select vend_id,count(*) as vend_num from products group by vend_id;
+---------+----------+
| vend_id | vend_num |
+---------+----------+
| 1001 | 3 |
| 1002 | 2 |
| 1003 | 7 |
| 1005 | 2 |
+---------+----------+
4 rows in set (0.00 sec)
mysql> select count(*) as vend_num from products where vend_id=1003;
+----------+
| vend_num |
+----------+
| 7 |
+----------+
1 row in set (0.00 sec)
mysql>因为使用了GROUP BY，就不必指定要计算和估值的每个组了。系统会自动完成。
GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。如果在
GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在
SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。除聚集计算语句外，
SELECT语句中的每个列都必须在GROUP BY子句中给出。
错误示例：
mysql> select vend_name,vend_id,count(*) as vend_num from products group by vend_id;
ERROR 1054 (42S22): Unknown column 'vend_name' in 'field list'
mysql>如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前.
7.过滤分组(HAVING)
WHERE用于过滤行数据，而HAVING则用于过滤分组数据，WHERE没有分组的概念，所以在用了GROUP BY子句后想过滤数据则使用HAVING子句。
不过在以上WHERE用法中，HAVING是可以替代WHERE的，反过来则不能。
示例：
mysql> select prod_name from products having prod_name regexp '^1';
+-------------+
| prod_name |
+-------------+
| 1 ton anvil |
+-------------+
1 row in set (0.00 sec)
mysql> select vend_id,count(*) as vend_num from products group by vend_id having vend_num>2;
+---------+----------+
| vend_id | vend_num |
+---------+----------+
| 1001 | 3 |
| 1003 | 7 |
+---------+----------+
2 rows in set (0.00 sec)
mysql>同时使用where和having：
mysql> select vend_id,count(*) as num_prod from products where prod_price>=10 group by vend_id having num_prod>=2 order by num_prod desc limit 2 offset 0;
+---------+----------+
| vend_id | num_prod |
+---------+----------+
| 1003 | 4 |
| 1005 | 2 |
+---------+----------+
2 rows in set (0.00 sec)
mysql> select distinct vend_id,count(*) as num_prod from products group by vend_id having num_prod>=2 order by num_prod asc;
+---------+----------+
| vend_id | num_prod |
+---------+----------+
| 1002 | 2 |
| 1005 | 2 |
| 1001 | 3 |
| 1003 | 7 |
+---------+----------+
4 rows in set (0.00 sec)
mysql>解析：
where子句首先过滤所有价格大于等于10的行，然后按vend_id分组数据，having过滤分组数据中计数大于等于2的分区，之后按num_prod降序排序
配合聚集函数使用：
mysql> select prod_id,sum(quantity*item_price) as total_price from orderitems group by prod_id having total_price >=50 order by total_price;
+---------+-------------+
| prod_id | total_price |
+---------+-------------+
| JP2000 | 55.00 |
| ANV01 | 59.90 |
| FC | 125.00 |
| TNT2 | 1050.00 |
+---------+-------------+
4 rows in set (0.00 sec)
mysql>8.子查询("()")
在SELECT语句中，子查询总是从内向外处理
示例：
mysql> select order_num from orderitems where prod_id='tnt2';
+-----------+
| order_num |
+-----------+
| 20005 |
| 20007 |
+-----------+
2 rows in set (0.00 sec)
mysql> select cust_id from orders where order_num in (20005,20007);
+---------+
| cust_id |
+---------+
| 10001 |
| 10004 |
+---------+
2 rows in set (0.00 sec)
mysql> select cust_id from orders where order_num in (select order_num from orderitems where prod_id='tnt2');
+---------+
| cust_id |
+---------+
| 10001 |
| 10004 |
+---------+
2 rows in set (0.00 sec)
mysql>多层子查询，现在得到了TNT2的所有客户的ID(10001和10004)，下一步检索这些ID的客户信息：
mysql> 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.00 sec)
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'));
+----------------+--------------+
| cust_name | cust_contact |
+----------------+--------------+
| Coyote Inc. | Y Lee |
| Yosemite Place | Y Sam |
+----------------+--------------+
2 rows in set (0.00 sec)
mysql>☆where子句中使用子查询可以编写功能强大且灵活的SQL语句。
☆于能嵌套的子查询的数目没有限制，不过考虑实际使用中性能的限制，最好不能嵌套太多的子查询。
所有子查询一般与IN操作符结合使用，但也可以用于测试等于(=)、不等于(<>或!=)、大于(>)、小于(<)等逻辑运算符
mysql> select order_num from orderitems where prod_id='tnt2' limit 1;
+-----------+
| order_num |
+-----------+
| 20005 |
+-----------+
1 row in set (0.00 sec)
mysql> select cust_id from orders where order_num != (select order_num from orderitems where prod_id='tnt2' limit 1);
+---------+
| cust_id |
+---------+
| 10001 |
| 10003 |
| 10004 |
| 10005 |
+---------+
mysql>作为计算字段使用子查询
示例:
1.从customer表中检索客户列表。
2.对于检索出的每个客户，统计其在orders表中的订单数目。
mysql> select * from orders where orders.cust_id=10001;
+-----------+---------------------+---------+
| order_num | order_date | cust_id |
+-----------+---------------------+---------+
| 20005 | 2005-09-01 00:00:00 | 10001 |
| 20009 | 2005-10-08 00:00:00 | 10001 |
+-----------+---------------------+---------+
2 rows in set (0.00 sec)
##返回两个产品
mysql> select count(*) from orders where orders.cust_id=10001;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
mysql>
##对于每个用户，执行count(*)产品计数：
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;
+----------------+------------+--------+
| cust_name | cust_state | orders |
+----------------+------------+--------+
| Coyote Inc. | MI | 2 |
| E Fudd | IL | 1 |
| Mouse House | OH | 0 |
| Wascals | IN | 1 |
| Yosemite Place | AZ | 1 |
+----------------+------------+--------+
5 rows in set (0.00 sec)
mysql>分析：
这条SELECT 语句对customers 表中每个客户返回3 列：cust_name、cust_state和orders。orders是一个计算字段，它是由圆括号中的子查询建立的。该子查询对检索出的每个客户执行一次。在此例子中，该子查询执行了5次，因为检索出了5个客户。
8.联结表
如果数据存储在多个表中，使用单条SELECT语句检索出数据，就需要用到联结
重要的是，联结不是物理实体。换句话说，它在实际的数据库表中不存在，它存在于查询的执行当中
*示例：
创建联结:
mysql> select prod_id,vend_name,prod_price from vendors,products where vendors.vend_id=products.vend_id;
+---------+-------------+------------+
| prod_id | vend_name | prod_price |
+---------+-------------+------------+
| ANV01 | Anvils R Us | 5.99 |
| ANV02 | Anvils R Us | 9.99 |
| ANV03 | Anvils R Us | 14.99 |
| FU1 | LT Supplies | 3.42 |
| OL1 | LT Supplies | 8.99 |
| DTNTR | ACME | 13.00 |
| FB | ACME | 10.00 |
| FC | ACME | 2.50 |
| SAFE | ACME | 50.00 |
| SLING | ACME | 4.49 |
| TNT1 | ACME | 2.50 |
| TNT2 | ACME | 10.00 |
| JP1000 | Jet Set | 35.00 |
| JP2000 | Jet Set | 55.00 |
+---------+-------------+------------+
14 rows in set (0.00 sec)
mysql>完全限定列名：在引用的列可能出现二义性时，必须使用完全限定列名（用一个点分隔的表名和列名）。如果引用一个没有用表名限制的具有二义性的列名，MySQL将返回错误：
mysql> select vend_id,prod_id,prod_price from vendors,products where vendors.vend_id=products.vend_id;
ERROR 1052 (23000): Column 'vend_id' in field list is ambiguous
##正确的限定：
mysql> select vendors.vend_id,prod_id,prod_price from vendors,products where vendors.vend_id=products.vend_id;
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1001 | ANV01 | 5.99 |
| 1001 | ANV02 | 9.99 |
| 1001 | ANV03 | 14.99 |
| 1002 | FU1 | 3.42 |
| 1002 | OL1 | 8.99 |
| 1003 | DTNTR | 13.00 |
| 1003 | FB | 10.00 |
| 1003 | FC | 2.50 |
| 1003 | SAFE | 50.00 |
| 1003 | SLING | 4.49 |
| 1003 | TNT1 | 2.50 |
| 1003 | TNT2 | 10.00 |
| 1005 | JP1000 | 35.00 |
| 1005 | JP2000 | 55.00 |
+---------+---------+------------+
14 rows in set (0.00 sec)
mysql>注意： 应该保证所有联结都有WHERE子句，否则MySQL将返回比想要的数据多得多的数据。同理，应该保证WHERE子句的正确性。不正确的过滤条件将导致MySQL返回不正确的数据。
1)内部联结（inner join on）
目前为止所用的联结都为等值联结，基于两个表之间的相等测试。这种联结也称为内部联结，所以也可以用不同的语法来明确指定联结类型
把上面的语句修改如下：
mysql> select prod_id,vend_name,prod_price from vendors inner join products on vendors.vend_id=products.vend_id;
+---------+-------------+------------+
| prod_id | vend_name | prod_price |
+---------+-------------+------------+
| ANV01 | Anvils R Us | 5.99 |
| ANV02 | Anvils R Us | 9.99 |
| ANV03 | Anvils R Us | 14.99 |
| FU1 | LT Supplies | 3.42 |
| OL1 | LT Supplies | 8.99 |
| DTNTR | ACME | 13.00 |
| FB | ACME | 10.00 |
| FC | ACME | 2.50 |
| SAFE | ACME | 50.00 |
| SLING | ACME | 4.49 |
| TNT1 | ACME | 2.50 |
| TNT2 | ACME | 10.00 |
| JP1000 | Jet Set | 35.00 |
| JP2000 | Jet Set | 55.00 |
+---------+-------------+------------+
14 rows in set (0.00 sec)
mysql>可以看到效果是一样的
基本格式为：table1 inner on table2 on xxx=xxx （表1和表2联结，on替代where)
使用那种语法：ANSI SQL规范首选INNER JOIN语法，此外，使用这种语法有时候性能上比WHERE快。
2）联结多个表
SQL对一条SELECT语句中可以联结的表的数量没有限制，规则也基本相同：
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;
+----------------+-------------+------------+----------+
| prod_name | vend_name | prod_price | quantity |
+----------------+-------------+------------+----------+
| .5 ton anvil | Anvils R Us | 5.99 | 10 |
| 1 ton anvil | Anvils R Us | 9.99 | 3 |
| TNT (5 sticks) | ACME | 10.00 | 5 |
| Bird seed | ACME | 10.00 | 1 |
+----------------+-------------+------------+----------+
4 rows in set (0.00 sec)
mysql>性能考虑：MySQL在运行时关联指定的每个表以处理联结。这种处理可能是非常耗费资源的，因此应该仔细，不要联结不必要的表。联结的表越多，性能下降越厉害。
回到第七节的子查询：
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'));
+----------------+--------------+
| cust_name | cust_contact |
+----------------+--------------+
| Coyote Inc. | Y Lee |
| Yosemite Place | Y Sam |
+----------------+--------------+
2 rows in set (0.00 sec)
mysql>之前说过，子查询并不总是执行复杂SELECT操作的最有效的方法，所以我们使用联结查询代替：
mysql> select cust_name,cust_contact from customers,orders,orderitems where customers.cust_id=orders.cust_id and orders.order_num=orderitems.order_num and orderitems.prod_id='tnt2';
+----------------+--------------+
| cust_name | cust_contact |
+----------------+--------------+
| Coyote Inc. | Y Lee |
| Yosemite Place | Y Sam |
+----------------+--------------+
2 rows in set (0.00 sec)
mysql>9.创建高级联结
前面提到过如何给列起别名：
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)
mysql>SQL还允许给表起别名，优点：
1.缩短SQL语句；
2.允许在单挑SELECT语句中多次使用相同的表。
示例：
mysql> select cust_name,cust_contact from customers as a,orders as b,orderitems as c where a.cust_id=b.cust_id and b.order_num=c.order_num and c.prod_id='tnt2';
+----------------+--------------+
| cust_name | cust_contact |
+----------------+--------------+
| Coyote Inc. | Y Lee |
| Yosemite Place | Y Sam |
+----------------+--------------+
2 rows in set (0.00 sec)
mysql>注意：表别名只在查询执行中使用。与列别名不一样，表别名不返回到客户机
使用不同类型的联结
目前为止，我们使用的都为内部联结或等值联结的简单联结。现在来看3种其他联结：自联结、自然联结、外部联结
1）自联结
示例，找出prod_id为DTNTR的供应商生产的其他物品：
mysql> select prod_id,prod_name from products where vend_id=(select vend_id from products where prod_id='DTNTR');
+---------+----------------+
| prod_id | prod_name |
+---------+----------------+
| DTNTR | Detonator |
| FB | Bird seed |
| FC | Carrots |
| SAFE | Safe |
| SLING | Sling |
| TNT1 | TNT (1 stick) |
| TNT2 | TNT (5 sticks) |
+---------+----------------+
7 rows in set (0.00 sec)
mysql>以上使用子查询实现，因为是在同一个表，可以使用自联结实现：
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';
+---------+----------------+
| prod_id | prod_name |
+---------+----------------+
| DTNTR | Detonator |
| FB | Bird seed |
| FC | Carrots |
| SAFE | Safe |
| SLING | Sling |
| TNT1 | TNT (1 stick) |
| TNT2 | TNT (5 sticks) |
+---------+----------------+
7 rows in set (0.00 sec)
mysql>其实自联结是内部联结(inner join on)或者说前面提到过的联结的变相写法，只不过由于检索的都是同一个表，并且需要使用两次，使用as定义表别名排除二义性
2）自然联结
标准的联结返回所有数据，甚至相同的列出现多次，自然联结排除多次出现，使每个列只返回一次。
你只能选择那些唯一的列，一般是通过对表使用通配符(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';
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+
| 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 | 2005-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 | 2005-10-08 00:00:00 | FB | 1 | 10.00 |
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+
2 rows in set (0.00 sec)
mysql>事实上，目前我们建立的每个内部联结都是自然联结，很可能我们永远不会用到不是自然联结的内部联结。
3）外部联结
联结包含那些在相关表中没有关联行的行，称为外部链接。
一个简单的内部联结示例，检索所有客户及其订单：
mysql> select c.cust_id,o.order_num from customers as c inner join orders as o on c.cust_id=o.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
| 10001 | 20005 |
| 10001 | 20009 |
| 10003 | 20006 |
| 10004 | 20007 |
| 10005 | 20008 |
+---------+-----------+
5 rows in set (0.00 sec)
mysql>
外部联结语法类似。检索所有客户，包括哪些没有订单的客户：
mysql> select c.cust_id,o.order_num from customers as c left outer join orders as o on c.cust_id=o.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 c.cust_id,o.order_num from customers as c right outer join orders as o on o.cust_id=c.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 c.cust_id,o.order_num from orders as o right outer join customers as c on o.cust_id=c.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>可以看到，只要把内部联结的inner join on换成left outer join on或者right outer join on就是外部联结了
使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表
RIGHT指出OUTER JOIN右边的表，而LEFT指出OUTER JOIN左边的表
☆左外部联结和右外部联结唯一的差别是所关联的表的顺序不同，换句话说，左外部联结可以通过颠倒FROM或WHERE子句中的表顺序转换为右外部联结，因此两种类型的外部联结可互换使用，使用哪种纯粹根据方便而定。
4）使用带聚集函数的联结(配合聚集函数一起使用)
示例：
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)
mysql>解析：此SELECT语句使用INNER JOIN将customers和orders表互相关联。GROUP BY 子句按客户分组数据， 因此， 函数调用COUNT(orders.order_num)对每个客户的订单计数，将它作为num_ord返回。
和其他联结结合使用：
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;
+----------------+---------+---------+
| 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.00 sec)
mysql>这个例子使用左外部联结来包含所有客户，甚至包含那些没有任何下订单的客户。结果显示也包含了客户Mouse House，它有0个订单。
使用联结的联结条件总结：
- 注意所使用的联结类型。一般我们使用内部联结，但使用外部联结也是有效的。
- 保证使用正确的联结条件，否则将返回不正确的数据。
- 应该总是提供联结条件，否则会得出笛卡儿积。
- 在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前，分别测试每个联结。这将使故障排除更为简单。
10.组合查询(UNION)
多个查询(SELECT)，并将结果作为单个查询结果集返回。这些组合查询通常称为并(union)或复合查询
以下两种基本情况需要用到组合查询：
- 在单个查询中从不同的表返回类似结构的数据；
- 对单个表执行多个查询，按单个查询返回数据。
创建组合查询：
可用UNION操作符来组合数条SQL查询。利用UNION，可给出多条SELECT语句，将它们的结果组合成单个结果集。
示例，检索价格<=5的所有物品，并且还想包括供应商1001和1002生产的所有物品(不考虑价格)
可以用WHERE来完成此工作：
mysql> select vend_id,prod_id,prod_price from products where prod_price<=5 or vend_id in (1001,1002);
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1001 | ANV01 | 5.99 |
| 1001 | ANV02 | 9.99 |
| 1001 | ANV03 | 14.99 |
| 1003 | FC | 2.50 |
| 1002 | FU1 | 3.42 |
| 1002 | OL1 | 8.99 |
| 1003 | SLING | 4.49 |
| 1003 | TNT1 | 2.50 |
+---------+---------+------------+
8 rows in set (0.00 sec)
mysql>使用UNION实现：
mysql> select vend_id,prod_id,prod_price from products where prod_price<=5 union
select vend_id,prod_id,prod_price from products where vend_id in (1001,1002);
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1003 | FC | 2.50 |
| 1002 | FU1 | 3.42 |
| 1003 | SLING | 4.49 |
| 1003 | TNT1 | 2.50 |
| 1001 | ANV01 | 5.99 |
| 1001 | ANV02 | 9.99 |
| 1001 | ANV03 | 14.99 |
| 1002 | OL1 | 8.99 |
+---------+---------+------------+
8 rows in set (0.00 sec)
mysql>UNION规则：
- UNION必须由两条或两条以上的
SELECT语句组成，语句之间用关键字UNION分隔（因此，如果组合4条SELECT语句，将要使用3个UNION关键字） UNION中的每个查询必须包含相同的列、表达式或聚集函数(不过各个列不需要以相同的次序列出)- 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型
包含或取消重复的行
UNION查询默认会取消重复的行(和使用WHERE的效果一样)，如果需要，可以改变它，使用UNION ALL返回所有匹配行：
mysql> select vend_id,prod_id,prod_price from products where prod_price<=5 union all select vend_id,prod_id,prod_price from products where vend_id in (1001,1002);
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1003 | FC | 2.50 |
| 1002 | FU1 | 3.42 |
| 1003 | SLING | 4.49 |
| 1003 | TNT1 | 2.50 |
| 1001 | ANV01 | 5.99 |
| 1001 | ANV02 | 9.99 |
| 1001 | ANV03 | 14.99 |
| 1002 | FU1 | 3.42 |
| 1002 | OL1 | 8.99 |
+---------+---------+------------+
9 rows in set (0.00 sec)
mysql>对组合查询结果排序
select语句的输出用order by子句排序，在用UNION组合查询时，只能使用一条ORDER BY子句，并且只能出现在最后一条SELECT语句之后
mysql> select vend_id,prod_id,prod_price from products where prod_price<=5 union all select vend_id,prod_id,prod_price from products where vend_id in (1001,1002) order by vend_id,prod_price;
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1001 | ANV01 | 5.99 |
| 1001 | ANV02 | 9.99 |
| 1001 | ANV03 | 14.99 |
| 1002 | FU1 | 3.42 |
| 1002 | FU1 | 3.42 |
| 1002 | OL1 | 8.99 |
| 1003 | TNT1 | 2.50 |
| 1003 | FC | 2.50 |
| 1003 | SLING | 4.49 |
+---------+---------+------------+
9 rows in set (0.00 sec)
mysql>
