MYSQL入门过渡进阶资料

1.数据和数据库
1.1 什么是数据?
客观世界符号表示(文本,图片,音频…)

1.2 存储数据?
经历阶段 --> 文件存储(txt) 存储量增长(检索速度直线下降)
–> 数据库存储 (数据结构)

1.3 什么是数据库(DataBase: DB)
按照特定数据结构存储数据的仓库

1.4 数据库管理系统(DBMS)
操纵和管理数据库的一套软件

1.5 数据库管理系统分类
1) 关系型的数据库管理系统(RDBMS: relationship)
数据可以通过关系模型(表格)来进行统一的表示存储

	 oracle: oracle公司大型的分布式的关系型数据库管理系统, 稳定
	         产品免费(服务收费)   阿里--> 去ioe化
			 
	 mysql: oracle公司,开源免费
	 
	 sql server: microsoft,中小型
	 
  2)非关系型数据库管理系统(Nosql: not only sql)
     	  
	 redis / HBase / mongdb  / Neo4j	  

2. Mysql数据库管理系统

mysql是开源免费的关系型的数据库管理系统。
通过关系模型实现数据的存储和管理的 – 表(Table)
mysql数据库管理系统可以操纵和管理多个数据库,每个数据库有可以存储多张表。

在GPL(开源软件许可证)条款下发布,数据量支持千万级别。

2.1 下载和安装mysql
http://www.mysql.com
http://www.oracle.com

 默认:
 C:\Program Files\MySQL\MySQL Server 5.7: mysql server软件的安装目录
     ---> bin: 二进制的可执行文件(命令)
  
 C:\ProgramData\MySQL\MySQL Server 5.7: mysql的数据和配置目录
     ---> Data: 存放数据
	      --> 每个目录代表数据库
		  --> 每个目录下的文件代表table
	 ---> my.ini: mysql的配置文件

2.2 登录mysql服务
mysql -uroot -p

 不能连接:
    1.检查服务是否启动   services.msc
	2.检查密码是否正确   错误提示: password:YES

3. sql
   结构化查询语言,关系型的数据库管理系统的通用语言。

3.1 sql分类
根据功能不同

 DDL: 数据定义语言(定义数据库对象结构)
     create  / alter  / drop
 
 DML: 数据操纵语言(数据操作)
     insert / update  / delete
 
 DQL: 数据查询语言
     select
 
 TCL: 事务控制语言
     commit   / rollback
 
 DCL: 数据控制语言(授权)
     grant  / revoke

3.2 DDL语言(数据库对象 -> 数据库,表,视图,索引等)
语法:
1. 每句结束标识是;
2. 对大小写不敏感
3. 给数据库,表等数据库对象起名字时不能使用关键字(sql语言内置的特定含义单词)
4. # – 代表注释(不执行,只是来展示给开发者)
5. 起名字时: 字母数字和下划线,不要数字打头
1. 数据库DDL操作
创建数据库:
create database [ if not exists ] dbname;

	销毁数据库:
	   drop database [if exists] dbname;
 
 2. 表的DDL操作
    table: 行和列
	  行称之为记录,列称为字段
 
    创建表(创建字段):
	  create table t1(
	     sname varchar(20),
		 age int,
		 sex varchar(10)
	  );

---

• 常见命令

  show databases;  显示所有数据库
  use dbname; 指定使用某个数据库
  show tables; 显示该数据库下所有的表
  

• 常用客户端工具

  sqlyog，navicat

2.sql初识

2.1 什么是sql

结构化查询语言(Structured Query Language),是关系型数据的一门通用语言,用于实现对数据库的查询，更新和管理。

2.2 sql的分类

• DDL(Data Definition Language),数据定义语言，它用来定义我们的数据库对象，包括数据库、数据表和列。通过使用 DDL，我们可以创建，删除和修改数据库和表结构。
• DML(Data Manipulation Language),数据操作语言，我们用它操作和数据
  库相关的记录，比如增加、删除、修改数据表中的记录。
• DCL(Data Control Language),数据控制语言，我们用它来定义访问权限和
  安全级别。
• DQL(Data Query Language),数据查询语言，我们用它查询想要的记录，它
  是 SQL 语言的重中之重。在实际的业务中，我们绝大多数情况下都是在和查询打交道，
  因此学会编写正确且高效的查询语句。

2.3 ddl操作

2.3.1 数据库操作

• 创建数据库

  create database [if not exists] dbname
  

• 销毁数据库

  drop database [if exists] dbname
  

2.3.2 数据类型和表操作

数据库中的数据存放在数据库表中，以二维表格的形式存在。

一行代表一条数据记录，称为记录。

一列代表同一域的数据，表示同一属性,称为字段

• 数据类型

  1)	数值类型
  	TINYINT      1
  	SMALLINT    2
  	MEDIUMINT  3
  	INT         4                 -21亿~21亿
  	BIGINT      8
  	float(n,m)    n代表长度，m代表小数点后位数
  	double(n,m)
  	decimal(n,m)
  
  2)	日期类型
  	date:日期
  	time:时间
  	datetime:日期时间
  	timestamp:时间戳
  	year：年
  	
  3)	字符串类型
  	char:定长字符串
  	varchar:可变长字符串
  	
  4)  其他类型	
  	binary：定长二进制
  	varbinary：可变长二进制
  	blob:二进制
  	text:大对象，字符，文本
  	enum:枚举
  	set:集合
  

• 创建表

  create table `user`(
    userid int,
    username varchar(20),
    `password` varchar(20),
    birthday date
  )
  

• 修改表

  A.添加字段
  alter table tname add [column] col_name col_definition [after|first]
  
  eg:   alter table students add birthday datatime
  
      添加列(默认最后一列)
      alter table tname add colname colType
  
      添加到指定列后
      alter table tname add colname2 colType after colname1
  
      添加到首列
      alter table tname add colname colType first;
  
  B.	修改字段
      alter table tname change/modify col_name new_col_name col_definition;
      修改字段名称
      eg:   alter table students change birthday(旧名字) birth(新名字) data;
      alter table tname change colname1 colname2 colType
      修改数据类型(不重命名)
      eg:   alter table students change birthday data;
      alter table tname change colname colname colType
      alter table tname modify colname colType
      修改字段位置
      alter table tname modify colname colType after colname2;
  
  C.	删除字段
  alter table tname drop colname;
  
  D.	重命名(了解)
  rename table tname to newname;
  
  E.	清空表（在删除表中数据时，主键的自增不会停止，如果要重置，可用）
  truncate table tname;
  
  F.重命名(了解)
  alter table tname rename to newName;
  

• 销毁表

  drop table tname
  

2.4 数据完整性

数据完整性指代数据的准确性和可靠性

2.4.1 实体完整性

保证记录是唯一的，不重复的

• 主键约束(primary key)

  主键字段唯一且不能为空

  创建表时声明主键
  create table user(
  userid int primary key,
  username varchar(20)
  );
  create table user(
   userid int,
   username varchar(20),
   primary key(userid)
  );
  
  修改表结构添加约束
  alter table user add CONSTRAINT PK_USERID primary key(userid);
  
  1.每张表必须有且只有一个主键
  2.允许有联合主键
  primary key(sid, cid)
  3.尽量选择非业务字段
  
  销毁:
  alter table user drop primary key;
  

• 唯一约束(unique)

  唯一约束字段值不能重复，但允许为空
  联合唯一：unique(sid, uid)

  创建表时声明唯一约束
  create table user(
  userid int primary key,
  username varchar(20),
  card varchar(18) unique
  );
  
  create table user(
  userid int primary key,
  username varchar(20),
  card varchar(18),
  unique(card) 
  );
  
  表创建完成后添加主键约束
  alter table student add constraint PK_SID primary key(sid)
  
  修改表结构添加唯一约束
  alter table user add CONSTRAINT UN_CAED unique(card);
  
  销毁:
  drop unique index UN_CARD on user;
  

• 主键自增(auto_increment)

  create table user(
  userid int primary key auto_increment,
  username varchar(20),
  card varchar(18)
  );
  

2.4.2 域完整性

保证列的数据正确性

• 类型约束
• 非空约束(not null)
• 默认值(default)

2.4.3 引用完整性

保证表中字段值的有效

• 外键约束(foreign key)

  表中外键字段的取值需要依赖于另张表的主键的取值

  alter table emp add CONSTRAINT FK_DEPTNO foreign key(deptno) REFERENCES dept(deptno);
  alter table emp drop foreign key;
  

3.运算符

3.1 算术运算符

select 1+2;
select 1-2;
select 3*4;
select 3/4;  #0.75
select 3/0;  #null
select 3 div 4; #整，取整
select 3 % 4;  #取余

3.2 比较运算符

表达式为真结果为1,否则为0

select 1>1;
select 1<1;
select 1<>1;
select 1!=1;  select 1 <> 1;

is true,is not true,
is null,is not null,
between and,not between and,
in, not in

3.3 逻辑运算符

表达式为真结果为1,否则为0

select 1=1 and 1=2;
select 1=1 or 1=2;
select !(1<>1);

3.4 位运算符

先将数据转化为二进制，在进行运算

select 1&0; #按位与：一侧为0，结果为0
select 1 | 1; #按位或：一侧为1，结果为1
select 1^2; #按位异或：两侧相同为0，不一样为1

4.DML操作

对表数据的操作,会更改数据，不改变结构
都是在事务环境中实现的

4.1 插入

• 向表中所有字段添加数据

  insert into tname[(col1, col2, ...)] values(col1,col2...)
  value值的个数和顺序必须和表中字段的个数和顺序保持一致
  

• 向表中部分字段添加数据

  前提是不添加值的字段允许为空，但value值的顺序必须和前面字段名称的顺序一致
  insert into tname(col1,col2...) values(val1,val2...)
  

• 批量数据插入

  insert into tname[(col1,col2...)] values(val1,val2...),(val1,val2...)... 
  

• 复制表数据
  将查询得到的数据作为输入，插入到另一张表中

  insert into tname([col1,col2...]) select col1,col2...from tname2
  

4.2 修改

update

update tname set col1=val1,col2=val2... [where...]
如果update没有使用where则代表对整张表所有记录修改

4.3 删除

delete

delete from tname [where...]
不使用where将删除整张表中所有的记录

delete,truncate和drop的区别?
 delete：只删除数据,不修改表结构,不重置自增,逐行删除
 truncate：清空表数据,不修改表结构,重置自增,删除速度比delete高
 drop：清空表结构,表结构文件和数据文件全部删除

注意（插入知识点）：

在终端运行：
mysql -h localhost -u root -p test(自己的数据库名，自行指定，数据库必须已存在) < C:\mysqldata\emp.sql(自己的sql文件存放位置) --default-character-set=utf8
输入mysql的密码即可
或者：
进入.sql文件路径——进入mysql界面——使用use 数据库名 进入要操作的数据库——source 该文件.sql

show variables like ‘%char%’ 展示数据库的编码信息

5.DQL操作

5.1 dql语法

语法：

select cols  #查询并展示的数据(字段,表达式等)
from tables  #查询的数据来源(表,结果集,视图等)
where condition  #条件语句
group by  #分组
having    #分组之后的条件判断
order by  #排序(asc升序   desc降序)
limit     #限制结果查询

5.2 基本查询语句(emp表为例)

• 查询所有字段的数据

  select * from emp
  

• 查询部分字段的数据

  # 查询emp表中empno和ename的值
  select empno,ename from emp
  

• 单一条件查询

  # 查询emp表中薪水>2000的员工信息
  select * from emp where sal > 2000
  

• 组合条件查询

  # 查询薪水>2000的20号部门的员工信息
  select * from emp where sal > 2000 and deptno = 20
  # 查询薪水>2000或者20号部门的员工信息
  select * from emp where sal > 2000 or deptno = 20
  

• 范围查询

  # 查询薪水在1000到2000之间的员工信息
  select * from emp where sal >= 1000 and sal <= 2000
  select * from emp where sal between 1000 and 2000
  

• 集合查询

  # 查询员工编号为7369,7521,7788的员工信息
  select * from emp where empno=7369 or empno=7521 or empno=7788
  select * from emp where empno in(7369,7521,7788)
  

• 别名

  select empno [as] 编号,ename [as] 名称 from emp
  select e.ename,e.deptno from emp e
  

• 去重
  distinct

  # 在emp表中查询所有的职位信息
  select distinct job from emp
  

• 非空验证

  # 查询员工奖金为空的员工信息
  select * from emp where comm is [not] null
  

• 排序

  # 按照薪水升序排序
  select * from emp order by sal [asc]
  # 按照薪水升序排序,如果薪水一样则按照编号降序排序
  select * from emp order by sal [asc],empno desc 
  

• 模糊查询

  % 代表匹配0到多个字符
  _ 代表匹配1位字符
  
  #查询以a打头的员工信息
  select * from emp where ename like 'a%';
  #查询以n结尾的员工信息
  select * from user where ename like '%n';
  #查询包含l的员工信息
  select * from user where ename like '%l%';
  #查询第二个字为l的员工信息
  select * from user where ename like '_l%'
  

• 限制结果查询
  需要放在最后

  limit offset,length: 
     offset指代起始位置,从0开始
     length指代获取的长度
     
  # 查询前五条记录
  select * from emp limit 0,5;
  select * from emp limit 5;
  常用于分页查询
  
  # 计算时间类型数据（比如计算年龄）
  curdate()、now()、timestampdiff(type,begin_date,end_date)
  
  select CONCAT(TIMESTAMPdiff(MONTH,'2018-01-10',now()) div 12,'年零',TIMESTAMPdiff(MONTH,'2018-01-10',now()) % 12,'月零',TIMESTAMPdiff(day,'2018-01-10',now()) % 30,'天');
  

---

1.函数

1.1 单行函数

• 数学函数

  # 绝对值
  abs()
  # 向上取整,最接近并且大于等于该值的整数值
  ceiling()/ceil()
  # 向下取整,最接近并且小于等于该值的整数值
  floor()
  # 取模运算
  mode(m,n)
  # 求PI值
  PI()
  # 求幂运算
  pow(m,n)
  # 随机数
  rand()     [0,1)
  # 四舍五入
  round(m,n)
  # 截取m小数点后n位
  truncate(m,n)
  

• 字符函数

  # 获取str的ascii码值
  ascii(str)
  # 将字符串转换为小写
  lower(字段|表达式)
  # 将字符串转换为大写
  upper(字段|表达式)
  # 拼接字符串
  concat(str1,str2…)
  # 获取字符串长度
  length(字段|表达式)
  # 截取字符串,pos开始位置，从1开始；len表示长度
  substr(str,pos,len)
  # 在str中搜索old，使用new代替
  replace(str,old,new)
  # str长度不够len，使用s左侧填充
  lpad(str,len,s)
  # str长度不够len,使用s右侧填充
  rpad(str,len,s)
  # 去重左右两侧的空格
  trim()
  

• 日期函数

  # 获取当前日期时间
  NOW()/SYSDATE()/CURRENT_TIMESTAMP()
  # 获取当前系统日期
  CURRENT_DATE()/CURDATE()
  # 获取当前系统时间
  CURRENT_TIME()/CURTIME()
  # 获取天数
  DAY(date)
  # 获取月份
  MONTH(date)
  # 获取年份
  YEAR(date)
  # 返回一年中的周数
  week(date)
  # 返回一周中的第几天(0-6)
  weekday(date)
  # 日期计算
  DATE_ADD(date,INTERVAL expr unit)
  #返回该月最后一天的日期
  last_day(date)
  

1.2 聚合函数

# 平均值
avg()
# 获取总数
sum()
# 获取最大值
max()
# 获取最小值
min()
# 统计数目
count()
统计记录数或者统计某个非空字段的个数
select count(*) from emp;

1.3 分组函数

分组之后，默认只能拿到分组依据，即分组的字段

GROUP BY
 	用于将信息划分为更小的组
 	每一组行返回针对该组的单个结果
HAVING子句
 	用于指定 GROUP BY 子句检索行的条件
#查询平均工资大于2000的部门编号
select deptno,avg(sal) avg from emp group by deptno having avg>=2000

1.4 加密函数

select md5('root');
select sha('root');
select password('root');

1.5 流程函数

如果expr1是真, 返回expr2, 否则返回expr3
IF(expr1,expr2,expr3)	

如果expr1不是NULL,返回expr1,否则返回expr2
IFNULL(expr1,expr2)	

如果value是真, 返回result1,否则返回default（多分支）
这里的条件判断可以有包含全部逻辑符号
CASE WHEN [value1] THEN[result1]… ELSE[default] END	
select 
case 
	when sex=1 then '男'
	when sex=2 then '女'
	else '人妖'
end
from emp

如果expr等于value1, 返回result1,否则返回default
这里的条件判断只能是等号
CASE [expr] WHEN [value1] THEN[result1]… ELSE[default] END	
select
case sex
	when 1 then '男'
	when 2 then '女'
	else '人妖'
end
from emp

补充：
顺序：where——group by——having
是否能使用聚合函数：having 可以使用

2.高级查询

2.1 连接查询

• 笛卡尔积

  多张表中记录组合所有可能有序对集合

• 内连接

  多张表均匹配的记录连接并展示在结果集

  无主从表之分,与连接顺序无关

  # 查询所有雇员及其部门信息
  select * from emp,dept where emp.deptno = dept.deptno;
  
  select * from emp inner join dept on emp.deptno = dept.deptno;
  
  select * from emp inner join dept using(deptno); #字段名称必须一致，必须等值连接（不通用）,会自动去除重复列
  

• 自然连接

  自然连接都是等值连接

  表中字段名称相等的字段进行连接，会自动去重重复列

  select * from emp natural join dept;
  

• 外连接

  以驱动表为依据,依次在匹配表中进行记录匹配,如果匹配则连接并展示在结果集中,否则以null填充

  有主从表之分，与连接顺序有关。

  left/right [outer] join … on…

  select * from emp 
  LEFT JOIN dept
  on emp.deptno = dept.deptno
  

• 自连接

  以本身为镜像进行连接(自身连接自身)

  # 查询员工及其领导的名称
  select e1.ename 名称,e2.ename 领导
  from emp e1,emp e2
  where e1.mgr = e2.empno
  

2.2 子查询

嵌套查询，将一个查询结果作为另一个查询条件或组成部分的查询

# 单行子查询（子查询返回的结果只有一条）
  #查询工资大于7788号员工的所有员工信息
  select * from emp where sal >(select sal from emp where empno =7788);
# 多行子查询（子查询返回的结果只有多条记录）
  返回多值可以使用any或all来修饰。
  =any相当于in,<any小于最大值，>any大于最小值；
  <>all相当于not in,>all大于最大值，<all小于最小值。
  
# 查询超过所在部门平均工资的员工信息
select * from emp e1 where sal > 
(select avg(sal) from emp e2 where e1.deptno = e2.deptno);

#查询薪水大于2000的部门名称
select dname from dept d where deptno in
(select deptno from emp e where sal > 2000);
    或
select dname from dept d where EXISTS 
(select * from emp e where sal > 2000 and d.deptno=e.deptno);
# in和exists的区别
in先执行子查询，再执行主查询，字段需要一一对应，子查询返回具体结果集;
exists先执行主查询，exists子查询不返回具体结果，返回true值出现在结果集，否则不出现。

2.3 联合查询

union: 并集，所有的内容都查询，重复的显示一次
union all: 并集，所有的内容都显示，包括重复的

select * from emp where deptno = 20
UNION ALL/UNION
select * from emp where sal <=2000

---

1.数据库设计

数据库的设计是基础,数据库设计的目标是为用户和各种应用系统提供一个基础的信息设施和高效的运行环境。

1.1 数据库设计三个范式

• 1NF: 所有的域是原子性的

  表中的每一列应该是不可拆分的最小单元。最低要求。

• 2NF: 所有的非主键字段必须与主键相关,不能与部分主键相关(联合主键)

  每张表中描述和表示一类数据,多种不同的数据需要拆分到多张表中。一张表只能存储一种类型数据。

• 3NF: 所有非主键字段必须与主键直接相关,而不能依赖于其他非主键字段

  数据不能具有传递依赖,每个字段与主键的关系是直接的而非间接的。

1.2 E-R模型

Entity Relationship Model: 实体关系模型

提供了表示实体类型、属性和联系的方法，用来描述现实世界的概念模型。

1)表之间的关系

• 一对一: 表A的一条记录对应表B的一条记录,反之亦然(个人信息表和档案表)

  实现:

  ​ 1) 外键添加唯一约束

  ​ 2)主键做外键

• 一对多: 表A的一条记录对应表B的多条记录。(部门表和雇员表)

  实现:

  ​ 添加外键

• 多对多: 表A的一条记录可以对应表B的多条记录,表B的一条记录也可以对应表A的多条记录。

  实现:

  ​ 1)添加关系表

  ​ 2)设置联合主键（为了保证数据唯一）

2)实体关系模型图(ER)

矩形框：表示实体，在框中记入实体名。

菱形框：表示联系，在框中记入联系名。

椭圆形框：表示实体或联系的属性，将属性名记入框中。对于主属性名，则在其名称下划一下划线。

连线：实体与属性之间；实体与联系之间；联系与属性之间用直线相连，并在直线上标注联系的类型。

• 示例

3)常用的数据库设计工具

powerdesinger   

2.商城3C产品数据分析案例

• 设计实体及其关系

• 数据分析案例

  • 统计各个城市销售额的前10名

    1) 创建城市维度表: 包含区域信息,省份信息和城市信息
      create table china_orgin_dim(
    	   d_id int,
    	   d_name varchar(40), # 区域信息
    	   p_id int,
    	   p_name varchar(40), # 省份信息
    	   c_id int,
    	   c_name varchar(40)  # 城市信息
      );
    2) 向城市维度表插入数据
      select distinct length(c_id) from china; 
        c_id为3表示区域,6代表省份,9代表城市,12代表区。
      
     insert into china_orgin_dim
     select c.*,c3.c_id c_id,c3.c_name c_name from china c3, 
     (select c1.c_id d_id,c1.c_name dname,c2.c_id p_id,c2.c_name p_name from china c2,
     (select * from china where superior_c_id = 0) c1
     where c2.superior_c_id = c1.c_id) c
     where c3.superior_c_id = c.p_id;	
    
    3)查看各个城市的销售额
     set @rank=0;   #设置变量0
     select p_name,c_name,sales,
     case 
       when length(sales_e) >8 then concat(round(sales_e/100000000,2),'亿')
       when length(sales_e) >7 then concat(round(sales_e/10000000,2),'千万')
     end sales_e,@rank:=@rank+1 rank from	 
     (select p_name,c_name,sum(amount) sales,
     sum(price*amount) sales_e from product
     inner join order_detail on product.pid = order_detail.pid
     inner join users on users.user_no = order_detail.user_no
     inner join china_orgin_dim on users.c_id = china_orgin_dim.c_id
     group by p_name,c_name order by sales_e desc limit 10) sale;
    

• 统计不同年龄层次的消费及其占比情况

  1)创建年龄分级表
   create table age_level(
    grade  int primary key auto_increment,
    lowage int not null,
    hiage  int not null
   );
   insert into AGE_LEVEL(lowage, hiage) 
     values (10, 20),(20, 30),(30, 40),(40, 50),(50, 60);
  2)查看比例
  select concat(lowage,'-',hiage),sum(amount) sales,
  case 
     when length(sum(price*amount)) >8 then concat(round(sum(price*amount)/100000000,2),'亿')
  	 when length(sum(price*amount)) >7 then concat(round(sum(price*amount)/10000000,2),'千万')
   end sales_e,
  concat(round(sum(price*amount)/(select sum(price*amount) from order_detail
   inner join product on order_detail.pid = product.pid)*100,2),'%')
  from order_detail
  inner join users on order_detail.user_no = users.user_no
  inner join product on order_detail.pid = product.pid
  inner join age_level on age >= lowage and age < hiage
  group by grade,lowage,hiage;	 
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SlouP7kT-1597586680406)(img\各年龄消费比例.png)]

• 统计2018年度月销售量及环比变化比例

  select thisMonth.period as Period,
    thisMonth.amount as thisMonthAmount,lastmonth.amount as lastMonthAmount,
    concat(round((thisMonth.amount-lastmonth.amount)*100/lastmonth.amount,2),'%') as Rate 
  from
      (select extract(YEAR_MONTH from order_date) as period,sum(amount) as amount
      from order_detail
      group by period) as thisMonth
      left join
      (select extract(YEAR_MONTH from (DATE_ADD(order_date,INTERVAL 1 MONTH))) as period,sum(amount) as amount
      from order_detail
      group by period) as lastMonth
      on thisMonth.period=lastMonth.period
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7aEFDAYm-1597586680410)(img\销售环比变化.png)]

---

1.事务

1.1 存储引擎

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。Mysql的核心就是存储引擎。

• 常见存储引擎

  • InnoDB是事务型数据库的首选，执行安全性数据库，行锁定和外键。mysql5.5之后默认使用。
  • MyISAM插入速度和查询效率较高，但不支持事务。
  • MEMORY将表中的数据存储在内存中，速度较快。

• 查看存储引擎

  show engines;
  

• 修改存储引擎: my.ini

  default-storage-engine=INNODB
  

1.2 事务

事务用于保证数据的一致性，由一组DML操作组成，该组SQL语句要么同时成功，要么同时失败。例如转账。

• 事务的四个特性

  • A(Atomicity)原子性:事务是原子工作单元，要么同时执行，要么同时不执行。
  • C(Consistency)一致性:符合约束规则；执行之前的整体状态和执行后数据一致。
  • I(Isolation)隔离性:并发事务之间相互不影响。
  • D(Durability)持久性:事务完成之后，对数据库的影响是永久的。

• 如何使用事务

  # mysql默认开启自动事务提交,将每个dml操作当做一个事务。
  # 如果需要将多个dml操作放在同一事务,需要关闭自动事务提交
  set Autocommit=0;
  
  //开启事务
  start TRANSACTION;
  #一组dml操作
  update user set money = money -100 where userid = 1;
  update user set money = money + 100 where userid = 2;
  ...
  
  commit;//提交事务(数据持久化到底层文件中)
  rollback;//回滚事务(数据恢复到开启事务之前的状态)
  

• 并发事务的存在问题

  • 脏读

    一个事务处理过程里读取了另一个未提交的事务中的数据

    当一个事务正在多次修改某个数据，而在这个事务中这多次的修改都还未提交，这时一个并发的事务来访问该数据，就会造成两个事务得到的数据不一致。例如：用户A向用户B转账100元，对应SQL命令如下

    update account set money=money+100 where name=’B’;  (此时A通知B)
    update account set money=money - 100 where name=’A’;
    

    当只执行第一条SQL时，A通知B查看账户，B发现确实钱已到账（此时即发生了脏读），而之后无论第二条SQL是否执行，只要该事务不提交，则所有操作都将回滚，那么当B以后再次查看账户时就会发现钱其实并没有转。

  • 不可重复读

    在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。

    例如事务T1在读取某一数据，而事务T2立马修改了这个数据并且提交事务给数据库，事务T1再次读取该数据就得到了不同的结果，发生了不可重复读。

  • 虚读(幻读)

    事务非独立执行时发生的一种现象。例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。

    幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。

• 事务的隔离级别

  • Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。
  • Repeatable read (可重复读)：可避免脏读、不可重复读的发生。
  • mysqlRead committed (读已提交)：可避免脏读的发生。
  • Read uncommitted (读未提交)：最低级别，任何情况都无法保证。

  以上四种隔离级别最高的是Serializable级别，最低的是Read uncommitted级别，当然级别越高，执行效率就越低。像Serializable这样的级别，就是以锁表的方式(类似于Java多线程中的锁)使得其他的线程只能在锁外等待，所以平时选用何种隔离级别应该根据实际情况。在MySQL数据库中默认的隔离级别为Repeatable read (可重复读)。

  在MySQL数据库中，支持上面四种隔离级别，默认的为Repeatable read (可重复读)；而在Oracle数据库中，只支持Serializable (串行化)级别和Read committed (读已提交)这两种级别，其中默认的为Read committed级别。

• 查看事务隔离级别

  select @@tx_isolation;
  

• 修改事务隔离级别

  set  [glogal | session]  transaction isolation level 隔离级别名称;
  或
  set tx_isolation=’隔离级别名称;’
  

2.存储过程

>    一组存储和执行在数据库服务器端的程序,在服务器的进程或者线程的内存中执行的  
>
>    包含输入输出参数实现数据的传入和传出,内部执行一组sql命令

• 参数的三种模式

  • in: 输入模式(默认),将值传入该存储程序
  • out: 输出模式,从程序中将值传出
  • inout: 输入输出模式

• 存储过程创建和调用

  • 不带参数

  # 创建
  delimiter //; #修改结束标识符为//(默认mysql的结束标识为;,但是在存储程序中需要多次用到;,不能代表结束)
  create procedure sel_user()
  BEGIN
    select username from user;
  end //;
  #调用
  call sel_user();
  

  • 带参数

  # 创建
  delimiter //;
  create PROCEDURE sel_user_id(uid int)
  BEGIN
   select * from user where userid = uid;
  end //;
  
  #调用
  call sel_user_id(1);
  
  #创建
  delimiter //;
  create PROCEDURE sel_user_id( INOUT count int)
  BEGIN
  select sal into count from emp where empno = count;
  end //;
  
  #调用
  set @count = 7788;
  call sel_user_id(@count);
  select @count;
  

  • 变量的创建和修改

  # 创建
  delimiter //;
  create PROCEDURE sel_user()
  BEGIN
   -- 声明变量
   DECLARE v_name varchar(20);
   -- 设置变量值
   set v_name = 'zs';
   select v_name;
  end //;
  
  # 调用
  call sel_user();
  

  • 流程控制

  # 选择结构
  
  delimiter //;
  CREATE PROCEDURE test(IN v_count int,OUT v_name VARCHAR(20))
  BEGIN
   IF v_count > 10 THEN
     set v_name = 'zs';
   ELSEIF v_count > 5 THEN
     set v_name = 'ls';
   ELSE
     set v_name = 'ww';
   END IF;
  END //;
  # 调用存储过程
  call test(12,@name);
  select @name;
  
  # 循环结构
  # while..do..end while/loop…end loop/repeat … util..end repeat
  
  -- 使用while来计算1....10的和
  delimiter //;
  create PROCEDURE my_sum(n int,out m_sum int)
  BEGIN
  	DECLARE i int;
  	set i=0;
  	set m_sum=0;
  	-- while 和do中间加条件
  	while i<=n DO
  		set m_sum=m_sum+i;
  		set i=i+1;
  	end while;
  end //;
  
  call my_sum(10,@m_sum);
  select @m_sum;
  
  -- 使用loop来计算1....10的和
  delimiter //;
  create PROCEDURE my_sum1(n int,out m_sum1 int)
  BEGIN
  	DECLARE i int;
  	set i=0;
  	set m_sum1=0;
  	-- loop需加标记 方便leave时指定
  	lip:loop
  	
  		set m_sum1=m_sum1+i;
  		set i=i+1;
  		if i>n THEN leave lip;
  		end if;
  	end loop lip;
  end //;
  
  call my_sum1(10,@m_sum1);
  select @m_sum1;
  
  
  
  -- 使用repeat来计算1....10的和
  delimiter //;
  create PROCEDURE my_sum2(n int,out m_sum2 int)
  BEGIN
  	DECLARE i int;
  	set i=0;
  	set m_sum2=0;
  	-- loop需加标记 方便leave时指定
  	REPEAT
  		set m_sum2=m_sum2+i;
  		set i=i+1;
  		UNTIL i>n
  	end REPEAT;
  end //;
  call my_sum2(10,@m_sum2);
  select @m_sum2;
  
  

---

百万级数据查询优化

1.MySQL性能分析

1.1 explain性能解释器

explain select * from userinfo where id = 10

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lRYqXngO-1597592468614)(img\性能指标.png)]

1.2 profiling执行开销

查询会话中sql执行的开销(时间,内存,io等)

#查看profiling状态
show variables like '%profiling%';

#开启profiling
set profiling = on;

#执行查询

#查看开销
show profiles;

# 查看指定查询的开销
show profile cpu,memory for query 120;

2.存储引擎和索引

2.1 mysql数据库架构

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-z7Wc2Sgy-1597592468616)(img\mysql架构图.png)]

• mysql的执行流程图

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nB7Sx3jZ-1597592468617)(img\sql执行流程.png)]

• 存储引擎

  数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。Mysql的核心就是存储引擎。

  mysql底层设计采用可插拔是的存储引擎,用户可以根据需求，选择或自定义存储引擎。

  mysql5.5之后默认存储引擎为Innodb.

  • 查看mysql支持的存储引擎

    show engines
    

  • 各存储引擎对比

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GWE6dnhh-1597592468620)(img\存储引擎.png)]

  • 设置和查看存储引擎

    设置存储引擎:
     default-storage-engine=INNODB
    查看表的基本信息:
     show table status from mydb where name='user' \G
    

2.2 索引

索引是为了提升数据库查询效率创建的数据结构

2.2.1 索引的添加

​ 查看索引

show index from userinfo

• 普通索引

  1.直接创建索引

  CREATE INDEX index_name ON table(column(length))
  

  2.创建表的同时创建索引

  CREATE TABLE `table` (
      `id` int(11) NOT NULL AUTO_INCREMENT ,
      `title` char(255) CHARACTER NOT NULL ,
      `content` text CHARACTER NULL ,
      `time` int(10) NULL DEFAULT NULL ,
      PRIMARY KEY (`id`),
      INDEX index_name (title(length))
  )
  

  3.删除索引

  DROP INDEX index_name ON table
  

• 唯一索引

  索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

  1.直接创建

  CREATE UNIQUE INDEX indexName ON table(column(length))
  

  2.创建表同时创建

  CREATE TABLE `table` (
      `id` int(11) NOT NULL AUTO_INCREMENT ,
      `title` char(255) CHARACTER NOT NULL ,
      `content` text CHARACTER NULL ,
      `time` int(10) NULL DEFAULT NULL ,
      UNIQUE indexName (title(length))
  );
  

  3.删除索引

  alter table stu drop index INDEX_NAME;
  

• 主键索引

  一种特殊的唯一索引 primary key

  alter table stu add constraint PK_SCORE primary key(score)
  

• 组合索引

  指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用

  CREATE INDEX index_name ON table(col1,col2,col3...)
  

2.2.2 InnoDB的索引模型

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组
织表。InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+树中的 。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vuHmYMEX-1597592468622)(img\innodb.png)]

1. 索引类型分为主键索引和非主键索引。
   主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引
   （clustered index）。
   非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引
   （secondary index）。

2. 根据主键查找和非主键索引查找的区别

   根据主键查询会直接查询该B+树；非主键索引查找将会先找到主键,然后再根据主键索引查找。

   2.2.3 索引添加原则

3. 对表进行增删改操作会涉及到索引的维护,因此尽量选择增删改较少的表实现索引的添加。

4. 有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高 。

5. 主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小

6. 尽量使用主键查询

2.2.4 索引失效的状况

• 索引列使用函数或者参与运算

  select * from userinfo where substr(username,1,2) = '张三';
  
  select * from userinfo where id * 10 = 100; 
  

• 索引列使用前导模糊查询

  select * from userinfo where username like '%三'
  select * from userinfo where username like '%三%'
  

• 索引列存储或者通过null等查询

  select * from userinfo where username is null;
  

• 条件中有or,部分条件带有索引走全表扫描

  select * from userinfo where id = 10 or username = '张三';
  

2.2.5 InnoDB和MyISAM的区别

• InnoDB

  1. 数据存储形式: 将数据表文件分为.frm(表结构)文件和.ibd(表数据和索引)文件进行存储。

  2. 事务: InnoDB是事务型的存储引擎,支持热备,对数据完整性要求比较高时选择有优势。

  3. 锁的粒度: 采用MVVC(多版本并发)支持高并发,支持四种事务隔离级别;锁的粒度是行锁。

  4. 存储特点: InnoDB表是基于聚簇索引建立，对主键的查询有很高的性能。

  5. 应用场景:

     1.更新和查询都相当的频繁，多重并发
     2.要求事务，或者可靠性要求比较高
     3.外键约束，MySQL支持外键的存储引擎只有InnoDB
     一般来说，如果需要事务支持，并且有较高的并发读取频率，InnoDB是不错的选择

• MyISAM

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ISfeffsn-1597592468623)(img\myisam.png)]

  1. 数据存储形式: 将表数据和索引分离,分为.frm,.MYD,.MYI存储机构,数据和索引

  2. 事务: 不支持

  3. 存储特点: 非聚簇索引

  4. 其他: MyISAM支持全文索引，压缩，空间函数，延迟更新索引键等

  5. 应用场景

     1.做很多count的计算
     2.查询非常频繁

3.表分区

当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。

3.1 分区类型

• range分区: 给定数据范围进行分区
• list分区: 枚举出来具体的数据分区
• hash分区: 根据字段取hash值分散存储
• key分区: 内置的hash函数
• 子分区: 再次进行分区,复合分区

3.2 range分区

# 创建表的同时创建分区
create table tname(col...)
partition by range columns(分割字段)(
  partition p0 values less than (value1),
  partition p1 values less than (value1), 
    ...
  partition p0 values less than MAXVALUE
)

# 修改表分区
alter table tname partition by range columns(分割字段)(
  partition p0 values less than (value1),
  partition p1 values less than (value1), 
    ...
  partition p0 values less than MAXVALUE
)

3.3 hash分区

利用hash函数对字段值进行计算,最后分散到不同的分区中

# 普通分区
alter table tname partition by hash(col)
partitions num;

平均分散: mod 4  /  mod 5

# 线性分区
alter table tname partition by linear hash(col)
partitions num;

3.4 合并分区

ALTER TABLE userinfo REORGANIZE PARTITION p0,p1 INTO (
   PARTITION p3 VALUES LESS THAN MAXVALUE
 );

4.设计优化原则总结

• 数据库设计是基础，数据库优化是建立在设计基础之上的。好的数据库一定拥有好的设计
• 主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小
• 尽可能减少select *的使用,尽量使用列名
• 数据量较多时使用limit能够减轻系统的压力
• 查询时条件尽量走索引查询
• 尽量使用多表连接（join）查询代替和避免子查询