MySQL概述

数据库相关概念

主流的关系型数据库管理系统

1. ORACLE
2. MySQL
3. SQL Server
4. PostgreSQL

MySQL数据库

启动与停止

1. 启动 net start mysql
2. 停止 net stop mysql

客户端连接

1. MySQL提供的客户端命令行工具
2. 使用系统自带的命令行工具执行指令mysql [-h 127.0.0.1] [-p 3306] -u root -p
   1. -h指定连接的ip
   2. -p指定连接的端口

数据模型

关系型数据库

1. 概念：建立在关系模型基础上，由多张相互连接的二维表组成的数据库
2. 特点：
   1. 使用表存储数据，格式统一，便于维护
   2. 使用SQL语言操作，标准统一，使用方便

SQL

SQL通用语法

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

• 单行注释：--注释内容或#注释内容(MySQL特有)
• 多行注释：/* 注释内容 */

SQL分类

DDL

DDL-数据库操作

1. 查询
   1. 查询所有数据库SHOW DATABASES;
   2. 查询当前数据库SELECT DATABASE();
2. 创建CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
3. 删除DROP DATABASE [IF EXISTS] 数据库名;
4. 使用USE 数据库名;

DDL-表操作-查询

1. 查询当前数据库所有表SHOW TABLES;
2. 查询表结构DESC 表名;
3. 查询指定表的建表语句SHOW CREATE TABLE 表名;

DDL-表操作-创建

CREATE TABLE 表名(
	字段1 字段1类型 [COMMENT 字段1注释],
	字段2 字段2类型 [COMMENT 字段1注释],
	字段3 字段3类型 [COMMENT 字段1注释],
	......
	字段n 字段n类型 [COMMENT 字段n注释]
) [COMMENT 表注释];

DDL-表操作-数据类型

MySQL的数据类型主要分为三类：数值类型、字符串类型、日期时间类型

数值类型

精度是指整个数的长度(除去小数点后)，标度是指小数位数。注意double也可以利用精度和标度

字符串类型

char和varchar在使用时都需要带参数。例如char(n)，表示最多存放多少个字符，但char即使只存放一个字符，也会占用n个字符的空间，其它空间用空格填充；而varchar占用的空间则会随着字符数的增加而增加，几个字符就占用几个字符的空间，但不能超过n个字符。char性能好，varchar相对性能较差

日期时间类型

DDL-表操作-修改

1. 添加字段ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
2. 修改数据类型ALTER 表名 MODIFY 字段名 新数据类型(长度);
3. 修改字段名和字段类型ALTER TABLE 表明 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
4. 删除字段ALTER TABLE 表明 DROP 字段名;
5. 修改表名ALTER TABLE 表名 RENAME TO 新表名;

DDL-表操作-删除

1. 删除表DROP TABLE [IF EXISTS] 表名;
2. 删除指定表，并重新创建该表TRUNCATE TABLE 表名;，该语法会把表结构删除，然后再次创建这张表结构，相当于把数据清空了
3. 请注意，在删除表时，表中的数据会全部被删除

DML

英文全称Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进行增删改操作

• 添加数据(INSERT)
• 修改数据(UPDATE)
• 删除数据(DELETE)

DML-添加数据

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

INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES(值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);

INSERT INTO 表名 VALUES(值1, 值2, ...),(值1, 值2, ...),(值1, 值2, ...);

注意事项

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

DML-修改数据

UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2, ... [WHERE 条件];
注意：修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张的所有数据

DML-删除数据

DELETE FROM 表名 [WHERE 条件];

1. DELETE语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据
2. DELETE语句不能删除某一个字段的值(可以使用UPDATE)

DQL

英文全称Data Query Language(数据查询语言)，数据查询语言，用来查询数据库中表的记录

DQL-语法

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

DQL-基本查询

1. 查询多个字段SELECT 字段1, 字段2, 字段3... FROM 表名;
2. 查询全部字段SELECT * FROM 表名;
3. 设置别名SELECT 字段1 [AS 别名1], 字段2 [AS 别名2] ... FROM 表名;
4. 去除重复记录SELECT DISTINCT 字段列表 FROM 表名;

DQL-条件查询

1. 语法SELECT 字段列表 FROM 表名 WHERE 条件列表;
2. 条件
   | 比较运算符 | 功能 |
   | --- | --- |
   | > | 大于 |
   | >= | 大于等于 |
   | < | 小于 |
   | <= | 小于等于 |
   | = | 等于 |
   | <> 或 != | 不等于 |
   | BETWEEN ... AND ... | 在某个范围之内(含最小、最大值) |
   | IN(...) | 在in之后的列表中的值，多选一 |
   | LIKE 占位符 | 模糊匹配(_匹配单个字符，%匹配任意个字符) |
   | IS NULL | 是NULL |

DQL-聚合函数

1. 将一列数据作为一个整体，进行纵向计算

2. 常见聚合函数
   | 函数 | 功能 |
   | --- | --- |
   | count | 统计数量 |
   | max | 最大值 |
   | min | 最小值 |
   | avg | 平均值 |
   | sum | 求和 |

3. 语法SELECT 聚合函数(字段列表) FROM 表名;

4. null值不参与所有聚合函数运算

DQL-分组查询

1. 语法SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];
2. where与having区别
   1. 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤
   2. 判断条件不同：where不能对聚合函数进行判断，而having可以
3. 注意事项
   1. 执行顺寻：where > 聚合函数 > having
   2. 分组之后，查询的字段一般为聚合函数和分组字段，查询其它字段无任何意义

DQL-排序查询

1. 语法SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;
2. 排序方式
   1. ASC: 升序（默认值）
   2. DESC: 降序
3. 注意，若是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

DQL-分页查询

1. 语法SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;
2. 注意
   1. 起始索引从0开始，起始索引 = (查询页码 - 1) * 每页显示记录数。
   2. 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL是LIMIT
   3. 如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10;

DOL-执行顺序

1. FROM子句
2. WHERE子句
3. GROUP BY子句
4. HAVING子句
5. SELECT子句
6. ORDER BY子句
7. LIMIT子句

DCL

英文全称Data Control Language(数据控制语言)，用来管理数据库用户、控制数据库的访问权限

DCL-管理用户

1. 查询用户

USE mysql;
SELECT * FROM user;

2. 创建用户CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';，若想让该用户在任意主机上访问MySQL，主机名可以写%
3. 修改用户密码ALTER USER '用户名'@'主机名' IDENTTIFIED WITH mysql_native_passwoed BY '新密码';
4. 删除用户DROP USER '用户名'@'主机名';
5. 注意
   1. 主机名可以使用%通配
   2. 这类SQL开发人员操作的比较少，主要是DBA(Database Administrator 数据库管理员)使用

DCL-权限控制

以下是MySQL中常用的几种权限

1. 查询权限SHOW GRANTS FOR '用户名'@'主机名';
2. 授予权限GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
3. 撤销权限REVOKE 权限列表 ON 数据库名.表名 FROM '用户名@ '主机名';
4. 注意
   1. 多个权限之间，使用逗号分隔
   2. 授权时，数据库名和表名可以使用 * 进行通配，代表所有

函数

函数是指一段可以直接被另一端程序调用的程序或代码

字符串函数

常用的字符串函数如下

SELECT 函数(参数);

数值函数

常见的数值函数如下

日期函数

常见的日期函数如下

流程函数

流程函数可以在SQL语句中实现条件筛选，从而提高语句的效率

约束

概述

1. 概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据

2. 目的：保证数据库中数据的正确、有效性和完整性

3. 分类
   | 约束 | 描述 | 关键字 |
   | --- | --- | --- |
   | 非空约束 | 限制该字段的数据不能为null | NOT NULL |
   | 唯一约束 | 保证该字段的所有数据都是唯一、不重复的 | UNIQUE |
   | 主键约束 | 主键是一行数据的唯一标识，要求非空且唯一 | PRIMARY KEY |
   | 默认约束 | 保存数据时，如果为指定该字段的值，则采用默认值 | DEFAULT |
   | 检查约束(8.0.16版本之后) | 保证字段值满足某一个条件 | CHECK |
   | 外键约束 | 用来让两张表的数据之间建立连接，保证数据的一致性和完整性 | FOREIGN KEY |

4. 注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束

外键约束

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

1. 添加外键
   1. 创建表时添加

CREATE TABLE(
  字段名 数据类型,
  ...
  [CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
);

2. 创建表后添加ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名);
3. 删除外键ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

删除/更新行为

1. 添加CASCADEALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;
2. 添加SET NULLALTER TABLE 表明 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名 (主表字段名) ON UPDATE SET NULL ON DELETE SET NULL;

多表查询

多表关系

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

1. 一对多(多对一)
2. 多对多
3. 一对一

一对多(多对一)

1. 案例：部门与员工的关系
2. 关系：一个部门对应多个员工，一个员工对应一个部门
3. 实现：在多的一方建立外键，指向一的一方的主键

多对多

1. 案例：学生与课程的关系
2. 关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择
3. 实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

一对一

1. 案例：用户与用户详情的关系
2. 关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
3. 实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

多表查询概述

1. 概述：指从多张表中查询数据
2. 笛卡尔积：笛卡尔乘积是指在数学中，两个集合A集合和B集合的所有组合情况(在多表查询时，需要消除无效的笛卡尔积)
3. 多表查询分类
   1. 连接查询
      1. 内连接：相当于查询A、B交集部分数据
      2. 外联接
         1. 左外连接：查询左表所有数据，以及两张表交集部分数据
         2. 右外连接：查询右表所有数据，以及两张表交集部分数据
      3. 自连接：当前表与自身的连接查询，自连接必须使用表别名
   2. 子查询

连接查询

连接查询-内连接

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

1. 隐式内连接SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ...;
2. 显示内连接SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件 ...;
3. 若已经给表起了别名，则不能再使用表原来的名字，注意，仅限于当前DQL语句中，它不会改变实际上表的名字
4. 内连接不会显示为null的数据

连接查询-外连接

1. 左外连接SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件 ...;,相当于查询表1(左表)的所有数据 包含 表1和表2交集部分的数据
2. 右外连接SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件 ...;,相当于查询表2(右表)的所有数据 包含 表1和表2交集部分的数据
3. 外连接会显示为null的数据

连接查询-自连接

1. 语法SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...;
2. 自连接查询，可以是内连接查询，也可以是外连接查询

联合查询-union, union all

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集

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

1. 联合查询的多张表的列数必须保持一致，字段类型也需要保持一致
2. union all会将全部的数据直接合并在一起，union会对合并之后的数据去重

子查询

1. 概念：SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询
2. 语法SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
3. 子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个
4. 根据子查询结果不同，分为：
   1. 标量子查询（子查询结果为单个值）
   2. 列子查询（子查询结果为一列）
   3. 行子查询（子查询结果为一行）
   4. 表子查询（子查询结果为多行多列）
5. 根据子查询位置，分为：WHERE之后、FROM之后、SELECT之后

子查询-标量子查询

1. 子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询
2. 常用的操作符：= <> > >= < <=

子查询-列子查询

1. 子查询返回的结果是一列（可以是多行），这种子查询称为列子查询
2. 常用的操作负： IN、NOT IN、ANY、SOME、ALL
   | 操作符 | 描述 |
   | --- | --- |
   | IN | 在指定的集合范围之内，多选一 |
   | NOT IN | 不在指定的集合范围之内 |
   | ANY | 子查询返回列表中，有任意一个满足即可 |
   | SOME | 与ANY等同，使用SOME的地方都可以使用ANY |
   | ALL | 子查询返回列表的所有值都必须满足 |

子查询-行子查询

1. 子查询返回的结果是一行（可以是多列），这种子查询称为行子查询
2. 常用的操作符：=、 <>、 IN、 NOT IN

子查询-表子查询

1. 子查询返回的结果是多行多列，这种子查询称为表子查询
2. 常用的操作符：IN

事务

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务

事务操作

1. 查看/设置事务提交方式

SELECT @@autocommit; -- 查看事务提交方式，为1是自动提交，为0是手动提交
SET @@autocommit = 0; -- 设置事务提交方式为手动提交

2. 提交事务COMMIT;
3. 回滚事务ROLLBACK;
4. 开启事务START TRANSACTION 或 BEGIN;

事务四大特性(ACID)

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

并发事务问题

事务隔离级别

1. 查看事务隔离级别SELECT @@TRANSACTION_ISOLATION;
2. 设置事务隔离级别SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
3. 事务隔离级别越高，数据越安全，但是性能越低

本栏目推荐文章
• 2024/1/13 算法笔记
• Python学习笔记
• Jmeter 如何连接mysql数据库？
• HDP 笔记
• 如何通过Navicat把MySQL远程数据库导入到本地数据库
• 学习进度笔记2
• 微软企业库Unity学习笔记(一)
• 微软企业库Unity学习笔记(二)
• kepware关于U-CON的使用笔记 (串口_非主动问答式)
• Anaconda使用笔记