MySQL必知必会——代码3

InnoDB 是一个可靠的事务处理引擎，它不支持全文本搜索；
MEMORY 在功能等同于 MyISAM ，但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）；
MyISAM 是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。

更新表
1.ALTER TABLE 之后给出要更改的表名
2.所做更改的列表
ALTER TABLE vendors
ADD vend_phone CHAR(20) ;
删除刚刚添加的列，可以这样做：
ALTER TABLE vendors
DROP COLUMN vend_phone ;

复杂的表结构更改一般需要手动删除过程，它涉及以下步骤：
1.用新的列布局创建一个新表；
2.使用 INSERT SELECT 语句从旧表复制数据到新表。如果有必要，可使用转换函数和计算字段；
3.检验包含所需数据的新表；
4.重命名旧表（如果确定，可以删除它）；
5.用旧表原来的名字重命名新表；
6.根据需要，重新创建触发器、存储过程、索引和外键。

删除表
DROP TABLE 语句即可：
DROP TABLE customers2 ;

重命名表
RENAME TABLE 语句即可：
RENAME TABLE customers2 TO customers ;

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

视图的规则和限制
1.与表一样，视图必须唯一命名。
2.对于可以创建的视图数目没有限制。
3.为了创建视图，必须具有足够的访问权限。这些限制通常由数据库管理人员授予。
4.图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图。
5.ORDER BY 可以用在视图中，但如果从该视图检索数据 SELECT 中也含有 ORDER BY ，那么该视图中的 ORDER BY 将被覆盖。
6.视图不能索引，也不能有关联的触发器或默认值。
7.视图可以和表一起使用。例如，编写一条联结表和视图的 SELECT语句。

使用视图
1.视图用 CREATE VIEW 语句来创建
2.使用 SHOW CREATE VIEW viewname ；来查看创建视图的语句
3.用 DROP 删除视图，其语法为 DROP VIEW viewname;
4.更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE ORREPLACE VIEW。如果要更新的视图不存在，则第 2 条更新语句会创建一个视图；如果要更新的视图存在，则第 2 条更新语句会替换原有视图。

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 ;

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

更新视图：通常，视图是可更新的
如果视图定义中有以下操作，则不能进行视图的更新
1.分组（使用 GROUP BY 和 HAVING ）；
2.联结；
3.子查询；
4.并；
5.聚集函数（ Min() 、 Count() 、 Sum() 等）；
6.DISTINCT；
7.导出（计算）列

存储过程：为以后的使用而保存的一条或多条MySQL语句的集合。
1.通过把处理封装在容易使用的单元中，简化复杂的操作
2.由于不要求反复建立一系列处理步骤，这保证了数据的完整性
3.简化对变动的管理
4.提高性能
5.存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码
缺陷
1.编写存储过程需要更高的技能，更丰富的经验
2.可能没有创建存储过程的安全访问权限

使用存储过程
执行存储过程：
CALL productpricing(@pricelow,@pricehigh,@priceaverage) ;
创建存储过程：
CREATE PROCEDURE productpricing()
BEGIN
SELECT AVG( prod_price) AS priceaverage
FROM products ;
END ;
使用存储过程
CALL productpricing();
删除存储过程
DROP PROCEDURE productpricing ;

关键字 OUT 指出相应的参数用来从存储过程传出一个值（返回给调用者）
CREATE PROCEDURE 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 ;

MySQL支持IN(传递给存储过程),OUT(从存储过程传出,如这里所用)和INOUT(对存储过程传入和传出)类型的参数。
关键字：DECIMAL
column_name DECIMAL(P,D);
P是表示有效数字数的精度。 P范围为1~65。P的默认值为10
D是表示小数点后的位数。 D的范围是0~30。MySQL要求D小于或等于(<=)P。

CREATE PROCEDURE ordertotall(
IN onumber INT,
IN taxable BOOLEAN,
OUT ototal DECIMAL(8,2)
)
COMMENT 'Obtain order total, optionally adding tax'
BEGIN
	DECLARE total DECIMAL(8,2) ;
	DECLARE taxrate INT DEFAULT 6 ;
	SELECT SUM(item_price*quantity)
	FROM orderitems
	WHERE order_num = onumber
	INTO total ;
	IF taxable THEN
	SELECT total+(total/100*taxrate) INTO total ;
	END IF ;
	SELECT total INTO ototal ;
END ;

DECLARE 语句定义了两个局部变量， 要求指定变量名和数据类型
COMMENT 关键字：如果给出，将在 SHOW PROCEDURE STATUS 的结果中显示

CALL ordertotall(20005,0,@total) ;
SELECT @total ;
检查存储过程
SHOW CREATE PROCEDURE ordertotall

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

创建游标
CREATE PROCEDURE process()
BEGIN
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders ;
END ;

打开和关闭游标
OPEN ordernumbers ;
CLOSE ordernumbers ;

触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句
1.DELETE ；
2.INSERT ；
3.UPDATE

创建触发器
1.唯一的触发器名
2.触发器关联的表,仅支持表 只有表才支持触发器
3.触发器应该响应的活动（ DELETE 、 INSERT 或 UPDATE ）
4.触发器何时执行（处理之前或之后）
CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_numm into @ee;
每个表最多支持6个触发器（每条 INSERT 、 UPDATE和 DELETE 的之前和之后）

删除触发器
DROP TRIGGER neworder ;

INSERT 触发器在 INSERT 语句执行之前或之后执行:
1.在 INSERT 触发器代码内，可引用一个名为 NEW 的虚拟表，访问被插入的行
2.在 BEFORE INSERT 触发器中， NEW 中的值也可以被更新
3.对于 AUTO_INCREMENT 列， NEW 在 INSERT 执行之前包含 0 ，在 INSERT执行之后包含新的自动生成值
INSERT INTO orders(order_date,cust_id)
VALUES(Now(),10001) ;

DELETE触发器
1.在 DELETE 触发器代码内，你可以引用一个名为 OLD 的虚拟表，访问被删除的行
2.OLD 中的值全都是只读的，不能更新

UPDATE触发器
1.在 UPDATE 触发器代码中，你可以引用一个名为 OLD 的虚拟表访问以前（ UPDATE 语句前）的值，引用一个名为 NEW 的虚拟表访问新更新的值
2.在 BEFORE UPDATE 触发器中， NEW 中的值可能也被更新
3.OLD 中的值全都是只读的，不能更新

事务处理:可以用来维护数据库的完整性
保证成批的MySQL操作要么完全执行，要么完全不执行
事务（ transaction ）指一组SQL语句
回退（ rollback ）指撤销指定SQL语句的过程
提交（ commit ）指将未存储的SQL语句结果写入数据库表
保留点（ savepoint ）指事务处理中设置的临时占位符（place-holder），你可以对它发布回退（与回退整个事务处理不同）

使用ROLLBACK:事务处理用来管理 INSERT 、 UPDATE 和DELETE 语句
SELECT *FROM orders;
START TRANSACTION ;
DELETE FROM orders;
SELECT *FROM orders;
ROLLBACK;
SELECT * FROM orders;

如果第一条 DELETE 起作用，但第二条失败，则 DELETE 不会提交
START TRANSACTION ;
DELETE FROM orderitems WHERE order_num = 20010 ;
DELETE FROM orders WHERE order_num = 20010 ;
COMMIT ;

使用保留点
SAVEPOINT delete1 ;
回退到保留点
ROLLBACK TO delete1 ;

更改默认的提交行为:设置 autocommit 为 0 （假）指示MySQL不自动提交更改
SET autocommit = 0;

查看所支持的字符集完整列表
SHOW CHARACTER SET ;
查看支持校对的完整列表
SHOW COLLATION ;
SHOW VARIABLES LIKE ‘character%’ ;
SHOW VARIABLES LIKE ‘collation%’ ;

指定字符集
CREATE TABLE mytable
(
columnn1 INT,
columnn2 VARCHAR(10),
columnn3 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci
)DEFAULT CHARACTER SET hebrew
COLLATE hebrew_general_ci ;

安 全 管 理
访问控制
管理用户
USE mysql;
SELECT user FROM user;
创建用户账号
CREATE USER ben IDENTIFIED BY ‘123465’ ;
重新命名一个用户账号
RENAME USER ben TO bin;
删除用户账号
DROP USER bin ;
查看用户权限
SHOW GRANTS FOR ben ;
设置权限，使用 GRANT 语句
1.要授予的权限
2.被授予访问权限的数据库或表
3.用户名
GRANT SELECT ON test.* TO ben ;
允许ben用户在test.数据库的所有表上使用SELECT
撤销特定的权限
REVOKE SELECT ON crashcourse.* FROM ben ;
更改口令
SET PASSWORD FOR ben = `PASSWORD(‘HELLO’)

GRANT 和 REVOKE 可在几个层次上控制访问权限
1.整个服务器，使用 GRANT ALL 和 REVOKE ALL
2.整个数据库，使用 ON database.*
3.特定的表，使用 ON database.table
4.特定的列
5.特定的存储过程

权限
ALL 除GRANT OPTION外的所有权限
ALTER 使用ALTER TABLE
ALTER ROUTINE 使用ALTER PROCEDURE和DROP PROCEDURE
CREATE 使用CREATE TABLE
CREATE ROUTINE 使用CREATE PROCEDURE
CREATE TEMPORARY TABLES 使用CREATE TEMPORARY TABLE
CREATE USER 使用CREATE USER、DROP USER、RENAME USER和REVOKE ALL PRIVILEGES
CREATE VIEW 使用CREATE VIEW
DELETE 使用DELETE
DROP 使用DROP TABLE
EXECUTE 使用CALL和存储过程
FILE 使用SELECT INTO OUTFILE和LOAD DATA INFILE
GRANT OPTION 使用GRANT和REVOKE
INDEX 使用CREATE INDEX和DROP INDEX
INSERT 使用INSERT
LOCK TABLES 使用LOCK TABLES
PROCESS 使用SHOW FULL PROCESSLIST
RELOAD 使用FLUSH
REPLICATION CLIENT 服务器位置的访问
REPLICATION SLAVE 由复制从属使用
SELECT 使用SELECT
SHOW DATABASES 使用SHOW DATABASES
SHOW VIEW 使用SHOW CREATE VIEW
SHUTDOWN 使用mysqladmin shutdown（用来关闭MySQL）
SUPER 使用CHANGE MASTER、KILL、LOGS、PURGE、MASTER和SET GLOBAL。还允许mysqladmin调试登录
UPDATE 使用UPDATE
USAGE 无访问权限

数据库维护
ANALYZE TABLE ，用来检查表键是否正确
ANALYZE TABLE orders ;
CHECK TABLE 用来针对许多问题对表进行检查
CHECK TABLE orders ;

查看日志文件
1.错误日志。它包含启动和关闭问题以及任意关键错误的细节。此日志通常名为 hostname.err ，位于 data 目录中。此日志名可用–log-error 命令行选项更改。
2.查询日志。它记录所有MySQL活动，在诊断问题时非常有用。此日志文件可能会很快地变得非常大，因此不应该长期使用它。此日志通常名为 hostname.log ，位于 data 目录中。此名字可以用–log 命令行选项更改。
3.二进制日志。它记录更新过数据（或者可能更新过数据）的所有语句。此日志通常名为 hostname-bin ，位于 data 目录内。
4.缓慢查询日志。顾名思义，此日志记录执行缓慢的任何查询。这个日志在确定数据库何处需要优化很有用。此日志通常名为hostname-slow.log ， 位 于 data 目 录 中 。 此 名 字 可 以 用–log-slow-queries 命令行选项更改。