MySQL知识大全

1、概述        

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle旗下产品。它使用SQL语言进行数据库操作，具有以下特点：

• 开源免费（部分高级功能收费）

• 性能高、运行稳定

• 社区活跃、文档丰富

• 支持多种操作系统和编程语言

2、数据库的基础概念

结构概念：

数据库 (Database)、表 (Table)、行/记录 (Row/Record)、列/字段 (Column/Field)

这几个结构概念之间的关系是：

一个数据库种有一个或多个表

一个表有一个或多个行

一个表有一个或多个列

而MySQL是一个二维关系型数据库，这里的表就可以类似看作我们平时使用的excel表格

因此，SQL语句就有对这几个概念的操作语句

除了最基础的这几个结构概念，还有其他的基础概念

其他的基础概念：

主键 (Primary Key)、外键 (Foreign Key)、索引 (Index)、数据类型

主键 (Primary Key)

概念：
主键是数据库表中用于唯一标识每一条记录的一个列或一组列。

核心特性：

1. 唯一性：主键的值必须是唯一的，不能有重复。

2. 非空性：主键的值不能为 NULL，必须包含一个值。

3. 唯一标识：一个表只能有一个主键。主键的值一旦分配，就最好不要再更改，因为它可能被其他表的外键所引用。

作用：

• 保证数据的实体完整性。

• 作为表中记录的唯一标识符，方便快速定位和操作特定记录。

• 通常是表中其他外键所引用的目标。

外键 (Foreign Key)

概念：
外键是表中的一列（或一组列），其值必须与另一个表的主键值相匹配。它用来建立两个表之间的关联关系。

核心特性：

1. 引用完整性：外键的值必须是其引用的主表中主键存在的值，或者是 NULL。

2. 关联关系：它定义了表与表之间的“从属”或“关联”关系。被引用的表称为主表（父表），包含外键的表称为从表（子表）。

作用：

• 维护数据库的引用完整性，防止“孤立的”记录（例如：有一个订单指向一个不存在的用户）。

• 明确表与表之间的逻辑关系，是数据库关系模型的基础。

索引 (Index)

概念：
索引是数据库中一种提高查询速度的数据结构，类似于书籍的目录。

核心特性：

1. 加速查询：通过索引可以快速定位到数据所在的位置，而无需扫描整个表。

2. 额外的开销：索引本身需要占用额外的存储空间。并且在对表进行插入、更新、删除操作时，数据库也需要维护索引，这会降低写操作的速度。

3. 自动创建：通常，数据库会为主键自动创建索引。

作用：

• 极大地优化数据检索（SELECT 查询）的性能。

• 通过唯一索引（UNIQUE Index）也可以强制实现某列值的唯一性（但允许有NULL值，这与主键不同）。

视图 (View)

概念：
视图是一种虚拟表，其内容由查询结果动态定义。它本身并不实际存储数据，而是基于一个或多个底层基表（或其它视图）的 SQL 查询语句。

核心特性：

1. 虚拟性：视图不是物理存在的表，没有实际的数据存储。每次访问视图时，数据库都会执行其定义的查询语句来动态生成结果集。

2. 简化性：可以封装复杂的查询逻辑，将频繁使用的多表连接、过滤条件或计算字段组合成一个简单的虚拟表，对用户屏蔽底层复杂性。

3. 安全性：可以作为权限控制的手段。只授予用户对视图的访问权限，而非底层基表，从而隐藏敏感数据（如工资、身份证号等），只暴露必要的信息。

4. 逻辑独立性：允许应用程序在一定程度上独立于数据库的表结构。即使底层基表的结构发生了变化（如增加列、拆分表），只要修改视图定义使其查询结果保持不变，就可以避免对应用程序代码的修改。

作用：

• 简化操作：让用户像操作普通表一样操作复杂的查询结果。

• 数据安全：提供了一种精细的数据访问控制机制。

• 逻辑抽象：为应用程序提供了底层数据结构的抽象层。

数据类型

编程语言中有数据类型，在数据库中也有数据类型的概念，而且好多地方都大差不差，如下：

数值类型:

INT, TINYINT, BIGINT, FLOAT, DOUBLE, DECIMAL

字符串类型:

CHAR, VARCHAR, TEXT, BLOB

日期时间类型:

DATE, TIME, DATETIME, TIMESTAMP, YEAR

枚举与集合:

ENUM, SET

3、SQL的五大种语言

        在MySQL数据库操作中，SQL语句按照功能可以分为五大类型，每种类型负责不同的操作范畴。掌握这些分类对于编写高效、安全的数据库操作至关重要。

这五大类分别是：

DDL (Data Definition Language, 数据定义语言)

DML (Data Manipulation Language, 数据操作语言)

DQL (Data Query Language, 数据查询语言)

DCL (Data Control Language, 数据控制语言)

TCL (Transaction Control Language, 事务控制语言)

接下来我们将从数据库、表、数据、索引、视图这五个方面来挨个看对应的操作：

1、DDL (Data Definition Language, 数据定义语言)

        DDL用于定义和管理数据库中的所有结构和模式，例如数据库、表、索引、视图等。核心是操作“结构”。

        1、对数据库：

        CREATE DATABASE（创建数据库）、ALTER DATABASE（修改数据库）、DROP DATABASE（删除数据库） 注：这里的操作都是对结构的操作

        2、对表：

        CREATE TABLE（创建表）、ALTER TABLE（修改表）、DROP TABLE（删除表）、TRUNCATE TABLE（清空表中所有数据）、RENAME（重命名表）

        3、对数据：无

        4、对索引：

        CREATE INDEX（创建索引）、ALTER INDEX（修改索引）、DROP INDEX（删除索引）

        5、对视图：

        CREATE VIEW（创建视图）、ALTER VIEW（修改视图）、DROP VIEW（删除视图）

        6、对用户/权限操作：

        CREATE USER（创建用户）、ALTER USER（修改用户权限）、DROP USER（删除用户）

2、DML (Data Manipulation Language, 数据操作语言)

        用于对数据库表中的数据进行操作，即“增删改”记录。核心是操作“数据本身”。

        1、对数据库：无

        2、对表：无

        3、对数据：

        INSERT INTO（插入数据）、UPDATE（修改数据）、DELETE FROM（删除数据）

        4、对索引：无

        5、对视图：

        INSERT INTO（插入视图）、UPDATE（）、DELETE FROM

        6、对用户/权限操作：无

3、DQL (Data Query Language, 数据查询语言)

        这是使用最频繁的语句，专门用于从数据库中查询/检索数据。

        1、对数据库：无

        2、对表：无

        3、对数据：

        SELECT（查询数据）

        4、对索引：无

        5、对视图：

        SELECT（查询视图）

        6、对用户/权限操作：无

4、DCL (Data Control Language, 数据控制语言)

        用于控制对数据库的访问权限和安全性。

        1、对数据库：

        GRANT（授权数据库级别权限）、REVOKE（撤销授权数据库级别权限）

        2、对表：

         GRANT（授权表级别权限）、REVOKE（撤销授权表级别权限）

        3、对数据：无

        4、对索引：

        GRANT（授权索引级别权限）、REVOKE（撤销授权索引级别权限）

        5、对视图：

        GRANT（授权视图级别权限）、REVOKE（撤销授权视图级别权限）

        6、对用户/权限操作：

        GRANT（授权表级别权限）、REVOKE（撤销授权表级别权限）

5、TCL (Transaction Control Language, 事务控制语言)

        用于管理数据库中的事务，确保数据的完整性。通常与DML语句结合使用。

        1、对数据库：无

        2、对表：无

        3、对数据：

      COMMIT（提交事务）、ROLLBACK（回滚事务）、SAVEPOINT（设置回滚点）

        4、对索引：无

        5、对视图：无

        6、对用户/权限操作：

        COMMIT（提交事务）、ROLLBACK（回滚事务）

4、总结对比

理解这五种SQL语句类型的区别和用途，有助于编写更加规范、高效的数据库操作代码，也能更好地进行数据库权限管理和事务控制。