SQL 必知必会

时间：2021-10-09 00:27:10

本文介绍基本的 SQL 语句，包括查询、过滤、排序、分组、联结、视图、插入数据、创建操纵表等。入门系列，不足颇多，望诸君指点。

注意本文某些例子只能在特定的DBMS中实现（有的已标明，有的未标明），不同的DBMS有不同的语法和函数，具体请查阅相应的文档。

参考书籍：

Ben Forta.《SQL必知必会》.第4版

王姗 萨师煊.《数据库系统概论》.第5版

（不会做目录链接，蓝瘦，香菇……好像也太长了，以后改写连载……）

搭建数据库

供应商表   Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)

产品表      Products(prod_id, vend_id, prod_name, prod_price, prod_desc)

顾客表      Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)

订单表      Orders(order_num, order_date, cust_id)

产品表      Products(prod_id, vend_id, prod_name, prod_price, prod_desc

------------------------- Create Vendors table-----------------------
CREATE TABLE Vendors
(
  vend_id      ) NOT NULL ,        --唯一的供应商ID
  vend_name    ) NOT NULL ,        --供应商名
  vend_address ) NULL ,            --供应商地址
  vend_city    ) NULL ,            --供应商所在城市
  vend_state   )  NULL ,            --供应商所在州
  vend_zip     ) NULL ,            --供应商地址邮政编码
  vend_country ) NULL              --供应商所在国家
);

-------------------------- Create Products table------------------------
CREATE TABLE Products
(
  prod_id    )      NOT NULL ,     --唯一的产品ID
  vend_id    )      NOT NULL ,     --产品供应商ID（关联到Vendorse表vend_id）
  prod_name  )     NOT NULL ,     --产品名
  prod_price ,)  NOT NULL ,     --产品价格
  prod_desc  ) NULL           --产品描述
);

--------------------------- Create Customers table-------------------------
CREATE TABLE Customers
(
  cust_id      )  NOT NULL ,       --唯一的顾客ID
  cust_name    )  NOT NULL ,       --顾客名
  cust_address )  NULL ,           --顾客地址
  cust_city    )  NULL ,           --顾客所在城市
  cust_state   )   NULL ,           --顾客所在州
  cust_zip     )  NULL ,           --顾客地址邮政编码
  cust_country )  NULL ,           --顾客所在国家
  cust_contact )  NULL ,           --顾客的联系名
  cust_email   ) NULL             --顾客的email
);

------------------------ Create Orders table----------------------
CREATE TABLE Orders
(
  order_num  int      NOT NULL ,          --唯一的订单号
  order_date datetime NOT NULL ,          --订单日期
  cust_id    ) NOT NULL            --订单顾客ID（关联到Customers表的cust_id）
);

---------------------------- Create OrderItems table--------------------------
CREATE TABLE OrderItems
(
  order_num  int          NOT NULL ,      --订单号（关联到Orders表的order_num）
  order_item int          NOT NULL ,      --订单物品（int，订单内的顺序）
  prod_id    )     NOT NULL ,      --产品ID（关联到Products表的prod_id）
  quantity   int          NOT NULL ,      --物品数量
  item_price ,) NOT NULL        --物品价格
);                                        --一个订单有几种物品就有几行

------------------------ Define primary keys----------------------
ALTER TABLE Customers WITH NOCHECK ADD CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (cust_id);
ALTER TABLE OrderItems WITH NOCHECK ADD CONSTRAINT PK_OrderItems PRIMARY KEY CLUSTERED (order_num, order_item);
ALTER TABLE Orders WITH NOCHECK ADD CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED (order_num);
ALTER TABLE Products WITH NOCHECK ADD CONSTRAINT PK_Products PRIMARY KEY CLUSTERED (prod_id);
ALTER TABLE Vendors WITH NOCHECK ADD CONSTRAINT PK_Vendors PRIMARY KEY CLUSTERED (vend_id);

------------------------ Define foreign keys----------------------
ALTER TABLE OrderItems ADD
CONSTRAINT FK_OrderItems_Orders FOREIGN KEY (order_num) REFERENCES Orders (order_num),
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);

----------------------------- Populate Customers table---------------------------
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
', '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)
', 'USA', 'Michelle Green');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
', '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)
', '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)
', 'USA', 'Kim Howard');

--------------------------- Populate Vendors table-------------------------
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
', '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)
', '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)
, ');
INSERT INTO Orders(order_num, order_date, cust_id)
, ');
INSERT INTO Orders(order_num, order_date, cust_id)
, ');
INSERT INTO Orders(order_num, order_date, cust_id)
, ');
INSERT INTO Orders(order_num, order_date, cust_id)
, ');

------------------------------ Populate OrderItems table----------------------------
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
, , , 5.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
, , , 10.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
, , , 5.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
, , , 8.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
, , , 11.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
, , , 11.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
, , , 2.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
, , , 2.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
, , , 2.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
, , , 4.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
, , , 4.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
, , , 11.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
, , , 3.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
, , , 3.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
, , , 3.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
, , , 2.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
, , , 2.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
, , , 2.49);

SQL 必知必会

1. 检索数据

1.1 检索单个列

SELECT prod_name
FROM Products;

注意事项：

1.该查询返回未排序数据，如果没有明确排序查询结果，则返回的数据没有特定的数据，可能是数据被添加到列表中的顺序，也可能不是。后面所有的检索结果在没有明确排序时都是无序的。

2.多条SQL语句必须以分号(;)分隔，单条语句可加可不加。

3.SQL语句不区分大小写。许多开发人员喜欢对SQL关键字使用大写，而对列名和表名使用小写，这样使代码易于阅读和调试。但是表名、列名和值依赖于具体的DBMS及其如何配置。

4.每个SQL语句的关键字之间用空格符号分隔，而对换行没有要求。多数开发人员认为，将SQL语句分成多行易于阅读和调试。

1.2 检索多个列

SELECT prod_id,prod_name,prod_price
FROM Products;

5.选择多个列时，要在列名之间加上逗号(,)分隔，最后一个列名不加，加了将出现错误。

1.3 检索所有列

SELECT *
FROM Products;

6.通配符(*)在不明确指定列名时能检索出名字位置的列，但是检索不需要的列通常会降低检索和应用程序的性能。

1.4 检索不同的值

SELECT DISTINCT vend_id
FROM Products;

SQL 必知必会

7.DISTINCT关键字作用于所有列，不仅仅是跟在其后的列。

例如：

SELECT prod_price
FROM Products;
SELECT DISTINCT vend_id,prod_price
FROM Products;

SQL 必知必会SQL 必知必会

1.5 限制结果

在SQL Sever和Access中

 prod_name
FROM Products;

在Oralce中

SELECT prod_name
FROM Products
;  --行计数器

在MySQL中

SELECT prod_name
FROM Products
LIMIT ;    --返回不超过5行的数据

SELECT prod_name
FROM Products
LIMIT  OFFSET ;    --从第4行起的5行数据

SELECT prod_name
FROM Products
LIMIT ,;   --从第3行起的4行数据

1.6 注释

--之后的文本为注释，#之后的文本为注释，/* */之间的文本为注释。

2. 排序检索数据

2.1 按列排序

子句(clause)：SQL语句由子句构成，有些子句是必需的，有些则是可选的，一个子句通常由一个关键字加上所提供的数据组成。如SELECT语句中的FROM子句。

SELECT prod_name
FROM Products
ORDER BY prod_name;  --对prod_name列以字母顺序排序数据

8.ORDER BY 子句应该是SELECT语句中最后一条子句。

9.ORDER BY 子句使用的列可以是非检索的列，即用非检索的列排序数据。

2.2 按多个列排序数据

SELECT prod_id,prod_price,prod_name
FROM Products
ORDER BY prod_price,prod_name;  --在多个行具有相同的prod_price值时才对产品按prod_name进行排序。如果prod_price列中所有的值都是唯一的，则不会按prod_name排序

2.3 按列位置排序

SELECT prod_id,prod_price,prod_name
FROM Products
,; --按 SELECT 清单中第2列，第3列排序，即按prod_price,prod_name排序 

10.缺点：不明确给出列名可能造成错用列名；对 SELECT 清单进行更改时容易忘记对 ORDER BY 子句做相应的更改；如果进行排序的列不在 SELECT 清单中不能用该技术。

（如有必要，可以混合使用实际列名和相对列位置）

2.4 指定排序方向

SELECT prod_id,prod_price,prod_name
FROM Products
ORDER BY prod_price DESC;  --按prod_price降序排列

SELECT prod_id,prod_price,prod_name
FROM Products
ORDER BY prod_price DESC,prod_name;  --先按prod_price降序排列，再按prod_name排列

11.DESC 关键字只应用到直接位于其前面的列。如果想在多个列上进行降序，必须对每一列制定 DESC 关键字。

（DESC 是 DESCENDING 的缩写，这两个关键字都可以用。而升序的关键字 ASC/ASENGDING 没有多大用处，因为升序是默认的。）

12. a 与 B 的排序取决于数据库的设置方式，但在字典（dic）排序中，大多数数据库 a 排在 B 前。

3. 过滤数据

3.1 检查当个值

SELECT prod_name,prod_price
FROM Products
WHERE prod_price = 3.49;   --过滤出prod_price值为3.49的产品的名称和价格，数据小数点和位数与数据库的设置有关

SQL 必知必会

13.在同时使用 ORDER BY 和 WHERE 子句时，应该让 ORDER BY 位于 WHERE 之后。

3.2 不匹配检查

SELECT vend_id,prod_name
FROM Products
WHERE vend_id != 'DDL01';   -- <> 与 != 相同

14.参数为字符型时需要使用单引号，数值型不需要。

3.3 范围检查

SELECT prod_name,prod_price
FROM Products
 ;  --过滤出prod_price在5到10之间的所有产品

15. WHERE 子句可以过滤 SELECT 语句清单中没有的列。

3.4 空值检查

SELECT cust_name
FROM Customers
WHERE cust_email IS NULL; --过滤出cust_email为空的顾客

16.NULL 不同于0、空格，0和空格占内存，而 NULL 未分配内存。

17.通过过滤选择不包含指定值的所有行时，不会返回 NULL 值的行。因为未知（unknown）有特殊的含义，数据库不知道他们是否匹配。

4. 高级数据过滤

4.1 组合WHERE 子句

操作符(operator)：用来联结或改变 WHERE 子句中的子句的关键字，也称为逻辑操作符。

SELECT prod_id,prod_price,prod_name
FROM Products
;  --过滤出由供应商DLL01提供且价格小于等于4美元的所有产品
SELECT prod_name,prod_price,vend_id
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01'  --过滤出由供应商商DLL01或BRS01提供的所有产品的名称
ORDER BY vend_id DESC; 
SELECT prod_name,prod_price,vend_id
FROM Products

ORDER BY vend_id DESC;

SQL 必知必会

18.SQL 在处理 OR 操作符前，优先处理 AND 操作符。上述语句意思是：由DDL01生产的所有产品或由BRS01生产的价格在10以上的产品。

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

SELECT prod_name,prod_price
FROM Products
;

4.2 IN操作符

SELECT prod_name,prod_price
FROM Products
WHERE vend_id IN ('DLL01','BRS01') --过滤出由DLL01和BRS01提供的所有产品
ORDER BY prod_name;

19. IN 操作符完成与 OR 相同的功能。IN 操作符比 OR 执行更快，语法更直观。

4.3 NOT 操作符

SELECT prod_name
FROM Products
WHERE NOT vend_id ='DLL01';  --过滤出非DLL01提供的所有产品
SELECT prod_name,prod_price
FROM Products
WHERE  NOT vend_id IN ('DLL01','BRS01') --过滤出非DLL01、BRS01提供的所有产品
ORDER BY prod_name; 
SELECT cust_name,cust_email
FROM Customers
WHERE cust_email is not null;  --过滤出邮箱非空的所有顾客

5. 用通配符进行过滤

通配符(wildcard)：用来匹配值得一部分的特殊字符。

搜索模式(search pttern)：由字面值、通配符或两者组合而成的搜索条件。

LIKE 谓词(predicate)：指示DBMS,后跟的搜索模式利用通配符匹配而不是简单的相等匹配进行比较。

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

5.1 % 通配符

Access 使用 * 而不是 %

% ：表示任何字符出现任意次数。

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_id,prod_name
FROM Products
WHERE prod_name LIKE 'F%y';  --过滤出名称以F开头y结尾的所有产品 
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE 'J%' OR  cust_contact LIKE 'M%' --过滤出联系名以J开头或者以M开头的顾客
ORDER BY cust_contact; 

21.大小写和DBMS的配置有关。

22.当字段名后有空格时，不能用 'F%y' 匹配，应用函数去掉空格或用 'F%y%' 匹配。'%' 也不能匹配 NULL 。

5.2 _ 通配符

DB2 不支持 _ 通配符，Access 使用 ？ 而不是 _ 。

_  : 和 % 一样，但它只匹配单个字符。

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

SQL 必知必会

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

SQL 必知必会

SELECT *
FROM Customers
WHERE  cust_name LIKE 'Ki\_s'  ESCAPE '\';    --如果查询的字符串本身含有通配符，需要用 ESCAPE '转义字符' 进行转义

SELECT *
FROM Customers
WHERE  cust_name LIKE 'Ki\_%s__'  ESCAPE '\';   --以 Ki_ 开头，倒数第三个字符为 s

5.3 [] 通配符

[] : 匹配指定位置（通配符的位置）[] 中的一个字符。

SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[JM]%'  --过滤出联系名以J开头或者以M开头的顾客
ORDER BY cust_contact;
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[^JM]%'   --过滤出所有联系名不是J、M开头的顾客，同 NOT cust_contact LIKE '[JM]%' , Access中用 [!JM]
ORDER BY cust_contact;

23.通配符影响搜索性能。能用其它操作符达到相同目的时，应该使用其它操作符。使用通配符时尽量不要放在搜索模式的开始处，且要主要通配符的位置。

查询条件 操作符
比较  =, >, <, >=, <=, !=, <>, !>, !<; NOT + 上述比较符        
确定范围  BETWEEN AND, NOT BETWEEN AND
确定集合  IN, NOT IN
字符匹配  LIKE, NOT LIKE
空值  IS NULL, IS NOT NULL
逻辑运算                  AND, OR, NOT

6.创建计算字段

直接从数据库中检索出转换、计算或格式化过的数据，而不是检索出数据再在客户端应用程序中重新格式化。一般来说，在数据库服务器上完成这些操作比在客户端中完成要快得多。

6.1拼接字段

拼接(concatenate)：将值联结到一起（将一个值附加到另一个值）构成当个值。

SELECT vend_name + '(' + vend_country + ')'  -- 在Oracle中使用 || 而不是 + ，在 MySQL 中使用 Concat(vend_name,'(',vend_country,')')
FROM Vendors
ORDER BY vend_name;

SQL 必知必会

SELECT RTRIM(vend_name) + '(' + RTRIM(vend_country) + ')'   -- RTRIM()去掉值右边的空格，LTRIM()去掉值左边的空格，TRIM()不多说了
FROM Vendors
ORDER BY vend_name;

SQL 必知必会

SELECT RTRIM(vend_name) + '(' + RTRIM(vend_country) + ')' AS vend_title --定义别名，方面在客户端应用中引用
FROM Vendors
ORDER BY vend_name;

SQL 必知必会

6.2 执行算术计算

SELECT prod_id,quantity,item_price,
       quantity*item_price AS expanded_price --计算字段
FROM OrderItems
;

SQL 必知必会

SQL 必知必会

24.当 SELECRT 语句中不包含 FROM 子句时可以用来测试计算。如 SELECT 3*2 ;SELECT TRIM('   F F   ');

7. 使用函数处理数据

可移植(portable)：所编写的代码可以在多个系统上运行。

SQL 函数是不可移植的。

7.1 文本处理函数

SELECT vend_name,UPPER(vend_name) AS vend_name_upcase  --将供应商名转换为大写名为 vend_name_upcase
FROM Vendors
ORDER BY vend_name

SQL 必知必会

SELECT cust_name,cust_contact
FROM Customers
WHERE cust_contact ='Michael GREEN';

SELECT cust_name,cust_contact
FROM Customers
WHERE SOUNDEX (cust_contact) =SOUNDEX ('Michael GREEN');  --过滤出与 Michael GREEN 发音相似的联系名

SQL 必知必会 

7.2 日期和时间处理函数

在SQL Server 中

SELECT order_num
FROM Orders
;   --过滤出2012产生的所有订单

在 Oracel 中

SELECT order_num
FROM Orders
;  --to_char()提取日期的成分，to_number将提取出来的成分转换为数值，以便能与2012比较

SELECT order_num
FROM Orders
WHERE orders_date BETWEEN to_date('01-01-2012') AND to_date('12-31-2012');      --to_date()函数用来将两个字符串转换为日期

在MySQL中

SELECT order_num
FROM Orders
;    --YEAR()函数从日期中提取年份

25.DBMS提供的功能远不止简单的日期成分提取。大多数DBMS具有比较日期、执行基于日期的运算、选择日期格式等函数。但是，可以看到，不同DBMS的日期-时间处理函数可能不同。关于具体的DBMS支持的日期-时间处理函数，请参阅相应的文档。

7.3 数值处理函数

在主要的DBMS的函数中，数值函数是最一致、最统一的函数。

SQL 必知必会

关于具体DBMS所支持的算术处理函数，请参阅相应的文档。

8. 汇总数据

聚集函数(aggregate function)： 对某些行运行的函数，计算并返回一个值（汇总数据而不把它们实际检索出来）。

SQL 的聚集函数在各种主要的DBMS中有相当一致的支持。

8.1 聚集函数

SQL 必知必会

SELECT AVG(prod_price) AS avg_price   --avg_price是别名，值为表中所有产品的平均价格
FROM Products;

SQL 必知必会 

SELECT AVG(prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';  --该语句返回由 DLL01 提供的所有产品的平均价格

SQL 必知必会 

26.AVG() 只能用来确定特定数字列的平均值，列名必须作为函数参数给出。获得多个列的平均值，必须使用多个AVG() 函数。AVG() 忽略 NULL 行。

SELECT COUNT (*) AS num_cust,COUNT (cust_email ) AS num_cust
FROM Customers ;

SQL 必知必会

27. COUNT (*) 对表中行的数目进行计数，不管列表中包含的是空值 ( NULL) 还是非空值。

COUNT(column) 对特定列中具有值的行进行计数，忽略 NULL 值。

SELECT MAX (order_date) AS max_order_date  --最大订单日期
FROM Orders ;

SQL 必知必会
找最大日期的订单号
SELECT MAX (order_date) AS max_order_date,cust_id
FROM Orders ;

SQL 必知必会 

SELECT order_date,cust_id
FROM Orders
WHERE order_date = MAX (order_date);

SQL 必知必会

SELECT order_date,cust_id
FROM orders
WHERE order_date=(SELECT MAX(order_date)
                  FROM orders);

) order_date,cust_id
FROM orders
ORDER BY  order_date DESC; 

28.聚集函数只能用于 SELECT 语句和 HAVING 子句中。

29.MAX() 函数一般用来找出最大的数值或日期，但在用于本文数据时，MAX() 返回按该列排序后的最后一行。MAX() 忽略值为 NULL 的行。

MIN() 函数一般用来找出最小的数值或日期，但在用于本文数据时，MIN() 返回按该列排序后的最前一行。MAX() 忽略值为 NULL 的行。

SELECT SUM (quantity) AS items_ordered
FROM OrderItems
; 
SELECT SUM (item_price*quantity) AS total_price  --订单中所有物品的价钱之和
FROM OrderItems
;  --过滤出某个订单

30. 利用标准的算术操作符，所有的聚集函数都可以来执行多个列上的计算。SUM() 函数也忽略列值为 NULL 的列。

8.2 聚集不同值

SELECT AVG( DISTINCT prod_price) AS avg_price   --所有异价产品的价格平均值
FROM Products
WHERE vend_id = 'DLL01';
SELECT COUNT( DISTINCT prod_price) AS prod_price_num   --所有异价产品的数量；表示共有几种价格
FROM Products
WHERE vend_id = 'DLL01';

31. DISTINCT 不能用于 COUNT(*) ，用于 MAX() MIN() 无意义。

8.3 组合聚集函数

SELECT COUNT (*) AS items_num,
       MIN (prod_price ) AS price_min,
       MAX (prod_price ) AS price_max,
       AVG (prod_price ) AS Price_avg
FROM Products;

SQL 必知必会

32.别名一般不与列名相同，一般不用相同的列明，避免错误。

9. 分组数据

目前为止的所有计算都是在表的所有数据或匹配特定的 WHERE 子句的数据上进行的。如果要返回供应商提供的产品数目、只提供一项产品的供应商的产品（用子查询）或者提供10个以上产品的供应商的产品（用子查询），这时就要用到数据分组了。

9.1 创建分组

SELECT vend_id, COUNT (*) AS num_prods  --对表行的数目进行计数，包括 NULL 值
FROM Products
GROUP BY vend_id   --按 vend_id 排序并分组。对每个 vend_id 计算 num_prods 一次（而不是整个表）
ORDER BY num_prods ;  --按 num_prods 排序

SQL 必知必会 
SELECT  COUNT (*) AS num_prods  --对表行的数目进行计数，包括 NULL 值
FROM Products
GROUP BY vend_id   --按 vend_id 排序并分组。对每个 vend_id 计算 num_prods 一次（而不是整个表）

SQL 必知必会

33. GROUP BY 子句可以包含任意数目的列，因而可以对分组进行嵌套，更细致的分组。

34.如果在 GROUP BY 子句中嵌套了分组，数据将在最后指定的分组上进行汇总。

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

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

37.除聚集函数外，SELECT 语句中的每一列都必须在 GROUP BY 子句中给出。但 GROUP BY 子句中的列可以不再 SELECT 清单中。

38.如果分组列中包含 NULL 值的行，则 NULL 将作为一个分组返回。如果列中有多行的 NULL 值，它们将分一组。

39.GROUP BY 子句必须出现在 WHERE 子句之后， ORDER BY 子句之前。

9.2 过滤分组

SELECT cust_id, COUNT (*) AS orders
FROM Orders
GROUP BY cust_id
;

40. WHERE 子句过滤行，HAVING 子句过滤分组；WHERE 子句在数据分组前过滤，HAVING 子句在数据分组后过滤。

HAVING 子句后不能使用别名，HAVING 子句后可以跟 SELECT 清单中没有的列。

SELECT vend_id, COUNT (*) AS num_prods
FROM Products

GROUP BY vend_id
;
SELECT prod_name,vend_id
FROM Products
WHERE vend_id IN (SELECT vend_id
                  FROM Products
                  GROUP BY vend_id
                  );

SQL 必知必会

/*查询买了所有产品的顾客名单*/
SELECT Customers.cust_id,cust_name,order_num
INTO COPY
FROM Customers LEFT OUTER JOIN Orders ON Customers.cust_id=Orders.cust_id ;

SELECT cust_name
FROM  COPY LEFT OUTER JOIN OrderItems ON OrderItems.order_num = COPY.order_num
GROUP BY cust_name
HAVING COUNT(prod_id) = (SELECT COUNT(prod_id)
                         FROM Products);

9.3 排序分组

SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num

ORDER BY items,order_num;

10 使用子查询

迄今为止看到的所有 SELECT 语句都是简单查询，即从单个数据库表中检索数据的单条语句。子查询即嵌套在其他查询中的查询。

10.1 利用子查询进行过滤

查询订购物品 RGAN01 的所有顾客名称和联系名。

SQL 必知必会

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

SQL 必知必会

41.作为子查询的 SELECT 语句只能查询一个列。子查询影响性能。

10.2 使用子查询作为计算字段

查询 Customers 表中每个顾客的订单数。

SELECT cust_id,COUNT (*) AS num_order  --只能查询产生订单顾客的订单个数，且不能查询顾客的其它信息
FROM Orders
GROUP BY cust_id;

SQL 必知必会

SELECT cust_name,
       (SELECT COUNT(*)
        FROM Orders
        WHERE Orders.cust_id = Customers.cust_id) AS orders  --完全限制列名，避免产生歧义
FROM Customers
ORDER BY orders;

SQL 必知必会

42. 如果 WHERE 或 ORDER BY 子句指定的某个列名出现在多个表格中，应使用完全限制列名避免歧义。

11. 联结表

SQL 最强大的功能之一就是能在数据查询的执行中联结 (join) 表。联结是利用 SQL 的 SELECT 能执行的最重要的操作。但联结不是物理实体，它在实际的数据库中并不存在，DBMS 会根据需要建立联结，它在查询执行期间一直存在。

11.1 等值联结

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

SQL 必知必会

SELECT cust_name,cust_contact
FROM Customers,Orders,OrderItems
WHERE  Customers.cust_id=Orders.cust_id AND Orders.order_num=OrderItems.order_num AND OrderItems.prod_id ='RGAN01'
ORDER BY cust_name;

SQL 必知必会

43.必须保证所有的联结都要有 WHERE 子句，不然 DBMS 将返回笛卡儿积。

联结字段类型必须是可比的，但字段名称不必相同。

11.2 内联结

内联结(inner join)：基于两个表之间的相等测试，也称为等值联结(equijoin)

SELECT vend_name,prod_name,prod_price
FROM Products INNER JOIN  Vendors
 ON Vendors.vend_id = Products.vend_id;  --内联结的规范语法，之前使用的是简单的等值联结；对表名称的位置没有要求

12. 创建高级联结

12.1 使用表别名

44.表别名的作用：缩短 SQL 语句；允许一条 SELECT 语句中多次使用相同的表。

SELECT cust_name,cust_contact
FROM Customers AS C,Orders AS O,OrderItems AS OI
WHERE  C.cust_id=O.cust_id AND O.order_num=OI.order_num AND OI.prod_id ='RGAN01'
ORDER BY cust_name;

Oracel 中去掉 AS。

12.2 自然联结

SELECT Customers.*,Orders.*,OrderItems.*
FROM Customers,Orders,OrderItems
WHERE Customers.cust_id=Orders.cust_id AND Orders.order_num=OrderItems.order_num AND OrderItems.prod_id ='RGAN01'

SQL 必知必会

检索所有的列发现内联结返回所有的列，包括重复列。自然联结排除多次出现，使每一列只返回一次。

45.我们要手动完成自然联结，一般对第一个表使用通配符，对其他表使用明确的子集。

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 O.order_num=OI.order_num AND OI.prod_id ='RGAN01'
ORDER BY cust_name;

SQL 必知必会

12.3 外联结

外联结(outer join)：联结包含了那些在相关表格中没有关联行的行。

如：对每个顾客下的订单进行计数，包括那些至今尚未下订单的顾客；列出所有产品以及订购数量，包括那些没有人订购的产品；

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

SQL 必知必会

SELECT Customers.cust_name,COUNT(order_num) AS num_order
FROM Customers LEFT OUTER JOIN Orders
 ON Orders.cust_id = Customers.cust_id
GROUP BY Customers.cust_name
ORDER BY num_order;

SQL 必知必会

SELECT Products.prod_name,SUM(quantity) AS num_sales
FROM Products LEFT OUTER JOIN OrderItems
 ON Products.prod_id = OrderItems.prod_id
GROUP BY Products.prod_name
ORDER BY num_sales DESC;

SQL 必知必会

46. LEFT OUTER JOIN 表示包括左边表的所有行，RIGHT OUTER JOIN 表示包括右边表的所有行。调整表格顺序可以转换左右联结。

13. 组合查询

执行多个查询（多条 SELECT 语句），并将结果作为一个查询结果返回。

使用情况：在一个查询中从不同的表中返回数据结构；对一个表执行多个查询，按一个查询返回数据。

13.1 并操作 UNION

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

SQL 必知必会

SELECT cust_name,cust_contact,cust_email
FROM Customers
WHERE cust_state IN ( 'IL','IN','MI') OR cust_name='Fun4ALL';

47.UNION 与 WHERE 子句功能相似，性能有待测试。

48.UNION 必须由两条或两条以上的 SELELCT 语句组成，语句之间用关键字 UNION 分隔（如四条语句要使用三个 UNION 关键字）

49.UNION 中的每个查询必须包含相同个数的列、表达式或聚集函数，对应列的数据类型也必须相同。

50.列数据类型必须兼容：类型不必完全相同，但必须是 DBMS 可以转换的类型，如不同的数字类型或不同的日期类型。

51.用 UNION 组合查询时，只能使用一条 ORDER BY 子句，放在最后一行。

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

SQL 必知必会

52. 使用 UNION ALL，DBMS 不取消重复的行，而 WHERE 子句不能实现。

13.2 交操作 INTERSECT

/*买了 BNBG01 产品与买产品个数为5的顾客联系名的交集*/
SELECT cust_contact
FROM ProductCustomers          --视图，见 17 视图
WHERE prod_id = 'BNBG01'
INTERSECT
SELECT cust_contact
FROM ProductCustomers
WHERE cust_contact IN (SELECT cust_contact
                       FROM ProductCustomers
                       GROUP BY cust_contact
                       );

SELECT cust_contact
FROM ProductCustomers
WHERE prod_id = 'BNBG01' AND cust_contact IN (SELECT cust_contact
                                              FROM ProductCustomers
                                              GROUP BY cust_contact
                                              );

13.3 差操作 EXCEPT

/*买了 BNBG01 产品与买产品个数小于3的顾客联系名的差集*/
SELECT cust_contact
FROM ProductCustomers
WHERE prod_id = 'BNBG01'
EXCEPT
SELECT cust_contact
FROM ProductCustomers
WHERE cust_contact IN (SELECT cust_contact
                       FROM ProductCustomers
                       GROUP BY cust_contact
                       );

SELECT cust_contact
FROM ProductCustomers
WHERE prod_id = 'BNBG01' AND cust_contact IN (SELECT cust_contact
                                              FROM ProductCustomers
                                              GROUP BY cust_contact
                                              );

SELECT 语句是 SQL 最常用的语句，前面13节都在讲它，接下来介绍 SQL 另外3个常用的语句。

14. 插入数据

插入数据的几种情况：插入完整的行；插入行的一部分；插入某些查询的结果。

14.1 插入完整的行

INSERT INTO Customers
,,'USA',NULL,NULL);

53.表名后不跟列名，插入数据要的顺序要和表中列的定义次序完全相同。为了保证改变表结构后，INSERT 语句还能正常工作，表名后应加上列名。

INSERT INTO Customers(cust_id,cust_contact,cust_email,cust_name,cust_address,cust_city, cust_state,cust_zip,cust_country)
,,'USA');

14.2 插入部分行

INSERT INTO Customers(cust_id,cust_name,cust_address,cust_city, cust_state,cust_zip,cust_country)
,,'USA') 

54.省略列需要满足：该列定义允许 NULL 值或在表定义中给出默认值。

14.3 插入检索出的数据

假如想把另一个表中的顾客列合并到 Customers 表中。

INSERT INTO Customers(cust_id,
                      cust_address,
                      cust_city,
                      cust_contact,
                      cust_country,
                      cust_email,
                      cust_name,
                      cust_state,
                      cust_state,
                      cust_zip)
SELECT cust_id,
       cust_address,
       cust_city,
       cust_contact,
       cust_country,
       cust_email,
       cust_name,
       cust_state,
       cust_state,
       cust_zip
FROM CustNEW;

55.SELECT 列出的每一列对应于表名后的每一列，它使用的是列位置，与 SELECT 列出的列名无关。

56.INSERT SLECT 中 SELECT 语句可以包含 WHERE 子句以过滤插入的数据。

14.4 从一个表复制到另一个表

SELECT *
INTO CustCopy
FROM Customers; 

57.SELECT * 将复制每一列，复制部分列可以给出具体的列名。

在MySQL、Oracel中

CREATE TABLE CustCopy AS
SELECT *
FROM Customers;

58.任何 SELECT 选项和子句都可以用，包括 WHERE 和 GROUP BY 。

59.可以利用联结表吃从多个表中插入数据。

60.不管从多少个表中检索数据，数据都只能插入到一个表中。

15. 更新和删除数据

15.1 更新数据

给新表中特定的行；更新表中的所有行。

UPDATE Customers
SET cust_contact ='Sam Roberts',
    cust_email = 'sam@toyland.com'
;

61. 在 UPDATE 语句可以使用子查询，使能能用 SELECT 语句检索出的数据更新数据列。

62.部分 DBMS 支持在 UPDATE 语句中使用 FROM 子句，用一个表的数据更新另一个表的数据。

UPDATE Customers
SET cust_email = NULL
;

63.UPDATE 可以用来删除指定列的值。

15.2 删除数据

从表中删除特定的行；从表中删除所有的行。

DELETE FROM Customers
;

64. DELETE 后 FROM 关键字可选，但它保证可移植。

65. DELETE 不需要列名或通配符。DELET 删除整行而不是列，删除列用 UPDATE。

66. DELETE 删除行而不删除表本身，当要删除所有的行时，可以使用 TRUNCATE TABLE 语句，效率更高。

15.3 更新和删除的使用原则

67.除非打算删除或更新每一行，否则绝对不能使用不带 WHERE 子句的 UPDATE 或 DELETE 语句。

68. 在 UPDATE 或 DELETE 语句使用 WHERE 子句前，应该先用 SELECT 语句进行测试，保证 WHERE 子句正确。

69. 使用强制引用完整性的数据库，这样 DBMS 将不允许删除其数据与其他表相关联的行。

16 创建和操纵表

16.1 创建表

CREATE TABLE Products
(
  prod_id    )      NOT NULL ,
  vend_id    )      NOT NULL ,
  prod_name  )     NOT NULL ,
  prod_price ,)  NOT NULL ,
  prod_desc  ) NULL
);

70.不同的 DBMS 的 CREATE TABLE 的语法有所不同，例如 MySQL 中 varchar 必须替换为 text。

71.创建新表时，指定的表名必须不存在。防止意外覆盖已有的表，要求首先删除该表，然后再重建它，而不是简单地用创建表语句覆盖它。

CREATE TABLE Vendors
(
  vend_id      ) NOT NULL ,
  vend_name    ) NOT NULL ,
  vend_address )  ,
  vend_city    )  ,
  vend_state   )   ,
  vend_zip     )  ,
  vend_country )
);

72.在不指定 NOT NULL 时，多数 DBMS 认为指定的是 NULL。

CREATE TABLE OrderItems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    )     NOT NULL ,
  quantity   ,
  item_price ,) NOT NULL
); 
CREATE TABLE Orders
(
  order_num  int      NOT NULL ,
  order_date datetime NOT NULL      DEFAULT GETDATE(),  --将系统日期作为默认日期
  cust_id    ) NOT NULL
);

73.关键字 DEFAULT 在 CREATE TABLE 语句的列定义中指定关键字。

16.2 更新表

74.理想情况下，不要在表中包含数据时对其进行更新。应该在表的设计过程充分考虑未来可能的需求，避免今后对表结构做大改动。

75.所有的 DBMS 都允许给现有的表增加列，不过对所增加的列的数据类型（以及 NULL 和 DEFAULT 的使用）有所限制。

76.许多 DBMS 不允许删除或更改表中的列。

77.多数 DBMS 允许重新命名表中的列。

78.许多 DBMS 限制对已经填有数据的列进行更改，对为填充数据的列几乎没有限制。

ALTER TABLE Vendors
);   --添加列

ALTER TABLE Vendors
DROP COLUMN vend_phone;     --删除列

16.3 删除表

DROP TABLE CustCopy [RESTRICT|CASCADE];

RESTRICT：表示删除是有限制条件的。不能被其他表的约束所引用，不能有视图，不能有触发器，不能有储存过程或函数等。默认为 RESTIC。

CASCADE：在删除基本表的同时，相关依赖的对象，例如通过外码引用的表，视图等，都一起删除。

16.4 重命名表

在 SQL server 中

EXEC sp_rename 'OldTableName','NewTableName' --EXEC是执行存储过程的命令，可以省略，sp_rename是系统自带的存储过程可以用了来重命名表名、列名
EXEC sp_rename 'tablename.[OldFieldName ]', 'NewFieldName', 'COLUMN'  --重命名列

在 MySQL、Oracel 中

RENAME TABLE OldTbaleName TO NewTableName;     --重命名表
ALTER TABLE TableName
RENAME COLUMN OldFieldName TO NewFieldName;     --重命名列

17 使用视图

视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询。视图仅仅是用来查储存在别处数据的一种设施，视图本身不包含数据，因此返回的数据是从其它表中检索出来的。在添加或更改这些表中的数据时，视图将返回改变过的数据。

创建视图之后，可以用与表基本相同的方式使用它们。可以对视图执行 SELECT 操作，过滤和排序数据，将视图联结到其他视图或表，甚至添加和更新数据。

17.1 利用视图简化复杂的联结

CREATE VIEW ProductCustomers AS
SELECT cust_name,cust_contact,prod_id
FROM Customers,Orders,OrderItems
WHERE Customers.cust_id=Orders.cust_id AND Orders.order_num=OrderItems.order_num;   
--创建ProductCustmoers的视图，它联结三个表，返回已订购了任意产品的所有顾客的列表。
SELECT cust_name,cust_contact,prod_id
FROM ProductCustomers
ORDER BY cust_contact,prod_id;

SQL 必知必会

SELECT cust_name,cust_contact,prod_id
FROM (SELECT cust_name,cust_contact,prod_id,ROW_NUMBER()OVER(PARTITION BY cust_contact ORDER BY prod_id) AS A
      FROM ProductCustomers) T
;         --检索出每个联系名的第一条记录。

SQL 必知必会
SELECT cust_name,cust_contact
FROM ProductCustomers
WHERE prod_id= 'RGAN01';

17.2 使用视图与计算字段

CREATE VIEW VendorLocations AS
SELECT RTRIM(vend_name)+'('+RTRIM(vend_country)+')' AS vend_title
FROM Vendors;

SELECT *
FROM VendorLocations;

SQL 必知必会

CREATE VIEW OrderExpended AS
SELECT order_num,SUM(quantity*item_price) AS expanded_price
FROM OrderItems
GROUP BY order_num;

SELECT *
FROM OrderExpended;

SQL 必知必会

17.3 用视图过滤不想要的数据

CREATE VIEW CustomerEmailList AS
SELECT cust_id,cust_name,cust_email
FROM Customers
WHERE cust_email IS NOT NULL;  --过滤没有电子邮件的顾客

SELECT *
FROM CustomerEmailList;

SQL 必知必会

17.4 删除视图

DROP VIEW CustomerEmailList [CASCADE];

79.视图优点：简化复杂的 SQL 操作；使用表的一部分而不是整个表；保护数据；更改数据格式和表示，可返回和底层表的表示和格式不同的数据。

80.与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）。

81.对于可以创建的视图数目没有限制。

82.视图可以嵌套，即可以利用从其他视图中检索数据来构造视图。

83.许多 DBMS 禁止在视图中使用 ORDER BY 子句。

84.视图不能索引，也不能有关联的触发器或默认值。

85.有些 DBMS 把视图作为只读查询，这表示可以从视图检索数据，但不能将数据写回底层表。

86.有些 DBMS 允许创建这样的视图，它不能进行导致行不再属于视图的插入或更新。

数据库系统概论补充：

CREATE TABLE Student
(Sno        ) PRIMARY KEY,        --学号
 Sname      ) UNIQUE,            --姓名
 Ssex       ),                    --性别
 Sage       SMALLINT,                   --年龄
 Sdept      )                    --所在系
);
CREATE TABLE Course
(Cno        ) PRIMARY KEY,        --课程号
 Cnam       )NOT NULL,           --课程名
 Cpno       ),                    --先行课
 Ccredit    SMALLINT,                   --学分
 FOREIGN KEY (Cpno) REFERENCES Course(Cno)
);
create table SC
(sno        ),                    --学号
 cno        ),                    --课程号
 grade      smallint,                   --成绩
 primary key (sno,cno),
 foreign key (sno) references student(sno),
 foreign key (cno) references course (cno)
);
,'CS');
,'CS');
,'MA');
,'IS');  

');
');
');
');
');
');
'); 

');
');
');
');
');

10 使用子查询

10.1 利用子查询进行过滤

不相关子查询：子查询的条件不依赖予父查询的条件。

相关子查询：子查询的条件依赖父查询。

/*找出每一个学生超过他自己选修课平均成绩的课程号。*/
SELECT Sno,Cno
FROM SC x
WHERE grade >=(SELECT AVG(grade)
               FROM SC y
               WHERE y.sno=x.sno);
                              --先从外层查询中取出SC的一条记录，将sno传给内层查询，然后执行内层得出平均成绩，执行外层过滤出大于平均成绩的课程，再重复  
/*找出每个顾客买的大于他所买产品平均价格的产品编号*/
SELECT cust_id,prod_id
FROM CUST_PROD_PRICE X
WHERE prod_price > (SELECT AVG(prod_price)
                    FROM CUST_PROD_PRICE Y
                    WHERE X.cust_id=Y.cust_id);
/*视图CUST_PROD_PRICE*/
CREATE VIEW CUST_PROD_PRICE AS
SELECT cust_id,Products.prod_id,prod_price
FROM COPY,OrderItems,Products
WHERE COPY.order_num=OrderItems.order_num AND OrderItems.prod_id=Products.prod_id; 

a.带有 ANY 、ALL 操作符的子查询

/*查询非计算机系比计算机系任意一个学生年龄小的学生全部信息*/
SELECT *
FROM student
WHERE sage < ANY( SELECT sage
                  FROM student
                  WHERE sdept = 'cs')
      AND sdept <> 'cs';                  

SELECT *
FROM student
WHERE sage < ( SELECT MAX(sage)
               FROM student
               WHERE sdept = 'cs')
      AND sdept <> 'cs';                                        
/*查询非计算机系比计算机系所有学生年龄小的学生全部信息*/
SELECT *
FROM student
WHERE sage < ALL( SELECT sage
                  FROM student
                  WHERE sdept = 'cs')
      AND sdept <> 'cs';                  

SELECT *
FROM student
WHERE sage < (SELECT MIN(sage)
              FROM student
              WHERE sdept = 'cs')
      AND sdept <> 'cs';

b.带有 EXISTS 操作符的子查询

带有 EXISTS 操作符的子查询不返回任何数据，只产生逻辑真值 'true' 或逻辑假值 'false'。带有 EXISTS 操作符的子查询都是相关子查询。

EXISTS：如果内层查询结果非空，则外层 WHERE 子句返回真值，输出外层查询结果。

NOT EXISTS：如果内层查询结果为空，则外层 WHERE 子句返回真值，输出外层查询结果。

/*查询所有选修了 1 号课程的学生名字*/
SELECT sname
FROM student
WHERE EXISTS (SELECT *  --由EXISTS引出的子查询目标列都用*，因为带EXISTS的子查询只返回真值或假值
              FROM sc
              );  --如果某学生内存返回值非空，则 WHERE 子句为真，外层查询返回结果   

/*查询没有选修 1 号课程的学生名字*/
SELECT sname
FROM student
WHERE NOT EXISTS (SELECT *
                  FROM sc
                  );  --如果某学生内存返回值空，则 WHERE 子句为真，外层查询返回结果

/*查询与刘晨同系学生的所有信息*/
SELECT *
FROM student x
WHERE EXISTS (SELECT *
              FROM student y
              WHERE y.sdept=x.sdept AND y.sname = '刘晨');  --EXCISTS 能替换 IN、< 等

10.3 FROM 子句中的子查询（派生表）

/*找出每个学生超过他选修课程平均成绩的课程号*/
SELECT sno,cno
FROM SC,(SELECT sno,AVG(grade)
         FROM SC
         GROUP BY sno) AS avg_sc(avg_sno,avg_grade)
WHERE SC.sno=avg_sc.avg_sno  AND  SC.grade>=avg_sc.avg_grade;

/*找出选修了1号课程的学生名单*/
SELECT sname
FROM Student,(SELECT Sno
              FROM SC
              ) AS SC1
WHERE Student.Sno=SC1.sno;

87.在使用聚集函数时，表名后要注明列名。没有聚集函数时，SELECT 子句后面的列名为其默认列名。

17 使用视图

17.1 创建视图

/*只有 IS 系的学生*/
CREATE VIEW IS_student AS
SELECT Sno,sname,sage
FROM Student
WHERE Sdept='IS'
WITH CHECK OPTION;   --对该视图进行插入、修改和删除操作时，DBMS 会自动加上 Sdept='IS'的条件

88.视图不保存数据，保存视图的定义，在对视图查询时，才按视图的定义从基本表中将数据查出（执行 SELECT 语句）

89.视图的列名或者全部指定或者全部省略。全部省略时，对聚集函数和需要更新名字的列使用别名。

行列子集视图：从单个基本表中导出的，并且只是去掉了基本表的某些行和某些列，但保留了主码的视图。如上。

/*建立在 IS_student 视图上的视图*/
CREATE VIEW IS_student_1 AS
SELECT IS_student.sno,sname,grade
FROM IS_student,SC
;

17.2 查询视图

视图消解：把视图定义中的 SELECT 语句和用户查询的 SELECT 语句结合起来，转换成等价的对基本表的查询，再执行修正了的查询。

SELECT Sno,sage
FROM IS_student
;

/*转换后的查询语句*/
SELECT Sno,sage
FROM Student
;
/*不能消解的例子*/
CREATE VIEW S_G AS
SELECT sno,AVG(grade) AS avg_grade
FROM SC
GROUP BY sno;

SELECT *
FROM S_G
;

/*原因:组合后的语句如下*/
SELECT sno,AVG(grade)
FROM SC

GROUP BY sno;

/*应直接对基本表查询*/
SELECT sno,AVG(grade) AS avg_grade
FROM SC
GROUP BY sno
;

SELECT *
FROM (SELECT sno,AVG(grade) AS avg_grade
      FROM SC
      GROUP BY sno) S_G
;

90.多数 DBMS 能对行列子集视图的查询均能进行正确转换。但对非行列子集视图的查询不一定能转换。

17.3 更新视图

/*更新数据*/
UPDATE IS_student
SET Sname='刘晨'
;
/*转换的语句*/
UPDATE Student
SET Sname='刘晨'
 AND Sdept='IS';

/*插入的语句*/
INSERT INTO IS_student
,);
/*转换的语句*/
INSERT INTO Student(Sno,Sname,Sage,Sdept)
,,'IS');

/*删除数据*/
DELETE FROM IS_student
;
/*转换的语句*/
DELETE FROM Student
 AND Sdept='IS';

91.并不是所有的视图都是可以更新的，有些视图的更新不能唯一的有意义的转换成相应基本表的更新，如聚集函数列。一般行列子集视图是可以更新的。

92.不是所有的视图都是允许更新的，具体请查阅 DBMS 文档。

终于写完了，阿西吧。总结一下：

SQL 必知必会

相关文章