MYSQL（基本篇）——一篇文章带你走进MYSQL的奇妙世界

MYSQL算是我们程序员必不可少的一份求职工具了

无论在什么岗位，我们都可以看到应聘要求上所书写的”精通MYSQL等数据库及优化“

那么我们今天就先来了解一下MYSQL的整体布局并且带你掌握MYSQL的基本内容，希望能为你带来帮助！

MYSQL基本布局

• MYSQL概述
• SQL语法

• SQL优化
• 视图/存储过程/触发器
• InnoDB核心
• MYSQL管理

# 即使看到这么多内容，也不要太过害怕
# 我们这篇文章只为大家介绍到基础篇的内容，而我们如果不深入的话，学习到进阶篇已经是足够工作使用的了

MYSQL概述

我们先来整体了解一下MYSQL

数据库相关概念

数据库整体而言，分为三个部分：

目前主流关系型数据库

目前主流关系型数据库有四种：

• ORACLE
• MYSQL
• SQL Server
• PostgreSQL

# 注意：我们所学习的SQL语句是针对所有关系型数据库，所以不存在你学习了MYSQL却在工作中遇到ORACLE却无法使用的情况

数据库的启动和连接

数据库的正常启动一般在cmd命令行中：

# 启动：
net start mysql80
# 关闭：
net stop mysql80
#客户端连接：
在电脑中找到客户端命令行，并输入密码即可

但我们的日常使用中，使用cmd命令行是极其不美观且不实用的，我们一般使用软件代替：

• 这里我们推荐使用DataGrip软件
• 但下载只有30天试用，需要收费，可以到其他网站找到破解版，这里暂不做内容讲解

关系型数据库

关系型数据库概念：

• 建立在关系模型基础上，由多张相互连接的二维表组成的数据库

• 使用表存储数据，格式统一，便于维护
• 使用SQL语言操作，标志统一，使用方便

数据模型示意图：

• 客户端 -> DBMS -> 数据库 -> 表

• DBMS可以操作多个数据库
• 单个数据库可以存在多个表

SQL数据类型

我们在讲解SQL语法之前需要先来了解一下SQL中的数据类型：

注意：
TINYINT 可以用于一些非常小的数值中，因为只占用1byte，可以减少内存使用
SIGNED  可以用于非负数的情况下，增大数值

字符串类型

注意：
CHAR 属于定长字符串，长度不可改变，未书写长度用空格代替，但性能好
VARCHAR 属于变长字符串，长度可变，但性能较差

SQL语法

SQL语法分为多个部分，让我们来逐步了解：

SQL通用语法

SQL通用语法适用于所有SQL语句中：

• SQL语句可以单行或多行书写，以分号结尾
• SQL语句可以使用空格/缩进来增强语句的可读性
• MYSQL数据库的SQL语句不区分大小写，但关键字建议大写
• 注释：#单行注释，/**/多行注释

SQL语句分类

SQL语句主要分为四种：

DDL讲解

Data Definition Language：数据定义语言，用来定义数据库对象(数据库，表，字段) 。

1. 库操作：

# 查询所有数据库
SHOW DATABASES;
# 查询当前数据库
SELECT DATABASE();

# 创建数据库
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则]；
/*
注意：
[IF NOT EXISTS]：当数据库不存在时创建数据库（防止重复创建）
*/

# 删除数据库
DROP DATABASE [IF EXISTS] 数据库名;
注意：
[IF EXISTS]：当数据库存在时删除数据库（防止出错）
*/

# 使用/转换数据库
USE 数据库名;

1. 表操作：

# 创建表
CREATE TABLE 表名(
	字段1 字段1类型[COMMENT 字段1注释]，
    字段2 字段2类型[COMMENT 字段2注释]，
    字段3 字段3类型[COMMENT 字段3注释]，
    字段4 字段4类型[COMMENT 字段4注释]
)[COMMENT 表注释];
/*
注意：
数据之间用“，”隔开，最后一个没有“，”
*/

# 查询表
SHOW TABLES;

查询表结构：

# 查询表结构
DESC 表名;

查询指定表的创表语句：

# 查询指定表的创表语句
SHOW CREATE TABLE 表名;

添加字段：

# 添加字段
ALTER TABLE 表名 ADD 字段名 类型（长度） [COMMENT 注释][约束];

修改数据类型：

# 修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型（长度）;

修改字段名和字段类型：

# 修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型（长度） [COMMENT 注释][约束];

删除字段：

# 删除字段
ALTER TABLE 表名 DROP 字段名;

修改表名：

# 修改表名
ALTER TABLE 表名 RENAME TO 新表名;

# 删除表
DROP TABLE[IF EXISTS] 表名;
# 删除表并重建该表
TRUNCATE TABLE 表名;
/*
注意：
无论哪种方法，里面数据都会被全部删除
*/

1. 案例展示：

设计一张员工信息表，要求如下：

• 编号（纯数字）
• 员工工号（字符串，长度不超过10）
• 员工姓名（字符串，长度不超过10）
• 性别（男/女，存放一个字）
• 年龄（正常人年龄，没有负数）
• 身份证号（二代身份证均为18位，且包含X）
• 入职时间（年月日即可）

代码展示：

create table emp(
    id int comment "编号",
    empid varchar(10) comment "员工工号",
    empname varchar(10) comment "员工姓名",
    sex char(1) comment "性别",
    age tinyint unsigned comment "年龄",
    identify char(18) comment "身份证号",
    dateenter data comment "入职时间"
) comment "员工表";

DML讲解

Data Manipulation Language：数据操作语言，用来对数据库中表的数据记录进行增、删、改操作。

• 增加数据（INSERT）
• 修改数据（UPDATE）
• 删除数据（DELETE）

1. 增加数据：

# 给指定字段增加数据
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);
# 给全部字段增加数据
INSERT INTO 表名 VALUES (值1, 值2, ...);
# 批量增加数据
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...)，(值1, 值2, ...);
INSERT INTO 表名 VALUES (值1, 值2, ...)，(值1, 值2, ...);

• 插入数据时，指定的字段顺序需要和值的顺序一一对应
• 字符串和日期数据应包含在引号中
• 插入的数据大小，应该在字段的规定范围内

1. 修改数据：

# 修改数据
UPDATE 表名 SET 字段1 = 值1，字段2 = 值2,....[WHERE 条件]；
/*
条件例如： id=1，sex='女'
*/

• 修改语句的条件可以有可以没有，若没有条件，修改整张表

1. 删除数据：

# 删除数据
DELETE FROM 表名 [WHERER 条件];
/*
条件例如： id=1，sex='女'
*/

• 删除语句的条件可以有可以没有，若没有条件，删除整张表
• DELETE不能特定删除某一个字段，如有需要采用UPDATE使该值为NULL

DQL讲解

Data Query Language：数据查询语言，用来查询数据库中表的记录。

• 查询关键字: SELECT

首先我们给出DQL的整体语法：

SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段列表
HAVING
分组后条件列表
ORDER BY
排序字段列表
LIMIT
分页参数

接下来我们将一一讲解上述语法

1. 基本查询：

# 查询多个字段
SELECT 字段1，字段2，字段3，... FROM 表名;

# 查询所有字段
SELECT * FROM 表名;

# 查询时设置别名（在查询结果中的最上方类别会变成别名），AS可省略
SELECT 字段1 AS '别名'...FROM 表名;

# 去除重复记录（去除相同元素）
SELECT DISTINCT 字段列表 FROM 列表;

1. 条件查询：

# 条件查询语法：
SELECT 字段列表 FROM 表名 WHERE 列表条件;

比较运算符列表条件：

逻辑运算符列表条件：

1. 分组查询：

分组查询常常和聚合函数一同使用，所以我们先来讲解一下聚合函数

• 聚合函数是指将一列数据作为一个整体，进行纵向计算

SELECT 聚合函数（字段列表） FROM 表名;

常见聚合函数：

介绍完聚合函数，我们来讲解分组查询：

# 分组查询
SELECT 字段列表 FROM 表名[WHERE 条件] GROUP BY 分组字段名[HAVING 分组后过滤条件];

这里讲解一下WHERE和HAVING的区别：

• WHERE 和 HAVING 都是过滤条件，但过滤的时机不同
• WHERE：在分组之前进行过滤，被过滤掉的数据不参与分组
• HAVING：在分组之后对结果进行过滤，可以对聚合函数进行过滤

• 执行顺序：WHERE > 聚合函数 > HAVING
• 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段没有意义！

1. 排序查询：

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排列方式1，字段2 排列方式2 ...；

排列方式只有两种：

• ASC：升序（默认，可不写）
• DESC：降序

• 若为多字段排序，仅当第一个字段相同时，才会根据第二个字段进行排序

1. 分页查询：

我们在网页中经常看到的翻页操作中的当前页面内容就是通过分页查询做到的：

SELECT 字段列表 FROM 表名 LIMIT 起始索引，查询记录数;

• 起始索引从0开始，起始索引 = （查询页码-1）*每页显示记录数
• 分页查询是数据库的方言，不同的数据库有不同的实现方法（MYSQL中是LIMIT）
• 如果查询的是第一页数据，起始索引可以省略，直接写查询记录数即可

1. 执行顺序：

在上面我们已经介绍了DQL的所有语法，下面我们将给出DQL的执行顺序：

FROM
表名列表

WHERE
条件列表

GROUP BY
分组字段列表
HAVING
分组后条件列表

SELECT
字段列表

ORDER BY
排序字段列表

LIMIT
分页参数

DCL讲解

Data Control Language：数据控制语言，用来管理数据库用户、控制数据库的访问权限。

1. 管理用户：

管理用户主要是针对用户的操作，我们分为四小部分讲解：

• 查询用户：

# 我们在MYSQL中存在mysql数据库，在库中存在user表，我们的用户都存储在里面
USE mysql；
SELECT * FROM user；

• 创建用户：

# 创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
/*
用户名可以随机定义
主机名分为 localhost本机 和 %任意机型
*/

• 修改用户密码：

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

• 删除用户：

DROP USER '用户名'@'主机名';

• 主机名可以使用%通配
• 这类SQL开发人员操作较少，主要由DBA（数据库管理员）使用

1. 权限控制：

我们所创建的用户在开始时只有连接数据库的权限，所以其他权限都需要我们去控制

但是在了解如何控制权限之前，我们需要先来了解数据库都具备什么权限：

好的，接下来让我们来讲解权限控制语句：

• 查询权限：

# 查询该用户的权限
SHOW GRANTS FOR '用户名'@'主机名';

• 授予权限：

# 授予该用户权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

• 撤销权限：

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

• 多个权限之间用逗号隔开
• 授权时，数据库名和表名可以采用*进行通配，代表所有

• 是指一段可以直接被另一段程序调用的程序或代码。

函数大致被分为四种：

• 字符串函数

函数的统一测试标准：

• SELECT 函数（函数参数）

字符串函数

• 约束是作用于表中字段上的规则，用来限制存储在表中的数据

• 保证数据库中的数据的正确，有效性和完整性

• 约束是作用于表中字段上的，可以在创建表/修改表的时候加上约束

• 用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

添加外键：

-- 创建表时添加外键
CREATE TABLE 表名（
	字段名 数据类型，
	...
	[CONSTRAINT] [外键名称] FOREIGN (外键字段名) REFERENCES 主表（主表列名）
）;

-- 外部修改时添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表（主表列名）;

删除外键：

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

外键同样也具有约束条件：

外键约束条件修改代码：

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名（主表字段名）
	ON UPDATE 修改时行为 ON DELETE 删除时行为

多表关系概述：

• 在项目开发中，在进行数据库表的结构设计时，会根据业务需求以及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所有各个表之间也存在着各种联系

表与表的关系大致分为三种：

• 一对多
  • 关系：一个部门对应多个员工，一个员工对应一个部门
  • 实现：在多的一方建立外键，指向一的一方的主键
• 多对多
  • 关系：一个学生选择多个课程，一个课程有多个学生选择
  • 实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
• 一对一
  • 关系：一对一关系常用于单表内容过多，进行拆分使用
  • 实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE）

多表查询概述：

• 指从多张表中查询数据
• 当我们同时查询两张表时，会出现笛卡尔积现象，我们的目的就是消除多余的笛卡尔积

多表查询分为以下几种：

内连接查询的是两张表交集的部分

-- 隐式内连接
SELECT 字段列表 FROM 表1，表2 WHERE 连接条件...;

-- 显式内连接
SELECT 字段列表 FROM [INNER] JOIN 表2 ON 连接条件...;

外连接查询一方的所有内容和所需要查询的另一方内容

-- 左连接：包含左表A的所有内容和左表和右表的交集部分的数据
SELECT 字段列表 FROM 表A LEFT [OUTER] JOIN 表B ON 条件...;

-- 右连接：包含右表B的所有内容和左表和右表的交集部分的数据
SELECT 字段列表 FROM 表A RIGHT [OUTER] JOIN 表B ON 条件...;

自连接负责查询自我内容

-- 只有一张表，但注意需要有不同的别名，负责进行比较和筛选
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;

联合查询负责把两个查询内容结合起来

SELECT 字段列表 FROM 表A...
UNION [ALL]
SELECT 字段列表 FROM 表B...;

• 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致
• UNION ALL会将所有内容都合并在一起，UNION会对合并的内容进行数据去重

• SQL语句中嵌套SELECT语句，称为嵌套语句，也被称为子查询

SELECT * FROM T1 WHERE column1 = (SELECT column1 FROM T2)

• T1,T2分别表示两个表

• column表示表中的元素，进行比较

• 子查询外部的语句可以是INSERT/DELETE/SELECT语句中的任意一个

子查询可以大概分为四种：

• 标准子查询

标准子查询

• 子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。

常用操作符：

• = <> > >= < <=

• 子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。

常用操作符：

• IN 、NOT IN 、 ANY 、SOME 、 ALL

操作符解释：

• 子查询返回的结果是一行（可以是多列），这种子查询被称为行子查询

常用操作符：

• = 、<> 、IN 、NOT IN

• 子查询返回的结果是多行多列，这种子查询称为表子查询

常用操作符：

• 事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作都作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败

• 默认MYSQL的事务是自动提交的，也就是说每执行一条DML语句，MYSQL就会立即隐式的提交事务

事务操作分为几个部分：

• 查看事务提交方法

-- 查看事务提交方法
SELECT @@autocommit
-- 自动：1
-- 手动：0
-- 系统设置为1自动提交

• 设置事务提交方法

-- 设置事务提交方法
SET @@autocommit = 0/1;
-- 我们可以设置为0（因为默认为1）
-- 在设置为0后，后续操作不会影响数据，必须手动提交

-- 开启事务
START TRANSACTION 或 BEGIN
--效果和@@autocommit = 0一样

-- 当数据无法自动提交或开启事务后，我们需要手动提交，只有手动提交后，信息才会上传
COMMIT

-- 当事务中途出现错误（无法完全执行事务），我们常常采用回滚事务，让全部回到事务进行前
ROLLBACK

事务四大特性

事务具有四大特性，被称为ACID：

• 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败
• 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态
• 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
• 持久性（Durability）：事务一旦提交或滚回，它对数据库的改变就是永久的

并发事务问题

并发事务常常出现三种问题：

事务隔离级别

事务隔离级别分为四种：

• √：会发生

• ×：不会发生

• 性能：从上往下越来越差

• 约束：从上往下越来越好

下面给出两条代码来查看和设置事务隔离级别：

-- 查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION

-- 设置事务隔离级别
SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL 
{Read uncommitted|Read committed|Repeatable Read|Serializable}
-- SESSION ：仅针对当前事务
-- GLOBAL：针对所有事务

好的，关于MYSQL的基本内容就介绍到这里，希望能为你带来帮助！

我在之前学习过MYSQL，所以基础篇基本没有练习题和实例，

后面的进阶篇我会详细书写每个例子包括练习，如果上面的内容有你不太明白的地方，这里推荐跟着老师的课程学习

这篇文章是属于学习笔记，主要是学习B站黑马程序员邓老师的MYSQL课程

下面为大家附上连接：黑马程序员 MySQL数据库入门到精通，从mysql安装到mysql高级、mysql优化全囊括_哔哩哔哩_bilibili