MySQL从入门到精通：看这篇就够了

MySQL从入门到精通：看这篇就够了

前言

在现代软件开发中，数据库是支撑应用的重要基础，而MySQL作为开源且高效的关系型数据库管理系统，广泛应用于各种企业和互联网应用中。无论是小型网站还是大型分布式系统，MySQL都能提供强大的数据存储与管理支持。

本篇教程将带领你从SQL的基础知识讲起，逐步深入到复杂的查询操作、优化技巧、事务处理等内容，帮助你全面掌握MySQL的使用，成为MySQL的高手。

正文

一、SQL简述

1. SQL的概述

SQL（Structured Query Language）是用于与关系型数据库系统交互的标准语言。SQL既可以用于查询、插入、更新、删除数据，也可用于定义数据库结构、管理用户权限等操作。它通过一套通用的语法标准，使得不同的数据库系统能够互通操作。

2. SQL的优点

• 简洁易学：SQL语法简洁明了，能够高效地完成复杂的数据操作。
• 跨平台性：SQL是标准化的，几乎所有的关系型数据库都支持SQL。
• 高效性：通过合理的查询优化，SQL能够高效地操作和处理海量数据。
• 兼容性：SQL支持多种数据库引擎，具有较好的向后兼容性和向前兼容性。

3. SQL的分类

SQL主要分为以下几种类型：

• 数据定义语言（DDL）：用于定义数据库结构，如CREATE、ALTER、DROP等。
• 数据操作语言（DML）：用于数据的插入、更新和删除，如INSERT、UPDATE、DELETE等。
• 数据查询语言（DQL）：用于从数据库中查询数据，如SELECT语句。
• 数据控制语言（DCL）：用于定义权限控制，如GRANT、REVOKE等。

二、数据库的三大范式

数据库设计的三大范式旨在消除冗余数据，保证数据一致性和完整性。

1. 第一范式（1NF）

第一范式要求数据库中的每个字段都必须是原子的，不能再分割。也就是说，每个字段必须存储单一的数据类型，避免出现重复的数据集合。

2. 第二范式（2NF）

第二范式要求在第一范式的基础上，所有非主键字段必须完全依赖于主键，不能只依赖于主键的一部分。这样可以消除部分依赖。

3. 第三范式（3NF）

第三范式要求所有非主键字段必须直接依赖于主键，不能通过其他非主键字段进行依赖。消除了传递依赖，确保数据的高效管理。

三、数据库的数据类型

1. 整数类型

• TINYINT：存储小范围的整数（-128到127）。
• SMALLINT：存储小整数（-32,768到32,767）。
• INT：标准整数类型（-2,147,483,648到2,147,483,647）。
• BIGINT：用于存储非常大的整数。

2. 浮点数类型和定点数类型

• FLOAT：存储浮动的小数，适合存储近似数值。
• DOUBLE：存储更高精度的浮动小数。
• DECIMAL：定点数类型，通常用于财务数据的存储，避免浮动小数的精度问题。

3. 字符串类型

• CHAR：定长字符类型，适用于长度固定的字符串。
• VARCHAR：变长字符类型，适用于长度可变的字符串。
• TEXT：适用于存储大块的文本数据。

4. 日期与时间类型

• YEAR：表示年份，格式为YYYY。
• TIME：表示时间，格式为HH:MM:SS。
• DATETIME：表示日期和时间，格式为YYYY-MM-DD HH:MM:SS。
• TIMESTAMP：表示时间戳，通常用于记录事件发生的具体时间。

5. 二进制类型

• BLOB：用于存储二进制数据，如图片、音频等。

四、数据库、数据表的基本操作

1. 数据库的基本操作

• 创建数据库：

CREATE DATABASE mydb;

• 查看数据库：

SHOW DATABASES;

• 选择数据库：

USE mydb;

• 删除数据库：

DROP DATABASE mydb;

2. 数据表的基本操作

2.1 创建数据表

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    grade CHAR(1)
);

2.2 查看数据表

SHOW TABLES;

2.3 修改数据表

ALTER TABLE students ADD COLUMN gender CHAR(1);

2.4 删除数据表

DROP TABLE students;

五、数据表的约束

1. 主键约束

主键约束确保每个表中的记录唯一且非空。一个表只能有一个主键。

2. 非空约束

非空约束确保列的每个值都不能为NULL。

3. 默认值约束

通过DEFAULT关键字为某些列设置默认值。

4. 唯一性约束

通过UNIQUE约束确保列中的每个值都是唯一的。

5. 外键约束

外键约束确保两个表之间的关系一致性，即在主表中存在的值必须在外表中有对应的记录。

6.1 数据一致性概念

数据一致性指的是数据库中各数据表之间的关联数据在插入、更新、删除时的保持一致性。外键约束能确保这一点。

6.2 删除外键

删除外键约束时，需使用ALTER TABLE语句删除外键关联：

ALTER TABLE child_table DROP FOREIGN KEY fk_name;

6.3 关于外键约束需要注意的细节

• 外键字段的类型必须与主键字段的类型相同。
• 外键约束通常会在插入、更新数据时进行检查，可能导致性能开销。

六、数据表插入数据

1. 为表中所有字段插入数据

INSERT INTO students VALUES (1, 'Alice', 20, 'A');

2. 为表中指定字段插入数据

INSERT INTO students (student_id, name) VALUES (2, 'Bob');

3. 同时插入多条记录

INSERT INTO students (student_id, name, age, grade) VALUES
    (3, 'Charlie', 22, 'B'),
    (4, 'David', 23, 'C');

七、更新数据

1. UPDATE基本语法

UPDATE students SET grade = 'A' WHERE student_id = 1;

2. UPDATE更新部分数据

UPDATE students SET age = 21 WHERE name = 'Alice';

3. UPDATE更新全部数据

UPDATE students SET grade = 'B';

八、删除数据

1. DELETE基本语法

DELETE FROM students WHERE student_id = 1;

2. DELETE删除部分数据

DELETE FROM students WHERE grade = 'C';

3. DELETE删除全部数据

DELETE FROM students;

4. TRUNCATE和DELETE的区别

• DELETE：逐条删除，支持条件，可以回滚。
• TRUNCATE：快速删除所有记录，不能回滚。

九、MySQL数据表简单查询

1. 简单查询概述

查询数据是MySQL操作中最常见的操作。可以通过SELECT语句选择需要的数据。

2. 查询所有字段

SELECT * FROM students;

3. 查询指定字段

SELECT student_id, name FROM students;

4. 常数的查询

SELECT 100 AS constant_value;

5. 从查询结果中过滤重复数据

SELECT DISTINCT grade FROM students;

6. 算术运算符

SELECT student_id, age + 1 AS new_age FROM students;

十、函数

1. 聚合函数

聚合函数对一组数据进行计算，常见的聚合函数有：

• COUNT()：计算记录数。
• MAX()：返回最大值。
• MIN()：返回最小值。
• SUM()：计算总和。
• AVG()：计算平均值。

2. 其他常用函数

2.1 时间函数

SELECT NOW();  -- 当前时间
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);  -- 日期加一天

2.2 字符串函数

SELECT CONCAT('Hello', ' ', 'World');  -- 字符串拼接
SELECT LENGTH('MySQL');  -- 字符串长度

2.3 数学函数

SELECT ROUND(123.456, 2);  -- 四舍五入

十一、条件查询

1. 使用关系运算符查询

SELECT * FROM students WHERE age > 20;

2. 使用IN关键字查询

SELECT * FROM students WHERE grade IN ('A', 'B');

3. 使用BETWEEN AND关键字查询

SELECT * FROM students WHERE age BETWEEN 20 AND 22;

4. 使用空值查询

SELECT * FROM students WHERE grade IS NULL;

5. 使用AND关键字查询

SELECT * FROM students WHERE age > 20 AND grade = 'A';

6. 使用OR关键字查询

SELECT * FROM students WHERE age > 20 OR grade = 'A';

7. 使用LIKE关键字查询

SELECT * FROM students WHERE name LIKE 'A%';

十二、别名设置

1. 为表取别名

SELECT * FROM students AS s;

2. 为字段取别名

SELECT student_id AS id, name AS student_name FROM students;

十三、表的关联关系

1. 关联查询

使用JOIN进行多表查询。根据不同的关联关系，可以使用内连接、外连接等。

2. 关联关系的删除数据

删除多表关联数据时，必须考虑外键约束与级联删除。

十四、多表连接查询

1. 交叉连接查询

SELECT * FROM table1 CROSS JOIN table2;

2. 内连接查询

SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;

3. 外连接查询

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id;

十五、子查询

1. 带比较运算符的子查询

SELECT * FROM students WHERE age > (SELECT AVG(age) FROM students);

2. 带EXISTS关键字的子查询

SELECT * FROM students WHERE EXISTS (SELECT 1 FROM students WHERE grade = 'A');

总结

通过本教程的学习，你已经掌握了MySQL的基础知识和常见操作，包括数据库管理、数据表设计、查询、函数等内容。希望你能继续深入学习，提升自己的数据库设计和优化能力，成为一名MySQL的专家。