【学习笔记】MySQL（Ⅰ）

贺一航【Niki】

已于 2024-06-19 12:58:19 修改

阅读量679

点赞数 24

文章标签：学习笔记 mysql

于 2024-06-05 16:21:07 首次发布

本文链接：https://blog.csdn.net/Eddie_hyh/article/details/139428455

版权

这篇博客是MySQL的基础教程，涵盖了MySQL的概述、优缺点和关系型数据库模型。详细介绍了SQL的通用语法、DDL（数据定义语言）如创建、删除数据库和表，DML（数据操纵语言）的增删改查操作，DQL（数据查询语言）的查询技巧，以及DCL（数据控制语言）的权限管理。此外，还讨论了MySQL的事务管理，包括事务的四大特性ACID和不同的事务隔离级别。

摘要由CSDN通过智能技术生成

MySQL（Ⅰ）

1、介绍
- 1.1、概述
- 1.2、MySQL 的优缺点
- 1.4、关系型数据库模型
2、基础篇 —— SQL（结构化查询语言）
- 2.1、通用语法
- 2.2、SQL 语句的分类
- 2.3、DDL 语句
- - 2.3.1 数据库结构相关的语法
  - 2.3.2 数据表相关的语法
  - 2.3.3 类型
- 2.4、DML语句
- - 2.4.1 增
  - 2.4.2 改
  - 2.4.3 删
- 2.5、DQL 语句
- - 2.5.1 简单查询
  - 2.5.2 条件查询
  - 2.5.3 分组查询
  - 2.5.4 排序查询
  - 2.5.5 分页查询
  - 2.5.6 总结 —— DQL 的执行顺序
- 2.6、DCL 语句
3、基础篇 —— 函数
- 3.1、字符串函数
- 3.2、数值函数
- 3.3、日期时间函数
- 3.4、控制流函数
4、基础篇 —— 约束
5、基础篇 —— 多表查询
- 5.1、笛卡尔积
- 5.2、内连接
- 5.2、外连接
- 5.3、自连接
- 5.4、联合查询
- 5.4、子查询
- - 5.4.1 标量子查询
  - 5.4.2 列子查询
  - 5.4.3 行子查询
6、基础篇 —— 事务
- 6.1、简介
- 6.2、事务操作
- 6.3、事务的实现
- 6.4、事务的四大特性 ACID
- 6.5、并发事务出现的问题及事务隔离级别
- - 6.5.1 Read Uncommitted 存在脏读问题
  - 6.5.2 Read Committed 解决脏读问题
  - 6.5.3 Read Committed 存在不可重复读问题
  - 6.5.4 Repeatable Read 解决不可重复读问题
  - 6.5.5 Repeatable Read 存在幻读问题
  - 6.5.6 SERIALIZABLE 解决幻读问题
- 6.6、总结

1、介绍

1.1、概述

MySQL是一种广泛使用的开源关系数据库管理系统（RDBMS），由瑞典公司MySQL AB开发，后被Sun Microsystems收购，最终被Oracle Corporation收购。MySQL通常用于Web应用开发，其高性能、可靠性和便捷性使其成为许多开发者和企业的首选数据库管理系统
在这里插入图片描述

下载完 MySQL 之后会自动注册为系统服务并开启，要启动和停止服务有如下两种方式
1、打开系统的 ”服务“ 面板，找到 MySQL 相应的项然后启动 / 停止即可
2、使用系统的命令行工具，执行net start mysql的服务名称进行启动，执行net stop mysql的服务名称进行停止

链接 MySQL 的方式
1、打开 MySQL 自带的命令行工具 Common Line Client，然后输入密码即可
2、使用系统的命令行工具 cmd，输入 mysql [-h 127.0.0.1] [-p 3306] -u root -p，然后输入密码即可
3、使用 Navicat、DataGrip 之类的图形化工具进行连接

1.2、MySQL 的优缺点

优点	描述
易于使用	MySQL安装和配置简单，提供了用户友好的图形界面工具（如MySQL Workbench）和命令行工具
开源且免费	社区版本免费，且代码公开，可以根据需要进行定制
高性能	在处理大规模数据和高并发环境中表现优越，特别是对于读操作的优化非常好
跨平台支持	兼容多种操作系统，使其在不同环境中都能运行
灵活的存储引擎选择	用户可以根据应用需求选择不同的存储引擎，优化性能
广泛的社区和文档支持	提供了丰富的官方文档和大量的第三方资源，方便用户学习和解决问题

缺点	描述
功能有限	相比一些商业数据库系统（如Oracle Database、Microsoft SQL Server），MySQL在某些高级功能（如复杂查询优化、分析功能、并行查询等）方面较为薄弱
事务处理能力	虽然InnoDB存储引擎支持事务处理，但MySQL整体的事务处理能力和复杂性控制不如一些其他高级数据库系统
存储引擎不一致	不同的存储引擎在功能和特性上有所差异，可能导致开发和维护的复杂性增加
扩展性有限	虽然MySQL支持一定程度的横向扩展，但在超大规模的分布式系统中，扩展性和灵活性不如一些NoSQL数据库（如Cassandra、MongoDB）
并发处理	在极高并发场景下，MySQL的性能可能会受到限制，需要依赖于架构优化和硬件资源提升

1.4、关系型数据库模型

关系型数据库模型（Relational Database Model）是一种将数据组织成表格（关系）形式的数据库模型。每个表格包含若干行（记录）和列（字段），数据通过这些表格进行存储、管理和检索。关系型数据库模型是由Edgar F. Codd在1970年提出的，它的核心概念是通过数学上的关系理论来实现数据的组织和查询

基本概念	描述
数据库 DB	数据库是一个有组织的数据集合，用于存储、管理和检索数据。数据库可以是文件系统中的一组文件，也可以是某种专门的数据库管理系统（DBMS）中的数据集合。在MySQL中，数据库是一个逻辑容器，包含了一组相关的表、视图、存储过程、触发器等
数据库管理系统 DBMS	数据库管理系统是用于创建、管理和操作数据库的软件。它提供了一系列工具和接口，使用户能够定义数据库结构、存储数据、执行查询、进行备份和恢复等。DBMS负责管理数据库中的数据，确保数据的一致性、完整性和安全性
关系型数据库管理系统 RDBMS	关系型数据库管理系统是一种特定类型的DBMS，它使用关系型数据模型来管理数据。关系型数据模型将数据组织成表格（关系），表格由行和列组成。RDBMS通过SQL（结构化查询语言）提供对数据的访问和操作。MySQL就是一种RDBMS
MySQL	一种具体的RDBMS实现，广泛应用于各种数据管理需求

TIP：DBA（Database Administrator）是数据库管理员

2、基础篇 —— SQL（结构化查询语言）

2.1、通用语法

	通用语法
①	SQL 语句可以写单行 / 多行，以分号结尾（非必须）
②	SQL 语句可以使用空格 / 缩进来增强语句的可读性
③	MySQL 数据库的 SQL 不区分大小写，关键字建议用大写
④	单行注释使用`--`或者`#`，多行注释使用`/* */`

2.2、SQL 语句的分类

分类	描述
数据定义语言 DDL	用于定义和管理数据库结构（数据库、表、字段）
数据操作语言 DML	用于修改数据库中的数据（增、删、改）
数据查询语言 DQL	用于查询数据库中的数据（查）
数据控制语言 DCL	用于创建数据库用户、控制数据库的访问权限

2.3、DDL 语句

2.3.1 数据库结构相关的语法

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

-- 创建数据库
CREATE DATABASE mysql_study DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

-- 显示当前的数据库
SELECT DATABASE();

2.3.2 数据表相关的语法

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

创建表：CREATE TABLE 表名( 字段1 类型 [COMMENT 注释], 字段2 类型 [COMMENT 注释]... )[COMMENT 注释]
修改表名：rename table 旧名 to 新名
修改表结构（添加字段）：ALTER TABLE 表名 ADD 字段类型[(长度)] [COMMENT 注释] [约束];
修改表结构（修改字段）：ALTER TABLE 表名 CHANGE 旧字段名新字段名类型[(长度)] [COMMENT 注释] [约束];
修改表结构（删除字段）：ALTER TABLE 表名 DROP 字段;
删除表：DROP TABLE [IF EXISTS] 表名;
删除表并重新创建该表（清空数据）：TRUNCATE TABLE 表名

-- 创建表
CREATE TABLE tb_user ( 
    id INT COMMENT '编号',
    name VARCHAR ( 50 ) COMMENT '用户名',
    age INT COMMENT '姓名',
    gender VARCHAR ( 1 ) COMMENT '性别'   # 最后一个字段不能加逗号
) COMMENT '用户表';

-- 添加字段
ALTER TABLE tb_user ADD nickname VARCHAR ( 20 ) COMMENT '昵称';

-- 修改字段
ALTER TABLE tb_user CHANGE nickname nickname VARCHAR ( 30 ) COMMENT '昵称';

-- 删除字段
ALTER TABLE tb_user DROP nickname;

2.3.3 类型

数值类型	描述	大小	有符号范围	无符号范围
TINYINT	小整数值	1B	（-128，127）	（0，255）
SMALLINT	大整数值	2B	（-32768，32767）	（0，65535）
MEDIUMINT	大整数值	3B	（-8388608，8388607）	（0，16777215）
INT / INTEGER	大整数值	4B	（-2³¹-1，2³¹）	（0，2³²-1）
BIGINT	极大整数值	8B	（-2⁶³-1，2⁶³）	（0，2⁶⁴-1）
FLOAT	单精度浮点数	4B	略	略
DOUBLE	双精度浮点数	8B	略	略
DECIMAL	小数值	/	依赖于精度M和标度D	依赖于精度M和标度D

字符串类型	描述	范围
CHAR	定长字符串	0~2⁸-1B
VARCHAR	变成字符串	0~2¹⁶-1B
TINYBLOB	二进制数据	0~2⁸-1B
BLOB	二进制数据	0~2¹⁶-1B
MEDIUMBLOB	二进制数据	0~2²⁴-1B
LONGBLOB	二进制数据	0~2³²-1B
TINYTEXT	文本数据	0~2⁸-1B
TEXT	文本数据	0~2¹⁶-1B
MEDIUMTEXT	文本数据	0~2²⁴-1B
LONGTEXT	文本数据	0~2³²-1B

日期类型	描述	格式	大小	范围
DATE	日期值	YYYY-MM-DD	3B	1000-01-01 ~ 9999-12-31
TIME	时间值或持续时间	HH:MM:SS	3B	-839:59:59~838:59:59
YEAR	年份值	YYYY	1B	1901~2155
DATETIME	日期值+时间值	YYYY-MM-DD HH:MM:SS	8B	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
TIMESTAMP	日起值+时间值的时间戳形式	/	4B	1970-01-01 00:00:01 ~ 2038-01-19 03:14:07

-- 创建一张员工表
CREATE TABLE employee (
    id INT COMMENT '编号',
    workno VARCHAR ( 10 ) COMMENT '员工工号',
    name VARCHAR ( 10 ) COMMENT '员工姓名',
    gender CHAR ( 1 ) COMMENT '性别',
    age TINYINT UNSIGNED COMMENT '年龄',
    idcard CHAR ( 18 ) COMMENT '身份证号',
	entrydate DATE COMMENT '入职时间' 
) COMMENT '员工表';

Tip
1、MySQL 8.0 及以上版本 INT 类型不需要指定宽度
2、字符串类型需要指定字符长度（不是字节长度）

2.4、DML语句

2.4.1 增

指定字段，一条：INSERT INTO 表名 (字段1,字段2...) VALUES (值1,值2...);
指定字段，批量：INSERT INTO 表名 (字段1,字段2...) VALUES (值1,值2...),(值1,值2...),...;
全部字段，一条：INSERT INTO 表名 VALUES (值1,值2...);
全部字段，批量：INSERT INTO 表名 VALUES (值1,值2...),(值1,值2...),...;

-- 插入一条数据
INSERT INTO employee(id, workno, name, gender, age, idcard, entrydate) VALUE (1, '1', 'niki', '男', 24, '123456789012345678', '2000-01-01')

-- 插入多条数据
INSERT INTO employee
VALUES (2, 2, '张无忌', '男', 18, '123456789012345670', '2005-09-01'),
       (3, 3, '韦一笑', '男', 38, '123456789712345670', '2005-08-01'),
       (4, 4, '赵敏', '女', 18, '123456757123845670', '2009-12-01'),
       (5, 5, '小昭', '女', 16, '123456769012345678', '2007-07-01'),
       (6, 6, '杨道', '男', 28, '12345678931234567X', '2006-01-01'),
       (7, 7, '范骚', '男', 40, '123456789212345670', '2005-05-01'),
       (8, 8, '黛绮丝', '女', 38, '123456157123645670', '2015-05-01'),
       (9, 9, '范凉凉', '女', 45, '123156789012345678', '2010-04-01'),
       (10, 10, '陈友凉', '男', 53, '123456789012345670', '2011-01-01'),
       (11, 11, '张士诚', '男', 55, '123567897123465670', '2015-05-01'),
       (12, 12, '常遇春', '男', 32, '123446757152345670', '2004-02-01'),
       (13, 13, '张三丰', '男', 88, '123656789012345678', '2020-11-01'),
       (14, 14, '灭绝', '女', 65, '123456719012345670', '2019-05-01'),
       (15, 15, '胡青牛', '男', 70, '12345674971234567X', '2018-04-01'),
       (16, 16, '周芷若', '女', 18, NULL, '2012-06-01');

2.4.2 改

修改数据：UPDATE 表名 SET 字段1=值1,字段2=值2... [WHERE 条件]

-- 修改一条数据的一个字段
UPDATE employee SET name='siri' WHERE id = 8;
-- 修改一条数据的多个字段
UPDATE employee SET name='pyy',gender='男' WHERE id = 14;
-- 修改多条数据
UPDATE employee SET entrydate='2008-01-01' WHERE 1;
-- 查看结果
SELECT * FROM employee WHERE id = 8 OR id = 14

2.4.3 删

删除数据：DELETE FROM 表名 [WHERE 条件]

-- 删除数据
DELETE FROM employee WHERE id >= 15;
-- 查看结果
SELECT * FROM employee

2.5、DQL 语句

先重置一下 employee 表

-- 删表
DROP TABLE employee;
-- 建表
CREATE TABLE `employee`
(
    `id`          int                                    DEFAULT NULL COMMENT '编号',
    `workno`      varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '员工工号',
    `name`        varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL