SQL-- 检索、排序、过滤、通配符、函数、汇总、分组

客官°小女子只卖身不卖艺 2024-02-18 17:40 100阅读 0赞

**一：检索数据：**

1、 **DISTINCT**关键字使返回的结果具有唯一性，该关键字必须放在列名的前面  
如：SELECT DISTINCT vend\_id,prod\_price FROM products; 该关键字对所有选定的关键字起作用，即例中ven\_id、prod\_price与其他记录完全相同

2、 限制返回结果：

（1）在sql server 和Access 数据库中通过top 关键字限制最多返回多少行：  
如：SELECT **top 5** prod\_name FROM products;

（2）DB2数据库：

如：SELECT prod\_name FROM products **FETCH FIRST ROWS 5 ONLY**;

（3）oracle数据库使用ROWNUM（行计数器）来计算：

如：SELECT prod\_nameFROM products WHERE **ROWNUM <= 5**;

（4）MySQL 、MariaDB、POstgreSQL、SQLite等数据库使用limit子句：  
如：SELECT prod\_name FROMproducts **LIMIT 5**;

\*\*\*SELECT prod\_name FROM products **LIMIT 5 OFFSET 5**; 返回从第五行起的记录

对于LIMIT 4 OFFSET 3可以简化成：LIMIT 3,4。

注意**：**  检索是从第0行开始的，LIMIT 4OFFSET 3中的3表示检索的是第四行而不是第三行。

**二、排序检索数据：关键字（order by）**

1、例：SELECT prod\_name FROM products ORDER BY prod\_name;

ORDER BY 子句的位置一定保证是SELECT 的最后一条子句，否则出错。

2、按照多个列排序：  
例：SELECTprod\_name,prod\_price FROM products ORDER BY prod\_price,prod\_name; 先按照prod\_price 升序，如果相同在按照prod\_name排序。

3、按列位置排序：  
例：SELECTprod\_name,prod\_price FROM products ORDER BY 2,1;

ORDER BY 2,1表示先按照prod\_price排序再按照prod\_name排序。

注意：orderby 后面的字段不一定是要选择的字段。

4、指定排序方向（ASC、DESC）

例：SELECTprod\_name,prod\_price FROM products ORDER BY prod\_price,prod\_name DESC; 默认为升序(ASC)

**三：过滤数据：WHERE子句**指定搜索条件

1、SE LECT prod\_name,prod\_price FROM products WHERE prod\_price=4.490;

同时使用where和order by 子句时，order by 子句位于 where 子句之后

2、检查单个值

SELECT prod\_name,prod\_price FROM products WHERE prod\_price<4.490;

3、检查不匹配项：

SELECT prod\_name,prod\_price FROMproducts WHERE prod\_id<>'FB'; （不等于 ，和 !=效果相同）

4、检查空值 （IS NULL）

SELECT cust\_name FROM customers WHEREcust\_email IS NULL;

**四、高级过滤：AND 、OR、NOT、IN操作符**

1、AND和OR都属于多条件过滤。

2、IN 操作符的效果和 OR一样：

例：SELECTprod\_name,prod\_price FROM products WHERE vend\_id **IN**('1001','1002') ORDER BY prod\_name;

3、NOT操作符：

例：SELECTprod\_name,prod\_price FROM products WHERE **NOT**vend\_id = '1001' ORDER BY prod\_name; 否定后面的条件，也可以用<>、!=

4、NOT IN:

例：SELECTprod\_name,prod\_price FROM products WHERE vend\_id **NOT IN**('1001','1002') ORDER BY prod\_name;

**五、使用通配符（wildcard）进行过滤：（LIKE操作符）**

1、通配符使用LIKE操作符:用于指示DBMS，其后跟的是通配符匹配而不是简单的相等匹配；只能用于搜索文本字段，即字符串类型的字符。

2、百分号（%）通配符： %表示任何字符出现任意次数。（Access数据库中使用的是\*）  
例（1）：SELECT prod\_id,prod\_name FROM products WHEREprod\_name **LIKE 's%'**; 表示以s开头的任意字符串。

（2）：’%s%’：表示匹配任何位置上包含文本s的字符串，包括最前或者最后，即（slike,loves）。

SELECT prod\_id,prod\_name FROM products WHERE prod\_name **LIKE '%s%'**;

（3）：’c%s’：表示检索以c开头,以s结尾的字符串。

SELECT prod\_id,prod\_name FROM productsWHERE prod\_name **LIKE 'c%s'**;

注意：有些数据库对于未达到指定字段长度，采取在其后添加空格的形式（如Access数据库）；这种情况下 ，以c开头，以s结尾的字符串无法被检索出来，可以写成’c%s%’的形式。

注意：%除了能匹配一个或多个字符外，还能匹配0个字符 ；%代表搜索模式中的0个、1个、多个字符。%看似可以匹配所有东西，但是有一个例外：不能匹配 NULL，即 LIKE ‘%’ 的结果不会被检索出来。

3、下划线（\_）通配符：只能匹配单个字符，不能多也不能少：  
例：SELECTprod\_id,prod\_name FROM products WHERE prod\_name LIKE '\_afe'; 匹配以任意单个字符开头afe结尾的字符串。

**不同数据库：**DB2不支持\_通配符，而Access数据库的是?而不是 \_

4、方括号（\[\]）通配符：用来指定一个字符集，匹配指定位置上的一个字符

不是所有DBMS都支持 \[\] 通配符，只用微软的Access 和 SQL Server支持集合。

**通配符使用总结**：通配符搜索比其他搜索方式花费的时间更长，因此不要过度的使用通配符，如果其他操作符能达到相同的目的，应该使用其他操作符。

在确实需要使用到通配符的时候，尽量不要用到开始处，将通配符置于开始位置处搜索起来是最慢的。

**六、创建计算字段；**

1、为什么要创建字段？数据库表中存储的字段值通常不是应用程序所需求的格式。例如：

（1）城市、州、邮政编码通常不是在同一个字段，但邮件标签打印程序需要把它们作为一个恰当的字段检索出来。

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

计算字段并不存在于实际的数据库表当中，计算字段是运行时在SELECT语句内创建的。

注意：客户端与服务器的格式：在SQL语句内可以完成的许多转换和格式化工作都可以在客户端应用程序上完成；单一般来说，在数据库服务器上完成这些操作比在客户端完成要快的多。

2、拼接字段：

\*\*\*拼接（concatenate）：将值连接在一起，构成单个值。在SQL的SELECT语句中可以使用特使的操作符（+）或者（||），对于MySQL和MariaDB数据库来说，必须使用特殊函数。

\*\*\*Access和SQL Server使用 + 号，DB2 、Oracle 、PostgreSQL、SQLite和Open Office Base使用||号。

例（1）SELECT vend\_name+'('+vend\_country+')' FROMvendors ORDER BY vend\_name;

（2）SELECTvend\_name||'('||vend\_country||')' FROM vendors ORDER BY vend\_name;

这两种SELECT语句返回的结果前后带有空格，可以通过**TRIM()**函数去掉不必要的空格。

\*\*\*SELECTRTRIM(vend\_name) + '('+RTRIM(vend\_country) + ')' FROM vendors ORDER BYvend\_name;

\*\*\*SELECTRTRIM(vend\_name) || '('||RTRIM(vend\_country) || ')' FROM vendors ORDER BYvend\_name;

其中 RTRIM()、LTRIM()、TRIM()分别表示去掉字符串右边空格、字符串左边空格和字符串左右空格。

（3）对于 MySQL 来说需要使用函数连接Concat()：

SELECTCONCAT(vend\_name,'(',vend\_country,')') FROM vendors ORDER BY vend\_name;

3、使用别名：使用**AS**关键字  
例：SELECTCONCAT(vend\_name,'(',vend\_country,')') AS name\_contry FROM vendors ORDER BYvend\_name;

4、执行算术计算：

例：SELECTprod\_id,quantity,item\_price,quantity\*item\_price AS totalPrice FROM orderitemsWHERE order\_num = 20009;

**七、使用函数处理数据：**

1、函数： sql函数的出现让数据的存储、转变变得更加的方便；但是也要注意其带来的问题，不同的DBMS实现相同的功能的函数可能不一样，所以sql函数不同于sql语句，不能移植。大多数sql实现支持以下实现：

（1）用于处理文本字符串（如删除、填充值，转换值为大写或者小写）的文本函数。

（2）用于在数值上进行算术操作（如返回绝对值，进行代数运算）的数值函数。

（3）用于处理日期和时间值并从这些值中提取特定的成分（如返回连个日期之差，检查日期有效性）的日期和时间处理函数。

2、文本处理函数（字符串处理函数）

（1）UPPER(str):

例：SELECTvend\_name,**UPPER**(vend\_name) AStoUpper FROM vendors ORDER BY vend\_name; 作用是将小写字母转换为大写

（2）常用的文本处理函数：

LEFT() : 返回字符串左边字符

LENGTH(): 返回字符串的长度

LOWER(): 将字符串转换为小写（Access使用LCASE()）

LTRIM(): 去掉字符串左边空格

RTRIM() : 去掉字符串右边空格

SOUNDEX(): 返回字符串的SOUNDEX值

UPPER()： 返回字符串的大写值（Access使用UCASE()）

对于 SOUNDEX的说明，将任何文本字符串转换为描述其语音表示的字母数字模式的算法；即按照发音字符和音节，对比字符串的发音而不是字母。

其中Microsoft Access、PostgreSQL不支持SOUNDEX。

例：SELECT cust\_name,cust\_contact FROM customers WHERE **SOUNDEX**(cust\_contact) = **SOUNDEX**('Michael Green');

3、日期和时间处理函数：

不同的DBMS的处理函数很不一致：

如：（1）SQL Server中: SELECT order\_num FROM orders WHERE **DATEPART**(yy,order\_date) = 2012;

（2）Access中：SELECT order\_num FROM orders WHERE **DATEPART**('yyyy',order\_date) = 2012;

（3）PostgreSQL版本中：SELECT order\_num FROM orders WHERE **DATE\_PART**('year',order\_date) = 2012;

（4）Oracle中：SELECT order\_num FROM orders WHERE **to\_number**(**to\_char**(order\_date,'yyyy')) = 2012; 其中to\_char()是用来提取日期的成分，to\_number用来将提取的成分转换为数值，一遍能与2012比较。

（5）MySQL和MariaDB中：SELECT order\_num FROM orders WHERE **YEAR**(order\_date) = 2012;

4、数值处理函数：  
常用数值处理函数：

ABS() : 返回一个数的绝对值

COS(): 返回一个角度的余弦

EXP(): 返回一个数的指数值

PI()： 返回圆周率

SIN(): 返回一个角度的正弦

SQRT(): 返回一个数的平方

TAN(): 返回一个角度的正切

**八、汇总数据：**

1、聚集函数（aggregate function）:常见的聚集函数有：

AVG()、COUNT()、MAX()、MIN()、SUM()

（1） AVG()函数：只用于单个列，而且列名必须作为函数参数给出；为了获得多个列的平均值，必须使用多个AVG()函数；同时AVG()函数忽略列值为NULL的行。

例：SELECTAVG(prod\_price) AS avg\_Price FROM products;

SELECT AVG(prod\_price) AS avg\_Price FROMproducts WHERE vend\_id='1001';

（2）COUNT()函数：有两种表示

\*\*\*COUNT(\*)计算所用行，包括是空值还是非空值

\*\*\*COUNT(column)，对特定的列中进行计算，忽略NULL值

例：SELECT COUNT(\*) FROM customers;

SELECTCOUNT(cust\_email) FROM customers;

（3）MAX()函数：一般用来返回数值或日期的最大值，但是很多DBMS允许它用来返回任意列中的最大值，包括返回文本列中的最大值。忽略列值为NULL的行

例：SELECT **MAX**(prod\_price) ASMax\_Price FROM products;

（4）MIN函数作用与MAX()函数相反：

例：SELECT **MIN**(prod\_price) ASMax\_Price FROM products;

（5）SUM()函数：将忽略列值为NULL的行

例：SELECT SUM(quantity) AS item\_orders FROM orderitems WHERE order\_num= 20005;

SELECTSUM(quantity\*item\_price) AS item\_orders FROM orderitems WHERE order\_num =20005; //也可以用来合计计算值

2、聚集不同值：

以上五个聚集函数可以使用以下两个参数：

（1） ALL，对所用行执行计算，默认情况下是ALL

（2） DISTINCT，只包含不同的值（不能在Access中使用）

例：SELECT **AVG**(**DISTINCT** prod\_price) AS avg\_price FROMproducts WHERE vend\_id = '1001';

3、组合聚集函数：select语句可以包含多个聚集函数。如：

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;

总结：SQL支持五种聚集函数，这些函数很高效，它们的返回结果一般比在客户端应用程序中计算要快的多。

**九、分组数据：GROUP BY 子句和HAVING子句**

1、以上通过聚集函数进行数据汇总是在where子句上进行的。而使用分组可以将数据分为多个逻辑组，再对每个组进行聚集计算。

2、创建分组：

例：SELECT vend\_id,COUNT(\*) AS num\_prods FROM products GROUP BY vend\_id;

结果：

GROUP BY子句的规定：

（1） 该子句可以包含任意数目的列，因而可以对分组进行嵌套，更细致地进行数据分组。

（2） 如果在GROUP BY 子句中嵌套了分组，数据将在最后指定的分组上进行汇总；换句话说，在建立分组的时候，指定的所有列都一起计算（不能从个别的列取回数据）。

（3） GROUP BY 子句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用了表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。

（4） 大多数SQL实现不允许GROUP BY列带有长度可变的数据类型（如文本或备注型字段）

（5） 除聚集计算语句外-，GROUP BY子句中的每一列都必须在SELECT语句中给出。

（6） 如果分组列中包含具有NULL值得行，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。

（7） GROUP BY子句必须出现在WHERE 子句之后，ORDER BY子句之前。

3、过滤分组：

WHERE和 HAVING的区别：WHERE过滤行而不是过滤分组；另外，WHERE在数据分组前进行过滤，而HAVING在数据分组后进行过滤。

（1） 例：SELECT cust\_id,COUNT(\*) AS orders FROM ordersGROUP BY cust\_id HAVING COUNT(\*) > 1;

输出：+-

（2） where 和 having一同使用：

例：SELECTvend\_id,COUNT(\*) AS num\_prods FROM products WHERE prod\_price >= 4 GROUP BYvend\_id HAVING COUNT(\*) >= 2;

输出：

4、SELECT子句顺序：

SELECT -> FROM -> WHERE（行级过滤） -> GROUP BY（分组说明） -> HAVING（组级过滤） -> ORDER BY（输出排序顺序）

SQL-- 检索、排序、过滤、通配符、函数、汇总、分组

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 SQL：利用 having 实现分组过滤

相关 SQL-- 检索、排序、过滤、通配符、函数、汇总、分组

相关 MySql010——检索数据：过滤数据（使用LIKE操作符结合%、_通配符）

相关 SQL （五）用通配符过滤（LIKE操作符）

相关 MYSQL回顾1-检索、排序、过滤

相关 Java8 排序、分组、统计、过滤

相关 Java List对象集合按对象属性分组、分组汇总、过滤

相关 MySQL基础（2）——检索、排序与过滤

相关 SQL学习笔记3 通配符计算字段函数汇总

相关 SQL学习笔记2 检索排序过滤