[关闭]
@FunC 2018-05-15T11:10:14.000000Z 字数 5412 阅读 1495

SQL必知必会 CH15~22

学习笔记


CH15 插入数据

可以使用INSERT来将行插入到数据表。插入有几种方式:
1. 插入完整的行
2. 插入行的一部分
3. 插入某些查询结果

插入完整的行

使用INSERT INTO语句:

  1. INSERT INTO Customers(cust_id,
  2. cust_country,
  3. cust_contact,
  4. cust_email)
  5. VALUES('1000000006',
  6. 'USA',
  7. NULL,
  8. NULL);”

其中列名可以不用列出,但列出列名更安全(这样即使表的结构发生改变,也能正常工作)

插入部分行

可某些列允许为空/有默认值时,可以省略

插入检索出的数据

即INSERT SELECT语句:

  1. INSERT INTO Customers(cust_id,
  2. cust_contact,
  3. cust_email,
  4. cust_name,
  5. cust_address,
  6. cust_city,
  7. cust_state,
  8. cust_zip,
  9. cust_country)
  10. SELECT cust_id,
  11. cust_contact,
  12. cust_email,
  13. cust_name,
  14. cust_address,
  15. cust_city,
  16. cust_state,
  17. cust_zip,
  18. cust_country
  19. FROM CustNew;

注意填充CustNew时,不应该使用用过的cust_id值。同时SELECT的列名不一定要一样,DBMS会根据列的次序来匹配插入。

INSERT SELECT 可以使用WHERE子句,用于过滤插入数据

从一个表复制到另一个表

即SELECT INTO语句。和INSERT SELECT的区别在于,它会动态创建一张全新的表,并把SELECT的数据导入

  1. CREATE TABLE CustCopy AS
  2. SELECT * FROM Customers;

使用时有几个需要注意的点:
* 任何SELECT选项和子句都可以使用(包括WHERE和GROUP BY)
* 可利用联结从多个表插入数据
* 数据只能插入到一个表中

这种操作很适合用来创建复制的数据表,来测试新SQL语句

CH16 更新和删除数据

更新数据

使用UPDATE语句更新,UPDATE语句由三部分组成:
1. 要更新的表
2. 列名及其新值
3. 确定要更新的行的过滤条件

  1. UPDATE Customers
  2. SET cust_email = 'kim@thetoystore.com'
  3. WHERE cust_id = '1000000005';

在UPDATE语句中可以使用子查询

要删除某个列的值时,可设置它为NULL(假如表允许NULL值)

删除数据

使用DELETE语句删除行

  1. DELETE FROM Customers
  2. WHERE cust_id = '1000000006';

关于外键:

> 先前提到可以通过联结来结合两个表。DBMS还可以使用外键来严格实施关系,保证引用的完整性。从而防止删除某个关系需要用到的行

CH17 创建和操纵表

创建表

使用CREATE TABLE 关键字

  1. CREATE TABLE OrderItems
  2. (
  3. order_num INTEGER NOT NULL,
  4. order_item INTEGER NOT NULL,
  5. prod_id CHAR(10) NOT NULL,
  6. quantity INTEGER NOT NULL DEFAULT 1,
  7. item_price DECIMAL(8,2) NOT NULL
  8. );

注意各列之间用逗号分隔。

对于默认值,还可以使用系统日期的函数或变量来作默认日期。不同的DBMS之间实现不一:

[image:E3D8C3A4-134E-4542-8897-DD04D3DD49D5-355-00000FF843A11F5A/41D25A7D-B13F-4585-8B1F-D33DE38BEA5F.png]

更新表

使用ALTER TABLE语句更新表

不同的DBMS之间允许更新的内容差别很大,下面是在更新表的时候需要考虑的事情:
* 尽量不要在表中包含数据时对表进行更新
* 许多DBMS不允许删除或更改表中的列
* 多数DBMS允许重命名表中的列
* 许多DBMS限制对已经填有数据的列进行更改,对未填有数据的列几乎没有限制。

其中给已有表增加列是所有DBMS都支持的操作:

  1. ALTER TABLE Vendors
  2. ADD vend_phone CHAR(20);

删除列:

  1. ALTER TABLE Vendors
  2. DROP COLUMN vend_phone;

删除表

使用DROP TABLE语句

  1. DROP TABLE CustCopy;

重命名表

DB2、MariaDB、MySQL、Oracle和PostgreSQL用户使用RENAME语句,SQL Server用户使用sp_rename存储过程,SQLite用户使用ALTER TABLE语句。具体查阅响应的DBMS文档

CH18使用视图

视图是虚拟的表,它只包含使用时动态检索数据的查询。

下面是视图的一些常见应用:
* 重用SQL语句
* 简化复杂的SQL操作,隐藏细节
* 使用表的一部分而不是整个表
* 保护数据
* 更改数据格式和表示

视图的规则和限制

创建视图

使用CREATE VIEW语句创建(如果想重命名则必须先DROP VIEW再重新创建)

以利用视图简化复杂的联结为例

  1. CREATE VIEW ProductCustomers AS
  2. SELECT cust_name, cust_contact, prod_id
  3. FROM Customers, Orders, OrderItems
  4. WHERE Customers.cust_id = Orders.cust_id
  5. AND OrderItems.order_num = Orders.order_num;

CH19 使用存储过程

存储过程类似于编程中的函数。

优点:
* 简化复杂操作
* 防止错误,保证数据一致性
* 简化对变动的管理
* 存储过程以编译过的形式存储,性能更高

缺点:
* 不同DBMS的存储过程语法有所不同,可移植性差(但调用方式基本一致)
* 编写存储过程复杂,需要更高的技能和经验

执行存储过程

使用EXECUTE语句:

  1. EXECUTE AddNewProduct( 'JTS01',
  2. 'Stuffed Eiffel Tower',
  3. 6.49,
  4. 'Plush stuffed toy with the text La
  5. ➥Tour Eiffel in red white and blue' );

很像函数调用。

创建存储过程

Oracle版本:

  1. CREATE PROCEDURE MailingListCount (
  2. ListCount OUT INTEGER -- ListCount 是一个存储返回结果的变量
  3. )
  4. IS
  5. v_rows INTEGER; -- v_rows 变量
  6. BEGIN
  7. SELECT COUNT(*) INTO v_rows -- SELECT 的结果赋值给 v_rows
  8. FROM Customers
  9. WHERE NOT cust_email IS NULL;
  10. ListCount := v_rows; -- 结果赋值给外部的 ListCount
  11. END;

SELECT 语句有类似于“执行语句”的功能,如SQL Server中SELECT @order_num=@order_num+1

不同DBMS提供的特性和语法都有所不同,具体需查阅响应的DBMS文档

CH20 管理事务处理

使用事务处理(transaction processing),通过确保成批的SQL操作要么完全执行,要么完全不执行,来维护数据库的完整性。

事务处理用来管理INSERT,UPDATE和DELETE语句,不能回退CREATE和DROP操作

管理事务处理类似于 git,我们需要将SQL语句组分解为逻辑块,明确规定何时应该回退,何时不应该回退。

事务处理块的开始和结束(SQL Server中):

  1. BEGIN TRANSACTION
  2. ...
  3. COMMIT TRANSACTION

使用ROLLBACK

用来回退SQL语句:

  1. DELETE FROM Orders;
  2. ROLLBACK;

使用COMMIT

一般SQL使用隐式提交,直接对表进行编写。在事务处理块中,则需要进行明确的提交(使用COMMIT 语句)

使用保留点

类似于游戏的存档点,可以使用ROLLBACK回到特定的保留点:

  1. -- MySQL
  2. SAVEPOINT delete1;
  3. -- 回到保留点
  4. ROLLBACK TRANSACTION delete1;

保留点越多,越能灵活地进行回退

CH21 使用游标

有时候需要在检索出来的行中前进或者后退一行或多行,这就是游标的用途。

使用游标

步骤:
1. 声明游标(这个过程实际上没有检索数据,只是定义要使用的SELECT语句)
2. 使用前要打开游标(实际即检索定义时的SELECT语句)
3. 对于填有数据的游标,根据需要取出各行
4. 在结束游标使用时,必须关闭游标

创建游标:

使用DECLARE命名游标,并定义相应的WHERE语句:

  1. -- MySQL
  2. DECLARE CustCursor CURSOR
  3. FOR
  4. SELECT * FROM Customers
  5. WHERE cust_email IS NULL

使用游标

使用OPEN CURSOR语句打开游标:

  1. OPEN CURSOR CustCursor;

打开后使用FETCH语句访问游标数据:

  1. -- Oracle
  2. DECLARE TYPE CustCursor IS REF CURSOR
  3. RETURN Customers%ROWTYPE;
  4. DECLARE CustRecord Customers%ROWTYPE
  5. BEGIN
  6. OPEN CustCursor;
  7. FETCH CustCursor INTO CustRecord;
  8. CLOSE CustCursor;
  9. END;

关闭游标

部分DBMS要求明确释放游标所占用的资源:

  1. -- Oracle
  2. CLOSE CustCursor

第二次使用时,只需用OPEN打开即可。

CH22 高级SQL特性

约束

约束时管理如何插入或处理数据库数据的规则

主键

主键的值唯一标识表中的每一行,方便处理表中的行。

满足以下条件都可以用于主键:
* 任意两行的主键值都不相同
* 不允许NULL值
* 主键值所在的列从不修改或更新
* 主键值不能重用(某值删除后不能重新分配)

  1. CREATE TABLE Vendors
  2. (
  3. vend_id CHAR(10) NOT NULL PRIMARY KEY,
  4. vend_name CHAR(50) NOT NULL,
  5. )

外键

如同之前提到过的,外键时保证引用完整性的极其重要部分。

使用 REFERENCES 关键字

  1. CREATE TABLE Orders
  2. (
  3. order_num INTEGER NOT NULL PRIMARY KEY,
  4. order_date DATETIME NOT NULL,
  5. cust_id CHAR(10) NOT NULL REFERENCES Customers(cust_id)
  6. );

唯一约束

唯一约束用来保证一列中的数据是唯一的,和主键些微类似但有以下区别:
* 可以有多个唯一约束,只能有一个主键
* 唯一约束列可以包含NULL值
* 唯一约束列克修改或更新
* 唯一约束列的值可重复使用
* 与主键不同,唯一约束不能用来定义外键

一个常见的例子就是微博昵称(区别于微博账号)。
可以使用 UNIQUE 关键字在表定义中定义。

检查约束

即列的数据需要满足一定要求,例如性别只能是M或F。

  1. CREATE TABLE OrderItems
  2. (
  3. prod_id CHAR(10) NOT NULL,
  4. quantity INTEGER NOT NULL CHECK (quantity > 0),
  5. );

索引

索引用来排序数据以加快搜索和排序操作的速度。
主键数据总是排序的,所以按主键检索特定的行总是一种快速有效的操作。

在开始创建索引时,注意:
* 索引改善检索操作的性能,但降低了数据插入、修改和删除的性能。因为必须动态地更新索引
* 索引数据可能要占用大量的存储空间
* 并非所有数据都适合做索引(取值多的数据比较适合)
* 索引用于数据过滤和数据排序

例如:

  1. CREATE INDEX prod_name_ind
  2. ON PRODUCTS (prod_name);

触发器

触发器是特殊的存储过程,它与特定表上的INSERT,UPDATE和DELETE操作相关联。

触发器内代码具有以下数据的访问权:
* INSERT操作中的所有新数据
* UPDATE操作中的新旧数据
* DELETE操作中删除的数据

常见用途:
* 保证数据一致(如确保大写)
* 基于某个表的变动在其他表上执行活动(如更新日志表)
* 进行数据验证,根据需要回退数据

例子(Oracle):

  1. CREATE TRIGGER customer_state
  2. AFTER INSERT OR UPDATE
  3. FOR EACH ROW
  4. BEGIN
  5. UPDATE Customers
  6. SET cust_state = Upper(cust_state)
  7. WHERE Customers.cust_id = :OLD.cust_id
  8. END;

可以发现,部分需求可以通过约束来实现,而约束的处理也往往比触发器快。因此在可能的时候,应该尽量使用约束。

数据库安全

一般来说,需要保护的操作有:
* 对数据库管理功能的访问(如创建表、更改或删除已存在的表等)
* 对特定数据库或表的访问
* 访问的类型(只读、对特定列的访问等)
* 仅通过视图或存储过程对表进行访问
* 创建多层次的安全措施,从而允许多种基于登陆的访问和控制
* 限制管理用户账号的能力

安全性使用SQL的 GRANT 和 REVOKE 语句来管理。

添加新批注
在作者公开此批注前,只有你和作者可见。
回复批注