十七、创建和操作表
创建表
SQL中一般有两种创建表的方法：
- 多数DBMS都具有交互式创建和管理数据库表的工具
- 表也可以直接使用SQL语句来操控；通过create table来实现
CREATE TABLE Products -- 创建表
(
prod_id CHAR(10) NOT NULL,
vend_id CHAR(10) NOT NULL,
prod_name CHAR(254) NOT NULL,
prod_price DECIMAL(8,2) NOT NULL,
prod_desc VARCHAR(1000) NULL
);
上面代码的解释：
- 表名紧跟CREATE TABLE 关键字
- 列名在圆括号中，各个列之间通过逗号隔开
- 每列的定义以列名开始，后紧跟数据类型 ，是否允许控制等
- 整条语句是以分号结束
使用NULL值
NULL值就是没有值或者缺失值。每个表中的列要么是NULL列，要么是NOT NULL列。
主键是其值唯一标识表中每一行的列。只有不允许NULL值的列可作为主键，允许NULL值的列不能作为唯一标识。
笔记：NULL是默认设置。如果不指定NOT NULL，则认为指定的就是NULL。
注意NULL和空字符串的区别：
- NULL值没有值，不是空字符串
- 空字符串值一个有效的值，它不是无值
- NULL值使用关键字NULL而不是空字符串指定
指定默认值
SQL中创建表的时候可以指定通过关键字DEFAULT来指定：
CREATE TABLE OrderItems
(
order_num INTEGER NOT NULL,
order_item INTEGER NOT NULL,
prod_id CHAR(10) NOT NULL,
quantity INTEGER NOT NULL DEFAULT 1, -- 指定默认值
item_price DECIMAL(8,2) NOT NULL
);
默认值一般是用于日期或者时间戳列。
更新表
更新表中的数据使用关键词ALTER TABLE。
ALTER TABLE Vendors
ADD vend_phone CHAR(20);
删除表
删除整个表而不是其内容，使用DROP TABLE。
DROP TABLE CustCopy; -- 执行这条语句将会永久删除该表
重命名表
通过关键字RENAME来实现
RENAME TABLE old_name TO new_name;
旧表(old_name)必须存在，新表(new_name)必须不存在。 如果新表new_name存在，则该语句将失败。
十八、视图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并不是一个表，只是一个视图，它不包含任何列或者数据，包含的是一个查询。
为什么使用视图
总结以下几点使用视图的原因：
- 重用SQL语句
- 简化复杂的SQL操作
- 使用表的一部分而不是整个表
- 保护数据。可以授予用户访问表的特定部分数据，而不是整个表的数据
- 更改数据格式和表示、视图可以返回和底层表的表示和格式不同的数据
笔记：视图本身不包含数据，使用的是从别处检索出来的数据。
视图规则和限制
关于视图创建和使用的一些常见规则和限制：
- 与表一样，视图命名必须唯一
- 创建视图的数目没有限制
- 创建视图必须具有足够的访问权限
- 视图是可以嵌套的
- 视图不能索引，也不能有关联的触发器或者默认值
创建视图
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语句。
为什么要使用存储过程
- 通过把处理封装在一个易用的单元中，可以简化复杂的操作
- 存储过程保证里数据的一致性，降低出错可能性
- 简化对变动的关管理。如果表名、列名或者业务逻辑有变化，那么只需要更改存储过程中的代码，使用它的人员无需知道变化
- 存储过程通常是以编译过的形式进行存储，所以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表中，并将传入的属性赋给相应的列
