Hello World

MySQL基本操作

MySQL
2025/03/02
loading

数据库：数据存储的仓库
数据库管理系统DBMS：操纵和管理数据库的大型软件
SQL：操作关系型数据库的编程语言，是一套标准
mysql默认端口号是3306
mysql –version
mysql_secure_installation
mysql -u root -p：以root身份用密码登录
mysql [-h 127.0.0.1] [-P 3306] -u root -p

关系型数据库：建立在关系模型的基础上，由多张相互连接的二维表组成的数据库
![[截屏2024-09-20 21.20.38.png]]
DDL：
数据库：
查询：show databases; 查询所有数据库
select database(); 查询当前数据库
创建：
create database [if not exists]数据库名[default charset 字符集][collate 排序规则];
删除：drop database [if exists] 数据库名;
使用：use 数据库名;
字符集不建议使用utf8，占三个字节，有些数据是四个字节的，用utf8mb4
表：
查询：
show tables; 查询当前数据库中的所有表
desc 表名; 查询表结构
show create table 表名； 查询表的建表语句
创建表
![[截屏2024-09-20 21.39.15.png]]
类型：字符串：varchar(50)
数据类型主要分为三类：数值，字符串，日期时间
数值类型中的decimal 范围依赖于M（精度）和D（标度），123.45中精度为5，标度为2
年龄：age tinyint unsigned
字符串：定长char，变长varchar
日期：date，time，datetime

表结构的修改：
添加字段：alter table 表名 add 字段名 类型 【comment 】【约束】；
修改数据类型：alter table 表名 modify 字段名 新的类型；
修改字段名和字段类型：alter table 表名 change 旧字段名 新字段名 类型 【comment】【约束】；
删除字段：alter table 表名 drop column 字段名；
修改表名：rename table 旧表名 to 新表名；
删除表：drop table 【if exists】 表名；
删除表并重新创建：truncate table 表名；

DML：增删改 数据
添加：
指定字段：insert into 表名（字段名1，字段名2） values（值1，值2）；
所有字段：insert into 表名 values（值1，值2）；
批量添加的话（值1，值2），（值1，值2），（值1，值2）

修改：
update 表名 set 字段名1=值1，字段名2=值2.。。【where 条件】；
没有where会修改整张表的字段数据

删除：
delete from 表名 【where 条件】；

DQL 查询

![[截屏2024-09-20 22.37.13.png]]

基本查询

select 字段1，字段2 from 表名；
![[截屏2024-09-20 22.39.41.png]]
select distinct 字段 from 表名； 去除重复记录

条件查询

where后跟条件列表
![[截屏2024-09-20 22.45.08.png]]
名字为两个字的：select * from emp where name like '__';
身份证最后为X的：select * from emp where idcard like '%X';

判断值是null不要用= null，要用is null

聚合函数

将一列数据作为一个整体，进行纵向计算。不对null进行运算。
常见聚合函数：count，max，min，avg，sum。
count()，可以作用于字段名、常量、*。推荐使用count(*)

聚合函数，作用于表中的某一列
select 聚合函数（字段列表）from 表名；
注：null不参与聚合函数计算

![[截屏2024-09-21 07.01.07.png]]
![[截屏2024-09-21 08.23.51.png]]
如果直接执行select * from tableName group by fieldName是会报错的，用group by表示将多行合并到了一个组里，这时候select其他字段是不对的，只能select分组的字段或者聚合函数。

分组查询

group by 通常结合聚合函数使用，对结果进行分组
将具有相同值的行分组为汇总行
select gender, count(gender) from employees group by gender;
理解 GROUP BY 的关键是：它会将具有相同分组列值的多行数据压缩成一行，然后你可以对这些分组进行聚合计算。
HAVING 子句用于过滤分组后的结果。

![[截屏2024-09-21 08.32.43.png]]
ASC升序，DESC降序
多个字段，先按第一个字段排序，如果第一个相同，然后再按第二个

分页查询

![[截屏2024-09-21 09.13.01.png]]
如果查的是第一页，起始索引0可以省略

![[截屏2024-09-21 09.29.47.png]]

编写顺序和执行顺序：
![[截屏2024-09-21 09.26.09.png]]

DCL 管理数据库用户，控制数据库的访问权限
![[截屏2024-09-21 09.43.52.png]]
主机名用%表示任意主机

![[截屏2024-09-21 09.51.18.png]]

![[截屏2024-09-21 09.52.39.png]]

函数：
![[截屏2024-09-21 09.58.50.png]]
注：substring的索引从1开始
length函数，返回varchar包含字符数

![[截屏2024-09-21 10.06.42.png]]

![[截屏2024-09-21 10.14.47.png]]

![[截屏2024-09-21 10.21.38.png]]
例如学生成绩：

1
2
3
4
5
select
	id,
	name,
	case when math >= 85 then 'good' when math >= 60 'ok' else 'bad' end
from score;

约束：作用于字段上的规则，限制数据
![[截屏2024-09-21 10.33.05.png]]
自增：auto_increment，可以配合主键

外键让两张表的数据之间建立连接
![[截屏2024-09-21 14.01.29.png]]
![[截屏2024-09-21 14.04.27.png]]
![[截屏2024-09-21 14.07.07.png]]
语法：在创建外键时，后面加上 on update cascade on delete cascade;

物理外键：使用foreign key定义外键关联另一张表。缺点：影响增删改的效率（需要检查外键关系），仅用于单节点数据库，不适用于分布式、集群场景，容易引发数据库死锁问题。
建议使用逻辑外键：在业务层逻辑中，解决外键关联。

多表查询：

多表关系：一对多，多对多，一对一

一对多：
部门和员工，一个部门有很多员工，一个员工只属于一个部门
部门表和员工表如果没有关联，如果直接删除部门，不会影响员工表，还是有员工属于被删除的部门，出现数据不完整、不一致。
实现：在多的一方建立外键，指向一的主键

多对多：
学生与课程，
实现：建立第三方中间表，至少包含两个外键，分别关联两方的主键

一对一：
用户和身份证信息，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提升操作效率
常用于单表拆分，任意一方加入外键，关联另一方的主键，并设置外键为unique

多表查询：

笛卡尔积：A集合和B集合的所有组合情况
select * from A, B where a.id = b.id;
下面的操作都是在过滤笛卡尔积

多表查询分类：
连接查询：
内连接：A和B交集
外连接：左/右外连接：查询左/右表的数据和交集的数据
自连接
子查询

![[截屏2024-09-21 14.31.45.png]]
select e.name, d.name from emp e, dept d where e.dept_id = d.id;
注：给表起了别名之后，就不能用原名了

select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;

![[截屏2024-09-21 14.37.40.png]]
比如说有一个员工表和一个部门表，其中有一个员工暂时没有分配部门，如果使用内连接的话，这个员工的信息不会显示，这时候可以使用外连接。

![[截屏2024-09-21 14.42.02.png]]

自连接必须起表别名，就当成两张表

![[截屏2024-09-21 14.46.32.png]]
union all是直接将两个结果拼起来，不加all的话可以去重。
字段列表必须一致

子查询（嵌套查询）

sql中嵌套select语句，称为嵌套查询，又称子查询
根据内层select语句返回内容可以区分为：标量子查询，列子查询，行子查询，表子查询

标量子查询，直接用就行，可以用大于号，小于号等等

![[截屏2024-09-21 14.59.58.png]]

![[截屏2024-09-21 15.05.53.png]]
![[截屏2024-09-21 15.15.11.png]]
通常将结果放在from后，

事务：

![[截屏2024-09-21 20.30.50.png]]
开启事务：start transaction; 或begin;
提交事务：commit; （事务里操作都成功后才commit）
回滚事务：rollback; （事务里有操作失败的话就rollback）

1
2
3
4
select @@autocommit;    为1是自动提交事物
set @@autocommit = 0;   为0是手动提交事物


![[截屏2024-09-21 20.46.19.png]]

![[截屏2024-09-21 20.50.54.png]]

![[截屏2024-09-21 20.56.34.png]]

![[截屏2024-09-21 20.58.29.png]]
脏读，read uncommitted
例如：事务A先读取了数据，然后事务B修改了数据但没提交，这时A再读取，读取的是B修改后的数据，这个叫read uncommitted

不可重复读
例如：事务A先读取了数据，然后事务B修改了数据，然后提交了，A再读取，发现数据不一样了，也就是在一个事务中，读取了两次数据，结果不一样，这个叫read committed

幻读：
例如：事务A读取一条当前不存在的数据，找不到，然后事务B插入了这条数据，并且提交了，这时事务A想要插入这条数据，不能插入，但是读取还是读不到，

Serializable 是串行化，事务A在进行的时候，事务B的行为会阻塞（等待事务A完成）

![[截屏2024-09-21 21.32.24.png]]

![[截屏2024-09-21 21.35.21.png]]
注意index索引在引擎层，表示不同的引擎有不同的处理索引的方式
![[截屏2024-09-21 21.38.09.png]]
每个数据库表可以有不同的引擎
show engine; 查看当前数据库支持的存储引擎

![[截屏2024-09-21 21.46.00.png]]

![[截屏2024-09-21 21.50.47.png]]

![[截屏2024-09-21 21.53.09.png]]

![[截屏2024-09-21 21.54.24.png]]
![[截屏2024-09-21 21.55.00.png]]

![[截屏2024-09-21 21.57.08.png]]
MyISAM被NoSQL数据库MongoDB取代，MEMORY被Redis取代

索引

![[截屏2024-09-22 09.07.10.png]]
![[截屏2024-09-22 09.11.30.png]]

![[截屏2024-09-22 09.17.49.png]]

![[截屏2024-09-22 09.19.58.png]]

https://www.cs.usfca.edu/~galles/visualization/BTree.html
节点中key的个数大于设置的最大度数后，中间key向上分裂

![[截屏2024-09-22 09.40.12.png]]
数据只存在叶子节点，叶子节点是一个单向链表

![[截屏2024-09-22 09.44.31.png]]

![[截屏2024-09-22 09.55.07.png]]

![[截屏2024-09-22 09.56.54.png]]

![[截屏2024-09-22 09.57.47.png]]

![[截屏2024-09-22 09.59.16.png]]

![[截屏2024-09-22 10.03.37.png]]
先在name对应的二级索引里面查找，找到之后再到聚集索引里面查找

下面的没看懂
![[截屏2024-09-22 10.30.20.png]]

![[截屏2024-09-22 10.32.46.png]]

![[截屏2024-09-22 10.56.26.png]]

![[截屏2024-09-22 10.58.56.png]]

show variables like 'slow_query_log'; 查看慢查询日志是否为开

![[截屏2024-09-22 16.32.09.png]]
这个是查看是否支持，具体开没开要用：

1
2
select @@profiling;
set profiling = 1;

![[截屏2024-09-22 16.35.22.png]]

![[截屏2024-09-22 19.58.39.png]]

![[截屏2024-09-22 20.11.14.png]]
重点关注type，
![[截屏2024-09-22 20.14.10.png]]

![[截屏2024-09-22 20.28.48.png]]

![[截屏2024-09-22 20.39.03.png]]
如果用大于等于就没有这个问题
![[截屏2024-09-22 20.42.33.png]]

![[截屏2024-09-22 20.44.56.png]]
有隐式类型转换
![[截屏2024-09-22 20.47.03.png]]

![[截屏2024-09-22 20.50.16.png]]

![[截屏2024-09-22 20.53.56.png]]
感觉找少部分数据会走索引

![[截屏2024-09-22 21.02.19.png]]

![[截屏2024-09-22 21.04.59.png]]

![[截屏2024-09-22 21.09.05.png]]
![[截屏2024-09-22 21.12.55.png]]

![[截屏2024-09-22 21.14.57.png]]

![[截屏2024-09-22 21.16.38.png]]

![[截屏2024-09-22 21.22.05.png]]
![[截屏2024-09-22 21.26.01.png]]
上图查询前5位，lvbu6，找到后回表查询，得到一条行数据。需要检查整个的email是否匹配，

![[截屏2024-09-22 21.36.56.png]]

![[截屏2024-09-22 21.38.28.png]]
先按phone排序，如果phone一样，再按name排序

![[截屏2024-09-22 21.48.26.png]]

![[截屏2024-09-22 22.15.34.png]]

![[截屏2024-09-22 22.17.28.png]]
![[截屏2024-09-22 22.18.07.png]]
![[截屏2024-09-22 22.32.22.png]]
![[截屏2024-09-22 22.30.58.png]]
![[截屏2024-09-22 22.34.19.png]]
![[截屏2024-09-22 22.50.47.png]]

![[截屏2024-09-22 22.54.40.png]]

![[截屏2024-09-22 23.16.57.png]]

![[截屏2024-09-22 23.29.42.png]]
sql优化没太看懂，主要是针对索引进行优化的

![[截屏2024-09-23 06.31.16.png]]

![[截屏2024-09-23 06.36.20.png]]

with cascaded check option 阻止了添加数据，添加后在视图中看不到的数据

![[截屏2024-09-23 07.13.23.png]]

![[截屏2024-09-23 07.20.01.png]]
local 也会对它基于的视图进行检查，cascaded会将检查传递给其他视图，而local不会传递，只是检查

![[截屏2024-09-23 07.22.18.png]]

![[截屏2024-09-23 07.26.24.png]]

![[截屏2024-09-23 07.34.08.png]]

![[截屏2024-09-23 07.36.44.png]]

![[截屏2024-09-23 07.39.00.png]]

![[截屏2024-09-23 08.12.39.png]]

![[截屏2024-09-23 08.16.10.png]]
默认是session级别
图片中有错误：select @@global.xxx; 注意global与变量名之间是“点”
mysql服务重启后会重置系统变量，想要永久改变，需要修改/etc/my.cnf

![[截屏2024-09-23 08.33.51.png]]
自定义的变量不需要声明或初始化，直接用就是null

![[截屏2024-09-23 08.40.12.png]]

![[截屏2024-09-23 08.43.44.png]]

![[截屏2024-09-23 08.47.02.png]]

![[截屏2024-09-23 09.05.28.png]]

![[截屏2024-09-23 09.08.33.png]]

![[截屏2024-09-23 09.13.11.png]]

![[截屏2024-09-23 09.16.13.png]]

![[截屏2024-09-23 09.34.50.png]]

![[截屏2024-09-23 09.44.45.png]]

![[截屏2024-09-23 09.50.17.png]]

![[截屏2024-09-23 09.56.18.png]]
行级和语句级的区别：例如，如果一个update更新了5行，则要触发5次

![[截屏2024-09-23 10.00.25.png]]

![[截屏2024-09-23 10.32.53.png]]

![[截屏2024-09-23 10.35.01.png]]

![[截屏2024-09-23 10.36.34.png]]
![[截屏2024-09-23 10.41.11.png]]

![[截屏2024-09-23 10.50.22.png]]

![[截屏2024-09-23 10.52.28.png]]
![[截屏2024-09-23 10.58.42.png]]
读锁，只能读不能写，可以阻塞其他客户端的写，其他客户端可以读
写锁，当前客户端可以读可以写，其他客户端不能读不能写

元数据可以简单理解为表结构
![[截屏2024-09-23 14.59.03.png]]

![[截屏2024-09-23 15.14.40.png]]

意向锁解决的是在innodb引擎中，行锁与表锁的冲突问题

![[截屏2024-09-23 15.18.54.png]]

![[截屏2024-09-23 15.19.29.png]]

![[截屏2024-09-23 15.26.40.png]]

![[截屏2024-09-23 15.27.57.png]]
![[截屏2024-09-23 15.28.14.png]]

![[截屏2024-09-23 15.30.29.png]]

![[截屏2024-09-23 15.41.59.png]]

![[截屏2024-09-23 15.50.44.png]]

![[截屏2024-09-23 15.56.55.png]]

![[截屏2024-09-23 15.58.39.png]]

![[截屏2024-09-23 16.00.58.png]]

![[截屏2024-09-23 16.03.12.png]]
![[截屏2024-09-23 16.04.30.png]]

![[截屏2024-09-23 16.06.45.png]]

![[截屏2024-09-23 16.09.41.png]]
![[截屏2024-09-23 16.11.41.png]]

![[截屏2024-09-23 16.13.08.png]]

原文作者：LuHai

原文链接：http://lhai0704.github.io/2025/03/02/MySQL%E5%9F%BA%E6%9C%AC%E6%93%8D%E4%BD%9C/

发表日期：March 2nd 2025, 1:27:53 pm

更新日期：March 2nd 2025, 12:44:56 am

版权声明：本文采用知识共享署名-非商业性使用 4.0 国际许可协议进行许可

CATALOG
  1. 1. DQL 查询
    1. 1.1. 基本查询
    2. 1.2. 条件查询
    3. 1.3. 聚合函数
    4. 1.4. 分组查询
    5. 1.5. 分页查询
  2. 2. 多表查询：
    1. 2.1. 多表关系：一对多，多对多，一对一
    2. 2.2. 多表查询：
      1. 2.2.1. 子查询（嵌套查询）
  3. 3. 事务：
  4. 4. 索引
Total : 22
2025
2024
Invalid date
LLM Paper Social_Network Bots Java Agent Survey 汇编语言 爬虫 C++ 服务器 DeepSeek RAGFlow 网络 Linux Electron React MySQL 配置环境