MySQL基础（命令）

（拓展）MySQL三大范式： 

补充：

 关系数据库的几种设计范式介绍

1、第一范式（1NF）

在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。

所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式（1NF）中表的每一行只包含一个实例的信息。例如，对于图3-2 中的员工信息表，不能将员工信息都放在一列中显示，也不能将其中的两列或多列在一列中显示；员工信息表的每一行只表示一个员工的信息，一个员工的信息在表中只出现一次。简而言之，第一范式就是无重复的列。

2、第二范式（2NF）

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被唯一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。如图3-2 员工信息表中加上了员工编号（emp_id）列，因为每个员工的员工编号是唯一的，因此每个员工可以被唯一区分。这个唯一属性列被称为主关键字或主键、主码。

第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。简而言之，第二范式就是非主属性非部分依赖于主关键字。

3、第三范式（3NF）

满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在图3-2的员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。简而言之，第三范式就是属性不依赖于其它非主属性。

为什么要有数据库：文件 管理数据的问题：

1.数据冗余和不一致性

2.大数据访问困难

3.数据孤立

4.完整性和原子性

5.并发访问异常

6.安全性问题

由此，有了数据库。。。

数据库概述

数据库：指的是以一定方式存储在一起、能为多个用户共享，具有尽可能小的冗余度的特点、是与应用程序彼此独立的数据集合。

数据库服务器的基本概念

数据库

       DBMS 数据库管理系统（能够操作和管理数据库的大型软件）

       RDBMS 关系式数据库（=DBMS）

              1.数据以表格的形式出现

              2.每行为各种记录名称

              3.每列为记录名称所对应的数据域

              4.许多的行和列组成一张表单

              5.若干的表单组成database

数据表

       数据（记录）

       字段（id name ....）

       类型（定义字段中的内容）

       主键（检索时用）

数据库的三种基本形式

层次模型：

按照层次结构的形式组织数据库数据的模型

缺点：冗余数据

网状模型:

按照网状结构的形式组织数据库数据的模型

缺点：后期维护困难

关系模型：

按照关系结构的形式组织数据库数据的模型

sql语句：

结构化查询语句

sql类型：

DML      数据操作语言：用来操作数据库中的数据

       INSERT

       DELETE

       SELECT

       UPDATE

DDL       数据描述语言：用来建立数据库、定义数据关系

       CREATE

       DROP

       ALTER

DCL        数据控制语言：用来控制数据库组建的权限

       GRANT

       REVOKE

关系式数据库结构

文件逻辑关系：

上层：文件

底层：二进制的方式存储在硬盘的数据块中

中间层：文件系统

数据库逻辑关系：

上层：数据表

底层：文件

中间层：存储引擎（提供存储、创建、更新、查询数据的实现方法）

mysql 支持的存储引擎：

MYISAM

默认引擎、插入和查询速度较快

不支持事务、行级锁和外键约束等功能

注：

事务：一段sql语句的批处理、为了保证数据原子性

锁：行级锁（冲突少、速度慢）；表级锁（冲突多、速度快）；页级锁（折中方案）

                     约束

                            域约束：数据类型约束

                            外键约束：引用完整性约束

                            主键约束：某字段能惟一标识此字段所属的实体，并且不允许为空

                            一张表中只能有一个主键

                            惟一性约束：每一行的某字段都不允许出现相同值，可以为空

                            一张表中可以有多个

                            检查性约束: age: int

INNODB  

              支持事务、行级锁和外键约束等功能

MEMORY

工作在内存中，通过散列保存数据，速度快、不能永久保存数据

数据的存储和查询

存储管理器（实现存储的功能，通过DDL创建数据表的结构，再通过DML来保存数据）

事务管理器：提供事务功能

文件管理器：保存数据库数据和文件的对应关系

权限及完整性管理器：设置存储权限

缓冲管理器：管理缓冲空间

查询管理器（实现查询的功能，接收用户的查询请求、理解用户查询请求，将查询请求提交给存储管理器、实现最终存储）

DDL 、DML解释器

查询执行引擎

数据库工作模式：

单进程多线程的工作模式

守护线程

应用线程（用户线程）

补充：apache的工作模式:

一个进程处理一个请求

一个线程处理一个请求

一个线程处理多个请求

mysql优化

1.垂直扩展

2.线程重用

3.缓存机制（nosql<memcache  redis  mongodb>）

缺点：

SMP      对称多处理器架构

3.  E-R模型

实体关系建模

实体：数据对象，即看得见摸得着

联系：表示一个或多个实体之间的关联

属性：实体的某一特性

数据库—基本概述

数据库的版本

1.社区版

2.企业版

3.集群版

数据库的安装

1. 专用软件包管理器（二进制）

deb 、rpm等

mysql                 MySQL客户端程序和共享库

mysql-server           MySQL服务器需要的相关程序

2. 源代码软件包（编译安装）

configure、cmake

数据库常用的配置选项

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql    ----指定残可安装路径（默认的就是/usr/local/mysql）

-DMYSQL_DATADIR=/data/mysql                    ----mysql的数据文件路径

-DSYSCONFDIR=/etc                                     ----配置文件路径

-DWITH_INNOBASE_STORAGE_ENGINE=1        ----使用INNOBASE存储引擎

-DWITH_ARCHIVE_STORAGE_ENGINE=1         ----常应用于日志记录和聚合分析，不支持索引

-DWITH_BLACKHOLE_STORAGE_ENGINE=1      ----黑洞存储引擎

-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1  编译过程中取消一些存储引擎指令介绍

-DWITHOUT_FEDERATED_STORAGE_ENGINE=1

-DWITHOUT_PARTITION_STORAGE_ENGINE=1

-DWITH_READLINE=1      ----支持批量导入mysql数据

-DWITH_SSL=system      ----mysql支持ssl会话，实现基于ssl的数据复

-DWITH_ZLIB=system      ----压缩库

-DWITH_LIBWRAP=0        ----是否可以基于WRAP实现访问控制

-DMYSQL_TCP_PORT=3306                  ----默认端口

-DMYSQL_UNIX_ADDR=/tmp/mysql.sock      ----默认套接字文件路径

-DENABLED_LOCAL_INFILE=1                ----是否启用LOCAL_INFILE功能

-DEXTRA_CHARSETS=all                                  ----是否支持额外的字符集

-DDEFAULT_CHARSET=utf8                  ----默认编码机制

-DDEFAULT_COLLATION=utf8_general_ci    ----设定默认语言的排序规则

-DWITH_DEBUG=0                          ----DEBUG功能设置

 -DENABLE_PROFILING=1                    ----性能分析功能是否启用

3. 服务：mysqld

4. 端口：3306

5. 主配置文件：/etc/my.cnf

6. 脚本：mysql_install_db

7. mysqld_safe

8. 数据目录 ：/var/lib/mysql

9. 套接字文件：/var/lib/mysql/mysql.sock

10. 当意外关闭数据库时，再开启时假如开启不了，找到这个，删除再启动

11. 进程文件：/var/run/mysqld/mysqld.pid

登录及退出mysql环境

a)     设置密码 mysqladmin     -uroot          password ‘123’

b)     登录              mysql  -u 用户名 -p

-p 用户密码 

-h 登陆位置（主机名或ip地址）

-P 端口号（3306改了就不是了）

-S 套接字文件（/var/lib/mysql/mysql.sock）

c)    退出              exit

d)   创建登录用户      create user 用户名@’%’ identified by ‘密码’

e)    修改密码       set password=password(‘新密码’)

set password for 用户@登录位置=password(‘新密码’)      — — — root用户为其他用户找回密码

当管理员把自己密码忘记了，怎么找回？？？

1）关闭数据库

2）修改主配置文件（/etc/my.cnf）<------ skip-grant-tables

3）启动数据库

4）空密码登录并修改密码

update  mysql.user set password=password(‘新密码’) where user=’root’;

5）删除skip-grant-tables,重启数据库验证新密码

SQL语句

关于库的操作：

关于表的操作

注：库和表的删除用drop,记录删除用delete

权限

grant 权限1，权限2，...... on 数据库.数据表 to 用户@登录位置 (identified by ‘密码’)；

revoke 取消的权限1，取消的权限2，...... on 数据库.数据表 from 用户@登录位置；

show grants for 用户@登录位置；

备份和还原

冷备份：把数据库关闭，离线备份（使用cp、tar等命令直接备份数据库所存放的目录）

FRM 结构

MYI  索引

MYD  数据

快照备份：（利用逻辑卷）

逻辑备份：

mysqldump

备份：mysqldump -u 用户名 -p 数据库名  > /备份路径/备份文件名（备份单个数据库）

mysqldump -u 用户名 -p 数据库名 表名 > /备份路径/备份文件名（备份数据表）

--databases 库1，库2 (此时还原--->mysql < 备份文件)

--all-databases —备份服务器中的所有数据库内容

还原：mysql 数据库 < 备份文件

mysqlhotcopy

备份：mysqlhotcopy --flushlog -u=’用户’ -p=’密码’ --regexp=正则 备份目录

还原：cp -rpf 备份目录 数据目录（/var/lib/mysql）

补充的备份机制

1.日志备份

>mysql show global variables like ‘%log%’

列出mysql中和日志相关的变量

错误日志

服务器启动和关闭时的信息

服务器运行过程中的错误信息

从服务器启动从服务器进程时产生的信息

log-error     错误日志的路径

一般日志（不启用）

记录用户对数据库的查询操作

general-log=ON  启动一般查询日志

log=ON              全局日志开关

log-output         日志的记录类型

慢查询日志

记录需要较长时间的查询操作

log-slow-queries=保存路径  启动慢查询日志，并设置个路径

二进制日志

所有对数据库状态更改的操作（create、drop、update等）

log-bin=位置 启动二进制日志

>mysql  show binary logs  查看当前使用的二进制日志

>mysql show binlog events in ‘二进制日志(mysql-bin.000001)’ 查看二进制日志的内容

还原：(mysqlbinlog)

按时间还原：

mysqlbinlog  --start-datetime ‘YY-MM-DD HH:MM:SS’ --stop-datetime ‘YY-MM-DD HH:MM:SS’ 二进制日志 | mysql(-urot -p)   

 按文件大小还原：

--start-position

--stop-position

事务日志：记录事务相关的日志信息

中继日志：记录从服务器的备份信息

2.多机备份

主从配置：实时备份

主主配置：（配置了2遍的主从）实时备份、负载均衡

多从一主：实时备份（更多的备份节点）

多主一从：实时备份、节约成本

实验

1.创建mysql的登录用户yzh可以登录mysql服务器

1. 创建登录用户

在另一台虚拟机上测试

创建的用户为自己更改密码

root用户为其他用户找回密码

root找回自己的密码并修改

数据库的增删改查实验

创建数据库

创建数据表

复杂一点的，，，

插入数据

将表a2的数据复制到表a1

删除数据库

删除数据表

删除表里的数据记录

删除年龄在23-25之间的

修改表中的数据

修改数据表的名称

修改数据表的字段类型

修改数据表的字段

添加字段

删除字段

给用户授权

1.给用户全部权限

取消yzh用户的删除库、表、表中数据的权限

备份和还原数据库文件

1.把数据库aa 备份到/root目录下

2.模拟数据库aa丢失（删除数据库aa）

3.还原

备份多个数据库（--databases）

还原

备份有规则的数据库

模拟数据库删除

还原

关于二进制日志还原

开启二进制日志

查看二进制日志文件

按时间还原：

如果数据库中的bb库被删，需要还原

查看二进制日志内容

还原并查看

按文件大小还原：

还原到bb库被删除的数据状态

查看bb库被删除前后的文件大小

还原并查看