数据库系统概述

本文系统的介绍了数据库的相关知识，包括数据库相关概念、以及MySQL的安装、SQL结构化查询语言（DDL、DML、DQL）数据库中数据类型、约束（主键约束、非空约束、外键约束）、视图、事务、索引等知识！

一、数据库介绍

1.数据库概念

---

数据库（DataBase，简称DB）是长期存储在计算机内部有结构的、大量的、共享的数据集合（也可以简说为存放数据的仓库）。

• 长期存储： 持久存储
• 有结构：
  • 类型：数据库不仅可以存放数据，而且存放的数据还是有类型的
  • 关系：存储数据与数据之间的关系
• 大量： 大多数数据库都是文件系统的，也就是说存储在数据库中的数据实际上就是存储在磁盘的文件中
• 共享： 多个应用程序可以通过数据库实现数据的共享

2.数据库的分类

数据库分为关系型数据库与非关系型数据库

---

关系型数据库：

• 概念： 关系型数据库，采用了关系模型来组织数据的存储，以行和列的形式存储数据并记录数据与数据之间的关系 —— 将数据存储在表格中，可以通过建立表格与表格之间的关联来维护数据与数据之间的关系。
• 应用示例：
  学生信息 – 学生类 – 学生表
  订单信息 – 订单类 – 订单表
• 常见数据库产品：
  • MySQL 免费
    • MariaDB
    • Percona Server
  • Oracle 收费
  • PostgreSQL
  • SQL Server
  • Access
  • Sybase
  • SQLite

---

非关系型数据库：

• 概念： 非关系型数据库，采用键值对的模型来存储数据，只完成数据的记录，不会记录数据与数据之间的关系。
• 应用： 在非关系型数据库中基于其特定的存储结构来解决一些大数据应用的难题。
• 常见数据库产品：
  • 面向检索的列式存储 Column-Oriented
    • HaBase （Hadoop子系统）
    • BigTable （Google）
  • 面向高并发的缓存存储Key-Value
    • Redis
    • MemcacheDB
  • 面向海量数据访问的文档存储 Document-Oriented
    • MongoDB ES（搜索引擎代替文档非关系型数据库）
    • CouchDB

3.数据库术语

• 数据库（Database） ： 存储数据的集合，提供数据存储服务
• 数据（Data） ： 实际上指的是描述事物的符号记录
• 数据库管理系统（Database Management System，DBMS ） ： 数据库管理系统，是位于用户与操作系统之间的一层数据管理软件
• 数据库系统管理员（Database Anministrator，DBA） ： 负责数据库创建、使用及维护的专门人员
• 数据库系统（Database System，DBS） ： 数据库系统管理员、数据库管理系统及数据库组成整个单元

---

二、MySQL安装

介绍绿色解压版配置：MySQL_5.7.17的安装与配置

1.下载安装包

	https://dev.mysql.com/downloads/mysql/

2.解压MySQL压缩包

	下载完成后解压，将其放到想要安装的目录下，例如：D:\apps\mysql-5.7.17-winx64

3.创建并修改配置文件my.ini

	新建一个my.ini配置文件，原始的my-default.ini配置文件只是个模版，不要在里面改动。

	my.ini的内容如下：


	[mysql]
		default-character-set=utf8

	[mysqld]
		port = 3306
		basedir=D:\apps\mysql-5.7.17-winx64
		datadir=D:\apps\mysql-5.7.17-winx64\data
		max_connections=200
		character-set-server=utf8
		default-storage-engine=INNODB
		explicit_defaults_for_timestamp=true


*************************************************
	/*default-character-set #客户端默认字符集

	basedir #安装目录

	datadir #数据库的数据的存放目录

	max_connections #允许最大连接数

	character-set-server #服务端使用的字符集

	default-storage-engine=INNODB #创建新表时将使用的默认存储引擎

	explicit_defaults_for_timestamp=true #从MySQL5.6开始，timestamp的默认行为是deprecated。因此加上这一行来避免警告。*/

4.在安装路径下新建一个空的data文件夹。

5.添加环境变量

	5.1 在系统环境变量中“新建”一个名为“MYSQL_HOME”的变量。变量值：“D:\apps\mysql-5.7.17-winx64”

	5.2 编辑现有的环境变量“Path”，在最后增加“%MYSQL_HOME%\bin”。

6.以管理员身份运行cmd，进入bin目录，执行 mysqld --initialize-insecure --user=root 命令。不进行这一步，安装完成之后无法启动服务。

7.添加mysql服务

	依然在管理员cmd窗口的bin目录下，执行 mysqld install  MySQL 命令安装。完成后会提示安装成功。


	删除服务：mysqld -remove mysql

8.启动

   依然在管理员cmd窗口的bin目录下，执行 net start MySQL 命令启动MySQL服务。
   net stop MySQL

9.登陆

	在普通cmd窗口中，进入bin目录，执行 mysql -u root -p 命令，默认没有密码，回车进入

10.修改登录密码

	 alter user 'root'@'localhost' identified by '新密码';

---

三、SQL结构化查询语言

1.概述

（1）概念

SQL（Structured Query Language）结构化查询语言，用于存取、查询、更新数据以及管理关系型数据库系统

（2）SQL分类

1.DDL（Data Definition Language）数据定义语言

• 用于完成对数据库对象（数据库、数据表、视图、索引等）的创建、删除、修改
• create（创建）、alter（修改）、drop（删除）、truncate（截断：删除数据但保留结构）

---

2.DML（Data Manipulation Language）数据操作/操纵语言

• 用于完成对数据表中的数据的添加、删除、修改操作
• 添加（insert）：将数据存储到数据表
• 删除（delete）：将数据从数据表移除
• 修改（update）：对数据表中的数据进行修改

---

3.DQL（Data Query Language）数据查询语言

• 查询（select）：用于将数据表中的数据查询出来（有时也将此部分划分到DML中）

---

4.DCL（Data Control Language）数据控制语言

• 用于完成事务管理等控制性操作
• grant、revoke

---

5.TCL（Transaction Controll language）事物控制语言

• commit、rolltroll、savepoint

（3）SQL基本语法

• SQL指令不区分大小写
• 每条SQL表达式结束之后都以 ; 结束
• SQL关键字之间以 空格 进行分隔
• SQL之间可以不限制换行（可以有空格的地方就可以有换行）
• MySQL注释：
  • 单行注释： – 注释内容
  • 单行注释： #注释内容
  • 多行注释： /注释内容/

2.DDL（数据定义语言）

• 使用DDL语句可以创建数据库、查询数据库、修改数据库、删除数据库

（0）查询

查询数据库相关：

• show databases: 显示当前mysql中的数据库列表
• show create database 库名: 显示指定名称的数据的创建的SQL指令
• use 库名: 使用/切换数据库

---

查询表相关：

• show tables： 查询数据表
• desc 表名： 查询表结构

（1）创建（create）

创建数据库：

• create database 库名： 创建数据库
• create database if not exists 库名： create database if not exists 库名
• create database 库名 character set 字符集: 在创建数据库的同时指定数据库的字符集（字符集：数据存储在数据库中采用的编码格式utf8、gbk）

2.创建表：

• 若创建的数据库无主键，mysql内核会自动分派一个伪列（_row_id），这样默认插入每行数据不相同
• primary key 关键字 标识的列 为主键列 ：该列不空，且不重复，是数据库中某行数据的唯一标识
• 语法：

create  table   表名  (
  字段名  数据类型  [字段特征]  [default 默认值 ], 
  字段名  数据类型  [字段特征]  [default 默认值 ],
   ... 
  字段名  数据类型  [字段特征]  [default 默认值 ] 
)

方式1： 普通主键

create table student01(
	sno int primary key,
	sname varchar(20),
	gender int,
	class char(10)
)

方式2： 主键+不为空的默认值

create table student02(
	sno int not null,
	sname varchar(20) not null default '待输入',
	gender int not null default 1,
	class char(10),
	primary key(sno)
)

方式3： 联合主键+不为空的默认值

create table student03(
	sno int,
	sname varchar(20) not null default '待输入',
	gender int not null default 1,
	class char(10),
	primary key(sno,sname)
)

方式4： 自增长主键+不为空的默认值

create table student04(
	sno int primary key auto_increment,
	sname varchar(10) not null default '待输入',
	gendar int not null,
	class char(10)
)

（2）修改（alter）

1.修改数据库：

• 修改数据库的字符集： alter database 库名 character set 字符集;

2.修改表：

1.修改表名

-- alter table 旧表名 rename to 新表名
alter table student04 rename to student05

2.修改表字符集

-- alter table 表名 character set 字符集
alter table student05 character set gbk

3.增加表字段（表结构）

-- 增加表字段
---(添加新字段时指定默认值，会将表中数组新字段的值设置为指定的默认值)
alter table student02 add age varchar(20) not null default '18'

4.修改表字段数据类型（表结构）

-- 修改字段
alter table student02 modify age int default 20

5.删除表字段（表结构）

-- 删除表字段
alter table student02 drop class

（3）删除（drop）

1.删除数据库

• 删除数据库 : drop database 库名
• 如果数据库存在则删除数据库 : drop database is exists 库名

2.删除数据库表：

-- 删除表和表中数据
drop table student04

（4）截断（truncate）

#截断（对于表操作：删除表内数据，但是保留表结构）
truncate table student04

3.DML（数据操纵语言）

（1）插入（insert）

• insert into 表名 (字段列表) values(列值列表);
• 当表名后的字段列表不是默认列表时，values的值序 必须与其保持一致
• 当表名后的列省略时，values后的给值必须与创建表时的字段顺序一致
• 当表的列未给值，则使用默认值填充
• 自增长主键添加数据时，0作为键值可以重复执行（但添加新的一行数据的主键是从最大值递增的）
• 自增长主键添加数据时,也可以指定添加新行数据的主键值（指定的键值不能和表中已经生成的键值重复）

1.插入单条数据

insert into student01(sno,sname,gender,class) values(0,'张三',1,'java2205');
insert into student02(sno,sname,gender,class) values(0,'张三',0,null);
insert into student03(sno,sname,gender,class) values(1,'李四',0,null)
insert into student03(sno,sname) values(2,'张三')

2.批量添加数据

/*增加多条数据*/
insert into student02 values(1,'李四',0,'一阶段')
		,(2,'王五',1,'二阶段')
		,(3,'赵六',1,'三阶段')

（2）删除（delete）

• delete from 表名 where 条件
• 删除的最小数据操作单元是行：删除数据时，是删除满足条件的某行数据，不是某个字段值，所以是delete from 其含*

delete from student04 where sno>8

（3）更新（update）

• update 表名 set 字段名=新值，[字段名=新值]… where 条件

-- 更新数据
update student02 set sname='李四' where sno=0
update student02 set gender=1,class='四阶段' where sno>1

4.DQL（数据查询语言）

单表查询

（1）where⼦句

在删除、修改及查询的语句后都可以添加where⼦句（条件），⽤于筛选满⾜特定的添加的数据进⾏删除、修改和查询操作。

• delete from 表名 where 条件;
• update 表名 set … where 条件;
• select … from 表名 where 条件;

-- 场景10：between  and 、条件关系运算符  （上下包含）
select * from student06 where age between 20 and 50
select * from student06 where age>=20 and age<=50

（2）like⼦句

在where⼦句的条件中，我们可以使⽤like关键字来实现模糊查询

• select * from 表名 where 列名 like ‘reg’;
• 在like关键字后的reg表达式中
  • % 表⽰任意多个字符
  • _ 表⽰任意单个字符

-- 场景9-3：查出所有名为天虹的人  _代表单一字符  like 模糊查询
select * from student06 where sname like '_三'

-- 场景9-3-1：查出所有名为天虹的人  %代表任意字符  like 模糊查询
select * from student06 where sname like '%四%'

（3）计算列

对从数据表中查询的记录的列进⾏⼀定的运算之后显⽰出来

-- 场景9-3-2计算某个列的值
select sname,s.age+1 from student06 s

（4）as字段起别名

我们可以为查询结果的列名 去⼀个语义性更强的别名 (如下案例中 as 关键字也可以省略)

-- as 可以省略
select sname as '姓名',age '年龄' from student06 

（5）distinct消除重复⾏

从查询的结果中将重复的记录消除（每一条数据，联合去重，去除所查询列所有重复的值）

-- 场景5：查询去重(联合去重，去除所查询列所有重复的值)
select distinct sname,sno from student06

（6）order by 排序

将查询到的满⾜条件的记录按照指定的列的值升序/降序排列

• select * from 表名 where 条件 order by 列名 asc|desc,[列名 asc|desc];
• order by 列名 表⽰将查询结果按照指定的列排序
  • asc 按照指定的列升序（默认）
  • desc 按照指定的列降序

-- 场景6: 数据排序 order by 字段  desc|ASC
select * from student06 order by sno asc,age desc

（7）聚合函数

SQL中提供了⼀些可以对查询的记录的列进⾏计算的函数

• count() 统计函数，统计满⾜条件的指定字段值的个数（记录数）
• max() 计算最⼤值，查询满⾜条件的记录中指定列的最⼤值
• min() 计算最⼩值，查询满⾜条件的记录中指定列的最⼩值
• sum() 计算和，查询满⾜条件的记录中 指定的列的值的总和
• avg() 求平均值，查询满⾜条件的记录中 计算指定列的平均值

-- 场景14： 分组函数（聚合函数） count 、sum、avg、min、max
select count(*) as '总数目',sum(age) '年龄累加和', avg(age) '平均年龄',min(age)  '最小年龄' ,max(age) as '最大年龄' from student06

（8）日期函数

当我们向⽇期类型的列添加数据时，可以通过字符串类型赋值（字符串格式必须为：yyyy-MM-dd hh:mm:ss）

• 获取当前系统时间
  • now()
  • sysdate()

-- 场景15 ：日期类型----now(),sysdate()
select now(),sysdate(),date '2022-01-01'

（9）字符串函数

通过SQL指令对字符串进⾏处理

• concat(colnum1,colunm2,…) 拼接多列
• upper(column) 将字段的值转换成⼤写
• lower(column) 将指定列的值转换成⼩写
• substring(column,start,len) 从指定列中截取部分显⽰ start从1开始

（10）分组函数group by

分组：就是将数据表中的记录按指定的列进⾏分组

• 语法：

select 分组字段/聚合函数
from 表名
[where 条件]
group by 分组列名 [having 条件]
[order by 排序字段]

• select 后 from前通常显⽰分组字段和聚合函数(对分组后的数据进⾏统计、求和、平均值等)
• 语句执⾏顺序：
  • 先根据where条件从数据库查询记录
  • group by对查询记录进⾏分组
  • 执⾏having对分组后的数据进⾏筛选

/*在分组查询中，select和from间出现的值只能有:1 分组字段；2、分组函数（count、sum、avg、min、max）；3、常量*/
select min(sal) from employee group by deptno;

-- 场景19：各部门 工资>=8000 员工工资总额       
select sum(sal) from employee where sal>=8000 group by deptno

（11）分⻚查询limit

当数据表中的记录⽐较多的时候，如果⼀次性全部查询出来显⽰给⽤⼾，⽤⼾的可读性/体验性就不太好，因此我们可以将这些数据分⻚进⾏展⽰。

• …limit param1,param2;
  • param1：表⽰获取查询语句的结果中的第⼀条数据的索引（索引从0开始）
  • param2：表⽰获取的查询记录的条数（如果剩下的数据<param2，则返回剩下的所有记录）
• initNUmber =(pageNumber -1) x pageSize

-- 场景8-2：查询年龄从小到大 第4-6人 (分页)
-- 分页 limit initNumber,pageSize ( initNUmber =(pageNumber -1)*pageSize)
select * from student06 order by age asc limit 3,6

（12）根据查询记录对表内数据进行更改

复制表时，无法复制约束

-- 场景1：复制表结构,带数据
create table student06 select * from student05

drop table student06

-- 场景2：复制表结构,不带数据
create table student06 select * from student05 where 1=0

-- 场景3：复制部分表结构,带数据  （复制表时，无法复制主键约束）
create table student06 select sno,sname from student05 

-- 场景5 ：将查询的结果插入某表中
insert into student06 select sno,sname,age from student06 

连接查询

在MySQL中可以使⽤join实现多表的联合查询⸺连接查询，join按照其功能不同分为三个操作：

• inner join 内连接
• left join 左连接
• right join 右连接

---

• 注意：连接时一定要使用 where或on设置连接条件，不然会造成恶性连接
• 语法： select * from leftTabel inner | left | right JOIN rightTable ON 匹配条件 [where 条件];

（1）内连接

• 使用inner join连接两张表，使⽤ on 设置两张表连接查询的匹配条件，查询结果只获取两种表中匹配条件成⽴的数据，任何⼀张表在另⼀种表如果没有找到对应匹配则不会出现在查询结果中
• 内连接 （交集）

注意：

• 使⽤where设置过滤条件：先⽣成笛卡尔积再从笛卡尔积中过滤数据（效率很低）
• 使⽤ON设置连接查询条件：先判断连接条件是否成⽴，如果成⽴两张表的数据进⾏组合⽣成⼀条结果记录

 -- 基于where 
select d.*,e.empno,e.ename,e.sal from department d,employee e where d.deptno=e.deptno

 -- 基于join
select d.*,e.* from department d inner join employee e on(d.deptno=e.deptno)

（2）左连接

• 内连接 （交集+左有而右边没有的记录）
• 左连接：显⽰左表中的所有数据，如果在有右表中存在与左表记录满⾜匹配条件的数据，则进⾏匹配；如果右表中不存在匹配数据，则显⽰为null

-- 场景16 -2  ：左外连接 （交集+左有而右边没有的记录）
select d.*,e.* from department d left join employee e on(d.deptno=e.deptno)

（3）右连接

• （交集+右有而左边没有的记录）

-- 场景16 -3  ：右外连接 （交集+右有而左边没有的记录）
select d.*,e.* from department d right join employee e on(d.deptno=e.deptno)

（3）全外连接

-- 场景16 - 4 ：全外连接 （使用union连接）
select d.*,e.* from department d left join employee e on(d.deptno=e.deptno)
union
select d.*,e.* from department d right join employee e on(d.deptno=e.deptno)

⼦查询/嵌套查询

⼦查询 ： 先进⾏⼀次查询，第⼀次查询的结果作为第⼆次查询的源/条件（第⼆次查询是基于第⼀次的查询结果来进⾏的）

（1）列子查询（⼦查询返回单个值（单⾏单列））

• 在列上书写查询语句
• 如果⼦查询返回的结果是⼀个值（单列单⾏），条件可以直接使⽤关系运算符（= != …）

-- 场景17-2：子查询 （查询出的所有员工详细信息（包括部门））
select e.empno,e.ename,e.sal,(select dname from department d where e.deptno=d.deptno) dnamex from employee e

（2）条件子查询（多行多列）

• 在where条件上书写查询语句
• 如果⼦查询返回的结果是多个值（单列多⾏），条件使⽤IN / NOT IN
• 先查询cid=1班级中的所有学⽣信息，将这些信息作为⼀个整体虚拟表(多⾏多列)，再基于这个虚拟表查询性别为男的学⽣信息（‘虚拟表’需要别名）

-- IN查询
select * from students where cid IN (select class_id from classes where class_name LIKE 'Java%');
-- 虚拟表查询
select * from (select * from student06 where sno=1) t where t.age>28;

---

四、MySQL数据类型

• 数据类型，指的是数据表中的列中支持存放的数据的类型

（1）数值类型

类型	内存空间	范围	说明
tinyint	1byte	有符号 -128~127 无符号 0~255	特小型整数
smallint	2byte	有符号 -32768 ~ 32767 无符号 0~65535	小型整数
mediumint	3byte	有符号 -2^23 ~ 2^23 - 1 无符号 0~2^32-1	中型整数
int/integer	4byte		整数
bigint	8byte		大型整数
float	4byte		单精度小数
double	8byte		双精度数字
decimal	(第一参 数+2)byte		decimal(10,2) 表示数值一共有10位 其 中小数位有2位

（2）字符串类型 ：存储字符序列的类型

类型	字符长度	说明
char	0~255 字节	定长字符串，最多可以存储255个字符 ;当我们指定数据表字段 为char(n) 此列中的数据最长为n个字符，如果添加的数据少于 n，则补’\u0000’至n长度
varchar	0~65536 字节	可变长度字符串，此类型的类最大长度为65535
tinyblob	0~255 字节	存储二进制字符串
blob	0~65535 字节	存储二进制字符串
mediumblob	0~1677215 字 节	存储二进制字符串
longblob	0~4294967295 字节	存储二进制字符串
tinytext	0~255 字节	文本数据（字符串）
text	0~65535 字节	文本数据（字符串）
mediumtext	0~1677215 字 节	文本数据（字符串）
longtext	0~4294967295 字节	文本数据（字符串）

（3）日期类型
在MySQL数据库中，我们可以使用字符串来存储时间，但是如果我们需要基于时间字段进行查询操作（查询在某个时间段内的数据）就不便于查询实现

类型	格式	说明
date	yyyy-MM-dd	日期，只存储年月日
time	hh-mm-ss	时间，只存储时分秒
year	yyyy	年份
datetime	yyyy-MM-dd hh-mm-ss	日期+时间，存储年月日时分秒
timestamp	yyyyMMddhhmmss	日期+时间 （时间戳）

---

五、约束

（1）字段约束

1.字段约束的原因
在创建数据表的时候，指定的对数据表的列的数据限制性的要求（对表的列中的数据进行限制）

• 保证数据的有效性
• 保证数据的完整性
• 保证数据的正确性

---

2.字段约束的分类

• 非空约束（not null）：限制此列的值必须提供，不能为null
• 唯一约束（unique）：在表中的多条数据，此列的值不能重复
• 主键约束（primary key）：非空+唯一，能够唯一标识数据表中的一条数据
• 外键约束（foreign key）：建立不同表之间的关联关系

（2）主键约束

1.概念：

• 主键：就是数据表中记录的唯一标识，在一张表中只能有一个主键（主键可以是一个列，也可以是多个列的组合）

---

2.操作

• 删除数据表主键约束 ：alter table books drop primary key
• 创建表之后添加主键约束 ： alter table books modify book_isbn char(4) primary key;

---

3.主键自增长

• 在我们创建一张数据表时，如果数据表中有列可以作为主键（例如：学生表的学号、图书表的isbn）我们可以直接这是这个列为主键；
• 当有些数据表中没有合适的列作为主键时，我们可以额外定义一个与记录本身无关的列（ID）作为主键，此列数据无具体的含义主要用于标识一条记录，在mysql中我们可以将此列定义为int，同时设置为 自动增长 ，当我们向数据表中新增一条记录时，无需提供ID列的值，它会自动生成。
• 定义int类型字段自动增长： auto_increment
• 自动增长从1开始，每添加一条记录，自动的增长的列会自定+1，当我们把某条记录删除之后再添加数据，自动增长的数据也不会重复生成（自动增长只保证唯一性、不保证连续性）

（3）外键约束

1.关联关系
MySQL是⼀个关系型数据库，不仅可以存储数据，还可以维护数据与数据之间的关系⸺通过在数据表中添加字段建⽴外键约束。

---

数据与数据之间的关联关系分为四种：

• ⼀对⼀关联
• ⼀对多关联
• 多对⼀关联
• 多对多关联

2.外键约束
将⼀个列添加外键约束与另⼀张表的主键进⾏关联后，这个外键约束的列添加的数据必须要在关联表的主键字段中存在，保证了数据的完整性

• 主表： 主键被引用的表
• 从表： 需要设置外键的表

ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名)

3.外键约束——级联
当存在外键关系时，只要存在对主表某一主键的引用时，就无法对该主键进行删除和更改，但是可通过级联操作来进行删除和修改

#添加外键约束，同时添加级联更新  标准语法：
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) 
ON UPDATE CASCADE;
#当主表中的主键进行更新时，所有引用该主键的表也会进行更新

#添加外键约束，同时添加级联删除  标准语法：
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) 
ON DELETE CASCADE;
#主表的主键进行删除时，所有引用该主键的记录（表中的一行即为一个元组，或一条记录）将会被删除。

#添加外键约束，同时添加级联更新和级联删除  标准语法：
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) 
ON UPDATE CASCADE ON DELETE CASCADE;

---

六、事务

1.概念、特征

• 概念：
  • 多条指令 一起执行，成功或失败；

  ---

• 特征（ACID）：
  • 原子性（Atomic）： 事务中所有的指令，不可分割
  • 一致性（Consistence）： 事务前与事务后，数据本身需要保持一致
  • 隔离性（Isolation）： 多个事务间 不得干扰
  • 持久性（Duration）： 事务一旦提交，不可更改

2.指令

事务涉及指令
当执行开启事务的命令后，遇到第一个终止事务的命令时，会结束此事务（只有成功-commit和失败两种-rollback）

• begin :开启事务
• start transaction :开启事务
• commit ：提交事务（终止）
• rollback: 回滚事务（终止）

---

指令特征

• insert、update、delete 自动开启事务、自动提交事务
• 手动开启事务 ，则自动事务失效

3.其他注意事项：

数据脏读问题：

• 脏读： 查询到其他事务 未提交的数据
• 不可重复读： 在一个事务中 第一查询与二次查询的结果不一致
• 幻读： 第一次查询中的数据 在第二次查询时 消失

锁问题： 锁是事务的内在机制，事务是锁的外在呈现

• 行级锁
  • 多个互斥操作，开启事务操作同一行数据（未终止事务前），会造成后面操作此数据的等待，即此时第一个操作对此行数据加上了行级锁
  • for update是一种行级锁，又叫排他锁，一旦用户对查询行为行为施加来了行级锁(for update)，则查询也可以实现加行级锁

-- 示例1：未终止事务前
begin;
update bankAccount set bmoney=bmoney-100 where bname='张三';

begin;
delete from bankAccount where bname='张三';

-- 示例2：for update
begin;
delete from bankAccount where bname='张三';

begin;
	select * from bankAccount where bname='张三' for update;

• 表锁
  • 当更新数据库数据时，如果没有触发索引，则会锁表（事务未提交前），锁表后再对表做任何变更操作都会导致锁冲突，所以表锁的锁冲突概率较高。

begin;
update bankAccount set bmoney=bmoney+100

begin;
	select * from bankAccount where bname='张三' for update;

---

七、视图

1.概念

视图，就是 由数据库中⼀张表或者多张表根据特定的条件查询出得数据构造成得 虚拟表（存储到内存中）

2.视图的作用

• 安全性： 如果我们直接将数据表授权给⽤⼾操作，那么⽤⼾可以CRUD数据表中所有数据，加⼊我们想要对数据表中的部分数据进⾏保护，可以将公开的数据⽣成视图，授权⽤⼾访问视图；⽤⼾通过查询视图可以获取数据表中公开的数据，从⽽达到将数据表中的部分数据对⽤⼾隐藏。
• 简单性： 如果我们需要查询的数据来源于多张数据表，可以使⽤多表连接查询来实现；我们通过视图将这些连表查询的结果对⽤⼾开放，⽤⼾则可以直接通过查询视图获取多表数据，操作更便捷。

3.视图操作

• 创建视图：

	create view 视图名 as select_statement(查询出的表)

• 查询视图结构：

	desc 视图名

• 修改视图：

-- 方式1：
create or replace view view_bank
as
select ba.bid,ba.bname,ba.bmoney,(select bn.bname from bankName >bn where ba.bno=bn.bno) '新名字' from bankAccount ba;
-- 方式1：
alter view view_bank
as
select ba.bid,ba.bname,ba.bmoney,(select bn.bname from bankName >bn where ba.bno=bn.bno) '新名字2' from bankAccount ba;

4.视图特性

视图是虚拟表，查询视图的数据是来源于数据表的。当对视图进⾏操作时，对原数据表中的数据是否由影响呢？

• 查询操作: 如果在数据表中添加了新的数据，⽽且这个数据满⾜创建视图时查询语句的条件，通过查询视图也可以查询出新增的数据；当删除原表中满⾜查询条件的数据时，也会从视图中删除。
• 新增数据: 如果在视图中添加数据，数据会被添加到原数据表
• 删除数据: 如果从视图删除数据，数据也将从原表中删除
• 修改操作: 如果通过修改数据，则也将修改原数据表中的数据
• 视图的使⽤建议 对复杂查询简化操作，并且不会对数据进⾏修改的情况下可以使⽤视图。

---

八、索引

数据库是⽤来存储数据，在互联⽹应⽤中数据库中存储的数据可能会很多(⼤数据)， 数据表中数据的查询速度会随着数据量的增⻓逐渐变慢 ，从⽽导致响应⽤⼾请求的速度变慢，⽤⼾体验差，我们如何提⾼数据库的查询效率呢？

1.概念

• 索引，就是⽤来提⾼数据表中数据的查询效率的。是将数据表中某⼀列/某⼏列的值取出来构造成便于查找的结构进⾏存储，⽣成数据表的 ⽬录 。
• 当我们进⾏数据查询的时候，系统执行查询时先在 ⽬录 中进⾏查找得到对应的数据的地址，然后再到数据表中根据地址快速的获取数据记录，避免全表扫描。

2.索引的分类

MySQL中的索引，根据创建索引的列的不同，可以分为：

• 主键索引： 在数据表的主键字段创建的索引，这个字段必须被primary key修饰，每张表只能有⼀个主键
• 唯⼀索引： 在数据表中的唯⼀列创建的索引(unique)，此列的所有值只能出现⼀次，可以为NULL
• 普通索引： 在普通字段上创建的索引，没有唯⼀性的限制
• 组合索引： 两个及以上字段联合起来创建的索引

---

注意：

• 在创建数据表时，将字段声明为主键（添加主键约束），会⾃动在主键字段创建主键索引
• 在创建数据表时，将字段声明为唯⼀键（添加唯⼀约束），会⾃动在唯⼀字段创建唯⼀索引

3.创建索引

• 唯一索引

# 创建唯⼀索引: 创建唯⼀索引的列的值不能重复（unique）
# create unique index <index_name> on 表名(列名);
create unique index index_test1 on tb_testindex(tid);

• 普通索引

# 创建普通索引: 不要求创建索引的列的值的唯⼀性
# create index <index_name> on 表名(列名);
create index index_test2 on tb_testindex(name);

• 组合索引

# 创建组合索引
# create index <index_name> on 表名(列名1,列名2...);
create index index_test3 on tb_testindex(tid,name);

• 全文索引
  MySQL 5.6 版本新增的索引，可以通过此索引进⾏全⽂检索操作，因为MySQL全⽂检索不⽀持中⽂，因此这个全⽂索引不被开发者关注，在⽤开发中通常是通过搜索引擎（数据库中间件）实现全⽂检索

create fulltext index <index_name> on 表名(字段名);

4.索引的使用

（1）注意事项

• 索引创建完成之后⽆需调⽤，当根据创建索引的列进⾏数据查询的时候，会⾃动使⽤索引；
• 组合索引需要根据创建索引的所有字段进⾏查询时触发。
• 在 命令⾏窗⼝中可以查看查询语句的查询规划：

		explain select * from tb_testindex where tid=250000\G;

（2）查看索引

# 查询数据表的索引
show indexes from tb_testindex;
# 查询索引
show keys from tb_testindex;

（3）删除索引
删除索引：索引是建⽴在表的字段上的，不同的表中可能会出现相同名称的索引，因此删除索引时需要指定表名

	drop index index_test3 on tb_testindex;

（4）索引使用总结

• 优点
  • 索引⼤⼤降低了数据库服务器在执⾏查询操作时扫描的数据，提⾼查询效率
  • 索引可以避免服务器排序、将随机IO编程顺序IO
• 缺点
  • 索引是根据数据表列的创建的，当数据表中数据发⽣DML操作时，索引⻚需要更新
  • 索引⽂件也会占⽤磁盘空间（物理文件，视图存储在内存中）
• 注意事项
  • 数据表中数据不多时，全表扫⾯可能更快，不要使⽤索引
  • 数据量⼤但是DML操作很频繁时，不建议使⽤索引
  • 不要在数据重复读⾼的列上创建索引（性别）
  • 创建索引之后，要注意查询SQL语句的编写，避免索引失效