MySQL常用的sql语句

一、数据查询语句（SELECT）

功能：从数据库中检索数据，是 SQL 中使用最频繁的操作。

1. 基础查询

   -- 查询单个表的所有列和行
   SELECT * FROM table_name;
   
   -- 查询指定列
   SELECT column1, column2, column3 FROM table_name;
   
   -- 去重查询
   SELECT DISTINCT column1 FROM table_name;
   

    

   应用场景：快速获取表中数据，去重用于统计唯一值（如用户 ID、商品类别等）。

2. 条件查询（WHERE）

   -- 等于
   SELECT * FROM users WHERE age = 18;
   
   -- 不等于
   SELECT * FROM users WHERE age <> 30;
   
   -- 大于/小于
   SELECT * FROM orders WHERE amount > 1000;
   
   -- 范围查询（BETWEEN）
   SELECT * FROM students WHERE score BETWEEN 80 AND 90;
   
   -- 包含查询（IN）
   SELECT * FROM products WHERE category IN ('电子产品', '家居用品');
   
   -- 模糊查询（LIKE）
   SELECT * FROM employees WHERE name LIKE '张%'; -- 以"张"开头
   SELECT * FROM employees WHERE name LIKE '%三'; -- 以"三"结尾
   SELECT * FROM employees WHERE name LIKE '%华%'; -- 包含"华"
   

    

   应用场景：根据条件筛选数据，如订单系统中查询金额大于 5000 的订单，或用户系统中查询姓名包含特定字的用户。

3. 排序与限制（ORDER BY/LIMIT）

   -- 升序排序（默认ASC）
   SELECT * FROM users ORDER BY age ASC;
   
   -- 降序排序（DESC）
   SELECT * FROM orders ORDER BY amount DESC;
   
   -- 多字段排序
   SELECT * FROM employees ORDER BY department, salary DESC;
   
   -- 限制结果数量（MySQL）
   SELECT * FROM products LIMIT 10; -- 前10条
   SELECT * FROM products LIMIT 5, 10; -- 从第6条开始取10条（偏移量+数量）
   
   -- 分页查询（标准SQL）
   SELECT * FROM users OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY;
   

    

   应用场景：数据分页展示（如网站列表页）、按金额降序查看 top 订单、按时间排序查看最新记录等。

4. 聚合函数与分组（GROUP BY/HAVING）

   -- 聚合函数（COUNT/SUM/AVG/MAX/MIN）
   SELECT COUNT(*) FROM users; -- 统计总行数
   SELECT SUM(amount) FROM orders; -- 订单总金额
   SELECT AVG(score) FROM students; -- 平均分数
   SELECT MAX(salary) FROM employees; -- 最高工资
   
   -- 分组查询
   SELECT category, COUNT(*) FROM products GROUP BY category;
   
   -- 分组后过滤（HAVING）
   SELECT department, AVG(salary) 
   FROM employees 
   GROUP BY department 
   HAVING AVG(salary) > 10000;
   

    

   应用场景：统计各部门人数、计算各类商品的销量总和、筛选平均薪资超过 1 万的部门等。

5. 多表查询（JOIN）

   -- 内连接（INNER JOIN）：返回两表匹配的记录
   SELECT e.name, d.dept_name
   FROM employees e
   INNER JOIN departments d ON e.dept_id = d.dept_id;
   
   -- 左连接（LEFT JOIN）：返回左表所有记录和右表匹配的记录
   SELECT e.name, o.order_id
   FROM employees e
   LEFT JOIN orders o ON e.emp_id = o.emp_id;
   
   -- 右连接（RIGHT JOIN）：返回右表所有记录和左表匹配的记录
   SELECT o.order_id, c.customer_name
   FROM orders o
   RIGHT JOIN customers c ON o.cust_id = c.cust_id;
   
   -- 全连接（FULL JOIN，部分数据库支持）
   SELECT * FROM table1 FULL JOIN table2 ON table1.id = table2.id;
   
   -- 交叉连接（CROSS JOIN）：笛卡尔积，慎用
   SELECT * FROM table1 CROSS JOIN table2;
   

    

   应用场景：关联查询用户及其订单、部门及其员工、商品及其分类等场景，如电商系统中查询订单对应的用户信息。

二、数据操作语句（DML）

功能：对数据库中的数据进行增删改操作。

1. 插入数据（INSERT）

   -- 插入完整记录
   INSERT INTO users (name, age, email) VALUES ('张三', 25, 'zhangsan@example.com');
   
   -- 批量插入
   INSERT INTO users (name, age, email) 
   VALUES 
   ('李四', 30, 'lisi@example.com'),
   ('王五', 22, 'wangwu@example.com');
   
   -- 从查询结果插入
   INSERT INTO new_users (name, age)
   SELECT name, age FROM old_users WHERE age > 18;
   

    

   应用场景：新增用户、订单、商品等数据，或通过查询其他表数据批量导入。

2. 更新数据（UPDATE）

   -- 更新单个字段
   UPDATE users SET age = 26 WHERE name = '张三';
   
   -- 更新多个字段
   UPDATE users SET age = 26, email = 'zhangsan_new@example.com' WHERE id = 1;
   
   -- 结合子查询更新
   UPDATE orders SET status = '已完成'
   WHERE order_id IN (SELECT order_id FROM completed_orders);
   

    

   应用场景：修改用户信息、更新订单状态、根据条件批量更新数据（如价格调整）。

3. 删除数据（DELETE）

   -- 删除单条记录
   DELETE FROM users WHERE name = '张三';
   
   -- 删除多条记录
   DELETE FROM orders WHERE create_time < '2023-01-01';
   
   -- 清空表（TRUNCATE，速度更快，不记录日志）
   TRUNCATE TABLE temporary_data;
   

    

   应用场景：删除无效用户、清理过期订单、清空临时表数据等。

三、数据定义语句（DDL）

功能：定义数据库结构，如创建、修改、删除表、索引、视图等。

1. 创建表（CREATE TABLE）

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

    

   字段说明：

   • PRIMARY KEY：主键（唯一且非空）
   • AUTO_INCREMENT：自增主键
   • NOT NULL：非空约束
   • UNIQUE：唯一约束
   • DEFAULT：默认值
   • VARCHAR(50)：可变长度字符串，最大 50 字符
   • INT：整数类型
   • DATETIME：日期时间类型

2. 修改表结构（ALTER TABLE）

   -- 添加字段
   ALTER TABLE users ADD COLUMN phone VARCHAR(20);
   
   -- 修改字段类型
   ALTER TABLE users MODIFY COLUMN age SMALLINT;
   
   -- 重命名字段
   ALTER TABLE users CHANGE COLUMN phone telephone VARCHAR(20);
   
   -- 删除字段
   ALTER TABLE users DROP COLUMN telephone;
   
   -- 添加约束
   ALTER TABLE orders ADD FOREIGN KEY (user_id) REFERENCES users(id);
   

    

   应用场景：表结构迭代（如新增字段存储用户手机号）、修改字段约束（如添加外键关联）。

3. 创建索引（CREATE INDEX）

   -- 普通索引
   CREATE INDEX idx_name ON users (name);
   
   -- 唯一索引
   CREATE UNIQUE INDEX idx_email ON users (email);
   
   -- 组合索引
   CREATE INDEX idx_age_status ON users (age, status);
   
   -- 删除索引
   DROP INDEX idx_name ON users;
   

    

   应用场景：提高查询效率，如按姓名查询用户时，对name字段创建索引。

4. 创建视图（CREATE VIEW）

   -- 创建视图（虚拟表，不存储实际数据）
   CREATE VIEW v_employee_salary AS
   SELECT e.name, d.dept_name, e.salary
   FROM employees e
   JOIN departments d ON e.dept_id = d.dept_id;
   
   -- 查询视图
   SELECT * FROM v_employee_salary WHERE salary > 15000;
   
   -- 修改视图
   ALTER VIEW v_employee_salary AS
   SELECT e.name, d.dept_name, e.salary, e.hire_date
   FROM employees e
   JOIN departments d ON e.dept_id = d.dept_id;
   
   -- 删除视图
   DROP VIEW v_employee_salary;
   

    

   应用场景：简化复杂查询（如多表关联）、保护原始表数据（通过视图限制访问字段）。

5. 创建数据库（CREATE DATABASE）

   -- 创建数据库
   CREATE DATABASE IF NOT EXISTS mydb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
   
   -- 删除数据库
   DROP DATABASE IF EXISTS mydb;
   
   -- 切换数据库
   USE mydb;
   

    

   应用场景：初始化项目数据库、按业务拆分数据库（如用户库、订单库）。

四、数据控制语句（DCL）

功能：控制数据库的访问权限和安全级别。

1. 用户管理

   -- 创建用户（MySQL）
   CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password123';
   
   -- 授权用户（赋予指定数据库的查询和插入权限）
   GRANT SELECT, INSERT ON mydb.* TO 'user1'@'localhost';
   
   -- 赋予所有权限（谨慎使用）
   GRANT ALL PRIVILEGES ON mydb.* TO 'user1'@'localhost';
   
   -- 刷新权限
   FLUSH PRIVILEGES;
   
   -- 删除用户
   DROP USER 'user1'@'localhost';
   

    

   应用场景：为不同角色分配权限（如只读用户、管理员用户），保障数据安全。

2. 事务控制

   -- 开始事务
   START TRANSACTION;
   
   -- 或
   BEGIN;
   
   -- 执行多个操作
   UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
   UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
   
   -- 提交事务（永久保存）
   COMMIT;
   
   -- 回滚事务（撤销操作）
   ROLLBACK;
   
   -- 设置保存点（可回滚到指定点）
   SAVEPOINT sp1;
   -- 执行操作
   ROLLBACK TO sp1;
   

    

   应用场景：保证数据一致性（如转账操作），确保多个操作要么全部成功，要么全部失败。

五、高级 SQL 技巧

1. 子查询（Subquery）

   -- 嵌套在查询中的查询
   SELECT name, age FROM users
   WHERE age > (SELECT AVG(age) FROM users);
   
   --  EXISTS子查询（检查是否存在）
   SELECT * FROM departments
   WHERE EXISTS (SELECT 1 FROM employees WHERE dept_id = departments.dept_id);
   

    

   应用场景：复杂条件筛选（如查询高于平均年龄的用户）、关联子查询（如判断部门是否有员工）。

2. CTE（公共表表达式，WITH 子句）

   -- MySQL 8.0+支持
   WITH sales_by_month AS (
       SELECT MONTH(create_time) AS month, SUM(amount) AS total_sales
       FROM orders
       GROUP BY MONTH(create_time)
   )
   SELECT * FROM sales_by_month WHERE total_sales > 100000;
   

    

   应用场景：简化复杂查询，将中间结果临时存储为 CTE，便于复用和维护。

3. 窗口函数（Window Function）

   -- 排序窗口函数
   SELECT name, salary, 
          ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank,
          DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
   FROM employees;
   
   -- 聚合窗口函数
   SELECT name, dept_id, salary,
          AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg_salary
   FROM employees;
   

    

   应用场景：排名计算（如查询各部门薪资排名）、分组聚合（如计算每个员工薪资与部门平均薪资的对比）。

六、常用 SQL 优化建议

1. 索引优化

   • 为高频查询字段创建索引（如 WHERE、JOIN、ORDER BY 条件字段）。
   • 组合索引遵循 “最左前缀” 原则（如索引(a,b,c)可用于WHERE a=1 AND b=2或WHERE a=1）。
   • 避免在索引字段上使用函数（如WHERE YEAR(create_time)=2023会导致索引失效）。

2. 查询优化

   • 避免使用SELECT *，只查询需要的字段。
   • 用EXISTS替代IN（当子查询结果集较大时更高效）。
   • 用LIMIT限制结果集大小，减少网络传输和内存消耗。

3. 事务优化

   • 缩短事务范围，只在必要时开启事务。
   • 大事务拆分为小事务（如批量插入时分批提交）。