当前位置：首页 > article >正文

MySQL基本知识梳理

article 2025/1/17 22:52:35

看前tip：

1.本文全部用小写字母书写，因为我看见大写单词不认识

2.[ ]表示可选项

3.{ }表示需要写，| 在大括号中选一个

总结：查找的执行顺序，确实不好记所以我写在前面了。

①from -> ②join on -> ③where -> ④group by -> ⑤having -> ⑥select -> ⑦distinct -> ⑧order by -> ⑨limit

我的理解：①②数据从哪些表来，③第一次筛选数据，④分组，⑤分组之后筛选，⑥显示哪些列，⑦⑧⑨对显示的列进行排版。

一.库的操作

1.查看数据库

show databases;

2.创建数据库

create {database | schema} [if not exists] 数据库名 [create_option]...

下面的是create_option的可选项
{ character [=] charset_name | collate [=] collation_name }
-- charset_name的可选项有{utf8mb4 | latin1}但是latin1不能储存中文
-- collation_name的可选项有{utf8mb4_0900_ai_ci | utf8mb4_general_ci} 我们一般选择第一个因为表示口声不敏感和大小写不敏感

3.修改数据库

alter database 数据库名 alter_option
-- alter_option可以选的内容就是上面create_option可选的内容

4.删除数据库

drop { database | schema } [if exists] 数据库名

5.其他指令

select database(); -- 查看当前选中是那个数据库
use 数据库名; -- 切换到这个数据库
show charset;  -- 查看数据库⽀持的字符集编码
show collation;  -- 查看数据库支持的排序规则
show variables like '%character%'; -- 查看系统默认支持的字符集
show variables like '%collation%'; -- 查看系统默认排序规则
show create database 数据库名; -- 查看数据库创建语句

二.数据类型

1.数据值类型

2.字符串类型

3.日期类型

我一般只用过varchar(M)和int这两个数据类型

三.表的操作

1.查看所有表

 show tables;

2.创建表

①直接创建一个表

create table [if not exists] 表名 (
    列名 数据类型 [约束] [comment '注解内容']
    [, 列名 数据类型 [约束] [comment '注解内容']] ...
)[character set 字符集] [collate 排序规则];
-- 这里的字符集和排序规则就是上面创建库里面的规则

②通过其他的表创建一个结构相同的表

create table 新表的表名 like 旧表的表名;

3.查看表结构

desc 表名

4.修改表结构

alter table 要修改的表名 [alter_option [, alter_option] ...];

alter_option: {
      table_options
    | add [column] col_name column_definition [first | after col_name]    -- 向表中添加一列
    | modify [column] col_name column_definition [first | after col_name] -- 修改某列的长度
    | drop [column] col_name  -- 删除某列
    | rename column old_col_name to new_col_name  -- 重命名某列
    | rename [to | as] new_tbl_name -- 修改表名
}

5.删除表

drop table [if exists] 表名 [,表名] ... ;
-- 也可以用库名.表名的方法指定

四.表的增删查改

1.新增

insert into 表名[(列名 [,列名] ...)]
values (插入的值)[,(插入的值)] ... ; 
-- 注意这里插入的值一定要与插入的列名相对应

2.查询

下面时全部写在一起的格式：

select
    [distinct]
    列名 [, 列名] ...
    from 表名
    [where where_condition]
    [group by {col_name | expr}, ...]
    [having where_condition]
    [order by {col_name | expr } [asc | desc], ... ]
    [limit {[start_count] 显示的行数 | 显示的行数 offset start_count}]

①select

1.全列查询

select * from exam;

2.指定列查询

select 列名[, 列名]... from 表名;

3.查询的字段为表达式(就是列表可以为一个表达式)

-- 示例1：把所有学⽣的语⽂成绩加10分 
select id, name, chinese + 10 from exam;
-- 示例2：计算所有学⽣语⽂、数学和英语成绩的总分
select id, name, chinese + math + english from exam;

-- 建议用了表达式之后建议换取别名，因为这样太难看了。

4.为查询结果指定别名

select 需要取别名的列(这个列也可以为上面所说的表达式) [as] 别名 [, ...] from 表名;
-- 这里的as可写可不写

5.结果去重的查询

select distinct 列名... from 表名;
-- 只有所有的列表相同才会算重复，才会被去除。

②where

1.比较运算符

2.逻辑运算符

3.where的使用格式
select 列名[,列名] from 表名 where 用上面的关键字组成的判断语句
4.模糊查询

①查询所有姓孙的同学
select * from exam where name like '孙%';
②查询姓孙且姓名共有两个字同学
select * from exam where name like '孙_';
注意：%代表0个或者多个字符，_代表一个字符

③order by

select ... from 表名 [where...] order by 需要排序的列名 [asc | desc] [,需要排序的列名 [asc | desc]], ... ;

-- asc 为升序（从⼩到⼤）
-- desc 为降序（从⼤到⼩）
-- 默认为 asc

-- 然后这里的列名是可以使用别名的，这是因为执行顺序决定的

注意事项：NULL 进⾏排序时，视为⽐任何值都⼩，升序出现在最上⾯，降序出现在最下⾯。

④分页查询

-- 起始下标为 0
-- 从 0 开始，筛选 num 条结果
select ... from table_name [where ...] [order by ...] limit num;
-- 从 start 开始，筛选 num 条结果
select ... from table_name [where ...] [order by ...] limit start, num;
-- 从 start 开始，筛选 num 条结果，⽐第⼆种⽤法更明确，建议使⽤
select ... from table_name [where ...] [order by ...] limit num offset start;

3.插入查询结果

-- 代码模板
insert into table_name [(column [, column 1 ...])] select ...

演示案例：原表中的记录去重后写入到新表
insert into t_recored_new select distinct * from t_recored_old;

4. 聚合函数

①常用的函数

演示案例：

数据表：

演示案例：

1.记录有多少行数据 -- count

2.有多少人参加参加了英语考试 -- count

count不会统计null所以只有6个

3.统计所有学生英语成绩总分 -- sum

②分组查询

这个查询时用于把几行数据根据一个指标合在一起的，然后用聚合函数把这些合在一起的行算出我们需要的内容。

1.基本使用

select column1,sum(column), ... from 表名 group by column1;
-- 对column1进行分组

2.having筛选，用不了where，因为where会比分组先进行，所以只能用having进行筛选

案例演示：把上面的两个知识点都综合了

5.修改

update 表名 set 列名 = 值 [,列名 = 值] [where ...] [order by ...] [limit ...]

注意事项：

①sql中没有 += 这个运算符号

②不加where会导致全表更新

6.删除数据

delete from 表名 [where where_condition] [order by ...] [limit row_count]

注意事项：

①根据后面的表名添加内容的限制条件，筛选删除的内容。

②如果不添加的这些限制条件，就会把整张表删除。

五.约束

约束书写的地方：

create table [if not exists] 表名 (
    列名 数据类型 [约束] [comment '注解内容']
    [, 列名 数据类型 [约束] [comment '注解内容']] ...
)[character set 字符集] [collate 排序规则];

1.not null：约束

添加了这个约束之后，这个位置的值不能填null

查看表结构：这加了not null 之后，就是yes会变成no。

2.unique：唯一约束

添加了这个约束之后，这一列就不能在有重复值了。

查看表结构：这力添加了unique之后，key的主键从空变成nui。

3.default 默认值：默认值约束

添加了这个约束之后，如果这里在插入的时候不填值，就会用这个默认值。

查看表结构：default下面的null会变成默认值

4.primary key：主键约束

添加了这个约束之后，这一列在插入的时候，不能为空且不能有重复值。

①查看表结构：key从空的变为pri

②自增主键

只需要在添加主键约束的时候后面加上 auto_increment 这个关键字就可以了。

1.每次自增就是在最大值的上面加1

2.插入自增主键后表的结构

③主键或唯⼀键冲突时的更新操作，否则插⼊。
-- 方法1
insert into ... values ...
on duplicate key update column = value [, column = value] ...
-- 更新这条主键重复的值，上面的这个模板也不好理解，建议结合ai的讲解一起看
-- 方法2
replace [into] table_name
    [(column [, column] ...)]
values
    (value_list) [, (value_list)] ... ;
④表中最多只能有一个主键

⑤复合主键

主键冲突：由选中作为复合主键的内容，全部相同才会引起主键冲突。
-- 声明复合主键的样例
create table student (
    id bigint,
    name varchar(20),
    primary key (id, name) # 指定复合主键
);

5.foreign key：外键约束

外键⽤于定义主表和从表之间的关系
外键约束主定义在从表的列上，主表关联的列必须是主键或唯⼀约束
当定义外键后，要求从表中的外键列数据必须在主表的主键或唯⼀列存在或为null。

1.创建外键约束

上图是我们需要建立的关系，假设主表已经创建，我们直接创建从表
-- 语法格式
foreign key (id) references 主表的表名(id);

create table student(
    id bigint PRIMARY KEY auto_increment,
    name varchar(20) not null,
    age int DEFAULT 18,
    class_id bigint,
    foreign key (class_id) references class(id) # 创建外键约束
)
2.查看表结构：key的空变为MUL

3.删除主表某条记录时，从表中不能有对该记录的引⽤，删除主表时要先删除从表。

六.多表查询

两张表取笛卡尔积的结果：

这里我们需要查几张表就需要把几张表取笛卡尔积。

1.内连接

select 字段 from 表1 别名1, 表2 别名2 where 连接条件 and 其他条件;

2.外连接

这是右外连接和内连接的区别，然后左外连接与右外连接类似

①左外连接

返回左表的所有记录和右表中匹配的记录。如果右表中没有匹配的记录，则结果集中对应字段会显示为NULL。

-- 左外连接，表1完全显⽰
select 字段名 from 表名1 left join 表名2 on 连接条件;

②右外连接

返回右表的所有记录和左表中匹配的记录。如果左表中没有匹配的记录，则结果集中对应字段会显示为NULL。

-- 右外连接，表2完全显⽰
select 字段 from 表名1 right join 表名2 on 连接条件;

3.自连接

之前我们做比较都是在一行中的某一列和莫一列之间比较，如果我们在某一列中需要某几行的内容做比较就需要自连接。

select 字段列表 from 表A 别名A join 表A 别名B on 条件...;

注意：连接的时候一定要取别名。

4.子查询(嵌套查询)

①⼦查询是把⼀个select语句的结果当做另⼀个select语句的条件，这里一定要注意对应关系：
select * from table1 where col_name1 {= | in} (
    select col_name1 from table2 where ...
)
-- 用=表示单行子查询
-- 用in表示多行子查询
-- 如果这里的col_name1是多列，这就是多列子查询
②上面是把子查询写在where语句里面，其实子查询的语句也可以写在from语句里面，这就相当于使用子查询创建了一张临时表，让后在临时表里面查询。

5.合并查询结果

使用时一定要列名匹配，要不然返回的结果没有意义。

在单表查询中建议使用or连接，但是在多表连接一般是用到下面的关键字。

-- 使用案例
select * from student where id < 3 union select * from student1;
-- 其实语法规则很简单，只需要把两条sql语句用符号连接起来就可以了。

①union

该操作符⽤于取得两个结果集的并集。当使⽤该操作符时，会⾃动去掉结果集中的重复⾏。

②union all

该操作符⽤于取得两个结果集的并集。当使⽤该操作符时，不会去掉结果集中的重复⾏。

七.视图

视图是⼀个虚拟的表，它是基于⼀个或多个基本表或其他视图的查询结果集。

1.视图的优点

1. 简单性：视图可以将复杂的查询封装成⼀个简单的查询。例如，针对⼀个复杂的多表连接查询，可以创建⼀个视图，⽤⼾只需查询视图⽽⽆需了解底层的复杂逻辑。

2. 安全性：通过视图，可以隐藏表中的敏感数据。例如，⼀个系统的⽤⼾表中，可以创建⼀个不包含密码列视图，普通⽤⼾只能访问这个视图，⽽不能访问原始表。
3. 逻辑数据独⽴性：视图提供了⼀种逻辑数据独⽴性，即使底层表结构发⽣变化，只需修改视图定义，⽽⽆需修改依赖视图的应⽤程序。使⽤到应⽤程序与数据库的解耦

4. 重命名列：视图允许⽤⼾重命名列名，以增强数据可读性。

2.创建视图

create view 视图名 [(column_list)] as (sql语句);
-- 上面包含了两种创建视图的方法
-- 1. 创建视图，在select中使⽤别名，不需要用到上面[]里面的内容。
-- 2.创建视图，指定结果集中的列名，需要用到上面[]里面的内容。
column_list：是指定的别名。

注意 column_list 中可能会出现重复的列，所以需要注意取别名，因为sql语句中可能有多个表，表中的列重复了，所以需要取别名。然后在里面书写列的时候语法为 ‘ 表名.列名 ’ 。

可以用 show tables 查看创建了哪些视图。

3.使用视图

-- 方法1：
show create view 视图名;
-- 方法2：
select * from 视图名;

4.修改数据

①通过真实表修改数据，会影响视图

②通过视图修改数据会影响基表

在以下情况下修改不了视图

1.创建视图时使⽤聚合函数的视图

2.创建视图时使⽤ distinct
3.创建视图时使⽤ group by 以及 having ⼦句
4.创建视图时使⽤ union 或 union all

5.查询列表中使⽤⼦查询

6.在 from ⼦句中引⽤不可更新视图

7.使用了order by也不能更新视图

5.删除视图

drop view 视图的名字

八.索引

1.索引的数据结构

①哈希索引

一般不会使用：不支持范围查询，哈希冲突可能导致性能下降。

②B+树索引

结构如下图，数据全部储存在叶子节点。

2.索引的分类

①主键索引

• 当在⼀个表上定义⼀个主键 PRIMARY KEY 时，InnoDB使⽤它作为聚集索引。

• 推荐为每个表定义⼀个主键。如果没有逻辑上唯⼀且⾮空的列或列集可以使⽤主键，则添加⼀个⾃增列。

②普通索引

• 最基本的索引类型，没有唯⼀性的限制。

• 可能为多列创建组合索引，称为复合索引或组全索引。

③唯一索引

• 当在⼀个表上定义⼀个唯⼀键 UNQUE 时，⾃动创建唯⼀索引。

• 与普通索引类似，但区别在于唯⼀索引的列不允许有重复值。

④全文索引

• 基于⽂本列(CHAR、VARCHAR或TEXT列)上创建，以加快对这些列中包含的数据查询和DML操作。

• ⽤于全⽂搜索，仅MyISAM和InnoDB引擎⽀持。

两个查询时的重要概念：

回表查询：当查询所需的字段不完全包含在索引树中时，数据库需要通过索引定位到数据表的行，再从数据表中读取额外的字段。

索引覆盖：查询所需要的所有字段都能从索引树中直接获取，查询不需要访问表中的数据页。

3.使用索引

①自动创建

1.当我们为⼀张表加主键约束(Primary key)，外键约束(Foreign Key)，唯⼀约束(Unique)时，MySQL会为对应的的列⾃动创建⼀个索引。

2.如果表不指定任何约束时，MySQL会⾃动为每⼀列⽣成⼀个索引并⽤ ROW_ID 进⾏标识

②手动创建

1.手动创建主键索引

-- ⽅式⼀，创建表时创建主键
create table t_test_pk (
    id bigint primary key auto_increment,
    name varchar(20)
);

-- ⽅式⼆，创建表时单独指定主键列
create table t_test_pk1 (
    id bigint auto_increment,
    name varchar(20),
    primary key (id)
);

-- ⽅式三，修改表中的列为主键索引
create table t_test_pk2 (
    id bigint,
    name varchar(20)
);
alter table t_test_pk2 add primary key (id) ;
alter table t_test_pk2 modify id bigint auto_increment;
-- 这里的alter可以看看上面表修改的语法

2.手动创建唯一索引

和创建主键索引类似，只是把primary key 换成了 unique 了，所以这里不演示。

3.手动创建普通索引：查看表结构的时候key里面显示的是 MUL

-- ⽅式⼀，创建表时指定索引列
create table t_test_index (
id bigint primary key auto_increment,
    name varchar(20),
    sno varchar(10),
    index(sno)
);

-- ⽅式⼆，修改表中的列为普通索引
create table t_test_index1 (
    id bigint primary key auto_increment,
    name varchar(20),
    sno varchar(10)
);
alter table t_test_index1 add index (sno) ;

-- ⽅式三，单独创建索引并指定索引名，建议使用
create table t_test_index2 (
    id bigint primary key auto_increment,
    name varchar(20),
    sno varchar(10)
);
create index 索引名 on t_test_index2(sno);

③创建复合索引

创建复合索引和上面的创建普通索引一样，只是把上面的需要创建索引的字段用","分隔开就可以了。索引名就是第一个字段的名字。

④查看索引

-- 方式一
show keys from 表名;
-- 方式二
show index from 表名;
-- 方式三：这个显示的少一点
desc 表名;

⑤删除索引

1.删除主键索引

alter table 表名 drop primary key;

如果是自增的主键需要先删除自增的属性，才可以删除主键

-- 下面是一个案例是删除自增，不是模板，模板就是前面的修改表里面的
alter table t_test_index6 modify id bigint;

2.删除其他索引

alter table 表名 drop index 索引名;
-- 唯一健也是这样删除的，唯一健的索引名就是字段的名字
-- 好像创建索引的时候没有索引名，索引名就是字段的名字

4.查看执行计划

explain sql语句

这个可以用于优化sql语句，我们暂时不用学习。

九.事务

1.事务的四大特性：ACID

①Atomicity (原⼦性)：⼀个事务中的所有操作，要么全部成功，要么全部失败，不会出现只执⾏了⼀半的情况，如果事务在执⾏过程中发⽣错误，会回滚（ Rollback ）到事务开始前的状态，就像这个事务从来没有执⾏过⼀样。
②Consistency (⼀致性)：在事务开始之前和事务结束以后，数据库的完整性不会被破坏。这表⽰写⼊的数据必须完全符合所有的预设规则，包括数据的精度、关联性以及关于事务执⾏过程中服务器崩溃后如何恢复。
③Isolation (隔离性)：数据库允许多个并发事务同时对数据进⾏读写和修改，隔离性可以防⽌多个事务并发执⾏时由于交叉执⾏⽽导致数据的不⼀致。事务可以指定不同的隔离级别，以权衡在不同的应⽤场景下数据库性能和安全。
④Durability (持久性)：事务处理结束后，对数据的修改将永久的写⼊存储介质，即便系统故障也不会丢失。

2.使用事务

# 开始⼀个新的事务
start transaction;
# 或
begin；

# 提交当前事务，并对更改持久化保存
commit；

# 回滚当前事务，取消其更改
rollback；

3.设置保存点

在事务执⾏的过程中设置保存点，回滚时指定保存点可以把数据恢复到保存点的状态。

-- 设置保存点
savepoint 保存点名
-- 回滚到哪个保存点
rollback to 想要回滚到的保存点

-- 回滚时不指定保存点，直接回滚到事务开始时的原始状态，事务关闭

4.手动/自动提交事务

默认情况下，MySQL是⾃动提交事务的，也就是说我们执⾏的每个修改操作，⽐如插⼊、更新和删除，都会⾃动开启⼀个事务并在语句执⾏完成之后⾃动提交，发⽣异常时⾃动回滚。

1.检测事务是否为自动提交
show variables like 'autocommit';
2.设置事务为手动还是自动提交
-- 设置事务自动提交
set autocommit = 1; -- 方式一
set autocommit = on; -- 方式二

-- 设置事务手动提交
set autocommit = 0; -- 方式一
set autocommit =o ff; -- 方式二
注意事项：

• 只要使用 start transaction 或 begin 开启事务，必须要通过 commit 提交才会持久化，与是否设置 set autocommit 无关。
• 手动提交模式下，不用显示开启事务，执行修改操作后，提交或回滚事务时直接使用 commit或rollback
• 已提交的事务不能回滚

5.事务与隔离级别

事务间不同程度的隔离，称为事务的隔离级别；不同的隔离级别在性能和安全方面做了取舍，有
的隔离级别注重并发性，有的注重安全性，有的则是并发和安全适中；在MySQL的InnoDB引擎中事务的隔离级别有四种。

1.查看和设置事务的隔离级别

①事务的隔离级别分为全局作用域和会话作用域，查看不同作用域事务的隔离级别，可以使用以下的
-- 全局作用域
SELECT @@GLOBAL.transaction_isolation;
-- 会话作用域
SELECT @@SESSION.transaction_isolation;
-- 会话作用域的意思是，不同的客户端打开的一个终端我们称为一个会话
②设置事务的隔离级别和访问模式
语法：
set [global | session] transaction isolation level level_1;

-- 通过 global|session 分别指定不同作用域的事务隔离级别
-- 设置全局事务隔离级别，后续所有事务生效，不影响当前事务
-- 设置会话事务隔离级别，当前会话后续的所有事务生效，不影响当前事务，可以在任何时候执行
-- 如果不指定任何作用域，设置只针对下一个事务，随后的事务恢复之前的隔离级别

-- 隔离级别
level_1: {
REPEATABLE READ -- 可重复读
| READ COMMITTED -- 读已提交
| READ UNCOMMITTED -- 读未提交
| SERIALIZABLE -- 串行化
}
2.不同隔离级别存在的问题

①未读提交

脏读：脏读是指一个事务读取了另一个事务尚未提交的修改数据。如果后续事务回滚，这个数据就会变得无效，从而导致读取到的数据是“脏”的。

②读已提交

不可重复读是指同一个事务中，先后两次读取同一行数据时，读到的值不一致。这通常是因为另一个事务在中途修改并提交了该数据。

③可重复读

幻读是指一个事务中，两次执行相同的查询时，结果集中出现了新的数据行或消失了某些数据行。这是因为另一事务在中途插入或删除了记录。

④串行化

此时所有事务串行执行，可以解决所有并发中的安全问题。