MySQL教程FG076-MySQL DML语句详解

内容简介：本文主要介绍MySQL相关知识，包括安装、配置、优化等内容。风哥教程参考MySQL官方文档MySQL SQL Syntax、MySQL Server Administration。 01 更多视频教程www.fgedu.net.cn 02 学习交流加群风哥微信: itpux-com

Part01-基础概念与理论知识

DML（Data Manipulation Language，数据操作语言）是SQL语言的一个重要组成部分，用于对数据库中的数据进行增删改查操作。在MySQL中，DML语句主要包括INSERT、UPDATE、DELETE和SELECT等语句。 03 学习交流加群风哥QQ113257174

1.1 DML语句的特点

DML语句执行后不会自动提交，需要手动COMMIT（除非设置了自动提交）
DML语句会对数据进行操作，可能会影响数据库的性能
DML语句执行后会导致数据库中的数据发生变化

1.2 常用DML语句分类

语句	作用
INSERT	向表中插入数据
UPDATE	更新表中的数据
DELETE	删除表中的数据
SELECT	查询表中的数据

Part02-生产环境规划与建议

2.1 基本INSERT语句

-- 插入单条数据
INSERT INTO employees (name, age, department, salary) 
VALUES ('风哥1号', 28, '技术部', 10000);

-- 插入多条数据
INSERT INTO employees (name, age, department, salary) 
VALUES 
('风哥2号', 32, '技术部', 15000),
('王五', 25, '市场部', 6000),
('赵六', 35, '人力资源部', 9000);

2.2 插入指定列

-- 只插入部分列，其他列使用默认值或NULL
INSERT INTO employees (name, department, salary) 
VALUES ('孙七', '技术部', 12000);

2.3 插入默认值

-- 使用默认值
INSERT INTO employees (name, age, department, salary) 
VALUES ('周八', 26, '市场部', DEFAULT);

-- 显式指定默认值
INSERT INTO employees (name, age, department, salary) 
VALUES ('吴九', 29, '技术部', DEFAULT);

2.4 从其他表插入数据

-- 从其他表插入数据
INSERT INTO employees_backup (name, age, department, salary) 
SELECT name, age, department, salary 
FROM employees 
WHERE department = '技术部';

2.5 INSERT IGNORE语句

-- 插入时忽略重复键错误
INSERT IGNORE INTO employees (id, name, age, department, salary) 
VALUES (1, '风哥1号', 28, '技术部', 10000);

2.6 REPLACE语句

-- 替换已存在的数据
REPLACE INTO employees (id, name, age, department, salary) 
VALUES (1, '风哥1号', 29, '技术部', 11000);

Part03-生产环境项目实施方案

3.1 基本UPDATE语句

-- 更新单条记录
UPDATE employees 
SET salary = 12000 
WHERE id = 1;

-- 更新多条记录
UPDATE employees 
SET salary = salary * 1.1 
WHERE department = '技术部';

3.2 更新多列

-- 更新多列
UPDATE employees 
SET salary = 13000, age = 30 
WHERE id = 1;

3.3 使用子查询更新

-- 使用子查询更新
UPDATE employees 
SET salary = (SELECT AVG(salary) FROM employees WHERE department = '技术部') 
WHERE department = '市场部';

3.4 UPDATE JOIN语句

-- 使用JOIN更新
UPDATE employees e 
JOIN departments d ON e.department_id = d.id 
SET e.salary = e.salary * 1.05 
WHERE d.name = '技术部';

3.5 限制更新行数

-- 限制更新行数
UPDATE employees 
SET salary = salary * 1.1 
ORDER BY salary ASC 
LIMIT 10;

Part04-生产案例与实战讲解

4.1 基本DELETE语句

-- 删除单条记录
DELETE FROM employees 
WHERE id = 1;

-- 删除多条记录
DELETE FROM employees 
WHERE department = '市场部';

4.2 使用子查询删除

-- 使用子查询删除
DELETE FROM employees 
WHERE id IN (SELECT id FROM employees WHERE salary < 5000);

4.3 DELETE JOIN语句

-- 使用JOIN删除
DELETE e FROM employees e 
JOIN departments d ON e.department_id = d.id 
WHERE d.name = '人力资源部';

4.4 限制删除行数

-- 限制删除行数
DELETE FROM employees 
ORDER BY hire_date ASC 
LIMIT 5;

4.5 TRUNCATE语句

-- 截断表（删除所有数据，但保留表结构）
TRUNCATE TABLE employees;

Part05-风哥经验总结与分享

5.1 基本SELECT语句

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

-- 查询指定列
SELECT id, name, salary FROM employees;

5.2 WHERE子句

-- 使用WHERE子句
SELECT * FROM employees 
WHERE department = '技术部' AND salary > 10000;

5.3 ORDER BY子句

-- 使用ORDER BY排序
SELECT * FROM employees 
ORDER BY salary DESC, age ASC;

5.4 LIMIT子句

-- 使用LIMIT限制结果集
SELECT * FROM employees 
ORDER BY salary DESC 
LIMIT 10;

-- 使用LIMIT分页
SELECT * FROM employees 
ORDER BY id ASC 
LIMIT 10 OFFSET 20;

5.5 GROUP BY子句

-- 使用GROUP BY分组
SELECT department, COUNT(*) AS employee_count, AVG(salary) AS avg_salary 
FROM employees 
GROUP BY department;

5.6 HAVING子句

-- 使用HAVING筛选分组结果
SELECT department, COUNT(*) AS employee_count, AVG(salary) AS avg_salary 
FROM employees 
GROUP BY department 
HAVING employee_count > 5;

5.7 JOIN操作

-- INNER JOIN
SELECT e.id, e.name, e.salary, d.name AS department_name 
FROM employees e 
INNER JOIN departments d ON e.department_id = d.id;

-- LEFT JOIN
SELECT e.id, e.name, e.salary, d.name AS department_name 
FROM employees e 
LEFT JOIN departments d ON e.department_id = d.id;

-- RIGHT JOIN
SELECT e.id, e.name, e.salary, d.name AS department_name 
FROM employees e 
RIGHT JOIN departments d ON e.department_id = d.id;

-- FULL JOIN（MySQL不直接支持，需要使用UNION）
SELECT e.id, e.name, e.salary, d.name AS department_name 
FROM employees e 
LEFT JOIN departments d ON e.department_id = d.id
UNION
SELECT e.id, e.name, e.salary, d.name AS department_name 
FROM employees e 
RIGHT JOIN departments d ON e.department_id = d.id 
WHERE e.id IS NULL;

5.8 子查询

-- 标量子查询
SELECT name, salary, (SELECT AVG(salary) FROM employees) AS avg_salary 
FROM employees;

-- 列子查询
SELECT * FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

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

-- 表子查询
SELECT * FROM (
    SELECT department, AVG(salary) AS avg_salary 
    FROM employees 
    GROUP BY department
) AS dept_avg 
WHERE avg_salary > 8000;

5.9 常用函数

-- 聚合函数
SELECT 
    COUNT(*) AS total_employees,
    SUM(salary) AS total_salary,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary
FROM employees;

-- 字符串函数
SELECT 
    CONCAT(name, ' (', department, ')') AS employee_info,
    LENGTH(name) AS name_length,
    UPPER(name) AS upper_name,
    LOWER(name) AS lower_name
FROM employees;

-- 日期函数
SELECT 
    name, 
    hire_date,
    DATEDIFF(NOW(), hire_date) AS days_employed,
    DATE_FORMAT(hire_date, '%Y-%m-%d') AS formatted_date
FROM employees;

6. DML语句最佳实践

6.1 INSERT语句最佳实践

使用批量插入（多条数据一次性插入）以提高性能
明确指定列名，不要依赖列的顺序
对于自增主键，不要显式指定值
使用INSERT IGNORE或REPLACE处理重复键情况

6.2 UPDATE语句最佳实践

总是使用WHERE子句，否则会更新所有记录
使用索引列作为WHERE条件，以提高性能
对于大表更新，分批进行以减少锁定时间
使用事务来确保更新的原子性

6.3 DELETE语句最佳实践

总是使用WHERE子句，否则会删除所有记录
使用索引列作为WHERE条件，以提高性能
对于大表删除，分批进行以减少锁定时间
考虑使用TRUNCATE TABLE代替DELETE删除所有数据

6.4 SELECT语句最佳实践

只查询需要的列，不要使用SELECT *
使用WHERE子句过滤数据，减少返回的行数
使用索引列作为WHERE条件和ORDER BY列
对于复杂查询，使用EXPLAIN分析执行计划
使用LIMIT限制结果集大小
避免在WHERE子句中使用函数，以免索引失效

7. 常见错误和解决方案

7.1 INSERT语句错误

错误信息	原因	解决方案
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'	主键重复	使用INSERT IGNORE或REPLACE，或修改插入的值
ERROR 1364 (HY000): Field 'name' doesn't have a default value	必填字段没有值	为必填字段提供值，或设置默认值
ERROR 1292 (22007): Incorrect date value: '2023/12/31' for column 'hire_date' at row 1	日期格式错误	使用正确的日期格式（YYYY-MM-DD）

7.2 UPDATE语句错误

错误信息	原因	解决方案
ERROR 1048 (23000): Column 'salary' cannot be null	将列更新为NULL，但该列不允许NULL	为该列提供非NULL值
ERROR 1264 (22003): Out of range value for column 'age' at row 1	值超出列的范围	使用在列范围内的值

7.3 DELETE语句错误

错误信息	原因	解决方案
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails	外键约束阻止删除	先删除子表中的相关记录，或使用ON DELETE CASCADE

7.4 SELECT语句错误

错误信息	原因	解决方案
ERROR 1054 (42S22): Unknown column 'department_name' in 'field list'	列名错误	使用正确的列名
ERROR 1064 (42000): You have an error in your SQL syntax	SQL语法错误	检查SQL语句的语法

8. 示例演示

8.1 完整的DML操作示例

-- 创建测试表
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    age INT,
    department VARCHAR(50),
    salary DECIMAL(10,2),
    hire_date DATE
);

-- 插入数据
INSERT INTO employees (name, age, department, salary, hire_date) 
VALUES 
('风哥1号', 28, '技术部', 10000, '2023-01-15'),
('风哥2号', 32, '技术部', 15000, '2022-06-10'),
('王五', 25, '市场部', 6000, '2023-03-20'),
('赵六', 35, '人力资源部', 9000, '2021-11-05'),
('孙七', 30, '技术部', 12000, '2022-09-01');

-- 查询数据
SELECT * FROM employees;

-- 查询技术部员工
SELECT * FROM employees WHERE department = '技术部';

-- 按工资排序
SELECT * FROM employees ORDER BY salary DESC;

-- 统计各部门人数和平均工资
SELECT department, COUNT(*) AS count, AVG(salary) AS avg_salary 
FROM employees 
GROUP BY department;

-- 更新数据
UPDATE employees SET salary = 11000 WHERE id = 1;

-- 批量更新市场部工资
UPDATE employees SET salary = salary * 1.05 WHERE department = '市场部';

-- 删除数据
DELETE FROM employees WHERE id = 5;

-- 删除工资低于8000的员工
DELETE FROM employees WHERE salary < 8000;

-- 再次查询
SELECT * FROM employees;

8.2 复杂查询示例

-- 创建部门表
CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    manager VARCHAR(100)
);

-- 插入部门数据
INSERT INTO departments (name, manager) 
VALUES 
('技术部', '风哥2号'),
('市场部', '王五'),
('人力资源部', '赵六');

-- 更新员工表，添加部门ID
ALTER TABLE employees ADD COLUMN department_id INT;
UPDATE employees e 
JOIN departments d ON e.department = d.name 
SET e.department_id = d.id;

-- 复杂查询：获取各部门经理及其部门员工信息
SELECT 
    d.name AS department_name,
    d.manager,
    e.name AS employee_name,
    e.salary,
    e.hire_date
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
ORDER BY d.name, e.salary DESC;

9. 总结

DML语句是MySQL数据库操作的核心，用于对数据进行增删改查。本文详细介绍了MySQL中常用的DML语句，包括INSERT、UPDATE、DELETE和SELECT语句的使用方法、语法规则、最佳实践和常见错误解决方案。 04 风哥提示：

在使用DML语句时，需要注意以下几点： 05更多学习教程公众号风哥教程itpux_com

使用合适的DML语句来操作数据
总是使用WHERE子句来限制操作的范围
使用索引来提高查询和更新的性能
对于大表操作，分批进行以减少锁定时间
使用事务来确保操作的原子性
遵循最佳实践，写出高效、安全的DML语句

通过掌握DML语句的使用方法，可以有效地管理MySQL数据库中的数据，提高数据操作的效率和准确性。 06 from mysql视频:www.itpux.com

GF-MySQL数据库培训文档系列

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html