MYSQL必知必会 再捋[21-30]（五）

(二十一) 创建和操纵表

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(5) NULL,

Cust_zip char(10) NULL,

Cust_country char(50) NULL,

Cust_contact char(50) NULL

Cust_email char(50) NULL

PRIMARY KEY (cust_id)

）ENGINE = InnoDB;

 

处理现有的表：删除原表，重建。

 

1.1 使用NULL值

NULL值就是没有值或缺值

理解NULL 不要把NULL值与空串相混淆，NULL值是没有值

它不是空串，如果指定‘’（两个单引号，其间没有字符），这在NOT NULL列中是允许，空串是一个有效的值，它不是无值。

1.2 主键在介绍

主键值必须唯一

PRIMARY KEY (order_num, order_item)

主键和NULL值： 主键为其值唯一标识表中每个行的列

主键中只能使用不允许NULL值的列。

允许NULL值的列不能作为唯一标识。

 

1.3 使用AUTO_INCREMENT

Cust_id    int  NOT NULL AUTO_INCREMENT,

AUTO_INCREMENT告诉MySQL，本列每当增加一行时自动增量。每次执行一个INSERT操作时， MySQL自动对该列增量（从而才有这个关键字AUTO_INCREMENT），给该列赋予下一个可用的值 。

 

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

 

确定AUTO_INCREMENT值：

让MYSQL生成（通过自动增量）主键的一个缺点是你不知道这些值都是谁。

如何使用AUTO_INCREMENT列时获得这个值？可使用last_insert_id()函数获得这个值

SELECT last_insert_id()

1.4 指定默认值 DEFAULT

CREATE TABLE orderitems

（

Quantity init    NOT NULL DEFAULT 1,

）

 

默认值：只支持常量，不允许使用函数。

使用默认值而不是NULL值

 

1.6 引擎类型  

为什么要发行多种引擎？

为不同的任务选择正确的引擎能获得良好的功能和灵活性。

默认引擎： MyISAM

ENGINE=

 

几个需要知道的引擎：

InnoDB: 一个可靠的失误处理引擎，它不支持全文本搜索

MEMORY:功能等同于MYISAM,但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）

MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。

 

外键不能跨引擎 混用引擎类型有一个大缺陷，外键（用于强制实施引用完整性）不能跨引擎，即使用一个引擎的表不能引用具有使用不同引擎的表的外键。

 

2 更新表 ALTER TABLE

更改表结构： 需要提供表名 所做更改的列表

ALTER TABLE vendors

ADD vend_phone CHAR(20)

必须指定数据类型

 

ALTER TABLE orderitems

ADD CONSTRINT fk_orderitems_orders

FOREIGN KEY (order_num) REFERNCES orders (order_num)

 

小心使用ALTER TABLE 使用ALTER TABLE要极为小心，应该
在进行改动前做一个完整的备份（模式和数据的备份）。数据库表的更改不能撤销，如果增加了不需要的列，可能不能删除它们。类似地，如果删除了不应该删除的列，可能会丢失该列中的所有数据 .

 

3 删除表

DROP TABLE custoemrs2;

 

4 重命名表

RENAME TABLE customers2 TO customers;

 

对多个表重命名：

RENAME TABLE back_customers TO customers,

Bace_vendors TO vendors,

Backup_products TO products;

 

(二十二)使用视图

1 视图

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

 

2 为什么使用视图

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

性能问题：

因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时所需的任一个检索。如果你用多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能下降得很厉害。因此，在部署使用了大量视图的应用前，应该进行测试。

3 使用视图

创建视图： CREATE VIEW

查看创建视图：SHOW CREATE VIEW viewname

删除视图：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;

 

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

SELECT Concat(RTrim(vend_name),‘(’,RTrim(vend_country), ‘)’) AS vend_title   FROM vendors

ORDER BY vend_name;

使用视图查询：

SELECT * FROM vendorlocations;

 

4 用视图过滤不想要的数据

过滤没有电子邮件地址的客户：

CREATE VIEW customeremaillist AS

SELECT cust_id, cust_name, cust_email

FROM customers

WHERE cust_email IS NOT NULL;

 

视图:

SELECT *

FROM customeremaillist;

 

5 使用视图与计算字段

计算物品的总价格：

SELECT prod_id,

Quantity,

Item_price,

Quantity*item_price as expanded_price

FROM orderitems

WHERE order_num = 2005;

创建视图：

CREATE VIEW orderiemsexpanded AS

SELECT order_num,

Prod_id,

Quantity,

Item_price,

Quantity*item_price as expanded_price

FROM orderitems;

 

使用视图：

SELECT *

FROM orderiemsexpanded

WHERE order_num = 20005;

 

5 更新视图

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

分组 GROUP BY         HAVING

联结

子查询

并

聚集函数（Min(), Count(), Sum()）

DISTINCE                       导出（计算）列

 将视图用于检索   一般，应该将视图用于检索，而不用于更新（INSERT UPDATE DELETE）

 

(二十三)使用存储过程

1 存储过程

为以后的使用而保存的一条或多条MYSQL语句的集合

 

2 为什么要使用存储过程

优点： 简单   明确  高性能

 

3 使用存储过程

Mysql称存储过程的执行为调用

CALL  productpricing(@pricelow,

@pricehigh,

@priceaverage);

 

3.1创建存储过程

CREATE PROCEDURE productpricing()

BEGIN

SELECT  Avg(prod_price) AS priceaverage

FROM products;

END;

 

重新定义分隔符：

DELIMITER 分隔符

 

使用存储过程:

CALL productpricing();

 

3.3 删除存储过程

DROP PROCEDURE productpricing;

DROP PROCEDURE IF EXISTS productpricing

 

3.4 使用参数

CREATE PROCEDURE productpricing(

OUT p1 DECIMAL(8.2),

OUT ph DECIMAL(8.2),

OUT pa DECIMAL(8.2)

)

BEGIN

SELECT Min(prod_price)

INTO p1

FROM products;

SELECT MAX(prod_price)

INOT ph

FROM products;

SELECT Avg(prod_price)

INTO pa

FROM products;

END;

分析： IN 传递给存储过程    OUT 从存储过程传出

INOUT 对存储过程传入和传出

 

参数的数据类型 存储过程的参数允许的数据类型与表中使用
的数据类型相同。

注意，记录集不是允许的类型，因此，不能通过一个参数返回
多个行和列。这就是前面的例子为什么要使用3个参数（和3
条SELECT语句）的原因。

 

使用变量：

CALL productpricing（

@pricelow,

@pricehigh,

@priceaverage

）;

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

调用：

SELECT @priceaverage;

SELECT @priceaverage, @pricelow, @priceaverage;

 

Ordertotal接受订单号并返回该订单的合计：

CREATE PROCEDURE 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 ordertotal(20005, @total);

SELECT @total;

 

CALL ordertotal(20009, @total);

SELECT @total;

3.5 建立智能存储过程 略过

3.6 检查存储过程

为显示用来创建一个存储过程CREATE语句，使用SHOW CREATE  

PROCEDURE语句

 

限制过程状态结果： SHOW PROCEDURE STATUS列出所有存储过程。为限制其输出，可使用LIKE指定一个过滤模式

SHOW PROCEDURE STATUS LIKE ‘ordertotal’;

 

(二十四)游标

1 游标

游标（cursor） 是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

 

游标主要用于交互式引用

只能用于存储过程（和函数）

 

2 使用游标

使用游标涉及几个明确的步骤。
q 1在能够使用游标前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的SELECT语句。
q2 一旦声明后，必须打开游标以供使用。这个过程用前面定义的
SELECT语句把数据实际检索出来。
q3 对于填有数据的游标，根据需要取出（检索）各行。
q4 在结束游标使用时，必须关闭游标

 

2.1 创建游标（用DECLARE）

CREATE PROCEDURE processorders()

BEGIN

DECLARE ordernumbers CURSOR

FOR

SELECT order_num FROM orders;

END;

打开游标：

OPEN ordernumbers;

处理OPEN语句时执行查询，存储检索出的数据以供浏览和滚动。

关闭游标：

CLOSE ordernumbers;

CLOSE释放游标使用的所有内部内存和资源，因此在每个游标不再需要时都应该关闭。

 

隐含关闭 如果你不明确关闭游标， MySQL将会在到达END语
句时自动关闭它。

 

整合：

CREATE PROCEDURE processorders()

BEGIN

     DECLARE ordernumbers CURSOR

     FOR

     SELECT order_num FROM orders;

     OPEN ordernumbers;

     CLOSE ordernumbers;

END;

 

2.3 使用游标数据

可以使用FETCH语句分别访问它的每一行。FETCH指定检索什么数据（所需的列），检索出来的数据存储在什么地方

 

FETCH用来检索当前行的order_num列（将自动从第一行开
始）到一个名为o的局部声明的变量中

CREATE PROCEDURE processorders()

BEGIN

 DECLARE o INT

     DECLARE ordernumbers CURSOR

     FOR

     SELECT order_num FROM orders;

     OPEN ordernumbers;

  FETCH ordernumbers INTO o;

     CLOSE ordernumbers;

END;

 

DECLARE语句的次序 DECLARE语句的发布存在特定的次序。
用DECLARE语句定义的局部变量必须在定义任意游标或句柄
之前定义，而句柄必须在游标之后定义。不遵守此顺序将产
生错误消息

 

(二十五) 触发器

1 触发器

触发器： 当某个表发生更改时自动处理。

触发器是mysql响应一下任意语句而自动执行的一条mysql语句（或位于BEGIN和END语句之间的一组语句）：

DELETE        INSERT            UPDATE

其他sql语句不支持触发器

 

2 创建触发器（CREATE TRIGGER）

步骤：

1 唯一的触发器名（保持每个数据库的触发器名唯一）

2 触发器关联的表

3 触发器应该响应的活动

4 触发器何时执行（处理之前或之后）

 

CREATE TRIGGER newproduct

AFTER INSERT ON products   #操作发生之后

FOR EACH ROW  #插入行执行

SELECT ‘Product added’

 

仅支持表 ，只有表才支持触发器，视图不支持（临时表也不支持）

 

触发器按每个表每个事件每次地定义，每个表每个事件每次只允许
一个触发器。因此，每个表最多支持6个触发器（每条INSERT、 UPDATE和DELETE的之前和之后）。单一触发器不能与多个事件或多个表关联，所以，如果你需要一个对INSERT和UPDATE操作执行的触发器，则应该定义两个触发器。

 

触发器失败 如果BEFORE触发器失败，则MySQL将不执行请
求的操作。此外，如果BEFORE触发器或语句本身失败， MySQL
将不执行AFTER触发器（如果有的话）。

 

3 删除触发器

触发器不能更新或覆盖，（修改：先删除在创建）

DROP TRIGGER newproduct;

 

4 使用触发器

4.1 INSERT触发器

NSERT触发器在INSERT语句执行之前或之后执行。需要知道以下几点：
q 在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行；
q 在BEFORE INSERT触发器中， NEW中的值也可以被更新（允许更改被插入的值）；
q 对于AUTO_INCREMENT列， NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值。

 

CREATE TRIGGER neworder

AFTER INSERT ON orders

FOR EACH ROW SELECT NEW.order_num;

插入测试：

INSERT TNTO orders(order_date, cust_id)

VALUES(Now(), 10001)

 

BEFORE或AFTER？ 通常，将BEFORE用于数据验证和净化（目
的是保证插入表中的数据确实是需要的数据）。UPDATE也适用.

4.2 DELETE 触发器

DELETE触发器在DELETE语句执行之前或之后执行。需要知道以下两点：
q 在DELETE触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行；
q OLD中的值全都是只读的，不能更新

 

CREATE TRIGGER deleteorder

BEFORE DELETE ON orders

FOR EACH ROW

BEGIN

INSERT INTO archieve_orders(order_num, order_date, cust_id)

VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);

END;

多语句触发器：

使用BEGIN END块的好处是触发器能容纳多条SQL语句（在BEGIN END块中一条挨着一条）。

 

 

 

 

4.3 UPDATE触发器

UPDATE触发器在UPDATE语句执行之前或之后执行。需要知道以下几点：
q 在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟表访问以前（UPDATE语句前）的值，引用一个名为NEW的虚拟表访问新更新的值；
q 在BEFORE UPDATE触发器中， NEW中的值可能也被更新（允许更改将要用于UPDATE语句中的值）；
q OLD中的值全都是只读的，不能更新

 

CREATE TRIGGER updatevendor

BEFORE UPDATE ON vendors

FOR EACH ROW

SET NEW.vend_state=Upper(NEW.vend_state)

 

使用触发器时需要记住的重点:
q 1 与其他DBMS相比， MySQL 5中支持的触发器相当初级。未来的MySQL版本中有一些改进和增强触发器支持的计划。
q 2 创建触发器可能需要特殊的安全访问权限，但是，触发器的执行是自动的。如果INSERT、 UPDATE或DELETE语句能够执行，则相关的触发器也能执行。
q 3 应该用触发器来保证数据的一致性（大小写、格式等）。在触发器中执行这种类型的处理的优点是它总是进行这种处理，而且是透
明地进行，与客户机应用无关。
q3  触发器的一种非常有意义的使用是创建审计跟踪。使用触发器，
把更改（如果需要，甚至还有之前和之后的状态）记录到另一个
表非常容易。
q 4 遗憾的是， MySQL触发器中不支持CALL语句。这表示不能从触发器内调用存储过程。所需的存储过程代码需要复制到触发器内。

 

(二十六)管理事务处理

1 事务处理

术语：

事务： （transaction）指一组SQL语句

回退：（rollback）指撤销指定SQL语句的过程

提交：（commit）指将为存储的SQL语句结果写入数据库表

保留点：（savepoint）指事务处理中设置的临时占位符（place-holder）,你可以对它发布回退（与回退整个事务处理不同）

 

MyISAM：不支持事务处理

事务处理：可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。

事务处理是一种机制，用来管理必须成批执行的MYSQL操作，以保证数据库不包含不完整的操作结果。

利用事务处理，可以保证一组操作不会中途停止，它们或者作为整体执行，或者完全不执行（除非明确指示）。

如果没有错误发生，整组语句提交给（写到）数据库表。

如果发生错误，则进行回退（撤销）以恢复数据库到某个已知且安全的状态。

 

2.1 使用ROLLBACK

SELECT * FROM ordertotals;

START TRANSACTION;

DELETE FROM ordertotals;

SELECT * FROM ordertotals;

ROLLBACK;

SELECT *FROM ordertotals;

分析： ROLLBACK只能在一个事务处理内使用（在执行一条START
TRANSACTION命令之后）。

 

哪些语句可以回退？

事务处理用来管理INSERT、 UPDATE和DELETE语句。你不能回退SELECT语句。（这样做也没有什么意义。）你不能回退CREATE或DROP操作。事务处理块中可以使用这两条语句，但如果你执行回退，它们不会被撤销。

2.2 使用COMMIT

一般的MySQL语句都是直接针对数据库表执行和编写的。这就是
所谓的隐含提交（implicit commit），即提交（写或保存）操作是自动进行的。

但是，在事务处理块中，提交不会隐含地进行。为进行明确的提交，
使用COMMIT语句

 

START TRANSACTION;

DELETE FROM orderitems WHERE order_num = 20010;

DELETE FROM orderitems WHERE order_num = 20010;

COMMIT;

 

隐含事务关闭： 当COMMIT或ROLLBACK语句执行后，事务会自动关闭（将来的更改会隐含提交）

 

2.3 使用保留点

SAVEPOINT deletel;

ROLLBACK TO deletel;

保留点越多越好

 

 

释放保留点 保留点在事务处理完成（执行一条ROLLBACK或
COMMIT）后自动释放。自MySQL 5以来，也可以用RELEASE
SAVEPOINT明确地释放保留点

 

2.4 更改默认的提交行为

SET autocommit = 0

autocommit标志决定是否自动提交更改，不管有没有COMMIT
语句。设置autocommit为0（假）指示MySQL不自动提交更改
（直到autocommit被设置为真为止）。

 

标志为连接专用 autocommit标志是针对每个连接而不是服务器的。

 

（二十七）全球化和本地化

SHOW CHARSET SET; 支持的字符集完整列表

SHOW COLLATION;  查看支持校对的完整列表

一次区分大小写（_cs）

一次不区分大小写（_ci）

 

(二十八)安全管理

USE mysql;

SELECT user FROM user;

CREATE USER ben IDENTIFIED BY ‘p@$$word’

RENAME USER ben TO bforta;

DROP USER bforta;

 

设置访问权限

SHOW GRANS FOR bforta;

 

GRANT SELECT ON crashcourse.* TO bforta;

 

SHOW GRANTS FOR bforta;

 

撤销使用REVOKE

REVOKE SELECT ON crashcourse.* TO bforta;

 

2.4 更改口令

SET PASSWORD FOR btorta = Password(‘’)

 

(二十九)数据库维护

ANALYZE TABLE order;

CHECK TABLE orders, orderitems;

 

 

(三十) 改善性能
1 MySQL是用一系列的默认设置预先配置的，从这些设置开始通常是很好的。但过一段时间后你可能需要调整内存分配、缓冲区大小等。（为查看当前设置，可使用 SHOW VARIABLES;和 SHOW
STATUS;。）
2 MySQL一个多用户多线程的DBMS，换言之，它经常同时执行多个任务。如果这些任务中的某一个执行缓慢，则所有请求都会执
行缓慢。如果你遇到显著的性能不良，可使用SHOW PROCESSLIST
显示所有活动进程（以及它们的线程ID和执行时间）。你还可以用
KILL命令终结某个特定的进程（使用这个命令需要作为管理员登
录）。
3 总是有不止一种方法编写同一条SELECT语句。 应该试验联结、并、子查询等，找出最佳的方法。
4 使用EXPLAIN语句让MySQL解释它将如何执行一条SELECT语句。
5 一般来说，存储过程执行得比一条一条地执行其中的各条MySQL语句快。应该总是使用正确的数据类型。
6 决不要检索比需求还要多的数据。换言之，不要用SELECT *（除
非你真正需要每个列）。
7 有的操作（包括INSERT）支持一个可选的DELAYED关键字，如果使用它，将把控制立即返回给调用程序，并且一旦有可能就实际
执行该操作。
8 在导入数据时，应该关闭自动提交。你可能还想删除索引（包括
FULLTEXT索引），然后在导入完成后再重建它们。
9 必须索引数据库表以改善数据检索的性能。确定索引什么不是一
件微不足道的任务，需要分析使用的SELECT语句以找出重复的
WHERE和ORDER BY子句。如果一个简单的WHERE子句返回结果所花的时间太长，则可以断定其中使用的列（或几个列）就是需要索引的对象。
10 你的SELECT语句中有一系列复杂的OR条件吗？通过使用多条
SELECT语句和连接它们的UNION语句，你能看到极大的性能改
进。
11 索引改善数据检索的性能，但损害数据插入、删除和更新的性能。
如果你有一些表，它们收集数据且不经常被搜索，则在有必要之
前不要索引它们。（索引可根据需要添加和删除。）
12 LIKE很慢。一般来说，最好是使用FULLTEXT而不是LIKE。
q 最重要的规则就是，每条规则在某些条件下都会被打破

 

完结

继续努力