Loading... **刚学的、刚忘的** 来自看SQL必知必会的笔记，省略了一些内容。 # 第1课 数据库 1. 数据库：保存有组织数据的容器 2. 表：某种特定类型数据的结构化清单 3. 列和数据类型 1. 列：表中的一个字段<br>提示：分解字段，正确地将数据分为多个列极为重要。 2. 数据类型: 所允许的数据类型，限制该列中存储的数据。 4. 行：表中的一个记录 5. 主键：一列（或一组列），其值能够唯一标识表中的每一行<br>需要满足以下条件： 1. 任意两行都不具有相同的主键值 2. 每一行都必须具有一个主键值（主键列不允许NULL值） 3. 主键列中的值不允许修改或更新 4. 主键值不能重用（如果某行从表中删除，它的主键不能赋给以后的新行） # 第2课 检索数据 2.1 SELECT语句 > 关键字，作为SQL组成部分的保留字，关键字不能用作表或列的名字。 2.2 检索单个列 ```mysql SELECT prod_name FROM Products; ``` 如上一条简单的数据将会返回表中的所有行。数据没有过滤，也没有排序。 > 未排序数据：如果没有明确排序结果，则返回的数据没有特定的顺序<br>多条SQL语句必须以“;”分隔。<br>SQL语句一般不区分大小写，不过一般对SQL关键字使用大写，对列名和表名使用小写。<br>SQL语句可以写成长长的一行，也可以分写在多行。 2.3 检索多个列 下面的SELECT语句从Products中选择3列。 ``` SELECT prdo_id, prod_name, prod_price FROM Products; ``` 2.4 检索所有列 ``` SELECT * FROM Products; ``` 给定一个通配符“*”，同时也返回表中所有列。 2.5 检索不同的值 使用DISTINCT关键字，指示数据库返回不同的值。必须直接放在列名前面。 ``` SELECT DISTINCT vend_id FROM Products; ``` > DISTINCT关键字，作用于所有的列，不仅仅是跟在其后的那一列。 2.6 限制结果 假如只想返回第一行或者一定数量的行 SQL Server / Access ``` SELECT TOP 5 prod_name FROM Products; ``` MySQL、MariaDB、PostgreSQL ``` SELECT prod_name FROM Products LIMIT 5; ``` 使用OFFSET指定从开始 ``` SELECT prod_name FROM Products LIMIT 5 OFFSET 5; ``` > 第一个被检索的是第0行，不是第1行。因此，LIMIT 1 OFFSET1会检索第2行，而不是第1行。 > 提示：MySQL、MariaDB、PostgreSQL支持简化版本的 LIMIT 4 OFFSET 3 语句，即`LIMIT 3,4`，逗号之前对应OFFSET，逗号之后 对应LIMIT 2.7 使用注释 ``` # 关于注释的演示 SELECT prod_name FROM Products LIMIT 5; --仅输出前5条 /* SELECT prod_name FROM Products */ # 也可以使用多行注释 ``` # 第3课 排序检索数据 > 子句：SQL语句由子句构成，有些子句是必须的，有些则是可选的。一个子句通常由一个关键字加上所提供的数据组成。子句的例子有我们在前一课看到的SELECT语句的FROM子句。 3.1 按一个列排序 用ORDER BY子句取一个或多个列的名字，据此对输出进行排序。 ``` SELECT prod_name FROM Products ORDER BY prod_name； ``` 3.2 按多个列排序 ``` SELECT prod_name , prod_price, prod_name FROM Products ORDER BY prod_price, prod_name； ``` 3.3 按列位置排序 ``` SELECT prod_name , prod_price, prod_name FROM Products ORDER BY 2, 3； ``` 与上面的查询结果相同，2代表prod_price, 3代表prod_name。 > 当根据不出现在SELECT清单中的列进行排序时，不能采用这项技术。当然，如有必要，可以混合使用实际列名和相对列位置。 3.4 指定排序方向 使用DESC关键字 ``` SELECT prod_name FROM Products ORDER BY prod_name DESC； ``` 多个列：以降序排列产品价格，再加上产品名 ``` SELECT prod_name , prod_price, prod_name FROM Products ORDER BY prod_name DESC, prod_name； ``` > DESC关键字只应用到直接位于前面的列名。如果想在多个列上进行降序排序，必须对每一列指定DESC关键字。 > DESC是DESCENDING的缩写，都可以用。另外使用ASC（ASCENDING）进行升序排序，不过一般不用，因为升序是默认的。 # 第4课 过滤数据 4.1 使用WHERE子句 在SELECT语句中，数据根据WHERE子句中指定的搜索条件进行过滤。WHERE子句在表名（FROM子句）之后给出。 ``` SELECT prod_name, prod price FROM Products WHERE prod_price = 3.49; ``` > 在同时使用WHERE和ORDER BY子句时，应该让ORDER BY位于WHERE之后，否则会产生错误。 4.2 WHERE子句操作符 |操作符|说明| |-|-| |=|等于| |<>|不等于| |!=|不等于| |<|小于| |<=|小于等于| |!<|不小于| |>|大于| |>=|大于等于| |!>|不大于| |BETWEEN|在两个指定的值之间| |IS NULL|为NULL值| > 以上符号有重复，并非所有DBMS都支持所有符号 > NULL值与字段包含0，空字符串，或仅仅包含空格不同 > 通过过滤不包含指定值的文档时，不会返回包含NULL的行。 # 第5课 高级数据过滤 5.1 组合WHERE语句 5.1.1 AND操作符 ``` SELECT prod_id, prod_price, prod_name FROM Products WHERE vend_id = 'DLL01' AND prod_price <=4; ``` 检索由供应商DLL01制造且价格小于等于4美元的所有产品的名称和价格。 > 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'; ``` 检索由任一指定供应商制造的所有产品的产品名和价格。 5.1.3 求值顺序 ``` SELECT prod_name, prod_price FROM Products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01' AND prod_price >=10; ``` SQL在处理OR操作符前，优先处理AND操作符。所以以上语句的筛选条件是： 1. 由BRS01公司制造且价格大于10 2. 由DLL01公司制造 须采用“()”对操作符进行明确分组 ``` SELECT prod_name, prod_price FROM Products WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01') AND prod_price >=10; ``` 5.2 IN操作符 IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。IN取一组由逗号分隔、括在圆括号中的合法值。 ``` SELECT prod_id, prod_price, prod_name FROM Products WHERE vend_id IN ('DLL01','BRS01') ORDER BY prod_name; ``` 效果等同于 ``` SELECT prod_id, prod_price, prod_name FROM Products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01' ORDER BY prod_name; ``` > 在有很多合法选项时，IN 操作符的语法更加清楚、直观。 > 在与其他AND和OR操作符组合使用IN时，求值顺序更容易管理。 > IN操作符一般比一组OR操作符执行得更快 > IN 最大的优点是可以包含其他SELECT语句，能够更动态地建立WHERE子句。 5.3 NOT操作符 否定其后所跟的任何条件。 ``` 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; ``` # 第6课 用通配符进行过滤 6.1 LIKE操作符 > 通配符：用来匹配值 的一部分的特殊字符。 > 由字面值、通配符或两者组合构成的搜索条件。 > 操作符何时不是操作符？答案是，它作为谓词时。从技术上说，LIKE是谓词而不是操作符。 通配符搜索只能用于文本字段（串），非文本数据类型字段不能使用通配符搜索。 6.1.1 百分号（%）通配符 在搜索串中，%表示任何字符出现任意次数（包括0次）。 ``` SELECT prod_id prod_name FROM Products WHERE prod_name LIKE 'Fish%' ``` > Microsoft Access 使用*而不是% > 注意DBMS是否区分大小写 ``` SELECT prod_id prod_name FROM Products WHERE prod_name LIKE '%bean bag%' ``` 搜索模式'%bean bag%'表示匹配任何位置上包含文本bean bag的值。 ``` SELECT prod_id prod_name FROM Products WHERE prod_name LIKE 'F%y' ``` > 可以使用WHERE email LIKE b%forta.com来根据部分信息搜索电子邮件地址。 > 包括Access在内的许多DBMS都用空格来填补字段的内容，例如某列有50个字符，那Fish bean bag toy（17个字符）后会接上33个空格。这样可能会对上述SQL语句有负面影响，因为不是y结尾了。 > 通配符%不匹配NULL 6.1.2 下划线(_)通配符 匹配单个字符。 > Microsft Access 使用?而不是_ 6.1.3 方括号([])通配符 > 不是所有DBMS都支持用来创建集合的[]。只有微软的Access和SQL Server支持集合。具体请查询相关文档。 ``` SELECT cust_contact FROM Customers WHERE cust_contact LIKE '[JM]%' ORDER BY cust_contact; ``` 搜索以J或M打头的联系人。 ``` SELECT cust_contact FROM Customers WHERE cust_contact LIKE '[^JM]%' ORDER BY cust_contact; ``` 不以J或M打头的联系人 > Microsoft Access使用!而不是^来否定一个集合 6.2 使用通配符的技巧 通配符比其它搜索慢。这里给出一些使用技巧。 > 不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。 > 在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始处。把通配符置于开始处搜索起来是最慢的。 > 仔细注意通配符的位置。如果放错地方，可能不会返回想要的结果。 # 第7课 创建计算字段 7.1 计算字段 存储在数据库表中的数据一般不是应用程序所需要的格式，计算字段并不实际存在于数据库表中。计算字段是运行时在SELECT语句内创建的。 > 字段（field） > 基本上与列（column）的意思相同，经常互换使用，不过数据库列一 般称为列，而术语字段通常与计算字段一起使用。 7.2 拼接字段 将两个列拼接起来，依据DBMS可以使用`+`或者`||` > Access 和 SQL Server 使用`+`号。DB2、Oracle、PostgreSQL、SQLite 和 Open Office Base使用`||`。 使用加号的例子 ``` SELECT vend_name + '(' + vend_country + ')' FROM Vendors ORDER BY vend_name; ``` 使用`||`的例子 ``` SELECT vend_name || '(' || vend_country || ')' FROM Vendors ORDER 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 ) ``` 以下是MySq或MariaDB时需要使用的语句 ``` SELECT Concat(vend_name, '(', vend_country, ')' ) FROM Vendors ORDER BY vend_name; ``` SELECT语句返回的输出。结合成一个计算字段的两个列用空格填充。许多数据库(不是所有)保存填充为列宽的文本值，而实际上你要的结果不需要这些空格。为正确返回格式化的数据，必须去掉这些空格。 使用加号的例子 ``` SELECT RTRIM(vend_name) + '(' + RTRIM(vend_country) + ')' FROM Vendors ORDER BY vend_name; ``` 使用`||`的例子 ``` SELECT RTRIM(vend_name) || '(' || RTRIM(vend_country) || ')' FROM Vendors ORDER 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) ``` > TRIM函數 > 大多数DBMS都支持RTRIM()（去掉字符串右边的空格）、LTRIM() （去掉字符串左边的空格）以及TRIM()（去掉字符串左右两边的空格） **使用别名** 别名（alias）是一个字段或值的 替换名 使用加号的例子 ``` SELECT RTRIM(vend_name) + '(' + RTRIM(vend_country) + ')' AS vend_title FROM Vendors ORDER 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) ``` 使用`||`的例子 ``` SELECT RTRIM(vend_name) || '(' || RTRIM(vend_country) || ')' AS vend_title FROM Vendors ORDER BY vend_name; ``` 以下是MySq或MariaDB时需要使用的语句 ``` SELECT Concat(vend_name, '(', vend_country, ')' ) AS vend_title FROM Vendors ORDER BY vend_name; ``` > **AS通常可选** > 在很多DBMS中，AS关键字是可选的，不过最好使用它，这被视为 一条最佳实践。 > **别名的其他用途** > 别名还有其他用途。常见的用途包括在实际的表列名包含不合法的字符（如空格）时重新命名它，在原来的名字含混或容易误解时扩充它。 7.3 执行算术计算 ``` SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM OrderItems WHERE order_num = 20008 ``` 支持符号 |操作符|说明| |-|-| |+|加| |-|减| |*|乘| |/|除| # 第8课 使用数据处理函数 8.1 函数 > 与几乎所有DBMS都等同地支持SQL语句（如SELECT）不同，每一个DBMS都有特定的函数。事实上，只有少数几个函数被所有主要的DBMS等同地支持。 DBMS函数的差异 |函数|语法| |:-|:-| |提取字符串的组成部分|Access使用 MID()；<br>DB2、Oracle、PostgreSQL和SQLite使用SUBSTR()；<br>MySQL和SQL Server使用SUBSTRING()| |数据类型转换|Access和Oracle使用多个函数，每种类型的转换有一个函数；<br>DB2 和PostgreSQL使用CAST()；<br>MariaDB. MySQL和SQL Server使用 CONVERT()| |取当前日期|Access 使用 NOW();<br>DB2 和 PostgreSQL 使用 CURRENT_DATE；<br>MariaDB和MySQL使用CURDATE()；<br>Oracle使用SYSDATE；<br>SQL Server用GETDATE()；<br>SQLite使用DATE()| 8.2 使用函数 大多数SQL支持以下类型的函数 > 用于处理文本字符串的文本函数。 > 用于在数值数据上进行算术操作的数值函数、 > 用于处理日期和时间值并从这些值中提取特定成分的日期和时间函数。 > 返回DBMS正使用的特殊信息的系统函数。 8.2.1 文本函数 例：使用UPPER()将文本转为大写。 ``` SELECT vend_name, UPPER(vend_name) AS vend_name_upcase FROM Vendors ORDER BY vend_name; ``` 常用文本处理函数 |函数|说明| |:-|:-| |LEFTO （或使用子字符串函数）|返回字符串左边的字符| |LENGTH()（也使用DATALENGTH()或LEN()）|返回字符串的长度| |LOWER()（Access使用LCASE()）|将字符串转换为小写| |LTRIM()|去掉字符串左边的空格| |RIGHT()（或使用子字符串函数）|返回字符串右边的字符| |RTRIM()|去掉字符串右边的空格| |SOUNDEX()|返回字符串的SOUNDEX值| |UPPERQ()(Access使用UCASE()）|将字符串转换为大写| > SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法 > 如果在创建SQLite时使用了SQLITE_SOUNDEX编译时选项，那么SOUNDEX()在SQLite中就可用。因为SQLITE_SOUNDEX不是默认的编译时选项，所以多数SQLite实现不支持SOUNDEX。 > 例：Michael Green和 Michelle Green发音相似，所以它们的SOUNDEX值匹配 8.2.2 日期和时间处理函数 检索2012年的所有订单 SQL SERVER 和 Sybase版本如下 ``` SELECT order_num FROM Orders WHERE DATEPART(yy, order_date) = 2012; ``` Access 版本如下 ``` SELECT order_num FROM Orders WHERE DATEPART('yyyy', order_date) = 2012; ``` 使用DATE_PART() 的类似函数的PostgreSQL版本： ``` SELECT order_num FROM Orders WHERE DATE_PART('year', order_date) = 2012; ``` Oracle 没有DATEPART()函数，不过有几个可用来完成相同检索的日期处理函数。例如： ``` SELECT order_num FROM Orders WHERE to_number(to_char(order_date, 'YYYY')) = 2012; ``` 或者使用BETWEENR操作符 ``` SELECT order_num FROM Orders WHERE order_date BETWEEN to_date('01-01-2012') AND to_date('12-31-2012') ``` 在此例中，Oracle的to_date()函数用来将两个字符串转换为日期。BETWEEN操作符用来找出两个日期之间的所有订单。 MySQL和MariaDB具有各种日期处理函数，但没有DATEPART()。MySQL和MariaDB用户可使用名为YEAR()的函数从日期中提取年份： ``` SELECT order_num FROM Orders WHERE YEAR(order_date) = 2012; ``` 8.2.3 常用数值处理函数 |函数|说明| |:-:|:-:| |ABS()|返回一个数的绝对值| |COS()|返回一个角度的余弦| |EXP()|返回一个数的指数值| |PI()|返回圆周率| |SIN()|返回一个角度的正弦| |SQRT()|返回一个数的平方根| |TAN()|返回一个角度的正切| 具体请参阅对应文档 # 第9课 汇总数据 9.1 聚集函数 有时需要汇总数据而不用把它们实际检索出来。 > **聚集函数（ aggregate function)** > 对某些行运行的函数，计算并返回一个值。 |函数|说明| |:-:|:-:| |AVG()|返回某列的平均值| |COUNT()|返回某列的行数| |MAX()|返回某列的最大值| |MIN()|返回某列的最小值| |SUM()|返回某列值之和| 9.1.1 AVG()函数 返回所有列的平均值： ``` SELECT AVG(prod_price) AS avg_price FROM Products; ``` 返回特定列的平均值： ``` SELECT AVG(prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01'; ``` > 注意 AVG()函数只能用于单个列，并且忽略值为NULL的行。 9.1.2 COUNT()函数 COUNT()函数进行计数。可利用COUNT()确定表中行的数目或符合特定条件的行的数目。 两种使用方式： 使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值 (NULL)还是非空值。 ``` SELECT COUNT(*) AS num_cust FROM Customers; ``` 指定列名会忽略指定列的值为空的行，下面的例子仅对具有电子邮箱的客户计数 ``` SELECT COUNT(cust_email) AS num_cust FROM Customers; ``` 9.1.3 MAX()函数 MAX()返回指定列中的最大值。MAX()要求指定列名，如下： ``` SELECT MAX(prod_price) AS max_price FROM Products; ``` > 许多(并非所有) DBMS允许将它用来返回任意列中的最大值，包括返回文本列中的最大值。在用于文本数据时，MAX()返回按该列排序后的最后一行。 > MAX()函数忽略列值为NULL的行。 9.1.4 MIN()函数 略 > 同上返过来，MIN()返回按该列排序后的第一行。 > 同上 9.1.5 SUM()函数 SUM()用来返回指定列值的和(总计)。 SUM()也可以用来合计计算值。在下面的例子中，合计每项物品的 item_price*quantity，得出总的订单金额： ``` SELECT SUM(item_price*quantity) AS total_price FROM OrderItems WHERE order_num = 20005; ``` > **在多个列上进行计算** > 如本例所示，利用标准的算术操作符，所有聚集函数都可用来执行多个列上的计算。 > **NULL值** > SUM()函数忽略列值为NULL的行。 9.2 聚集不同值 以上5个聚集函数都可以如下使用。 1. 对所有行执行计算，指定ALL参数或不指定参数(因为ALL是默认行为)。 2. 只包含不同的值，指定DISTINCT参数。 > Microsoft Access在聚集函数中不支持DISTINCT。 下面的例子使用AVG()函数返回特定供应商提供的产品的平均价格。使用了DISTINCT参数，因此平均值只考虑各个不同的价格： ``` SELECT AVG(DISTINCT prod_price) AS avg.price FROM Products WHERE vend_id = 'DLLO1'; ``` > 如果指定列名，则DISTINCT只能用于COUNT()，DISTINCT不能用于C0UNT(*)。类似地，DISTINCT必须使用列名，不能用于计算或表达式。 > DISTINCT从技术上可用于MIN()和MAX（）,但这样做实际上没有价值。 9.3 组合聚集函数 ``` SELECT COUNT(*) AS num_items MIN(prod_price) AS price_min MAX(prod_price) AS price_max AVG(prod_price) AS price_avg FROM Products; ``` > 在指定别名以包含某个聚集函数的结果时，不应该使用表中实际的列名。虽然这样做也算合法，但许多SQL实现不支持，可能会产生模糊的错误消息。 # 第10课 分组数据 这一课介绍如何分组数据，以便汇总表内容的子集。这涉及两个新 SELECT语句子句：GROUP BY子句和HAVING子句。 10.1 数据分组 一堆废话 10.2 创建分组 使用SELECT语句的GROUP BY子句建立分组。 ``` SELECT vend_id, COUNT(*) AS num_prods FROM Products GROUP BY vend_id; ``` 在使用GROUP BY子句前，需要知道一些重要的规定。 * GROUP BY子句可以包含任意数目的列，因而可以对分组进行嵌套，更细致地进行数据分组。 * 如果在CROUP BY子句中嵌套了分组，数据将在最后指定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。 * GROUP BY子句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY 子句中指定相同的表达式。不能使用别名。 * 大多数SQL实现不允许GROUP BY列带有长度可变的数据类型（如文 本或备注型字段）。 * 除聚集计算语句外，SELECT语句中的每一列都必须在GROUP BY子句中给出。 * 如果分组列中包含具有NULL值的行，则NULL将作为一个分组返回。 如果列中有多行NULL值，它们将分为一组。 * GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。 > 有的SQL实现允许根据SELECT列表中的位置指定GROUP BY的列。 10.3 过滤分组 SQL还允许过滤分组，使用HAVING语句过滤分组（区分使用WHERE过滤行） > **HAVING支持所有WHERE操作符** > 在第4课和第5课中，我们学习了WHERE子句的条件（包括通配符条件和带多个操作符的子句）。学过的这些有关WHERE的所有技术和选项都适用于HAVING()，它们的句法是相同的，只是关键字有差别。 > **HAVING和WHERE的差别** 这里有另一种理解方法，WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。这是一个重要的区别，WHERE排除的行不包括在分组中。这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。 同时使用HAVING 和WHERE: 例：列出具有两个以上产品且其价格大于等于4的供应商： ``` SELECT vend_id, COUNT(*) AS num_prods WHERE prod_price >=4 GROUP BY vend_id HAVING COUNT(*) >=2; ``` tips: 大于等于4这个条件在聚集后就不能用了，所以要用WHERE先筛选出来。 > **使用HAVING和WHERE** > HAVING与WHERE非常类似，如果不指定GROUP BY,则大多数DBMS 会同等对待它们。不过，你自己要能区分这一点。使用HAVING时应 该结合GROUP BY子句，而WHERE子句用于标准的行级过滤。 10.4 分组和排序 ORDER BY 和GROUP BY经常完成相同的工作，但他们非常不同。 |ORDER BY|GRUOP BY| |:-|:-| |对产生的输出排序|对行分组，但输出可能不是分组的顺序| |任意列都可以使用（甚至非选择的列也可以使用）|只可能使用选择列或表达式列，而且必须使用每个选择列表达式| |不一定需要|如果与聚集函数一起使用列（或表达式），则必须使用| > **不要忘记ORDER BY** > 一般在使用GROUP BY子句时，应该也给出ORDER BY子句。这是保证数据正确排序的唯一方法。千万不要仅依赖GROUP BY排序数据 <div class="panel panel-default collapse-panel box-shadow-wrap-lg"><div class="panel-heading panel-collapse" data-toggle="collapse" data-target="#collapse-4a5b2f6c7e3831d30b01595d315aa16e82" aria-expanded="true"><div class="accordion-toggle"><span style="">为什么要使用GROUP BY</span> <i class="pull-right fontello icon-fw fontello-angle-right"></i> </div> </div> <div class="panel-body collapse-panel-body"> <div id="collapse-4a5b2f6c7e3831d30b01595d315aa16e82" class="collapse collapse-content"><p></p> 第一项差别极为重要。我们经常发现，用GROUP BY分 组的数据确实是以分组顺序输出的。但并不总是这样，这不是SQL规范 所要求的。此外，即使特定的DBMS总是按给出的GROUP BY子句排序 数据，用户也可能会要求以不同的顺序排序。就因为你以某种方式分组数据（获得特定的分组聚集值），并不表示你需要以相同的方式排序输出。应该提供明确的ORDER BY子句，即使其效果等同于GROUP BY子句。 <p></p></div></div></div> 例： ``` SELECT order_num, COUNT(*) AS items FROM OrderItems GROUP BY order_num HAVING COUNT(*) >= 3 --必须使用相同的表达式，不能使用别名 ORDER BY items, order_num; ``` > **Access的不兼容性** > Microsoft Access不允许按别名排序，解决方法是用实际的计算或字段位置替换items（在ORDER BY子句中), 即 `ORDER BY COUNT(*), order_num` 或 `ORDER BY 2, order_num`。 10.5 SELECT 子句顺序 |子句|说明|是否必须使用| |:-|:-|:-| |SELECT|要返回的列或表达式|是| |FROM|从中检索数据的表|仅在从表选择数据时使用| |WHERE|行级过滤|否| |GROUP BY|分组说明|仅在按组计算聚集时使用| |HAVING|组级过滤|否| |ORDER BY|输出排序顺序|否| # 第11课 使用子查询 11.1 子查询 SQL允许创建子查询（subquery）,即嵌套在其他查询中的查询。 11.2 SQL还允许创建子查询（subquery）,即嵌套在其他查询中的查询 例： 订单存储在两个表中。每个订单包含订单编号、客户ID、订单日期，在Orders表中存储为一行。各订单的物品存储在相关的Orderltems表中。Orders表不存储顾客信息，只存储顾客ID。顾客的实际信息存储在Customers表中。 列出订购物品RGANO1的所有顾客。 步骤： 1. 检索包含物品RCANO1的所有订单的编号。 2. 检索具有前一步骤列出的订单编号的所有顾客的ID。 3. 检索前一步骤返回的所有顾客ID的顾客信息。 ``` SELECT cust_name, cust_contact FROM Customers WHERE cust_id IN (SELECT cust_id FROM Order WHERE order_num IN (SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01')); # 括号不要少打了 ``` > **格式化SQL** > 包含子查询的SELECT语句难以阅读和调试，它们在较为复杂时更是如此。如上所示，把子查询分解为多行并进行适当的缩进，能极大地简化子查询的使用。 > **只能是单列** 作为子查询的SELECT语句只能查询单个列。企图检索多个列将返回 错误。 > **子查询和性能** 这里给出的代码有效，并且获得了所需的结果。但是，使用子查询并不总是执行这类数据检索的最有效方法。 <div class="panel panel-default collapse-panel box-shadow-wrap-lg"><div class="panel-heading panel-collapse" data-toggle="collapse" data-target="#collapse-d02bd391d2fe54fed04770636779685d53" aria-expanded="true"><div class="accordion-toggle"><span style="">分析过程</span> <i class="pull-right fontello icon-fw fontello-angle-right"></i> </div> </div> <div class="panel-body collapse-panel-body"> <div id="collapse-d02bd391d2fe54fed04770636779685d53" class="collapse collapse-content"><p></p> 第一条 SELECT语句： 输入▼ ``` SELECT order_num FROM OrderItems WHERE prod_id = 'RGANO1'; ``` 输出▼ ``` order_num --------------------------- 20007 20008 ``` 第二条 SELECT语句： 输入▼ ``` SELECT cust_id FROM Orders WHERE order.num IN (20007,20008); ``` 输出▼ ``` cust_id --------------------------- 1000000004 1000000005 ``` 组合： 输入▼ ``` SELECT cust_id FROM Orders WHERE order_num IN (SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01'); ``` 输出▼ ``` cust_id --------------------------- 1000000004 1000000005 ``` 第三条SELECT语句也如法炮制 <p></p></div></div></div> 11.3 作为计算字段使用子查询 使用子査询的另一方法是创建计算字段。假如需要显示Customers表中 每个顾客的订单总数。订单与相应的顾客ID存储在Orders表中。 执行这个操作，要遵循下面的步骤： 1. 从Customers表中检索顾客列表； 2. 对于检索出的每个顾客，统计其在Orders表中的订单数目。 ``` SELECT cust_name, cust_state, (SELECT COUNT(*) FROM Orders WHERE Orders.cust_id = Customers.cust_id) AS orders FROM Customers ORDER BY cust_name; ``` 这条SELECT语句对Customers表中每个顾客返回三列：cust_name、cust_state和orders。orders是一个计算字段，它是由圆括号中的子查询建立的。 > **完全限定列名**（指定指定表名和列名） > 如果在 SELECT语句中操作多个表，就应使用完全限定列名来避免歧义。 这里给出的样例代码运行良好，但它并不是解决这种数据检索的最有效方法。 # 第12课 联结表 12.1 联结 先了解关系表以及数据库设计的基础知识 12.1.1 关系表 关系表的设计就是要把信息分解成多个表，一类数据一个表。各 表通过某些共同的值互相关联。 关系数据库的可伸缩性远比非关系数据库要好。 <div class="panel panel-default collapse-panel box-shadow-wrap-lg"><div class="panel-heading panel-collapse" data-toggle="collapse" data-target="#collapse-a3b02c52569cb26316db66ebc6981b3929" aria-expanded="true"><div class="accordion-toggle"><span style="">举例：产品与供应商</span> <i class="pull-right fontello icon-fw fontello-angle-right"></i> </div> </div> <div class="panel-body collapse-panel-body"> <div id="collapse-a3b02c52569cb26316db66ebc6981b3929" class="collapse collapse-content"><p></p> 有一个包含产品目录的数据库表，其中每类物品占一行。对于每一种物品要存储的信息包括产品描述、价格，以及生产该产品的供应商。 现在有同一供应商生产的多种物品，将供应商名、地址、联系方法等供应商信息与产品信息分开存储的理由是： > 同一供应商生产的每个产品，其供应商信息都是相同的，对每个产品 重复此信息既浪费时间又浪费存储空间； > 如果供应商信息发生变化，例如供应商迁址或电话号码变动，只需修改一次即可； > 如果有重复数据（即每种产品都存储供应商信息），则很难保证每次 输入该数据的方式都相同。不一致的数据在报表中就很难利用。 建立两个表：一个存储供应商信息，另一个存储产品信息。Vendors表包含所有供应商信息，每个供应商占一行，具有唯一的 标识。此标识称为主键（primary key），可以是供应商ID或任何其他唯一值。 Products表只存储产品信息，除了存储供应商ID （Vendors表的主键） 外，它不存储其他有关供应商的信息。Vendors表的主键将Vendors表与Products表关联，利用供应商ID能从Vendors表中找出相应供应商的详细信息。 <p></p></div></div></div> > 能够适应不断増加的工作量而不失败。设计良好的数据库或应用程序 称为可伸缩性好(scale well)。 12.1.2 为什么使用联结 联结是一种机制，用来在一条SELECT语句中关联表。 例： ``` SELECT vend_name, prod_name, prod_price FROM Vendors, Products WHERE Vendors.vend_id = Products.vend_id; ``` > **完全限定列名** 就像前一课提到的，在引用的列可能出现歧义时，必须使用完全限定 列名（用一个句点分隔表名和列名） 12.2.1 WHERE子句的重要性 * 要**保证所有联结都有WHERE子句**，否则DBMS将返回比想要的数据多得多的数据。 * 同理，要**保证WHERE子句的正确性**。不正确的过滤条件 会导致DBMS返回不正确的数据。 <div class="panel panel-default collapse-panel box-shadow-wrap-lg"><div class="panel-heading panel-collapse" data-toggle="collapse" data-target="#collapse-00ee5c3e1ddd33d306a690a068c2b65e72" aria-expanded="true"><div class="accordion-toggle"><span style="">为什么一定要WHERE子句</span> <i class="pull-right fontello icon-fw fontello-angle-right"></i> </div> </div> <div class="panel-body collapse-panel-body"> <div id="collapse-00ee5c3e1ddd33d306a690a068c2b65e72" class="collapse collapse-content"><p></p> 在一条SELECT语句中联结几个表时，相应的关系是在运行中构造的。在数据库表的定义中没有指示DBMS如何对表进行联 结的内容。你必须自己做这件事情。在联结两个表时，实际要做的是将 第一个表中的每一行与第二个表中的每一行配对。WHERE子句作为过滤 条件，只包含那些匹配给定条件（这里是联结条件）的行。没有WHERE 子句，第一个表中的每一行将与第二个表中的每一行配对，而不管它们 逻辑上是否能配在一起。 > **笛卡儿积（cartesian product）** 由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目 将是第一个表中的行数乘以第二个表中的行数。 <p></p></div></div></div> 12.2.2 内联结 等值联结(equijoin)/内联结(inner join)，它基于两个表之间的相等测试。 可以明确指定联结的类型 ``` SELECT vend_name, prod_name, prod.price FROM Vendors INNER JOIN Products ON Vendors.vend_id = Products.vend_id; ``` * 两个表之间的关系是以INNER JOIN指定的部分FROM子句。 * 在使用这种语法时，联结条件用特定的ON子句而不是WHERE子句给出。 12.2.3 联结多个表 ``` SELECT prod_name, vend_name, prod_price, quantity FROM OrderItems, Products, Vendors WHERE Products.vend_id = Vendors.vend_id AND OrderItems.prod_id = Products.prod_id AND order_num = 20007; ``` 这个例子显示订单20007中的物品。 1. 订单物品存储在OrderItems表中。 2. 每个产品按其产品ID存储，它引用Products表中的产品。 3. 这些产品通过供应商ID联结到Vendors表中相应的供应商，供应商ID存储在每个产品的记录中。 > 联结的表越多，性能下降越厉害 回顾第11课中的例子，如下的SELECT语句返回订购产品 RGANO1的顾客列表： ``` SELECT cust_name, cust_contact FROM Customers WHERE cust_id IN (SELECT cust_id FROM Order WHERE order_num IN (SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01')); # 括号不要少打了 ``` 使用联结的相同查询 ``` SELECT cust_name, cust_contact FROM Customers, Orders, OrderItems WHERE Customers.cust_id = Orders.cust_id AND OrderItems.order_num = Orders.order_num AND prod.id = 'RGAN0l'; ``` # 第13课 创建高级联结 13.1 使用表别名 SQL除了可以对列名和计算字段使用别名，还允许给表名起别名。 前一课例： ``` SELECT cust_name, cust_contact FROM Customers AS C, Orders AS O , OrderItems AS OI WHERE C.cust_id = O.cust_id AND OI.order_num = O.order_num AND prod.id = 'RGAN01'; ``` > **Oracle中没有AS** > Oracle不支持AS关键字。要在Oracle中使用别名，可以不用AS,简单地指定列名即可（因此，应该是Customers C，而不是Customers AS C)。 13.2 使用不同类型的联结 除开内联结或等值联结，还有自联结(self-join)、自然联结(natural join)和外联结(outer join)。 13.2.1 自联结 使用表别名的一个主要原因是能在一条SELECT语句中不止一次引用相同的表。 <div class="panel panel-default collapse-panel box-shadow-wrap-lg"><div class="panel-heading panel-collapse" data-toggle="collapse" data-target="#collapse-5fd0460dacb5b71d1b3db38aaf2210ce9" aria-expanded="true"><div class="accordion-toggle"><span style="">给和Jim Jones同一公司的所有顾客发送邮件</span> <i class="pull-right fontello icon-fw fontello-angle-right"></i> </div> </div> <div class="panel-body collapse-panel-body"> <div id="collapse-5fd0460dacb5b71d1b3db38aaf2210ce9" class="collapse collapse-content"><p></p> 假如要给与Jim Jones同一公司的所有顾客发送一封信件。这个查询要求首先找出JimJones工作的公司，然后找出在该公司工作的顾客。 ``` SELECT cl.cust_id, cl.cust_name, cl.cust_contact FROM Customers AS cl, Customers AS c2 WHERE cl.cust_name = c2.cust_name AND c2.cust_contact = 'Jim Jones'; ``` 此査询中需要的两个表实际上是相同的表，因此Customers表在FROM 子句中出现了两次。虽然这是完全合法的，但对Customers的引用具有歧义性，因为DBMS不知道你引用的是哪个Customers表。 解决此问题，需要使用表别名。Customers第一次出现用了别名C1,第二次出现用了别名C2。现在可以将这些别名用作表名。例如，SELECT语句使用C1前缀明确给出所需列的全名。如果不这样，DBMS将返回错误, 因为名为cust_id、cust_name、cust_contact的列各有两个。DBMS不知道想要的是哪一列（即使它们其实是同一列）。WHERE首先联结两个表，然后按第二个表中的cust_contact过滤数据，返回所需的数据。 tips. 也可以使用子查询的方法。 另外，Oracle中没有AS > 用自联结而不用子查询 自联结通常作为外部语句，用来替代从相同表中检索数据的使用子查询语句。虽然最终的结果是相同的，但许多DBMS处理联结远比处理子查询快得多。应该试一下两种方法，以确定哪一种的性能更好。 <p></p></div></div></div> 13.2.2 自然联结 <div class="panel panel-default collapse-panel box-shadow-wrap-lg"><div class="panel-heading panel-collapse" data-toggle="collapse" data-target="#collapse-81d10f7f317c5414268008486fec1c8b23" aria-expanded="true"><div class="accordion-toggle"><span style="">自然联结排除多次出现，使每一列只返回一次。系统不提供这项服务，需要自己完成。</span> <i class="pull-right fontello icon-fw fontello-angle-right"></i> </div> </div> <div class="panel-body collapse-panel-body"> <div id="collapse-81d10f7f317c5414268008486fec1c8b23" class="collapse collapse-content"><p></p> ``` SELECT C.* O.order_num, O.order_date, OI.prod_id, OI.quantity, OI.item_price FROM Customers AS C, Order AS O, OrderItems AS OI WHERE C.cust_id = O.cust_id AND OI.order_num = O.order_num AND prod_id = 'RGAN01'; ``` > Oracle中没有AS Oracle用户应该记住去掉AS。 无论何时对表进行联结，应该至少有一列不止岀现在一个表中（被联结的列）。标准的联结（前一课中介绍的内联结）返回所有数据，相同的列甚至多次出现。自然联结排除多次出现，使每一列只返回一次。 怎样完成这项工作呢？答案是，系统不完成这项工作，由你自己完成它。 自然联结要求你只能选择那些唯一的列,一般通过对一个表使用通配符（SELECT *），而对其他表的列使用明确的子集来完成。 <p></p></div></div></div> 13.2.3 外联结 外联结包含了那些在相关表中没有关联行的行，分为左外联结、右外联结、全外联结（不全支持）。 > 用来创建外联结的语法在不同的SQL实现中可能稍有不同。 内联结 ``` SELECT Customers.cust_id, Orders.order_num FROM Customers INNER JOIN Orders ON Customers.cust_id = Orders.cust_id ``` 外联结语法类似。以下检索包括没有订单顾客在内的所有顾客。 ``` SELECT Customers.cust_id, Orders.order_num FROM Customers LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id ``` 外联结包括没有关联行的行。**在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表**（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）。 > **SQLite外联结** SQLite支持 LEFT OUTER JOIN，但不支持 RIGHT OUTER JOIN。 > 总是有两种基本的外联结形式：左外联结和右外联结。调整FROM或WHERE子句中表的顺序，左外联结可以转换为右外联结。 全外联结(FULL OUTER JOIN)，它检索两个表中的所有行并关联那些可以关联的行。 > Access、MariaDB、MySQLOpen Office Base 或SQLite 不支持FULL OUTER JOIN语法。 13.3 使用带聚集函数的联结 检索所有顾客及每个顾客所下的订单数 ``` SELECT Customers.cust_id, Count(Orders.order_num) AS num_ord FROM Customers INNER JOIN Orders ON Customers.cust_id = Orders.cust_id GRUOP BY Customers.cust_id; ``` 这条SELECT语句使用INNER JOIN将Customers和Orders表互相关联。 GROUP BY子句按顾客分组数据，因此，函数调用COUNT(Orders. order_num)对每个顾客的订单计数，将它作为num_ord返回。 与其他联结使用 ``` SELECT Customers.cust_id, Count(Orders.order_num) AS num_ord FROM Customers LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id GROUP BY Customers.cust_id; ``` > Oracle中没有AS 再次提醒Oracle用户，请记住删除AS。 这个例子使用左外部联结来包含所有顾客,甚至包含那些没有任何订单的顾客。 13.4使用联结和联结条件 在总结讨论联结的这两课前，有必要汇总一下联结及其使用的要点。 * 注意所使用的联结类型。一般我们使用内联结，但使用外联结也有效。 * 关于确切的联结语法，应该査看具体的文档，看相应的DBMS支持何种语法（大多数DBMS使用这两课中描述的某种语法）。 * 保证使用正确的联结条件（不管釆用哪种语法），否则会返回不正确的数据。 * **应该总是提供联结条件**，否则会得出笛卡儿积。 * 在一个联结中可以包含多个表，甚至可以对每个联结采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前分别测试每个联结。这会使故障排除更为简单。 # 第14课 组合查询 14.1组合查询 SQL也允许执行多个查询（多条SELECT语句），并将结果作为一个查询结果集返回。这些组合査询通常称为并（union）或复合査询（compound query）。 14.2 创建组合查询 可用UNION操作符来组合数条SQL查询。 14.2.1 使用组合查询 给出每条SELECT语句，在语句之前放上关键字UNION。 举例：需要Illinois、Indiana和Michigan等美国几个州的所有顾客的报表，还想包括不管位于哪个州的所有的Fun4All。 ``` 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'; ``` 使用多条WHERE子句的相同查询 ``` SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_state IN ('IL','IN','MI') OR cust_name = 'Fun4All'; ``` <div class="panel panel-default collapse-panel box-shadow-wrap-lg"><div class="panel-heading panel-collapse" data-toggle="collapse" data-target="#collapse-0edd45480a42cc83fdf0a5003e2a5fec55" aria-expanded="true"><div class="accordion-toggle"><span style="">UNION的限制和性能问题</span> <i class="pull-right fontello icon-fw fontello-angle-right"></i> </div> </div> <div class="panel-body collapse-panel-body"> <div id="collapse-0edd45480a42cc83fdf0a5003e2a5fec55" class="collapse collapse-content"><p></p> **UNION的限制** 使用UNION组合SELECT语句的数目，SQL没有标准限制。但是，最 好是参考一下具体的DBMS文档，了解它是否对UNION能组合的最大 语句数目有限制。 **性能问题** 多数好的DBMS使用内部查询优化程序，在**处理各条SELECT语句前组合它们**。理论上讲，这意味着从性能上看使用多条WHERE子句条件 还是UNION应该没有实际的差别。不过我说的是理论上，实践中多数查询优化程序并不能达到理想状态，所以最好测试一下这两种方法， 看哪种工作得更好。 <p></p></div></div></div> 14.2.2 UNION 规则 * UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔（因此，如果组合四条SELECT语句,将要使用三个UNION关键字）。 * UNION中的每个查询必须包含**相同的列、表达式或聚集函数**（不过，各个列**不需要以相同的次序**列出）。 * **列数据类型必须兼容**：类型不必完全相同，但必须是DBMS可以隐含转换的类型（例如，不同的数值类型或不同的日期类型）。 14.2.3 包含或取消重复的行 UNION从查询结果集中自动去除了重复的行。 使用UNION ALL包含重复的行。（Indiana州的Fun4All单位） ``` 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'; ``` 14.2.4 对组合查询结果进行排序 在用UNION组合査询时，**只能使用一条ORDER BY子句，它必须位于最后一条SELECT语句之后**。不允许使用多条ORDER BY子句（不存在用一种方式排序一部分，而又用另一种方式排序另一部分的情况）。 # 第15课 插入数据 15.1 数据插入 INSERT用来将行插入（或添加）到数据库表。插入有几种方式： * 插入完整的行； * 插入行的一部分； * 插入某些查询的结果。 15.1.1 插入完整的行 简单方法，依赖表中列的定义次序。 ``` INSERT INTO Customers VALUES('1000000006', 'Toy Land', '123 Any Street', 'New York', 'NY', '11111', 'USA'， NULL, NULL); ``` 各列必须以它们在表定义中出现的次序填充，没有值则应该使用空值。 烦琐但更安全的方式 在表名后的括号里明确给出列名，在VALUES中以其指定的次序匹配指定的列名，不一定按照各列出现在表中实际次序。当表结构改变时依然能正常工作。 ``` 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); ``` > **不要使用没有明确给出列的INSERT语句**。给出列能使SQL代码继续发挥作用，即使表结构发生了变化。 15.1.2 插入部分行 明确给出表的列名的方法可以省略列。 在满足以下条件之一时可以省略： 1. 该列定义为允许NULL值。 2. 在表定义中给出默认值。这表示如果不给出值，将使用默认值。 ``` INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country) VALUES('1000000006', 'Toy Land', '123 Any Street', 'New York', 'NY', '11111', 'USA'); ``` 此例省略了cust_contact和cust_email。 15.1.2 插入部分行 INSERT SELECT可以将SELECT语句的结果插入表中。由一条SELECT语句和一条SELECT语句构成。 例：将CustNew表中的值合并到Customers表中。 ``` INSERT INTO Customers(cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country) SELECT cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country FROM CustNew; ``` 此例中两表结果应该相同，且主键值不能重复。此外，虽然两表列名相同，但实际DBMS使用的是例的顺序。 15.2 从一个表复制到另一个表 SELECT INTO可以将一个表的内容复制到一个全新的表（运行创建的表） * 任何SELECT选项和子句都可以使用，包括WHERE和GROUP BY; * 可利用联结从多个表插入数据； * 不管从多少个表中检索数据，数据都只能插入到一个表中。 > DB2不支持这里描述的SELECT INTO。 > INSERT SELECT 与 SELECT INTO之间的一个重要差别是前者导出数据，而后者导入数据。 ``` SELECT * INTO CustCopy FROM Customers; ``` 如果只想复制部分列，可以明确给出列名，而是不是使用通配符。 MariaDB、MySQL、Oracle, PostgreSQL 和 SQLite使用的语法稍有不同: ``` CREATE TABLE CustCopy AS SELECT * FROM Customers; ``` # 第16课 更新和删除数据 16.1 更新数据 使用UPDATE语句更新（修改）表中的数据。 使用时，提供以下三个信息： 1. 要更新的列表； 2. 列名和它们的新值； 3. 确定要更新哪些行的过滤条件。 **如果不提供WHERE语句，将会更新所有行!** 更新单个列 ``` UPDATE Customers SET cust_emial = 'kim@thetoystore.com' WHERE cust_id = '1000000005'; ``` 更新多个列只用一条SET命令，每个“例=值”对之间用逗号分隔。 ``` UPDATE Customers SET cust_contact = 'Sam Roberts', cust_email = 'sam@toyland.com' WHERE cust_id = '1000000006'; ``` > UPDATE语句允许使用子查询，能用SELECT语句检索出的数据更新列数据，具体请CSDN查，有些复杂。[SQL中update与update select结合语法详解与实例](https://blog.csdn.net/weixin_44671994/article/details/87720196) 删除某个列的值可以将其设置为NULL（需要允许NULL值）。 16.2 删除数据 使用DELECT语句从表中删除数据。 ** 如果不提供WHERE语句，将会删除所有行！** ``` DELETE FROM Customers WHERE cust_id = '1000000006'; ``` <div class="panel panel-default collapse-panel box-shadow-wrap-lg"><div class="panel-heading panel-collapse" data-toggle="collapse" data-target="#collapse-2438d44efda67e668278f3473ba0f18e63" aria-expanded="true"><div class="accordion-toggle"><span style="">友好的外键与FROM关键字</span> <i class="pull-right fontello icon-fw fontello-angle-right"></i> </div> </div> <div class="panel-body collapse-panel-body"> <div id="collapse-2438d44efda67e668278f3473ba0f18e63" class="collapse collapse-content"><p></p> * 友好的外键 第12课介绍了联结，简单联结两个表只需要这两个表中的常用字段。 也可以让DBMS通过使用外键来严格实施关系（这些定义在附录A中）。存在外键时，DBMS使用它们实施引用完整性。例如要向 Products表中插入一个新产品，DBMS不允许通过未知的供应商id 插入它，因为vend_id列是作为外键连接到Vendors表的。那么，这与DELETE有什么关系呢？使用外键确保引用完整性的一个好处是， DBMS通常可以防止删除某个关系需要用到的行。例如，要从 Products表中删除一个产品，而这个产品用在Orderitems的已有订单中，那么DELETE语句将抛出错误并中止。这是总要定义外键的另一个理由。 * FROM关键字 在某些SQL实现中，跟在DELETE后的关键字FROM是可选的。但是 即使不需要,也最好提供这个关键字。这样做将保证SQL代码在DBMS 之间可移植。 <p></p></div></div></div> 16.3 更新和删除的指导原则 再次强调：如果省略了WHERE子句，则UPDATE或DELETE将被应用到表中所有的行。 下面是许多SQL程序员使用UPDATE或DELETE时所遵循的重要原则。 * 除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的 UPDATE 或 DELETE 语句。 * **保证每个表都有主键**（如果忘记这个内容，请参阅第12课），尽可能像WHERE子句那样使用它（可以指定各主键、多个值或值的范围）。 * 在UPDATE或DELETE语句使用WHERE子句前，**应该先用SELECT进行测试**，保证它过滤的是正确的记录，以防编写的WHERE子句不正确。 * 使用强制实施引用完整性的数据库（关于这个内容，请参阅第12课）, 这样DBMS将不允许删除其数据与其他表相关联的行。 * 有的DBMS允许数据库管理员施加约束，防止执行不带WHERE子句的UPDATE或DELETE语句。如果所釆用的DBMS支持这个特性，应该使用它。 # 第17课 创建和操纵表 17.1 创建和操纵表 使用SQL的CREATE TABLE语句创建表，注意，使用交互式工具时，实际上也是使用（系统自动生成的）CREATE TABLE语句。 此处只介绍基本选项。 17.1.1 创建表基础 使用CREATE TABLE创建表时，需要给出以下信息： 1. 新表的名字，在关键字CREATE TABLE后给出； 2. 表列的名字和定义，用逗号分隔； 3. 有的DBMS还要求指定表的位置。 以下语句创建Products表： ``` 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 VARCUAR(1000) NULL ); ``` 注意，不同DBMS的语法可能会有不同，这条语句在Oracle、PostgreSQL、SQL Server和SQLite 中有效。而对于MySQL, varchar必须替换为text；对于DB2,必须从最后一列中去掉NULL。 创建新表时，指定的表名必须不存在。 17.1.2 使用NULL值 在不指定NOT NULL时，多数DBMS认为指定的是NULL，但DB2要求指定关键字NULL。 只有不允许NULL值的列可作为主键。 创建NULL和NOT NULL列混合的表。 ``` CREATE TABLE Vendors ( vend_id CHAR(IO) NOT NULL, vend_name CHAR(50) NOT NULL, vend_address CHAR(50) , vend_city CHAR(50) , vend_stat CHAR(5) , vend_zip CHAR(10) , vend_country CHAR(50) )； ``` 17.1.3 指定默认值 SQL 允许指定默认值，在插入行时如果不给出值，DBMS将自动采用默认值。默认值在CREATE TABLE语句的列定义中用关键字DEFAULT指定 ``` CREATE TABLE OrderItems ( order_num INTEGER NOT NULL, order_item INTEGER NOT NULL, prod_id CHAR(IO) NOT NULL, quantity INTEGER NOT NULL DEFAULT 1, item_price DECIMAL(8,2) NOT NULL ); ``` 默认值经常用于日期或时间戳列。 以下是获得系统日期命令在不同系统中的语法： |DBMS|函数/变量| |:-|:-| |Access|N0W()| |DB2|CURRENT_DATE| |MySQL|CURRENT_DATE()| |Oracl|SYSDATE| |PostgreSQL|CURRENT_DATE| |SQL Server|GETDATE()| |SQLite|date('now')| 17.2 更新表 使用ALTER TABLE更新表。虽然所有的DBMS都支持ALTER TABLE，但它们所允许更新的内容差别很大。 <div class="panel panel-default collapse-panel box-shadow-wrap-lg"><div class="panel-heading panel-collapse" data-toggle="collapse" data-target="#collapse-bbfbd7c997f377c3e04043194bc2d83096" aria-expanded="true"><div class="accordion-toggle"><span style="">使用ALTER TABLE 更新表之前，需要考虑许多事情。</span> <i class="pull-right fontello icon-fw fontello-angle-right"></i> </div> </div> <div class="panel-body collapse-panel-body"> <div id="collapse-bbfbd7c997f377c3e04043194bc2d83096" class="collapse collapse-content"><p></p> * 理想情况下，不要在表中包含数据时对其进行更新。应该在表的设 计过程中充分考虑未来可能的需求，避免今后对表的结构做大 改动。 * 所有的DBMS都允许给现有的表增加列，不过对所增加列的数据类型（以及NULL和DEFAULT的使用）有所限制。 * 许多DBMS不允许删除或更改表中的列。 * 多数DBMS允许重新命名表中的列。 * 许多DBMS限制对已经填有数据的列进行更改，对未填有数据的列几乎没有限制。 <p></p></div></div></div> 使用ALTER TABLE更改表结构，必须给出下面的信息： * 在ALTER TABLE之后给出要更改的表名（该表必须存在，否则将出错）； * 列出要做哪些更改。 增加列： ``` ALTER TABLE Vendors ADD vend_phone CHAR(20); ``` 删除列（并非对所有DBMS生效）： ``` ALTER TABLE Vendors DROP COLUMN vend_phone; ``` <div class="panel panel-default collapse-panel box-shadow-wrap-lg"><div class="panel-heading panel-collapse" data-toggle="collapse" data-target="#collapse-2fd7d9745574d2872225254612a8c46c72" aria-expanded="true"><div class="accordion-toggle"><span style="">复杂的表结构更改</span> <i class="pull-right fontello icon-fw fontello-angle-right"></i> </div> </div> <div class="panel-body collapse-panel-body"> <div id="collapse-2fd7d9745574d2872225254612a8c46c72" class="collapse collapse-content"><p></p> 复杂的表结构更改一般需要手动删除过程，它涉及以下步骤： 1. 用新的列布局创建一个新表； 2. 使用INSERT SELECT语句(关于这条语句的详细介绍，请参阅第15 课)从旧表复制数据到新表。有必要的话，可以使用转换函数和计算 字段； 3. 检验包含所需数据的新表； 4. 重命名旧表(如果确定，可以删除它)； 5. 用旧表原来的名字重命名新表； 6. 根据需要，重新创建触发器、存储过程、索引和外键。 ALTER TABLE 和 SQLite SQLite对使用ALTER TABLE执行的操作有所限制。最重要的一个限制是，它不支持使用ALTER TABLE定义主键和外键，这些必须在最初创建表时指定。 <p></p></div></div></div> **小心使用ALTER TABLE** 使用ALTER TABLE要极为小心，应该在进行改动前做**完整的备份（模式和数据的备份）**。数据库表的更改不能撤销，如果增加了不需要的列，**也许无法删除**它们。类似地，如果删除了不应该删除的列，可能会**丢失该列中的所有数据**。 17.3 删除表 使用DROP TABLE 删除表。 ``` DROP TABLE CustCopy; ``` **删除表没有确认，也无法撤销！** 17.4 重命名表 每个DBMS对表重命名的支持不同，所以略。 # 第18课 使用视图 18.1 视图 视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。 Mircrosft Access不支持视图，没有与SQL视图一致的工作方式。 <div class="panel panel-default collapse-panel box-shadow-wrap-lg"><div class="panel-heading panel-collapse" data-toggle="collapse" data-target="#collapse-37d86166d8f311bf1c40c0c5f236b85c45" aria-expanded="true"><div class="accordion-toggle"><span style="">18.1.1 为什么使用视图</span> <i class="pull-right fontello icon-fw fontello-angle-right"></i> </div> </div> <div class="panel-body collapse-panel-body"> <div id="collapse-37d86166d8f311bf1c40c0c5f236b85c45" class="collapse collapse-content"><p></p> 18.1.1 为什么使用视图 视图的一些常见应用。 * 重用SQL语句。 * 简化复杂的SQL操作。在编写査询后，可以方便地重用它而不必知道其基本查询细节。 * 使用表的一部分而不是整个表。 * 保护数据。可以授予用户访问表的特定部分的权限，而不是整个表的访问权限。 * 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。 **创建视图之后，可以用与表基本相同的方式使用它们**。可以对视图执行SELECT操作，过滤和排序数据，将视图联结到其他视图或表，甚至添加和更新数据（添加和更新数据存在某些限制，关于这个内容稍后做介绍）。 视图本身不包含数据，因此返回的数据是从其他表中检索出来的。在添加 或更改这些表中的数据时，视图将返回改变过的数据。 <p></p></div></div></div> <div class="panel panel-default collapse-panel box-shadow-wrap-lg"><div class="panel-heading panel-collapse" data-toggle="collapse" data-target="#collapse-727d26cc758f3625ede2cd2a1f70f91419" aria-expanded="true"><div class="accordion-toggle"><span style="">18.1.2 视图规则和限制</span> <i class="pull-right fontello icon-fw fontello-angle-right"></i> </div> </div> <div class="panel-body collapse-panel-body"> <div id="collapse-727d26cc758f3625ede2cd2a1f70f91419" class="collapse collapse-content"><p></p> 下面是关于视图创建和使用的一些最常见的规则和限制。 * 与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的 名字）。 * 对于可以创建的视图数目没有限制。 * 创建视图，必须具有足够的访问权限。这些权限通常由数据库管理人员授予。 * 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造视图。所允许的嵌套层数在不同的DBMS中有所不同（嵌套视图可能会严重降低査询的性能，因此在产品环境中使用之前，应该对其进行全面测试）。 * 许多DBMS禁止在视图査询中使用ORDER BY子句。 * 有些DBMS要求对返回的所有列进行命名，如果列是计算字段，则需 要使用别名（关于列别名的更多信息，请参阅第7课）。 * 视图不能索引，也不能有关联的触发器或默认值。 * 有些DBMS把视图作为只读的查询，这表示可以从视图检索数据，但不能将数据写回底层表。详情请参阅具体的DBMS文档。 * 有些DBMS允许创建这样的视图，它不能进行导致行不再属于视图的插入或更新。例如有一个视图，只检索带有电子邮件地址的顾客。如果更新某个顾客，删除他的电子邮件地址，将使该顾客不再属于视图。这是默认行为，而且是允许的，但有的DBMS可能会防止这种情况发生。 <p></p></div></div></div> 18.2 创建视图 使用CREATE VIEW创建视图，只能创建不存在的视图。 * 视图重命名 删除视图可以使用DROP VIEW 语句，其语法为DROP VIEW viewname；覆盖（或更新）视图，必须先删除它，然后再重新创建。 18.2.1 利用视图简化复杂的联结 一个最常见的视图应用是隐藏复杂的SQL，这通常涉及联结。 例：第12课的例子，查询订购了某种产品的顾客。 ``` SELECT cust_name, cust_contact FROM Customers, Orders, OrderItems WHERE Customers.cust_id = Orders.cust_id AND OrderItems.order_num = Orders.order_num AND prod.id = 'RGAN0l'; ``` 使用视图则可以改为： 1. 创建视图 ``` CREATE VIEW ProductCustomers AS SELECT cust_name, cust_contact, PROD_ID FROM Customers, Orders, OrderItems WHERE Customers.cust_id = Orders.cust_id AND OrderItems.order_num = Orders.order_num; ``` 2. 使用视图 ``` SELECT cust_name, cust_contact FROM ProductCustomers WHERE prod_id = 'RGAN01'; ``` > **创建可重用的视图** 创建不绑定特定数据的视图是的好办法。 18.2.2 用视图重新格式化检索出的数据。 第7课的例子： 使用加号的版本（使用`||`的版本就不复制了） ``` SELECT RTRIM(vend_name) + '(' + RTRIM(vend_country) + ')' FROM Vendors ORDER BY vend_name; ``` 假设经常需要这个格式的结果，可以创建一个视图。 ``` CREATE VIEW VendorLocations AS SELECT RTRIM(ven_name) + '(' + RTRIM(vend_country) + ')' AS vend_title FROM vendors; ``` 查询时： ``` SELECT * FROM VendorLocations; ``` > SELECT约束全部适用 视图只包含一个SELECT语句，这个语句的语法必须遵循具体DBMS的所有规则和约束 18.2.3 用视图过滤不想要的数据 视图对于应用普通的WHERE子句也很有用。 例如，可以定义CustomerEMailList视图，过滤没有电子邮件地址的顾客。 ``` CREATE VIEW CustomerEMailList AS SELECT cust_id, cust_name, cust_email FROM Customers WHERE cust_email IS NOT NULL; ``` 现在可以像使用其他表一样使用视图CustomerEMailList ``` SELECT * FROM CustomerEMailList; ``` > **WHERE子句与WHERE子句** 从视图检索数据时如果使用了一条WHERE子句，则两组子句（一组在 视图中，另一组是传递给视图的）将自动组合。 18.2.4 使用视图与计算字段 视图也可以用来简化计算字段。 第7课的例子 ``` SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM OrderItems WHERE order_num = 20008 ``` ``` CREATE VIEW OrderItemsExpandedAS SELECT order_num, prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM OrderItem; ``` 检索订单20008的详细内容 ``` SELECT * FROM OrderItemsExpanded WHERE order_num = 20008; ``` Last modification：May 15, 2024 © Allow specification reprint Like 如果觉得我的文章对你有用，请留下评论。