MySQL CRUD 操作

数据库操作

1. 创建数据库

-- 创建数据库
CREATE DATABASE database_name;

-- 创建数据库并指定字符集
CREATE DATABASE database_name
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

2. 选择数据库

-- 使用数据库
USE database_name;

-- 查看当前数据库
SELECT DATABASE();

3. 删除数据库

-- 删除数据库
DROP DATABASE database_name;

表操作

1. 创建表

-- 创建表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT CHECK (age >= 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. 修改表结构

-- 添加列
ALTER TABLE users
ADD COLUMN phone VARCHAR(20);

-- 修改列
ALTER TABLE users
MODIFY COLUMN phone VARCHAR(30);

-- 删除列
ALTER TABLE users
DROP COLUMN phone;

3. 删除表

-- 删除表
DROP TABLE table_name;

-- 清空表数据
TRUNCATE TABLE table_name;

数据操作

1. 插入数据

-- 插入单条数据
INSERT INTO users (username, email, age)
VALUES ('john_doe', 'john@example.com', 25);

-- 插入多条数据
INSERT INTO users (username, email, age) VALUES
('jane_doe', 'jane@example.com', 22),
('bob_smith', 'bob@example.com', 30);

2. 查询数据

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

-- 查询指定列
SELECT username, email FROM users;

-- 条件查询
SELECT * FROM users
WHERE age >= 25;

-- 排序
SELECT * FROM users
ORDER BY age DESC;

-- 分页
SELECT * FROM users
LIMIT 10 OFFSET 0;

3. 更新数据

-- 更新单个字段
UPDATE users
SET age = 26
WHERE username = 'john_doe';

-- 更新多个字段
UPDATE users
SET age = 27, email = 'john.new@example.com'
WHERE username = 'john_doe';

4. 删除数据

-- 删除指定记录
DELETE FROM users
WHERE username = 'john_doe';

-- 删除所有记录
DELETE FROM users;

高级查询

1. 聚合函数

-- 计数
SELECT COUNT(*) FROM users;

-- 平均值
SELECT AVG(age) FROM users;

-- 最大值和最小值
SELECT MAX(age), MIN(age) FROM users;

2. 分组查询

-- 按年龄分组统计
SELECT age, COUNT(*) as count
FROM users
GROUP BY age;

-- 分组后过滤
SELECT age, COUNT(*) as count
FROM users
GROUP BY age
HAVING count > 1;

3. 连接查询

-- 内连接
SELECT users.username, orders.order_date
FROM users
INNER JOIN orders ON users.id = orders.user_id;

-- 左连接
SELECT users.username, orders.order_date
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

最佳实践

1. 使用事务

   -- 开始事务
   START TRANSACTION;
   
   -- 执行操作
   INSERT INTO users (username, email) VALUES ('test', 'test@example.com');
   UPDATE orders SET status = 'paid' WHERE user_id = 1;
   
   -- 提交或回滚
   COMMIT;
   -- ROLLBACK;
   

2. 使用预处理语句

   PREPARE stmt FROM 'SELECT * FROM users WHERE age > ?';
   SET @age = 25;
   EXECUTE stmt USING @age;
   DEALLOCATE PREPARE stmt;
   

3. 正确使用索引

   -- 创建索引
   CREATE INDEX idx_username ON users(username);
   
   -- 使用索引的查询
   SELECT * FROM users WHERE username = 'john_doe';
   

练习题

1. 创建一个包含多个表的数据库
2. 编写各种CRUD操作的SQL语句
3. 实现复杂的查询需求

下一步

学习更多高级查询技术，包括子查询、视图和存储过程等内容。