SQL必知必会笔记2

15. 插入数据

1). 数据插入

INSERT 用来将行插入到数据库表中，插入的方式有几种：

（1）插入完整的行；

INSERT INTO Customers VALUES（

'1000000006',

       'Toy Land',

       '123 Any Street',

       'New York',

       'NY',

       '11111',

       'USA',

       NULL,

       NULL

）；

（2）插入行的一部分；

INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country)

 VALUES('100000000006','Toy Land','123 Any Street', 'New York', 'NY', '11111','USA');

（3）插入某些查询的结果:

用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通常只插入一行，要插入多行，要执行多个INSERT语句，但INSERT SELECT 语句是个例外，不管SELECT语句返回多少条，INSERT都会插入。

2). 从一个表复制到另一个表

可以使用SELECT INTO语句，将一个表的内容复制到一个全新的表中（这个表是在运行时创建的）。

Insert select 和 select into 的区别：

前者是从一个已经存在的表中选择数据插入到另一个已存在的表中，后者是从一个已经存在的表中复制数据到另一个新创建的表中。

SELECR * INTO CustCopy FROM Customers;

16. 更新和删除数据

1). 更新数据

基本的update语句包括：要更新的表； 列名和他们的新值； 确定要更新哪些行的过滤条件。

（1）更新表中特定行

UPDATE Customers SET cust_name = 'Bonnie Cobin' WHERE cust_id = '10000005';

（2）更新表中所有的行

要是没有选择的条件，就会更新整个表中的所有行。

但是，可以更新多个列（属性）：

UPDATE Customers SET cust_name = 'Bonnie Cobin' , cust_email = 'sam@microsoft.com' WHERE cust_id = '10000005';

By the way: 要删除某个列的值，可以设置它为NULL: UPDATE Customers SET cust_email = NULL WHERE cust_id = '10000005';

2). 删除数据

（1）删除特定的行

DELETE FROM Customers WHERE cust_id = '10000005';

（2）删除所有的行

DELETE FROM Customers;

还有一种快速删除所有行的方法： 使用TRUNCATE TABLE 语句，速度更快！

在做出删除或者更新操作之前，要先用Select语句测试一下，确保自己更新的或删除的是正确的数据，一般都得使用带WHERE条件的语句去做更新或删除操作。

17.创建表

1）. 创建表：

一般创建表使用语句：Create table TableName（）；

Create Table Products (prod_id  CHAR(10)  NOT NULL, vend_id  CHAR(10  NOT NULL, prod_name CHAR(30)  NOT NULL) );

Table name: Products

注： NULL值，是没有值，而不是空字符串，字符串是一个有效的值，不是无值。

2）.更新表

用 ALTER TABLE语句。

在更新表的时候，这个表必须存在。

Ie: 给已有的表增加列，这几乎是每个数据库都支持的唯一操作：

ALTER TABLE Vendors ADD vend_phone CHAR(20);

注：因为数据库的更改不能撤销，所以在更改表之前必须要备份。

3）. 删除表

使用DROP TABLE 语句来删除数据库表。

删除表也不能撤销，表将会被永久删除。

提示：使用关系规则放置意外删除

许多DBMS允许强制实施有关规则，防止删除与其他表相关联的表。在实施这些规则的时候，要是有相关关系的表被drop table语句删除时，会强制阻止被删除，除非关系被删除为止。

4）.重命名表

DB2、MariaDB、MySQL、Oracle和PostgreSQL用户使用RENAME ，SQL Serversp_rename存储过程，SQLite用户使用ALTER TABLE语句

SQL  Server 用户使用sp_rename语句，SQLite用户使用ALTER TABLE语句。

18. 使用视图

1）. 视图

视图是虚拟的表，与包含数据的表不一样，视图只是包含使用时的动态检索数据的查询。

I.e. : 有一个查询语句：

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 = 'RGAN01'; 

假使把整个查询包装成一个名为ProductCustomers的虚拟表，就可以轻松的检索出相同的数据：

SELECT cust_name, cust_contact FROM ProductCustomers WHERE prod_id = 'RGAN01';

所以说ProductCustomers作为一个视图，不包含任何是、列或者数据，只包含一个查询。

使用视图有哪些好处呢？（1）可以重用SQL查询语句，简化复杂的SQL操作，在编写查询后，可以方便的重用它而不必知道其基本的查询细节；（2）使用表的一部分而不是表的全部；（3）保护数据，可以授予用户访问表的特定部分的权限，而不是整个表的访问权限；（4）更改数据格式和表示，视图可返回与底层表的表示和格式不同的数据。创建属兔后，可以用与表基本相同的方式使用它们。

使用视图的规则和限制：（1）视图必须唯一命名；（2）创建视图，必须要有足够的访问权限，而这些权限通常是有数据库管理人员授予；

（3）视图可以嵌套使用；（4）重降低查询的性能，因此在产品环境使用之前，应该对其进行全面测试；（5）许多DBMS禁止在视图查询中使用ORDER BY子句。（6）视图不能索引，也不能有关联的触发器或默认值。

2）. 创建视图

视图用CREATE VIEW viewName语句来创建，要删除视图，可以使用DROP VIEW viewName语句，要是想覆盖（或更新）视图，必须先删除它，然后再重新创建。

ie：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，联结了3个表，返回已定购任意产品的所有顾客的列表。 要是执行SELECT * From ProductCustomers，将列出订购了任意产品的顾客。要是只检索订购了产品RGAN1的顾客，可执行：

SELECR cust_name, cust_contact, prod_id FROM ProductCustomers Where prod_id = 'RGAN01';

• 使用视图可以重新格式化检索出来的数据；
• 使用视图可以过滤不想要的数据：

Ie :  CREATE VIEW CustomerEMailList AS SELECT cust_id, cust_name, cust_email FROM Customers WHERE cust_email IS NOT NULL;

Select * from CustomerEMailList;

• 使用视图与计算字段：

SELECT prod_id,        quantity,        item_price,

       quantity*item_price AS expanded_price

FROM OrderItems

WHERE order_num = 20008;

输出：

prod_id      quantity      item_price      expanded_price

--------     ---------     -----------     --------------

RGAN01       5             4.9900          24.9500

BR03         5             11.9900         59.9500

BNBG01       10            3.4900          34.9000

BNBG02       10            3.4900          34.9000

BNBG03       10            3.4900          34.9000

使用视图：

CREATE VIEW OrderItemsExpanded AS

SELECT order_num,

       prod_id,        quantity,        item_price,

       quantity*item_price AS expanded_price

FROM OrderItems;

 

Input:

SELECT * FROM OrderItemsExpanded WHERE order_num = 20008;

Output:

order_num     prod_id     quantity      item_price     expanded_price

---------     -------     ---------     ----------     -------------20008         RGAN01      5             4.99           24.95

20008         BR03        5             11.99          59.95

20008         BNBG01      10            3.49           34.90

20008         BNBG02      10            3.49           34.90

20008         BNBG03      10            3.49           34.90

19. 使用存储过程

场景：为了处理订单，需核对以保证库存中有相应的物品；

如果物品有库存，需要预定，不再出售给别人，并且减少物品的数据以反应正确的库存量；

如果库存中没有物品需要订购，就需要跟供应商进行某些交互；

关于哪些物品入库（并且可以立即发货）和哪些物品退订，是需要通知相应的顾客。

1）. 存储过程

上面的场景需要针对多表的多条SQL语句执行，有时候次序也不是固定的，而且每次处理都要执行这些语句，有点繁琐，为了以后可以多次使用，我们可以创建存储过程。简单来说，存储过程就是为以后使用而保存的一条或多条语句。可将其视为批文件。

2）. 为什么要使用存储过程呢？

（1）通过把处理封装在一个易用的单元中，可以简化复杂的操作（如前面例子所述）。

由于不要求反复建立一系列处理步骤，因而保证了数据的一致性。如果所有开发人员和应用程序都使用同一存储过程，则所使用的代码都

是相同的。

这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性。

（2）简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，那么只需要更改存储过程的代码。使用它的人员甚至不需要知道

这些变化。

这一点的延伸就是安全性。通过存储过程限制对基础数据的访问，减少了数据讹误（无意识的或别的原因所导致的数据讹误）的机会。

 （3）因为存储过程通常以编译过的形式存储，所以DBMS处理命令的工作较少，提高了性能。

存在一些只能用在单个请求中的SQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。

所以说使用存储过程有三个主要的好处，简单，安全，高性能。

3）. 执行存储过程

EXECUTE语句

EXECUTE AddNewProduct（'JTS01', 'Stuffed Eiffel Tower', 6.49, 'Plush stuffed toy with the text La Tour Eiffel in red white and blue'）;

上面的例子是执行存储过程，往表Products中添加新产品。

而在我们执行上述语句时，存储过程所完成的工作有：验证传递的数据，保证所有4个参数都有值；生成主键的唯一ID；将新产品插入Products表，在合适的列中存储生成的主键和传递的数据。

4）. 创建存储过程

SQL Server版本：

Input:

CREATE PROCEDURE MailingListCount

AS

DECLARE @cnt INTEGER//使用DECLARE语句声明了一个名为@cnt的局部变量（SQL中的所有局部变量都以@起头）

SELECT @cnt = COUNT(*)

FROM Customers

WHERE NOT cust_email IS NULL; RETURN @cnt;

调用SQL Server：

DECLARE @ReturnValue INT

EXECUTE @ReturnValue = MailingListCount;

SELECT @ReturnValue;

20. 管理事务处理

1）.  事务处理（transaction processing）：通过确保成批的SQL操作要么完全执行，要么完全不执行；来维护数据库的完整性。事务处理是一种机制，用来管理必须成批执行的SQL操作，保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，他们要么都完成，要么完全不执行（除非明确指示）。如果没有发生错误，整组语句提交给数据库表，如果发生错误，则进行回退，将数据库恢复到某个已知且安全的状态。

其中，有几个反复出现的关键词：

事务（transaction）指一组SQL语句；

回退（rollback）指撤销指定SQL语句的过程；

提交（commit）指将未存储的SQL语句结果写入数据库表；

提示：可以回退哪些语句？

事务处理用来管理INSERT、UPDATE和DELETE语句。不能回退SELECT语句（回退SELECT语句也没有必要），也不能回

退CREATE或DROP操作。事务处理中可以使用这些语句，但进行回退时，这些操作也不撤销。

2）. 控制事务处理

管理事务的关键在于将SQL语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退。

在SQL Server中，标识处理块开始和结束的是：

Begin Transaction

…

Commit Transaction

SQL 的ROLLBACK命令用来回退（撤销）SQL语句：

ie： Delete From Orders；

ROLLBACK；//这样做可以将已删除的行恢复回来

一般的SQL语句，都可以直接对数据库表直接执行和编写，这就是所谓的隐式提交（implicit commit），即提交（写或保存）操作是自动进行的。

但有些也是需要进行明确提交的，使用COMMIT语句：

ie： BEGIN TRANSACTION

DELETE OrderItems WHERE order_num = 12345;

DELETE Orders WHERE order_num = 12345;

COMMIT TRANSACTION

对于有些复杂的事务来说，不能一次性的提交，可能需要分模块的进行提交，这样可以回退到某个点，而要支持回退部分的事务，必须在事务处理块中的合适位置放置占位符，这样，如果需要回退，可以回退到某个占位符。而每个保留点都应该有他自己的唯一标识的名字，以便在回退时， DBMS知道回退到何处。

Ie: (SQL Server)

SAVE TRANSACTION delete1;

ROLLBACK TRANSCATION delete1;

21. 使用游标

1）. 游标

有时，需要在检索出来的行中前进或后退一行或多行，这就是游标的用途所在。游标（sursor）是一个存储在DBMS服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览器中的数据。

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

2）. 使用游标

在使用游标前，必须要声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的SELECT语句和游标选项。一旦声明，就必须打开游标以供使用。这个过程用前面定义的SELECT语句把数据实际检索出来。对于填有数据的游标，根据需要取出（检索）各行。在结束游标使用时，必须关闭游标，可能的话，释放游标（有赖于具体的DBMS）。声明游标后，可根据需要频繁地打开和关闭游标。在游标打开时，可根据需要频繁地执行取操作。

创建游标（使用DECLARE语句）：

DECLARE CustCursor CURSOR FOR SELECT * FROM Customers WHERE cust_email IS NULL； //为邮件为空的顾客创建游标，找出空缺电子邮件的地址。

打开游标（使用OPEN CURSOR 语句）

用FETCH 语句访问游标数据， FETCH指出要检索哪些行，从何处检索他们以及将它们放于何处。

关闭游标（使用CLOSR Cursor语句）

一旦游标被关闭，要再使用的话需要再次打开，第二次使用的时候不需要声明。

22. 高级SQL特性

1）. 约束（constraint）

管理如何插入或处理数据库数据的规则。DBMS通过在数据库上施加约束来实施引用完整性。主键（Primary key）是一种特殊的约束.外键是表中的一列，其值必须列在另一表的主键中。外键是保证引用完整性的极其重要部分。唯一约束用来保证一列（或一组列）中的数据是唯一的。唯一约束和主键类似，却与主键有很大的区别：

表可包含多个唯一约束，但每个表只允许一个主键。

唯一约束列可包含NULL值。

唯一约束列可修改或更新。

唯一约束列的值可重复使用。

与主键不一样，唯一约束不能用来定义外键。索引用来排序数据以加快搜索和排序操作的速度。

2）. 索引

在创建索引前，应该记住以下内容：

索引改善检索操作的性能，但降低了数据插入、修改和删除的性能。在执行这些操作时，DBMS必须动态地更新索引。

索引数据可能要占用大量的存储空间。

并非所有数据都适合做索引。取值不多的数据（如州）不如具有更多可能值的数据（如姓或名），能通过索引得到那么多的好处。

索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据，则该数据可能适合做索引。

创建索引： Create Index prod_name_ind ON Products(prod_name); //在Product表上创建索引prod_name_ind，索引中包含的列在表名后的圆括号中给出。

3）. 触发器

触发器是特殊的存储过程，它在特定的数据库活动发生时自动执行。触发器可以与特定表上的INSERT，UPDATE，DELETE操作想关联。与存储过程不一样（存储过程只是简单的存储SQL语句），触发器与单个的表相关联。与Orders表上的INSERT操作相关联的触发器只在Orders表中插入行时执行。下面是触发器的一些常见用途：

保证数据一致。例如，在INSERT或UPDATE操作中将所有州名转换为大写。

基于某个表的变动在其他表上执行活动。例如，每当更新或删除一行时将审计跟踪记录写入某个日志表。

进行额外的验证并根据需要回退数据。例如，保证某个顾客的可用资金不超限定，如果已经超出，则阻塞插入。

计算列的值或更新时间戳。

创建触发器：（下面的例子创建的触发器，它对所有INSERT 和 UPDATE操作，将Customers表中的cust_state列转换为大写）

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;