SQL学习笔记

最新推荐文章于 2022-04-18 19:46:42 发布

潘森L

最新推荐文章于 2022-04-18 19:46:42 发布

阅读量613

点赞数

分类专栏： SQL 学习笔记

本文链接：https://blog.csdn.net/weixin_44636558/article/details/107575919

版权

SQL 同时被 2 个专栏收录

2 篇文章 0 订阅

订阅专栏

学习笔记

1 篇文章 0 订阅

订阅专栏

这些笔记主要记录自SQL必知必会，都是比较简单的内容。
书本链接：
链接：https://pan.baidu.com/s/1iZxpNE0ISjOPMIn0mPihCw
提取码：m2x1

一、检索数据

1、DISTINCT

指示数据库只返回不同的值（具有唯一性）

注意：不能部分使用 DISTINCT，因为 DISTINCT 关键字作用于所有的列，不仅仅是跟在其后的那一列。

2、限制结果（limit、offset）

限制结果返回第一行或者一定数量的行。

Oracle， ROWNUM（行计数器）计算行：

SELECT prod_name FROM Products WHERE ROWNUM <=5;

MySQL， LIMIT 子句：

SELECT prod_name FROM Products LIMIT 5;

limit、offset用法：

imit n ：表示查询结果返回前n条数据

offset m ：表示跳过m条语句

limit y offset x ：表示查询结果跳过前 x 条数据，从第x+1行开始读取 y 条数据

下面两种写法都表示取2,3,4三条数据：

select* from article LIMIT 1,3

select * from article LIMIT 3 OFFSET 1

二、排序检索数据

1、order by

以字母顺序排序数据：

SELECT prod_name FROM Products ORDER BY prod_name;

注意：order by在select语句的最后一条子句。

2、按多个列排序

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

仅在多个行具有相同的 prod_price 值时才对产品按 prod_name 进行（从小到大）排序：

3、按列相对位置排序

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

ORDER BY 2 表示按 SELECT 清单中的第二个列 prod_name 进行排序。ORDER BY 2， 3 表示先按 prod_price，再按 prod_name 进行排序

4、指定排序方向

降序DESC排序。默认是升序ASC

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

三、where 过滤数据

1、查找指定的值：

SELECT prod_name, prod_price FROM Products WHERE prod_price = 3.49;

2、列出所有价格小于 10美元的产品：

SELECT prod_name, prod_price FROM Products WHERE prod_price < 10;

3、列出所有不是供应商 DLL01 制造的产品：

SELECT vend_id, prod_name FROM Products WHERE vend_id <> 'DLL01';

注意：不等于使用的是 <> 操作符

4、范围值检查：

在使用 BETWEEN 时，必须指定两个值——所需范围的低端值和高端值。这两个值必须用 AND 关键字分隔。BETWEEN 匹配范围中所有的值，包括指定的开始值和结束值。

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

5、空值检查

SELECT prod_name FROM Products WHERE prod_price IS NULL;

四、高级数据过滤

使用 AND 、OR操作符给 WHERE 子句附加条件

1、and

SELECT prod_id, prod_price, prod_name FROM Products WHERE vend_id = 'DLL01' AND prod_price <= 4;

2、or

SELECT prod_name, prod_price FROM Products WHERE vend_id = 'DLL01' OR vend_id = ‘BRS01’;

3、and和or组合

AND 优先级更高，可使用括号改变运算顺序

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

注意：任何时候使用具有 AND 和 OR 操作符的 WHERE 子句，都应该使用圆括号明确地分组操作符。

4、in

in指定条件范围，范围中的每个条件都可以进行匹配。

IN 操作符后跟由逗号分隔的合法值，这些值必须括在圆括号中。

SELECT prod_name, prod_price FROM Products WHERE vend_id IN ( 'DLL01', 'BRS01' ) ORDER BY prod_name;

IN 与 OR 功能相当。

IN操作符的优点

在有很多合法选项时，IN 操作符的语法更清楚，更直观。
在与其他 AND 和 OR 操作符组合使用 IN 时，求值顺序更容易管理。
IN 操作符一般比一组 OR 操作符执行得更快（在上面这个合法选项很少的例子中，你看不出性能差异）。
IN 的最大优点是可以包含其他 SELECT 语句，能够更动态地建立 WHERE 子句

5、not

not 否定其后所跟的任何条件

SELECT prod_name FROM Products WHERE NOT vend_id = 'DLL01' ORDER BY prod_name;

在更复杂的子句中，NOT 是非常有用的。例如，在与 IN 操作符联合使用时，NOT 可以非常简单地找出与条件列表不匹配的行。

五、用通配符进行过滤

为在搜索子句中使用通配符，必须使用 LIKE 操作符

1、百分号（%）通配符

%表示任何字符出现任意次数。% 代表搜索模式中给定位置的 0个、1个或多个字符。

找出所有以词 Fish 起头的产品：

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

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

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

注意：子句 WHERE prod_name LIKE '%'不会匹配产品名称为 NULL 的行

2、下划线（ _ ）通配符

下划线（ _ ）只匹配单个字符

3、方括号（[ ]）通配符

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

找出所有名字以 J 或 M 起头的联系人：

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

输出▼ 
cust_contact
----------------- 
Jim Jones 
John Smith 
Michelle Green

[JM]匹配方括号中任意一个字符，它也只能匹配单个字符。

此通配符可以用前缀字符^（脱字号）来否定。

的查询匹配以 J 和 M 之外的任意字符起头的任意联系人名（与前一个例子相反）：

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

使用 NOT 操作符得出类似的结果。^的唯一优点是在使用多个 WHERE 子句时可以简化语法：

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

4、通配符使用技巧

通配符搜索一般比前面讨论的其他搜索要耗费更长的处理时间。

不要过度使用通配符。
把通配符置于开始处，搜索起来是最慢的。
仔细注意通配符的位置。

六、创建计算字段

1、拼接字段

根据你所使用的 DBMS，此操作符可用加号（+）或两个竖杠（||）表示。

说明：Access和 SQL Server使用 +号。DB2、Oracle、PostgreSQL、SQLite和 Open Office Base使用 ||。

创建 vend_name 和 vend_ country 的组合值：

-- 使用加号（多数 DBMS使用这种语法）
SELECT vend_name + ' (' + vend_country + ')' FROM Vendors ORDER BY vend_name; 

--使用||：
SELECT vend_name || ' (' || vend_country || ')' FROM Vendors ORDER BY vend_name;

输出▼ 
----------------------------------------------------------- 
Bear Emporium                                (USA        )
Bears R Us                                   (USA        )
Doll House Inc.                              (USA        )
Fun and Games                                (England    )
Furball Inc.                                 (USA        )
Jouets et ours                               (France     )

去掉其中的空格RTRIM()函数：

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

输出▼ 
----------------------------------------------------------- 
Bear Emporium (USA) 
Bears R Us (USA) 
Doll House Inc. (USA) 
Fun and Games (England) 
Furball Inc. (USA) 
Jouets et ours (France)

RTRIM()：它去掉字符串右边的空格

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

TRIM()：去掉字符串左右两边的空格

使用别名

别名（alias）用 AS 关键字赋予。

提示：Oracle 中没有 AS

2、算术计算

SELECT prod_id,quantity,item_price,quantity*item_price AS expanded_price FROM OrderItems WHERE order_num = 20008;

expanded_price 列是一个计算字段，此计算为 quantity* item_price

虽然 SELECT 通常用于从表中检索数据，但是省略了 FROM 子句后就是简单地访问和处理表达式，例如 SELECT 3 * 2;将返回6，SELECT Trim(’ abc '); 将返回 abc，SELECT Now();使用 **Now()**函数返回当前日期和时间。

七、使用函数处理数据

1、常用的文本处理函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WBpVmtFJ-1595646886868)(C:\Users\Pan\AppData\Roaming\Typora\typora-user-images\1595173300736.png)]

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

--输出
vend_name                       vend_name_upcase 
---------------------------     ---------------------------- 
Bear Emporium                   BEAR EMPORIUM
Bears R Us                      BEARS R US
Doll House Inc.                 DOLL HOUSE INC.
Fun and Games                   FUN AND GAMES

SOUNDEX

SOUNDEX 是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。。SOUNDEX 考虑了类似的发音字符和音节，使得能对字符串进行发音比较而不是字母比较。虽然 SOUNDEX 不是 SQL概念，但多数 DBMS都提供对 SOUNDEX 的支持。

例子：Customers 表中有一个顾客 Kids Place，其联系名为 Michelle Green。但如果这是错误的输入，此联系名实际上应该是 Michael Green，该怎么办呢？显然，按正确的联系名搜索不会返回数据。

使用 SOUNDEX()函数进行搜索，它匹配所有发音类似于 Michael Green 的联系名：

SELECT cust_name, cust_contact
FROM Customers 
WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');

输出▼ 
cust_name                      cust_contact 
--------------------------     ---------------------------- 
Kids Place                     Michelle Green

2、日期和时间处理函数

DATEPART() ，此函数返回日期的某一部分。DATEPART()函数有两个参数，它们分别是返回的成分和从中返回成分的日期。

在SQL Server中检索 2012年的所有订单：

SELECT order_num FROM Orders WHERE DATEPART(yy, order_date) = 2012;

在 Access中使用如下版本：

SELECT order_num FROM Orders WHERE DATEPART('yyyy', order_date) = 2012;

Oracle没有 DATEPART()函数，不过有几个可用来完成相同检索的日期处理函数。

SELECT order_num FROM Orders WHERE to_number(to_char(order_date, 'YYYY')) = 2012;

to_char()函数用来提取日期的成分，to_number()用来将提取出的成分转换为数值，以便能与 2012 进行比较。

MySQL 和 MariaDB 具有各种日期处理函数，但没有 DATEPART()。 MySQL和 MariaDB用户可使用名为 YEAR()的函数从日期中提取年份：

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

3、常用数值处理函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AAujXlFO-1595646886871)(C:\Users\Pan\AppData\Roaming\Typora\typora-user-images\1595173909064.png)]

八、汇总数据

聚集函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CNE4uJ0e-1595646886873)(C:\Users\Pan\AppData\Roaming\Typora\typora-user-images\1594785164721.png)]

1、AVG()

AVG()通过对表中行数计数并计算其列值之和，求得该列的平均值

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

SELECT AVG(prod_price) AS avg_price FROM Products;

输出▼ 
avg_price
-------------
6.823333

返回特定供应商所提供产品的平均价格：

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

注意： AVG()只用于单个列。为了获得多个列的平均值，必须使用多个 AVG()函数。 AVG()函数忽略列值为 NULL 的行。

2、 COUNT()

COUNT()函数进行计数。可利用 COUNT()确定表中行的数目或符合特定条件的行的数目。

两种使用方式：

使用 COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。
使用 COUNT(column)对特定列中具有值的行进行计数，忽略 NULL 值。

返回 Customers 表中顾客的总数：

SELECT COUNT(*) AS num_cust FROM Customers;

利用 COUNT(*)对所有行计数，不管行中各列有什么值。

输出▼ 
num_cust
--------
5

对具有电子邮件地址的客户计数：

SELECT COUNT(cust_email) AS num_cust FROM Customers;

输出▼ 
num_cust 
-------- 
3

3、MAX()

MAX()返回指定列中的最大值。MAX()要求指定列名。

MAX()函数忽略列值为 NULL 的行。

返回 Products 表中最贵物品的价格：

SELECT MAX(prod_price) AS max_price FROM Products;

输出▼
max_price 
---------- 
11.9900

提示：在用于文本数据时，MAX()返回按该列排序后的最后一行。

4、MIN()

MIN()返回指定列中的最小值。MIN()要求指定列名。

MIN()函数忽略列值为 NULL 的行

SELECT MIN(prod_price) AS min_price FROM Products;

提示：在用于文本数据时，MIN()返回该列排序后最前面的行

5、SUM()

SUM()用来返回指定列值的和（总计）。

检索所订购物品的总数：

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

函数 SUM(quantity)返回订单中所有物品数量之和，WHERE 子句保证只统计某个物品订单中的物品。。

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

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

6、DISTINCT 聚集不同值

只包含不同的值，指定 DISTINCT 参数。ALL参数是默认。

使用了 DISTINCT 参数，平均值只考虑各个不同的价格：

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

注意：DISTINCT 不能用于 COUNT(*) 。DISTINCT 必须使用列名

7、组合聚集函数

包含多个聚集函数：

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;

九、分组数据

使用分组可以将数据分为多个逻辑组，对每个组进行聚集计算。

1、创建分组 GROUP BY

分组是使用 SELECT 语句的 GROUP BY 子句建立的。

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

输出▼ 
vend_id     num_prods 
-------     --------- 
BRS01       3 
DLL01       4 
FNG01       2

分析：GROUP BY 子句指示 DBMS按 vend_id 排序并分组数据。这就会对每个 vend_id 而不是整个表计算 num_prods 一次。从输出中可以看到，供应商 BRS01 有 3 个产品，供应商 DLL01 有 4 个产品，而供应商 FNG01 有 2 个产品。

GROUP BY 使用规定：

GROUP BY 子句可以包含任意数目的列，因而可以对分组进行嵌套。
如果在 GROUP BY 子句中嵌套了分组，数据将在后指定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
GROUP BY 子句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在 SELECT 中使用表达式，则必须在 GROUP BY 子句中指定相同的表达式。不能使用别名。
大多数 SQL实现不允许 GROUP BY 列带有长度可变的数据类型（如文本或备注型字段）。
除聚集计算语句外，SELECT 语句中的每一列都必须在 GROUP BY 子句中给出。
如果分组列中包含具有 NULL 值的行，则 NULL 将作为一个分组返回。如果列中有多行 NULL 值，它们将分为一组。
GROUP BY 子句必须出现在 WHERE 子句之后，ORDER BY 子句之前。

2、过滤分组 HAVING

目前为止所学过的所有类型的 WHERE 子句都可以用 HAVING 来替代。唯一的差别是，WHERE 过滤行，而 HAVING 过滤分组。

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

输出▼ 
cust_id        orders 
----------     -----------
1000000001     2

最后一行增加了 HAVING 子句，它过滤 COUNT(*) >= 2（两个以上订单）的那些分组。

WHERE和HAVING 还有一种理解：WHERE 在数据分组前进行过滤，HAVING 在数据分组后进行过滤。

WHERE 排除的行不包括在分组中。影响 HAVING 子句中基于这些值过滤掉的分组。。

它列出具有两个以上产品且其价格大于等于 4 的供应商：

SELECT vend_id, COUNT(*) AS num_prods FROM Products 
WHERE prod_price >= 4 
GROUP BY vend_id 
HAVING COUNT(*) >= 2;

WHERE 子句过滤所有 prod_price 至少为 4 的行，然后按 vend_id 分组数据，HAVING 子句过滤计数为 2或 2以上的分组

输出▼ 
vend_id     num_prods 
-------     ----------- 
BRS01       3
FNG01       2

说明：使用 HAVING 时应该结合 GROUP BY 子句，而 WHERE 子句用于标准的行级过滤。

3、分组和排序

GROUP BY 和 ORDER BY ：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iygmzLtc-1595646886878)(C:\Users\Pan\AppData\Roaming\Typora\typora-user-images\1594867903519.png)]

提示：一般在使用 GROUP BY 子句时，应该也给出 ORDER BY 子句。这是保证数据正确排序的唯一方法。千万不要仅依赖 GROUP BY 排序数据。

它检索包含三个或更多物品的订单号和订购物品的数目：

SELECT order_num, COUNT(*) AS items 
FROM OrderItems
GROUP BY order_num 
HAVING COUNT(*) >= 3;

--ORDER BY items, order_num;

输出▼ 
order_num     items
---------     ----- 
20006         3 
20007         5
20008         5 
20009         3

按订购物品的数目排序输出，需要添加 ORDER BY 子句

输出▼ 
order_num     items
---------     ----- 
20006         3 
20009         3 
20007         5 
20008         5

4、SELECT 子句顺序

在 SELECT 语句中使用时必须遵循的次序，列出迄今为止所学过的子句。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-z2I1j7Ox-1595646886880)(C:\Users\Pan\AppData\Roaming\Typora\typora-user-images\1594868768708.png)]

十、使用子查询

1、利用子查询进行过滤

每个订单包含订单编号、客户 ID、订单日期，在 Orders 表中存储为一行。各订单的物品存储在相关的 OrderItems 表中。Orders 表不存储顾客信息，只存储顾客 ID。顾客的实际信息存储在 Customers 表中。
现在，假如需要列出订购物品 RGAN01 的所有顾客，应该怎样检索？下面列出具体的步骤。

(1) 检索包含物品 RGAN01 的所有订单的编号。

(2) 检索具有前一步骤列出的订单编号的所有顾客的 ID。

(3) 检索前一步骤返回的所有顾客 ID的顾客信息。

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

分析：最里边的子查询返回订单号列表，此列表用于其外面的子查询的 WHERE 子句。外面的子查询返回顾客ID列表，此顾客 ID列表用于最外层查询的 WHERE 子句。最外层查询返回所需的数据。

注意：作为子查询的 SELECT 语句只能查询单个列。企图检索多个列将返回错误。使用子查询并不总是执行这类数据检索的最有效方法。

2、作为计算字段使用子查询

假如需要显示 Customers 表中每个顾客的订单总数。订单与相应的顾客 ID存储在 Orders 表中。

执行这个操作，要遵循下面的步骤：

(1) 从 Customers 表中检索顾客列表；

(2) 对于检索出的每个顾客，统计其在 Orders 表中的订单数目。

要对每个顾客执行 COUNT(*)，应该将它作为一个子查询

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

。该子查询对检索出的每个顾客执行一次。在此例中，该子查询执行了 5次，因为检索出了 5个顾客。

十一、联结(join)表

1、内联结（inner join）

内联结（inner join）即等值联结，下面两例子相同：

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

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

2、自联结（self-join）

3、自然联结（natural join）

4、外联结（outer join）

外联接包含没有关联行的那些行。

如：

对每个顾客下的订单进行计数，包括那些至今尚未下订单的顾客；
列出所有产品以及订购数量，包括没有人订购的产品；
计算平均销售规模，包括那些至今尚未下订单的顾客

检索包括没有订单顾客在内的所有顾客：

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

潘森L

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL学习笔记

这些笔记主要记录自SQL必知必会，都是比较简单的内容。书本链接：链接：https://pan.baidu.com/s/1iZxpNE0ISjOPMIn0mPihCw提取码：m2x1一、检索数据1、DISTINCT指示数据库只返回不同的值（具有唯一性）注意：不能部分使用 DISTINCT，因为 DISTINCT 关键字作用于所有的列，不仅仅是跟在其后的那一列。2、限制结果（limit、offset）限制结果返回第一行或者一定数量的行。Oracle， ROWNUM（行计数器）计算行：SE
复制链接

扫一扫