Mac安装使用Mysql教程(从零开始)
- 第一章 Mac安装MySQL
- 第二章 安装数据库管理软件DBeaver
- 第三章 DBeaver创建MySQL数据库
- 第四章 终端管理MySQL
- 第五章 MySQL基本操作之查询
- 第六章 MySQL基本操作之插入
- 第七章 MySQL基本操作之更新与删除
- 第八章 创建和操纵表
- 第九章 使用视图
- 第十章 使用存储过程
- 第十一章 管理事务处理
- 第十二章 游标
- 第十三章 MySQL学习问题及解决记录
第五章 MySQL基本操作之查询
MySQL中的基本操作主要有选择,插入,删除和更新四种,对应代码分别为SELECT
,INSERT
,DELETE
和UPDATE
。对MySQL中基本操作的介绍将以下图所示五个表格为例。本文将总结介绍MySQL的查询操作。
5.1 检索数据排序
-
单列排序
SELECT prod_name FROM Products ORDER BY prod_name;
将检索出的 prod_name 数据根据 prod_name进行排序。
-
多列排序
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 进行排序。
-
按列相对位置排序
SELECT prod_id, prod_price, prod_name FROM Products ORDER BY 2, 3;
用 SELECT 中列名的相对位置代替多列排序中 ORDER BY 后面的列名,依然遵循多列排序的规则。
-
降序排列
SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price DESC, prod_name;
一般默认进行升序排列,要降序排列时,只需在进行排序的列名后添加语句DESC
,多列降序排列时则在每一列后面都添加语句DESC
。
5.2 数据过滤
5.3 汇总数据
有时不需要实际的数据本身,而需要汇总表中数据。这时就需要聚集函数来实现数据汇总,各种数据库对聚集函数的实现都非常一致。五种常用的聚集函数如下所示:
-
返回某列的平均值
SELECT AVG(prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01'; # 添加`where`子句对特定若干行或列求均值
注意:
NULL值:自动忽略。 -
返回某列最大值
SELECT MAX(prod_price) AS max_price FROM Products;
注意:
NULL值:自动忽略;
文本列:返回按列排序的最后一行。 -
返回最小值
SELECT MIN(prod_price) AS max_price FROM Products;
-
返回某列行数
SELECT COUNT(*) AS num_cust FROM Customers;
注意:
NULL值:COUNT(*)不会忽略,统计表中所有行;但COUNT(COLUMN)会忽略。 -
对某列值求和
SELECT SUM(item_price*quantity) AS total_price FROM OrderItems WHERE order_num = 20005;
注意:
NULL值:自动忽略。 -
聚集不同值
- 当指定 ALL 或不指定时,包含所有行。
- 当指定 DISTINCT 时,只包含不同的值。
5.4 数据分组
聚集函数是在表的所有数据或匹配where子句的数据上进行计算,当要将数据划分为若干组且对每个组分别进行聚集函数计算时,仅有聚集函数是不够的,还要进行分组。
-
创建分组
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子句之前;
-
过滤分组
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)进行查询。
-
使用子查询过滤
例子:
需要列出订购物品 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 语句只能查询单个列。企图检索多个列将返回 错误;
- 使用子查询并不总是执行上诉数据检索的最有效方法;
-
作为计算字段使用子查询
例子:
显示 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 联结表
-
联结
用于在一条SELECT语句中关联多个表的机制。它并不是物理实体,DBMS会根据需要建立联结。 -
创建联结
指定要联结的表以及它们的联结方式即可。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 高级联结
-
使用表别名
原始表名 AS 表别名
注意:
Oracle 中没有AS,所以直接用原始表名 表别名
的方式创建表别名。 -
使用不同类型的联结
之前使用的都是内联结或等值联结等简单联结。除此之外还有自联结,自然联结和外联结。
自联结
自联结即为表自己和自己联结,一般比使用查询要快。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
语法时,必须使用RIGHT
或LEFT
关键字指定包括其所有行的表(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规则
- UNION必须包含两个或两个以上的SELECT语句,且用UNION分隔;
- UNION中每个查询的列名,表达式,聚集函数必须相同,但列名出现顺序可以不同(亲自实践发现在MySQL中列名不同也不会报错,但组合不同的列名显然无意义,具体例子见操作多个表);
- 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)