MySQL 数据库操作

一、基础连接与环境操作

1. 连接 MySQL 服务器

bash

运行

# 本地连接（默认端口3306）
mysql -u 用户名 -p
# 示例：使用root用户连接
mysql -u root -p

# 远程连接（指定IP和端口）
mysql -h 服务器IP -P 端口号 -u 用户名 -p
# 示例：连接192.168.1.100的MySQL，端口3306
mysql -h 192.168.1.100 -P 3306 -u root -p

    2. 查看基础信

    sql

    -- 查看当前MySQL版本
SELECT VERSION();

-- 查看当前登录用户
SELECT USER();

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

    二、数据库（Database）

    1. 创建数据库

    sql

    -- 基础创建（指定字符集和排序规则，避免中文乱码）
CREATE DATABASE IF NOT EXISTS 数据库名 
CHARACTER SET utf8mb4  -- 推荐utf8mb4（兼容emoji，比utf8更完整）
COLLATE utf8mb4_unicode_ci;

-- 示例：创建名为test_db的数据库
CREATE DATABASE IF NOT EXISTS test_db 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

      2. 选择 / 使用数据库

      sql

      -- 切换到指定数据库（后续操作基于该库）
USE test_db;

      3. 修改数据库

      sql

      -- 修改数据库字符集
ALTER DATABASE test_db 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

      4. 删除数据库

      sql

      -- 谨慎操作！删除数据库（包含所有表和数据）
DROP DATABASE IF EXISTS test_db;

      三、数据表（Table）操作

      1. 创建数据表

      sql

      -- 示例：创建用户表（user）
CREATE TABLE IF NOT EXISTS user (
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键，自增
    username VARCHAR(50) NOT NULL UNIQUE,  -- 用户名，非空且唯一
    password VARCHAR(100) NOT NULL,  -- 密码（建议存储加密后的值）
    age TINYINT UNSIGNED DEFAULT 0,  -- 年龄，无符号，默认0
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP  -- 创建时间，默认当前时间
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

      2. 查看数据表

      sql

      -- 查看当前数据库下所有表
SHOW TABLES;

-- 查看表结构（字段名、类型、约束等）
DESC user;  -- 等价于 DESCRIBE user;

-- 查看表的创建语句（含引擎、字符集等）
SHOW CREATE TABLE user;

      3. 修改数据表

      sql

      -- 1. 添加字段（示例：给user表加email字段）
ALTER TABLE user 
ADD COLUMN email VARCHAR(100) UNIQUE AFTER password;

-- 2. 修改字段（示例：修改age字段类型和默认值）
ALTER TABLE user 
MODIFY COLUMN age INT UNSIGNED DEFAULT 18;

-- 3. 删除字段（示例：删除email字段）
ALTER TABLE user 
DROP COLUMN email;

-- 4. 修改表名
ALTER TABLE user 
RENAME TO t_user;

      4. 删除数据表

      sql

      -- 谨慎操作！删除表（包含所有数据）
DROP TABLE IF EXISTS user;

      四、数据（Data）操作（CRUD）

      1. 新增数据（CREATE）

      sql

      -- 1. 插入单条数据（指定字段）
INSERT INTO user (username, password, age) 
VALUES ('zhangsan', '123456', 20);

-- 2. 插入多条数据
INSERT INTO user (username, password, age) 
VALUES 
('lisi', '654321', 22),
('wangwu', '987654', 25);

-- 3. 插入所有字段（省略字段名，需按表结构顺序）
INSERT INTO user 
VALUES (NULL, 'zhaoliu', '111222', 19, '2026-03-19 10:00:00');
-- 主键自增时，对应位置填NULL即可，MySQL会自动生成值

      2. 查询数据（READ）

      sql

      -- 1. 查询所有字段（* 表示所有，生产环境尽量避免，指定具体字段更高效）
SELECT * FROM user;

-- 2. 查询指定字段
SELECT username, age FROM user;

-- 3. 条件查询（WHERE）
SELECT * FROM user WHERE age > 20;  -- 年龄大于20
SELECT * FROM user WHERE username = 'zhangsan';  -- 用户名等于zhangsan
SELECT * FROM user WHERE age BETWEEN 20 AND 25;  -- 年龄在20-25之间
SELECT * FROM user WHERE username LIKE 'z%';  -- 用户名以z开头（模糊查询）

-- 4. 排序（ORDER BY）：ASC升序（默认），DESC降序
SELECT * FROM user ORDER BY age DESC;

-- 5. 分页查询（LIMIT）：LIMIT 起始位置, 条数（起始位置从0开始）
SELECT * FROM user LIMIT 0, 2;  -- 第1页，每页2条
SELECT * FROM user LIMIT 2, 2;  -- 第2页，每页2条

-- 6. 去重查询（DISTINCT）
SELECT DISTINCT age FROM user;  -- 去重后查询年龄

-- 7. 聚合查询（常用函数：COUNT/SUM/AVG/MAX/MIN）
SELECT COUNT(*) FROM user;  -- 统计总条数
SELECT AVG(age) FROM user;  -- 平均年龄
SELECT MAX(age) FROM user;  -- 最大年龄

      3. 更新数据（UPDATE）

      sql

      -- 修改单条数据（必须加WHERE，否则修改全表！）
UPDATE user 
SET password = '888888', age = 21 
WHERE username = 'zhangsan';

-- 批量修改（示例：所有年龄<20的用户，年龄+1）
UPDATE user 
SET age = age + 1 
WHERE age < 20;

      4. 删除数据（DELETE）

      sql

      -- 删除指定数据（必须加WHERE，否则删除全表！）
DELETE FROM user WHERE username = 'wangwu';

-- 清空表（保留表结构，自增主键重置为1）
TRUNCATE TABLE user;

      五、权限与用户管理

      1. 创建用户

      sql

      -- 创建用户（仅允许本地访问）
CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'test123';

-- 创建用户（允许远程访问，% 表示任意IP）
CREATE USER 'test_user'@'%' IDENTIFIED BY 'test123';

      2. 授权

      sql

      -- 授权：test_user拥有test_db所有表的所有权限
GRANT ALL PRIVILEGES ON test_db.* TO 'test_user'@'%';

-- 授权：仅允许查询和插入test_db的user表
GRANT SELECT, INSERT ON test_db.user TO 'test_user'@'%';

-- 刷新权限（授权后必须执行）
FLUSH PRIVILEGES;

      3. 撤销权限

      sql

      REVOKE INSERT ON test_db.user FROM 'test_user'@'%';
FLUSH PRIVILEGES;

      4. 修改密码

      sql

      ALTER USER 'test_user'@'%' IDENTIFIED BY 'new_test123';
FLUSH PRIVILEGES;

      5. 删除用户

      sql

      DROP USER 'test_user'@'%';

      六、常用进阶操作

      1. 事务操作（保证数据一致性）

      sql

      -- 开启事务
START TRANSACTION;

-- 执行操作（示例：转账）
UPDATE user SET balance = balance - 100 WHERE id = 1;
UPDATE user SET balance = balance + 100 WHERE id = 2;

-- 提交事务（确认操作）
COMMIT;

-- 回滚事务（出错时撤销操作）
-- ROLLBACK;

      2. 索引（提升查询效率）

      sql

      -- 创建索引（示例：给user表的username字段加索引）
CREATE INDEX idx_user_username ON user(username);

-- 查看索引
SHOW INDEX FROM user;

-- 删除索引
DROP INDEX idx_user_username ON user;

      总结

      1. 核心操作分层：数据库（库）→ 数据表（表）→ 数据（行），操作遵循「先选库，再操作表 / 数据」的逻辑；
      2. 安全操作原则：UPDATE/DELETE 必须加 WHERE 条件，DROP/TRUNCATE 谨慎执行，重要操作前建议备份；
      3. 最佳实践：字符集优先用 utf8mb4，存储引擎用 InnoDB，查询避免 SELECT *，高频查询字段加索引。
      # oracle # 数据库
      Logo
      https://edu.csdn.net/learn/39067/627173?utm_source=2019755004

      汇聚全球AI编程工具，助力开发者即刻编程。

      更多推荐

      cover

      一个 API 入口调用多个大模型：AiiOnly客户端 + CC Switch 打通 Codex AI编程流程

      avatar AI编程社区
      cover

      VSCode Claude Code + MiMo 中转免登录完整配置教程（解决原生登录弹窗 + 模型报错）

      avatar AI编程社区
      cover

      从零开始做一个属于自己的 Skill（保姆级教程）

      avatar AI编程社区