MySQL数据库入门：从安装到增删改查

# Ubuntu/Debian
sudo apt install mysql-server

# macOS (Homebrew)
brew install mysql

# 启动服务
sudo systemctl start mysql
sudo systemctl enable mysql

-- 创建数据库
CREATE DATABASE mydb CHARACTER SET utf8mb4;

-- 查看所有数据库
SHOW DATABASES;

-- 选择数据库
USE mydb;

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

-- 查看表结构
DESC users;
SHOW CREATE TABLE users;

-- 插入数据
INSERT INTO users (username, email, age) 
VALUES ('小明', 'xiaoming@example.com', 25);

INSERT INTO users (username, email, age) VALUES
('小红', 'xiaohong@example.com', 23),
('小华', 'xiaohua@example.com', 28);

-- 查询数据
SELECT * FROM users;
SELECT username, email FROM users WHERE age > 20;
SELECT * FROM users ORDER BY age DESC LIMIT 10;

-- 更新数据
UPDATE users SET age = 26 WHERE username = '小明';

-- 删除数据
DELETE FROM users WHERE id = 3;

-- 聚合函数
SELECT COUNT(*) FROM users;
SELECT AVG(age) FROM users;
SELECT MAX(age), MIN(age) FROM users;

-- 分组查询
SELECT age, COUNT(*) as cnt 
FROM users 
GROUP BY age 
HAVING cnt > 1;

-- 多表关联（需要先创建 orders 表）
SELECT u.username, o.total 
FROM users u 
JOIN orders o ON u.id = o.user_id;

-- 子查询
SELECT * FROM users 
WHERE age > (SELECT AVG(age) FROM users);

-- 创建索引
CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_email ON users(email);

-- 查看索引
SHOW INDEX FROM users;

-- 分析查询性能
EXPLAIN SELECT * FROM users WHERE username = '小明';

# 备份
mysqldump -u root -p mydb > backup.sql

# 恢复
mysql -u root -p mydb < backup.sql