一、数据库基础操作

1. SHOW DATABASES

• 功能：显示 MySQL 实例中所有已创建的数据库。

• 语法：

  SHOW DATABASES;

• 用途：查看当前 MySQL 实例中的数据库列表，确认某个数据库是否存在。

• 示例：

  SHOW DATABASES;

  输出可能类似于：

  +--------------------+
  | Database           |
  +--------------------+
  | information_schema |
  | mysql              |
  | performance_schema |
  | sys                |
  | my_database        |
  +--------------------+

2. SELECT DATABASE()

• 功能：返回当前使用的数据库名称。

• 语法：

  SELECT DATABASE();

• 用途：确认当前会话正在操作哪个数据库。

• 示例：

  SELECT DATABASE();

  输出可能为：

  +------------+
  | DATABASE() |
  +------------+
  | my_database|
  +------------+

3. USE <数据库名>

• 功能：切换到指定的数据库，设定当前会话的默认数据库。

• 语法：

  USE database_name;

• 用途：在执行后续的 SQL 语句时，无需每次都指定数据库名称。

• 示例：

  USE my_database;

  输出：

  Database changed

---

二、数据定义语言 (DDL)

DDL 主要用于定义和修改数据库结构，包括创建、修改和删除数据库对象（如表、索引等）。

2.1 CREATE：创建数据库或表

• 功能：创建新的数据库或表。

• 语法：

  -- 创建数据库
  CREATE DATABASE database_name;
  
  -- 创建表
  CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
  );

• 示例：

  -- 创建数据库
  CREATE DATABASE my_database;
  
  -- 创建表
  CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  );

2.2 ALTER：修改表结构

• 功能：修改现有表的结构，如添加、修改或删除列。

• 语法：

  ALTER TABLE table_name
    ADD COLUMN column_name datatype;
  
  ALTER TABLE table_name
    MODIFY COLUMN column_name new_datatype;
  
  ALTER TABLE table_name
    DROP COLUMN column_name;

• 示例：

  -- 添加新列
  ALTER TABLE users ADD COLUMN age INT;
  
  -- 修改列数据类型
  ALTER TABLE users MODIFY COLUMN age SMALLINT;
  
  -- 删除列
  ALTER TABLE users DROP COLUMN age;

2.3 DROP：删除数据库或表

• 功能：删除现有的数据库或表。

• 语法：

  DROP DATABASE database_name;
  
  DROP TABLE table_name;

• 示例：

  -- 删除数据库
  DROP DATABASE my_database;
  
  -- 删除表
  DROP TABLE users;

2.4 TRUNCATE：清空表数据

• 功能：快速删除表中所有数据，但保留表结构。

• 语法：

  TRUNCATE TABLE table_name;

• 示例：

  TRUNCATE TABLE users;

  注意：TRUNCATE 操作无法回滚，请谨慎使用。

2.5 更改表名和列名

在 MySQL 中，更新表名和列名的操作同样属于 DDL 的范畴，主要通过 ALTER TABLE 语句来完成。

2.5.1 更改表名

• 语法：

  ALTER TABLE old_table_name RENAME TO new_table_name;

• 示例：

  ALTER TABLE users RENAME TO customers;

  以上语句将 users 表重命名为 customers。

2.5.2 更改列名

• 语法：

  ALTER TABLE table_name
  CHANGE COLUMN old_column_name new_column_name new_data_type;

• 示例：

  ALTER TABLE users 
  CHANGE COLUMN username user_name VARCHAR(50);

  上述语句将 users 表中的 username 列重命名为 user_name，并设置数据类型为 VARCHAR(50)。

  注意：在 CHANGE COLUMN 中不仅要指定新列名，还需再次声明数据类型，否则会导致语法错误。

---

三、数据操作语言 (DML)

DML 用于对表中的数据进行增、删、改操作，包括 INSERT、UPDATE 和 DELETE。

3.1 INSERT：插入数据

• 功能：向表中插入新记录。

• 语法：

  -- 插入单条记录
  INSERT INTO table_name (column1, column2, ...)
  VALUES (value1, value2, ...);
  
  -- 插入多条记录
  INSERT INTO table_name (column1, column2, ...)
  VALUES 
    (value1a, value2a, ...),
    (value1b, value2b, ...),
    ...;

• 示例：

  -- 插入一条记录
  INSERT INTO users (username, email) VALUES ('Alice', 'alice@example.com');
  
  -- 插入多条记录
  INSERT INTO users (username, email) VALUES 
  ('Bob', 'bob@example.com'),
  ('Charlie', 'charlie@example.com');

3.2 UPDATE：更新数据

• 功能：修改表中现有的记录。

• 语法：

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

• 示例：

  -- 更新某一条记录
  UPDATE users SET email = 'alice_new@example.com' WHERE id = 1;
  
  -- 更新多条记录
  UPDATE users SET email = 'updated@example.com' WHERE username LIKE 'B%';

  注意：不使用 WHERE 子句会更新表中所有记录。

3.3 DELETE：删除数据

• 功能：删除表中的记录。

• 语法：

  DELETE FROM table_name WHERE condition;

• 示例：

  -- 删除某一条记录
  DELETE FROM users WHERE id = 2;
  
  -- 删除所有记录
  DELETE FROM users;

  注意：DELETE 操作可以回滚，但性能较 TRUNCATE 略低。

---

四、数据查询语言 (DQL)

DQL 主要用于从数据库中查询数据，最常用的语句是 SELECT。

4.1 SELECT：查询数据

• 功能：从一个或多个表中检索数据。

• 语法：

  SELECT column1, column2, ...
  FROM table_name
  WHERE condition
  ORDER BY column [ASC|DESC]
  GROUP BY column
  HAVING condition
  LIMIT [offset,] row_count;

  或者

  LIMIT row_count OFFSET offset;

• 示例：

  -- 查询所有列
  SELECT * FROM users;
  
  -- 查询指定列
  SELECT username, email FROM users;
  
  -- 查询带条件的记录
  SELECT * FROM users WHERE username = 'Alice';
  
  -- 查询带排序的记录
  SELECT * FROM users ORDER BY created_at DESC;
  
  -- 使用 LIMIT 进行分页查询（方式一）
  SELECT * FROM users LIMIT 10 OFFSET 0;
  
  -- 使用 LIMIT 进行分页查询（方式二）
  SELECT * FROM users LIMIT 0, 10;

4.2 聚合查询

聚合函数用于对一组值执行计算，并返回单一结果。常用的聚合函数包括 COUNT、SUM、AVG、MIN 和 MAX。

• 示例：

  -- 统计总记录数
  SELECT COUNT(*) FROM users;
  
  -- 按照某列进行分组统计
  SELECT username, COUNT(*) AS user_count FROM users GROUP BY username;

4.3 模糊查询的 _ 和 % 用法

在 MySQL 中，LIKE 操作符用于进行模糊查询，可以使用 _ 和 % 来匹配特定模式。

1. %：匹配零个或多个字符。

   • 示例：

     SELECT * FROM users WHERE username LIKE 'A%';

     说明：匹配所有以 “A” 开头的用户名。

2. _：匹配单个字符。

   • 示例：

     SELECT * FROM users WHERE username LIKE '_B';

     说明：匹配第二个字符为 “B” 的用户名，例如 “aB”、“cB”。

---

五、数据控制语言 (DCL)

DCL 主要用于控制数据库用户的权限，确保数据的安全性。常见的 DCL 语句包括 GRANT 和 REVOKE。

5.1 GRANT：授予权限

• 功能：授予用户特定的权限。

• 语法：

  -- 授予所有权限
  GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';
  
  -- 授予特定权限
  GRANT SELECT, INSERT ON database_name.* TO 'username'@'host';

• 示例：

  -- 给用户授予所有权限
  GRANT ALL PRIVILEGES ON my_database.* TO 'user'@'localhost';
  
  -- 给用户授予某些权限
  GRANT SELECT, INSERT ON my_database.* TO 'user'@'localhost';

5.2 REVOKE：撤销权限

• 功能：撤销用户的特定权限。

• 语法：

  REVOKE privilege_type ON database_name.* FROM 'username'@'host';

• 示例：

  -- 撤销某用户的 INSERT 权限
  REVOKE INSERT ON my_database.* FROM 'user'@'localhost';

5.3 用户管理

除权限控制外，管理用户的创建和删除也是 DCL 的一部分。

• 创建用户：

  CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password';

• 删除用户：

  DROP USER 'new_user'@'localhost';

---

六、排序与分组查询

在查询数据时，常常需要对结果进行排序和分组，以便更好地分析和展示数据。

6.1 ORDER BY：排序

• 功能：根据指定的列对查询结果进行排序。

• 语法：

  ORDER BY column_name [ASC|DESC];

  • ASC：升序（默认）。
  • DESC：降序。

• 示例：

  SELECT * FROM users ORDER BY created_at DESC;

  说明：按 created_at 字段降序排列用户记录。

6.2 GROUP BY：分组

• 功能：将查询结果按指定列分组，常与聚合函数搭配使用。

• 语法：

  GROUP BY column_name;

• 示例：

  SELECT username, COUNT(*) AS user_count FROM users GROUP BY username;

  说明：按 username 分组，并统计每个用户名的出现次数。

6.3 HAVING：筛选分组结果

• 功能：对 GROUP BY 分组后的结果进行筛选。

• 语法：

  HAVING condition;

  注意：HAVING 必须与 GROUP BY 搭配使用。

• 示例：

  SELECT username, COUNT(*) AS user_count 
  FROM users 
  GROUP BY username 
  HAVING user_count > 1;

  说明：筛选出用户数大于 1 的用户名组。

6.4 LIMIT：限制结果行数

• 功能：限制查询返回的结果行数，可以用于分页显示。

• 语法：

  MySQL 支持两种 LIMIT 语法格式：

  1. LIMIT row_count OFFSET offset

     LIMIT row_count OFFSET offset;

  2. LIMIT offset, row_count

     LIMIT offset, row_count;

  • offset：偏移量，表示从第几行开始（从 0 开始计数）。
  • row_count：返回的行数。

• 示例：

  1. 使用 LIMIT row_count OFFSET offset 语法：

     SELECT * FROM users LIMIT 10 OFFSET 5;

     说明：从第 6 行开始，返回 10 条记录。

  2. 使用 LIMIT offset, row_count 语法：

     SELECT * FROM users LIMIT 5, 10;

     说明：同样是从第 6 行开始，返回 10 条记录。

  3. 仅限制返回行数：

     SELECT * FROM users LIMIT 10;

     说明：返回前 10 条记录。

注意：

• LIMIT 子句中的 offset 是可选的。如果省略 offset，则默认从第一行开始。
• 两种 LIMIT 语法在功能上等效，可以根据个人或项目的编码规范选择使用哪种格式。

---

七、查询执行顺序及关系

在构建复杂查询时，理解 MySQL 的执行顺序有助于优化查询性能和结果的准确性。以下是 MySQL 查询执行的主要步骤顺序：

1. FROM：从指定的表中获取数据。
2. WHERE：筛选符合条件的记录。
3. GROUP BY：将筛选后的结果按指定列分组。
4. HAVING：对分组结果进行条件筛选。
5. SELECT：选择所需的列。
6. ORDER BY：对最终结果排序。
7. LIMIT：限制返回的行数。

示例解析

SELECT username, COUNT(*) AS user_count 
FROM users 
WHERE age > 20 
GROUP BY username 
HAVING user_count > 1 
ORDER BY user_count DESC 
LIMIT 10;

• FROM users：从 users 表中获取数据。
• WHERE age > 20：筛选年龄大于 20 的记录。
• GROUP BY username：按 username 分组。
• HAVING user_count > 1：筛选分组后用户数大于 1 的组。
• *SELECT username, COUNT() AS user_count**：选择 username 和用户数。
• ORDER BY user_count DESC：按用户数降序排列。
• LIMIT 10：限制返回的结果为前 10 条记录。

---

八、数据类型详解

MySQL 提供了多种数据类型，以适应不同类型的数据存储需求。理解这些数据类型有助于设计高效且合理的数据库结构。

8.1 数值类型

• 整数类型：

  • TINYINT：1 字节，范围 -128 到 127（有符号）。
  • SMALLINT：2 字节，范围 -32768 到 32767（有符号）。
  • MEDIUMINT：3 字节，范围 -8388608 到 8388607（有符号）。
  • INT 或 INTEGER：4 字节，范围 -2147483648 到 2147483647（有符号）。
  • BIGINT：8 字节，范围 -9223372036854775808 到 9223372036854775807（有符号）。

• 浮点和精确小数类型：

  • FLOAT：单精度浮点数。
  • DOUBLE：双精度浮点数。
  • DECIMAL：精确小数，适用于财务计算。

8.2 日期和时间类型

• DATE：日期，格式 YYYY-MM-DD。
• TIME：时间，格式 HH:MM:SS。
• DATETIME：日期和时间，格式 YYYY-MM-DD HH:MM:SS。
• TIMESTAMP：时间戳，自动记录数据的创建或修改时间。

8.3 字符串类型

• CHAR(n)：固定长度字符串，n 表示最大字符数。例如，CHAR(10) 总是占用 10 个字符空间。
• VARCHAR(n)：可变长度字符串，n 表示最大字符数。例如，VARCHAR(255) 根据实际字符数占用空间。
• TEXT：长文本字段，适用于存储大块文本数据。

8.4 其他类型

• ENUM：枚举类型，允许一组预定义的值。
• SET：集合类型，允许多个预定义的值组合。