MySQL

SQL

结构化查询语言，用于存取，查询，更新数据以及管理关系型数据库系统

在不同的数据库产品中遵循SQL的通用规范，但是也对SQL有一些不同的改进，形成了一些数据库的专有指令
- MySQL: limit
- SQLServer: top
- Oracle: rownum

SQL分类

根据SQL指令完成的数据库操作的不同，可以将SQL指令分类

DDL Data Definition Language 数据定义语言
- 用于完成对数据库对象(数据库，数据表，视图，索引等)的创建，删除，修改
DML Data Manipulation Language 数据操作语言
- 用于完成对数据表中的数据的添加，删除，修改操作
DQL Data Query Language 数据查询语言
- 用于将数据表中的数据查询出来
DCL Data Control Language 数据控制语言
- 用于完成事务管理等控制性操作

SQL基本语法

在MySQL Command Line Client 或者 navicat 等工具中都能编写SQL指令

SQL 指令不区分大小写
每条SQL表达式结束之久都以;结束
SQL 关键字之间以空格进行分割
SQL 之间可以不限制换行(可以有空格的地方就可以换行)

DDL

DDL-数据库操作

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

查询数据库

## 显示当前数据库列表
show databases;

## 显示此数据库创建SQL指令
show create database <dbName>;

创建数据库

## 创建数据库 dbName表示创建的数据库名称
create database <dbName>;

## 创建数据库，防止数据库存在报错,当不存在时创建
create database if not exists <dbName>;

## 在创建数据库的同时指定数据库的字符集(编码格式,utf8可选)
create database <dbName> character set <utf8>;

修改数据库

## 修改数据库的字符集
alter database <dbName> character set <utf8>; # utf8 gbk

删除数据库

## 删除数据库,防止数据库不存在报错
drop database if exists <dbName>;

使用/切换数据库

use <dbName>;

创建数据表

数据表实际上就是一个二位的表格，是由多列组成，表格的每一列时表格的一个字段

create table <dbTableName>(
    <TableName> char(8) not null unique, # 格式，下为例子
    T_name varchar(20) not null,
    T_gender char(2) not null,
    T_age int not null,
    T_tel char(11) not null unique
);

查询数据表列
1
show tables;
查询表结构
1
desc <tableName>;

删除数据表

## 删除数据表
drop table <tableName>;

## 当数据表存在时删除数据表
drop table if exists <tableName>;

修改数据表

## 修改表名
alter table <tableName> rename to <newTableName>;

## 数据表也是有字符集的，默认字符集与数据库一致
alter table <tableName> character set <utf8>;

## 添加字段
alter table <tableName> add <columnName> varchar(200);

## 修改字段的列表和类型
alter table <tableName> change <oldColumnName> <newColumnName> <type>;

## 只修改字段类型
alter table <tableName> modify <columnName> <newType>;

## 删除字段
alter table <tableName> drop <columnName>;

MySQL 数据类型

数值类型
tinyint smallint mediumint int/integer bigint float double decimal
1
2
3
create table books(
stock tinyint
);
字符类型
日期类型

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

字段约束

在创建数据表的时候，指定的对数据表的列的数据限制性的要求(对表的列中的数据进行限制)

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

字段常见的约束

非空约束

限制此列的值必须提供，不能null

create table books(
    a varchar(20) not null # 添加not null字段
);

唯一约束

在表中的多条数据，此列的值不能重复

create table books(
    a varchar(20) unique # 添加unique字段
);

主键约束

非空+唯一，能够唯一标识数据表中的一条数据

主键 ——数据表中记录的唯一标识，在一张表中只能有一个主键(主键可以是一列，也可以是多个列的集合)

当一个字段声明为主键之后，添加数据时

此字段数据不能为空

此字段数据不能重复

创建表时添加主键约束

create table books(
    a varchar(20) primary key # 添加primary key字段
);

或

create table books(
    a varchar(20),
    b int,
    primary key(a) # 最后定义
);

删除数据表主键约束

alter table <tableName> drop primary key;

创建表之后添加主键约束

alter table <tableName> modify <cloumnName> <type> primary key

主键自增长

在我们创建一张数据表时，如果数据表中有列可以作为主键(例如: 学生表的学号)我们可以直接使这个列为主键；当有些数据表中没有合适的列作为主键时，我们可以额外定义一个与记录本身无关的列(ID)作为主键，此列无具体含义主要用于表示一条记录，在MySQL中我们可以将此列定义为int，同时设置自动增长，当我们向数据表中新增一条记录时，无需提供ID列的值，它会自动生成

定义主键自动增长

create table types(
    # 关键词添加 auto_increment,一定要在主键中，字段名自定义
    <type_id> int primary key auto_increment,
    type_name varchar(20) not null,
    type_remark varchar(100)
);

注意：自动增长从1开始，每添加一条记录，自动的增长的列会自+1，当我们把某条记录删除之后再添加数据，自动增长的数据也不会重复生成(自动增长只保证唯一性，不保证连续性)

联合主键

将数据表中的多列组合在一起设置为表的主键
- 定义联合主键
  1
  2
  3
  4
  5
  6
  create table grades(
  stu_num char(8),
  course_id int,
  score int,
  primary key(stu_num, course_id) # 添加联合主键
  );
  注意：在实际企业项目的数据库设计中，联合主键使用的频率并不高；当一个数据表中没有明确的字段可以作为主键时，我们可以额外添加一个ID字段作为主键

外键约束：建立不同表之间的关联关系

DML

create table T(
 T_num char(8) primary key,
 T_name varchar(20) not null,
 T_gender char(2) not null,
 T_age int not null,
 T_tel char(11) not null unique
);

插入数据

语法

## 像数据表中指定的列添加数据(不允许为空的列必须提供数据)
insert into <tableName>(columnName, columnName ...) 
    -> value (value1, value2 ...);

## 数据表名后的字段名列表顺序可以不与表中一致
## 但是value中值的顺序必须与表名后的字段名顺序对应
insert into T(T_num, T_name, T_gender, T_age, T_tel) 
    -> values('12', '1234', '12', 8, '123');

## 当要向表中的所有列添加数据时，数据表名后面的字段列表可以省略，
## 但是value中的值的顺序要与数据表定义的字段顺序保持一致
insert into T values('12', '1234', '12', 8, '123');

删除数据

从数据表中删除满足特定条件(所有)的数据

语法

delete from <tableName> [where conditions];

修改数据

对数据表中以及添加的记录进行修改

语法

update <tableName> set columnName=value, columnName1=value2...
    -> [where conditions]

DQL

从数据表中提取满足特定条件的记录

单表查询

多表联合查询

查询基础

语法

## select 关键字后指定要显示查询到的记录的那些列
select columnName1[,columnName2,...] from <tableName>;

## 查询所有列
select * from <tableName>; # 不建议使用

where 子句

在删除，修改及查询的语句后都可以添加where子句(条件)，用于筛选满足条件的数据进行操作

delete from <tableName> where conditions

update <tableName> set ... where conditions

select ... from <tableName> where conditions

多条件查询

在 where 子句中，可以将多个条件通过逻辑预算符进行连接，通过多个条件来筛选要操作的数据

## and
select * from <tableName> where <columnName>=value and <columnName>=value

## or
select * from <tableName> where <columnName>=value or <columnName>=value

## not between ... and ...
select * from <tableName> where <columnName> not between a and b

LIKE 子句

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

语法
1
select * from <tableName> where <columnName> like 'reg'
- 在like关键字后的reg表达式中，为正则表达式的使用
  - % 表示任意多个字符【%o%包含字母o】
  - _表示任意一个字符【_o% 第二个字母为o】

对查询结果的处理

设置查询的列

声明显示查询结果的指定列

select columnName1,columnName2... 
    -> from <tableName> where <columnName> > 20

计算列

对从数据表中查询的记录的列进行运算之后显示出来

select columnName1,x - columnName2... from <tableName>

字段别名

我们可以为查询结果的列名，去一个语义性更强的别名

select <columnName> as <xxx> from <tableName>

消除重复行

从查询的结果中将重复的记录消除

select distinct <columnName> from <tableName> # 关键字distinct

排序

将查询到的结果按照一定规则升序/降序排列

语法

## 单字段排序
select * from <tableName> where conditions 
    -> order by <columnName> asc|desc # 关键字段 order by ... asc|desc

order by columnName 表示将查询结果按照指定的列排序(置于where条件后)
asc 升序(默认)
desc 降序

## 多字段排序，先满足第一个排序规则，后满足下一个
... by <columnName> asc, <columnName> desc

聚合函数

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

count() 统计函数，统计满足条件的指定字段值的个数(记录数)
max() 获取最大值
min() 获取最小值
sum() 计算和，指定列的总和
avg() 求平均值

日期函数和字符串函数

日期函数

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

如果我们想要获取当前系统时间到日期类型的列，可以使用now()或sysdate()

insert into T values('1','2',now());

字符串函数

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

## 拼接字符串 concat
select concat(xxx,'-',aaa) from T;

## 将字段的值转换成大写
select upper(xxx) from T;

## 将字段的值转换为小写
select lower(xxx) from T;

## 从指定列中截取部分显示 start从1开始,len是长度
select xxx, substring(xxxx,start,len) from T;

分组查询

将数据表中的列记录按照指定的列进行分组

语法
1
2
select 分组字段/聚合函数 from 表名 [where 条件]
-> group by 分组字段 [having 条件] [order by 排序字段]
- select 后通常显示分组字段和聚合函数
- 语句执行顺序：
  1. 先根据where条件从数据库查询记录
  2. group by对查询记录进行分组
  3. 执行having对分组后的数据进行筛选

分页查询

当数据表中的记录比较多的时候，如果一次性全部查询出来显示给用户，用户的可读性、体验性就不太好，因此我们可以将这些数据分页进行展示

语法
1
select ... from ... where ... limit param1,param2
- param1 int 表示获取查询语句的结果中的第一条数据的索引(索引从0开始)
- param2 int 表示获取的查询记录的条数(如果剩下的数据条数<param2,则返回剩下的条目)
案例：对数据表中的学生信息进行分页显示，总共有10条数据，我们每页显示3条

总记录数 count 10

每页显示 pageSize 3

总页数 pageCount = count%pageSize==0? count/pageSize + 1
1
2
3
4
5
6
## 查询第一页
select * from T limit 0,3;

## 通用表达式
select * from <tableName> [where ...]
-> limit (pageNum-1)*pageSize,pageSize;

数据表的关联关系

关联关系介绍

MySQL是一个关系型数据库，不仅可以存储数据，还可以维护数据与数据之间的关系——通过在数据表中添加字段建立外键约束

数据与数据之间的关系
- 一对一关联
- 一对多关联
- 多对一关联
- 多对多关联

一对一关联

一个实体对应另一个实体

方案一主键关联——两张数据表中主键相同的数据为相互对应的数据
方案二唯一外键——在任意一张表中添加一个字段外键约束与另一张表主键关联，并且将外键添加唯一约束

一对多与多对一关联

一个实体对应多个实体

方案在多的一段添加外键，与一的一端主键进行关联

多对多关联

多个实体对应其他多个实体

方案额外创建一张关系表来维护多对多关联——在关系表中定义两个外键，分别与两个数据表的主键进行关联

外键约束

将一个列添加外键约束与另一张表的主键(唯一列)进行关联之后，这个外键约束的列添加的数据必须要在关联的主键字段中存在

案例：学生表与班级表 (在学生表中添加外键与班级表的主键进行关联)

先创建班级表

create table classes(
  class_id int primary key auto_increment,
  class_name varchar(40) not null unique,
  class_remark varchar(200)
);

创建学生表(在学生表中添加外键与班级表中的主键进行关联)

## 方式一
## 在创建表的时候，添加外键约束
create table students(
  stu_num char(8) primary key,
  stu_name varchar(20) not null,
  stu_gender char(2) not null,
  stu_age int not null,
  cid int,
  constraint FK_STUDENTS_CLASSES foreign key(cid) references 
  classes(class_id) ## 添加外键约束
);

## 方式二
## 在创建表之后，为cid添加外键约束
create table students(
  stu_num char(8) primary key,
  stu_name varchar(20) not null,
  stu_gender char(2) not null,
  stu_age int not null,
  cid int,
);
alter table students add constraint FK_STUDENTS_CLASSES 
  -> foreign key(cid) references classes(class_id);

#删除外键约束
alter table students drop foreign key FK_STUDENTS_CLASSES;

向班级表添加班级信息
向学生表中添加学生信息

添加学生的时候，设置给cid外键列的值必须在其关联的主表classes的class_id列存在

级联

当学生表中存在学生信息关联班级表的某条记录时，就不能对班级表的这条记录进行修改，删除。

如果一定要修改，需：

将被引用的记录内外键cid设置为null

再修改原记录内的主键class_id

将cid中设置为null的记录重新修改关联为你所需要的新的class_id

我们可以使用级联操作来实现：
在添加外键时，设置 级联修改 和 级联删除
1
2
3
4
5
6
7
8
## 删除原有的外键
alter table students drop foreign key FK_STUDENTS_CLASSES;

## 重新添加外键，并设置级联修改和级联删除
## ON UPDATE 级联修改 ON DELETE 级联修改
alter table students add constraint FK_STUDENTS_CLASSES 
    -> foreign key(cid) references classes(class_id)
    -> ON UPDATE CASCADE ON DELETE CASCADE;
删除class_id 的班级信息，学生表引用此班级信息的记录也会被同步删除

连接查询

通过连接查询从多张数据表提取数据

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

inner join 内连接

left join 左连接

right join 又连接

内连接 INNER JOIN

语法

select ... from tableName1 inner join tableName2

笛卡尔积

笛卡尔积(A集合&B集合)：使用A中的每个记录一次关联B中每个记录，笛卡尔积=A总数*B总数

如果直接执行，会获取两种数据表中的数据集合的笛卡尔积(一次使用tableName1 表中的每一条记录去匹配tableName2中的每一条数据)

内连接条件

两张表用inner join连接查询之后生成的笛卡尔积数据中很多都是无意义的，我们需要消除这个缺点

使用 on 设置两张表连接查询的匹配条件

-- 使用where设置过滤条件：先生成笛卡尔积再从笛卡尔积中过滤数据(效率很低)

select * from  students INNER JOIN classes 
    -> where students.cid = classes.class_id;

-- 使用ON设置连接查询条件：先判断连接条件是否成立，如果成立两张表的数据进行
-- 组合生成一条结果记录
select * from students INNER JOIN classes 
    -> ON students.cid = classes.class_id;

结果：只获取两种表中匹配条件成立的数据，任何一张表再另一张表如果没有找到对应匹配则不会出现在查询结果中

左连接 LEFT JOIN

显示左表中的所有数据，如果再右表中存在与左表记录满足匹配条件的数据，则进行匹配，如果左表不存在匹配数据，则显示为NULL

语法

select * from lefttable LEFT JOIN righttable 
    -> ON 匹配条件 [where 条件];

右连接 RIGHT JOIN

显示右表中的所有数据，如果再左表中存在与右表记录满足匹配条件的数据，则进行匹配，如果右表不存在匹配数据，则显示为NULL

语法

select * from lefttable RIGHT JOIN righttable 
    -> ON 匹配条件 [where 条件];

数据表别名

如果在连接查询的多张表中存在相同名字的字段，我们可以使用表名，字段名 来进行区分，如果表名太长则不便于SQL语句的编写，我们可以使用数据表别名

示例

select s.*,c.class_name
from students s
INNER JOIN classes c ## 空格+别名
ON s.cid = c.class_id;

子查询 / 嵌套查询

先进行一次查询，第一次查询的结果作为第二次查询的源条件

示例一

## 先查询括号里面的
select * from  students where cid = (
select class_id from classes where class_name='Java2105'
);

示例二

-- 如果子查询返回的结果时多个值(单列多行)，条件使用IN
select * from  students where cid 
IN (select class_id from classes where class_name LIKE 'Java%');

示例三

-- (多行多列)先查询cid=1班级中的所有学生信息，将这些信息作为一个整体虚拟表
-- 在基于这个虚拟表查询性别为男的学生信息('虚拟表'需要别名)
select * from (select * from students where cid=1) t 
where t.stu_gender='男';

存储过程

存储过程介绍

SQL 指令执行过程
1. 编写并允许SQL指令
2. 通过数据库连接将SQL指令发送到数据库
3. 数据库接收SQL指令至SQL引擎
4. SQL引擎编译并执行SQL指令
5. 将执行SQL的结果通过数据库连接返回给用户端
6. 用户端显示查询结果
- 存在的问题
  1. 如果我们需要重复多次执行相同的SQL，SQL执行都需要通过连接传递到MySQL，并且需要经过编译和执行的步骤
  2. 如果我们需要连续执行多个SQL指令，第二个SQL指令往往需要使用第一个SQL指令执行的结果作为参数
SQL指令执行过程存在问题解决方法——存储过程(按需配置)

将能够完成特定功能的SQL指令进行封装(SQL指令集)，编译之后存储在数据库服务器上，并且为之去一个名字，客户端可以通过名字直接调用这个SQL指令集，获取执行结果
- 步骤
  1. 通过存储过程的名字进行调用
  2. 向数据库传输存储过程的名字
  3. 再数据库中通过存储过程的名字查询对应的指令集
  4. 执行指令集
  5. 返回执行结果
  6. 显示查询结果
- 优点
  1. SQL指令无需客户端编写，通过网络传送，可以节省网络开销，同时避免SQL指令再网络传输过程中被恶意修改
  2. 存储过程经过编译创建并保存在数据库中的，执行过程无需重复的进行编译操作，对SQL指令的执行过程进行了性能提升
  3. 存储过程中多个SQL指令之间存在逻辑关系，支持流程控制语句(分支，循环)，可以实现更为复杂的业务
- 缺点
  1. 存储过程是根据不同的数据库进行编译，创建并存储在数据库中；当我们需要切换到其他的数据库产品时，需要重新编写针对于新数据库的存储过程
  2. 存储过程受限于数据库产品，如果需要高性能的优化会成为一个问题
  3. 在互联网的项目中，如果需要数据库的高(连接)并发访问，使用存储过程会增加数据库的连接执行时间(因为我们将复杂的业务交给了数据库进行处理)

创建存储过程

语法

## 创建一个存储过程实现加法运算
create procedure <procName>(
    [IN param1 int, IN param2 int,...,OUT param3 int]
)
begin
    -- SQL语句
end;

案例

## 创建一个存储过程实现加法运算
create procedure test1(IN a int, IN b int, OUT c int)
begin
    SET c = a+b;
end;

调用存储过程

set @m = 0; ## 定义变量@m
call test1(3, 4, @m) ## 将@m传递给c
-- 显示变量值
select @m from DUAL;

存储过程中变量的使用

局部变量和用户变量

局部变量

定义在存储过程中的变量，只能在存储过程内部使用

-- 创建一个存储过程，计算 输入参数的平方 与 输入参数/2 之和
create procedure test2(IN a int, OUT c int)
begin
    declare x int default 0; ## 定义x int类型 默认值为0
    declare y int default 0;
    set x = a*a; ## 设置x的值
    set y = a/2;
    set r = x+y;
end;

select x from DUAL; ## 无结果，局部变量不会出现在DUAL内

局部变量要定义在存储过程中，而且必须定义在存储过程开始

declare <变量名> <变量类型> [default 默认值];

用户变量

相当于全局变量，定义的用户变量可以通过select @attrName from DUAL查询到

-- 用户变量直接使用set设置
-- 用户变量需要舒勇@来修饰
set @n=1;
call test2(6,@n);
select @n from DUAL;

注意

因为用户变量相当于全局变量，可以在SQL指令以及多个存储过程中共享，用户变量过多会导致程序不易理解，在开发中少使用

设置变量值

无论时局部变量还是用户变量，都是使用set 关键字修改值

set @n=1;
-- 或
set y = a+b;

在SQL语句中赋值给变量

create procedure test3(OUT c int)
begin
    select count(stu_num) INTO c from students;
    ## 使用select...INTO关键字段，赋值给c
end;

存储过程的参数

MySQL存储过程的参数：IN / OUT / INOUT

IN 输入参数

在调用存储过程中传递数据给存储过程的参数(在调用的过程中必须为具有实际值的变量或者字面值)

create procedure test4(
    IN snum char(8), IN sname varchar(20), IN gender char(2), IN age int,
    IN cid int, IN remark varchar(255))
begin
    insert into students(stu_num,stu_name,stu_gender,stu_age,cid,remark)
    values(snum,sname,gender,age,cid,remark);
end;

OUT 输出参数

将存储过程中产生的数据返回给过程调用者

INOUT 输入输出参数

输入与输出都在同一个参数上传递

create procedure test6(INOUT str varchar(20))
begin
    select stu_name INTO str from students where stu_num=str;
end;

set @name='aa';
call test6(@name);
select @name from dual;

存储过程中的流程控制

在存储过程中支持流程控制语句用于实现逻辑的控制

分支语句

if-then-else

-- 单分支
if conditions then
    -- SQL
end if;

-- 双分支
if confitions then
    -- SQL1
else
    -- SQL2
end if;

case

case 变量名
when value1 then
    -- SQL1
when value2 then
    -- SQL2
else
    -- SQL3
end case;

循环语句

while

declare i int;
set i = 0;
while i < num do
    --SQL
    set i = i + 1;
end while;

repeat

declare i int;
set i = 0;
repeat
    --SQL
    insert into classes(class_name,remark) 
    values(CONCAT('Python',i),'....');
    set i = i+1
until i > num end repeat;

loop

declare i int;
set i = 1;
myloop:loop
    --SQL
    if i = num then
        leave myloop;
    end if;
end loop;

存储过程管理

查询存储过程

存储过程是属于某个数据库的，也就是说当我们将存储过程创建在某个数据库之后，只能在当前数据库调用此存储过程

查询：查询某个数据库中有哪些存储过程

-- 根据数据库名查询数据库内存储过程
show procedure status [where db=数据库名];

-- 查询某数据库中特定存储过程的实现细节
show create procedure 数据库名.存储过程;

修改存储过程

修改存储过程的特征 / 特性

alter procedure <proc_name> 特征1 [特征2 特征3 ...];

存储过程的特征参数
- CONTAINS SQL 表示子程序包含 SQL 语句，但不包含读或写数据的语句
- NO SQL 表示子程序中不包含 SQL 语句
- READS SQL DATA 表示子程序中不包含 SQL 语句
- MODIFIES SQL DATA 表示子程序中包含写数据的语句
- SQL SECURITY {DEFINER | INVOKER} 指明谁有权限来执行
  - DEFINER 表示只有定义者自己才能够执行
  - INVOKER 表示调用者可以执行
- COMMENT 'string' 表示注释信息

删除存储过程

-- drop 删除数据库中的对象
-- delete 删除数据表中的数据
dro procedure proc_test1

游标

创建一个存储过程，需要返回查询语句查询到的多条数据

concat_ws('~', x, xx, aa) = x~xx~aa 拼接

游标的概念

游标可以用来一次取出查询结果中的每一条数据，逐个读取

游标的使用步骤

声明游标

声明游标语法

declare cursor_name CURSOR FOR <查询语句>;

打开游标
- 语法
  1
  open cursor_name;

使用游标

语法

set i=0;
while i<num do
    FETCH cursor_name INTO bname,bauthor,bprice; ## 循环取出
    set i=i+1;
end while;

关闭游标
- 语法
  1
  close cursor_name;

触发器

触发器介绍

触发器是一种特殊的存储过程，能够完成特定功能，存储在数据库服务器上的SQL片段，但是触发器无需调用，当对数据表中的数据执行DML操作时自动触发这个SQL片段的执行，无需手动调用

在MySQL中只有执行insert\delete\update操作才能触发触发器的执行

触发器的使用

创建触发器

语法

create trigger tri_name
<before|after>   --定义触发时机
<insert|delete|update>  --定义DML类型
ON <table_name>
for each row  --声明为行级触发器(只要操作一条记录就触发触发器一次)
--SQL语句

--例
create trigger tri_test1
before insert on students
for each row
--SQL语句

查看触发器

语法
1
show triggers;

删除触发器

语法
1
drop trigger tri_name;

NEW 与 OLD

我们可以通过NEW 和 OLD 关键字在触发器中货期触发器的MDML操作的数据

NEW：在触发器中用于获取insert操作添加的数据，update操作修改后的记录

OLD：在触发器中用于获取delete操作删除前的数据，update操作修改前的数据

NEW

insert操作

create trigger tri_test1
before insert on students
for each row
insert into stulogs(time, log_text) 
values(now(), concat('添加', NEW.stu_name, '学生信息'));

update操作

create trigger tri_test2
after update on students for each row
insert into stulogs(time, log_text) 
values(now(), concat('修改', NEW.stu_num, NEW.stu_name));

OLD

delete操作

create trigger tri_test2
after delete on students for each row
insert into stulogs(time, log_text) 
values(now(), concat('删除', OLD.stu_num, '学生信息'));

update操作

create trigger tri_test2
after update on students for each row
insert into stulogs(time, log_text) 
values(now(), concat('修改', OLD.stu_name, NEW.stu_name));

总结

优点

触发器时自动执行的，当对触发器相应的表执行响应的DML操作时立即执行
触发器可以实现表中的数据的级联操作(关联操作)，有利于保证数据库的完整性
触发器可以对DML操作的数据进行更为复杂的合法性校验

缺点

使用触发器实现的业务逻辑如果出现问题，将难以定位，后期维护难
如果大量使用，容易导致代码结构杂乱，增加了程序的复杂性
当触发器操作的数据量比较大时，执行效率会大大降低

使用建议

在互联网项目中，应避免使用触发器
对于并发量不大的项目可以选择使用存储过程，但是在互联网引用中不提倡使用存储过程(原因：存储过程将实现业务逻辑交给数据库处理，一则增加了数据库的负载，二则不利于数据库的迁移)

视图

视图的概念

视图就是由数据库中一张表或者多张表根据特定的条件查询出的数据构造成的虚拟表

视图的作用

安全性

如果我们直接将数据表授权给用户操作，那么用户可以操作数据表中的所有数据，假如我们想要对数据表中的部分数据进行保护，可以将公开的数据生成视图，授权用户访问视图；用户通过查询视图可以获取数据表中公开的数据，从而达到将数据表中的部分数据对用户隐藏
简单性

如果我们需要查询的数据来源于多张数据表，可以使用多表连接查询来实现；我们通过视图将这些连表查询的结果对用户开放，用户则可以直接通过查询视图获取多表数据，操作更便捷

创建视图

语法

create view view_name
AS
-- SQL语句

--例
create view view_test1
AS
select * from students where stu_genter='男'; ## 单表

create view view_test2
AS
select s.stu_name,b.book_name,borrow_num
from books b inner join records r inner join student s
on b.book_id=r.bid and r.snum=s.stu_num;  ## 多表

--查询视图
select * from view_test2;

视图数据的特性

查询操作

如果在数据表中添加了新的数据，而且这个数据满足创建视图时的查询语句条件，通过查询视图也可以查询出新增的数据；当删除原表中男足查询条件的数据时，也会从视图中消失
新增数据

如果在视图中添加数据，数据会被添加到原数据表
删除数据

如果在视图中删除数据，数据也将在原表中被删除
修改数据

如果在视图中修改数据，数据也将在原表中被修改

使用建议

对复杂查询简化操作，并且不会对数据进行修改的情况下可以使用视图

查询视图结构

desc view_test2;

修改视图结构

--方式1
create OR REPLACE view view_test1 # 关键段 OR REPLACE 若存在则覆盖
AS
-- SQL语句

--方式2
alter view view_test1
AS
-- SQL语句

删除视图

删除数据表时会同时删除数据表中的数据，但是删除视图时不会影响原表中的数据

drop view view_test1

索引

数据库是用来存储数据的，在互联网应用中数据库中存储的数据可能会很多(大数据)，数据表中数据的查询速率会随着数据量的增长逐渐变慢，从而导致响应用户请求的速度变慢——用户体验差

索引的介绍

索引就是用来提高数据表中数据的查询效率的

将数据表中某一列/某几列的值取出来构造成便于查找的结构进行存储，生成数据表的目录，当我们进行数据查询时，则现在目录中进行查找得到对应数据的地址，然后再到数据表中根据地址快速获取数据记录，避免全表扫描

索引的分类

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

主键索引：在主键字段创建的索引

唯一索引：在唯一约束字段创建的索引，可以为NULL

普通索引：没有限制，在普通字段创建的索引

组合索引：两个及以上的字段创建的索引

说明
1. 在创建数据表时，将字段声明为主键时，会自动在主键字段创建主键索引
2. 在创建数据表时，将字段声明为唯一键，会自动在唯一字段创建唯一索引

创建索引

语法

## unique 唯一索引，无 普通索引
create [unique] index index_索引名 on 表名(字段名);

## 组合索引，需要多个字段值
create index index_索引名 on 表名(字段名1，2);

全文索引

通过此索引进行全文检索操作

语法

create fulltext index index_索引名 on 表名(字段名);

索引的使用

索引创建完成之后无需调用，当根据创建索引的列进行数据查询的时候，会自动使用索引

组合索引需要根据创建索引的所有字段进行查询时触发

在命令行中查看查询语句的查询规划
1
explain <SQL语句>\G;

查看索引

--方案1 ，
show create table tb_testindex\G;
--方案2 查询数据表的索引
show indexes from tb_testindex;
--方案3 查询索引
show keys from tb_testindex;

删除索引

--索引是建立在表的字段上的，不同的表可能会出现相同名称的索引，
--因此删除索引时需要指定表名
drop index index_test3 on tb_testindex;

总结

优点

索引大大降低了数据库服务器在执行查询操作时扫描的数据

索引可以避免服务器排序，将随机IO变成顺序IO

缺点

索引是根据数据表列创建的，当数据表中数据发生DML操作时，索引页也需要更新

索引文件也会占用磁盘空间

注意

数据表中数据不多时，全表扫描可能更快，不要使用索引

数据量大但是DML操作很频繁，不建议使用

不要在数据重复高度高的列上创建索引

创建索引之后，要注意查询SQL语句的编写，避免索引失效

数据库事务

数据库事务介绍

我们把完成特定的业务的多个数据库DML操作步骤称为一个数据库事务

数据库事务的特性

ACID特性

原子性

Atomicity ：一个事务中的多个DML操作，要么同时执行成功，要么同时失败

一致性

Consistency：事务执行之前和事务执行之后，数据库中的数据是一致的，完整性和一致性不能破坏

隔离性

Isolation：数据库允许多个事务同时进行(张三借Java书同时允许李四借)，多个并行的事务之间不能相互影响

持久性

Durability：事务完成之后，对数据得到操作是永久的

MySQL事务管理

自动提交与手动提交

在MySQL中，默认DML指令的执行是自动提交的，当我们执行一个DML指令之后，自动同步到数据库

事务管理

开启事务，关闭自动提交

当开始事务第一个操作之前执行start transaction 开启事务

依次执行事务中的每个DML操作

如果在执行的过程中的任何位置出现异常，则执行rollback 回滚事务

如果所有操作都执行成功，则在最后执行commit 提交

start transaction; ## 开始事务
--SQL语句
rollback; ## 错误则回滚
--SQL语句
commit; ## 全部正确则提交

事务隔离级别

MySQL数据库事务隔离级别

读未提交

T2可以读取T1执行但未提交的数据，可能会导致出现脏读

脏读：一个事务读取到了另一个事务中未提交的数据
读已提交

T2只能读取T1已经提交的数据，可以避免脏读，但可能会导致不可重复读(虚读)

虚读：在同一个事务中，两次查询操作读取到数据不一致
可重复读

T2执行第一次查询后，在事务结束之前其他事务不能修改对应数据，避免了不可重复读(虚读)，但可能导致幻读

幻读：T2对数据表中的数据进行修改然后查询，在查询之前T1像数据表中新增了一条数据，就导致T2以为修改了所有数据，但却查询除了与修改不一致的数据(T1事务新增的数据)
串行化

同时只允许一个事务对数据表进行操作，避免了脏读，虚读，幻读问题

隔离级别	脏读	不可重复读(虚读)	幻读
read uncommitted	√	√	√
read committed	×	√	√
repeatable read	×	×	√
serializable	×	×	×

设置数据库事务隔离级别

通过设置数据库默认的事务隔离级别来控制事务之间的隔离性，也可以通过客户端与数据库连接设置来设置事务间的隔离性(在应用程序中设置)

MySQL数据库默认的隔离级别为可重复读

查看MySQL数据库默认的隔离级别
1
select @@transaction_isolation;

设置MySQL默认隔离级别

set session transaction isolation level <read committed>;

数据库设计

数据库设计流程

根据应用系统功能，分析数据实体(实体，就是要存储的数据对象)
- 电商系统：商品，用户，订单
提取实体的数据项(数据项，就是实体的属性)
- 商品(商品名称，商品图片)
根据数据库设计三范式规范视图的数据项

检查实体的数据是否满足数据库设计三范式，如果实体的数据项不满足三范式，可能会导致数据的冗余，从而引起数据维护困难，破环数据一致性等问题
绘制E-R图(实体关系图，直观的展示实体与实体之间的关系)
数据库建模
- 三线图进行数据表设计
- PowerDesigner
- PDMan
建库建表(编写SQL指令创建数据库，数据表)
添加测试数据，SQL测试

数据库设计三范式

第一范式(1NF)：要求数据表中的字段(列)不可再分
第二范式(2NF)：不存在非关键字段对关键字段的部分依赖
第三范式(3NF)：不存在非关键字段不存在传递依赖

绘制E-R图

E-R实体关系图，用于直观的体现实体与实体之间的关联关系(一对一，一对多，多对一，多对多)

E-R图基本图例

示例

三线图

统一数据实体的表结构

数据库建模

E-R 图数据表设计建库建表

PowerDesigner建模工具导出数据表

PDMan建模工具

PowerDesigner使用

概念数据模型 - E-R 图
逻辑数据模型 - 体现实体主外键关联
物理数据模型
- 可以对物理模型进行微调
- 可以通过物理数据模型生成建库建表的SQL语句
面向对象模型
- 可以根据语言设置，生成实体类

在企业项目开发，通常是不会使用建模工具来生成数据表，实体类的，因为生成的代码规范不合乎代码需求