MySQL DataBase

DBMS

DataBase Management System，数据库管理系统。常见的有 MySQL、Oracle、Microsoft SqlServer

SQL

Structured Query Language，结构化查询语言。

DBMS 通过 SQL 语句对 DB 中的数据进行增删改查。

分组函数 在使用的时候必须先进行分组，然后才能用。如果你没有对数据进行分组，整张表默认为一组。

count(具体字段)：表示统计该字段下所有不为NULL的元素的总数。
count(*)：统计表当中的总行数。只要有一行数据,则 count++。

str_to_date 将字符串转换成日期
date_format 格式化日期
format 设置千分位

备份数据库

mysqldump -u 用户名 -p 数据库 表1 表n > d:\\文件名.sql
mysqldump -u 用户名 -p -B 数据库1 数据库n > d:\\文件名.sql

恢复数据库
source d:\\文件名.sql;

数据类型

数据类型	说明	大小（字节）
BIT(M)	1 - 64，位类型。M 指定位数，默认1
TINYINT [UNSIGNED]	有符号（-128 - 127），无符号（0 - 255）。默认无符号	1
SMALLINT [UNSIGNED]	有符号（-2^15 - 2^15 - 1），无符号（0 - 2^16 - 1）	2
MEDIUMINT [UNSIGNED]	有符号（-2^23 - 2^23 - 1），无符号（0 - 2^24 - 1）	3
INT [UNSIGNED]	有符号（-2^31 - 2^31 - 1），无符号（0 - 2^32 - 1）	4
BIGINT [UNSIGNED]	有符号（-2^63 - 2^63 - 1），无符号（0 - 2^64 - 1）	8
FLOAT [UNSIGNED]	表示小数	4
DOUBLE [UNSIGNED]	表示比 FLOAT 精度更大的小数	8
DECIMAL(M, D) [UNSIGNED]	定点数 M （max 65）指定长度，默认10，D （max 30）表示小数点的位数
CHAR(SIZE)	固定长度字符串，最大 255 字符，SIZE 表示字符的数量
**VARCHAR(**SIZE)	可变长度字符串 （0 - 65535 字节）	utf8 最大21844字符 1 - 3 个字节用于记录大小。 gbk 最大 32766 字符。
BLOB LONGBLOB	二进制数据 BLOB （0 - 2^16 - 1），LONGBLOB （0 - 2^32 - 1）
TEXT LONGTEXT	文本 TEXT （0 - 2^16 - 1），LONGTEXT （0 - 2^32 - 1）
DATE	日期类型（YYYY-MM-DD）
DATETIME	（YYYY-MM-DD HH:MM:SS）
TIMESTAMP	TimeStamp 时间戳，自动记录 insert、update操作时间

事务

多条 DML 语句联合起来的执行某一个业务逻辑。原子性、一致性、隔离性、持久性。

事务与事务之间的隔离性级别，read uncommitted(脏读问题) read committed(不可重复读取数据) repeatable read(读取到的数据是幻象，不够真实) serializable(最高的隔离级别)

视图

视图对象存储在硬盘上，方便，简化开发，利于维护。视图对应的语句只能是DQL语句。

create view emp_dept_view as select e.name,e.sal,d.name from emp e join dept d on e.deptno = d.deptno;

数据库设计三范式

第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分。一对一，外键唯一。

第二范式：建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不要产生部分依赖。多对多，三张表，关系表两个外键。

第三范式：建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖。一对多，两张表，多的表加外键。

分组查询

select
	...
from
	...
group by
	...
	
关键字的顺序不能颠倒：
select ... from ... where ... group by ... having ... order by ... limit ...
执行顺序为：
from - where - group by - having - select - order by - limit

注意 ：

• 在一条 select 语句中，如果有 group by，select 后面只能跟参与分组的字段，以及分组函数。
• 从某张表中查询数据，先经过 where 条件筛选出有价值的数据，再对这些有价值的数据进行分组，分组之后可以使用 having 继续筛选。select 查询出来，最后排序输出。

C# 连接 MySQL

• data source=服务器IP地址;
• database=数据库名称;
• user id=数据库用户名;
• password=数据库密码;
• pooling=是否放入连接池;
• charset=编码方式;

字符集

名称	Maxlen (字节)
ascii	1
latin1	1
gb2312	2
gbk	2
utf8	3
utf8mb4	4

1. 字符集指的是某个字符范围的编码规则。

2. 比较规则是针对某个字符集中的字符比较大小的一种规则。

3. 在MySQL 中，一个字符集可以有若干种比较规则，其中有一个默认的比较规则，一个比较规则必须对应一个
   字符集。

4. 查看MySQL 中查看支持的字符集和比较规则的语句如下：

   SHOW (CHARACTER SET|CHARSET) [LIKE 匹配的模式];
   SHOW COLLATION [LIKE 匹配的模式];
   

5. MySQL有四个级别的字符集和比较规则，服务器、数据库、表、列。

存储引擎

MySQL 服务器上负责对表中数据读取和写入工作的部分，例如 InnoDB 、MyISAM 、Memory 等。真实数据在不同存储引擎中存放的格式一般是不同的。

InnoDB

将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB 中页的大小一般为 16 KB。

行格式

在Compact 行格式中，把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长字段长度列表，各变长字段数据占用的字节数按照列的顺序逆序存放。

Dynamic和Compressed行格式类似于Compact行格式，只不过在处理行溢出数据时有点儿分歧，它们不会在记录的真实数据处存储字符串的前768个字节，而是把所有的字节都存储到其他页面中，只在记录的真实数据处存储其他页面的地址。

记录头信息

名称	大小（bit）	描述
预留位1	1	没有使用
预留位2	1	没有使用
delete_mask	1	标记该记录是否被删除
min_rec_mask	1	B+树的每层非叶子节点中的最小记录都会添加该标记
n_owned	4	表示当前记录拥有的记录数
heap_no	13	表示当前记录在记录堆的位置信息
record_type	3	表示当前记录的类型， 0 表示普通记录， 1 表示B+树非叶节点记录（目录项记录）， 2 表示最小记录， 3 表示最大记录
next_record	16	表示下一条记录的相对位置

注意：next_record 下一条记录指得并不是按照我们插入顺序的下一条记录，而是按照主键值由小到大的顺序的下一条记录。而且规定 Infimum 记录（也就是最小记录） 的下一条记录就是本页中主键值最小的用户记录，而本页中主键值最大的用户记录的下一条记录就是 Supremum 记录（也就是最大记录）。

不论我们怎么对页中的记录做增删改操作，InnoDB 始终会维护一条记录的单链表，链表中的各个节点是按照主键值由小到大的顺序连接起来的。

当数据页中存在多条被删除掉的记录时，这些记录的 next_record 属性将会把这些被删除掉的记录组成一个垃圾链表，以备之后重用这部分存储空间。

数据页结构

Page Directory 由每个组的最后一条记录的地址偏移量（槽）按照顺序存储。对于最小记录所在的分组只能有 1 条记录，最大记录所在的分组拥有的记录条数只能在 1~8 条之间，剩下的分组中记录的条数范围只能在是 4~8 条之间。

在一个数据页中查找指定主键值的记录：

• 通过二分法确定该记录所在的槽，并找到该槽中主键值最小的记录
• 通过记录的 next_record 属性遍历该槽所在的组中的各个记录

B+ 树索引

各个 InnoDB 数据页面组成一个双向链表，每个数据页中的记录会按照主键值从小到大组成一个单向链表，每个数据页会为存储在里面的记录生成一个页目录，在通过主键查找记录的过程中，先使用二分法在页目录中找到对应的槽，然后遍历该槽对应的分组找到指定的记录。

下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。需要给所有的页建立一个目录项每个页对应一个目录项，每个目录项包含页的记录中最小的主键值（key）以及页号（page_no）。这个目录就是索引。

不论是存放用户记录的数据页，还是存放目录项记录的数据页，我们都把它们存放到 B+树 这个数据结构中了，所以我们也称这些数据页为节点。实际用户记录其实都存放在 B+ 树的最底层的节点上，这些节点也被称为叶子节点或叶节点，其余用来存放目录项的节点称为非叶子节点或者内节点，其中B+ 树最上边的那个节点称为根节点。

InnoDB 中的索引即数据，数据即索引，而 MyISAM 中却是索引是索引、数据是数据。