数据库学习技术路线

MySQL技术路线

1.入门

数据库入门首选要学会基本环境搭建，然后选择一款顺手的图形界面工具，学习各种SQL语句，包括基本建库建表的DDL语句，然后增删改查语句，再掌握了基本的操作之后，需要学会使用一些高级特性，比如视图、存储过程、游标、自定义函数等。

1.1安装配置

1.2数据库设计

设计步骤

E-R图

三大范式

1.3SQL基础

DDL

数据类型

1、整值类型：

整数类型：TINYINT、SMALLINT、MEDIUMINT、INT、INTEGER、BIGINT

浮点数类型：FLOAT、DOUBLE

定点数类型：DEC、DECIMAL

位类型：BIT

2、字符串类型：

CHAR/VARCHAR

BINARY、VARBINARY

TEXT、BLOB

ENUM、SET

3、日期类型选择

DATE、DATETIME、TIMESTAMP、TIME、YEAR

字符集

1、概述

2、Unicode

3、汉字编码

4、字符集选择

5、MySQL字符集

表类型选择

1、存储引擎概述

2、各种存储引擎基本特性

3、如何选择存储引擎

约束

主键约束

字段名数据类型PRIMARYKEY[默认值]

主键自增长

字段名数据类型AUTO_INCREMENT

外键约束

[CONSTRAINT外键名]FOREIGNKEY字段名[，字段名2，…]

REFERENCES主表名主键列1[，主键列2，…]

唯一约束

字段名数据类型UNIQUE

检查约束

CHECK表达式

默认值

字段名数据类型DEFAULT默认值;

非空约束

字段名数据类型NOTNULL;

查看约束

SHOWCREATETABLE数据表名;

CREATE

建库：

CREATEDATABASE[IFNOTEXISTS]数据库名

[[DEFAULT]CHARACTERSET字符集名]

[[DEFAULT]COLLATE校对规则名];

建表：

CREATETABLE表名([表定义选项])[表选项][分区选项];

DROP

DROPDATABASE[IFEXISTS]数据库名

DROPTABLE[IFEXISTS]表名1[,表名2,表名3]

ALTER

修改库：

ALTERDATABASE[数据库名]{

[DEFAULT]CHARACTERSET字符集名|

[DEFAULT]COLLATE校对规则名}

修改表：

ALTERTABLE表名[修改选项]

字段：

ALTERTABLE表名CHANGE旧字段名新字段名新数据类型；

DML

INSERT

INSERTINTO表名[列名1[,…列名n]]

VALUES(值1)[…,(值n)];

INSERT…SELECT语句向表中插入其他表的数据

UPDATE

UPDATE表名SET字段1=值1[,字段2=值2…][WHERE子句]

[ORDERBY子句][LIMIT子句]

DELETE

DELETEFROM表名[WHERE子句][ORDERBY子句][LIMIT子句]

TRUNCATE

TRUNCATE[TABLE]表名

DQL

基本查询

SELECT

{*|字段列名}

[

FROM表1,表2…

[WHERE表达式

[GROUPBYgroupbydefinition

[HAVINGexpression[{operatorexpression}…]]

[ORDERBYorderbydefinition]

[LIMIT[offset,]rowcount]

]

别名

表名[AS]别名

条件查询

WHERE查询条件

查询条件可以是：

带比较运算符和逻辑运算符的查询条件

带BETWEENAND关键字的查询条件

带ISNULL关键字的查询条件

带IN关键字的查询条件

带LIKE关键字的查询条件

模糊查询

[NOT]LIKE'字符串'

范围查找

[NOT]BETWEEN取值1AND取值2

空值查询

IS[NOT]NULL

分组/聚合

GROUPBY字段名

过滤分组

HAVING查询条件

排序

ORDERBY字段名[ASC|DESC]

去重

SELECTDISTINCT字段名FROM表名;

分页

LIMIT初始位置，记录数

表连接

内连接

SELECT字段名FROM表1INNERJOIN表2[ON子句]

外连接

左外连接

SELECT字段名FROM表1LEFTOUTERJOIN表2ON子句

右外连接

SELECT字段名FROM表1RIGHTOUTERJOIN表2ON子句

交叉连接

SELECT字段名FROM表1CROSSJOIN表2[WHERE子句]

或

SELECT字段名FROM表1,表2[WHERE子句]

并集

子查询

WHERE表达式操作符(子查询)

其中，操作符可以是比较运算符和IN、NOTIN、EXISTS、NOTEXISTS等关键字。

1）IN|NOTIN

当表达式与子查询返回的结果集中的某个值相等时，返回TRUE，否则返回FALSE；若使用关键字NOT，则返回值正好相反。

2）EXISTS|NOTEXISTS

用于判断子查询的结果集是否为空，若子查询的结果集不为空，返回TRUE，否则返回FALSE；若使用关键字NOT，则返回的值正好相反。

正则表达式

运算符

算数运算符

加（+）、减（-）、乘（*）、除（/，DIV）和模运算（%，MOD）

比较运算符

=、/!=、=、、=、、=、BETWEEN、IN、ISNULL、ISNOTNULL、LIKE、REGEXP、RLIKE

逻辑运算符

NOT/!、AND/、OR/||、XOR

位运算符

、|、^、~、、

运算符优先级

常用函数

字符串函数

CONCAT()、INSERT()、LOWER()、UPPER()、LEFT()、RIGHT()、LPAD()、RPAD()、LTRIM()、RTRIM()、REPEAT()、REPLACE()、STRCMP()、TRIM()、SUBSTRING()

数值函数

ABS()、CEIL()、FLOOR()、MOD()、RAND()、ROUND()、TRUNCATE()

日期和时间函数

CURDATE()、CURTIME()、NOW()、UNIX_TIMESTAMP()、FROM_UNIXTIME()、WEEK()、YEAR()、HOUR()、MINUTE()、MONTHNAME()、DATE_FORMAT()、DATE_ADD()、DATEDIFF()

流程函数

IF、IFNULL、CASEWHEN

其他函数

DATABASE()、VERSION()、USER()、INET_ATON()、INET_NTOA()、PASSWORD()、MD5()

DCL

用户管理

创建用户

CREATEUSER用户[IDENTIFIEDBY[PASSWORD]'password'][,用户[IDENTIFIEDBY[PASSWORD]'password']]

修改用户

RENAMEUSER旧用户TO新用户

其中：

旧用户：系统中已经存在的MySQL用户账号。

新用户：新的MySQL用户账号。

删除用户

DROPUSER用户1[,用户2]…

其中，用户用来指定需要删除的用户账号。

查看用户权限

在MySQL中，可以通过查看表中的数据记录来查看相应的用户权限，也可以使用SHOWGRANTS语句查询用户的权限。

mysql数据库下的user表中存储着用户的基本权限，可以使用SELECT语句来查看。SELECT语句的代码如下：

SELECT*;

除了使用SELECT语句之外，还可以使用SHOWGRANTSFOR语句查看权限。其语法格式如下：

SHOWGRANTSFOR'username'@'hostname';

其中，username表示用户名，hostname表示主机名或主机IP。

GRANT

GRANTpriv_type[(column_list)]

TOuser[IDENTIFIEDBY[PASSWORD]'password']

[,user[IDENTIFIEDBY[PASSWORD]'password']]

[WITHwith_option[with_option]]

REVOKE

REVOKEpriv_type[(column_list)]

FROMuser[,user]

密码管理

修改密码

SETPASSWORDFOR'username'@'hostname'=PASSWORD('newpwd');

重置密码

DPL

BEGINTRANSACTION

COMMIT

ROLLBACK

1.4高级特性

MySQL分区

1、什么是分区？

2、分区类型：Range、List、Columns、Hash、Key、子分区

3、分区管理

4、分区与性能

视图

1、什么是视图？

2、视图基本操作：

创建视图

CREATEVIEW视图名ASSELECT语句

修改视图

ALTERVIEW视图名ASSELECT语句

删除视图

DROPVIEW视图名1[,视图名2…]

查看视图

DESCRIBE视图名;

或简写成：

DESC视图名;

存储过程

1、什么是存储过程

2、存储过程基本操作

创建存储过程

CREATEPROCEDURE过程名([过程参数[,…]])过程体[过程参数[,…]]格式

[IN|OUT|INOUT]参数名类型

修改存储过程

ALTERPROCEDURE存储过程名[特征]

删除存储过程

DROPPROCEDURE[IFEXISTS]过程名

查看存储过程

SHOWPROCEDURESTATUSLIKE存储过程名;

游标

声明游标

DECLAREcursor_nameCURSORFORselect_statement;

打开游标

OPENcursor_name;

使用游标

FETCHcursor_nameINTOvar_name[,var_name]

关闭游标

CLOSEcursor_name;

绑定变量

定义变量

DECLAREvar_name[,]type[DEFAULTvalue]

为变量赋值

SETvar_name=expr[,var_name=expr]

自定义函数

CREATEFUNCTIONsp_name([func_parameter[]])

RETURNStype

[characteristic]routine_body

触发器

1、什么是触发器

2、触发器基本操作：

创建触发器

CREATE触发器名BEFORE|AFTER

INSERT|UPDATE|DELETE

ON表名FOREACHRow触发器主体

删除触发器

DROPTRIGGER[IFEXISTS][数据库名]触发器名

查看触发器

SHOWTRIGGERS;

全文索引

SQLMode

1、什么是SQLMode？

2、常见SQLMode

3、数据迁移中使用SQLMode

2.进阶

了解基本SQL操作和高级特性可以基本满足平时开发的需要，但是如果真正从事数据库底层开发，还需要了解这些SQL语句背后的算法，这就涉及索引实现及优化，锁的使用，事务控制，SQL优化。

如果需要掌握一个庞大的数据库系统的操作和运维，还需要掌握主从复制、备份恢复、高可用、基准测试。

2.1索引

基本操作

创建索引

CREATE(UNIQUE)INDEXindexNameONtable_name(column_name);

添加索引

ALTERtabletableNameADD(UNIQUE)INDEXindexName(columnName);

删除索引

DROP(UNIQUE)INDEX[indexName]ONmytable;

显示索引

SHOWINDEXFROMtable_name;

底层数据结构

二叉树

B树

B+树

索引失效

索引设计原则及优化

锁

锁分类

读锁/写锁

表锁/行锁/页锁

共享锁/排它锁

乐观锁/悲观锁

锁与并发控制

锁

无锁：MVCC

死锁

日志

错误日志

二进制日志

查询日志

慢查询日志

备份恢复

备份恢复策略

逻辑备份与恢复

物理备份与恢复

导入导出

导入

SELECTINTOOUTFILE

mysqldump

导出

mysql

mysql-u用户名-p密码要导入的数据库数据()

source

source/home/abc/#导入备份数据库

LOADDATA

LOADDATALOCALINFILE''INTOTABLEmytbl;

mysqlimport

事务

什么是事务？

ACID实现原理

事务控制

分布式事务

SQL优化

SQL优化的基本步骤

常用优化策略

服务器优化

磁盘存储优化

应用优化

架构

主从复制

1、主从复制原理

2、启动参数

3、主从切换

MySQLCluster

高可用

词法解析/语法解析

词语解析

语法解析

语义分析

Lex/yacc

InnoDB存储引擎

体系架构

内存结构

Bufferpool

缓冲池缓存的数据包括PageCache、ChangeBuffer、DataDictionaryCache等，通常MySQL服务器的80%的物理内存会分配给BufferPool。

ChangeBuffer

Changebuffer的主要目的是将对非唯一辅助索引页的操作缓存下来，以此减少辅助索引的随机IO，并达到操作合并的效果。

AdaptiveHashIndex

自适应哈希索引(AHI)查询非常快，一般时间复杂度为O(1)，相比B+树通常要查询3~4次，效率会有很大提升。innodb通过观察索引页上的查询次数，如果发现建立哈希索引可以提升查询效率，则会自动建立哈希索引，称之为自适应哈希索引，不需要人工干预，可以通过innodb_adaptive_hash_index开启，默认开启。

LogBuffer

磁盘结构

段/区/页

表空间

系统表空间

数据字典

Checkpoint

DoubleWrite

Undo

InnoDBFile-Per-Table表空间

redolog

事务

隔离级别

ACID

测试

学习数据库，必要的测试工具还是需要掌握的。比如压力测试、稳定性测试等等。

基准测试

测试工具

3.拓展

学习关系型数据库MySQL，其实还是需要对比着学习一些其他关系型和非关系型数据库，对比它们底层实现原理不同，算法，事务以及应用场景。

3.1Redis

什么是NoSQL？

数据类型

基本操作

持久化

事务

主从复制

集群

3.2MongoDB

索引

事务

3.3HiveSQL

4.推荐书籍

目前我读过质量比较高的MySQL入门和深入学习的书籍有几个，你可以大概参考一下：

编译原理：

龙书，编译器原理的经典之作，但是不需要每个章节都需要深入了解，当作工具书使用就行，大概原理弄懂即可，深究可能很容易糊涂。

词法解析：

这个是市面上不多的讲解词法解析和语法解析的书，介绍的是flex和bison，贴近实际开发，这本书比龙叔要更贴近实践也更易懂。

数据库入门：

网易数据库大牛的著作，讲解比较全面细致，设计基本SQL，索引，锁，备份恢复，主从复制等等。

数据库提升：

这本书讲解的涉及基本语句的实现原理以及应用场景，非常好，可以作为提升。

数据库提升的经典之作，涉及非常知识面非常广泛，包括TPCC测试，主从复制原理，备份恢复，高可用，高并发等等。

存储引擎：

姜承尧老师经典著作，存储引擎底层原理介绍，非常全面详细，对于了解ACID实现原理，MVCC，redo、undolog等等实现原理非常有用，了解存储引擎必备书籍。

索引：

专门讲解索引的原理，优化，讲解的非常好也很实用。

很赞哦!（23）