【MYSQL】库表的操作

目录

连接数据库

数据库操作

创建数据库

查看数据库

使用数据库

删除数据库

表操作

创建表

查看表

修改表

删除表

使用 mysqldump 备份

基本语法

 备份所有数据库

备份单个数据库

备份特定表

常用备份选项

恢复数据库

从 SQL 文件恢复

恢复特定表

---

连接数据库

-- 连接MySQL服务器
mysql -u 用户名 -p

-- 连接指定主机的MySQL服务器
mysql -h 主机名 -u 用户名 -p

-- 连接指定端口和数据库
mysql -h 主机名 -P 端口号 -u 用户名 -p 数据库名

数据库操作

创建数据库

CREATE DATABASE 数据库名;

-- 带字符集和排序规则
CREATE DATABASE 数据库名 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_general_ci;

查看数据库

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

-- 查看数据库创建语句
SHOW CREATE DATABASE 数据库名;

使用数据库

USE 数据库名;

删除数据库

DROP DATABASE 数据库名;

表操作

创建表

CREATE TABLE 表名 (
    列名1 数据类型 [约束条件],
    列名2 数据类型 [约束条件],
    ...
    [PRIMARY KEY (列名)]
) ENGINE=存储引擎 DEFAULT CHARSET=字符集 COMMENT='表注释';

示例：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

查看表

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

-- 查看表结构
DESCRIBE 表名;
DESC 表名;

-- 查看表创建语句
SHOW CREATE TABLE 表名;

修改表

-- 添加列
ALTER TABLE 表名 ADD 列名 数据类型 [约束条件];

-- 修改列
ALTER TABLE 表名 MODIFY 列名 新数据类型 [约束条件];

-- 重命名列
ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 [约束条件];

-- 删除列
ALTER TABLE 表名 DROP COLUMN 列名;

-- 重命名表
ALTER TABLE 旧表名 RENAME TO 新表名;
-- 或
RENAME TABLE 旧表名 TO 新表名;

删除表

DROP TABLE 表名;

使用 mysqldump 备份

基本语法

• 注意：-p 和密码之间不要有空格，或者不写密码，执行命令后会提示输入

mysqldump -u 用户名 -p[密码] [选项] 数据库名 [表名] > 备份文件.sql

 备份所有数据库

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

# 备份所有数据库（包含系统数据库）
mysqldump -u root -p --all-databases --include-mysql-host > complete_backup.sql

备份单个数据库

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

# 备份时包含创建数据库语句
mysqldump -u root -p --databases mydatabase > mydatabase_backup.sql

备份特定表

# 备份指定表
mysqldump -u root -p mydatabase table1 table2 > tables_backup.sql

常用备份选项

选项	描述
--add-drop-database	在创建数据库前添加 DROP DATABASE 语句
--add-drop-table	在每个表创建前添加 DROP TABLE 语句
--no-data	只备份结构，不备份数据
--no-create-info	只备份数据，不备份结构
--lock-tables	备份前锁定所有表
--single-transaction	对 InnoDB 使用事务保证一致性
--routines	包含存储过程和函数
--triggers	包含触发器
--events	包含事件
--hex-blob	以十六进制格式导出二进制数据

恢复数据库

从 SQL 文件恢复

# 方法1：使用mysql客户端
mysql -u root -p mydatabase < mydatabase_backup.sql

# 方法2：在mysql shell中执行
mysql> source /path/to/mydatabase_backup.sql

恢复特定表

# 先创建数据库（如果需要）
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS mydatabase"

# 恢复特定表
mysql -u root -p mydatabase < table_backup.sql