目录
视图
- 适用情况:对于复杂的查询,往往是有多个数据表进行关联查询而得到,如果数据库因为需求等原因发生了改变,为了保证查询出来的数据与之前相同,则需要在多个地方进行修改,维护起来非常麻烦
- 通俗的讲,视图就是一条 SELECT 语句执行后返回的结果集;所以我们在创建视图的时候,主要的工作就落在创建这条 SQL 查询语句上
- 视图是对若干张基本表的引用,一张虚表,查询语句执行的结果,不存储具体的数据(基本表数据发生了改变,视图也会跟着改变)
- 方便查询操作,减少复杂的 SQL 语句,增强可读性
定义视图
- 建议以 v_ 开头
create view 视图名称 as select语句;
查看视图
- 查看表会将所有的视图也列出来
show tables;
使用视图
select * from v_stu_score;
删除视图
drop view 视图名称;
eg:
drop view v_stu_sco;
作用
- 提高了重用性,就像一个函数
- 对数据库重构,却不影响程序的运行
- 提高了安全性能,可以对不同的用户
- 让数据更加清晰
示例
事务
为什么要有事务
事务广泛的运用于订单系统、银行系统等多种场景。
例如:
A用户和B用户是银行的储户,现在A要给B转账500元,那么需要做以下几件事:
- 检查 A 的账户余额 >500 元
- A 账户中扣除500元
- B 账户中增加500元
正常的流程走下来,A 账户扣了500,B 账户加了 500,皆大欢喜。
那如果 A 账户扣了钱之后,系统出故障了呢?A 白白损失了 500,而 B 也没有收到本该属于他的 500 。
以上的案例中,隐藏着一个前提条件:A 扣钱和 B 加钱,要么同时成功,要么同时失败。事务的需求就在于此。
所谓事务,它是一个操作序列,这些操作要么都执行,要么都不执行,它是一个不可分割的工作单位。
例如,银行转帐工作:从一个帐号扣款并使另一个帐号增款,这两个操作要么都执行,要么都不执行。所以,应该把他们看成一个事务。事务是数据库维护数据一致性的单位,在每个事务结束时,都能保持数据一致性。
事务四大特性(简称 ACID )
- 原子性( Atomicity )
- 一致性( Consistency )
- 隔离性( Isolation )
- 持久性( Durability )
下面举一个银行应用是解释事务必要性的一个经典例子。假如一个银行的数据库有两张表:支票表(checking)和储蓄表(savings)。现在要从用户Jane的支票账户转移200美元到她的储蓄账户,那么至少需要三个步骤:
- 检查支票账户的余额高于或者等于200美元
- 从支票账户余额中减去200美元
- 在储蓄帐户余额中增加200美元
上述三个步骤的操作必须打包在一个事务中,任何一个步骤失败,则必须回滚所有的步骤。
可以用 START TRANSACTION 语句开始一个事务,然后要么使用 COMMIT 提交将修改的数据持久保存,要么使用 ROLLBACK 撤销所有的修改。事务 SQL 的样本如下:
- start transaction
- select balance from checking where customer_id = 10233276
- update checking set balance = balance - 200.00 where customer_id = 10233276
- update savings set balance = balance + 200.00 where customer_id = 10233276
- commit
一个很好的事务处理系统,必须具备这些标准特性:
- 原子性(atomicity)
一个事务必须被视为一个不可分割的最小工作单元,整个事务中的所有操作要么全部提交成功,要么全部失败回滚,对于一个事务来说,不可能只执行其中的一部分操作,这就是事务的原子性
- 一致性(consistency)
数据库总是从一个一致性的状态转换到另一个一致性的状态。(在前面的例子中,一致性确保了,即使在执行第三、四条语句之间时系统崩溃,支票账户中也不会损失200美元,因为事务最终没有提交,所以事务中所做的修改也不会保存到数据库中。)
- 隔离性(isolation)
通常来说,一个事务所做的修改在最终提交以前,对其他事务是不可见的。(在前面的例子中,当执行完第三条语句、第四条语句还未开始时,此时有另外的一个账户汇总程序开始运行,则其看到支票帐户的余额并没有被减去200美元。)
- 持久性(durability)
一旦事务提交,则其所做的修改会永久保存到数据库。(此时即使系统崩溃,修改的数据也不会丢失。)
事务命令
表的引擎类型必须是 innodb 类型才可以使用事务,这是 mysql 表的默认引擎。
开启事务
- 开启事务后执行修改命令,变更会维护到本地缓存中,而不维护到物理表中
begin;
或者
start transaction;
提交事务
- 将缓存中的数据变更维护到物理表中
commit;
回滚事务
- 放弃缓存中变更的数据
rollback;
注意
- 修改数据的命令会自动的触发事务,包括 insert、update、delete
- 在 SQL 语句中有手动开启事务的原因是:可以进行多次数据的修改,如果成功一起成功,否则一起会滚到之前的数据
提交
- 需要打开两个终端窗口,使用同一个数据库,操作同一张表( jing_dong 数据 )
step1:连接
- 终端1:查询商品分类信息
select * from goods_cates;
step2:增加数据
- 终端2:开启事务,插入数据
begin;
insert into goods_cates(name) values('小霸王游戏机');
- 终端2:查询数据,此时有新增的数据
select * from goods_cates;
step3:查询
- 终端1:查询数据,发现并没有新增的数据
select * from goods_cates;
step4:提交
- 终端2:完成提交
commit;
step5:查询
- 终端1:查询数据,发现有新增的数据
select * from goods_cates;
回滚
- 需要打开两个终端窗口,使用同一个数据库,操作同一张表( jing_dong 数据 )
step1:连接
- 终端1:查询商品分类信息
select * from goods_cates;
step2:增加数据
- 终端2:开启事务,插入数据
begin;
insert into goods_cates(name) values('小霸王游戏机');
- 终端2:查询数据,此时有新增的数据
select * from goods_cates;
step3:查询
- 终端1:查询数据,发现并没有新增的数据
select * from goods_cates;
step4:提交
- 终端2:完成回滚
rollback;
step5:查询
- 终端1:查询数据,发现没有新增的数据
select * from goods_cates;
索引
- 索引是一种特殊的文件( InnoDB 数据表上的索引是表空间的一个组成部分 ),它们包含着对数据表里所有记录的引用指针。
原理
使用
- 查看索引
show index from 表名;
- 创建索引
- 如果指定字段是字符串,需要指定长度,建议长度与定义字段时的长度一致
- 字段类型如果不是字符串,可以不填写长度部分
create index 索引名称 on 表名(字段名称(长度))
- 删除索引:
drop index 索引名称 on 表名;
- 开启运行时间监测:
set profiling=1;
- 查看执行的时间:
show profiles;
注意
- 建立索引会占用磁盘空间
- 建立太多的索引将会影响更新和插入的速度,因为它需要同样更新每个索引文件
- 对于一个经常需要更新和插入的表格,就没有必要为一个很少使用的 where 字句单独建立索引了,对于比较小的表,排序的开销不会很大,也没有必要建立另外的索引
账户管理
- 在生产环境下操作数据库时,绝对不可以使用 root 账户连接,而是创建特定的账户,授予这个账户特定的操作权限,然后连接进行操作,主要的操作就是数据的 crud
- MySQL 账户体系:根据账户所具有的权限的不同,MySQL 的账户可以分为以下几种
- 服务实例级账号:启动了一个 mysqld,即为一个数据库实例;如果某用户如 root,拥有服务实例级分配的权限,那么该账号就可以删除所有的数据库、连同这些库中的表
- 数据库级别账号:对特定数据库执行增删改查的所有操作
- 数据表级别账号:对特定表执行增删改查等所有操作
- 字段级别的权限:对某些表的特定字段进行操作
- 存储程序级别的账号:对存储程序进行增删改查的操作
- 账户的操作主要包括创建账户、删除账户、修改密码、授权权限等
- 注意:
- 进行账户操作时,需要使用 root 账户登录,这个账户拥有最高的实例级权限
- 通常都使用数据库级操作权限
授予权限
使用实例级账户登录后操作,以 root 为例,主要操作包括:
- 查看所有用户
- 修改密码
- 删除用户
查看所有用户
- 所有用户及权限信息存储在 mysql 数据库的 user 表中
- 查看 user 表的结构
desc user;
- 主要字段说明:
- Host 表示允许访问的主机
- User 表示用户名
- authentication_string 表示密码,为加密后的值
- 查看所有用户
select host,user,authentication_string from user;
-- 结果:
+-----------+------------------+-------------------------------------------+
| host | user | authentication_string |
+-----------+------------------+-------------------------------------------+
| localhost | root | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | debian-sys-maint | *EFED9C764966EDB33BB7318E1CBD122C0DFE4827 |
+-----------+------------------+-------------------------------------------+
创建账户、授权
- 需要使用实例级账户登录后操作,以 root 为例
- 常用权限主要包括:create、alter、drop、insert、update、delete、select
- 如果分配所有权限,可以使用 all privileges
- 注意:添加新用户时,MySQL8.0 版本前后语句不同
grant 权限列表 on 数据库 to '用户名'@'访问主机' identified by '密码';
- 示例1:本地访问
-- step1:使用root登录
mysql -uroot -p
回车后写密码,然后回车
-- step2:创建账户并授予查询权限
grant select on jing_dong.* to 'shilizhanghu'@'localhost' identified by '123456';
-- 查看用户有哪些权限
show grants for shilizhanghu@localhost;
-- step3:退出root的登录
quit
-- step4:使用shilizhanghu账户登录
mysql -ushilizhanghu -p
回车后写密码,然后回车
- 示例2:远程访问
grant all privileges on jing_dong.* to "shilizhanghu"@"%" identified by "12345678"
- 说明:
- 可以操作 python 数据库的所有表,方式为:jing_dong.*
- 访问主机通常使用百分号 % 表示此账户可以使用任何 ip 的主机登录访问此数据库
- 访问主机可以设置成 localhost 或具体的 ip,表示只允许本机或特定主机访问
账户操作
修改权限
grant 权限名称 on 数据库 to 账户@主机 with grant option;
修改密码
使用 root 登录,修改 mysql 数据库的 user 表。
- 使用 password() 函数进行密码加密
update user set authentication_string=password('新密码') where user='用户名';
eg:
update user set authentication_string=password('123') where user='laowang';
- 注意修改完成后需要刷新权限
flush privileges;
远程登录(危险慎用)
如果想在一个 Ubuntu 中使用 mysql 命令远程连接另外一台 mysql 服务器的话,通过以下方式即可完成,但是此方法仅仅了解就好,不要在实际生产环境中使用。
- 修改 /etc/mysql/mysql.conf.d/mysqld.cnf 文件
vim /etc/mysql/mysql.conf.d/mysqld.cnf
- 重启 mysql
service mysql restart
- 在另外一台 Ubuntu 中进行连接测试
-
如果依然连不上,可能原因:
- 网络不通
通过 ping xxx.xxx.xxx.xxx 可以发现网络是否正常
- 查看数据库是否配置了 bind_address 参数
本地登录数据库查看 my.cnf 文件和数据库当前参数 show variables like ‘bind_address’;
如果设置了 bind_address=127.0.0.1 那么只能本地登录- 查看数据库是否设置了 skip_networking 参数
如果设置了该参数,那么只能本地登录 mysql 数据库
- 端口指定是否正确
删除账户
- 方法1:使用 root 登录
drop user '用户名'@'主机';
eg:
drop user 'laowang'@'%';
- 方法2:使用 root 登录,删除 mysql 数据库的 user 表中数据
delete from user where user='用户名';
eg:
delete from user where user='laowang';
-- 操作结束之后需要刷新权限
flush privileges;
- 推荐使用方法1删除用户, 如果使用方法1删除失败,采用方法2
MySQL主从同步配置
主从同步的定义
主从同步使得数据可以从一个数据库服务器复制到其他服务器上;在复制数据时,一个服务器充当主服务器(master),其余的服务器充当从服务器(slave)。因为复制是异步进行的,所以从服务器不需要一直连接着主服务器,从服务器甚至可以通过拨号断断续续地连接主服务器。通过配置文件,可以指定复制所有的数据库,某个数据库,甚至是某个数据库上的某个表。
使用主从同步的好处:
- 通过增加从服务器来提高数据库的性能,在主服务器上执行写入和更新,在从服务器上向外提供读功能,可以动态地调整从服务器的数量,从而调整整个数据库的性能
- 提高数据安全,因为数据已复制到从服务器,从服务器可以终止复制进程;所以,可以在从服务器上备份而不破坏主服务器相应数据
- 在主服务器上生成实时数据,而在从服务器上分析这些数据,从而提高主服务器的性能
主从同步的机制
Mysql 服务器之间的主从同步是基于二进制日志机制,主服务器使用二进制日志来记录数据库的变动情况,从服务器通过读取和执行该日志文件来保持和主服务器的数据一致。
在使用二进制日志时,主服务器的所有操作都会被记录下来,然后从服务器会接收到该日志的一个副本。从服务器可以指定执行该日志中的哪一类事件(譬如只插入数据或者只更新数据),默认会执行日志中的所有语句。
每一个从服务器会记录关于二进制日志的信息:文件名和已经处理过的语句,这样意味着不同的从服务器可以分别执行同一个二进制日志的不同部分,并且从服务器可以随时连接或者中断和服务器的连接。
主服务器和每一个从服务器都必须配置一个唯一的 ID 号(在 my.cnf 文件的 [mysqld] 模块下有一个 server-id 配置项);另外,每一个从服务器还需要通过 CHANGE MASTER TO 语句来配置它要连接的主服务器的 ip 地址,日志文件名称和该日志里面的位置(这些信息存储在主服务器的数据库里)。
配置主从同步的基本步骤
有很多种配置主从同步的方法,可以总结为如下的步骤:
- 在主服务器上,必须开启二进制日志机制和配置一个独立的 ID
- 在每一个从服务器上,配置一个唯一的 ID,创建一个用来专门复制主服务器数据的账号
- 在开始复制进程前,在主服务器上记录二进制文件的位置信息
- 如果在开始复制之前,数据库中已经有数据,就必须先创建一个数据快照(可以使用 mysqldump 导出数据库,或者直接复制数据文件)
- 配置从服务器要连接的主服务器的 IP 地址和登录授权,二进制日志文件名和位置
详细配置主从同步的方法
主和从的身份可以自己指定,我们将虚拟机 Ubuntu 中 MySQL 作为主服务器,将 Windows 中的 MySQL 作为从服务器。 在主从设置前,要保证 Ubuntu 与 Windows 间的网络连通。
备份主服务器原有数据到从服务器
如果在设置主从同步前,主服务器上已有大量数据,可以使用 mysqldump 进行数据备份并还原到从服务器以实现数据的复制。
在主服务器 Ubuntu 上进行备份,执行命令:
mysqldump -uroot -pmysql --all-databases --lock-all-tables > ~/master_db.sql
说明:
- -u :用户名
- -p :密码
- –all-databases :导出所有数据库
- –lock-all-tables :执行操作时锁住所有表,防止操作时有数据修改
- ~/master_db.sql :导出的备份数据( sql 文件)位置,可自己指定
在从服务器 Windows 上进行数据还原
找到 Windows 上 mysql 命令的位置。
新打开的命令窗口,在这个窗口中可以执行类似在 Ubuntu 终端中执行的 mysql 命令。
将从主服务器 Ubuntu 中导出的文件复制到从服务器 Windows 中,可以将其放在上面 mysql 命令所在的文件夹中,方便还原使用。
在刚打开的命令窗口中执行还原操作:
mysql –uroot –pmysql < master_db.sql
配置主服务器 master( Ubuntu 中的 MySQL )
编辑设置 mysqld 的配置文件,设置 log_bin 和 server-id
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
重启 mysql 服务
sudo service mysql restart
登入主服务器 Ubuntu 中的 mysql,创建用于从服务器同步数据使用的帐号
mysql –uroot –pmysql
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' identified by 'slave';
FLUSH PRIVILEGES;
获取主服务器的二进制日志信息
SHOW MASTER STATUS;
File 为使用的日志文件名字,Position 为使用的文件位置,这两个参数须记下,配置从服务器时会用到。
配置从服务器 slave( Windows 中的 MySQL )
找到 Windows 中 MySQL 的配置文件
编辑 my.ini 文件,将 server-id 修改为2,并保存退出
打开 windows 服务管理
在开始菜单中输入 services.msc 找到并运行。
在打开的服务管理中找到 MySQL57,并重启该服务
进入 windows 的 mysql,设置连接到 master 主服务器
change master to master_host='10.211.55.5', master_user='slave', master_password='slave',master_log_file='mysql-bin.000006', master_log_pos=590;
说明:
- master_host:主服务器 Ubuntu 的 ip 地址
- master_log_file:前面查询到的主服务器日志文件名
- master_log_pos:前面查询到的主服务器日志文件位置
开启同步,查看同步状态
测试主从同步
在 Ubuntu 的 MySQL 中(主服务器)创建一个数据库。
在 Windows 的 MySQL 中(从服务器)查看新建的数据库是否存在。