2.1数据库-MySQL

关系型数据库	描述
MySQL	开源免费的数据库,小型的数据库,已经被 Oracle 收购了。MySQL6.x 版本也开始收费。
Oracle	收费的大型数据库，Oracle 公司的产品
DB2	IBM 公司的数据库产品,收费的。常应用在银行系统中。
SQL Server	MicroSoft 公司收费的中型的数据库。C#、.net 等语言常使用。
SQLite	嵌入式的小型数据库，应用在手机端，如：Android。
OceanBase	阿里的大型数据库,性能已经超过Oracle全球第一
非关系型数据库(NoSQL)	描述
MongoDB	MongoDB最流行的文档型数据库
Readis	基于内存的键值型数据库通过 Key-Value 键值的方式来存储数据

待更新--------

2.1.1.4

待更新内容：候选码、主码主键、外键。等数据库原理的基础概念

2.1.2 SQL

2.1.2.1 什么是SQL

SQL （发音sequel）:结构化查询语言(Structured Query Language)。SQL语句就是对数据库进行操作的一种语言。

SQL是一套标准，所有的数据库厂商都实现了此标准；但是各自厂商在此标准上增加了特有的语句，这部分内容我们称为方言。

2.1.2.1.1 数据库分类：

早期比较流行的数据库模型有三种

层次式数据库
网络式数据库
关系型数据库

层次式数据库和网络式数据库如今已经消亡，而在当今的互联网中，最常用的数据库模型主要是两种，即

关系型数据库
NOSQL 数据库（和层次式数据库、网络式数据库不同）

2.1.2.1 .2 为什么选择MySQL？

从公司运营角度来说，选择MySQL有多种原因，主要就是Oracle太贵，MySQL一开始并不是功能强大的数据库，但随着阿里巴巴将数据从Oracle数据库迁移到MySQL后，国内的社区和互联网公司就迅速开始对MySQL的应用和技术升级。到现在为止，国内公司使用MySQL主要从数据安全，稳定，技术服务，以及非常重要的价格上综合考虑成本问题，最终大多选择了MySQL。

从技术角度来看，MySQL 由于开源，可以带来两大优势：

可以更加了解软件运作的原理，更好的设置MySQL。一旦出了故障也可以准确定位。

可以更容易开发周边产品。

所以MySQL对于初创公司、互联网公司和很多传统行业不管从功能还是性能，又或者从成本角度考虑都是一个很好的选择。

2.1.2.1.3 数据库引擎

数据库引擎InnoDB与MyISAM

虽然数据库引擎非常多，但是现在MySQL默认使用的引擎是 InnoDB，在5.5之前的版本默认的引擎为MyISAM。所以就以这两款引擎做一点对比：

1、存储结构

MyISAM：每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。

InnoDB：所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。

2、存储空间

MyISAM：可被压缩，存储空间较小。支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。

InnoDB：需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

3、可移植性、备份及恢复

MyISAM：数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。

InnoDB：免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。

4、事务支持

MyISAM：强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持。

InnoDB：提供事务支持事务，外部键等高级数据库功能。具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。

5、 AUTO_INCREMENT

MyISAM：可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。

InnoDB：InnoDB中必须包含只有该字段的索引。引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。

6、表锁差异

MyISAM：只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。

InnoDB：支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。

7、全文索引

MyISAM：支持 FULLTEXT类型的全文索引

InnoDB：不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。

8、表主键

MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。

InnoDB：如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。

9、表的具体行数

MyISAM：保存有表的总行数，如果select count() from table;会直接取出出该值。

InnoDB：没有保存表的总行数，如果使用select count() from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后，myisam和innodb处理的方式都一样。

10、 CURD操作

MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。

InnoDB：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。DELETE 从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令。

11、外键

MyISAM：不支持

InnoDB：支持

通过上述的分析，基本上可以考虑使用InnoDB来替代MyISAM引擎了，原因是InnoDB自身很多良好的特点，比如事务支持、存储过程、视图、行级锁定等等，在并发很多的情况下，相信InnoDB的表现肯定要比MyISAM强很多。另外，任何一种表都不是万能的，只用恰当的针对业务类型来选择合适的表类型，才能最大的发挥MySQL的性能优势。如果不是很复杂的Web应用，非关键应用，还是可以继续考虑MyISAM的，这个具体情况可以自己斟酌。

2.1.2.1.4 MySQL数据类型

整数类型

取值范围如果加了unsigned，则最大值翻倍，如tinyint unsigned的取值范围为(0~255)。

int(m)里的m是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围，忽略就行。

浮点数类型

字符串类型

*char 和 varchar：

char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格，varchar不限于此。
char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，所以varchar(4),存入3个字符将占用4个字节。
char类型的字符串检索速度要比varchar类型的快。

varchar 和 text ：

varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，text是实际字符数+2个字节。
text类型不能有默认值。
varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text,在都创建索引的情况下，text的索引似乎不起作用。

另外还有，日期类型：Date、DateTime、TimeStamp、Time、Year，其他数据类型：BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等。

2.1.2.1.5 选择数据类型的基本原则

前提：使用适合存储引擎。

选择原则：根据选定的存储引擎，确定如何选择合适的数据类型。

MySQL 从5.5 开始，默认引擎为InnoDB，所以数据类型选择都建议以InnoDB为主

下面的选择方法按存储引擎分类：

MyISAM 数据存储引擎和数据列：MyISAM数据表，最好使用固定长度(CHAR)的数据列代替可变长度(VARCHAR)的数据列。

MEMORY存储引擎和数据列：MEMORY数据表目前都使用固定长度的数据行存储，因此无论使用CHAR或VARCHAR列都没有关系。两者都是作为CHAR类型处理的。

InnoDB 存储引擎和数据列：建议使用 VARCHAR类型。

对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在本质上，使用固定长度的CHAR列不一定比使用可变长度VARCHAR列简单。因而，主要的性能因素是数据行使用的存储总量。由于CHAR平均占用的空间多于VARCHAR，因此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I/O是比较好的

注意事项：空字符串和 null

空字符串是有字符串内容，但是长度为0

null 则表示列中没有数据

创建表时,不允许某列为空可以使用 not null 命令

2.1.2.2 SQL语句的分类

分类	说明
DDL(Data Definition Language)数据定义语言	定义数据库对象:数据库,表,列等.关键字:create, drop,alter 等
DML(Data Manipulation Language)数据操作语言	对数据库中表的数据进行增删改.关键字:insert, delete, update 等
DQL(Data Query Language)数据查询语言	用来查询数据库中表的记录(数据).关键字:select, where 等
DCL(Data Control Language)数据控制语言	用来定义数据库的访问权限和安全级别,及创建用户.关键字：GRANT,REVOKE 等
TCL(Transaction Control Language) 事务控制语言	用于控制数据库的事务操作，关键字;commit,savepoint,rollback等

2.1.2.3 SQL基础语句

2.1.2.3.1 SQL基础语句

创建数据库	create database 数据库名称;
创建数据库，并指定字符集	create database 数据库名称 character set 字符集名;
查询所有数据库的名称	show databases;
查询某个数据库的字符集:查询某个数据库的创建语句	show create database 数据库名称;
修改数据库的字符集	alter database 数据库名称 character set 字符集名称;
删除数据库	drop database 数据库名称;
查询当前正在使用的数据库名称	select database();
创建表
创建表	create table 表名( 列名（字段名）数据类型, 列名（字段名）数据类型, 列名（字段名）数据类型 ... );
查询某个数据库中所有的表名称	show tables;
查询表结构	desc 表名；
修改表结构	alter 表名列名1...；
修改列	alter 表名...modify column...
删除表	drop table 表名;
添加数据	insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);
删除数据	delete from 表名 [where 条件]
清空表	truncate是DDL语句，而且delete是DML语句 truncate比使用delete一行行删除数据要快得多，特别是清空大数据的表。
修改数据	update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件];
查询完整语法	select 字段列表 from 表名列表 where 条件列表 group by 分组字段 having 分组之后的条件 order by 排序 limit 分页限定

2.1.2.3.2 条件查询

SELECT 字段名 FROM 表名 [WHERE 条件];

运算符	说明
> 、< 、<= 、>= 、= 、<> !=	<>在 SQL 中表示不等于，在 mysql 中也可以使用!= 没有==
BETWEEN...AND	在一个范围之内
IN( 集合)	集合表示多个值，使用逗号分隔
IS NULL 不为空 is not null	查询某一列为 NULL 的值，注：不能写=NULL
LIKE	模糊查询占位符: _:单个任意字符 %：多个任意字符
AND 或 &&	与，SQL 中建议使用前者，后者并不通用。
OR 或 \|\|	或
NOT 或 !	非

2.1.2.3.3 排序查询

语法

SELECT 字段名 FROM 表名 [WHERE条件] ORDER BY 字段名 [ASC|DESC];

ASC: 升序，默认值

DESC: 降序

如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件。

2.1.2.3.4 聚合函数

SELECT 聚合函数(列名) FROM 表名;

常见的聚合函数

聚合函数	说明
count(*) \| count(主键)	计算表中的总记录数
max	计算最大值
min	计算最小值
sum	计算和
avg	计算平均值
length(str)	字符函数	获取字符的字节个数
upper(str)	字符函数	将字符转换为大写字符
lower(str)	字符函数	将字符转换为小写字符
substring(str,pos)	字符函数	截取从指定索引处后面所有的字符
substring(str,pos,len)	字符函数	截取从pos索引开始截取len个字符
replace(str,from_str,to_str)	字符函数	将str中的字符 from_str字符替换成to_str字符
round(x)	数学函数	四舍五入
round(x,d)	数学函数	四舍五入 d:代表的是保留小数点后几位
ceil(x)	数学函数	向上取整
floor(x)	数学函数	向下取整
mod(n,m)	数学函数	取余数 mod(10,3) 相当于: select 10 % 3
str_to_date(str,format)	日期函数	将日期字符转换成指定格式的日期 str_to_date('1990-11-11','%Y-%m-%d');
date_format(date,format)	日期函数	将日期转换成字符 date_format(now(),'%Y/%m/%d');

注意：聚合函数的计算，排除null值。
- 使用聚合函数后，不能直接查询其他列，因为聚合后仅展示第一条数据
- 注意聚合函数使用的位置

解决方案：
- 选择不包含非空的列进行计算
- IFNULL函数

2.1.2.3.5 分组、分页查询

*SQL执行流程

from
on
where、join
group by（开始使用select中的别名，后面的语句中都可以使用）
avg,sum.... （聚合函数）
having
select
distinct

聚合函数的字段如果数据为null，则忽略为null的记录。

聚合函数返回的数据仅为一条。

GROUP BY子句必须出现在WHERE子句之后（如果有 WHERE 子句的话），ORDER BY子句之前。

注意事项

分组之后查询的字段：分组字段、聚合函数

HAVING和WHERE的差别

WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。这是一个重要的区别，

WHERE排除的行不包括在分组中。这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。

但是 where 和 having 可以同时使用

书写位置不同:where书写在from后,having书写在group by后

执行顺序不同:where先执行,having后执行

过滤数据不同:where过滤原数据中的行,having过滤结果分组中的组

函数调用不同:where不能直接调用函数,having可以调用函数进行结果判断

where: 操作的数据源: 原始表,having: 操作的数据源: 结果集。

where 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来 where 对基本的条件筛选

where 后不可以跟聚合函数，having可以进行聚合函数的判断。

举例：

查询年龄大于25岁的人,按性别分组,统计每组的人数,并只显示性别人数大于2的数据

SELECT sex,COUNT(*) FROM stu WHERE age >25 GROUP BY sex HAVING COUNT(*)>2;

LIMIT语法格式

LIMIT offset,length;

offset：起始行数，从 0 开始计数，如果省略，默认就是 0

length： 返回的行数

计算公式

开始的索引 = （当前的页码 - 1） * 每页显示的条数

-- 每页查询显示3条数据 SELECT * FROM stu LIMIT 0,3; -- 第1页 SELECT * FROM stu LIMIT 3,3; -- 第2页 SELECT * FROM stu LIMIT 6,3; -- 第3页

2.1.3 数据库三大范式

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式和第五范式（5NF）。

而通常我们用的最多的就是第一范式（1NF）、第二范式（2NF）、第三范式（3NF），也就是数据库设计的“三大范式”。

第一范式（1NF）：要求数据库表的每一列都是不可分割的原子数据项

第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）

第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

下面详细讲解三大范式：

第一范式：列不可再分

原子数据，就是不可再分的数据，如，数据表中有一列名为人，这就不可以，为什么，因为人有男人和女人之分，也就是可以再细分，不符合第一范式。

第二范式：一张表只描述一件事情

定义一个部门表

部门表只能有部门编号，部门名等涉及部门的，部门表不能有餐厅菜名和服装店员工的信息吧，部门表之后能干和部门有关的事。

第三范式：表中的每一列和主键都是直接依赖关系，而不是间接依赖。

定义一个学生表

设主键为学生ID，那么学生姓名、学生性别和学生ID（学生ID代表学生本身，具有唯一性）是直接依赖的（有直接关系的），但是服装店员工工资和学生没有直接关系，就不能把服装店员工工资加入学生表中。

2.1.4 多表联合查询

2.1.4.1 SQL约束

约束英文: constraint
约束实际上就是表中数据的限制条件
作用
表在设计的时候加入约束的目的就是为了保证表中的记录完整性和有效性，比如用户表有些列的值（手机号)不能为空，有些列的值(身份证号)不能重复。
分类

NOT NULL - 指示某列不能存储 NULL 值。
UNIQUE - 保证某列的每行必须有唯一的值。
PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
CHECK - 保证列中的值符合指定的条件。
DEFAULT - 规定没有给列赋值时的默认值。

另外还有，自增长约束(auto_increment)，零填充约束(zerofill)

2.1.4.1.1 PRIMARY KEY 约束

PRIMARY KEY 约束唯一标识数据库表中的每条记录。

主键必须包含唯一的值。

主键列不能包含 NULL 值。

每个表都应该有一个主键，并且每个表只能有一个主键。

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

2.1.4.1.2 NOT NULL约束

NOT NULL 约束强制列不接受 NULL 值。

NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Age int
);

向已存在的表中添加、删除非空约束

添加not null约束
ALTER TABLE Persons
MODIFY Age int NOT NULL;

删除not null约束
ALTER TABLE Persons
MODIFY Age int NULL;

2.1.4.1.3 UNIQUE 约束

UNIQUE 约束唯一标识数据库表中的每条记录。

UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。

PRIMARY KEY 约束拥有自动定义的 UNIQUE 约束。

请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)

2.1.4.1.4 FOREIGN KEY 约束

一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY(唯一约束的键)。

FOREIGN KEY 约束用于预防破坏表之间连接的行为。

FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

外键的使用场景

有员工表和部门表，如果不使用外键，那么员工表里的部门名就可以随便填（如，部门名：我自己一个部门），这也能提交成功，但是部门里没有这个部门，这显然是不符合规定的。所以通过外键约束，保证，员工表的部门名只有填在部门表中已存在的部门名后才会保存信息。

2.1.4.1.5 CHECK 约束

CHECK 约束用于限制列中的值的范围。

如果对单个列定义 CHECK 约束，那么该列只允许特定的值。

如果对一个表定义 CHECK 约束，那么此约束会基于行中其他列的值在特定的列中对值进行限制。

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
)

2.1.4.1.6 DEFAULT 约束

DEFAULT 约束用于向列中插入默认值。

如果没有规定其他的值，那么会将默认值添加到所有的新记录。

2.1.4.2 SQL关联查询

关联查询，也称为多表查询，指两个或更多个表一起完成查询操作。前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。

SQL join 用于把来自两个或多个表的行结合起来。

2.1.4.2.1 INNER JOIN

内连接：连接结果仅包含符合连接条件的行

拿左表的记录去匹配右表的记录，若符合条件显示（二张表的交集）

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;
或者
SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;

内连接，没有（null）不显示

2.1.4.2.2 LEFT JOIN

左外连接：表示左表全部，再去匹配右表记录，若条件符合显示，若条件不符合显示NULL

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
或者
SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

外连接，没有（null）也显示

2.1.4.2.3 RIGHT JOIN

右外连接：展示右表全部，再去匹配左表记录，若条件符合显示，若条件不符合显示NULL

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;
或
SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;

外连接，没有（null）也显示

2.1.4.2.4 FULL OUTER JOIN

全连接：只要左表（table1）和右表（table2）其中一个表中存在匹配，则返行.

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

mysql不支持全连接，但可以通过左外连接+ union+右外连接实现

2.1.4.2.5 CROSS JOIN

交叉连接，也称笛卡尔积

2.1.4.3 组合查询

组合查询：多个select语句组合在一起的查询

SQL UNION 操作符合并两个或多个 SELECT 语句的结果。

UNION 操作符用于合并两个或多个 SELECT 语句的结果集

2.1.4.3.1 UNION

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

UNION自动去除重复，自动按默认规则排序，效率高。

2.1.4.3.2 UNION ALL

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

UNION ALL不去除重复，不排序，效率比UNION低。

2.1.5 视图与索引

2.1.5.1 视图

2.1.5.1.1 什么是视图

这就是一个视图

视图，就是让你看的见，方便查看的一张虚拟表。

2.1.5.1.2 视图的作用

重用SQL语句。

简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节。

使用表的组成部分而不是整个表。

保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。

更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

视图创建之后，可以用与表基本相同的方式利用它们。可以对视图执行SELECT操作，过滤和排序数据，将视图联结到其他视图或表。

视图中可以进行字段的编辑、添加、删除，记录的删除和数据查找等操作。

视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。在添加或更改这些表中的数据时，视图将返回改变过的数据。

注意事项

视图不包含数据，所以每次使用视图时，都必须处理查询执行时所需的任一个检索。如果你用多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能下降得很厉害。因此，在部署使用了大量视图的应用前，应该进行测试。

2.1.5.1.3 视图的规则和限制

与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）。

对于可以创建的视图数目没有限制。

为了创建视图，必须具有足够的访问权限。这些限制通常由数据库管理人员授予。

视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图。

ORDER BY可以用在视图中，但如果从该视图检索数据SELECT中也含有ORDER BY，那么该视图中的ORDER BY将被覆盖。

视图不能索引，也不能有关联的触发器或默认值。

视图可以和表一起使用。例如，编写一条联结表和视图的SELECT语句。

2.1.5.2 索引

以汉语字典的目录页（索引）打比方，我们可以通过拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

在庞大的数据中进行检索时，合理使用索引能够极大的提升数据查询效率，这就是数据库引用索引技术的根本原因。

2.1.5.2.1 索引的优缺点

优点：

索引大大减小了服务器需要扫描的数据量

索引可以帮助服务器避免排序和临时表

索引可以将随机IO变成顺序IO

索引对于InnoDB（对索引支持行级锁）非常重要，因为它可以让查询锁更少的阻塞。在MySQL5.1和更新的版本中，InnoDB可以在服务器端过滤掉行后就释放锁，但在早期的MySQL版本中，InnoDB直到事务提交时才会解锁。对不需要的元组的加锁，会增加锁的开销，降低并发性。 InnoDB仅对需要访问的元组加锁，而索引能够减少InnoDB访问的元组数。但是只有在存储引擎层过滤掉那些不需要的数据才能达到这种目的。一旦索引不允许InnoDB那样做（即索引达不到过滤的目的），MySQL服务器只能对InnoDB返回的数据进行WHERE操作，此时，已经无法避免对那些元组加锁了。如果查询不能使用索引，MySQL会进行全表扫描，并锁住每一个元组，不管是否真正需要。

缺点：

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存索引文件。

建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。

如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

对于非常小的表，大部分情况下简单的全表扫描更高效；

索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句

只为最经常查询和最经常排序的数据列建立索引

MySQL里同一个数据表里的索引总数限制为16个

什么情况下适合创建索引？

1.数据量大的表

2.不频繁用于添加修改删除的表

2.1.5.2.2 索引的类型

索引分类

1.普通索引index ：加速查找速度

2.唯一索引

主键索引：primary key ：加速查找+约束（不为空且唯一）
唯一索引：unique：加速查找+约束（唯一）

3.联合索引

primary key(id,name)：联合主键索引
unique(id,name)：联合唯一索引
index(id,name)：联合普通索引

4.全文索引fulltext：用于搜索很长一篇文章的时候，效果最好

5.空间索引spatial：了解即可

索引是在存储引擎中实现的，而不是在服务器层中实现的。所以，每种存储引擎的索引都不一定完全相同，并不是所有的存储引擎都支持所有的索引类型。

2.1.5.2.3 索引失效

查询条件包含or，可能导致索引失效。
表的字段类型是字符串，where时一定用引号括起来，否则索引失效。
like通配符可能导致索引失效。并不是用了like通配符，索引一定失效，而是like查询是以%开头，才会导致索引失效。
违背最左匹配原则：联合查询时，查询时的条件列不是联合索引中的第一个列，索引失效。
在索引列上使用mysql的内置函数，索引失效。
对索引列运算（如，+、-、*、/），索引失效。
索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效。
索引字段上使用is null， is not null，可能导致索引失效。
左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效。
mysql估计使用全表扫描要比使用索引快,则不使用索引。

2.1.6 SQL优化

2.1.6.1 MySQL查询过程

在进行MySQL的优化之前，必须要了解的就是MySQL的查询过程，很多查询优化工作实际上就是遵循一些原则，让MySQL的优化器能够按照预想的合理方式运行而已。

在数据库优化上有两个主要方面：即安全与性能。

安全->数据可持续性；

性能->数据的高性能访问。

2.1.6.2 优化维度

数据库优化维度有四个：

硬件、系统配置、数据库表结构、SQL及索引。

2.1.6.3 SQL优化

1.选择最适用的字段

为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。

2.使用连接（JOIN）来代替子查询

使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接（JOIN）..替代

3.使用联合(UNION)来代替手动创建的临时表

4.事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，通过锁定表的方法来获得更好的性能

5.使用外键

6.使用索引

ALL 全表扫描，没有优化，最慢的方式
index 索引全扫描
range 索引范围扫描，常用语<，<=，>=，between等操作
ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询
const 当查询是对主键或者唯一键进行精确查询，系统会把匹配行中的其他列作为常数处理
null MySQL不访问任何表或索引，直接返回结果
System 表只有一条记录(实际中基本不存在这个情况)
性能排行： System > const > eq_ref > ref > range > index > ALL优化查找语句

7.优化查找语句

相同类型的字段间进行比较操作
在建有索引的字段上尽量不要使用函数进行操作
在搜索字符型字段时，我们有时会使用LIKE关键字和通配符，虽然这种做法简单，但是却是以牺牲系统性能为代价。
注意避免在查询中让MySQL进行自动类型转换，因为转换过程也会使索引变得不起作用。
尽量不要使用select *，而是具体字段
varchar变长字段按数据内容实际长度存储，存储空间小，可以节省存储空间
char按声明大小存储，不足补空格
其次对于查询来说，在一个相对较小的字段内搜索，效率更高
使用vartchar代替char
避免在where字句中使用or来连接条件
使用or可能会使索引失效，从而全表扫描
避免在where中使用!= 或者<>操作符
引擎将放弃使用索引而进行全表扫描
where中使用默认值代替null

2.1.7 事务

2.1.7.1 事务概述

事务，一般是指要做的或所做的事情。在计算机术语中是指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。事务由事务开始(begin transaction)和事务结束(end transaction)之间执行的全体操作组成。一个事务可以是一条SQL语句，一组SQL语句或整个程序。

简单来说如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。

2.1.7.2 事务提交方式

自动提交

mysql就是自动提交的一条DML(增删改)语句会自动提交一次事务。
手动提交

Oracle 数据库默认是手动提交事务需要先开启事务，再提交

查看事务默认提交方式

SELECT @@autocommit; -- 1 代表自动提交 0 代表手动提交
或
show variables like 'autocommit'; -- NO 代表自动提交 OFF 代表关闭自动提交，改为手动提交

//修改默认提交方式

set @@autocommit = 0;
或
set autocommit=off;

2.1.7.3 事务四大特性

2.1.7.3.1 ACID

原子性(Atomicity) ：是不可分割的最小操作单位，要么同时成功，要么同时失败。
一致性(Consistency) ：事务操作前后，保证数据的一致性
隔离性(Isolation) ：多个事务之间,相互独立,互不干扰
持久性(Durability) ：当事务提交或回滚后，数据库会持久化的保存数据。掉电/关机内存硬盘上

2.1.7.4 事务5类问题

多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。

存在问题

脏读：一个事务，读取到另一个事务中没有提交的数据
不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样。
幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。

脏读无法容忍，不可重复读和幻读有时候可以接受。

隔离级别

级别	名字	隔离级别	脏读	不可重复读	幻读	数据库默认隔离级别
1	读未提交	read uncommitted	是	是	是
2	读已提交	read committed	否	是	是	Oracle和SQL Server
3	可重复读	repeatable read	否	否	是	MySQL
4	串行化	serializable	否	否	否

隔离级别从小到大安全性越来越高，但是效率越来越低。

oracle默认是read committed，mysql默认是repeatable read(底层有些优化机制，可以改善性能)

mysql可以通过select @@transaction_isolation可以查看事务隔离级别

修改事务隔离级别：global / session

set session transaction isolation level read committed;

set session transaction isolation level serializable;

我们详细解释上面的知识点。

首先只有存在并发数据访问时才需要事务。当多个事务访问同一数据时，可能会存在 5 类问题，包括 3 类数据读取问题（脏读、不可重复读和幻读）和 2 类数据更新问题（第 1 类丢失更新和第 2 类丢失更新）。

2.1.7.4.1 脏读（Dirty Read）

注意以下是一个账户，但是有两种不同的操作A和B。

脏读（Dirty Read）：A 事务读取 B 事务尚未提交的数据并在此基础上操作，而 B 事务执行回滚，那么 A 读取到的数据就是脏数据。

2.1.7.4.2 不可重复读（Unrepeatable Read）

事务 A 重新读取前面读取过的数据，发现该数据已经被另一个已提交的事务 B 修改过了。

2.1.7.4.3 幻读（Phantom Read）

注：初始数据库内没有张三

时间	事务A	事务B
T1		开启事务
T2	开启事务
T3	查询张三
T4		新增用户张三
T5		提交事务
T6	打印张三信息（幻读）

对于事务A来说，数据库内根本没有张三，应该返回空，怎么后来平白无故出现了张三？？这就是幻读

2.1.7.4.4 第1类丢失更新

事务 A 撤销时，把已经提交的事务 B 的更新数据覆盖了。

事务B已经成功提交事务，此时账户内余额为1100，但是事务A之前查到余额是1000，由于撤销事务导致回滚，账号余额又回到1000。

2.1.7.4.5 第2类丢失更新

事务 A 覆盖事务 B 已经提交的数据，造成事务 B 所做的操作丢失。

2.1.7.4.6 如何解决

数据库通常会通过锁机制来解决数据并发访问问题，按锁定对象不同可以分为表级锁和行级锁；按并发事务锁定关系可以分为共享锁和独占锁。

直接使用锁是非常麻烦的，为此数据库为用户提供了自动锁机制，只要用户指定会话的事务隔离级别，数据库就会通过分析 SQL 语句然后为事务访问的资源加上合适的锁，此外，数据库还会维护这些锁通过各种手段提高系统的性能，这些对用户来说都是透明的。ANSI/ISO SQL 92 标准定义了 4 个等级的事务隔离级别，如下表所示：

上一篇：2 数据库

老李头喽

关注

1
点赞
踩
7

收藏

觉得还不错? 一键收藏
打赏
0
评论
2.1数据库-MySQL

数据库就是存储数据的仓库，其本质是一个文件系统，数据按照特定的格式将数据存储起来，用户可以对数据库中的数据进行增加，修改，删除及查询操作。SQL （发音sequel）:结构化查询语言(Structured Query Language)。SQL语句就是对数据库进行操作的一种语言。SQL是一套标准，所有的数据库厂商都实现了此标准；但是各自厂商在此标准上增加了特有的语句，这部分内容我们称为方言。这就是一个视图视图，就是让你看的见，方便查看的一张虚拟表。
复制链接

扫一扫