MySQL语法大全

一、基础语法规则

1. 关键字不区分大小写（建议大写，提高可读性）

   sql

   SELECT * FROM users;  -- 正确
   select * from users;  -- 也正确
   

2. 语句以分号（;）结尾

   sql

   CREATE TABLE students (id INT);  -- 结尾需加分号
   

3. 注释
   • 单行注释：-- 这是单行注释 或 # 这是单行注释（MySQL 支持）
   • 多行注释：/* 这是多行注释 */

二、数据定义语言（DDL）

用于创建、修改、删除数据库对象（数据库、表、索引等）。

1. 数据库操作

• 创建数据库

  sql

  CREATE DATABASE IF NOT EXISTS mydb DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
  

• 删除数据库

  sql

  DROP DATABASE IF EXISTS mydb;
  

• 切换数据库

  sql

  USE mydb;
  

2. 表操作

• 创建表

  sql

  CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age TINYINT,
    email VARCHAR(100) UNIQUE,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP
  );
  

   
  • 常用数据类型：
    • 数值：INT、BIGINT、FLOAT、DECIMAL（精准计算）
    • 字符串：VARCHAR（可变长）、CHAR（定长）、TEXT（长文本）
    • 日期时间：DATETIME（含时区）、TIMESTAMP（自动更新）
    • 枚举 / 集合：ENUM('男','女')、SET('阅读','运动')

• 修改表结构

  • 添加字段：

    sql

    ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER age;  -- AFTER 指定位置
    

  • 修改字段：

    sql

    ALTER TABLE users MODIFY COLUMN phone VARCHAR(30) NOT NULL;  -- 修改类型/约束
    ALTER TABLE users CHANGE COLUMN phone tel VARCHAR(30);       -- 修改字段名
    

  • 删除字段：

    sql

    ALTER TABLE users DROP COLUMN tel;
    

  • 重命名表：

    sql

    RENAME TABLE users TO user_info;
    

• 删除表

  sql

  DROP TABLE IF EXISTS user_info;
  

3. 索引操作

• 创建索引

  sql

  CREATE INDEX idx_name ON users(name);        -- 普通索引
  CREATE UNIQUE INDEX idx_email ON users(email);  -- 唯一索引
  CREATE FULLTEXT INDEX idx_content ON articles(content);  -- 全文索引（用于文本搜索）
  

• 删除索引

  sql

  DROP INDEX idx_name ON users;
  

三、数据操作语言（DML）

用于操作表中的数据（增、删、改）。

1. 插入数据

• 单条插入

  sql

  INSERT INTO users (name, age, email) VALUES ('Alice', 25, 'alice@example.com');
  INSERT INTO users VALUES (NULL, 'Bob', 30, 'bob@example.com');  -- 按字段顺序插入，NULL 用于自增字段
  

• 批量插入

  sql

  INSERT INTO users (name, age, email) VALUES
  ('Charlie', 35, 'charlie@example.com'),
  ('David', 40, 'david@example.com');
  

2. 更新数据

• 条件更新

  sql

  UPDATE users SET age = age + 1 WHERE name = 'Alice';
  UPDATE users SET email = 'new_alice@example.com' WHERE id = 1;
  

• 全表更新（谨慎使用！）

  sql

  UPDATE users SET age = 0;  -- 无 WHERE 条件会更新所有行
  

3. 删除数据

• 条件删除

  sql

  DELETE FROM users WHERE age < 18;
  

• 清空表（比 DELETE 更快，不可回滚）

  sql

  TRUNCATE TABLE users;
  

四、数据查询语言（DQL）

核心语法，用于从表中检索数据。

1. SELECT 基本语法

sql

SELECT [DISTINCT] 字段1, 字段2, ...  -- DISTINCT 去重
FROM 表名
[WHERE 条件]
[GROUP BY 分组字段 [HAVING 分组后条件]]
[ORDER BY 排序字段 [ASC/DESC]]
[LIMIT 起始行, 行数];  -- 分页，起始行从 0 开始

2. 条件查询（WHERE）

• 比较运算符：=, <, >, <=, >=, <>（不等于）

  sql

  SELECT * FROM users WHERE age > 30 AND email IS NOT NULL;
  

• 范围查询：BETWEEN...AND、IN(值列表)、NOT IN

  sql

  SELECT * FROM orders WHERE price BETWEEN 100 AND 200;
  SELECT * FROM users WHERE name IN ('Alice', 'Bob');
  

• 模糊查询：LIKE（% 匹配任意字符，_ 匹配单个字符）

  sql

  SELECT * FROM users WHERE email LIKE '%@example.com';  -- 以 @example.com 结尾
  SELECT * FROM products WHERE name LIKE '手机_';  -- 名称以“手机”开头，且总长度为3个字
  

• 空值判断：IS NULL、IS NOT NULL

  sql

  SELECT * FROM users WHERE phone IS NULL;
  

3. 分组与聚合（GROUP BY + 聚合函数）

• 常用聚合函数：
  • COUNT(*)：统计行数
  • SUM(字段)：求和
  • AVG(字段)：平均值
  • MAX(字段)/MIN(字段)：最大值 / 最小值
• 示例：

  sql

  -- 按年龄分组，统计每组人数，且只显示人数>1的组
  SELECT age, COUNT(*) AS total
  FROM users
  GROUP BY age
  HAVING total > 1;
  

4. 排序与分页（ORDER BY + LIMIT）

sql

-- 按年龄降序排列，取前5条数据
SELECT * FROM users
ORDER BY age DESC
LIMIT 0, 5;  -- 等价于 LIMIT 5

5. 多表查询

• 内连接（INNER JOIN）：返回两表匹配的行

  sql

  SELECT u.name, o.order_date
  FROM users u
  INNER JOIN orders o ON u.id = o.user_id;
  

• 左连接（LEFT JOIN）：返回左表所有行，右表匹配不到的行用 NULL 填充

  sql

  SELECT u.name, o.order_id
  FROM users u
  LEFT JOIN orders o ON u.id = o.user_id;
  

• 右连接（RIGHT JOIN）：与左连接相反，返回右表所有行

  sql

  SELECT u.name, o.order_id
  FROM users u
  RIGHT JOIN orders o ON u.id = o.user_id;
  

• 子查询：嵌套在查询中的查询

  sql

  -- 查询购买过商品的用户
  SELECT * FROM users
  WHERE id IN (SELECT DISTINCT user_id FROM orders);
  

6. 联合查询（UNION）

合并多个 SELECT 结果（去重），字段数和类型需一致。

sql

SELECT name, age FROM users WHERE age > 30
UNION
SELECT name, age FROM users WHERE email LIKE '%@example.com';

五、数据控制语言（DCL）

用于管理用户权限。

1. 用户管理

• 创建用户（指定主机访问权限，如 'user'@'localhost'）

  sql

  CREATE USER IF NOT EXISTS 'admin'@'localhost' IDENTIFIED BY 'password123';
  

• 修改密码

  sql

  ALTER USER 'admin'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_password';
  

• 删除用户

  sql

  DROP USER IF EXISTS 'admin'@'localhost';
  

2. 权限管理

• 授予权限（权限包括 SELECT, INSERT, UPDATE, DELETE, ALL PRIVILEGES 等）

  sql

  GRANT SELECT, INSERT ON mydb.* TO 'user'@'localhost';  -- 授予对 mydb 库所有表的查询和插入权限
  GRANT ALL PRIVILEGES ON mydb.* TO 'admin'@'localhost';  -- 授予所有权限
  

• 回收权限

  sql

  REVOKE UPDATE ON mydb.users FROM 'user'@'localhost';  -- 回收对 mydb.users 表的更新权限
  

• 查看权限

  sql

  SHOW GRANTS FOR 'user'@'localhost';
  

六、事务控制（Transaction）

用于保证数据的一致性（适用于 Innodb 引擎）。

sql

START TRANSACTION;  -- 开始事务
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- 提交事务（所有操作生效）
-- 或 ROLLBACK;  -- 回滚事务（撤销所有操作）

七、常用函数

1. 字符串函数

• CONCAT(str1, str2)：拼接字符串

  sql

  SELECT CONCAT('Hello, ', name) AS greeting FROM users;
  

• SUBSTRING(str, start, length)：截取子字符串

  sql

  SELECT SUBSTRING(email, 1, INSTR(email, '@')-1) AS username FROM users;  -- 提取邮箱前缀
  

• TRIM()/LTRIM()/RTRIM()：去除首尾 / 左 / 右空格

  sql

  SELECT TRIM('  abc  ') AS result;  -- 输出 'abc'
  

2. 日期函数

• NOW()：获取当前日期时间

  sql

  SELECT NOW();  -- 输出类似 '2023-10-01 12:34:56'
  

• DATE_FORMAT(date, format)：格式化日期

  sql

  SELECT DATE_FORMAT(create_time, '%Y-%m-%d') AS date FROM orders;  -- 格式化为年月日
  

• DATEDIFF(date1, date2)：计算天数差

  sql

  SELECT DATEDIFF(NOW(), order_date) AS days_since_order FROM orders;
  

3. 数值函数

• ROUND(num, decimals)：四舍五入

  sql

  SELECT ROUND(price, 2) AS formatted_price FROM products;
  

• ABS(num)：绝对值

  sql

  SELECT ABS(-10) AS result;  -- 输出 10
  

八、存储过程与函数

用于封装复杂逻辑，提高复用性。

1. 存储过程（无返回值）

sql

DELIMITER $$  -- 修改分隔符（避免与分号冲突）
CREATE PROCEDURE GetUserByAge(IN min_age INT, OUT total INT)
BEGIN
  SELECT * FROM users WHERE age >= min_age;
  SELECT COUNT(*) INTO total FROM users WHERE age >= min_age;
END$$
DELIMITER ;  -- 恢复默认分隔符

-- 调用存储过程
CALL GetUserByAge(30, @total);
SELECT @total;  -- 查看输出参数

2. 自定义函数（有返回值）

sql

DELIMITER $$
CREATE FUNCTION CalculateTax(amount DECIMAL(10,2)) RETURNS DECIMAL(10,2)
BEGIN
  RETURN amount * 0.06;  -- 假设税率6%
END$$
DELIMITER ;

-- 使用函数
SELECT order_id, amount, CalculateTax(amount) AS tax FROM orders;

九、其他常用操作

1. 查看表结构

   sql

   DESC users;  -- 等价于 SHOW COLUMNS FROM users;
   

2. 查看数据库 / 表列表

   sql

   SHOW DATABASES;  -- 查看所有数据库
   SHOW TABLES;     -- 查看当前数据库中的表
   

3. 备份与恢复
   • 命令行备份：

     bash

     mysqldump -u root -p mydb > mydb_backup.sql  -- 备份数据库
     mysql -u root -p mydb < mydb_backup.sql      -- 恢复数据库
     

注意事项

• 字符集问题：建议使用 utf8mb4 字符集（支持 emoji 等特殊字符）。
• 索引优化：避免在低选择性字段（如性别）创建索引，定期分析慢查询（EXPLAIN SELECT ...）。
• 权限最小化原则：为用户分配最小必要权限，避免安全风险。