事物 视图 索引 恢复

#创建数据库
CREATE DATABASE bank;


USE bank;


#创建表


DELETE FROM counttable;
CREATE TABLE counttable(
NAME VARCHAR(20) NOT NULL,
money INT(5) NOT NULL
)


#插入数据\
INSERT INTO counttable VALUES('郭溧',1000),('吕灿',1);


SELECT * FROM counttable;
#关闭自动提交事务
SHOW VARIABLES;


SET autocommit=0;


#转账
BEGIN;
UPDATE counttable SET money=money-500 WHERE NAME='郭溧';
UPDATE counttable SET mony=mony+500 WHERE NAME='吕灿';
COMMIT;




#创建视图


SELECT * FROM student;




#单表视图
CREATE VIEW v_stu AS ( SELECT NAME,birth,card FROM student)


#查询视图
SELECT * FROM v_stu;


#对于视图进行增删改查
DELETE FROM v_stu WHERE NAME='田七';


INSERT INTO v_stu VALUES('旺旺','1996-3-6',45121);


#复合视图


CREATE VIEW v_student_grade AS (SELECT s.*,g.gradename FROM student s 
INNER JOIN grade g ON s.gradeid=g.gradeid);


SELECT * FROM v_student_grade


#删除视图
DROP VIEW v_stu;


CREATE VIEW v_test AS (SELECT * FROM v_student_grade);




SELECT * FROM v_test
#查看所有视图
SELECT * FROM views;


#创建索引
CREATE INDEX index_gradeid ON student(gradeid);


#删除索引
DROP INDEX index_gradeid ON student;


#查看索引


SHOW INDEX FROM student;