MySQL数据库基本概念

本文最后更新于：March 9, 2019 pm

MySQL学习笔记。主要是各类基本定义和概念的了解。

1、什么是数据库？

1.1 数据（Data）的定义

描述事物的符号记录称为数据。

1.2 数据库（DataBase）的定义

数据库是长期储存在计算机内的，有组织的，可共享的大量数据的集合。

数据库中的数据按一定的数据模型组织、描述和储存，具有较小的冗余度、较高的数据独立性和易扩展性，并可为各种用户共享。概况地讲，数据库数据具有永久存储、有组织和可共享三个特点。

1.3 数据库管理系统（DataBase Management System）的定义##

数据库管理系统是位于用户与操作系统之间的一层数据管理软件。

数据库管理系统是一个系统软件，它的任务是科学地组织和存储数据，高效地获取和维护数据。

DBMS的主要功能有数据定义功能，数据组织、存储和管理，数据操纵功能，数据库的事务管理和运行管理，数据库的建立和维护功能，其他功能。

1.4 数据库系统（DataBase System）的定义

数据库系统是由数据库、数据库管理系统（及其应用开发工具）、应用程序和数据库管理员（DataBase Administrator）组成的存储、管理、处理和维护数据的系统。

1.5 存储数据库的介质

介质主要分为两种：硬盘和内存

2、数据库的分类和区别

2.1 分类

数据库基于存储介质的不同，可以分为两类：关系型数据库（SQL）和非关系型数据库（NoSQL）。

NoSQL：Not Only SQL， 不是关系型的数据库都叫做非关系型数据库

此外还可以基于对数据的处理方式的不同进行分类，还有诸如面向对象型数据库（object-oriented database(OODB)）等。

2.2区别

2.2.1 SQL

  • 安全（保存到磁盘，断电不会丢失）
  • 基于关系的数据库便于人类理解和使用
  • 但是比较浪费空间（使用二维表，对应的表格中不论有没有数据都要占用空间）

2.2.2 NoSQL

  • 效率高（因为存储在内存中，内存的速度要远高于硬盘）
  • 不安全（内存断点丢失数据）

3、常见的数据库

3.1 关系型数据库

大型：Oracle,DB2
中型：SQL-SERVER,MySQL
小型：access等

3.2 非关系型数据库

memcached(小)，mongodb（中），redis（大，可实现断电不丢失数据）

4、什么是关系型数据库

4.1 定义

关系型数据库是一种建立在关系模型（数学模型）上的数据库。

4.2 关系模型

关系模型是一种建立在关系上的模型，关系模型包含下列三个部分：

  • 数据结构：解决数据存储的问题，使用二维表来存储数据（包括行和列）
  • 操作指令集合：所有的SQL语句
  • 完整性约束：表内数据约束（字段与字段），表与表之间的数据约束（外键）

数据完整性约束指的是为了防止不符合规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，DBMS自动按照一定的约束条件对数据进行监测，使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容。

5、关系型数据库的设计

关系型数据库：从需要存储的数据需求中分析，如果 是一类数据（实体事物）应该设计成一张二维表。

关系型数据库：维护实体内部，实体与实体之间的联系。

表是由表头和数据部分组成。其中表头为字段名，用来规定数据的名称；数据组成部分为实际存储的数据单元。

如下所示为一张简单的二维表:

表头 字段名1 字段名2
数据单元a 数据a1 数据a2
数据单元b 数据b1 数据b2
数据单元c 数据c1 数据c2

6、简单案例分析

以教师、学生、班级三者为核心的教学系统案例进行一个简单的分析：

  1. 找出该教学系统中的所有实体：教师、学生、班级

  2. 列出每个实体对应所拥有的数据信息：（此处仅列举少量数据）

  • 教师：姓名、年龄、职称
  • 学生：姓名、学号、专业
  • 班级：班级名称、班级编号、上课地点

下面开始建表

6.1 实体内部联系

下面是一张学生的信息表

姓名 学号 专业
小明 001 计算机
小红 002 艺术设计
小黑 003 null

这里我们可以看到，表中小黑的专业这一栏是空白的（null），但是这一栏的位置还是要给小黑留着，不能就这样将其删除，这就是为什么关系型数据库占用位置的原因。

  • 第二行的所有字段（即整个第二行）都是在描述小明的个人信息，即描述小明这个学生，这个是内部联系。
  • 第二列的所有字段都是学号，即整个第二列只能存放对应的学号信息，这个是内部约束。

6.2 实体与实体之间的关系

每个学生肯定属于某个班级，而一个班级应该有多个学生，这种是一对多的对应关系。

这里以班级和学生为例

首先我们补充一个班级表，还有补充限定规则，表中的班级名称和班级编号是唯一不可重复的，而上课地点则不是。

班级表

班级编号 班级名称 上课地点
CS01 计算机1班 D301
CS02 计算机2班 E204
AD01 艺术设计1班 G306
AD02 艺术设计2班 G407

这个时候我们如果需要把学生和班级之间建立联系，有两种选择：

  • 在班级表中插入学生信息
  • 在学生表中插入班级信息

因为我们知道班级和学生是一对多的关系，一个班级可以有很多个学生，而一个学生只能属于一个班级，因此在学生表中插入班级信息则只需要多添加一列字段即可。
为了保证添加班级信息的时候只能够找到唯一一个对应的班级而不发生重复冲突的情况，对应的班级数据信息必须是在班级表中唯一不重复的，也就是班级编号或者是班级名称。

于是我们得到了下表

带班级信息的学生表

姓名 学号 专业 班级
小明 001 计算机 CS01
小红 002 艺术设计 AD02
小黑 003 null null

行/记录和列/字段的区别

行/记录：row/record，本质相同，都是指表中的一行/一条记录。

列/字段：column/field，本质相同，都是指表中的一列/一个字段。

只是行和列是从二维表的结构角度出发，而记录和字段是从数据库的数据角度出发。

7、什么是SQL

  • SQL：Structured Query Language，结构化查询语言，（并不只是查询操作，也有其他操作，只是因为数据库中的99%的操作都是查询操作）

SQL分为三个部分：

  • DDL：Data Definition Language，数据定义语言，用来维护存储数据的结构（数据库、表），代表指令：create，drop，alter等

  • DML：Data Manipulation Language，数据操纵语言，用来对数据进行操作（数据表中的内容），代表指令：insert，delete，update等（分别对应增删改三个操作）；

其中在DML内部又专门划分出一个分类：DQL：Data Query Language：数据查询语言，如select

  • DCL：Data Control Language，数据控制语言，主要是负责权限管理（用户），代表指令：grant，revoke等。（对应分配和回收权限）

注意，SQL是关系型数据库的操作指令，SQL是一种约束但不强制（类似W3C标准）。这意味着不同的数据库如Oracle和MySQL之间的指令不一定能通用，可能会有细微的差别。

8、MySQL数据库

MySQL数据库是一种C/S结构的软件：客户端/服务端。
特点：若想访问服务器必须通过客服端（服务器一直运行，客户端在需要使用的时候运行）

8.1 交互方式

  1. 客户端连接认证：连接服务器，认证身份：mysql.exe -hPup

    1
    mysql -hlocalhost -P3306 -uroot -p

其中h是找主机的IP，这里是localhost即为本机，P是找端口，mysql对应的是3306端口，u是用户，这里是root用户，p则是使用密码

  1. 客户端发送SQL指令
  2. 服务器接受SQL指令；处理SQL指令；返回操作结果
  3. 客户端接受结果：显示结果
  4. 断开连接（释放资源：保证服务器的并发性）

9、MySQL服务器对象

因为我们没有办法完全了解服务器内部的内容，只能粗略地去分析数据库服务器内部的结构。

将MySQL服务器内部对象分为四层：

数据库管理系统（DBMS）-> 数据库（DB）-> 数据表（table）-> 字段（field）

10、SQL基本操作

基本操作：增删改查

将SQL的基本操作根据操作对象分为三类：库操作，表操作（字段），数据操作

11、数据库操作

11.1 新增数据库

11.1.1 指令

1
create database 数据库名 [库选项]

11.1.2数据库命名规则

  • 数据库名字不能使用关键字或者保留字
  • 如果使用关键字或者保留字，需要使用反引号把数据库名字括起来
  • 保证服务器可以识别中文的情况下可以使用中文命名数据库，但是非常不建议这样做

11.1.3 库选项

库选项：用来约束数据库，分为两个选项

  • 字符集设定：charset/character set 具体字符集（数据存储的编码格式）
  • 校对集设定：collate 具体校对集（数据比较的规则）

11.1.4 注释

双中划线+空格或者使用井号

1
2
-- 这是一个注释
# 这也是一个注释

11.1.5 报错规则

静默模式，即只会告知报错的大概位置而不会告知错误的原因。

11.1.6 创建了数据库之后发生了什么

  1. 在数据库系统中，增加了对应的数据库信息；
  2. 会在保存数据库的文件目录下新增一个对应数据库名字的文件夹；
  3. 每个数据库文件夹中都会有一个对应的db.opt文件，文件中记录库选项

▼图中为对应数据库的字符集设定和校对集设定。（校对集依赖字符集）

11.2 查看数据库

11.2.1 查看所有数据库

1
mysql> show databases;

11.2.2 查看指定部分的数据库（模糊查询）

1
show databases like 'pattern'；

其中pattern是匹配模式的意思，分为两种匹配模式

%：匹配多个字符
_:匹配单个字符

查看以mydata_开头的数据库时，需要对_进行转义处理，即在前面加一个反斜杠，否则会查询错误，因为系统会认为mydata_中的_是匹配单个字符，而%是匹配多个字符，这样子的话：

1
2
3
show databases like 'mydata_%'

show databases like 'mydata%'

这两条语句的实际使用效果就是一样的了。

正确的操作应该是

1
show databases like 'mydata\_%'

11.2.3 查看创建数据库的时候使用的语句

1
show create database 数据库名;

因为SQL是一种编译型语言，所以数据库在执行SQL语句之前会对其进行优化，导致最终我们查询到的创建数据库时使用的语句和我们输入的语句并不完全一致。

11.3 更新数据库

  • 数据库名字不可以修改（修改名字不安全，牵一发而动全身）

  • 数据库的修改 仅限于库选项，即字符集和校对集（注意校对集依赖字符集）

    alter database 数据库名 [库选项]

  • charset/character set = 字符集 //注意此处可以使用=也可以不用，但是一般不用

  • collate = 校对集

一般不轻易修改库选项

11.4 删除数据库

数据库属于结构，所以使用ddl语言，即drop语句；

1
drop database 数据库名;

注意：数据库删除后极难恢复，一定要注意备份，不要轻易删库

11.4.1 数据库删除后发生了什么

  1. 在数据库内部该数据库被删除了；
  2. 数据库对应的文件夹也被递归删除了；