so5jih

• Mysql基础
• SQL语句

• DDL
• DML
• DQL

• MySQL核心查询

• 排序 聚合 分组
• SQL约束
• 外键约束
• 多表查询
• 合并查询
• 子查询

• MySQL高级应用

• 窗口函数

• 专有窗口函数
• 聚合类窗口函数

• 索引机制
• MySQL视图

什么是数据库

数据库Database是按照数据结构来组织、存储和管理数据的仓库，其本质是一个文件系统，以文件的方式将数据保存在电脑上。

DBMS(数据库管理系统/软件)是一款管理软件，用于对数据库进行统一管理和控制，以保证数据库的安全性和完整性，MySQL就是一种DBMS，我们平常说的xx数据库其实就是xxDBMS。

• Oracle公司的Oracle和MySQL(MySQL已被Oracle收购)

• MySQL是最流行的关系型数据库软件之一，体积小、速度快

• IBM公司的DB2
• Microsoft公司的Access和SQL Server

IOE：IBM小型机、Oracle数据库、EMC易安信存储
去IOE

为什么要使用数据库

数据存储方式的比较：

1. 内存

1. 优点：速度快
2. 缺点：容量有限，不能够永久保存，数据是临时存储的状态

2. 文件(Excel)

1. 优点：数据可以永久保存
2. 缺点：少量数据可以直接打开获取数据，大量数据使用程序IO流操作文件不方便

3. 数据库

1. 优点

1. 海量数据存储，提供不错的查询效率
2. 数据可以永久保存
3. 方便存储和管理数据
4. 使用统一的方式操作数据库

2. 缺点

1. 占用资源(一般会把数据库安装在服务器上)
2. 有些数据库需要付费

数据库分类

• 关系型数据库(RDB：Relationship Database)

• 是创建在关系模型基础上的数据库
• 有明确行和列的二维表格模型
• 例如：MySQL、Oracle、DB2、access、SQL server、PostgreSQL等。

• 非关系型数据库(NoSQL：Not only SQL，常规情况下我们是不能使用SQL进行操作的，它不是关系模型)

• 存储数据通常都是以k，v键值对的形式{name:"jack",age:12}
• 例如：Hbase、redis(k/v)、MongoDB(最像关系型数据库的非关系型数据库)、memcache、CouchDB等。

关系型数据库的主要对象

C/S架构中的mysql客户端有三种方式：

1. 命令行客户端
2. 第三方客户端工具，例如Navicat、SQLyog(小海豚)
3. 代码客户端，例如java、scala、python等语言中使用代码访问MySQL服务器进行操作

操作MySQL数据库流程：创建数据库->创建数据表->在表中记录数据

关系型数据库的主要对象：

1. 表

1. 由行和列组成，用于存储数据

2. 字段

1. 表中每一列称为一个字段，可设置字段类型

3. 索引

1. 索引是一个单独的、物理的数据库结构，它是依赖于表建立，在数据库中使用索引，无需对整张表进行扫描就可以找到需要的数据

4. 视图

1. 视图是从一张或多张表中导出的表(虚拟表)，是用户查看数据表中数据的一种方式

MySQL数据库起源与发展

MySQL最初是由瑞典MySQLAB公司开发的，后来被SUN(Sun Microsystems)公司收购，之后Oracle公司又把SUN公司收购，所以MySQL现在属于Oracle。

MySQL是最好的RDBMS(关系型数据库管理系统)应用软件之一。

MySQL的存储引擎

存储引擎是对于数据库文件的一种存取机制，如何实现存储数据，如何为存储的数据建立索引以及如何更新，查询数据等技术实现的方法。

MySQL5.1版本之前默认的存储引擎是MyIsam，之后默认的存储引擎是InnoDB。

常用存储引擎：

• InnoDB

• 优点：提供了良好的事务处理、崩溃修复能力和并发控制

• 事务处理、回滚
• 自增长
• 外键

• 缺点：占用的数据空间相对较大，读写效率较差

• MyIsam

• 优点：占用空间小，处理速度快
• 缺点：不支持事务的完整性和并发性

• Memory(用的很少见，内存异常会影响数据，生命周期很短，一次性)

• 数据全部放在内存中
• 使用哈希索引

MySQL的数据类型

• 数值类型

• 整数型(INTEGER)

• TINYINT(1个字节)，一个非常小的整数，可以带符号。如果是有符号，存储的数据范围是-128到127；如果是无符号，允许的数据范围是0到255.
• INT(4个字节)，正常大小的整数，可以带符号。如果是有符号，允许范围是-2的31次方到2的31次方-1；如果是无符号，允许范围是0到2的32次方-1
• BIGINT(8个字节)，一个大的整数，可以带符号。

• 小数型

• FLOAT(M,D)，不能使用无符号的单精度浮点，M为显示长度，D为小数位数；例如float(5,2) -> 999.98
• DOUBLE(M,D)，不能使用无符号的双精度浮点，M,D默认为16,4。

• 字符串类型

• 普通类型(一般存储短文本)

• CHAR，固定长度的字符串，例如性别、密码，字符长度在1到255之间，CHAr(5)表示数据占5个字符，如果用CHAr(5)存储字符”ab“，就会造成3个字符的空间浪费。创建CHAR类型字段时，如果不指定长度，默认为1。
• VARCHAR，可变长度的字符串，字符长度在1-255之间。如果用VARCHAr(5)存储字符”ab“，就只会占用2个字符的空间，不会造成浪费(节省内存空间)。创建VARCHAR类型字段时，必须要指定长度。

• 可变类型

• TEXT，超长文本
• BLOB，以二进制保存文本或图片

• 特殊类型

• SET
• ENUM

• 日期时间型

• DATE

• YYYY-MM-DD

• TIME

• HH:MM:SS

• DATETIME

• YYYY-MM-DD HH:MM:SS

• TIMESTAMP

• 区间为1970年1月1日到2037年之间
• 1973年12月30日下午3点30分将被存储为19731230153000(YYYYMMDDHHMMSS)

• TEAR

• 以2位或4位数字格式来存储年份
• 如果长度指定为2，年份就是1970-2069区间；如果长度指定为4，年份就是1901-2155区间

MySQL Server连接

用命令行模式访问Server端：mysql -h127.0.0.1 -uroot -p123456

或者用Navicat连接Server端：

分类

CURD(create、update、retrieve、delete)

SQL语句按照功能分成几类：

• DDL语句(结构性的操作，不涉及具体存储的数据)

• Data Definition Language数据定义语言

• 创建数据库
• 创建表
• 修改表结构

• DML语句

• Data Manipulation Language数据操纵语言

• 针对table数据表中数据的增删改

• DQL语句

• Data Query Language数据查询语言

• 针对table数据表中数据的查

• DCL语句

• Data Control Language数据控制语言

• 事务的提交、回滚等

DDL

DML

DQL

三、MySQL核心查询

排序 聚合 分组

SQL约束

多表查询的外键约束

多表查询

合并查询

子查询

子查询：一条select查询语句的结果作为另一条select语句的一部分
子查询常见分类：

• where型子查询：将子查询的结果作为父查询的比较条件

• select * from products where price=(select max(price) from products);
• select * from products where price<(select avg(price) from products);
• select * from category where cid in (select distinct(category_id) from products where price<2000)

• from型子查询：将子查询的结果当做一张表，提供给父查询使用
• exists型子查询：子查询的结果是单列多行，类似一个数组，父层查询使用in函数，包含子查询的结果

参考：

https://zhuanlan.zhihu.com/p/165308096zhuanlan.zhihu.com

数学函数

字符串函数

日期时间函数

条件判断

三、MySQL高级应用

窗口函数

MySQL从8.0开始支持窗口函数，有的也叫分析函数(处理相对复杂的报表统计分析场景)。
窗口函数也就是在满足某种条件的记录集合上执行的特殊函数，对于每条记录都要在此窗口内执行函数，窗口大小都是固定的，这种属于静态窗口；
不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。

函数名 ([expr]) over(partition by <要分列的组> order by <要排序的列> rows between <数据范围>)

over是关键字，用来指定函数执行的窗口范围，包含三个分析子句：partition by、order by、rows，如果后面括号中什么都不写则表示该窗口包含满足where条件的所有行，窗口函数基于所有行进行计算；如果不为空则支持上面语法进行设置窗口。

计算的行数范围：
rows between 2 preceding and current row # 取当前行和前面两行
rows between unbounded preceding and current row # 包括本行和之前所有的行
rows between current row and unbounded following # 包括本行和之后所有行
rows between 3 preceding and current row # 取当前行和前面3行
rows between 3 preceding and 1 following # 从前面3行到下面一行，总共5行

当order by后面缺少窗口从句条件时，窗口规范默认是rows between unbounded preceding and current row
当order by和窗口从句都缺失，窗口规范默认是rows between unbounded preceding and unbounded following

窗口函数的分类

一般窗口函数分为两种：

• 专有窗口函数

• rank()
• dense_rank()
• row_number()

• 聚合类窗口函数 - 普通情景下，聚合函数往往和group by一起使用，但是窗口环境下，聚合函数也可以应用进来，那么此时它们就被称为聚合类窗口函数，属于窗口函数的一种

• sum()
• count()
• avg()
• max()
• min()

窗口函数(专有窗口函数+聚合类窗口函数)：
每条记录都会执行，有几条纪录执行完还是几条；
窗口按照字段进行分组，窗口函数在不同的分组上分别执行；
窗口函数按照排序后的记录顺序进行编号，可以和partition子句配合使用，也可以单独使用。如果没有partition子句，数据范围则是整个表的数据行。
窗口rows是进行函数分析时要处理的数据范围

普通场景下的聚合函数：
将多条记录聚合为一条(多到一)；

案例

• 累计计算函数应用

需求1：查询出2019年每月的支付总额和当年累积支付总额

需求2：查询出2018-2019年每月的支付总额和当年累积支付总额

需求3：查询出2019年每个月的近三月移动平均支付金额

需求4：查询出每四个月的最大月总支付金额

• 排序函数应用

1. row_number() over() 生成行号
2. rank() over() 跳跃排名
3. dense_rank() over() 连续排名，一般用这个

需求5：2020年1月，购买商品品类数的用户排名

需求6：查询出将2020年2月的支付用户，按照支付金额分成5组后的结果

ntile函数用来排序，将一组数据平均划分多少份。ntile不支持rows between。

需求7：查询出2020年支付金额排名前30%的所有用户（分成10份，取前三份）

• 偏移分析函数应用

1. lag() over()
2. lead() over()

需求8：查询出King和West的时间偏移(前N行)

效果:

需求9：查询出King和West的时间偏移(后N行)

需求10：查询出支付时间间隔超过100天的用户数

需求11：查询出每年支付时间间隔最长的用户

结果为：

索引机制

索引可以提升查询速度，会影响where条件查询和order by的排序索引在大量数据场景下效果明显

常见索引分类：

1、从索引的存储结构划分：B Tree索引、Hash索引、fulltext全文索引、R Tree索引2、从应用层次划分：主键索引、唯一索引、普通索引、复合索引3、从索引键值(字段)类型划分：主键索引、辅助索引(二级索引)4、从索引数据和内容数据逻辑关系划分：聚集索引(聚簇索引)、非聚集索引(非聚簇索引)

• 主键索引primary key：

• 主键是一种唯一性索引，每个表只能有一个主键，用于标识数据表中的每一条记录
• 不能为空
• alter table table_name add primary key column_name

• 唯一索引unique：

• 唯一索引指的是索引列的所有值都只能出现一次，必须唯一
• 很多时候大家是为了避免数据的重复
• 在已有的表上创建索引

• create unique index index_name on table_name(column_name)

• 修改表结构添加索引

• alter table table_name add unique index_name(column_name)

• 普通索引normal index：

• 最常见的索引，作用就是加快对数据的访问速度
• 对数据没有唯一、非空的强制要求
• create table index_name on table_name(column_name)
• 添加多列索引-复合索引

• create table index_name1 on table_name(column_name1,column_name2)

• 删除索引

• alter table table__name drop index index_name

• alter table table__name add index index_name(column__name)

索引优缺点：

• 优点：提升查询速度、减少查询和排序时间
• 缺点：对表中的数据进行增删改时，索引要同时进行维护，数据量越大维护时间越长

MySQL视图

什么是视图：

• 视图是一种虚拟表
• 视图建立在已有表的基础上，这些表为基表(实体表)
• 向视图提供内容的语句为select语句，可以将视图理解为存储起来的select语句
• 视图是 向用户提供基表数据的另一种表现形式

为什么要使用视图：

• 权限控制

• 比如某几个列可以运行用户查询，其他列不允许，可以开通视图查询特定的列，起到权限控制的作用

• 简化复杂的多表查询

• 将一次复杂的查询构建成一张视图
• 视图主要是为了简化多表的查询

需求：查询各个分类下的商品平均价格

需求：查询鞋服分类下最贵的商品的全部信息(包括商品表信息和分类表信息)

通过视图查询

视图与表的区别

• 视图建立在表基础上
• 表存储数据库中的数据，视图只是做一个数据的展示
• 删除视图，表不受影响；删除表，视图不再起作用。

另外mysql数据库命令也可参考如下：