MySQL语句的用法有哪些

发布时间：2021-10-22 13:40:54 作者：iii
来源：亿速云阅读：173

# MySQL语句的用法有哪些

## 目录
1. [概述](#概述)
2. [数据定义语言(DDL)](#数据定义语言ddl)
   - [数据库操作](#数据库操作)
   - [表操作](#表操作)
   - [索引操作](#索引操作)
3. [数据操作语言(DML)](#数据操作语言dml)
   - [INSERT语句](#insert语句)
   - [UPDATE语句](#update语句)
   - [DELETE语句](#delete语句)
   - [REPLACE语句](#replace语句)
4. [数据查询语言(DQL)](#数据查询语言dql)
   - [基础查询](#基础查询)
   - [条件查询](#条件查询)
   - [排序与分页](#排序与分页)
   - [分组聚合](#分组聚合)
   - [多表连接](#多表连接)
   - [子查询](#子查询)
5. [数据控制语言(DCL)](#数据控制语言dcl)
   - [用户权限管理](#用户权限管理)
   - [事务控制](#事务控制)
6. [高级特性](#高级特性)
   - [存储过程](#存储过程)
   - [触发器](#触发器)
   - [视图](#视图)
   - [临时表](#临时表)
7. [性能优化](#性能优化)
   - [EXPLN分析](#explain分析)
   - [索引优化](#索引优化)
   - [查询优化](#查询优化)
8. [实战案例](#实战案例)
9. [总结](#总结)

## 概述
MySQL作为最流行的开源关系型数据库管理系统，其SQL语句按照功能可分为四大类：数据定义语言(DDL)、数据操作语言(DML)、数据查询语言(DQL)和数据控制语言(DCL)。本文将全面解析这些语句的用法，并通过大量示例演示实际应用场景。

## 数据定义语言(DDL)

### 数据库操作
```sql
-- 创建数据库
CREATE DATABASE IF NOT EXISTS shop DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 查看所有数据库
SHOW DATABASES;

-- 选择数据库
USE shop;

-- 修改数据库字符集
ALTER DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 删除数据库
DROP DATABASE IF EXISTS shop_backup;

表操作

-- 创建表
CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password CHAR(60) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 添加列
ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;

-- 修改列
ALTER TABLE users MODIFY COLUMN phone VARCHAR(15) NULL;

-- 重命名列
ALTER TABLE users CHANGE COLUMN phone mobile VARCHAR(15);

-- 删除列
ALTER TABLE users DROP COLUMN mobile;

-- 重命名表
RENAME TABLE users TO customers;

-- 截断表(清空数据)
TRUNCATE TABLE temp_logs;

-- 删除表
DROP TABLE IF EXISTS old_products;

索引操作

-- 创建普通索引
CREATE INDEX idx_username ON users(username);

-- 创建唯一索引
CREATE UNIQUE INDEX uq_email ON users(email);

-- 创建复合索引
CREATE INDEX idx_name_age ON employees(last_name, age);

-- 删除索引
DROP INDEX idx_username ON users;

-- 查看索引
SHOW INDEX FROM users;

数据操作语言(DML)

INSERT语句

-- 单行插入
INSERT INTO users (username, email, password) 
VALUES ('john_doe', 'john@example.com', '$2y$10$N9qo8uLOickgx2ZMRZoMy...');

-- 多行插入
INSERT INTO products (name, price, stock) VALUES
('Laptop', 999.99, 50),
('Phone', 699.99, 100),
('Tablet', 349.99, 75);

-- 插入查询结果
INSERT INTO user_backups 
SELECT * FROM users WHERE created_at < '2023-01-01';

-- 替换插入(存在则替换)
REPLACE INTO settings (user_id, theme, language) 
VALUES (1, 'dark', 'en');

UPDATE语句

-- 单表更新
UPDATE products 
SET price = price * 0.9, stock = stock - 1 
WHERE id = 1001;

-- 多表关联更新
UPDATE orders o
JOIN order_items i ON o.id = i.order_id
SET o.total_amount = o.total_amount - i.price
WHERE i.id = 5002;

-- 使用子查询更新
UPDATE employees
SET salary = salary * 1.05
WHERE department_id IN (
    SELECT id FROM departments WHERE name = 'Engineering'
);

-- 限制更新数量
UPDATE user_logs 
SET status = 'processed' 
WHERE status = 'pending' 
LIMIT 100;

DELETE语句

-- 条件删除
DELETE FROM sessions 
WHERE last_activity < NOW() - INTERVAL 30 DAY;

-- 多表关联删除
DELETE o, i
FROM orders o
JOIN order_items i ON o.id = i.order_id
WHERE o.status = 'cancelled';

-- 清空表数据
DELETE FROM temp_data;

-- 限制删除数量
DELETE FROM audit_logs 
ORDER BY created_at ASC 
LIMIT 1000;

REPLACE语句

-- 替换整行数据
REPLACE INTO products (id, name, price) 
VALUES (1, 'New Laptop', 1299.99);

-- 相当于以下操作的组合：
-- 1. 如果主键存在则删除原记录
-- 2. 插入新记录

数据查询语言(DQL)

基础查询

-- 查询所有列
SELECT * FROM employees;

-- 查询特定列
SELECT id, first_name, last_name, salary FROM employees;

-- 使用列别名
SELECT 
    id AS employee_id,
    CONCAT(first_name, ' ', last_name) AS full_name,
    salary * 12 AS annual_salary
FROM employees;

-- 使用DISTINCT去重
SELECT DISTINCT department_id FROM employees;

-- 使用LIMIT限制结果
SELECT * FROM products ORDER BY price DESC LIMIT 10;

条件查询

-- 比较运算符
SELECT * FROM products WHERE price > 100 AND stock < 50;

-- BETWEEN范围查询
SELECT * FROM orders 
WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';

-- IN运算符
SELECT * FROM customers 
WHERE country IN ('USA', 'Canada', 'Mexico');

-- LIKE模糊查询
SELECT * FROM products 
WHERE name LIKE '%Pro%' OR description LIKE '%高性能%';

-- NULL值判断
SELECT * FROM contacts WHERE phone IS NOT NULL;

-- 正则表达式查询
SELECT * FROM cities WHERE name REGEXP '^San';

排序与分页

-- 单列排序
SELECT * FROM employees ORDER BY hire_date DESC;

-- 多列排序
SELECT * FROM products 
ORDER BY category_id ASC, price DESC;

-- 分页查询
SELECT * FROM orders 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 40;  -- 等价于 LIMIT 40, 20

-- 使用变量实现分页
SET @page = 2, @size = 10;
SELECT * FROM articles 
ORDER BY publish_date DESC 
LIMIT @page, @size;

分组聚合

-- 基本聚合函数
SELECT 
    COUNT(*) AS total_orders,
    SUM(amount) AS total_sales,
    AVG(amount) AS avg_order,
    MAX(amount) AS max_order,
    MIN(amount) AS min_order
FROM orders;

-- 分组统计
SELECT 
    department_id,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

-- HAVING过滤分组
SELECT 
    customer_id,
    COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING order_count > 5;

-- WITH ROLLUP小计功能
SELECT 
    YEAR(order_date) AS year,
    MONTH(order_date) AS month,
    SUM(amount) AS monthly_sales
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date) WITH ROLLUP;

多表连接

-- 内连接
SELECT 
    o.order_id,
    o.order_date,
    c.customer_name,
    SUM(oi.quantity * oi.unit_price) AS total_amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id;

-- 左外连接
SELECT 
    d.department_name,
    COUNT(e.employee_id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

-- 自连接(查找同一部门员工)
SELECT 
    e1.employee_name,
    e2.employee_name AS colleague
FROM employees e1
JOIN employees e2 ON e1.department_id = e2.department_id 
    AND e1.employee_id != e2.employee_id;

-- 交叉连接
SELECT 
    p.product_name,
    s.size_option
FROM products p
CROSS JOIN size_options s;

子查询

-- WHERE子句中的子查询
SELECT * FROM products 
WHERE price > (SELECT AVG(price) FROM products);

-- FROM子句中的子查询
SELECT 
    dept.name,
    emp_stats.avg_salary
FROM departments dept
JOIN (
    SELECT 
        department_id,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) emp_stats ON dept.department_id = emp_stats.department_id;

-- EXISTS子查询
SELECT c.customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.customer_id
    AND o.order_date > '2023-01-01'
);

-- 行子查询
SELECT * FROM employees
WHERE (department_id, salary) IN (
    SELECT department_id, MAX(salary)
    FROM employees
    GROUP BY department_id
);

数据控制语言(DCL)

用户权限管理

-- 创建用户
CREATE USER 'app_user'@'%' IDENTIFIED BY 'SecurePass123!';

-- 修改密码
ALTER USER 'app_user'@'%' IDENTIFIED BY 'NewSecurePass456!';

-- 授予权限
GRANT SELECT, INSERT, UPDATE ON shop.* TO 'app_user'@'%';

-- 授予所有权限
GRANT ALL PRIVILEGES ON shop.* TO 'admin_user'@'localhost';

-- 撤销权限
REVOKE DELETE ON shop.* FROM 'app_user'@'%';

-- 查看权限
SHOW GRANTS FOR 'app_user'@'%';

-- 删除用户
DROP USER IF EXISTS 'temp_user'@'%';

事务控制

-- 开启事务
START TRANSACTION;

-- 执行多个操作
INSERT INTO orders (customer_id, amount) VALUES (1001, 199.99);
UPDATE inventory SET stock = stock - 1 WHERE product_id = 5003;

-- 根据条件提交或回滚
IF everything_ok THEN
    COMMIT;
ELSE
    ROLLBACK;
END IF;

-- 设置保存点
SAVEPOINT before_update;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 42;
-- 如果需要可以回滚到保存点
ROLLBACK TO before_update;

高级特性

存储过程

DELIMITER //
CREATE PROCEDURE process_monthly_sales(IN month INT, IN year INT)
BEGIN
    DECLARE total DECIMAL(12,2);
    
    -- 计算总销售额
    SELECT SUM(amount) INTO total
    FROM orders
    WHERE MONTH(order_date) = month AND YEAR(order_date) = year;
    
    -- 插入报表
    INSERT INTO sales_reports (report_month, report_year, total_sales)
    VALUES (month, year, total);
    
    -- 返回结果
    SELECT CONCAT('Processed sales for ', month, '/', year) AS result;
END //
DELIMITER ;

-- 调用存储过程
CALL process_monthly_sales(6, 2023);

触发器

-- 审计日志触发器
CREATE TRIGGER after_user_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    IF NEW.email != OLD.email THEN
        INSERT INTO user_audit_logs (
            user_id, 
            changed_field, 
            old_value, 
            new_value, 
            changed_at
        ) VALUES (
            NEW.id, 
            'email', 
            OLD.email, 
            NEW.email, 
            NOW()
        );
    END IF;
END;

-- 库存更新触发器
CREATE TRIGGER after_order_insert
AFTER INSERT ON order_items
FOR EACH ROW
UPDATE products 
SET stock = stock - NEW.quantity
WHERE id = NEW.product_id;

视图

-- 创建视图
CREATE VIEW customer_order_summary AS
SELECT 
    c.id,
    c.name,
    c.email,
    COUNT(o.id) AS order_count,
    SUM(o.total_amount) AS total_spent,
    MAX(o.order_date) AS last_order_date
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id;

-- 使用视图
SELECT * FROM customer_order_summary 
WHERE total_spent > 1000
ORDER BY total_spent DESC;

-- 更新视图定义
CREATE OR REPLACE VIEW product_inventory AS
SELECT 
    p.id,
    p.name,
    p.price,
    i.quantity_in_stock,
    i.last_restocked
FROM products p
JOIN inventory i ON p.id = i.product_id;

-- 删除视图
DROP VIEW IF EXISTS old_report_view;

临时表

-- 创建临时表
CREATE TEMPORARY TABLE temp_top_customers AS
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
ORDER BY total_spent DESC
LIMIT 10;

-- 使用临时表
SELECT c.*, t.total_spent
FROM customers c
JOIN temp_top_customers t ON c.id = t.customer_id;

-- 会话结束自动删除
-- 也可以手动删除
DROP TEMPORARY TABLE IF EXISTS temp_top_customers;

-- 内存临时表
CREATE TEMPORARY TABLE temp_session_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    key_name VARCHAR(50),
    key_value VARCHAR(255)
) ENGINE=MEMORY;

性能优化

EXPLN分析

-- 基本用法
EXPLN SELECT * FROM orders WHERE customer_id = 1001;

-- 分析连接查询
EXPLN FORMAT=JSON
SELECT p.name, oi.quantity, o.order_date
FROM products p
JOIN order_items oi ON p.id = oi.product_id
JOIN orders o ON oi.order_id = o.id
WHERE o.customer_id = 1001;

-- 分析关键指标
EXPLN ANALYZE
SELECT * FROM users WHERE username LIKE 'john%';

-- 检查索引使用
EXPLN 
SELECT * FROM employees 
WHERE last_name = 'Smith' AND department_id = 5;

索引优化

-- 添加复合索引
ALTER TABLE orders ADD INDEX idx_customer_date (customer_id, order_date);

-- 使用覆盖索引
-- 查询只需要访问索引列，无需回表
SELECT customer_id, order_date FROM orders 
WHERE customer_id = 1001;

-- 索引提示
SELECT * FROM orders USE INDEX (idx_customer_date)
WHERE customer_id = 1001 AND order_date > '2023-01-01';

-- 忽略索引
SELECT * FROM orders IGNORE INDEX (idx_status)
WHERE status = 'shipped';

-- 强制索引
SELECT * FROM orders FORCE INDEX (primary)
WHERE id BETWEEN 1000 AND 2000;

查询优化

-- 避免SELECT *
SELECT id, name, email FROM users;

-- 优化LIKE查询
-- 前导通配符无法使用索引
SELECT * FROM products WHERE name LIKE 'Pro%';  -- 可以使用索引
SELECT * FROM products WHERE name LIKE '%Pro%'; -- 无法使用索引

-- 使用连接代替子查询
-- 优化前
SELECT * FROM customers 
WHERE id IN (SELECT customer_id FROM orders WHERE amount > 100);

-- 优化后
SELECT DISTINCT c.* 
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.amount > 100;

-- 分批处理大数据
-- 使用游标或LIMIT分批次处理
SELECT * FROM large_table WHERE condition LIMIT 1000 OFFSET 0;
-- 处理完后
SELECT * FROM large_table WHERE condition LIMIT 1000 OFFSET 1000;

实战案例

电商系统常用查询

”`sql – 1. 查找热销商品 SELECT p.id, p.name, SUM(oi.quantity) AS total_sold, SUM(oi.quantity * oi.unit_price) AS total_revenue FROM products p JOIN order_items oi ON p.id = oi.product_id JOIN orders o ON oi.order_id = o.id WHERE o.order_date BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW() GROUP BY p.id ORDER BY total_sold DESC LIMIT 10;

– 2. 客户价值分析 SELECT c.id, c.name, c.email, COUNT(o.id) AS order_count, SUM(o.total_amount) AS lifetime_value, DATEDIFF(NOW(), MAX(o.order_date)) AS days_since_last_order FROM customers c LEFT JOIN orders o ON c.id = o.customer_id GROUP BY c.id ORDER BY lifetime_value DESC;

– 3. 库存预警