MySQL学习笔记4

1.使用子查询（嵌套在WHERE子句中的子查询）

例：以下为三个步骤（1）检索包含物品 TNT2 的所有订单的编号。(2) 检索具有前一步骤列出的订单编号的所有客户的ID。(3) 检索前一步骤返回的所有客户ID的客户信息。把三个查询作为一条语句.在 SELECT 语句中，子查询总是从内向外处理

SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num FROM orderitems WHERE pro_id="TN2");

现在得到了订购物品TN2的所有客户的ID，下一步是检索这些客户ID的信息

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 pro_id="TN2"));

2.作为计算字段使用子查询：(1) 从 customers 表中检索客户列表。(2) 对于检索出的每个客户，统计其在 orders 表中的订单数目

SELECT cust_name，cust_state，(SELECT COUNT(*)
                                FROM orders
                                WHERE orders.cust_id=customers.cust_id) AS orders
FROM customers
ORDER BY cusst_name;

这条 SELECT 语句对 customers 表中每个客户返回 3 列：cust_name 、 cust_state 和 orders 。 orders 是一个计算字段，它是由圆括号中的子查询建立的。子查询中的 WHERE 子句用了完全限定列名,比较orders 表中的 cust_id 与当前正从 customers 表中检索的 cust_id

3.连接（join）在两个或多个表中查询数据，把来自两个或多个表的行结合起来，基于这些表之间的共同字段

MySQL学习笔记4

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。两个表之间的关系是 FROM 子句的组成部分，以 INNER
JOIN 指定

SELECT vend_name,prod_name,prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

其中vendors表只存储供应商信息，有供应商ID和名字，products储存供应商ID和产品信息。这里vendors的主键（供应商ID）就作为products表的外键。这两个表是通过vend_id列来联系起来的

等价于

SELECT vend_name,prod_name,prod_price
FROM vendors,prodicts
WHERE vendors.vend_id=products.vend_id

在本文最上面的例子：

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 pro_id="TN2"));

子查询并不总是执行复杂SELECT操作的最有效方法,下面使用了两个等值连接

SELECT cust_name,cust_contact
FROM customers,orders,orderitems
WHERE customers.cust_id=orders.cust_id
AND orderitems.order_num=orders.order_num
AND prod_id="TNT2";

为了缩短上面的操作，可以使用表别名，允许在单条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="TNT2";

4.表别名的另一个应用：自联结（两次查询实际上是相同的表）。假如你发现某物品（其ID为 DTNTR ）存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。此查询要求首先找到生产ID为 DTNTR 的物品的供应商，然后找出这个供应商生产的其他物品。重点是找出可能存在问题的物品信息

方法1：子查询

SELECT prod_id,prod_name
FROM products
WHERE vend_id=(SELECT vend_id
                FROM products
                WHERE prod_id="DTNDR");

方法2：自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句

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="DTNTE";

5.外部连接：对每个客户下了多少订单进行计数，包括那些至今尚未下订单的客户；列出所有产品以及订购数量，包括没有人订购的产品。LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。这种类型的联结称为外部联结。

检索所有用户，包括哪些没有订单的用户，查询用户id和订单号
```
SELECT customers.cust_id,orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id=order.cust_id;
```
外部联结还包括没有关联行的行。在使用 OUTER JOIN 语法时，必须使用 RIGHT 或 LEFT 关键字指定包括其所有行的表（ RIGHT 指出的是 OUTER JOIN 右边的表，而 LEFT指出的是 OUTER JOIN 左边的表）。上面的例子使用 LEFT OUTER JOIN 从 FROM子句的左边表（ customers 表）中选择所有行

完整外部连接。当左（表1）或右（表2）表记录匹配时，FULL OUTER JOIN关键字将返回所有记录。FULL OUTER JOIN关键字返回左表（Customers）中的所有行，以及右表（Orders）中的所有行。如果 "Customers"中的行中没有"Orders"中的匹配项，或者"Orders"中的行中没有 "Customers"中的匹配项，那么这些行也会列出。

6.使用带聚集函数的联结：要检索所有客户及每个客户所下的订单数两个表：customers表有customers.id 和customers.name 。orders表有customers.id和order.num.要将这两个表联结起来（使用OUTER JOIN来包含所有用户）GROUP BY按客户进行分组，count对每个客户的订单计数

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;

7.组合查询或者并（union）。给出多条SELECT语句，将他们的结果组合成单个结果集返回。在各条SELECT语句之间放上UNION关键字即可。NION 从查询结果集中自动去除了重复的行，如果想返回所有匹配行，可使用 UNION ALL 而不是 UNION

在用 UNION 组合查询时，只能使用一条 ORDER BY 子句，它必须出现在最后一条 SELECT 语句之后。

UNION中的每个SELECT语句必须具有相同的列数

以下SQL语句使用UNIONALL从"Customers"和 "Suppliers" 表中选择所有德国城市（也是重复数值）：

实例

SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION ALL
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;

8.复制表。完全复制表的结构（索引、默认值等）仅适用CREATE TABLE ... SELECT 命令太过于麻烦，以下是三个步骤来完全的复制表

使用 SHOW CREATE TABLE 命令获取创建数据表(CREATE TABLE) 语句，该语句包含了原数据表的结构，索引等。这个语句就是我们要在使用一次
```
SHOW CREATE TABLE w3cschool_tbl
```
复制上述的SQL语句，修改数据表名称为我们想要的，再执行，可完全复制数据表结构
如果你想拷贝数据表的数据你可以使用 INSERT INTO... SELECT 语句来实现。

INSERT INTO clone_tbl (w3cschool_id,
    ->                        w3cschool_title,
    ->                        w3cschool_author,
    ->                        submission_date)
    -> SELECT w3cschool_id,w3cschool_title,
    ->        w3cschool_author,submission_date
    -> FROM w3cschool_tbl;
Query OK, 3 rows affected (0.07 sec)

9.你可能想知道MySQL以下三种信息：

查询结果信息： SELECT, UPDATE 或 DELETE语句影响的记录数。
数据库和数据表的信息： 包含了数据库及数据表的结构信息。（SHOW TABLES SHOW DATABASES等语句）
MySQL服务器信息： 包含了数据库服务器的当前状态（SHOW STATUS），版本号(SELECT VERSION())等,当前用户名（SELECT USER()）,当前数据库名（SELECT DATABASE）

实例

相关推荐