mysql的与decode函数相似的

转载

西洋无悔 2024-12-03 07:09:05

文章标签 mysql的与decode函数相似的 mysql sql 数据库其他 文章分类 MySQL 数据库

使用表别名

之前在创建计算字段的时候，已经介绍了如何使用别名来引用被检索的表列，如下代码：

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

mysql的与decode函数相似的_mysql的与decode函数相似的

SQL 除了可以对列名和计算字段使用别名，还可以允许对表名起别名

这样做的理由主要是：
缩短 SQL 的语句
允许在一条 SELECT 语句中多次使用相同的表

如下面的示例代码，与之前讲述的例子语句基本相同，但是改成了使用别名：

SELECT cust_name, cust_contact
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 = 'ANV01';

mysql的与decode函数相似的_其他_02

可以看到，FROM 子句中的三个表都是用了别名，这样就可以使用省略的 C 而不使用全名 Customers 等等

在这个例子中，表的别名只用于了 WHERE 子句

但是其实别名不仅是能够用于 WHERE 子句，还可以用于 SELECT 的列表，ORDER BY 子句以及其他的语句部分

注意：
并不是所有的 DBMS 都支持使用 AS 作为别名，有些语法可能需要一些改变；但是在 MySQL 中，使用 AS 指定列名

表别名只在查询执行中使用，与列别名不同，表别名不返回到客户端

使用不同类型的联结

之前讲述的联结都是内联结或者等值联结的简单联结
接下来是三种其他的联结：

自联结（self-join）

自然联结（natural join）

外联结（outer join）

自联结（self-join）

如前所述，使用表别名的一个主要原因就是能在一条 SELECT 语句中不止一次引用相同的表

看一个例子，假如要给与 Jim Jones 同一个公司的所有顾客发送一封信件；这个查询要求首先找出 Jim Jones 工作的公司，然后找出在该公司工作的顾客，下面是解决此问题的一种方法：

SELECT cust_id, cust_name, cust_contact
FROM Customers
WHERE cust_name = (
SELECT cust_name FROM Customers
WHERE cust_contact = 'Jim Jones');

mysql的与decode函数相似的_mysql_03

使用子查询，内部的 SELECT 语句做一个简单的搜索，返回 Jim Jones 工作公司的 cust_name
该名字用于外部查询的 WHERE 子句中，以检索出为该公司工作的所有雇员

现在来看使用联结实现相同的查询：

SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM Customers AS c1, Customers AS c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim jones';

mysql的与decode函数相似的_数据库_04

此查询中需要的两个表实际是相同的表，因此 Customers 表在 FROM 子句中出现了两次
虽然这是合法的，但是对 Customers 的引用具有歧义性，因为 DBMS 不知道你引用的是哪个 Customers 表

解决此问题，需要使用表别名

Customers 第一次出现使用了别名 c1，第二次出现使用了别名 c2，现在可以将这些别名用作表名

例如，SELECT 语句使用 c1 前缀明确给出所需列的全名，如果不这样，DBMS 将会返回错误，因为名为 cust_id、cust_name 、cust_contact 的列各有两个；DBMS 并不知道想要的是哪一列（即使它们其实是同一列）

WHERE 首先联结两个表，然后按第二个表中的 cust_contact 过滤数据，返回所需的数据

使用自联结而不是子查询：
自联结通常作为外部语句，用来代替从相同表中检索数据的使用子查询语句
虽然最终的结果是相同的，但是许多 DBMS 处理联结的速度远比处理子查询快得多
实际中可以试验一下，以确定哪一种的性能更好

自然联结（natural join）

无论何时对表进行联结，应该至少有一列不止出现一个表中（被联结的列）

标准的联结（即内联结）返回所有的数据，相同的列甚至多次出现

自然联结排除多次出现，使每一列只返回一次

那么怎么完成这一项工作呢？
系统完不成的工作，由自己完成！

自然联结要求你只能选择那些唯一的列，一般通过对一个表使用通配符（SELECT *），而对其他表的列使用明确的子集来完成

如下面的示例代码：

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 = 'ANV01';

mysql的与decode函数相似的_sql_05

在这个例子中，通配符只对第一个表使用，所有其他列明确列出，所有没有重复的列被检索

事实上，大部分使用的内联结都是自然联结，很多人基本不会用到不是自然联结的内联结

外联结

许多联结将一个表中的行与另一个表中的行相关联，但是有时候需要包含没有关联行的那些行

例如，可能需要使用联结来完成以下工作：

对每个顾客下的订单进行计数，包括那些至今尚未下订单的顾客
列出所有产品以及订购数量，包括没有人订购的产品
计算平均销售规模，包括那些至今尚未下订单的顾客

在这个例子中，联结包含了那些在相关表中没有关联行的行，这种联结称为外联结

注意：
用来创建外联结的语法在不同的 SQL 实现中可能稍有不同

首先看一个简单的内联结，它检索所有的顾客及其订单：

SELECT Customers.cust_id, Orders.order_num
FROM Customers INNER JOIN Orders
ON Customers.cust_id = Orders.cust_id;

mysql的与decode函数相似的_mysql的与decode函数相似的_06

外联结的语法类似，要检索包括没有订单顾客在内的所有顾客，可以如下进行：

SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id;

mysql的与decode函数相似的_sql_07

这条 SELECT 语句使用了关键字 OUTER JOIN 来指定联结类型（而不是在 WHERE 子句中指定）

但是，与内联结关联两个表中的行不同的是，外联结还包括没有关联行的行

在使用 OUTER JOIN 语法时，必须使用 RIGHT 或者 LEFT 关键字指定包括其所有行的表
（RIGHT 指出的是 OUTER JOIN 右边的表，而 LEFT 指出的是 OUTER JOIN 左边的表）

上面语句中，使用 LEFT OUTER JOIN 从 FROM 子句左边的表（Customers 表）中选择所有行，为了从右边的表中选择所有的行，需要使用 RIGHT OUTER JOIN，如下示例：

SELECT Customers.cust_id, Orders.order_num
FROM Customers RIGHT OUTER JOIN Orders
ON Orders.cust_id = Customers.cust_id;

外联结的类型：

总是有两种基本的外联结形式：
左外联结和右外联结

它们之间的唯一差别是所关联的表的顺序

调整 FROM 或 WHERE 子句中表的顺序，左外联结可以转化为右外联结

还存在一种外联结，就是全外联结（full outer join），它检索两个表中的所有行并关联那些可以关联的行

与左外联结或右外联结包含一个表的不关联的行不同，全外联结包含两个表的不关联的行

语法如下：

SELECT Customers.cust_id, Orders.order_num
FROM Orders FULL OUTER JOIN Customers
ON Orders.cust_id = Customers.cust_id;

（MySQL 不支持全外联结）

使用带聚集函数的联结

聚集函数用来汇总数据，聚集函数可以从一个表中汇总数据，也可以与联结一起使用

如下例子中，要检索所有顾客及每个顾客所下的订单数，可以使用 COUNT() 函数来完成：

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

mysql的与decode函数相似的_mysql_08

上述 SELECT 语句使用 INNER JOIN 将 Customers 和 Orders 表互相关联，GROUP BY 子句按顾客分组数据

因此，函数调用 COUNT(Orders.order_num) 对每个顾客的订单计数，将它作为 num_ord 返回

聚集函数也可以方便地与其他联结一起使用：

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

mysql的与decode函数相似的_mysql的与decode函数相似的_09