二、数据的检索
检索单个列:
SELECT prod_name -- 检索什么
FROM Products; -- 从哪里检索
检索多个列:
SELECT pro_id,
prod_name,
prod_price
FROM Products;
- 列和列之间使用逗号进行分割,书写使用多次分行可以
检索所有的列:
SELECT * -- *代表所有列
FROM Products;
- 当不知道具体的列名的时候可以使用通配符获取未知列。
检索不同的值:
SELECT distinct vend_id
FROM products;
- SQL中的DISTINCT关键字表示的是去重,只返回不同的值。它必须放在列的前面。
- 不能部分使用DISTINCT关键字。它是作用于所有的列
SELECT DISTINCT vend_id, prod_price -- DISTINCT作用于所有的列,并不仅仅是后面的列
限制结果:
SQL Server中:
SELECT TOP 5 prod_name -- 最多返回5行
FROM Products;
- 在列名前面使用了
top
关键字。
MySQL中:
SELECT prod_name
FROM Products
LIMIT 5; -- 使用LIMIT5
- 使用了
limit
关键字
关于limit的使用:
SELECT prod_name
FROM Products
LIMIT 4 OFFSET 5; -- 第5行开始显示4行数据
-- 简化版本
SELECT prod_name
FROM Products
LIMIT 5,4 -- 效果同上
三、排列检索数据
排列数据**(单个列):**
- 使用关键字:order by
- 排序的结果默认是升序asc,降序是desc
SELECT prod_name
FROM Products
ORDER BY prod_name; -- 根据产品名称的字母进行排序
注意:我们需要注意order by子句的位置,一定要保证它是select语句的最后一条子句。如果它不是最后的子句,那么就会报错。
按多个列排序:
SELECT prod_id,prod_price,prod_name -- 选择3个列
FROM Products
ORDER BY prod_price,prod_name; -- 先根据价格排序,如果有相同的价格再根据姓名排序
- 只有当prod_price有相同的值,才会根据prod_name进行排
按照列的位置排序:
SELECT
prod_id,
prod_price,
prod_name -- 选择3个列
FROM Products
ORDER BY 2,3; -- 2,3就是相对位置
- 2表示的是第2个列(prod_price)
- 3表示的是第3个列名(prod_name)
- 如果有必要,可以混合使用实际列名和相对列位置。
指定排序方向:
-- 正确写法
SELECT
prod_id,
prod_price,
prod_name
FROM Products
ORDER BY prod_price DESC, prod_name DESC; -- 每个列都指定DESC
-- 错误写法!!!
SELECT
prod_id,
prod_price,
prod_name
FROM Products
ORDER BY prod_price, prod_name DESC; -- DESC只对最近的prod_name起作用,
那么prod_price仍然是升序排列的
- 对多个列进行降序排列时,每个列后面都必须写
desc
才能满足条件。
课后习题:
- 逗号可不是乱加的。
- 少了个by
四、过滤数据
本节中讲解的是使用where关键词来过滤数据。数据库中一般存在大量的数据,一般我们只需要检索表中少量的行。只检索所需数据需要指定搜索条件,搜索条件也称之为过滤条件。
使用where子句
SELECT prod_name, prod_price
FROM Products
WHERE prod_price=5; -- 指定条件
“ 笔记:当ORDER BY 和WHERE子句同时存在的时候,ORDER BY子句应该位于WHERE子句之后。
”
WHERE子句操作符
- 常见的操作符不做说明,以下比较特殊的操作符
| 操作符 | 说明 |
| — | — |
| <> | 不等于 |
| !< | 不小于 |
| !> | 不大于 |
| between | 在指定的两个值之间 |
| is null | 为空值 |
检查单个值
SELECT prod_name, prod_price
FROM Products
WHERE prod_price <= 5; -- 指定1个条件
SELECT vend_id, prod_name, prod_price
FROM Products
WHERE vend_id <> 'DLL01' -- 指定不等于条件
- 进行字符比较的时候应该注意使用单引号括起来;
范围值检查
要检查某个范围的值,可以使用BETWEEN操作符。BETWEEN操作符要搭配AND同时使用,指定范围的最大值和最小值:
SELECT vend_id, prod_name, prod_price
FROM Products
WHERE prod_price BETWEEN 100 AND 600 -- BETWEEN ... AND...联合使用
空值检查
SELECT prod_name, prod_price
FROM Products
WHERE prod_price IS NULL; -- 找出价格为NULL的数据
五、高级数据过滤
- 本节中介绍的是如何组合WHERE子句以建立功能更强、更高级的搜索条件
AND操作符:
SELECT vend_id, prod_name, prod_price
FROM Products
WHERE vend_id >= 'DLL01' AND prod_price <= 20; -- AND操作符指定2个条件
- 查找同时满足这两个条件的行。
OR操作符:
SELECT vend_id, prod_name, prod_price
FROM Products
WHERE vend_id >= 'DLL01' OR prod_price <= 20; -- AND操作符指定2个条件
- 满足第一个条件第二个条件就不会执行。
AND和OR联用:
⚠️:AND操作符的优先级是高于OR操作符
SELECT prod_name,prod_price
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01' AND prod_price >= 10;
- 在程序执行的时候实际上首先执行and的部分。
SELECT prod_name,prod_price
FROM Products
WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01') AND prod_price >= 10;
- 加上括号后or的部分优先级会
IN操作符
SELECT prod_name,prod_price
FROM Products
WHERE vend_id IN ('DLL01','BRS01') -- 功能类似于OR操作符
ORDER BY name:
笔记:IN操作符的主要优点
1、IN操作符的语法更清楚、更直观
2、使用了IN操作符更容易管理求值顺序
3、IN操作符一般比OR操作符执行的更快
4、在IN操作符中可以包含其他SELECT子句,能够动态地建立where子句
NOT操作符:
NOT操作符只有一个功能:就是否定后面所跟的任何条件。
SELECT prod_name
FROM Products
WHERE NOT vend_id = 'DLL01' -- 找出不是DLLO1名字的数据
ORDER BY prod_name
- 需要注意
NOT
的位置,需要放在列的前面。
上面的语句的功能也可以用<>来实现:
SELECT prod_name
FROM Products
WHERE vend_id <> 'DLLO1' -- 不等于,效果同上
ORDER BY prod_name
六、使用通配符进行过滤
什么是通配符:
通配符wildcard是用来匹配值的一部分的特殊字符;利用通配符,可以创建比较特定数据的搜索模式。
- 为了在搜索子句中使用通配符,必须使用LIKE操作符。
- ⚠️通配符搜索只能用于文本字段(字符串),对于非文本数据类型不能使用通配符搜索。
百分号**%**
:
功能:匹配任意字符出现的任意次数,即任意内容
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE "Fish%" -- 找出所有Fish开头的产品,不管后面是什么内容
通配符可以在任意位置使用,可以使用多次:
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE "%bean bag%" -- 匹配名字中包含bean bag的任意名字的数据,不管前后
通配符出现在中间:
SELECT prod_name
FROM Products
WHERE prod_name LIKE 'F%y' -- 找出F开头y结尾的数据
⚠️:百分号%能够匹配任意位置的0个、1个或者多个字符,但是不能匹配NULL
下划线**_**
:
下划线通配符的用途和百分号类似,但是它只能匹配一个字符,百分号是匹配多个字符,这是二者的区别。
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '__ inch teddy bear' -- 前面是两个下划线_
-- 结果
prod_id prod_name
------- ---------
BR02 12 inch teddy bear -- 12或者18刚好是两个字符
BR03 18 inch teddy bear
- 两个下划线就匹配两个字符。
方括号**[]**
:
方括号[]通配符用来指定一个字符集,它必须匹配指定位置的一个字符。
SELECT cust_contact FROM Customers WHERE cust_contact LIKE '[JM]%' -- 匹配JM当中一个字母开头,后面是任意字符的内容 ORDER BY cust_contact
取反字符**^**
:
使用取反符号^来否定内容:
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[^JM]%' -- 匹配不是JM开头的任意内容
ORDER BY cust_contact;
通配符使用技巧:
- 不要过度使用通配符
- 如果确实需要使用通配符,尽可能不要将它们放在搜索模式的开始位置。通配符处于开始处,搜索是最慢的。
- 仔细注意通配符的位置。如果放错地方,可能得不到我们想要的结果。
- 通配符应该要细心使用,不要过度使用。
七、创建计算字段
拼接字段:
SELECT vend_name + '(' + vend_country + ')'
FROM Vendors
ORDER BY vend_name;
- 相当于把两个字段放在了一起进行显示
- 在SQL Server中使用
+
进行连接。
执行结果如下:
在MySQL中必须使用使用函数来完成这个操作:
SELECT
Concat(vend_name, ' (', vend_country, ')')
FROM Vendors
ORDER BY vend_name;
小知识:MySQL中如何去掉空格?
RTRIM(col) -- 去掉值右边的所有空格
LTRIM(col) -- 去掉值左边的所有空格
TTRIM(col) -- 去掉字符串左右两边的所有空格
例如:
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
FROM Vendors
ORDER BY vend_name;
执行结果如图:
下面是没有使用去空格函数的执行结果:
使用别名:
别名(alias)是一个字段或者值的替换明,别名是使用关键词AS来赋予的。
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
AS vend_title
FROM Vendors
ORDER BY vend_name;
执行结果如下:
执行算术运算
计算字段的另一个常见运算是对检索出来的数据进行算术运算。
SELECT prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;
输出结果:
经常使用算术运算符:
操作符 | 说明 |
---|---|
+ | |
- | |
* | |
/ |
八、使用函数处理数据
该部分内容较多,用法较简单,暂不整理,函数内容见P100;
九、汇总数据
聚集函数指的是对某些行运行的一个函数,并且返回一个值,常用的聚集函数有:
函 数 | 说 明 |
---|---|
AVG() | 返回某列的平均值 |
COUNT() | 返回某列的行数 |
MAX() | 返回某列的最大值 |
SUM() | 返回某列值之和 |
MIN() | 返回某列的最小值 |
注意:这里的计算都是基于列进行的。
1、AVG()
SELECT AVG(prod_price) AS avg_price -- 求平均值
FROM Products;
上面求解的是所有行各自的平均值,也可以在prod_price
中指定某个特定的行来求解:
SELECT AVG(prod_price) AS avg_price -- 求平均值
FROM Products
WHERE vend_id = 'DLLO1'; -- 指定特定的行
2、COUNT()函数
COUNT()函数进行计数,可以使用它来确定表中的函数或者符合特定条件的行的数目,两种使用情况:
- count(*):不管是空值(NULL)还是非空值,都会统计进去
- count(column):对特定的列进行计数,会忽略表该列的NULL值
SELECT COUNT(*) AS num_cust
FROM Customers;
num_cust
--------
5
SELECT COUNT(cust_email) AS num_cust
FROM Customers;
num_cust
--------
3
这里的执行结果出现偏差,就是因为前者统计NULL的情况,后者忽略NULL的情况。
3、MAX()/MIN()函数
- 在使用上面两个函数的时候需要在
()
中指定列名
SELECT MAX(prod_price) AS MAX_price -- 求最大值
SELECT MAX(prod_price) AS MIN_price -- 求最小值
FROM Products;
使用上面上面的最值的函数的时候会自动忽略NULL的行。
4、SUM()函数
- 返回指定列值的和(总计)
SELECT SUM(item_price * quantity) AS total_price -- 返回所有物品的价钱之和
FROM OrderItems
WHERE order_num = 20005;
SUM()函数会自动忽略值为NULL的行。
聚集不同的值
上面的5个聚集函数都可以如下使用:
- 对所有的行执行计算,指定ALL参数或不指定参数(因为ALL是默认行为)
- 只包含不同的值,指定DISTINCT参数,表示去重之后再进行计算
SELECT AVG(DISTINCT prod_price) AS avg_price -- 去重之后再求平均值
FROM Products
WHERE vend_id = 'DLLO1'; -- 指定特定的行
1、DISTINCT不能用于COUNT(*);如果指定列名,则DISTINCT只能用于COUNT()
2、DISTINCT必须使用列名,不能用于计算或者表达式
3、DISTINCT用于MAX()和MIN()意义不大,因为最值不管是否考虑去重,都是一样的
组合聚集函数
SELECT
AVG(prod_price) AS avg_price, -- 求平均值
MAX(prod_price) AS max_price, -- 求最大值
MIN(prod_price) AS min_price, -- 求最小值
COUNT(*) AS num_items -- 物品的数目
FROM Products;
执行结果如下:
十、分组数据(GROUP BY()
)
分组使用的是两个子句:
- GROUP BY()
- HAVING()
创建分组:
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;-- 分组的列
执行结果:
可以理解为:先执行分组,再对每组执行一次聚合函数。
GROUP BY子句使用时候的常见规定:
- GROUP BY子句可以包含任意数目的列,可以对分组进行嵌套
- GROUP BY子句中列出的每一列都是检索列或者有效的表达式(但是不能是聚集函数)
- 如果在SELECT中使用表达式,则必须在GROUP BY子句中使用相同的表达式,而不是使用别名
- 除了聚集函数外,SELECT语句中的每列都必须在GROUP BY子句中列出
- 如果分组中包含具有NULL的行,则NULL将作为一个分组返回;如果列中出现多个NULL,它们将分成一个组
- GROUP BY子句必须在WHERE子句之后,ORDER BY子句之前
- GROUP BY子句中可以使用相对位置:GROUP BY 2, 1 表示先根据第二个列分组,再根据第一个列分组
值得注意的是:
- Microsoft SQL Server 等有些 SQL 实现在 GROUP BY 中支持可选的 ALL 子句。这个子句可用来返回所有分组,即使是没有匹配行的分组也返 回(在此情况下,聚集将返回 NULL)。具体的 DBMS 是否支持 ALL, 请参阅相应的文档。(具体介绍见书P109-例4-45)
过滤分组(HAVING
)
- 在WHERE子句中指定过滤的是行而不是分组;实际上WHERE种并没有分组的概念。在SQL使用HAVING来实现过滤分组。
- HAVING支持所有的WHERE操作符
总而言之:where是过滤行,having是过滤分组的(也可以单纯看做另一种行)
SELECT
cust_id,
COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2; -- 过滤分组
第六行对每个分组计算行数,找出大于或等于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; -- 分组后再执行,找出数目大于2的数据
具体执行过程:在select中定义了显示列,from告诉我们从Products表中提取数据,where限制提取的行条件,group进行分组,having限制每个分组行数大于等于2数据进行显示。
分组与排序:
SELECT
order_num,
COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3
ORDER BY items, order_num; -- 先分组再过滤,最后排序输出
十一、使用子查询
- 任何SELECT语句都是查询,SQL还允许在查询中嵌套查询。
- 作为子查询的语句的时select只能检索单个列。
问题:假如需要列出订购物品 RGAN01 的所有顾客,应该怎样检索?
- 检索包含物品 RGAN01 的所有订单的编号。
- 检索具有前一步骤列出的订单编号的所有顾客的 ID。
- 检索前一步骤返回的所有顾客 ID 的顾客信息。
SELECT cust_id -- 再根据子查询中的order_num找出符合要求的cust_id
FROM Orders
WHERE order_num IN (SELECT order_num -- 先根据WHERE条件找出满足符合要求的order_num
FROM OrderItems
WHERE prod_id = 'RGAN01');
子查询其实由内而外进行处理的。
SELECT Customers -- 最后根据找出的cust_id查询Customers
FROM cust_id IN(SELECT cust_id -- 再根据子查询中的order_num找出符合要求的cust_id
FROM Orders
WHERE order_num IN (SELECT order_num -- 先根据WHERE条件找出满足符合要求的order_num
FROM OrderItems
WHERE prod_id = 'RGAN01'));
- 进行了三层嵌套
作为计算字段使用子查询:
SELECT
cust_name
,cust_state
,(SELECT COUNT(*) -- 将子查询作为一个计算字段输出:统计每个cust_id的数量
FROM Orders
WHERE Orders.cust_id = Customers.cust_id) AS orders -- Orders.cust_id = Customers.cust_id 使用完全限定列名来避免歧义
FROM Customers
ORDER BY cust_name;
子查询中的 WHERE 子句与前面使用的 WHERE 子句稍有不同,因为它使用 了完全限定列名,而不只是列名(cust_id)。它指定表名和列名(Orders.cust_id和 Customers.cust_id)。下面的 WHERE子句告诉 SQL,比较Orders表中的cust_id和当前正从Customers表中检索的cust_id:
WHERE Orders.cust_id = Customers.cust_id
十二、联结
等值联结:
- SQL最强大的功能就是数据查询的过程中使用联结表(join)。
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;
这里的where只是创造行与行之间的联结关系。
- 如果上面的代码中没有WHERE子句来指定联结条件,则返回的是笛卡尔积,返回出来行数就是第一个表中的行乘以第二个表中的行。
- 要保证所有联结都有 WHERE 子句,否则 DBMS 将返回比想要的数据多得多的数据。
内联结:
SELECT
vend_name,
prod_name,
prod_price
FROM Vendors
INNER JOIN Products -- 内联结
ON Vendors.vend_id = Products.vend_id; -- 指定联结条件
联结多个表:
SELECT
vend_name,
prod_name,
prod_price
FROM OrderItems, Products, Vendors
WHERE Products.vend_id = Vendors.vend_id -- 多个表的联结
AND OrderItems.prod_id = Products.prod_id
AND order_num = 20007;
- 和书上不一致P113
十三、创建高级联结
使用的表的别名:
SELECT
cust_name,
cust_contact
FROM Customers AS C, Orders AS O, OrderItems AS OI -- 取别名,看上去更简洁
WHERE C.cust_id = O.cust_id -- 多个表联结查询
AND OI.order_num = O.order_num
AND prod_id = 'RGAN01'
使用不同类型的联结:
介绍3种不同的联结:
- 自联结self join
- 自然联结natural join
- 外联结outer join
自联结self join
- 如前所述,使用表别名的一个主要原因是能在一条 SELECT 语句中不止一次引用相同的表。下面举一个例子。
假如要给与 Jim Jones 同一公司的所有顾客发送一封信件。这个查询要求首先找出 Jim Jones 工作的公司,然后找出在该公司工作的顾客。下面是解决此问题的一种方法:
-- 子查询
SELECT cust_id,cust_name,cust_contact
FROM Customers
WHERE cust_name = (SELECT cust_name
FROM Customers
WHERE cust_contact = 'Jim Jones');
-- 自联结
SELECT c1.cust_id, c2.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';
- 上面使用了Customers表两次,为了避免歧义,必须使用不同的别名加以区分。
- 在子查询中,先查到jim所在公司,在利用公司的条件找到同一样表中公司的所有雇员。
自然联结:
SELECT C.*, O.order_num, O.order_date,
OI.prod_id, OI.quantity, OI.item_price
FROM Customers AS C, Orders AS O,
OrderItems AS OI
WHERE C.cust_id = O.cust_id
AND OI.order_num = O.order_num
AND prod_id = 'RGAN01';
事实上,我们迄今为止建立的每个内联结都是自然联结,很可能永远都不会用到不是自然联结的内联结。
外联结:
在内联结中:
SELECT Customers.cust_id, Orders.order_num
FROM Customers
INNER JOIN Orders ON Customers.cust_id = Orders.cust_id;
只有两边都满足联结条件该行才会显示。
在外联结中:
SELECT Customers.cust_id, Orders.order_num
FROM Customers
LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;
这是一个左外连接,customers作为主表,都显示出来。即便Orders上没有匹配。
带有聚集函数的联结:
SELECT Customers.cust_id,
COUNT(Orders.order_num) AS num_ord
FROM Customers
LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;
十四、组合查询UNION
- union实际上就是一个并集操作
-- 语句1
SELECT cust_name, cust_contact,cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI');
-- 语句2
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')
UNION -- 关键字
SELECT cust_name, cust_contact,cust_email
FROM Customers
WHERE cust_name = 'Fun4ALL';
1、UNION 中的每个查询必须包含相同的列、表达式或聚集函数(不过,各个列不需要以相同的次序列出)
2、在查询过程中如果遇到同一数据类型但列名不一致,数据库会返回第一个值作为列名。
包含或取消重复的行:
- 在使用union的时候所取得的并集结果会自动去除重复的行。
如果想要改变这一默认设置:
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION ALL
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';
- UNION必须由两条或者两条以上的SELECT语句组成;语句之间通过UNION关键字隔开
- UNION中的每个查询必须包含相同的列、表达式或者聚集函数
- 列数据类型必须兼容:类型不必完全相同
- UNION从查询结果集中会自动消除重复的行;但是如果想保留所有的行,使用UNION ALL 实现。
对组合的结果进行排序:
-- 组合查询
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'
ORDER BY cust_name, cust_contact; -- 组合之后再进行排序
十五、插入数据
插入完整的行:
INSERT INTO Customers
VALUES('1000000006',
'Tony',
'123 Any Street',
'New York',
'NY',
'1111',
'USA',
NULL,
NULL
)
按照表列次序进行书写,这是方法是不安全的。
更安全的写法如下:
INSERT INTO Customers(cust_id, -- 明确列出列名
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country,
cust_contact,
cust_email
)
VALUES('1000000006', -- 和上面的列名一一对应
'Tony',
'123 Any Street',
'New York',
'NY',
'1111',
'USA',
NULL,
NULL
)
这里首先给出了所有的列名,并按照列名的顺序给出了对应插入值。
这种写法的好处就是即便标的结构改变了,也能正确的插入数据。