【一步到位】全网最全的MySQL数据库讲解

黎䪽圓

于 2025-06-11 23:20:56 发布

阅读量791

点赞数 28

CC 4.0 BY-SA版权

分类专栏：数据库文章标签： java 数据库

本文链接：https://blog.csdn.net/2401_87735879/article/details/148596361

数据库专栏收录该内容

1 篇文章

订阅专栏

数据库是啥？

数据库是一个广义的概念

它可以有以下的定义：

1》表示一门学科

2》表示一类软件，管理数据的软件

3》表示某一个具体的数据库软件

4》表示部署了某个数据库软件的主机(电脑)

mysql是一个"关系型数据库",关系型数据库是用”表“来组织数据的，就像excel表一样，有很多行，很多列，每一条数据作为一行(一个记录），一行里面有很多列(一个字段)，每一行的列数，列的含义都得匹配，对于数据的格式要求比较高，一板一眼

而非关系型数据库，则更加灵活，会使用”文档/键值对“这样的结构来组织数据，一条数据就是一个文档，文档和文档之间，可以差异很大，而键值对也没有太多的要求，只是什么格式，也没有要求，非常灵活

数据库操作

1.创建数据库

创建数据库的时候可以手动指定一下字符集，我们需要在字符集当中保存中文，而mysql默认的的字符集是拉丁文，不支持中文，必须在创建数据库的时候，手动指定编码方式为支持中文的编码(GBK,UTF-8)

在一个计算机中，一个汉字占几个字节？

如果你认为是2的话就错了

在不同的字符集下，不同的编码方式之下，一个汉字占几个字节，是不同的

两个字节，是学习c语言的时候，留下的印象，当时使用的VS是在windows上面写的代码，windows简体中文版默认的字符集是GBK，VS默认的字符集是跟系统是一致的(GBK)，在这个情况下，一个汉字是占两个字节

但是现在GBK已经用的很少了，主要是用UTF-8作为编码方式，UTF-8是变长编码，不仅仅可以表示中文，也可以表示世界上任何一种语言文字，如果使用UTF-8编码，一个汉字通常是3个字节

unicode是给一个字符进行编码的，但是无法给“字符串”编码，比如把多个unicode编码的字符放到一起，构成一个字符串，就可能会乱套了，无法区分字符和字符之间的边界

基于unicode就演化出了一些可以给字符串编码的版本，可以把UTF-8看作unicode的字符串版本

在Java中，char类型是用unicode来编码的，而字符串是用UTF-8来编码的，如果想要将字符加到字符串上的话，java会将字符通过UTF-8进行编码，然后加到字符串上，这都是由Java自主实现的

mysql数据库的字符集UTF8是一个"残本"，不是一个完全体的UTF8，少了一些emoji表情，比特的教务系统的数据库使用的就是UTF8，如果提交的作业里面有表情的话，就会提交失败

后来mysql又搞了一个UTF8mb4，是完全体的UTF8了

sql都需要以" ; "结束(英文分号)

在这里插入图片描述

mysql客户端允许你输入sql的时候换行

2.查看数据库

列出当前的mysql服务器上一共有哪些数据

格式：show databases;
在这里插入图片描述

默认里面会有一个叫mysql的数据库，修改一些配置啥的都可以通过mysql数据库来操作，这里要慎重操作

3.选中数据库

格式：use 数据库名;

数据库中最重要的操作就是针对表进行增删查改，而表是从属于数据库的

要对表进行操作，就i需要把哪个数据库的表这个事情给确定了

4.删除数据库

格式：drop database 数据库名;

删除操作，不仅是删除database，而且也删除了database里面的所有表和表里面的所有数据

删除数据库操作，是一个非常危险的操作！！！！！！

线上数据库/生产环境数据库：是被用户访问的数据库，是真实用户的数据

线下数据库：开发/测试节点，自己构造的一些"假的"数据

线上的程序，一般会配备报警，mysql服务器必须保持7*24小时运行，如果挂了，就会产生很大的影响

报警程序，是一个程序，会自动监视mysql的服务器的运行状态，一旦数据库存在问题，就会及时通过短信/邮件/wx/电话报警

公司为了避免数据库出现问题，也会采用以下的措施：

1.控制好权限，新人程序员只能查询修改，不能删除，比较危险的操作，交给有经验的程序员进行操作，有的公司会专门有dba(数据库管理员)这样的岗位，专门负责更复杂更加危险的的数据库操作

2.把数据及时做好备份

数据表的操作

针对数据表的操作，前提是先选中数据库

一个表包含很多行，每一行也可以称为一个记录

一行里面可以有很多列，每一列也称为是一个字段，每一列都是有一个具体类型的

因此在学习数据表操作的时候，我们先要学习一下数据表支持哪些类型

数据表支持的类型

1》数字类型

在这里插入图片描述

BIT代表的是一个二进制比特位，如果要用的话，会用来表示是否(类似于布尔)，或者只对需要四个或者其他数量的比特位进行操作，可以更好的压缩节省空间

FlOAT(M,D)和DOUBLE(M,D)后面的括号用来描述精度，M表示小数的长度，D表示小数掉后的位数

比如DOUBLE(3，1)长度是3位，小数点后是1位，例如99.9 10.0 20.3

此处的float和double都是和java/c类似，都是IEEE 754标准的浮点数

而IEEE 754标准存在一些缺陷，精度会丢失，存在一定的误差，所以在进行浮点数的运算的时候，用"="来比较连个浮点数是比较危险的，如下图

在这里插入图片描述

0.1+0.2应该等于0.3，但是由于IEEE 754浮点数运算标准之下精度的丢失，计算出来的结果并不是0.3，如上图，使用"="的返回值就是false

在float和double的基础上又有了decimal来表示小数，它是精度更高的浮点数(使用了其他方法来存储小数)，虽然它的精度更高，但是，运算速度会变慢，占用的空间也会更多

在这里插入图片描述

BigDecimal是java中对于decimal的一个实现

关于单价这种关于钱的数据应该用什么类型来存储

钱都是带小数的，比如超市里面商品的单价

小数的话，我们就想到了用double来存储，但是mysql的double类型是IEEE 754标准的，存在误差，但是算钱一定要算标准，不能有误差

于是我们又想到了用decimal类型，它的精度更高，但是decimal类型的运算速度比较慢并且占用的空间比较大，如果处理的数据很多的话，会影响程序的运行

有什么即精度高，运行速度块，而且占用空间不大的类型呢

有的兄弟，有的！

他就是int类型

这时就会有人问了，我们不是要存储小数吗，int类型咋存小数呀？

现实生活中，我们一般会把商品单价精确到分，只有像银行这种地方在计算的时候会用更高的精度

所以我们换个单位，把元换为分，0.5元就是50分，直接把0.5弄成了50，50不就是整数吗

2》字符串类型

在这里插入图片描述

VARCHAR(SIZE)的size表示该类型里面最多存储几个字符(不是字节)，一个汉字算是一个字节，但是可能是对应多个字节

注意，但写了size为10的字符串，不是当即就会分配十个字符的储存空间，是会先分配一个比较小的空间，如果不够的话，会自动扩容，但是最大空间不会超过十

BLOB是存储二进制数据的

注意，在java当中要存储二进制数据，使用的是byte[]

如何区分文本数据和二进制数据呢，文本数据里面存储的都是字符，这些字符是可以在对应的码表上查找到的，文档等文件就是以文本数据形式储存的

而在码表上查找不到的，就是二进制数据，像音乐/图片/视频等就越是以二进制数据形式储存的

注意，一般很少会在数据库的某一列当中，存储特别大的数据，这么做会大大影响数据库增删查改的效率，比如查找数据时，会把数据表遍历一遍，如果某一列当中存储的数据非常大，就会在这里消耗很多的时间，降低效率

实际开发中如果需要保存图片，一般都是把图片单独放到专门的目录中，让后让数据库保存图片的路径

3》时间类型

在这里插入图片描述

TIMESTAMP是来保存时间戳的

什么是时间戳？

计算机时使用时间戳来表示时间的，以1970年1月1日0时0分0秒作为基准，计算当前时刻和基准时刻的秒数/毫秒数/微秒数之差

但是我们尽量不要使用TIMESTAMP，因为它只有四个字节，四个字节可以存储多大的数据呢，如果时有符号的话，是从-21亿->+21亿，如果是无符号的话，0->42亿，Java当中就没有无符号类型，mysql当中虽然有无符号类型，但是不推荐用，所以四个字节的最大值是21亿，此时的时间戳已经来到了1742441127174（17亿），到了2038年，四个字节就无法继续表示秒级时间戳了，所以我们推荐使用DATETIME(八个字节)

Java当中的Timestamp是八个字节，而且是毫秒级别的，不用担心2038年的问题

java当中的Date这个类型，只能表示年月日，不能表示时分

上述类型，只需要掌握一下几个就可以：

1.int

2.long

3.double

4.decimal

5.varchar

6.datetime

关于无符号类型，mysql提供了无符号版本的数据类型，但是官方文档上明确写了不建议使用，会在未来版本删除

java没有无符号类型

两个无符号类型相减，可能会产生溢出的情况
为什么有闰年呢？

我们通常认为地球公转一圈，是365天，但是实际上比365多，这就会导致会少算下时间，如果不管，误差会越来越大，于是就有了闰年，在闰年补一天来修补误差，所以闰年会比正常的年份多一天，但是有时误差并不是恰好是完整的一天，为了更精准的弥补误差，就有了闰秒，用秒来弥补误差，至于什么时候用闰秒来补误差，是天文学家说了算

对于计算机来说，是如何用来用闰秒来弥补误差的？

比如现在是23：59：59，正常来说，下一秒过后，就因该是12：00：00了，但是我们要补闰秒，于是计算机就会把59秒这一秒走两遍，本来下一秒该十二点了，但是计算机的下一秒还是59秒

如果有这样的程序

计算一段代码的运行时间，根据这段代码的运行时间来决定下一步要来执行什么逻辑，在程序运行前计算一次时间戳a，程序运行后再计算一次时间戳b，用b-a来计算程序运行的时间，通常情况下，时间差应该是大于0的，但是如果出现了闰秒的情况就不一定了，比如第一次计算时间戳是59.8秒，恰好此时计算机要补闰秒，就会把59秒再走一遍，恰好第二次计算的时间戳是59.3秒，结果计算出来就会比0小，如果这个结果你是用无符号类型来存储的话，就会产生一个非常大的数字，可能会导致奔溃

1.创建表

格式：create table 表名(列名类型，列名类型…);

注意列名和表名不能跟关键字一样，如果确实想要一样的话，可以用反引号`来吧表名和列名引起来

2.查看该数据库中的所有表

格式：show tables；

3.查看指定表的结构

desc 表名；

在这里插入图片描述

field就是字段(列)

type就是列对应的数据类型，int后面的(11)表示显示的宽度，显示这个int类型的数据的时候，最多占据11个字符的宽度(和存储的容量是无关的)

Null表示的是表格中的这个格子是空的，而yes是允许这一列为空，这个跟我们填写表时的选填项和必填项类似，如果这个空是选填项，我们可以把Null设置为yes，可填也可不填，这一空允许为空，如果这个空是必填项，就可以把Null设置为no，就代表这个空不允许为空，必须填写

key代表约束

default代表的是默认值，Null就是说明默认值为空

extra就代表额外信息

4.删除表

格式：drop table 表名；

删除表时，也会把表里的数据一起给删掉，所以删除表也一定要慎重，这个操作也是非常危险的

删表的严重性可能比删数据库还要严重，虽然删除库删除了的数据并没有删除表删除的数据多

为什么呢？

如果是删除库操作，程序会第一时间报错，程序员第一时间收到警报，就会赶紧处理，把之前备份的数据恢复回去

但是如果是删除表的操作，程序不一定会第一时间报错，程序就会以这种错误的状态运行，并且运行的结果都是错误的，可能发现问题的时候，程序已经以这种错误的状态运行了好长的时间

CRUD

增删查改，是数据库非常基础的部分，也是后端开发日常工作中，最主要的一项工作

1.新增

格式：insert into 表名 values(值，值…);

括号里面的数值，要和列相匹配(列的个数和类型)

如果列的类型是varchar，增添的格式应该是’张三’或者"张三"，sql里面没有字符这个类型，所以字符串使用单引号或者双引号都可以

还可以指定列插入

格式：insert into 表名 (列名，列名…) values(值，值…);
在这里插入图片描述

还可以一次插入多行记录

格式：insert into 表名 value(值，值…),(值，值…);

我们会发现，当我们输入一条指令以并且回车以后，底下会出现以下的提示，这里的提示就是反馈效果，我们输入的语句是客户端给服务器发起的请求，而底下的提示是服务器返回给我们的反馈

在这里插入图片描述

一次插入多行，相比于一次插入一行，分多次插入，要快不少

比如要插入三行记录，一次插入一行，分多次插入，而客户端和服器是靠网络来连接的，就会涉及到三次网络交互，如果一次插入多行，就值涉及一次网络交互，一次网络交互要比三次网络交互块

如果是datatime类型的数据，应该如何插入呢？

可以是用一个固定格式的字符串，来表示时间日期

比如：‘2001-2-13 12:34:00’

如果想要填写的时间，是当前时刻，sql提供了一个现成的函数now()

2.查询

1》全列查询

格式：select * from 表名；select * from 表名；

把表中的所有行和所有列都查询出来

" * “表示"通配符”，可以指代所有的列

在这里插入图片描述

上图是查询出来的结果，服务器通过网络把这些数据返回给客户端，并且在客户端以表格的形式打印出来

select *也是一个危险的操作，注意mysql是一个客户端-服务器结构的软件，客户端这里进行的操作，都会通过请求发送给服务器，服务器查询的结果也会通过响应返回给客户端

如果数据库当前表里面的数据特别多，就可能会产生问题：

1.读取硬盘，把硬盘io给跑满了，此时程序的其他部分像访问键盘的话，就会非常慢

2.操作网络，也可能把网卡的宽带也跑满，此时其他客户端想要通过网络访问服务器就会非常困难，也会非常慢

如果说得通俗易懂一些的话，就相当于堵车了

这样的拥堵，就可能导致客户端无法顺利访问到数据库，进一步也对整个系统造成了影响(想当于服务器挂了)

当前我们的学习阶段，怎么写都没有问题的，以后在公司中，针对数据量比较大的生产环境，可不能随便select*

但是什么是生产环境呢？

接下来我们讲解一下工作中涉及到的几套环境

线下环境(公司内部的)：

1》办公环境

入职公司，公司给你配的电脑(台式机/笔记本)

2》开发环境

有的开发环境，就是办公环境(公司发的电脑)

有的开发环境，如果开发的程序比较复杂，是需要专门的服务器

比如有的程序运行，就要吃100G的内存，或者对计算机的性能要求更高，而服务器的性能一定比电脑好

但是是几个人公用一个服务器

3》测试环境

测试工程师，针对程序测试的时候，搭建的环境

也是需要服务器的，因为要尽可能的跟开发环境保持一致

线上环境(生产环境)：

是一组服务器，是外面的用户能直接访问到的服务器，如果生产环境的服务器出现问题，外面的用户都能直接感受到，会直接影响到

用户的体验(cpu,内存，网卡都会尽可能用的最好的)

2》指定列查询

格式：select 列名，列名…from 表名

一个表会有很多列，某个场景下的操作，只需要关注其中的几个列

3》查询字段为表达式

一边查询，一边进行计算

在查询的时候，写成由列名构成的表达式，把这一列的所有行都带入到表达式中，参与运算

在这里插入图片描述

如上图，将列名写为了chinese-10的表达式，打印出来的结果都减了十

这里的操作不会修改数据库服务器上的原始数据，只是在最终响应的"临时结果"中做了运算

进行查询的时候，是把服务器这里的数据读出来，返回给客户端，并且以临时表的形式进行展示，所以这个表只能用一次

在这里插入图片描述

还可以将各列加起来，计算一行中各列的总和，sql在查询的时候，可以进行一些简单的统计操作

表达式查询，是列和列之间的运算，把每一列都带入到这样的运算当中，不是行和行之间的运算

如果我们所写的表达式太复杂，别人在看的时候可能看不懂表达式的含义，我们此时就可以给表达式起一个别名

4》别名

select 表达式 as 别名 from 表名；

as可以省略，但是不建议

在这里插入图片描述

5》去重

关键字：distinct修饰某个列/多个列

去重操作的效率还是比较低的，以为当每次查到一个记录a的时候，数据库都要查询一下剩下的所有记录里面是否有跟a相同的记录

值相同的行，只会保留一个
在这里插入图片描述

当distnct修饰很多列的时候，只有这些列的值都相同的时候，才会去重

在这里插入图片描述

6》查询时进行排序

把行进行排序，是以行为代为进行排序的

格式：select 列名 from 表名 order by 列名 asc(升序)/desc(降序)；

后边的asc和desc如果省略的话就是默认升序

order by 表名就是指定某个列，然后根据这个列进行排序

mysql是一个客户端服务器的程序，客户端把请求发给服务器后，服务器进行查询数据，并且把查询到的数据(排序仍然是针对临时数据展开的)进行排序排序之后，再组织成响应数据返回给客户端

此处的排序，不影响原有数据在mysql服务器上存储的数据

如果一个sql不加order by，此时的查询的结果的数据的顺序，是不确定/无序的

我们当前如果多次select * from student；的话，这几次服务器给我们返回的数据的顺序可能是一样的，看起来好像顺序没有改变，但是如果再进行一些复杂的结果的话，比如增加/删除，返回的数据的顺序就不一定一样了

如果不加order by，代码就不应该依赖上述的顺序，尤其是在代码中，不能依赖以上的顺序来展开一些逻辑，数据库没有承诺过给你返回的数据是有序的

在这里插入图片描述

order by指定的列，如果你select的时候没有把这一列查出来，也不影响排序

上图中的第二个表格里面最后一行的唐三藏的chinese为null，是默认为最小，升序排列的时候是在最上面，降序排列的时候是在最下面

在这里插入图片描述

order by 还可以针对表达式进行排序，如上图

但是上图我们注意到，唐三藏的总分是null，但是我们从上面的上面的年表里可以知道，唐三藏的语文成绩虽然为零，但是数学成绩不为零，按照我们的逻辑来说，他的成绩不应该为null呀，这是为什么呀?

这是因为再数据库当中，不论什么运算，里面只要有null参加了运算，运算的结果均为null

指定多个列来排序，order by后面可以写成多个列，使用" , "来分开

在这里插入图片描述

先按照数学成绩来排序，如果数学成绩相同的话，再按照语文成绩排序

在这里插入图片描述

可以先根据数学成绩进行降序排序，如果数学成绩相同的话，再根据语文成绩进行升序排列，如上图的第一个表

也可以先根据数学成绩进行降序排序，如果数学成绩相同的话，再根据语文成绩进行降序排序，如上图的第二个表

7》条件查询

关键字:where

会指定具体的条件，按照条件针对数据进行筛选

格式：select 列名 from 表名 where 条件；

遍历这个表里的每一行记录，把每一行的数据分别带入到条件中，如果条件成立，这个记录就会被放入结果集合中，如果条件不成立，这个记录就pass

运算符：

在这里插入图片描述

在sql当中没有"==“,判断两个值是否相等就是要使用”="，跟我们在java里面学习的赋值不一样

但是我们在使用"="的时候，它是null不安全的，如果是null=null的话，由于null参与了运算，最后的结果是null，就是false，但是我们需要的结果是null=null的结果是true

如何处理以上的情况呢，我们可以使用"<=>"，这个运算符是null安全的，可以进行null=null的运算，而且结果是true

"<>"是上古时期的写法了

like是模糊匹配，通过一些特殊的符号，描述出规则/特征，根据这个规则/特征来筛选数据

如何理解上面的模糊匹配呢？

比如一个人要找对象的话，这个人找对象有三个条件，第一个条件是高，第二个条件是白，第三个条件是身材好，这个人可能并不知道自己具体要去追哪个人，但是他选对象的标准已经出来了，根据这个标准筛选即可

模糊匹配也是一样，虽然不知道这个数据的具体数值是多少，但是通过一些标准来筛选这个数据，跟上面的选对象类似
在这里插入图片描述

例子：

1》查询英语成绩不及格的同学

在这里插入图片描述

2》查询语文成绩好于英语成绩的同学

上面第一个例子只有一列参与了运算，很多人认为只允许一个列参与运算，其实无论有几个列，都可以使用上述运算，如下图就有两个列参与了运算

在这里插入图片描述

3》查询总分在200以下的同学(条件查询搭配表达式)

在这里插入图片描述

但其实这里面应该还有一个唐三藏的名字，唐三藏只有一门有分数，其他两门都是null，按理来说，唐三藏的总分应该比200小，但是为什么这里面没有唐三藏的名字呢？

在计算总分的时候，唐三藏的chinese+math+english在运算的过程中，有null参与了运算，所以他的总分算出来是null，而null<200的运算结果因为有了null的参加也成了null，所以where后面的运算结果整体来说是null，就是false，所以没有唐三藏的成绩

在这里插入图片描述

如果计算总数的chinese+math+english这个表达式太复杂，我们是否可以用别名来替代它呢？

在这里插入图片描述

但是我们实际操作后可以发现是不可以用别名来替代where后面的表达式的，这是为什么呢？

这又不得不提到select条件查询执行的顺序了：

1）遍历表中的每一个记录

2）把当前记录的值，带入条件，根据条件进行筛选

3）如果这个记录条件成立，就要保留，进行列上的表达式(chinese+math+english)的计算

4）如果有order by的话，会在所有的行都被获取之后(表达式也算完了)再针对所有的结果进行排序

而我们的别名是在第三步定义的，where是在第二步定义的，所以执行where的时候，total还处于未定义的状态

因为order by是在最后一步被定义的，所以我们在使用order by的时候，别名已经在第三步的时候被定义了，所以在order by后面可以使用别名

4》查询语文成绩大于80分，并且英语成绩大于80分的同学

在这里插入图片描述

5》查询语文成绩大于80分,或英语成绩大于80分的同学

在这里插入图片描述

在sql中,and的运算符优先级更高,但是不建议大家来记这个优先级,建议使用( )

6》查询语文成绩在[80,90]分的同学及语文成绩

在这里插入图片描述

between and是闭区间

7》查询数学成绩是58或者59或者98或者99分的同学及其数学成绩

在这里插入图片描述

8》关于like模糊匹配

涉及到一些通配符,通配符就是一些特殊的字符,可以表示特定的含义

%代指任意个任意字符

_代指任意一个字符

查询姓孙的同学的成绩，名字中，以孙开头

在这里插入图片描述

%也可以指代零个字符

在这里插入图片描述

孙%：查询以孙为开头的内容

在这里插入图片描述

%孙：查询以孙为结尾的内容

在这里插入图片描述

%孙%：查询包含孙的

275)

此处的模糊查询的功能是有限的，在计算机中，进行模糊匹配字符串，还有"正则表达式"这样的方式来进行实现

javaSE有个章节String，其中有一个方法sqlit，这个方法的参数也是正则表达式，比如要以" . "来分割字符串

但是" . “是一个正则表达式，在正则表达式里，”." 表示匹配除换行符 “\n” 之外的任何单个字符。例如，正则表达式 “a.c” 可以匹配 “abc”、“a.c”、“a0c” 等字符串，但不能匹配 “ac”（中间没有字符）或 “a\nc”（包含换行符）

如果我们要表达" . “的原始含义的话，必须在前面加一个” \ “,但是在java当中，要想表达一个单独的” \ “,是要用两个反斜杠来表示的” \ \"

,因为" \ "是转义字符

所以split的参数里面如果要表达一个单独的" . "的话，前面必须加两个反斜杠

为什么mysql不用正则表达式呢？
正则表达式的匹配效率其实是很慢的，而且我们mysql本身就不快，所以就没有使用正则表达式

在这里插入图片描述

这里我们查询的条件是语文成绩为空的同学，服务器给我们返回的结果是空的，就是没有语文成绩为空的同学，但是我们可以从上面的表里面看到，明明是有语文成绩为空的同学的，这是为什么呢？

null和chinese比较的时候用的是" = "，这个运算符是null不安全的，chinese=null的结果计算下来还是null，null就是false，所以mysql会认为，没有语文成绩为空的同学

在这里插入图片描述

如果我们使用"<=>“的话，就根我们想要的结果是一样的，”<=>"是null安全的

在这里插入图片描述

也可以用is null来判断是否是空

在这里插入图片描述

"<=>"也可以用于两个列的比较，这个运算符可以针对两个列进行比较，而"is null"只能比较一个列

在这里插入图片描述

如果用"="来比较两个列的话也有风险，如果两个列都是null的话，由于null参加了运算，这个式子的结果就是null，在MySQL看来就是false，就有可能漏掉一个同学

8》分页查询

用select *这种方法查询，是比较危险的，数据非常多的话，一次全显示出来的话，会让硬盘和网卡跑满，会影响到效率，也不方便用户去看

所以我们需要保证一次查询，查出来的东西不要太多

而limit就可以限制这次查询最多可以查出来多少的数据

在这里插入图片描述

limit后面什么都不加的话，默认后面是offset 0，就是偏移量是0，就是从下标为0的数据开始查询，就是第一个数据

后面的偏移量可以是任意的数字，规定了查询的起点是哪里，而limit后面的数字规定了一次最多可以查几个数据

偏移量是计算机中广泛使用的概念，而在汇编语言里面偏移量是用来寻址的(寻找指定的内存)

编程语言，分成三个大类：

1）机器语言(计算机，cpu能认识的二进制的指令)

2）汇编语言

3）高级语言(c,java)

cpu在研发的时候，就会确定我这个cpu都支持哪些指令，市面上的cpu主要是两大类：

1）x86系列的cpu，Intel，amd，给电脑用的

2）arm系列的cpu，高通，苹果，给手机用的

这两类cpu支持的指令是截然不同的

机器语言是二进制的数据，不方便人来看，所以就引入了一些"助记符"(简单的英文单词)，用英语单词来代替各种指令的二进制形式

汇编语言中的每个代码都是和cpu支持的指令(机器语言)是一一对应的

但是由于cpu差异很大，cpu支持的指令也各不相同，对应的也就产生了不同的汇编

在学校一般是学习8086cpu的汇编语言，8086是上古神u，intel初代搞出来的一个知名cpu，intel又在8086的基础上，又搞出来了80286，80386，80486…(都是cpu的型号，类似现在的i9-13900K)，前面说的cpu结构类似，把这个系列称为x86系列

虽然现在cpux86系列是8086的后裔，实际上支持的指令已经差异很大了

3.修改

格式：update 表名 set 列名=值 where 条件；

set这个词，在计算机里面，有两种典型的含义：

1》设置，比如在类和对象里面，我们要修改private修饰的变量，要用setter方法

2》集合，TreeSet/HashSet

而我们这里的set是设置的意思

where后面的条件限制这次操作具体要修改哪些数据

在这里插入图片描述

比如上图，我们就限制了名字是’孙悟空’的同学，并且将这个同学的数学成绩改为80

第三行的Row matched意思是where条件筛选出的记录的数量

Changed意思是成功修改了几行

Warnings的意思是有几条警告

在这里插入图片描述

使用update也可以一次修改多个列

set 列名=值，列名=值…，这里的" = "相当于赋值了

在这里插入图片描述

查询总分排名里面排倒数前三的同学

在这里插入图片描述

如果将这三个同学的数学成绩各加上三十分的话，mysql会报错，为什么呢

就拿唐三藏的数学成绩来说，原来是98分，加上30分就变成了128.0分，我们在建表的时候设置的成绩的类型是decimal(3,1)，做多三位，而128.0已经四位了，所以会报错

注意mysql不支持math+=30的写法，必须写成math=math+30

在这里插入图片描述

update后面如果不加任何条件的话，就是针对所有行进行修改

如果我们想要把所有的同学的成绩变为原来的二倍的话，会报错，跟上面的情况是一样的，成绩的位数超过了decimal(3，1)允许存储的位数

在这里插入图片描述

如果我们将所有同学的语文成绩变为原来的二分之一倍的话

我们可以看到第三行服务器给我们返回的响应

where语句筛选出了8条记录，但是只改变了7条记录，还有两条警告

我们可以用show warnings；来查看这两条警告

在这里插入图片描述

这里的truncated是截断的意思

当我们将所有的同学的语文成绩除以二以后，如果有一个同学原成绩是87.5，除以二以后应该是43.75，但是我们在建表的时候设置的成绩的类型是decimal(3，1)，小数点后只能有一位，但是我们算出来的结果小数点后面有两位，超出了类型允许存储的位数，于是进行了截断，讲多出来的那一位小数截走了，并且进行了四舍五入

但是为什么我们符合where条件的记录有八条，但是只修改了七条呢

在这里插入图片描述

其中有一个人的数据是这样的，他的语文成绩是null，将其的语文成绩除以二以后，由于null参与了运算，计算出来的新的语文成绩也是null，计算前是null，计算后也是null，mysql就会认为语文成绩没有改变，所以只修改了7条记录

4.删除

格式：delete from 表名 where 条件/order by/limit；

会把符合条件的行，从表格中删除

在这里插入图片描述

例如上图，删除名字为孙悟空的同学

在这里插入图片描述

如果不指定任何的条件，就是删除整个表

delete跟drop table还不太一样，drop table是删除了表，也删除了表里面的数据，delete只是删除了表里的记录，表还在数据库里面，只是变成了空表

delete和update都是很危险的操作，delete 的条件一旦没有设置好，就会把不敢删的数据删除了，update也是一样的，这里的修改和删除都是持久生效的，都会影响到数据库服务器硬盘中的数据，所以sql里面全是危险的操作

数据库的备份，有很多的方式(备份本质上就是拷贝)：

1）数据库最终都是存储在硬盘上，以文件的形式体现(文件是二进制)，可以把这里的文件直接拷贝走放到别的机器上(全量备份)

2）mysqldump工具(mysql自带的一个程序)会把你mysql中的数据导出成一系列的insert语句，再把这些语句，放到另一个mysql中进行执行(全量/增量备份都行)

3）mysql还有一个binlog功能(把mysql中的各种操作，通过日志记录下来)，借助binlog功能(让另一个数据库按照binlog的内容执行，也能的得到一样的数据(增量备份/实时备份)

mysql服务器启动的时候，需要绑定一个端口号，想要搞多个mysql服务器的话，就要把端口号设置成不同的，数据目录也要设置成不同的

一般不会一个主机部署多个mysql服务器，因为一个mysql服务器就可以将一个主机的资源充分利用

5.数据库的约束

有的时候，数据库中的数据，是有一定要求的，有些数据被认为是合法的数据，有些数据被认为是不合法的

也可以通过人工检查来约束，但是人相对于电脑来说是不太靠谱的

数据库自动对数据的合法性进行校验检查的一系列机制就是约束

目的就是为了保证数据库当中能够避免被插入/修改一些非法的数据

mysql提供了一下约束

在这里插入图片描述

not null指示这一列的值不能为空，必须是必填的，不是选填项

unique就是这一列里面不能有重复值

default可以指定没有给列赋值时的默认值，默认的默认值是null，比如在插入数据时，我们指定一些列插入，剩下哪些没有被指定的列就会被赋值为默认值，如果我们设置了默认值，就会被赋值我们设置的值，如果我们没有设置默认值，就会被赋值为mysql默认的null

primary key就是跟人的身份证一样，不能为空，也不能重复

foreign key描述了两个表的约束关系

check的作用跟where差不多，也可以在后面加一些逻辑运算符

数据库的约束不仅是在限制插入，也会限制修改

1》not null

在这里插入图片描述

如果在建表的时候没有做限制，mysql是默认可以插入值为null的数据的

在这里插入图片描述

但是如果我们一开始在建表的时候，就设置了约束not null，约束此列(id)的值不能为null，就让这一类从选填项变成了必填项

当我们插入null的时候，mysql就会报错，如上图

在这里插入图片描述

当设置了约束之后，不论是插入一个值，还是修改一个值，这个新的数据都必须符合约束

2》unique

在这里插入图片描述

当没有设置unique约的时候，表里面是允许插入重复相同的数据的

在这里插入图片描述

但是当我们设置了unique的约束以后，插入的值不能的记录的id不能是重复的

在这里插入图片描述

这里的entry是什么意思?

entry有入口和条目的意思，我们在数据结构Map那里学习了entry

java如果要遍历集合类，都是通过迭代器来进行的，对应的集合类，得实现Iterable接口才能够进行，但是Map没有实现Iterable

接口，于是通过entrySet方法将Map转化为Set，Set实现了Iterable接口，Sel里面的一个元素就是Entry(条目，包含了key和value)

而我们这里的entry也是条目的意思

unique约束，会让后续插入/修改数据的时候，都会触发一次查询操作，通过这个查询，来确定当前这个记录已经存在

所以数据库引入约束之后，执行效率就会收到影响，就可能降低很多

这就意味着，数据库其实是比较慢的系统，也比较吃资源的系统，部署数据库的服务器，很容易成为一整个系统的"性能瓶颈"

3》default

在这里插入图片描述

描述这一列的默认值，默认的默认值是null，可以通过default约束来修改默认值

在这里插入图片描述

后续插入数据的时候，default就会在没有显示指定插入的值的时候生效了

上述设置约束的时候，都是先删表，再重新创建表，能不能不删表，直接设置约束呢？

也是可以的

但是比较麻烦，alter table可以修改表结构，可以删除列，添加列，修改列的名字类型，修改列的约束

在这里插入图片描述

可以实现一个列有多个约束，比如上图

3》primary key

这是最重要的约束，是一个记录的标识，就像一个人的身份证一样

一张表里面只能有一个primary key

一个表里的记录，只能有一个作为身份标识的数据，假设你在这个表里面设置了两个身份标识，其中一个是相同的，另一个是不相同的。那这两个记录到底是不是一个记录

在这里插入图片描述

当一个表里面设置了两个身份标识的时候，MySQL就会报错

虽然只能有一个主键，但是主键不一定只是一个列，也可以用多个列共同组成一个主键(联合主键)

在这里插入图片描述

primary key的功能相当于not null和unique

在这里插入图片描述

当id被设置为身份标识之后，id那一列的Null变成了No，意思是不能为空，变成了必填项，类似于not null的作用

Key多了PRI就是primary key的意思

对于有主键的表来说，每次插入/修改表的数据，都会涉及到进行先查询的操作(跟unique类似)

这里我们所说的查询，不是从头到尾把表里的数据遍历一遍，mysql会把带有unique和primary key的列，自动生成索引，从而加快查询的速度

如何确保主键的唯一呢？

mysql提供了一种"自增主键"这样的机制

主键经常会用int/bigint，程序员插入的时候，不必手动指定主键的值，由数据库服务器自己给你分配一个主键，会从1开始，依次递增的分配主键的值

在这里插入图片描述

当设置了自增主键以后，在表的结构里面额外一列就会有提示

在这里插入图片描述

当我们插入数据时，把id写作null并不是要插入null的值，写作null其实是交给数据库服务器自行分配

在这里插入图片描述

当我们设置了自增主键后，还是可以自己指定别的值来插入数据，如上图

但是当我们想再用mysql的自增机制插入数据的时候，mysql给新数据分配的id是4还是11？

在这里插入图片描述

如上图，当再次插入数据时，mysql给新数据分配的id是11，就是从表里的最大数值开始继续往后分配的

相当于使用了一个变量，保存了当前表的id的最大值，后续分配的自增主键都是根基这个最大值来分配的，如果手动指定id的话，也会更新最大值

4_{9之间的id于是就浪费掉了，但是如果我们手动指定4}9的id还是可以的，但是自动分配就分配不到了

此处id的自动分配也是有一定局限性的，如果是单个mysql的服务器没有问题，如果是一个分布式系统，有多个mysql服务器构成的集群，依靠这个自增主键就不行了

分布式系统是什么呢，面临的数据大(大数据)，客户端的请求比较大(高并发)，一台机器搞不定，就需要多台机器(分布式)

一台服务器的主机，硬盘空间是有限的，当需要存储的数据非常大的时候，某一个表或者几个表数据量非常大，比如淘宝里面的商品表，淘宝里面的商品非常多，而且时刻都在增加，一台服务器的硬盘可能装不下，就可以引入多个数据库服务器的主机，分库分表，将一个表分成几个较小的表，分别放到多个服务器当中，比如一个表的数据是100TB，就可以把这个表分成三份，一份33TB，分别放到三个机器当中

但是问题来了，此时新增了一个商品，商品的id如何分配呢？

肯定是要把这个记录保存在某个数据库的表中，但是如何保证这里的id跟其他数据库里面的id不重复呢？机器A并不知道机器B的情况，这种情况很有可能造成id重复的情况

这时就要用到分布式系统当中生成唯一id的算法

公式：id=时间戳+机房编号/主机编号+随机因子

如果添加商品的速度比较满，每个商品添加的对应的时间不一样，但是实际上的速度时非常快的，可以一个时间戳之内添加多个商品

但是再分布式系统中同一时间添加的商品，是可能添加到不同的数据库服务器上的，如果我们再在后面加上商品添加到的服务器机器的主机编号的话，就可以保证同一时间内添加到不同主机上的商品的id是不同的

但是如果是同一时间内添加到了同一个机器上的话，上面得出的结论就不行了，但是如果我们再在后面一个随机因子，就是随机生成一个随机数的话，形成两个重复id的概率就会小很多了

于是就得到了分布式系统中生成唯一id的方法

这里随机因子有一定概率生成相同的因子，但是概率比较小

注意公式里面的"+"不是算术相加，而是字符串拼接

但是如果同一个ms之内，给同一个机器上添加多个商品，而且随机因子恰好相同了

从理论上来说，这样的情况是可能存在的，概率不为零，但是咱们是程序员，做的是工程，不是理论的计算，做工程一定会涉及到误差，只要误差在合理的范围之内，就可以忽略不计

4》foreign key

描述了两个表之间的关联关系

在这里插入图片描述

比如现在有两个表，一个班级表，一个是学生表

学生表里有一列班级id，对应的班表里面的classid，学生表里面的班级必须是在班级表里面存在的

外键就是用来描述这样约束的过程的

calss表中的数据，约束了student表里面的数据，就把class表称为父表(约束别人的表)，把student表称为子表(被别人约束的表)

在这里插入图片描述

我们创建一个班级表，并且在里面添加三个班级的信息

在这里插入图片描述

我们再创建一个学生表，再插入一个学生的信息，此学生的班级的id是200，但是我们先前创建的班级表里面是没有id为200的这个班的，一个学生的班级id理应应该在班级表里面找到对应的班级，这并不符合我们的常识

如何解决这种问题呢，这就要用到我们的外键了

在这里插入图片描述

我们把刚才的学生表删除了，再创建一个新的学生表，这个新的学生表设置了外键约束，此时班级表里面的班级id就会对插入新的学生的班级id进行约束

在这里插入图片描述

当我们插入一个班级id为200的学生的时候，由于班级表里面就不存在班级id为200的班级，于是就会报错

在这里插入图片描述

以上就是设置外键的格式

references有引用的意思，此处表示当前这个表的这一列数据引用了另一个表的某一列的数据，简单来讲就是，当前这个表的这一列数据应该全部出自于另一个表的某一列，比如上面的例子，学生表里卖的classid这一列应该全部出自于班级表里面的classid这一列

foreign key()括号里面是当前表中被约束的列a，class()括号里面的这一列b，列a是被列b所约束的

在这里插入图片描述

执行这个操作就会触发对class表的查询，就会查询id为200的班级是否在class表里面存在

在这里插入图片描述

如果插入的值没有查询到的话，就会报错

在这里插入图片描述

如果是要修改数据的话，也会触发对class的查询，会查询插入的值是否在class的表里面存在

以上我们讲的都是父表约束子表，但是同时子表也会约束着父表，就是“言传身教”，父亲的行为也影响着孩子的行为，所以父亲要端正自己的言行，这时也可以理解为儿子也约束着父亲

而在数据库中，如果针对父表进行删除/修改操作的话，如果当前被修改/删除的值已经被子表应用了，这样的操作也会失败，这就是子表也在约束着父表

外键约束始终要保持子表中的数据在对应的父表的列中要存在，此时如果把父表的一条数据删除了，而父表的这条数据恰好又被子表应用了，如果可以删除或者修改的话，子表中的数据就不能保证都在父表中了，也就打破了我们的外键约束了

在这里插入图片描述

如上表，student表里面有一个classId为100班的学生，class表里面只有一个100班被学生表应用了，如果我们要在class表里面删除100班的话，MySQL是不允许我们删除的，会报错，如下图

在这里插入图片描述

但是如果我们要删除的父表的数据没有被子表引用的话，是可以被删除的，比如我们删除101班

在这里插入图片描述

那么可以修改父类class的班级名吗？也就是name一列

外键准确来说，是两个表的列产生关联关系，其他列是不受影响的，如下图

在这里插入图片描述

如果尝试drop table class，是否能删除整个表呢？
要想删除表的话，就得删除记录，此处的删除，是必然不可以的，父表没了，子表后续添加新的元素就没得参考了，如下图

在这里插入图片描述

但是如何删除父表呢，那可以先删除子表，再删除父表

在这里插入图片描述

如果我们把子表删除为空的话，不删除子表，如果想要删除父表，也是不可以的，如上图

在这里插入图片描述

如果我们以上图的方式创建外键的话，MySQL会提示无法建立外键，这是为什么呢？

我们可以发现，我们在创建class表的时候并没有设置主键，但是我们之前创建的时候都是将被引用的列设置为主键所以原因就是calssId这一列没有设置主键的原因

所以我们指定外键约束的时候，要求父表中被关联(引用)的这一列，得是主键或者unique

考虑现在是一个电商网站的场景，有两个表

商品表(goodsId int,…)

订单表(orderId int,gooodsId int,…,foreign key(goodId) references 商品表(goodsId))

现在商品表里面有一个goodsId为1的男士衬衫

订单表里面有一个orderId为100，goodsId为1的订单

过了一段时间以后，商家想把这个衬衫下架掉(删除掉)，如何完成删除呢？

尝试删除父表的数据的时候，如果父表的数据被子表引用了的话，是不能删除的，如果要删除的话，就会报错

但是我们又不能真的把订单表里的订单删除，比如淘宝app，用户在购买商品之后，购买的订单记录在app里面仍然可以看到，我们又如何做到不删除订单表里面的记录，从而实现这个功能呢？

但是这样的话，电商网站如何做到，保证外键存在的前提下，实现商品下架的功能呢？

可以给商品表新增一个单独的列，表示商品是否在线(不在线的话，相当与下架了)

商品表(goodsId int,name varchar(20),…,isOk int)

如果isOk的值为1，表示商品在线

如果isOk的值为0，表示商品下线

如果需要下架商品的话，使用update把isOk的值从1变为0即可

然后查询商品的时候，都加上where isOk=1这样的条件，这样用户即搜索不到已经下架的商品，但是又不会删除商品的数据

这样的删除其实是逻辑删除

电脑上有个文件，你想删除掉，也是通过逻辑删除的方式实现的

在硬盘上数据其实还有，就是被标记成无效了，后续其他文件就可以重复利用这块空间了

想把电脑的某个文件彻底删除掉的话，通过扔进回收站，清空回收站，都是没有用的，硬盘上的数据什么时候消亡，就需要时间，需要后续有文件把这块标记无效的空间重复利用，才会真正消失

什么才是正确的，彻底删除数据的方式呢？

物理删除->把硬盘砸了

表的设计

根据实际的需求场景，明确要创建几个表，每个表啥样子，这些表之间是否存在一定联系

1.梳理清楚需求中的“实体”，就是咱们所说的“对象”，需求中的关键词，比如学生，班级，作业，课程等等

一般来说，每个实体，都需要安排一个表，表的列就需要对应到实体的各个属性

2.再确定好实体之间的关系

不同的关系，设计表的方式也是不同的：
1》一对一

2》一对多

3》多对多

4》没关系

1.一对一

一个学生只能拥有一个账号

一个账号也只能被一个学生拥有

在这里插入图片描述

可以把studentId写到账号表里面

在这里插入图片描述

也可以把accountId写到学生表里面

在这里插入图片描述

也可以把两个表合并为一个表，将两个表的所有列合并在一起

2.一对多

一个学生，只能在一个班级中

一个班级，可以包含多个学生

在这里插入图片描述

可以有以上两种设计的方法

由于MySQL数据库不支持数组这样的类型，所以MySQL就不能用左边的方法来实现了，只能用右边的方法来实现

左边的写法，像redis这样的能够支持数组类型的数据库，就可以使用

3.多对多

一个学生可以选择多门课程

一门课程也可以包含多个学生

在这里插入图片描述

如果上述三种关系都无法套进去，此时这样的表就完全没有山关系，各自独立设计即可，不必考虑对方

查询操作的进阶

1.查询可以搭配插入使用

把查询语句的查询结果，作为插入的数值

在这里插入图片描述

此处要求查询出来的结果集合，列数/类型要和插入的这个表匹配

2.聚合查询

表达式查询，是针对列和列之间进行运算的

聚合查询，相当于是在行和行之间进行运算了

sql中提供了一些“聚合函数”通过聚合函数来完成上述之间的运算

在这里插入图片描述

sql可以通过–表示注释，但是–后面至少要有一个空格

另外也支持# 开头作为注释

现阶段我们写的sql是直接在控制台输入的，但是实际开发中，经常会把sql语句写入到一个文件中(方便后续反复多次执行)

一般执行的时候就可以直接把sql的内容一复制，粘粘到MySQL控制台即可

也可以通过重定向的方式，让MySQL客户端读取sql文件，但是在windows中这么操作比较麻烦

在这里插入图片描述

先执行select *，再针对集合进行统计(看看具体有几行)

在这里插入图片描述

如上图，我们通过select * 底下的服务器给我们的响应，已经能知道这里的行数了，那为什么还要用count呢？

这是MySQL内置的功能，如果你是通过代码来操作MySQL服务器，就没有这个功能了

另外count(*)得到的结果还可以参与各种运算，还可以搭配其他sql使用

在这里插入图片描述

也可以根据列名来进行查询

但是如果当前列里面有null，两种方式计算的count就不同了

在这里插入图片描述

如果是count(*)的话，遇到null也会算进去

但如果是count(mane)的话，遇到null就不算数了

用count指定具体列的时候，是可以进行去重的吗，如下图

在这里插入图片描述

很多语言中。函数名和后面的()中间的空格是不做要求的

在这里插入图片描述

但是MySQL不允许聚合函数跟后面的括号之间有空格

为什么呢？

因为MySQL里面可以给列起别名，而且起别名的关键字“as”是可以省略的

如果中间有空格的话，MySQL可能会认为count是一个列的名字，后面的(*)是别名，中间的“as”是被省略掉了

这样的话，MySQL就分不清我们写的到底是一个聚合函数，还是别名

在这里插入图片描述

sum聚合函数，是把这一列的若干行给进行求和(算术运算)，只能针对数字类型使用

虽然字符串可以相加，但是不是"算术相加"

在这里插入图片描述

sum在进行算术运算的时候，会把null排除掉

如果非要将字符串类型的数据用sum来计算的话

在这里插入图片描述

结果会是0，并且会有四行警告，有问题，但是不严重

我们可以用show warnings来查看警告是什么

在这里插入图片描述

MySQL会尝试把这一列给转换成double类型，如果转成了的话，就可以进行运行算，如果没有被转成的话，就会报错

在这里插入图片描述

但是如果字符串里面是纯数字的话，MySQL是可以将其转化为double类型的，如上图有一个班级的名字是200，MySQL就可以将其转化为数字，其他行不能转化，就会返回0，0加上200，结果就是200

在这里插入图片描述

sum也可以对表达式进行求和

其他聚合函数的例子

在这里插入图片描述

综上所述，sql还可以进行一些简单的统计操作

3.分组查询

使用group by进行分组，再针对每个分组，再分别进行聚合查询

针对指定的列进行分组，把这一列中，值相同的行，分到一组中，得到若干个组，针对这些列分别使用聚合函数
在这里插入图片描述

如果针对分组之后，不使用聚合函数，此时的结果就是查询出每一组中的某个代表数据，比如上图中程序员查询出来的值就是张三的工资

分组查询还是要搭配聚合函数使用的，否则这里的查询姐结果是没有意义的

由于role这一列是group by指定的列，每一组所有的记录的role一定是相同的

这种情况就是MySQL的配置项强制使用使用group by的时候要搭配聚合函数(如下图)

在这里插入图片描述

有些程序功能是非常复杂的，会提供很多的功能，有时候只想使用其中的一些功能，有些功能是有很多选项，是想使用其中的一个选项，就可以使用配置文件来进行设置，使程序能够按照咱们的要求去运行

使用group by的时候，还可以搭配条件，但是需要区分清楚是分组之前的条件还是分组之后的条件

1）查询每个岗位的平均工资，但是排除张三(直接使用where即可，但是where句子一般写在group by的前面)

在这里插入图片描述

2)查询每个岗位的平均薪资，但是排除平均薪资超过两万的结果(使用having描述条件，但是having一般写在group by之前)

在这里插入图片描述

3）在group by可以一个sql同时完成这两类条件的筛选

查询每个岗位的平均值，不算张三，并且保留平均值小于两万的结果

在这里插入图片描述

4.联合查询(多表查询)

前面的查询都是针对一个表来进行查询，相比之下，有些查询，则是一次性需要从多个表中进行查询

联合查询的关键思路就是在理解"笛卡尔积"的过程，就是排列组合

笛卡尔积就是通过排列组合的方式得到一个更大的表，笛卡尔积的列数是这两个表的列数相加，笛卡尔积的行数是这两个表的行数相乘

在这里插入图片描述

但是如果仔细观察的话，可以看到有一些数据是非法的(不符合实际情况，无意义的数据)

笛卡尔积是简单无脑的排列组合，把所有的情况尽可能都穷举了一遍，就包含一些合法的数据也包含非法的无意义的数据

进行多表查询的时候就需要把有意义的数据筛选出来，无意义的数据过滤掉

如何通过sql的条件筛选出来呢

where 班级表的id=学生表的classid(连接条件)

1》查询"许仙"同学的成绩，这里面涉及到两个表，一个是学生表，一个是成绩表

这时我们要查询一个学生的成绩的时候，就是针对两个表来查询

1）先把这两个表进行笛卡尔积

在这里插入图片描述

然后就得到了以下的表

在这里插入图片描述

(先取哪一个表和另外的表的所有记录排列组合都可以)

2）加上连接条件，筛选出有效的信息

学生表的id=分数表的student_id

在这里插入图片描述

当前student和class的表里面没有重复的列，但是不能保证就就一定没有重复的列，所以建议写作：表名.列名

加粗样式

3）结合要求，进一步添加条件，针对结果进行查询

此处就是查询许仙的成绩

就可以加上一个student.name=‘许仙’

在这里插入图片描述

4）针对查询到的列进行精简，只保留需求中关心的列

在这里插入图片描述

2》查询所有同学的总成绩，及同学的个人信息

之前都是通过表达式查询来完成总成绩的计算(如下图)

在这里插入图片描述

此时同学的成绩是按照行来组织了，此处就是多行数据进行加和

需要基于联合查询和聚合查询进行查询

1）先进行笛卡尔积

在这里插入图片描述

2）根据连接条件筛选出有用的信息

3）先精简列

在这里插入图片描述

4）针对以上的结果，再进行group by的聚合查询

在这里插入图片描述

3》查询每个同学每门课的成绩

1）先机进行笛卡尔积

在这里插入图片描述

此时得到的结果非常多，是三个表行数进行乘积的结果

一旦表的数据大，或表的数目多，此时得到的笛卡尔积就会非常大，因此，如果针对大表进行笛卡尔积，就会生成大量的临时结果，这个过程是非常消耗时间的

如果多表查询涉及到的数目比较多的时候，此时sql就会非常复杂可读性也大大降低了

2）指定连接条件

三个表涉及到两个连接条件

在这里插入图片描述

3）精简列

在这里插入图片描述

上述操作都是基于内连接

mysql来说，进行多表查询，还可以使用外连接(左外连接，右外连接)

如果这两个表里面的记录都是对应关系，内连接和外连接的结果都是一样的

如果存在不对应的记录，内连接和外连接就会出现差别

在这里插入图片描述

studnet中的每一条记录，都可以在score表中找到对应

每一个score中的记录，也可以在student表里面找到对应

在这里插入图片描述

上述写法得到的结果就是内连接

在这里插入图片描述
)

这种写法和上面的写法等价，也是得到一样的内连接结果了

左外连接：left join

右外连接：right join

左外连接就是以左侧表为基准，保证左侧表的每个数据都会出现在最终结果里面，如果在右侧表里面不存在，对应的表就填成null

在这里插入图片描述

右外连接就是以右侧表为基准，如果左侧表中不存在，对应的列就填成null

在这里插入图片描述

自连接

一张表，自己跟自己进行笛卡尔积

有时候需要去进行行和行之间的比较，只能进行列和列之间的比较，此时我们就可以使用自连接把行关系转化为列的关系

显示所有“计算机原理”成绩比“java”成绩高的成绩信息

这里我们要比较行和行之间的关系，我们让成绩表自己跟自己进行笛卡尔积，就可以完成行到列的转化了

在这里插入图片描述

但是我们将此表自己跟自己笛卡尔积之后发现mysql报错了

此时我们跟两个表分别起一个别名就不会报错了

在这里插入图片描述

然后我们加上连接条件，筛选掉没有用的信息

在这里插入图片描述

然后我们加上限制条件

在这里插入图片描述

所有工作做完以后，我们开始精简列

在这里插入图片描述

如果我们还想要学生的姓名的话，就需要学生表里面的信息，就可以拿上述表跟学生表进行笛卡尔积

如果想要课程的名字，就需要课程表里面的信息，我们就可以让上述表跟课程表进行笛卡尔积

5.子查询

子查询本质上就是在套娃

把多个简单的sql拼接成复杂的sql

单行子查询：返回一行记录的子查询

1）查询与“不想毕业”同学同班的同学

在这里插入图片描述

子查询的结果必须是一行记录

多行子查询

使用关键字in

1）查询”语文“或”英语课程的成绩信息

可以使用联合查询

在这里插入图片描述

或者先通过课程名字找到课程的id，再通过课程id在分数表中进行查询

在这里插入图片描述

也可以使用子查询

在这里插入图片描述

6.合并查询

把多个sql查询的结果集合并到一起

union关键字

再c语言里面就右一个自定义类型联合体union

查询id小于3，或者名字为“英文”的课程

在这里插入图片描述

允许把不同的两个表，查询结果合并在一起

合并的两个sql的结果集合的列需要匹配，列的个数和类型是要一致的(列名不需要一致)

合并的时候是可以去重的

要是不想去重的话，可以使用union all

在这里插入图片描述

Mysql索引事务

数据库使用select查询的时候

1.先遍历表

2.把当前的行给带入到条件中，看条件是否成立

3.条件成立，这样的行就保留，不成立就跳过

如果表非常大的话，这样遍历的成本就比较高了，而我们的数据库是把数据存储在鹰派上，每次读取一个数据，都要读取硬盘

而读取硬盘的开销本来就比较大

索引是针对于查询操作引入的优化手段，可以通过索引来加快查询的速度，避免针对表进行遍历

索引是能提高查询速度，但是也是有代价的：

1）占用更多的空间，生成一些索引是需要一系列数据结构的，以及一些额外的数据，来存储在硬盘中的

2）但是可能会降低插入修改的速度

既然索引会降低插入修改的速度我们为什么还要引入索引呢？

实际开发中，查询(读)的频率，比插入查找删除(写)的频率，高的多

比如教务系统，学生的信息，每天都要进行很多次的查询，啥时候修改，写错终究是少数，修改的频率非常低

1.查看索引

格式：show index from 表名；

主键，unique，外键都是会自动生成索引的

在这里插入图片描述

第二列是双重否定的，0就表示是唯一的

第五列就代表这个索引是指对哪一个列来指定的，只有针对这一个列进行条件查询的时候，查询的速度才能够被索引优化，就是说如果根据id来查询的话就会很快，但是如果根据其他列来进行查询的话就不会用到索引

在这里插入图片描述

第五列第一行是主键自动生成的索引

第二行是外键自动形成的索引

一本书可以有多个目录，比如一个词典，就有很多种目录。拼音目录，笔画目录，部首目录等等，一个表也可以有多个索引

2.创建索引

格式：create index 索引名字 on 表名(列名)；

在这里插入图片描述

创建索引操作，也是一个非常危险的操作

创建索引的时候。需要针对现有的数据，机械能大规模的重新调整

如果当前是一个空表的话，或者数据不多，创建索引都没啥问题

如果这个表本来就很大的话，创建索引，也很容易就把数据库给卡住

一般来说，创建索引都是在创建表的时候就规划好的，一旦表已经使用很久了，有很多数据了，再想修改索引的话，就要慎重了

非得创建也不是不行，就需要使用一些其他的技巧

一个做法：另外再搞一个机器，部署MySQL的服务器，也创建同样的表，并且把表上的索引创建好，再把之前机器上的数据导入到新的MySQL服务器上(导入数据的过程可以控制节奏)，多花点时间导入数据都没事，不要影响到原来的服务器的正常运转，当所有的数据都导入完毕，就可以使用新的数据库，替换旧的数据库了

3.删除索引

格式：drop index 索引名 on 表名；

手动创建的索引可以手动删除

但是如果是自动创建的索引(主键/外键/unique)就不能删除了

索引的原理

索引也是通过一定的数据结构来实现的

数据库引入的索引是一个改进的树形结构，B+树(n叉搜索树)

再了解B+树之前，先要了解B树

在这里插入图片描述

每个节点的度都是不确定的

每一个节点上保存n个key就划分出n+1个区间

每个区间就可以衍生出一系列子树了

由于每个节点是在一个硬盘的区域当中，一次读取硬盘就读取了整个节点(多个key)，再进行几次比较(读取一次硬盘，相当于1w次比较)

一个节点当中，虽然是可以保存n个key，但是也不是没有限制的，达到一定的规模，就会触发节点的分裂

当删除元素达到一定的程度的时候，就会触发节点的合并

1）B+树也是一个N叉搜索树，一个节点上存在n个key，换分成n个区间

2）每个节点上的n个key中，最后一个，就相当于当前子树的最大值

3）父节点上的每个key都会以最大值的身份在子节点的对应区间中存在(key可能会重复出现)

叶子节点这一层，就直接包含了整个树的全集

4）B+树会使用链表这样的结构，把叶子节点串起来

此时就可以非常方便的完成数据集合的遍历，而且也很方便的从数据集合当中按照范围取出一个“子集”

对于B树来说，要查询的元素如果再根节点或者层次比较高的节点，就能非常快的拿到元素，但是如果要查询的元素再叶子节点的话，此时化的时间就比较多，而且B树的每个节点都存储着一条记录，不稳定

对于B+树来说，非叶子节点只是存储着key值，所有记录都是存储在叶子节点里面，所有查询都要终止到叶子上进行查询，无论哪个元素整体时间开销都是差不多的，稳定

这一行数据，内容是比较多的，而这以一个id内容是比较少的，叶子节点会非常占用空间，非叶子节点则占用不了多少空间，而这些非叶子节点会缓存到内存中(硬盘上还是要存这些叶子节点的，但是当我们进行查询的时候就可以把这些非叶子节点加载到内存中，整体查询的比较过程就可以在内存中进行了，又进一步减少了io的访问次数)

事务

开发中经常会涉及到一些场景，需要"一气呵成"的完成一些操作

比如说转账，张三给李四转了500元，我们就要在张三的余额里面减500，在李四的余额里面加500，但是如果执行到一半，程序崩溃，数据库崩溃，张三的余额里面减少了500，但是李四的余额里面却没有增加500

此时，数据就会出现不上不下的中间状态

引入事务就是为了避免上述问题

事务就可以将多个sql打包成一个整体，要么保证这些sql全部执行，要么一个都不执行

但是不是真的一个都没有执行，必然执行，才知道失败，只是看起来好像"一个都没有执行一样”

关键操作就是翻新，此处在数据库中称为“回滚”

事务把这多个sql打包到一起，作为一个整体来执行，这样的特点，称为“原子性”

在这里插入图片描述

rollback一般是要搭配一些条件判断逻辑来使用，sql里面也能支持条件，循环，变量，函数，但是日常开发一般不会这么写，更多的是搭配其他编程语言

回滚是如何做到的？

其实是日志的方式，记录事务中的关键操作，这样的记录就是回滚的依据

日志会打印出来操作的内容在文件里，即使是主机掉电，也不影响(回滚用的日志已经在文件中了)，一旦重新启动主机，MySQL也重新启动，就会发现回滚日志里面又一些需要回滚的操作，于是就可以完成这里的回滚了

事务不仅仅是有原子性还有其他方面的一些特性

1）原子性，回滚的方式保证这一系列操作都能执行正确，或者恢复如初

2）一致性，事务执行之前，和之后，数据都不能太离谱

3）持久性，事务做出的修改，都是在硬盘上持久存在的，重启服务器，数据依然是存在，事务执行的修改仍然是有效的

4）隔离性，这是数据库并发执行多个事务的时候涉及到的问题

MySQL是一个客户端服务器结构的程序，一个服务器可以给多个客户端提供服务，多个客户端都会让数据库执行事务，很有可能客户端1提交事务1，执行了一半，客户端2提交的事务2也来了，数据库服务器就需要同时处理这两个事务，并发执行

在这里插入图片描述

但是数据并发执行多个事务的时候，就会涉及到一些问题

如果我们希望数据库服务器执行效率高的话，就要提高并发程度，但是提高了并发程度之后。可能会存在一些问题，导致数据出现一些“错误”的情况

隔离级别就额是在“数据正确”和“效率”之间做权衡，往往提升了效率就会牺牲正确性，提升了正确性就会牺牲效率

1.脏读问题

在这里插入图片描述

这种情况就称为"脏读"，一个事务A正在写数据的过程，另一个事务B读取了同一个数据，接下来事务A又修改了数据，导致B之前读的数据，是一个无效/过时的数据(脏数据)

这俩事务如果一个接着一个的串行执行，没事，如果并发执行就容易出现脏读

解决脏读问题，核心思路就是针对写操作加锁(我和同学约定好，我写代码的时候，你不要来读)

等我确认写完了，提交到码云上，你从我的码云上来看

之前我和同学是完全并发执行的，但是约定了写加锁之后，并发程度就降低了，但是读到的数据更靠谱了

并发性降低了，隔离性提高了，效率降低了，数据准确性更高了·

2.不可重复读

在这里插入图片描述

并发执行事务的过程中，如果事务A在内部多次读取同一个数据的时候，出现不同的情况，这种就是不可重复读，事务A在两次读的之间，有一个事务B修改了数据并提交了事务

如果是两个事务，分两次读，结果不一样，这个能接受

如果是一个事务内部的两次读，结果不一样，就难以接受了

刚才写加锁，只是要求大家不要看我的屏幕，在我写的过程中不要读，但是没说你们读的时候我们就不能继续写

解决不可重复读，就需要给读操作加锁(也就是约定同学们读的时候，我也不能写)

并发程度又进一步降低了，隔离性也进一步提高了，效率降低了，数据准确性又提高了

隔离性也进一步提高了，效率降低了

如果两个事务之间的影响越大，隔离性就越低，影响越小，隔离性就越低

3.幻读

在这里插入图片描述

一个事务A执行过程中，两次读取的操作，数据内容虽然没有变，但是结果集变了

具体这个情况不是问题，具体场景具体分析

引入串行化的方式，解决幻读，保持绝对的串行执行事务，此时完全没有并发了

釜底抽薪，根本上解决了并发中涉及到的各个问题，此时，并发程度最低(没有并发)，隔离性最高，效率是最低的，数据却是最准确的

在这里插入图片描述

为什么B+树要比B树效率更高？

如果是树形结构的话，没有链式结构，去找下一个元素，就涉及到一系列节点回溯的问题，每次回溯就是一个额外的硬盘开销

JBDC

第一步我们先创建DataSource

在这里插入图片描述

url是计算机中非常重要的概念，表示网络上的资源位置

MySQL是一个客户端服务器结构的程序，是通过网络进行交互的，我们要通过java来操作MySQL的话，就要找到MySQL在网络上的位置

url其实就是我们平常所说的网址

在这里插入图片描述

这一段字符串是什么意思呢？

在这里插入图片描述

上图的部分表示url是干什么用的，是给jbdc操作MySQL用的

在这里插入图片描述

这一部分是ip地址，描述网络上一个主机所在的位置，计算机中的地址，是用一段数字来表示的，描述了MySQL在网络上的位置

而127.0.0.1是一个特殊的ip地址，叫做“环回ip”

在这里插入图片描述

我们电脑上一般有会有网线，如果我们想在网络上发送消息的话，就会通过网线发送出去

但是如果我们把网线的两端都插到一个电脑上的话，电脑上发出的信息又会发回来

自己把数据发给自己，就叫做“环回”

虽然又这种专门的环回网线，但是也可以通过软件来实现环回的效果

每个机器上都有环回ip，只要把消息发给这个ip,j就等于发送给自己

由于我们目前的jbdc的程序和MySQL服务器是在同一个电脑上，使用环回ip即可

如果你的数据库程序和你的应用程序在不同的主机上的话，此时就该写对应主机的ip

在这里插入图片描述

后面紧跟着的3306是端口号，上面的ip地址指示一个大概的地址，而端口号是收件人的电话，可以帮我们更加详细的找到MySQL

为什么要有端口号呢？

一个主机里面有很多个应用程序，比如我们的电脑里面就有很多的软件，比如微信，qq音乐等等，如果我们要往一个软件上发送数据的话，有了ip地址，我们只是知道该发给哪一个主机了，但是并不知道该发给哪一个程序，这时我们就需要端口号了，每一个程序都有端口号，用这些端口号我们就可以知道要发给哪一个程序了

而我们部署MySQL的主机上可不只有MySQL这一个程序，通过MySQL的端口号我们就可以把数据通过网络发给MySQL

在这里插入图片描述

后面紧跟的就是要访问MySQL上的哪一个数据表了

在这里插入图片描述

这是设置一个字符集

注意这里是满血版的utf-8，不能写utf8mb4，utf8mb4是数据库里面特有的字符集，在MySQL里面可以用utf8mb4，但是在jdbc里面不可以用，因为jdbc根本不认识这个字符集，只认识utf8

在这里插入图片描述

这里的意思是是否要加密

建议填false

这里如果设为true的话可能会连接不上服务器

在这里插入图片描述

java后面的问号，是干什么的？

？后面表示访问资源的时候，需要哪些参数

而&用于连接各个参数

这里DataSource光写url的话只是能找到MySQL服务器了，但是还得认证(用户名，密码)、

在这里插入图片描述

这里的root是MySQL里面默认自带的用户，是管理员的意思，平常我们使用cmd客户端的时候只需要输入密码就可以，那是因为MySQL默认帮我们输入了用户名，如果我们使用别的第三方的客户端的话就需要手动输入用户名了

在这里插入图片描述

这是输入你在MySQL里面设置的密码

但是以上的都是准备工作，还没有给MySQL服务器进行通讯呢

2.和数据库建立连接

跟我们平常的打电话一样，我们把电话打出去，对方接听了，这才叫建立了连接

如果对方没有接听的话，我们说的话对方根本听不到，这时没有建立联系

在这里插入图片描述

这是建立联系的过程

但是我们发现getConnection方法下面有报错

这是jbdc中常见的异常，如果执行sql或者操作数据库的过程中出现问题，一般会抛出这个异常

这里我们要区分连接和链接的区别

连接是两个程序产生关系、

链接就是快捷方式，就是我们电脑里面常说的快捷方式

3.构造sql

即使是使用代码操作数据库，依然是依赖sql完成

以前学过的各种sql在这里是同样适用的

在这里插入图片描述

prepared是准备好的/预处理的

statement是语句的意思

PreparedStatement就是已经处理好的语句的意思

我们其实也是有办法把字符串格式的sql直接发给MySQL的，但是这么做的话不太好

因为我们写的sql是有可能存在语法错误之类的

我们如果直接把字符串形式的sql发给MySQL的话，MySQL就需要对sql进行解析和校验

但是MySQL不只有一个客户端呀，每一个客户端都需要MySQL对sql进行检查的话，对MySQL的负荷就会很大

而我们这里的PreparedStattement就是解决这个问题的，它会先检查这个sql是不是有什么问题，解析完毕之后，也会得到结构化的数据，直接把解析好的结构化的数据发给数据库服务器，服务器省下了这部分解析工作

结构化数据是什么呢，在编译原理(编译器的内部原理)里面，我们写好的语言源代码其实都是字符串，我们需要将其构建成一个结构化数据，是一个树形结构，它会将我们语句里面的关键部分解析出来构建一个语法树(树形结构)，每一个节点代表不同的含义

4.发送sql给服务器，执行sql

在这里插入图片描述

返回值是一个整数，表示影响到的行数

这个过程就涉及到一系列的网络通信

5.执行完之后，关闭连接，释放资源

程序通过代码和服务器进行通讯，是需要消耗一定的硬件/软件资源的

在程序结束的时候，就要告诉服务器，释放这些资源/客户端也需要释放资源
在这里插入图片描述