MySQL 基础语法详解

在信息爆炸的时代，数据成为了宝贵的资源。如何有效地管理和利用数据，成为了每个企业和个人都需要面对的挑战。MySQL作为一款开源的关系型数据库管理系统，凭借其强大的功能和便捷的操作，成为了数据管理领域的主流选择。而掌握MySQL的数据操作语言，则是打开高效数据管理大门的钥匙。

一、MySQL 数据库基础

在正式学习数据操作语言之前，我们先来了解一下MySQL数据库的基本概念。

数据库 (Database):
- 类似于一个仓库，用于存储和管理数据的集合。
- 例如，一个电商网站的数据库可能包含用户信息、商品信息、订单信息等。
表 (Table):
- 数据库中的数据以表格的形式组织，每个表格都有自己的名称和结构。
- 类似于仓库中的货架，每个货架存放特定类型的数据。
- 例如，"用户信息表" 可能包含用户的姓名、邮箱、密码等信息。
列 (Column):
- 表格中的每一列代表一种数据类型，每个列都有自己的名称和数据类型。
- 类似于货架上的每个格子，每个格子存放特定类型的数据。
- 例如，"用户信息表" 中的 "姓名" 列存放用户的姓名信息，数据类型为文本类型。
行 (Row):
- 表格中的每一行代表一条记录，每条记录包含多个字段的信息。
- 类似于货架上的每一格商品，每个商品都有名称、价格、库存等信息。
- 例如，"用户信息表" 中的一行记录可能包含某个用户的姓名、邮箱、密码等信息。

二、MySQL数据操作语言分类

MySQL提供了强大的数据操作语言，主要分为四类：

数据定义语言（DDL - Data Definition Language）： 用于定义数据库和表结构，例如创建、修改、删除数据库和表等操作。
数据操作语言（DML - Data Manipulation Language）： 用于管理数据库中的数据，例如插入、更新、删除数据等操作。
数据查询语言（DQL - Data Query Language）： 用于查询数据库中的数据，例如检索、排序、筛选数据等操作。
数据控制语言（DCL - Data Control Language）： 用于管理数据库用户的权限，例如授权、撤销权限等操作。

三、数据定义语言 (DDL) 详解

DDL 用于定义数据库和表结构，是数据库操作的基础。

1. CREATE: 创建数据库或数据表

创建数据库:

sql">CREATE DATABASE 数据库名;

例如，创建名为 school 的数据库：

sql">CREATE DATABASE school;

创建数据表:

sql">CREATE TABLE 表名 (列名1 数据类型 约束,列名2 数据类型 约束,...
);

例如，在 school 数据库中创建名为 students 的学生信息表：

sql">USE school;  -- 选择数据库
CREATE TABLE students (id INT PRIMARY KEY AUTO_INCREMENT,  -- 学生ID，主键，自增name VARCHAR(255) NOT NULL,        -- 学生姓名，非空约束age INT,                           -- 学生年龄gender ENUM('男', '女') DEFAULT '男' -- 学生性别，枚举类型，默认值为'男'
);

2. ALTER: 修改数据库或数据表

修改表名:

sql">ALTER TABLE 旧表名 RENAME TO 新表名;

例如，将 students 表名修改为 student_info:

sql">ALTER TABLE students RENAME TO student_info;

添加列:

sql">ALTER TABLE 表名 ADD 列名 数据类型 约束;

例如，向 student_info 表中添加 email 列:

sql">ALTER TABLE student_info ADD email VARCHAR(255);

修改列:

sql">ALTER TABLE 表名 MODIFY COLUMN 列名 新数据类型 约束;

例如，将 student_info 表中 email 列的数据类型修改为 TEXT:

sql">ALTER TABLE student_info MODIFY COLUMN email TEXT;

删除列:

sql">ALTER TABLE 表名 DROP COLUMN 列名;

例如，删除 student_info 表中的 email 列:

sql">ALTER TABLE student_info DROP COLUMN email;

3. DROP: 删除数据库或数据表

删除数据库:
```
sql">DROP DATABASE 数据库名;
```
例如，删除数据库 school:
```
sql">DROP DATABASE school;
```
注意： 删除数据库会删除数据库中的所有数据表！
删除数据表:
```
sql">DROP TABLE 表名;
```
例如，删除数据表 student_info:
```
sql">DROP TABLE student_info;
```

4. TRUNCATE: 清空数据表数据

清空数据表:
```
sql">TRUNCATE TABLE 表名;
```
例如，清空数据表 student_info:
```
sql">TRUNCATE TABLE student_info;
```
注意： TRUNCATE TABLE 语句会删除表中的所有数据，并且无法回滚！

四、数据操作语言 (DML) 详解

DML 用于管理数据库中的数据，是数据库操作的核心。

1. INSERT: 插入数据

语法:

sql">INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...);

示例:

向 students 表中插入一条学生信息:

sql">INSERT INTO students (name, age, gender) VALUES ('张三', 18, '男');

向 students 表中插入多条学生信息:

sql">INSERT INTO students (name, age, gender) VALUES ('李四', 19, '女'),('王五', 20, '男');

2. UPDATE: 更新数据

语法:

sql">UPDATE 表名 SET 列1 = 值1, 列2 = 值2, ... WHERE 条件;

示例:

将 students 表中姓名为张三的学生年龄更新为 20 岁:
```
sql">UPDATE students SET age = 20 WHERE name = '张三';
```
将 students 表中所有学生的年龄增加 1 岁:
```
sql">UPDATE students SET age = age + 1;
```

3. DELETE: 删除数据

语法:
```
sql">DELETE FROM 表名 WHERE 条件;
```
示例:

删除 students 表中姓名为张三的学生信息:
```
sql">DELETE FROM students WHERE name = '张三';
```
删除 students 表中所有学生信息:
```
sql">DELETE FROM students;
```
注意： DELETE 语句可以使用 WHERE 子句指定删除条件，如果不使用 WHERE 子句，则会删除表中的所有数据。

五、数据查询语言 (DQL) 详解

DQL用于查询数据库中的数据，是最常用、也是最灵活的SQL语句。

1. 基本查询

语法:

sql">SELECT 列1, 列2, ... FROM 表名 WHERE 条件;

示例:
- 查询students表中所有学生信息:
```
sql">SELECT * FROM students;
```
- 查询students表中所有学生的姓名和年龄:
```
sql">SELECT name, age FROM students;
```

2. 条件查询

条件查询可以使用 WHERE 子句指定查询条件，并支持多种运算符和函数。

比较运算符:

运算符	描述
=	等于
>	大于
<	小于
>=	大于等于
<=	小于等于
!=	不等于

逻辑运算符:

运算符	描述
AND	并且
OR	或者
NOT	非

模糊查询:

运算符	描述
LIKE	匹配模式
NOT LIKE	不匹配模式

%: 匹配任意长度的字符序列，包括空字符串。
_: 匹配任意单个字符。
范围查询:

运算符	描述
BETWEEN ... AND ...	在某个范围内
IN (..., ..., ...)	在指定的集合中
NOT IN (..., ..., ...)	不在指定的集合中

空值判断:

运算符	描述
IS NULL	为空
IS NOT NULL	非空

示例:

查询年龄大于等于 18 岁的学生信息:
```
sql">SELECT * FROM students WHERE age >= 18;
```

查询姓名为张三并且年龄为 20 岁的学生信息:

sql">SELECT * FROM students WHERE name = '张三' AND age = 20;

查询姓名以张开头的学生信息:

sql">SELECT * FROM students WHERE name LIKE '张%';

查询年龄在 18 到 22 岁之间的学生信息:

sql">SELECT * FROM students WHERE age BETWEEN 18 AND 22;

查询年龄为 18 岁或 20 岁的学生信息:

sql">SELECT * FROM students WHERE age IN (18, 20);

3. 排序查询

排序查询可以使用 ORDER BY 子句对查询结果进行排序。

语法:

sql">SELECT 列1, 列2, ... FROM 表名 WHERE 条件 ORDER BY 列名 [ASC|DESC];

ASC: 升序排序 (默认)
DESC: 降序排序
示例:
- 查询所有学生信息，按照年龄升序排序:
```
sql">SELECT * FROM students ORDER BY age;
```
- 查询所有学生信息，按照年龄降序排序:
```
sql">SELECT * FROM students ORDER BY age DESC;
```
- 查询所有学生信息，先按照年龄降序排序，再按照姓名升序排序:
```
sql">SELECT * FROM students ORDER BY age DESC, name ASC;
```

4. 连接查询

连接查询用于从多个表中查询数据，根据连接方式的不同，可以分为内连接、左连接、右连接等。

假设有两张表:

students: 学生信息表，包含 id, name, class_id 字段。
classes: 班级信息表，包含 id, name 字段。

内连接 (INNER JOIN):

返回两个表中满足连接条件的所有记录.

语法:

sql">SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

示例:

查询所有学生的姓名和班级名称:

sql">SELECT s.name AS student_name, c.name AS class_name
FROM students AS s
INNER JOIN classes AS c ON s.class_id = c.id;

左连接 (LEFT JOIN):

返回左表中所有记录，以及右表中满足连接条件的记录.

语法:

sql">SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

示例:

查询所有学生的姓名和班级名称，即使该学生没有分配班级:

sql">SELECT s.name AS student_name, c.name AS class_name
FROM students AS s
LEFT JOIN classes AS c ON s.class_id = c.id;

右连接 (RIGHT JOIN):

返回右表中所有记录，以及左表中满足连接条件的记录.

语法:

sql">SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

示例:

查询所有班级的名称和学生姓名，即使该班级没有学生:

sql">SELECT s.name AS student_name, c.name AS class_name
FROM students AS s
RIGHT JOIN classes AS c ON s.class_id = c.id;

5. 子查询

子查询是指将一个查询语句嵌套在另一个查询语句中，内部的查询语句称为子查询。

语法:

sql">SELECT column_name(s)
FROM table_name
WHERE column_name operator (SELECT column_nameFROM table_nameWHERE condition
);

示例:

查询年龄大于平均年龄的学生信息:

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

6. 聚合函数

聚合函数用于对一组值进行计算并返回单个值，常用于统计分析.

常用的聚合函数:

函数	描述
*COUNT()**	统计记录数
SUM(列名)	求和
AVG(列名)	求平均值
MAX(列名)	求最大值
MIN(列名)	求最小值

示例:

查询学生总数:
```
sql">SELECT COUNT(*) FROM students;
```
查询所有学生的平均年龄:
```
sql">SELECT AVG(age) FROM students;
```

查询年龄最大的学生的姓名和年龄:

sql">SELECT name, age FROM students ORDER BY age DESC LIMIT 1;

7. 分组查询

分组查询可以使用 GROUP BY 子句对查询结果进行分组，并可以使用聚合函数对每个分组进行统计计算。

语法:

sql">SELECT 列1, 列2, ...
FROM 表名
WHERE 条件
GROUP BY 列名
[HAVING 条件];

HAVING 子句用于对分组后的结果进行筛选。

示例:

统计每个班级的学生人数:

sql">SELECT c.name AS class_name, COUNT(*) AS student_count
FROM students AS s
INNER JOIN classes AS c ON s.class_id = c.id
GROUP BY c.name;

统计学生人数大于 2 的班级:

sql">SELECT c.name AS class_name, COUNT(*) AS student_count
FROM students AS s
INNER JOIN classes AS c ON s.class_id = c.id
GROUP BY c.name
HAVING COUNT(*) > 2;

六、数据控制语言 (DCL) 详解

DCL 用于管理数据库用户的权限，常用的语句有:

GRANT: 授予用户访问权限
REVOKE: 取消用户的访问权限

1. GRANT 授予权限

语法:

sql">GRANT 权限列表 ON 数据库对象 TO 用户名@主机名 [IDENTIFIED BY '密码'] [WITH GRANT OPTION];

权限列表: 可以是多个权限，用逗号分隔，例如 SELECT, INSERT, UPDATE, DELETE
数据库对象: 可以是数据库名、表名或者具体的列名，使用 * 表示所有对象。
用户名@主机名: 指定用户名和允许访问的主机名，使用 % 表示任意主机。
IDENTIFIED BY '密码': 设置用户密码。
WITH GRANT OPTION: 允许用户将权限授予其他用户。

示例:

授予用户 testuser 对数据库 testdb 的所有权限:

sql">GRANT ALL PRIVILEGES ON testdb.* TO 'testuser'@'localhost' IDENTIFIED BY 'password';

授予用户 testuser 对数据库 testdb 的 students 表的查询和插入权限:
```
sql">GRANT SELECT, INSERT ON testdb.students TO 'testuser'@'localhost';
```

2. REVOKE 取消权限

语法:

sql">REVOKE 权限列表 ON 数据库对象 FROM 用户名@主机名;

示例:
- 收回用户 testuser 对数据库 testdb 的所有权限:
```
sql">REVOKE ALL PRIVILEGES ON testdb.* FROM 'testuser'@'localhost';
```
- 收回用户 testuser 对数据库 testdb 的 students 表的插入权限:
```
sql">REVOKE INSERT ON testdb.students FROM 'testuser'@'localhost';
```