[原创]数据库基础知识详解

数据库结构模型分类

1、层次模型

2、网状模型

3、关系模型

关系模型的组成部分

• 二维关系
  • 表
    • row
    • column
  • 索引
    • index
  • 视图
    • view (只包含固定字段，不包含其他字段)

关系型数据库的常见组件:

• 数据库
  : Database
• 表
  : table
  • 行
    : row
  • 列
    : column
• 索引
  : index
• 视图
  : view
• 用户
  : user
• 权限
  : privilege
• 存储过程
  : procedure
• 存储函数
  : function
• 触发器
  : trigger
• 事件调度器
  : event schedule

DBA的组成部分

• SQL引擎
  • 分析器
  • 计划执行器
  • 优化器
  • 操作求解器
• 存储引擎
  • 文件和存储接口
  • 缓冲管理器
  • 磁盘空间管理器
  • 恢复管理器
  • 事务管理器
  • 锁管理器

SQL接口（structured query Language）

类似于OS的shell接口，操作数据库的数据接口，也提供了编程功能

• SQL接口语言的标准（其由ANSL组织定义）

  • SQL86
  • SQL89
  • SQL92
  • SQL99
  • SQL03

• SQL接口的语言分类

  • DDL:(Data Defined Language)
    • create
    • alter
    • drop
  • DML:(Data Manapulating Language)
    • insert
    • delete
    • update
    • select
  • DCL: (Data Control Language)
    • grant
    • revoke

DB是否支持事务的测试标准

• ACID

  • A : 原子性 → 不可分割的整体
  • C : 一致性 → 数据的变化是一致性的
  • I : 隔离性 → 事务彼此之间是隔离的
  • D : 持久性 → 只要一个事务完成，它都是持久完成的。

• 事务是将组织多个操作为一个整体，要么全部执行，要全部不执行。其实现机制为：

  • 回滚机制
  • 事务机制

RDBMS设计范式基础概念

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴德斯科范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般说来，数据库只需满足第三范式(3NF）就行了。

• 1） 第一范式（1NF）
  • 所谓第一范式（1NF）是指在关系模型中，对域添加的一个规范要求，所有的域都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。即实体中的某个属性有多个值时，必须拆分为不同的属性。在符合第一范式（1NF）表中的每个域值只能是实体的一个属性或一个属性的一部分。简而言之，第一范式就是无重复的域，并不可分析
• 2） 第二范式 （2NF）
  • 第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或记录必须可以被唯一地区分，即不能有两个行是一样的。选取一个能区分每个实体的属性或属性组，作为实体的唯一标识。
• 3） 第三范式 （3NF）
  • 第三范式（3NF）是第二范式（2NF）的一个子集，即满足第三范式（3NF）必须满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个关系中不包含已在其它关系已包含的非主关键字信息。简而言之，不依赖于非主属性，表与子表中不能有相同的字段。

数据库的运行模型：

数据库为C/S架构，如下：

• S : server ,监听于套接字止，接收并处理客户端的应用请求
• C : Client
  • 程序接口
    • CLI
    • GUI
  • 应用编程接口
    • ODBC : Open Database Connection(开放式数据互联)

MYSQL的工作模型

• 单进程多线程
• 用户连接通过线程实现
• 一个线程池可以定义mysql的并发连接
• 处理用户连接的叫连接线程
• 数据字典:数据一切的元数据信息,依赖mysql库来存储

数据库的基础概念

• 1）约束
  : Constraint
  • 主键
    : 一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行，且必须提供数据，即NOT NULL
    
  • 唯一键
    : 一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行，允许为null
    
  • 外键
    : 一个表中的某字段可填入数据取决于另一个表中的主键已有的数据。
  • 检查性约束
    : 表达式约束,取决于表达式的要求
• 2） 索引
  : 将表中的一个或多个字段中的数据复制一份另存，并且这些字段需要按特定的次序排序存储,常见的索引类型:
  • 树形索引
    (MariaDB默认为Btree索引)
  • bash索引
    

    索引有利于读请求,但不得写请求

• 3） 关系运算
  
  • 选择
    : 挑选出符合条件的行（部分）
  • 投影
    : 挑选出需要的字段
  • 连接
    : 将多张表关联起来
    • 内连接
    • 外连接
      • 左外连接
      • 右外连接
    • 自连接
• 4）数据抽象
  :
  • 物理层
    : 决定数据的存储格式,即RDBMS    在磁盘上如何组织文件
  • 逻辑层
    : 描述DB存储什么数据,以及数据间存在什么样的关系
  • 视图层
    : 描述DB中的部分数据
• 5) 关系模型的分类
  • 关系模型
    
  • 实体关系模型
    
  • 基于对象的关系模型
    
  • 半结构化的关系模型
    
    • XML格式就是一种半结构化数据

常见的RDBMS数据库

• MySQL
• MariaDB
• PostgreSQL(pgsql)
• Oracle
• MSSQL

MariaDB的特性

• 插件式存储引擎(注:存储引擎也称之为”表类型
  “)
  • MYISAM
    → Aria
    • 不支持事务
  • INNODB
    → XtraDB
    • 支持事务
• 诸多扩展和新特性
• 提供了更多的测试组件
• truly open source

Mariadb的安装

通用二进制格式安装过程
a. 准备数据目录
    以/mydata/data为例；
b. 配置mariadb
                    # groupadd -r -g 306 mysql
                    # useradd -r -g 306 -u 306 mysql
                    # tar xf mariadb-VERSION.tar.xz -C /usr/local
                    # ln -sv mariadb-version mysql
                    # cd /usr/local/mysql
                    # chown -R root:mysql ./*
                    # scripts/mysql_install_db --datadir=/mydata/data --user=mysql
                    # cp supper-files/mysql.server /etc/rc.d/init.d/mysqld
                    # chkconfig --add mysqld
c. 准备配置文件
    配置格式：类ini格式，为各程序均通过单个配置文件提供配置信息；
    [prog_name]
能用二进制格式安装,配置文件查找次序：
/etc/my.cnf --> /etc/mysql/my.cnf --> --default-extra-file=/PATH/TO/CONF_FILE --> ~/.my.cnf
OS提供的mariadb rpm包安装的配置文件查找次序:
/etc/mysql/my.cnf --> /etc/my.cnf --> --default-extra=/PATH/TO/conf_file --> ~/my.cnf
以上两者越靠后就是最后生效的.
                    # mkdir /etc/mysql
                    # cp support-files/my-large.cnf /etc/mysql/my.cnf
                    添加三个选项：
                        [mysqld]
                        datadir = /mydata/data
                        innodb_file_per_table = on
                        skip_name_resolve = on

MariDB程序的组成

• Client
  
  • mysql
    : CLI交互式客户端程序
  • mysqldump
    : 备份工具
  • mysqladmin
    : 管理工具
  • mysqlbinlog
    : 查看二进制日志工具
• Server
  
  • mysqld
    : 服务端进程
  • mysqld_safe
    : 服务端进程,默认也是运行的此进程
  • mysqld_multi
    : 服务端进程, 多实例
  • mysql_upgrade
    : 升级工具

服务端监听的两种socket地址

• ip socket
  
  • 监听在3306/tcp,支持远程通信
• unix socket
  
  • 监听在sock文件上(/tmp/mysql.sock, /var/lib/mysql/mysql.sock),仅支持本地通信,通信主机为localhost,127.0.0.1都基于unix socket文件通信

命令行交互式客户端程序—-mysql工具

• options
  
  • -uUSERNAME
    : 用户名,默认为root
  • -hHOST
    : 服务器主机,默认为localhost
  • -pPASSWD
    : 用户的密码
  • dDB_NAME
    : 连接到服务端之后,指明默认数据库
  • -e 'SCRIPT'
    : 连接至MYSQL运行某命令后,直接退出,并返回结果
    ````
    mysql -uroot -h127.0.0.1 -pmagedu -e ‘show databases;’

注意: mysql的用户帐号由两部分组成,'username'@'hostname',其中host用于限制此用户可通过哪些主机连接当前的MSYQL服务器

• 支持通配符
  :

  • %
    : 匹配任意长度的任意字符

    172.16.%.%
    

  • _
    : 匹配任意单个字符
• 内置命令
  
  • \u DB_NAME
    : 设定哪个库为默认数据库
  • \q
    : 退出
  • \d CHAR
    : 设定新的语句结束符
  • \g
    : 语句通用结束标记
  • \G
    : 语句结束标记,但以竖排方式显示
  • \s
    : 返回客户端与服务端的连接状态
  • \c
    : 取消命令运行

通过mysql协议发往服务器执行并取回结果,每个命令都必须有结束符,默认为";",示例如下:
for i in {1..100};do AGE=$[$RANDOM%100];mysql -uroot -pM8T9cw -e "insert mydb.student(id,name,age) value ($i,\"stu$i\",$AGE);"; done

• 获取命令帮助
  
  • help
    

SQL中的数据类型

• 字符型
  
  • 定长字符型 : (最多255个字符)
    • CHAR(#)
      : 不区分字符大小写
    • BINARY(#)
      : 区分字符大小写
  • 可变长字符型 :
    • VARCHAR(#)
      : 不区分字符大小写,需要在字符长度加1,最多65536个字符
    • VARBINARY(#)
      : 区分字符大小写,需要在字符长度加1,最多65536个字符
    • TEXT
      : 可存文本(2^32)个字符,只能存纯文本,不区分大小写
      • TINYTEXT
        
      • TEXT
        
      • MEDIUMTEXT
        
      • LONGTEXT
        
    • BLOB
      : 文本(2^32)个字符,可以存储图片
• 内置类型
  
  • SET
    : 集合
  • ENUM
    : 枚举
• 数值型
  
  • 精确数值型
    
    • INT
      
      • TINYINT
        : 一个字节
      • SMALLINT
        : 二个字节
      • MEDINUMINT
        : 三个字节
      • INT
        : 四个字节
      • BIGINT
        : 八个字节
  • 近似数据型
    • FLOAT
      : 单精度
    • DOBULE
      : 双精度
• 日期时间型
  
  • DATE
    : 日期型
  • TIME
    : 时间型
  • DATETIME
    : 日期时间型
  • TIMESTAMP
    : 时间戳(从过去到现在经过的秒数)
  • YEAR(2)
    : 2位年数
  • YEAR(4)
    : 4位年数
• 数据类型修饰符
  
  • 所有类型都适用:
    • NOT NULL
      : 非空
    • DEFAULT value
      : 默认值
  • 数值型适用
    • AUTO_INCREMENT
      : 自增长
    • UNSIGNED
      : 无符号,通常用于Int后面,进行修饰为正整数
  • 字段修饰符
    • PRIMARY KEY
      : 主键定义
    • UNIQUE KEY
      : 唯一键定义

数据库操作语言

数据库操作

• 使用格式
  

create database | schema [if not exists]'DB_NAME';
drop database | schema 'DB_NAME';

• 查看数据库
  • show databases;
    
• 创建数据库
  • create database mydb;
    
• 删除数据库
  • drop database mydb;
    
• 查看支持的所有字符集
  • show character set;
    
• 查看支持的所有排序规则
  • show collation;
    
• 修改数据库的默认字符集
  • alter database testdb character set utf32;
    
• 修改数据库的默认字符排序规则
  • alter database testdb collate utf32_sinhala_ci;
    

表操作

• 使用格式
  

create table [if not exists] tb_name (col1 datatype 修饰符, col2 datatype 修饰符) engine=' '

• 查看所有的引擎(被支持的)
  • show engines
    
• 查看所有数据库中的表
  • show tables;
    
  • show tables from mysql;
    
• 查看表结构
  • desc students;
    
• 创建表
  • create table students(id int unsigned not null primary key, name varchar(30) not null, age tinyint unsigned not null,gender enum('F','M'))
    
• 向表中添加字段
  • alter table students add second_name char(30);
    
• 向表中删除字段
  • alter table students drop second_name;
    
• 对表添加主键
  • alter table students2 add primary key (id);
    
• 对表删除主键
  • alter table students2 drop primary key;
    
• 对表添加索引
  • alter table students2 add index name (name);
    
• 对表删除索引
  • alter table students2 drop index name;
    
  • drop index name on students2;
    
• 对表添加唯一键
  • alter table students drop second_name;
    
• 对表删除唯一键
  • alter table students drop index name;
    

• 修改字段字义属性

  • alter table students modify name char(20);
    
  • alter table students2 change name new_name char(20) after id;
    

  • alter table students2 change name new_name char(20);
    

    modify : 只可以修改字段定义的属性
    change : 可以修改字段名称和定义的字段属性
    

• 查看表状态
  • show table status like 'students2'\G
    
• 修改表引擎
  • alter table students2 engine[=]myisam;
    
• 查看表的字段描述
  • desc students;
    
• 查看库中的所有表
  • show tables
    
  • show tables from mysql;
    
• 删除表
  • drop table students2;
    

向表中插入数据

• insert weizi value(1,'zhen',30,'F');
  
• insert weizi values(3,'wei',30,'F'),(4,'ping',31,'F');
  
• insert weizi (id,name) value (5,"weizi");
  

删除表中的数据

• delete from weizi where name="zhen";
  
• delete from weizi where age is null;
  
• delete from weizi where age > 30;
  
• delete from weizi where id >=50 and age <=20;
  
• delete from weizi order by age asc limit 5;
  

修改表中的字段值

• update weizi set age=35 where id=3 and age=30;
  
• update weizi set age=age-5 where age=35;
  
• update weizi age=age-age;
  
• update weizi set age=age-5 where order by id desc limit 10;
  
• update weizi set age=age-15 where name not like 'stu%';
  

Select操作语句

• 使用格式

select col1,col2,....from tb1_name [where clause] [order y 'col_name'] [limit [m,]n]

• 字段表示法
  • *
    : 表示所有字段
  • as
    : 字段别名, col1 as alias1
• where clause
  • 操作符
    :
    • ==
      
    • <
      
    • >
      
    • <=
      
    • >=
      
    • !=
      
    • between....and...
      
  • 条件逻辑操作
    
    • and
      
    • or
      
    • not
      
  • 模糊匹配
    
    • like
      
      • %
        
      • _
        
    • rlike 'pattern'
      : 基于正则表达式匹配
    • is null
      
    • is no null
      
• 排序
  • desc
    : 降序
  • asc
    : 升序

Select示例

• select name,age from students where age >30 and age<80;
  
• select name,age from students where age between 30 and  80;
  
• select name from students where name like '%ang%';
  
• select name from students where name rlike '^.*ang.*$;
  
• select name,age from students where age is null;
  
• select name,age form students where age is not null;
  
• select id,name from students order by name;
  
• select id,name from students order by name desc;
  

权限及授权管理

• 用户表示方法:

  username@host
  

• 管理权限的分类

  • 管理权限
    
  • 数据库
    
  • 表
    
  • 字段
    
  • 存储例程
    

授权:

• 授权语法格式
  • grant pri_type,...on [object_type] db_name.tbl_name to 'user'@'host' [identified by 'PASSWD']
    
    • pri_type
      
      • all privileges
        : 表示全部权限
    • db_name.tbl_name
      的表示方法:
      • *.*
        : 所有库的所有表
      • db_name.*
        : 指定库的所有表
      • db_name.tbl_name
        : 指定库的特定表
      • db_name.routine_name
        : 指定库上的存储过程或存储函数
    • object_type
      
      • table
        
      • function
        
      • procedure
        
• 授权示例:
  • grant all privileges on mydb.* to 'zhenping'@'172.16.%.%' identified by 'MT8ddd';
    

取消权限

• 取消授权语法格式
  • revoke pri_type,.... on db_name.tb_name from 'user'@'host';
    
• 取消授权示例:
  • revoke all privileges on mydb.* from 'zhenping'@'172.16.%.%';
    

查看用户权限

• show grants for 'user'@'host';
  

让新授权的权限立即生效

• flush privileges;
  

1 Mariadb服务进程启动时会读取mysql库中的所有授权表至内存中;
2 grant和revoke等执行权限操作时会保存于表中,mariadb的服务进程会自动重读授权表
3 对于不能够或不能及时重读授权表,可手动让服务进程重启授权表,使用flush privileges

号外号外：

现在我们公众号推出参与奖和互动奖，凡是通过微信与我们参与讨论互动最多的朋友，将获得我们送出的神秘礼物，还有机会获得Ansible中文官网马上将出版的新书哦！ 我们将不定期的推出奖励计划 ！ 奖励多多，小伙伴们，一起来吧！

以上是今天为大家带来的内容，如果有任何问题，大家也可以添加以下QQ群参与问题的讨论。

• Ansible中文权威群：372011984(已满）

• AWK&SED企业实战： 260039357

• docker企业架构实践：491533668

• Jumpserver交流群 ：399218702

• Ansible中文权威-2号群：486022616

关于我们：