MySQL的管理(五完结)

1.定义结束符

delimiter $$

2.创建触发器

create trigger trig_name after/ before insert/ update tb_name 
for each row
begin
......
end$$

3.查看触发器/删除触发器

# 查看触发器
show triggers;
​
# 删除触发器
drop trigger trig_name;
drop trigger if exits trig_name;

4.开启事务/回滚

start transaction;
rollback;         # 回滚到开启事务之前
commit;           # 需要关闭自动提交 set autocommit=0; 但是我这个版本好像用不了

5.保留点

# 设置保留点
delete from tb_name where id=20;
savepoint delete1;  # 保存的0-19
delete from tb_name where id=19;
savepoint delete2; # 保存的0-18
delete from tb_name where id=18;
savepoint delete3; # 保存的0-17
​
# 回滚到保留点
rollback to delete2;

6.用户管理

# 查看用户账号
use mysql;
select user from user;
# 创建用户账号
create user user_name identified by "密码";
# 重命名账号
rename user old_name to new_name;
# 删除账号
drop user use_name;
# 查看权限
show grants for user_name;
# 设置权限
grant select on tb_name.* to user_name;  # select是相关权限
/*
整个服务器权限    grant all,revoke all
整个数据库        on database.*
数据表            on database.table
常用的相关权限:
all          c除了grant之外的所有权限
select       查看权限
update       更新权限
create       创建表权限
drop         删除表权限
delete       删除数据权限
usage        无权限
*/
​
# 更新密码
set password for "用户名" = "密码";