MySql基础教程二（视图，存储过程，游标，触发器，事务，权限，数据类型）

我的学习资料是《MySQL必知必会》，被称为sql入门经典，PDF版下载点我。
相关链接：
MySql基础教程一（查询，插入，更新，删除，创建）

视图

视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。比如下面的语句:

select cust_name, cust_contact from customers, orders, orderitems where customers.cust_id=orders.cust_id and orders.order_num=orderitems.order_num andorderitems.prod_id='TNT2';

如果使用视图，可以将整个查询包装成一个名为productcustomers的虚拟表，则采用下面的查询即可得到相同的结果：

select cust_name, cust_contact from productcustomers where prod_id='TNT2';

这就是视图的作用，productcustomers是一个视图，作为视图，它不包含表中应该有的任何数据，它包含的是一个sql查询。创建上述视图的方法：

create view productcustomers asselect cust_name, cust_contact, prod_id from customers, orders, orderitemswhere customers.cust_id=orders.cust_id and orderitems.order_num=orders.order_num;

在视图创建之后，可以用与表基本相同的方式利用它们。可以对视图进行select操作，过滤和排序操作，将视图联接到其他视图或表，甚至能添加和更新数据，但是更新一个视图将更新其基表，尽量将视图用于检索，而不是更新。
使用show create view viewname; 来查看视图；用drop删除视图，其语法为drop view viewname;。更新视图时，可以先用drop，再用create，也可以使用create or replace view。

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

存储过程

很多时候，一个完整的操作需要多条语句才能完成，存储过程就是为以后的使用而保存的一条或多条mysql语句的集合。并且运行速度快，可将其视为批文件、函数。

DELIMITER $CREATE PROCEDURE ordertotal(    IN onumber INT,    IN taxable BOOLEAN,    OUT ototal DECIMAL(8,2)) COMMENT 'Obtain order total'BEGIN    -- Declare variable.    DECLARE total DECIMAL(8,2);    DECLARE taxrate INT DEFAULT 6;    SELECT sum(item_price*quantity)    FROM orderitems    WHERE order_num = onumber    INTO ototal;    -- Is this taxable?    IF taxable THEN        SELECT total+(total/100*taxrate) INTO total;    END IF;    SELECT total INTO ototal;END$DELIMITER ;

1，关键字out表明相应的参数用来从存储过程中传出一个值。mysql支持in（传递给存储过程）和out（从存储过程中传出）和inout（传入和传出）。布尔值0表示假，非零表示真。
2，调用该存储过程的语句是：call ordertotal(20005,0,@total); 所有的变量都必须以@开始，在调用时，并不显示任何数据，要想显示结果，则select @total;。
3，存储过程在创建之后，被保存在服务器上以供使用，直到被删除。删除存储过程的命令如下：drop procedure ordertotal;
4，使用show create procedure name查看存储过程。
5，DELIMITER $表示临时修改分割符，因为存储过程中用到了;分号。

游标

使用简单的select语句，没有办法得到第一行、下一行或前10行，也不存在每次一行的处理所有行的简单方法，有时，需要在检索出来的行中前进或后退一行或多行，这时就需要使用游标。
游标是一个存储在mysql服务器上的数据库查询，它不是一条select语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。mysql游标只能用于存储过程，存储过程处理完成后，游标就消失。

CREATE PROCEDURE processorders()BEGIN    -- declare local variables    DECLARE done BOOLEAN DEFAULT 0;    DECLARE o INT;    DECLARE t DECIMAL(8,2);    -- declare the cursor    DECLARE ordernumbers CURSOR    FOR    SELECT order_num FROM orders;    -- declare continue handler    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;    -- createa table to store the results    CREATE TABLE IF NOT EXISTS ordertotals(order_num INT,    total DECIMAL(8,2));    --open the cursor    OPEN ordernumbers;    -- loop through all rows    REPEAT        -- get order number        FETCH ordernumbers INTO o;        -- get the total for this order        CALL ordertotal(o, 1, t);        -- insert order and total into ordertotals        INSERT INTO ordertotals(order_num, total)         VALUES(o, t);    -- end of loop    UNTIL done END REPEAT;    --close the cursor    CLOSE ordernumbers;END;

1，在使用游标之前，必须DECLARE声明定义它。这个过程实际上没有检索数据，它只是定义要使用的select语句。
2，一旦声明之后，必须OPEN打开游标以供使用。这个过程，会执行前面定义的select语句，并存储检索出的数据以供浏览和滚动。
3，游标在打开后，可以使用FETCH语句，分别访问它的每一行，FETCH还将移动游标中的内部行指针，使下一条FETCH语句检索下一行。所以在上例中，FETCH ordernumbers INTO o;表示将检索当前行的order_num到一个名为o的局部声明的变量中。FETCH在REPEAT中，它会反复执行，直到done为真。done为真的条件是：
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; 这条语句定义了一个句柄HANDLER，它在条件出现时被执行，也就是当sqlstate ‘02000’ 出现时，set done = 1。 sqlstate ‘02000’ 表示未找到行，当repeat没有更多的行而不能继续循环时，这个条件就会出现。
注意：用declare定义的局部变量必须在定义任意游标或句柄之前，而句柄必须在游标之后定义。
4，在结束游标使用时，必须关闭游标。

触发器

触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句（或位于BEGIN和END语句之间的一组语句）：DELETE；INSERT；UPDATE。其他的MySQL语句不支持触发器。只有表才支持触发器，视图不支持。

创建触发器需要给出4条信息：
1，唯一的触发器名；
2，触发器关联的表；
3，触发器应该响应的活动（DELETE、INSERT或UPDATE）
4，触发器何时执行（处理前还是后,前是BEFORE 后是AFTER）

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

我们建立的数据库一般都是 InnoDB 数据库，其上建立的表是事务性表，也就是事务安全的。这时，若SQL语句或触发器执行失败，MySQL 会回滚事务，有：
1，如果 BEFORE 触发器执行失败，SQL 无法正确执行。
2，SQL 执行失败时，AFTER 型触发器不会触发。
3，AFTER 类型的触发器执行失败，SQL 会回滚。

假设系统中有两个表：
班级表 class(班级号 classID, 班内学生数 stuCount)
学生表 student(学号 stuID, 所属班级号 classID)
要创建触发器来使班级表中的班内学生数随着学生的添加自动更新，代码如下：

DELIMITER $CREATE TRIGGER tri_stuInsert AFTER INSERT ON student FOR EACH ROWBEGIN    DECLARE c INT;    SET c = (SELECT stuCount FROM class     WHERE classID=NEW.classID);    UPDATE class SET stuCount = c + 1     WHERE classID = NEW.classID;END$DELIMITER ;

NEW 和 OLD用来表示触发器的所在表中，触发了触发器的那一行数据。NEW表示经过处理后的新表，OLD表示处理前的旧表。
另外，OLD 是只读的，不能更新，而 NEW 则可以在触发器中使用 SET 赋值，这样不会再次触发触发器。
下面例子时使用OLD保存将要删除的行到一个存档表中：

CREATE TRIGGER delete_order BEFORE DELETE ON ordersFOR EACH ROWBEGIN      INSERT INTO archive_orders(order_num, order_date, cust_id)    VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);END;

删除触发器：DROP TRIGGER name; 触发器不能更新或覆盖，所以修改触发器只能先删除再创建。
查看触发器：SHOW TRIGGERS [FROM schema_name];

事务

mysql中，并非所有的引擎都支持事务管理，比如常见的引擎myisam和innodb，前者就不支持事务，而后者支持。事务处理是一种机制，用来管理必须成批执行的mysql操作，以保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们或者作为整体执行，或者完全不执行。如果没有错误发生，整组语句提交给数据库表。如果发生错误，则进行回退，已恢复数据库到某个一直且安全的状态。
事务处理的关键词有：
事务（transaction）：指一组sql语句；
回退（rollback）：撤销指定sql语句的过程；
提交（commit）：将未存储的sql语句结果写入数据库表中；
保留点（savepoint）：事务处理中设置的临时占位符，可以对他进行回退，而不是回退整个事务。

select * from ordertotals;start transaction;delete from ordertotals;select * from ordertotals;rollback;select * from ordertotals;

事务的开始：start transaction; 用rollback;命令回退。事务处理用来管理insert、update和delete语句，不能回退select（无意义），create和drop操作。

一般的mysql语句都是直接对数据库表执行和编写的，这就是所谓的隐含提交，也就是提交操作是自动进行的。但是，在事务处理中，必须使用commit语句进行明确的提交：

start transaction;delete from orderitems where order_num = 20010;delete from orders where order_num = 20010;commit;

事务中的语句全部成功时，commit才会成功，如果上边第一条delete起作用，但第二条失败，则commit不会成功。当commit或rollback语句执行后，事务会自动关闭。

对于复杂的事务处理，可能需要部分提交或回退。为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符（保留点）。如果需要回退，可以回退到某个占位符。使用savepoint语句设置保留点： savepoint delete1;，使用rollback回退到保留点：rollback to delete1;。保留点在事务处理完成（执行commit或rollback）之后自动释放。

权限

MySQL用户账号和信息存储在名为mysql的数据库中，在其中的user表记录了用户信息：

USE mysql;SELECT user FROM user;

1，接下来我们创建一个新用户账号，使用CREATE USER语句：CREATE USER gq IDENTIFIED BY '123123';
2，重命名一个用户账号，使用RENAME USER语句：RENAME USER gq TO gq2;
3，为了删除用户账号，使用DROP USER语句：DROP USER gq;
4，新创建的用户没有访问权限，它们只能登陆服务器，但不能看到数据，也不能执行任何数据库操作。查看用户账号的权限，使用：SHOW GRANTS FOR gq;
5，授权：GRANT SELECT,INSERT ON test.* TO gq;上述语句允许用户gq在test .*(test数据库的所有表)上使用SELECT和INSERT。如果是所有权限用ALL。
6，撤销权限使用revoke语句：REVOKE SELECT ON test.* FROM gq;
7，修改用户密码：mysqladmin -u 用户名 -p password 新密码; 输入这个命令后，需要输入用户名的原密码。之后，再FLUSH PRIVILEGES;即可生效。
另一种方法是： SET PASSWORD FOR gq = Password('n3wp@$$wOrd'); 这是以root用户的身份给用gq改密码，如果用户gq自己改自己，就去掉FOR gq。
还有一种方法是更改本节开头说的User表。

MySql数据类型

数值型：

整形：

上面定义的都是有符号的，当然了，也可以加上unsigned关键字，定义成无符号的类型，那么对应的取值范围就要翻翻了，比如：tinyint unsigned的取值范围为0~255。
浮点型：

特别注意浮点型会出现截断问题，对于float(5, 3)类型：
1，插入123.45678，最后查询得到的结果为99.999；
2，插入123.456，最后查询结果为99.999；
3，插入12.34567，最后查询结果为12.346；

字符串型

1，char和varchar最大的区别就在于char不管实际value都会占用n个字符的空间，而varchar只会占用实际字符应该占用的空间+1。
2，超过char和varchar的设置的n后，字符串会被截断。
3，char在存储的时候会截断尾部的空格，varchar和text不会。
4，mysql处理定长比处理变长快很多，而且不能对变长索引。
5，varchar会使用1-3个字节来存储长度，text不会。

日期时间类型

二进制类型

二进制类型可存储任意数据，如图像、多媒体、文档。