SQL必知必会总结4-第18到22章

《SQL必知必会》万字精华

本文是《SQL必知必会》一书的精华总结，帮助读者快速入门SQL或者MySQL，主要内容包含：

• 数据库基础知识
• 库表的相关操作
• 检索数据的方法
• …

前面的章节请参考：

• SQL必知必会总结1-第1到7章
• SQL必知必会总结2-第8到13章
• SQL必知必会总结3-第14到17章

视图VIEW

什么是视图

视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询。之前的例子：检索订购了某种产品的顾客

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'

现在我们将上面的结果包装成一个名ProductsCustomers的虚拟表，可以得到相同的数据：

SELECT cust_name, cust_contact
FROM ProductsCustomers
WHRE prod_id = 'RGAN01'

ProductsCustomers并不是一个表，只是一个视图，它不包含任何列或者数据，包含的是一个查询。

为什么使用视图

总结以下几点使用视图的原因：

1. 重用SQL语句
2. 简化复杂的SQL操作
3. 使用表的一部分而不是整个表
4. 保护数据。可以授予用户访问表的特定部分数据，而不是整个表的数据
5. 更改数据格式和表示、视图可以返回和底层表的表示和格式不同的数据

笔记：视图本身不包含数据，使用的是从别处检索出来的数据。

视图规则和限制

关于视图创建和使用的一些常见规则和限制：

1. 与表一样，视图命名必须唯一
2. 创建视图的数目没有限制
3. 创建视图必须具有足够的访问权限
4. 视图是可以嵌套的
5. 视图不能索引，也不能有关联的触发器或者默认值

创建视图

1、利用视图简化复杂的联结

CREATE VIEW ProductsCustomers AS  -- 创建视图
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

上面 代码创建了一个名为ProductsCustomers的视图，我们查询一条信息：

SELECT cust_name, cust_contact
FROM ProductsCustomers
WHRE prod_id = 'RGAN01'

2、利用视图重新格式化检索出的数据

CREATE VIEW VendorLocations AS  -- 创建视图
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'  AS vend_title
FROM Vendors;

-- 从视图中检索数据
SELECT *
FROM VendorLocations;

3、使用视图过滤数据

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

-- 检索数据
SELECT *
FROM CustomerEmailList;

4、使用视图和计算字段

CREATE VIEW OrderItemExpanded AS   -- 创建视图
SELECT
	order_num,
	prod_id,
	quantity,
	item_price,
	quantity * item_price AS expanded_price
FROM OrderItems;

-- 使用视图查询数据
SELECT *
FROM OrderItemExpanded
WHERE order_num = 2008;

使用存储过程

什么是存储过程

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

笔记：简答来说，存储过程就是为以后使用而保存的一条或者多条SQL语句。

为什么要使用存储过程

1. 通过把处理封装在一个易用的单元中，可以简化复杂的操作
2. 存储过程保证里数据的一致性，降低出错可能性
3. 简化对变动的关管理。如果表名、列名或者业务逻辑有变化，那么只需要更改存储过程中的代码，使用它的人员无需知道变化
4. 存储过程通常是以编译过的形式进行存储，所以DBMS处理命令所需的工作量少，提高了性能

笔记：总结存储过程的3个优点：简单、安全、高性能

创建存储过程

MySQL中创建存储过程：

CREATE
    [DEFINER = { user | CURRENT_USER }]
　PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
　　Valid SQL routine statement

[begin_label:] BEGIN
　　[statement_list]
　　　　……
END [end_label]

MYSQL 存储过程中的关键语法

声明语句结束符，可以自定义:

DELIMITER $$
或
DELIMITER //

声明存储过程:

CREATE PROCEDURE demo_in_parameter(IN p_in int)

存储过程开始和结束符号:

BEGIN .... END

变量赋值:

SET @p_in=1

变量定义:

DECLARE l_int int unsigned default 40000;

创建MySQL存储过程、存储函数:

CREATE procedure 存储过程名(参数)

存储过程体:

CREATE function 存储函数名(参数)

创建一个实际的存储过程：

mysql> delimiter $$　　-- 将语句的结束符号从分号;临时改为两个$$(可以是自定义)
mysql> CREATE PROCEDURE delete_matches(IN p_playerno INTEGER)
    -> BEGIN
    -> 　　DELETE FROM MATCHES
    ->    WHERE playerno = p_playerno;
    -> END$$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter;　　-- 将语句的结束符号恢复为分号

执行存储过程

EXECUTE AddNewProduct('JS01',
                      'Stuffed Eiffel Tower',
                      9.83,
                      'Plush stuffed toy with the text La Tour Tower'
                     )

• 关键词是EXECUTE，后面紧跟的是存储过程的名字
• 名字后面有4个参数
• 作用：这个存储过程将行添加到Products表中，并将传入的属性赋给相应的列

管理事务处理

这一章介绍的是MySQL中事务的相关知识点，包含什么是事务处理，怎样利用COMMIT 和 ROLLBACK语句管理事务处理

事务处理

事务Transaction，一个最小的、不可再分的工作单元，通常一个事务对应一个完整的业务。InnoDB引擎是支持事务的，MyISAM不支持事务。事务是针对数据库中DML数据操作语言的。

事务处理（transaction processing）是一种机制，用来管理必须成批执行的SQL操作。利用事务处理，可以保证一组操作不会中途停止，要么完全执行，要么完全不执行，来维护数据库的完整性。

• 在MySQL中，一条SQL语句就是一个事务。
• 一个完整的业务需要大量的DML（insert、update、delete）语句来共同完成。只有DML数据操作语句才有事务。
• 事务保证一组SQL语句要么全部执行成功，要么全部失败，以此来维护数据库的完整性。
• 如果没有发生错误，整个语句提交到数据库表中；如果发生错误，则进行回退（撤销），将数据库恢复到某个已知且安全的状态

栗子：银行转账业务

比如在两个表中，A（原有400）给B（原有200）转200块钱，包含两个过程：

• A转出200，B转进200
• 只有当两个过程全部完成才算真正的执行了一个完整的事务过程。

update user set fee=200 where id=1;     # 语句1
-- 由于断网、安全限制、超出磁盘空间等不可控制原因，下面的语句可能无法成功执行
update user set fee=400 where id=2;     # 语句2

• 语句1的成功执行，并不能将底层数据库中的第一个账户的数据进行修改，只是单纯地记录操作，记录在内存中完成
• 第二条语句成功执行之后，和底层数据库文件中的数据完成同步
• 若第二条数据执行失败，清空所有的历史记录

事务相关术语

事务处理相关的术语：

1. 事务transaction：一组SQL语句。只能处理DML语句：insert、update、delete语句
2. 回退rollback：指撤销指定SQL的语句
3. 提交commit：指将未存储的SQL语句结果写入到数据库表；只有进行了commit操作，数据才会从内存中写入磁盘中
4. 保留点savepoint：指事务处理过程中设置的临时占位符plaveholder，可以对它发布回退（与整个事务回退处理不同）；保留点越多越好：越多的话，能够更加灵活地处理回退问题

CREATE DATABASE IF NOT EXISTS employees;    -- 创建数据库
USE employees;

CREATE TABLE `employees`.`account` (   -- 数据库中创建表account
  `id` BIGINT (11) NOT NULL AUTO_INCREMENT,
  `p_name` VARCHAR (4),
  `p_money` DECIMAL (10, 2) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)  -- 设置主键
) ;
INSERT INTO `employees`.`account` (`id`, `p_name`, `p_money`) VALUES ('1', 'tim', '200');    -- 插入两条记录
INSERT INTO `employees`.`account` (`id`, `p_name`, `p_money`) VALUES ('2', 'bill', '200');

START TRANSACTION;   -- 开启事务
SELECT p_money FROM account WHERE p_name="tim";    -- 三条语句必须完整执行
UPDATE account SET p_money=p_money-100 WHERE p_name="tim";
UPDATE account SET p_money=p_money+100 WHERE p_name="bill";
COMMIT TRANSACTION;   -- 显式提交

事务四大特性

事务具有四大特点，简称为ACID：

• 原子性Atomicity：一个事务中的语句，要么全部成功，要么全部失败。不存在只执行了部分的情况。
• 一致性Consistency：在事务开始之前或者结束之后，必须保持数据库的一致性。比如上面的栗子中，A减掉200，那么相应的，B一定要加上200。否则数据库中的数据不一致。
• 隔离性Isolation：当多个用户并发访问数据库，操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。用户的操作之间存在独立性。事务A和B之间具有隔离性。
• 持久性Durability：事务一旦被提交，对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。事务的成功，是硬盘数据上的更改，不仅是内存上的变化。持久性是事务的保证，是事务结束的标志。

隔离级别

隔离级别	脏读(Dirty Read)	不可重复读（NonRepeatable Read）	幻读（Phantom Read）	加锁读Locked Read
未提交读（Read uncommitted）	Y	Y	Y	N
已提交读（Read committed）	N	Y	Y	N
可重复读（Repeatable read）	N	N	Y	N
可串行化（Serializable ）	N	N	N	Y

• 脏读：未提交读，事务中修改即使没有提交，对其他会话也是可见的，可以读取到未被提交的数据。脏读会导致很多的问题，较少使用
• 提交读：不可重复读，该级别保证事务如果没有成功执行（commit），事务中的操作不会被其他会话可见。解决了脏读问题，但是会对其他session产生两次不一样的读取结果
• 幻读：会话T1事务中执行一次查询，然后会话T2新插入一行记录，这行记录恰好可以满足T1所使用的查询的条件。然后T1又使用相同 的查询再次对表进行检索，但却看到了事务T2刚才插入的新行。这个新行就称为“幻像”。

查看隔离级别

-- 查看系统隔离级别：
select @@global.tx_isolation;

-- 查看当前会话隔离级别
select @@tx_isolation;

-- 设置当前会话隔离级别
SET session TRANSACTION ISOLATION LEVEL serializable;   -- serializable 级别

-- 设置全局系统隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  -- READ UNCOMMITTED 可读未提交级别

---

事务开始和结束标志

开始标志：任何一条DML语句的执行，标志事务开始

结束的标志分为两种：成功结束的标识和失败结束的标识

1、成功结束的标志

• commit：提交
• 将所有的DML语句的操作历史记录和底层硬盘中的数据进行了同步。
• 只有事务成功执行，硬盘中的数据才会进行修改更新。

2、 失败结束的标识

• rollback：回滚
• 将所有的DML语句的操作记录进行全部清空。

使用游标

本章节中讲解的是什么是游标，以及如何使用游标。

什么是游标

SQL检索操作返回一组称为结果集的行，这组返回的行都是与SQL语句相匹配的行（零行或者多行）。

简单地使用SELECT语句，没有办法得到第一行、下一行或者前10行的数据。但是实际中，我们需要在检索出来的行中前进或者后退一行甚至多行，这时候便可以使用游标。

游标是一个存储在DBMS服务器上的数据库存查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。

使用游标

使用游标的几个步骤：

• declare：在使用游标之前，必须先进行声明
• open：一旦声明了游标，就必须打开游标
• 对于填有数据的游标，根据需要取出检索的各行
• close：在结束的时候，必须关闭游标；有的DBMS还需要释放游标

创建游标

DECLARE关键词来定义和命名游标

DECLARE CustCursor CURSORVB   -- 声明游标
FOR
SELECT * FROM Customers
WHERE cust_email IS NULL;

使用游标

1、OPEN CURSOR语句来打开游标

OPEN CURSOR CustCursor   -- 打开游标

2、打开游标之后我们可以使用FETCH语句来访问游标数据了。FETCH指出要检索哪些行？从什么地方开始检索？将它们放于何处？

关闭游标

关闭游标的关键词是close。

游标一旦关闭，如果不再次打开，将无法使用；第二次使用的时候，不需要声明，直接open即可

CLOSE Custcursor

Python操作游标

下面的内容是个人增加部分，讲解的是如何通过Python的第三方库pymysql操作游标。

连接数据库

使用的是pymysql模块，需要先进行导入

import pymysql

• host：主机名或者IP地址
• port：默认是3306
• user：用户名
• passwd：user账户登录mysql的密码
• db：创建的数据库
• charset：防止中文出错，编码格式设置为charset="utf8"

使用模块的connect()方法进行连接

import pymysql
conn = pymysql.connect(host=“localhost”,  # 主机名
                       port=3306,  # 端口
                       user="root",  # 用户名
                       passwd="123456",   # 密码
                       db="bookdb",   # 数据库名字
                       charset="utf8")  # 指定字符集

操作数据库

Python建立了和数据库的连接，实际上就是建立了一个pymysql.connect()的实例对象，或者称之为连接对象。

Python就是通过连接对象和数据库进行对话。pymysql.connect()实例对象的方法有：

• commit：提交数据
• rollback：如果有权限，取消当前的操作，否则会报错
• cursor([cursorclass])：返回连接的游标对象，执行SQL语句
• close()：关闭连接

游标对象cur操作方法

名称	描述
close	关闭游标
execute	执行一条SQL语句，可以带上参数； 执行之后需要conn.commit()，数据库中才会真正建立
fetchone	返回一条语句
fetchall	返回所有的语句
fetchmany	返回many条语句
nextset()	移动到下一个结果

• 插入

import pymysql    # 导入模块
conn = pymysql.connect(host=“localhost”,  # 建立python和数据库的连接
                       port=3306,
                       user="root",
                       passwd="123456",
                       db="bookdb",
                       charset="utf8")
cur = conn.cursor()  # 建立游标对象cur
cur.execute("insert into users (username, passwd, email) values (%s,  %s, %s)", ("python", "123456", "python@gmail.com"))
conn.commit()  # 需要再执行conn.commit()

# 同时执行插入多条信息：多条信息用元组的形式表示
cur.executemany("insert into users (username, passwd, email) values (%s,  %s, %s)", (("python", "123456", "python@gmail.com"), ("java", "456789", "java@gmail.com"), ("php", "123789", "php@gmail.com")))
conn.commit()  # 需要再执行conn.commit()

• 查询

>>cur.execute("select * from users")
>>lines = cur.fetchall()   # 返回所有的查询结果
>>for line in lines:   # 遍历返回所有的结果
    print(line)

# 只想返回一条结果
>>cur.execute("select * from users where id=1")  # 查询id=1

>>cur.execute("select * from users")
>>cur.fetchone()   # 返回一条结果；游标会跟着移动，直到将所有的数据取完
>>cur.fetchone()
>>cur.fetchone()

• 游标移动scroll到指定位置
• 操作存储在内存中的对象时候游标会移动，可以指定游标到某个位置：

>>cur.scroll(2)  # 相对于当前位置移动2个单位
>>cur.fetchone()  # 显示数据
>>cur.scroll(2, "absolute")   # 加上参数，实现“绝对移动”，到第三条

绝对移动的数字不能是负数，相对移动可以是负数

Python的连接对象的游标方法中提供一个参数，将读取到的数据保存成字典形式：

>>cur = conn.cursor(pymysql.cursors.DictCursor)
>>cur.execute("select * from users")
>>conn.commit()  # 需要再执行conn.commit()
>>cur.fetchall()

更新

>>cur.execute("update users set username=s% where id=2", ("mypython"))   # 更新第二条语句
>>cur.execute("select * from users where id=2")
>>conn.commit()  # 需要再执行conn.commit()
>>cur.fetchone()

SQL高级特性

在最后的一章中简单的介绍了SQL的3个高级特性：

• 约束constraint
• 索引index
• 触发器trigger

约束

约束是管理如何插入或者处理数据库数据的规则。DBMS通过在数据库表上施加约束来实施引用完整性。

1、主键

主键是一种特殊的约束，用来保证一列（或者一组列）中的值是唯一的。主键必须满足的4个条件：

• 任意两行的主键值不相同
• 每行都具有一个主键值，即列中不允许NULL值
• 包含主键的列从不修改或者更新
• 主键值不能重复用

-- 方式1
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
);

-- 方式2
ALTER TABLE Vendors
ADD CONSTRAINT PRIMARY KEY (vend_id);

2、外键

外键值表中的一列，其值必须列在另一表的主键中。外键是保证引用完整性的重要部分。

通过订单信息表Orders表中的顾客ID和顾客信息表Customers的顾客ID进行关联。

CREATE TABLE Orders
(
  order_num       INTEGER   NOT NULL PRIMARY KEY,   -- 主键：订单号唯一
  order_date      DATETIME  NOT NULL,
  cust_id         CHAR(10)  NOT REFERENCES    -- 外键：顾客id不唯一，一个顾客可能有多个订单
)

3、唯一约束

唯一约束用来保证一列中的数据是唯一的，可以通过关键词UNIQUE来定义。它和主键的区别在于：

• 表中可以包含多个唯一约束，但是只能有一个主键
• 唯一约束列可以包含NULL值
• 唯一约束列可以修改或者更新
• 唯一约束列的值可以重复使用
• 与主键不同，唯一约束不能用来定义外键

4、检查约束

检查约束用来保证一列（或者一组列）中的数据满足一组指定的条件，常见的用途有：

• 检查最大值或者最小值
• 指定数据的范围
• 只允许特定的值，例如性别字段中只允许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),   -- 设置检查约束，保证大于0
  item_price     MONEY      NOT NULL
)

索引index

索引用来排序数据以加快搜索和排序操作的速度。创建索引前记住几点：

1. 索引改善检索操作的性能，但是降低了数据插入、修改和删除的性能
2. 索引数据可能要占用大量的存储空间
3. 索引用于数据过滤和数据排序
4. 可以在索引中定义多个列
5. 索引必须唯一命名

CREATE INDEX prod_name_ind
ON Products (prod_name);

触发器trigger

触发器(trigger)：监视某种情况，并触发某种操作，它是提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发。

触发器可以与特定表上INSERT、UPDATE、DELETE操作相关联。触发器的常见用途：

• 保证数据一致
• 基于某个表的变动在其他表上执行活动
• 进行额外的验证并根据需要回退数据
• 计算计算列的值或者更新时间戳

触发器创建语法四要素：

1. 监视地点(table)
2. 监视事件(insert/update/delete)
3. 触发时间(after/before)
4. 触发事件(insert/update/delete)

笔记：触发器只能创建在永久表上，而不能对临时表创建触发器

触发器创建的基本语法如下

CREATE TRIGGER trigger_name
AFTER/BEFORE INSERT/UPDATE/DELETER ON table_name
FOR each row   -- 固定语句
begin
	sql语句;
end;