[大数据]数据库学习笔记，值得收藏，不会的时候翻一翻

数据库（MYSQL）学习笔记

1、数据库概述

• 数据库（ DataBase ）：按照一定的格式来储存数据的一些文件的组合，就是储存数据的仓库。
• 数据库管理系统（ DataBaseMangement ）：是专门用来管理数据库中的数据的，可以对数据库中的数据进行增删改查。常见数据库管理系统：MySQL、Oracle、MS等。
• SQL：结构化语言，通过SQL语句来实现对数据库中的数据进行增删改查。

上面三者的关系是：

数据库管理系统 --》执行—》SQL —》操作—》数据库

学习MySQL需要先下载MySQL的环境，可以去官网下载

MySQL数据库的官网

2、数据库常用的cmd命令

1. 启动和停止MySQL服务

语法：

? net start MySQL服务的名称

? net stop MySQL服务的名称

其它的服务也是可以通过该方式来启动和停止的。

2. 登录数据库

语法：mysql -uroot -p

然后会让你输入数据库的密码，输入密码后就登录了数据库。

3. 退出mysql

语法：exit（是在mysql登录的情况下的命令）

4. 查询数据库中有哪些数据库

语法：show databases; （注意要以分号结尾）

5. 使用某个数据库

语法：use 数据库的名称;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-s87heOAN-1632382028348)(https://i.loli.net/2021/09/13/6PJUuQZY83Ngwle.png)]

上面表示正在使用名称为mybatis的数据库。

6. 创建数据库

语法：create database test; （创建一个test名称的数据库）

7. 查看某个数据库下的表

语法：show tables;

8. 导入某个资源

语法：source 资源的绝对路径;

注意：在资源的绝对路径中不能有中文！

9. 查看某个表格的结构

语法：desc 表名;

10. 查看数据库的版本号

语法：select version();

11. 终止某个命令的输入

语法：\c

注意：以上的命令不区分大小写，以分号来结束一天sql命令。

3、表的理解

表是数据库的最基本单元，数据库使用表来存储数据的，表比较直观。

任何一张表都有行和列：

? 行：被称为数据、记录。

? 列：被称为字段，每一个字段都有字段名（要见名知意）、数据类型和约束。

4、 SQL语句的分类

• DQL：数据查询语言，凡是带有select关键字的都是查询语句。
• DML：数据操作语言，凡是对数据库的增、删、改。insert、delete、update。
• DDL：数据定义语言，主要操作表的结构，凡是带有create、drop、alter。
• TCL：事务控制语言，包括事务提交（commit）、事务回滚（rollback）。
• DCL：数据控制语言，包括授权和撤销权限。

5、DQL语句（查询语言）

5.1、简单查询

1. 查询一个字段

语法：select 字段名 from 表名;

2. 查询两个及以上的字段

语法：select 字段1,字段2，字段3 from 表名;

需要查询的字段之间使用逗号隔开。

3. 查询所有字段

语法一：把所有的字段名依次写上。

语法二：select *from 表名;

? * 表示所有字段，但是这种方式性差一些，它会先转换成字段名，然后再执行sql语句，所以在实际开发中，不推荐使用该方法。

4. 给查询的列起别名

语法一：select 字段1,字段2 as 字段2的别名 from 表名;

语法二：select 字段1,字段2 字段2的别名 from 表名; 可以省略as，使用空格隔开也是可以的。

如果你取的别名中也存在空格或者是中文，那必须使用单引号把别名括起来。

注意：只是将显示的结果为别名，并不是把表中的字段名改为别名，select语句只会查询数据，永远不会修改表中的内容。

5. 字段可以参与数学表达式的运算

语法：select 字段1,字段2 * 12 as 字段2 from 表名;

上面表示将字段2的所有值全部乘以12，然后取别名为字段2。数学上的其它表达式也是可以的，加、减、乘、除。

5.2、条件查询

? 条件查询就是查询符合条件的数据，不符合条件的就不查询出来。

语法：select 字段名 from 表名 where 条件表达式;

1. 条件表达式

• = 等于

select 字段名1 from 表名 where 字段名2 = 条件值;

查询满足 字段名2 等于 条件值的所有数据。

• <> 或 != 不等于

select 字段名1 from 表名 where 字段名2 != 条件值;

• < 小于

select 字段名1 from 表名 where 字段名2 < 条件值;

• <= 小于等于

select 字段名1 from 表名 where 字段名2 <= 条件值;

• 大于和大于等于和上面的类似
• between … and … 在什么之间，相当于大于等于和小于等于两个一起使用

select 字段名1 from 表名 where 字段名2 between 值1 and 值2;

注意：使用between and的时候必须是左小，右大。

• is null 值为空

select 字段名1 from 表名 where 字段名2 is null;

注意：不能使用 = 来判断是否为空。必须使用 is null 来判断。

• is not null 值不为空

select 字段名1 from 表名 where 字段名2 is not null;

注意：null表示是什么都没有，不表示任何值。

• and 并且

select 字段名1 from 表名 where 字段名2 = 值1 and 字段3 = 值2;

条件就是，满足字段2的值为值1并且字段3的值为值2。

• or 或者

select 字段名1 from 表名 where 字段名2 = 值1 or 字段3 = 值2;

注意：and和or同时出现时，and的优先级较高，我们可以通过添加括号来改变优先级。

• in 包含，相当于多个or，并不是一个区间，括号的值相当于一个数组，使用逗号隔开。

select 字段名 from 表名 where 字段名 in(值1,值2,值3);

• not in 表示不在这些值的数据

select 字段名 from 表名 where 字段名 not in(值1,值2);

表示值不是值1和值2的数据记录。

5.3、模糊查询

使用 like 关键字来进行模糊查询，支持 % 和 _ 匹配。

%：匹配多个任意字符。

_：匹配任意一个字符。

语法：select 字段名 from 表名 where 字段名 like '%张%';

上面表示查询字段名的值包含张的所有数据，’_张%’ 表示第二个字是张的数据。

注意：在查询时，遇到特殊字符，例如 _ 和 % 需要使用转义字符来转义。

例如：select 字段名 from 表名 where 字段名 like '%\_%';

上面表示查询包含下划线的数据。

5.4、排序查询

语法：select 字段名 from 表名 order by 字段名1;

表示查询的数据按照字段名1的值进行排序查询，默认是升序排列。

如果需要进行降序排列，只需在排序的字段名后面添加 desc。

select 字段名 from 表名 order by 字段名1 desc;

也可以指定升序排列，但是默认是升序排列，再排列的字段名后添加 asc 。

select 字段名 from 表名 order by 字段名1 asc;

多个字段的排序：

如果需要将多个字段进行排序，也是可以使用 order by 完成的。

语法：select 字段名 from 表名 order by 字段1 asc,字段2 asc;

上面表示先按照字段1进行升序排列，如果遇到字段1的值相等的时候，在按照字段2的值进行升序排列。前面的字段起主导作用。

根据字段的位置进行排列：

语法：select 字段名 from 表名 order by 2;

上面表示按照表中的第二列的字段进行排序，但是在实际开发中不建议使用该方法，可能表中的字段位置可能发生变化。

拓展：

select 

?	字段

from

?	表名

where 

?	条件表达式

order by

?	字段

上面的执行顺序是

? from --》where —》 select —》order by（排序总是在最后执行的）

5.5、数据处理函数（单行处理函数）

单行处理函数：一个输入对应一个输出，只处理一条数据。

1. lower 转小写函数

语法：select lower(字段) as 别名 from 表名;

上面表示把字段的值全部转变为小写，然后查询出来，as是起别名的作用。

2. upper 转大写函数，使用方法和转小写类似。

3. substr 取子串

语法：select substr(字段,起始下标,截取的长度) from 表名;

注意：数据库中的起始下标是从 1 开始的。

4. concat 字符串拼接函数

语法：select concat(字段1,字段2) from 表名;

表示把字段1和字段2的值进行拼接处理，然后在查询出来。

5. length 取长度的函数

语法：select length(字段1) from 表名;

表示查询到字段1的长度的值，然后查询出来。

6. trim 去除前后空格

语法：select 字段 from 表名 where 字段 = trim(值);

这个一般用于对传过来的数据进行去除前后空格处理，然后在进行查询。

7. round 四舍五入函数

语法：select round(字段,保留的位数) from 表名;

注意：如果保留的位数为负数，则整数的位数也会四舍五入。

例如：select round(123.78,0) from 表名; 保留后就是 --》124

select round(123.78,-1) from 表名; 保留后是–》120，整数为也可以舍去。

8. rand 产生随机数

语法：select rand()*100 from 表名;

表示参数100以内的随机数，rand()产生的是大于0小于1的一个小数。

9. ifnull 对于空数据的处理函数

语法：select ifnull(字段,被看成哪个值) from 表名;

表示如果字段的值为空，则被看成指定的值来计算。

注意：在数据库中，任何值和null进行计算，结果都是null。

10. case …when…then…when…then…else…end 匹配某个字段，当什么的时候怎样，当什么的时候右怎摸样。

语法：

select 字段,字段,(case 字段1 when '值1' then 操作1 when '值2' then 操作二 else '值3' end) as 别名 from 表名;

表示当字段1是值1的时候进行操作1，当字段1为值2的时候进行操作2，其它使用else。

11. str_to_date 将字符串varchar转换成date类型。

语法：str_to_date(字符串,转换的格式);

该函数通常使用在 insert 语句中，用于date数据的插入。

例如：

insert into 表名
	(日期字段)
values 
	(str_to_date('01-11-1999','%d-%m-%Y'));

表示将 01-11-1999 这个日期插入到数据库中，1999年11月01日。

12. date_format 将date类型转换成varchar类型

语法：date_format(date,转换的字符串格式);

该函数通常用于查询date类型按照指定的格式把日期输入。

例如：

select date_format(字段,指定格式) from 表名;

如果不使用该函数来查询date类型的字段，数据库会自动将date类型按照数据库默认的格式进行自动转换。

默认格式：%Y-%m-%d

5.6、数据处理函数（多行处理函数）

? 输入多行，最终输出一行。一共有5个函数，count、sum、max、min、avg

注意：分组函数在使用时需要进行分组，然后才能使用，如果没有分组，默认整张表就是一组。

语法：select 函数名(字段) from 表名;

1. max 求最大函数，查询某个字段中的最大值。

select max(字段) from 表名;

2. min 求最小函数，查询某个字段中的最小函数。

select min(字段) from 表名;

3. sum 求和函数，查询某个字段的值的和。

select sum(字段) from 表名;

4. avg 求平均值函数，查询某个字段的值的平均值。

select avg(字段) from 表名;

5. count 统计函数，统计某个字段中数据有多少条。

select count(字段) from 表名;

分组函数注意事项：

1. 分组函数自动忽略 null 值得数据记录，不需要对 null 的数据处理。
2. count(*) 函数表示统计表中所有数据的条数，而count(某个字段)会自动忽略 null 值得数据。
3. 分组函数不能直接使用在 where 语句中。因为分组函数使用前必须先进行分组，但是where语句得执行顺序比分组 order by 先执行。
4. 所有的分组函数可以组合起来一起使用。

5.7、分组查询

? 在实际开发中，某些数据是需要先进行分组，然后对每一组得数据进行操作，这时候我们就需要进行分组查询。

语法：select 字段,sum(字段名) from group by 字段;

表示先按照字段进行分组，分完组后进行分组函数查询。

注意：

1. 分组函数后的 select 只能查询到参与分组的字段和使用分组函数得到的值。如果存在其它字段在其它数据库中可能会报错。
2. 分组查询可以对多个字段进行分组查询

select 字段1,字段2 from 表名 group by 字段1,字段2;

多个字段使用逗号隔开，依次按照指定的字段进行分组。

having关键字：

? having 用于对分完组的数据进行筛选，和group by联合使用，不能单独使用，也不能代替where。能用where就用where，where不行的在使用having。

语法：select 字段 from 表名 where 条件1 group by 字段 having 条件2;

5.8、distinct 关键字

? 使用 distinct 关键字去除查询的重复数据。

语法：select distinct 字段 from 表名;

把查询的数据，然后去除重复的数据。

注意：distinct 只能使用在 select 的后面，字段的前面。

不能这样写 select 字段1,distinct 字段2 from 表名;

如果distinct后面有多个字段，则是多个字段的联合，然后去除重复的数据。

5.9、单表查询总结

sql 语句中各个关键字的执行顺序。

select ... from ... where....group by....having...order by;

先执行from，从某个表中查询数据。

再执行where，过滤一些数据。

再执行group by，对数据进行分组。

再执行having，对分组后的数据再过滤。

再执行select，查询到满足条件的数据。

最后指定order by，对查询到的数据进行分组。

5.10、连接查询

? 两张表或多张表联合起来查询数据称为连接查询。

连接查询的分类：

• 内连接：两张表之间没有主次之分，没有匹配就不显示。
  1. 等值连接
  2. 非等值连接
  3. 自连接
• 外连接：
  1. 左外连接：left 左边的表为主表，显示表中的所有数据，没有匹配的也会显示。
  2. 右外连接：right 右边得表为主表，显示表中的所有数据，没有匹配的也会显示。
• 全连接：两张表都为主表，查询两张表的所有数据。

笛卡尔积现象：

? 当两张或多张表进行连接查询时，没有任何的条件限制，最终的查询结果条数是两张表条数的乘积，这个现象称为笛卡尔积现象。

select 字段 from 表1,表2; —> 查询的数据就是表1数据的条数乘以表2数据的条数。

如何避免笛卡尔积：

? 在进行多表联查的时候，对查询的条件进行加限制。

select 字段 from 表1,表2 where 表1.字段 = 表2.字段;

上面的方法虽然避免了笛卡尔积现象，但是表之间的比配次数还是没有减少，就是表1和表2数据记录数的乘积。

select 字段 from 表1 表1的别名,表2 表2的别名 where 表1.字段 = 表2.字段;

注意：表的连接次数越多，查询的效率越低，尽量避免表之间的连接。

内连接的等值连接：

语法：

select
	字段
from
	表1
inner join
	表2
on 
	连接的表达式;

表示将表1和表2按照连接条件进行连接查询。如果还需过滤，可以在后面添加where语句。

注意：inner 可以省略，建议写上，可读性好。

内连接的非等值连接:
指的是连接条件不是一个等量关系，称为非等值连接。

内连接的自连接：
就是一张看成两张表，然后自己和自己进行联表查询。

外连接：

语法：

select
	字段
from 
	表1
left outer join
	表2 
on 
	条件表达式

表示表1作为主表，查询表1中的所有数据，匹配表2中的数据。

左外连接和右外连接是可以相互转换的，只是表的位置不同，更具需求来写sql语句。

注意：outer可以省略，建议写上，可读性较强。

外连接查询的数据数，一定大于或等于内连接查询的数据数。

一条sql语句中，内连接和外联接可以同时存在。

多表连接查询：

语法：

select
	字段
from 
	a
join
	b
on
	a和b的连接条件
join
	c
on
	a与c的连接条件
join 
	d
on
	a与d的连接条件

表示a和b表先进行连接查询，然后查询的结果和c进行连接查询，查询的结构再和d表进行连接查询。

5.11、子查询

? 指select语句中嵌套select语句，被嵌套的select语句称为子查询。

where语句中出现子查询：

语法：

select
	字段
from 
	表名
where
	(select 字段 from 表名)

表示where中的select语句查询的结构来作为where的条件表达式。

from中的子查询：
from中的子查询是把查询的结构当成一张临时的表来进行查询。

语法：

select
	字段
from 
	(select 字段 from 表名) as 临时表的别名
where 
	条件表达式

表示from中的select作为一张临时表来作为外面一层select的查询表。

注意：临时表必须起别名，不然无法在where语句中使用表。临时表中的字段也是要取别名的。

5.12、union关键字

? 用于将查询的结构集合并起来，称为一个结果集。

语法：

select 字段 from 表1 where 条件表达式
union
select 字段 from 表2 where 条件表达式

表示会把从表1查询到的数据和从表2查询的结果合并为一个结果集，然后显示出来。

注意：使用union两个的查询的字段数目必须保持一样，不然会报错。

==union的查询效率较高，在进行联表查询时，相当于将两个表各自查询一遍，不会像连接查询那样匹配符合条件的数据。=

5.13、limit关键字

? 将查询结果集中的一部分取出，通常用于分页查询。

语法：

select
	字段
from
	表名
where
	条件表达式
order by
	排序方式
limit startindex,length;

limit 后面需要指定数据开始的下标，和查询的长度。它的下标是从0开始的。

limit 在order by后执行。

例如

limit 0,10;

表示从0的下标开始，查询10条语句。

通用的分页查询：

? 实现分页查询，一般是从前端传入一个页码和每一页的数据条数，然后执行相应的sql语句。

所以，limit的下标公式就为

• startindex = （页码-1）*每一页的数据条数。
• length = 每一页的数据条数。

6、DDL语句（定义语言）

6.1、表的创建

语法：

create	table 表名(
	字段1 数据类型,
    字段2 数据类型,
    字段3 数据类型
);

注意：最后一个字段的数据类型的后面不能有逗号，否则报错。

建议表名以 t_ 开头，字段名见名知意，这样可读性较强。

快速复制表：

语法：

create table 新表名 as select *from 要复制的表名;

表示将一个查询的结果当成一张新的表，创建出来，表中的数据也是可以保存下来的。也可以只查询一部分数据，然后复制。

6.2、数据库中的数据类型

• varchar：可变长度的字符串，根据实际的数据来动态分配空间，节省空间，最长255。
• char：定长的字符串，不管实际长度多少，都会分配固定的长度来储存数据，可能会导致空间的浪费，最长255。

当数据为固定长度的时候使用char，这样的速度较快，不用动态分配空间。当数据为可变长度的时候，使用varchar这样可以避免空间的浪费。

• int：数字中的整型，等同于java中的int，最长11。
• bigint：数字中的长整型，等同于java中的long。
• float：单精度的浮点型数据。
• double：双精度的浮点型数据。
• date：短日期类型。
• datetime：长日期类型。
• clob：字符大对象，最多存储4G的字符串，超过255的字符串使用该类型来存储，通常存储一篇文章、说明等。
• blob：二进制大对象，专门用来储存图片、声音、视频等，往该类型的字段插入数据的时候通常使用IO流来完成。

date和datetime的区别：

date：短日期，只包括年月日，不包括时分秒。默认格式为：%Y-%m-%d。

datetime：长日期，包括年月日时分秒。默认格式为：%Y-%m-%d %h-%i-%s。

now()函数：
用户获取当前系统时间，默认时datetime类型。

例如：

insert into 表名
	(当前系统时间字段)
values
	(now());

6.3、删除表

语法：

drop table if exists 表名;

6.4、删除表中的数据

语法：

truncate table 表名;

表示删除表中的所有数据，这种删除是直接删除物理内存的删除。

• 优点：删除效率很高。
• 缺点：不支持事务回滚，删除后不能恢复。

7、DML 语句（操作语言）

7.1、数据的插入（insert语句）

语法：

insert into 表名
	(字段1,字段2,字段3)
values
	(值1,值2,值3);

• 字段的顺序可以和表中的字段顺序不一样，但是值的顺序要和前面指定得顺序对应。
• 如果缺少一些表中得字段，那些缺少得字段则会为默认值，如果没有设置默认值，则指为null。
• 如果所有得字段都不写，默认是插入全部得字段，后面得值要全部填上。

例如：insert into 表名 values(值1,值2,值3);默认是插入全部得字段。

给字段指定默认值：

? 在创建表得时候给字段指定默认值

create	table 表名(
	字段1 数据类型,
    字段2 数据类型 default 默认值,
    字段3 数据类型
);

表示给字段2指定了一个默认值。

insert插入日期类型：

语法：

insert into 表名
	(日期字段)
values 
	(str_to_date('01-11-1999','%d-%m-%Y'));

数据库中的格式：

%Y：表示年

%m：表示月

%d：表示天

%h：表示时

%i：表示分

%s：表示秒

注意：如果插入的日期字符串正好是 年-月-日 格式，则不用使用 str_to_date 函数，数据库会自动进行数据转换。

insert into 表名
	(日期字段)
values 
	('1999-11-01'));

会自动将1999-11-01转换成日期类型。

insert一次插入多条记录：

语法：

insert into 表名
	(字段)
values
	(一条数据),
	(两条数据),
	(三条数据);

将查询结果插入到表中：

语法：

insert into 插入的表名 select *from 查询的表名;

表示将查询的结果插入到对应的表中。

注意：查询结果的字段和插入表的字段需要保持一致。不然报错！

7.2、数据的修改（update语句）

语法：

update 表名
set 
	字段1=值1,字段2=值2
where
	条件表达式;

表示将字段1和字段2的值修改为值1和值2。

注意：在修改时没有条件来限制的的话，它会修改表中的全部数据。

7.3、数据的删除（delete语句）

语法：

delete from 表名 
where 
	条件表达式;

注意：如果没有条件表达式，则会删除表中的所有数据，可以用于清空表中的数据。

删除表中的数据：
语法：delete from 表名;

这种方式删除表中的数据，只是删除表中的数据，但是硬盘中的真实内存空间不会被释放。

• 优点：支持事务回滚，删除后可以后悔。
• 缺点：删除效率低。

8、约束（constraint）

? 在创建表时，我们可以给表中的字段添加一些约束，来保证数据的完整性、有效性。

常见约束：非空约束（not null）、唯一性约束（unique）、主键约束（primary key）pk、外键约束（foreign key）fk、检查约束（check）mysql不支持，oracle支持。

8.1、非空约束（not null）

create	table 表名(
	字段1 数据类型 not null,
    字段2 数据类型,
    字段3 数据类型
);

这样字段1的值就是不能为空，如果不设置不为空，数据库默认是可以为空的。

8.2、唯一性约束（unique）

? 唯一性约束的字段的值不能重复，但是可以为 null ，多个 null 是可以的。

create	table 表名(
	字段1 数据类型 unique,
    字段2 数据类型,
    字段3 数据类型
);

表示字段1的值就不能重复，插入重复的数据就会报错！

8.3、多个字段联合唯一约束

语法：

create	table 表名(
	字段1 数据类型,
    字段2 数据类型,
    字段3 数据类型
    unique(字段1,字段2)
);

表示将字段1和字段2联合的唯一性约束。两个都一样则不能插入，有一个不一样就可以插入。

not null 和 unique 联合使用：

语法：

create	table 表名(
	字段1 数据类型 not null unique,
    字段2 数据类型,
    字段3 数据类型
);

表示字段1不能为空并且具有唯一性。

在mysql中，如果同时设置了not null 和 unique ，则该字段就自动转换成主键约束。 但是在oracle中不能。

8.4、主键约束（primary key）

? 主键是每一条数据的唯一标识，不能为空，也不能重复。

语法：

create	table 表名(
	字段1 数据类型 primary key,
    字段2 数据类型,
    字段3 数据类型
);

表示字段1就是这样表的主键，不能为空也不能重复。

任何一张表都应该有主键，没有主键的表是无效的表。

联合主键：

? 多个字段联合起来作为联合主键。

语法：

create	table 表名(
	字段1 数据类型,
    字段2 数据类型,
    字段3 数据类型
    primary key(字段1,字段2)
);

表示字段1和字段2作为联合主键，两个都相同就不能插入，有一个不同就可以插入。

注意：任何表，主键约束只能存在一个，不能存在多个，联合主键是将多个字段联合起来作为一个主键。

建议主键值的类型为int、bigint、char，不建议使用varchar，因为主键一般为定长的。

主键的分类：
主键可以分为自然主键和业务主键。

• 自然主键：主键值是一个自然数，和业务没有任何关系。
• 业务主键：主键值和业务紧密关联，例如使用银行卡号作为主键值。

在实际开发过程中，通常使用自然主键作为一个表的主键，因为主键主要就是来防止数据重复的，如果使用业务主键，一旦业务发生变化，主键值也会发生改变，所以尽量使用自然主键来作为表的主键。

主键自增的机制：

语法：

create	table 表名(
	字段1 int primary key auto_increment,
    字段2 数据类型,
    字段3 数据类型
);

表示添加了 auto_increment 后，主键值会根据值类型自动增加。一般添加了自增的字段类型为int、bigint。

8.5、外键约束（foreign key）

我们使用两张表来测试外键约束：

t_class表：

create table if not exists t_class(
    c_no int(11) primary key, // 被引用的字段
    c_name varchar(255)
);

t_student表：

create table if not exists t_student(
	stu_no int(11) primary key,
    stu_name varchar(255),
    c_no int, // 被设置外键的字段
	foreign key (c_no) references t_class(c_no)  // 这是设置外键的语法
);

我们创建了两个表，t_class是班级的信息表，t_student是学生的信息表，在t_student中创建了一个外键c_no，引用t_class表中的c_no字段。t_class作为父表，引用表t_student作为子表。

外键约束：就是被设置了外键约束的字段，只能从引用表中指定的字段取值，不能为其它的值。

上面的两张表，t_student中的c_no值，只能是t_class中c_no的值。

外键的注意事项：

1. 上面的两张表中，t_class是父表，t_student是子表。
2. 删除表时，要先删除子表，然后删除父表。
3. 创建表时，要先创建父表，然后创建子表。
4. 删除数据时，要删除子表中的数据，然后才能删除父表中的数据。
5. 插入数据时，要插入父表中的数据，然后才能在子表中插入数据。

上面的顺序不能改变，否则会报错！

6. 外键的值可以为空。
7. 子表引用父表中的某个字段，父表中的这个字段不一定为主键，但是一定唯一，如果不唯一，则不知道引用的是哪个值。

9、存储引擎

? 存储引擎是mysql中的特有的术语，在其它数据库中没有。实际上存储引擎就是一个表存储数据的方式，不同的存储引擎，表数据的存储方式不一样。

通常在创建表的时候就指定表的存储引擎

CREATE TABLE `books` (
  `bookId` int NOT NULL AUTO_INCREMENT COMMENT '书的id',
  `bookName` varchar(100) NOT NULL COMMENT '书名',
  `bookCounts` int NOT NULL COMMENT '数量',
  `detail` varchar(200) NOT NULL COMMENT '描述',
  PRIMARY KEY (`bookId`),
  KEY `bookID` (`bookId`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8mb3

上面的最后面的小括号的后面就是指定存储引擎，engine=InnoDB

mysql默认的存储引擎为InnoDB

charset是指定表的编码格式，mysql默认的编码是ust-8。

查看mysql支持哪些存储引擎：

? 使用命令：show engines \G

mysql支持9大存储引擎，但是由于版本，可能有些引擎不支持。

mysql常用存储引擎：

• MyISAM：它使用三个文件夹来表示每个表。
  1. 格式文件：存储表结构的定义（mytable.frm）
  2. 数据文件：存储表的数据（mytable.MYD）
  3. 索引文件：存储表的索引（mytable.MYI）

它可以转换为压缩、只读表来节省空间。但是不支持事务，安全性低。

• InnoDB：安全性高，但是性能低。

  1. 每一个数据库表在数据库目录中以 .frm 格式表示。
  2. 支持事务
  3. 在mysql服务器崩溃时，提供自动恢复。

• MEMORY：内存存储引擎，数据存储在内存中，且行的长度固定。

  1. 每个表均以 .frm 格式表示。
  2. 表的数据及索引全部存放在内存中，断电消失。
  3. 不能包含blob类型，text类型，因为太大。

它的查询效率是最高的，不需要和硬盘交互，但是不安全，关机后就消失了。

10、事务（重点）

? 一个事务就是一个完整的业务逻辑，是一个最小的工作单元，不可再分。

例如：

? 一个账户a向b账户转账100元，a账户减少100元，b账户增加100元。这个就是一个完整的业务逻辑。

事务时最小的工作单元，必须要莫同时成功，要莫同时失败，不能成功一半。

只有DML语句和事务有关，insert、update、delete，只有这三个语句是操作数据的。一但涉及数据，一定要注意安全。

正是因为做某件事的时候，需要多条DML语句才能完成，所以需要事务的存在。实质上事务就是多条DML语句同时成功或同时失败。

10.1、事务如何实现

事务的执行流程：

开启事务----》执行DML语句（包括多条的insert、update、delete。。。。。）-------》事务结束

再执行事务的过程中，每一条的DML语句都会记录到事务性活动的日志文件中。在执行的过程中，我们可以提交事务，也可以回滚事务。

• 提交事务：清空事务性活动的日志，将数据全部彻底持久化到数据库表中。提交事务标志着事务以成功结束。
• 回滚事务：将之前的DML语句全部撤销，清空事务性活动日志文件，标志着事务以失败结束。

怎么提交事务和回滚事务：

提交事务：使用命令 commit;

回滚事务：使用命令 rollback; （回滚事务只能回滚到上次提交事务的位置）

开启事务：使用命令 start transaction;

在mysql中事务的默认是自动提交事务的，就是执行一条DML语句就会自动提交一次事务。但是在实际的开发中需要多条的DML语句来完成一件事情，所以不能使用mysql中的默认事务机制。

开启手动提交机制：

? 开启事务后，就开启了手动提交事务的机制。

事务回滚演示：

mysql> select *from books;    // 先查询books表中的数据
+--------+--------------------+------------+------------------------+
| bookId | bookName           | bookCounts | detail                 |
+--------+--------------------+------------+------------------------+
|      1 | Java               |         25 | 从入门到放弃           |
|      2 | MySQL              |         25 | 从删库到跑路           |
|      3 | Linux              |          5 | 从进门到进牢           |
|     24 | HTML基础教程       |         12 | 叫你如何入门web前端    |
|     25 | JavaScript基础教程 |         23 | 叫你如何入门JavaScript |
+--------+--------------------+------------+------------------------+
5 rows in set (0.00 sec)

mysql> start transaction;   // 开启事务，手动进行提交和回滚事务
Query OK, 0 rows affected (0.00 sec)

mysql> delete from books where bookId = 25;  // 删除表中bookId为25的书
Query OK, 1 row affected (0.01 sec)

mysql> select *from books;  // 删除后的数据
+--------+--------------+------------+---------------------+
| bookId | bookName     | bookCounts | detail              |
+--------+--------------+------------+---------------------+
|      1 | Java         |         25 | 从入门到放弃        |
|      2 | MySQL        |         25 | 从删库到跑路        |
|      3 | Linux        |          5 | 从进门到进牢        |
|     24 | HTML基础教程 |         12 | 叫你如何入门web前端 |
+--------+--------------+------------+---------------------+
4 rows in set (0.01 sec)

mysql> rollback;        // 事务回滚
Query OK, 0 rows affected (0.01 sec)

mysql> select *from books;    // 事务回滚后，被删除的数据就恢复了
+--------+--------------------+------------+------------------------+
| bookId | bookName           | bookCounts | detail                 |
+--------+--------------------+------------+------------------------+
|      1 | Java               |         25 | 从入门到放弃           |
|      2 | MySQL              |         25 | 从删库到跑路           |
|      3 | Linux              |          5 | 从进门到进牢           |
|     24 | HTML基础教程       |         12 | 叫你如何入门web前端    |
|     25 | JavaScript基础教程 |         23 | 叫你如何入门JavaScript |
+--------+--------------------+------------+------------------------+
5 rows in set (0.00 sec)

事务提交演示：

mysql> select *from books;     // 先查询表中的数据
+--------+--------------------+------------+------------------------+
| bookId | bookName           | bookCounts | detail                 |
+--------+--------------------+------------+------------------------+
|      1 | Java               |         25 | 从入门到放弃           |
|      2 | MySQL              |         25 | 从删库到跑路           |
|      3 | Linux              |          5 | 从进门到进牢           |
|     24 | HTML基础教程       |         12 | 叫你如何入门web前端    |
|     25 | JavaScript基础教程 |         23 | 叫你如何入门JavaScript |
+--------+--------------------+------------+------------------------+
5 rows in set (0.00 sec)

mysql> start transcation; 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'transcation' at line 1
mysql> start transaction;         // 开启事务
Query OK, 0 rows affected (0.00 sec)

mysql> delete from books where bookId = 25;   // 删除一本书
Query OK, 1 row affected (0.01 sec)

mysql> commit;                    // 然后提交事务
Query OK, 0 rows affected (0.01 sec)

mysql> select *from books;       // 删除后查询书中的数据
+--------+--------------+------------+---------------------+
| bookId | bookName     | bookCounts | detail              |
+--------+--------------+------------+---------------------+
|      1 | Java         |         25 | 从入门到放弃        |
|      2 | MySQL        |         25 | 从删库到跑路        |
|      3 | Linux        |          5 | 从进门到进牢        |
|     24 | HTML基础教程 |         12 | 叫你如何入门web前端 |
+--------+--------------+------------+---------------------+
4 rows in set (0.01 sec)

mysql> rollback;           // 事务回滚到最后一次的事务提交，也就是上面的事务提交
Query OK, 0 rows affected (0.00 sec)

mysql> select *from books;    // 所以被删除的内容不能恢复
+--------+--------------+------------+---------------------+
| bookId | bookName     | bookCounts | detail              |
+--------+--------------+------------+---------------------+
|      1 | Java         |         25 | 从入门到放弃        |
|      2 | MySQL        |         25 | 从删库到跑路        |
|      3 | Linux        |          5 | 从进门到进牢        |
|     24 | HTML基础教程 |         12 | 叫你如何入门web前端 |
+--------+--------------+------------+---------------------+
4 rows in set (0.00 sec)

10.2、事务的特性

• 原子性（A）：

  事务是最小的工作单元，不能再分。

• 一致性（C）：

  所有的事务要求，在同一个事务中，所有的操作必须同时成功或同时失败。

• 隔离性（I）：重点

  a事务和b事务之间有一定的隔离，a和b同时操作一张表时，会有线程安全问题。

• 持久性（D）：

  事务结束的最终保障是事务提交，就相当于将没有保存到硬盘中的数据保存到硬盘中。

10.3、事务的隔离级别

? 事务的隔离存在一定的级别，级别越高，事务于事务之间的隔离性就越强。一共有4个级别。

1. 读未提交（read uncommitted） 最低级别
2. 读已提交（read committed）
3. 可重复读（repeatable read）
4. 序列化（serializable） 最高级别

读未提交：
事务a可读取到事务b未提交的数据。

这种级别存在读到脏数据的现象（事务b未提交的数据），一般这种隔离级别是理论上的，一般没人使用，大多数的数据库默认隔离级别都是读已提交以上。

读已提交：
事务a只能读取到事务b已提交的数据。

这种级别解决的脏读的现象，但是也存在不可重复读取数据的缺点。（不可重复读取数据：开启事务后，第一次读到的数据是3条，当前事务未结束可能存在第二次读取数据，可能第二次读取到的数据是4条，3不等于4，这就称为不可重复读取数据）这种级别读取到的数据绝对真实，oracle数据库默认的使用该隔离级别。

可重复读：
事务a开启后，不管多久，在每次事务a读取的数据都是一致的，即使事务b修改了数据，并提交。事务a读取 到的数据还以第一次读取到的数据。

这个隔离级别解决了不可重复的的问题，但是存在幻影读的现象，不够真实。mysql中默认的级别就是可重复读的级别。

序列化：
只有事务a执行完以后，事务b才能执行。

这种级别是最高的级别，解决了所有的问题，但是效率极低。这种级别表示事务的排队，不能并发。类似于java中线程安全 synchronized 线程同步（事务同步），这个读取到的数据最真实。

验证各隔离级别：

1. 验证读未提交：

? 因为mysql默认的隔离级别是 repeatable read，所以我们需要先修改mysql的隔离级别。

步骤：

• 查看数据库的隔离级别

使用命令：select @@transaction_isolation; (我是用的是8以上的版本，其它版本可能不一样)

• 修改数据库的隔离级别未读未提交

使用命令：set global transaction isolation level read uncommitted ;

注意：修改以后需要重新登录数据库才能生效。

我们使用两个cmd窗口来模仿两个事务，以一个user表，只有一个name字段来测试：

结论：当处于读未提交级别时，事务B没有提交的数据，在事务A中也能够查询到。

2. 验证读以提交：

   依然需要修改数据库的隔离级别。

步骤：

• 修改级别

set global transaction isolation level read committed;

• 依然使用两个cmd窗口来模拟：

结论：当数据库处于读已提交的级别时，当事务B插入一条数据时，但是未提交事务，事务A是不能查询到事务B未提交的数据。只有当事务B提交事务后才能查询到提交的数据。

3. 验证可重复读

   依然需要修改数据库的隔离级别。

步骤：

• 修改级别

set global transaction isolation level repeatable read;

• 依然使用两个cmd窗口来模拟：

结论：当数据库在可重复的级别时，事务A查询的到数据始终是一致的，是第一次查询到的数据，即使事务B修改了并提交了数据。

4. 验证序列化

   依然需要修该数据库的隔离级别。

步骤：

• 修改级别：

set global transaction isolation level serializable;

• 依然使用两个cmd窗口来模拟：

结论：当数据库处于序列化的隔离级别时，当事务A在向数据库中插入一条数据，但是没有提交，事务B也操作user表，查询数据库中的数据，事务B的查询语句会一直卡在那里，等待事务A提交事务，只有事务A提交了事务，事务B才会执行对应的SQL语句。相当于是事务排队，两个事务操作同一张表时，只能一个事务一个事务进行，只有等前面的一个事务提交后，后面的事务才能执行。

11、索引 index（重点）

索引是在数据库字段上添加的，是为了提高查询的速度而存在的一种机制。一张表的一个字段可以添加索引，多个字段联合起来也可以添加索引。索引相当于是一本书的目录，是为了缩小扫描的范围。索引检索的查找效率较高。

select *from 表名 where 字段1 = 值;

如果字段1上没有添加索引，在进行查找时，mysql会将字段1上的所有值全部都扫描一遍，然后查询到符合条件的数据。

mysql的主要两种查找方式就是两种：

• 全表扫描：例如上面的例子。
• 根据索引扫描：

在mysql数据库中，索引是需要排序的，这个排序和TreeSet数据结构相同，TreeSet的底层是一个自平衡的二叉树。在mysql当中索引是一个B-Tree数据结构。遵循左小右大的原则，采用中序遍历方式遍历取数据。

11.1、索引的实现原理

使用一张表user来举例：

注意事项：

1. 在任何数据库中主键会自动添加索引对象，例如上面的id自动就有一个索引对象。另外，如果一个字段中存在unique约束也会自动添加索引对象
2. 在任何数据库中，任何一张表中的任何一条数据在硬盘的记录上都有一个硬盘的物理存储地址。
3. 在mysql中，索引是一个单独的对象，不同的存储引擎索引的存在方式不一样。在MyISAM存储引擎中，索引存储在 .MYI 文件中。在InnoDB存储引擎中，索引存储在一个逻辑名称为tablespace当中的。在MEMORY引擎中，索引存储在内存中。
4. 不管索引储存在哪里，它都是一个树的形式存在的（自平衡二叉树）

索引原理：

1. 在id字段上，将所有值按照平衡二叉树储存好，并且会把每一条数据的物理储存地址也会储存。
2. 当mysql发现查询的字段上有索引，它就会通过索引对象来查询。

例如：

? select *from user where id = 56;

通过id字段的索引定位到 56 ，缩小了扫描的范围。通过 56 获取到这条数据的物理位置 0x6666，然后就能查询出来这条数据记录。

索引的使用条件：

1. 数据量庞大（这个要根据硬件来说，每个硬件的性能不一样）
2. 该字段经常出现在where后面，以条件的形式存在，也就是说这个字段经常被扫描。
3. 该字段很少的DML语句（insert、delete、update），因为数据改变后，索引需要重新排序。

建议不要随便添加索引，因为索引需要维护的，太多的话也会降低系统的性能。建议一般的查询通过主键和unique约束来查询。这样的效率比较高。

11.2、索引的创建和删除

索引的创建：

语法：

create index 索引的名称 on 表名(添加索引的字段);

例如：给user表中的name字段添加索引

create index user_name_index on user(name);

索引的删除：

语法：

drop index 删除的索引名称 on 表名;

例如：把user表中的name字段的索引删除

drop index user_name_index on user;

查看一条sql语句是否使用了索引：

语法：

explain 执行的sql语句;

例如：

explain select *from user where name = 'zhangsan';

11.3、索引失效

? 有时候一些sql语句会导致索引失效，即使添加了索引，查询的时候不会经过索引。

1. 模糊查询时以 % 开头

例如：

select *from user where name = '%hangsan';

所以在模糊查询的时候尽量避免给以 % 开头。

但是，在mysql版本8以上的是支持以 % 开头的索引查询的。为了统一，尽量不使用以 % 开头的索引查询。

2. 使用 or 来查询

? 使用 or 的时候也会使索引失效，如果使用的 or 来进行索引查询，则要求两边的字段都必须有索引。如果一个字段没有索引，那莫另一个字段上的索引就会失效。

3. 使用复合索引时，没有使用左侧字段查找，索引失效

复合索引：两个或多个字段联合起来创建的一个索引。

创建复合索引：

语法：

create index 索引名称 on 表名(字段1,字段2);

将字段1和字段2联合起来创建一个索引。

例如：给user表中的id和name创建一个复合索引。

create index user_id_name_index on user(id,name);

如果使用id来查询的话，就可以使用索引，但是使用name来查询的话，就不会经过索引。

4. 在where中，索引列参加了运算，索引失效

例如：查询薪水是9999的员工

select *from staff where sallary + 1 = 10000; 

这样的查询会使索引失效。

5. 在where中，索引字段使用了函数，索引失效

例如：

select *from user where upper(name) = 'ZHANGSAN';

这样的查询也会使索引失效。

11.4、索引的分类

• 单一索引：单个字段添加索引。
• 复合索引：两个或多个字段联合来添加一个索引。
• 主键索引：主键上添加索引。
• 唯一性索引：具有unique约束的字段上添加索引。

注意：唯一性较弱的字段上添加索引用处不大。

12、视图view

? 视图就是站在不同的角度去看待同一份数据。

12.1、创建和删除视图

创建视图：

语法：

create view 使视图的名称 as select语句;

例如：创建一个user表的视图。

create view user_view as select *from user;

删除视图：

语法：

drop view 视图的名称;

注意：as 后面必须是 select 语句。

12.2、视图的作用

? 我们可以面向视图对象进行增删改查，对视图对象的增删改查，会导致原表的数据被操作。通过操作视图，会影响到原表的数据。方便、利于维护、简化开发

视图在原表中的作用：

? 视图主要是用来简化SQL语句的。

如果一条SQL语句很复杂，然而这条语句需要在多个地方使用，我们就可以创建视图对象，然后对试图对象进行操作，需要使用该条SQL语句就直接使用视图对象就可以了。

例如：

create view 视图名称
as
	很长的select语句;

在多次使用该复杂的SQL语句时，我们就直接对视图对象操作，也会更改原表中的数据。

在以后的实际开发中，使用视图就可以当成一个表来使用，可以对视图进行增删改查。视图不存在内存中，而是存在硬盘中，不会消失，可以当成一张表来使用。

13、DBA命令

数据的导出：
通常用于数据的备份。

语法：

// 导出所有表的数据
mysqldump 数据库名>导出的保存路径 -uroot -p密码
// 导出某个表的数据
mysqldump 数据库名 表名>保存的路径 -uroot -p密码

注意：上面的命令必须在windows的dos命名窗口上运行。

数据的导入：
用于将数据导入到数据库中。

语法：

// 登录数据库
mysql -uroot -p密码
// 创建一个数据库
// s
// 使用命令
source 导入的路径;

?

?