第三篇：MySQL 数据库与表操作详解
|
文章 , 学习笔记
|
1.3k 字
|
5 分钟

数据库基本操作（DDL）

查看所有数据库

1
2
<code>SHOW DATABASES;
</code>

创建数据库

1
2
3
4
5
6
<code>-- 直接创建（若数据库已存在会报错）
CREATE DATABASE 数据库名;

-- 推荐：判断不存在时再创建
CREATE DATABASE IF NOT EXISTS 数据库名;
</code>

示例：创建名为test_db的数据库

1
2
<code>CREATE DATABASE IF NOT EXISTS test_db;
</code>

删除数据库

1
2
3
4
5
6
<code>-- 直接删除（若数据库不存在会报错）
DROP DATABASE 数据库名;

-- 推荐：判断存在时再删除
DROP DATABASE IF EXISTS 数据库名;
</code>

数据存储位置

MySQL 数据库默认存储在安装目录的data文件夹下，路径为{MySQL安装目录}/data/{数据库名}/，每个数据库对应一个独立文件夹。

MySQL 数据类型（选对类型提升性能）

选择合适的数据类型能减少空间浪费、提升查询效率，以下是常用类型分类：

数值类型

类型名称说明存储需求适用场景
TINYINT小整数1 字节状态值（0/1）、性别
SMALLINT较小整数2 字节数量（0-65535）
MEDIUMINT中等整数3 字节较大数量（如商品库存）
INT（INTEGER）普通整数4 字节ID、年龄、数量
BIGINT大整数8 字节超大数值（如订单号）
FLOAT单精度浮点数4 字节精度要求不高的小数
DOUBLE双精度浮点数8 字节高精度小数（如坐标）
DECIMAL(M,D)定点数M+2 字节金额（精确到分）

日期 / 时间类型

类型名称格式范围存储需求适用场景
YEARYYYY1901~21551 字节年份（如出生年）
TIMEHH:MM:SS-838:59:59~838:59:593 字节时间间隔（如时长）
DATEYYYY-MM-DD1000-01-01~9999-12-033 字节日期（如生日）
DATETIMEYYYY-MM-DD HH:MM:SS1000-01-01 00:00:00~9999-12-31 23:59:598 字节记录时间（如订单时间）
TIMESTAMPYYYY-MM-DD HH:MM:SS1980-01-01 00:00:01~2040-01-19 03:14:074 字节自动记录时间（如创建时间）

字符串类型

类型名称说明存储需求适用场景
CHAR(M)固定长度字符串M 字节短固定长度文本（如手机号）
VARCHAR(M)可变长度字符串L+1 字节（L≤M）变长文本（如用户名、邮箱）
TEXT小文本L+2 字节短文本（如描述）
MEDIUMTEXT中等文本L+3 字节中等文本（如文章内容）
LONGTEXT大文本L+4 字节长文本（如日志）
ENUM枚举类型1 或 2 字节固定选项（如性别：男 / 女）
SET集合类型1-8 字节多选选项（如爱好）

表的约束（保证数据合法性）

约束是数据库的 “质检员”，用于防止无效数据进入表中，常见类型：

  • 主键约束（PRIMARY KEY）：唯一标识一条记录，非空且唯一（一张表只能有一个主键）。
1
2
<code>id INT PRIMARY KEY  -- 例如用户ID
</code>
  • 非空约束（NOT NULL）：字段必须填写，不能为NULL。
1
2
<code>email VARCHAR(100) NOT NULL  -- 邮箱必须填写
</code>
  • 唯一约束（UNIQUE）：字段值不能重复（允许NULL，但NULL只允许出现一次）。
1
2
<code>username VARCHAR(50) UNIQUE  -- 用户名不能重复
</code>
  • 外键约束（FOREIGN KEY）：关联两张表，确保表间数据关系一致（如订单表的user_id关联用户表的id）。
1
2
3
<code>user_id INT, 
FOREIGN KEY(user_id) REFERENCES users(id)  -- 关联users表的id字段
</code>
  • 默认值约束（DEFAULT）：字段未填写时自动使用默认值。
1
2
<code>status VARCHAR(10) DEFAULT &#039;active&#039;  -- 状态默认值为“active”
</code>
  • 检查约束（CHECK）：限制字段值范围。
1
2
<code>age INT CHECK(age &gt;= 18)  -- 年龄必须≥18
</code>

表的创建与修改（DDL）

创建表

1
2
3
4
5
6
<code>CREATE TABLE 表名 (
  列名1 数据类型 约束条件,
  列名2 数据类型 约束条件,
  ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8;  -- 引擎用InnoDB，字符集utf8
</code>

示例：创建users表存储用户信息

1
2
3
4
5
6
7
8
<code>CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,  -- 自增主键
  username VARCHAR(50) NOT NULL UNIQUE,  -- 用户名非空且唯一
  email VARCHAR(100) NOT NULL,  -- 邮箱非空
  age INT CHECK(age &gt;= 18),  -- 年龄≥18
  register_time DATETIME DEFAULT CURRENT_TIMESTAMP  -- 注册时间默认当前时间
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
</code>

删除表

1
2
<code>DROP TABLE 表名;  -- 删除表结构和所有数据（谨慎操作！）
</code>

修改表结构

  • 添加字段：
1
2
3
4
<code>ALTER TABLE 表名 ADD COLUMN 列名 数据类型 约束;
-- 示例：给users表添加phone字段
ALTER TABLE users ADD COLUMN phone VARCHAR(20) UNIQUE;
</code>
  • 修改字段类型：
1
2
3
4
<code>ALTER TABLE 表名 MODIFY COLUMN 列名 新数据类型;
-- 示例：将age字段改为SMALLINT
ALTER TABLE users MODIFY COLUMN age SMALLINT;
</code>
  • 删除字段：
1
2
3
4
<code>ALTER TABLE 表名 DROP COLUMN 列名;
-- 示例：删除phone字段
ALTER TABLE users DROP COLUMN phone;
</code>
  • 修改字段名：
1
2
3
4
<code>ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 新数据类型;
-- 示例：将username改为user_name
ALTER TABLE users CHANGE COLUMN username user_name VARCHAR(50);
</code>
  • 修改表名：
1
2
3
<code>RENAME TABLE 旧表名 TO 新表名;
-- 示例：将users改为user_info
RENAME TABLE users TO user_info;</code>
上一篇
下一篇