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

MySQL知识解析

news 2025/8/16 17:44:03

目录

一、MySQL介绍

二、数据类型

三、数据库命令

四、表命令

1.查看所有表

2.创建数据库表

3.查看表结构

4.删除数据库表

5.修改表

五、数据操作CURD

1. 插入数据(INSERT)

2. 查询数据(SELECT)

3. 更新数据(UPDATE)

4. 删除数据(DELETE)

六、约束

1.主键约束primary key

2.唯一约束unique

3.检查约束check

4.非空约束not null

5.默认约束default

6.外键约束foreign key(列名)  references 表名（主键）

七、外键

八、三大范式

1.第一范式

2.第二范式

3.第三范式

九、用户与权限

1. 用户管理

2. 权限管理

十、条件查询

1.比较运算符

2.逻辑运算符

3.成员运算符

4.范围运算符

5.模糊查询

6.空查询

十一、分页查询

十二、排序查询

十三、分组查询

十四、去重查询

十五、聚合函数

十六、关联查询

1.嵌套查询

2.连接查询

十七、关联关系

十八、视图

1. 创建视图

2.使用视图

3. 修改视图

4. 删除视图

十九、存储过程与函数

1. 存储过程

2. 函数

二十、事务

1.事务特性(ACID)

2.事务生命周期

二十一、索引

1. 索引类型

2.索引方法

二十二、存储引擎

总结

一、MySQL介绍

  • 开源关系型数据库管理系统(RDBMS)

  • 使用SQL(结构化查询语言)进行数据库操作

  • 支持多种操作系统，包括Linux、Windows和macOS

  • 由Oracle公司开发、维护和支持

二、数据类型

整数tinyint、smallint、mediumint、int、bigint
浮点数float、double、decimal
字符串char、varchar、text、mediumtext、longtext
二进制推荐：可以将二进制文件存放在磁盘，数据库中存储文件对应的磁盘路径
日期与时间date、time、datetime、timestamp
枚举enum

三、数据库命令

不区分大小写，单行分号结尾。

  1. 创建数据库：create database 数据库名 character  set  utf8;
  2. 查看所有数据库：show databases;
  3. 删除数据库： drop database 数据库名;
  4. 切换数据库：use 数据库名;
  5. 查看数据库：select database();

四、表命令

1.查看所有表

show tables;

2.创建数据库表

CREATE TABLE table_name (column1 datatype constraints,column2 datatype constraints,...PRIMARY KEY (one_or_more_columns)
);

3.查看表结构

desc 表名；

4.删除数据库表

DROP TABLE table_name;

5.修改表

-- 添加列
ALTER TABLE table_name ADD column_name datatype;-- 修改列
ALTER TABLE table_name MODIFY COLUMN column_name datatype;-- 删除列
ALTER TABLE table_name DROP COLUMN column_name;-- 重命名表
RENAME TABLE old_name TO new_name;

五、数据操作CURD

1. 插入数据(INSERT)

-- 插入单行
INSERT INTO table_name (column1, column2) VALUES (value1, value2);-- 插入多行
INSERT INTO table_name (column1, column2) 
VALUES (value1, value2), (value3, value4), (value5, value6);

2. 查询数据(SELECT)

-- 基本查询
SELECT column1, column2 FROM table_name;-- 条件查询
SELECT * FROM table_name WHERE condition;-- 排序
SELECT * FROM table_name ORDER BY column1 ASC, column2 DESC;-- 分组
SELECT column1, COUNT(*) FROM table_name GROUP BY column1;-- 分页
SELECT * FROM table_name LIMIT offset, count;

3. 更新数据(UPDATE)

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

4. 删除数据(DELETE)

DELETE FROM table_name WHERE condition;

六、约束

1.主键约束primary key

唯一且非空，一表只能有一个。

ALTER TABLE emp ADD CONSTRAINT pk_emp PRIMARY KEY (id);

2.唯一约束unique

该列不能出现重复值。

ALTER TABLE emp ADD CONSTRAINT uq_email UNIQUE (email);

3.检查约束check

某一列需要满足条件。

ALTER TABLE emp ADD CONSTRAINT chk_age CHECK (age BETWEEN 0 AND 120);

4.非空约束not null

不能为空。

ALTER TABLE emp MODIFY city VARCHAR(20) NOT NULL;

5.默认约束default

拥有默认值。

ALTER TABLE emp ALTER city SET DEFAULT 'Beijing';

6.外键约束foreign key(列名)  references 表名（主键）

一个表中的主键可以作为另外一个表中的外键。

ALTER TABLE emp ADD CONSTRAINT fk_emp_deptFOREIGN KEY (dept_id) REFERENCES dept(id)ON UPDATE CASCADEON DELETE SET NULL;

七、外键

1.级联删除、级联更新cascade。

2.拒绝更新或删除父表中的记录restrict。

-- 父表：部门
CREATE TABLE dept (dept_id INT PRIMARY KEY,name    VARCHAR(30)
);-- 子表：员工
CREATE TABLE emp (emp_id  INT PRIMARY KEY,name    VARCHAR(30),dept_id INT,-- 外键约束CONSTRAINT fk_emp_deptFOREIGN KEY (dept_id)REFERENCES dept(dept_id)ON UPDATE CASCADE   -- 父表更新，子表跟着更新ON DELETE SET NULL  -- 父表删除，子表设为 NULL
);

八、三大范式

1.第一范式

每个字段都是不可再分的原子值。

2.第二范式

必须满足第一范式，所有非主键字段必须完全依赖于整个主键，而不能只依赖于主键的一部分。

3.第三范式

必须满足第二范式，非主键字段不能依赖于其他非主键字段（消除传递依赖）。

九、用户与权限

1. 用户管理

-- 创建用户
CREATE USER 'username'@'host' IDENTIFIED BY 'password';-- 修改密码
ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';-- 删除用户
DROP USER 'username'@'host';

2. 权限管理

-- 授予权限
GRANT ALL PRIVILEGES ON database.* TO 'username'@'host';
GRANT SELECT, INSERT ON database.table TO 'username'@'host';-- 撤销权限
REVOKE ALL PRIVILEGES ON database.* FROM 'username'@'host';-- 刷新权限
FLUSH PRIVILEGES;

十、条件查询

1.比较运算符

SELECT * FROM users WHERE age < 18 ;

2.逻辑运算符

-- AND: 同时满足多个条件
SELECT * FROM users WHERE age > 18 AND gender = '男';-- OR: 满足任意一个条件
SELECT * FROM users WHERE age < 18 OR age > 60;-- NOT: 否定条件
SELECT * FROM users WHERE NOT age = 20;

3.成员运算符

-- 查询年龄为18、20或22岁的用户
SELECT * FROM users WHERE age IN (18, 20, 22);-- 等价于
SELECT * FROM users WHERE age = 18 OR age = 20 OR age = 22;

4.范围运算符

-- 查询年龄在18到30岁之间的用户(包含18和30)
SELECT * FROM users WHERE age BETWEEN 18 AND 30;

5.模糊查询

-- % 表示任意多个字符
SELECT * FROM users WHERE name LIKE '张%';  -- 姓张的用户-- _ 表示单个字符
SELECT * FROM users WHERE name LIKE '_三';  -- 名字为两个字且第二个字是"三"的用户

6.空查询

-- 查询没有填写电话号码的用户
SELECT * FROM users WHERE phone IS NULL;-- 查询填写了电话号码的用户
SELECT * FROM users WHERE phone IS NOT NULL;

十一、分页查询

SELECT 列名 FROM 表名
[WHERE 条件]
[ORDER BY 排序字段]
LIMIT 偏移量, 每页记录数;

分页通用公式：limit  (m-1)*n, n   （每一页显示n条，第m页）

-- 第一页，每页10条
SELECT * FROM products ORDER BY id LIMIT 0, 10;
-- 等价于
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 0;-- 第二页
SELECT * FROM products ORDER BY id LIMIT 10, 10;
-- 等价于
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 10;

十二、排序查询

SELECT 列名1, 列名2, ...
FROM 表名
[WHERE 条件]
ORDER BY 排序列1 [ASC|DESC], 排序列2 [ASC|DESC], ...;
-- 默认升序(ASC)
SELECT * FROM products ORDER BY price;-- 显式指定升序
SELECT * FROM products ORDER BY price ASC;-- 降序排序
SELECT * FROM products ORDER BY price DESC;

十三、分组查询

SELECT 列名1, 列名2, ..., 聚合函数(列名)
FROM 表名
[WHERE 条件]
GROUP BY 分组列1, 分组列2, ...
[HAVING 分组后条件]
[ORDER BY 排序列]
[LIMIT 限制行数];
-- 统计每个部门的员工数量
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

十四、去重查询

-- 单列去重
SELECT DISTINCT department FROM employees;-- 多列联合去重
SELECT DISTINCT department, position FROM employees;

十五、聚合函数

  • COUNT()：计数

  • SUM()：求和

  • AVG()：平均值

  • MAX()：最大值

  • MIN()：最小值

  • GROUP_CONCAT()：连接字符串(MySQL特有)

十六、关联查询

1.嵌套查询

在一个查询语句中嵌入另一个查询语句。

-- WHERE子句中的子查询
SELECT * FROM table1 WHERE column1 IN (SELECT column1 FROM table2);-- FROM子句中的子查询
SELECT * FROM (SELECT column1, column2 FROM table1) AS temp_table;-- SELECT子句中的子查询
SELECT column1, (SELECT MAX(column2) FROM table2) AS max_value FROM table1;

2.连接查询

笛卡尔连接、内连接、外连接、全连接。

-- 内连接
SELECT a.column1, b.column2 
FROM table1 a INNER JOIN table2 b ON a.id = b.id;-- 左连接
SELECT a.column1, b.column2 
FROM table1 a LEFT JOIN table2 b ON a.id = b.id;-- 右连接
SELECT a.column1, b.column2 
FROM table1 a RIGHT JOIN table2 b ON a.id = b.id;-- 全连接(MySQL不支持FULL JOIN，需用UNION实现)
SELECT a.column1, b.column2 FROM table1 a LEFT JOIN table2 b ON a.id = b.id
UNION
SELECT a.column1, b.column2 FROM table1 a RIGHT JOIN table2 b ON a.id = b.id;

十七、关联关系

1.自关联：一个表与自己发生关联。

2.一对一：可以合并为一个表，外键定义在任意一方。

3.一对多：外键定义在多方。

4.多对多：外键定义在任意一方，通过第三个表来存储两个主表的主键。

十八、视图

视图是一张虚拟表，操作视图相当于操作真实表。

1. 创建视图

CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition;

2.使用视图

SELECT * FROM view_name;

3. 修改视图

ALTER VIEW view_name AS SELECT column1, column2, column3 FROM table_name;

4. 删除视图

DROP VIEW view_name;

十九、存储过程与函数

1. 存储过程

-- 创建存储过程
DELIMITER //
CREATE PROCEDURE procedure_name(IN param1 INT, OUT param2 VARCHAR(255))
BEGIN-- SQL语句SELECT column1 INTO param2 FROM table_name WHERE id = param1;
END //
DELIMITER ;-- 调用存储过程
CALL procedure_name(1, @result);
SELECT @result;

2. 函数

-- 创建函数
DELIMITER //
CREATE FUNCTION function_name(param1 INT) RETURNS INT
DETERMINISTIC
BEGINDECLARE result INT;-- 计算逻辑SET result = param1 * 2;RETURN result;
END //
DELIMITER ;-- 调用函数
SELECT function_name(10);

二十、事务

1.事务特性(ACID)

  • 原子性(Atomicity)：事务中的操作要么全部提交，要么全部回滚。

  • 一致性(Consistency)：事务执行前后，数据库都必须处于一致状态。

  • 隔离性(Isolation)：并发事务之间互不影响。

  • 持久性(Durability)：一旦提交，数据永久保存，即使宕机也不丢失。

2.事务生命周期

-- 开始事务
START TRANSACTION;-- 执行SQL语句
INSERT INTO table1 VALUES (1, 'value1');
UPDATE table2 SET column1 = 'new_value' WHERE id = 1;-- 提交或回滚
COMMIT; -- 提交事务
-- 或
ROLLBACK; -- 回滚事务

二十一、索引

为了提升性能，建立索引。

1. 索引类型

  • 普通索引(INDEX)

  • 唯一索引(UNIQUE)

  • 主键索引(PRIMARY KEY)

  • 全文索引(FULLTEXT)

2.索引方法

BTREE、HASH

二十二、存储引擎

存储引擎是MySQL的核心组件，负责数据的存储、检索和管理。MySQL采用插件式架构，支持多种存储引擎，每种引擎都有其特点和适用场景。InnoDB为默认存储引擎。

总结

MySQL作为最流行的开源关系型数据库，提供了全面的数据管理解决方案。其核心特性包括支持SQL标准、ACID事务、多种存储引擎和跨平台能力。数据库操作涵盖CRUD基本命令、复杂查询（连接、子查询、聚合函数）以及索引优化。高级功能涉及视图、存储过程、触发器和事务管理，确保数据完整性和业务逻辑封装。MySQL提供完善的用户权限体系、备份恢复机制和性能优化工具，如EXPLAIN分析和配置参数调整。存储引擎方面，InnoDB作为默认引擎支持事务和行级锁，MyISAM适合读密集型场景，MEMORY引擎提供高速内存访问，ARCHIVE引擎实现高压缩归档。MySQL 8.0引入了窗口函数、CTE和JSON增强等现代特性。与各种编程语言和ORM框架的集成使其成为全栈开发的理想选择。在高可用方面支持主从复制、组复制和InnoDB集群。安全措施包括访问控制、数据加密和审计功能。云服务集成和丰富的监控工具使其适应现代化部署环境。学习资源涵盖官方文档、在线课程和活跃社区，而持续的功能演进保持其在大数据时代的竞争力。无论是OLTP系统、数据仓库还是Web应用，MySQL都能提供可靠、高效且灵活的数据存储解决方案。

http://www.lryc.cn/news/622455.html

相关文章：

  • linux-----------------锁
  • week1-[一维数组]传送
  • 【Spring框架】SpringAOP
  • 六大主流负载均衡算法
  • Java项目基本流程（四）
  • Python day45
  • lcm通信库介绍与使用指南
  • 【121页PPT】锂膜产业MESERP方案规划建议（附下载方式）
  • 【学习嵌入式day-25-线程】
  • 华测科技的3D GPR数据分析
  • 前瞻性技术驱动，枫清科技助力制造企业借助大模型完成生产力转化
  • 2025戴尔科技峰会：破局者的力量与智慧
  • 【C#补全计划】事件
  • PCA降维理论详解
  • 学习嵌入式之硬件——I2C
  • 系统介绍pca主成分分析算法
  • C语言：指针（5）
  • 智能指针：C++内存管理的利器
  • c++程序示例：多线程下的实例计数器
  • [HDCTF 2023]Normal_Rsa(revenge)
  • 主流开源实时互动数字人大模型
  • 读书笔记-积极心理学 《心流，最优体验心理学》
  • 条件变量的基本介绍与有界缓冲区问题
  • 小红书帖子评论的nodejs爬虫脚本
  • 补充日志之-配置文件解析指南(Centos7)
  • CAXA电子图板2026（国产CAD之光）
  • 机器学习之PCA
  • pyqt5无法显示opencv绘制文本和掩码信息
  • OpenCV 阈值处理
  • 应用侧华为云LoTDA设备接入平台