数据库高级

#创建视图
create view vw_stu as SELECT order_no,shipping_id,payment,product_name FROM mmall_order INNER JOIN mmall_order_item USING(order_no);

#查看视图
SELECT * FROM vw_stu;

#查看视图结构
desc vw_stu;

#查看创建视图的语法
show create view vw_stu;

#定界符，以//开始
delimiter //

#开始事务，提交或回滚事务才会结束
start TRANSACTION;
update goodsinfo set Gamount=Gamount-10 where Gid='10001';
update goodsinfo set Gamount=Gamount+10 where Gids='10001';//
#有错误进行事务回滚
rollback //
#提交事务
COMMIT //

#s设置事务回滚点
start TRANSACTION;
insert into bank values('1003',1000)
SAVEPOINT aa;#设置回滚点aa
insert into bank values('1004',500)
SAVEPOINT bb;#设置回滚点bb
#事务回滚到aa点
rollback to aa;
#提交事务
commit;

#创建普通索引
create index ix_stuname on stuinfo(stuname);

#创建唯一索引
create UNIQUE index ix_stuname on stuinfo(stuname);

#删除索引
drop index ix_stuname on stuinfo;