读《SQL必知必会》笔记

SQL是一门语言，该语言是用于与数据库沟通的语言。与其他语言不通（Java、PHP、Python）不同，该语言只有极少的关键字组成。提供了一种与从数据库中读写数据的有效方法。数据库就是一个文件夹，而里面的表就是文件夹里面的文件。

多动手练习、多动手练习、多动手练习

书中给了样例数据和表结构,样例

样例表结构

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 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 TABLE Orders
(
  order_num  int      NOT NULL ,
  order_date datetime NOT NULL ,
  cust_id    char(10) NOT NULL 
);
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 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 
);
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);
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);

样例数据

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

主键：主键用于标识表中的每一行的列。应该总是定义主键，即使你并不总是需要。
任何列都可以作为主键，只需要满足如下条件：

 1.主键必须唯一，且不能为null
 2.主键列中的值不允许修改或者更新
 3.即使某行删除。该行的主键也不能用于以后的新行。即主键不能重新使用。

当然一列可以定义为主键。多列也可以同时使用定义为主键。SQL不是某个特定数据库供应商专有的语言，几乎所有的数据库管理系统都支持SQL,所以学习SQL几乎能与所有的数据库打交道。

查询

SELECT 语句用于检索信息。必须给该语句两条信息:

1.想查询什么
2.从哪里查询

检索单个列

SELECT [column] FROM [tablename];//column 要检索的列，tablename要检索的名字

该语句会返回表中所有的行，没有过滤或者排序。多条SQL语句用分号分割。SQL语句是不区分大小写的。很多开发人员喜欢把SQL语句的关键字大写，列明和表名小写这样更易于阅读和调试。在处理SQL语句时空格将被忽略。在SQL里面一个空格两个空格都是等效的，都被忽略。

检索所有的列

使用通配符*可以检索表中所有的列，而不用一一列举各个列的字段。

SELECT * FROM [tablename];

除非需要表中的所有字段，否则请不要使用* 因为检索不需要的列通常会降低检索和应用程序的性能。

DISTINCT 命令数据库只返回不同的值

SELECT DISTINCT [colunm] ,[column1] FROM tablename;//DISTINCT关键字必须放在列名的前面，作用于所有的列，并不仅仅跟在后面的列。

限制结果

如果想对返回的结果进行限制，比如返回固定的几条或者返回第几条等。该怎么办呢？不同的数据库对该SQL的实现是不相同的。因此使用不同的数据库应该使用相应的SQL语句。
以MySQL为例

SELECT [column] FROM [tablename] LIMIT [num1] OFFSET [num2];//使用limit关键字来实现这一需求，来指定返回的行数，num1表示返回的条数，num2表示从哪里开始。

第一个检索的行是第0行，而不是第1行。MySQL或者sqlite支持简化版的LIMIT 5 OFFSET 5;即LIMIT 3,4; 逗号之前的表示OFFSET的值，逗号之后表示LIMIT限制返回的行数。

注释

# 这是一条注释
-- 这是一条注释
/*
这是一条注释
*/

子句的定义: 一个子句通常有关键字加上提供的数据组成；

排序

ORDER BY子句可以对检索出来的数据进行排序。

注意

一条SQL语句如果含义ORDER BY 的子句时，应该保证它是SELECT语句的最后一条子句，否则会报错。

SELECT [column1],[column2] ... FROM [tablename] ORDER BY [colunmx],[colunmy] ... 
//首先会按照ORDER BY 的第一列即columnx 排序，如果第一列有相同的值，再按照第二列排序，如果第一列各不相同则不会按照第二列排序，以此类推。

ORDER BY 后面也可以直接跟数字，表示按照第几个检索字段排序

SELECT [column1], [column2],[column3] FORM [tablename] ORDER BY 2,3; //表示先按[column2]排序，当有相同的[column2]时，再按[colunm3]排序。不推荐。

指定排序方向

ORDER BY 默认按照升序排序的，如果想按照降序排序必须指定DESC关键字；

SELECT [column1],[column2] ... FROM [tablename] ORDER BY [colunmx] DESC,[colunmy] DESC ,...

注意

关键字DESC只作用于前面的列，如果没有DESC 则该字段依然按默认的升序排序。所以如果想对多个字段按照降序排序必须对每个字段后面加上DESC。

过滤数据

在SELECT 语句中，数据根据WHERE子句的条件进行过滤。WHERE子句的位置一般在表名之后（FROM子句之后）。

条件操作符：
>
<
=
<> 不等于
!= 不等于
BETWEN  在指定的两个值之间
IS NULL  为NULL值
....

example:

SELECT column_name FROM Products WHERE column_name = ‘iphone7’;

查询表Products里面的字段column_name 为iphone7的所有数据。
WHERE子句中的条件’iphon7’加了引号。单引号用于限定字符串。如果将值与字符串类型的列进行比较就需要有单引号。如果值与数值类型的列进行比较就不需要限定引号。

范围值检索

BETWEEN  num1 AND num2; 匹配范围中的所有值，包括开始值（num1）和结束值（num2）;

eg:

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

空值检查

确定某一个字段是否为空，不能简单的判断字段是否=NULL。

SELECT 语句提供了一个特殊的WHERE子句来检查NULL值的列。这个WHERE子句就是 IS NULL。

eg:

SELECT prod_name FROM Products WHERE prod_price IS NULL;

AND或OR

为了更强的过滤控制，SQL提供了对WHERE子句的逻辑操作符AND和OR。
eg:

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

SELECT vend_id, prod_name,prod_price FROM Products WHERE vend_id='DLL01' OR vend_id='BRS01';
结果：
+---------+---------------------+------------+
| vend_id | prod_name           | prod_price |
+---------+---------------------+------------+
| BRS01   | 8 inch teddy bear   |       5.99 |
| BRS01   | 12 inch teddy bear  |       8.99 |
| BRS01   | 18 inch teddy bear  |      11.99 |
| DLL01   | Fish bean bag toy   |       3.49 |
| DLL01   | Bird bean bag toy   |       3.49 |
| DLL01   | Rabbit bean bag toy |       3.49 |
| DLL01   | Raggedy Ann         |       4.99 |
+---------+---------------------+------------+

AND和OR的优先级

AND和OR一般用于WHERE子句中，并且WHERE子句可以包含任意多个AND或OR操作符。
在组合使用AND和OR时，有时会带来意想不到的问题。
eg:
比如查找价格在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 |
+---------------------+------------+
| 18 inch teddy bear  |      11.99 |
| Fish bean bag toy   |       3.49 |
| Bird bean bag toy   |       3.49 |
| Rabbit bean bag toy |       3.49 |
| Raggedy Ann         |       4.99 |
+---------------------+------------+

可以看到结果并不是我们想要的，因为有的价格小于10美元。
造成这个原因是因为操作符AND 的优先级高于OR的。所以该SQL语句优先结合 vend_id = ‘BRS01’ AND prod_price >= 10，然后再查出供应商为vend_id = ‘DLL01’的所有产品。
解决方案：使用圆括号对操作明确分组，提高优先级；

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.99 |
+--------------------+------------+

注意

在WHERE子句中如果含有AND和OR的组合时，应尽量使用圆括号进行明确的分组操作，这样能消除歧义

IN 操作符

IN操作符用于查找条件范围，相当于OR。
格式：

IN (条件1，条件2，条件3，...)  //范围中的每一个条件都会匹配。

eg:

SELECT prod_name,prod_price FROM Products WHERE vend_id IN ('DLL01','BRS01') AND prod_price >= 10;
+--------------------+------------+
| prod_name          | prod_price |
+--------------------+------------+
| 18 inch teddy bear |      11.99 |
+--------------------+------------+

IN 操作符后跟有逗号分隔的合法值，这些值用圆括号括起来。它能完成OR相同的功能。

为什么要使用IN操作符

1. IN 操作符比 OR操作符更清晰、直观、对求值顺序更容易管理。
2. IN操作符比OR操作符执行的更快，效率更高。
3. IN操作符最大的优点是可以包含其他SELECT子句，这样可以动态的创建WHERE子句。

NOT 操作符

NOT操作符只有一个功能，就是否定后面跟的任何条件。

SELECT prod_name,prod_price FROM Products WHERE  NOT vend_id = 'DLL01'; 
+--------------------+------------+
| prod_name          | prod_price |
+--------------------+------------+
| 8 inch teddy bear  |       5.99 |
| 12 inch teddy bear |       8.99 |
| 18 inch teddy bear |      11.99 |
| King doll          |       9.49 |
| Queen doll         |       9.49 |
+--------------------+------------+

通配符

怎么搜索产品名称中包含bang的所有产品？

用简单的比较操作符是不行的，必须使用通配符。

关键概念

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

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

为了在搜索子句中使用通配符，必须使用LIKE操作符。LIKE操作符命令DMMS 后面的搜索模式使用通配符匹配而不是简单的相等匹配等。

LIKE属于谓词而不是操作符。以后在其他书籍遇到谓词时能够想到像LIKE这样的词就行。

通配符搜索只能用于文本（字符串）。其他类型的字段不能用于通配符搜索。

• % 通配符：表示任意字符出现任意次数，包括0个字符。在其他数据库系统中有可能使用*号。
  eg:查找以Fish开头的产品

SELECT prod_name,prod_price FROM Products WHERE prod_name LIKE 'Fish%';
+-------------------+------------+
| prod_name         | prod_price |
+-------------------+------------+
| Fish bean bag toy |       3.49 |
+-------------------+------------+

有很多数据库系统都用空格来填补字段。比如某列有30个字符，而存储的文本只占用了10个字符，那么后面会填充20个空格。在进行通配符匹配的时候注意这种情况。解决方案用函数去掉空格。后面会解决。

% 并不能匹配NULL值

WHERE prod_name LIKE ‘%’; 并不能匹配产品名称为NULL的行。

• _ 下划线总是匹配一个字符，不能多也不能少。DB2不支持下划线，如果使用Access需要使用？而不是下划线。

[]字符集通配符

能够匹配该集合里面的任意一个字符。也只能匹配一个。

并不是所有的数据库管理系统支持该集合操作。只有微软的Access和SQL Server支持集合。为确保您的数据库是否支持集合，请查阅相应的文档。

查找名字以J或M开始的联系人

SELECT cust_contact FROM Customers WHERE cust_contact LIKE '[MJ]%';

在MySQL下查询为空，说明不支持此方式。
^用于否定集合里面的字符.

eg:查找以名字J或M之外的联系人

SELECT cust_contact FROM Customers WHERE cust_contact LIKE '[^MJ]%';

总结

通配符搜索一般比其他搜索会消耗更长的时间。
因此应遵守以下原则：

1. 如果其他操作符能够达到目的，应尽量使用其他操作符。
2. 如果非要使用通配符，应尽量放在搜索模式的最后面，放在最前面搜索起来最慢。
3. 仔细查看通配符的位置，如果位置放错，可能得不到想要的数据。

计算字段

如果要求某列的平均值，或查询出的字段需要进行拼接？

在SQL语句内完成的转换工作和格式化工作，完全也能在客户端应用程序内完成，但一般来说在数据库服务器上完成要比在客户端应用程序内完成要快的多。

这个时候就需要计算字段了。
计算字段并不直接存在数据库表中，计算字段是运行时在SQL语句中创建的。只有数据库知道SQL中哪些列是实际的表列，哪些是计算字段。从客户端的角度来看，计算字段的数据与其他数据库中表的列数据返回方式并没有什么不同。

eg:把两个列拼接起来。
不同的数据库使用的方式不同，有的使用+ 有的使用|| 而MySQL必须使用函数Concat()来完成。

 SELECT Concat(vend_name,' (',vend_country,' )') FROM Vendors;
+------------------------------------------+
| Concat(vend_name,' (',vend_country,' )') |
+------------------------------------------+
| Bear Emporium (USA )                     |
| Bears R Us (USA )                        |
| Doll House Inc. (USA )                   |
| Fun and Games (England )                 |
| Furball Inc. (USA )                      |
| Jouets et ours (France )                 |
+------------------------------------------+

可以看出SELECT语句把vend_name、（、vend_country、)四个元素作为一列来返回。

使用别名

上面的例子结果计算字段并没有名字。这样直接给客户端程序使用，客户端是没有办法引用的。这时可以给新的计算字段取一个别名(alias)。别名使用AS关键字赋予。

eg:

 SELECT Concat(vend_name,' (',vend_country,' )') AS vend_title  FROM Vendors;
+--------------------------+
| vend_title               |
+--------------------------+
| Bear Emporium (USA )     |
| Bears R Us (USA )        |
| Doll House Inc. (USA )   |
| Fun and Games (England ) |
| Furball Inc. (USA )      |
| Jouets et ours (France ) |
+--------------------------+

在很多DMBS中AS是可选的，不过最好使用的，这是最佳实践。别名还有其他用途，比如数据库表列有歧义、容易混淆或者列名不合法比如有空格等。别名有时也称为导出列。

计算字段的另一种用途是数值计算

eg:

SELECT prod_id,quantity,item_price,quantity*item_price AS expand_price FROM OrderItems WHERE order_num=20008;
+---------+----------+------------+--------------+
| prod_id | quantity | item_price | expand_price |
+---------+----------+------------+--------------+
| RGAN01  |        5 |       4.99 |        24.95 |
| BR03    |        5 |      11.99 |        59.95 |
| BNBG01  |       10 |       3.49 |        34.90 |
| BNBG02  |       10 |       3.49 |        34.90 |
| BNBG03  |       10 |       3.49 |        34.90 |
+---------+----------+------------+--------------+

SQL支持+、-、*、/等基本操作。
当SELECT子句省略FROM子句时，就是处理简单的表达式或数值计算
比如

 SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2019-03-14 14:44:51 |
+---------------------+

与其他大多数计算机语言一样SQL也支持函数来处理数据。
与支持SQL语句不同，几乎不同的数据库都提供特定的函数。即函数不具有通用性和可移植性。
使用或不使用函数也没有对错之分，决定权在与你，但应该保证好代码的注释。

聚集函数

聚集函数用于汇总数据，比如对表中的某一列求和、求平均等。
与上面介绍的数据处理函数不同，聚集函数在主要的数据库中都有相当一致的实现。

• AVG() 用于求某列的平均值，只能求某一特定列的平均值，而且列名必须做为参数传递，如果要求多个列的平均值，必须使用多个AVG()函数。而且会忽略列值为NULL的行。
  eg:求DDL01供应商产品的平均价格。

SELECT AVG(prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01';
+-----------+
| avg_price |
+-----------+
|  3.865000 |
+-----------+

• COUNT() 用于求表中行的数目或符合特定条件的数目。

使用方法有两种：

1. COUNT(*) :对表中行的数目进行计算，不管表中的列是否为NULL。
2. COUNT(column): 对特定的列中的数据进行计数，忽略NULL。有NULL值不会计数。

SELECT COUNT(*) AS totolRow FROM Customers;
+----------+
| totolRow |
+----------+
|        5 |
+----------+

SELECT COUNT(cust_email) AS num_email FROM Customers;
+-----------+
| num_email |
+-----------+
|         3 |
+-----------+

• MAX() 返回列的最大值，会主动忽略列值为NULL的行。用于找出最大数值或日期。参数为列名，必须给出。

SELECT MAX(prod_price) AS maxPrice FROM Products;
+----------+
| maxPrice |
+----------+
|    11.99 |
+----------+

• MIN() 与MAX()功能正好相反，求列的最小值。必须给出列名作为参数，主动忽略NULL。求数值的最小值或日期的最小值。
• SUM() 用于返回指定列值的和也可以用来合计计算值。忽略列值为NULL的行。
  eg:计算订单号为20005的所有物品价钱之和。

 SELECT SUM(quantity*item_price) AS totolPrice FROM OrderItems WHERE order_num = 20005;
+------------+
| totolPrice |
+------------+
|    1648.00 |
+------------+

组合聚合函数使用

SELECT COUNT(*) AS num_items,MAX(prod_price) AS price_max,MIN(prod_price) AS price_min,AVG(prod_price) AS price_avg FROM Products;
+-----------+-----------+-----------+-----------+
| num_items | price_max | price_min | price_avg |
+-----------+-----------+-----------+-----------+
|         9 |     11.99 |      3.49 |  6.823333 |
+-----------+-----------+-----------+-----------+

在取别名时最好不要与表的实际列名相同，虽然合法。但有些SQL不支持。

这些聚合函数很高效，一般来说比把数据返回给客户端，客户端再计算结果要快的多。

使用分组

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

建立分组：使用GROUP BY 子句建立。

eg:

SELECT vend_id,COUNT(*) AS num_products FROM Products GROUP BY vend_id;
+---------+--------------+
| vend_id | num_products |
+---------+--------------+
| BRS01   |            3 |
| DLL01   |            4 |
| FNG01   |            2 |
+---------+--------------+

GROUP BY 子句命令DBMS按vend_id分组数据，这就会对每个分组进行num_products计算。

1. 如果分组列中包含NULL值，则NULL将作为一个分组返回。如果包含多个NULL值则这些NULL值将分为一组。
2. GROUP BY 子句必须在WHERE子句之后，ORDER BY子句之前。
3. GROUP BY 子句可以包含任意多的列，进而对分组进行嵌套，更细致的进行分组。

对分组的数据进行过滤，我们通常使用HAVING 。比如：查询至少含有两个订单的的所有顾客

WHERE是用来过滤指定的行的而不是分组数据。HAVING专门用于过滤分组的。WHERE 在分组前进行过滤，HAVING在分组之后进行过滤。使用HAVING时应该结合GROUP BY 子句。HAVING也可以用于连接查询(内连接、左连接、右连接等)之后进行过滤。

eg:

SELECT cust_id,COUNT(*) FROM Orders GROUP BY cust_id HAVING COUNT(*) >= 2;
+------------+----------+
| cust_id    | COUNT(*) |
+------------+----------+
| 1000000001 |        2 |
+------------+----------+

eg:

SELECT vend_id,COUNT(*) AS num_produs FROM Products WHERE prod_price >= 4 GROUP BY vend_id HAVING COUNT(*) >= 2;
+---------+------------+
| vend_id | num_produs |
+---------+------------+
| BRS01   |          3 |
| FNG01   |          2 |
+---------+------------+

SELECT 子句的顺序—-到目前所学过的子句

SELECT 要返回的列或表达式 FROM 表名  WHERE(行级过滤) GROUP BY(分组说明) HAVING(组级过滤) ORDER BY (排序)

子查询

SQL允许创建子查询，即嵌套在其他查询中的查询。

作为子查询的SELECT只能查询单个列。

DBMS系统先查询子语句，在查询外层语句，以此类推。

当使用多个表查询时，使用完全限定列名来避免歧义。格式：[tablename.column]

关系表

理解关系表，最好看例子，这样会有更加直观的感受。

如果有个产品表，用来存储每个物品。每个物品独占一行。包括物品的描述、物品的价格、生产该物品的供应商。

现在有同一个供应商生产多个物品。该供应商的的信息如果保存在产品表里面是否合理。供应商的信息包括：供应商名、地址、联系电话、所在的城市等。

如果直接把供应商的信息也直接放在产品表中，就会带来以下问题：

1. 由于一个供应商可以生产多个不同种类的物品，如果把供应商的信息也放在产品表中就会产生很多重复相同的数据。每多一种该供应商的物品就会多一条该供应商重复的信息数据。这样就会浪费很多的时间和存储空间。
2. 如果供应商的信息发生变化，比如地址或联系电话。那么产品表的很多地方都要修改。
3. 每次向产品表中插入数据就会把供应商的所有信息也要插入，这样很难保证每次插入供应商的信息都正确。还浪费很多时间

相同的数据出现多次决不是一件好的事情，这就是关系型数据库设计的理论基础。关系型数据库就是把信息分解成多个表。一类数据一个表。各表通过某些共同的值相互关联，所以叫关系型数据库。

这个例子中我们可以建立两张表，一张存储供应商信息、一张存储产品信息。Vendors只存储供应商信息，每个供应商独占一行。并具有唯一的标识通常称为主键来标识该行。可以是供应商ID或其他唯一值就行。

Products只存储产品有关的信息，除了存储供应商ID外，不存储供应商的其他信息。Vendors表的主键将Vendors表和Products关联起来。利用供应商ID可以查询出有关供应商的所有信息。

关系型数据库能够更加方便存储和处理数据因此可伸缩性比非关系型数据库要好。

任何事情都是有代价的，关系型数据库方便数据的存储的同事，也带来了一些问题。比如如何使用一条SELECT语句就能在多张表中查询出数据呢。答案是使用联结。联结是一种机制，用来在一条SELECT语句中关联表。

创建联结

指定联结的表名和连接的方式即可

eg:

SELECT prod_name,prod_price,vend_name FROM Products,Vendors WHERE Products.vend_id = Vendors.vend_id; 
+---------------------+------------+-----------------+
| prod_name           | prod_price | vend_name       |
+---------------------+------------+-----------------+
| Fish bean bag toy   |       3.49 | Doll House Inc. |
| Bird bean bag toy   |       3.49 | Doll House Inc. |
| Rabbit bean bag toy |       3.49 | Doll House Inc. |
| 8 inch teddy bear   |       5.99 | Bears R Us      |
| 12 inch teddy bear  |       8.99 | Bears R Us      |
| 18 inch teddy bear  |      11.99 | Bears R Us      |
| Raggedy Ann         |       4.99 | Doll House Inc. |
| King doll           |       9.49 | Fun and Games   |
| Queen doll          |       9.49 | Fun and Games   |
+---------------------+------------+-----------------+

分析：SELECT语句后面仍然跟的是列名，只是列来源不同的表中，FROM后面跟的仍然是表名只是跟了多张表以逗号分隔。WHERE后面跟的是完全限定列名，在引用的列可能有歧义时必须使用完全限定列名。完全限定列名，使用句点把表名与列名进行分隔。
在联结两个表时，实现上第一个表的每一行与第二个表的每一行进行配对。WHERE 子句作为过滤条件，只匹配给定条件的记录。如果没有WHERE条件，则返回第一表的每一行与第二个表的每一行进行匹配。结果的行数将是第一个表的行数乘以第二个表的行数。这种没有联结条件的联结结果为笛卡尔积。

上面的联结我们称为等值连接，也叫内联结。也可以对这种连接使用稍微不同的语法。内连接相当于取表的交集，拿两个表举例，会先从一张表里面取一行记录然后根据条件向另一张表里面一行一行匹配如果匹配成功则返回，如果不成功则忽略。

SELECT Prod_name,prod_price,vend_name FROM Products INNER JOIN Vendors ON Products.vend_id = Vendors.vend_id;
+---------------------+------------+-----------------+
| Prod_name           | prod_price | vend_name       |
+---------------------+------------+-----------------+
| Fish bean bag toy   |       3.49 | Doll House Inc. |
| Bird bean bag toy   |       3.49 | Doll House Inc. |
| Rabbit bean bag toy |       3.49 | Doll House Inc. |
| 8 inch teddy bear   |       5.99 | Bears R Us      |
| 12 inch teddy bear  |       8.99 | Bears R Us      |
| 18 inch teddy bear  |      11.99 | Bears R Us      |
| Raggedy Ann         |       4.99 | Doll House Inc. |
| King doll           |       9.49 | Fun and Games   |
| Queen doll          |       9.49 | Fun and Games   |
+---------------------+------------+-----------------+

使用INNER JOIN联结两张表，ON做为条件与WHERE等同。这也是内联结。具体使用哪一种格式都行。

联结多个表
SQL不限制联结表的数目，联结规则也相同。首先列出所有的表，然后在定义联结条件.

 SELECT prod_name,prod_price,vend_name,quantity FROM OrderItems,Products,Vendors WHERE Products.vend_id = Vendors.vend_id AND OrderItems.prod_id = Products.prod_id AND order_num = 20007;
+---------------------+------------+-----------------+----------+
| prod_name           | prod_price | vend_name       | quantity |
+---------------------+------------+-----------------+----------+
| 18 inch teddy bear  |      11.99 | Bears R Us      |       50 |
| Fish bean bag toy   |       3.49 | Doll House Inc. |      100 |
| Bird bean bag toy   |       3.49 | Doll House Inc. |      100 |
| Rabbit bean bag toy |       3.49 | Doll House Inc. |      100 |
| Raggedy Ann         |       4.99 | Doll House Inc. |       50 |
+---------------------+------------+-----------------+----------+

联结的表越多性能下降的越厉害，因此不要关联不必要的表。

使用表别名

使用表别名能够缩短SQL语句

eg:

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 prod_id = 'RGAN01';
+---------------+--------------------+
| cust_name     | cust_contact       |
+---------------+--------------------+
| Fun4All       | Denise L. Stephens |
| The Toy Store | Kim Howard         |
+---------------+--------------------+

表别名只在查询执行中使用，与列别名不同的是表别名不返回给客户端。