MySQL: SQL基础语法入门与分类详解

-- 这条 SQL 在 MySQL、PostgreSQL、Oracle、SQL Server 中都能运行
SELECT 1 + 1 AS result;

-- 创建数据库
CREATE DATABASE mydb;

-- 创建表
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 修改表结构
ALTER TABLE users ADD COLUMN email VARCHAR(100);

-- 删除表
DROP TABLE users;

-- 清空表数据（保留结构）
TRUNCATE TABLE users;

-- 插入数据
INSERT INTO users (id, name) VALUES (1, 'Alice');

-- 更新数据
UPDATE users SET name = 'Bob' WHERE id = 1;

-- 删除数据
DELETE FROM users WHERE id = 1;

-- 查询所有字段
SELECT * FROM users;

-- 条件查询
SELECT name, email FROM users WHERE id > 10;

-- 排序查询
SELECT * FROM users ORDER BY created_at DESC;

-- 限制结果
SELECT * FROM users LIMIT 10;

-- 授予权限
GRANT SELECT, INSERT ON mydb.* TO 'user'@'localhost';

-- 撤销权限
REVOKE INSERT ON mydb.* FROM 'user'@'localhost';

-- 开始事务
START TRANSACTION;

-- 执行操作
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 提交事务
COMMIT;

-- 或回滚事务
ROLLBACK;

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

-- 查询所有字段
SELECT * FROM employees;

-- 查询指定字段
SELECT first_name, last_name, salary FROM employees;

-- 使用别名
SELECT first_name AS 名, last_name AS 姓, salary AS 薪资 FROM employees;

-- 去重查询
SELECT DISTINCT department FROM employees;

+------+--------+---------+
| 名   | 姓     | 薪资    |
+------+--------+---------+
| John | Smith  | 5000.00 |
| Jane | Doe    | 6000.00 |
+------+--------+---------+

-- 等值查询
SELECT * FROM employees WHERE department = 'Engineering';

-- 范围查询
SELECT * FROM employees WHERE salary BETWEEN 4000 AND 8000;

-- 列表查询
SELECT * FROM employees WHERE department IN ('Sales', 'Marketing');

-- 模糊查询
SELECT * FROM employees WHERE first_name LIKE 'J%';

-- 空值查询
SELECT * FROM employees WHERE manager_id IS NULL;

+-------------+-----------+--------+-------------+
| first_name  | last_name | salary | department  |
+-------------+-----------+--------+-------------+
| John        | Smith     | 5000.00| Engineering |
| Jane        | Doe       | 6000.00| Engineering |
+-------------+-----------+--------+-------------+

-- 这是单行注释（标准写法）
SELECT * FROM users; -- 查询所有用户

# 这也是单行注释（MySQL 特有）
SELECT * FROM users;

/*
这是多行注释
可以跨越多行
常用于说明复杂查询
*/
SELECT * FROM users
WHERE status = 'active';

/*
功能：查询活跃用户的统计信息
作者：Alice
日期：2026-07-03
修改记录：
  - 2026-07-02 添加 email_verified 条件
  - 2026-07-03 添加注册时间筛选
*/
SELECT 
    COUNT(*) AS total_users,           -- 用户总数
    AVG(age) AS avg_age,               -- 平均年龄
    MIN(created_at) AS earliest_reg    -- 最早注册时间
FROM users
WHERE status = 'active'                -- 只统计活跃用户
  AND email_verified = TRUE            -- 邮箱已验证
  AND created_at >= '2025-01-01';      -- 2025年后注册

-- 错误：order 是关键字
CREATE TABLE order (id INT);

-- 正确：使用反引号包裹
CREATE TABLE `order` (id INT);

-- 更好：避免使用关键字作为表名
CREATE TABLE orders (id INT);

-- 好的命名
CREATE TABLE user_orders (
    id INT PRIMARY KEY,
    user_id INT,
    order_number VARCHAR(20),
    total_amount DECIMAL(10,2),
    created_at TIMESTAMP
);

-- 不好的命名
CREATE TABLE t1 (
    c1 INT,
    c2 VARCHAR(20),
    c3 DECIMAL(10,2)
);

-- 推荐格式：关键字大写，每行一个字段
SELECT 
    first_name,
    last_name,
    salary
FROM employees
WHERE department = 'Engineering'
ORDER BY salary DESC
LIMIT 10;

-- 分号是语句结束符
SELECT * FROM users;  -- 语句1
SELECT * FROM orders; -- 语句2

-- 多条语句执行时必须有分号