MySQL数据库管理（20210726笔记）

MySQL数据库管理

MySQL数据库简介

数据类型

结构化数据：二维表数据

非结构化数据：图片、视频

应用服务器 ——>关系型数据服务器

​ ——>文件型服务器

利用不同介质存储数据

效率、性能要求问题

电子表格——数据库

数据库发展

1964 通用电气IDS网状模式

1969 IBM IMS层状模型

1970 E.F.CODD关系理论 数据库系统的关系模型，关系数据库管理系统（RDBMS）基础

关系模型组成：对象或关系集合，处理关系的运算符集，确保精确性和一致性的数据完整性

1996 MySQL 1.0发布

关系型数据库——基本术语

关系(Relation)：一个关系对应一张二维表。

元组(Tuple)：表中的一行即为一个元组。

属性(Atturibute)：表中的一列即为一个属性，每一列的第一行是属性名，其余行是属性值。

候选码：表中的某个属性或属性组合（某列），它可以唯一的标识一个元组

主码：在多个候选码中选择一个作为主码 【主键】

主码是一个能唯一标识一个元组的属性

【关系应满足如下性质】

关系必须是规范化的，即要求关系必须满足一定的规范条件， 其中最基本的一条就是，关系的每一列不可再分。

关系中必须有主码（虽然没有主码时也能创建成功），使得元组唯一。如学生关系中，学号属性是主码，课程关系中，编号是主码，选修关系中，学号和 编号一起是主码。

元组的个数是有限的且元组的顺序可以任意交换。

属性名是唯一的且属性列的顺序可以任意交换。

SQL定义

数据库的概念：按照数据结构来组织、存储和管理数据的仓库

数据的存放：数据库由表、关系和操作对象组成

表——行和列

行——记录

列——字段、属性

数据库管理系统

DBMS：操纵和管理数据库的大型软件，用于建立、使用和维 护数据库。

常用：SYBASE、ORACLE、DB2、Mysql、SQL Server、Postgre

结构化查询语言

数据库查询和程序设计语言

用于存取数据以及查询、更新和管理关系数据库系统

---

SQL 是一门 ANSI 的标准计算机语言，用来访问和操作数据库系统。

SQL 语句用于取回和更新数据库中的数据。

目前，已被大多数商品化关系数据库采用，如：Oracle（甲骨文）、Sybase、 DB2、Informix、SQL Server、Teradata。

虽然绝大多数的数据库系统使用SQL，但是它们同样有它们 自立另外的专有扩展功能用于它们的系统。标准的SQL命令， 比如 SELECT、UPDATE、DELETE、INSERT、WHERE 等 等被用于完成绝大多数数据库的操作。

SQL语言的特点

综合统一

查询、操纵、定义、控制操作功能一体化

语言风格统一，数据操作符统一性

高度非过程化

用户只需提出“做什么”，而不必指明“怎么做” （不需要关注底层）

隐蔽数据存取路径

面向集合的操作方式

操作的对象和结果都是元组的集合（关系）（表）

以同一种语法结构提供两种使用方式

交互式语言，一般DBMS都提供联机交互工具，用户可直接键入SQL命令对数据库进行操作，由DBMS来进行解释，针对所有DB用户。

嵌入式语言，将SQL语句嵌入到高级语言（宿主语言），使应用程序充分利用SQL访问数据库的能力，针对应用程序员。 【应用服务去管理】

两种类型的语法结构基本一致

语言简洁，易学易用

类似于英语的自然语言

操作谓词少（核心功能只用9个动词）

关系型数据库——关系完整性规则

实体完整性规则

主码的属性值不能为空值。因为如果出现空值，那么主码就无法保证元组的唯一性。

参照完整性规则

关系之间的联系是靠公共属性实现的

如果这个公共属性是一个关系R1的主码，那么在另一个与它有联系的关系R2中就称为外码。

用户定义完整性

反映某一具体应用所涉及的数据必须满足的语义要求。

MySQL数据库介绍

开源：MySQL源代码免费下载

简单：体积小，便于安装

性能优越：性能足够与商业数据库媲美

功能强大：提供的功能足够与商业数据库媲美

MySQL发行版本

MySQL的版本采用双授权政策，即分为社区版和商业版，而这两个版本又各自分四个版本依次发布

原理图

connectors连接器：SDK包连进MySQL，单进程多线程

【内存】

connection pool连接池：做身份验证（是否具备权限）

SQL Interface接口：运行SQL语句

Parser解析器：检查语法语义，不对则报错

Optimizer优化器：形成执行计划

Caches Buffers缓存

Pluggable Storage Engines存储引擎：一般只用到InnoDB（只有它支持事务，默认存储引擎）

【硬盘】

File System File Logs

常用存储引擎（关注Transactions）

MySQL内存结构

事务

关系型数据库最小的执行单元

Begin transarction

Update t1 set 余额=余额-100 where id =2021001

Update t1 set 余额=余额+100 where id=2021002

Commit

事务的特性ACID

原子性；一致性；完整性；持久性

内存结构

innodb_buffer_pool （数据缓冲区）

innodb_log_buffer （日志缓冲区）

query cache （5.6版本后没有了）

sort_buffer_size

join_buffer_size

read_buffer_size

【查询】

客户端先从数据缓冲区（内存）里找需要的数据，如果有就直接给客户端

如果没有，则缓冲区从数据文件中找数据缓存再数据缓冲区

此时下一个客户端访问，则可以直接从缓冲区返回需要的数据

【修改】

修改后的数据在内存（数据缓冲区）里，不会马上写入数据文件（硬盘）

check point机制：check point来的时候才会写入硬盘（关机、备份）

【问题1】

check point来之前宕机了，数据改了吗？

——改了，事务一致性，存在日志缓冲区，在写入的日志文件里找

日志文件记录执行的操作记录，commit的时候就会写入

日志文件redo log重写

日志写入硬盘的时间和操作复杂度比数据写入硬盘要低，所以这种事务一致性和效率不矛盾

【问题2】

执行了第一句SQL就check point到了，没有commit就宕机了

在undo log里把副本回滚（回修改前状态）

MySQL文件结构

数据文件

undo回滚日志

二进制日志文件 bin log 数据恢复/主从（热备，平时不提供服务）复制（两台服务器）

参数文件

慢查询日志

redo重做日志 默认三组，每组50兆BIT

pid文件

sock文件

安装MySQL数据库

MySQL安装

Rpm包安装：最为简单，但不够灵活，安装的路径固定，并且只能安装一个版本。

二进制安装：自定义的设置安装，方便快捷，可自主配置，适合生产布署。 【更加规范】

源码安装：较为复杂，耗时长，且出现故障时不易确定是MySQL原本问题还是编译过程的文问题，一般不建议使用。

连接MySQL服务器

当MySQL客户机与MySQL服务器是同一台主机时， 打开命令提示符窗口，输入

mysql -h 127.0.0.1 -P 3306 -u root –proot 

或者

mysql -h localhost -P 3306 -u root –proot 

回车（注意-p后面紧跟密码root），即可实现本地 MySQL客户机与本地MySQL服务器之间的成功连接

使用MySQL

创建数据库

create database choose;

成功创建choose数据库后，数据库根目录下会自动创建数据库目录。

show databases; 

即可查看MySQL服务实例上所有的数据库。

MySQL系统库

用户权限相关系统表

系统对象相关系统表

Replication（主从复制）相关系统表

Optimizer（优化）相关系统表

显示数据库结构

show create database choose; #查看choose数据库的相关信息（MySQL版本ID号、默认字符集等）。

选择当前操作的数据库

use choose;  #到达该数据库里

后续的命令及语句将自动操作choose数据库中的数据库对象。

删除数据库

drop database student; #删除student数据库

基本操作

a) 查看表

show tables;

b) 查看表结构

desc [数据库名称];

c) 创建表

use choose;

set default_storage_engine=InnoDB;

create table my_table(

today datetime,  #格式是YYYY-MM-DD hh:mm:ss

name char(20) #长度为20的变量

);

【操作案例】

d) 查看表数据

select * from [表名称];

e) 在表中插入数据

INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

INSERT INTO Persons VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing') 

INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees')

f) 更新表数据

Update语句用来修改表中已存在的数据。

Update语句既可以一次修改一行数据， 也可以一次修改许多行，甚至可以一次修改表中的全部数据。

Update 语句使用 Where 子句指定要修改的行，使用Set子句给出新的数据。新数据可以是常量，也可以是指定的表达式，还可以是使用From子句来自其他表的数据。

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

g) 删除表数据

DELETE FROM [表名称]; #Delete 语句可以一次从一个表中删除一条或者多条数据行

DELETE FROM 表名称 WHERE 列名称 = 值

DELETE FROM Person WHERE LastName = 'Wilson' #删除单行

DELETE FROM Person #删除所有行

h) 删除表结构

drop table table_name;

删除表后，MySQL服务实例会自动删除该表结构定义文件（例如 second_table.frm文件），以及数据、索引信息。该命令慎用！

SQL语言

DQL（data query language）数据查询语言

DML（data manipulation language）数据操作语言

DDL（data defination language）数据库定义语言

DCL（data control language）数据库控制语言

创建数据库表

使用SQL语句“create table表名”即可创建一个数据库表。

create table 表名

显示表结构

使用MySQL命令“desc table_name;”即可查看表名为table_name的表结构。

desc table_name;

使用MySQL命令“show create table table_name;” ，查看名为table_name表的详细信息。

show create table table_name;

查询数据

语法格式

select 字段列表
from 数据源
[ where条件表达式 ]
[ group by 分组字段
[ having条件表达式 ] 
]
[ order by 排序字段 [ asc | desc ] ]

插入数据

INSERT INTO <表名> [ (<列名1> [,<列名2>...] ) ] VALUES(<值>)