数据库管理系统概述

• DBMS:数据库管理系统
• RDBMS:关系型数据库管理系统
  • C/S架构：服务器端与客户端通过专有协议通讯
  • 关系模型：表（行、列），二维关系；
  • 范式：第一范式、第二范式、第三范式
    • 所谓第一范式（1NF）是指在关系模型中，对域（域代表字段）添加的一个规范要求，所有的域都应该是原子性的（不可分拆），即数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。即实体（每一行）中的某个属性有多个值时，必须拆分为不同的属性。在符合第一范式（1NF）表中的每个域值只能是实体的一个属性或一个属性的一部分。简而言之，第一范式基本要求就是无重复的域。
    • 第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或记录必须可以被唯一地区分（不能有两个行是一样的）。选取一个能区分每个实体的属性或属性组，作为实体的唯一标识。
    • 第三范式（3NF）是第二范式（2NF）的一个子集，即满足第三范式（3NF）必须满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个关系中不能包含已在其它关系已包含的非主关键字信息。简而言之，第三范式就是属性不依赖于其它非主属性，也就是在满足2NF的基础上，任何非主属性不得传递依赖于主属性。
  • 运算关系：
    一个表中有多个列或字段，从中挑选出期望的一部分；使用select语句中进行过单表查询，关系运算还可以有复杂查询操作，用来实现较为复杂的表和表之间的运算；例如，对表进行连接运算，做笛卡尔乘积；做自然连接、外连接、左外连接、右外连接、全外连接、内连接、自连接等等都是关系运算；
    • 选择：在一个表中挑选出符合条件的行
    • 投影：在一个表中挑选出符合条件的列
  • 数据库：表、索引、视图（虚表）、SQL（二次封装的API）、存储过程（有调用无返回值）、存储函数（有调用有返回值）、触发器、时间调度器；
    数据库中有表组成，这些表糅合起来就称之为数据库，围绕表本身还有很多其它组件：
    • 索引：是一种按照特定数据结构组织起来的表中的某个或某些字段数据；所以一个表上可以有多个索引，每一次构建索引，其实就是从表中把特定字段是数据抽取出来；一般而言是按照特定模式组织起来的；如果是组织成树状结构，使用B+树（平衡树）索引，需要把整个数据按顺序进行排列以后，构建成平衡树的样式来进行索引的创建；索引的主要目的是为了加速查询操作；但不可避免，索引一定会影响写入操作；
    • 视图：又称为虚表，实际上就是存储下来的select语句，select语句的查询结果展示为一个表的形式，但事实上可以把一个语句存下来，然后对这个语句本身再次做查询操作，类似于做了一次子查询；
    • SQL：结构化查询语言主要分为ddl和dml，ddl数据定义语言，dml叫做数据操纵语言；数据定义语言主要是定义数据中的表、索引、视图等等；数据操纵语言则主要是操作表中的数据；
      • DDL：CREATE，ALTER，DROP
      • DML：INSERT/UPDATE/DELETE/SELECT
    • 内建函数：mysql或Oracle内部都有很多内建的函数，调用后直接完成特定数据处理，或者是完成某些系统信息获取的相关功能
    • 自定义函数：在编程接口下，自行可以定义存储过程（过程通常指的是没有返回值的函数）、存储函数（通常指的是有返回结果的代码片段）；
    • procedure：如果一段代码对数据做处理以后没有任何返回值，仅仅是做数据加工的，所以通常称为一个过程
    • function：如果对数据加工以后有返回值，能调用能返回这个通常称为函数
    • 触发器：其实也是代码片段，当某一特殊事件发生时，触发器会被满足触发条件，然后被触发，而触发器通常是一类的sql语句，或是之前写好的一段代码片段；
    • 事件调度器：event scheduler组件，它的主要作用有的类似于linux中的crontab，能够定期执行一些内建的任务，从而完成某些特定操作，或某些周期性任务操作；只不过，在mysql中这个被称作事件调度器；
  • 约束：
    • 主键约束：唯一（key）、非空、一张表中只能有一个
    • 唯一键约束：唯一、可以存在多个
    • 外键约束：参考性约束
    • 检查性约束：check
  • 实现：
    • 商业：Oracle, DB2, Sybase, Infomix, SQL Server；
    • 开源：MySQL, MariaDB, PostgreSQL, SQLite；
  • 三层模型：
    一个关系型数据库，站在不同的角度，底层系统工程师角度，数据库管理工程师角度，最终用户的角度来看，整个数据大体可分为三次模型；
    • 物理层：-->SA，数据库真正存储下来以后，表现在文件系统或是存储设备上，它被组织成什么样式；例如，一个表表现成一个文件，或者是多张表表现为一个文件，它们存储在一个被叫做表空间的组件中等等；系统工程师打开文件系统以后，进入某个数据存储目录里可以看到这样组件的；这这种就叫做物理层视图；
    • 逻辑层：-->DBA，即向上一层叫逻辑层，就是在dba角度看到的组件，像表、索引、视图等等，数据库内建组件，称作逻辑层的视图；
    • 视图层：-->Coder，所谓视图层指的是，最终用户所看到的样子，例如一个数据库中有10张表，可以授权一个用户只能看其中3张表，这就是一个用户的视图；同样的，库中10张表中，有些表中有敏感数据，授权某一用户只能通过表上构建的视图看到其中某些字段和某些行；这也是一个用户最终所看到的视图；

所以说，数据库有三个不同的管理层次，作为系统工程师，有可能帮助dba，去组织把他的数据放在特定的存储设备上，必要时还有给他们提供raid、提供分布式存储等等，各种各样底层组织的形式，还有手动帮它们管理这些文件，这个就是在物理层完成的工作；
而作为dba，需要设计、创建表、设计索引、优化索引、监控索引运行、必要时设计视图等等，这些都是要在逻辑层完成的工作；一个dba通常就负责这些事情；当然也包括数据集的导入导出即所谓数据备份和恢复；
而终端用户，只需要能看到数据完成所需要的增、删、查、改等操作；这个就叫视图层；

• NoSQL：非关系型数据库
  • Document store 文档存储
    • Search engine：倒排索引,如：Solr,ElasticSearch
  • Key-value store 键值存储
  • Wide column store 列式存储
  • Graph DBMS 图式存储
  • Time series DBMS 时间序列存储 InfluxDB

MySQL简介

mysql现在主要由Oracle维护的；在基本功能上mysql和mariadb仍然是兼容的，二者是两种不同的发布路线；

• 特性：
  • 插件式存储：
    每一个关系型数据库管理系统都需要自己的物理层，都需要存储引擎将逻辑组件最终转换为物理组件，以存储在文件系统上；但mysql采用了非常精巧的方案，即插件式存储引擎，也就意味着mysql能支持众多存储引擎，而且有能力的话还可以开发自己的存储引擎；在mysql和mariadb就有很多存储引擎可选择，有的是开源的，有的可能是额外购买的商业版本；另外不同存储引擎由于它们是物理层的实现，所以使得mysql中是否支持某些功能是由存储引擎决定的；例如，mysql是否支持事务，则取决于存储引擎自己是否支持；处理mysql-server是关键组件之外，而存储引擎是另外一个关键的核心要件；
  • 单进程多线程模型：
    mysql是单进程多线程模型，也就意味着mysql启动后能看到的进程只有一个，其内部靠多线程分别完成不同的功能；在众多线程中最关键的有两类：
    • 连接线程：连接线程主要负责维护用户连接的
    • 守护线程；而守护线程包括像对应的数据从内存中刷写到磁盘上，从磁盘加载数据至内存中维护一个cache和buffer等等都是由守护线程完成；

• 安装方法：

  • os vendor：镜像自带的yum仓库中的rpm包安装（很少使用）
  • MySQL：（官方提供的程序包）
    • prepackage：便于批量管理，容易实现自动化；
    • source code（源码包）：有利于根据需求自定制，要使用cmake编译
    • binary package（二进制包）：展开可用的包，不利于大规模部署；

• mysql二进制包安装简要说明：

  • 创建数据目录，属主属组属于运行着用户身份mysql；
  • 初始化：mysqld --initialize-insecure --basedir=/usr/local/mysql --datadir=/data/mysql --user=mysql
  • 查看配置文件路径，修改配置文件：/usr/local/mysql/etc/my.cnf
    • datadir:数据目录
    • socket：
    • includedir：
    • pid：
  • 准备错误日志文件：touch后要改属主属组；
  • 复制启动脚本：/usr/local/mysql/support-files/mysql.server --> /etc/init.d/mysqld

• 配置文件：
  mysql会读取多处的多个配置文件，而且会以指定的次序依次进行：
  查看命令：my_print_defaults

  Default options are read from the following files in the given order:
  /etc/mysql/my.cnf  /etc/my.cnf ~/.my.cnf 
  

  不同的配置文件中出现同一参数且拥有不同值时，后读取将为最终生效值；

    # 修改默认读取的配置文件（mysqld_safe命令）：
                 --defaults-file=file_name
    # 于读取的默认配置文件之外再加载一个文件：  
                --defaults-extra-file=path
  

配置文件格式：ini风格的配置文件，能够为mysql的各种应用程序提供配置信息：

[mysqld]
[mysqld_safe]
[mysqld_multi]
[server]
[mysql]
[mysqldump]
[client]
...
            
    格式：PARAMETER = VALUE 
                
        ARAMETER：#一般情况下下划线和短横线通用，个别参数除外
                    innodb_file_per_table
                    innodb-file-per-table 

• 服务端程序：服务器端命令需要通过mysql协议，通过远程发送给mysql服务器，由服务器运行并且把结果取回到本地这种方式来实现；所有的服务器端命令都需要使用分号命令结束符；
  • mysqld：
  • mysqld_safe：线程安全
  • mysqld_multi：单主机运行多实例
• 客户端程序：
  • mysql：交互式CLI工具
  • mysqldump：备份工具；基于mysql协议，向mysqld发起查询请求，并将查得的所有数据转换成insert等写操作语句保存于文本文件中，从而完成文件备份工作；
  • mysqladmin：基于mysql协议管理mysql
  • mysqlimport：数据导入工具；不是导入mysqldump命令导出来的数据的，而是那些被简单格式化为文本的数据；
  • 非客户端程序：
    • myisamchk：检查整理修复isam表
    • myisampack：把myisam表答包压缩以后存放，只能实现查询

mysql：交互式CLI工具；

用法：mysql [option] db_name

常用选项：
    --host=host_name,-h host_name:服务端地址
    --user=user_name,-u user_name:用户名
    --password[=password],-p[password]:用户密码
    --port=port_num,-P port_num:服务器端口
    --protocol={TCP|SOCKET|PIPE|MEMORY}: 
            本地通信：基于本地回环地址进行请求，将基于本地通信协议；
                    Linux：SOCKET
                    Windows：PIPE,MEMORY
            非本地通信：使用非本地回环地址进行的请求：TCP协议；
    --socket=path,-S path:socket文件路径
    --database=db_name,-D db_name:数据文件路径名称
    --compress,-C :数据压缩传输
    --execute=statement,-e statement:非交互模式执行SQL语句
    --vertical, -E:查询结果纵向现实
mysql命令的使用帮助：
    # man mysql
    # mysql  --help  --verbose  
sql脚本运行：
    mysql [options] [DATABASE] < /PATH/FROM/SOME_SQL_SCRIPT

mysqld服务器程序：

• 工作特性的定义方式 ：

  • 命令行选项
  • 配置文件参数

• 用法：SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]

• 服务器参数/变量：设定MySQL的运行特性；mysql> SHOW GLOBAL|[SESSION] VARIABLES [LIKE clause];

• 状态（统计）参数/变量：保存MySQL运行中的统计数据或状态数据；mysql> SHOW GLOBA|[SESSION] STATUS [LIKE clause];

• 显示单个变量设定值得方法：`mysql>SELECT @@[GLOBAL.|SESSION.]system_var_name

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

• 变量/参数级别：

  • 全局：为所有会话设定默认值
  • 会话：跟单个会话相关，会话建立会从全局继承

• 服务器变量的调整方法：

  • 运行时参数：
    • global：仅对修改后新建立的会话有效
    • session：仅对当前会话有效，且立即生效
  • 启动前通过配置文件修改：重启后生效

• 运行时修改变量值操作方法：mysql> HELP SET

  • SET [GLOBAL | SESSION] system_var_name = expr
  • SET [@@global. | @@session. | @@]system_var_name = expr
    注意：GLOBAL 值得修改要求用户拥有管理权限

• 安装完成后的安全初始化：mysql_secure_installation

• 运行前常修改的参数：

  • innodb_file_per_table=ON
  • skip_name_resolve=ON

MySQL的数据类型：

字符型、数值型、日期时间型、内建类型

• 字符型：
  • CHAR(#),BINARY(#)：定长数据类型；CHAR不区分大小写，而BINARY区分；最多支持255字符长度；
  • VARCHAR(#)：变长数据类型，需要结束符；不区分大小写；2^16,最多支持65535个字符长度
  • VARBINARY(#)：变长数据类型，需要结束符；区分字符大小写；0-65535bytes；
  • TEXT：也是文本大对象，基于对象方式存储；定义的长度是二进制数的位数表示的；不区分字符大小写；
    • TINYTEXT：小文本，支持2^8个字符(255)长度
    • TEXT：文本，支持2^16个字节（65535）长度
    • MEDIUMTEXT：中等文本，支持2^24个字节（16777215）长度
    • LONGTEXT：长文本，支持2^32个字节（4294967295）长度
  • BLOB：区分字符大小写
    • TINYBLOB
    • BLOB
    • MEDIUMBLOB
    • LONGBLOB
• 数值型：
  • 浮点型：近似
    • FLOAT：单精度浮点型
    • DOUBLE：双精度浮点型
    • BIT：位数据类型
    • REAL：实数；单精度浮点型和双精度浮点型都算是实数，具体属于哪个取决于sql_mode（sql模型）；sql模型定义了mysql自己可以运行在与哪种sql使用风格兼容的模式之下；比如以Oracle兼容的模型运行，将来可以非常方便的把mysql的数据集导入到Oracle中；
  • 整型：精确
    • INTEGER：整型
    • TINYINT：微整型，数值范围2^8；
    • SMALLINT：小整型，数值范围2^16；
    • MEDIUMINT：中整型，数值范围2^24；
    • INT：整型，数值范围2^32；
    • BIGINT：大整型，数值范围2^64；
    • DECIMAL：十进制型
• 日期时间型：
  • DATE：日期，3个字节；范围：1000-01-01到9999-12-31
  • TIME：时间，3个字节；范围：-838:59:59到838:59:58
  • DATETIME：日期时间，8个字节；范围：100-0101 00:00:01到9999-12-31 23:59:59
  • TIMESTAMP：时间戳，4个字节；范围：1970-01-01 00:00:00到2038-01-18 22:14:07
  • YEAR：年份，YEAR(2),YEAR(4)
• 内建：
  • ENUM：枚举类型；数据类型其实就是字符串类型，范围：1-65535种字符串变化形式；从给定的字符串中选一个字符串；
  • SET：集合类型；范围：1-64种字符串；从给定的字符集合中选择任意多个组合成字符串；
• 类型修饰符：
  • 字符型：
    • NOT NULL
    • NULL
    • DEFALUT ‘STRING'
    • CHARACET SET 'CHARSET'
    • COLLATION 'collocation'
  • 整型：
    • NOT NULL
    • NULL
    • DEFALUT value
    • AUTO_INCREMENT：专用修饰符自动增长字段，要约束该修饰：
    • UNSIGNED：必须是无符号；是字段数据类型的修饰符，必须紧跟在数据类型之后；用来修饰类型本身；
  • 日期时间型：
    • NOT NULL
    • NULL
    • DEFAULT

注意：SET和ENUM二者在存储时不是存储的字符串，所以不能拿来做字符串比较，排序；ENUM真正存储时是存的字符串在ENUM中的索引数，只是存储的数字代表出现在第几位上；
ENUM类型例如，'a'，''，'1'
存储a则实际存储的是0
存储1则实际存储的是2
而SET类型可最多存储64位的二进制数据；存储的是位，只能按位进行比较；
SET类型例如，a，b，c
存储a则实际存储的是100
存储bc则实际存储的011

SQL MODE：定义mysqld对约束等违反时的响应行为等设定；

• 常用MODE：
  • TRADITIONAL：传统模型，要求不允许对非法值做插入操作；
  • STRICT_TRANS_TABLES：表示对所有支持事务类型的表做严格约束；
  • STRICT_ALL_TABLES：表示对所有表做严格约束；
• 查看：mysql>SHOW GLOBAL VARIABLES LIKE ’sql_mode‘;
• 修改方式：
  • 全局
    • mysql>SET GLOBAL sql_mode='MODE';
    • mysql>SET @@GLOBAL.sql_mode='MODE';
  • 会话：
    • mysql>SET SESSION sql_mode='MODE';
    • mysql>SET @@GSESSION.sql_mode='MODE';

注意：改全局不会立即生效且需要权限，只会对以后新建的会话有效，因为新建会话都会从全局继承；想要立即有效则要修改会话级sql接口模式；修改的仅是默认值；
再次强调，无论是全局配置还是会话配置这两种方式都不会永久有效，mysql重启后就没了，想要永久生效只能定义在配置文件中或在mysql启动时直接在命令行参数传递给mysql运行；

SQL: DDL,DML

• DDL：数据定义语言，主要用来定义数据库组件的，例如：库、表、索引、用户、视图、存储过程、存储函数等等;mysql> HELP Data Definition
  • CREATE：创建
  • ALTER：修改
  • DROP：删除
  • 查看命令帮助：HELP {CREATE | ALTER | DROP}

• 数据库：

  • CREATE {DATABASE | SCHEMA} [IF NOT ESISTS] db_name CHARACTER SET [=] charset_name COLLATE [=] collation_name;
  • ALTER {DATABASE | SCHEMA} [db_name] CHARACTER SET [=] charset_name COLLATE [=] collation_name
  • DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

• 表：

  • CREATE:
    • CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options] [partition_options]
      • table_options：ENGINE [=] engine_name
      • 查看支持的所有存储引擎：mysql> SHOW ENGINES;
      • 查看指定表的存储引擎：mysql> SHOW TABLE STATUS LIKE clause;
    • CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_option] [partition options] select_statement直接创建表，并将查询语句的结果插入道新创建的表中
    • CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name)}复制某存在的表的结构来创建新的空表
  • DROP：
    • DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [,tbl_name];
  • ALTER:
    • ALTER TABLE tbl_name [alter_specification [,alter_specification] ...]
      可修改内容：
      (1) table_options
      (2) 添加定义：ADD 字段、字段集合、索引、约束
      (3) 修改字段：CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
      MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
      (4) 删除操作：DROP 字段、索引、约束
  • 表重命名：RENAME [TO | AS ] new_tbl_name;
  • 查看表结构定义：DESC tbl_name;
  • 查看表定义：SHOW CREATE TABLE tbl_name;
  • 查看表属性信息： SHOW TABLE STATUS[{FROM | IN } db_name] [LIKE 'pattern'| WHRER expr] ;

• 索引：

  • 创建：CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name [index_type] ON tbl_name (index_col_name,...)
  • 查看：SHOW {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name [{FROM | IN} db_name] [WHRER expr]
  • 删除：DROP INDEX index_name ON tbl_name
  • 索引类型：
    • 聚集索引、非聚集索引：索引是否与数据存在一起；
    • 主键索引、辅助索引
    • 稠密索引、稀疏索引：是否索引了每一个数据项；
      BTREE（B+）、HASH、R Tree、FULLTEXT
      BTREE：左前缀；
    • EXPLAIN：分析查询语句的执行路径；

• 视图：VIEW，虚表，存储下来的select语句

  • 创建：CREATE VIEW view_name [(column_list)] AS sekect_statement
  • 修改：ALTER VIEW view_name [(column_list)] AS select_statement
  • 删除： DROP VIEW [IF EXISTS] view_name [,view_name] ...

• DML：数据操作语言；mysql> HELP Data Manipulation

  • INSERT
  • DELETE
  • UPDATE
  • SELECT

• INSERT：
  单行插入
  批量插入

            INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
            [INTO] tbl_name [(col_name,...)]
            {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
            [ ON DUPLICATE KEY UPDATE
            col_name=expr
                [, col_name=expr] ... ]
  
            Or:
  
            INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
            [INTO] tbl_name
            SET col_name={expr | DEFAULT}, ...
            [ ON DUPLICATE KEY UPDATE
            col_name=expr
                [, col_name=expr] ... ]
  
            Or:
  
            INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
            [INTO] tbl_name [(col_name,...)]
            SELECT ...
            [ ON DUPLICATE KEY UPDATE
            col_name=expr
                [, col_name=expr] ... ]
  

• DELETE：

  DELETE FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]

            注意：一定要有限制条件，否则将清空整个表；
                限制条件：
                    [WHERE where_condition]
                     [ORDER BY ...] [LIMIT row_count]
  

• UPDATE：

  UPDATE table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

            注意：一定要有限制条件，否则将修改整个表中指定字段的数据；
                限制条件：
                    [WHERE where_condition]
                     [ORDER BY ...] [LIMIT row_count]
                     
        
            注意：sql_safe_updates变量可阻止不带条件更新操作；       
  

• SELECT：详细见后续Mysql多表查询及子查询