MySQL-基础篇学习总结(2025-03-02)
几个月前学习了MySQL,后来忙着准备毕业论文的事情，好几个月没有回顾，最近又开始看这块内容准备春招了，所以决定把学习过的东西做一下总结。
1. MySQL概述
这部分内容介绍数据库相关概念及MySQL数据库的介绍、下载、安装、启动及连接。具体的内容这里略过。下面几个命令比较实用：
在cmd里面启动mysql服务和关闭mysql服务的命令：
net start mysql
net stop mysql
在命令行连接mysql的命令：
mysql [-h 主机ip] [-p 端口] -uroot -p密码
2. SQL
全称structured query language。SQL语法有几个要点：
1 SQL可以单行或者多行书写，以分号结尾；
2 SQL可以实用空格/缩进来增强语句的可读性；
3 MySQL数据库的SQL语句不区分大小写，关键字建议使用大写
4 单行注释：–/# 多行注释：/* */
2.1 SQL分类
分为DDL\DML\DQL\DCL四类
2.1.1 DDL 数据定义语言
用来定义数据库对象(数据库、表、字段)
重点命令如下：
（1） 数据库操作
- 查询所有数据库：show databases;
- 查询当前使用的数据库：select databases();
- 创建数据库：create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];
- 删除数据库：drop database [if exists] 数据库名;
- 切换数据库：use 数据库名;
(2) 表操作
- 查看当前数据库所有表：show tables;
- 查看指定表的结构：desc 表名;
- 查询指定表的建表语句：show create table 表名；
- 创建表结构：create table 表名(
字段1 字段1类型 [comment 字段1注释],
字段2 字段2类型 [comment 字段2注释],
…
字段n 字段n类型 [comment 字段n注释]
）[comment 表注释];
[]表示可选内容 - 修改表：
- 添加字段：alter table table_name add 字段名 类型(长度) [comment 注释] [约束];
- 修改数据类型： alter table table_name modify 字段名 新数据类型 (长度);
- 修改字段名和字段类型：alter table table_name change 旧字段名 新字段名 类型(长度) [comment 注释] [约束];
- 删除字段：alter table 表名 drop 字段名；
- 修改表名：alter table 表名 rename to 新表名;
- 删除表：drop table [if exists] 表名；
- 删除指定表，并重新创建表：truncate table 表名;
(3) MySQL中的数据类型
主要分为三类：数值类型、字符串类型、日期时间类型
数值类型：
字符串类型：
日期时间类型：
2.1.2 DML 数据操作语言
用来对数据库表中的数据进行增删改操作。
1. 添加数据 insert
- 给特定字段添加数据：insert into 表名 (字段1，字段2，…，字段n) values (值1，值2，…,值n);
- 给全部字段添加数据：insert into 表名 values (值1，值2，…，值n);
- 批量添加数据：insert into 表名（字段名1，字段名2，…，字段名n）values (值1，值2，…，值n),(值1，值2，…，值n),(值1，值2，…，值n);
注意 ： - 插入数据时，指定的字段顺序要与值的顺序对应；
- 字符串和日期型数据应该包含在引号中；
- 插入的数据大小，应该在字段的规定范围内。
2. 修改数据 update
update 表名 set 字段名1=值1，字段名2=值2，…，[where 条件];
修改的条件如何没有，则会修改整张表的数据。
3. 删除数据 delete
delete from table [where 条件];
条件如何没有，则会删除整张表的数据。
2.1.3 DQL 数据查询语言
用来查询数据库中表的记录。这部分的操作开发人员使用较多。
语法：
select 字段列表
from 表名列表
where 条件列表
group by 分组字段列表
having 分组后条件列表
order by 排序字段列表
limit 分页参数
基本查询
select 字段1，字段2，字段3 … from 表名;
select * from 表名;
select 字段1 [as 别名1], 字段2 [as 别名2], 字段3 [as 别名3] … from 表名；
select 字段1 [别名1]， 字段2 [别名2]， 字段3 [别名3] … from 表名；
select distinct 字段列表 from 表名；
条件查询
select 字段列表 from 表名 where 条件列表；
常用比较运算符如下：
常用逻辑运算符如下：
聚合函数
聚合函数将一列数据作为一个整体，进行纵向计算。
常见的聚合函数如下：
语法：select 聚合函数(字段列表) from 表名；
null值不参与所有聚合函数运算。
分组查询
select 字段列表 from 表名 [where 条件] group by 分组字段号 [having 分组后过滤条件];
where 是分组之前进行过滤，不满足where条件，不进行分组；而having是分组之后对结果进行过滤。
where不能对聚合函数进行判断，而having可以。
注 ：分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。
执行顺序：where > 聚合函数 > having.
支持多字段分组，如：group by columnA,columnB。
排序查询
select 字段列表 from 表名 order by 字段1 排序方式1，字段2 排序方式2;
默认升序asc, 降序是desc
可以多字段排序，这时，只有第一个字段值相同时，才会根据第二个字段进行排序；
分页查询
select 字段列表 from 表名 limit 起始索引，查询记录数;
起始索引从0开始，起始索引=(查询页码-1)*每页显示记录数
分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。
DQL执行顺序
2.1.4 DCL 数据控制语言
用来创建数据库用户、控制数据库的访问权限。
管理用户
查询用户：select * from mysql.user;
创建用户：create user ‘username’@‘主机名’ identified by ‘密码’;
修改用户密码：alter user ‘username’@'主机名’ identified with mysql_native_password by ‘新密码’;
删除用户：drop user ‘用户名’@‘主机名’;
注：
在mysql中需要通过用户名@主机名的方式，来唯一标识一个用户；
主机名可以使用%通配；
这类SQL开发人员操作的比较少，主要是DBA(database administrator)使用。
权限控制
常用权限如下：
- 查询权限：show grants for ‘用户名’@‘主机名’;
- 授予权限：grant 权限列表 on 数据库名.表名 to ‘用户名’@‘主机名’;
- 撤销权限：revoke 权限列表 on 数据库名.表名 from ‘用户名’@‘主机名’;
多个权限之间，使用逗号分隔；授权时，数据库名和表名可以使用*进行通配，代表所有。
3. 函数
函数，指一段可以直接被另一段程序调用的程序或代码。在MySQL中，已经提供了这样的多种函数。主要分为：字符串函数、数值函数、日期函数、流程函数。
3.1 字符串函数
常用的如下：
3.2 数值函数
常用的几个数值函数如下：
3.3 日期函数
3.4 流程函数
如下：
4. 约束
约束是作用于表中字段上的规则，用于限制存储在表中的数据。
目的是保证数据库中数据的正确、有效性和完整性。
分类：
约束作用于表中字段上，可以在创建表/修改表的时候添加约束。
4.1 外键约束
这里重点记录一下外键约束。
添加外键以后，再删除父表数据时产生的约束行为，我们称为删除/更新行为。具体的有以下几种。
# 5. 多表查询
5.1 多表关系
表与表之间的关系，分为三种：
一对多、多对多、一对一。
5.2 多表查询概述
连接查询有以下几种类型：
内连接、外连接、左外连接、右外连接、自连接。
5.3 内连接
隐式内连接：select 字段列表 from 表1，表2 where 条件…;
显式内连接：select 字段列表 from 表1 [inner] join 表2 on 连接条件…;
5.4 外连接
分为：左外连接、右外连接两类。
左外连接：select 字段列表 from 表1 left [outer] join 表2 on 条件 …;
右外连接：select 字段列表 from 表1 right [outer] join 表2 on 条件…;
5.5 自连接
自连接就是自己连接自己，也就是把一张表连接查询多次。
select 字段列表 from 表1 别名1 join 表1 别名2 on 条件…;
自连接可以式内连接，也可以是外连接查询。
注 在自连接查询中，必须要为表起别名，要不然不清楚所指定的条件、返回的字段到底是哪一张表的字段。
5.5.1 联合查询
union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。
select 字段列表 from 表1 …
union [all]
select 字段列表 from 表2 …;
对于联合查询，多张表的列数必须保持一致，字段类型也需要保持一致。
union all会将全部的数据直接合并在一起，union会对合并之后的数据去重。
5.6 子查询
- 概述
SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。
select * from t1 where column1=(select column1 from t2);
子查询外部的语句可以是insert/update/delete/select中的任何一个。 - 分类
根据子查询结果不同，分为：
-
标量子查询(子查询结果为单个值)，常用操作符：= <> > >= < <=
-
列子查询(子查询结果为一列)，常用的操作符： in, not in, any, some, all
-
行子查询(子查询结果为一行)，常用操作符： = <> in not in
-
表子查询(子查询结果为多行多列)，常用操作符：in
根据子查询位置，分为： -
where之后
-
from 之后
-
select 之后
6. 事务
6.1 事务概述
定义：事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
注 默认MySQL的事务是自动提交的，也就是，当执行完一条DML语句时，MySQL会立即隐式的提交事务。
6.2 控制事务1
6.3 控制事务2
- 开启事务
start transaction 或者 begin; - 提交事务
commit; - 回滚事务
rollback;
6.4 事务四大特性
- 原子性atomicity:事务是不可分割的最小操作单元，要么全部成功，要么全部失败；
- 一致性consistency：事务完成时，必须使所有的数据都保持一致状态；
- 隔离性isolation：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
- 持久性durability：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。
6.5 并发事务问题
- 脏读：指的是一个事务读到另一个事务还没有提交的数据。
- 不可重复读：指的是一个事务先后读取同一条记录，但是两次读取的数据不同，称为不可重复读。
- 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了“幻影”。
6.6 事务隔离级别
为了解决并发事务所引发的问题，在数据库中引入了事务隔离级别。主要有以下几种：
几条命令
- 查看事务隔离级别：select @@transaction_isolation;
- 设置事务隔离级别：set [session | global] transaction isolation level [read uncommited | read committed | repeatable read | seriallizable
事务隔离级别越高，数据越安全，但是性能越低。