SQL系列1-检索过滤处理汇总数据

什么是SQL？

SQL（发音为字母 S-Q-L 或 sequel）是 Structured Query Language（结构化查询语言）的缩写。SQL 是一种专门用来与数据库沟通的语言。

本学习过程中用的数据库管理系统（DBMS）为Mysql，图形化工具为MySQL Workbench。

查询与排序

在此过程中的样例表以及数据可下载：博客后台 - 博客园 (cnblogs.com)

注释

1.--+空格

2.#

3./*
cccc
*/

SQL关键字的语法顺序：

1. select[distinct]
2. from
3. join（如left join）
4. on
5. where
6. group by
7. having
8. union
9. order by
10. limit

DISTINCT

distinct在列名之前

SELECT DISTINCT vend_id, prod_price

DISTINCT 关键字作用于所有的列，不仅仅是跟在其后的那一列。以上vend_id, prod_price都会被影响并只查出唯一值。

vend_id 3个不重复值 ，prod_price6个不重复值，则查询结果有6条结果，以prod_price为主。

limit

SELECT 语句返回指定表中所有匹配的行，很可能是每一行。如果你只想返回第一行或者一定数量的行。

限制结果：限制查询的结果，不想全查出来

只查某几条如下：

这里解释为什么order by ，因为如果不按顺序排好的话不加limit关键字的后4条，和用limit关键字的后四条不完全相同。

order by

1关系数据库设计理论认为，如果不明确规定排序顺序，则不应该假定检索出的数据的顺序有任何意义

2order by如果不是在sql语句的最后，会报错。order by某一英文列是按 1，2，3.。。。A-Z

3按多个列排序

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

prod_price有相同的时候按 prod_name排序。

过滤

数据库表一般包含大量的数据，很少需要检索表中的所有行。通常只会根据特定操作或报告的需要提取表数据的子集。只检索所需数据需要指定搜索条件（search criteria），搜索条件也称为过滤条件（filter condition）。在 SELECT 语句中，数据根据 WHERE 子句中指定的搜索条件进行过滤。WHERE 子句在表名（FROM 子句）之后给出。

where

SELECT prod_name, prod_price
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01'
 AND prod_price >= 10;
 
 SELECT prod_name, prod_price
FROM Products
WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01')
 AND prod_price >= 10;

AND 比 OR在有更高的优先级，在处理 OR 操作符前，优先处理 AND 操作符

between

要检查某个范围的值，可以使用 BETWEEN 操作符。其语法与其他 WHERE子句的操作符稍有不同，因为它需要两个值，即范围的开始值和结束值。

例如，BETWEEN 操作符可用来检索价格在 5 美元和 10 美元之间的所有产品，或在指定的开始日期和结束日期之间的所有日期。

SELECT prod_name, prod_price
FROM Products
WHERE prod_price BETWEEN 5 AND 10;

空值检查

SELECT prod_name
FROM Products
WHERE prod_price IS NULL;

NOT

WHERE 子句中的 NOT 操作符有且只有一个功能，那就是否定其后所跟的任何条件。因为 NOT 从不单独使用（它总是与其他操作符一起使用），所以它的语法与其他操作符有所不同。NOT 关键字可以用在要过滤的列前，而不仅是在其后。

在与 IN 操作符联合使用时，NOT 可以非常简单地找出与条件列表不匹配的行。

Like

以上所有操作符都是针对已知值进行过滤的。不管是匹配一个值还是多个值，检验大于还是小于已知值，或者检查某个范围的值，其共同点是过滤中使用的值都是已知的

可以构造一个通配符搜索模式，找出在产品名的任何位置出现”XXX“的产品

通配符搜索只能用于文本字段（字符串），非文本数据类型字段不能使用通配符搜索

通配符有：

%通配符

最常使用的通配符是百分号（%）。在搜索串中，%表示任何字符出现任意次数。例如，为了找出所有以词 Fish 起头的产品，可写以下的 SELECT 语句：

SELECT prod_id, prod_name 
FROM Products 
WHERE prod_name LIKE 'Fish%';

%告诉 DBMS 接受 Fish 之后的任意字符，不管它有多少字符。给定位置的 0 个、1 个或多个字符

通配符可在搜索模式中的任意位置使用，并且可以使用多个通配符。下面的例子使用两个通配符，它们位于模式的两端

SELECT prod_id, prod_name 
FROM Products 
WHERE prod_name LIKE '%bean bag%';

通配符也可以出现在搜索模式的中间，虽然这样做不太有用。下面的例

子找出以 F 起头、以 y 结尾的所有产品

SELECT prod_name
FROM Products
WHERE prod_name LIKE 'F%y';

下划线通配符_

与%能匹配多个字符不同，_总是刚好匹配一个字符，不能多也不能少

SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '__ inch teddy bear';

方括号（[ ]）通配符

方括号（[]）通配符用来指定一个字符集，它必须匹配指定位置（通配符的位置）的一个字符

找出所有名字以 J 或 M 起头的联系人，可进行如下查询

SELECT cust_contact 
FROM Customers 
WHERE cust_contact LIKE '[JM]%' 
ORDER BY cust_contact;

此通配符可以用前缀字符^ （脱字号）来否定。例如，下面的查询匹配以J 和 M 之外的任意字符起头的任意联系人名（与前一个例子相反）

SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[^
JM]%'
ORDER BY cust_contact;

总结：SQL 的通配符很有用。但这种功能是有代价的，即通配符搜索一般比前面讨论的其他搜索要耗费更长的处理时间。这里给出一些使用通配符时要记住的技巧

 不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用

其他操作符。

 在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始

处。把通配符置于开始处，搜索起来是最慢的。

 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据

创建字段

存储在数据库表中的数据一般不是应用程序所需要的格式，下面举几个例子：

需要显示公司名，同时还需要显示公司的地址，但这两个信息存储在

不同的表列中。

 城市、州和邮政编码存储在不同的列中（应该这样），但邮件标签打

印程序需要把它们作为一个有恰当格式的字段检索出来。

 列数据是大小写混合的，但报表程序需要把所有数据按大写表示出来。

 物品订单表存储物品的价格和数量，不存储每个物品的总价格（用价

格乘以数量即可）。但为打印发票，需要物品的总价格。

 需要根据表数据进行诸如总数、平均数的计算。

在上述每个例子中，存储在表中的数据都不是应用程序所需要的。我们需要直接从数据库中检索出转换、计算或格式化过的数据，而不是检索出数据，然后再在客户端应用程序中重新格式化，这就是计算字段可以派上用场的地方了。与前几课介绍的列不同，计算字段并不实际存在于数据库表中。计算字段是运行时在 SELECT 语句内创建的。

需要特别注意，只有数据库知道 SELECT 语句中哪些列是实际的表列，哪些列是计算字段。从客户端（如应用程序）来看，计算字段的数据与其他列的数据的返回方式相同。在 SQL 语句内可完成的许多转换和格式化工作都可以直接在客户端应用程序内完成。但一般来说，在数据库服务器上完成这些操作比在客户端中完成要快得多。

注意。这里mysql不能用这个‘+’，需要专门的函数

SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
FROM Vendors
ORDER BY vend_name;

MySQL的语法如下：

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

函数

与大多数其他计算机语言一样，SQL 也可以用函数来处理数据。函数一般是在数据上执行的，为数据的转换和处理提供了方便。前一课中用来去掉字符串尾的空格的 RTRIM()就是一个函数。

与几乎所有 DBMS 都等同地支持 SQL 语句（如 SELECT）不同，每一个DBMS 都有特定的函数。事实上，只有少数几个函数被所有主要的 DBMS等同地支持。虽然所有类型的函数一般都可以在每个 DBMS 中使用，但各个函数的名称和语法可能极其不同。为了说明可能存在的问题，表 8-1列出了 3 个常用的函数及其在各个 DBMS 中的语法：

关于你的 DBMS （Mysql）具体支持函数，请参阅相应的文档

文本函数

SELECT vend_name, UPPER(vend_name) AS vend_name_upcase
FROM Vendors
ORDER BY vend_name;

时间函数

SELECT order_num
FROM Orders
WHERE YEAR(order_date) = 2020;

检索 2020 年的所有订单

数值函数

聚集函数

我们经常需要汇总数据而不用把它们实际检索出来，为此 SQL 提供了专门的函数。使用这些函数，SQL 查询可用于检索数据，以便分析和报表生成。这种类型的检索例子有：

 确定表中行数（或者满足某个条件或包含某个特定值的行数）；

 获得表中某些行的和；

 找出表列（或所有行或某些特定的行）的最大值、最小值、平均值。

上述例子都需要汇总出表中的数据，而不需要查出数据本身。因此，返回实际表数据纯属浪费时间和处理资源（更不用说带宽了）。再说一遍，我们实际想要的是汇总信息。

说明：聚集函数是对某些行运行的函数，计算并返回一个值。 数值函数是对一个值进行操作。

AVG()函数

下面的例子使用 AVG()返回 Products 表中所有产品的平均价格：

SELECT AVG(prod_price) AS avg_price
FROM Products;

AVG()也可以用来确定特定列或行的平均值。下面的例子返回特定供应

商所提供产品的平均价格：

SELECT AVG(prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';

SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';

只统计唯一值的平均值

COUNT()函数

COUNT()函数有两种使用方式：

 使用 COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值

（NULL）还是非空值。

 使用 COUNT(column)对特定列中具有值的行进行计数，忽略 NULL 值。

SELECT COUNT(cust_email) AS num_cust
FROM Customers;

这条 SELECT 语句使用 COUNT(cust_email)对 cust_email 列中有值的行进行计数。在此例子中，cust_email 的计数为 3（表示 5 个顾客中只有 3 个顾客有电子邮件地址）。

MAX()函数

MAX()一般用来找出最大的数值或日期值

MAX()返回指定列中的最大值。MAX()要求指定列名，如下所示：

SELECT MAX(prod_price) AS max_price
FROM Products;

MIN()函数

SELECT MIN(prod_price) AS min_price
FROM Products;

SUM()函数

SELECT SUM(quantity) AS items_ordered
FROM OrderItems
WHERE order_num = 20005;

SUM()也可以用来合计计算值。在下面的例子中，合计每项物品的item_price*quantity，得出2005这个订单编号下面总的订单金额

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

聚集函数都可用来执行多个列上（item_price*quantity）的计算

组合聚集函数

SELECT COUNT(*) AS num_items,
 MIN(prod_price) AS price_min,
 MAX(prod_price) AS price_max,
 AVG(prod_price) AS price_avg
FROM Products;

其他

本栏目推荐文章
• Jmeter 如何连接mysql数据库？
• SQL Server 检测是不是数字型数据的两种方法
• 如何通过Navicat把MySQL远程数据库导入到本地数据库
• 和利时MACS6通过dat文件实现任意时间范围与测点的历史数据提取与科研分析
• 深入理解spring框架：剖析多线程模式下数据库连接
• SQL Join的一些总结
• SQL Server 高性能写入的一些总结
• 网络攻击技术开篇——SQL Injection
• 数据可视化是如何帮助大型企业提高效率的？
• redis 数据库

数据 SQL数据sql 20%数据sql teamcenter数据sql sqlserver数据sql 数据表 数据sql 语句sql数据update 数据库 数据sql 语句navicat数据sql 数据server sql 语句sqlserver数据sql