MySQL DDL 与 DML 数据定义与操作（完整版）

---

课程目标

[ ] 了解编码格式

[ ] 掌握表的 DDL 操作

[ ] 掌握常用字段类型

[ ] 了解 auto_increment 的作用及主键生成策略

[ ] 掌握 DML 操作

[ ] 掌握字段约束

---

第一部分：DDL 之表操作

---

一、表（Table）是什么？

表（Table） 是数据库中最核心的对象，用来存储数据。

表在mysql的安装路径的数据目录下面 /usr/local/mysql/data 目录的数据库同名目录下面的文件。

就像你在 Excel 里看到的那种"表格"一样，数据库中的"表"也是由行（row）和列（column）组成的结构化数据集合。

1.1 生活中的例子

想象你在学校管理学员：

学员编号(id)	姓名(name)	年龄(age)	生日(birthday)
1	张三	25	2025-10-11
2	李四	30	2025-10-11
3	王五	28	2025-10-11

这个"学员表"其实就是数据库中的一张表（table）。它的每一列代表一个"属性(字段)"，每一行代表一个"数据记录"。

1.2 表的结构组成

一张表由以下几部分组成：

组成部分	说明	举例
表名 ☆	表的名字，用来标识表	student、user_info
列（字段，Column） ☆☆	表中每一列代表一个属性	name、age、gender
行（记录，Row）	表中每一行代表一条完整数据	张三、25、先生
数据类型（Data Type） ☆☆	定义每一列能存放什么类型的数据	INT、VARCHAR、DATE、TEXT
约束（Constraint） ☆	对列的规则限制	NOT NULL、PRIMARY KEY、UNIQUE、DEFAULT

---

二、数据表字段类型

数据类型（Data Type） 用来定义表中"每一列（字段）"可以存储的数据类型和范围。

通俗讲：就像在 Excel 中定义某一列只能输入数字、或者只能输入日期。在数据库中，数据类型帮助 MySQL 知道这列该存什么、占多少空间、怎么比较。

2.1 数值类型

整数类型

类型	占用字节	取值范围（有符号）	取值范围（无符号 UNSIGNED）
TINYINT	1 字节	-128 ~ 127	0 ~ 255
SMALLINT	2 字节	-32768 ~ 32767	0 ~ 65535
MEDIUMINT	3 字节	-8388608 ~ 8388607	0 ~ 16777215
INT	4 字节	-2³¹ ~ 2³¹-1	0 ~ 2³²-1
BIGINT	8 字节	-2⁶³ ~ 2⁶³-1	0 ~ 2⁶⁴-1

说明： UNSIGNED 表示无符号整数，即不存负数，取值范围翻倍。

小数类型

类型	说明	特点	示例
FLOAT	单精度浮点数（4字节）	精度有限	比例
DOUBLE	双精度浮点数（8字节）	精度更高	科学计算
DECIMAL(M,D)	定点小数（M总位数，D小数位）	精度准确，不丢失	金额计算

2.2 字符串类型

定长字符串：CHAR

长度可以是 0 到 255 之间的任何值

无论实际字符多少，都占用固定长度空间

适用场景：长度固定的内容，如身份证号、性别、状态码

-- 表示用1个字符来存储数据
gender CHAR(1)

可变长字符串：VARCHAR

特点	说明
变长（按内容长度存储）	节省空间，存放长度不一的文本
适用场景	姓名、地址、邮箱等
最大长度	65535 字符（受行大小限制）

-- 表示最多存储50个字符
name VARCHAR(50)

CHAR vs VARCHAR 对比

类型	优点	缺点
CHAR	查询更快，空间浪费	固定长度
VARCHAR	节省空间	查询稍慢

文本类型（Text 系列）

类型	最大长度	用途
TINYTEXT	255 字符	短文本，如备注
TEXT	65KB	一般文本内容
MEDIUMTEXT	16MB	大段文本，如日志
LONGTEXT	4GB	超大文本，如文章内容

comment TEXT

注意：

TEXT 类型不能有默认值。

适合存储内容较多的字段，不适合频繁查询或排序。（分表 1张表，分成多张表）

2.3 日期与时间类型

时间戳 是指格林威治时间1970年01月01日00时00分00秒（北京时间1970年01月01日08时00分00秒）起至现在的总秒数，它是一个数值。

类型	存储字节	范围	格式	用途
DATE	3	1000-01-01 ~ 9999-12-31	YYYY-MM-DD	日期
TIME	3	-838:59:59 ~ 838:59:59	HH:MM:SS	时间
YEAR	1	1901 ~ 2155	YYYY	年份
DATETIME	8	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	日期时间（本地时间）
TIMESTAMP	4	1970-01-01 ~ 2038	YYYY-MM-DD HH:MM:SS	自动记录当前时间（UTC）

---

三、创建数据表

表一定要在一个数据库中

3.1 创建表语法

-- if not exists 如果表不存在则创建，存在则不创建
create table [if not exists] 表名 (
    字段名1  数据类型 [约束],
    字段名2  数据类型 [约束]
    ...
) [character set 字符集(如：utf8mb4，默认为utf8mb4,不用指定)];

3.2 案例：创建admin管理员表

-- 前提创建一个数据库
create database test;

-- 使用数据库
use test;

-- 创建admin表，拥有3个字段（编号、用户名称、用户密码）
create table if not exists `admin` (
   `id` int,
   `uname` varchar(10),
   `pwd`   char(32)
);

-- 查看当前库下面所有的数据表
root@localhost [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| admin          |
+----------------+

3.3 案例：创建article文章表

-- 创建article文章表，拥有4个字段（编号、标题、作者、内容）
create table if not exists article (
    id int,
    title varchar(20),
    author varchar(10),
    content text
);

---

四、查看表

-- use 先切换到具体的库
mysql> use 数据库名称;

-- 查看当前库下面所有的数据表
root@localhost [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| admin          |
| article        |
+----------------+

4.1 查看表结构

-- 显示数据表的创建过程（编码格式、字段等信息）
mysql> desc 数据表名称;
mysql> show create table 数据表名称;

-- 查看 admin 表 相关字段信息
root@localhost [test]> desc admin;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| uname | varchar(10) | YES  |     | NULL    |       |
| pwd   | char(32)    | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

-- 查看创建表的语句
root@localhost [test]> show create table admin\G
*************************** 1. row ***************************
       Table: admin
Create Table: CREATE TABLE `admin` (
  `id` int DEFAULT NULL,
  `uname` varchar(10) DEFAULT NULL,
  `pwd` char(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

---

五、删除表

注意： 在生产中，如果有删除表操作，前提你一定要先备份好，再考虑删除。

mysql> drop table 数据表名称;

root@localhost [test]> drop table admin;

root@localhost [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| article        |
+----------------+
1 row in set (0.00 sec)

---

六、修改表名称（了解）

rename table 旧名称 to 新名称;

root@localhost [test]> rename table admin to user;
Query OK, 0 rows affected (0.00 sec)

root@localhost [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| article        |
| user           |
+----------------+
2 rows in set (0.01 sec)

---

七、修改表字段（了解）

7.1 添加字段

-- 默认在表的最后列中添加一个新字段
mysql> alter table 数据表名称 add 新字段名称 字段类型;

-- 把新添加字段放在第一位
mysql> alter table 数据表名称 add 新字段名称 字段类型 [first];

-- 把新添加字段放在指定字段的后面
mysql> alter table 数据表名称 add 新字段名称 字段类型 [after 其他字段名称];

选项说明：

first：把新添加字段放在第一位

after 字段名称：把新添加字段放在指定字段的后面

案例： 在article文章表中添加一个addtime字段，类型为date

-- 默认没有加位置条件，它会在表的最后列中添加一个新字段
root@localhost [test]> alter table article add addtime date;
root@localhost [test]> desc article;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int         | YES  |     | NULL    |       |
| title   | varchar(20) | YES  |     | NULL    |       |
| author  | varchar(10) | YES  |     | NULL    |       |
| content | text        | YES  |     | NULL    |       |
| addtime | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

-- 在列表第1列中添加一个新字段
root@localhost [test]> alter table article add addtime1 date first;

-- 在title字段后面添加一个新字段
root@localhost [test]> alter table article add addtime2 date after title;

7.2 删除字段

mysql> alter table 表名 drop 字段名称;

-- 删除 addtime1 字段
root@localhost [test]> alter table article drop addtime1;

-- 删除 addtime2 字段
root@localhost [test]> alter table article drop addtime2;

7.3 修改字段名称、类型

使用 change 修改字段名称与类型

-- change 比较神奇，既可以修改字段名称，又可以修改字段类型！
-- change 源字段名称 修改后字段名称 修改后的字段类型（这个也可以不改，但是必须要写）
mysql> alter table 表名 change 原字段名 新字段名 类型  [约束];

-- 修改表user 中的字段 uname为username新字段，类型不变
root@localhost [test]> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| uname | varchar(10) | YES  |     | NULL    |       |
| pwd   | char(32)    | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

root@localhost [test]> alter table user change uname username varchar(10);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [test]> desc user;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| username | varchar(10) | YES  |     | NULL    |       |
| pwd      | char(32)    | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

使用 modify 仅修改字段类型

-- modify 字段名称 新字段类型（仅可以修改字段类型）
mysql> alter table 表名 modify 字段名 类型  [约束];

-- 只修改username字段的类型
root@localhost [test]> alter table user modify username varchar(100);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [test]> desc user;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | YES  |     | NULL    |       |
| username | varchar(100) | YES  |     | NULL    |       |
| pwd      | char(32)     | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+

7.4 课堂练习

情景： 在企业开发中，小明接到一个需求，要求参与研发一套新的OA系统。其中有一张管理员表，交给了他。

-- 1、创建一张表 tb_admin（ id、name、sex）
create database if not exists test;
use test;

create table tb_admin (
    id int,
    name varchar(10),
    sex char(1)
);

-- 2、应前端要求，添加字段address、phone_number
alter table tb_admin add address varchar(20);
alter table tb_admin add phone_number varchar(20);

-- 3、后来发现，地址信息几乎不怎么用，要求把 address 字段删除
alter table tb_admin drop  address;

-- 4、后来组员提出疑问，性别用gender更合适。建议把 sex 字段改成 gender
alter table tb_admin change sex gender char(1);

---

第二部分：DML 数据库操作语言

---

一、什么是 DML？

DML（Data Manipulation Language） —— 数据操作语言，用于对表中数据内容进行"增、删、改"的操作。

---

二、插入数据（INSERT）

2.1 指定字段插入【重点】

INSERT INTO 表名 (字段1, 字段2, 字段3) VALUES (值1, '值2', 值3);

-- 复制表中的内容[蠕虫复制]
insert into 表名 select * from 表名

注意：

字段名与值的数量必须一致，字段名可以使用反引号括起来

数字类型不用单引号，其它的都要用单引号括起来

插入语句执行后，会在表中新增一条或多条记录

案例： 向学生表(student)中插入一条学生信息数据

-- 创建数据库
create database if not exists test;
use test;

-- 创建数据表
create table student (
    id int,
    name varchar(20),
    age int,
    gender varchar(10)
);

-- 插入数据
insert into student (id,name, age, gender) values (1,'张三',20,'先生');

2.2 省略字段名（了解）

INSERT INTO 表名 VALUES (值1, 值2, 值3);

注意： 必须写出所有字段顺序

-- 值的数量和表字段的数量是相同的，所以插入是可以成功的
root@localhost [test]> insert into student values (2,'aaa',20,'aa');
Query OK, 1 row affected (0.00 sec)

-- 值的数量和表字段的数量不一样，所以插入不会成功
root@localhost [test]> insert into student values (3,'aaa',20);
ERROR 1136 (21S01): Column count doesn't match value count at row 1

-- 好处：不用写字段名
-- 坏处：字段顺序要对应上不能错，而且所有的字段还都要给值

2.3 一次插入多行数据（了解）

INSERT INTO 表名 (字段1, 字段2, 字段3) VALUES (值1, 值2, 值3),(值1, 值2, 值3);

优点： 效率高于多次单条 INSERT。

-- 需求1：向学生表(student)中插入3条学生信息数据
root@localhost [test]> insert into student (id,name, age, gender) values (3,'bb',20,'bbb'),(4,'cc',20,'cccc');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

需求2： 请在商品表(product)中，一次性插入以下数据

-- 商品表(product)
-- 1 iPhone16 Pro Max 12999 c01
-- 2 三星W25 15999 c01
-- 3 海尔冰箱 4999 c02
-- 4 联想电脑Y9000 4399 c03
-- 5 微波炉 899 c02

create table product (
    id int,
    name varchar(20),
    price int,
    fenli varchar(10)
);

insert into product (id,name,price,fenli) values
(1,'iPhone16 Pro Max',12999,'c01'),
(2,'三星W25',15999,'c01'),
(3,'海尔冰箱',4999,'c02'),
(4,'联想电脑Y9000',4399,'c03'),
(5,'微波炉',899,'c02');

需求3： 快速给用户表中添加100条数据【扩展】

#!/bin/bash

sql="insert into test.student (id,name, age, gender) values "
for i in {1..2}
do
     sql="$sql($i,'user_$i',20,'先生'),"
done

# 把最后1位的字符去除
sql=$(echo $sql | rev | cut -c2- | rev)

mysql -uroot -pAa123456. -e "$sql;" &>/dev/null

if [ $? -eq 0 ];then
        echo "成功"
else
        echo "失败"
fi

命令作用：

使用 mysql 客户端连接数据库并执行 SQL 或客户端命令。

把命令后的文本或变量展开结果写到标准输出；配合 > 或 >> 时会改为写入文件，前者覆盖、后者追加。

执行结果：

成功后客户端会连接目标服务并执行指定操作；失败时检查地址、端口、用户、密码和数据库名称。

成功后文本或变量展开结果会输出到终端；带重定向时会写入目标文件。

关键参数：

注意事项：

使用select语句把原有的数据进行重复插入

---

---

注意：

对比项	DELETE	TRUNCATE
删除方式	一行一行删除	整表清空
自增ID	不会重置自增id	重置为1
速度	慢	快

+--------+-------------+------+-----+---------+-------+ +--------+-------------+------+-----+---------+-------+ +--------+-------------+------+-----+---------+-------+

+------+------+------+--------+--------+ +------+------+------+--------+--------+ | 4 | cc | 20 | cccc | 0 | +------+------+------+--------+--------+


---


---





---






**在手写sql时最常用**

  ...
)

);

+------------+-------------+------+-----+---------+-------+ +------------+-------------+------+-----+---------+-------+ +------------+-------------+------+-----+---------+-------+



**在所有字段后去设置主键，此方案好处它可以指定单个字段为主键，也可以指定多个字段为主键**

  ...,
);

  ...,
);

( );



(
);

(1,'a','b','aa','bb');

(null,'a','b','aa','bb');

+------------+-------------+------+-----+---------+-------+
+------------+-------------+------+-----+---------+-------+
+------------+-------------+------+-----+---------+-------+



(
);

( );


---



**规则：**


  ...
)

( );

+------------+-------------+------+-----+---------+----------------+ +------------+-------------+------+-----+---------+----------------+ +------------+-------------+------+-----+---------+----------------+

( );


---





)

( );


---






| 对比项 | PRIMARY KEY | UNIQUE |
|---|---|---|
| 定义的语句 | 不一样 | 不一样 |
| NULL值 | 不能为null | 可以为null |
| 数量 | 一个表只能有一个 | 可以有多个 |
| 自动特性 | 自动拥有唯一特性 | 没有 |


);

( );



(
);
+------------+-------------+------+-----+---------+----------------+
+------------+-------------+------+-----+---------+----------------+
+------------+-------------+------+-----+---------+----------------+

);



(
);

( );


---


**DEFAULT**，默认值，插入数据时当不填写字段对应的值会使用默认值，如果填写时以填写为准。

**注意：**

);

( );


---



**外键约束前提：**

);

);

);


);


);

---

约束类型	关键字	说明
主键约束	PRIMARY KEY	唯一标识，不能重复，不能为null
自动增长	AUTO_INCREMENT	配合主键使用，自动生成唯一值
非空约束	NOT NULL	必须填写有效值，不能为null
唯一约束	UNIQUE	值必须唯一，但可以为null
默认值	DEFAULT	不填写时使用默认值
外键约束	FOREIGN KEY	多表关联，级联操作