数据库操作

一、数据库的核心概念

1. 数据库（Database）
   • 数据的逻辑容器，用于分类管理数据。
   • 示例：一个电商系统可能包含 user_db（用户数据）、order_db（订单数据）等。
2. 表（Table）
   • 结构化数据的集合，由行（记录）和列（字段）组成。
   • 设计原则：遵循数据库范式（如第一范式：字段不可再分）。
3. 索引（Index）
   • 提高查询速度的数据结构，但会增加写操作的开销。
   • 类型：主键索引、唯一索引、普通索引、全文索引。

二、SQL 语句分类

1. DDL（Data Definition Language）
   • 定义数据库结构：CREATE, ALTER, DROP。
   • 示例：

ALTER TABLE users ADD INDEX idx_created_at (created_at); -- 添加索引

1. DML（Data Manipulation Language）
   • 操作数据：INSERT, UPDATE, DELETE, SELECT。
   • 示例：

UPDATE users SET status = 'inactive' WHERE last_login < '2023-01-01';

1. DCL（Data Control Language）
   • 控制权限：GRANT, REVOKE。
   • 示例：

GRANT SELECT, INSERT ON sales.* TO 'report_user'@'%';

1. TCL（Transaction Control Language）
   • 管理事务：COMMIT, ROLLBACK, SAVEPOINT

三、数据库操作

1. 创建数据库

CREATE DATABASE database_name;

1. 删除数据库

DROP DATABASE database_name;

1. 切换数据库

USE database_name;

四、表操作

1. 创建表

CREATE TABLE users (

  id INT AUTO_INCREMENT PRIMARY KEY,

  name VARCHAR(50) NOT NULL,

  email VARCHAR(100) UNIQUE,

  age INT DEFAULT 18,

  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

1. 删除表

DROP TABLE users;

1. 修改表结构

-- 添加列

ALTER TABLE users ADD COLUMN address VARCHAR(200);

-- 删除列

ALTER TABLE users DROP COLUMN age;

-- 修改列类型

ALTER TABLE users MODIFY COLUMN name VARCHAR(100);

1. 查看表结构

DESC users;

五、数据操作（CRUD）

1. 插入数据

INSERT INTO users (name, email, age)

VALUES ('张三', 'zhangsan@example.com', 25);

1. 查询数据

-- 查询所有字段

SELECT * FROM users;

-- 条件查询

SELECT name, email FROM users WHERE age > 20;

-- 排序

SELECT * FROM users ORDER BY created_at DESC;

-- 分页

SELECT * FROM users LIMIT 10 OFFSET 0; -- 第1页，每页10条

-- 聚合函数

SELECT COUNT(*) AS total_users FROM users;

SELECT AVG(age) FROM users;

-- 多表连接（JOIN）

SELECT orders.id, users.name

FROM orders

LEFT JOIN users ON orders.user_id = users.id;

1. 更新数据

UPDATE users

SET age = 26, email = 'new_email@example.com'

WHERE id = 1;

1. 删除数据

DELETE FROM users WHERE id = 1;

-- 清空表（谨慎使用！）

TRUNCATE TABLE users;

六、索引优化

1. 创建索引

CREATE INDEX idx_name ON users(name);

CREATE UNIQUE INDEX idx_email ON users(email);

1. 删除索引

DROP INDEX idx_name ON users;

七、事务管理

START TRANSACTION;

-- 执行操作（如插入、更新）

INSERT INTO orders (user_id, amount) VALUES (1, 100);

UPDATE users SET balance = balance - 100 WHERE id = 1;

-- 提交或回滚

COMMIT; -- 确认操作

ROLLBACK; -- 撤销操作

八.用户与权限

1. 创建用户

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password';

1. 授予权限

GRANT SELECT, INSERT ON database_name.* TO 'new_user'@'localhost';

1. 撤销权限

REVOKE DELETE ON database_name.* FROM 'user'@'localhost';

九.备份与恢复

1. 备份数据库

mysqldump -u username -p database_name > backup.sql

1. 恢复数据库

mysql -u username -p database_name < backup.sql

十、进阶操作

1. 存储过程

DELIMITER //

CREATE PROCEDURE GetUser(IN userId INT)

BEGIN

  SELECT * FROM users WHERE id = userId;

END //

DELIMITER ;

-- 调用存储过程

CALL GetUser(1);

1. 视图

CREATE VIEW user_summary AS

SELECT id, name, email FROM users WHERE age > 18;