¶一、创建表

¶1、简单示例如下：

CREATE TABLE customers
(
    cust_id int NOT NULL AUTO_INCREMENT,
    cust_name  char(50) NOT NULL,
    cust_address  char(50) NULL,
    cust_city char(50) NULL,
    cust_state char(50) NULL,
    cust_zip char(50) NULL,
    cust_country char(50) NULL,
    cust_contact char(50) NULL,
    cust_email char(50) NULL,
    PRIMARY KEY(cust_id)
) ENGINE = InnoDB;

新表的名字，在关键字create table之后给出
表列的名字和定义，用逗号分隔
使用PRIMARY KEY()指出主键
使用ENGINE = InnoDB指出使用的引擎

¶2、IF NOT EXISTS

如果你仅想在一个表不存在则创建它，应该在表名后给出IF NOT EXISTS。

这样不做检查已有表的模式是否与你打算创建的表模式相匹配。它只是查看表名是否存在，并且仅在表名不存在时创建它。

¶3、NULL

默认为NULL，可以设定为NOT NULL

允许NULL值的列也允许在插入行时不给出该列的值。

¶4、主键

主键值必须唯一。

主键中只能使用不允许NULL值的列。允许值为NULL的列不能作为唯一标识。

如果主键使用单个列，则它的值必须唯一，
如果使用多个列，则这些列的组合值必须唯一。

主键可以在创建表时定义，也可以在创建表之后定义。

¶5、AUTO_INCREMENT

AUTO_INCREMENT告诉MySQL，本列每当增加一行时，自动增量。

每个表只允许一个AUTO_INCREMENT列，而且它必须被索引

INSERT插入一行时若给定一个值（只要没使用过，是唯一的）该值会覆盖自动增量生成的值，后续的增量将开始使用该手工插入的值。

last_insert_id()

使用last_insert_id()获取最后一次自增的值！

1	`SELECT last_insert_id()；`

¶6、指定默认值

建表时可以指定默认值，使用DEFAULT

默认值只能是常量，不能是函数或其他！

注意：一般不使用NULL值，而使用默认值！

CREATE TABLE customers
(
    cust_id int NOT NULL AUTO_INCREMENT,
    quantity int NOT NULL DEFAULT 1,
    PRIMARY KEY(cust_id)
) ENGINE = InnoDB;

¶6、三种引擎

引擎类型可以混用。不同的表可以根据需要的功能使用不同的引擎.

外键不能跨引擎：混用引擎有一个大缺陷，外键不能跨引擎，即使用一个引擎的表不能引用具有使用不同引擎的表的外键。

InnoDB：可靠的事务处理引擎，不支持全文本搜索
MEMORY：功能上等同于MyISAM，但由于数据存储在内存中，速度很快
MyISAM：性能极高的引擎，支持全文本搜索，但不支持事务的处理。

¶二、更新表

使用 ALTER TABLE语句！

¶1、增加列

1 2	`ALTER TABLE vendors ADD vend_phone CHAR(20);`

¶2、删除列

1 2	`ALTER TABLE vendors DROP vend_phone;`

¶3、定义外键

1
2
3

ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_orders
FOREING KEY(order_num) REFERENCES orders(order_num);

¶4、复杂的表结构更改步骤

用新的列布局创建一个新表
使用INSERT SELECT语句从旧表复制数据到新表。如果有必要，可使用转换函数和计算字段
检验包含所需数据的新表
重命名旧表(如果确定，可以删除它)
用旧表原来的名字重新命名新表
根据需要，重新创建触发器、存储过程、索引和外键。

¶三、删除表

使用DROP TALBE删除一个表！

1	`DROP TABLE 表名;`

¶四、重命名表

使用RENAME TABLE 新表名 TO 旧表名，可以有多个重命名

RENAME TABLE backup_customers TO customers;

RENAME TABLE backup_customers TO customers,
             backup_vendors TO vendors,
             backup_products TO products;

¶五、视图

¶1、视图

视图是虚拟的表，视图本身不包含数据。

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

视图是个啥？

简单来说就是针对SELECT一系列操作的集合。。。为了之后操作的简单而引入！

注意：视图返回的数据是从其他表中检索出来的，在添加或更加这些表中的数据时，视图将返回改变过的数据。

¶2、为什么使用视图

算是优点吧！

重用SQL语句
简化复杂SQL的操作
使用表的组成部分而不是整个表
保护数据。可以给用户手续表的特定部分的访问权限而不是整个表的访问权限。
更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

在视图创建之后，可以用与表基本相同的方式利用它们。可以对视图执行 SELECT 操作，过滤和排序数据，将视图联结到其他视图或表，甚至能添加和更新数据！

性能问题：

如果你用多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会造成性能下降得很厉害。

¶3、视图的规则和限制

视图名字必须唯一(和表名一样，不能给视图取与别的视图或表名相同的名字)
对于可以创建的视图数目没有限制
为了创建视图，必须具有一足够的访问权限，这些限制通常由数据库管理人员授予。
视图可以嵌套，即可以利用从其他视图中检索出来的数据的查询来构造一个视图
ORDER BY可以用在视图中，但如果从该视图检索数据的select语句中也含有order by，那么该视图的order by会被覆盖
视图不能索引，也不能有关联的触发器或默认值
视图可以和表一起使用。例如，编写一条联结表和视图的select语句。

¶4、使用视图

视图用CREATE VIEW语句创建
使用show create view viewname查看创建视图的语句
使用drop view viewname来删除视图
更新视图：先drop再create，也可以直接使用create or replace view。

一次编写，到处运行？！！

¶4.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 = 'TNT2';

¶4.2 重新格式化检索出的数据

CREATE VIEW vendorlocations AS
select Contact(RTrim(vend_name)), '(', RTrim(vend_country),')')
from vendors
order by vend_name;

¶4.3 过滤不想要的数据

视图中的WHERE会和检索中的WHERE实行AND操作！

CREATE VIEW customeremaillist AS
SELECT cust_id, cust_name, cust_email
FROM customers
WEHRE cust_email IS NOT NULL;

¶4.4 使用视图与计算字段

CREATE VIEW orderitemsexpanded AS
SELECT order_num,
       prod_id,
       quantity,
       item_price,
       quantity*item_price AS expanded_price
FROM orderitems;

¶4.5 更新视图

通常，视图是可更新的，即可以对它们进行INSERT、UPDATE和DELETE。

更新一个视图将更新其基表。如果你对视图增加或删除，实际上是对基表进行增加和删除。

但是，并非所有的视图都是可更新的。基本上可以说，如果MySQL不能正确地确定被更新的基数据，则不允许更新。

注意：视图主要用于数据检索，而不用于更新。

即如果视图定义中有以下操作，则不能进行视图的更新：

分组(GROUP BY 或者HAVING)
联结
子查询
并
聚集函数(Min()、Max()、Count()、Sum()等)
DISTINCT
导出(计算)列

¶六、存储过程

存储过程，简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。

类似其他语言的函数，一次声明，到处调用！

¶1、存储过程的优缺点

优点

通过把处理封装在容易使用的单元中，简化复杂操作。
由于不要求反复建立一系列处理步骤，保证了数据的完整性。如果所有开发人员和应用程序都使用同一存储过程，则使用的代码都是相同的。(减少错误)
简化对变动的管理。如果表名、列名或业务逻辑有变化，只需要修改存储过程的代码。使用它的人员甚至不需要知道这些变化。(安全性)
提高性能。存储过程比单独的SQL语句要快。
存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写共鞥更强更灵活的代码。

缺点

存储过程比SQL语句复杂得多
并不是所有人都有创建存储过程的安全访问权限。许多数据库管理员限制存储过程的创建权限，允许用户使用存储过程，但不允许他们创建存储过程。

¶2、执行存储过程

存储过程名可以理解为函数名，CALL表示简要调用此存储过程！

@param1、@param2.....等等为当前存储过程的参数，可以理解为函数的参数！

CALL 存储过程名(@param1,
              @param2,
              ...
);

-- 实例如下：
CALL productpricing(@pricelow,
                    @pricehigh,
                    @priceaveage
);

¶3、创建存储过程

语法格式：CREATE PROCEDURE 存储过程名（@param1,@param2...） BEGIN ... END;

BEGIN与END中间的就是存储过程的语句！

CREATE PROCEDURE productpricing()
BEGIN
    SELECT Avg(prod_price) AS priceaverage
    FROM products;
END;

¶4、mysql命令行客户机的分隔符

默认的MySQL分隔符为;(和SQL语句一样)，mysql命令行实用程序也使用，作为语句分隔符。如果命令行实用程序要解释存储过程自身内；字符，则它们最终不会成为存储过程的成分，这会使存储过程中的SQL语句出现句法错误。

简单来说就是;被占用了，sql语句就不会正确执行了。

解决办法：

更改默认分隔符;，使用完再进行还原！

除\ 符号外，任何字符都可以用作语句分隔符。

-- 更改分隔符
DELIMITER //
CREATE PRODUCER productpricing()
BEGIN
 SELECT Avg(prod_price) AS priceaverage
  FROM products;
END //
-- 还原分隔符
DELEMITER ;

¶5、删除存储过程

使用IF EXISTS，若不存在也将不会报错！

语法格式：DROP PROCUDURE 存储过程

1
2
3

DROP PROCEDURE 存储过程名;

DROP PROCUDURE 存储过程名 IF EXISTS;

¶6、带参数的存储过程

一般，存储过程并不显示结果，而是把结果返回给你指定的变量（临时保存返回结果）。

参数必须有指定的类型。不能通过一个参数返回多个行和列。

格式：OUT/IN/INOUT 变量名类型

SELECT ... INTO 参数名：即将结果传给该参数！

CREATE PRODUCER productpricing(
    OUT pl DECIMAL(8, 2),
    OUT ph DECIMAL(8, 2),
    OUT pa DECIMAL(8, 2)
)
BEGIN
   SELECT Min(prod_price) 
   INTO pl
   FROM products;
   SELECT Max(prod_price) 
   INTO ph
   FROM products;
   SELECT Avg(prod_price) 
   INTO pa
   FROM products;
END;

参数类型：

OUT：指出相应的参数用来从存储过程传出一个值(返回给调用者)
IN：传递给存储过程
INOUT：对存储过程传入和传出

开始执行存储过程：

这样调用存储过程就会将内部得到的值返回给@开头的三个参数！使用 SELECT @参数名 ...即可查询到当前结果！

变量名：所有MySQL的变量都必须以@开始

CALL productpricing(@pricelow,
                    @pricehigh,
                    @priceaveage
);

SELECT @priceaverage;
SELECT @pricelow, @pricehigh, @priceaveage;

使用IN和OUT参数：

使用了IN自然就得传入一个参数，如下方的20005。

-- 创建存储过程
CREATE PRODUCER ordertotal(
    IN onumber INT,
    OUT ototal DECIMAL(8, 2)
)
BEGIN
    SELECT Sum(item_price*quantity)
    FROM  orderitems
    WHERE order_num = onumber
    INTO ototal;
END;

-- 调用存储过程
CALL orderitems(20005, @total);

-- 查看结果
select @total;

¶7、复杂存储过程

DECLARE：用于定义变量，需要指明类型！

COMMIT：不是必需的，但是如果给出，将在SHOW PROCEDURE STATUS的结果中显示。

IF：条件语句，还支持ELSEIF， ELSE语句，格式：IF THEN ELSEIF ELSE END IF;

-- Name：ordertotal
-- Parameters：onumber = order number
--            taxable = 0 if not taxable, 1 if taxable
--            ototal = order total variable

CREATE PROCEDURE ordertotal(
  IN onumber INT,
  IN taxable BOOLEAN,
  OUT ototal DECIMAL(8, 2)
) COMMIT "obtain order total, optionally adding tex"
BEGIN
  -- Declare variable for total
  DECLARE total DECIMAL(8, 2);
  -- Declare tax percentage
  DECLARE taxrate INT DEFAULT 6;

  -- Get the order total
  SELECT Sum(item_price*quantity)
  FROM orderitems
  WHERE order_num = onumber
 INTO total;
  
  -- Is it taxable?
  IF taxable THEN
    --   Yes, so add taxrate to the total
    SELECT total+(total/100*taxrate) INTO total;
  END IF;
  -- And finally, save to out variable
  SELECT total INTO ototal;
END;

-- 调用存储过程
CALL orderototal(20005, 0, @total);
SELECT @total;

¶8、检查存储过程

可以显示用来创建一个存储过程的 CREATE 语句。

为了获得包括何时、由谁创建等详细信息的存储过程列表，使用 SHOW PROCEDURE STATUS 。

-- 显示创建存储过程的语句
SHOW CREATE PROCEDURE ordertotal;

-- 何时、由谁创建等详细信息
SHOW PROCEDURE STATUS;

-- 使用过滤
SHOW PROCEDURE STATUS LIKE 'ordertotal';