MySQL 必知必会 - 检索数据

2022 年 9 月 12 日
陕西
本文字数：7712 字
阅读完需：约 25 分钟

检索数据

写在前面，sql 语句是不区分大小写的，SELECT 和 select 是一样的，但是许多的 SQL 开发人员习惯对 sql 关键字大写，而对所有的列和表名使用小写。

select 语句

检索单个列

select prod_name from products;

复制代码

检索多个列

SELECT prod_id , prod_name,prod_price  FROM products;

复制代码

检索所有列

SELECT * FROM products;

复制代码

检索不同行

使用 DISTINCT 关键字使得返回的信息是不同的行信息。

但是你不能这样：

除非两个列相同，否则所有行信息都会被展示出来。

限制结果

SELECT 语句返回所有匹配的行。为了返回第一行或者前几行，可使用 LIMIT 子句

SELECT prod_name  FROM  products  LIMIT 5;

复制代码

SELECT prod_name  FROM  products  LIMIT 5,5;

复制代码

LIMIT 5 , 5 代表的是从行 5 开始的 5 行。

行 0：检索出来第一行为行 0 而不是行 1。LIMIT 1，1 检索出来的是第二行，而不是第一行。

行不够时，如 LIMIT 10,5, 。如果只有 13 行时，mysql 只能返回他能返回的行数了就。

MySQL 5 的 LIMIT 语法。LIMIT 4 OFFSET 3 表示从行 3 开始取 4 行。

使用完全限定的表明

这里其实就是指，把列名通过表明限定，甚至把表明通过数据库名来完全限定。

SELECT products.prod_name FROM bizhibihui.products;

复制代码

排序检索数据（ORDER BY）

排序数据

子句：之前写到的 FROM 就算是 SELECT 的一个子句。为了明确排序顺序所以我们可以使用 ORDER BY 子句。

SELECT prod_name FROM products ORDER BY prod_name;

复制代码

这样上面的搜索结果就实现了字母排序。

:joystick:上面 ORDER BY 子句使用的列是检索列，其实不使用检索的列进行排序也是完全合法的。

按多个列排序

我们在现实生活中也会遇到这样的情况：比如你说按姓名排序，当姓一样的时候，你就需要按名字来排序

SELECT prod_id , prod_price ,prod_name FROM  productsORDER BY prod_price,prod_name;

复制代码

上面的结果就可以观察到，当价格一样的时候，就会按照产品名称排序。

指定排序方向（DESC 降序）

数据排序不仅限于升序排序（从 A 到 Z），这只是默认的排序顺序。我们还可以使用降序排序关键字 DESC

SELECT prod_id,prod_price,prod_nameFROM productsORDER BY prod_price DESC;

复制代码

上面的图片就实现了价格的降序排序。

打算对多个列排序怎么办呢？

SELECT prod_id,prod_price,prod_nameFROM productsORDER BY prod_price DESC,prod_name;

复制代码

上面就实现了先对价格降序，然后价格相同的按名字升序。

DESC 关键词只应用到直接位于其前面的列名。所以如果你想对多个列进行降序排序，必须每个列指定 DESC 关键字。

ASC 是升序关键词，但其实是没有什么用处的，因为默认就是升序呀。

例子：找出最昂贵的物品

SELECT prod_priceFROM productsORDER BY prod_price DESCLIMIT 1;

复制代码

过滤数据（WHERE 子句）

使用 WHERE 子句

我们很少需要检索表中的所有行，通常会根据特定操作或报告的需要提取数据的子集。

在 SELECT 语句中，数据根据 WHERE 子句中指定的搜索条件进行过滤。WHERE 子句在表名（FROM 子句）之后给出。

SELECT prod_name ,prod_priceFROM productsWHERE prod_price=2.50;

复制代码

只展示价格为 2.50 的产品。

WHERE 子句的位置：在同时使用 ORDER BY 和 WHERE 子句时，应该让 ORDER BY 位于 WHERE 之后，否则将会产生错误。

WHERE 子句操作符

检查单个值：

SELECT prod_name,prod_priceFROM productsWHERE prod_name = 'fuses';

复制代码

SELECT  prod_name,prod_priceFROM productsWHERE prod_price < 10;

复制代码

不匹配检查：

SELECT vend_id,prod_nameFROM productsWHERE vend_id<>1003;

复制代码

范围值检查:

SELECT prod_name ,prod_priceFROM productsWHERE prod_price BETWEEN 5 AND 10;

复制代码

从上面的结果可以看出来这个 BETWEEN 的范围是闭区间

空值检查：

SELECT cust_idFROM customersWHERE cust_email IS NULL;

复制代码

一定要注意一点，空值不是 0。

过滤与不匹配：在通过过滤选择出不匹配值的行中，你可能希望可以返回具有 NULL 值的行，但是，不行。

数据过滤

组合 WHERE 子句

之前的 WHERE 子句都是单一的条件，其实是可以使用多个 WHERE 子句的，这些子句可以使用 AND 和 OR 子句的方式使用。

AND 操作符

为了通过不止一个列进行过滤，可使用 AND 操作符给 WHERE 子句附加条件。

SELECT prod_id , prod_price, prod_nameFROM productsWHERE vend_id = 1003 AND prod_price <= 10;

复制代码

OR 操作符

检索匹配任一条件的行。

SELECT prod_name,prod_priceFROM productsWHERE vend_id = 1002 OR vend_id = 1003;

复制代码

AND 的计算次序优先级更高

为了解决这一问题，方法就是加圆括号（）

IN 操作符

IN 操作符用来指定条件范围，范围内的每个条件都可以进行匹配，IN 取合法值的由逗号隔开。

SELECT prod_name ,prod_priceFROM productsWHERE vend_id IN (1002,1003)ORDER BY prod_name;

复制代码

如果你认为 IN 和 OR 功能相同，这种想法是正确的。下面就是晚上上面的同样的工作。

SELECT prod_name ,prod_priceFROM productsWHERE vend_id = 1002 OR vend_id = 1003ORDER BY prod_name;

复制代码

那为什么选择 IN 呢？

在使用长的合法选项清单时，IN 操作符的语法更清楚更直观

在使用 IN 时，计算次序更容易管理

IN 操作符一般比 OR 操作符清单执行的更快

IN 的最大优点是可以包含其他 SELECT 语句，使得能够更动态地建立 WHERE 子句。

NOT 操作符

NOT 功能：否定他之后所跟的任何条件。

SELECT prod_name,prod_priceFROM productsWHERE vend_id NOT IN (1002,1003)ORDER BY prod_name;

复制代码

创建计算字段

计算字段

存储在表中的数据都不是应用程序所需要的。我们需要直接从数据库中检索出转换计算，格式化后的数据。这就是计算字段发挥作用的所在了。计算字段并不实际存在于数据库表中。计算字段是在运行 SELECT 时语句内创建的。

拼接字段

我们需要输出这样的一个格式 name（location），但数据库中是没有这样的字段的，所以我们需要拼接列来显示一个这样的信息。

解决方法：我们使用 Concat 函数来拼接两个列

SELECT Concat(vend_name,'(',vend_country,')')FROM vendorsORDER BY vend_name;

复制代码

RTrim 函数去掉值右边的所有空格，通过使用 RTrim（）。各个列都进行了整理。

SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')')FROM vendorsORDER BY vend_name;

复制代码

LTrim 呢，显而易见就是去除串左边的空格。

Trim，去除串左右两边的空格。

刚刚的输出我们可以看到那个表头是很不美观的，所以我们可以使用别名

AS 别名

SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')') AS vend_titleFROM vendorsORDER BY vend_name;

复制代码

执行算术计算

我们可以在 SELECT 语句中就添加一些算数计算，以计算一些列的值。

SELECT prod_id,quantity,item_price,quantity*item_price AS expanded_priceFROM orderitemsWHERE order_num = 20005;

复制代码

使用数据处理函数

函数

函数没有 SQL 的可移植性强

使用函数

文本处理函数

:mag:Upper（）函数:将文本转化为大写

SELECT vend_name ,Upper(vend_name) AS vend_name_upcaseFROM vendorsORDER BY vend_name;

复制代码

常用的文本处理函数

日期和时间处理函数

常用日期和时间处理函数

SELECT cust_id,order_numFROM ordersWHERE Date(order_date) = '2005-09-01';

复制代码

加 Date（）是一个好习惯，即使你知道表里只有日期，但是这样的方式可以万无一失，万一以后表里加入时间，也不会出现匹配不到的错误。

例:如果你想检索出 2005 年 9 月下的所有订单。

法一：

SELECT cust_id,order_numFROM ordersWHERE Date(order_date) BETWEEN '2005-09-01'AND '2005-09-30';

复制代码

法二：

SELECT cust_id ,order_numFROM ordersWHERE Year(order_date) = 2005 AND Month(order_date) = 9;

复制代码

数值处理函数

常用的数值处理函数

汇总数据

聚集函数

聚集函数：运行在行组上，计算和返回单个值的函数

SQL 聚集函数

AVG 函数

例：返回 products 表中所有产品的平均价格：

SELECT  AVG(prod_price) AS avg_priceFROM products;

复制代码

AVG 函数忽略列值为 NULL 的行

COUNT 函数

确定表中行的数目或符合特定条件的行的数目。

例：返回 customers 表中客户的总数

SELECT COUNT(*) AS num_custFROM customers;

复制代码

例：只对具有电子邮件的客户计数

SELECT COUNT(cust_email) AS num_custFROM customers;

复制代码

MAX 函数

SELECT MAX(prod_price) AS max_priceFROM products;

复制代码

用于文本数据时，如果数据按相应的列排序，则 max 返回最后一行。

MAX 函数忽略列值为 NULL 的行

MIN 函数

MIN 函数与 MAX 函数正好相反

SUM 函数

sum 用来返回指定列值的和

例：检索所订购的物品的总数。

SELECT SUM(quantity) AS items_orderedFROM orderitemsWHERE order_num = 20005;

复制代码

SUM 还可以与之前的计算字段进行联动

例：求总的订单金额

SELECT SUM(item_price*quantity) AS total_priceFROM orderitemsWHERE order_num = 20005;

复制代码

聚集不同值

下面介绍聚集函数的 DISTINCT 的使用，mysql4.x 是不能正常使用的。

SELECT AVG(DISTINCT prod_price) AS avg_priceFROM productsWHERE vend_id = 1003;

复制代码

DISTINCT 不能用于 COUNT（*）。

组合聚集函数

SELECT COUNT(*)  AS num_items,        MIN(prod_price) AS price_min,        MAX(prod_price) AS price_max,        AVG(prod_price) AS price_avgFROM products;

复制代码

分组数据

数据分组

分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算。

创建分组

分组是在 SELECT 语句的 GROUP BY 子句中建立的。

例：

SELECT vend_id ,COUNT(*) AS num_prodsFROM productsGROUP BY vend_id;

复制代码

重要规定

GROUP BY 子句可以包含任意数目的列，这使得能对分组进行嵌套，为数据分组提供更细致的控制。
如果在 GROUP BY 子句中嵌套了分组，数据将会在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算。（所以不能从个别列取回数据）
GROUP BY 子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果 SELECT 中使用表达式，则必须在 GROUP BY 子句中指定相同的表达式，不能使用别名
除聚集计算语句外，SELECT 语句中的每一列都必须在 GROUP BY 子句中给出
如果分组列中有 null 值，则 null 将作为一个分组返回，如果有多行 null 值，他们将分为一个组
GROUP BY 子句必须在 WHERE 子句之后，ORDER BY 子句之前。

使用 ROLLUP 关键字

SELECT vend_id ,COUNT(*) AS num_prodsFROM productsGROUP BY vend_id WITH ROLLUP;

复制代码

过滤分组

HAVING 非常类似 WHERE。事实上，目前为止的所学习的所有类型的 WHERE 子句均可用 HAVING 来代替。唯一的区别在于 HAVING 过滤分组，WHERE 过滤行

SELECT cust_id ,COUNT(*) AS ordersFROM ordersGROUP BY cust_idHAVING COUNT(*)>=2;

复制代码

HAVING 和 WHERE 的区别：

WHERE 是在数据分组前进行过滤，HAVING 是在数据分组后进行过滤。这是一个重要区别，WHERE 排除的行不包括在分组中。这可能改变计算值，从而影响 HAVING 子句中基于这些值过滤掉的分组。

例：列出具有 2 个以上，价格为 10 以上的产品的供应商

SELECT vend_id,COUNT(*) AS num_prodsFROM productsWHERE prod_price >= 10GROUP BY vend_idHAVING COUNT(*) >= 2;

复制代码

分组和排序

虽然 GROUP BY 和 ORDER BY 经常完成相同的工作，但是他们是非常不同的。

SELECT order_num ,SUM(quantity*item_price) AS ordertotal FROM orderitemsGROUP BY order_numHAVING SUM(quantity*item_price) >= 50ORDER BY ordertotal;

复制代码

SELECT 子句顺序

SELECT 子句及其顺序

使用子查询

子查询

版本要求：MySQL4.1 引入了对子查询的支持，所以要想使用本章描述的 mysql 必须使用 4.1 更高的版本。

利用子查询进行过滤

例：列出订购物品 TNT2 的所有客户的客户信息。

SELECT cust_name , cust_contactFROM customersWHERE cust_id IN (SELECT cust_id                  FROM orders                  WHERE order_num IN (SELECT order_num                               FROM orderitems                              WHERE prod_id = 'TNT2') );

复制代码

虽然子查询一般与 IN 操作符结合使用，但也可以用于测试等于(=)，不等于（<>）

作为计算字段使用子查询

例：显示 customers 表中每个客户的订单总数

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

复制代码

联结表

联结

SQL 强大的功能之一就是可以在数据检索查询的执行中联结。

关系表

我们设计两个表，一个供应商表，一个商品表，供应商表的主键标识就是商品表的外键。

这样关系数据可以有效的存储和方便的处理，他的可伸缩性是要远远好于非关系数据库。

可伸缩性
能够不断适应增加的工作量而不失败，设计良好的数据库或应用程序称之为可伸缩性好。

为什么使用联结

就像上面说的，你把数据分解到多个数据表这是有代价的，如果你想要的数据在多个数据表中你要怎么办呢？

答案就是使用联结。

创建联结

SELECT vend_name ,prod_name,prod_priceFROM vendors,productsWHERE vendors.vend_id = products.vend_idORDER BY vend_name,prod_name;

复制代码

这里要完全限定列名，不然 MySQL 可处理不了这种充满二义性的 WHERE 语句。

WHERE 子句的重要性

在联结两个表的时候。实际是将第一个表的每一行，与第二个表的每一行配对，WHERE 子句作为过滤条件，他只包含哪些匹配给定条件的行。没有 WHERE 子句，返回的信息将是特别特别多的呀。

笛卡尔积
由于没有连接条件的表关系返回的结果为笛卡尔积。检索出的行的数目

内部联结

目前为止的所有连接都是等值连接，他是基于两个表之间的相等测试。这种连接称为内部联结。其实，这种连接是原有另外一种写法的。（这种写法可以明确指定连接的类型）

SELECT vend_name ,prod_name ,prod_priceFROM vendors INNER JOIN productsON vendors.vend_id = products.vend_id;

复制代码

连接多个表

SELECT prod_name ,vend_name ,prod_price ,quantityFROM  orderitems ,products, vendorsWHERE products.vend_id = vendors.vend_id      AND orderitems.prod_id = products.prod_id      AND order_num = 20005;

复制代码

mysql 处理联结表是特别浪费性能的，所以我们要仔细，不要联结不必要的表。联结的表越多，性能的下降越厉害。

我们可以把之前的一个 SELECT 拿出来搞一个一题多解。

SELECT cust_name , cust_contactFROM customersWHERE cust_id IN (SELECT cust_id                  FROM orders                  WHERE order_num IN (SELECT order_num                               FROM orderitems                              WHERE prod_id = 'TNT2') );

复制代码

我们试试联结的方法

SELECT cust_name , cust_contactFROM customers , orders, orderitemsWHERE customers.cust_id = orders.cust_id        AND orderitems.order_num = orders.order_num        AND prod_id = 'TNT2';

复制代码

创建高级联结

使用表别名

这是我们之前使用到的表别名的例子

SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')') AS vend_titleFROM vendorsORDER BY vend_name;

复制代码

别名除了用于列名和计算字段之外，SQL 还允许给表名起列名。主要两个原有、

缩短 SQL 语句
允许在单条 SELECT 语句中多次使用相同的表

那么我们就来试一试这个别名

SELECT cust_name,cust_contactFROM customers AS c ,orders AS o ,orderitems AS oiWHERE c.cust_id = o.cust_id        AND oi.order_num = o.order_num        AND prod_id = 'TNT2';

复制代码

使用不同类型的联结

直到现在，我们使用的都是内部联结或者等值联结的简单联结，下面我们要继续向前通关了呀。

自联结

例：你发现某物品存在问题，因此想知道生产该物品的供应商生产的其他物品是否也有这些问题。

SELECT  p1.prod_id , p2.prod_nameFROM products AS p1,products AS p2WHERE p1.vend_id = p2.vend_idAND p2.prod_id = 'DTNTR';

复制代码

使用别名，我们就可以用自联结而不是子查询。有时候会快的多。

自然联结

迄今为止，我们建立的每一个内部联结都是自然联结，很可能我们永远都不会用到不是自然联结的内部联结。

外部联结

联结包含哪些在相关表中没有关联行的行，这种类型的联结称为外部联结。

例：内部联结:

SELECT customers.cust_id ,orders.order_numFROM customers INNER JOIN ordersON customers.cust_id  = orders.cust_id;

复制代码

例：外部联结：

SELECT customers.cust_id ,orders.order_numFROM customers LEFT OUTER JOIN ordersON customers.cust_id  = orders.cust_id;

复制代码

那个 10002 对应的 null 就很能说明问题，因为你是 left 联结，所以左边表是要包含全部内容的，所以就会有 10002 对应的 null 这样一个信息，因为你左联结了，所以左边表的所有行都要出现。

mysql 不支持一个*=的操作符，这在其他的 dbms 里是十分受欢迎的。

使用带聚集函数的联结

例：检索所有客户以及每个客户所下的订单数

SELECT customers.cust_name,        customers.cust_id,        COUNT(orders.order_num) AS num_ordFROM customers INNER JOIN  ordersON customers.cust_id = orders.cust_idGROUP BY customers.cust_id;

复制代码

下面我们用一用这个左连接，把那些没有下单的客户也揪出来

SELECT customers.cust_name,        customers.cust_id,        COUNT(orders.order_num) AS num_ordFROM customers LEFT OUTER JOIN  ordersON customers.cust_id = orders.cust_idGROUP BY customers.cust_id;

复制代码

例：检索所有客户及每个客户所下的订单数

SELECT customers.cust_name,        customers.cust_id,        COUNT(orders.order_num) AS num_ordFROM customers INNER JOIN ordersON customers.cust_id = orders.cust_idGROUP BY customers.cust_id;

复制代码

组合查询

MySQL 也允许执行多个查询（多条 SELECT 语句），并将结果作为单个查询结果集返回。这些组合查询通常称为并或复合查询。

创建组合查询

可以使用 union 操作符来组合数条 SQL 查询。利用 union，可给出多条 SELECT 语句，将他们的结果组合成单个结果集。

使用 union

union 的使用其实是很简单的，所需要你做的就是给出每条 SELECT 语句，在各条语句之间放上关键字 union。

你比如说你像找价格小于等于 5 的所有物品，还想找供应商是 1001 和 1002 生产的所有物品，当然你写两个 SELECT 语句必然能解决这一问题，但是那就不是一个结果集了呀，所以你可以使用 union。

SELECT vend_id ,prod_id,prod_priceFROM productsWHERE prod_price <= 5UNIONSELECT vend_id ,prod_id,prod_priceFROM productsWHERE vend_id IN (1001,1002);

复制代码

呐。union 的使用是不是很简单呢。

当然上面这个例子我们使用 WHERE 子句加 or 也是可以完成这个任务的。但是对于更加复杂的过滤条件，或者从多个表中检索数据的情形，使用 union 可能会使处理更简单。

union 规则

union 的使用虽然是很简单的，但是还是有一些需要注意的地方

union 必须由两条或两条以上的 SELECT 语句组成，语句之间用 union 关键词分隔。
union 中每个查询必须包含相同的列，表达式或聚集函数（不过每个列不需要以相同的次序出现）
列数据的类型必须兼容：类型不必完全相同，但是必须是 DBMS 可以隐含转换的类型。

包含或取消重复的行

union 是有一个默认行为的，就是虽然是两个 SELECT 语句但是对于重复的行，它是会自动去除掉的。

既然是默认行为就能改变，我们使用 union all 关键词就可以返回所有匹配行。

对组合查询结果排序

union 只需要在最后的一条 select 语句上写 order by。就可以对整个结果集进行排序。

全文本搜索

发布于: 刚刚阅读数: 5

原文链接:【http://xie.infoq.cn/article/b03c06e65566e4048b04ae2c5】。文章转载请联系作者。

阿柠xn

关注

还未添加个人签名 2022.08.29 加入

还未添加个人简介

发布

暂无评论

创作场景

MySQL 必知必会 - 检索数据

检索数据

select 语句

检索单个列

检索多个列

检索所有列

检索不同行

限制结果

使用完全限定的表明

排序检索数据 （ORDER BY）

排序数据

按多个列排序

指定排序方向 （DESC 降序）

过滤数据 （WHERE 子句）

使用 WHERE 子句

WHERE 子句操作符

数据过滤

组合 WHERE 子句

AND 操作符

OR 操作符

AND 的计算次序优先级更高

IN 操作符

NOT 操作符

创建计算字段

计算字段

拼接字段

AS 别名

执行算术计算

使用数据处理函数

函数

使用函数

文本处理函数

日期和时间处理函数

数值处理函数

汇总数据

聚集函数

AVG 函数

COUNT 函数

MAX 函数

MIN 函数

SUM 函数

聚集不同值

组合聚集函数

分组数据

数据分组

创建分组

过滤分组

分组和排序

SELECT 子句顺序

使用子查询

子查询

利用子查询进行过滤

作为计算字段使用子查询

联结表

联结

关系表

为什么使用联结

创建联结

WHERE 子句的重要性

内部联结

连接多个表

创建高级联结

使用表别名

使用不同类型的联结

自联结

自然联结

外部联结

使用带聚集函数的联结

组合查询

组合查询

创建组合查询

使用 union

union 规则

包含或取消重复的行

对组合查询结果排序

全文本搜索

阿柠xn

评论

排序检索数据（ORDER BY）

指定排序方向（DESC 降序）

过滤数据（WHERE 子句）