mysql基础(5)--子查询、联结查询(join)、组合查询(union)

一: 子查询

子查询:意为在SELECT查询语句中嵌套另外的查询语句.

1. 利用子查询进行过滤

例:

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 = '123'));

解析:
为了执行上述 SELECT 语句,MySQL实际上必须执行3条 SELECT语句。最里边的子查询prod_id='123的order_num,此列表用于其外面的
子查询的 WHERE 子句。外面的子查询返回cust_id的数值们,然后用于最外层查询的 WHERE 子句。最外层查询返回所需的数据。

2. 作为计算字段使用子查询

相关子查询: 涉及外部查询的子查询称为相关子查询
例:

 SELECT cust_name, cust_state, (
 SELECT COUNT(*) FROM orders WHERE orders.cust_id=customers.cust_id
 ) AS orders FROM customers;

解析:
这 条 SELECT 语 句 对 customers 表 中 每 个 客 户 返 回 3 列 :cust_name 、 cust_state 和 orders 。 orders 是一个计算字段,
它是由圆括号中的子查询建立的。该子查询对检索出的每个客户执行一次。该子查询执行n次,就会检索出n个客户。
其中使用了完全限定列名:orders.cust_id=customers.cust_id,这会告诉sql比较orders 表中的 cust_id 与当前正从 customers 表中检索的 cust_id
,为了不产生歧义,要尽量使用完全限定列名(格式:表名.列名)

二: 联结查询(join)

我们以 省 市 县 三级来进行举例

1. 笛卡尔积

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

 SELECT * FROM province, city, xian;

求出的结果为 省的数量 * 市的数量 * 县的数量

2. 内连接

语法格式:

SELECT 字段名 FROM 表1 INNER JOIN 表2 ON 条件 INNER JOIN 表3 ON 条件;

例: 显示省市详细信息

SELECT sheng.s_name,city.c_name FROM
sheng INNER JOIN city ON sheng.s_id=city.cfather_id;

显示省市县的详细信息

SELECT sheng.s_name,city.c_name,xian.x_name FROM
sheng INNER JOIN city ON sheng.s_id=city.cfather_id
INNER JOIN xian ON city.c_id=xian.xfather_id;

3. 外连接

• 左连接(以 左表 为主显示查询结果)
  语法格式:

    SELECT 字段名 FROM
    表1 LEFT JOIN 表2 ON 条件
    LEFT JOIN 表3 ON 条件;
  

• 右连接(以 右表 为主显示查询结果)
  语法格式:

    SELECT 字段名 FROM
    表1 RIGHT JOIN 表2 ON 条件
    RIGHT JOIN 表3 ON 条件;
  

联结使用小结

• 注意所使用的联结类型。一般我们使用内部联结,但使用外部联结也是有效的。
• 保证使用正确的联结条件,否则将返回不正确的数据。
• 应该总是提供联结条件,否则会得出笛卡儿积。
• 在一个联结中可以包含多个表,甚至对于每个联结可以采用不同的联结类型。虽然这样做是合法的,一般也很有用,但应该在一起测试它们前,分别测试每个联结。这将使故障排除更为简单。

三:组合查询

1. 组合查询

MySQL允许执行多个查询(多条 SELECT 语句),并将结果作为单个查询结果集返回。这些组合查询通常称为并( union )或复合查询(compound query)
使用场景

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

2. 使用union

例:

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);

解析:
这条语句由前面的两条 SELECT 语句组成,语句中用 UNION 关键字分隔。 UNION 指示MySQL执行两条 SELECT 语句,并把输出组合成单个查询结果集。
UNION 从查询结果集中自动去除了重复的行,这是 UNION 的默认行为,如果想返回所有匹配行,可使用 UNION ALL 而不是 UNION 。

3. union规则

• UNION 必须由两条或两条以上的 SELECT 语句组成,语句之间用关键字 UNION 分隔(因此,如果组合4条 SELECT 语句,将要使用3个UNION 关键字)
• UNION 中的每个查询必须包含相同的列、表达式或聚集函数(不过各个列不需要以相同的次序列出)
• 列数据类型必须兼容:类型不必完全相同,但必须是DBMS可以隐含地转换的类型(例如,不同的数值类型或不同的日期类型)

4. 对组合查询结果进行排序

规则:
SELECT 语句的输出用 ORDER BY 子句排序。在用 UNION 组合查询时,只能使用一条 ORDER BY 子句,它必须出现在最后一条 SELECT 语句之后。对于结果集,不存在用一种方式排序一部分,而又用另一种方式排序另一部分的情况,因此不允许使用多条 ORDER BY 子句.
例:

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) ORDER BY vend_id, prod_price;

解析
这条 UNION 在最后一条 SELECT 语句后使用了 ORDER BY 子句。虽然 ORDER BY 子句似乎只是最后一条 SELECT 语句的组成部分,但实际上MySQL将用它来排序所有 SELECT 语句返回的所有结果。