目录
1. 计算字段
计算字段并不实际存在于数据库表中。计算字段是运行时在SELECT语句内创建的。
字段（eld）基本上与列（column）的意思相同，经常互换使用，不过数据库列一般称为列，而术语字段通常用在计算字段的连接上。
重要的是要注意到，只有数据库知道SELECT语句中哪些列是实际的表列，哪些列是计算字段。从客户机（如应用程序）的角度来看，计算字段的数据是以与其他列的数据相同的方式返回的。
客户机与服务器的格式 可在SQL语句内完成的许多转换和格式化工作都可以直接在客户机应用程序内完成。但一般来说，在数据库服务器上完成这些操作比在客户机中完成要快得多，因为DBMS是设计来快速有效地完成这种处理的。
1.1 拼接字段（concat() ）
1.2 使用别名
1.3 执行算术计算
//拼接字段 concat()函数来拼接两个列
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)
//rtrim()函数删除数据右侧多余的空格
mysql> select concat(vend_name,'(',rtrim(vend_country),')') from vendors order by vend_name;
+-----------------------------------------------+
| concat(vend_name,'(',rtrim(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> select concat(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> select * from orderitems;
+-----------+------------+---------+----------+------------+
| order_num | order_item | prod_id | quantity | item_price |
+-----------+------------+---------+----------+------------+
| 20005 | 1 | ANV01 | 10 | 5.99 |
| 20005 | 2 | ANV02 | 3 | 9.99 |
| 20005 | 3 | TNT2 | 5 | 10.00 |
| 20005 | 4 | FB | 1 | 10.00 |
| 20006 | 1 | JP2000 | 1 | 55.00 |
| 20007 | 1 | TNT2 | 100 | 10.00 |
| 20008 | 1 | FC | 50 | 2.50 |
| 20009 | 1 | FB | 1 | 10.00 |
| 20009 | 2 | OL1 | 1 | 8.99 |
| 20009 | 3 | SLING | 1 | 4.49 |
| 20009 | 4 | ANV03 | 1 | 14.99 |
+-----------+------------+---------+----------+------------+
11 rows in set (0.01 sec)
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> 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.01 sec)
2. 使用数据处理函数
2.1 函数
函数没有SQL的可移植性强
能运行在多个系统上的代码称为可移植的（portable）。相对来说，多数SQL语句是可移植的，在SQL实现之间有差异时，这些差异通常不那么难处理。而函数的可移植性却不强。几乎每种主要的DBMS的实现都支持其他实现不支持的函数，而且有时差异还很大。
为了代码的可移植，许多SQL程序员不赞成使用特殊实现的功能。虽然这样做很有好处，但不总是利于应用程序的性能。如果不使用这些函数，编写某些应用程序代码会很艰难。必须利用其他方法来实现DBMS非常有效地完成的工作，如果你决定使用函数，应该保证做好代码注释，以便以后你（或其他人）能确切地知道所编写SQL代码的含义。
2.2 使用函数
大多数SQL实现支持以下类型的函数。
- 用于处理文本串（如删除或填充值，转换值为大写或小写）的文本函数。
- 用于在数值数据上进行算术操作（如返回绝对值，进行代数运算）的数值函数。
- 用于处理日期和时间值并从这些值中提取特定成分（例如，返回两个日期之差，检查日期有效性等）的日期和时间函数。
- 返回DBMS正使用的特殊信息（如返回用户登录信息，检查版本细节）的系统函数。
2.3 文本处理函数
常用的文本处理函数：
- Left() 返回串左边的字符
- Length() 返回串的长度
- Locate() 找出串的一个子串
- Lower() 将串转换为小写
- LTrim() 去掉串左边的空格
- Right() 返回串右边的字符
- RTrim() 去掉串右边的空格
- Soundex() 返回串的SOUNDEX值
- SubString() 返回子串的字符
- Upper() 将串转换为大写
2.4 日期和时间处理函数
- Date() 返回日期时间的日期部分
- Day() 返回一个日期的天数部分
- Month() 返回一个日期的月份部分
- Now() 返回当前日期和时间
- Year() 返回一个日期的年份部分
-
//文本处理函数 mysql> select vend_name,upper(vend_name) as vend_name_upcase from vendors order by vend_name; +----------------+------------------+ | vend_name | vend_name_upcase | +----------------+------------------+ | 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> select * from orders; +-----------+---------------------+---------+ | 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 | | 20008 | 2005-10-03 00:00:00 | 10005 | | 20009 | 2005-10-08 00:00:00 | 10001 | +-----------+---------------------+---------+ 5 rows in set (0.01 sec) //时间日期处理函数 mysql> select cust_id,order_num from orders where order_date='2005-09-01'; +---------+-----------+ | cust_id | order_num | +---------+-----------+ | 10001 | 20005 | +---------+-----------+ 1 row in set (0.00 sec) mysql> select cust_id,order_num from orders where date(order_date)='2005-09-01'; +---------+-----------+ | cust_id | order_num | +---------+-----------+ | 10001 | 20005 | +---------+-----------+ 1 row in set (0.01 sec) mysql> select cust_id,order_num from orders where date(order_date) between '2005-09-01' and '2005-09-30'; +---------+-----------+ | cust_id | order_num | +---------+-----------+ | 10001 | 20005 | | 10003 | 20006 | | 10004 | 20007 | +---------+-----------+ 3 rows in set (0.01 sec) mysql> select cust_id,order_num from orders where year(order_date)=2005 and month(order_date)=9; +---------+-----------+ | cust_id | order_num | +---------+-----------+ | 10001 | 20005 | | 10003 | 20006 | | 10004 | 20007 | +---------+-----------+ 3 rows in set (0.00 sec)
2.5 数值处理函数
常用数值处理函数
- Abs() 返回一个数的绝对值
- Cos() 返回一个角度的余弦
- Exp() 返回一个数的指数值
- Mod() 返回除操作的余数
- Pi() 返回圆周率
- Rand() 返回一个随机数
- Sin() 返回一个角度的正弦
- Sqrt() 返回一个数的平方根
- Tan() 返回一个角度的正切
3. 汇总数据
3.1 聚集函数
聚集函数 运行在行组上，计算和返回单个值的函数
sql 聚集函数
- AVG（）返回某列的平均值
- COUNT（）返回某列的行数
- MAX（）返回某列的最大值
- MIN（）返回某列的最小值
- SUM（）返回某列值之和
COUNT（）函数有两种使用方式。
- 使用COUNT（*）对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。
- 使用COUNT（column）对特定列中具有值的行进行计数，忽略NULL值。
对非数值数据使用MAX（）
虽然MAX（）一般用来找出最大的数值或日期值，但MySQL允许将它用来返回任意列中的最大值，包括返回文本列中的最大值。在用于文本数据时，如果数据按相应的列排序，则MAX（）返回最后一行。
对非数值数据使用MIN（）
MIN（）函数与MAX（）函数类似，MySQL允许将它用来返回任意列中的最小值，包括返回文本列中的最小值。在用于文本数据时，如果数据按相应的列排序，则MIN（）返回最前面的行。
NULL值 MAX（），MIN（）函数忽略列值为NULL的行。
//avg() 函数
mysql> select avg(prod_price) from products;
+-----------------+
| avg(prod_price) |
+-----------------+
| 16.133571 |
+-----------------+
1 row in set (0.01 sec)
mysql> select avg(prod_price) from products where vend_id=1003;
+-----------------+
| avg(prod_price) |
+-----------------+
| 13.212857 |
+-----------------+
1 row in set (0.00 sec)
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.01 sec)
//使用COUNT（*）对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。
mysql> select count(*) as num_cust from customers;
+----------+
| num_cust |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)
//使用COUNT（column）对特定列中具有值的行进行计数，忽略NULL值。
mysql> select count(cust_email) as num_cust from customers;
+----------+
| num_cust |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)
mysql> select max(prod_price) as max_price from products;
+-----------+
| max_price |
+-----------+
| 55.00 |
+-----------+
1 row in set (0.00 sec)
mysql> select max(cust_city) as max from customers;
+---------+
| max |
+---------+
| Phoenix |
+---------+
1 row in set (0.00 sec)
mysql> select sum(quantity) from orderitems where order_num=20005;
+---------------+
| sum(quantity) |
+---------------+
| 19 |
+---------------+
1 row in set (0.00 sec)
mysql> select sum(quantity*item_price) as total_price from orderitems where order_num=20005;
+-------------+
| total_price |
+-------------+
| 149.87 |
+-------------+
1 row in set (0.00 sec)
//聚集不同值 distinct
mysql> select avg(distinct prod_price) as avg_price from products where vend_id=1003;
+-----------+
| avg_price |
+-----------+
| 15.998000 |
+-----------+
1 row in set (0.01 sec)
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> select count(*) as num_items,min(prod_price) as min_price,max(prod_price) as max_price,avg(prod_price) as avg_price from products;
+-----------+-----------+-----------+-----------+
| num_items | min_price | max_price | avg_price |
+-----------+-----------+-----------+-----------+
| 14 | 2.50 | 55.00 | 16.133571 |
+-----------+-----------+-----------+-----------+
1 row in set (0.00 sec)4. 分组数据
4.1 数据分组
SQL聚集函数可用来汇总数据。这使我们能够对行进行计数，计算和与平均数，获得最大和最小值而不用检索所有数据。
分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算。
4.2 创建分组（group by）
在具体使用GROUPBY子句前，需要知道一些重要的规定。
- GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。
- 如果在GROUPBY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
- GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
- 除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出。
- 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
- GROUP BY子句必须田现在WHERE子句之后，ORDER BY子句之前。
使用ROLLUP 使用WITH ROLLUP关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值。
//group by 指示MySQL按vend_id排序并分组数据，然后对每个组进行聚集
mysql> 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.00 sec)
mysql> select vend_id,count(*) as num_prods from products group by vend_id with rollup;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| 1001 | 3 |
| 1002 | 2 |
| 1003 | 7 |
| 1005 | 2 |
| NULL | 14 |
+---------+-----------+
5 rows in set (0.00 sec)4.3 过滤分组（having）
HAVING和WHERE的差别 WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。这是一个重要的区别，WHERE排除的行不包括在分组中。这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。
//过滤基于分组聚集值而不是特定行值
mysql> 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)
mysql> select cust_id,count(*) as orders from orders group by cust_id having count(*)=1;
+---------+--------+
| cust_id | orders |
+---------+--------+
| 10003 | 1 |
| 10004 | 1 |
| 10005 | 1 |
+---------+--------+
3 rows in set (0.00 sec)
mysql> 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)
mysql> select vend_id,count(*) as num_prods from products group by vend_id having count(*)>=2;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| 1001 | 3 |
| 1002 | 2 |
| 1003 | 7 |
| 1005 | 2 |
+---------+-----------+
4 rows in set (0.00 sec)4.4 分组和排序
order by和group by区别
| order by | group by |
| 排序产生的输出 | 分组行。但输出可能不是分组的顺序 |
| 任意列都可以使用（甚至非选择的列也可以使用） | 只可能使用选择列或表达式列，而且必须使用每个选择列表达式 |
| 不一定需要 | 如果与聚集函数一起使用列（或表达式），则必须使用 |
ORDER BY 一般在使用GROUPBY子句时，应该也给出ORDER BY子句，这是保证数据正确排序的唯一方法。千万不要仅依赖GROUP BY排序数据。
mysql> select order_num,sum(quantity*item_price) as ordertotal from orderitems group by order_num having sum(quantity*item_price)>=50;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
| 20005 | 149.87 |
| 20006 | 55.00 |
| 20007 | 1000.00 |
| 20008 | 125.00 |
+-----------+------------+
4 rows in set (0.00 sec)
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)4.5 select 子句顺序
| 子句 | 说明 | 是否必须使用 |
|---|---|---|
| select | 要返回的列或表达式 | 是 |
| from | 从中检索数据的表 | 仅在从表选择数据时使用 |
| where | 行级过滤 | 否 |
| group by | 分组说明 | 仅在按组计算聚集时使用 |
| having | 组级过滤 | 否 |
| order by | 输出排序顺序 | 否 |
| limit | 要检索的行数 | 否 |
5. 使用子查询
6. 联结表
6.1 联结
SOL最强大的功能之一就是能在数据检索查询的执行中联结（join）表。联结是利用SQL的SELECT能执行的最重要的操作。
外键（foreign key）外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。
关系数据可以有效地存储和方便地处理。因此，关系数据库的可伸缩性远比非关系数据库要好。
可伸缩性（scale）能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称之为可伸缩性好（scale well）。
为什么要使用联结
正如所述，分解数据为多个表能更有效地存储，更方便地处理，并且具有更大的可伸缩性。但这些好处是有代价的。
如果数据存储在多个表中，怎样用单条SELECT语句检索出数据？答案是使用联结。简单地说，联结是一种机制，用来在一条SELECT语句中关联表，因此称之为联结。使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。
维护引用完整性 重要的是，要理解联结不是物理实体。换句话说，它在实际的数据库表中不存在，联结由MySQL根据需要建立，它存在于查询的执行当中。
在使用关系表时，仅在关系列中插入合法的数据非常重要。回到这里的例子，如果在products表中插入拥有非法供应商ID
（即没有在vendors表中出现）的供应商生产的产品，则这些产品是不可访问的，因为它们没有关联到某个供应商。
为防止这种情况发生，可指示MySQL只允许在products表的供应商ID列中出现合法值（即出现在vendors表中的供应商）。
这就是维护引用完整性，它是通过在表的定义中指定主键和外健来实现的。
6.2 创建联结
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.01 sec)
//笛卡尔积
mysql> select vend_name,prod_name,prod_price from vendors,products order by vend_name,prod_name;
+----------------+----------------+------------+
| vend_name | prod_name | prod_price |
+----------------+----------------+------------+
| ACME | .5 ton anvil | 5.99 |
| ACME | 1 ton anvil | 9.99 |
| ACME | 2 ton anvil | 14.99 |
| ACME | Bird seed | 10.00 |
| ACME | Carrots | 2.50 |
| ACME | Detonator | 13.00 |
| ACME | Fuses | 3.42 |
| ACME | JetPack 1000 | 35.00 |
| ACME | JetPack 2000 | 55.00 |
| ACME | Oil can | 8.99 |
| 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 |
| Anvils R Us | Bird seed | 10.00 |
| Anvils R Us | Carrots | 2.50 |
| Anvils R Us | Detonator | 13.00 |
| Anvils R Us | Fuses | 3.42 |
| Anvils R Us | JetPack 1000 | 35.00 |
| Anvils R Us | JetPack 2000 | 55.00 |
| Anvils R Us | Oil can | 8.99 |
| Anvils R Us | Safe | 50.00 |
| Anvils R Us | Sling | 4.49 |
| Anvils R Us | TNT (1 stick) | 2.50 |
| Anvils R Us | TNT (5 sticks) | 10.00 |
| Furball Inc. | .5 ton anvil | 5.99 |
| Furball Inc. | 1 ton anvil | 9.99 |
| Furball Inc. | 2 ton anvil | 14.99 |
| Furball Inc. | Bird seed | 10.00 |
| Furball Inc. | Carrots | 2.50 |
| Furball Inc. | Detonator | 13.00 |
| Furball Inc. | Fuses | 3.42 |
| Furball Inc. | JetPack 1000 | 35.00 |
| Furball Inc. | JetPack 2000 | 55.00 |
| Furball Inc. | Oil can | 8.99 |
| Furball Inc. | Safe | 50.00 |
| Furball Inc. | Sling | 4.49 |
| Furball Inc. | TNT (1 stick) | 2.50 |
| Furball Inc. | TNT (5 sticks) | 10.00 |
| Jet Set | .5 ton anvil | 5.99 |
| Jet Set | 1 ton anvil | 9.99 |
| Jet Set | 2 ton anvil | 14.99 |
| Jet Set | Bird seed | 10.00 |
| Jet Set | Carrots | 2.50 |
| Jet Set | Detonator | 13.00 |
| Jet Set | Fuses | 3.42 |
| Jet Set | JetPack 1000 | 35.00 |
| Jet Set | JetPack 2000 | 55.00 |
| Jet Set | Oil can | 8.99 |
| Jet Set | Safe | 50.00 |
| Jet Set | Sling | 4.49 |
| Jet Set | TNT (1 stick) | 2.50 |
| Jet Set | TNT (5 sticks) | 10.00 |
| Jouets Et Ours | .5 ton anvil | 5.99 |
| Jouets Et Ours | 1 ton anvil | 9.99 |
| Jouets Et Ours | 2 ton anvil | 14.99 |
| Jouets Et Ours | Bird seed | 10.00 |
| Jouets Et Ours | Carrots | 2.50 |
| Jouets Et Ours | Detonator | 13.00 |
| Jouets Et Ours | Fuses | 3.42 |
| Jouets Et Ours | JetPack 1000 | 35.00 |
| Jouets Et Ours | JetPack 2000 | 55.00 |
| Jouets Et Ours | Oil can | 8.99 |
| Jouets Et Ours | Safe | 50.00 |
| Jouets Et Ours | Sling | 4.49 |
| Jouets Et Ours | TNT (1 stick) | 2.50 |
| Jouets Et Ours | TNT (5 sticks) | 10.00 |
| LT Supplies | .5 ton anvil | 5.99 |
| LT Supplies | 1 ton anvil | 9.99 |
| LT Supplies | 2 ton anvil | 14.99 |
| LT Supplies | Bird seed | 10.00 |
| LT Supplies | Carrots | 2.50 |
| LT Supplies | Detonator | 13.00 |
| LT Supplies | Fuses | 3.42 |
| LT Supplies | JetPack 1000 | 35.00 |
| LT Supplies | JetPack 2000 | 55.00 |
| LT Supplies | Oil can | 8.99 |
| LT Supplies | Safe | 50.00 |
| LT Supplies | Sling | 4.49 |
| LT Supplies | TNT (1 stick) | 2.50 |
| LT Supplies | TNT (5 sticks) | 10.00 |
+----------------+----------------+------------+
84 rows in set (0.01 sec)
笛卡尔积 由没有联结条件的表关系返回的结果为笛卡尔积，检索出的行的数目是第一个表中的行数乘以第二个表中的行数。
6.3 内部联结（等值联结）
mysql> select vend_name,prod_name,prod_price from vendors inner join products on vendors.vend_id=products.vend_id;
+-------------+----------------+------------+
| vend_name | prod_name | prod_price |
+-------------+----------------+------------+
| Anvils R Us | .5 ton anvil | 5.99 |
| Anvils R Us | 1 ton anvil | 9.99 |
| Anvils R Us | 2 ton anvil | 14.99 |
| LT Supplies | Fuses | 3.42 |
| LT Supplies | Oil can | 8.99 |
| ACME | Detonator | 13.00 |
| ACME | Bird seed | 10.00 |
| ACME | Carrots | 2.50 |
| ACME | Safe | 50.00 |
| ACME | Sling | 4.49 |
| ACME | TNT (1 stick) | 2.50 |
| ACME | TNT (5 sticks) | 10.00 |
| Jet Set | JetPack 1000 | 35.00 |
| Jet Set | JetPack 2000 | 55.00 |
+-------------+----------------+------------+
14 rows in set (0.00 sec)
//联结多个表
mysql> select prod_name,vend_name,prod_price,quantity
-> from products,orderitems,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在运行时关联指定的每个表以处理联结。这种处理可能是非常耗费资源的，不要连接不必要的表，联结的表越多，性能下降的越厉害。
6.4 自联结
//自联结
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> 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 right outer 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_id,customers.cust_name,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_id | cust_name | num_ord |
+---------+----------------+---------+
| 10001 | Coyote Inc. | 2 |
| 10003 | Wascals | 1 |
| 10004 | Yosemite Place | 1 |
| 10005 | E Fudd | 1 |
+---------+----------------+---------+
4 rows in set (0.00 sec)
4473
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
