hbase数据分布第五周知识点——邓慧斐

视图

#创建视图:逻辑上的虚拟表
CREATE VIEW v_author(编号,姓名)
AS
SELECT * FROM author WITH CHECK OPTION;
#查询视图
SELECT * FROM v_author;
#创建多表视图(自己写)
CREATE VIEW v_a_b(作者名字,博客标题,博客内容)
AS
SELECT a.author_name,b.title,b.content FROM author a LEFT JOIN blog b ON a.id=b.author_id; 
SELECT * FROM v_a_b;
#修改视图:没有就创建,有就替换
CREATE OR REPLACE VIEW v_blog(编号,标题,内容,作者编号) 
AS SELECT * FROM blog
WITH CHECK OPTION;
#修改视图的数据->修改基表数据
UPDATE v_blog SET 内容 = '321' WHERE 编号 = 10;
#部分数据创建视图
CREATE OR REPLACE VIEW v_blog_1(编号,标题,内容,作者编号)
AS 
SELECT * FROM blog WHERE author_id = 1
WITH CHECK OPTION
#with check option:where条件约束
INSERT INTO v_blog_1 (编号,标题,内容,作者编号)VALUES(12,'123','123',1);

触发器

DELIMITER $
CREATE TRIGGER ins_stu
AFTER INSERT ON student
FOR EACH ROW
BEGIN 
INSERT INTO cj(stu_id,sti_name)
VALUES(new.stuid,new.username)

END $

事务

USE test;

CREATE TABLE runoob (id INT(5))ENGINE = INNODB;

SELECT * FROM runoob ;

#开启事务
BEGIN;
#插入语句
INSERT INTO runoob(id)VALUES(5),(6);
#提交事务
COMMIT;

#开启事务
BEGIN;
#插入语句
INSERT INTO runoob(id)VALUES(7),(8);
#事务回滚
ROLLBACK;

存储过程

USE test;
#创建存储过程
DELIMITER $
CREATE PROCEDURE testa()
BEGIN 
	SELECT * FROM  student;
	SELECT * FROM  cj;
END $

#调用存储过程
CALL testa();


#创建存储过程
DELIMITER $
CREATE PROCEDURE testb()
BEGIN 
	#声明变量类型
	DECLARE uname VARCHAR(32) DEFAULT '';
	#给uname变量赋值
	SET uname = 'xiaoxiao';
	#
	SELECT username INTO uname FROM student WHERE stuid = 6;
	#查询uname
	SELECT uname;
END $
#调用存储过程
CALL testb();

#创建存储过程
DELIMITER $
CREATE PROCEDURE testc()
BEGIN
	BEGIN 
		#声明变量类型
		DECLARE uname VARCHAR(32) DEFAULT '';
		#给uname变量赋值
		SET uname = 'xiaoxiao';
		#将查询变量赋值给uname
		SELECT username INTO uname FROM student WHERE stuid = 6;
		#查询uname
		SELECT uname;
	END ;
	BEGIN 
		#声明变量类型
		DECLARE uname VARCHAR(32) DEFAULT '';
		#给uname变量赋值
		SET uname = 'xiaoxiao';
		#将查询变量赋值给uname
		SELECT username INTO uname FROM student WHERE stuid = 7;
		#查询uname
		SELECT uname;
	END ;
END $
#调用存储过程
CALL testc();