{"msg":"操作成功","code":200,"data":{"createBy":"admin","createTime":"2020-03-09 21:27:49","updateBy":"admin","updateTime":"2025-06-04 12:38:11","remark":null,"id":18,"articleTitle":"MySQL（二）常用操作","articleUrl":"mysql_cmd","articleThumbnail":"https://www.asumimoe.com/imgfiles/20220908/9e192744fab244d39d3b15fca035c9f7.jpg","articleFlag":"0","draftStatus":"1","reprintStatement":"1","articleSummary":"结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。","articleContent":"## 数据库操作\n\n### 1.查看数据库\n\n```sql\n1）SHOW DATABASES;     --查看所有数据库\n2）SELECT VERSION();     --查看数据库版本\n3）USE db_name    --使用对应数据流库\n4）SELECT DATABASE() --查看当前在哪个数据库\n6）SHOW TABLES    --查看当前数据库下的所有表\n```\n\n### 2.创建数据库\n\n```sql\n-- 创建基础数据库\nCREATE DATABASE company_db;\n\n-- 创建数据库并指定字符集\nCREATE DATABASE company_db CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;\n\n-- 查看创建数据库的语句\nSHOW CREATE DATABASE company_db;\n```\n\n### 3.修改数据库\n\n```sql\n-- 修改数据库字符集\nALTER DATABASE company_db CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;\n```\n\n### 4.删除数据库\n\n```sql\n-- 删除数据库（不可逆操作）\nDROP DATABASE legacy_db;\n\n-- 安全删除（先检查存在性）\nDROP DATABASE IF EXISTS temp_db;\n```\n\n## 数据类型\n\n| **数值类型**     |          |                                                     |                                                      |\n| ---------------- | -------- | --------------------------------------------------- | ---------------------------------------------------- |\n| 类型             | 字节大小 | 有符号范围                                          | 无符号范围                                           |\n| TINYINT          | 1        | -127~127                                            | 0~255                                                |\n| SMALLINT         | 2        | -32768~32767                                        | 0~65535                                              |\n| MEDIUMINT        | 3        | -8388608~8388607                                    | 0~16777215                                           |\n| INT/INTEGER      | 4        | -2147483648~2147483647                              | 0~4294967265                                         |\n| BIGINT           | 8        | -9223372036854775808~-9223372036854775807           | 0~18446744073709551615                               |\n| **字符串**       |          |                                                     |                                                      |\n| 类型             | 字节大小 | 示例                                                |                                                      |\n| CHAR             | 0-255    | char(3)不管输入几个字节都会占3个字节                |                                                      |\n| VARCHAR          | 0-255    | varchar(3)输入比三小的字节会占用实际字节大小        |                                                      |\n| TEXT             | 0-65535  | 大文本                                              |                                                      |\n| **日期时间类型** |          |                                                     |                                                      |\n| DATE             | 4        | '2020-01-01'                                        |                                                      |\n| TIME             | 3        | '12:05:34'                                          |                                                      |\n| DATETIME         | 8        | '2020-01-01 12:05:34’                               | DATETIME(3)表示精确到毫秒；DATETIME(6)表示精确到微秒 |\n| YEAR             | 1        | '2019'                                              |                                                      |\n| TIMESTAMP        | 4        | '1970-01-01   00:00:01’UTC~‘2038-01-01 00:00:01’UTC |                                                      |\n\n## 表操作\n\n### 1.创建表\n\n```sql\ncreate table employees (\n    id int unsigned not null auto_increment primary key, -- 整数、非负数、非空、自增、主键\n    name varchar(20),\n    age tinyint unsigned default 0,\n    high decimal(5,2), -- 五位数字，精确度两位小数\n    gender enum('男', '女', '中性', '保密') default '保密', -- 枚举类型，插入数据仅能在列出的范围中\n    dept_id int unsigned\n);\n```\n\n### 2.修改表\n\n```sql\n-- 添加新列\nALTER TABLE employees ADD COLUMN phone VARCHAR(15) AFTER gender;\n\n-- 修改列类型\nALTER TABLE employees MODIFY COLUMN high DECIMAL(6,2);\n\n-- 重命名列\nALTER TABLE employees CHANGE COLUMN phone mobile_phone VARCHAR(15);\n\n-- 删除列\nALTER TABLE employees DROP COLUMN mobile_phone;\n\n-- 添加索引\nALTER TABLE employees ADD INDEX idx_name (hire_date);\n\n-- 添加外键约束\nALTER TABLE employees \nADD CONSTRAINT fk_department\nFOREIGN KEY (dept_id) REFERENCES departments(id)\nON DELETE SET NULL;\n```\n\n### 3.查看表结构\n\n```sql\n-- 查看表结构\nDESCRIBE employees;\n\n-- 查看建表语句\nSHOW CREATE TABLE employees;\n```\n\n### 4.删除表\n\n```sql\n-- 删除表\nDROP TABLE temp_table;\n\n-- 安全删除（先检查存在性）\nDROP TABLE IF EXISTS backup_table;\n```\n\n## 用户操作\n\n### 1.创建用户\n\n```sql\n-- 创建用户并设置密码，并且只能在本机登录，无法在远程登录\nCREATE USER 'username'@'host' IDENTIFIED BY 'password';\n```\n\n- `'username'`: 要创建的用户名。\n- `'host'`: 指定该用户可以从哪台主机连接到 MySQL 服务器。常用值：\n  - `'localhost'`: 仅允许从 MySQL 服务器本机连接。\n  - `'%'`: 允许从任何主机连接（**谨慎使用，存在安全风险**）。\n  - `'192.168.1.%'`: 允许从 `192.168.1.0/24` 网段连接。\n  - `'specific_ip'`: 允许从特定 IP 地址连接 (e.g., `'192.168.1.100'`).\n- `'password'`: 用户的密码（明文）。MySQL 会自动加密存储。\n\n- **注意 (MySQL 8.0+):** 在 MySQL 8.0 中，`CREATE USER` 和 `GRANT` 语句被更严格地分离。`CREATE USER` 通常只创建账户，权限需要用 `GRANT` 单独赋予（见下文权限管理部分）。早期版本有时可以在 `GRANT` 时隐式创建用户（取决于 `sql_mode` 设置），但显式使用 `CREATE USER` 是推荐做法。\n\n### 2.修改用户\n\n```sql\n-- 修改连接主机限制\n-- 将 'reportuser' 从特定网段改为只能从特定 IP 连接\nRENAME USER 'reportuser'@'192.168.10.%' TO 'reportuser'@'192.168.10.50';\n\n-- 锁定/解锁用户\n-- 锁定账户 (禁止登录)\nALTER USER 'username'@'host' ACCOUNT LOCK;\n-- 解锁账户\nALTER USER 'username'@'host' ACCOUNT UNLOCK;\n```\n\n**修改用户密码**\n\n```sql\n-- 修改密码\nALTER USER 'username'@'host' IDENTIFIED BY 'new_password';\n-- 设置密码过期策略\nALTER USER 'user'@'localhost' IDENTIFIED BY 'TempP@ss' PASSWORD EXPIRE INTERVAL 90 DAY;\n\n-- 兼容方式\n-- MySQL 5.7.5及以下版本\nSET PASSWORD FOR 'user'@'host' = PASSWORD('new_password');\n-- MySQL 8.0+ 版本（PASSWORD()函数已移除）\nSET PASSWORD FOR 'user'@'host' = 'new_password';\n\n-- 修改当前用户密码，无需指定用户名\nSET PASSWORD = 'CurrentUserNewPass!';\n```\n\n```bash\n# 命令行中修改密码，无需登录\nmysqladmin -u root -p'old_password' password 'new_password'\n```\n\n### 3.删除用户\n\n```sql\nDROP USER 'username'@'host';\n-- 或者 (一次删除多个用户)\nDROP USER 'user1'@'host1', 'user2'@'host2';\n```\n\n- **重要：** 必须指定 `'host'`。MySQL 认为 `'username'@'localhost'` 和 `'username'@'%'` 是**两个完全不同的用户**。\n\n### 4.权限管理\n\n创建用户后，需要赋予权限才能操作数据库。修改用户权限也是“改”的重要部分。\n\n- **授予权限 (`GRANT`):**\n\n  ```sql\n  GRANT privilege_type [(column_list)] [, privilege_type ...]\n  ON [object_type] privilege_level\n  TO 'username'@'host' [WITH GRANT OPTION];\n  ```\n\n  - `privilege_type`: `SELECT`, `INSERT`, `UPDATE`, `DELETE`, `CREATE`, `DROP`, `ALTER`, `ALL PRIVILEGES` 等。\n\n  - ALL PRIVILEGES包括的权限\n    当您在不同的级别（全局、数据库、表、列等）使用`GRANT ALL PRIVILEGES`时，它实际上包括以下类型的权限：\n\n    - 全局级别 (*.*)：包括但不限于`SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER`等。\n    - 数据库级别 (database.*)：包括`SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE, EVENT, TRIGGER`等。\n    - 表级别 (database.table)：包括`SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER`等。\n    - 对于存储过程，`ALL PRIVILEGES`在适当的范围内会包括`CREATE ROUTINE, ALTER ROUTINE, 和 EXECUTE`权限，这使得用户能够创建、修改和执行存储过程。\n    - 对于定时任务（事件调度器），如果是在数据库级别上授予`ALL PRIVILEGES`，则通常也会包括EVENT权限，允许用户创建和管理事件。但是，确保事件调度器本身是启用状态(event_scheduler变量设置为ON)也是必要的。\n\n  - `privilege_level`: `*.*` (所有数据库所有表), `database_name.*` (指定数据库的所有表), `database_name.table_name` (指定表), `*` (当前数据库的所有表)。\n\n  - `WITH GRANT OPTION`: 允许该用户将自己拥有的权限授予其他用户（**谨慎授予**）。\n\n  - **示例:**\n\n    ```sql\n    -- 给 'appuser'@'localhost' 赋予对 `mydb` 数据库中所有表的所有权限 (无 GRANT OPTION)\n    GRANT ALL PRIVILEGES ON mydb.* TO 'appuser'@'localhost';\n    \n    -- 给 'reportuser'@'192.168.10.50' 赋予对 `reportdb.sales` 表的 SELECT 权限\n    GRANT SELECT ON reportdb.sales TO 'reportuser'@'192.168.10.50';\n    \n    -- 给 'adminuser'@'localhost' 赋予全局创建用户、创建数据库、管理进程的权限，并允许其授权\n    GRANT CREATE USER, CREATE DATABASE, PROCESS ON *.* TO 'adminuser'@'localhost' WITH GRANT OPTION;\n    ```\n\n- **撤销权限 (`REVOKE`):**\n\n  ```sql\n  REVOKE [GRANT OPTION FOR] privilege_type [(column_list)] [, privilege_type ...]\n  ON [object_type] privilege_level\n  FROM 'username'@'host';\n  -- 撤销所有权限 (在该权限级别上)\n  REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'username'@'host';\n  ```\n\n  - **示例:**\n\n    ```sql\n    -- 撤销 'appuser'@'localhost' 在 `mydb` 上的 DELETE 权限\n    REVOKE DELETE ON mydb.* FROM 'appuser'@'localhost';\n    \n    -- 撤销 'adminuser'@'localhost' 的 GRANT OPTION (但保留其他权限)\n    REVOKE GRANT OPTION ON *.* FROM 'adminuser'@'localhost';\n    \n    -- 撤销 'reportuser'@'192.168.10.50' 在 `reportdb.sales` 上的所有权限\n    REVOKE ALL PRIVILEGES ON reportdb.sales FROM 'reportuser'@'192.168.10.50';\n    ```\n\n- **查看权限 (`SHOW GRANTS`):**\n\n  ```sql\n  SHOW GRANTS FOR 'username'@'host';\n  ```\n\n### 5. 刷新权限 \n\n- **目的：** 在执行 `CREATE USER`, `GRANT`, `REVOKE`, `DROP USER`, `ALTER USER`（修改认证信息或资源限制时）等语句后，MySQL 服务器通常会自动将权限更改更新到内存中。但在某些情况下（如直接修改 `mysql` 系统表），需要手动执行 `FLUSH PRIVILEGES` 命令来强制重新加载权限表，使更改**立即生效**。\n- **最佳实践：** 在脚本或不确定自动刷新是否发生时，执行权限变更后加上 `FLUSH PRIVILEGES` 是一个安全的好习惯。","categoryId":4,"viewCount":878,"categoryName":"MySQL","author":"球接子","authorAvatar":null,"tagIds":[2,17],"tagNames":["MySQL","数据库"]}}