学习数据库的增删改查

发布于:2024-07-11 ⋅ 阅读:(32) ⋅ 点赞:(0)

一、创建数据库和表

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

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. 分页查询

使用 LIMITOFFSET 实现分页:

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.cnfmy.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 的使用技巧,并在实际应用中灵活运用所学知识,提高数据库系统的效率和可靠性。掌握这些技巧不仅能提升工作效率,还能为系统的稳定性和安全性提供有力保障。