🔧

MySQL 入门基础

📂 devops ⏱ 3 min 596 words

MySQL 入门基础

什么是 MySQL

MySQL 是一个开源的关系型数据库管理系统（RDBMS），广泛用于 Web 应用开发。它支持标准 SQL 语法，具有高性能、高可靠性和易用性。

安装 MySQL

Ubuntu/Debian

sudo apt update
sudo apt install mysql-server

# 安全初始化
sudo mysql_secure_installation

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

CentOS/RHEL

sudo yum install mysql-server

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

# 获取临时密码
sudo grep 'temporary password' /var/log/mysqld.log

# 安全初始化
mysql_secure_installation

macOS

brew install mysql

# 启动服务
brew services start mysql

基本操作

连接 MySQL

# 连接到本地 MySQL
mysql -u root -p

# 连接到远程 MySQL
mysql -h 192.168.1.100 -u root -p

# 指定端口
mysql -h 192.168.1.100 -P 3306 -u root -p

基本 SQL 命令

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

-- 创建数据库
CREATE DATABASE mydb;

-- 使用数据库
USE mydb;

-- 查看表
SHOW TABLES;

-- 查看表结构
DESC users;

-- 查看创建表的 SQL
SHOW CREATE TABLE users;

数据类型

常用数据类型

-- 数值类型
INT              -- 整数
BIGINT           -- 大整数
FLOAT            -- 浮点数
DOUBLE           -- 双精度浮点数
DECIMAL(10,2)    -- 精确小数

-- 字符串类型
VARCHAR(255)     -- 可变长度字符串
TEXT             -- 长文本
CHAR(10)         -- 固定长度字符串

-- 日期时间类型
DATE             -- 日期
TIME             -- 时间
DATETIME         -- 日期时间
TIMESTAMP        -- 时间戳

创建表

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

CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CRUD 操作

插入数据

-- 单条插入
INSERT INTO users (username, email, password)
VALUES ('john', 'john@example.com', 'hashed_password');

-- 多条插入
INSERT INTO users (username, email, password)
VALUES 
    ('alice', 'alice@example.com', 'hashed_password1'),
    ('bob', 'bob@example.com', 'hashed_password2');

查询数据

-- 基本查询
SELECT * FROM users;

-- 条件查询
SELECT * FROM users WHERE username = 'john';

-- 多条件查询
SELECT * FROM users WHERE id > 1 AND email LIKE '%@example.com';

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

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

-- 聚合函数
SELECT COUNT(*) as total FROM users;
SELECT AVG(id) as avg_id FROM users;

-- 分组
SELECT user_id, COUNT(*) as post_count 
FROM posts 
GROUP BY user_id;

更新数据

-- 更新单条
UPDATE users SET email = 'newemail@example.com' WHERE id = 1;

-- 更新多条
UPDATE users SET status = 'inactive' WHERE last_login < '2023-01-01';

删除数据

-- 删除单条
DELETE FROM users WHERE id = 1;

-- 删除多条
DELETE FROM users WHERE status = 'inactive';

-- 清空表
TRUNCATE TABLE users;

索引

创建索引

-- 创建普通索引
CREATE INDEX idx_username ON users(username);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

-- 创建组合索引
CREATE INDEX idx_user_status ON users(user_id, status);

查看索引

SHOW INDEX FROM users;

删除索引

DROP INDEX idx_username ON users;

用户管理

创建用户

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'newuser'@'%' IDENTIFIED BY 'password';  -- 允许远程访问

授权

-- 授予所有权限
GRANT ALL PRIVILEGES ON mydb.* TO 'newuser'@'localhost';

-- 授予特定权限
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'newuser'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

撤销权限

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

删除用户

DROP USER 'newuser'@'localhost';

备份与恢复

备份数据库

# 备份单个数据库
mysqldump -u root -p mydb > mydb_backup.sql

# 备份所有数据库
mysqldump -u root -p --all-databases > all_databases.sql

# 备份特定表
mysqldump -u root -p mydb users posts > tables_backup.sql

恢复数据库

# 恢复数据库
mysql -u root -p mydb < mydb_backup.sql

# 恢复所有数据库
mysql -u root -p < all_databases.sql

配置优化

my.cnf 配置

[mysqld]
# 基本设置
max_connections = 1000
max_allowed_packet = 64M
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# InnoDB 设置
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1

# 慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

常见问题

忘记 root 密码

# 停止 MySQL
sudo systemctl stop mysql

# 以安全模式启动
sudo mysqld_safe --skip-grant-tables

# 连接 MySQL
mysql -u root

# 修改密码
UPDATE mysql.user SET authentication_string=PASSWORD('newpassword') WHERE User='root';
FLUSH PRIVILEGES;

连接被拒绝

# 检查 MySQL 服务状态
sudo systemctl status mysql

# 检查端口监听
netstat -tlnp | grep 3306

# 检查用户权限
SELECT user, host FROM mysql.user;

最佳实践

使用 InnoDB 存储引擎
合理设计表结构和索引
定期备份数据
使用参数化查询防止 SQL 注入
监控慢查询日志

总结

MySQL 是最流行的关系型数据库之一。掌握基本的 SQL 语法、表设计和优化技巧是开发高质量 Web 应用的基础。