导图社区 SQL必知必会(第5版)1-5课学习笔记
这是一篇关于SQL必知必会(第5版)1-5课学习笔记的思维导图,包含了解SQL,检索数据,排序检索数据,过滤数据和高级数据过滤这五课的内容。
编辑于2022-07-29 14:17:23SQL必知必会(第5版)1-5课
第1课 了解SQL
数据库基础
数据库(database)
数据集合
表(table)
某种特定类型数据的结构化清单
列(column)
表中的一个字段
行(row)
表中的一个记录
主键
唯一标识自己
什么是SQL
SQL是一种专门用来与数据库沟通的语言
第2课 检索数据
SELECT语句
多条SQL语句必须以分号(;)分隔
SQL语句不区分大小写
其中所有空格都被忽略
关键字(keyword)
关键字不能用作表或列的名字
检索单个列
SELECT prod_name FROM Products;
所需的列名写在SELECT关键字之后,FROM关键字指出从哪个表中检索数据。
prod_name ------------------- Fish bean bag toy Bird bean bag toy Rabbit bean bag toy 8 inch teddy bear 12 inch teddy bear 18 inch teddy bear Raggedy Ann King doll Queen doll
检索多个列
列名之间加上逗号,最后一个列名后不加
SELECT prod_id, prod_name, prod_price FROM Products;
指定了3个列名,列名之间用逗号分隔
prod_id prod_name prod_price --------- -------------------- ---------- BNBG01 Fish bean bag toy 3.49 BNBG02 Bird bean bag toy 3.49 BNBG03 Rabbit bean bag toy 3.49 BR01 8 inch teddy bear 5.99 BR02 12 inch teddy bear 8.99 BR03 18 inch teddy bear 11.99 RGAN01 Raggedy Ann 4.99 RYL01 King doll 9.49 RYL02 Queen dool 9.49
检索所有列
如果给定一个通配符(*),则返回表中所有列。
SELECT * FROM Products;
检索不同的值
SELECT vend_id FROM Products;
检索Products表中所有产品供应商的ID
vend_id ---------- BRS01 BRS01 BRS01 DLL01 DLL01 DLL01 DLL01 FNG01 FNG01
SELECT DISTINCT vend_id FROM Products;
只返回不同(具有唯一性)的vend_id行
vend_id ---------- BRS01 DLL01 FNG01
DISTINCT关键字作用于所有的列
限制结果
TOP关键字
SELECT TOP 5 prod_name FROM Products;
prod_name ----------------- 8 inch teddy bear 12 inch teddy bear 18 inch teddy bear Fish bean bag toy Bird bean bag toy
子主题
ROWNUM(行计数器)
SELECT prod_name FROM Products WHERE ROWNUM <=5;
LIMIT 子句
SELECT prod_name FROM Products LIMIT 5;
LIMIT 5 OFFSET 5指示
LIMIT 5 OFFSET 5指示MySQL等DBMS返回从第5行起的5行数据
SELECT prod_name FROM Products LIMIT 5 OFFSET 5;
prod_name ------------------- Rabbit bean bag toy Raggedy Ann King doll
使用注释
SELECT prod_name -- 这是一条注释 FROM Products;
# 这是一条注释 SELECT prod_name FROM Products;
/* SELECT prod_name, vend_id FROM Products; */ SELECT prod_name FROM Products;
第3课 排序检索数据
排序数据
但请看其输出,并没有特定的顺序。
SELECT prod_name FROM Products;
prod_name -------------------- Fish bean bag toy Bird bean bag toy Rabbit bean bag toy 8 inch teddy bear 12 inch teddy bear 18 inch teddy bear Raggedy Ann King doll Queen doll
ORDER BY子句
在指定一条ORDER BY子句时,保证它是SELECT语句中最后一条子句
SELECT prod_name FROM Products ORDER BY prod_name;
prod_name -------------------- 12 inch teddy bear 18 inch teddy bear 8 inch teddy bear Bird bean bag toy Fish bean bag toy King doll Queen doll Rabbit bean bag toy Raggedy Ann
按列位置排序
ORDER BY还支持按相对列位置进行排序。
ORDER BY 2,3表示先按prod_price,再按prod_name进行排序。
SELECT prod_id, prod_price, prod_name FROM Products ORDER BY 2, 3;
prod_id prod_price prod_name ------- ---------- -------------------- BNBG02 3.4900 Bird bean bag toy BNBG01 3.4900 Fish bean bag toy BNBG03 3.4900 Rabbit bean bag toy RGAN01 4.9900 Raggedy Ann BR01 5.9900 8 inch teddy bear BR02 8.9900 12 inch teddy bear RYL01 9.4900 King doll RYL02 9.4900 Queen doll BR03 11.9900 18 inch teddy bear
按多个列排序
要按多个列排序,只须指定这些列名,列名之间用逗号分开即可
SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price, prod_name;
prod_id prod_price prod_name ------- ---------- -------------------- BNBG02 3.4900 Bird bean bag toy BNBG01 3.4900 Fish bean bag toy BNBG03 3.4900 Rabbit bean bag toy RGAN01 4.9900 Raggedy Ann BR01 5.9900 8 inch teddy bear BR02 8.9900 12 inch teddy bear RYL01 9.4900 King doll RYL02 9.4900 Queen doll BR03 11.9900 18 inch teddy bear
指定排序方向
DESC关键字
降序排序,必须指定DESC关键字
SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price DESC;
prod_id prod_price prod_name ------- ---------- -------------------- BR03 11.9900 18 inch teddy bear RYL01 9.4900 King doll RYL02 9.4900 Queen doll BR02 8.9900 12 inch teddy bear BR01 5.9900 8 inch teddy bear RGAN01 4.9900 Raggedy Ann BNBG01 3.4900 Fish bean bag toy BNBG02 3.4900 Bird bean bag toy BNBG03 3.4900 Rabbit bean bag toy
如果想在多个列上进行降序排序,必须对每一列指定DESC关键字
SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price DESC, prod_name;
prod_id prod_price prod_name ------- ---------- -------------------- BR03 11.9900 18 inch teddy bear RYL01 9.4900 King doll RYL02 9.4900 Queen doll BR02 8.9900 12 inch teddy bear BR01 5.9900 8 inch teddy bear RGAN01 4.9900 Raggedy Ann BNBG02 3.4900 Bird bean bag toy BNBG01 3.4900 Fish bean bag toy BNBG03 3.4900 Rabbit bean bag toy
挑战题
1.编写SQL语句,从Customers中检索所有的顾客名称(cust_names),并按从Z到A的顺序显示结果。
2.编写SQL语句,从Orders表中检索顾客 ID(cust_id)和订单号(order_num),并先按顾客ID对结果进行排序,再按订单日期倒序排列。
3.显然,我们的虚拟商店更喜欢出售比较贵的物品,而且这类物品有很多。编写SQL语句,显示OrderItems表中的数量和价格(item_price),并按数量由多到少、价格由高到低排序。
4.下面的SQL语句有问题吗?(尝试在不运行的情况下指出。)SELECT vend_name, FROM Vendors ORDER vend_name DESC;
第4课 过滤数据
使用WHERE子句
在SELECT语句中,数据根据WHERE子句中指定的搜索条件进行过滤
SELECT prod_name, prod_price FROM Products WHERE prod_price = 3.49;
prod_name prod_price ------------------- ---------- Fish bean bag toy 3.49 Bird bean bag toy 3.49 Rabbit bean bag toy 3.49
WHERE子句操作符
=等于
<>不等于
!=不等于
<小于
<=小于等于
!<不小于
>大于
>=大于等于
!>不大于
BETWEEN在指定的两个值之间
IS NULL为NULL值
检查单个值
第一个例子是列出所有价格小于10美元的产品
SELECT prod_name, prod_price FROM Products WHERE prod_price < 10;
prod_name prod_price ------------------- ---------- Fish bean bag toy 3.49 Bird bean bag toy 3.49 Rabbit bean bag toy 3.49 8 inch teddy bear 5.99 12 inch teddy bear 8.99 Raggedy Ann 4.99 King doll 9.49 Queen doll 9.49
下一条语句检索所有价格小于等于10美元的产品 (因为没有价格恰好是10美元的产品,所以结果与前一个例子相同)
SELECT prod_name, prod_price FROM Products WHERE prod_price <= 10;
不匹配检查
列出所有不是供应商DLL01制造的产品:
SELECT vend_id, prod_name FROM Products WHERE vend_id <> 'DLL01'
vend_id prod_name ---------- ------------------ BRS01 8 inch teddy bear BRS01 12 inch teddy bear BRS01 18 inch teddy bear FNG01 King doll FNG01 Queen doll
单引号用来限定字符串
SELECT vend_id, prod_name FROM Products WHERE vend_id != 'DLL01';
SELECT prod_name, prod_price FROM Products WHERE prod_price BETWEEN 5 AND 10;
prod_name prod_price ------------------- ---------- 8 inch teddy bear 5.99 12 inch teddy bear 8.99 King doll 9.49 Queen doll 9.49
范围值检查
BETWEEN操作符
SELECT prod_name, prod_price FROM Products WHERE prod_price BETWEEN 5 AND 10;
prod_name prod_price ------------------- ---------- 8 inch teddy bear 5.99 12 inch teddy bear 8.99 King doll 9.49 Queen doll 9.49
从这个例子可以看到,在使用BETWEEN时, 必须指定两个值——所需范围的低端值和高端值。 这两个值必须用AND关键字分隔。 BETWEEN匹配范围中所有的值, 包括指定的开始值和结束值
空值检查
NULL
SELECT prod_name FROM Products WHERE prod_price IS NULL;
SELECT cust_name FROM Customers WHERE cust_email IS NULL;
cust_name ---------- Kids Place The Toy Store
挑战题
1.编写SQL语句,从Products表中检索产品ID(prod_id)和产品名称(prod_name),只返回价格为9.49美元的产品
2.编写SQL语句,从Products表中检索产品ID(prod_id)和产品名称(prod_name),只返回价格为9美元或更高的产品。
3.结合第3课和第4课编写SQL语句,从OrderItems表中检索出所有不同订单号(order_num),其中包含100个或更多的产品。
4.编写SQL语句,返回Products表中所有价格在3美元到6美元之间的产品的名称(prod_name)和价格(prod_price),然后按价格对结果进行排序。
第5课 高级数据过滤
AND操作符
SELECT prod_id, prod_price, prod_name FROM Products WHERE vend_id = 'DLL01' AND prod_price <= 4;
prod_id prod_price prod_name ------- ---------- -------------------- BNBG02 3.4900 Bird bean bag toy BNBG01 3.4900 Fish bean bag toy BNBG03 3.4900 Rabbit bean bag toy
SELECT语句中的WHERE子句包含两个条件, 用AND关键字联结在一起。 AND指示DBMS只返回满足所有给定条件的行
OR操作符
它指示DBMS检索匹配任一条件的行
SELECT prod_id, prod_price, prod_name FROM Products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';
prod_name prod_price ------------------- ---------- Fish bean bag toy 3.4900 Bird bean bag toy 3.4900 Rabbit bean bag toy 3.4900 8 inch teddy bear 5.9900 12 inch teddy bear 8.9900 18 inch teddy bear 11.9900 Raggedy Ann 4.9900
求值顺序
WHERE子句可以包含任意数目的AND和OR操作符, 允许两者结合以进行复杂、高级的过滤。
假如需要列出价格为10美元及以上,且由DLL01或BRS01制造的所有产品
SELECT prod_name, prod_price FROM Products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01' AND prod_price >= 10;
prod_name prod_price ------------------- ---------- Fish bean bag toy 3.4900 Bird bean bag toy 3.4900 Rabbit bean bag toy 3.4900 18 inch teddy bear 11.9900 Raggedy Ann 4.9900
由于AND在求值过程中优先级更高,操作符被错误地组合了。
SELECT prod_name, prod_price FROM Products WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01') AND prod_price >= 10;
prod_name prod_price ------------------- ---------- 18 inch teddy bear 11.9900
圆括号具有比AND或OR操作符更高的优先级, 所以DBMS首先过滤圆括号内的OR条件
IN操作符
IN操作符用来指定条件范围,范围中的每个条件都可以进行匹配。 IN取一组由逗号分隔、括在圆括号中的合法值。
SELECT prod_name, prod_price FROM Products WHERE vend_id IN ('DLL01','BRS01') ORDER BY prod_name;
prod_name prod_price ------------------- ---------- 12 inch teddy bear 8.9900 18 inch teddy bear 11.9900 8 inch teddy bear 5.9900 Bird bean bag toy 3.4900 Fish bean bag toy 3.4900 Rabbit bean bag toy 3.4900 Raggedy Ann 4.9900
此SELECT语句检索由供应商DLL01和BRS01制造的所有产品。 IN操作符后跟由逗号分隔的合法值,这些值必须括在圆括号中。
SELECT prod_name, prod_price FROM Products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01' ORDER BY prod_name;
prod_name prod_price ------------------- ---------- 12 inch teddy bear 8.9900 18 inch teddy bear 11.9900 8 inch teddy bear 5.9900 Bird bean bag toy 3.4900 Fish bean bag toy 3.4900 Rabbit bean bag toy 3.4900 Raggedy Ann 4.9900
SELECT prod_name, prod_price FROM Products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01' ORDER BY prod_name;
IN操作符优点
语法更清楚,更直观
在与其他AND和OR操作符组合使用IN时,求值顺序更容易管理
IN操作符一般比一组OR操作符执行得更快
可以包含其他SELECT语句,能够更动态地建立WHERE子句
NOT操作符
WHERE子句中用来否定其后条件的关键字。
为了列出除DLL01之外的所有供应商制造的产品
SELECT prod_name FROM Products WHERE NOT vend_id = 'DLL01' ORDER BY prod_name;
prod_name ------------------ 12 inch teddy bear 18 inch teddy bear 8 inch teddy bear King doll Queen doll
上面的例子也可以使用<>操作符来完成
SELECT prod_name FROM Products WHERE vend_id <> 'DLL01' ORDER BY prod_name;
prod_name ------------------ 12 inch teddy bear 18 inch teddy bear 8 inch teddy bear King doll Queen doll
挑战题
编写SQL语句,从Vendors表中检索供应商名称(vend_name),仅返回加利福尼亚州的供应商(这需要按国家[USA]和州[CA]进行过滤,没准其他国家也存在一个加利福尼亚州)。提示:过滤器需要匹配字符串。
编写SQL语句,查找所有至少订购了总量100个的BR01、BR02或BR03的订单。你需要返回OrderItems表的订单号(order_num)、产品ID(prod_id)和数量,并按产品ID和数量进行过滤。提示:根据编写过滤器的方式,可能需要特别注意求值顺序。
现在,我们回顾上一课的挑战题。编写SQL语句,返回所有价格在3美元到6美元之间的产品的名称(prod_name)和价格(prod_price)。使用AND,然后按价格对结果进行排序。
下面的SQL语句有问题吗?(尝试在不运行的情况下指出。)SELECT vend_name FROM Vendors ORDER BY vend_name WHERE vend_country = 'USA' AND vend_state = 'CA';