MySQL之数据库基础知识，库和表的操作以及基础数据类型

一.数据库基础知识

1.1 什么是数据库

在我们学习Linux时说过一切皆文件，而文件是由内容加属性构成的。所以数据其实也就是文件中的内容，我们对数据进行操作也就是对文件的内容进行修改但是我们想要完成对数据的非数值类操作比如增加删除查找等就需要我们自己一个一个的来进行操作所以Linux本身对数据的管理工作是不太便利的，这就需要用到我们说的数据库了。
数据库是20 世纪 60 年代末发展起来的⼀项重要技术，已经成为计算机科学与技术的⼀个重要分
⽀。数据库技术主要是⽤来解决数据处理的⾮数值计算问题，数据处理的主要内容是数据的存储、查询、修改、排序和统计等。

1.2主流数据库

数据库分为关系类数据库和非关系类数据库，关系类数据库就指使用了关系模型来组织数据的数据库具体表现为数据库采用的是二维表格模型来完成数据的关系构成。
关系类数据库：

• Oracle： 甲骨文产品，适合大型项目，业内最强的数据库，没有之⼀，需要收费。
• MySQL：开源免费，最受欢迎的数据库，在中小企业中普及率非常高，属于甲骨文，主要用在电商，论坛等。
• PostgreSQL：加州⼤学伯克利分校计算机系开发的关系型数据库，可以免费使用，修改和分发。
• SQL Sever：微软推出的⼀款专业级数据库，与旗下的操作系统，编程环境配合使用，适用于中大型项目。
• SQLite：⼀款轻型的数据库，占用资源非常低，常用于嵌入式设备，手机APP，桌面程序等场景。
  非关系类数据库：
• Redis：流行的基于键值对的内存数据库，常用作缓存，支持数据持久化，支持多种数据结构。
• MongoDB：基于NoSQL的文档型数据库，易扩展，高性能，高可用性，支持丰富的查询和聚合操作。

1.3MySQL的实现

MySQL的下载和安装我就不再赘述了大家可以看其他人的文章，在安装完成MySQL后我们可以发现我们的系统中有一个守护进程mysqld在运行中。那就是MySQL的服务器，是直接部署在我们本地的。

而我们使用mysql命令打开的MySQL则是客户端，因为mysql使用的客户端和服务器的通信方式就是最基础的C/S架构模式即客户端/服务器架构模式也就是客户端和服务器直接进行通信。

大部分的数据库服务名称都有着一个单词SQL，那么这个单词有着什么特殊含义吗？
SQL（Structured Query Language）是结构化查询语⾔的简称，是⼀种数据库查询和程序设计语⾔，⽤于存取数据以及查询、更新和管理关系数据库。
而SQL也被分为三种：DDL，DML，DCL。

• DDL（Data Definition Language）：数据定义语⾔，⽤来维护存储数据的结构，代表指令: create, drop, alter。
• DML（Data Manipulation Language）：数据操纵语⾔，⽤来对数据进⾏操作，代表指令： insert，delete，update，select。
• DCL（Data Control Language）：数据控制语⾔，主要负责权限管理和事务，代表指令： grant，revoke，commit。

不同的数据库服务器实现也不尽相同，对于MySQL来说它一样利用了常规的分层设计来完成自己的功能。
MySQL8.0服务器是由连接池、服务管理工具和公共组件、NoSQL接口、SQL接口、解析器、优化器、缓存、存储引擎、文件系统组成。MySQL还为各种编程语⾔提供了⼀套用于外部程序访问服务器的连接器。整体架构图如下所示：

从这张图中我们可以发现MySQL分为了四层，最顶上的连接层包含了连接池用来管理客户端发起的连接，其下的服务层则是由不同的组件来完成用于完成对SQL语句的解析，改写等。再往下就是存储引擎层，我们可以发现其中很多的存储引擎但是我们最常用的其实就是InnoDB和MyISAM这两种。话虽如此但是我们还需要大致了解存储引擎是一个什么东西：存储引擎是MySQL数据库处理数据的核心组件，不同的存储引擎对数据如何存储、索引、更新和查询的实现方式各有不同，在不同的业务场景可以选用合适的存储引擎。MySQL服务器采⽤可插拔的存储引擎架构，在服务器运行时可以动态的加载和卸载。
在MySQL中我们可以使用show engines来查看当前服务器支持哪些存储引擎。

最下层就是文件系统层了，这里面就连接了操作系统的文件管理。那么大家可能会有疑惑为什么数据库还会牵涉到文件呢明明它就是为了解决文件的管理应运而生的，大家要知道MySQL是在操作系统的基础上使用的所以它也逃脱不了Linux中一切皆文件这句话，只是它通过一系列的封装来将对文件的一系列操作变成了一个一个便利的语句就像机器语言和高级语言来说，高级语言是基于机器语言的基础上变得更加便利更加好用但是它最后也就会被翻译成一段一段的机器语言。
想要证明MySQL同样逃脱不了文件这句话我们只需要查看一个目录即可。


从这两个图中我们可以发现无论是数据库还是表其实底层就是操作系统中的一个个目录和文件而已，这也就证明了为什么MySQL的第四层是文件系统层因为无论是数据库的操作还是表的操作最后都是文件的增删查改。

二.数据库的操作

在大致了解数据库的作用后我们就可以开始上手使用MySQL了，首先我们就需要了解如何对数据库进行增删查改。

2.1增加数据库

• create {database | schema} [if not exists] db_name [create_option];

我们将这段语法拆成三部分

1. create {database | schema} [if not exists]
   这部分中{}中由database和scheme我们必须进行二选一，这两个的功能是一样的我们挑其一即可。而[]中的if not exists则是表明名称为db_name的数据库如果不存在就创建存在了就会报错。
   

2. db_name
   这就是很简单的数据库名称。

3. [create_option]
   这同样是一个可选项，它可以完成创建数据库时的一些扩展性的操作，其中比较常用的就是设置数据库的字符集编码CHARACTERSET[=]charset_name，校验集编码COLLATE[=]collation_name以及数据库是否加密ENCRYPTION[=]{‘Y’|‘N’}。
   

在创建数据库的可选项中我们提到了数据库的字符集和校验集，这两个又是什么意思呢？首先我们可以使用show charset来查看数据库支持的字符集和校验集

可以看到我们数据库支持的字符集和校验集是很多的，并且还都是成双成对的。而MySQL中字符集(character sets)是指一系列符号以及符号对应的编码的集合，比如英文字母可以用ASCII编码，中文可以用GBK或者UTF8编码。校验规则(collations)则是指一种比较字符的规则，这种比较规则决定了MySQL如何进行排序以及如何对字符比较大小，所以校验规则又可以被叫做排序规则。
不同的字符集绝对了数据在MySQL中是如何进行存储的，而校验集则是决定了MySQL中如何对数据进行排序。
我们可以通过show variables like ‘%character%’;来查看MySQL默认的字符集

也通过show variables like ‘%collation%’;来查看MySQL默认的排序集

2.2删除数据库

• drop {database | schema} [if exists] db_name;

删除数据库相对于创建数据库就更加简单了只有一个可选项if exists来判断数据库是否存在即可。但是我们要注意的是数据库里的数据一旦删除是无法找回的，所以尽量不要删除一个数据库就算真的要删除一个数据库了也要先将这个数据库进行备份再完成删除的操作。

那么要如何进行数据库的备份和恢复呢？

• mysqldump -u 用户名 -p [密码] [选项] [库名] > /备份路径/备份文件名;
• source 备份文件的绝对路径;

我们接下来直接进行演示

那么这个备份文件中到底存储了什么呢？难道里面存储的就是我们数据库中的数据吗？

大家可以发现这个备份文件中存储的并不是一些数据而是存储着我们对数据库的操作，创建表插入数据等等这些操作。那么恢复大家也就很好理解，并不是将数据直接恢复到表中而是将这些操作再进行一遍。

为什么我们在进行恢复了之后缺出现报错了呢？大家仔细看一下报错的内容是没有数据库被选中再仔细查看一下我们备份文件中的内容，我们可以发现其中第一个MySQL指令是drop table if exists student。这是删除名字为student的表，并没有牵涉到我们数据库的创建。所以如果我们想要恢复备份的文件我们需要自己创建一个数据库再在其中进行恢复。

同样我们可以对数据库进行备份也可以对表进行备份，操作都是相同的我就再演示了。

2.3修改数据库

• alter {database | schema} [db_name] [alter_option];

对数据库的修改一般都是修改数据库的字符集和校验集，内容很简单我就不再演示了大家可以自己去试一试。

2.4查询数据库

• show databases;

查询语句也非常的简单就这么一句即可。

三.表的操作

在说明了对数据库的操作后我们接下来就是说明表的操作了，一样是增删查改四部分。

3.1增加表

• create [temporary] table [if not exists] tbl_name (field datatype [约束] [comment ‘注释内容’],field datatype [约束] [comment ‘注释内容’],…) [create option]

增加一个表相对来说比较复杂我们同样将其分为三部分来说

1. create [temporary] table [if not exists]
   这部分中我们只需要说明temporay是什么意思即可，它表明创建的是一个临时表具体何时使用我们之后会介绍
2. tbl_name (field datatype [约束] [comment ‘注释内容’],field datatype [约束] [comment ‘注释内容’],…)
   在创建一个表时我们需要说明这个表里有多少列以及每列中存储的数据又是什么类型所以field是列名，datatype是数据类型，而[]中的注释内容则很明显就是注释，约束呢也需要我们后面再进行讲述现在只需要知道有这个东西即可。
3. [create option]
   这于创建数据库时相同，我们可以选择这个表是什么字符集和校验集同时也可以选择是哪种存储引擎总而言之这个选项就是拓展性的意义。

例如我们创建一个表来存储学生信息，其中包含学生编号，名字，性别，身份证号同时指定字符集是utf8mb4，排序集是utf8mb4_0900_ai_ci。

3.3修改表

• alter table tbl_name [alter_option]

要注意我们对表的修改仅仅停留在对结构的调整也可以说是对他属性的调整并不是对其内容的调整。
对表的修改主要是在表中增加列修改列删除列重命名列或者表。这四项分别都有着一个关键词来代表

1. add [column] col_name column_definition [first | after col_name]
   增加列我们需要说明新列的名字，存储的数据类型同时还可以指定这列是在原先哪列的前面或者后面。
   例如我们在学生信息表中增加一列生日在性别之后身份证号之前。
   

2. modify [column] col_name column_definition [first | after col_name]
   对列的修改一般都是对数据类型的修改所以我们需要说明列的名字以及新的数据类型，同样也可以设置修改后列的位置。
   例如我们将刚刚的生日的数据类型从date改到int并且将位置改为身份证号之后。
   

3. drop [column] col_name
   删除列则很简单了和删除数据库和表类似。
   例如我们将生日这列给删除了。
   

4. rename column old_col_name to new_col_name
   重命名列也很简单就只需要说明旧的列名和新的列名即可
   例如我们将学生id改成电话号码。
   

5. rename [to | as] new_tbl_name
   例如将表student的名字改成teacher
   

3.4查询表

• desc tbl_name

查询表的具体信息

在介绍了数据库的操作和表的操作后我有个问题想问大家，我们知道MySQL的指令统称为sql而其中也分为三类：DDL，DML，DCL。那么我们刚刚对数据库以及表的操作属于这三种的哪一种呢？答案是DDL，因为无论是数据库和表我们对它们的增删查改统统只是在定义范围而已定义数据库和表的名字，定义它使用什么存储引擎，定义它的字符集和校验集，定义表中列的名字和数据类型。这个大家需要注意！

四.数据类型

在创建表时我们需要说明列的数据类型那么在MySQL中有多少种数据类型呢每种数据类型又代表什么意思其中有没有需要注意的呢？
在MySQL中我们通常将其分为四种数据类型：数据值类型，字符串类型，二进制类型，日期类型。这四种类型具体又有着不同的种类我们就一个一个的来说。

4.1数据值类型

这个数据类型我们介绍其中的七种

类型	大小	说明
tinyint[(M)]	1byte	取值范围 -2^7 ~ 2^7-1 ，无符号取值范围 2^8-1
bool	1byte	TINYINT(1)的同义词。值为零被认为是假，非零值被认为是true
int[(M)]	4byte	取值范围 -2^31 ~ 2^31-1 ，无符号取值范围 2^32-1
bigint[(M)]	8byte	取值范围 -2^63 ~ 2^63-1 ，无符号取值范围 2^64-1
float[(M,D)]	4byte	单精度浮点型，M是总位数，D是小数点后面的位数，大约可以精确到小数点后7位
double[(M,D)]	8byte	双精度浮点型，M是总位数，D是小数点后面的位数，大约可以精确到小数点后15位。
decimal[(M,D)]	动态	不存在精度损失，M是总位数，D是小数点后的位数。DECIMAL的最大位数(M)为65，最大小数位数(D)为30。如果省略M，则默认为10，如果省略D，则默认为0。M中不计算小数点和负数的-号，如果D为0，则值没有小数点和小数部分。

这七个数据值类型就是我们比较常见的了，我们可以将其分为整数类型和浮点数类型两种，我们来分开讲述一下其中的一些注意事项。

1. 对于整数类型来说无符号和有符号的取值范围是不同的，如果我们想要使用无符号类型我们只需要在其后加一个unsigned即可。
2. 整数类型中的M代表了显示宽度，对于整数类型中它作用不是很大所以不太需要专门设置，对于整数类型我们只需要根据取值范围和大小来综合挑选使用哪个即可。
3. 浮点数中的M代表精度而D代表标度，具体一点就是M=整数位+小数位而D=小数位。所以这两者是结合在一起使用的，例如我们设置float(5,2)那么它的范围就是-999.99~999.99，超过这个范围就会进行报错。对于精度和标度如果我们不显式设置那么MySQL则会根据实际的精度来进行显示。
4. 浮点数也可以使用unsigned但是并不会改变取值范围
5. 无论浮点数是否规定了精度和标度MySQL都会对存储的数值进行处理，例如我们定义了float(5,2)它的取值范围就是-999.99~999.99。如果整数部分超过了999就会发生报错，如果是小数部分超过了就会进行四舍五入比如999.009就会被四舍五入成999.01这就不会发生报错但是如果四舍五入后导致整数部分超过限制也会发生报错就像999.997四舍五入是1000.00。
6. decimal[(M,D)]准确来说不算是浮点数而是定点数，由于浮点数涉及到四舍五入以及精度的问题所以浮点数类型在精度要求很高的情况下不适合使用。而定点数在MySQL中是以字符串的形式存储的所以它的精度是完全没问题的，这就造成了这两者的使用场景不同，浮点数适用于取值范围大但是对精度要求不太高的场景下而定点数则是适用于取值范围但是对精度要求极高的场景下。

4.2字符串类型

字符串类型同样我们取其中比较重要的几个来说明

类型	说明
char[(M)]	固定长度字符串， M 表示长度，以字符为单位，取值范围 0 ~ 255 ， M 省略则长度为 1
varchar[(M)]	可变长度字符串， M 表示字符最大长度，的范围 0 ~ 65535 ，有效字符个数取决于实际字符数和使用的字符集
test[(M)]	文本类型，最大长度为65535 (2^16 - 1)个字符，有效字符个数取决于使用的字符集

同样我们说一下其中的一些注意事项

1. 在使用char类型时我们可以指定它的长度如果不指定默认是1，在指定了长度后如果其中存储的字符没有达到规定的长度的话会自动在右侧填充空格来达到要求，但是当MySQL检索char类型的数据时也会自动将右侧的空格去除，所以char类型无论存储了多少的内容它的字节数始终都是指定的M的大小。
2. 使用varchar时必须指定最大长度也是M否则会进行报错但是于char不同的是如果我们使用varchar存储字符时没有达到规定的最大长度其不会在右侧进行填充空格，所以MySQL检索时也不会去除右侧的空格。这也就导致了varchar类型占用的存储空间是实际存储字符串的长度加1。也就是说它是会进行变化的也就是为什么varchar被叫做可变长度字符串，我们可以将其联想成我们学习的数据结构中的容量capacity和大小size，容量是规定的最大的存储空间也就是M而size则是实际的存储空间。
3. 对于varchar来说我们同样需要注意的是M表示的是字符数不是字节数，在我们常用的utf8中一个字符占用三个字节，在MySQL4.0版本及以下时M代表的是字节数所以varchar(24)代表的是24个字节也就是8个字符（utf8）而在MySQL5.0版本以上将M改成了字符数所以varchar(24)代表的是24个字符也就是24*8=192个字节。所以上面我们说varchar的范围是0~65535也是按照字节数来算的换算成字符数的话varchar的范围是0-21845。
4. 同样由于varchar是可变长度字符串所以它必须在存储空间中使用一个字符来表示当前的长度（注意：不同的字符集一共字符占用的字节数是不同的有些是一个字节有些是两个字节，这里我们统统用utf8为例子）所以真正的varchar的可用字符数范围是0~21844。

值	char(4)	所需要的空间大小	varchar(4)	所需要的空间大小
‘’	‘’	12byte	‘’	3bytes
‘ab’	‘ab’	12byte	‘ab’	9byte
‘abcd’	‘abcd’	12byte	‘abcd’	13byte
‘abcdefg’	x	超出最大长度	x	超出最大长度

对于字符串类型我们现阶段只需要了解char和varchar的区别即可。

4.3二进制类型

类型	说明
bit[(M)]	位值类型。M表示每个值的位数，取值范围为 1 ~ 64 。如果省略M，则默认为1 。
binary[(M)]	固定长度二进制字节，于char类似，但存储的是二进制字节而不是字符串。M表示长度，以字节为单位，取值范围为0~255，M省略则长度为1
varbinary	可变长度二进制字节，于varchar类似，但存储的是二进制字节而不是字符串。M表示长度，以字节为单位
blob[(M)]	二进制字节类型，最大长度为16,777,215 (2^24 - 1)个字节

对于二进制类型我们可以从上面的数据值类型和字符串类型中各取出来几个组成这个类型，这个类型于其他的类型不同的是他们是以二进制字节为单位的，仅仅也就这方面不同而已。唯一需要注意的是blob这个类型，这个类型是一个二进制的大对象它可以存储一个很多字节所以可以用来存储图片，音频和视频文件。

4.4日期类型

类型	大小	说明	0值
date	3bytes	⽇期类型，⽀持范围 1000-01-01 ~ 9999-12-31，显⽰格式为 YYYY-MM-DD	0000-00-00
datetime[(fsp)]	8bytes	⽇期类型和时间类型的组合，⽀持范围 1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.499999，显⽰格式为 YYYY-MM-DD hh:mm:ss[.fraction]	0000-00-00 00:00:00
timestamp[(fsp)]	4bytes	时间戳类型，⽀持范围 1970-01-01 00:00:01.000000 ~ 20388-01-19 03:14:07.499999	0000-00-00 00:00:00

日期类型相对于其他的几个更加的简单，使用的种类也比较少注意事项也比较简单

1. date类型只显示日期并且这个日期是需要你自己设置的。
2. datetime则是会显示日期和时间，同样也是需要你自己设置的。
3. timestamp的显示形式于datetime是相同的，但是其中的原理是不一样的timestamp是会自动计算当前的时间戳并转化为日期和时间来进行显示的。所以timestamp不需要手动设置它会在你创建修改的时候自动发生变化。
4. 这三种类型的使用场景各不相同，date类型适用于生日这种不需要精确到几分几秒的场景下使用而datetime则是会更加精确。这两者都需要我们自己设置所以插入时会有点麻烦，而timestamp由于会自动发生变化所以适用于记录比如日志功能，我们需要记录每次操作的时间就可以利用timestamp。
5. fsp 为可选设置，⽤来指定小数秒精度，范围从0到6，值为0表示没有小数部分，如果省略，默认精度为0

4.5enum和set类型

除了上面的四大类型我还要和大家介绍两个用于选择的类型即enum类型和set类型。

4.5.1enum类型

类型	大小	说明
enum(data,data,…)	一到二个字节	ENUM类型也叫作枚举类型，设置字段值时，ENUM类型只允许从成员中选取单个值，不能一次选取多个值。

1. 当ENUM类型包含1～255个成员时，需要1个字节的存储空间。当ENUM类型包含256～65535个成员时，需要2个字节的存储空间。ENUM类型的成员个数的上限为65535个。
2. enum类型是我们用来从多个字段中进行选择的一个类型，比如我们在表中设置了sport enum(‘足球’,‘篮球’,‘网球’)。那么我们在具体插入行的时候就需要在这三种里挑选一种填入，就像我们平时做单选题一样。
3. 插入行时我们不仅可以直接写出具体的成员名称也可以用数字来代替内容，对于enum类型它会把里面的成员转化为一种类似于数组的形式，所以我们可以每个成员的下标来代替比如上面的sport我们可以用使用insert into sport values('足球‘),(‘篮球’);来插入也可以使用insert into sport values(1),(‘2’)来插入。这两者是相等的功能都是插入足球和篮球。

4.5.2set类型

在了解了enum类型后set类型就更好理解了，enum类型是单选题那么set就是多选题。

类型	大小	说明
set(data,data,…)	一到八个字节	设置字段值时，set类型允许从成员中选取0个或多个值

1. 当SET类型包含的成员个数不同时，其所占用的存储空间也是不同的，但是set类型的最大存储空间是64个成员也就是八个字节。
2. set类型和enum类型相同也可以通过数字来代表插入的成员只是于enum不同的是set不再是用下标来代表成员而是利用位图的形式来表示每个成员都代表一个二进制位，只要让对应的二进制位为1即代表你要插入对应的成员例如hobby set('吃饭’，‘睡觉’，‘编程’，‘打豆豆’)。如果我们想要让一行里的这列选择全部的成员我们可以一个一个的写入即insert into hobby('吃饭’，‘睡觉’，‘编程’，‘打豆豆’)，也可以直接使用insert into hobby(15)。因为我们有四个成员也就是四个bit位即0000~1111，全选也就是1111转为十进制就是15。