数据库基础以及基本建库建表的简单操作

一、数据库是啥

1.1、数据库的概念

    什么是数据库，人如其名，可以简单理解为一台电脑可以用来存储数据，这台电脑的存储容量非常大，大到什么程度呢？没有非常明确的范围，但可以知道的是它可以供给整个地球上的人进行使用，并且查询数据的效率还非常高，同时也可以对数据有安全方面的保障，目前所有的公司都会使用数据库来对数据进行存储，数据对用公司来说是一种无形的资产，如果数据遭到泄露该公司就有可能面临破产的情况，因此数据的安全存储尤为重要，这也间接的说明了数据库在安全方面有着极为重要的帮助。
需要注意的是：数据并非仅仅是一个简单的存储工具，其还有对数据的管理功能。
    
    数据库的本质是一个结构化数据的集合及其管理系统，其核心不仅仅是存储数据，而是通过一套软件系统（数据库管理系统，DBMS）实现数据的高效访问，安全控制，逻辑关系维护及复杂操作支持。即使数据最终存储在磁盘上，直接使用文件存储与通过数据库管理存在根本性差异。

    数据库存在的核心意义在于高效、安全、结构化管理和操作数据，解决了传统文件存储方式在多用户、高并发、复杂查询及数据一致性上的不足。

1.1、关系型数据库、非关系型数据库

    关系型数据库又称行列数据库，关系型数据库是采用了关系模型来组织数据的数据库，以行和列的形式来存储数据，可以简单理解为就是一个Excel表

因此关系型模型可以理解为是二位表格模型，而一个关系型数据库就是由二维表及其之间的关系组成的一个关系组织。说点人话就是多个二维表之间构建某种关系，将多个表紧紧联系在一起，形成一张大型的关系网，互相可以参照数据存储数据管理数据，而这个关系网就是一个关系型数据库。

1.1、数据库服务器，数据库与表之间的关系

    当我们在电脑上安装了数据库管理系统程序（MySQL），该程序可以管理多个数据库，该程序就是指数据库服务器，通过数据库服务可以管理多个数据库，同时数据库里面可以包含有多个表格，每个表格中有很多数据行，每一个数据行都是通过数据列组成的。

    在面向对象编程当中，通常需要通过对事物的需求分析从而得到类和属性，数据库也是如此，通过对数据进行分析得到表的类型以及存储的类型，因此我们可以抽象理解为数据库其实就是面向对象语言java里面的类
    通过类可以new出一个或者多个对象，通过数据库可以创建出一个或者多个表，那么表就可以理解为是一个对象，表是数据库当中的基本单位，对象是面向对象编程中的基本单位。
    每一个对象都含有自己的行为和属性，表由列组成，那么列就可以简单理解为是对象的属性
    对象的属性都拥有自己的数据类型，而列也拥有自己的数据类型

二、为啥要使用数据库

    在我们的电脑当中，我们可以将数据存储到文件当中，通过编程也可以实现将数据存储到文件当中，那为什么还要使用数据库来进行存储呢，数据库也是存储到本地磁盘上，存储到文件当中也是存储磁盘中，那为什么还要安装数据库管理系统程序，然后通过数据库服务将数据存储到本地磁盘上呢？
    实现我们要理解传统文件存储数据的缺陷：

2.1：传统数据文件存储

1、数据冗余于不一致：

    不同的文件可能会存储相同的数据，假设表一和表二都存储了用户当前的身份信息，如今过去了两年，用户的身份从高中生变成了大学生，年龄变大了，这个时候对表一进行信息更新，但是表二不会因为表一的更新同步进行更新，传统的文件存储表与表之间没有同步关系，相同的数据一个表在同步变化而另外一个表没有同步变化，那么就会导致数据的矛盾，严重可能导致虚拟现实无法区分。

2、查询效率低下：

    在文件当中查询需要遍历所有的数据进行一一比较查询，时间复杂度为O(n)，当文件内存储的数据量很大的时候，就有可能会出现系统卡顿的情况

3、高并发访问冲突：

    当通过多线程来实现对同一文件操作，有可能会出现覆写的情况，就是两个用户同时打开了一个文件并编辑，那么就有可能会出现编辑慢一点的用户将上一个用户编辑的操作覆盖了，那么结果就是只表现为只有一个用户在操作文件

4、安全性差：

    当我们的电脑处于启动状态，同时防火墙没有经过特殊加强，那么我们的数据就有可能会被他人轻易窃取，从而造成经济损失，传统文件存储方式文件的级别控制粗糙。

5、无事务保障：

    我们通过文件存储的时候，若突然出现断电情况或者是电脑卡死了，那么就可能会保存失败，从而造成数据丢失，并且这些特殊的情况想要进行数据方面的恢复非常困难，电脑的缓冲区直接被清空了，此时要对缓冲区的数据进行恢复代价较高。

6、数据备份

    在传统文件存储方式当中，当文件需要备份的时候，只能手动进行文件的复制粘贴，把文件里面的内容粘贴到另外一个文件当中，但此时又产生了数据不一致的问题，同时又因为需要人为手动备份，导致效率降低

2.2：数据库存储数据

1、数据持久化

    数据库可以将数据保存在存储介质中，即使应用程序关闭或服务器重启，以及断电情况，数据也不会丢失

2、数据结构化

    严格的结构定义，结构化的方式存储使得数据易于存储和查询，井然有序的数据相比于无序的数据，肯定是有序的数据更易于查找操作

3、数据完整性

    数据库服务系统提供了一系列的服务，保证数据的一致性和准确性

4、并发控制

    数据库支持多用户同时访问和同时进行修改，同时保障数据的一致性，不会产生读写冲突

5、安全性

    数据库提供多种安全机制，如访问控制，加密等，可以对数据提供安全性保障，未经授权无法访问他人的数据库

6、事务保障

    大部分关系数据库都支持事务，确保一系列操作要么完全成功，要么完全失败，提高了操作的可靠性

7、备份与恢复

    数据库支持数据的备份和恢复，会定期进行数据的备份，以防数据的丢失和损失

8、查询优化

    数据库系统提供了高效的查询优化，可以快速执行复杂的查询操作

2.3、结论

    相比于传统的文件存储方式，数据库存储数据更有性价比，相当于多了一个管家来提我们时时刻刻看管数据，虽然数据库在物理层面上依赖于磁盘存储数据，但是在逻辑方面可以通过技术的提高来实现高效管理，因此使用数据库来进行存储数据是目前对于数据的管理最为便捷的方式，一系列的数据库的技术提供，为我们保障了数据的安全性和查询高效性。随着时代的发展，不能只停留在老时代的技术上，技术的诞生一切都是为了便捷人类生活，哪一个好用就用那个。

三、使用数据库了会咋样

    通过数据库可以便捷我们对数据的操作，我们只需要学会使用数据库，懂得数据的操作即可，那MySQL数据库来说我们只要学会SQL语言使用，通过一系列的SQL语句就可以将数据存储到数据库当中，亦或是从数据库当中提取数据，至于数据库是如何存储数据，如何提取数据，如何对数据进行保存和管理，我们程序员并不关心，只要数据库能够存储程序员需要存储的数据，能够提取出需要的数据即可。
    数据库的核心价值在于将原始的数据存储提示为智能数据服务，一切只需要交给数据库服务管理即可，大大提高了程序员开发的效率。

四、应该咋用数据库（MySQL数据库）

    使用MySQL服务器需要使用到SQL语言

4.1、MySQL架构

    外部程序：包括需要使用MySQL服务的应用程序或者是MySQL服务端
    链接层：主要用于确认身份，相当于我们登陆账号的过程，向数据库请求访问，同时输入用户名和密码，如果账号密码正确，服务器就会响应程序
    服务层：对用户的指令进行解析及优化
    存储引擎层：对数据进行处理
    文件系统层：真正保存数据的存储介质

    
    我们可以把一整个过程理解为去找吃的过程：首先我们可以在某团上面挑选附近的餐饮店家，（这里相当于我们在操作外部程序），当我们选好店家并去到店面，店家门口有一个接待的人员，他会咨询我们是不是来吃饭的，（此处相当于链接层也就是验证身份的过程，如果不是来吃饭是来砸场子的该人员不会给我们进入）。我们确定身份是来吃饭的，那么接下来我们进入到店中，接下来会有服务员来咨询我们需要点什么菜，（此处相当于服务层），服务层（服务人员）解析我们的需求并将需求传递给后厨，后厨就开始炒菜开始调整甜点（后厨相当于存储引擎层），后厨炒菜需要在冰箱里面拿出菜品进行清洁和烹饪（冰箱相当于文件系统层）最终经过服务员将菜品端上桌子给我们食用。这就是外部应用向MySQL服务器存储（提取）数据的简单过程
    而指令的编写涉及一门语言，与Java，C语言同一等级的SQL语言，MySQL服务器的所有指令都是通过该语言进行编写的。

4.2、数据库建库操作

    数据库的安装以及环境请自行搭建

4.2.1、查看数据库

语法：

show databases;

    需要注意的是在对数据库发车指令之前需要将本地环境链接到数据库，并且代码需要使用英文符号。

    可以下载该软件，用于代码运行，也可以通过CMD命令提示符输入：mysql -u 用户名 -p

    密码是在安装本地环境的时候设置的

    输入代码查看我们当前的本地环境里面有什么数据库

4.2.2、创建数据库

语法：

CREATE {DATABASE | SCHEMA } [IF NOT EXISTS] name [create_option]
create_option: [DEFAULT]{CHARACTER SET [=] charset_name| COLLATE [=] collation_name| ENCRYPTION [=] {'Y' | 'N'}
}

大写部分表示关键字
db_name：表示自定义的数据库名 { }大括号表示必须选 | 竖线表示任选其中之一 [ ] 中括号表示为可选项
CHARACTER SET：表示指定数据库采用的字符编码集
COLLATE：表示指定数据库字符集的校验规则
ENCRYPTION：数据库是否需要加密，默认不加密

    创建一个名为：textnum的数据名，并且指定字符编码集为：utf8mb4，指定排序规则为：utf8mb4_0900_ai_ci;

create database if not exists textnum character set utf8mb4 collate utf8mb4_0900_ai_ci;

    因此建议在编写代码的时候，能够添加判断代码一定要添加。
    warning是警告的意思，我们可以通过show 关键字进行查看

show warnings;
#查看报错信息

    当我们成功创建数据库后，会在数据目录底下生成与数据库名相同的目录，用于保存数据库当中的所有数据

4.2.3、查看数据库支持的字符集编码

语法：

show charset;

    MySQL8.0 默认的字符编码集是utf8mb4.

4.2.4、查看数据库支持的排序（校验）规则

语法：

show collation;

    MySQL8.0 默认的排序规则是utf8mb4_0900_ai_ci

4.2.5、不同的字符集与排序规则对数据库的影响

    utf8mb4_0900_ai_ci是MySQL8.0引入的新规则，老版本不能识别
    utf8mb4编码是Unicode字符集的一种实现，用1到4字节表示一个字符，可以表示世界上几乎全部的字符，而且更节省空间，按照存储的字符实际消耗的空间大小来进行编码，最大空间大小可达到4字节，范围足够宽 ， Uniocde字符集基本包含世界上的全部字符，是一种规则，而utf8mb4是对Unicode的一种实现，类似于接口的关系

    0900：是基于UCA 9.0.0算法，是Unicode Collation
Algorithm的缩写
    ai：是Accent-insensitive的缩写，表示口音不敏感，可以拿汉语拼音的声调来理解，汉语拼音有四种声调，使用该排序规则相当于四合一，也就是说无论是那种声调都统一认为是同一种
    ci：是Case-insensitive的缩写，表示大小写不敏感，英文的大小写统一认为是同一种
    bin：表示二进制的排序规则

4.2.6、查看系统默认字符集和排序规则

#查看系统默认字符集
show variables like '%character%';
#查看系统默认排序规则
show variables like '%collation%';

4.2.7、查看创建语句

语法：

show create database db_name;

4.2.8、修改数据库

    需要注意的是：并非是修改数据库当中存储的内容，而是修改数据库的字符编码集和修改排序规则，数据库存储的数据是存储在表当中的，而表是存储在数据库当中的，是无法直接通过数据库层面的代码修改到表层面的内容的
    
语法：

ALTER {DATABASE | SCHEMA} [db_name]alter_option...
alter_option: {[DEFAULT] CHARACTER SET [=] charset_name| [DEFAULT] COLLATE [=] collation_name| [DEFAULT] WNCRYPTION [=] {'Y' | 'N'}| READ ONLY [=] {DEFAULT | 0 | 1 }
}

4.2.9、删除数据库

语法：

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name;

    将数据库删除之后，就无法再查看到该数据库的所有信息了，数据库对应目录，以及目录内的所有文件也无法查看到，因此删除数据库是一种极其危险的行为，非必要不可删除
    
    数据库删除的本质类似于数组的删除，只是简单的将数据进行覆盖，简单来说就是需要删除的位置不是置0或者置空，而是将新的数据放到需要删除的数据的位置，将旧的数据覆盖，此时就完成了删除操作。

总结：

    对于数据库层面：
查看：show
新建：create
修改：alter（只能修改库的字符编码集和排序规则）
删除：drop
    代码编写不区分大小写

4.3、数据类型（特殊标记为常用）

    创建表的时候也就意味着可以往里面存储数据了，但是我们知道数据分有分多种有字符串，浮点型，数值型，但是在SQL语言当中只有4种，分别是：
1、数值类型
2、字符串类型
3、二进制类型
4、时间类型
    在SQL当中行是由列组成的，那么也就是说列是类似于属性的存在，行（实体）对于的是Java里面的对象，那么每一列就是对应一个属性，因此如果要定义实体的属性，就需要为属性命名并指定合适的数据类型

4.3.1、数值类型

    decimal的保存原理：
将数据以小数点进行分割，然后计算整数和小数的位数，一般默认用INT来进行存储，INT最大可以存储9位数，也就是说decimal的保存原理是将数据进行拆分，将其存储到INT当中，如果不满足9位数，那么就选择最大值最接近该数的数据类型

4.3.2、字符串以及二进制类型

    变长类型的数据类型，是通过实际字符长度来定义实际大小的，也就是说当我们存储数据之后，数据库会计算该数据的大小，最终为其开辟空间存储。如果确定后面的M值，当存储的数据超过M值时，运行该代码会报错，M值规定了多大空间，那么存储的数据就不可以大于M值

4.3.3、时间类型

    时间戳类型：TIMESTAMP有很大的问题，在以前一般以两位数来称呼，比如1999年叫做99年，那么00年到底是1900年还是2000年呢？据说这个情况在以前引起了很大的恐慌，这个情况有一个名字，叫做千年虫问题。同时该数据类型在2038年的时候会出现溢出情况，因此避免使用该数据类型

    其他语法：

CURRENT_DATE 和 CURRENT_DATE()是CURDATE()的同义词，用于获取当前的日期
CURRENT_TIME 和 CURRENT_TIME()是CURTIME（[fsp]）的同义词，用于获取当前的时间
CURRENT_TIMESTAMP 和 CURRENT_TIMESTAMP([fsp]) 是 NOW（）的同义词，用于获取当前的时间和日期

4.4、表的操作

4.4.1、查看表

语法：

#查看表
SHOW TABLES;

    在进行任意表操作之前都必须要选择数据库，否则系统不知道当前的表操作是对那个数据库进行操作，因此我们需要先选择数据库

语法：

#选择库
use 库名;

4.4.2、创建表

语法：

CREATE [TEMPORARV] TABLE [IF NOT EXISTS] tbl_name(field datatype [约束] [comment '注解内容'][, field datatype [约束] [comment '注解内容']] ...
) [engine 存储引擎] [character set 字符集] [collate 排序规则]; 

TEMPORARV：表示创建的是一个临时表（目前不做了解）
field ：列名称
datatype：列的数据类型
comment：对列的描述和说明
engine：存储引擎，不指定则使用默认存储引擎
character set：字符集，不指定则使用默认字符集
collate：排序规则，不指定则使用默认排序规则

例：创建⼀个⽤⼾表，其中包含⽤⼾编号、⽤⼾名、密码、⽣⽇，并指定字符集为utf8mb4，排序规则
为utf8mb4_0900_ai_ci

create table users (id bigint,name varchar(20) comment '⽤⼾名',password char(32) comment '密码是32位的md5值',birthday date comment '⽣⽇') CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

    一般建议在创建每一个表 的时候都多添加一个ID列，用作标识符，在后期的查找数据的过程之中，可以提高查询的效率。
    建议语法怎么规定我们就怎么写，比如说指定存储引擎以及字符集排序规则等，当我们在进行来回移交SQL代码的时候，就不会因为其他的环境默认设置与我们本地配置不同导致出现不必要的问题。标准语句意味着我们可以将这些语句拿去任意机器任意环境使用，执行的时候我们导出代码的这台机器，或者说我们在创建表的时候指定的参数，包括这个数据库的默认设置，系统默认设置要保持一致，因此为避免出现不可意料的情况，在来回移交SQL代码的时候，最好还是保证机器的设置是完全相同的。

例：创建⼀个表并指定存储引擎为MyISAM

create table t_myisam (id bigint,name varchar(20) comment '⽤⼾名') engine = MyISAM;

    当我们成功创建存储引擎为innoDB的表了之后，会在当前数据库的目录底下生成一个用来存储数据的物理文件命名格式为xxx.jdb

    而创建存储引擎为myISAM的表，会在数据库目录底下生成三个不同后缀的表分别是
xxx.MYD的数据文件
xxx.MYI的索引文件
xxx.sdi的表信息描述文件

    也就是说存储引擎生成的物理文件是myISAM存储引擎生成的三个文件合一。

    在8.0版本以前，表信息描述文件是以.frm为后缀的二进制文件

    同样的，我们也建议在创建表，删除表的时候加上判断语句，避免系统报错导致代码停止运行。

4.4.3、查看表

语法：

desc tabl_name；

Field：表示列名
Type：表示列的数据类型
Null：表示该列是否可以为空，可以为YES，不可以为NO，默认是YES
Key：表示键，约束，主键
Default：表示是否含有默认值
Extra：表示拓展信息

4.4.4、修改表

语法：

ALTER TABLE tbl_name [alter_option [, alter_option] ...];
alter_option: {table_options| ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]| MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]| DROP [COLUMN] col_name| RENAME COLUMN old_col_name TO new_col_name| RENAME [TO | AS] new_tbl_name}

tbl_name ：表示需要进行修改的表名
ADD ：表示向表中添加列，不指定位置则默认放置到最后一列 FIRST ：表示将列放置到第一列
AFTER col_name：表示将列放置到col_name列的后面
MODIFY ：表示修改现有列的内容，不指定位置则默认存放在原位
DROP ：删除现有列
RENAME COLUMN：重命名现有列
RENAME ：重命名当前表

例：向表中添加一列，放置到第一列

例：将新增的列修改数据类型为char（50）为arr

例：将新增的列删除

例：将编号的名称改为ids

例：将表名修改为user

4.4.5、删除表

语法:

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...

TEMPORARY：表⽰临时表(目前不做解释)
tbl_name：将要删除的表名

例：删除当前表

    同样的我们也建议在删除表的时候加上判断语句，防止重复删除导致系统报错。

操作总结

    在删除表的时候要更加谨慎，删除表的操作相比删除库更加危险，当我们误删掉数据库的时候，应用程序一旦无法访问数据库就会马上报错，这个时候我们是可以轻易发现问题的，但是如果是误删表的情况就比较难以发现了，如果该表还是不经常用的就更加难以发现了，不访问改表是完全无法发现的。无论是访问哪一个表，都必须要经过数据库，如果误删数据库了对应的表也会被删除，一旦访问就直接报错速度很快。总结来说无论是删除表还是删除库，都是极其危险的行为，在行动之前一定要确认再确认。

    当我们部署新的数据库的时候，一般不会像当前编写一句代码就执行一句代码，而是事先将代码全部编写完，然后将代码存储到文件当中统一执行，而SQL代码和我们的Java代码一样，一旦代码报错那么剩下的代码就无法继续执行，因此为避免文件中的代码因为某条代码的问题出现报错导致剩下的代码无法执行，我们建议在编写删除和新建内容代码的时候加上
判断语句 能够降低出现错误的概率