文章目录
视图
什么是视图
视图是对若干张基本表的引用,一张虚表,查询语句执行的结果,不存储具体的数据,基本表数据发生改变,视图也会跟着变化
定义视图
建议以v_开头作为视图的名称
create view 视图名称 as select语句;
查看视图
show tables
使用视图
视图的用途就是查询
select * from v_视图名称;
删除视图
drop view 视图名称;
事务
什么是事务
事务广泛的应用于订单系统、银行系统等多种场景
事务是一个操作序列,这些操作要么都执行,要么都不执行,它是一个不可分割的单位。
事件的四大特征(简称ACID)
建议阅读 《高性能MySQL》
- 原子性(atomicity)
一个事务必须被视为一个不可分割的最小工作单元,整个事务中的所有操作要么全部提交成功,要么全部失败回滚,对于一个事务来说,不可能只执行其中的一部分操作,这就是事务的原子性
- 一致性(consistency)
数据库总是从一个一致性的状态转换到另一个一致性的状态。(在前面的例子中,一致性确保了,即使在执行第三、四条语句之间时系统崩溃,支票账户中也不会损失200美元,因为事务最终没有提交,所以事务中所做的修改也不会保存到数据库中。)
- 隔离性(isolation)
通常来说,一个事务所做的修改在最终提交以前,对其他事务是不可见的。(在前面的例子中,当执行完第三条语句、第四条语句还未开始时,此时有另外的一个账户汇总程序开始运行,则其看到支票帐户的余额并没有被减去200美元。)
- 持久性(durability)
一旦事务提交,则其所做的修改会永久保存到数据库。(此时即使系统崩溃,修改的数据也不会丢失。)
事务命令
表的引擎类型必须是innodb类型才可以使用事务,这是mysql的默认引擎
开启事务命令
开启事务后执行修改命令,变更会维护到本地缓存中,而不维护到物理表中
begin
或者
start transaction
提交事务命令
commit;
回滚事务命令
rollback;
重要说明
- 修改数据的命令会自动的触发事务,包括insert、update、delete
- 而在SQL语句中有手动开启事务的原因是:可以进行多次数据的修改,如果成功一起成功,否则一起会滚到之前的数据
索引
一般的应用系统对比数据库的读写比例在10:1左右(即有10次查询操作时有1次写的操作),优化数据库的查询效率的解决方案就是索引
什么是索引
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着数据表里所有的记录的引用指针
索引命令
查看索引
show infdex from 表名;
创建索引
如果指定字段是字符串,需要指定长度,建议长度与定义字段时的长度一致;
字段类型如果不是字符串,可以不填写长度部分
create index 索引名称 on 表名(字段名称(长度));
删除索引
drop index 索引名称 on 表名;
运行时间监测
- 开启运行时间监测
set profiling=1;
- 查看执行时间监测
show profiles;
注意
-
建立太多的索引将会影响更新和插入的速度,因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格,就没有必要为一个很少使用的where字句单独建立索引了,对于比较小的表,排序的开销不会很大,也没有必要建立另外的索引。
-
建立索引会占用磁盘空间
账户管理
mysql账号分类
- 服务实例级账号:该账号就可以删除所有的数据库、连同这些库中的表
- 数据库级别账号:对特定数据库执行增删改查的所有操作
- 数据表级别账号:对特定表执行增删改查等所有操作
- 字段级别的权限:对某些表的特定字段进行操作
- 存储程序级别的账号:对存储程序进行增删改查的操作
mysql账号操作
账户的操作主要包括创建账户、删除账户、修改密码、授权权限等
授予权限
查看所有用户
desc user
字段说明:
- Host表示允许访问的主机
- User表示用户名
- authentication_string表示密码,为加密后的值
创建用户
create user "用户名"@"主机名" [ identified ] by [ password ] <口令>
语法说明如下:
- 用户名
指定创建用户账号,格式为 ‘user_name’@‘host_name’。这里user_name是用户名,host_name为主机名,即用户连接 MySQL 时所在主机的名字。若在创建的过程中,只给出了账户的用户名,而没指定主机名,则主机名默认为“%”,表示一组主机。
- PASSWORD
可选项,用于指定散列口令,即若使用明文设置口令,则需忽略PASSWORD关键字;若不想以明文设置口令,且知道 PASSWORD() 函数返回给密码的散列值,则可以在口令设置语句中指定此散列值,但需要加上关键字PASSWORD。
- IDENTIFIED BY子句
用于指定用户账号对应的口令,若该用户账号无口令,则可省略此子句。
- 口令
指定用户账号的口令,在IDENTIFIED BY关键字或PASSWOED关键字之后。给定的口令值可以是只由字母和数字组成的明文,也可以是通过 PASSWORD() 函数得到的散列值。
用户授权
- mysql8.0以后版本需要在创建用户后再执行grant命令,否则会报错
- 常用权限主要包括:create,alter,drop,insert,update,delete,select
- 分配所有权限使用all privileges|all
- 使用百分号%表示此账户可以使用任何ip的主机登录访问此数据库
- 访问主机设置成localhost或具体的ip,表示只允许本机或特定主机访问
- 相同用户名,不同主机为不同账号
# *.*表示所有数据库下的所有表,第一个星表示数据库名,第二个星表示表名
grant 权限列表 on *.* to "用户名"@"访问主机" with grant option;
例
grant select on jing_dong.* to 'laowang'@'localhost';
修改加密插件
- 查看当前用户使用的密码插件
mysql> show variables like '%auth%';
- 查看mysql8支持的密码验证插件
mysql> show plugins;
- 修改用户的密码和验证插件
alter user "用户名"@"主机名" identified with 插件名 by "密码"
例:
alter user 'root'@'localhost' identified with mysql_native_password by 'root';
- 修改系统默认的密码验证插件
以下修改是在cnf配置文件中修改
例:default-authentication-plugin=插件名称
default-authentication-plugin=caching_sha2_password
查看用户权限
# mysql 数据库中存储了用户信息的 user 表
use mysql;
# 查看user表中用户权限
select host, user, authentication_string, plugin from user;
修改用户密码
#将authentication_string字段设置为空
update user set authentication_string="" where user="用户名";
#修改密码
alter user "用户名"@"主机名" identified by "密码"
删除用户
- 使用drop命令
drop user 用户名@主机名
- 直接操作user数据表
delete from user where user="用户名"