hadoop触发器，回滚，存储过程

hadoop触发器，回滚，存储过程

触发器

 

USE test;

CREATE TABLE IF NOT EXISTS student

(

 username VARCHAR(20),

 PASSWORD VARCHAR(20),

 stuid INT AUTO_INCREMENT PRIMARY KEY,

 birthday DATE

);

DROP TABLE student;

SELECT * FROM student;

#创建学生表

INSERT INTO student(username,PASSWORD,stuid,birthday)VALUE

("王五",111111,1,"2016-08-23"),

("李四",123456,2,"2016-07-23"),

("杨三",123456,3,"2016-08-17"),

("刘五",100001,4,"2016-08-18"),

("黄六",666666,5,"2016-08-23");

 

#创建成绩表

CREATE TABLE IF NOT EXISTS cj

(

 num INT,

 stu_id INT,

 stu_name VARCHAR(50),

 math FLOAT,

 chinese FLOAT,

 english FLOAT

);

#触发器

CREATE TRIGGER ins_stu

AFTER INSERT ON student FOR EACH ROW

INSERT INTO cj(num,stu_id,stu_name,math,chinese,english)

VALUES(1,new.stuid,new.username,88,88,88);

 

#执行触发

INSERT INTO student(username,PASSWORD,birthday)VALUE

('张三','22222','2016-08-23');

 

 

 

存储过程

 

USE test;

#c创建存储过程

DELIMITER $;

CREATE PROCEDURE testa()

BEGIN

 SELECT * FROM student;

 SELECT * FROM cj;

END $

DELIMITER ;

 

#调用存储过程

CALL testa();

 

#创建存储过程

DELIMITER $

CREATE PROCEDURE test2()

BEGIN

 #声明变量类型

 DECLARE un VARCHAR(32) DEFAULT '';

 #给username变量赋值

 SET un='xiaoxiao';

 #将查询结果赋值给un变量

 SELECT username INTO un FROM student WHERE stuid =3;

 #查询un变量,返回

 SELECT un;

END $

DELIMITER ;

#调用存储过程

CALL test2();

 

 

#创建存储过程

DELIMITER $

CREATE PROCEDURE test3()

BEGIN

 BEGIN

  #声明变量类型

  DECLARE un VARCHAR(32) DEFAULT '';

  #给username变量赋值

  SET un='xiaoxiao';

  #将查询结果赋值给un变量

  SELECT username INTO un FROM student WHERE stuid =3;

  #查询un变量,返回

  SELECT un;

 END;

 BEGIN

  #声明变量类型

  DECLARE un VARCHAR(32) DEFAULT '';

  #给username变量赋值

  SET un='xiaoxiao';

  #将查询结果赋值给un变量

  SELECT username INTO un FROM student WHERE stuid =4;

  #查询un变量,返回

  SELECT un;

 END;

END $

DELIMITER ;

#调用存储过程

CALL test3();

 

 

回滚

USE test;

#创建数据库

CREATE TABLE r_t_t(id INT(5));

 

SELECT * FROM r_t_t;

 

#开启事务

BEGIN;

#插入数据

INSERT INTO r_t_t(id) VALUE(5);

INSERT INTO r_t_t(id) VALUE(6);

#提交事务

COMMIT;

 

#开启事务

BEGIN;

#插入数据

INSERT INTO r_t_t(id) VALUE('abc');

INSERT INTO r_t_t(id) VALUE(8);

#提交事务

ROLLBACK;