《SQL必知必会(第4版)》读书笔记【全22课】

数据库 专栏收录该内容
4 篇文章 0 订阅

内容提要&相关资源

  • 本书由浅入深地讲解了 SQL的基本概念和语法,涉及数据的排序过滤分组,以及视图联结子查询游标存储过程触发器等内容,实例丰富,便于查阅。
  • 新版增加了针对 Apache Open Office BaseMariaDBSQLite 等 DBMS 的描述,并根据最新版本的 OracleSQL ServerMySQLPostgreSQL 更新了相关示例。
  • 本书适合SQL初学者
  • 英文原版勘误表: Errata: Sams Teach Yourself SQL in 10 Minutes (Fourth Edition).
  • 英文原版第5版:Sams Teach Yourself SQL in 10 Minutes (Fifth Edition)

正文

第1课 了解SQL

数据库基础

  • 数据库(database):保存有组织的数据的容器(通常是一个文件或一组文件)。
  • 数据库软件:数据库管理系统(DBMS/Database Management System)。
    数据库是通过DBMS创建和操纵的容器。

  • 表(table):某种特定类型的结构化清单。
    数据库名和表名的组合是唯一的,即在相同数据库中表名唯一不可重复,但在不同数据库中可使用相同表名。
  • 模式(schema):关于数据库和表的布局及特性的信息。
    模式可用来描述数据库中特定的表,也可以用来描述整个数据库(和其中表的关系)。

  • 列(column):表中的一个字段。所有表都是由一个或多个列组成的。
  • 数据类型(datatype):所允许的数据的类型。每个表列都有相应的数据类型,它限制(或允许)该列中存储的数据。
    不同的DBMS中,相同的数据类型可能有不同的名称。
  • 行(row):表中的一个记录。

  • 主键(primary key):一列(或一组列),其值能够唯一标识表中的每一行。
    应该总是定义主键。
  • 主键需要满足的条件
  1. 任意两行都不具有相同的主键值(Not Null)
  2. 每一行都必须具有一个主键值
  3. 主键列中的值不允许修改或更新
  4. 主键值不能重用(如果删去某行,不可将它的主键赋给以后的新行)
    主键可以是多个列的组合,多列作为主键时,它们及它们的组合必须满足以上条件,它们的组合必须是唯一的,但单个列的值可以不唯一。
  • 外键

什么是SQL

SQL(sequel)是Structured Query Language(结构化查询语言)的缩写,是一种专门用来与数据库沟通的语言。




第2课 检索数据

检索单个列

SELECT prod_name
FROM Products;
  • 默认不排序
  • 以分号分隔多条SQL语句
  • SQL语句不区分大小写,但一般关键字大写,列名表名小写
  • 是否换行无影响,但分成多行更利于阅读调试

检索多个列

SELECT prod_id, prod_name, prod_price
FROM Products;
  • 选择多个列时,记得在列名间加上逗号,但最后一个列名后不加

检索所有列

SELECT *
FROM Products;
  • 通配符(*)

检索不同的值 DISTINCT

SELECT DISTINCT vend_id 
FROM Products;
  • DISTINCT作用于所有的列,不仅仅是跟在其后的那一列.

限制结果

  • SQL Server、 Access - TOP
SELECT TOP 5 prod_name 
FROM Products; 
  • DB2
SELECT prod_name 
FROM Products 
FETCH FIRST 5 ROWS ONLY; 
  • Oracle
SELECT prod_name 
FROM Products 
WHERE ROWNUM <=5; 
  • MySQL、MariaDB、PostgreSQL、SQLite - LIMIT
SELECT prod_name 
FROM Products 
LIMIT 6 OFFSET 5; 
-- 返回从第6行起的5行数据,即第7、8、9、10、11行
-- 在MySQL和MariaDB中,可省略OFFSET,即 LIMIT 5,6,逗号前的值对应OFFSET,逗号后的值对应LIMIT

/* 给代码加注释的方法 
SELECT prod_name, vend_id
FROM Products; */



第3课 排序检索数据 ORDER BY

  • 子句(clause): SQL语句由子句构成,有些子句是必需的,有些则是可选的。一个子 句通常由一个关键字加上所提供的数据组成。
  1. ORDER BY 必须是SELECT语句中的最后一条子句;
  2. 可用非检索的列排序数据;
  3. 可按照多个列排序,排序顺序按规定进行
  4. 可按照列位置排序:
SELECT prod_id, prod_price, prod_name 
FROM Products 
ORDER BY 2, 3;
-- 按照列的相对位置对应列排序,即按照SELECT中的第二列prod_price、第三列prod_name排序
-- 按照这种方法将无法按照不在SELECT清单中的列排序
-- 两种排序方法(实际列名、相对列位置)可以混合使用
  1. 默认升序ASC,可使用DESC关键字进行降序。DESC关键字之应用到直接位于其前面的列名,如果想在多个列上进行降序排序,必须对每一列制定DESC关键字;



第4课 过滤数据

搜索条件(search criteria)/ 过滤条件(filter condition)
WHERE
WHERE子句操作符

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

SELECT prod_name 
FROM Products 
WHERE prod_price IS NULL; 



第5课 高级数据过滤

  • 操作符(operator): 用来联结或改变 WHERE 子句中的子句的关键字,也称为逻辑操作符 (logical operator)。
操作符定义
AND用在 WHERE 子句中的关键字,用来指示检索满足所有给定条件的行
ORWHERE 子句中使用的关键字,用来表示检索匹配任一给定条件的行
注意SQL默认优先处理AND,后处理OR;所以最好用括号明确分组操作符以避免错误。
INWHERE 子句中用来指定要匹配值的清单的关键字,功能与 OR 相当
NOTWHERE 子句中用来否定其后条件的关键字
说明MariaDB 中的 NOT MariaDB 支持使用 NOT 否定 IN、BETWEEN 和 EXISTS 子句。大多数 DBMS允许使用 NOT 否定任何条件。
SELECT prod_name, prod_price 
FROM Products 
WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01')
	   AND prod_price >= 10; 

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

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



第6课 用通配符进行过滤

  • 通配符(wildcard):用来匹配值的一部分的特殊字符。
    通配符本身实际上是 SQL的 WHERE 子句中有特殊含义的字符。
    通配符搜索只能用于文本字段(字符串),非文本数据类型字段不能使用 通配符搜索。
  • 搜索模式(search pattern):由字面值、通配符或两者组合构成的搜索条件。
    LIKE操作符
通配符含义注意
百分号(%)表示任何字符出现任意次数% 代表搜索模式中给定位置的0个、1个或多个字符; %不会匹配为 NULL 的行
下划线(_)只匹配单个字符_总是刚好匹配一个字符,不能多也不能少
方括号([])用来指定一个字符集,它必须匹配指定位置(通配符的位置)的一个字符此通配符可以用前缀字符^(脱字号)来否定;只有微软的 Access 和 SQL Server 支持集合
SELECT prod_id, prod_name  
FROM Products  
WHERE prod_name LIKE 'Fish%';
-- 检索任意以Fish起头的词,%告诉 DBMS接受 Fish 之后的任意字符,不管它有多少字符。
-- 说明:Access 通配符如果使用的是 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 '__ inch teddy bear'; -- 两个下划线
-- 这个 WHERE 子句中的搜索模式给出了后面跟有文本的两个通配符。
-- 结果只显示匹配搜索模式的行,如“12 inch teddy bear ”、“18 inch teddy bear ”,因为搜索模式要求匹配两个通配符。
-- 说明:DB2 通配符 DB2不支持通配符_。 
-- 说明:Access 通配符 如果使用的是 Microsoft Access,需要使用?而不是_。 

FROM Customers 
WHERE cust_contact LIKE '[JM]%' 
ORDER BY cust_contact; 
-- 此语句的WHERE子句中的模式为'[JM]%'。这一搜索模式使用了两个不同的通配符。
-- [JM]匹配方括号中任意一个字符,它也只能匹配单个字符。因此,任何多于一个字符的名字都不匹配。
-- [JM]之后的%通配符匹配第 一个字符之后的任意数目的字符,返回所需结果。 
  • 不要过度使用通配符。如果其他操作符能达到相同的目的,应该使用其他操作符。
  • 在确实需要使用通配符时,也尽量不要把它们用在搜索模式的开始处。把通配符置于开始处,搜索起来是最慢的。
  • 仔细注意通配符的位置。如果放错地方,可能不会返回想要的数据。



第7课 创建计算字段

  • 字段(field):基本上与列(column)的意思相同,经常互换使用,不过数据库列一般称为列,而术语字段通常与计算字段一起使用。
    计算字段并不实际存在于数据库表中。计算字段是运行时在 SELECT 语句内创建的。
  • 拼接(concatenate):将值联结到一起(将一个值附加到另一个值)构成单个值。
DBMS拼接
Access、SQL Server+
DB2、Oracle、PostgreSQL、SQLite、Open Office BaseII
SELECT vend_name + ' (' + vend_country + ')' 
FROM Vendors 
ORDER BY vend_name; 

SELECT vend_name || ' (' || vend_country || ')' 
FROM Vendors 
ORDER BY vend_name; 

SELECT Concat(vend_name, ' (', vend_country, ')') --MySQL、MariaDB
FROM Vendors 
ORDER BY vend_name; 

去除值右边的所有空格 RTRIM()
许多数据库(不是所有)保存填充为列宽的文本值,而实际上你要的结果不需要这些空格。为正确返回格式化的数据,必须去掉这些空格。这可以使用 SQL的 RTRIM()函数来完成,如下所示:

SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')' -- 去掉vend_name和vend_country中的多余空格
FROM Vendors 
ORDER BY vend_name; 
  • TRIM 函数
函数用法
TRIM()去掉字符串左右两边的空格
RTRIM()去掉字符串右边的空格
LTRIM()去掉字符串左边的空格
  • 别名(alias)/ 导出列(derived column) AS



第8课 使用函数处理数据

DBMS函数的差异
文本处理函数
常用的文本处理函数
SOUNDEX 是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。用以检索所有发音类似于检索内容的行。
Microsoft Access和 PostgreSQL不支持 SOUNDEX()。

SELECT cust_name, cust_contact 
FROM Customers 
WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');  

输出:

cust_namecust_contact
Kids PlaceMichelle Green

日期和时间处理函数
每种DBMS都有自己的特殊形式,日期和时间函数在不同的DBMS中也差别很大,可移植性很差。以检索2012年的所有订单为例:

DBMS函数
SQL ServerWHERE DATEPART(yy, order_date) = 2012
AccessWHERE DATEPART(‘yyyy’, order_date) = 2012
PostgreSQLWHERE DATE_PART(‘year’, order_date) = 2012
OracleWHERE to_number(to_char(order_date, ‘YYYY’)) = 2012;
WHERE order_date BETWEEN to_date(‘01-01-2012’) AND to_date(‘12-31-2012’);
MySQL
MariaDB
WHERE YEAR(order_date) = 2012
SQLiteWHERE strftime(’%Y’, order_date) = ‘2012’;

数值处理函数

常用数值处理函数




第9课 汇总数据

  • 聚集函数(aggregate function):对某些行运行的函数,计算并返回一个值。
函数说明注意
AVG()返回某列平均值1. 只能用来确定特定数值列的平均值;
2. 忽略列值为NULL的行
COUNT()返回某列行数1.COUNT(*)对表中行的数目进行计数,不管表列中是否包含NULL
2. COUNT(column)对特定列中具有值的行计数,忽略NULL
MAX()返回某列最大值1. 可对非数值数据使用:在用于文本数据时,MAX()返回按该列排序后的最后一行;
2. 忽略列值为 NULL 的行
MIN()返回某列最小值同上
SUM()返回某列值之1. 可合计计算值,如SUM(item_price*quantity);
2. 忽略列值为 NULL 的行
  • 对所有行执行计算,指定 ALL 参数或不指定参数(因为 ALL 是默认行为)
  • 只包含不同的值,指定 DISTINCT 参数
  • Microsoft Access在聚集函数中不支持 DISTINCT



第10课 分组数据 GROUP BY & HAVING

规定:

  1. GROUP BY 子句可以包含任意数目的列,因而可以对分组进行嵌套,更细致地进行数据分组。
  2. 如果在 GROUP BY 子句中嵌套了分组,数据将在后指定的分组上进行汇总。换句话说,在建立分组时,指定的所有列都一起计算(所以不能从个别的列取回数据)。
  3. GROUP BY 子句中列出的每一列都必须是检索列或有效的表达式(但不能是聚集函数)。如果在 SELECT 中使用表达式,则必须在 GROUP BY 子句中指定相同的表达式。不能使用别名。
  4. 大多数 SQL实现不允许 GROUP BY 列带有长度可变的数据类型(如文本或备注型字段)。
  5. 除聚集计算语句外,SELECT 语句中的每一列都必须在 GROUP BY 子句中给出。
  6. 如果分组列中包含具有 NULL 值的行,则 NULL 将作为一个分组返回。 如果列中有多行 NULL 值,它们将分为一组。
  7. GROUP BY 子句必须出现在 WHERE 子句之后,ORDER BY 子句之前。

HAVING 和 WHERE 的差别
WHERE 在数据分组前进行过滤,HAVING 在数 据分组后进行过滤。这是一个重要的区别,WHERE 排除的行不包括在分组中。这可能会改变计算值,从而影响 HAVING 子句中基于这些值过滤掉的分组。
ORDER BY 与 GROUP BY
SELECT子句及其顺序




第11课 使用子查询

  • 查询(query):任何 SQL语句都是查询。但此术语一般指 SELECT 语句。
  • 子查询(subquery)
    在 SELECT 语句中,子查询总是从内向外处理。
    作为子查询的 SELECT 语句只能查询单个列。企图检索多个列将返回错误。
    完全限定列名:指定表名和列名,如Orders.cust_id



第12课 联结表 JOIN

关系表的设计就是要把信息分解成多个表,一类数据一个表。各 表通过某些共同的值互相关联(所以才叫关系数据库)。

可伸缩(scale):能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称为可伸缩性好(scale well)。
关系数据可以有效地存储,方便地处理。因此,关系数据库的可伸缩性远比非关系数据库要好。

联结是一种机制,用来在一条 SELECT 语句中关联表,因此称为联结。

联结不是物理实体,它在实际的数据库表 中并不存在。DBMS会根据需要建立联结,它在查询执行期间一直存在。

内联结(等值连接)

  • 笛卡儿积(cartesian product):由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数
    有时,返回笛卡儿积的联结,也称叉联结(cross join)

  • INNER JOIN

SELECT vend_name, prod_name, prod_price 
FROM Vendors 
INNER JOIN Products  
ON Vendors.vend_id = Products.vend_id; 




第13课 创建高级联结

表别名:

  1. 缩短SQL语句;
  2. 允许在一条SELECT语句中多次使用相同的表。
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,可直接省略AS,如"Customers C"。
联结类型内容
自联结(self-join)自联结通常作为外部语句,用来替代从相同表中检索数据的使用子查 询语句。
自然联结(natural join)自然联结排除多次出现,使每一列只能返回一次。
一般通过对一个表使用通配符(SELECT *),而对其他表的列使用明确的子集来完成。
外联结(outer join)包含了那些在相关表中没有关联行的行。
在使用 OUTER JOIN 语法时,必须使用 RIGHTLEFT 关键字指定包括其所有行的表 (RIGHT 指出的是 OUTER JOIN 右边的表,而 LEFT 指出的是 OUTER JOIN 左边的表)。还存在另一种外联结,就是全外联结(full outer join),它检索两个表中的所有行并关联那些可以关联的行。
  • Access、MariaDB、MySQL、Open Office Base和 SQLite不支持 FULL OUTER JOIN 语法。

使用联结和联结条件

  1. 注意所使用的联结类型。一般我们使用内联结,但使用外联结也有效。
  2. 关于确切的联结语法,应该查看具体的文档,看相应的DBMS支持何 种语法(大多数 DBMS使用这两课中描述的某种语法)。
  3. 保证使用正确的联结条件(不管采用哪种语法),否则会返回不正确的数据。
  4. 应该总是提供联结条件,否则会得出笛卡儿积。
  5. 在一个联结中可以包含多个表,甚至可以对每个联结采用不同的联结类型。虽然这样做是合法的,一般也很有用,但应该在一起测试它们前分别测试每个联结。这会使故障排除更为简单。



第14课 组合查询 UNION

  • 并(union)/ 复合查询(compound query):执行多个查询(多条SELECT语句),并将结果作为一个查询结果集返回。

使用场景:

  1. 在一个查询中从不同的表返回结构数据;
  2. 对一个表执行多个查询,按一个查询返回数据。 (多数情况下相同于多个WHERE子句)
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'; 

-- 等同于:

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

UNION规则:

  1. UNION 必须由两条或两条以上的 SELECT 语句组成,语句之间用关键字UNION分隔(因此,如果组合四条SELECT语句,将要使用三个UNION 关键字)。
  2. UNION 中的每个查询必须包含相同表达式聚集函数(不过, 各个列不需要以相同的次序列出)。
  3. 列数据类型必须兼容:类型不必完全相同,但必须是 DBMS可以隐含 转换的类型(例如,不同的数值类型或不同的日期类型)。
  4. UNION从查询结果集中自动去除了重复的行。
  5. 如果想返回所有的匹配行(不去重),可使用UNION ALL代替UNION。
  6. 在用 UNION 组合查询时,只能使用一条 ORDER BY 子句,它必须位于最后一条 SELECT 语句之后。
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' 
ORDER BY cust_name, cust_contact;



第15课 插入数据

数据插入 INSERT

1. 插入完整的行

  • 法一:基础INSERT语句
    给每一列提供一个值,如果某列没有值应该使用NULL值;各列必须以它们在表定义中出现的次序填充。
INSERT INTO Customers 
VALUES('1000000006',        
		'Toy Land',        
		'123 Any Street',        
		'New York',        
		'NY',        
		'11111',        
		'USA',        
		NULL,        
		NULL); 
  • 法二:
    明确列名,按照指定次序匹配指定列名不易出错。
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); 

2. 插入部分行
使用上述法二,可以省略列,即只给某些列提供值,给其他列不提供值。
省略的列需满足一下任一条件:

  1. 该列定义为允许NULL值(无值或空值);
  2. 在表定义中给出默认值。

3. 插入某些查询的结果 (一条INSERT插入多行)
INSERT + SELECT

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; 

INSERT SELECT 中的列名并不重要(不需要匹配列名),DBMS是根据列的位置来填充。

从一个表复制到另一个表 SELECT INTO

SELECT INTO 将数据复制到一个新表(有的 DBMS可以覆盖已经存在的表,这依赖于 所使用的具体 DBMS)。

SELECT * 
INTO CustCopy 
FROM Customers;
-- 这条 SELECT 语句创建一个名为 CustCopy 的新表,并把 Customers 表 的整个内容复制到新表中

CREATE TABLE CustCopy AS 
SELECT * FROM Customers;
-- MariaDB、MySQL、Oracle、PostgreSQL和 SQLite



第16课 更新和删除数据

更新数据 UPDATE

  1. 更新表中的特定行
    基本的UPDATE语句由三部分组成:
    1. 要更新的表
    2. 列名和它们的新值
    3. 确定要更新那些行的过滤条件
UPDATE Customers 
SET cust_email = 'kim@thetoystore.com' 
WHERE cust_id = '1000000005'; -- 更新单列

UPDATE Customers 
SET cust_contact = 'Sam Roberts',     
	cust_email = 'sam@toyland.com' 
WHERE cust_id = '1000000006'; -- 更新多列

UPDATE Customers 
SET cust_email = NULL 
WHERE cust_id = '1000000005'; -- 删除某列的值
  1. 更新表中的所有行
    不加WHERE过滤条件。

删除数据 DELETE

  1. 从表中删除特定的行
DELETE FROM Customers 
WHERE cust_id = '1000000006'; 
  1. 从表中删除所有行
    TRUNCATE TABLE table_name;
    DELETE FROM table_name;
    DELETE * FROM table_name;

使用 UPDATE 或 DELETE 时所遵循的重要原则:

  1. 除非确实打算更新和删除每一行,否则绝对不要使用不带 WHERE 子句 的 UPDATE 或 DELETE 语句。
  2. 保证每个表都有主键,尽可能像 WHERE 子句那样使用它(可以指定各主键、多个值或值的范围)。
  3. 在 UPDATE 或 DELETE 语句使用 WHERE 子句前,应该先用 SELECT 进行测试,保证它过滤的是正确的记录,以防编写的 WHERE 子句不正确。
  4. 使用强制实施引用完整性的数据库, 这样 DBMS将不允许删除其数据与其他表相关联的行。
  5. 有的 DBMS 允许数据库管理员施加约束,防止执行不带 WHERE 子句的 UPDATE 或 DELETE 语句。如果所采用的 DBMS支持这个特性,应该使用它。



第17课 创建和操纵表

创建表 CREATE TABLE

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 
); 
-- NULL为默认设置,可省略

CREATE TABLE OrderItems 
(     
	order_num      INTEGER          NOT NULL,     
	order_item     INTEGER          NOT NULL,     
	prod_id        CHAR(10)         NOT NULL,     
	quantity       INTEGER          NOT NULL      DEFAULT 1,  -- 在插入行时如果不给出值,DBMS将自动采用默认值   
	item_price     DECIMAL(8,2)     NOT NULL 
); 

默认值常用于日期或时间戳列。
在这里插入图片描述

更新表 ALTER TABLE

使用 ALTER TABLE 更改表结构,必须给出下面的信息:

  1. 在 ALTER TABLE 之后给出要更改的表名(该表必须存在,否则将 出错);
  2. 列出要做哪些更改(ADD/DROP)。
ALTER TABLE Vendors 
ADD vend_phone CHAR(20); -- 增加列

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

复杂的表结构更改一般需要手动删除过程,它涉及以下步骤:

  1. 用新的列布局创建一个新表;
  2. 使用 INSERT SELECT 语句从旧表复制数据到新表。有必要的话,可以使用转换函数和计算 字段;
  3. 检验包含所需数据的新表;
  4. 重命名旧表(如果确定,可以删除它);
  5. 用旧表原来的名字重命名新表; (6) 根据需要,重新创建触发器、存储过程、索引和外键。

删除表 DROP TABLE

DROP TABLE CustCopy; 

重命名表

DBMS语句
DB2、MariaDB、MySQL、Oracle、PostgreSQLRENAME
SQL Serversp_rename
SQLiteALTER TABLE



第18课 使用视图

  • 视图(view):通过相关的名称存储在数据库中的一个“假表”。作为视图,它不包 含任何列或数据,包含的是一个查询。

视图的规则和限制:

  1. 与表一样,视图必须唯一命名
  2. 对于可以创建的视图数目没有限制。
  3. 创建视图,必须具有足够的访问权限。这些权限通常由数据库管理人员授予。
  4. 视图可以嵌套,即可以利用从其他视图中检索数据的查询来构造视图。所允许的嵌套层数在不同的 DBMS中有所不同(嵌套视图可能会严重降低查询的性能,因此在产品环境中使用之前,应该对其进行全 面测试)。
  5. 许多 DBMS禁止在视图查询中使用 ORDER BY 子句。
  6. 有些 DBMS要求对返回的所有列进行命名,如果列是计算字段,则需要使用别名。
  7. 视图不能索引,也不能有关联的触发器或默认值。
  8. 有些 DBMS把视图作为只读的查询,这表示可以从视图检索数据,但 不能将数据写回底层表。详情请参阅具体的 DBMS文档。
  9. 有些 DBMS 允许创建这样的视图,它不能进行导致行不再属于视图的插入或更新。例如有一个视图,只检索带有电子邮件地址的顾客。如果更新某个顾客,删除他的电子邮件地址,将使该顾客不再属于视图。这 是默认行为,而且是允许的,但有的 DBMS可能会防止这种情况发生。

创建视图 CREATE VIEW view_name

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; -- 创建一个名为ProductCustomers的视图

SELECT cust_name, cust_contact 
FROM ProductCustomers 
WHERE prod_id = 'RGAN01'; -- 使用视图进行查询

删除视图 DROP VIEW view_name
视图重命名:先删除,再重新创建




第19课 使用存储过程

  • 存储过程:为以后使用而保存的一条或多条SQL语句。可将其视为批文件,虽然它们的作用不仅限于批处理。

存储过程的好处:简单、安全、高性能。

这里的例子只适用于Oracle和SQL Server

  • 存储过程的执行 EXECUTE
EXECUTE AddNewProduct( 'JTS01',                         
					   'Stuffed Eiffel Tower',                         
					   6.49,                        
					   'Plush stuffed toy with the text La ➥Tour Eiffel in red white and blue' ); 
-- 这里执行一个名为 AddNewProduct 的存储过程,将一个新产品添加到 Products 表中。
-- AddNewProduct 有四个参数,分别是:供应商 ID (Vendors 表的主键)、产品名、价格和描述。
-- 这4个参数匹配存储过程中4个预期变量(定义为存储过程自身的组成部分)。此存储过程将新行 添加到 Products 表,并将传入的属性赋给相应的列。 

对于具体的 DBMS,可能包括以下的执行选择:

  1. 参数可选,具有不提供参数时的默认值;
  2. 不按次序给出参数,以“参数=值”的方式给出参数值。
  3. 输出参数,允许存储过程在正执行的应用程序中更新所用的参数。
  4. 用 SELECT 语句检索数据。
  5. 返回代码,允许存储过程返回一个值到正在执行的应用程序。

  • 创建存储过程

Oracle

CREATE PROCEDURE MailingListCount (
	ListCount OUT INTEGER -- 参数 ListCount 从存储过程返回一 个值而不是传递一个值给存储过程(out)
) 
IS 
v_rows INTEGER; 
BEGIN     
	SELECT COUNT(*) INTO v_rows     
	FROM Customers     
	WHERE NOT cust_email IS NULL;     
	ListCount := v_rows; 
END; 
-- 存储过程的代码括在 BEGIN 和 END 语句中,这里执行一条简单的 SELECT 语句,它检索具有邮件地址的顾客。
-- 然后用检索出的行数设置 ListCount(要传递的输出参数)

var ReturnValue NUMBER 
EXEC MailingListCount(:ReturnValue); 
SELECT ReturnValue; 
-- 这段代码声明了一个变量来保存存储过程返回的任何值,然后执行存储过程,再使用 SELECT 语句显示返回的值。 
关键字行为
OUT从存储过程返回一值
IN传递值给存储过程
INOUT既传递值给存储过程也从存储过程传回值

SQL Server

CREATE PROCEDURE MailingListCount 
AS 
DECLARE @cnt INTEGER 
SELECT @cnt = COUNT(*) 
FROM Customers 
WHERE NOT cust_email IS NULL; 
RETURN @cnt; 
-- 此存储过程没有参数。调用程序检索 SQL Server的返回代码提供的值。 
-- 其中用 DECLARE 语句声明了一个名为@cnt 的局部变量(SQL Server中所 有局部变量名都以@起头);
-- 然后在 SELECT 语句中使用这个变量,让它包含 COUNT()函数返回的值;
-- 最后,用 RETURN @cnt 语句将计数返回给调用程序。 

DECLARE @ReturnValue INT 
EXECUTE @ReturnValue=MailingListCount; 
SELECT @ReturnValue; 
-- 这段代码声明了一个变量来保存存储过程返回的任何值,然后执行存储过程,再使用 SELECT 语句显示返回的值。 
CREATE PROCEDURE NewOrder @cust_id CHAR(10) 
AS
-- Declare variable for order number 
DECLARE @order_num INTEGER 
-- Get current highest order number 
SELECT @order_num=MAX(order_num) 
FROM Orders 
-- Determine next order number 
SELECT @order_num=@order_num+1 
-- Insert new order 
INSERT INTO Orders(order_num, order_date, cust_id) 
VALUES(@order_num, GETDATE(), @cust_id) 
-- Return order number 
RETURN @order_num; 

-- 此存储过程在 Orders 表中创建一个新订单。
-- 它只有一个参数,即下订单顾客的 ID。订单号和订单日期这两列在存储过程中自动生成。
-- 代码首先声明一个局部变量来存储订单号。
-- 接着,检索当前最大订单号(使用 MAX()函数)并增加 1(使用 SELECT 语句)。
-- 然后用 INSERT 语句插入由 新生成的订单号、当前系统日期(用 GETDATE()函数检索)和传递的顾客 ID组成的订单。
-- 最后,用 RETURN @order_num 返回订单号(处理订单物品需要它)。

CREATE PROCEDURE NewOrder @cust_id CHAR(10) 
AS 
-- Insert new order 
INSERT INTO Orders(cust_id) 
VALUES(@cust_id) 
-- Return order number 
SELECT order_num = @@IDENTITY;

-- 此存储过程也在 Orders 表中创建一个新订单。
-- 这次由DBMS生成订单号。 
-- 大多数DBMS都支持这种功能;SQL Server中称这些自动增量的列为标识字段(identity field),而其他DBMS称之为自动编号(auto number)或序列 (sequence)。
-- 传递给此过程的参数也是一个,即下订单的顾客 ID。
-- 订单号和订单日期没有给出,DBMS对日期使用默认值(GETDATE()函数),订单号自动生成。
-- 在SQL Server上可在全局变量@@IDENTITY 中得到自动生成的ID,它返回到调用程序(这里使用 SELECT 语句)。 




第20课 管理事务处理(transaction processing)

事务处理是一种机制,用来管理必须成批执行的SQL操作,保证数据库不包含不完整的操作结果。即,要么完全执行,要么完全不执行。如果没有错误发生,整组语句提交给(写到)数据库表;如果发生错误,则进行回退(撤销),将数据库恢复到某个已知且安全的状态。

  • 事务(transaction)指一组 SQL语句;

  • 回退(rollback)指撤销指定 SQL语句的过程;

  • 提交(commit)指将未存储的 SQL语句结果写入数据库表;

  • 保留点(savepoint)指事务处理中设置的临时占位符(placeholder), 可以对它发布回退(与回退整个事务处理不同)。

  • SQL Server

BEGIN TRANSACTION 
... 
COMMIT TRANSACTION 
  • MariaDB、MySQL
START TRANSACTION 
... 
  • Oracle
SET TRANSACTION ... 
  • PostgreSQL
BEGIN 
... 

撤销 ROLLBACK

DELETE FROM Orders; 
ROLLBACK; 
-- 执行 DELETE 操作,然后用 ROLLBACK 语句撤销

保存更改 COMMIT
隐式提交(implicit commit):提交(写或保存)操作时自动进行的。
进行明确的提交,需使用COMMIT语句。

-- SQL Server
BEGIN TRANSACTION 
DELETE OrderItems WHERE order_num = 12345 
DELETE Orders WHERE order_num = 12345 
COMMIT TRANSACTION 

-- Oracle
SET TRANSACTION 
DELETE OrderItems WHERE order_num = 12345; 
DELETE Orders WHERE order_num = 12345; 
COMMIT;  

占位符
要支持回退部分事务,必须在事务处理块中的合适位置放置占位符。这样,如果需要回退,可以回退到某个占位符。

在 MariaDB、MySQL和 Oracle中 创建占位符,可使用 SAVEPOINT 语句。

SAVEPOINT delete1; 

SQL Server:

SAVE TRANSACTION delete1; 



第21课 使用游标(cursor)

  • 结果集(result set):SQL查询所检索出的结果。
  • 游标(cursor):一个存储在DBMS服务器上的数据库查询,它时被SELECT语句检索出来的结果集。

游标主要用于交互式应用,其中用户需要滚动屏幕上的数据,并对数据进行浏览或做出更改。

使用游标的步骤:

  1. 在使用游标前,必须声明(定义)它。这个过程实际上没有检索数据, 它只是定义要使用的 SELECT 语句和游标选项。
  2. 一旦声明,就必须打开游标以供使用。这个过程用前面定义的 SELECT 语句把数据实际检索出来。
  3. 对于填有数据的游标,根据需要取出(检索)各行。
  4. 在结束游标使用时,必须关闭游标,可能的话,释放游标(有赖于具体的 DBMS)。

创建游标

  • DB2、MariaDB、MySQL和 SQL Server:
DECLARE CustCursor CURSOR 
FOR 
SELECT * FROM Customers 
WHERE cust_email IS NULL
  • Oracle、PostgreSQL:
DECLARE CURSOR CustCursor 
IS 
SELECT * FROM Customers 
WHERE cust_email IS NULL

打开游标

OPEN CURSOR CustCursor 

使用游标

FETCH 语句指出要检索哪些行,从何处检索它们以及将它们放于何处(如变量名)。

-- Oracle
-- 这个例子使用 FETCH 检索当前行,放到一个名为 CustRecord 的变量中。
-- 这里的 FETCH 位于 LOOP 内,因此它反复执行。
-- 代码 EXIT WHEN CustCursor%NOTFOUND 使在取不出更多的行时终止处理(退出循环)。
-- 这个例子也没有做实际的处理,实际例子中可用具体的处理代码替换占位符

DECLARE TYPE CustCursor IS REF CURSOR     
	RETURN Customers%ROWTYPE; 
DECLARE CustRecord Customers%ROWTYPE 
BEGIN     
	OPEN CustCursor;     
	LOOP     
	FETCH CustCursor INTO CustRecord;     
	EXIT WHEN CustCursor%NOTFOUND;     ...     
	END LOOP;     
	CLOSE CustCursor; 
END; 
-- SQL Server
-- 在此例中,为每个检索出的列声明一个变量,FETCH 语句检索一行并保存值到这些变量中。
-- 使用 WHILE 循环处理每一行,条件 WHILE @@FETCH_STATUS = 0 在取不出更多的行时终止处理(退出循环)。
-- 这个例子也不进行具体的处理,实际代码中,应该用具体的处理代码替换其中的...占位符。 

DECLARE @cust_id CHAR(10),         
		@cust_name CHAR(50),         
		@cust_address CHAR(50),         
		@cust_city CHAR(50),         
		@cust_state CHAR(5),         
		@cust_zip CHAR(10),         
		@cust_country CHAR(50),         
		@cust_contact CHAR(50),         
		@cust_email CHAR(255) 
OPEN CustCursor 
FETCH NEXT FROM CustCursor     
	INTO @cust_id, @cust_name, @cust_address,          
		 @cust_city, @cust_state, @cust_zip,          
	  	 @cust_country, @cust_contact, @cust_email 
WHILE @@FETCH_STATUS = 0 
BEGIN 
 
FETCH NEXT FROM CustCursor         
	INTO @cust_id, @cust_name, @cust_address,              
		 @cust_city, @cust_state, @cust_zip,              
		 @cust_country, @cust_contact, @cust_email 
END 
CLOSE CustCursor 
 

关闭游标

  • DB2、Oracle、PostgreSQL
CLOSE CustCursor 
  • Microsoft SQL Server
CLOSE CustCursor 
DEALLOCATE CURSOR CustCursor 



第22课 高级SQL特性

约束

  • 约束(constraint):管理如何插入或处理数据库数据的规则
    DBMS 通过在数据库表上施加约束来实施引用完整性(referential integrity)。
    大多数约束是在表定义中定义的,用 CREATE TABLEALTER TABLE 语句。

主键

创建主键

-- 法一
CREATE TABLE Vendors  
(     
	vend_id         CHAR(10)       NOT NULL PRIMARY KEY,      
	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 
); 

-- 法二 (SQLite不允许)
ALTER TABLE Vendors  
ADD CONSTRAINT PRIMARY KEY (vend_id); 

外键

外键是表中的一列,其值必须列在另一表的主键中。

外键可以保证引用完整性,利用外键还可以防止意外删除数据

CREATE TABLE Orders 
(     
	order_num     INTEGER     NOT NULL PRIMARY KEY,     
	order_date    DATETIME    NOT NULL, 
	cust_id       CHAR(10)    NOT NULL REFERENCES  
	➥Customers(cust_id) 
); 
 
 
ALTER TABLE Orders 
ADD CONSTRAINT 
FOREIGN KEY (cust_id) REFERENCES Customers (cust_id)  

唯一约束

唯一约束用来保证一列(或一组列)中的数据是唯一的。它们类似于主键,但存在以下重要区别。

  1. 表可包含多个唯一约束,但每个表只允许一个主键。
  2. 唯一约束列可包含 NULL 值。
  3. 唯一约束列可修改或更新。
  4. 唯一约束列的值可重复使用。
  5. 与主键不一样,唯一约束不能用来定义外键。

检查约束

检查约束用来保证一列(或一组列)中的数据满足一组指定的条件。检查约束的常见用途有以下几点。

  1. 检查最小或最大值。例如,防止 0个物品的订单(即使0是合法的数)。
  2. 指定范围。例如,保证发货日期大于等于今天的日期,但不超过今天起一年后的日期。
  3. 只允许特定的值。例如,在性别字段中只允许 M 或 F。
CREATE TABLE OrderItems 
(     
	order_num     INTEGER     NOT NULL,     
	order_item    INTEGER     NOT NULL,     
	prod_id       CHAR(10)    NOT NULL,     
	quantity      INTEGER     NOT NULL CHECK (quantity > 0),     
	item_price    MONEY       NOT NULL 
);


ADD CONSTRAINT CHECK (gender LIKE '[MF]') 

索引

索引用来排序数据以加快搜索和排序操作的速度。

主键数据总是排序的,这是 DBMS的工作。 因此,按主键检索特定行总是一种快速有效的操作。

可以在一个或多个列上定义索引,使 DBMS保存 其内容的一个排过序的列表。在定义了索引后,DBMS 以使用书的索引类似的方法使用它。DBMS 搜索排过序的索引,找出匹配的位置,然后检索这些行。

  1. 索引改善检索操作的性能,但降低了数据插入、修改和删除的性能。 在执行这些操作时,DBMS必须动态地更新索引。
  2. 索引数据可能要占用大量的存储空间。  并非所有数据都适合做索引。取值不多的数据(如州)不如具有更多可能值的数据(如姓或名),能通过索引得到那么多的好处。
  3. 索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据,则该数据可能适合做索引。
  4. 可以在索引中定义多个列(例如,州加上城市)。这样的索引仅在以州加城市的顺序排序时有用。如果想按城市排序,则这种索引没有用处。

索引用CREATE INDEX语句创建(不同DBMS创建索引的语句变化很大)。

CREATE INDEX prod_name_ind 
ON Products (prod_name); 

索引必须唯一命名。

ON 用来指定被索引的表,而索引中包含的列在表名后的圆括号中给出。

触发器

==触发器是特殊的存储过程,它在特定的数据库活动发生时自动执行。==触发器可以与特定表上的 INSERTUPDATEDELETE 操作(或组合)相关联。

根据所使用的DBMS的不同,触发器可在特定操作执行之前之后执行。

触发器的常见用途:

  1. 保证数据一致。例如,在 INSERT 或 UPDATE 操作中将所有州名转换为大写。
  2. 基于某个表的变动在其他表上执行活动。例如,每当更新或删除一行时将审计跟踪记录写入某个日志表。
  3. 进行额外的验证并根据需要回退数据。例如,保证某个顾客的可用资金不超限定,如果已经超出,则阻塞插入。
  4. 计算计算列的值或更新时间戳。
-- 创建一个触发器,它对所有 INSERT 和 UPDATE 操作,将 Customers 表中的 cust_state 列转换为大写。 

--  SQL Server
CREATE TRIGGER customer_state 
ON Customers 
FOR INSERT, UPDATE 
AS 
UPDATE Customers 
SET cust_state = Upper(cust_state) 
WHERE Customers.cust_id = inserted.cust_id; 


-- Oracle和 PostgreSQL
CREATE TRIGGER customer_state 
AFTER INSERT OR UPDATE 
FOR EACH ROW 
BEGIN 
UPDATE Customers 
SET cust_state = Upper(cust_state) 
WHERE Customers.cust_id = :OLD.cust_id 
END; 

数据库安全

一般说来,需要保护的操作有:

  1. 对数据库管理功能(创建表、更改或删除已存在的表等)的访问;
  2. 对特定数据库或表的访问;
  3. 访问的类型(只读、对特定列的访问等);
  4. 仅通过视图或存储过程对表进行访问;
  5. 创建多层次的安全措施,从而允许多种基于登录的访问和控制;
  6. 限制管理用户账号的能力。
  • 1
    点赞
  • 0
    评论
  • 2
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

参与评论 您还未登录,请先 登录 后发表或查看评论
©️2022 CSDN 皮肤主题:深蓝海洋 设计师:CSDN官方博客 返回首页

打赏作者

Yating260

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值