数据库的基本概念

数据库

源由

对于数据库的初次接触是毕业第一年到公司做的视频监控系统，利用PHP语言MySQL语言实现。虽然实现了功能，但由于当时间紧任务重，我对数据库并没有深入了解，在后续自己编写物联网管理后台受到了制约。为了更好的重构自己的iot后台，决心重学一边数据库，这篇博客是用来记录个人学习廖雪峰教程的笔记，用于以后的查询，（年纪大了，脑子不好使了）。

基本概念

数据库是独立于应用程序外的一种程序，用于数据的管理，实现增删查改，并暴露统一接口供不同的语言来调用

数据类型

名称	类型	说明
TINIINT	整形	单字节整形，范围0-255
INT	整形	4字节整形数据，范围约+/-21亿
BIGINT	长整形	8字节正数类型，范围约+/-922亿亿
REAL	浮点型	4字节浮点数，范围约+/-10的38次方
DOUBLE	浮点型	8字节浮点数，范围约+/-10的308次方
DECIMAL(M,N)	高精度小数	由用户指定精度的小数，eg：DECIMAL(20,10)表示一共20位，小数10位
CHAR(N)	定长字符串	存储指定长度的字符串，eg：CHAR(100)总是存储100个字符的字符串
VARCHAR(N)	变长字符串	存储可变长度的字符串，eg：VARCHAR(100)可以存储0-100个字符的字符串
BOOLEAN	布尔类型	存储True或者False
DATE	日期类型	存储日期 eg：2019-11-30
TIME	时间类型	存储时间 eg：16：13：43
DATETIME	日期时间	存储日期时间 eg：2019-11-30 16:14:44

一般BIGINT能满足证书存储的需求，VARCHAR(N)能满足字符串存储的需求。这两种应用最为广泛。

常用数据库

1. 商用数据库 Oracle、SQL Server、DB2
2. 开源数据库 MySQL、PostgreSQL
3. 桌面数据库 Access
4. 嵌入式数据库 Sqlite

SQL

sql是结构化查询语言的缩写，用来访问和操作数据库系统，是应用程序和数据库软件之间沟通的语言。

一般SQL具有以下几项功能

1. DDL 数据定义，数据表的创建、删除、修改表结构等，这些由数据库管理员来执行。
2. DML 数据管理，数据的增删查改，是应用程序对数据库的日常操作。
3. DQL 数据查询，同上。

语法

sql语法不区分大小写，但是一般为了与字段区分，关键字都大写。

MySQL

mySql是目前世界上最广泛的开源关系型数据库。前几年最流行的web架构是LAMP中的M指的是MySQL

安装

sudo apt-get install mysql-server
或者
doker run mysql

内置引擎

MySQL 本身只是一个SQL接口，内部包含了两种数据引擎，他们的关系就类似国产浏览器采用IE和谷歌内核一样，两种引擎分别如下：

1. InnoDB: 支持事务的引擎
2. MyISAM：MySQL默认的数据库引擎，不支持事务

如果不知道使用哪种，那就优先使用InnoDB引擎吧。

启动和运行

以docker方式为例，因为之前启动过mysql容器，所以直接查找对应容器id进入即可，、

找到对应id执行

sudo docker exec  -it id /bin/bash

进入docker容器内部的终端，开始mysql操作。

在命令行输入mysql -u root -p输入口令，即可连接到mysql服务器。连接结果如下图所示。、

注意：上述执行命令的对象并非是对mysql server的操作，而是对客户端的操作，表示通过客户端连接mysql服务器。mysql client和mysql server的关系如下：

（图片引自廖雪峰官方网站）

输入的mysql -u root -p是通过TCP连接到本地127.0.0.1的mysql server，默认端口是3306，如果要连接到远程的mysql server可以使用：

mysql -h xxx.xxx.xxx.xxx -u root -p	

• -h 代表server的IP地址
• -u 代表用户名
• -p 代表有密码

命令行程序中的mysql是sql客户端，真正的server可执行程序是mysqld

输入exit可以退出client客户端，server仍然在后台运行。

常用SQL命令

通过mysql client连接到server上后可以对数据库进行操作，包括数据库和数据表的增删查改。操作方式就是sql语句。

注意：命令以分号;结束

列出所有数据库

SHOW DATABASE;

执行结果如下：

上面四个数据库infomation_schma、mysql、performace_schema、sys都是mysql自带的系统库，不要动。

创建数据库

CREATE DATABASE test;

删除数据库

DROP DATABASE test;

注意：删除一个数据库会导致该库中所有数据都会被删除

切换数据库

要操作一个数据库必须先切换到目标数据库

USE test;

列出数据表

SHOW TABLES;	

目前test库中没有数据表。

查看数据表结构

DESC student;

• student是表名

#### 查看建表语句

SHOW CREATE TABLE student;

• student是表名

删除表

DROP TABLE 表名;

修改表

给student表新增一列birth

ALTER TABLE student ADD COLUMN birth VARCHAR(10) NOT NULL;

修改birth列，把列名修改为birthday,类型改为VARCHAR(20)

ALTER TABLE student CHANGE COLUMN birth birthday VALCHAR(20) NOT NULL;

删除列

ALTER TABLE student DROP COLUMN birthday;

关系模型

在关系数据库中，一个表类似一个Excel表格，有行有列。表中行数据称为记录，列数据称为字段。

比如说一张学生表，

id	class_id	name	gender	score
1	1	张三	MAN	90
2	1	李四	WOMAN	80
3	2	王二麻子	MAN	70

主键

对于关系型数据库，有个重要的约束是不能有完全相同的两条记录，一般通过某个字段区分不同的记录，这个记录被称为主键。

主键有几点注意事项：

1. 一条记录插入到表中，主键最好不要在修改，因为主键用唯一定位记录。
2. 主键选取的基本原则是：不适用与任何业务相关的字段作为主键。通常定义为id,id字段常见类型有
   1. 自增性数据，如BIGINT
   2. 全局唯一GUID类型，是通过时间戳随机数和MAC地址生产成本的唯一字符串

主键还可以用两个或者多个字段来确定，称为联合主键，一般不使用，避免复杂度的提升。

外键

为说明外键的来源和作用，首先需要了解关系型数据库中的表间关系。

表间关系

关系型数据库表之间关系有 一对多、多对一和多对多。举例说明：

一个学校有多个老师和多个班级，每个班级有多个学生，假设有班级表（一个年级所有班级的班级）和老师表（一个年级所有老师的集合）和学生表（一个班级学生的集合）。分别如下：

id	class_id(班级序号)	class_name
1	139	三年级1班
2	140	三年级2班
3	141	三年级3班

id	teacher_id(教师工号)	teacher_name
1	39730	李白
2	39731	杜甫
3	39732	辛弃疾

id	student_id(学号)	student_name
1	13193512220	淘气包
2	13193512221	马小跳
3	13193512222	阿衰
4	13193512223	大脸妹

一对多

一个班级有多个学生，也就是一条班级记录对应多条学生记录，那么班级表和学生表的关系是一对多，如下：

为表明这种关系，一般在多的这个表中新增一个一的唯一id，也就是在学生表中加入一班级表的唯一id，如下：

id	student_id(学号)	student_name	class_id(班级序号)
1	13193512220	淘气包	139
2	13193512221	马小跳	139
3	13193512222	阿衰	140
4	13193512223	大脸妹	140

其中class_id就是外键，他把班级表和学生表联系了起来。

也就说外键是联系两个表的纽带。

外键是通过定义外键约束来实现的：

ALTER TABLE student
ADD CONSTRAINT fk_class_id
FOREIGH KEY (class_id)
REFEENCES classes(id);

• ALTER TABALE student 选中student表
• ADD CONSTRAINT fk_class_id 外键约束名称为fk_class_id 名称可以任意定义
• REFERENCES classes（id）指定外键将关联到classes表的id列，即classes表的主键。

定义外键约束以后，能保证无法插入无效数据，比如如果班级表中没有id=99的记录，那么学生表就无法插入id为99的学生记录。

注意

由于外键约束会降低数据库的性能，大部分web应用为了追求速度都不设置外键约束，而是依靠应用程序自身来保证逻辑的正确性。此时，classs_id只是一个普通的列，在逻辑上起到了外键的作用，功能上并未开启外键功能。

ps：之前还心心念念的要把之前自己写的后台所有表格建立一对多多对多的关系呢，之前用thinkphp写的后台应用是单纯依靠逻辑程序来关联表间关系的，没想到误打误撞反而会提高性能，如此看来一定要看距离当前时间近的教程啊，之前看的教程是好几年前的认为一对多多对多这类的表间关系特别实用呢，早日看到廖雪峰的教程就好了。

删除外键约束使用

ALTER TABLE student
DROP FOREIGH KEY fk_class_id;

删除外键没有删除外键那一列，只是删掉了约束关系，距离列还得通过DROP COLUMN...实现。

多对多

一个班级对应多个老师，一个老师也会回多个班级上课，老师与班级的关系就是多对多。

一般多对多关系的建立是依靠新建中间表来实现的，中间表分别于班级表和教师表的关系是1对多。

id	class_id(班级序号)	class_name
1	139	三年级1班
2	140	三年级2班
3	141	三年级3班

id	teacher_id(教师工号)	teacher_name
1	39730	李白
2	39731	杜甫
3	39732	辛弃疾

id	class_id(班级序号)	teacher_id(教师工号)
1	1	2
2	1	3
3	2	2
4	3	3

多对一

参考一对多，反向而已。

一对一

一对一即两个表内部记录的关系是一对一，一对一可以在单个表中插入另一个表的字段其实就可以取消另一个表。一对一关系现在一般应用在表的拆分，目的是把经常读取的字段和不经常读取的字段分开，提高性能。比如一个用户表拆分为一个基本表和一个详细表，大部分时候查询基本表，而不需要查询详细表。需要时再查询详细表。

索引

关系型数据库中，索引能加快数据库查询速度。

索引是关系型数据库中对某一列活多个列的值尽心股排序的数据结构，通过使用索引，可以让数据库不必扫描整个表，而是直接定位到符合条件的记录，这就大大加快了查询速度。

创建索引

ALTER TABLE student
ADD INDEX idx_score(score);

• ADD INDEX idx_score(score) 创建名为 idx_score的索引，索引建立在score字段上。

如果索引有多列，那么

ALTER TABLE student
ADD INDEX idx_name_score (name,store);

• 为student表中的name和store字段建立索引。

特性：

1. 关系型数据库自动对主键创建主键索引，因为主键唯一，查询效率高。

2. 索引的效率取决于被索引字段的值是否散列，索引的字段值越不相同，查询效率约高。

3. 一张表可以建立多个索引，优点是提高了查询效率，缺点是插入更新和删除时需要同时修改索引，这三种操作会变慢。

唯一索引

唯一索引可以保证某一列值得唯一性。

ALTER TABLE student
ADD UNIQUE INDEX uni_name(name);	

• 为student表中的name字段创建唯一索引

此时插入表中的name不能相同，

还可以创建约束不创建唯一索引：

ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name);

• 只为name字段创建了唯一约束

无论是否使用索引，对于应用程序都是透明的，索引后能提高查询速度，但会较低删、修改、插入的速度