SQL创建和操纵表、使用视图、使用存储过程

创建和操作表

创建表

SQL有两种创建表的方法：
a、多数DBMS都具有交互式创建和管理数据库表的工具
b、表也可以直接用SQL语句操作
用程序创建表，可以使用SQL的CREATE TABLE语句。实际上，使用交互式工具时就是在使用SQL语句。

表创建基础

利用CREATE TABLE创建表，必须给出下列信息：
a、新表的名字，在关键字CREATE TABLE之后给出
b、表列的名字和定义，用逗号分隔
c、有的DBMS还要求指定表的位置

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
);

在创建新的表时，指定的表名必须不存在，否则会出错。

使用NULL值

在插入或更新行时，该列要么是NULL列，要么是NOT NULL列。

指定默认值

SQL允许指定默认值，在插入行时如果不给出值，DBMS将自动采用默认值。
默认值在CREATE TABLE语句的列定义中用关键字DEFAULT指定。

CREATE TABLE OrderItems
(
	order_num			INTERGER			NOT NULL,
	order_item			INTERGER			NOT NULL,
	prod_id				CHAR(10)			NOT NULL,
	quantity			INTERGER			NOT NULL		DEFAULT 1,
	item_price			DECIMAL(8, 2)	    NOT NULL
);

在这个例子中，quantity这一列增加了DEFAULT 1，它指示DBMS，如果不给出数量则使用数量1。
默认值经常用于日期或时间戳列。例如，通过指定引用系统日期的函数或变量，将系统日期用作默认日期。MySQL用户指定DEFAULT CURRENT_DATE()。

更新表

ALTER TABLE语句。使用时需要考虑以下几点：
a、理想情况下，不要在表中包含数据时对其进行更新。
b、允许给现有的表增加列，不过对所增加列的数据类型（以及NULL和DEFAULT的使用）有所限制。
c、许多DBMS不允许删除或更改表中的列。
d、多数DBMS允许重新命名表中的列。
e、许多DBMS限制对已经填有数据的列进行更改，对未填有数据的列几乎没有限制。
使用ALTER TABLE更改表结构，必须给出下面的信息：
1、在ALTER TABLE之后给出要更改的表名（该表必须存在，否则将出错）。
2、列出要做哪些更改。

ALTER TABLE Vendors
ADD vend_phone CHAR(20);

这条语句给Vendors表增加一个名为vend_phone的列，其数据类型为CHAR。
更改或删除列、增加约束或增加键，这些操作也使用类似的语法：

ALTER TABLE Vendors
DROP COLUMN vend_phone;

复杂的表结构更改一般需要手动删除过程，步骤如下：
a、用新的列布局创建一个新表
b、使用INSERT SELECT语句从旧表复制数据到新表。有必要的话，可以使用转换函数和计算字段
c、检验包含所需数据的新表
d、重命名旧表（如果确定，可以删除它）
e、用旧表原来的名字重命名新表
f、根据需要，重新创建触发器、存储过程、索引和外键
使用ALTER TABLE要小心，应该在进行改动前做完整的备份（表结构和数据的北备份）！

删除表

删除表（删除整个表而不是其内容）非常简单，使用DROP TABLE语句即可。

DROP TABLE CustCopy;

删除表没有确认，也不能撤销，执行这条语句将永久删除该表。

重命名表

alter table 旧表名 rename [to | as] 新表名;
rename table 旧表名 to 新表名;

四种方式！因为第一条语句可以省略to/as，也可以只是使用to，或只是使用as。

小结

CREATE TABLE用来创建新表，ALTER TABLE用来更改表列（或其他诸如约束或索引等对象），而DROP TABLE用来完整地删除一个表。这些语句使用应该小心，并且一个在备份后使用。

使用视图

视图

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

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是一个视图，它不包含任何列或数据，包含的是一个查询（与上面以正确联结表的查询相同）。

为什么使用视图

a、重用SQL语句
b、简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道其基本查询细节
c、使用表的一部分而不是整个表
d、保护数据。可以授予用户访问表的特定部分的权限，而不是整个表的访问权限
e、更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据

视图的规则和限制

a、与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）
b、对于可以创建的视图数目没有限制
c、创建视图，必须具有足够的访问权限。这些权限通常由数据库管理人员授予
d、视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造试图
e、许多DBMS禁止在视图查询中使用ORDER BY子句
f、有些DBMS要求对返回的所有列进行命名，如果列是计算字段，则需要使用别名
g、视图不能索引，也不能有关联的触发器或默认值
h、有些DBMS把视图作为只读的查询，这表示可以从视图检索数据，但不能将数据写回底层表
i、有些DBMS允许创建这样的视图，它不能导致行不再属于视图的插入或更新

创建视图

CREATE VIEW只能用于创建不存在的视图。
注：视图重命名
删除视图，可以使用DROP语句，其语法是DROP VIEW viewname;
覆盖或更新视图，必须先删除它，然后再重新创建。

利用视图简化复杂的联结

一个最常见的视图是隐藏复杂的SQL，这通常涉及联结。

CREATE VIEW ProductsCustomers AS
SELECT cust_name, cust_contact, cust_id
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
 AND OrderItems.order_num = Orders.order_num;

这条语句创建一个名为ProductsCustomers的视图，它联结三个表，返回已订购了任意产品的所有顾客的列表。如果执行SELECT * FORM ProductsCustomers，将列出订购了任意产品的顾客。

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

假设经常需要某个格式的结果。我们不必在每次需要时执行拼接，而是创建一个视图，使用它即可。

CREATE VIEW VendorLocations AS
SELECT Concat(RTRIM(vend_name), '(', RTRIM(vend_country), ')')
	   AS vend_title
FROM Vendors;

用视图过滤不想要的数据

视图对于应用普通的WHERE子句也很有用。例如，可以定义CustomersEMailList视图，过滤没有电子邮件地址的顾客：

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

使用视图和计算字段

在简化计算字段的使用上，视图也特别又用。以下语句检索某个订单中的物品，计算每种物品的总价格：

CREATE VIEW OrderItemsExpanded AS
SELECT order_num,
       quantity,
       item_price,
       quantity*iitem_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;

使用存储过程

存储过程就是为以后使用而保存的一条或多条SQL语句。可将其视为批文件，虽然它们的作用不仅限于批处理。

为什么要使用存储过程

a、通过把处理封装在一个易用的单元中，可以简化复杂的操作
b、由于不要求反复建立一系列处理步骤，因而保证了数据的一致性。如果所有开发人员和应用程序都使用同一存储过程，则所使用的代码都是相同的（防止错误）
c、简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，那么只需要更改存储过程的代码（安全性）
d、因为存储过程通常以编译过的形式存储，所以DBMS处理命令所需的工作量少，提高了性能
e、存在一些只能在单个请求中的SQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码
使用存储过程有三个主要的好处：简单、安全、高性能。

执行存储过程

EXECUTE语句。EXECUTE接受存储过程名和需要传递给它的任何参数。

EXECUTE AddNewProduct( 'JTS01',
                       'Stuffed Eiffel Tower;,
                       6.49,
                       'Plush stuffed toy with the text LaTour Eiffel in red white and blue' );

这里执行一个名为AddNewProduct的存储过程，将一个新产品添加到Products表中。
但是！在Products表中还有另一个需要值得列prod_id列，它是这个表的主键。为什么这个值不作为属性传递给存储过程？要保证恰当地生成此ID，最好是使生成此ID的过程自动化（而不是依赖于最终用户的输入）。这也是这个例子使用存储过程的原因。以下是存储过程所完成的工作：
a、验证传递的数据，保证所有4个参数都有值
b、生成用作主键的唯一ID
c、将新产品插入Products表，在合适的列中存储生成的主键和传递的数据
这就是存储过程执行的基本形式。对于具体的DBMS，可能包括以下的执行选择：
a、参数可选，具有不提供参数时的默认值
b、不按次序给出参数，以“参数=值”的方式给出参数值
c、输出参数，允许存储过程在正执行的应用程序中更新所用的参数
d、用SELECT语句检索数据
e、返回代码，允许存储过程返回一个值到正在执行的应用程序

创建存储过程

参考：https://www.cnblogs.com/chen-lhx/p/5602146.html

DELIMITER //
CREATE PROCEDURE MialingListCount()
BEGIN
DECLARE cnt INTEGER;
SELECT COUNT(*) AS cnt
FROM Customers
WHERE NOT cust_email IS NULL;
END //
DELIMITER ;

MySQL默认以";"为分隔符，如果没有声明分割符，则编译器会把存储过程当成SQL语句进行处理，因此编译过程会报错，所以要事先用“DELIMITER //”声明当前段分隔符，让编译器把两个"//"之间的内容当做存储过程的代码，不会执行这些代码；“DELIMITER ;”的意为把分隔符还原。

调用存储过程

CALL MialingListCount();

MySQL 存储过程参数如果不显式指定“in”、“out”、“inout”，则默认为“in”。习惯上，对于是“in” 的参数，我们都不会显式指定。

1. MySQL 存储过程名字后面的“()”是必须的，即使没有一个参数，也需要“()。
2. MySQL 存储过程参数，不能在参数名称前加“@”，如：“@a int”。下面的创建存储过程语法在 MySQL 中是错误的（在 SQL Server 中是正确的）。 MySQL 存储过程中的变量，不需要在变量名字前加“@”，虽然 MySQL 客户端用户变量要加个“@”。
3. MySQL 存储过程的参数不能指定默认值。
4. MySQL 存储过程不需要在 procedure body 前面加 “as”。而 SQL Server 存储过程必须加 “as” 关键字。
5. 如果 MySQL 存储过程中包含多条 MySQL 语句，则需要 begin end 关键字。
6. MySQL 存储过程中的每条语句的末尾，都要加上分号 “;” 。
7. 不能在 MySQL 存储过程中使用 “return” 关键字。
8. 调用 MySQL 存储过程时候，需要在过程名字后面加“()”，即使没有一个参数，也需要“()”。
9. 因为 MySQL 存储过程参数没有默认值，所以在调用 MySQL 存储过程时候，不能省略参数。可以用 null 来替代。

删除存储过程

1、DROP PROCEDURE IF EXISTS MialingListCount;
2、DROP PROCEDURE MialingListCount;

查看存储过程

参考：https://www.cnblogs.com/zejin2008/p/7686585.html
1、查询数据库中的存储过程：

 select `name` from mysql.proc where db = 'xx' and `type` = 'PROCEDURE'   //存储过程

2、查看存储过程的创建代码

a、SHOW CREATE PROCEDURE MialingListCount;
b、SHOW PROCEDURE STATUS like 'MialingListCount';

修改存储过程

参考：https://blog.csdn.net/zuoyouzouzou/article/details/86703242