SQL必知必会笔记（下）
十六、更新和删除数据
1.更新数据
两种方式：更新表中的特定行，更新表中的所有行
update语句
三部分：要更新的表，列名和它们的新值，确定要更新哪些行的过滤条件
UPDATE Customers
SET cust_contact = 'Sam Roberts',
cust_email = 'sam@gmail.com'
WHERE cust_id = '10086';
要删除某列的值，可设置其为NULL。
2.删除数据
两种方式：从表中删除特定的行；从表中删除所有行
DELETE语句
两部分：要删除数据的表名，要过滤的行名
DELETE FROM Customers
WHERE cust_id = '10086';
不要遗忘where子句，否则会删除表中所有数据
使用TRUNCATE TABLE语句，可以快速删除所有行
3.更新和删除的指导原则
保证每个表都有主键，尽可能像使用where子句那样使用它。
在update或delete语句使用where子句之前，应先用select进行测试，保证它过滤的是正确的记录。
使用强制实施引用完整性的数据库，这样DBMS将不允许删除其数据与其他表相关的行。
十七、创建和操纵表
1.创建表
两种方式：
使用交互式创建和管理数据库表的工具；直接使用SQL语句操纵。
1.1表创建基础
必要信息：新表名字；表列的名字和定义；指定表位置
CREATE TABLE Products
(
prod_id CHAR(10) NOT NULL,
prod_name CHAR(10) NOT NULL,
prod_price DECIMAL(8,2) NOT NULL,
prod_desc VARCHAR(1000) NULL
);
注：对于MySQL，varchar必须替换成text。
创建表语句不能覆盖已存在的表。
1.2使用NULL值
在不指定NOT NULL时，多数DBMS认为指定的是NULL。
允许NULL值的列不能作为唯一标识。
空字符串''在NOT NULL列中是允许的。
1.3指定默认值
在列定义中用关键字DEFAULT指定。
默认值常用于日期或时间戳列。如MySQL中获得系统日期的函数为CURRENT_DATE()
2.更新表
必要信息：要更改的表名；要做哪些更改。
ALTER TABLE Vendors
ADD vend_phone CHAR(20);
注：更改无法撤销，应该在进行改动前做完整的备份。
复杂表结构更改涉及步骤：
创建新表
使用INSERT SELECT从旧表复制数据到新表
检验新表
重命名旧表(或删除)
用旧表原名重命名新表
根据需要重新创建触发器、存储过程、索引和外键
3.删除表
DROP TABLE CustCopy;
注：删除表没有确认，也不能撤销。
十八、使用视图
1.视图
视图是虚拟的表，包含的不是数据而是使用时动态检索数据的查询。视图提供了一种封装select语句的层次，可用来简化数据处理，重新格式化或保护基础数据。
1.1为什么使用视图
视图的常见应用：
重用SQL语句，简化SQL操作；
使用表的一部分而不是整个表；
授予用户访问表的特定部分的权限，从而保护数据；
更改数据格式和表示。
1.2视图的规则和限制
视图必须唯一命名
视图可以嵌套，即可以利用从其他视图中检索数据的数据的查询来构造视图，但这样可能会严重降低查询的性能，所以使用前需要全面测试。
视图不能索引，也不能有关联的触发器或默认值。
有些DBMS要求对返回的所有列进行命名，如果列是计算字段，则需要使用别名。
2.创建视图
2.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;
这样就用视图联结了三张表，
如要检索订购了某产品的顾客，可用以下查询
SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';
2.2用视图重新格式化检索出的数据
REATE VIEW VendorLocations AS
SELECT RTRIM(vend_name)+'('+RTRIM(vend_coutry)+')'
AS vend_title
FROM Vendors;
创建视图完成后，就可以通过检索数据创建新的邮件标签
SELECT *
FROM VendorLocations;
输出
vend_title
Bear Emporium(USA)
Fun Games(England)
Jouets et ours(France)
...
2.3用视图过滤不想要的数据
创建视图
CREATE VIEW CustomerEmailList AS
SELECT cust_id, cust_name, cust_email
FROM Customers
WHERE cust_email IS NOT NULL;
使用视图
SELECT *
FROM CustomerEmailList;
2.4使用视图与计算字段
CREATE VIEW OrderItemsExpanded AS
SELECT order_num,
prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM OrderItems;
检索订单201809的详细内容
SELECT *
FROM OrderItemsExpanded
WHERE order_num = 201809;
十九、使用存储过程
1.存储过程
存储过程就是为以后使用而保存的一条或多条SQL语句。可将其视为批文件，事实上它们的作用不仅限于批处理。
2.为什么要使用存储过程
简化复杂的操作，提高工作性能；
保证数据的一致性、降低出错可能性；
简化对变动的管理，通过存储过程限制对基础数据的访问，减少数据讹误的机会；
提高代码灵活性和可移植性。
3.执行存储过程
EXECUTE接受存储过程名和需要传递给它的任何参数。
EXECUTE AddNewProduct('JS011',
'Stuffed Eiffel Tower',
659);
三个参数匹配存储过程中3个预期变量，不包括主键。
存储过程：
验证传递的数据，保证3个参数都有值
生成用作主键的唯一ID
将新产品插入Products表，在合适的列中存储生成的主键和传递的数据。
4.创建存储过程
声明一个变量来保存存储过程返回的任何值，然后执行存储过程，载使用SELECT语句显示返回的值。
CREATE PROCEDURE MilingListCount
AS
DECLARE @cnt INTEGER
SELECT @cnt = COUNT(*)
FROM Customers
WHERE NOT cust_email IS NULL;
RETURN @cnt;
注：SQLServer中所有局部变量名都以@起头。
调用例子：
DECLARE @ReturnValue INT
EXECUTE @ReturnValue=MailingListCount;
SELECT @ReturnValue;
第20课、管理事务处理
1.事务处理
通过确保成批的SQL操作要么完全执行，要么完全不执行，来维护数据库的完整性。
事务(transaction)指一组SQL语句；
回退(rollback)指撤销指定SQL语句的过程；
提交(commit)指将未储存的SQL语句结果写入数据库表；
保留点(savepoint)指事务处理中设置的临时占位符(placeholder)，可以对它发布回退。
2.控制事务处理
SQL Sever
BEGIN TRANSACTION
...
COMMIT TRANSACTION
MySQL
START TRANSACTION
...
2.1 使用ROLLBACK回退
可以回退INSERT、UPDATE、DELETE，不能回退CREATE、DROP。
2.2 使用COMMIT提交
保证整个事务操作完整可靠。
2.3 使用保留点
创建占位符
SQL Sever
SAVE TRANSACTION delete1；
MySQL
SAVEPOINT delete1；
回退到保留点
SQL Sever
ROLLBACK TRANSACTION delete1；
MySQL
ROLLBACK TO delete1；
第21课、使用游标
1.游标
是一个存储在DBMS服务器上的数据库查询，不是SELECT语句，而是被检索出来的结果集。
作用：
能够标记游标为只读，使数据能读取，但不能更新和删除。
能控制可以执行的定向操作。
规定范围，使游标对创建它的特定/所有请求可访问。
指示DBMS对检索出的数据进行复制，使数据在游标打开和访问期间不变化。
2.使用游标
先声明，再使用，结束使用后关闭或释放。
2.1创建游标
创建一个游标来检索没有电邮地址的所有顾客
DECLARE CustCursor CURSOR
FOR
SELECT * FROM Customers
WHERE cust_email IS NULL
2.2使用游标
打开游标，同时执行了查询
OPEN CURSOR CustCursor
访问游标数据
FETCH指出要检索哪些行，从何处检索它们以及将它们放于何处（如变量名）。
2.3关闭游标
CLOSE CustCursor
再次使用它时不需要再声明，只需要OPEN它即可。
第22课、高级SQL特性
1.约束
关系数据库存储分解为多个表的数据，每个表存储相应的数据。利用键来建立从一个表到另一个表的引用。引用完整性(referential integrity)由此而来。
1.1主键
主键是一种特殊的约束，用来保证一列(或一组列)的值是唯一的，而且永不改动。
1.2外键
外键是表中的一列，其值必须在另一表的主键中。外键是保证引用完整性的极其重要部分。
1.3唯一约束
用来保证一列(或一组列)中的数据是唯一的。
与主键不同，唯一约束不能用来定义外键，表可以包含多个唯一约束，但每个表只允许一个主键。
1.4检查约束
用来保证一列(或一组列)中的数据满足一些指定条件。
2.索引
索引用来排序数据以加快搜索和排序操作的速度。
但降低了数据插入、修改和删除的性能。所以需要经常更新的内容不适合作为索引。
3.触发器
触发器是特殊的存储过程，它在特定的数据库活动发生时自动执行。
触发器可以与特定表上的INSERT、UPDATE、DELETE操作相关联。
4.数据库安全
利用管理机制授予或限制对数据的访问。
