什么是数据库

什么叫做数据库：是一整套的数据存储容灾解决方案

数据库文件，其实就是磁盘文件
数据库=数据库服务器+数据库客户端，安装的MySQL就是安装客户端与服务端。

数据库客户端就是我们用命令mysql -uroot -p登录的或者是各种图形界面的客户端

MySQL不止有客户端，也有自己的服务器，是一个网络服务器，可以用ps命令查看

ps axj | head -nl && ps axj | gerp mysql
1

该进程的父进程为1，组id和会话id相同的，是一个守护进程，MySQL服务器底层也是套接字协议。

MySQL本质是网络服务器进程和文件的关系。

传统的文件保存数据有以下几个缺点：
文件的安全性问题
文件不利于数据查询和管理
文件不利于存储海量数据
文件在程序中控制不方便

所以文件操作虽然具有存储数据的能力，但是不能满足一些特定应用场景中的格式化数据的存储为了解决上述问题，专家们设计出更加利于管理数据的东西——数据库，它能更有效的管理数据。

数据库的文件一般有两种存在地点：
磁盘——具有持续化能力的数据库，典型的有MySQL。
内存——为了增加存储效率的中间件数据库，典型的有Redis
数据库不光要考虑使用方便与体系效率的问题，还要考虑安全问题，所以它的数据在某一个时刻内也不一定所有的数据都会刷新到磁盘上，数据库本身也有自己的缓存机制，也就是说数据库的数据一定是永久存在磁盘上，可能有一部分存在内存。有的内存级数据库大部分数据就是在内存。

主流的数据库
SQL Sever： 微软的产品，.Net程序员的最爱，中大型项目。
Oracle： 甲骨文产品，适合大型项目，复杂的业务逻辑，并发一般来说不如MySQL。
MySQL：世界上最受欢迎的数据库，属于甲骨文，并发性好，不适合做复杂的业务。主要用在电商，SNS，论坛。对简单的SQL处理效果好。
PostgreSQL :加州大学伯克利分校计算机系开发的关系型数据库，不管是私用，商用，还是学术研究使用，可以免费使用，修改和分发。
SQLite： 是一款轻型的数据库，是遵守ACID的关系型数据库管理系统，它包含在一个相对小的C库中。它的设计目标是嵌入式的，而且目前已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。
H2： 是一个用Java开发的嵌入式数据库，它本身只是一个类库，可以直接嵌入到应用项目中

服务器&&数据库&&表关系

所谓安装数据库服务器，只是在机器上安装了一个数据库管理系统程序，这个管理程序可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。

为保存应用中实体的数据，一般会在数据库中创建多个表，以保存程序中实体的数据。

在配置文件**/etc/my.cnf中的dataadir可以看到数据库的数据存放路径为/var/lib/mysql**，创建的数据库其实就是数据库进程在该目录下的帮我们新建了一个目录，删除数据库就是删掉这个目录，使用数据库就等价于cd到该目录下

创建表之前，一定要先选中数据库，进入到数据库目录下。创建表结构就是在指定的数据库目录下，新建普通文件。

数据存储表格结构

MySQL存储数据是以表格方式呈现的，包括行和列。

mysql> select * from student;
+----+-----+--------+--------------+
| id | sn  | name   | mail         |
+----+-----+--------+--------------+
|  1 | 123 | 张三   | 12345@qq.com |
|  5 | 456 | 李四   | 23456@qq.com |
|  6 | 789 | 王五   | 34567@qq.com |
+----+-----+--------+--------------+
1
2
3
4
5
6
7
8

一行消息被称为一条记录，一列代表一个属性，列的名称不能重复。

MySQL的整体架构

MySQL 是一个可移植的数据库，几乎能在当前所有的操作系统上运行，如 Unix/Linux、Windows、Mac 和Solaris。各种系统在底层实现方面各有不同，但是 MySQL 基本上能保证在各个平台上的物理体系结构的一致性。

MySQL在软件层被分为两层，第一层叫做网络服务器层，第二层叫做数据库插件引擎层。
网络服务器层分为两大部分：其中Connection Pool叫做连接池，可以同时让多个客户连接，除此之外服务器层还有很多模块:账户密码验证，词法语法分析，设置执行任务，开始执行。
开始执行之后就下面的数据库组件引擎层了，这里的引擎层就类似于驱动，真正的对文件进行操作，只不过整个MySQL都是应用层的

数据库插件引擎层的作用就是真实的进行数据的CURD，用户管理等操作。MySQL是一个可拔插的服务，本地支持什么样的存储引擎，想使用那个存储引擎，我就调对应的存储引擎。

SQL分类

DDL【data definition language】 数据定义语言，用来维护存储数据的结构
代表指令: create, drop, alter

DML【data manipulation language】 数据操纵语言，用来对数据进行操作
代表指令： insert，delete，update
DML中又单独分了一个DQL，数据查询语言，代表指令： select

DCL【Data Control Language】 数据控制语言，主要负责权限管理和事务
代表指令： grant，revoke，commit

存储引擎

存储引擎是：数据库管理系统，决定了如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等。
可以用以下命令查看当前数据库支持的插件

show engines \G;
1

默认使用的是InnoDB引擎，使用该引擎创建的表结构会在数据库目录下形成两个文件：表名.frm和表名.idb。
如果使用MyISAM引擎，默认在指定的database中形成的数据库文件是三个，分别是表名.frm（表结构）、表名.MYD（表数据）、表名.MYL（表索引）。

数据类型

数据库中的类型决定了在存储位置中占据的空间大小以及如何识别等问题

当我们插入数据时，因为数据类型本身有自己的取值范围，MySQL此时会不允许我们插入，在语言上出现这种情况时不会报错，任然可以写入，只是可能会写错。也就是说MySQL对数据的存储，是有更严格的约束的。

数值类型：

bit位字段类型

bit[(M)]
1

M表示每个值的位数，范围是1-64，可以让我们对数据进行bit位级别的操作,如果忽略M，默认为1。
位存储显示的时候默认以ASCII码表的形式显示，可能会有不可显字符，一般不建议使用位存储，除非这个数据就是单纯的给机器使用的，否则维护和调试都不太方便。

tinyint对应的就是C语言中的char，一个字节
smallint对应的就是short，两个字节
bigint对应long long，八个字节

小数类型
float和double对应的就是C语言的两个浮点数
float类型

float(m,d) [unsigned];
1

m指定整体的显示长度，d指定小数位数，默认大小为4字节
保存时会根据设定的范围进行四舍五入，如果四舍五入之后的值在范围之外，则不可以插入。
比如float(4,2)的取值范围就是-99.99~99.99，float(4,2) unsigned 的范围就是0~99.99，其实无符号的就是有符号的一个子集，

dceimal类型
对于数值类型，大部分都可以在C语言中找到对应
decimal也是一个浮点数，相比于上面的两个他的精度更优

fecimal(m,d) [unsigned];
1

参数的意义和float的一模一样，但是decimal的精度会更高一些。
decimal在存储时，存的是什么就是什么，而float则可能会有一定的精度损失。
float的最大精度大约是7位，decimal的整数最大支持65位，小数最大支持30位，如果m被省略，则默认为10，d被省略，默认为0。

字符串类型

char类型

char(L);
1

固定长度的字符串，L表示字符串的长度，最大长度是255。这里的字符串没有\0结尾。
固定长度的字符串，其长度本身也是一种约束。
插入汉字也符合规定的长度，而中文在存储时会根据编码不同，一个汉字可能占2字节或3字节，但是肯定不是按照ASCII表存的，但是其存世居然符合长度的限制，可以插入规定长度的汉字，所有数据库中的“字符”并不等于C/C++中的字符，C/C++中的字符就是指char，一个字节，而数据库的字符则是包含了普通字母，符号，中文等等，不一定是一字节。

MySQL限制“字符”的概念不是字节的好处在于可以让用户不用再关系复杂的编码细节。

varchar类型

varchar(L);
1

变长的字符串，L表示字符长度，设定长度的范围是0~65535字节
在设定的范围之内，可以插入。
在varchar的指定范围中，有1~3个字节用来记录数据的大小，所有有效字节数是最大是65532.
在utf8中，一个字符其实占3个字节，那么字符的最大值就是65532/3=21844，如果编码是gbk，一个字符占2个字节，则参数的最大值为65532/2=32766。
varchar所谓的变长的意义不是说定义好之后可以修改他的最大长度，而是当你插入的值长度不超过自定义范围的情况下，你使用多少字节就给你开辟多大的字节空间，而char在插入时，无论插入的内容有多大，都会直接开辟好自定义范围的空间。

定长的磁盘空间比较浪费，但是效率更高，变长比较节省磁盘空间，但是效率低。

时间和日期类型

常见的日期格式有三种
date：日期，只有年-月-日，占三个字节
datetime：时间日期格式，“yyyy-mm-dd hh:mm:ss”，占用8字节
timestamp：时间戳，从1970年开始到现在累计的秒数，但是不是以秒数呈现，而是以“yyyy-mm-dd hh:mm:ss”形式呈现，默认就是当前时间。占用4字节
使用时间戳时当你进行增加修改时，时间戳会自动更新

枚举和集合类型

enum类型

enmu('选项1','选项2',...);
1

枚举提供了多个选项，但是这些选项中只能选一个，也就是说，在实际的单元格中只存储了一个只，而且出于效率考虑，这些值实际存储的是“数字”，每个选项值依次对应如下数字，插入时如果使用数字的方案，那么每个数字对应的值就是从1开始一直递加，一个数字对应一个值。

通过enum列查找

select * from [表名] where [集合名]='选项';
1

set类型

set('选项1','选项2',...);
1

提供了多个选项，最终一个单元格中，可以存储选项中的任意个值；而且出于效率考虑，这些值实际存储的也是是“数字”，如果使用数字的方案，并不是像enmu类似一个一个数字对应一个选项，其方案类似于bit位，一个bit位代表特定的选项，bit位的内容表示是否具有该选项。

enum只能多选一，set可以多选多，如果选择的字段没有在他们的限定选项中出现，MySQL会直接终止sql语句，这也是一种约束。

通过set查找

集合查询函数

select find_in_set('元素','集合');
1

在集合中查找元素，如果找到，就返回元素在集合中的位置，如果没有，就返回0.

查询选项严格匹配的行

select * from [表名] where [集合名]='选项';
1

查询包含指定选项的行

把集合中有该选项的挑出来
select * from [表名] where find_in_set(['选项'],[集合名]);

把集合中有该选项1并且集合不等于选项2的挑出来
select * from [表名] where find_in_set(['选项1'],[集合名]) and [集合] <> ['选项2'];
1
2
3
4
5

表的约束

当你在数据库中插入的数据不符合数量类型要求或者超出了对应的范围时，数据库没有默认行为，而是直接终止你的sql，这种现象本质上就属于一种约束。

什么是约束：本质就是MySQL通过限制用户操作的方式，达到维护数据本身的安全，完整性的一套方案。因为MySQL是一套数据存储解决方案，除了解决基本的数据存储功能之外，还要尽可能的保证数据的安全性，减少用户误操作的可能性。约束也是一整套的约束方案，会有各种各样的约束。

真正约束字段的是数据类型，但是数据类型约束很单一，在不同的业务逻辑中，可能还需要有一些额外的约束，更好的保证数据的合法性，也就是说约束不仅仅存在于数据库中，在用户业务逻辑编码时，也可以进行约束。

空属性

数据库中允许设置空属性，有两种：null(默认，可以为空)和not null(不可以为空)

空属性可以用来修饰某些数据库中的某些字段，表示是否可以为空。可以为空时插入的数据是否为空都不影响，但是不可以为空时插入的数据为空就会失败，因为空属性就是一种约束
使用方法：

[列名] [列数据类型] [属性：null/not null];
1

默认值

某一种数据会经常性的出现某个具体的值，可以在一开始设置一个默认值，用户在插入时如果没有给该字段赋值，那么该字段的值就是默认值，否则就是用户赋的值。

[列名] [数据类型] default [默认值]
1

如果设置了not null，则代表用户一定会填，在语义上default的设置就没有意义，所以一般这两个只设置一个，但是实际上如果同时设置两个，not null约束就是无效的

列描述

comment，没有实际含义，专门用来描述字段，会根据表创建语句保存，用来给程序员或DBA来进行了解。

[列名] [数据类型] not null comment '[描述内容]'
1

一般情况下具有强约束的字段会放在尽量靠前的位置，comment字段一般都放在最后

查看描述字段，需要使用下面的语句

show create table [表名] \G;
1

可以看到创建表结构时的描述字段，包括引擎和编码类型。

comment也没有约束什么东西，他只是一个描述，其最大的意义其实是告诉程序员列的信息，就像是一个注释，是一个软性的约束。

zerofill

如果你的整形数据的宽度小于设置的宽度，会自动填充0，其改变的只是显示结果，实际存储的内容不会被改变。

例如：

a int(5) unsigned zerofill
1

如果在a列插入1，则显示的值就是00001。他的作用是一种设置格式化输出，以特定的形式显示。建表时整形类型后面的( )里面的值就是其显示宽度。

主键

库的删除，修改，查找都直接或间接的使用到了查找功能，要查找，就需要一个目标键值，知道要找谁，可是一行记录往往包含了多列信息，所以就需要有一列信息用来区分他们的唯一性，这一列信息就叫做主键。

主键：primary key用来唯一的约束该字段里面的数据，不能重复，不能为空，一张表中最多只能有一个主键；主键所在的列通常是整数类型。

[列名] [数据类型] primary key;
1

主键列在desc查看表时Key的值是PRI。

设置了主键之后，默认该列是不能为空的，但是给主键设置了default，插入的时候就可以为空了，但是因为主键不可重复，默认值也一样，所以默认值只会生效一次，毫无意义。建议不要给主键设置默认值。

如果把某一列改成主键，那么他的空属性会被修改成NO。

主键在插入时如果和其他数值没有冲突就可以插，如果冲突了就不允许插，这就是主键约束。

添加主键

alter table [表名] add primary key([列名])
1

删除主键

alter table [表名] drop primary key;
1

只是删除主键属性，不会删除该列。

复合主键
主键必须有一个，但是不一定只能用一列，也可以用多列来共同组成一个主键，如果有多个字段组成主键，这就叫做复合主键。

设置符合主键可以放在建表的最后

primary key([列名1],[列名2],...)
1

复合主键只有所有的列都冲突了才算冲突，本质就是把组成主键的列值联合起来构成一个长字符串，只有严格一样的才称为主键冲突

自增长

auto_increment：当对应的字段，如果不给值，会自动的被系统触发，系统会从当前字段中已经有的最大值+1，得到一个新的不同的值。通常和主键搭配使用，作为逻辑主键。
如果要设置自增长，那么类型必须是整形，而且一个表里只能有一个自增长

[列名] int unsigned primary key auto_increment,
1

主键不一定是自增长，但是自增长一定是主键

一般在设置主键时，建议设置成与业务无关的数据字段充当主键，减少主键对业务逻辑的影响。这样自增长配合上自增长做出一个与业务逻辑无关的主键

获得最近一次插入的自增长字段的值
select last_insert_id()
1
2

索引
在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用就相当于书的目录，可以根据目录中的页码快速找到所需的内容，提高查找的效率。

索引是MySQL创建的，本身也会占用MySQL的资源，内存或磁盘空间，所以索引的本质就是用空间换时间。

索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。

唯一键

一张表中有往往有很多字段具有唯一性，所以不是主键具有唯一性，而是一个具有唯一性的数据被选择成为了主键。主键规定了不可以重复，那么别的在语义上也应该保持唯一性的数据MySQL也带保护起来，所以就有了唯一键。目的不在于像主键一样保证绝对的唯一性，而是设置别的想要具有唯一性的列被唯一性约束。唯一性约束的本质是让数据更安全，完整性更高。

唯一键的本质和主键差不多，唯一键允许为空，而且可以多个为空，空字段不做唯一性比较。

设置唯一键
[列名] [数据类型] unique,

添加唯一键
alter table [表名] add unique([列名]);
1
2
3
4
5

外键

实际的业务场景中，一般不会只有一张表，表和表之间是有关系的，以下面两个表为例：

学生表的class_id就可以关联到班级表的id，被关联的这个班级表称为主表，关联别人的表称为从表。

外键和外键约束是不同的，以上面的两张表为例，假设我们的业务逻辑就是可以让学生表和班级表关联，在直接建表时在语义上我们就认为有了外键，但是这种直接建表的方式没有外键约束，那么我在学生表中插入班级表里没有的班级也是可以的，虽然业务逻辑是错的，但是数据库允许我们操作，所以光在语义上有外键，不添加外键约束是不行的。

外键用于定义主表和从表之间的关系：外键约束主要定义在从表上，主表则必须是有主键约束或unique约束。当定义外键后，要求外键列数据必须在主表的主键列存在或为null。

设置外键约束
foreign key([自己的列]) references [主表名]([主表列名]),

添加外键约束
alter table [从表名] add foreign key([从表列名]) references [主表名]([主表列名]);
1
2
3
4
5

外键约束好之后，在从表插入数据只能插入在主表中存在的数值，插入不存在的就会报错，表明两张表是有关系的，数据不符合就无法插入。