# 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; -- 单行插入 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 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 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 INTO products (id, name, price) VALUES (1, 'New Laptop', 1299.99); -- 相当于以下操作的组合： -- 1. 如果主键存在则删除原记录 -- 2. 插入新记录 -- 查询所有列 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 ); -- 创建用户 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 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. 库存预警
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
