mysql从入门到精通《四》
三、mysql高级 (接上篇)
1、视图
视图就是一条select语句执行后返回的结果集,所以我们在创建视图的时候,主要的工作就落在创建这条SQL语句上。
视图是对若干张基本表的引用,一张虚表,查询语句执行的结果,不储存具体的数据(基本表数据发生变化以后,视图也会跟着改变)
定义视图
建议以v_开头
create view 视图名称 as select语句;
查看视图
查看表会将所有视图也会显示出来
show tables;
使用视图
select * from 视图名;
删除视图
drop view 视图名;
视图的作用:
1.提高了重用性,就像一个函数
2.对数据库重构,却不影响程序的运行
3.提高了安全性能,可以对不同的用户
4.让数据更加清晰
2.事务
所谓事务,它是一个操作序列,这些操作要么都执行,要么都不执行,它是一个不可分割的工作单位
事务的四大特性:
原子性(atomicity):一个事务必须被视为一个不可分割的最小单位,整个事务中的操作要么都执行,要么都不执行,对于一个事务来说,不可能只执行其中一小部分操作,这就是事务的原子性
一致性(conssistency):数据库总是从一个一致性的状态转换到另一个一致性的状态
隔离性(isolation):通常来说,一个事务所做的修改在提交以前,对其他事务是不可见的
持久性(durability):一旦事务提交,则其所做的修改会被永久保存到数据库(即使系统崩溃,修改的数据也不会丢失)
事务命令
需要注意的是表的引擎类型必须是innodb类型才可以使用事务,想要确认表的引擎是否是innodb,可以使用 show create table 表名;语句来查看
开启事务
begin;
或者start transaction;
提交事务
commit;
回滚事务
rollback;
3.索引
索引是一种特殊的文件(innodb数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针
创建索引
create index 索引名称 on 表名(字段名(字段长度))
此处 字段长度必须和创建该字段时定义的长度保持一致
查看索引
show index from 表名;
删除索引
drop index 索引名称 on 表名;
注意:建立太多的索引会影响更新和插入的速度,因为它需要同样更新每个索引文件,建立索引会占用磁盘空间
4.运行时间监控
所谓运行时间监控就是一条SQl语句 从开始执行到结束所需要的时间,而开启运行时间监控以后,可以查看该时间
开启运行时间监控
set profiling=1;
查看运行时间
show profiles;
关闭运行时间监控:
set profiling=0;
5.mysql账户管理
- 在生产环境下操作数据库时,绝对不可以使用root账户连接,而是创建特定的账户,授予这个账户特定的操作权限,然后连接进行操作,主要的操作就是数据的crud
- MySQL账户体系:根据账户所具有的权限的不同,MySQL的账户可以分为以下几种
- 服务实例级账号:,启动了一个mysqld,即为一个数据库实例;如果某用户如root,拥有服务实例级分配的权限,那么该账号就可以删除所有的数据库、连同这些库中的表
- 数据库级别账号:对特定数据库执行增删改查的所有操作
- 数据表级别账号:对特定表执行增删改查等所有操作
- 字段级别的权限:对某些表的特定字段进行操作
- 存储程序级别的账号:对存储程序进行增删改查的操作
- 账户的操作主要包括创建账户、删除账户、修改密码、授权权限等
授予权限
(1)、查看所有用户
所有的用户信息都存储在mysql数据库中的user表中
查看user表的结构
desc user;
主要字段说明:
- Host表示允许访问的主机
- User表示用户名
- authentication_string表示密码,为加密后的值
查看所有用户
select host,user,authentication_string from user;
(2)、创建账户授权
- 需要使用实例级账户登录后操作,以root为例
- 常用权限主要包括:create、alter、drop、insert、update、delete、select
- 如果分配所有权限,可以使用all privileges
创建账户&授权
grant 权限列表 on 数据库 to '用户名'@'访问主机' identified by '密码';
访问主机通常使用 百分号% 表示此账户可以使用任何ip的主机登录访问此数据库访问主机可以设置成 localhost或具体的ip,表示只允许本机或特定主机访问
查看用户有哪些权限
show grants for 用户名@访问主机;
账户操作
(1),修改权限
grant 权限名称 on 数据库 to 账户@主机 with grant option;
(2)、修改密码
使用root登录,修改mysql数据库的user表
使用password()函数进行密码加密
update user set authentication_string=password("新密码") where user="用户名";
修改密码后需要刷新权限:
flush privileges;
(3)、删除账户
使用root登录,然后将mysql数据库中user表中所对应的要删除的账户给删除掉,然后刷新权限即可
delete from user where user="要删除的账户名";
6.mysql主从同步配置
关于主从同步配置请查看笔者另外一篇文章,奉上链接mysql主从同步配置