掌握MySQL数据库管理与查询技巧-优快云博客

本文链接：https://blog.youkuaiyun.com/Mixzi/article/details/123697538

Mysql数据库

库/表管理

数据库管理

功能描述	语句
查看所有的数据库名	show database;
创建一个数据库	create database 库名
删除一个数据库	drop database 库名

表文件管理

功能描述	语句
查看指定数据库下所有的表文件名	1、use 数据库名; 2、show tables;
在指定的数据库下创建表文件	1、use 数据库名; 2、create table 表名(字段数据类型,字段数据类型…)
删除一个表文件	drop table 表名
查看表文件字段信息	show create table 表名
为表文件添加字段	alter table 表名 add 新字段名数据类型
删除表文件字段	alter table 表名 drop 字段名

表文件数据行管理

功能描述	语句
插入	insert into 表名(字段名1,字段名2…)values(值1,值2…)
查询	select * from 表名
删除	1、delete from 表名 2、delete from 表名 where 判断条件
更新	update 表名 set 字段名=值,字段名=值

高级插入命令

功能描述
一次性插入多行数据	insert into 表名(字段1,字段2) values(值1,值2),(值3,值4)…
简化版插入(每个都要赋值)	insert into 表名 values(值1,值2,值3)
数据行复制	insert into B select * from A
表文件备份	create table B select * from A

临时表

定义：由查询命令在执行时，在内存中生成的表文件
作用：每一个查询命令在执行时，实际操作的都是上一个查询命令生成的临时表
生命周期：在当前查询命令执行完毕后，mysql服务器自动将上一个查询命令生成的临时表从内存中销毁。导致用户最终看到的临时表只能在查询语句中，最后一个查询命令生成的临时表
七个查询命令于临时表之间的关系：
- 七个查询命令中，只有from命令不需要操作临时表，from负责将硬盘上表文件加载到内存中生成第一个临时表，剩下的六个查询命令操作的都是上一个查询命令生成的临时表
- 七个查询命令中，只有group by命令在执行完毕之后，才有机会生成多个临时表。其他五个命令在执行完毕之后，只能在内存中生成一个临时表
- 七个查询命令中，只有having命令在执行完毕之后，不会生成新的临时表。负责将group by生成的临时表中不满足条件的临时表从内存中进行删除处理

from命令原理

执行顺序：mysql共有七个查询命令，from永远是第一个执行的命令
执行作用：from相当于一个读取流；负责将硬盘上的表文件加载到内存中生成一个临时表，供后续查询命令使用

由于查询命令对临时表的数据进行操作，因此查询命令时，是不会修改表文件的原有数据

where命令原理

执行顺序：from—>where—>select
作用：循环遍历临时表所有的数据行，每次得到一个数据行，根据数据行信息判断数据行是否需要被定位，在循环完毕后，where命令将所有定位的数据行取出来在内存生成一个临时表进行保存

运算符

关系运算：=、!=、>、>=、<、<=
逻辑运算：and、or
特殊运算：between…and…、

in()表示包含了一些数据、

not in()表示不包含一些数据、

is null、is not null

模糊查询

通配符：

%表示长度任意的字符

_表示一个任意字符

like示例：

查询所有名称以S开头的职员信息【前置条件模糊查询】

select * from emp where ename like 's%'

查询所有名称以S结尾的职员信息【后置条件模糊查询】

select * from emp where ename like '%s'

查询名称中第二个字母是A的人

select * from emp where ename like '__A'

聚合函数

分类：max(字段)、min(字段)、sum(字段)、avg(字段)、count(字段)

Group by

group by：分组查询命令

执行顺序：from—>where—>group by—>select
执行原理：group by执行时，首先根据分组字段数据种类，将临时表数据行进行分类。然后将具有相同特征的数据行读取出来保存到一个全新的临时表。
七个查询命令中，只有group by有机会在执行完毕后，一次生成多个临时表

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2nE8BiRT-1648043436916)(C:\Users\Dell\AppData\Roaming\Typora\typora-user-images\image-20220323200947306.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LlgMRc2y-1648043436918)(C:\Users\Dell\AppData\Roaming\Typora\typora-user-images\image-20220323201004565.png)]

select：切蛋糕命令

执行原理

select执行特征与提供临时表的命令有关
如果临时表由【from】或者【where】提供，此时select面对的只有一个临时表。此时select将指定字段下所有内容读取出来生成一个全新的临时表。
如果临时表由【group by】提供，此时select可能面对多张临时表。
1. select将依次操作每一个临时表
2. select在操作某个临时表时，只会读取【指定字段下第一个数据】
3. select将从多个临时表读取数据合成一列保存到一个全新的临时表
select和group by联合使用时，只会读取当前临时表下指定字段下的【第一个数据】，此时select抓取的数据应该是当前临时表所有数据行的【共有特征】，所有此时select抓取的字段，就应该是group by进行分组的字段

多字段分组规则

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fDTlm0Zb-1648043436918)(C:\Users\Dell\AppData\Roaming\Typora\typora-user-images\image-20220323202321030.png)]

Having

执行顺序：from—>where—>group by—>having—>select
执行原理：having命令在group by之后执行的。负责将group by生成的临时表不满足条件的临时表从内存中删除掉。

HAVING命令不能独立出现在查询语句。只能出现在group by后面

七个查询命令中，只有having命令在执行完毕之后，不会生成新的临时表。

Order by

作用：根据字段对临时表进行排序

根据字段名进行降序：order by 字段名 desc

根据字段名进行升序：order by 字段名 asc

Limit

执行顺序：from—>where—>group by—>having—>select—>order by—>limit
执行原理：从临时表指定位置的数据行开始，向下截取指定数量的数据行。将截取出来的数据行保存到一个全新的临时表
格式：limit 数据行起始行位置，从起始行开始向下截取行数

在mysql服务器中，表文件字段位置从1开始计算，表文件数据行位置从0开始计算

多表查询

连接查询合并方案

首先确保两张表之间存在【隶属关系】，才可以使用连接查询合并方案
两张表中的数据行【沿着水平方向进行拼接】，最终得到一个拥有【完整隶属关系】的新数据行

JOIN命令

将两个表文件数据行沿着水平方向进行拼接，拼接后数据行存在内存的一个临时表
命令格式 from 一方表 join 多方表、 from 多方表 join 一方表
join两边出现表位置，对查询结果没有任何影响
join生成临时表字段由一方表字段与多方表字段相加而来
join生成临时表字段名由表名.字段名组成，防止在同一个临时表出现同名字段问题
join生成临时表必然存在【脏数据行】，可以达到50%以上。【脏数据行】对后续统计分析带来巨大错误，因此在join工作完毕之后，必须要将【合法数据行】从当前临时表读取出来存放一个全新临时表

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QFSedBHN-1648043436919)(C:\Users\Dell\AppData\Roaming\Typora\typora-user-images\image-20220323205726198.png)]
笛卡尔积现象，就是a表中的记录*b表中的记录数，得出来就是连接合并方案的数据

连接查询过滤方案

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-y1sYv8PR-1648043436919)(C:\Users\Dell\AppData\Roaming\Typora\typora-user-images\image-20220323205933843.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Mp1MhSDL-1648043436920)(C:\Users\Dell\AppData\Roaming\Typora\typora-user-images\image-20220323210056392.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cLpafUzJ-1648043436920)(C:\Users\Dell\AppData\Roaming\Typora\typora-user-images\image-20220323210109458.png)]

内连接过滤方案

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-J6kr2IPD-1648043436921)(C:\Users\Dell\AppData\Roaming\Typora\typora-user-images\image-20220323210612055.png)]

注意！！！！！！！！！！on负责消除脏数据，而where循环筛选过滤信息

外连接过滤方案

内连接严格执法，外连接暗箱操作

外连接在join左右有主次之分！哪张表需要被帮助，那么关键字就指向哪边。左表在前，右表在后。需要被帮助的表是主表，显示全部内容。

指向的那个表会告诉数据库，别太严格执法了。悠着点数据。

外连接过滤方案标文件的角色划分：
1. 需要被帮助表
2. 不要被帮助表
外连接过滤方案执行原理：

如果【需要被帮助表】中某行数据与【不要被帮助表】所有的数据行都无法拼接为合法数据，此时已然将这个数据行作为一个独立的数据行存入到新的临时表

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4WMxE7cy-1648043436922)(C:\Users\Dell\AppData\Roaming\Typora\typora-user-images\image-20220323211021958.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YfaXX4gB-1648043436922)(C:\Users\Dell\AppData\Roaming\Typora\typora-user-images\image-20220323211230000.png)]

联合查询合并方案

不需要两张表之间存在【隶属关系】
将两张表中数据行【沿着垂直方向进行堆砌】

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-COvcnqoe-1648043436923)(C:\Users\Dell\AppData\Roaming\Typora\typora-user-images\image-20220323211650684.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-srWCfE6K-1648043436924)(C:\Users\Dell\AppData\Roaming\Typora\typora-user-images\image-20220323211612035.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0RwqfeUs-1648043436924)(C:\Users\Dell\AppData\Roaming\Typora\typora-user-images\image-20220323211619567.png)]

子查询

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-N268ApnV-1648043436925)(C:\Users\Dell\AppData\Roaming\Typora\typora-user-images\image-20220323211959975.png)]

七个命令原理