当前位置： 首页 > ds >正文

数据库的基本操作

ds 2025/8/25 5:52:53

MySQL 是当前最流行的开源关系型数据库管理系统之一，广泛应用于 Web 应用程序开发。以下是 MySQL 数据库的详细操作指南，包含文字说明和代码示例。

MySQL 数据库操作指南

一、数据库基础管理

1.1 数据库的创建与选择

MySQL 数据库作为关系型数据库管理系统(RDBMS)的核心数据存储容器，可包含多张数据表，用于组织和管理结构化数据。创建数据库是 MySQL 操作的首要步骤，也是数据存储的基础架构。

基本操作示例
-- 创建新数据库（指定字符集和排序规则）
CREATE DATABASE company_db 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_general_ci;-- 安全创建（避免重复创建报错）
CREATE DATABASE IF NOT EXISTS company_db;-- 查看所有数据库（包括系统数据库）
SHOW DATABASES;-- 选择要操作的数据库（后续SQL将在此数据库执行）
USE company_db;
高级数据库创建选项
-- 创建带详细参数的数据库
CREATE DATABASE inventory_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci
DEFAULT ENCRYPTION='N';
注意事项：

  1. 命名规范：

    • MySQL 中数据库名称默认区分大小写（取决于操作系统，Linux区分而Windows不区分）
    • 建议使用下划线命名数据库（如company_db）而非空格或特殊字符
    • 避免使用MySQL保留关键字作为数据库名

  2. 字符集推荐：

    • 推荐使用utf8mb4字符集以支持完整的Unicode字符（包括emoji）
    • 排序规则根据语言需求选择（如中文排序用utf8mb4_unicode_ci）

  3. 操作前必须通过USE命令选择目标数据库，否则会报"No database selected"错误

  4. 权限控制：创建数据库需要CREATE权限，可通过GRANT CREATE ON *.* TO 'user'@'host'授权

1.2 数据库删除

数据库删除是不可逆操作，执行前务必确认数据已备份或不再需要。

删除操作示例
-- 基本删除命令
DROP DATABASE company_db;-- 安全删除（避免不存在的数据库报错）
DROP DATABASE IF EXISTS company_db;-- 删除并立即释放空间（MySQL 8.0+）
DROP DATABASE company_db WAIT 10; -- 等待10秒IO完成
警告与注意事项：

  1. 数据永久丢失：删除操作将清除数据库内所有数据，包括：

    • 所有表结构和数据
    • 视图、存储过程等数据库对象
    • 相关权限设置

  2. 备份建议：

    # 使用mysqldump备份数据库
mysqldump -u root -p company_db > company_db_backup.sql

  3. 生产环境防护：

    • 设置--safe-updates选项防止误删
    • 实施权限分离，限制DROP权限
    • 考虑使用数据库回收站插件（如MySQL Shell的util.enableRecursiveDeletionProtection）

  4. 替代方案：对于测试环境，可考虑重命名而非删除：

    RENAME DATABASE company_db TO company_db_old;

二、数据表管理

2.1 创建数据表

数据表是MySQL中存储数据的基本结构，由行(记录)和列(字段)组成二维关系模型。

完整表示例
CREATE TABLE employees (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '员工ID',name VARCHAR(50) NOT NULL COMMENT '员工姓名',age TINYINT UNSIGNED CHECK (age >= 18) COMMENT '员工年龄',department VARCHAR(50) DEFAULT '未分配' COMMENT '所属部门',salary DECIMAL(10,2) COMMENT '月薪(保留2位小数)',hire_date DATE NOT NULL COMMENT '入职日期',email VARCHAR(100) UNIQUE COMMENT '电子邮箱',resume TEXT COMMENT '个人简历',status ENUM('active', 'on_leave', 'terminated') DEFAULT 'active' COMMENT '在职状态',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',INDEX idx_department (department),FULLTEXT INDEX idx_resume (resume)
) ENGINE=InnoDB 
DEFAULT CHARSET=utf8mb4
COMMENT='公司员工信息表'
PARTITION BY RANGE (YEAR(hire_date)) (PARTITION p2019 VALUES LESS THAN (2020),PARTITION p2020 VALUES LESS THAN (2021),PARTITION p2021 VALUES LESS THAN (2022),PARTITION pmax VALUES LESS THAN MAXVALUE
);
字段类型详解
类型说明示例
整数类型
TINYINT1字节整数(-128~127)age TINYINT
SMALLINT2字节整数small_id SMALLINT
INT4字节整数id INT
BIGINT8字节整数big_id BIGINT
小数类型
DECIMAL(m,n)精确小数salary DECIMAL(10,2)
FLOAT4字节浮点数ratio FLOAT
DOUBLE8字节浮点数precise DOUBLE
字符串类型
CHAR(n)定长字符串gender CHAR(1)
VARCHAR(n)变长字符串name VARCHAR(50)
TEXT长文本content TEXT
日期时间
DATE日期birthday DATE
TIME时间work_time TIME
DATETIME日期时间login_time DATETIME
TIMESTAMP时间戳created_at TIMESTAMP
特殊类型
ENUM枚举值status ENUM('Y','N')
SET集合tags SET('red','blue','green')
JSONJSON数据profile JSON
约束详解

  1. 主键约束：

    -- 单列主键
id INT PRIMARY KEY-- 复合主键
PRIMARY KEY (department, employee_code)

  2. 外键约束（确保引用完整性）：

    CREATE TABLE orders (order_id INT PRIMARY KEY,employee_id INT,FOREIGN KEY (employee_id) REFERENCES employees(id)ON DELETE CASCADEON UPDATE RESTRICT
);

  3. 检查约束（MySQL 8.0+）：

    salary DECIMAL(10,2) CHECK (salary > 0)

  4. 默认值约束：

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP

2.2 修改表结构

随着业务需求变化，表结构可能需要调整，MySQL提供丰富的ALTER TABLE操作。

常用修改操作
-- 添加新列（带位置控制）
ALTER TABLE employees 
ADD COLUMN phone VARCHAR(20) AFTER email;-- 修改列定义（保持数据）
ALTER TABLE employees 
MODIFY COLUMN phone VARCHAR(15) NOT NULL 
COMMENT '联系电话';-- 重命名列（同时可修改类型）
ALTER TABLE employees 
CHANGE COLUMN phone mobile_phone VARCHAR(15);-- 删除列（MySQL 8.0支持原子DDL）
ALTER TABLE employees 
DROP COLUMN mobile_phone,
ALGORITHM=INPLACE, LOCK=NONE;-- 添加外键约束
ALTER TABLE orders
ADD CONSTRAINT fk_employee
FOREIGN KEY (employee_id) REFERENCES employees(id);-- 修改表选项
ALTER TABLE employees 
ENGINE=InnoDB, 
COMMENT='更新后的员工表',
AUTO_INCREMENT=1001;
索引管理
-- 添加普通索引
ALTER TABLE employees 
ADD INDEX idx_name_department (name, department);-- 添加唯一索引
ALTER TABLE employees 
ADD UNIQUE INDEX idx_unique_email (email);-- 删除索引
ALTER TABLE employees 
DROP INDEX idx_name_department;-- 重建表优化（MyISAM适用）
ALTER TABLE employees ENGINE=MyISAM;
分区表操作（MySQL 5.7+）
-- 添加新分区
ALTER TABLE employees 
ADD PARTITION (PARTITION p2022 VALUES LESS THAN (2023)
);-- 重组分区
ALTER TABLE employees 
REORGANIZE PARTITION pmax INTO (PARTITION p2023 VALUES LESS THAN (2024),PARTITION pmax VALUES LESS THAN MAXVALUE
);-- 删除分区（数据会丢失）
ALTER TABLE employees 
DROP PARTITION p2019;

三、数据操作

3.1 插入数据

单行插入
-- 完整字段插入
INSERT INTO employees 
(name, age, department, salary, hire_date, email)
VALUES 
('张伟', 28, '研发部', 15000.00, '2020-06-15', 'zhangwei@example.com');-- 省略可为NULL字段
INSERT INTO employees 
(name, age, salary, hire_date)
VALUES 
('李娜', 26, 12000.00, '2021-03-10');-- 使用DEFAULT关键字
INSERT INTO employees 
(name, age, department, salary, hire_date)
VALUES 
('王芳', 30, DEFAULT, 18000.00, '2019-11-22');
批量插入
-- 多值插入（高效）
INSERT INTO employees 
(name, age, department, salary, hire_date, email)
VALUES 
('赵明', 32, '市场部', 14000.00, '2018-09-05', 'zhaoming@example.com'),
('刘红', 29, '人事部', 13000.00, '2020-02-18', 'liuhong@example.com'),
('陈强', 35, '研发部', 20000.00, '2017-07-30', 'chenqiang@example.com');-- 从查询结果插入
INSERT INTO new_employees
(name, department, salary)
SELECT name, department, salary 
FROM employees 
WHERE hire_date > '2022-01-01';
高级插入技术
-- 插入或更新（ON DUPLICATE KEY UPDATE）
INSERT INTO employees 
(id, name, email, salary)
VALUES 
(101, '张伟', 'zhangwei@example.com', 16000.00)
ON DUPLICATE KEY UPDATE 
salary = VALUES(salary),
updated_at = CURRENT_TIMESTAMP;-- 替换数据（REPLACE）
REPLACE INTO employees 
(id, name, email)
VALUES 
(101, '张伟新', 'zhangwei_new@example.com');-- 忽略错误插入（IGNORE）
INSERT IGNORE INTO employees 
(email, name)
VALUES 
('zhangwei@example.com', '张伟');

3.2 查询数据

基础查询
-- 查询所有列
SELECT * FROM employees;-- 查询特定列
SELECT id AS 员工编号,name AS 姓名,CONCAT('¥', FORMAT(salary, 2)) AS 月薪
FROM employees;-- 使用计算字段
SELECT name,salary,salary * 12 AS 年薪,salary * 0.2 AS 季度奖
FROM employees;-- 去重查询
SELECT DISTINCT department FROM employees;
条件查询
-- 比较运算符
SELECT * FROM employees WHERE salary >= 15000;-- 逻辑运算符
SELECT * FROM employees 
WHERE department = '研发部' AND salary > 10000;-- BETWEEN范围查询
SELECT * FROM employees 
WHERE hire_date BETWEEN '2020-01-01' AND '2021-12-31';-- IN查询
SELECT * FROM employees 
WHERE department IN ('研发部', '市场部');-- LIKE模糊查询
SELECT * FROM employees 
WHERE name LIKE '张%' OR name LIKE '%强%';-- NULL值处理
SELECT * FROM employees 
WHERE department IS NOT NULL;-- 正则表达式查询
SELECT * FROM employees 
WHERE name REGEXP '^[赵钱孙李]';
排序与分页
-- 单字段排序
SELECT * FROM employees 
ORDER BY hire_date DESC;-- 多字段排序
SELECT * FROM employees 
ORDER BY department ASC, salary DESC;-- 分页查询（LIMIT offset, count）
SELECT * FROM employees 
ORDER BY id 
LIMIT 10 OFFSET 20;  -- 第3页，每页10条-- MySQL 8.0+窗口函数
SELECT id, name, salary,RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees
LIMIT 10;
聚合函数
-- 基本聚合
SELECT COUNT(*) AS 总人数,AVG(salary) AS 平均薪资,MAX(hire_date) AS 最新入职日期
FROM employees;-- 分组聚合
SELECT department,COUNT(*) AS 人数,ROUND(AVG(salary), 2) AS 平均薪资,SUM(salary) AS 薪资总额
FROM employees
GROUP BY department;-- HAVING筛选
SELECT department,AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING avg_salary > 15000
ORDER BY avg_salary DESC;-- WITH ROLLUP小计
SELECT department,gender,COUNT(*) AS count
FROM employees
GROUP BY department, gender WITH ROLLUP;

3.3 更新数据

基本更新
-- 条件更新
UPDATE employees 
SET salary = salary * 1.05 
WHERE department = '研发部';-- 多字段更新
UPDATE employees 
SET salary = salary + 1000,department = '技术研发部',updated_at = CURRENT_TIMESTAMP
WHERE id = 1005;-- 使用子查询更新
UPDATE employees e
JOIN (SELECT department, AVG(salary) AS avg_salFROM employeesGROUP BY department
) dept ON e.department = dept.department
SET e.salary = e.salary * 1.1
WHERE e.salary < dept.avg_sal;
高级更新技术
-- CASE条件更新
UPDATE employees
SET salary = CASEWHEN performance = 'A' THEN salary * 1.2WHEN performance = 'B' THEN salary * 1.1ELSE salary * 1.05
END;-- LIMIT限制更新数量
UPDATE employees 
SET last_notice = CURRENT_DATE
WHERE notice_count = 0
LIMIT 100;-- 基于JOIN的更新
UPDATE employees e
JOIN departments d ON e.department_id = d.id
SET e.salary = e.salary * 1.15
WHERE d.location = '上海';

3.4 删除数据

条件删除
-- 基本删除
DELETE FROM employees 
WHERE status = 'terminated';-- 多表关联删除
DELETE e FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.name = '临时部门';-- LIMIT限制删除数量
DELETE FROM log_records
ORDER BY create_time
LIMIT 10000;
清空表数据
-- DELETE方式（可回滚）
DELETE FROM temp_data;  -- 保留自增值-- TRUNCATE方式（更快，不可回滚）
TRUNCATE TABLE temp_data;  -- 重置自增值-- 外键约束下的安全删除
SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE important_data;
SET FOREIGN_KEY_CHECKS = 1;

四、高级查询技术

4.1 连接查询

内连接(INNER JOIN)
-- 基本内连接
SELECT e.name, d.department_name,p.project_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
INNER JOIN projects p ON e.current_project = p.id;-- 使用USING简化相同列名连接
SELECT e.name, p.project_name
FROM employees e
INNER JOIN projects p USING (department_id);
外连接(OUTER JOIN)
-- 左外连接（保留左表所有记录）
SELECT e.name,m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;-- 右外连接（保留右表所有记录）
SELECT d.department_name,COUNT(e.id) AS employee_count
FROM departments d
RIGHT JOIN employees e ON d.id = e.department_id
GROUP BY d.department_name;-- 全外连接模拟（MySQL不支持FULL OUTER JOIN）
SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id
UNION
SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id WHERE table1.id IS NULL;
特殊连接类型
-- 交叉连接（笛卡尔积）
SELECT e.name,p.project_name
FROM employees e
CROSS JOIN projects p;-- 自然连接（自动匹配相同列名，慎用）
SELECT * FROM employees NATURAL JOIN departments;-- STRAIGHT_JOIN强制连接顺序
SELECT STRAIGHT_JOIN e.name, d.name 
FROM employees e JOIN departments d ON e.department_id = d.id;

4.2 子查询

WHERE子句中的子查询
-- 单行子查询
SELECT * FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);-- 多行子查询(IN)
SELECT * FROM employees 
WHERE department_id IN (SELECT id FROM departments WHERE location = '北京'
);-- 多行子查询(EXISTS)
SELECT * FROM departments d
WHERE EXISTS (SELECT 1 FROM employees eWHERE e.department_id = d.id AND e.salary > 20000
);-- 比较运算符子查询(ALL/ANY)
SELECT * FROM employees
WHERE salary >= ALL (SELECT salary FROM managers
);
FROM子句中的派生表
-- 基本派生表
SELECT dept_stats.department, dept_stats.avg_sal
FROM (SELECT department, AVG(salary) AS avg_salFROM employeesGROUP BY department
) AS dept_stats
WHERE dept_stats.avg_sal > 15000;-- LATERAL派生表(MySQL 8.0+)
SELECT e.name, latest_review.review
FROM employees e,
LATERAL (SELECT review FROM performance_reviewsWHERE employee_id = e.idORDER BY review_date DESCLIMIT 1
) AS latest_review;
SELECT子句中的标量子查询
SELECT e.name,e.salary,(SELECT AVG(salary) FROM employees) AS company_avg,e.salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees e;
公用表表达式(CTE)
-- 基本CTE
WITH dept_stats AS (SELECT department, COUNT(*) AS emp_count, AVG(salary) AS avg_salFROM employeesGROUP BY department
)
SELECT * FROM dept_stats
WHERE emp_count > 5;-- 递归CTE(生成序列)
WITH RECURSIVE numbers AS (SELECT 1 AS nUNION ALLSELECT n + 1 FROM numbers WHERE n < 100
)
SELECT * FROM numbers;-- 递归CTE(组织架构查询)
WITH RECURSIVE org_chart AS (SELECT id, name, manager_id, 1 AS levelFROM employees WHERE manager_id IS NULLUNION ALLSELECT e.id, e.name, e.manager_id, oc.level + 1FROM employees eJOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level;

五、索引优化

5.1 索引创建策略

单列索引
-- 基本索引
CREATE INDEX idx_employee_name ON employees(name);-- 前缀索引(长字符串)
CREATE INDEX idx_email_prefix ON employees(email(10));-- 函数索引(MySQL 8.0+)
CREATE INDEX idx_name_lower ON employees((LOWER(name)));
复合索引
-- 基本复合索引
CREATE INDEX idx_dept_salary ON employees(department, salary);-- 包含INCLUDE列的索引(MySQL不支持，此为概念示例)
-- 实际MySQL中所有索引列都会参与索引结构
特殊类型索引
-- 唯一索引
CREATE UNIQUE INDEX idx_unique_employee_code ON employees(employee_code);-- 全文索引(MyISAM/InnoDB)
CREATE FULLTEXT INDEX idx_article_content ON articles(content);-- 空间索引(地理数据)
CREATE SPATIAL INDEX idx_location ON stores(coordinates);
索引选项
-- 指定索引类型
CREATE INDEX idx_hash_name ON employees(name) USING HASH;-- 指定索引算法
CREATE INDEX idx_btree_name ON employees(name) USING BTREE;-- 索引注释
CREATE INDEX idx_department ON employees(department) COMMENT '部门查询优化';

5.2 索引管理与优化

索引维护
-- 查看表索引
SHOW INDEX FROM employees;-- 获取索引信息
SELECT * FROM information_schema.STATISTICS
WHERE table_name = 'employees';-- 删除索引
DROP INDEX idx_employee_name ON employees;-- 重建索引(MyISAM)
REPAIR TABLE employees QUICK;-- 优化表(InnoDB)
OPTIMIZE TABLE employees;
索引使用分析
-- 使用EXPLAIN分析查询
EXPLAIN SELECT * FROM employees WHERE name = '张伟';-- 检查索引使用情况
SELECT object_type,object_schema,object_name,index_name,count_read,count_fetch
FROM performance_schema.table_io_waits_summary_by_index_usage
ORDER BY count_read DESC;-- 查找未使用索引
SELECT object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
AND count_star = 0
AND object_schema NOT IN ('mysql', 'performance_schema');
索引优化建议

  1. 选择合适索引列：

    • 高选择性的列（如ID、用户名）
    • 常用于WHERE、JOIN、ORDER BY的列
    • 避免过度索引（一般表不超过5-6个索引）

  2. 复合索引设计原则：

    • 最左前缀原则：INDEX(a,b,c)可优化a=1、a=1 AND b=2、a=1 AND b=2 AND c=3
    • 等值查询列在前，范围查询列在后
    • 常用排序字段可加入索引

  3. 避免索引失效场景：

    -- 索引失效示例
SELECT * FROM employees WHERE LEFT(name, 1) = '张';  -- 函数操作
SELECT * FROM employees WHERE salary * 1.1 > 10000; -- 计算操作
SELECT * FROM employees WHERE name LIKE '%伟';      -- 前导通配符

  4. 使用索引提示：

    -- 强制使用索引
SELECT * FROM employees FORCE INDEX (idx_name) WHERE name = '张伟';-- 忽略索引
SELECT * FROM employees IGNORE INDEX (idx_salary) WHERE salary > 10000;-- 多索引选择
SELECT * FROM employees USE INDEX (idx_name, idx_dept) 
WHERE name = '张伟' OR department = '研发部';

  5. 定期维护：

    • 使用ANALYZE TABLE更新索引统计信息
    • 对大表考虑分区表设计
    • 监控慢查询日志调整索引策略
http://www.xdnf.cn/news/18810.html

相关文章：

  • 16、web应用系统分析语设计
  • 构建AI智能体：十二、给词语绘制地图：Embedding如何构建机器的认知空间
  • 基于Langchain框架的DeepSeek-v3+Faiss实现RAG知识问答系统（含完整代码）
  • 华为云Stack环境中计算资源，存储资源，网络资源发放前的准备工作（上篇）
  • wpf之Grid控件
  • 鸿蒙分布式计算实战：用 ArkTS+Worker 池落地可运行任务管理 Demo，从单设备到跨设备全方案
  • 07-分布式能力与多设备协同
  • JDBC入门
  • DAY 55 序列预测任务介绍
  • 小红书自动评论插件
  • JUC之并发容器
  • 深度学习与自动驾驶中的一些技术
  • Java基础（十四）分布式
  • KingBase数据库迁移利器：KDTS工具深度解析与实战指南
  • golang6 条件循环
  • 01-鸿蒙系统概览与发展历程
  • Android面试指南（五）
  • 青少年机器人技术（二级）等级考试试卷-实操题（2024年9月）
  • C语言文件操作精讲：从格式化读写到随机访问
  • GOLANG 接口
  • Axure：如何打开自定义操作界面
  • loj数列分块入门2-3
  • c++string
  • crypto.randomUUID is not a function
  • 拓扑排序|hash
  • frp+go-mmproxy 实现透明代理的内网穿透
  • Qt5 高级功能
  • 关于说明锂电池充电芯片实际应用
  • 曲面方程的三维可视化：从数学解析到Python实现
  • 从罗永浩访谈李想中学习现代家庭教育智慧