SELECT_in_MySQL

Mac安装使用Mysql教程(从零开始)

第五章 MySQL基本操作之查询

       MySQL中的基本操作主要有选择,插入,删除和更新四种,对应代码分别为SELECTINSERTDELETEUPDATE。对MySQL中基本操作的介绍将以下图所示五个表格为例。本文将总结介绍MySQL的查询操作。

在这里插入图片描述
在这里插入图片描述

图4-1.表格Orders和Customers

在这里插入图片描述
在这里插入图片描述

图4-2.表格Products和Vendors

在这里插入图片描述

图4-3.表格OrderItems

5.1 检索数据排序

  1. 单列排序

    SELECT prod_name
    FROM Products
    ORDER BY prod_name;
    

       将检索出的 prod_name 数据根据 prod_name进行排序。

  1. 多列排序

    SELECT prod_id, prod_price, prod_name
    FROM Products
    ORDER BY prod_price, prod_name
    

       将检索出的数据 prod_id,prod_price,prod_name先根据 prod_price 排序,如果有产品的价格相同则再根据 prod_name 进行排序。

  1. 按列相对位置排序

    SELECT prod_id, prod_price, prod_name
    FROM Products
    ORDER BY 2, 3;
    

       用 SELECT 中列名的相对位置代替多列排序中 ORDER BY 后面的列名,依然遵循多列排序的规则。

  1. 降序排列

    SELECT prod_id, prod_price, prod_name
    FROM Products
    ORDER BY prod_price DESC, prod_name;
    

       一般默认进行升序排列,要降序排列时,只需在进行排序的列名后添加语句DESC,多列降序排列时则在每一列后面都添加语句DESC

5.2 数据过滤

5.3 汇总数据

       有时不需要实际的数据本身,而需要汇总表中数据。这时就需要聚集函数来实现数据汇总,各种数据库对聚集函数的实现都非常一致。五种常用的聚集函数如下所示:

  1. 返回某列的平均值

    SELECT AVG(prod_price) AS avg_price
    FROM Products
    WHERE vend_id = 'DLL01';  # 添加`where`子句对特定若干行或列求均值
    

    注意
    NULL值:自动忽略。

  2. 返回某列最大值

    SELECT MAX(prod_price) 
    AS max_price
    FROM Products;
    

    注意
    NULL值:自动忽略;
    文本列:返回按列排序的最后一行。

  3. 返回最小值

    SELECT MIN(prod_price) 
    AS max_price
    FROM Products;
    
  4. 返回某列行数

    SELECT COUNT(*) AS num_cust
    FROM Customers;
    

    注意
    NULL值:COUNT(*)不会忽略,统计表中所有行;但COUNT(COLUMN)会忽略。

  5. 对某列值求和

    SELECT SUM(item_price*quantity) 
    AS total_price
    FROM OrderItems
    WHERE order_num = 20005;
    

    注意
    NULL值:自动忽略。

  6. 聚集不同值

  • 当指定 ALL 或不指定时,包含所有行。
  • 当指定 DISTINCT 时,只包含不同的值。

5.4 数据分组

       聚集函数是在表的所有数据或匹配where子句的数据上进行计算,当要将数据划分为若干组且对每个组分别进行聚集函数计算时,仅有聚集函数是不够的,还要进行分组。

  1. 创建分组GROUP BY

    SELECT vend_id, prod_price, COUNT(*) AS num_prods
    FROM Products
    GROUP BY vend_id, prod_price;
    

    注意

    • GROUP BY子句可以包含任意数目的列,因而可以进行嵌套分组,具体方式见上面的例子;
    • GROUP BY子句中的列必须是检索列或有效的表达式(但不能是聚集函数);
    • SELECT语句中的每一列都必须在GROUP BY子句中出现;
    • 大多数SQL不允许GROP BY列带有可变长度的数据类型(如文本或备注型字段);
    • NULL:会被当作一个单独的分组列出;
    • GROUP BY一定在WHERE子句之后,ORDER BY子句之前;
  2. 过滤分组HAVING
    之前的WHERE子句只可以过滤行,要过滤分组时需要使用HAVING子句。

    SELECT cust_id, COUNT(*) AS orders
    FROM Orders
    GROUP BY cust_id
    HAVING COUNT(*) >= 2;
    

    注意

    • WHERE 在数据分组前进行过滤,HAVING 在数据分组后进行过滤;
    • 所有类型的WHERE子句都可以用HAVING子句来代替(HAVING支持所有的WHERE操作符);

5.5 使用子查询

       SELECT语句只可以查询单个数据表,但有时需根据若干数据表查询数据,此时可以创建子查询(subquery)进行查询。

  1. 使用子查询过滤
    例子
    需要列出订购物品 RGAN01 的所有顾客?
    解答
    (1) 检索包含物品 RGAN01 的所有订单的编号。
    (2) 检索具有前一步骤列出的订单编号的所有顾客的 ID。
    (3) 检索前一步骤返回的所有顾客 ID 的顾客信息。
    上诉操作需检索三张表,分别为 OrderItems, Orders和 Customers。
    硬查询
    先执行步骤(1)

    SELECT order_num
    FROM OrderItems
    WHERE prod_id = 'RGAN01';
    

    再执行步骤(2):

    SELECT cust_id
    FROM Orders
    WHERE order_num IN (20007,20008); 
    

    第(3)步与前两步类似,不再赘述。
    子查询
           把一条 SELECT 语句返回的结果嵌套入另一条SELECT 语句中的 WHERE 子句。

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

    注意

    • 作为子查询的 SELECT 语句只能查询单个列。企图检索多个列将返回 错误;
    • 使用子查询并不总是执行上诉数据检索的最有效方法;
  2. 作为计算字段使用子查询
    例子
    显示 Customers 表中 每个顾客的订单总数
    解答
    (1) 从 Customers 表中检索顾客列表;
    (2) 对于检索出的每个顾客,统计其在 Orders 表中的订单数目;
    上诉操作需检索两张表,分别为 Orders 和 Customers。

    SELECT cust_name,
           cust_state,
          (SELECT COUNT(*)
           FROM Orders
           WHERE Orders.cust_id = Customers.cust_id) AS orders
    FROM Customers
    ORDER BY cust_name;
    

    注意

    • 子查询中的 WHERE 子句使用了完全限定列名,指定表名和列名 (Orders.cust_id 和 Customers.cust_id),而不只是列名。
    • 完全限定列名可以避免歧义,在 SELECT 语句中操作多个表就应使用完全限定列名来避免歧义。

5.6 联结表

  1. 联结
    用于在一条SELECT语句中关联多个表的机制。它并不是物理实体,DBMS会根据需要建立联结。

  2. 创建联结
    指定要联结的表以及它们的联结方式即可。

    SELECT vend_name, prod_name, prod_price
    FROM Vendors, Products
    WHERE Vendors.vend_id = Products.vend_id;
    

    叉联结
    如果没有 WHERE 子句,结果为两个表的笛卡尔积(叉联结),即结果的行数是被 查询表的行数的乘积。
    内联结
    之前的联结都可称为等值联结,它基于两个表之间的相等测试,也称为内联结。可以 用不同的语法明确指定联结类型,之前的语句可改写如下:

    SELECT vend_name, prod_name, prod_price
    FROM Vendors INNER JOIN Products
    ON Vendors.vend_id = Products.vend_id;
    

    联结多个表
    同样指定要联结的表以及各表之间的联系,具体如下:

    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 = 20007;
    

    注意

    • 不要联结不必要的表,联结表越多,性能下降越厉害。

5.7 高级联结

  1. 使用表别名
    原始表名 AS 表别名
    注意
    Oracle 中没有AS,所以直接用 原始表名 表别名 的方式创建表别名。

  2. 使用不同类型的联结
           之前使用的都是内联结或等值联结等简单联结。除此之外还有自联结,自然联结和外联结。
    自联结
           自联结即为表自己和自己联结,一般比使用查询要快。

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

    自然联结
           使用明确子集表示表的某一列,保证它只在结果中出现一次。迄今为止建立的每个内联结都是自然联结,很可能永远都不会用到不是自然联结的内联结。
    外联结
           之前的联结中,一个表中的行必定与另一个表中的一行相关联对应,但有时也需要包含没有关联行的行,此时需要使用外联结。

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

           外联结使用关键字 OUTER JOIN 实现包含无关联行的行。使用 OUTER JOIN 语法时,必须使用 RIGHTLEFT 关键字指定包括其所有行的表(RIGHT 指出的是 OUTER JOIN 右边的表,而 LEFT 指出的是 OUTER JOIN 左边的表)。此外还有一种全外联结(FULL OUTER JOIN),它检索两个表中 的所有行并关联那些可以关联的行(但Access、MariaDB、MySQL、Open Office Base 和 SQLite 不支持该外联结语法)。
    使用带聚集函数的联结

    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;
    

5.8 组合查询

组合查询
       进行组合查询有两种情况,第一种为对一个表进行多个查询,然后按一个查询返回结果;第二种为一个查询中从多个表返回结果。
创建组合查询
       可以通过两种方式创建组合查询,第一种使用多个WHERE子句;第二种为使用关键字UNION连接两个查询。具体见如下代码:

问题:需要 Illinois、Indiana 和 Michigan 等美国几个州的所有顾 客的报表,还想包括不管位于哪个州的所有的 Fun4All。  
解答:
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';
或
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
OR cust_name = 'Fun4All';

UNION规则

  1. UNION必须包含两个或两个以上的SELECT语句,且用UNION分隔;
  2. UNION中每个查询的列名,表达式,聚集函数必须相同,但列名出现顺序可以不同(亲自实践发现在MySQL中列名不同也不会报错,但组合不同的列名显然无意义,具体例子见操作多个表);
  3. UNION中列数据类型必须兼容,列数据类型不一定完全相同,但必须是DBMS可以隐含转换的类型。

保留或取消重复的行
       UNION默认为取消重复的行,如果要保留重复的行则将UNION改为UNION ALL

组合查询结果排序
       UNION查询中只允许使用一个ORDER BY子句,并且必须在最后一个SELECT语句之后。

5.9 操作多个表

       之前的例子为对一个表查询多次,UNION连接多个表的查询如下:

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION
SELECT order_num, order_item, prod_id
FROM OrderItems
WHERE prod_id = 'RGAN01';  
cust_name    |cust_contact|cust_email
结果为  
cust_name    |cust_contact|cust_email           |
-------------|------------|---------------------|
Village Toys |John Smith  |sales@villagetoys.com|
Fun4All      |Jim Jones   |jjones@fun4all.com   |
The Toy Store|Kim Howard  |                     |
20007        |5           |RGAN01               |
20008        |1           |RGAN01               | 

       两个表查询的列名并不相同,但也会强制将两个查询的结果组合在一起,但没什么意义。

5.10 参考

[1] Ben Forta.SQL必知必会-中文-第4版
[2] .MySQL 8.0参考手册(网页)
[3] .MySQL 8.0参考手册(pdf)

  • 1
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值