视图 View
逻辑上的虚拟表
#创建视图CREATE VIEW s_t(编号,姓名,性别,出生日期)ASSELECT * FROM student WITH CHECK OPTION#查询视图SELECT * FROM s_t#部分视图创建数据CREATE VIEW s_t_2(编号,姓名,性别,出生日期)ASSELECT * FROM student WHERE NAME='卫庄' WITH CHECK OPTION #约束条件 name=‘卫庄’ 下面那条张三会报错INSERT INTO s_t_2(编号,姓名,性别,出生日期) VALUES(0052,'卫庄','nan','-02-02')INSERT INTO s_t_2(编号,姓名,性别,出生日期) VALUES(0052,'张三','nan','-02-02')#创建多表视图CREATE VIEW v_s_e(姓名,语文,英语,数学)ASSELECT s.name,e.chinese,e.english,e.math FROM student s LEFT JOIN exam e ON s.id=e.id#修改视图，若是没有则创建，有就替换CREATE OR REPLACE VIEW s_1(编号,姓名,性别,出生日期)AS SELECT * FROM student WITH CHECK OPTION#修改视图的数据->修改基表数据UPDATE s_1 SET 姓名 = '改改' WHERE 编号 =0001;
事务
CREATE TABLE ab( id INT(5) ) ENGINE=INNODBSELECT * FROM ab#开启事务BEGIN;#插入语句INSERT INTO ab VALUES(3),(4)#提交事务COMMIT;#开启事务BEGIN;#插入语句INSERT INTO ab VALUES('7'),(8)#提交事务COMMIT;#回滚ROLLBACK;
触发器
#触发器CREATE TRIGGER ins_stuAFTER INSERT ON student FOR EACH ROWINSERT INTO cj(NUMBER,stu_id,stu_name,math,chinese,english)VALUES(1,new.stuid,new.username,87,98,78)#执行触发INSERT INTO student (username,PASSWORD,birthday)VALUES('张三','111111','-08-23')
存储过程
#创建存储过程DELIMITER $CREATE PROCEDURE testa()BEGINSELECT * FROM student;SELECT * FROM cj;END $DELIMITER ;#调用存储过程CALL testa()#创建存储过程DELIMITER $CREATE PROCEDURE testb()BEGIN#声明变量类型DECLARE un VARCHAR(32) DEFAULT '';#给username变量赋值SET un='xiaoxaio';#将查询结果赋值给un变量SELECT username INTO un FROM student WHERE stuid=3;#查询un变量，返回SELECT un;END $DELIMITER ;#调用存储过程CALL testb()#变量的作用范围DELIMITER $CREATE PROCEDURE testc()BEGINBEGIN#声明变量类型DECLARE un VARCHAR(32) DEFAULT '';#给username变量赋值SET un='xiaoxaio';#将查询结果赋值给un变量SELECT username INTO un FROM student WHERE stuid=3;#查询un变量，返回SELECT un;END;BEGIN#声明变量类型DECLARE un VARCHAR(32) DEFAULT '';#给username变量赋值SET un='xiaoxaio';#将查询结果赋值给un变量SELECT username INTO un FROM student WHERE stuid=3;#查询un变量，返回SELECT un;END;END $DELIMITER ;CALL testc()
