MYSQL事务

1.数据库引擎

你能用的数据库引擎取决于mysql在安装的时候是如何被编译的。
要添加一个新的引擎，就必须重新编译MYSQL。
在缺省情况下，MYSQL支持三个引擎：ISAM、MYISAM和HEAP。
另外两种类型INNODB和BERKLEY（BDB），也常常可以使用。

ISAM
　　ISAM是一个定义明确且历经时间考验的数据表格管理方法，它在设计之时就考虑到数据库被查询的次数要远大于更新的次数。因此，ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。ISAM的两个主要不足之处在于，它不支持事务处理，也不能够容错：如果你的硬盘崩溃了，那么数据文件就无法恢复了。如果你正在把ISAM用在关键任务应用程序里，那就必须经常备份你所有的实时数据，通过其复制特性，MYSQL能够支持这样的备份应用程序。
MYISAM
　　MYISAM是MYSQL的ISAM扩展格式和缺省的数据库引擎。除了提供ISAM里所没有的索引和字段管理的功能，MYISAM还使用一种表格锁定的机制，来优化多个并发的读写操作。其代价是你需要经常运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间。MYISAM还有一些有用的扩展，例如用来修复数据库文件的MYISAMCHK工具和用来恢复浪费空间的MYISAMPACK工具。
　　MYISAM强调了快速读取操作，这可能就是为什么MYSQL受到了WEB开发如此青睐的主要原因：在WEB开发中你所进行的大量数据操作都是读取操作。所以，大多数虚拟主机提供商和INTERNET平台提供商只允许使用MYISAM格式。
HEAP
　　HEAP允许只驻留在内存里的临时表格。驻留在内存里让HEAP要比ISAM和MYISAM都快，但是它所管理的数据是不稳定的，而且如果在关机之前没有进行保存，那么所有的数据都会丢失。在数据行被删除的时候，HEAP也不会浪费大量的空间。HEAP表格在你需要使用SELECT表达式来选择和操控数据的时候非常有用。要记住，在用完表格之后就删除表格。
INNODB和BERKLEYDB
　　INNODB和BERKLEYDB（BDB）数据库引擎都是造就MYSQL灵活性的技术的直接产品，这项技术就是MYSQL++ API。在使用MYSQL的时候，你所面对的每一个挑战几乎都源于ISAM和MYISAM数据库引擎不支持事务处理也不支持外来键。尽管要比ISAM和MYISAM引擎慢很多，但是INNODB和BDB包括了对事务处理和外来键的支持，这两点都是前两个引擎所没有的。如前所述，如果你的设计需要这些特性中的一者或者两者，那你就要被迫使用后两个引擎中的一个了。

2.事务 多个增删改操作合并成一个整体，是数据库执行操作的最小单元

1、原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。

2、一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到。

3、隔离性（ISOLATION）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。

4、持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

设置不允许自动提交:每次执行增删改则开启事务，直到rollback/commit事务才结束
SELECT @@autocommit; -- @@表示系统变量  @表示用户自定义变量 

3.练习

1.定义过程 传入 sex  得到该性别下 的平均年龄 最大 最小 年龄 

-- BEGIN
--  
--    DECLARE avgage INT(10);
--    DECLARE maxage INT(10);
--  	 DECLARE minage INT(10);
--  
--    SELECT AVG(sage),MAX(sage),MIN(sage) INTO avgage,maxage,minage FROM student WHERE sex = ssex; 
--  
--    SELECT avgage,maxage,minage;	
-- END//;
-- DROP  PROCEDURE myproc1;
CALL myproc1("man");

2.定义过程 传入 id  用输出参数的方式 得到 name  sex  age

-- DELIMITER//
-- CREATE PROCEDURE myproc2(stuid INT)
-- BEGIN
--    -- 定义变量  必须写在过程最开始的部分
--    DECLARE stname VARCHAR(20);
--    DECLARE stsex VARCHAR(10);
-- 	 DECLARE stsage INT;
--    -- 查询语句中 给变量赋值 查询条件使用参数 赋值查询时结果必须只有一个
--    SELECT sname,sex,sage INTO stname,stsex,stsage FROM student WHERE sid = stuid; 
--    -- 显示
--    SELECT stname,stsex,stsage;	
-- END//;
-- DROP  PROCEDURE myproc2;
CALL myproc2(2);

3.建立学生表(stuid name sex age score classid) 班级表(classid,classname,teachername)（学生与班级之间有主外键关联以班级id为参数 输出班级人数，男生人数，女生人数

MySQL

-- DELIMITER//
-- CREATE PROCEDURE myproc3(ccid INT)
-- BEGIN
--    -- 定义变量  必须写在过程最开始的部分
--    DECLARE total INT;
--    DECLARE woman INT;
-- 	 DECLARE man INT;
--    -- 查询语句中 给变量赋值 查询条件使用参数 赋值查询时结果必须只有一个
--    SELECT count(*) into man from student where sex = 'man' and cid=ccid;
-- 	 SELECT count(*) into woman from student where sex = 'woman' and cid=ccid;
-- 	 SELECT count(*) into total from student where cid=ccid;
--    -- 显示
--    SELECT total,man,woman;	
-- END//;
-- DROP  PROCEDURE myproc3;
CALL myproc3(2);

Java引用

import java.sql.*;

public class Test6 {
    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://127.0.0.1:3306/hc";
            Connection conn = DriverManager.getConnection(url, "root", "");

            //访问当前数据库下的存储过程 （输入 输出参数）
            CallableStatement callableStatement =  conn.prepareCall("CALL myproc3(?);");

            callableStatement.setInt(1,2);
            //执行过程
            callableStatement.execute();

            //获取输出参数的结果


            ResultSet rs = callableStatement.getResultSet();
            while(rs.next()) {
                System.out.println(rs.getString(1) + "\t" + rs.getInt(2)+ "\t" + rs.getInt(3));
            }

            System.out.println(callableStatement);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }


    }
}

4.以id为参数 执行对学员年龄的修改，男性年龄+2 女性年龄+1

-- DELIMITER//
-- CREATE PROCEDURE myproc4 (stid INT)
-- BEGIN
--    -- case 
-- 	 DECLARE stsex INT;
-- 	 DECLARE stage INT;
-- 	 SELECT sex,sage into stsex,stage from student where sid=stid;
--    SET @level_ =stage;
--    CASE 
--      WHEN stsex='man' THEN SET @result=stage+2;
-- 		 WHEN stsex='woman' THEN SET @result=stage+1;
--    END CASE;   
--    SELECT @result;
-- END//
-- 
-- DROP PROCEDURE myproc4;
CALL myproc4(1);

5.游标+循环 执行对所有学员年龄的修改，男性年龄+2 女性年龄+1

DELIMITER//
CREATE PROCEDURE myproc5()
BEGIN
   -- 存储过程中操作查询结果 需要将结果定义成游标，通过循环去访问游标

   DECLARE stage INT;
   DECLARE stsex VARCHAR(20) DEFAULT '';  
   DECLARE str VARCHAR(100) DEFAULT '';
   DECLARE flag INT DEFAULT 0; -- 标记游标是否结束
  
  
   -- 定义游标
   DECLARE myc CURSOR
   FOR 
   SELECT sex,sage FROM student;
   -- 设定游标访问结束之后的操作
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;
   
   OPEN myc;-- 打开游标
   myloop:LOOP -- 循环过程中 将结果的每一行抓取出来，
    FETCH myc INTO stsex,stage;
    
    SET str =CONCAT(str,stsex,'-',stage,' '); 
   CASE 
     WHEN stsex='man' THEN SET str =CONCAT(str,stsex,'-',stage+2,' ');
 		 WHEN stsex='woman' THEN SET str =CONCAT(str,stsex,'-',stage+1,' ');
    END CASE;   
    
    IF flag = 1 THEN
       LEAVE myloop;
    END IF;
   END LOOP;
 
   SELECT str;
END//;

-- DROP PROCEDURE myproc5;
-- CALL myproc5();

6.编写方法  以班级编号为参数  返回该班级人数

-- DELIMITER//
-- CREATE FUNCTION getName(stcid INT) RETURNS VARCHAR(20)
-- BEGIN
--     DECLARE c INT;	
--     SELECT count(*) INTO c FROM student WHERE cid = stcid;	
--   	
--     RETURN c;
-- END//;
-- DROP FUNCTION getName;
-- 
-- 
-- -- 调用函数
SELECT getName(1);