SQL必知必会阅读笔记，作者（Ben Forta）学习贴

最新推荐文章于 2024-05-21 20:44:15 发布

少年嘞

最新推荐文章于 2024-05-21 20:44:15 发布

阅读量225

点赞数

文章标签： sql 笔记学习

本文链接：https://blog.csdn.net/weixin_53067863/article/details/130131003

版权

在编写sql基础语法的记录贴时略感吃力，回来学习一下大佬的著作《SQL必知必会》

在学习这本书前应该有大致的sql语言基础和理解。起码知道creat等关键字和登录数据库等。

本书所使用测试用例可以在http://forta.com/books/0135182794 自行寻找。需要书籍pdf可联系我

下面是创建数据库表和插入数据代码：

-----------------------------------------------------
-- Sams Teach Yourself SQL in 10 Minutes, 5th Edition
-- http://forta.com/books/0135182794/
-- Example table creation scripts for MySQL & MariaDB
-----------------------------------------------------


-- ----------------------
-- Create Customers table
-- ----------------------
CREATE TABLE Customers
(
  cust_id      char(10)  NOT NULL ,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  NULL ,
  cust_city    char(50)  NULL ,
  cust_state   char(5)   NULL ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL 
);

-- -----------------------
-- Create OrderItems table
-- -----------------------
CREATE TABLE OrderItems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL ,
  item_price decimal(8,2) NOT NULL 
);


-- -------------------
-- Create Orders table
-- -------------------
CREATE TABLE Orders
(
  order_num  int      NOT NULL ,
  order_date datetime NOT NULL ,
  cust_id    char(10) NOT NULL 
);

-- ---------------------
-- Create Products table
-- ---------------------
CREATE TABLE Products
(
  prod_id    char(10)      NOT NULL ,
  vend_id    char(10)      NOT NULL ,
  prod_name  char(255)     NOT NULL ,
  prod_price decimal(8,2)  NOT NULL ,
  prod_desc  text          NULL 
);

-- --------------------
-- Create Vendors table
-- --------------------
CREATE TABLE Vendors
(
  vend_id      char(10) NOT NULL ,
  vend_name    char(50) NOT NULL ,
  vend_address char(50) NULL ,
  vend_city    char(50) NULL ,
  vend_state   char(5)  NULL ,
  vend_zip     char(10) NULL ,
  vend_country char(50) NULL 
);


-- -------------------
-- Define primary keys
-- -------------------
ALTER TABLE Customers ADD PRIMARY KEY (cust_id);
ALTER TABLE OrderItems ADD PRIMARY KEY (order_num, order_item);
ALTER TABLE Orders ADD PRIMARY KEY (order_num);
ALTER TABLE Products ADD PRIMARY KEY (prod_id);
ALTER TABLE Vendors ADD PRIMARY KEY (vend_id);


-- -------------------
-- Define foreign keys
-- -------------------
ALTER TABLE OrderItems ADD CONSTRAINT FK_OrderItems_Orders FOREIGN KEY (order_num) REFERENCES Orders (order_num);
ALTER TABLE OrderItems ADD CONSTRAINT FK_OrderItems_Products FOREIGN KEY (prod_id) REFERENCES Products (prod_id);
ALTER TABLE Orders ADD CONSTRAINT FK_Orders_Customers FOREIGN KEY (cust_id) REFERENCES Customers (cust_id);
ALTER TABLE Products ADD CONSTRAINT FK_Products_Vendors FOREIGN KEY (vend_id) REFERENCES Vendors (vend_id);

-------------------------------------------------------
-- Sams Teach Yourself SQL in 10 Minutes, 5th Edition
-- http://forta.com/books/0135182794/
-- Example table population scripts for MySQL & MariaDB
-------------------------------------------------------


-- ------------------------
-- Populate Customers table
-- ------------------------
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000001', 'Village Toys', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'John Smith', 'sales@villagetoys.com');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES('1000000002', 'Kids Place', '333 South Lake Drive', 'Columbus', 'OH', '43333', 'USA', 'Michelle Green');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000003', 'Fun4All', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'jjones@fun4all.com');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000004', 'Fun4All', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Denise L. Stephens', 'dstephens@fun4all.com');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES('1000000005', 'The Toy Store', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'Kim Howard');

-- ----------------------
-- Populate Vendors table
-- ----------------------
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('BRS01','Bears R Us','123 Main Street','Bear Town','MI','44444', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('BRE02','Bear Emporium','500 Park Street','Anytown','OH','44333', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('DLL01','Doll House Inc.','555 High Street','Dollsville','CA','99999', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('FRB01','Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('FNG01','Fun and Games','42 Galaxy Road','London', NULL,'N16 6PS', 'England');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('JTS01','Jouets et ours','1 Rue Amusement','Paris', NULL,'45678', 'France');

-- -----------------------
-- Populate Products table
-- -----------------------
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BR01', 'BRS01', '8 inch teddy bear', 5.99, '8 inch teddy bear, comes with cap and jacket');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BR02', 'BRS01', '12 inch teddy bear', 8.99, '12 inch teddy bear, comes with cap and jacket');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BR03', 'BRS01', '18 inch teddy bear', 11.99, '18 inch teddy bear, comes with cap and jacket');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BNBG01', 'DLL01', 'Fish bean bag toy', 3.49, 'Fish bean bag toy, complete with bean bag worms with which to feed it');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BNBG02', 'DLL01', 'Bird bean bag toy', 3.49, 'Bird bean bag toy, eggs are not included');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BNBG03', 'DLL01', 'Rabbit bean bag toy', 3.49, 'Rabbit bean bag toy, comes with bean bag carrots');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('RGAN01', 'DLL01', 'Raggedy Ann', 4.99, '18 inch Raggedy Ann doll');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('RYL01', 'FNG01', 'King doll', 9.49, '12 inch king doll with royal garments and crown');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('RYL02', 'FNG01', 'Queen doll', 9.49, '12 inch queen doll with royal garments and crown');

-- ---------------------
-- Populate Orders table
-- ---------------------
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20005, '2020-05-01', '1000000001');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20006, '2020-01-12', '1000000003');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20007, '2020-01-30', '1000000004');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20008, '2020-02-03', '1000000005');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20009, '2020-02-08', '1000000001');

-- -------------------------
-- Populate OrderItems table
-- -------------------------
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 1, 'BR01', 100, 5.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 2, 'BR03', 100, 10.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 1, 'BR01', 20, 5.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 2, 'BR02', 10, 8.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 3, 'BR03', 10, 11.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 1, 'BR03', 50, 11.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 2, 'BNBG01', 100, 2.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 3, 'BNBG02', 100, 2.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 4, 'BNBG03', 100, 2.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 5, 'RGAN01', 50, 4.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 1, 'RGAN01', 5, 4.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 2, 'BR03', 5, 11.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 3, 'BNBG01', 10, 3.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 4, 'BNBG02', 10, 3.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 5, 'BNBG03', 10, 3.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 1, 'BNBG01', 250, 2.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 2, 'BNBG02', 250, 2.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 3, 'BNBG03', 250, 2.49);

一、基本概念

database 数据库

表 table

模式 schemea

列 column

数据类型 datatype

行 row

主键 primary key

SQL Structured Query Language（结构化查询语言）的缩写。

数据库管理系统 DBMS

二、检索数据

2.1select语句

它的用途是从一个或多个表中检索信息，作为 SQL 组成部分的保留字。关键字不能用作表或列的名字。

2.2检索单个列

输入▼

SELECT prod_name FROM Products;

分析▼

上述语句利用 SELECT 语句从 Products 表中检索一个名为 prod_name 的列。所需的列名写在 SELECT 关键字之后，FROM 关键字指出从哪个表中检索数据。此语句的输出如下所示

输出▼

prod_name

------------------

说明：未排序数据如果你自己试验这个查询，可能会发现显示输出的数据顺序与这里的不同。出现这种情况很正常。如果没有明确排序查询结果（下一课介绍怎样指定顺序），则返回的数据没有特定的顺序。返回数据的顺序可能是数据被添加到表中的顺序，也可能不是。只要返回相同数目的行，就是正常的

提示：结束 SQL 语句多条 SQL 语句必须以分号（；）分隔。多数 DBMS 不需要在单条 SQL 语句后加分号，但也有 DBMS 可能必须在单条 SQL 语句后加上分号。当然，如果愿意可以总是加上分号。事实上，即使不一定需要，加上分号也肯定没有坏处。

提示：SQL 语句和大小写请注意，SQL 语句不区分大小写，因此 SELECT 与 select 是相同的。同样，写成 Select 也没有关系。许多 SQL 开发人员喜欢对 SQL 关键字使用大写，而对列名和表名使用小写，这样做代码更易于阅读和调试。不过，一定要认识到虽然 SQL 是不区分大小写的，但是表名、列名和值可能有所不同（这有赖于具体的 DBMS 及其如何配置）。

提示：使用空格在处理 SQL 语句时，其中所有空格都被忽略。SQL 语句可以写成长长的一行，也可以分写在多行。

2.3检索多个列

提示：当心逗号在选择多个列时，一定要在列名之间加上逗号，但最后一个列名后不加。如果在最后一个列名后加了逗号，将出现错误

输入▼

SELECT prod_id, prod_name, prod_price FROM Products;

分析▼

与前一个例子一样，这条语句使用 SELECT 语句从表 Products 中选择数据。在这个例子中，指定了 3 个列名，列名之间用逗号分隔。此语句的输出如下：

输出▼

prod_id prod_name prod_price

--------- -------------------- ---------

说明：数据表示 SQL 语句一般返回原始的、无格式的数据，不同的 DBMS 和客户端显示数据的方式略有不同（如对齐格式不同、小数位数不同）。数据的格式化是表示问题，而不是检索问题。因此，如何表示一般会在显示该数据的应用程序中规定。通常很少直接使用实际检索出的数据（没有应用程序提供的格式）

2.4检索所有列

select * from 。。。；

如果给定一个通配符（*），则返回表中所有列。列的顺序一般是表中出现的物理顺序，但并不总是如此。不过，SQL 数据很少直接显示（通常，数据返回给应用程序，根据需要进行格式化，再表示出来）。因此，这不应该造成什么问题

注意：使用通配符一般而言，除非你确实需要表中的每一列，否则最好别使用*通配符。虽然使用通配符能让你自己省事，不用明确列出所需列，但检索不需要的列通常会降低检索速度和应用程序的性能。

提示：检索未知列使用通配符有一个大优点。由于不明确指定列名（因为星号检索每一列），所以能检索出名字未知的列。

2.5检索不同的值

使用 DISTINCT 关键字，SELECT DISTINCT vend_id FROM Products;

如果使用 DISTINCT 关键字，它必须直接放在列名的前面

注意：不能部分使用 DISTINCT DISTINCT 关键字作用于所有的列，不仅仅是跟在其后的那一列。例如，你指定 SELECT DISTINCT vend_id, prod_price，则 9 行里的 6 行都会被检索出来，因为指定的两列组合起来有 6 个不同的结果。

2.6限制结果

SELECT prod_name FROM Products LIMIT 5；

LIMIT 5指示MySQL 等 DBMS 返回不超过 5 行的数据。

SELECT prod_name FROM Products LIMIT 5 OFFSET 5；

LIMIT 5 OFFSET 5 指示 MySQL 等 DBMS 返回从第 5 行起的 5 行数据。第一个数字是检索的行数，第二个数字是指从哪儿开始。

注意：第 0 行第一个被检索的行是第 0 行，而不是第 1 行。因此，LIMIT 1 OFFSET 1 会检索第 2 行，而不是第 1 行

提示：MySQL、MariaDB 和 SQLite 捷径 MySQL、MariaDB和 SQLite可以把 LIMIT 4 OFFSET 3 语句简化为 LIMIT 3,4。使用这个语法，逗号之前的值对应 OFFSET，逗号之后的值对应 LIMIT（反着的，要小心）

说明：并非所有的 SQL 实现都一样我加入这一节只有一个原因，就是要说明，SQL 虽然通常都有相当一致的实现，但你不能想当然地认为它总是这样。非常基本的语句往往是相通的，但较复杂的语句就不同了。当你针对某个问题寻找 SQL 解决方案时，一定要记住这一点

2.7使用注释

行内注释 --

#注释（有的dbms不支持）

/* */多行注释

2.8挑战题

1. 编写 SQL 语句，从 Customers 表中检索所有的 ID（cust_id）。

2. OrderItems 表包含了所有已订购的产品（有些已被订购多次）。编写 SQL 语句，检索并列出已订购产品（prod_id）的清单（不用列每个订单，只列出不同产品的清单）。提示：最终应该显示 7 行。

3. 编写 SQL语句，检索 Customers 表中所有的列，再编写另外的 SELECT 语句，仅检索顾客的 ID。使用注释，注释掉一条 SELECT 语句，以便运行另一条 SELECT 语句。（当然，要测试这两个语句。

3.排序检索数据

使用 SELECT 语句的 ORDER BY 子句，根据需要排序检索出的数据。

3.1排序数据

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

子句（clause） SQL 语句由子句构成，有些子句是必需的，有些则是可选的。一个子句通常由一个关键字加上所提供的数据组成。子句的例子有我们在前一课看到的 SELECT 语句的 FROM 子句

SELECT prod_name FROM Products ORDER BY prod_name;

为了明确地排序用 SELECT 语句检索出的数据，可使用 ORDER BY 子句。 ORDER BY 子句取一个或多个列的名字，据此对输出进行排序。

注意：ORDER BY 子句的位置在指定一条 ORDER BY 子句时，应该保证它是 SELECT 语句中最后一条子句。如果它不是最后的子句，将会出错。

提示：通过非选择列进行排序通常，ORDER BY 子句中使用的列将是为显示而选择的列。但是，实际上并不一定要这样，用非检索的列排序数据是完全合法的。

3.2按多个列排序

要按多个列排序，只须指定这些列名，列名之间用逗号分开即可

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

仅在多个行具有相同的 prod_price 值时才对产品按 prod_name 进行排序。如果 prod_price 列中所有的值都是唯一的，则不会按 prod_name 排序。

3.3按列位置排序

除了能用列名指出排序顺序外，ORDER BY 还支持按相对列位置进行排序。为理解这一内容，我们来看个例子

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

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

提示：按非选择列排序显然，当根据不出现在 SELECT 清单中的列进行排序时，不能采用这项技术。但是，如果有必要，可以混合使用实际列名和相对列位置。

3.4指定排序方向

数据排序不限于升序排序（从 A 到 Z），这只是默认的排序顺序。还可以使用 ORDER BY 子句进行降序（从 Z 到 A）排序。为了进行降序排序，必须指定 DESC 关键字。

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

如果打算用多个列排序，该怎么办？下面的例子以降序排序产品（最贵的在最前面），再加上产品名

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

DESC关键字只应用到直接位于其前面的列名。

警告：在多个列上降序排序如果想在多个列上进行降序排序，必须对每一列指定 DESC 关键字。

DESC 是 DESCENDING 的缩写，这两个关键字都可以使用。与 DESC 相对的是 ASC（或 ASCENDING），在升序排序时可以指定它。但实际上， ASC 没有多大用处，因为升序是默认的（如果既不指定 ASC 也不指定 DESC，则假定为 ASC）。

提示：区分大小写和排序顺序在对文本性数据进行排序时，A 与 a 相同吗？a 位于 B 之前，还是 Z 之后？这些问题不是理论问题，其答案取决于数据库的设置方式。在字典（dictionary）排序顺序中，A 被视为与 a 相同，这是大多数数据库管理系统的默认做法。但是，许多 DBMS 允许数据库管理员在需要时改变这种行为（如果你的数据库包含大量外语字符，可能必须这样做）。这里的关键问题是，如果确实需要改变这种排序顺序，用简单的 ORDER BY 子句可能做不到。你必须请求数据库管理员的帮助。

3.5挑战题

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;

答:没有by。vend_name后面不能有逗号。

4.过滤数据

这一课将讲授如何使用 SELECT 语句的 WHERE 子句指定搜索条件。

4.1使用where子句

只检索所需数据需要指定搜索条件（search criteria），搜索条件也称为过滤条件（filter condition）。在 SELECT 语句中，数据根据 WHERE 子句中指定的搜索条件进行过滤。 WHERE 子句在表名（FROM 子句）之后给出，如下所示：

SELECT prod_name, prod_price FROM Products WHERE prod_price = 3.49;

提示：有多少个 0？你在练习这个示例时，会发现显示的结果可能是 3.49、3.490、3.4900 等。出现这样的情况，往往是因为 DBMS 指定了所使用的数据类型及其默认行为。所以，如果你的输出可能与书上的有点不同，不必焦虑，毕竟从数学角度讲，3.49 和 3.4900 是一样的。

提示：SQL 过滤与应用过滤数据也可以在应用层过滤。为此，SQL 的 SELECT 语句为客户端应用检索出超过实际所需的数据，然后客户端代码对返回数据进行循环，提取出需要的行。通常，这种做法极其不妥。优化数据库后可以更快速有效地对数据进行过滤。而让客户端应用（或开发语言）处理数据库的工作将会极大地影响应用的性能，并且使所创建的应用完全不具备可伸缩性。此外，如果在客户端过滤数据，服务器不得不通过网络发送多余的数据，这将导致网络带宽的浪费

注意：WHERE 子句的位置在同时使用 ORDER BY 和 WHERE 子句时，应该让 ORDER BY 位于 WHERE 之后，否则将会产生错误（关于 ORDER BY 的使用，请参阅第 3 课）

4.2where子句操作符

注意：操作符兼容表 4-1 中列出的某些操作符是冗余的（如< >与!=相同，!< 相当于 >=）。并非所有 DBMS 都支持这些操作符。想确定你的 DBMS 支持哪些操作符，请参阅相应的文档

4.2.1检查单个值

列出所有价格小于 10 美元的产品。

SELECT prod_name, prod_price FROM Products WHERE prod_price < 10;

检索所有价格小于等于 10 美元的产品（因为没有价格恰好是 10 美元的产品，所以结果与前一个例子相同）

SELECT prod_name, prod_price FROM Products WHERE prod_price <= 10;

4.2.2不匹配检查

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

提示：何时使用引号如果仔细观察上述 WHERE 子句中的条件，会看到有的值括在单引号内，而有的值未括起来。单引号用来限定字符串。如果将值与字符串类型的列进行比较，就需要限定引号。用来与数值列进行比较的值不用引号。

4.2.3范围值检查

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

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

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

4.2.4空值检查

在一个列不包含值时，称其包含空值 NULL。

NULL 无值（no value），它与字段包含 0、空字符串或仅仅包含空格不同。

确定值是否为 NULL，不能简单地检查是否等于 NULL。SELECT 语句有一个特殊的 WHERE 子句，可用来检查具有 NULL 值的列。这个 WHERE 子句就是 IS NULL 子句。

SELECT prod_name FROM Products WHERE prod_price IS NULL;

注意：NULL 和非匹配通过过滤选择不包含指定值的所有行时，你可能希望返回含 NULL 值的行。但是这做不到。因为 NULL 比较特殊，所以在进行匹配过滤或非匹配过滤时，不会返回这些结果。

4.3挑战题

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.高级数据过滤

这一课讲授如何组合 WHERE 子句以建立功能更强、更高级的搜索条件。我们还将学习如何使用 NOT 和 IN 操作符。

5.1组合where子句

SQL 允许给出多个 WHERE 子句。这些子句有两种使用方式，即以 AND 子句或 OR 子句的方式使用

操作符（operator）用来联结或改变 WHERE 子句中的子句的关键字，也称为逻辑操作符（logical operator）

5.1.1and操作符

要通过不止一个列进行过滤，可以使用 AND 操作符给 WHERE 子句附加条件。

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

AND 用在 WHERE 子句中的关键字，用来指示检索满足所有给定条件的行。

说明：没有 ORDER BY 子句为了节省空间，也为了减少你的输入，我在很多例子里省略了 ORDER BY 子句。因此，你的输出完全有可能与书上的输出不一致。虽然返回行的数量总是对的，但它们的顺序可能不同。当然，如果你愿意也可以加上一个 ORDER BY 子句，它应该放在 WHERE 子句之后。

5.1.2 or操作符

OR 操作符与 AND 操作符正好相反，它指示 DBMS 检索匹配任一条件的行。事实上，许多 DBMS 在 OR WHERE 子句的第一个条件得到满足的情况下，就不再计算第二个条件了（在第一个条件满足时，不管第二个条件是否满足，相应的行都将被检索出来）。

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

OR WHERE 子句中使用的关键字，用来表示检索匹配任一给定条件的行。

5.1.3求值顺序

WHERE 子句可以包含任意数目的 AND 和 OR 操作符。允许两者结合以进行复杂、高级的过滤。

但在两者组合使用时，and会比or的优先级更高。可以使用圆括号来分割，圆括号的优先级高于and。

提示：在 WHERE 子句中使用圆括号任何时候使用具有 AND 和 OR 操作符的 WHERE 子句，都应该使用圆括号明确地分组操作符。不要过分依赖默认求值顺序，即使它确实如你希望的那样。使用圆括号没有什么坏处，它能消除歧义。

5.2 in 操作符

IN 操作符用来指定条件范围，范围中的每个条件都可以进行匹配。IN 取一组由逗号分隔、括在圆括号中的合法值。

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

为什么要使用 IN 操作符？其优点如下。

 在有很多合法选项时，IN 操作符的语法更清楚，更直观。

 在与其他 AND 和 OR 操作符组合使用 IN 时，求值顺序更容易管理。

 IN 操作符一般比一组 OR 操作符执行得更快（在上面这个合法选项很少的例子中，你看不出性能差异）。

 IN 的最大优点是可以包含其他 SELECT 语句，能够更动态地建立 WHERE 子句。

IN WHERE 子句中用来指定要匹配值的清单的关键字，功能与 OR 相当。

5.3 not操作符

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

NOT WHERE 子句中用来否定其后条件的关键字。

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

上面的例子也可以使用<>操作符来完成。

SELECT prod_name FROM Products WHERE vend_id <> 'DLL01' ORDER BY prod_name;

为什么使用 NOT？对于这里的这种简单的 WHERE 子句，使用 NOT 确实没有什么优势。但在更复杂的子句中，NOT 是非常有用的。例如，在与 IN 操作符联合使用时，NOT 可以非常简单地找出与条件列表不匹配的行。

说明：MariaDB 中的 NOT MariaDB 支持使用 NOT 否定 IN、BETWEEN 和 EXISTS 子句。大多数 DBMS 允许使用 NOT 否定任何条件。

5.4 挑战题

1. 编写 SQL 语句，从 Vendors 表中检索供应商名称（vend_name），仅返回加利福尼亚州的供应商（这需要按国家[USA]和州[CA]进行过滤，没准其他国家也存在一个加利福尼亚州）。提示：过滤器需要匹配字符串。

2. 编写 SQL 语句，查找所有至少订购了总量 100 个的 BR01、BR02 或 BR03 的订单。你需要返回 OrderItems 表的订单号（order_num）、产品 ID（prod_id）和数量，并按产品 ID 和数量进行过滤。提示：根据编写过滤器的方式，可能需要特别注意求值顺序。

3. 现在，我们回顾上一课的挑战题。编写 SQL 语句，返回所有价格在 3 美元到 6美元之间的产品的名称（prod_name）和价格（prod_price）。使用 AND，然后按价格对结果进行排序。

4. 下面的 SQL 语句有问题吗？（尝试在不运行的情况下指出。）

SELECT vend_name FROM Vendors ORDER BY vend_name WHERE vend_country = 'USA' AND vend_state = 'CA';

答：ORDER BY 位置错误。

6.用通配符进行过滤

这一课介绍什么是通配符、如何使用通配符，以及怎样使用 LIKE 操作符进行通配搜索，以便对数据进行复杂过滤。

6.1like操作符

前面介绍的所有操作符都是针对已知值进行过滤的。不管是匹配一个值还是多个值，检验大于还是小于已知值，或者检查某个范围的值，其共同点是过滤中使用的值都是已知的。但是，这种过滤方法并不是任何时候都好用。例如，怎样搜索产品名中包含文本 bean bag 的所有产品？用简单的比较操作符肯定不行，必须使用通配符。利用通配符，可以创建比较特定数据的搜索模式。在这个例子中，如果你想找出名称包含 bean bag 的所有产品，可以构造一个通配符搜索模式，找出在产品名的任何位置出现 bean bag 的产品。

通配符（wildcard）用来匹配值的一部分的特殊字符。

搜索模式（search pattern）由字面值、通配符或两者组合构成的搜索条件。

通配符本身实际上是 SQL 的 WHERE 子句中有特殊含义的字符，SQL 支持几种通配符。为在搜索子句中使用通配符，必须使用 LIKE 操作符。LIKE 指示 DBMS，后跟的搜索模式利用通配符匹配而不是简单的相等匹配进行比较。

谓词（predicate）操作符何时不是操作符？答案是，它作为谓词时。从技术上说，LIKE 是谓词而不是操作符。虽然最终的结果是相同的，但应该对此术语有所了解，以免在 SQL 文献或手册中遇到此术语时不知所云。

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

6.1.1 百分号（%）通配符

最常使用的通配符是百分号（%）。在搜索串中，%表示任何字符出现任意次数。

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

%告诉 DBMS 接受 Fish 之后的任意字符，不管它有多少字符。

说明：区分大小写根据 DBMS 的不同及其配置，搜索可以是区分大小写的。如果区分大小写，则'fish%'与 Fish bean bag toy 就不匹配。

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

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

提示：根据部分信息搜索电子邮件地址有一种情况下把通配符放在搜索模式中间是很有用的，就是根据邮件地址的一部分来查找电子邮件，例如 WHERE email LIKE 'b%@forta.com'。

需要特别注意，除了能匹配一个或多个字符外，%还能匹配 0 个字符。% 代表搜索模式中给定位置的 0 个、1 个或多个字符。

说明：请注意后面所跟的空格有些 DBMS 用空格来填补字段的内容。例如，如果某列有 50 个字符，而存储的文本为 Fish bean bag toy（17 个字符），则为填满该列需要在文本后附加 33 个空格。这样做一般对数据及其使用没有影响，但是可能对上述 SQL 语句有负面影响。子句 WHERE prod_name LIKE 'F%y'只匹配以 F 开头、以 y 结尾的 prod_name。如果值后面跟空格，则不是以 y 结尾，所以 Fish bean bag toy 就不会检索出来。简单的解决办法是给搜索模式再增加一个%号：'F%y%'还匹配 y 之后的字符（或空格）。更好的解决办法是用函数去掉空格。请参阅第 8 课。

注意：请注意 NULL 通配符%看起来像是可以匹配任何东西，但有个例外，这就是 NULL。子句 WHERE prod_name LIKE '%'不会匹配产品名称为 NULL 的行。

6.1.2 下划线（_）通配符

另一个有用的通配符是下划线（_）。下划线的用途与%一样，但它只匹配单个字符，而不是多个字符。

说明：DB2 通配符 DB2 不支持通配符_。

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

说明：请注意后面所跟的空格与上例一样，可能需要给这个模式添加一个通配符。

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

6.1.3 方括号（[]）通配符

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

说明：并不总是支持集合与前面描述的通配符不一样，并不是所有 DBMS 都支持用来创建集合的[]。微软的 SQL Server 支持集合，但是 MySQL，Oracle，DB2，SQLite 都不支持。为确定你使用的 DBMS 是否支持集合，请参阅相应的文档。

笔者用的mysql用不了。。。。。。不多做尝试了

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

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

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

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;

6.2使用通配符的技巧

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

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

 在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始处。把通配符置于开始处，搜索起来是最慢的。

 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。总之，通配符是一种极其重要和有用的搜索工具，以后我们经常会用到它。

6.3挑战题

1. 编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中包含 toy 一词的产品。

2. 反过来再来一次。编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中未出现 toy 一词的产品。这次，按产品名称对结果进行排序。

3. 编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中同时出现 toy 和 carrots 的产品。有好几种方法可以执行此操作，但对于这个挑战题，请使用 AND 和两个 LIKE 比较。

4. 来个比较棘手的。我没有特别向你展示这个语法，而是想看看你根据目前已学的知识是否可以找到答案。编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回在描述中以先后顺序同时出现 toy 和 carrots 的产品。提示：只需要用带有三个 % 符号的 LIKE 即可。

2023/4/13 22:18 明天继续

少年嘞

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
SQL必知必会阅读笔记，作者（Ben Forta）学习贴

在编写sql基础语法的记录贴时略感吃力，回来学习一下大佬的著作《SQL必知必会》在学习这本书前应该有大致的sql语言基础和理解。起码知道creat等关键字和登录数据库等。本书所使用测试用例可以在http://forta.com/books/0135182794 自行寻找。需要书籍pdf可联系我。
复制链接

扫一扫