《sql必知必会》------day6

第 18 课 使用视图

视图

     视图是虚拟的表，本身不包含数据，视图只包含使用时动态检索数据的查询。Microsoft Access 不支持视图,MySQL 从版本 5 起开始支持视图,SQLite 仅支持只读视图。视图不能索引，也不能有关联的触发器或默认值。视图提供了一种封装 SELECT 语句的层次，可用来简化数据处理，重新格式化或保护基础数据。
如下面查询：

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 是一个视图

创建视图

     用 CREATE VIEW 语句来创建，只能用于创建不存在的视图。删除视图 DROP VIEW viewname;

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 就是上面的视图

重新格式化检索出的数据,不必在每次需要时执行这种拼接

CREATE VIEW VendorLocations AS
SELECT CONCAT(vend_name，' (' ， vend_country， ')')
AS vend_title
FROM Vendors;

过滤不想要的数据,过滤没有电子邮件地址的顾客

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

 
 

第 19 课 使用存储过程

    存储过程就是为以后使用而保存的一条或多条 SQL 语句。可将其视为批文件，虽然它们的作用不仅限于批处理。Microsoft Access 和 SQLite 不支持存储过程，MySQL 5 已经支持存储过程。存储过程有三个主要的好处，即简单、安全、高性能。
 
 

第 20 课 管理事务处理

概念

     利用 COMMIT 和 ROLLBACK 语句管理事务处理。事务处理是一种机制，用来管理必须成批执行的 SQL 操作，保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们要么完全执行，要么完全不执行（除非明确指示）。如果没有错误发生，整组语句提交给（写到）数据库表；如果发生错误，则进行回退（撤销），将数据库恢复到某个已知且安全的状态。
   事务（transaction）：一组 SQL 语句。
    回退（rollback）：撤销指定 SQL 语句的过程。不能回退 SELECT语句（SELECT 语句回退也没有必要），也不能回退 CREATE 或 DROP 操作。 事务处理中可以使用这些语句，但进行回退时，这些操作也不撤销。
    提交（commit）：将未存储的 SQL 语句结果写入数据库表。
   保留点（savepoint）：事务处理中设置的临时占位符（placeholder），可以对它发布回退（与回退整个事务处理不同）。
 
    管理事务的关键在于将 SQL 语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退。在 SQL Server中明确标识事务处理块的开始和结束：

BEGIN TRANSACTION
...
COMMIT TRANSACTION

  

使用ROLLBACK

DELETE FROM Orders;
ROLLBACK;
#出故障不能删除才会回退

  

使用COMMIT

mysql中：
		START TRANSACTION;
		DELETE from vend;#存在的表
		DELETE from vend_cop;#不存在的表
		COMMIT;
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。要回退到本例给出的保留点，在 SQL Server 中使用：ROLLBACK TRANSACTION delete1。在 MariaDB、 MySQL 和 Oracle 中，如下进行：ROLLBACK TO delete1。
vend表：

		create table vend(
		vend_id int auto_increment primary key,
		vend_price int DEFAULT 22,
		vend_name char(10) unique,
		vend_a int ZEROFILL)

  
mysql：

第一次运行：
START TRANSACTION;
INSERT INTO vend(vend_name,vend_a)
VALUES('hujin', 122);
SAVEPOINT vend_first;
INSERT INTO vend(vend_name,vend_a)
VALUES('hujin', 122);
IF @@ERROR <> 0 ROLLBACK TO vend_first;
COMMIT; 
第二次运行：
START TRANSACTION;
INSERT INTO vend(vend_name,vend_a)
VALUES('hujin1', 122);
SAVEPOINT vend_first;
INSERT INTO vend(vend_name,vend_a)
VALUES('hujin1', 122);
IF @@ERROR <> 0 ROLLBACK TO vend_first;
COMMIT; 
第三次运行：
START TRANSACTION;
INSERT INTO vend(vend_name,vend_a)
VALUES('hujin11', 122);
SAVEPOINT vend_first;
INSERT INTO vend(vend_name,vend_a)
VALUES('hujin11', 122);
IF @@ERROR <> 0 ROLLBACK TO vend_first;
COMMIT; 

     可以看到vend_id是相差3，每次运行的第二个INSERT是有错误的，则回滚到第一次的INSERT去执行INSERT,这样才会出现vend_id是相差3。
  
完整的 SQL Server 例子：

BEGIN TRANSACTION;
INSERT INTO Customers(cust_id, cust_name)
VALUES('1000000010', 'Toys Emporium');
SAVE TRANSACTION StartOrder;
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20100,'2001/12/1','1000000010');
IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20100, 1, 'BR01', 100, 5.49);
IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity,item_price)
VALUES(20100, 2, 'BR03', 100, 10.99);
IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;
COMMIT TRANSACTION;

第 21 课 使用游标

概念

    结果集（result set）:SQL 查询所检索出的结果。使用 SELECT 语句，没有办法得到第一行、下一行或前 10 行。需要在检索出来的行中前进或后退一行或多行，这就是游标（cursor）的用途所在。
    游标（cursor）是一个存储在 DBMS 服务器上的数据库查询，它不是一条 SELECT 语句，而是被该语句检索出来的结果集。Microsoft Access不支持游标,SQLite 支持的游标称为步骤（step）.
 
 

创建游标

       使用 DECLARE 语句创建游标，DECLARE 命名游标，并定义相应的 SELECT 语句，根据需要带 WHERE 和其他子句。

#其实就是函数
create procedure p1()
begin
    declare id int;
    declare name varchar(15);
    -- 声明游标
    declare mc cursor
    for 
    select * from class;
    -- 打开游标
    open mc;
    -- 获取结果
    fetch mc into id,name;
    -- 这里是为了显示获取结果
    select id,name;
    -- 关闭游标
    close mc;
    
end;

《sql必知必会》------day6
《sql必知必会》------day5
《sql必知必会》------day4
《sql必知必会》------day3
《sql必知必会》------day2
《sql必知必会》------day1