努力的学习再学习

0%

MySQL进阶

发表于 更新于 分类于 阅读次数： 评论数：
本文字数： 7.5k 阅读时长 ≈ 7 分钟

MySQL备份和恢复

1.数据备份类型
完全备份：备份整个数据库

部分备份：

  1. 增量备份：自上次备份以来的改变数据的备份；
  2. 差异备份：自上次完全备份后改变数据的备份；

2.数据备份方式区别

备份方式 特点 存储引擎支持 MyISAM 存储引擎支持 innodb
热备份 数据库启动，读写不影响 N Y
温备份 读不影响，写不行 Y Y
冷备份 数据库掉线，读写都不行 Y Y

3.需要备份的东西

  1. 数据
  2. 二进制日志，innodb事务日志
  3. 代码
  4. 服务器配置文件

4.备份工具策略

  1. cp，tar物理备份
  2. mysqldump备份
  3. lvm2备份
  4. xtrabackup备份

5.部分备份步骤

1.cp

  1. 进入Mysql查看当前数据库数据文件保存位置
  2. 在数据存放根目录新建backup文件夹，拷贝需要备份的数据文件放入其中
  3. 删除test数据库文件模拟数据丢失，数据库
  4. 把文件拷贝回去data，重启Mysql，回复数据库

2.mysqldump

1.导出整个数据库

mysqldump -u 用户名 -p数据库名 \> 导出的文件名

mysqldump -u breezelark-p mydb \> mydb.sql

2.导出一个表（包括数据结构及数据）

mysqldump -u 用户名 -p数据库名 表名\> 导出的文件名

mysqldump -u lingxi -p mydb mytb\> mytb.sql

3.导出一个数据库结构（无数据只有结构）

mysqldump -u lingxi -p -d --add-drop-table mydb \>mydb.sql

-d 没有数据–add-drop-table 在每个create语句之前增加一个drop table

约束

1.含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性

2.六大约束

  1. NOT NULL：非空，用于保证该字段的值不能为空，如姓名、学号等。

  2. DEFAULT:默认，用于保证该字段有默认值，如性别。

  3. PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空，如学号、编号等。

  4. UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空，如座位号。

  5. CHECK:检查约束【mysql中不支持】，如年龄、性别。

  6. FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关 联列的值，在从表添加外键约束，用于引用主表中某列的值。　

3.添加约束时机

  1. 创建表时
  2. 修改表时

4.约束添加分类

  1. 列级约束：六大约束语法上都支持，但外键约束没有效果
  2. 表级约束：除了非空、默认，其他的都支持

5.主键和唯一的对比

保证唯一性 是否允许为空 一个表中可以有多少个 是否允许组合
主键 √ × 至多有1个 √，但不推荐
唯一 √ √ 可以有多个 √，但不推荐

数据模型

1.定义

  1. 数据模型是对现实世界数据特征的抽象；通俗的讲数据模型就是现实世界的模拟；
  2. 数据模型是严格定义的一组概念的集合
  3. 用来抽象、表示和处理现实世界中的数据和信息的工具
  4. 对现实世界的模拟
  5. 数据库系统的核心和基础

2.类型

  1. 概念数据模型
  2. 逻辑数据模型
  3. 物理数据模型

3.作用

精确的描述了系统的静态特性、动态特性和完整性约束条件

4.要求

  1. 真实的模拟现实世界
  2. 容易为人所理解
  3. 便于在计算机上实现

5.组成元素

  1. 数据结构：描述系统的静态特性；
  2. 数据操作：描述系统的动态特性；
  3. 完整性约束

6.模型类型

网状模型：成网状

特点：允许有一个以上的节点无双亲，至少有一个节点可以有多于一个的双亲。【一个节点有多个双亲，结点之间可以有多中联系】

层次模型：成树状

特点：有且只有一个结点没有双亲结点（这个结点叫根结点）；

除根结点外的其他结点有且只有一个双亲结点.

关系模型：成二维表状，平常的数据表就是关系模型

介绍

  1. 关系(Relation)：一个关系对应着一个二维表，二维表就是关系名。

  2. 元组(Tuple)：在二维表中的一行，称为一个元组。

  3. 属性(Attribute)：在二维表中的列，称为属性。属性的个数称为关系的元或度。列的值称为属性值；

  4. （值）域(Domain)：属性值的取值范围为值域。

  5. 分量：每一行对应的列的属性值，即元组中的一个属性值。

  6. 关系模式：在二维表中的行定义，即对关系的描述称为关系模式。一般表示为（属性1，属性2，……,属性n），如老师的关系模型可以表示为教师（教师号，姓名，性别，年龄，职称，所在系）。

  7. 键(码)：如果在一个关系中存在唯一标识一个实体的一个属性或属性集称为实体的键，即使得在该关系的任何一个关系状态中的两个元组，在该属性上的值的组合都不同。

7.用途

  1. 概念模型用于信息世界的建模；
  2. 是现实世界到机器世界的一个中间层次；
  3. 是数据库设计的有力工具；
  4. 数据库设计人员和用户之间进行交流的语言。

8.表示方法 E-R图

9.三级模型结构

数据库系统的三级模式结构是指模式、外模式和内模式。

模式也称为逻辑模式或概念模式，是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图。一个数据库只有一个模式，模式位于三级结构的中间层。

  1. 外模式

    外模式也称为用户模式，它是数据库用户（包括应用程序员和最终用户）能够看见和使用的局部数据的逻辑结构和特征的描述，是数据库用户的数据视图，是与某一应用有关的数据的逻辑表示。外模式是模式的子集，一个数据库可以有多个外模式。

  2. 内模式

    内模式也称为存储模式，一个数据库只有一个内模式，它是数据物理结构和存储方式的描述，是数据在数据库内部的表示方式。

10.二级映射

数据库管理系统在三级模式之间提供了两层映射，分别为外模式/模式映射、模式/内模式映射。

外模式/模式映射

对于同一个模式可以有任意多个外模式。对于每一个外模式，数据库系统都有一个外模式/模式映射。当模式被改变时，数据库管理员对各个外模式/模式映射做相应的改变，可以使外模式保持不变。这样，依据数据外模式编写的应用程序就不用修改，保证了数据与程序的逻辑独立性。

模式/内模式

数据库中只有一个模式和一个内模式，所以模式/内模式的映射是唯一的，它定义了数据库的全局逻辑结构与存储结构之间的对应关系。当数据库的存储结构被改变时，数据库管理员对模式/内模式映射做相应的改变，可以使模式保持不变，应用程序相应地也不做变动。这样，保证了数据与程序的物理独立性。

外键

1.作用

  1. 为了一张表记录的数据不要太过冗余。
  2. 保持数据的一致性、完整性。
  3. 实现一些级联操作

2.使用条件

  1. 两个表必须是InnoDB表，MyISAM表暂时不支持外键
  2. 外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显式建立；
  3. 外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以；

3.使用方法

1、创建外键的语法：

外键的定义语法：

1
2
3
4
5
6
7
[CONSTRAINT symbol] FOREIGN KEY [id] (index\_col\_name, ...)

REFERENCES tbl\_name (index\_col\_name, ...)

[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

该语法可以在 CREATE TABLE 和 ALTER TABLE 时使用，如果不指定CONSTRAINT symbol，MYSQL会自动生成一个名字。

  1. ON DELETE、ON UPDATE表示事件触发限制，可设参数：
  2. RESTRICT（限制外表中的外键改动，默认值）
  3. CASCADE（跟随外键改动）
  4. SET NULL（设空值）
  5. SET DEFAULT（设默认值）
  6. NO ACTION（无动作，默认的）

2、示例

创建表1

1
2
3
4
5
6
7
create table repo\_table(

repo\_id char(13) not null primary key,

repo\_name char(14) not null)

type=innodb;

创建表2

1
2
3
4
5
6
7
8
9
10
11
mysql\> create table busi\_table(

-\> busi\_id char(13) not null primary key,

-\> busi\_name char(13) not null,

-\> repo\_id char(13) not null,

-\> foreign key(repo\_id) references repo\_table(repo\_id))

-\> type=innodb;

插入数据

1
2
3
4
5
6
7
8
9
insert into repo\_table values("12","sz"); //success

insert into repo\_table values("13","cd"); //success

insert into busi\_table values("1003","cd", "13"); //success

insert into busi\_table values("1002","sz", "12"); //success

insert into busi\_table values("1001","gx", "11"); //failed,

提示：

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (smb_man.busi_table, CONSTRAINT busi_table_ibfk_1 FOREIGN KEY (repo_id) REFERENCES repo_table (repo_id))

增加级联操作

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql\> alter table busi\_table

-\> add constraint id\_check

-\> foreign key(repo\_id)

-\> references repo\_table(repo\_id)

-\> on delete cascade

-\> on update cascade;

   ENGINE=InnoDB DEFAULT CHARSET=gb2312； //另一种方法，可以替换type=innodb

3、相关操作

外键约束（表2）对父表（表1）的含义:

在父表上进行update/delete以更新或删除在子表中有一条或多条对应匹配行的候选键时，父表的行为取决于：在定义子表的外键时指定的on update/on delete子句。

关键字含义

  1. CASCADE：删除包含与已删除键值有参照关系的所有记录
  2. SET NULL：修改包含与已删除键值有参照关系的所有记录，使用NULL值替换(只能用于已标记为NOT NULL的字段)
  3. RESTRICT：拒绝删除要求，直到使用删除键值的辅助表被手工删除，并且没有参照时(这是默认设置，也是最安全的设置)
  4. NO ACTION：啥也不做

4.注意点

  1. 表引擎必须为InnoDB，MyISAM不支持
  2. 外键必须建立索引（可以为普通、主键、唯一，事先不建立的话会自动创建一个普通索引），你要用的外键和参照的外表的键，即
    1
    2
    3
    alter table B add constraint `b_foreign_key_name` foreign key (`bfk`)
    references A(`afk`) on delete no action on update no action;
    时 b\_foreign\_key\_name 为外键名，bfk字段和afk字段都必须存在索引
  3. 外表为约束表，约束着含有外键的被约束表，即 B 含有一个以 A 作为参考表的外键，则 A 为主 B 为从，若关联on delete on update等动作，则 A 变更 B 会被变更，B 怎样变 A 不必跟随变动，且表 A 中必须事先存在 B 要插入的数据外键列的值，列如 B.bfk作为外键 参照 A.ak ，则 B.bfk插入的值必须是 A.ak 中已存在的
  4. 把3说的简单点就是若B有以A作为参照的外键，则B中的此字段的取值只能是A中存在的值，从表B会实时受到主表A的约束，同时若关联on delete on update等操作则当A中的被参照的字段发生delete或update时，B中的对应的记录也会发生delete 或 update操作，完整性。

MySql关系

1.定义

将实体与实体的关系，反应到最终数据库表的设计上，将关系分为三种：一对一，一对多（多对一）和多对多，所有的关系都是表与表之间的关系

2.分类

  1. 一对一：一张表的一条记录只能与另外一条记录进行对应，反之亦然
  2. 一对多：一张表中的一条记录可以对应另外一张表中的多条记录，但是返过来，另外一张表中的一条记录只能对应第一张表的一条记录，这种关系就是一对多或者多对一的关系
  3. 多对多：一张表中（A）的一条记录能够对应另外一张表（B）中的多条记录，同时B表找中的一条记录也能对应A表中的多条记录，多对多的关系

3.比方

一个班级有很多学生，他们分别有不同的学号。一个学生对应一个学号，一个学号对应一个学生；这里学生和学号的关系就是一对一。

一个班级有很多学生，这个班级只有一个班主任。一个班主任对应多个学生，多个学生对应一个班主任，这里学生和班主任的关系就是多对一。

一个班级有很多学生，他们有语文课、数学课、英语课等很多课。一门课有很多人上，一个人上很多门课。这里学生和课程的关系就是多对多。

多表连接

1.图解

2.分类

  1. 内连接：INNER JOIN – 可简写为 JOIN；
  2. 左外连接：LEFT OUTER JOIN – 可简写为 LEFT JOIN；
  3. 右外连接：RIGHT OUTER JOIN – 可简写为 RIGHT JOIN；
  4. 全连接：使用 UNION 完成；
  5. 交叉连接：CROSS JOIN – 也称为 笛卡儿乘积连接，大抵不使用；

3.详述

3.1内连接

1
2
3
SELECT \*
FROM a
INNER JOIN b ON(a.id = b.id)

查询出 a表 和 b表 的 交集；

3.2左外连接

1
2
3
SELECT \*
FROM a
LEFT OUTER JOIN b ON(a.id = b.id)

查询出 左边表 – 即a表 的完全集，而 右边表 – 即b表 中匹配的则有值，没匹配的以 null 值取代

3.3右外连接

1
2
3
SELECT \*
FROM a
RIGHT OUTER JOIN b ON(a.id = b.id)

与 左外连接相反 ，查询出 右边表 – 即b表 的完全集，而 左边表 – 即a表 中匹配的则有值，没匹配的以 null 值取代；

3.4全连接

1
2
3
SELECT \* FROM a LEFT OUTER JOIN b ON(a.id = b.id)
UNION
SELECT \* FROM a RIGHT OUTER JOIN b ON(a.id = b.id)

全连接 是 左右外连接 的并集，连接表包含被连接的表的所有记录，如果缺少匹配的记录,，则以 null 取代

3.5交叉连接

CROSS JOIN 子句从连接的表返回行的 笛卡儿乘积；

假设使用 CROSS JOIN 连接两个表；

结果集将包括两个表中的所有行，其中结果集中的每一行都是第一个表中的行与第二个表中的行的组合；

当连接的表之间没有关系时，会使用这种情况；

要特别注意的是，如果每个表有 1000 行，那么结果集中就有 1000 x 1000 = 1,000,000 行，那么数据量是非常巨大的；

1
2
3
SELECT \*
FROM a
CROSS JOIN b

/**

* 注意:

* 与 INNER JOIN 或 [LEFT, RIGHT] OUTER JOIN 子句不同

* CROSS JOIN 连接没有 ON 条件

*/

添加了 WHERE 子句后，如果 a表 和 b表 有关系，则 CROSS JOIN 的工作方式与 INNER JOIN 类似，SQL 语句为：

1
2
3
4
SELECT \*
FROM a
CROSS JOIN b
WHERE a.id = b.id

子查询

1.主查询和子查询的关系

  1. 子查询是嵌入到主查询中
  2. 子查询是辅助主查询的,要么充当条件,要么充当数据源
  3. 子查询是可以独立存在的语句,是一条完整的 select 语句

2.子查询分类

  1. 标量子查询: 子查询返回的结果是一个数据(一行一列)
  2. 列子查询: 返回的结果是一列(一列多行)
  3. 行子查询: 返回的结果是一行(一行多列)

其他分类

  1. where型子查询：将内层查询结果当做外层查询的比较条件。
  2. from型子查询：把内层的查询结果当成临时表，供外层sql再次查询。查询结果集可以当成表看待。临时表要使用一个别名。
  3. exists 型子查询：把外层sql的结果，拿到内层sql去测试，如果内层的sql成立，则该行取出。内层查询是exists后的查询。

3.举例

标量子查询

查询班级学生平均年龄

查询大于平均年龄的学生

查询班级学生的平均身高
\* from students where age \> (select avg(age) from students);```
1
2
3
4
5
6
7
8
9
10

   列级子查询

     查询还有学生在班的所有班级名字

     找出学生表中所有的班级 id

     找出班级表中对应的名字

```select name from classes where id in (select cls\_id from students);

行级子查询

需求: 查找班级年龄最大,身高最高的学生

行元素: 将多个字段合成一个行元素,在行级子查询中会使用到行元素
\* from students where (height,age) 
1
2
3
4

 where查询

```select 列1,列2,...，列n from 表名 where 列i =/in (select 列1,列2,...，列n from 表名 where ...);

from查询

列1,列2,...，列n from (select 列1,列2,...，列n from 表名 where ...) as 表别名 where ....;```
1
2
3
4

exists查询

```select 列1,列2,...，列n from 表名 where exists (select 列1,列2,...，列n from 表名 where ...);

原文链接

-------------本文结束元宝感谢您的阅读-------------