MySQL-视图&存储引擎&事务&备份恢复
来源：互联网 发布：paintbrush for mac 编辑：程序博客网 时间：2024/05/02 00:35
一.视图
视图是一个或者多个表中数据的子集，我们通常将这的一个或多个表称为基表。
视图被定义好之后，存储在数据库中，但是里面的数据并不保存，通过视图查看到的数据是基表中的数据
需求：有一个比较大的表，需要把表中的一部分信息共享给其他的用户看，怎么办？
解决：创建一个视图就好了。
什么是视图：
视图是一个从 一个或多个表中 导出来的数据 组成的虚拟的表。
视图的作用：
主要就是提供给用户使用的常用数据。
如果视图中的数据被修改，那么 基表 的数据也会被修改；
如果基表中的数据被修改，那么 视图 的数据也会发生变化；
mysql> select * from stu1;+------------+-------------+-------------+| id | name | tel |+------------+-------------+-------------+| 2016060801 | yangchao | 13112345678 || 2016060802 | cuidongyang | 13212345678 || 2016060803 | wangxu | 13312345678 || 2016060810 | cuidongyang | 13212345678 || 2016080605 | a1 | 13512346578 |mysql> select * from stu2;+------------+------+------+--------+| id | ule | ula | oracle |+------------+------+------+--------+| 2016060801 | 65 | 75 | 85 || 2016060802 | 66 | 76 | 86 || 2016060803 | 66 | 76 | 86 || 2016060810 | 66 | 76 | 86 |使用 stu1 和 stu2 组成一个视图： 显示：id name ule语法： create view 视图名字 as 来自哪个表实验：（1）先在单表的基础上创建视图。mysql> create view shop_view as select name,price from shop where name is not NULL;mysql> select * from shop_view;+---------+--------+| name | price |+---------+--------+| .1 niu1 | 5.99 || .2 niu2 | 6.99 || .3 niu3 | 9.99 || 4 ma4 | 5.39 || 5 ma5 | 10.90 || 6 ma6 | 20.00 || wangqi | 10.50 || houba | 100.00 || zhaojiu | 999.00 |mysql> show full tables;+---------------+------------+| Tables_in_db1 | Table_type |+---------------+------------+| shop | BASE TABLE || shop_view | VIEW |mysql> update shop set name="niu1" where id=1001;mysql> select * from shop_view;+---------+--------+| name | price |+---------+--------+| niu1 | 5.99 |mysql> update shop_view set name="niu2" where price=6.99;mysql> select * from shop;+------+---------+--------+----------+--------------+------------+| id | name | price | city | street | riqi |+------+---------+--------+----------+--------------+------------+| 1001 | niu1 | 5.99 | beijing | jiancaicheng | 2016-08-02 || 1002 | niu2 | 6.99 | beijing | jiancaicheng | 2016-07-02 |mysql> create view stu2_view as select id,ule from stu2 where ule >=66;mysql> select * from stu2_view;+------------+------+| id | ule |+------------+------+| 2016060802 | 66 || 2016060803 | 66 || 2016060810 | 66 |mysql> update stu2 set ule=60 where id=2016060802;mysql> select * from stu2;+------------+------+------+--------+| id | ule | ula | oracle |+------------+------+------+--------+| 2016060801 | 65 | 75 | 85 || 2016060802 | 60 | 76 | 86 || 2016060803 | 66 | 76 | 86 || 2016060810 | 66 | 76 | 86 |mysql> select * from stu2_view;+------------+------+| id | ule |+------------+------+| 2016060803 | 66 || 2016060810 | 66 |创建视图的时候，指定了约束条件，视图中的数据必须是满足这个条件的。如果修改了基表，导致数据不满足约束条件，那么视图中的数据被 踢出。mysql> update stu2_view set ule=61 where id=2016060803;mysql> select * from stu2_view;+------------+------+| id | ule |+------------+------+| 2016060810 | 66 |mysql> drop view stu2_view;mysql> create view stu2_view as select id,ule from stu2 where ule >=61 with check option;在创建视图的时候，加了关键字 with check optionmysql> insert into stu2 values (2016060804,60,75,74);mysql> select * from stu2;+------------+------+------+--------+| id | ule | ula | oracle |+------------+------+------+--------+| 2016060801 | 65 | 75 | 85 || 2016060802 | 60 | 76 | 86 || 2016060803 | 61 | 76 | 86 || 2016060804 | 60 | 75 | 74 || 2016060810 | 66 | 76 | 86 |对基表的数据做 insert update delete 没有影响。mysql> update stu2_view set ule=60 where id=2016060801;ERROR 1369 (HY000): CHECK OPTION failed 'db1.stu2_view'改视图不行。2、在多个表上创建视图mysql> create view stu_view (id,name,ule) as select stu1.id,stu1.name,stu2.ule from stu1,stu2 where stu1.id=stu2.id;mysql> select * from stu_view;+------------+-------------+------+| id | name | ule |+------------+-------------+------+| 2016060801 | yangchao | 60 || 2016060802 | cuidongyang | 60 || 2016060803 | wangxu | 61 || 2016060810 | cuidongyang | 66 |mysql> create view stu_view1 (id,name,ule) as select stu1.id,stu1.name,stu2.ule from stu1,stu2 where stu1.id=stu2.id and stu2.ule >=70;mysql> select * from stu_view1;+------------+-------------+------+| id | name | ule |+------------+-------------+------+| 2016060801 | yangchao | 70 || 2016060802 | cuidongyang | 75 |3、查看视图的信息（1）mysql> desc stu_view;（2）mysql> show create view stu_view\G;（3）mysql> show full tables;（4）mysql> select * from information_schema.views\G;4、修改视图的数据 insert update delete5、删除视图mysql> drop view stu_view1;6、总结：（1）视图和表的关系 视图的数据来自表； 一个视图可以来自一个表，也可以来自多个表； 表的数据发生变化，视图也变；视图的数据发生变化，表也变。（2）视图和表的区别 视图是虚拟的表； 视图不占用物理空间，表需要占用物理空间； 创建和删除视图不会影响到表的数据。（3）在哪些情况下不可以修改视图的数据 创建视图的时候加了约束条件，视图准备要修改的数据不满足约束条件，关键字； 视图中的数据使用了 聚合函数-sum avg max min count（4）不可以创建视图的情况 在存储过程中不能创建视图 在创建视图的过程中不能使用变量二.备份和恢复
备份是为了保证在灾难发生的时候，保证数据不丢失或者是最小程度的丢失
备份策略要求：
损失最小、对数据库的影响最小
备份可以通过前面的计划任务+脚本来实现自动化
了解：第三方备份软件 bacula (开源备份软件)等
ORACLE：DBA
MySQL：DBA
（一）、备份的分类
1、根据备份方法分
1）冷备份(cold backup) 离线备份(offline backup)
这种备份最简单，直接拷贝物理文件
需要关闭数据库的，然后才能进行备份
2）热备份(hot backup) 在线备份(online backup)
无需关闭数据库，直接在线备份
3）温备（warm backup）
无需关闭数据库，但是会对当前数据库的操作有影响，因为会给数据库加全局读锁
2、根据备份后的文件分
逻辑备份：指备份出来的文件是可读的，也就是文本文件 如：mysqldump 属于热备
物理备份：指备份数据库的物理文件 如：ibbackup 属于冷备份
3、按照备份数据库的内容来分
完全备份：不管是否发生变化，将全库都备份
增量备份：仅备份上次备份到这次备份之间发生变化的部分
日志备份：备份二进制日志
在企业中，一般的备份策略是完全备份+增量备份
备份时间的选择：数据库访问量较小的时候去备份。
（二）、备份 mysqldump
mysqldump命令是在shell提示符下面执行。
mysqldump -u 用户名 -p密码 库名 表名 > 备份路径/XXX.sql
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] –databases | -B [OPTIONS] DB1 [DB2 DB3…]
OR mysqldump [OPTIONS] –all-databases | -A [OPTIONS]
mysql> drop table shop; mysql> drop table shop1;1、单库备份 [root@mysql ~]# mysqldump -u root -p up > /tmp/up1.sql Enter password: 库名2、单表备份 [root@mysql ~]# mysqldump -uroot -p up dept > /tmp/dept.sql Enter password: 库名 表名3、多表备份 [root@mysql ~]# mysqldump -uroot -p up dept user1 user2 > /tmp/duu.sql Enter password: 库名 表名 表名 表名4、多库备份 -B|--database [root@mysql ~]# mysqldump -uroot -p -B up test > /tmp/utdb.sql Enter password: 库名 库名5、全库备份 -A|--all-databases [root@mysql ~]# mysqldump -uroot -p --all-databases > /tmp/all.sql Enter password: （三）、恢复
1、单库恢复(要先创建数据库的)
mysql> drop database up;
mysql> create database up; //先建库
第一种恢复方式：在mysql提示符下运行
mysql> use up
mysql> source /tmp/up1.sql
第二种恢复方式：在shell提示符下执行
mysql> drop database up;
mysql> create database up;
[root@mysql ~]# mysql -uroot -p up < /tmp/up1.sql
Enter password:
2、单表恢复
mysql> drop table dept;
[root@mysql ~]# mysql -uroot -p up < /tmp/dept.sql
Enter password:
3、多表恢复(同单表恢复)
[root@mysql ~]# mysql -uroot -p test < /tmp/duu.sql //将表恢复到test库中的操作
Enter password:
[root@mysql ~]# mysql -uroot -predhat -e “use test;show tables”
4、多库恢复 (不需要手动建库)
mysql> drop database up;
mysql> drop database test;
mysql> source /tmp/utdb.sql
5、全库恢复
[root@mysql ~]# mysql -uroot -p < /tmp/all.sql
备份时候加锁 -x：给所有的表加读锁 -l：给单独的表加读锁 查看mysqldump的帮助 mysqldump --help man mysqldump三.复制记录和复制表
准备表mysql> create table user(id int primary key,name varchar(15));mysql> insert into user values(1,'root'),(2,'bin');1、复制记录(快速插入记录) 要求：有两张表，这两张表的字段数是可以不相同的 mysql> create table user1 (id int,name varchar(20),age int); mysql> insert into user1(id,name) select * from user; mysql> select * from user1; +------+------+------+ | id | name | age | +------+------+------+ | 1 | root | NULL | | 2 | bin | NULL | +------+------+------+ 2 rows in set (0.00 sec)2、复制表 可以复制表的结构，也可以连数据一起复制 复制表有一张原表就足够了。 1）全表复制(复制表的结构和数据，不会复制主键及extra信息等) 语法： create table 新表名 [as] select 字段列表 from 已经存在的表; mysql> create table user2 select * from user; mysql> select * from user2; +----+------+ | id | name | +----+------+ | 1 | root | | 2 | bin | +----+------+ 2 rows in set (0.00 sec) mysql> desc user2; //主键未被复制过来 +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | name | varchar(15) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.06 sec) 2）复制表的一部分 mysql> create table user3 select name from user; mysql> select * from user3; mysql> create table user4 select * from user where id=1; mysql> select * from user4; +----+------+ | id | name | +----+------+ | 1 | root | +----+------+ 1 row in set (0.00 sec) 3）只复制表结构 法一： mysql> create table user5 select * from user where 1=0; 假的条件 法二： mysql> create table user6 like user; mysql> desc user6; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(15) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.03 sec) 四.存储引擎
存储引擎：不同的存储引擎可以给数据库带来不同的功能和性能。
查看当前数据库软件支持哪些存储引擎
mysql> show engines;
+——————–+———+————————————————————+————–+——+————+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+——————–+———+————————————————————+————–+——+————+
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
+——————–+———+————————————————————+————–+——+————+
6 rows in set (0.07 sec)
在mysql 5.5及以后的版本中，默认的存储引擎是innodb。
（一）、设置表的存储引擎
1、启动服务时，临时指定默认的存储引擎(不太常用)
[root@mysql ~]# /etc/init.d/mysqld stop
[root@mysql ~]# mysqld_safe –default-storage-engine=myisam –user=mysql &
[root@mysql ~]# mysql -uroot -predhat
mysql> use up;
mysql> show create table user5\G
***************** 1. row *****************
Table: user5
Create Table: CREATE TABLE user5 (
id int(11) NOT NULL,
name varchar(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> create table tmy (id int);
Query OK, 0 rows affected (0.03 sec)
mysql> show create table tmy\G *************************** 1. row *************************** Table: tmy Create Table: CREATE TABLE `tmy` ( `id` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 1 row in set (0.00 sec)2、永久修改默认存储引擎
[root@mysql ~]# mysqladmin -uroot -predhat shutdown
[root@mysql ~]# vim /etc/my.cnf
在[mysqld]那一段，添加如下行：(对于5.5以上的版本，不添加该行默认也是如此)
default-storage-engine=innodb
[root@mysql ~]# /etc/init.d/mysqld start
3、在建表时直接指定存储引擎
[root@mysql ~]# mysql -uroot -predhat
mysql> create table tmy2 (id int) engine=memory;
mysql> show create table tmy2\G
***************** 1. row *****************
Table: tmy2
Create Table: CREATE TABLE tmy2 (
id int(11) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
（二）、对于已经存在的表，如何修改存储引擎？ 不常用
mysql> alter table zz engine=myisam;
（三）、MyISAM引擎
MyISAM存储引擎注重速度，但是不支持事务、不支持外键等。
MyISAM存储引擎的目录结构
[root@mysql ~]# cd /data/mysql/
[root@mysql mysql]# cd up
[root@mysql up]# ls tmy.*
tmy.frm tmy.MYD tmy.MYI
tmy.frm：存储表结构 frame
tmy.MYD：存储表中的数据
tmy.MYI：存储的是表的索引
[root@mysql up]# ll tmy.*
-rw-rw—- 1 mysql mysql 8556 Sep 14 14:57 tmy.frm
-rw-rw—- 1 mysql mysql 0 Sep 14 14:57 tmy.MYD
-rw-rw—- 1 mysql mysql 1024 Sep 14 14:57 tmy.MYI
mysql> alter table tmy add age int; //增加一个字段，表结构文件变大
[root@mysql up]# ll tmy.*
-rw-rw—- 1 mysql mysql 8584 Sep 14 15:45 tmy.frm
-rw-rw—- 1 mysql mysql 0 Sep 14 15:45 tmy.MYD
-rw-rw—- 1 mysql mysql 1024 Sep 14 15:45 tmy.MYI
mysql> insert into tmy(id) values(1);
Query OK, 1 row affected (0.00 sec)
[root@mysql up]# ll tmy.*
-rw-rw—- 1 mysql mysql 8584 Sep 14 15:45 tmy.frm
-rw-rw—- 1 mysql mysql 9 Sep 14 15:47 tmy.MYD
mysql> create index tmy_id on tmy(id);
[root@mysql up]# ll tmy.*
-rw-rw—- 1 mysql mysql 8584 Sep 14 15:48 tmy.frm
-rw-rw—- 1 mysql mysql 9 Sep 14 15:48 tmy.MYD
-rw-rw—- 1 mysql mysql 2048 Sep 14 15:48 tmy.MYI
（四）、InnoDB引擎
InnoDB存储引擎：支持事务、支持行级锁、支持外键 *
InnoDB存储引擎的目录结构
[root@mysql up]# pwd
/data/mysql/up
[root@mysql up]# ls emp.* //该存储引擎在数据库目录下只会存放表的结构
emp.frm
数据去哪了？表的数据都存放在数据目录下的ibdata1文件中。
[root@mysql up]# cd /data/mysql/
[root@mysql mysql]# ls
ibdata1
InnoDB支持事务——事务满足4个特性：ACID
dml操作支持事务(insert、update、delete)
1、A —— Atomicity 原子性
一个事务是一个整体，它里面的所有的操作要么都做，要么都不做，是不可再分割的。
2、C —— Consistency 一致性
事务开始前和结束后，数据库的完整性约束是一致的
3、I —— Isolation 独立性、隔离性
两个事务之间是互不相干的
4、D —— Durability 持久性
事务完成之后，对数据库的所有的更改都会持久的保存在数据库中。
正常来讲：事务是可以回滚和提交
例子：事务需要提交
mysql> create table tshui (id int,money float(7,2));
mysql> insert into tshui values(1,1234.56);
mysql> select * from tshui;
+——+———+
| id | money |
+——+———+
| 1 | 1234.56 |
+——+———+
1 row in set (0.00 sec)
第一个标签：
mysql> set autocommit=0; //关闭自动提交功能
mysql> insert into tshui values(2,23456.88);
mysql> select * from tshui;
+——+———-+
| id | money |
+——+———-+
| 1 | 1234.56 |
| 2 | 23456.88 |
+——+———-+
2 rows in set (0.00 sec)
第二个标签：
mysql> select * from up.tshui; //看不到第一个标签新插入的数据
+——+———+
| id | money |
+——+———+
| 1 | 1234.56 |
+——+———+
1 row in set (0.00 sec)
第一个标签：
mysql> commit; //提交更改
Query OK, 0 rows affected (0.08 sec)
第二个标签：
mysql> select * from up.tshui;
+——+———-+
| id | money |
+——+———-+
| 1 | 1234.56 |
| 2 | 23456.88 |
+——+———-+
2 rows in set (0.00 sec)
事务的回滚： rollback
mysql> insert into tshui values(3,30000);
mysql> insert into tshui values(4,40000);
mysql> select * from tshui;
+——+———-+
| id | money |
+——+———-+
| 1 | 1234.56 |
| 2 | 23456.88 |
| 3 | 30000.00 |
| 4 | 40000.00 |
+——+———-+
4 rows in set (0.00 sec)
mysql> rollback; //回滚
mysql> select * from tshui;
+——+———-+
| id | money |
+——+———-+
| 1 | 1234.56 |
| 2 | 23456.88 |
+——+———-+
2 rows in set (0.00 sec)
事务结束的标志
1、明确执行commit提交，表示确认修改
2、明确执行rollback回滚，表示取消所有的更改
3、遇到ddl语句的时候，自动提交(create、alter、drop、truncate)
4、正常退出数据库管理系统，自动提交
5、异常退出数据库管理系统，自动回滚
- MySQL-视图&存储引擎&事务&备份恢复
- Mysql（四）- 存储引擎和事务，备份和恢复，索引，存储过程
- mysql存储引擎与事务
- Mysql存储引擎与事务
- Mysql技术内幕InnoDB存储引擎——事务&备份&性能调优
- Mysql inndodb 存储引擎的简单总结（组成结构，锁，事务，备份，优化）
- Mysql inndodb 存储引擎的简单总结（组成结构，锁，事务，备份，优化）
- Mysql技术内幕InnoDB存储引擎读书笔记--《八》备份与恢复
- MYSQL备份+增量恢复+引擎 -学习笔记
- MySQL-索引、视图、导入、导出、备份、恢复
- MYSQL触发器、存储引擎、事务语法
- 浅析Mysql InnoDB存储引擎事务原理
- MySQL自学笔记7--存储引擎、事务
- 浅析Mysql InnoDB存储引擎事务原理
- MySql--存储引擎、索引、视图、DBA命令
- Mysql视图, 存储过程, 触发器, 事务
- mysql事务、触发器、视图、存储过程、函数
- mysql事务、触发器、视图、存储过程、函数
- 关于springmvc注解扫描报错500的解决方案
- 输入和输出(IO)流的简单介绍
- 在 Linux Mint 安装 Linux Kernel 4.12（稳定版）
- 51nod 1103 N的倍数 抽屉原理
- 推荐linux视频下载地址分享
- MySQL-视图&存储引擎&事务&备份恢复
- 如何使用文件字节输入流（FileInputStream）将文件读取到程序中
- IntentService的使用
- 黑盒测试用例设计--判定表驱动法
- python篇2_列表&元组&字符串
- 留学申请
- 获取手机中应用apk
- PhoneAccount理解
- mysql分组然后统计某个值的百分比sql实现
