专业的linux技术站点
Controlled by you.

MySQL高级用法总结(持续更新)- 包含函数、子查询、联结、组查询、数据汇总等

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的所有物品信息:
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_idDTNTR的供应商生产的其他物品:

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>
打赏作者

您的支持将鼓励我们继续创作!

[微信] 扫描二维码打赏

[支付宝] 扫描二维码打赏

正在跳转到PayPal...
转载请注明出处:北慕城南's Blog » MySQL高级用法总结(持续更新)-

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址