关系模型

一数据库基本介绍

1. 数据库的由来

我们使用数据库是为了永久保存数据，如果没有数据库，我们要想永久保存数据只能是存在硬盘，如果忽略用硬盘存储数据的读写效率问题，并且假设所有的数据都运行在一台机器之上，那么用硬盘存储数据是没有问题的，但毫无疑问，这个假设的前提条件是致命的。所有的数据都运行在一台机器上，假如这一台机器坏了，那么整个系统将会瘫痪，数据安全的问题都无法保证，首先丧失的是系统的稳定性，除此之外，一台机器的硬件性能的终究是有极限的，扩展机器的硬件性能有两种方案：

垂直扩展：在一台机上用多个最牛逼的CPU，硬盘和内存
水平扩展：把多台普通的机器组合起来，使程序的各部分组建分散运行在不同机器上

显然，单台机器的垂直扩展使用有极限(一台机器不能无限添加)，更好的使用水平扩展，使用这种方案性能提升的同时兼顾了系统的稳定性，但是不同的组建所使用的数据肯定是同一份数据，多台机的数据是不能共享的，这时我们就需要一个专门负责数据存储的机器，写一个基于C/S架构服务端与不同的客户端机器建立连接。

2. 数据库管理系统

专门用来存储数据的机器就相当于是一个存储数据的仓库，俗称数据库，在这台机器之上运行着一个数据库管理系统，这个系统其实就是一个套接字软件，也许你有能力写这样的一个软件，不过要想用于生产环境，需要具备以下几点要求：

数据的冗余与不一致：比如孙悟空是唐僧的大徒弟，也是花果山的美猴王，这两个文件中都要存储他的名字，这种不仅冗余导致存储和访问的开销增大，还能导致数据不一致性，假如孙悟空的名字改成了孙行者，可能其中一个文件更改了而另外一个文件没有更改。
数据库访问困难：比如某大学要找到所有课程成绩都是90分以上的同学，这个检索是相当复杂的。
数据孤立：不同的数据存储在不同的文件中，我们假设所有文件的格式都是统一的，即便是这样，在所有文件中检索需要的数据依然是很困难的。
完整性问题：数据库所存储的值必须满足某些特定的一致性约束，比如随着年龄的增长，一个小孩的年龄会增高，但是一个人的身高不可能超过3米，我们可以在系统中加入适当的代码来实现这个约束，然而当有新的约束加入时，比如一个人的体重不可能超过3吨，则很难再通过程序的限制来实现这些约束，尤其是当约束涉及到不同的文件的多项数据时，问题会变得极为复杂。
原子性问题：假如系统发生故障，当你给你女朋友转钱的时候，你的钱没了，你女朋友也没收到这个钱，原子性就是要么都发生，要么都不要发生，在传统的文件处理系统中，保持原子性是很难做到的。
并发访问：由于数据可能同时被多个不同的客户端机器访问，并发访问如果不加以控制，带来的问题就是数据错乱，在大量的文件中控制并发访问，这是很难的。
安全问题：不同的角色应该有不同的访问权限，如果一个长期被压榨的程序员看到公司其他的的工资都比他的工资高的多，如果他有执行权限的话，那么接下来可能就是删库到跑路了。

每个公司的项目都需要这样一个数据库管理系统，既然大家都有这样的需求，这也就产生了商业价值，这也就是造就了数据库的诞生，下图是全世界范围内数据库前十位的排名，一般企业里面使用以前面七种数据库为主。

3. 数据库的分类

数据库有关系型与非关系型之说，关系型数据库是建立在关系模型的基础之上，而非关系型数据库描述的是大量结构化数据存储方法的集合，如果一定要有一个通俗的说法，可以暂时先理解为按照key与value的方式存取，但是这种说法并不准确，本教程主要是对关系型数据库的研究(所有的关系型数据库都基本相似，目前企业中所使用的主流是Oracle，MySQL和PostgreSQL，学会了一种，另外的几种也就差不多会用了)，按照关系型与非关系型来划分，数据库可以分为两类：

关系型数据库(RDBMS)，例如Oracle，MySQL和PostgreSQL
非关系型数据库(NoSQL)，例如MongoDB和Redis

4. 数据库的规则

一个完整的项目各部分的功能拆出去在各个机器上运行，大家所使用的数据应该也是同一份的，如果不添加任何的解决方案，各部分的数据肯定是独立的，你要怎么来解决数据共享的问题呢？这个时候就要基于网络通信写一个C/S架构的套接字程序，有客户端，有服务端，这就是一个数据库管理软件了。要写这样一个软件需要考虑以下几个问题，并发，同时操作处理好所得问题，数据核心数据的安全问题，用户认证的操作，还要尽可能的降低IO操作，来提高程序的运行效率。以后所有的程序员写程序都要先写一个数据库管理软件，一写写一年，别的事都别干了，写好了之后发现还有bug，再改一年，还没改完，公司倒闭了，去下一家公司接着写。

既然有了硬性需求就会与商业价值，这个时候有一家公司跳出来了，他说我来写这个数据库管理软件，你们安心写程序的业务逻辑就好了，这家公司就是Oracle，到目前为止，数据库就诞生了。既然我们不想花时间写数据库管理软件，那么使用别人写好的软件，就要遵守别人的游戏规则。比如我们需要记录一个人的信息，就需要提取这个人一些明显的特征。

id = 1
name = 'Albert'
age = 18
height = 180
weight = 80
is_hansome = True

我们使用这样几个数据一起来描述一个人的信息，这在数据库中称为一条记录，我们把这条数据存在到文件中，那么就要创建一个文件，如果是在这个文件中有多条记录呢？

1,Albert,18,180,80,Ture
2,James,34,203,113,False
3,Curry,30,190,95,False

显然这样存储并不直观，如果是使用表格的形式的来存储，那么这将变得更加直观。

id	名字	年龄	身高	体重	是否长得帅
1	Albert	18	180	80	True
2	James	34	203	113	False
3	Curry	30	190	95	False

这其实特别像我们使用的Excel的表格，所以数据库里面就把它叫做表，而name，age和height这些就像是表头，在数据库中叫做字段。多条数据就构成了一张表，一个表有多个字段，这一张表就像是一个文件。你在自己的电脑上存东西不同的文件你会给它分类放到不同的文件夹中，写数据库管理软件的人自然也能想到，多个表就构成了一个库，就像是把多个文件放到一个文件夹中。

5. 数据库服务器

数据库管理软件其实就是一个应用程序，要想使这个应用程序运行起来就要把它在操作系统上启动，考虑到需求是稳定性优先，所以一定是在Linux系统上运行，当他运行起来，这就是数据库服务器。

人们常说的数据库指的是什么？

文件夹也就是库，这通常开发所说的
一台机器，也就是运行数据库管理软件的那台装有Linux系统的服务器
数据库管理软件

二数据库的安装

1. 数据库版本问题

目前MySQL最新版本是8.0以上，虽然有企业在使用，而且两三年以后也可能会大量普及，但是大部分企业还是集中使用5.5，5.6或者5.7的版本，这里我们的讲解会以5.6版本为标准讲解。

8.0以上的版本相对于5.6的版本确实做了升级，在极小的一部分细微的地方使用会更加得心应手，大部分的使用还是一样的，我们只要学会了5.6的版本，上手8.0以上版本是非常容易的，反之，则不易。

2. 安装方式

打开官网地址：https://dev.mysql.com/downloads/mysql/ ，点击链接：MySQL Community Server 5.6 下载对应操作系统版本。在Windows系统上以你可以很容易的找到MySQL的文件夹内的bin文件夹，这里面就会存放mysql.exe和mysqld.exe文件，分别对应MySQL客户端和服务端的启动程序，MacOS系统是前往文件夹/usr/local/mysql/，回车之后也可以看到同样的文件内容，Windows系统把MySQL做成系统服务是的开机就会启动，我们使用：mysqld --install命令来完成，MacOS系统不需要做这些配置。

3. 配置环境变量

配置环境变量的目的是希望能够在终端直接执行MySQL命令而不是在进入/usr/local/mysql/bin/路径之后才能执行MySQL命令，所以我们需要把这个路径添加至环境变量中，root权限使用命令vim /usr/local/mysql/.bash_profile创建并打开一个新文件，把export PATH=${PATH}:/usr/local/mysql/bin，添加到空文件中环境变量即配置完成。

三数据的基本使用

1. 登录

我们使用命令：mysql -h 127.0.0.1 -P 3306 -uroot -p来完成客户端的登录，-h指的是IP地址，本机连接可以省略，-P(大写P)指的是端口号，默认是3306，也可以省略，-uroot指的是用户名，自带一个管理员root用户名，-p(小写p)指的是密码。简化之后的登录命令是：mysql -uroot -p，刚开始我们没有设置密码，直接回车就可以登录了。

2. 修改密码

MySQL会自带一个管理员用户，默认是没有密码的，我们需要对他进行修改密码，方式有很多，这里我们讲三种。

使用mysqladmin -uroot -p password命令，这是直接修改服务端保存的密码，注意要以管理员的身份运行，并且在MySQL的安装路径下执行，Windows系统和MacOS系统略有差异，Windows：mysqladmin -uroot -p password '123',把无密码修改为密码123，mysqladmin -uroot -p123 password '123456'，把密码123改成123456，MacOS：先执行sudo -i命令获取本机管理员权限，在执行cd /usr/local/mysql/bin切换到安装路径下，最后执行./mysqladmin -uroot -p password '123',以上这些是把无密码改成123的完整操作，./mysqladmin -uroot -p password '123456'，这是把密码由123改成123456的操作，与Windows区别在于两点：命令前面要加./，原来的密码要再次输入而不是写在命令行。密码修改成功之后会有一个警告：Warning: Using a password on the command line interface can be insecure.,这说的是你在使用明文密码不安全，别人通过上下命令翻滚可以看到这个密码，可以暂时先忽略。
登录之后使用set password for 用户名@地址 = password('新密码');命令，这是通过客户端间接修改密码，示例：set password for root@localhost = password('1');，把原来密码修改为1，然后在执行刷新命令：flush privileges;。
登录之后使用命令：update mysql.user set password=password('1') where user='root' and host='localhost';，然后再刷新。

3. 破解管理员密码

如果密码忘记了，在测试环境下你可能会有很多种方式来解决这个问题，但是生产环境并不是允许的。这时我们可以思考一下，MySQL是操作系统的一个软件，我们只需要控制操作系统启动的时候mysql的用户认证机制，这样就能无需密码直接登录，然后再修改密码。Windows系统和MacOS系统略有差异，WIndows：先关闭MySQL服务，然后以管理员身份在安装路径下执行mysqld --skip-grant-tables命令，这指的是跳过授权表(表就是文件)启动MySQL服务，下一步再打开一个命令行窗口不输入密码直接登录，然后就可以进行密码修改了，最后关闭MySQL服务并正常启动。MacOS：执行命令略有差异./mysqld_safe --skip-grant-tables，其他操作一致。

4. 修改配置文件

在不同的操作系统和不同的SQL版本上字符编码会略有差异，登录之后我们可以使用\s命令来查看本机字符编码，所以，需要对配置文件的字符编码进行修改。Windows：在安装路径根目录下创建一个my.ini文件，MySQL会自动读取配置文件内容，MacOS：找到路径usr/local/mysql下的my.cnf文件，两个系统配置文件内容一致，如下所示：

# 1. 修改服务端字符编码配置
[mysqld]
default-character-set=utf8
collation-server=utf8_general_ci

# 2. 修改全局客户端字符编码配置
[client]
default-character-set=utf8

# 3. 修改客户端字符编码
[mysql]
default-character-set=utf8

# 全局客户端client配置指的是针对全局所有的客户端软件
# 如果与mysql配置有冲突以mysql为准
# 如果没有mysql配置，以client配置为准

四 SQL基本语句

基本SQL语句是入门的重点，但是简单到你怀疑人生。我们使用数据库就是对数据的增删改查，而数据的的对象就是库，表和记录。

创建或删除的过程中，你可以看到你的数据库安装目录中data目录下面的文件夹或者文件的变化，其中.frm文件使用存储表结构，.ibd文件是存放与表相关的数据。

# 查看当前登录用户
select user();
# 如果命令写错了可以使用:【\c】结束


# 1 文件夹（库）

# 数据库命名：可以由字母、数字、下划线、＠、＃组成，区分大小写，不能重复，不能使用关键字如 create select，不能单独使用数字，最长128位。

# 增:创建数据库db1,可以指定自己编码，如果不指定则使用配置文件字符编码，简化：create database db1;
create database db1 charset utf8;

# 改:修改数据库的字符编码为gbk,数据库名不能直接修改
alter database db1 charset gbk;

# 查：查看所有库的库名，在查看中可以看到字符编码的变化
show databases;
# 单独查看某一个库的信息
show create database db1;

# 删
drop database db1;


# 2 文件（表）
# 首先切换文件夹：
use db1;
# 查看当前所在的文件夹
select database();

# 增：创建表，字段名必须指定字符编码，int整型，char字符串。
create table t1
(
    id   int,
    name char
);

# 改：修改表名
rename table t1 to t0;
# 修改字段名长度，char类型有默认长度，为1
alter table t0
    modify name char(16);

# 查：查看当前库下所有的表名
show tables;
# 查看t0表的详细信息
show create table t0;
# 查看表结构,可以简写成【desc t0】
describe t0;

# 删；删除表内所有数据和模型关系
drop table t0;

# 删除：保留模型关系，只删除数据
delete from t0;


# 3 文件的一行内容（记录）

# 增：插入记录，在db1目录下可以把【db1.】省略
insert into db1.t0
values (1, 'Albert'),
       (2, 'James'),
       (3, 'zimuge');

# 改：修改字段名
update db1.t0
set name='nba'
where id >= 1;
# 同时修改多个字段
update db1.t0
set name='nbaaaa',id=111
where id >= 1;

# 查：查看数据
select id, name
from db1.t0;

# # 删：删除记录
delete
from db1.t0
where name = 'nba';

五系统库文件说明

information_schema：虚拟库，不占用磁盘空间，存储的是数据库启动后的一些参数，如用户表信息、列信息、权限信息、字符信息等。
performance_schema： MySQL 5.5开始新增一个数据库：主要用于收集数据库服务器性能参数，记录处理查询请求时发生的各种事件、锁等现象。
mysql：授权库，主要存储系统用户的权限信息。
test：MySQL数据库系统自动创建的测试数据库。

六存储引擎

我们可以在登录之后使用show engines;命令来查看存储引擎，所谓的存储引擎就是一个存储的功能，对于不同的文件，我们存储的方式是不同的，就是想你保存文本文件可以使用.doc，保存图片文件使用.jpg，MySQL就是存取数据的，5.6以上的版本存储引擎默认是InnoDB，我们直接使用就可以了。

# 默认存储引擎，我们重点讲解的
create table t1
(
    id int
) engine = innodb;

# 不支持事务，行级锁，外键
create table t2
(
    id int
) engine = myisam;

# 相当于是回收站，写进去的东西都消失了
create table t3
(
    id int
) engine = blackhole;

# 存入内存，不能永久保存
create table t4
(
    id int
) engine = memory;

七数据模型

数据库结构的基础是数据模型，数据模型是描述数据，数据联系，数据语义以及一致性约束的概念工具的集合，数据模型可以分为四类：

关系模型：其实就是SQL语句，使用表的集合来表示数据和数据间的联系，这是关系模型使用最广泛的数据模型，也是本教程讲解中所使用的数据模型。
实体联系模型：又叫做E-R数据模型，是基于对现实世界的一种认识，实体联系模型被广泛应用与数据库设计。
基于对象的数据模型：这就是用编程语言来操纵数据库，这种数据模型结合编程语言面向对象和关系模型的特征。
半结构化数据模型：半结构化数据模型允许出现相同类型的数据项含有不同属性集的数据定义，XML广泛用来表示半结构化数据，现在用的很少了，了解即可。

八数据库的码

1. 主键

数据库的每行数据是以元组(tuple)的形式来保存的，查询的结果是求笛卡尔积，也是以元组的形式呈现出来的。我们必须要有一种能够区分数据关系中不同元组的方法，这就是用它们的属性来表明，也就是说，一个关系中没有两个元组在所有属性上的取值都相同。

超码(super key)是一个或多个属性的集合，这些属性的组合可以使我们在一个关系中唯一的标识一个元组，第四小节用的的id就是一个超码。
候选码(candidate key)超码中可能会包含无关紧要的属性，假如我们用id和name的组合作为超码，这自然是符合超码的定义，但是我们通常只对一些超码感兴趣，最小的超码称为候选码。
主码(primary key)这也就是人们常说的主键了，我们用主码来表示被数据库设计者选中的，主要用来在一个关系中区分不同元组的候选码。码的指定代表了被建模事物在现实世界中的约束。

主码的选择必须慎重，正如我们所知道的，人名显然是不足以做主码的，而身份证号码或者护照号码却可以，习惯上我们会把一个关系模型的主键属性列在其他属性前面。

微信这么伟大的产品，一直以来因为不能更改微信号被人们吐槽，而在有些产品中昵称甚至都不能随意更改，这其中原因大家自由想象，但却说明了微信还是很NB的。

2. 外键

一个关系模型[r1]可能在它的属性中包括另一个关系模型[r2]中的主键，那么这个属性在[r1]上被称作参照[r2]的外键(foreign key)，模型[r1]被称为外键一类的参照关系，模型[r2]被称作外键的被参照关系。

九基本数据类型

SQL标准中有多种数据类型，在这里我们想讲几个最基础的，后面的章节会涉及到更多的数据类型。

char(n)：固定长度的字符串，用户指定长度n，默认为1，可以使用全程character。
varchar(n)：可变长度的字符串，用户指定最大长度n，等价于character varying。
int：整数类型，等价于integer。
numeric(p,d)：定点数，精度由用户指定，p指的是这个数有p位数字，其中d为数字在小数点右边。所以这个类型的字段上，numeric(3,1)可以精确存储到21.6，但不能精确存储898.2或者1.23这样的数。

char与varchar的争论由来已久，我们应该针对不同的业务场景选择合适的数据类型，我不喜欢中庸的评判，如果一定笼统的要选择一个话，我会更倾向于使用varchar。

char类型用于存放固定长度的字符串，例如，属性name的类型是char(10)，我们为此属性存入字符串“albert”，那么该字符串后面会追加4个空格以达到10个字符串的长度，反之，varchar则不会增加空格。当我们在比较两个char类型的值时，如果他们长度不同，在比较之前会自动在短值后面加上额外的空格以使它们长度一致。当比较一个char类型和一个varchar类型的时候，我们期待的自动补充可能发生也可能不会发生，这取决于数据库系统，幸运的是，在MySQL中5.6和5.6以上的版本会自动补充，但是我们依然建议始终使用一个char或者varchar来避免这样的问题，因为并不是所有的数据库都具有这样的容错性。

十基本模式定义

1. 数据库使用

这是对以上所讲的内容作为一个应用性的总结，另外还会设计到一个完整性约束的概念，这其实就是对建模事物在现实中的约束体现到了SQL语句上面。

# 部门表
create table department
(
    department_name varchar(20),
    building        varchar(15),
    budget          numeric(12, 2),
    primary key (department_name)
);

# 产品表
use db1;
create table product
(
    product_id      varchar(7),
    title           varchar(20) not null ,
    credits         numeric(8, 0),
    department_name varchar(20),
    primary key (product_id),
    foreign key (department_name) references department (department_name)
);

# 人员表
create table staff
(
    staff_id   varchar(10),
    name       varchar(15) not null ,
    age        int,
    product_id varchar(7),
    primary key (staff_id),
    foreign key (product_id) references product (product_id)
);

说明：

每种数据类型都可能包含一个被称作空值的特殊值，空值是一个缺失值，该值可能存在但并不为人所知，也可能根本不存在，在能够控制的情况下，我要禁止空值的加入。一个属性上的not null约束表明在该属性上不允许空值。
主键属性已经限制非空且唯一，所以不需要约束空值限制。
references约束外键属性必然存在于关联模型的主键属性中。

2. 完整性约束限制

SQL禁止破坏完整性约束的任何数据库更新，比如我们上面建了三个表，如果要删除department表这回破坏外键的完整性约束，SQL将会标记一个错误，并阻止更新。要想实现删除，目前只能是先删除没有被外键关联的模型。除此之外当我们需要更新或者删除数据时，也会因为外键的完整性约束被破坏而无法执行，这时我们应在在外键中使用cascade命令来串联更新。

# 优化外键关系

# 部门表
create table department
(
    department_name varchar(20),
    building        varchar(15),
    budget          numeric(12, 2),
    primary key (department_name)
);

# 产品表
use db1;
create table product
(
    # 用int类型 改成自动增长的主键
    product_id      int primary key auto_increment,
    title           varchar(20) not null,
    credits         numeric(8, 0),
    department_name varchar(20),
    foreign key (department_name) references department (department_name)
        on update cascade
        on delete cascade
);

# 人员表
create table staff
(
    staff_id   varchar(10),
    name       varchar(15) not null,
    age        int,

    # 关联的外键与被关联外键数据类型应该一致
    product_id int,
    primary key (staff_id),
    foreign key (product_id) references product (product_id)
        on update cascade
        on delete cascade
);

insert into department(department_name, building)
values ('市场部', '市场部大楼'),
       ('研发部', '研发部大楼'),
       ('人事部', '人事部大楼');


insert into product(title, department_name)
values ('1号产品', '研发部'),
       ('2号产品', '研发部'),
       ('3号产品', '研发部'),
       ('4号产品', '市场部');


delete
from department
where department_name = '研发部';

update department
set building='市场部牛逼大楼'
where department_name = '市场部';

posted @ 2019-04-22 19:40 马一特阅读(1793) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部