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 -p2. 查看基础信sql-- 查看当前MySQL版本 SELECT VERSION(); -- 查看当前登录用户 SELECT USER(); -- 查看所有数据库 SHOW DATABASES;二、数据库Database1. 创建数据库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 -- 创建时间默认当前时间 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;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操作CRUD1. 新增数据CREATEsql-- 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. 查询数据READsql-- 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 BYASC升序默认DESC降序 SELECT * FROM user ORDER BY age DESC; -- 5. 分页查询LIMITLIMIT 起始位置, 条数起始位置从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. 更新数据UPDATEsql-- 修改单条数据必须加WHERE否则修改全表 UPDATE user SET password 888888, age 21 WHERE username zhangsan; -- 批量修改示例所有年龄20的用户年龄1 UPDATE user SET age age 1 WHERE age 20;4. 删除数据DELETEsql-- 删除指定数据必须加WHERE否则删除全表 DELETE FROM user WHERE username wangwu; -- 清空表保留表结构自增主键重置为1 TRUNCATE TABLE user;五、权限与用户管理1. 创建用户sql-- 创建用户仅允许本地访问 CREATE USER test_userlocalhost 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. 撤销权限sqlREVOKE INSERT ON test_db.user FROM test_user%; FLUSH PRIVILEGES;4. 修改密码sqlALTER USER test_user% IDENTIFIED BY new_test123; FLUSH PRIVILEGES;5. 删除用户sqlDROP 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;总结核心操作分层数据库库→ 数据表表→ 数据行操作遵循「先选库再操作表 / 数据」的逻辑安全操作原则UPDATE/DELETE 必须加 WHERE 条件DROP/TRUNCATE 谨慎执行重要操作前建议备份最佳实践字符集优先用 utf8mb4存储引擎用 InnoDB查询避免SELECT *高频查询字段加索引。
MySQL 数据库操作笔记
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 -p2. 查看基础信sql-- 查看当前MySQL版本 SELECT VERSION(); -- 查看当前登录用户 SELECT USER(); -- 查看所有数据库 SHOW DATABASES;二、数据库Database1. 创建数据库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 -- 创建时间默认当前时间 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;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操作CRUD1. 新增数据CREATEsql-- 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. 查询数据READsql-- 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 BYASC升序默认DESC降序 SELECT * FROM user ORDER BY age DESC; -- 5. 分页查询LIMITLIMIT 起始位置, 条数起始位置从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. 更新数据UPDATEsql-- 修改单条数据必须加WHERE否则修改全表 UPDATE user SET password 888888, age 21 WHERE username zhangsan; -- 批量修改示例所有年龄20的用户年龄1 UPDATE user SET age age 1 WHERE age 20;4. 删除数据DELETEsql-- 删除指定数据必须加WHERE否则删除全表 DELETE FROM user WHERE username wangwu; -- 清空表保留表结构自增主键重置为1 TRUNCATE TABLE user;五、权限与用户管理1. 创建用户sql-- 创建用户仅允许本地访问 CREATE USER test_userlocalhost 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. 撤销权限sqlREVOKE INSERT ON test_db.user FROM test_user%; FLUSH PRIVILEGES;4. 修改密码sqlALTER USER test_user% IDENTIFIED BY new_test123; FLUSH PRIVILEGES;5. 删除用户sqlDROP 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;总结核心操作分层数据库库→ 数据表表→ 数据行操作遵循「先选库再操作表 / 数据」的逻辑安全操作原则UPDATE/DELETE 必须加 WHERE 条件DROP/TRUNCATE 谨慎执行重要操作前建议备份最佳实践字符集优先用 utf8mb4存储引擎用 InnoDB查询避免SELECT *高频查询字段加索引。
