数据库

数据库:存储数据的仓库,这个仓库是我们将数据的整合形式的一种比喻。实际上就是一堆文件，这些文件存储了具有特定格式的数据。

有了数据库就需要一种管理工具，让数据符合我们的预期为我们服务，我们将这种管理工具称为数据库管理系统DBMS（DatabaseManagement）。DBMS是专门提供了用来管理操作数据库中的数据的方法，对数据库的管理操作有增删改查(CURD)。

常见的数据库管理系统有：

Mysql、Oracle、MS SqlServer、DB2等

SQL语言的分类

DQL、DML、DDL、TCL、DCL

1.DQL数据查询语言**(凡是语句中带有select关键字的都是查询语言)**

select…

2.DML数据库操作语言(凡是对表中的数据进行增删改的都是DML)

DML主要操作的是表中的数据

insert 增、delete删、update改

3.DDL数据定义语言(凡是语句中带有create、drop、alter的都是DDL)

DDL主要操作的是表结构 ，不是表中的数据

create新建、drop删除、alter修改

4.TCL事物控制语言，主要有：

事物提交：commit

事物回滚：rollback

上面的这些语言在后面的文章中都会学到，所以请一定要看到最后面。

mysql常用基本命令

这里先介绍一些最常用，最基本的命令

1.show databases；查询当前所有的数据库

现在看到的数据库都是系统自带的

2.create if not exists 数据库名；创建数据库

**3.use 数据库名称 ；**使用数据库

当看到Database changed时表示正在使用当前数据库

**4.cource 文件路径名；**插入sql文件 到指定数据库中

注意在插入sql文件的时候一定要完成前面第二步和第三步

百度网盘 请输入提取码

提取码：bn4m

这里要声明一下这个sql文件的来源，这个sql文件是我在B站上面学习杜老师的MYSQL中，他课程里面的质料，我这里拿来引用一下。这里推荐大家多去看看杜老师的视频，讲的非常好。

(1)首先找到sql文件的目录位置：

(2)在DS窗口中，将桌面上的sql文件直接拖入DS窗口

这里需要注意sql文件路径不能有中文。当拖入以后他会自动生成文件所在的路径，这时我们只需要回车就能将sql文件批量导入mysql中。

只要没看到error 就说明导入成功

**5.show tables；**查看数据库当中的所有表

这里就我们刚才导入sql文件中的所有表。

6.select database()；查看当前属于哪个数据库

单表查询

SQL里面的查询

一：简单查询

1.desc 表名；查询表结构

可以看到上面就是dept表中的表信息，里面有字段deptno——部门编号、dname——部门名称、loc——部门地理位置；type表示数据类型(具体后面了解)int——整形，字符长度为2、varchar——字符串类型，长度为14。

2.select * from 表名；或者select 字段1,字段2…表名；查询整张表

3.select 字段 from 表名；查询单个字段，若要查找多个字段 ，字段与字段之间要用英文逗号隔开

4.select 字段 as 别名；给查询的字段起别名

注意，若要起中文的别名，或者别名之间有空格，则要用英文单引号将别名括起来。as可以省略。

注意：字段可以使用数学表达式

例如：查询员工表，显示员工姓名和对应的工资，要求员工工资都扩大12倍。

上面是全表的信息，注意看sal——薪资这里列的信息，下面会在它的基础上发生改变。

下面是按照要求查询出来的表。

二：条件查询

条件查询：不是将表中的数据全部查出来，而是根据条件查询出符合调教的数据。

语法格式：

select
    字段1，字段2，字段3...
from
    表名
where
    条件；
1
2
3
4
5
6

1.= 等于

例如：查询员工姓名为KING的员工信息

2.<>或!= 不等于

例如：查询薪资不等于5000的员工的姓名和薪资

3.< 小于 > d大于

例如：查询薪资小于2500的员工的姓名和部门编号

例如：查询薪资大于3000的员工姓名和入职时间

4.<= 小于等于 >=大于等于

5.and 并且

例如：查询工资大于等于1500并且工作部门编号为30的员工的姓名、薪资、部门编号

6.or 或者

例如：查询工作岗位为manager和salesman的员工的员工编号、姓名、工作

注意：and和or同时出现的时候，and的优先级高于or

例如：查询工资大于2500，并且部门编号为10或20的部门的员工的姓名、薪资、部门编号。

像这样写就是错误的，因为and的优先级比or大，上面这条语句的意思就是将把工资大于2500并且部门编号为10的员工的信息查出来，或者把部门编号为20的员工的信息查出来。显然这不符合我们的要求，若要按要求查询出结果，只需要将后面的语句用括号括起来。向下面这样。

7.in 包含 相当于多个or

例如：查询工作岗位是manager和salesman的员工的信息

注意括号里面不是区间，而是将括号里面条件查询出来；

8.not 可以取非，只要用在is或in中

例如：查询津贴不为空的员工的姓名和津贴

9.between…and…两个值之间，等同于>= and <=；

例如：查询工资在1000到2000之间的员工的姓名、工作、薪资

三：模糊查找

模糊查询的关键字like，支持%或下划线匹配

% ：匹配任意多个字符

例如：查找处名字中含有O的员工姓名

_：匹配任意一个字符

例如：查找第二个字母是A的员工姓名

SQL里面的排序

一：升序

语法：

select
    字段1，字段2...
from
    表名
order by
    排序字段 asc;
1
2
3
4
5
6

例如：查询所有员工的薪资，升序(mqsql默认是按升序排序)

二：降序

语法：

select
    字段1，字段2...
from
    表名
order by
    排序字段 desc;
1
2
3
4
5
6

例如：将员工的薪资按照降序排序并输出员工的姓名

三：多个字段排序

语法：

select
    字段1，字段2...
from
    表名
order by
    排序字段 asc/desc，排序字段 asc/desc...；//第一个排序字段在前起主导作用，只有第一个排序字段相等的时候，才会考虑启用第二个排序字段以及后面的字段
1
2
3
4
5
6

例如：查询员工名字和薪资，要求按照薪资升序，如果薪资一样按照姓名降序

从开始到现在，我们主要学习了以下4个关键字，关键字的执行顺序非常重要

select
    ...
from
    ...
where
    ...
order by
    ...
1
2
3
4
5
6
7
8

它们的执行顺序是1.from——2.where——3.select——4.order by(排序总是在最后面在执行)

可以理解为从一张表中，根据条件将数据选出来然后进行排序；

SQL中的函数

一：数据处理函数

数据处理函数又叫做单行处理函数，单行处理函数的特点：一个输入对应一个输出。

常见的单行处理函数有以下几种：

1.lower 转换小写

例如：将员工表中的姓名全部用小写代替

2.upper 转换大写

例如：将学生的姓名转换为小写

3.substr 取子串（substr（被截取的字符串，起始下标，截取的长度））

例如：截取员工姓名的前两个字母

4.length 取长度

例如：计算员工姓名的长度

5.trim 去空格

例如：去除字符‘ lisi ’的空格

6.case…when…then…when…then…else…end；在一则事例中，当条件1满足是，采取什么行动，当条件2满足时，采取什么行动，其他情况则结束(了解类容，实际编写中很少遇到这种需求)

例如：当员工的工作岗位是manager的时候，工资上调10%，当做工岗位是salesman的时候，工资上调50%，其他正常。

select
ename,job,sal old_sal,
(case job when 'manager' then sal*1.1 when 'salesman' then sal*1.5 else sal end)
as new_sal
from emp;
1
2
3
4
5

**7.select 后面跟字面量/字面值；**如果在一份表中查询某个字段，select后面跟的是字面量/字面值的话，查询出来的结果都是该字面量/之面值

例如：查询员工表中姓名为king的员工

正常的写法是向上面这样，但是一些人不小心他可能会写成下面这样。

这样写就错了。

8.round 四舍五入

语法：

select
    字段1，字段2... round(被操作的字段，保留位数)
from
    表名
1
2
3
4

例如：对员工的工资取整

例如：对员工的工资保留到十位

9.rand() 生成随机数

例如：生成100以内的随机数

10. ifnull(数据，被当做哪个值) 可以将null转换成一个具体的值

ifnull是空处理函数，专门用来处理空的。在所有数据库当中，只要有null参与的数学运算，最终的结果都是null

例如：计算每个员工的年薪(年薪 = （月薪+月补助）*12)

如果向上面这样写，津贴为null的员工，计算出来的年薪就会为null。所以为了避免这种状况，就卡可以采用ifnull函数

二：分组函数

**分组函数又叫做多行处理函数：**多行处理函数是指输入多行，最终输出一行

注意在使用分组函数的时候必须先进性分组，然后才能使用。如果没有对数据进行分组，整张表默认为一组。

1.count 计数

例如：计算员工的数量

也可以这样写：select count(*) from emp;结果和上面一样。

2.sum 求和

例如：计算员工工资和

3.avg 求平均值

例如：计算员工的平均工资

4.max 求最大值 min求最小值

例如：求员工工资的最大值和最小值

分组函数需要注意的事项：

1.分组函数自动忽略null，你不需要对null进行处理

例如：统计津贴不为null的员工数量

2.分组函数中count(*)和count(具体字段)的区别：

count(具体字段)：表示统计该字段下所有不为null的元素的总数，count(*)：统计表中总行数

3.分组函数不能直接使用在where的子句当中

例如：找出比最低工资高的员工信息

不能像下面这样写：

原因是因为在使用分组函数之前必须先分组，而在执行where语句的时候并没有分组。

4.分组函数可以一起使用

三：日期函数

1.format 数字格式化

语法：

format(数字, '格式')
1

例如：将员工表中的工资格式化

2.str_to_date：将字符串varchar类型转换成date类型

语法：

str_to_date('字符串日期', '日期格式')?
1

新建一个表(新建表和插入字段在后面详细说)

drop table if exists t_student;
create table t_student(
id int,
name varchar(255),
graduate_time date
);
1
2
3
4
5
6

插入学生信息

insert into t_student(id,name,graduate_time) values(1,'zhangsan','21-06-2021');
1

上面直接报错了，说插入的日期值错误。说明我们插入的日期格式不符合mysql的标准，在mysql中日期的格式为：‘%Y-%m-%d’

为了解决上面的问题我们可以使用字符串转日期函数

当然我们在插入数据的时候按照mysql的标准来插入数据就不会报错，例如像下面这样。

3.date_format；将日期转化成指定的字符串

语法：date_format(日期类型数据, ‘日期格式’)

这个函数通常使用在查询日期方面，设置展示的日期格式。

例如：将学生毕业的时间按照日——月——年的格式输出

分组查询

分组查询：对需求进行分组，然后对每组的数据进行操作

分组查询语法：

select
    ...
from
    ...
group by
    ...
1
2
3
4
5
6

例如：计算每个部门的工资和

思路：先按照工作岗位分组，然后对工资求和

上面的程序的解释为：先从emp表中查询数据，根据job分组，然后对每组的数据进行sum(sal)

例如：计算每个工作岗位的平均工资

例如：计算每个工作岗位的最高薪资

select ename,job,sum(sal) from emp group by job;

可以看到上面的ename字段和后面的查询信息一点关联都没有，所以是多余的，这种语法在oracle当中就是错误的。

这里有一个结论：在一条select语句当中，如果有group by语句的话，select后面只能跟：参加分组的字段，以及分组函数。其他一律不跟。

联合分组查询

例如：找出每个部门，不同工作岗位的最高薪资

将前面学过的关键字全部组合在一起，看看他们的执行顺序

select
    ...
from
    ...
where
    ...
group by
    ...
order by
    ...

执行顺序
1.from
2.where
3.group by
4.select
5.order by
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

上面关键字的执行顺序非常重要，必须要记住

having

having的功能和where一样，都是筛选条件。

使用having可以对分完组之后的数据进一步过滤，having不能单独使用，having不能替换where，having必须和group by联合使用

例如：找出每个工作岗位最高薪资，要求显示最高薪资大于3000的

第一步：找出每个工作岗位的最高薪资

然后只要根据条件筛选出工资大于3000的即可，这里用到having

其实上面的问题可以优化：

先将工资大于3000的都选出来，然后再分组

优化策略：where和having，优先选择where，where实在完成不了了，再选择having

例如：找出每个部门平均薪资，要求显示平均薪资高于2500(这个案列就不能用到where)

如果你像下面这样操作，结果是错误的。

因为根据我们一再强调的关键字执行顺序，上面语句翻译出来就是：从emp表中现将sal>2500的都选出来，然后根据部门编号分组，然后再对每组sal求平均值，最终显示出来。显然这样的逻辑不满足我们的要求。

正确的做法是像下面这样：

总结(单表的查询讲完了):

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


执行顺序
1.from
2.where
3.group by
4.having
5.select
6.order by
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

上面的语句解释为：

从某张表中查询数据，先经过where条件筛选出有价值的数据，对这些数据进行分组，分组之后可以使用having继续筛选，select查询出来，最后排序输出。

看一个综合案例：

找出每个岗位的平均工资，要求显示平均薪资大于1500的，除manager岗位之外，要求按照平均薪资降序排列。

distinct 对查询出来的结果去重

例如：对员工表中的工作岗位去重

多个字段联合起来去重：要求dintinct关键字必须在多个字段的前面

连接查询

连接查询就是多张表联合起来查询数据

连接查询的分类

根据语法年代分类

SQL92:1992年的时候出现的语法

SQL99:1999年的时候出现的语法

根据表连接的方式分类

内连接包括等值连接、非等值连接、自连接

外连接包括左外连接、右外连接

当两张表进行连接查询时，若果没有任何条件限制会发生笛卡尔积现象

所谓笛卡尔积现象就是在多张表查询的时候，最终查询结果条数是多张表记录条数的乘积，像下面这种情况。

查询每个员工所在的部门名称：

…

因为员工表有14条记录，部门表有4条记录，所以乘起来就是54条记录。显然这不符合实际开发需求。

为了避免笛卡尔积现象，连接的时候就要家条件，满足条件的才筛选出来。

这里有两种语法：92语法和99语法

92语法

select
    字段1，字段2...
from
    表1，表2...
where
    ...
1
2
3
4
5
6

以上面的案例为例演示92语法：

当然上面的语句还可以简化，就是给表取别名：

99语法

select
    字段1，字段2...
from
    表1
inner join
    表2
on
    连接条件
1
2
3
4
5
6
7
8

同样根据上面的案例演示99语法：

虽然上面查询结果只有14条，但是表与表之间的匹配次数并没有变少，还是54次匹配。

注意：通过笛卡尔积现象，表的连接次数越多效率越低，所以尽量避免表的连接次数

内连接之等值连接

例如：查询每个员工所在的部门地理位置，显示员工名和部门地理位置

内连接之非等值连接 (条件不是一个等量关系所以称为非等值连接)

找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级

内连接之自连接

查询员工的上级领导，要求显示员工名和对应的领导名

首先先将员工编号和领导编号都查出来：

然后将这样表想象成两张表，这两张表分别是员工表和领导表，然后对这两张表进行连接。

内连接的时候两张表的关系是平等的，没有主次关系。

但是内连接不能解决所有的问题，例如查询每个员工的上级领导，要求显示所有员工的名字和领导名。如果用内连接：上面查询出来的结果只有13条记录，并没有将所有的员工都查询出来，显然不符合要求。这个时候就要用到外连接了。

外连接之左外连接和右外连接

右外连接语法：

select
    字段1，字段2...
from
    表1
right outer join
    表2
on
    连接条件
1
2
3
4
5
6
7
8

左外连接语法

select
    字段1，字段2...
from
    表1
left outer join
    表2
on
    连接条件
1
2
3
4
5
6
7
8

右外连接right代表将join关键字右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表，左外连接恰恰相反。

任何一个右连接都有左连接的写法，任何一个左连接都有右连接的写法

在外连接当中，两张表连接，产生了主次关系。

根据上面的案列演示外连接：

可以看到king是这个公司的老大，所以他没有boss，现在查出来的记录就是14条。

多张表的连接

语法：

select
    ...
from
    表1
join
    表2
on
    表1和表2的连接条件
join
    表3
on
    表1和表3的连接条件
left/right outer join
    表4
on
表1和表4的连接条件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

上面的一条sql语句中内连接和外连接可以混合使用

例如：找出每个员工的部门名称和工资等级，要求显示员工名、部门名、薪资、薪资等级

子查询

子查询：select语句中嵌套select语句，被嵌套的select语句称为子查询

子查询可以出现在where子句中、from语句中、select语句中

where子句中的子查询

例如：找出比最低工资高的员工姓名和工资

第一步：查询最低工资是多少

第二步：查询大于最低工资的员工姓名和工资

第三步合并：

from子句中的子查询

注意：from后面的子查询，可以将子查询的查询结果当做一张临时表(技巧）

例如：找出每个岗位的平均工资的薪资等级

select后面出现的子查询(了解即可）

例如：找出每个员工的部门名称，要求显示员工名，部门名

union 合并查询结果集

对于表的连接来说，每连接一次新表，则匹配的次数满足笛卡尔积，成倍增加。但是使用union可以减少匹配的次数。在减少匹配次数的情况下，还可以完成两个结果的拼接。

例如：查询员工工作岗位是manager和salesman的员工

比如a连接b连接c，a、b、c都是10条记录，则匹配次数为1000次。

使用union：a连接b一个结果：10*10 = 100次；a连接c一个结果：10*10 = 100次，加起来就是200次。显然匹配次数少了很多。

使用union的注意事项：

1.union在进行结果集合合并的时候，要求两个结果集的列数相同。

2.结果集合合并时列和列的数据类型也要一致。

上面在mysql中语法没问题，结果集合的列数相同，但是列和列的数据类型不一致。

limit的作用：将查询结果集的一部分取出来。通常在分页查询当中，分页查询是为了提高用户体验。

limit的语法：limit startIndex，length

startIndex是起始下标，起始下标从0开始。length是长度

缺省用法：limit 5——这是取前5个

例如：按照薪资降序，取出排名在前5名的员工

注意：mysql当中limit在order by之后执行

例如：取出工资排名在[3-5]名的员工

下标从2开始，就是第3条记录

分页

每页显示pageSize条记录，第pageNo页：

公式：limit (pageNo-1)*pageSize,pageSize

关于DQL语句的大总结：

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

执行顺序：
1.from
2.where
3.group by
4.having
5.select
6.order by
7.limit
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

mysql中的数据类型

我们在这里只需要掌握一些基本的数据类型

varchar(最长255)：可变长度的字符串，比较智能，节省空间。会根据实际的数据长度动态分配空间。优点：节省空间，缺点：需要动态分配空间，速度慢。

char(最长255)：定长字符串，不管实际的数据长度是多少。分配固定长度的空间去存储数据。使用不恰当的时候，可能会导致空间的浪费。优点：不需要动态分配空间，速度快。缺点：使用不当可能会导致空间的浪费。

int(最长11)：数字中的整数型。等同于java的int。

bigint：数字中的长整型。等同于java中的long。

float：单精度浮点型数据

double：双精度浮点型数据

date：短日期类型只包括年月日信息

datetime：长日期类型包括年月日时分秒信息

clob：字符大对象，最多可以存储4G的字符串。比如：存储一篇文章，存储一个说明。超过255个字符的都要采用CLOB字符大对象来存储。Character Large OBject:CLOB

blob：二进制大对象，Binary Large OBject，专门用来存储图片、声音、视频等流媒体数据。
往BLOB类型的字段上插入数据的时候，例如插入一个图片、视频等，你需要使用IO流才行。

建表的语法格式:(建表属于DDL语句，DDL语句包括create、drop、alter)

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

删除表语法格式：drop table if exists 表名

例如：创建一个学生表，表中含有学生的编号、姓名、性别、年龄

快速建表之将一张表的结果查询结果插入到一张表中（了解内容）

例如：创建一张表，要求表的字段和数据类型都和部门表一样

向表中插入数据

插入数据insert (DML)

语法格式：insert into 表名(字段1，字段2，字段3…）values(值1，值2，值3…）；

注意：字段名和值要一一对应，即数量要对应，数据类型要对应。

例如：向学生表中插入学生的信息

删除表中的数据

删除数据 delete (DML)

语法格式：delete from 表名 where 条件;

注意：没有条件，整张表的数据都会被全部删除

例如：删除编号为2的学生信息

例如：删除整张的信息

删除表中的数据之快速删除表中的数据

delete from 表名;这种删除数据的方式比较慢

truncate table 表名;这种删除表的速度快

delete语句删除数据的原理？（delete属于DML语句）
表中的数据被删除了，但是这个数据在硬盘上的真实存储空间不会被释放！！！
这种删除缺点是：删除效率比较低。
这种删除优点是：支持回滚，后悔了可以再恢复数据！！！

truncate语句删除数据的原理？
这种删除效率比较高，表被一次截断，物理删除。
这种删除缺点：不支持回滚。
这种删除优点：快速。

用法：truncate table dept_bak; （这种操作属于DDL操作。）

大表非常大，上亿条记录？？？？
删除的时候，使用delete，也许需要执行1个小时才能删除完！效率较低。
可以选择使用truncate删除表中的数据。只需要不到1秒钟的时间就删除结束。效率较高。
但是使用truncate之前，必须仔细询问客户是否真的要删除，并警告删除之后不可恢复！

truncate是删除表中的数据，表还在！

更新/修改表中的数据

语法：

update 表名 set column = expr where 条件 [order by...] [limit...]
1

约束

约束：在创建表的时候，我们可以给表中的字段加上一些约束，来保证这个表中的数据的完整性、有效性。约束的作用就是为了保证表中的数据有效。

约束包括那些：

非空约束：not null

唯一性约束：unique

主键约束：primary key（简称PK）

外键约束：foreign key（简称FK）

非空约束：not null

非空约束not null约束的字段不能为null

例如：创建一个学生类，要求学生的姓名不能为空

如果在列的后面加非空约束，则将这种约束称为列级约束

唯一性约束：unique

唯一性约束的字段不能重复，但可以为null

例如：创建学生表要求学生姓名不能重复

多个字段联合具有唯一性：

例如：创建一个学生表，里面包含学生编号、姓名、邮箱，要求姓名和邮箱两个字段联合起来具有唯一性

但是当我们一旦插入的邮箱一样的时候就会报错

上面的约束没有添加在列的后面，这种约束称为表级约束，当需要给多个字段联合起来添加某一个约束的时候，需要使用标记约束。

unique和not null可以联合使用，当unique和not null联合时就变成了主键约束

主键约束：primary key

任何一张表如果没有主键约束，表无效

主键约束是一种约束，字段上面添加主键约束，该字段称为主键字段。主键字段上面的值称为主键值，主键值是每一行记录的唯一标识，主键值是每一行的身份证号。

例如：创建一张学生表，学生表中包含编号、姓名、年龄，要求编号和姓名不能为空也不能重复

注意：

1.在实际开发中不建议使用复合主键，建议使用单一主键，因为主键值存在的意义就是这行记录的身份证号码，只要达到意义即可，单一主键可以做到。

2.一个表中主键约束不能多于1个

在mysql中，有一种机制，可以帮助我们自动维护一个主键值

auto_increment表示自增，从1开始，一1递增

外键约束：foreign key

外键约束是一种约束，字段上面添加外键约束称为外键字段，外键字段上面的每一个值称为外键值。

现在有一个需求：请设计数据库表，来描述“班级和学生”的信息

		第一种方案：班级和学生存储在一张表中
		t_student
		no(pk)			name		classno			classname
		--------------------------------------------------------------------------------------------------------------
		1			    jack		100			北京市大兴区亦庄镇第二中学高三1班
		2			    lucy		100			北京市大兴区亦庄镇第二中学高三1班
		3			    lilei		100			北京市大兴区亦庄镇第二中学高三1班
		4			    hanmeimei	100			北京市大兴区亦庄镇第二中学高三1班
		5			    zhangsan	101			北京市大兴区亦庄镇第二中学高三2班
		6			    lisi		101			北京市大兴区亦庄镇第二中学高三2班
		7			    wangwu		101			北京市大兴区亦庄镇第二中学高三2班
		8			    zhaoliu		101			北京市大兴区亦庄镇第二中学高三2班
		分析以上方案的缺点：
		数据冗余，空间浪费！
		这个设计是比较失败的！
		
		第二种方案：班级一张表、学生一张表
		
		t_class 班级表
		classno(pk)		classname
		------------------------------------------------------
		100			北京市大兴区亦庄镇第二中学高三1班
		101			北京市大兴区亦庄镇第二中学高三1班
	
		t_student 学生表
		no(pk)			name			cno(FK引用t_class这张表的classno)
		-------------------------------------------------------------------------------------------------
		1			jack			100
		2			lucy			100
		3			lilei			100
		4			hanmeimei		100
		5			zhangsan		101
		6			lisi			101
		7			wangwu			101
		8			zhaoliu			101

		当cno字段没有任何约束的时候，可能会导致数据无效。可能出现一个102，但是102班级不存在。
		所以为了保证cno字段中的值都是100和101，需要给cno字段添加外键约束。
		那么：cno字段就是外键字段。cno字段中的每一个值都是外键值。

		注意：
			t_class是父表
			t_student是子表

			删除表的顺序：先删子，再删父。

			创建表的顺序：先创建父，再创建子。

			删除数据的顺序：先删子，再删父。

			插入数据的顺序：先插入父，再插入子。

		思考：子表中的外键引用的父表中的某个字段，被引用的这个字段必须是主键吗？
			不一定是主键，但至少具有unique约束。

			外键值可以为NULL。

drop table if exists t_student;
drop table if exists t_class;

create table t_class(
	classno int primary key,
	classname varchar(255)
);
create table t_student(
	no int primary key auto_increment,
	name varchar(255),
	cno int,
	foreign key(cno) references t_class(classno)
);

insert into t_class(classno, classname) values(100, '北京市大兴区亦庄镇第二中学高三1班');
insert into t_class(classno, classname) values(101, '北京市大兴区亦庄镇第二中学高三2班');

insert into t_student(name,cno) values('jack', 100);
insert into t_student(name,cno) values('lucy', 100);
insert into t_student(name,cno) values('lilei', 100);
insert into t_student(name,cno) values('hanmeimei', 100);
insert into t_student(name,cno) values('zhangsan', 101);
insert into t_student(name,cno) values('lisi', 101);
insert into t_student(name,cno) values('wangwu', 101);
insert into t_student(name,cno) values('zhaoliu', 101);

select * from t_student;
select * from t_class;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85

存储引擎(了解内容)

存储引擎就是一个表组织/存储数据的方式，不同的存储引擎，表存储数据的方式不同。

查看masql支持哪些存储引擎

show engines;

MySQL中主要的引擎

MyISAM存储引擎
它管理的表具有以下特征：
使用三个文件表示每个表：
格式文件 — 存储表结构的定义（mytable.frm）
数据文件 — 存储表行的内容（mytable.MYD）
索引文件 — 存储表上索引（mytable.MYI）：索引是一本书的目录，缩小扫描范围，提高查询效率的一种机制。
可被转换为压缩、只读表来节省空间

MyISAM存储引擎特点：
可被转换为压缩、只读表来节省空间
这是这种存储引擎的优势
MyISAM不支持事务机制，安全性低。

InnoDB存储引擎
这是mysql默认的存储引擎，同时也是一个重量级的存储引擎。
InnoDB支持事务，支持数据库崩溃后自动恢复机制。
InnoDB存储引擎最主要的特点是：非常安全。

它管理的表具有下列主要特征：
– 每个 InnoDB 表在数据库目录中以.frm 格式文件表示
– InnoDB 表空间 tablespace 被用于存储表的内容（表空间是一个逻辑名称。表空间存储数据+索引。）

– 提供一组用来记录事务性活动的日志文件
– 用 COMMIT(提交)、SAVEPOINT 及ROLLBACK(回滚)支持事务处理
– 提供全 ACID 兼容
– 在 MySQL 服务器崩溃后提供自动恢复
– 多版本（MVCC）和行级锁定
– 支持外键及引用的完整性，包括级联删除和更新

InnoDB最大的特点就是支持事务：
以保证数据的安全。效率不是很高，并且也不能压缩，不能转换为只读，
不能很好的节省存储空间。

MEMORY存储引擎
使用 MEMORY 存储引擎的表，其数据存储在内存中，且行的长度固定，
这两个特点使得 MEMORY 存储引擎非常快。

MEMORY 存储引擎管理的表具有下列特征：
– 在数据库目录内，每个表均以.frm 格式的文件表示。
– 表数据及索引被存储在内存中。（目的就是快，查询快！）
– 表级锁机制。
– 不能包含 TEXT 或 BLOB 字段。

MEMORY 存储引擎以前被称为HEAP 引擎。

MEMORY引擎优点：查询效率是最高的。不需要和硬盘交互。
MEMORY引擎缺点：不安全，关机之后数据消失。因为数据和索引都是在内存当中。

建表的时候可以指定存储引擎，以及字符编码方式

查看表的存储引擎show create table 表名;

事务(概念很多)

事务就是一个完整的业务逻辑，是一个最小的工作单元，不可再分。事务是批量的DML语句同时成功或者同时失败。

只有DML语句才会有事务一说insert、delete、update，其他语句和事务无关。因为只有DML语句才会对数据库中的数据进行增、删、改等操作，这关乎数据的安全，所以非常重要。

1.事务的执行机制

InnoDB存储引擎：提供一组用来记录事务性活动的日志文件

事务开启了：
insert
delete
update
事务结束了！

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

提交事务
清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中。
提交事务标志着，事务的结束。并且是一种全部成功的结束。

回滚事务
将之前所有的DML操作全部撤销，并且清空事务性活动的日志文件
回滚事务标志着，事务的结束。并且是一种全部失败的结束。

2.提交事务和回滚事务

提交事务：commit; 语句
回滚事务：rollback; 语句（回滚永远都是只能回滚到上一次的提交点！）

测试一下，在mysql当中默认的事务行为是怎样的？
mysql默认情况下是支持自动提交事务的。（自动提交）
什么是自动提交？
每执行一条DML语句，则提交一次！

我们以上面的表操作为例，来演示mysql的自动提交机制：

当我们在删除表中的数据的时候我们并没有提交，也就是没有commit，但是mysql自动就帮我们提交了，而且我们此时回滚也回不去。

这种自动提交实际上是不符合我们的开发习惯，因为一个业务，通常是需要多条DML语句共同执行才能完成的，为了保证数据的安全，必须要求同时成功之后再提交，所以不能执行一条，就提交一条。

怎么将mysql的自动提交机制关闭掉呢？先执行这个命令：start transaction;然后添加手动提交命令commit

还是以上面的案列为例：

3.事务的ACID特性

A：原子性

说明事务是最小的工作单元，不可再分。

C：一致性

所有事务要求，在同一个事务当中，所有操作必须同时成功，或者同时失败，以保证数据的一致性。

I：隔离性

A事务和B事务之间具有一定的隔离，就像教室A和教室B之间有一道墙，这道墙就是隔离性。

D：持久性

事务最终结束的一个保障，事务提交以后就相当于将没有保存到硬盘上的数据保存到硬盘上

I：隔离性

A教室和B教室中间有一道墙，这道墙可以很厚，也可以很薄。这就是事务的隔离级别。
这道墙越厚，表示隔离级别就越高。

事务和事务之间的隔离级别有哪些呢？4个级别

读未提交：read uncommitted（最低的隔离级别）《没有提交就读到了》

什么是读未提交，读未提交就是事务A可以读取到事务B未提交的数据。
这种隔离级别存在的问题就是：脏读现象！(Dirty Read)，我们称读到了脏数据。这种隔离级别一般都是理论上的，大多数的数据库隔离级别都是二档起步！

读已提交：read committed《提交之后才能读到》

什么是读已提交？事务A只能读取到事务B提交之后的数据。
这种隔离级别解决了脏读的现象。
这种隔离级别存在什么问题是不可重复读取数据，什么是不可重复读取数据呢？不可重复读取数据就是在事务开启之后，第一次读到的数据是3条，当前事务还没有结束，可能第二次再读取的时候，读到的数据是4条，3不等于4。

这种隔离级别是比较真实的数据，每一次读到的数据是绝对的真实。
oracle数据库默认的隔离级别是：read committed

可重复读：repeatable read《提交之后也读不到，永远读取的都是刚开启事务时的数据》

什么是可重复读取？重复读取就是，事务A开启之后，不管是多久，每一次在事务A中读取到的数据都是一致的。即使事务B将数据已经修改，并且提交了，事务A读取到的数据还是没有发生改变，这就是可重复读。
可重复读解决不可重复读取数据。可重复读存在的问题是可以会出现幻影读。每一次读取到的数据都是幻象。不够真实！
例如：早晨9点开始开启了事务，只要事务不结束，到晚上9点，读到的数据还是那样！读到的是假象。不够绝对的真实。mysql中默认的事务隔离级别就是这个！

序列化/串行化：serializable（最高的隔离级别）

这是最高隔离级别，效率最低。解决了所有的问题。
这种隔离级别表示事务排队，不能并发！每一次读取到的数据都是最真实的，并且效率是最低的。

4.验证各种隔离级别

被测试的表为t_product;

注意在每一次验证的时候，都需要将mysql的隔离级别更改，命令是：

set global transaction isolation level 不同的隔离级别

修改以后退出mysql，然后重写打开mysql数据库。

下面是mysql的默认隔离级别：

查看当前mysql的隔离级别命令：select @@tx_isolation

为了演示四种隔离级别，需要开两个mysql窗口

1.演示读未提交read uncommitted

首先修改隔离级别，然后退出mysql

然后开两个mysql窗口，(这里有点多线程的感觉 )各自登录到mysql数据库中打开bjpowernode数据库

然后1：事务A、B各自开启事务 、2：事务A查询表t_product、3：事务B向t_product中插入数据、4：事务A查询表t_product

可以看到事务B还没有提交事务即还没有commit，事务A中就已经能够查到数据了。

2.演示读已提交read committed

不要忘了修改隔离级别

步骤还是和上面的一样

可以看到事务A只有在事务B提交事务以后，才能查看到表中的数据

3.演示可重复读repeatable read

可以看到即使事务B提交了事务，但是事务A还是只能查询到事务A提交之间的记录

只有当事务A提交以后再次查询，才能查询到事务B提交的事务，像下面这样：

4.验证序列化/串行化：serializable

这个就和两个线程占用一个资源一样了，只有当一个事务结束操作以后，两一个事务才能操作

可以看到只要事务A不提交事务，事务B就会一直卡着，直到事务B的请求超时，向下面这样：

当事务B再次请求，然后事务A提交事务以后，事务B马上就能查看到数据，像下面这样：

索引

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

Mysql在查询方面主要有两种方式：第一种方式：全表扫描、第二种方式：根据索引检索

索引的实现原理

假设有一张用户表：t_user

?? ?id(PK)?? ??? ??? ?name?? ??? ??? ?每一行记录在硬盘上都有物理存储编号
?? ?----------------------------------------------------------------------------------
?? ?100?? ??? ??? ?   zhangsan?? ??? ??? ?0x1111
?? ?120?? ??? ??? ?   lisi?? ??? ??? ?    0x2222
?? ?99?? ??? ??? ?    wangwu?? ??? ??? ?  0x8888
?? ?88?? ??? ??? ?    zhaoliu?? ??? ??? ? 0x9999
?? ?101?? ??? ??? ?   jack?? ??? ??? ?    0x6666
?? ?55?? ??? ??? ?    lucy?? ??? ??? ?    0x5555
?? ?130?? ??? ??? ?   tom?? ??? ??? ?     0x7777
1
2
3
4
5
6
7
8
9

提醒1：在任何数据库当中主键上都会自动添加索引对象，id字段上自动有索引，
因为id是PK。另外在mysql当中，一个字段上如果有unique约束的话，也会自动
创建索引对象。

提醒2：在任何数据库当中，任何一张表的任何一条记录在硬盘存储上都有
一个硬盘的物理存储编号。

提醒3：在mysql当中，索引是一个单独的对象，不同的存储引擎以不同的形式
存在，在MyISAM存储引擎中，索引存储在一个.MYI文件中。在InnoDB存储引擎中
索引存储在一个逻辑名称叫做tablespace的当中。在MEMORY存储引擎当中索引
被存储在内存当中。不管索引存储在哪里，索引在mysql当中都是一个树的形式
存在

下列情况可以给给字段添加索引：

条件1：数据量庞大（到底有多么庞大算庞大，这个需要测试，因为每一个硬件环境不同）
条件2：该字段经常出现在where的后面，以条件的形式存在，也就是说这个字段总是被扫描。
条件3：该字段很少的DML(insert delete update)操作。（因为DML之后，索引需要重新排序。）

索引的创建和删除

创建索引

create index 索引名 on 表名(被加上索引的字段);

例如：create index emp_ename_index on emp(ename);

给emp表的ename字段添加索引，起名为：emp_ename_index

索引的删除

drop index 索引名 on 表名

例如：drop index emp_ename_index on emp;

将emp表上的emp_ename_index索引对象删除

查看字段上面是否有索引

explain select * from 表名 where 条件;

索引失效

索引也有失效的时候

失效的第一种情况，在模糊查询的时候会失效：

失效的第二种情况，使用or的时候会失效。如果使用or那么要求or两边的条件字段都要有索引，才会走索引，如果其中一边有一个字段没有索引，那么另一个字段上的索引也会失效。

索引失效的第三种情况，使用复合索引的时候，没有使用左侧的列查找，索引失效。

视图

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

创建视图

create view 视图名 as select * from 被复制的表；

删除视图

drop view 视图名；

注意：只有DQL语句才能以view的形式创建，也就是create view 视图名 as （这里的语句必须是DQL语句）

视图的作用

我们可以面向视图对象进行增删改查，对视图对象进行增删改查会导致原表被操作(视图的特点就是通过视图能够影响到原表的数据)

视图在开发中的作用：

假设有一条非常复杂的SQL语句，而这条SQL语句需要在不同的位置上反复使用，每一次使用这个SQL语句的时候都需要重新编写，很长，很麻烦，这个时候就可以把这条复杂的SQL语句以视图的对象的形式新建。这样可以大大简化开发，并且利于后期的维护，因为修改的时候也只需要修改一个位置就行，只需要修改视图对象所映射的SQL语句。

DBA常用命令

作为java程序员，只需要掌握导入导出命令即可：

数据导出
注意：在windows的dos命令窗口中：
mysqldump bjpowernode>D:jpowernode.sql -uroot -p123456

可以导出指定的表吗？
mysqldump bjpowernode emp>D:jpowernode.sql -uroot -p123456

数据导入
注意：需要先登录到mysql数据库服务器上。
然后创建数据库：create database bjpowernode;
使用数据库：use bjpowernode
然后初始化数据库：source D:jpowernode.sql

数据库设计三范式

数据库设计三范式是数据库表设计的依据

数据库三范式共有一下三种：

第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分。

第二范式：建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不产生部分依赖。

第三范式：建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不产生传递依赖。

第一范式

最核心，最重要的范式，所有表的设计都需要满足。
必须有主键，并且每一个字段都是原子性不可再分。

学生编号 学生姓名 联系方式
------------------------------------------
1001 张三 zs@gmail.com,1359999999
1002 李四 ls@gmail.com,13699999999
1001 王五 ww@163.net,13488888888

以上不满足第一范式，第一：没有主键。第二：联系方式可以分为邮箱地址和电话，修改以后像下面这样。

学生编号(pk) 学生姓名 邮箱地址 联系电话
----------------------------------------------------
1001 张三 zs@gmail.com 1359999999
1002 李四 ls@gmail.com 13699999999
1003 王五 ww@163.net 13488888888

第二范式

建立在第一范式的基础之上，
要求所有非主键字段必须完全依赖主键，不要产生部分依赖。

学生编号 学生姓名 教师编号 教师姓名
----------------------------------------------------
1001 张三 001 王老师
1002 李四 002 赵老师
1003 王五 001 王老师
1001 张三 002 赵老师

这张表描述了学生和老师的关系：（1个学生可能有多个老师，1个老师有多个学生）
这是非常典型的：多对多关系！

分析以上的表是否满足第一范式？
不满足第一范式。

怎么满足第一范式呢？修改

学生编号+教师编号(pk) 学生姓名 教师姓名
----------------------------------------------------
1001 001 张三 王老师
1002 002 李四 赵老师
1003 001 王五 王老师
1001 002 张三 赵老师

学生编号 教师编号，两个字段联合做主键，复合主键（PK: 学生编号+教师编号）
经过修改之后，以上的表满足了第一范式。但是满足第二范式吗？
不满足，“张三”依赖1001，“王老师”依赖001，显然产生了部分依赖。
产生部分依赖有什么缺点？
数据冗余了。空间浪费了。“张三”重复了，“王老师”重复了。

为了让以上的表满足第二范式，你需要这样设计：
使用三张表来表示多对多的关系！！！！
学生表
学生编号(pk) 学生名字
------------------------------------
1001 张三
1002 李四
1003 王五

教师表
教师编号(pk) 教师姓名
--------------------------------------
001 王老师
002 赵老师

学生教师关系表
id(pk) 学生编号(fk) 教师编号(fk)
------------------------------------------------------
1 1001 001
2 1002 002
3 1003 001
4 1001 002

背口诀：
多对多怎么设计？
多对多，三张表，关系表两个外键！

第三范式

第三范式建立在第二范式的基础之上
要求所有非主键字典必须直接依赖主键，不要产生传递依赖。

学生编号（PK） 学生姓名 班级编号 班级名称
---------------------------------------------------------
1001 张三 01 一年一班
1002 李四 02 一年二班
1003 王五 03 一年三班
1004 赵六 03 一年三班

以上表的设计是描述：班级和学生的关系。很显然是1对多关系！
一个教室中有多个学生。

分析以上表是否满足第一范式？
满足第一范式，有主键。

分析以上表是否满足第二范式？
满足第二范式，因为主键不是复合主键，没有产生部分依赖。主键是单一主键。

分析以上表是否满足第三范式？
第三范式要求：不要产生传递依赖！
一年一班依赖01，01依赖1001，产生了传递依赖。
不符合第三范式的要求。产生了数据的冗余。

那么应该怎么设计一对多呢？

班级表：一
班级编号(pk) 班级名称
----------------------------------------
01 一年一班
02 一年二班
03 一年三班

学生表：多

学生编号（PK） 学生姓名 班级编号(fk)
-------------------------------------------
1001 张三 01
1002 李四 02
1003 王五 03
1004 赵六 03

背口诀：
一对多，两张表，多的表加外键！

先自我介绍一下，小编13年上师交大毕业，曾经在小公司待过，去过华为OPPO等大厂，18年进入阿里，直到现在。深知大多数初中级java工程师，想要升技能，往往是需要自己摸索成长或是报班学习，但对于培训机构动则近万元的学费，着实压力不小。自己不成体系的自学效率很低又漫长，而且容易碰到天花板技术停止不前。因此我收集了一份《java开发全套学习资料》送给大家，初衷也很简单，就是希望帮助到想自学又不知道该从何学起的朋友，同时减轻大家的负担。添加下方名片，即可获取全套学习资料哦