学习数据库的增删改查

一、创建数据库和表

在进行增删改查操作之前，我们需要创建一个数据库和表。

1. 创建数据库

使用 CREATE DATABASE 语句创建数据库：

CREATE DATABASE test_db;

2. 选择数据库

使用 USE 语句选择数据库：

USE test_db;

3. 创建表

使用 CREATE TABLE 语句创建一个名为 users 的表：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

二、插入数据（INSERT）

使用 INSERT INTO 语句向表中插入数据：

INSERT INTO users (username, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (username, email) VALUES ('Bob', 'bob@example.com');

可以一次插入多条记录：

INSERT INTO users (username, email) VALUES 
('Charlie', 'charlie@example.com'),
('Dave', 'dave@example.com');

三、查询数据（SELECT）

使用 SELECT 语句从表中查询数据：

SELECT * FROM users;

查询特定列的数据：

SELECT username, email FROM users;

添加条件查询：

SELECT * FROM users WHERE username = 'Alice';

使用 LIKE 进行模糊查询：

SELECT * FROM users WHERE email LIKE '%example.com';

排序查询结果：

SELECT * FROM users ORDER BY created_at DESC;

四、更新数据（UPDATE）

使用 UPDATE 语句更新表中的数据：

UPDATE users SET email = 'alice_new@example.com' WHERE username = 'Alice';

更新多列的数据：

UPDATE users SET email = 'bob_new@example.com', username = 'Bobby' WHERE id = 2;

五、删除数据（DELETE）

使用 DELETE FROM 语句删除表中的数据：

DELETE FROM users WHERE username = 'Charlie';

删除所有数据：

DELETE FROM users;

注意：删除所有数据不会重置自增列（AUTO_INCREMENT），如果需要重置，可以使用：

TRUNCATE TABLE users;

六、实用操作

1. 插入或更新（INSERT … ON DUPLICATE KEY UPDATE）

当插入数据时，如果遇到主键冲突，可以选择更新数据：

INSERT INTO users (id, username, email) VALUES (1, 'Alice', 'alice@example.com')
ON DUPLICATE KEY UPDATE email = 'alice_new@example.com';

2. 计算查询（COUNT、SUM、AVG 等）

使用聚合函数进行计算：

SELECT COUNT(*) FROM users;
SELECT AVG(id) FROM users;

3. 分页查询

使用 LIMIT 和 OFFSET 实现分页：

SELECT * FROM users LIMIT 10 OFFSET 20;

4. 连接查询（JOIN）

使用 JOIN 语句连接多表查询：

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    amount DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

SELECT users.username, orders.order_date, orders.amount
FROM users
JOIN orders ON users.id = orders.user_id;

七、索引和性能优化

1. 创建索引

为提高查询效率，可以创建索引：

CREATE INDEX idx_username ON users(username);

2. 查看执行计划

使用 EXPLAIN 查看查询的执行计划：

EXPLAIN SELECT * FROM users WHERE username = 'Alice';

八、事务处理

使用事务可以保证一组操作的原子性：

START TRANSACTION;

INSERT INTO users (username, email) VALUES ('Eve', 'eve@example.com');
UPDATE users SET email = 'eve_new@example.com' WHERE username = 'Eve';

COMMIT;

在需要回滚时使用 ROLLBACK：

START TRANSACTION;

INSERT INTO users (username, email) VALUES ('Frank', 'frank@example.com');
-- 出现错误，回滚
ROLLBACK;

九、复杂查询

1. 子查询

子查询是在一个查询中嵌套另一个查询，用于复杂的数据检索需求：

SELECT username FROM users WHERE id = (SELECT MAX(id) FROM users);

2. 联合查询（UNION）

使用 UNION 将两个或多个查询的结果合并成一个结果集：

SELECT username FROM users WHERE id < 3
UNION
SELECT username FROM users WHERE id > 3;

3. 分组查询（GROUP BY）

使用 GROUP BY 进行分组查询，并结合聚合函数使用：

SELECT username, COUNT(*) AS order_count
FROM users
JOIN orders ON users.id = orders.user_id
GROUP BY username;

4. HAVING 过滤

HAVING 子句用于过滤分组后的结果：

SELECT username, COUNT(*) AS order_count
FROM users
JOIN orders ON users.id = orders.user_id
GROUP BY username
HAVING order_count > 1;

十、视图（VIEW）

1. 创建视图

视图是一种虚拟表，用于简化复杂的查询：

CREATE VIEW user_orders AS
SELECT users.username, orders.order_date, orders.amount
FROM users
JOIN orders ON users.id = orders.user_id;

2. 查询视图

使用视图进行查询，就像查询普通表一样：

SELECT * FROM user_orders;

3. 更新视图

视图的数据通常是只读的，但在某些情况下，可以通过视图更新数据：

UPDATE user_orders SET amount = 100.00 WHERE username = 'Alice';

十一、存储过程和函数

1. 创建存储过程

存储过程是一组预编译的 SQL 语句，用于封装复杂的操作：

DELIMITER //

CREATE PROCEDURE add_user(IN uname VARCHAR(50), IN uemail VARCHAR(50))
BEGIN
    INSERT INTO users (username, email) VALUES (uname, uemail);
END //

DELIMITER ;

2. 调用存储过程

CALL add_user('George', 'george@example.com');

3. 创建存储函数

存储函数是可以返回值的 SQL 代码块：

DELIMITER //

CREATE FUNCTION get_user_count() RETURNS INT
BEGIN
    DECLARE count INT;
    SELECT COUNT(*) INTO count FROM users;
    RETURN count;
END //

DELIMITER ;

4. 调用存储函数

SELECT get_user_count();

十二、触发器（TRIGGER）

1. 创建触发器

触发器是在特定事件发生时自动执行的 SQL 代码块：

CREATE TRIGGER before_insert_user
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    IF NEW.username = '' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Username cannot be empty';
    END IF;
END;

十三、用户权限管理

1. 创建用户

使用 CREATE USER 语句创建新用户：

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

2. 授予权限

使用 GRANT 语句授予用户权限：

GRANT SELECT, INSERT, UPDATE ON test_db.* TO 'newuser'@'localhost';

3. 撤销权限

使用 REVOKE 语句撤销用户权限：

REVOKE INSERT ON test_db.* FROM 'newuser'@'localhost';

4. 删除用户

使用 DROP USER 语句删除用户：

DROP USER 'newuser'@'localhost';

十四、备份与恢复

1. 数据库备份

使用 mysqldump 工具备份数据库：

mysqldump -u root -p test_db > test_db_backup.sql

2. 数据库恢复

使用 mysql 命令恢复数据库：

mysql -u root -p test_db < test_db_backup.sql

十五、性能优化

1. 查询优化

使用 EXPLAIN 分析查询计划，识别性能瓶颈：

EXPLAIN SELECT * FROM users WHERE username = 'Alice';

2. 索引优化

合理创建和使用索引可以显著提高查询性能：

CREATE INDEX idx_email ON users(email);

3. 表分区

使用表分区将大表分割为多个小表，提高查询性能和管理效率：

CREATE TABLE orders (
    id INT AUTO_INCREMENT,
    user_id INT,
    order_date DATE,
    amount DECIMAL(10, 2),
    PRIMARY KEY (id, order_date)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p0 VALUES LESS THAN (2021),
    PARTITION p1 VALUES LESS THAN (2022),
    PARTITION p2 VALUES LESS THAN (2023)
);

4. 配置优化

调整 MySQL 配置文件（my.cnf 或 my.ini）中的参数以优化性能，例如：

[mysqld]
innodb_buffer_pool_size = 1G
query_cache_size = 128M

十六、实际案例

案例1：用户管理系统

在用户管理系统中，我们需要进行用户信息的增删改查操作。以下是具体的实现过程。

1. 创建用户表

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(100) NOT NULL,
    email VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. 插入用户数据

INSERT INTO users (username, password, email) VALUES ('JohnDoe', 'password123', 'john@example.com');
INSERT INTO users (username, password, email) VALUES ('JaneDoe', 'password456', 'jane@example.com');

3. 查询用户数据

SELECT * FROM users WHERE username = 'JohnDoe';

4. 更新用户数据

UPDATE users SET email = 'john.doe@example.com' WHERE username = 'JohnDoe';

5. 删除用户数据

DELETE FROM users WHERE username = 'JaneDoe';

案例2：订单管理系统

在订单管理系统中，我们需要对订单信息进行管理，包括创建订单、查询订单、更新订单状态和删除订单。

1. 创建订单表

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    product_name VARCHAR(100) NOT NULL,
    quantity INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'Pending'
);

2. 插入订单数据

INSERT INTO orders (user_id, product_name, quantity) VALUES (1, 'Laptop', 1);
INSERT INTO orders (user_id, product_name, quantity) VALUES (1, 'Mouse', 2);

3. 查询订单数据

SELECT * FROM orders WHERE user_id = 1;

4. 更新订单状态

UPDATE orders SET status = 'Shipped' WHERE id = 1;

5. 删除订单数据

DELETE FROM orders WHERE id = 2;

十七、数据库安全

1. 数据库备份策略

定期备份数据库以防止数据丢失：

mysqldump -u root -p test_db > /backups/test_db_backup_$(date +%F).sql

2. 数据库恢复策略

在数据丢失或损坏时，使用备份文件进行恢复：

mysql -u root -p test_db < /backups/test_db_backup_2024-07-09.sql

3. 用户权限管理

授予用户最小权限原则，确保数据库安全：

GRANT SELECT, INSERT ON test_db.users TO 'appuser'@'localhost';
REVOKE DELETE ON test_db.users FROM 'appuser'@'localhost';

4. 数据加密

在存储敏感数据时进行加密，例如用户密码：

INSERT INTO users (username, password, email) VALUES ('JohnDoe', SHA2('password123', 256), 'john@example.com');

5. 审计日志

启用审计日志，记录用户的操作以便于审计和追踪：

SET GLOBAL audit_log_policy = 'ALL';

十八、性能调优

1. 查询优化

分析查询语句并创建适当的索引，以提高查询性能：

EXPLAIN SELECT * FROM users WHERE email = 'john.doe@example.com';
CREATE INDEX idx_email ON users(email);

2. 缓存机制

启用查询缓存，减少对数据库的直接访问：

SET GLOBAL query_cache_size = 128M;

3. 分区表

对于大数据量的表，使用分区提高查询效率：

CREATE TABLE log_data (
    id INT AUTO_INCREMENT,
    log_message TEXT,
    log_date DATE,
    PRIMARY KEY (id, log_date)
) PARTITION BY RANGE (YEAR(log_date)) (
    PARTITION p0 VALUES LESS THAN (2021),
    PARTITION p1 VALUES LESS THAN (2022),
    PARTITION p2 VALUES LESS THAN (2023)
);

4. 监控和调试

使用性能监控工具（如 MySQL Enterprise Monitor）监控数据库性能，并根据分析结果进行优化。

十九、数据迁移

在进行数据迁移时，保证数据的一致性和完整性：

1. 数据导出

将数据从一个数据库导出到文件：

mysqldump -u root -p --databases source_db > source_db_backup.sql

2. 数据导入

将导出的数据导入到新的数据库：

mysql -u root -p target_db < source_db_backup.sql

3. 数据验证

在数据迁移完成后，进行数据验证以确保数据完整性：

SELECT COUNT(*) FROM source_db.users;
SELECT COUNT(*) FROM target_db.users;

结语

本文详细介绍了 MySQL 5.7 中的增删改查操作，并通过实际案例和高级操作，进一步展示了如何高效管理和优化数据库。希望通过本文，读者能够全面掌握 MySQL 5.7 的使用技巧，并在实际应用中灵活运用所学知识，提高数据库系统的效率和可靠性。掌握这些技巧不仅能提升工作效率，还能为系统的稳定性和安全性提供有力保障。