SQL - 思维导图

1、了解SQL

数据库基础

数据库

保存有组织的数据的容器

表

某种特定类型数据的结构化清单

数据库中表名是唯一的，在同一个数据库中不能存在相同的表名，不同数据库之间可以实现

列和数据类型

列为表中的一个字段，数据类型为该列可以存储的数据种类，例如：日期

行

数据按行存储；例如：顾客表每行存储一个客户信息

主键

一列（或一组列），其值能够唯一标识表中每一行。

表中的任何列都可以作为主键，只要它满足以下条件： 任意两行都不具有相同的主键值； 每一行都必须具有一个主键值（主键列不允许NULL 值）； 主键列中的值不允许修改或更新； 主键值不能重用（如果某行从表中删除，它的主键不能赋给以后的新行）

SQL的定义

是一种专门用来与数据库沟通的语言

SQL 有如下的优点。 SQL 不是某个特定数据库供应商专有的语言。几乎所有重要的DBMS都支持SQL，所以学习此语言使你几乎能与所有数据库打交道。 SQL 简单易学。它的语句全都是由有很强描述性的英语单词组成，而且这些单词的数目不多。 SQL 虽然看上去很简单，但实际上是一种强有力的语言，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作。

2、检索数据

SELECT语句

select sth From 表

1、多条SELECT语句必须以；间隔，不区分大小写2、在选择多个列时，一定要在列名之间加上逗号，但最后一个列名后不加。如果在最后一个列名后加了逗号，将出现错误。例如：SELECT prod_id, prod_name, prod_priceFROM Products;3、如需要检索不同的值,DISTINCT表示只返回数据库不同的值SELECT DISTINCT prod_idFROM Products; 4、SELECT TOP 5 prod_nameFROM Products;表示只检索前面5行的数据（不同数据库有不同的方法检索，不一致）5、在一行的开始处使用#，这一整行都将作为注释。 /* SELECT prod_name, vend_idFROM Products; */SELECT prod_nameFROM Products;注释从/*开始，到*/结束，/*和*/之间的任何内容都是注释。

3、排序检索数据

ORDER BY检索时以字母顺序排序，使用时，必须保证其为最后一条子句

要按多个列排序，简单指定列名，列名之间用逗号分开即可

SELECT prod_id, prod_price, prod_nameFROM ProductsORDER BY prod_price, prod_name;

按列位置排序

SELECT prod_id, prod_price, prod_nameFROM ProductsORDER BY 2, 3; SELECT 清单中指定的是选择列的相对位置而不是列名。ORDER BY 2表示按SELECT 清单中的第二个列prod_name 进行排序。ORDER BY 2，3 表示先按prod_price，再按prod_name 进行排序。

制定方向排序，默认升序，如需使用降序排列，可是使用制定关键字DESC

1、SELECT prod_id, prod_price, prod_nameFROM ProductsORDER BY prod_price DESC; 2、SELECT prod_id, prod_price, prod_nameFROM ProductsORDER BY prod_price DESC, prod_name;，只对prod_price列指定DESC，对prod_name 列不指定。因此，prod_price 列以降序排序，而prod_name 列（在每个价格内）仍然按标准的升序排序。

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

4、使用WHERE子句

只检索自己在数据库中需要的指定数据

1、检索单个值SELECT prod_name, prod_priceFROM ProductsWHERE prod_price = 3.49;这条语句从products 表中检索两个列，但不返回所有行，只返回prod_price 值为3.49 的行操作符说明操作符说明= 等于 > 大于< > 不等于 >= 大于等于!= 不等于 !> 不大于< 小于 BETWEEN 在指定的两个值之间<= 小于等于 IS NULL 为NULL值!< 不小于2、不匹配检索 SELECT vend_id, prod_nameFROM ProductsWHERE vend_id <> 'DLL01';列出所有不是供应商DLL01 制造的产品3、范围值检索 SELECT prod_name, prod_priceFROM ProductsWHERE prod_price BETWEEN 5 AND 10;检索价格在5 美元和10美元之间的所有产品。4、空值检索SELECT prod_name FROM ProductsWHERE prod_price IS NULL;返回所有没有价格（空prod_price 字段，不是价格为0）的产品

5、高级数据检索

AND/OR

关联多个条件（用来检索满足所有指定条件的行）

1、and操作符SELECT prod_id, prod_price, prod_name FROM ProductsWHERE vend_id = 'DLL01' AND prod_price <= 4;检索由供应商DLL01 制造且价格小于等于4 美元的所有产品的名称和价格2、or操作符用来表示检索匹配任一给定条件的行。 SELECT prod_name, prod_priceFROM ProductsWHERE vend_id = 'DLL01' OR vend_id = ‘BRS01’;检索由任一个指定供应商制造的所有产品的产品名和价格。注：事实上，许多DBMS 在OR WHERE 子句的第一个条件得到满足的情况下，就不再计算第二个条件了（在第一个条件满足时，不管第二个条件是否满足，相应的行都将被检索出来）。3、求值顺序AND在求值过程中优先级高于OR，（）优先级高于AND和OR。要想实现选择由供应商DLL01或BRS01 制造的且价格在10 美元及以上的所有产品 SELECT prod_name, prod_priceFROM ProductsWHERE (vend_id = 'DLL01' OR vend_id = 'BRS01') AND prod_price >= 10;

IN

指定条件范围，范围中的每个条件都可以进行匹配。

1、SELECT prod_name, prod_priceFROM ProductsWHERE vend_id IN ( 'DLL01', 'BRS01' )ORDER BY prod_name; 2、SELECT prod_name, prod_priceFROM ProductsWHERE vend_id = 'DLL01' OR vend_id = 'BRS01'ORDER BY prod_name;1和2表达的逻辑一样，但是相对于而言，IN语法更清楚，更直观，求值顺序更容易管理，且执行更快

NOT

用来否定其后条件的关键字。

1、SELECT prod_nameFROM ProductsWHERE NOT vend_id = 'DLL01'ORDER BY prod_name;出除DLL01 之外的所有供应商制造的产品 2、另一种表达方式SELECT prod_nameFROM ProductsWHERE vend_id <> 'DLL01'ORDER BY prod_name;

6、使用通配符过滤

通配符：用来匹配值得一部分的特殊字符搜索模式（search pattern）由字面值、通配符或两者组合构成的搜索条件。通配符搜索只能用于文本字段（字符串），非文本数据类型字段不能使用通配符搜索。

like

%通配符

任何字符出现任意次数

SELECT prod_id, prod_nameFROM ProductsWHERE prod_name LIKE 'Fish%';检索任意以Fish 起头的词。%告诉DBMS 接受Fish 之后的任意字符，不管它有多少字符。注：如果使用系统为Access，通配符号为*而不是%，检索区分大小写 SELECT prod_id, prod_nameFROM ProductsWHERE prod_name LIKE '%bean bag%';搜索模式'%bean bag%'表示匹配任何位置上包含文本bean bag 的值，不论它之前或之后出现什么字符。通配符%看起来像是可以匹配任何东西，但有个例外，这就是NULL。子句WHERE prod_name LIKE '%'不会匹配产品名称为NULL 的行。

_通配符

只匹配单个字符

只匹配单个字符，而不是多个字符。DB2不适用_Access使用？SELECT prod_id, prod_nameFROM ProductsWHERE prod_name LIKE '__ inch teddy bear';搜索模式要求匹配两个通配符而不是一个，_总是刚好匹配一个字符，不能多也不能少。

[]通配符

在指定的字符集中匹配指定位置的一个字符

1、SELECT cust_contact FROM CustomersWHERE cust_contact LIKE '[JM]%'ORDER BY cust_contact;找出所有名字以J 或M 起头的联系人 [JM]匹配方括号中任意一个字符，它也只能匹配单个字符。因此，任何多于一个字符的名字都不匹配。[JM]之后的%通配符匹配第一个字符之后的任意数目的字符，返回所需结果。 2、此通配符可以用前缀字符^（脱字号）来否定。例如，下面的查询匹配以J 和M 之外的任意字符起头的任意联系人名（与前一个例子相反）：输入▼SELECT cust_contactFROM CustomersWHERE cust_contact LIKE '[^JM]%'ORDER BY cust_contact; Access 中的否定集合如果使用的是Microsoft Access，需要用!而不是^来否定一个集合，因此，使用的是[!JM]而不是[^JM]。

使用技巧

1、不要过度使用 2、使用时不要放在搜索最开始处，耗时 3、注意放置位置

7、创建计算字段

计算字段

计算字段并不实际存在于数据库表中。计算字段是运行时在SELECT 语句内创建的。

拼接字段(AS)

将值联结到一起（将一个值附加到另一个值）构成单个值。（+或者||）

1、输入▼SELECT vend_name + ' (' + vend_country + ')'FROM VendorsORDER BY vend_name;输出▼-----------------------------------------------------------Bear Emporium (USA )Bears R Us (USA )Doll House Inc. (USA )Fun and Games (England )Furball Inc. (USA )Jouets et ours (France ) 2、许多数据库（不是所有）保存填充为列宽的文本值，而实际上你要的结果不需要这些空格。为正确返回格式化的数据，必须去掉这些空格。这可以使用SQL 的RTRIM()函数来完成，如下所示：输入▼SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'FROM VendorsORDER BY vend_name;输出▼-----------------------------------------------------------Bear Emporium (USA)Bears R Us (USA)Doll House Inc. (USA)Fun and Games (England)Furball Inc. (USA)Jouets et ours (France) 大多数DBMS 都支持RTRIM()（正如刚才所见，它去掉字符串右边的空格）、LTRIM()（去掉字符串左边的空格）以及TRIM()（去掉字符串左右两边的空格）。3、使用别名，客户端在引用数据时没办法引用未命名的字段。别名（alias）是一个字段或值的替换名。别名用AS 关键字赋予。输入▼SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'AS vend_titleFROM VendorsORDER BY vend_name;输出▼vend_title-----------------------------------------------------------Bear Emporium (USA)Bears R Us (USA)Doll House Inc. (USA)Fun and Games (England)Furball Inc. (USA)Jouets et ours (France)

执行算术计算

对检索出的数据进行算术计算

1、Orders 表包含收到的所有订单，OrderItems 表包含每个订单中的各项物品。下面的SQL 语句检索订单号20008 中的所有物品：输入▼SELECT prod_id, quantity, item_priceFROM OrderItemsWHERE order_num = 20008;输出▼prod_id quantity item_price---------- ----------- ---------------------RGAN01 5 4.9900BR03 5 11.9900BNBG01 10 3.4900BNBG02 10 3.4900BNBG03 10 3.49002、如下汇总物品的价格（单价乘以订购数量）： SELECT prod_id,quantity,item_price,quantity*item_price AS expanded_priceFROM OrderItemsWHERE order_num = 20008;输出▼prod_id quantity item_price expanded_price---------- ----------- ------------ -----------------RGAN01 5 4.9900 24.9500BR03 5 11.9900 59.9500BNBG01 10 3.4900 34.9000BNBG02 10 3.4900 34.9000BNBG03 10 3.4900 34.9000

8、使用函数处理数据

文本处理函数

输入▼SELECT vend_name, UPPER(vend_name) AS vend_name_upcaseFROM VendorsORDER BY vend_name;输出▼vend_name vend_name_upcase--------------------------- ----------------------------Bear Emporium BEAR EMPORIUMBears R Us BEARS R USDoll House Inc. DOLL HOUSE INC.Fun and Games FUN AND GAMES Furball Inc. FURBALL INC.Jouets et ours JOUETS ET OURS可以看到，UPPER()将文本转换为大写，因此本例子中每个供应商都列出两次，第一次为Vendors 表中存储的值，第二次作为列vend_name_upcase 转换为大写。表8-2 常用的文本处理函数函数说明LEFT()（或使用子字符串函数）返回字符串左边的字符LENGTH()（也使用DATALENGTH()或LEN()）返回字符串的长度LOWER()（Access使用LCASE()）将字符串转换为小写LTRIM() 去掉字符串左边的空格RIGHT()（或使用子字符串函数）返回字符串右边的字符RTRIM() 去掉字符串右边的空格UPPER()（Access使用UCASE()）将字符串转换为大写

日期和时间处理函数（可移植性差）

Orders 表中包含的订单都带有订单日期。为在SQL Server 中检索2012 年的所有订单，可如下进行：输入▼SELECT order_numFROM OrdersSQL Server：WHERE DATEPART(yy, order_date) = 2012; Access： WHERE DATEPART('yyyy', order_date) = 2012;分析输出▼order_num-----------2000520006 200072000820009各个版本时间函数存在差异

数值处理函数（只处理数值数据）

表8-3 常用数值处理函数函数说明ABS() 返回一个数的绝对值COS() 返回一个角度的余弦EXP() 返回一个数的指数值PI() 返回圆周率SIN() 返回一个角度的正弦SQRT() 返回一个数的平方根TAN() 返回一个角度的正切

9、汇总数据

聚集函数

对于不需要数据本身，只需要汇总数据，可以使用聚集函数检索数据，以便于分析和报表生成，例如：  确定表中行数（或者满足某个条件或包含某个特定值的行数）； 获得表中某些行的和； 找出表列（或所有行或某些特定的行）的最大值、最小值、平均值。

AVG(返回某列的平均值）

1、使用AVG()返回Products 表中所有产品的平均价格：输入▼SELECT AVG(prod_price) AS avg_priceFROM Products;输出▼avg_price-------------6.823333 2、AVG()也可以用来确定特定列或行的平均值。下面的例子返回特定供应商所提供产品的平均价格：输入▼SELECT AVG(prod_price) AS avg_priceFROM ProductsWHERE vend_id = 'DLL01';输出▼avg_price-----------3.8650AVG()函数忽略列值为NULL 的行。

COUNT（返回某列的行数）

COUNT()函数有两种使用方式： 使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。 使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL 值。输入▼1、SELECT COUNT(*) AS num_custFROM Customers;输出▼num_cust--------5在此例子中，利用COUNT(*)对所有行计数，不管行中各列有什么值。计数值在num_cust 中返回。 2、下面的例子只对具有电子邮件地址的客户计数：输入▼SELECT COUNT(cust_email) AS num_custFROM Customers;输出▼num_cust--------3 如果指定列名，则COUNT()函数会忽略指定列的值为空的行，但如果COUNT()函数中用的是星号（*），则不忽略。

MAX（返回某列最大值）

MAX()返回指定列中的最大值。MAX()要求指定列名，如下所示：输入▼SELECT MAX(prod_price) AS max_priceFROM Products;输出▼max_price----------11.9900

MIN（返回某列最小值）

MIN()的功能正好与MAX()功能相反，它返回指定列的最小值。与MAX()一样，MIN()要求指定列名，如下所示：输入▼SELECT MIN(prod_price) AS min_priceFROM Products;输出▼min_price----------3.4900分析▼其中MIN()返回Products 表中最便宜物品的价格。

SUM（返回某列之和）

1、OrderItems 包含订单中实际的物品，每个物品有相应的数量。可如下检索所订购物品的总数（所有quantity 值之和）：输入▼SELECT SUM(quantity) AS items_orderedFROM OrderItemsWHERE order_num = 20005;输出▼items_ordered----------200分析▼函数SUM(quantity)返回订单中所有物品数量之和，WHERE 子句保证只统计某个物品订单中的物品。 2、SUM()也可以用来合计计算值。在下面的例子中，合计每项物品的item_price*quantity，得出总的订单金额：输入▼SELECT SUM(item_price*quantity) AS total_priceFROM OrderItemsWHERE order_num = 20005;输出▼total_price----------1648.0000

聚集不同值

ALL

对所有行执行计算

DISTINCT

子使用AVG()函数返回特定供应商提供的产品的平均价格。它与上面的SELECT 语句相同，但使用了DISTINCT 参数，因此平均值只考虑各个不同的价格：输入▼SELECT AVG(DISTINCT prod_price) AS avg_priceFROM ProductsWHERE vend_id = 'DLL01';输出▼avg_price-----------4.2400 如果指定列名，则DISTINCT 只能用于COUNT()。DISTINCT 不能用于COUNT(*)。类似地，DISTINCT 必须使用列名，不能用于计算或表达式。

只包含不同值

组合聚集函数

SELECT可包含多个聚集函数一起计算

输入▼SELECT COUNT(*) AS num_items,MIN(prod_price) AS price_min,MAX(prod_price) AS price_max,AVG(prod_price) AS price_avgFROM Products;输出▼num_items price_min price_max price_avg---------- --------------- --------------- ---------9 3.4900 11.9900 6.823333分析▼这里用单条SELECT 语句执行了4 个聚集计算，返回4 个值（Products表中物品的数目，产品价格的最高值、最低值以及平均值）。

10、分组数据

数据分组

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

创建分组

GROUP BY

输入▼SELECT vend_id, COUNT(*) AS num_prodsFROM ProductsGROUP BY vend_id;输出▼vend_id num_prods------- ---------BRS01 3DLL01 4FNG01 2 在使用GROUP BY 子句前，需要知道一些重要的规定。 GROUP BY 子句可以包含任意数目的列，因而可以对分组进行嵌套，更细致地进行数据分组。 如果在GROUP BY 子句中嵌套了分组，数据将在最后指定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。 GROUP BY 子句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）。(与例子黄色部分呼应），如果在SELECT 中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。 大多数SQL 实现不允许GROUP BY 列带有长度可变的数据类型（如文本或备注型字段）。 除聚集计算语句外，SELECT 语句中的每一列都必须在GROUP BY 子句中给出。 如果分组列中包含具有NULL 值的行，则NULL 将作为一个分组返回。如果列中有多行NULL 值，它们将分为一组。 GROUP BY 子句必须出现在WHERE 子句之后，ORDER BY 子句之前。

过滤分组

HAVING,WHERE 子句都可以用HAVING 来替代。

输入▼SELECT cust_id, COUNT(*) AS ordersFROM OrdersGROUP BY cust_idHAVING COUNT(*) >= 2;输出▼cust_id orders---------- -----------1000000001 2分析▼这条SELECT 语句的前三行类似于上面的语句。最后一行增加了HAVING子句，它过滤COUNT(*) >= 2（两个以上订单）的那些分组。可以看到，WHERE 子句在这里不起作用，因为过滤是基于分组聚集值，而不是特定行的值。 WHERE 在数据分组前进行过滤，HAVING 在数据分组后进行过滤。输入▼SELECT vend_id, COUNT(*) AS num_prodsFROM ProductsWHERE prod_price >= 4GROUP BY vend_idHAVING COUNT(*) >= 2;输出▼vend_id num_prods------- -----------BRS01 3FNG01 2 列出具有两个以上产品且其价格大于等于4 的供应商：

WHERE过滤行，而HAVING 过滤分组

分组和排序

理解GROUP BY和ORDER BY的区别

表10-1 ORDER BY与GROUP BYORDER BY GROUP BY对产生的输出排序对行分组，但输出可能不是分组的顺序任意列都可以使用（甚至非选择的列也可以使用）只可能使用选择列或表达式列，而且必须使用每个选择列表达式不一定需要如果与聚集函数一起使用列（或表达式），则必须使用一般在使用GROUP BY 子句时，应该也给出ORDER BY 子句。这是保证数据正确排序的唯一方法。千万不要仅依赖GROUP BY 排序数据。输入▼SELECT order_num, COUNT(*) AS itemsFROM OrderItemsGROUP BY order_num 分组HAVING COUNT(*) >= 3 过滤ORDER BY items, order_num; 排序输出▼order_num items--------- -----20006 3 20009 320007 520008 5 使用GROUP BY 子句按订单号（order_num 列）分组数据，以便COUNT(*)函数能够返回每个订单中的物品数目。HAVING 子句过滤数据，使得只返回包含三个或更多物品的订单。最后，用ORDER BY子句排序输出。

SELECT子句顺序

表10-2 SELECT子句及其顺序子句说明是否必须使用1、SELECT 要返回的列或表达式是2、FROM 从中检索数据的表仅在从表选择数据时使用3、 WHERE 行级过滤否4、 GROUP BY 分组说明仅在按组计算聚集时使用5、 HAVING 组级过滤否6、 ORDER BY 输出排序顺序否

12、联结表

联结

关系表（关系数据库）

把信息分解成多个表，一类数据一个表，各表通过共同的信息相互联结

可建立两个表：一个存储供应商信息，另一个存储产品信息。Vendors 表包含所有供应商信息，每个供应商占一行，具有唯一的标识。此标识称为主键（primary key），可以是供应商ID 或任何其他唯一值。Products 表只存储产品信息，除了存储供应商ID（Vendors 表的主键）外，它不存储其他有关供应商的信息。Vendors 表的主键将Vendors 表与Products 表关联，利用供应商ID 能从Vendors 表中找出相应供应商的详细信息。这样做的好处是： 供应商信息不重复，不会浪费时间和空间； 如果供应商信息变动，可以只更新Vendors 表中的单个记录，相关表中的数据不用改动； 由于数据不重复，数据显然是一致的，使得处理数据和生成报表更简单。

创建联结

WHERE子句的重要性

内联结（基于两个表之间的相等测试）输入▼SELECT vend_name, prod_name, prod_priceFROM Vendors, ProductsWHERE Vendors.vend_id = Products.vend_id;输出▼vend_name prod_name prod_price-------------------- -------------------- ----------Doll House Inc. Fish bean bag toy 3.4900Doll House Inc. Bird bean bag toy 3.4900Doll House Inc. Rabbit bean bag toy 3.4900Bears R Us 8 inch teddy bear 5.9900Bears R Us 12 inch teddy bear 8.9900Bears R Us 18 inch teddy bear 11.9900Doll House Inc. Raggedy Ann 4.9900Fun and Games King doll 9.4900Fun and Games Queen doll 9.4900在一条SELECT 语句中联结几个表时，相应的关系是在运行中构造的。在数据库表的定义中没有指示DBMS 如何对表进行联结的内容。你必须自己做这件事情。在联结两个表时，实际要做的是将第一个表中的每一行与第二个表中的每一行配对。WHERE 子句作为过滤条件，只包含那些匹配给定条件（这里是联结条件）的行。没有WHERE子句，第一个表中的每一行将与第二个表中的每一行配对，而不管它们逻辑上是否能配在一起。----------笛卡儿积（叉联结）输入▼SELECT prod_name, vend_name, prod_price, quantityFROM OrderItems, Products, VendorsWHERE Products.vend_id = Vendors.vend_idAND OrderItems.prod_id = Products.prod_idAND order_num = 20007;输出▼prod_name vend_name prod_price quantity--------------- ------------- ---------- --------18 inch teddy bear Bears R Us 11.9900 50Fish bean bag toy Doll House Inc. 3.4900 100Bird bean bag toy Doll House Inc. 3.4900 100Rabbit bean bag toy Doll House Inc. 3.4900 100Raggedy Ann Doll House Inc. 4.9900 50

内联结

联结多个表

13、创建高级联结

使用表别名

输入▼SELECT cust_name, cust_contactFROM Customers AS C, Orders AS O, OrderItems AS OIWHERE C.cust_id = O.cust_idAND OI.order_num = O.order_numAND prod_id = 'RGAN01';

1、可以缩短SQL语句

2、允许在一条SELECT 语句中多次使用相同的表。

使用不同类型的联结

外联结

包含相关表中没有关联行的行

1、内联结：检索所有顾客及其订单输入▼SELECT Customers.cust_id, Orders.order_numFROM Customers INNER JOIN OrdersON Customers.cust_id = Orders.cust_id; 2、外联结：检索包括没有订单顾客在内的所有顾客输入▼SELECT Customers.cust_id, Orders.order_numFROM Customers（左） LEFT OUTER JOIN Orders（右）ON Customers.cust_id = Orders.cust_id; 在使用OUTERJOIN 语法时，必须使用RIGHT 或LEFT 关键字指定包括其所有行的表（RIGHT 指出的是OUTER JOIN 右边的表，而LEFT 指出的是OUTER JOIN左边的表）。上面的例子使用LEFT OUTER JOIN 从FROM 子句左边的表（Customers 表）中选择所有行。为了从右边的表中选择所有行，需要使用RIGHT OUTER JOIN，如下例所示：输入▼SELECT Customers.cust_id, Orders.order_numFROM Customers RIGHT OUTER JOIN OrdersON Orders.cust_id = Customers.cust_id;

自然联结（内联结））

自联结

替代从相同表中检索数据的使用子查询语句

输入▼SELECT c1.cust_id, c1.cust_name, c1.cust_contactFROM Customers AS c1, Customers AS c2WHERE c1.cust_name = c2.cust_nameAND c2.cust_contact = 'Jim Jones';

使用带聚集函数的联结

可以从多个表中汇总数据

1、使用内联结（自然联结）输入▼SELECT Customers.cust_id,COUNT(Orders.order_num) AS num_ordFROM Customers INNER JOIN OrdersON Customers.cust_id = Orders.cust_idGROUP BY Customers.cust_id;输出▼cust_id num_ord---------- --------1000000001 21000000003 11000000004 11000000005 1 这条SELECT 语句使用INNER JOIN 将Customers 和Orders 表互相关联。GROUP BY 子句按顾客分组数据，因此，函数调用COUNT(Orders.order_num)对每个顾客的订单计数，将它作为num_ord 返回。2、左部外联结输入▼SELECT Customers.cust_id,COUNT(Orders.order_num) AS num_ordFROM Customers LEFT OUTER JOIN OrdersON Customers.cust_id = Orders.cust_idGROUP BY Customers.cust_id; 输出▼cust_id num_ord---------- -------1000000001 21000000002 01000000003 11000000004 11000000005 1

使用联结和联结条件

注意联结类型，确保正确的联结条件

14、组合查询

组合查询

主要有两种情况需要使用组合查询： 在一个查询中从不同的表返回结构数据； 对一个表执行多个查询，按一个查询返回数据。

创建组合查询

使用UNION

1、输入▼SELECT cust_name, cust_contact, cust_emailFROM CustomersWHERE cust_state IN ('IL','IN','MI');输出▼cust_name cust_contact cust_email----------- ------------- ------------Village Toys John Smith sales@villagetoys.comFun4All Jim Jones jjones@fun4all.comThe Toy Store Kim Howard NULL 2、输入▼SELECT cust_name, cust_contact, cust_emailFROM CustomersWHERE cust_name = 'Fun4All'; 输出▼cust_name cust_contact cust_email----------- ------------- ------------Fun4All Jim Jones jjones@fun4all.comFun4All Denise L. Stephens dstephens@fun4all.com 3、组合查询输入▼SELECT cust_name, cust_contact, cust_emailFROM CustomersWHERE cust_state IN ('IL','IN','MI')UNIONSELECT cust_name, cust_contact, cust_emailFROM CustomersWHERE cust_name = 'Fun4All';输出▼cust_name cust_contact cust_email----------- ----------- ----------------Fun4All Denise L. Stephens dstephens@fun4all.comFun4All Jim Jones jjones@fun4all.comVillage Toys John Smith sales@villagetoys.comThe Toy Store Kim Howard NULL

使用UNION规则

1、含两天及两条以上SELECT语句，用UNION分隔开；2、每个查询必须包含相同的列、表达式或聚集函数列3、数据类型必须兼容：类型不必完全相同，但必须是DBMS 可以隐含转换的类型（结果自动去除重复行，想要取消，使用UNION ALL即可）

对组合查询结果排序

ORDER BY秩序使用一次，放在SELECT语句最后即可，ORDER BY用来排序所有SELECT返回结果

15、插入数据

INSERT

数据插入

插入完整行

输入▼INSERT INTO CustomersVALUES('1000000006','Toy Land','123 Any Street','New York','NY','11111','USA',NULL,NULL);每一列都必须具有数据，如某列没有值，应该使用NULL2、更安全却更繁琐的方式 INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)VALUES('1000000006', 'Toy Land', '123 Any Street', 'New York', 'NY', '11111', 'USA', NULL, NULL);

插入行的一部分

如果插入的行中包含空值或者默认值，在语句中可以省略不写，但是前提条件必须满足：  该列定义为允许NULL 值（无值或空值）。 在表定义中给出默认值。这表示如果不给出值，将使用默认值。如果表中不允许有NULL 值或者默认值，这时却省略了表中的值，DBMS 就会产生错误消息，相应的行不能成功插入。

插入查询结果

INSERT SELECT（导出数据）

表的复制

SELECT INSERT（导入数据）

16、更新数据和删除数据

删除数据

DELETE(删除特定行/所有行）

输入▼DELETE FROM CustomersWHERE cust_id = '1000000006';这条语句很容易理解。DELETE FROM 要求指定从中删除数据的表名，WHERE 子句过滤要删除的行。在这个例子中，只删除顾客1000000006。如果省略WHERE 子句，它将删除表中每个顾客。如果想从表中删除所有行，不要使用DELETE。可使用TRUNCATE TABLE语句，它完成相同的工作，而速度更快（因为不记录数据的变动）。

更新数据

UPDATE（更新特定列/所有列）

1、更新单独列输入▼UPDATE CustomersSET cust_email = 'kim@thetoystore.com'WHERE cust_id = '1000000005'；更新数据以表明开始，以WHERE子句结束，如果没有WHERE子句，将会更新到表中的每一列 2、更新多列，只需使用一个SET，，每个“列=值”对之间用逗号分隔（最后一列之后不用逗号）输入▼UPDATE CustomersSET cust_contact = 'Sam Roberts',cust_email = 'sam@toyland.com'WHERE cust_id = '1000000006'; 3、要删除某个列的值，可设置它为NULL（假如表定义允许NULL 值）。如下进行：输入▼UPDATE CustomersSET cust_email = NULLWHERE cust_id = '1000000005';

使用原则

SQL 程序员使用UPDATE 或DELETE 时所遵循的重要原则。 除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE 子句的UPDATE 或DELETE 语句。 保证每个表都有主键（如果忘记这个内容，请参阅第12 课），尽可能像WHERE 子句那样使用它（可以指定各主键、多个值或值的范围）。 在UPDATE 或DELETE 语句使用WHERE 子句前，应该先用SELECT 进行测试，保证它过滤的是正确的记录，以防编写的WHERE 子句不正确。 使用强制实施引用完整性的数据库（关于这个内容，请参阅第12 课），这样DBMS 将不允许删除其数据与其他表相关联的行。 有的DBMS 允许数据库管理员施加约束，防止执行不带WHERE 子句的UPDATE 或DELETE 语句。如果所采用的DBMS 支持这个特性，应该使用它。若是SQL 没有撤销（undo）按钮，应该非常小心地使用UPDATE 和DELETE，否则你会发现自己更新或删除了错误的数据。

WHERE子句的重要性

17、创建和操纵表

创建表

CREATE TABLE

 新表的名字，在关键字CREATE TABLE 之后给出； 表列的名字和定义，用逗号分隔； 有的DBMS 还要求指定表的位置。例如：输入▼CREATE TABLE Products(prod_id CHAR(10) NOT NULL,vend_id CHAR(10) NOT NULL,prod_name CHAR(254) NOT NULL,prod_price DECIMAL(8,2) NOT NULL,prod_desc VARCHAR(1000) NULL);表明在数据库中必须不存在，否则将会返回错误，NOT NULL表示该列不能没有值，NULL表示这列无值，允许NULL值得列是不能作为主键的，因为主键是该表的唯一标识，在SOL中如果在插入行时不给出值，系统将会给出默认值，有DEFAULT指定

更新表

ALTER TABLE

删除表

DROP TABLE（删除整个表）

许多DBMS 允许强制实施有关规则，防止删除与其他表相关联的表。在实施这些规则时，如果对某个表发布一条DROP TABLE 语句，且该表是某个关系的组成部分，则DBMS 将阻止这条语句执行，直到该关系被删除为止。如果允许，应该启用这些选项，它能防止意外删除有用的表。

重命名表

RENAME/ALTER

18、视图

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

输入▼CREATE VIEW ProductCustomers ASSELECT cust_name, cust_contact, prod_idFROM Customers, Orders, OrderItemsWHERE Customers.cust_id = Orders.cust_idAND OrderItems.order_num = Orders.order_num;分析▼这条语句创建一个名为ProductCustomers 的视图，它联结三个表，返回已订购了任意产品的所有顾客的列表。如果执行SELECT * FROMProductCustomers，将列出订购了任意产品的顾客。检索订购了产品RGAN01 的顾客，可如下进行：输入▼SELECT cust_name, cust_contactFROM ProductCustomersWHERE prod_id = 'RGAN01';

用视图重新格式化检索出来的数据

输入▼SELECT RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')'AS vend_titleFROM VendorsORDER BY vend_name;输出▼vend_title-----------------------------------------------------------Bear Emporium (USA)Bears R Us (USA)Doll House Inc. (USA)Fun and Games (England)Furball Inc. (USA)Jouets et ours (France) 输入▼CREATE VIEW VendorLocations ASSELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'AS vend_titleFROM Vendors; 输入▼SELECT *FROM VendorLocations;输出▼vend_title-----------------------------------------------------------Bear Emporium (USA)Bears R Us (USA)Doll House Inc. (USA)Fun and Games (England)Furball Inc. (USA)Jouets et ours (France)

用视图过滤不想要的数据

可以定义CustomerEMailList 视图，过滤没有电子邮件地址的顾客输入▼CREATE VIEW CustomerEMailList ASSELECT cust_id, cust_name, cust_emailFROM CustomersWHERE cust_email IS NOT NULL; 输入▼SELECT *FROM CustomerEMailList;输出▼cust_id cust_name cust_email---------- ------------ ---------------------1000000001 Village Toys sales@villagetoys.com1000000003 Fun4All jjones@fun4all.com1000000004 Fun4All dstephens@fun4all.com

使用视图和计算字段

输入▼SELECT prod_id,quantity,item_price,quantity*item_price AS expanded_priceFROM OrderItemsWHERE order_num = 20008;输出▼prod_id quantity item_price expanded_price-------- --------- ----------- --------------RGAN01 5 4.9900 24.9500BR03 5 11.9900 59.9500BNBG01 10 3.4900 34.9000BNBG02 10 3.4900 34.9000BNBG03 10 3.4900 34.9000要将其转换为一个视图，如下进行：输入▼CREATE VIEW OrderItemsExpanded ASSELECT order_num,prod_id,quantity, item_price,quantity*item_price AS expanded_priceFROM OrderItems;检索订单20008 的详细内容（上面的输出），如下进行：输入▼SELECT *FROM OrderItemsExpandedWHERE order_num = 20008; 输出▼order_num prod_id quantity item_price expanded_price--------- ------- --------- ---------- --------------20008 RGAN01 5 4.99 24.9520008 BR03 5 11.99 59.9520008 BNBG01 10 3.49 34.9020008 BNBG02 10 3.49 34.9020008 BNBG03 10 3.49 34.90

19、使用存储过程

使用存储过程简单、安全、高性能

11、使用子查询

子查询

从单个数据库表中检索单条数据

利用子查询进行过滤

使用子查询将多个查询组合成一个语句进行检索

例如检索要列出订购物品RGAN01 的所有顾客（没办法从单一的一张表里找到自己想要的信息）(1) 检索包含物品RGAN01 的所有订单的编号。(2) 检索具有前一步骤列出的订单编号的所有顾客的ID。(3) 检索前一步骤返回的所有顾客ID 的顾客信息。输入▼SELECT cust_name, cust_contact FROM CustomersWHERE cust_id IN (SELECT cust_id FROM Orders WHERE order_num IN (SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01'));输出▼cust_name cust_contact----------------------------- --------------------Fun4All Denise L. StephensThe Toy Store Kim Howard注释：在OrderItems表中检索产品ID为RGAN01的订单号，从而从Orders表中根据订单号检索顾客的ID，再根据顾客ID检索顾客名以及顾客联系名 2、另一种使用方法：联结多个表输入▼SELECT cust_name, cust_contactFROM Customers, Orders, OrderItemsWHERE Customers.cust_id = Orders.cust_idAND OrderItems.order_num = Orders.order_numAND prod_id = 'RGAN01';输出▼cust_name cust_contact----------------------------- --------------------Fun4All Denise L. StephensThe Toy Store Kim Howard

作为计算字段使用子查询

输入▼SELECT cust_name,cust_state,(SELECT COUNT(*)FROM OrdersWHERE Orders.cust_id = Customers.cust_id) AS ordersFROM CustomersORDER BY cust_name;输出▼cust_name cust_state orders ------------------------- ---------- ------Fun4All IN 1Fun4All AZ 1Kids Place OH 0The Toy Store IL 1Village Toys MI 2 子查询中的WHERE 子句与前面使用的WHERE 子句稍有不同，因为它使用了完全限定列名，而不只是列名（ cust_id ）。它指定表名和列名（Orders.cust_id 和Customers.cust_id）。下面的WHERE 子句告诉SQL，比较Orders 表中的cust_id 和当前正从Customers 表中检索的cust_id：WHERE Orders.cust_id = Customers.cust_id用一个句点分隔表名和列名，在有可能混淆列名时必须使用这种语法。在这个例子中，有两个cust_id 列：一个在Customers 中，另一个在Orders 中。如果不采用完全限定列名，DBMS 会认为要对Orders 表中的cust_id 自身进行比较。因为SELECT COUNT(*) FROM Orders WHERE cust_id = cust_id总是返回Orders 表中订单的总数，而这个结果不是我们想要的：