Day11 - DDL操作、外键约束与DML操作

## 一、DDL操作

#### 1、删除表（谨慎操作）

**一旦删除将会把数据库中的表和表中的数据全都清空**

##### 语法

```
 -- 直接删除
 DROP TABLE 表名;
 -- 判断表是否存在
 DROP TABLE IF EXISTS 表名;
```

**示例**

```
 -- 创建emp02
 CREATE TABLE IF NOT EXISTS emp02(
 `EMPno` int(4) NOT NULL,
 PRIMARY KEY (`EMPno`)
 );
 -- 删除emp02
 DROP TABLE IF EXISTS emp02;
```

#### 2、截断表

**截断表的作用是把原来的表摧毁，重新创建一个结构和原来一模一样的新表**

##### 语法

```
 TRUNCATE TABLE 表名;
```

##### truncate与drop,delete的对比(重要)

**上面说过TRUNCATE与DELETE，DROP很相似，其实这三者还是与很大的不同的，下面简单对比下三者的异同。**

**TRUNCATE与DROP是DDL语句，执行后无法回滚;DELETE是DML语句，可回滚。**

**TRUNCATE只能作用于表；DELETE，DROP可作用于表、视图等。**

**TRUNCATE会清空表中的所有行，但表结构及其约束、索引等保持不变;**

**DROP会删除表的结构及其所依赖的约束、索引等。**

**TRUNCATE会重置表的自增；DELETE不会。**

**TRUNCATE不会激活与表有关的删除触发器;DELETE可以。**

**TRUNCATE后会使表和索引所占用的空间会恢复到初始大小；**

**DELETE操作不会减少表或索引所占用的空间，DROP语句将表所占用的空间全释放掉。**

#### 3、修改表名

##### 语法1

```
 RENAME TABLE 旧表名 TO 新表名;
```

##### 语法2

```
 ALTER TABLE 旧表名 RENAME TO 新表名;
```

**示例**

```
 RENAME TABLE emp02 TO emp03;
 ALTER TABLE emp03 RENAME emp04;
```

#### 4、修改表选项

**表选项：数据库引擎innerdb，数据编码字符集**

##### 语法

```
 ALTER TABLE [数据库]表名 表选项;
```

##### 修改字符集

```
 ALTER TABLE 表名 CHARACTER SET 新的字符集;
```

**示例**

**修改emp04的字符集为-gbk 并还原成-utf8**

```
 ALTER TABLE emp04 CHARACTER SET gbk;
 ALTER TABLE emp04 CHARACTER SET utf8;
```

**练习**

**1、创建数据库db01**

```
 CREATE DATABASE IF NOT EXISTS db01 CHARACTER SET utf8; 
```

**2、创建表：t01：INT id，VARCHAR sname，CHAR sex**

```
 USE db01;
 CREATE TABLE IF NOT EXISTS t01(
 `id` int(4) NOT NULL,
 `sname` VARCHAR(14) DEFAULT NULL,
 `sex` CHAR(2) DEFAULT NULL,
 PRIMARY KEY (`id`)
 );
```

**复制一个t01到t02**

```
 CREATE TABLE IF NOT EXISTS t02 LIKE t01;
```

**修改t01的名字为t03**

```
 ALTER TABLE t01 RENAME TO t03;
```

**查看所有数据库**

```
 SHOW DATABASES;
```

**使用db01**

```
 USE db01;
```

**查看db01中所有的表名**

```
 SHOW TABLES;
```

#### 5、添加表字段

##### 语法

```
 ALTER TABLE 表名 ADD 字段名字 字段类型;
```

**为emp04添加一个字段 sex varchar**

```
 ALTER TABLE emp04 ADD sex VARCHAR(2);
```

**使用db01数据库，为t03表添加字段**

**-- job varchar(10)**

**-- age int**

```
 USE db01;
 ALTER TABLE t03 ADD job VARCHAR(10);
 ALTER TABLE t03 ADD age int;
```

#### 6、设置字段位置

##### 语法

**第一个：****FIRST** **在某个字段的后面：****AFTER** 已经存在的字段

**向emp04中添加id int 放在第一位**

```
 ALTER TABLE emp04 ADD id INT FIRST;
```

**向emp04中添加phone varchar 放在EMPno后面**

```
 ALTER TABLE emp04 ADD phone VARCHAR(13) AFTER EMPno;
```

**练习**

**向db01数据库的t03中分别向第一个位置和sname后追加两个字段**

```
 USE db01;
 ALTER TABLE t03 ADD phone VARCHAR(13) FIRST;
 ALTER TABLE t03 ADD fff INT AFTER sname;
```

#### 7、修改字段类型

**原字段int->varchar**

##### 语法

```
 ALTER TABLE 表名 MODIFY 列名 新的类型 [字段属性] [位置];
```

**示例**

**修改emp04中的sex修改为int**

```
 ALTER TABLE emp04 MODIFY sex INT;
```

**练习**

**修改db01数据库的t03中的sex修改为varchar，并且放在sname后面**

```
 USE db01;
 ALTER TABLE t03 MODIFY sex VARCHAR AFTER sname;
```

#### 8、修改字段名称

##### 语法

```
 ALTER TABLE 表名 CHANGE 就字段名 新字段名 新字段类型;
```

**9、删除字段**

##### 语法

```
 ALTER TABLE 表名 DROP 字段名;
```

**删除emp04表中的phone**

```
 ALTER TABLE emp04 DROP phone;
```

**练习** **1、新建学生表student，包含字段stu\_id(INT),stu\_name(VARCHAR NOT NULL),stu\_class(INT)**

```
 USE db01;
 CREATE TABLE student(
 `stu_id` INT,
 `stu_name` VARCHAR(14) NOT NULL,
 `stu_class` INT
 )CHARACTER SET utf8;
```

**2、添加字段age(INT)**

```
 ALTER TABLE student ADD age INT;
```

**3、添加字段sex(INT) IDcard(VARCHAR)**

```
 ALTER TABLE student ADD sex INT;
 ALTER TABLE student ADD IDcard VARCHAR(20);
```

**4、修改age字段类型为smallint(4)**

```
 ALTER TABLE student MODIFY age SMALLINT(4);
```

**5、修改sex字段名为gender**

```
 ALTER TABLE student CHANGE sex gender INT;
```

**6、修改表名为studentinfo**

```
 ALTER TABLE student RENAME TO studentinfo;
```

**7、删除age字段**

```
 ALTER TABLE studentinfo DROP age;
```

**8、查看student的建表语句**

```
 SHOW CREATE TABLE studentinfo;
```

## 二、表字段的约束(DDL)

#### 1、常见的约束

| **约束类型**   | **英文**         | **备注**                              |
| -------------- | ---------------- | ------------------------------------- |
| **主键约束**   | `PRIMARY KEY`    | **自动创建唯一索引，不允许NULL值**    |
| **外键约束**   | `FOREIGN KEY`    | **引用另一个表的主键或唯一键**        |
| **唯一约束**   | `UNIQUE`         | **允许NULL值，MySQL可以有多个NULL值** |
| **非空约束**   | `NOT NULL`       | **强制字段必须包含值**                |
| **检查约束**   | `CHECK`          | **自定义业务规则验证**                |
| **默认值约束** | `DEFAULT`        | **简化数据插入操作**                  |
| **自增约束**   | `AUTO_INCREMENT` | **主要用于主键字段**                  |
| **无符号约束** | `UNSIGNED`       | **定义数字类型为无符号型**            |
| **补零约束**   | `FILLZERO`       | **数字长度不够的向前补零**            |

#### 2、添加约束的方式

**① 建表时添加约束**

**② 通过ALERT语句添加约束**

**示例**

**在db01中创建student表，包含ID、学号、姓名，姓名为非空**

```
 USE db01;
 CREATE TABLE student(
 id INT,
 sno INT,
 sname VARCHAR(10) NOT NULL
 );
 -- 查看表结构
 DESC student;
 -- 验证not null
 -- 新增数据
 INSERT INTO student VALUES (1,20250102,'张三');
 SELECT * FROM student;
 INSERT INTO student VALUES (1,20250103,NULL);
 SELECT * FROM student;
```

**练习**

**向db01中创建stu\_info表：用户名、密码、姓名、年龄、注册时间，所有字段不能为空**

```
 USE db01;
 CREATE TABLE stu_info(
 s_username VARCHAR(14) NOT NULL,
 s_password VARCHAR(24) NOT NULL,
 sname VARCHAR(14) NOT NULL,
 age INT NOT NULL,
 regdate DATE NOT NULL
 );
 -- 查看表结构
 DESC stu_info;
 -- insert语句验证
 INSERT INTO stu_info VALUES (NULL,'2311','123',12,'2025-10-13');
 INSERT INTO stu_info VALUES ('123',NULL,'123',12,'2025-10-13');
 INSERT INTO stu_info VALUES ('123','2311',NULL,12,'2025-10-13');
 INSERT INTO stu_info VALUES ('123','2311','123',NULL,'2025-10-13');
 INSERT INTO stu_info VALUES ('123','2311','123',12,NULL);
 INSERT INTO stu_info VALUES ('123','2311','123',12,'2025-10-13');
 SELECT * FROM stu_info;
```

#### 3、非空约束

##### **语法**

```
 ALTER TABLE 表名 MODIFY 字段 字段类型 NOT NULL;
```

**修改studentinfo的stu\_name为非空**

```
 USE db01;
 ALTER TABLE studentinfo MODIFY stu_name INT NOT NULL;
 DESC studentinfo;
```

**修改studentinfo的stu\_name为可空**

```
 USE db01;
 ALTER TABLE studentinfo MODIFY stu_name INT NULL;
 DESC studentinfo;
```

#### 4、无符号约束 数值(整数类型 浮点类型)

**关键字：**`UNSIGNED`，默认情况是有符号的。

##### **语法**

```
 ALTER TABLE 表名 MODIFY 字段 字段类型 NOT NULL;
```

**示例**

```
 USE db01;
 ALTER TABLE studentinfo MODIFY gender INT UNSIGNED;
 DESC studentinfo;
```

**创建一个t\_1的表，要求有id非空无符号，sname非空**

```
 USE db01;
 CREATE TABLE IF NOT EXISTS t_1(
 id INT UNSIGNED NOT NULL,
 sname VARCHAR(14) NOT NULL
 );
 DESC t_1;
 INSERT INTO t_1 VALUES(-1,'张三');
 SELECT * FROM t_1;
 INSERT INTO t_1 VALUES(10,'张三');
 SELECT * FROM t_1;
```

#### 5、默认值约束

##### 语法

```
 CREATE TABLE 表名(
 列名1 数据类型,
 列名2 数据类型 DEFAULT 默认值 NOT NULL,
 ...
 );
```

**触发方式**

**1、默认触发**

**2、主动触发**

**示例**

**创建t04：银行账号 非空，身份证号码，姓名，账号余额(0.0)**

```
 USE db01;
 CREATE TABLE IF NOT EXISTS t04(
 account VARCHAR(20) NOT NULL,
 id_card CHAR(18) NOT NULL,
 pname VARCHAR(20) NOT NULL,
 money DECIMAL(10,2) DEFAULT 0.00 NOT NULL
 )CHARSET utf8;
 -- 验证默认值
 INSERT INTO t04 (account,id_card,pname) VALUES ('100000101','210211202510102121','张三');
 SELECT * FROM t04;
 INSERT INTO t04 VALUES ('100000102','210211202510102122','李四',999999.99);
 INSERT INTO t04 VALUES ('100000102','210211202510102122','李四',DEFAULT);
 SELECT * FROM t04;
```

**练习**

**创建学生表stu01，包含字段id(主键)、学号、姓名、所在城市(默认沈阳)**

```
 USE db01;
 CREATE TABLE IF NOT EXISTS stu01(
   id INT PRIMARY KEY,
   s_id VARCHAR(14) NOT NULL,
   sname VARCHAR(14),
   city VARCHAR(14) DEFAULT '沈阳' NOT NULL,
 )CHARSET utf8;
 
 INSERT INTO stu01 VALUES(1,'100000101','张三',DEFAULT);
 SELECT * FROM stu01;
```

#### 6、主键约束

**作用**：用来唯一标志表中的一条记录

**分类**：

**    ****业务主键**：例如身份证号

**    ****逻辑主键**：无含义不重复的编号

**注意**：

**① 主键在一个表中只能有一个主键**

**② 表中的主键可以由一个字段构成，也可以由多个字段构成**

**③ 如果是由多个字段构成的主键，一般情况下不会超过两个**

**④ 主键不能为NULL**

##### 语法

**单一主键**

```
 CREATE TABLE 表名(
   列名1 数据类型 PRIMARY KEY,
   列名2 数据类型,
   ....
 );
```

**复合主键**

```
 CREATE TABLE 表名(
   列名1 数据类型,
   列名2 数据类型,
   列名3 数据类型,
   ....
   PRIMARY KEY [主键别名] (列名1，列名2...)
 );
```

**示例**

**单一主键：创建学生表02，包含字段ID、学号、姓名，主键为ID**

```
 USE db01;
 CREATE TABLE IF NOT EXISTS stu02(
   id INT PRIMARY KEY,
   sno INT UNIQUE,
   sname VARCHAR(14)
 );
 DESC stu02;
```

**复合主键**

```
 CREATE TABLE IF NOT EXISTS t05(
   account VARCHAR(10),
   ename VARCHAR(20),
   money DECIMAL(10,2) DEFAULT 0.00 NOT NULL,
   PRIMARY KEY prikey (account,ename)
 );
 DESC t05;
```

##### 删除主键

```
 ALTER TABLE 表名 DROP PRIMARY KEY;
```

**示例**：删除t05的主键

```
 ALTER TABLE t05 DROP PRIMARY KEY;
```

##### 追加主键

```
 ALTER TABLE 表名 ADD PRIMARY KEY (字段1,字段2)
```

**示例**：追加t05的主键account

```
 ALTER TABLE t05 ADD PRIMARY KEY (account);
```

##### 修改主键

**先****删除**再**添加**。

##### 主键自增策略

`AUTO_INCREMENT`被修饰的字段在新增时，自动增长数据

**示例**

```
 USE db01;
 CREATE TABLE IF NOT EXISTS stu03(
   id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
   sno INT UNIQUE,
   sname VARCHAR(14)
 );
 DESC stu03;
 INSERT INTO stu03(sno,sname) VALUES(4,'张三');
 SELECT * FROM stu03;
```

##### 主键自增长管理

**在某些特殊情况下需要自增长按照需求实现**

```
 ALTER TABLE 表名 AUTO_INCREMENT = 起始值;
```

**练习**

**创建一个person表：ID、身份证号、姓名、住址，id设置为主键**

```
 USE db01;
 CREATE TABLE person(
   id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
   idcard VARCHAR(18) UNIQUE,
   pname VARCHAR(14),
   home VARCHAR(30)
 )CHARSET utf8;
```

**添加两条数据**

```
 INSERT INTO person(idcard,pname,home) VALUES('10001','张三','沈阳');
 INSERT INTO person(idcard,pname,home) VALUES('10002','李四','沈阳');
```

**删除id主键**

```
 ALTER TABLE person CHANGE id id INT UNSIGNED;
 ALTER TABLE person DROP PRIMARY KEY;
```

**注意**：当主键设为`AUTO_INCREMENT`时是不能直接删除主键的，需要先取消其自增的属性。

**主键设置为身份证号**

```
 ALTER TABLE person ADD PRIMARY KEY(idcard);
```

**添加数据**

```
 INSERT INTO person VALUES(3,'10003','王五','沈阳');
```

**删除身份证号的主键**

```
 ALTER TABLE person DROP PRIMARY KEY;
```

**添加id为主键**

```
 ALTER TABLE person ADD PRIMARY KEY(id);
 ALTER TABLE person CHANGE id id INT UNSIGNED AUTO_INCREMENT;
```

**设置主键从2000开始**

```
 ALTER TABLE person AUTO_INCREMENT = 2000;
```

**添加数据**

```
 INSERT INTO person(idcard,pname,home) VALUES('10004','熊大','沈阳');
 DESC person;
 SELECT * FROM person;
```

#### 7、唯一约束

**唯一约束也叫唯一键，可以保证字段值的唯一性，一个表可以有多个唯一约束，主键约束只能有一个**

**唯一键允许有NULL，而且可以有多个NULL**

**复合唯一键：多个字段共同组成**

##### 语法

```
 CREATE TABLE 表名(
   列名1 数据类型 约束,
   列名2 数据类型 约束,
   ...
   UNIQUE KEY [复合唯一键别名] (列名1,列名2,...)
 );
```

**练习**

**创建学生表04，包含字段：ID、学号、姓名、身份证号，要求身份证号唯一**

```
 use db01;
 CREATE TABLE stu04(
   id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
   sid INT UNIQUE,
   sname VARCHAR(14),
   idcard VARCHAR(18) UNIQUE
 );
 INSERT INTO stu04(sid,sname,idcard) VALUES(1,'张三',1);
 INSERT INTO stu04(sid,sname,idcard) VALUES(2,'李四',2);
 INSERT INTO stu04(sid,sname,idcard) VALUES(NULL,'王五',NULL);
 INSERT INTO stu04(sid,sname,idcard) VALUES(NULL,'刘六',NULL);
 SELECT * FROM stu04;
 DESC stu04;
```

##### 删除唯一键

```
 ALTER TABLE 表名 DROP INDEX 唯一键名字;
```

##### 新增唯一键

```
 ALTER TABLE 表名 ADD UNIQUE KEY (字段名);
```

**练习**

**删除stu04中的sid、idcard两个唯一键，再添加回来**

```
 ALTER TABLE stu04 DROP INDEX sid;
 ALTER TABLE stu04 DROP INDEX idcard;
 DESC stu04;
 ALTER TABLE stu04 ADD UNIQUE KEY (sid);
 ALTER TABLE stu04 ADD UNIQUE KEY (idcard);
 DESC stu04;
```

**添加sid,idcard复合唯一键**

```
 ALTER TABLE stu04 ADD UNIQUE KEY (sid,idcard);
 DESC stu04;
```

#### 8、COMMENT约束

**作用**：为字段添加备注，查询时不显示

##### 语法

```
 CREATE TABLE 表名(
   列名1 数据类型 [约束],
   列名1 数据类型 [其他约束] COMMENT '描述内容',
   ...
 );
```

**示例**

```
 use db01;
 CREATE TABLE stu05(
   id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
   sid INT UNIQUE COMMENT '学号',
   sname VARCHAR(14) COMMENT '姓名',
   idcard VARCHAR(18) UNIQUE COMMENT '身份证'
 );
 DESC stu05;
```

#### 9、外键约束

`FOREIGN KEY`约束规则

**① 其他表的主键若在此表内有记录则为此表的外键**

**② 若增加记录时没有在外键内包含，则会添加失败**

**③ 若要删除修改外键表，那必须先删除外键绑定的表**

##### 语法

```
 CREATE TABLE 表名(
   列名1 数据类型,
   列名2 数据类型,
   ...
   [CONSTRAINT 外键名] FOREIGN KEY(列名) REFERENCES 外部表(主键字段)
 );
```

**注意**：如果一个字段要定义为外键，那么这个外键的所有约束都要和主表的字段保持一致

**示例**

```
 USE db01;
 CREATE TABLE mydept(
   deptno INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
   dname VARCHAR(10)
 );
 CREATE TABLE myemp(
   empno INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
   ename VARCHAR(10),
   dno INT UNSIGNED,
   FOREIGN KEY(dno) REFERENCES mydept(deptno)
 )
 -- 验证外键(错误做法)
 INSERT INTO myemp(ename,dno) VALUES('张三',10);
 
 -- 正确做法
 INSERT INTO mydept VALUES(10,'瞌睡部');
 INSERT INTO myemp(ename,dno) VALUES('张三',10);
```

##### 新增外键

```
 ALTER TABLE 表名 ADD [CONSTRAINT 外键别名] FOREIGN KEY(外键字段) REFERENCES 外键表名(主键) [ON 外键约束]
```

##### 删除外键

```
 ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
```

**练习**

**删除myemp表外键**

```
 SHOW CREATE TABLE myemp;
 ALTER TABLE myemp DROP FOREIGN KEY myemp_ibfk_1;
```

**新增myemp外键**

```
 ALTER TABLE myemp ADD CONSTRAINT dept_deptno FOREIGN KEY(dno) REFERENCES mydept(deptno);
```

#### 10、检查性约束

**检查性约束是确保某个列的所有行数据必须都满足一个表达式**

**例如性别列只能为男或女，年龄列只能在1岁到150岁之间，就可以通过检查性约束来完成。**

##### 语法

```
 CREATE TABLE 表名(
   列名1 数据类型 CHECK (条件表达式),
   列名2 数据类型,
  ...
  );
```

**示例**

**要求性别列上有检查性约束**

**只能为男或女，年龄上有检查性约束，只能在15\~30之间**

```
 USE db01;
 CREATE TABLE stu06(
   id INT PRIMARY KEY,
   sno INT,
   sname VARCHAR(10),
   gender CHAR(2) CHECK (gender IN ('男','女')),
   age INT CHECK (age >= 15 AND age <= 20)
 );
 INSERT INTO stu06 VALUES(1000,1000,'张三','男',16);
 SELECT * FROM stu06;
```

##### 删除检查性约束

```
 ALTER TABLE 表名 DROP CONSTRAINT 约束名;
```

## 三、DML操作

**DML操作是只对数据库数据操作的语句。**

#### 1、新增数据

**可以给全部列赋值或者给部分列赋值**

##### 语法

**给部分列赋值**

```
 INSERT INTO 表名 (列名1,列名2,...) VALUES (列名1,列名2,...);
```

**给全部列赋值**

```
 INSERT INTO 表名 VALUES (列名1,列名2,...);
```

**注意**：省略列名时必须按照字段顺序排列。

##### 批量添加数据

```
 USE db01;
 INSERT INTO stu06 VALUES
 (1001,1001,'李四','男',16),
 (1002,1002,'王五','男',17),
 (1003,1003,'刘六','女',18),
 (1004,1004,'熊大','男',19),
 (1005,1005,'熊二','男',20),
 (1006,1006,'光头强','女',15);
 SELECT * FROM stu06;
```

##### 利用子查询新增

**语法**

```
 INSERT INTO 表名
 SELECT 字段1,字段2,...
 FROM 表名
 WHERE 约束条件;
```

#### 2、更新数据

##### 语法

```
 UPDATE 表名
 SET 列名1=值1,列名2=值2,...
 WHERE 约束条件;
```

#### 3、删除数据

##### 语法

```
 DELETE FROM 表名
 WHERE 约束条件;
```

**注意**：不加约束条件即为清空表内容

## 一、DDL操作

#### 1、删除表（谨慎操作）

**一旦删除将会把数据库中的表和表中的数据全都清空**

##### 语法

```
 -- 直接删除
 DROP TABLE 表名;
 -- 判断表是否存在
 DROP TABLE IF EXISTS 表名;
```

**示例**

```
 -- 创建emp02
 CREATE TABLE IF NOT EXISTS emp02(
 `EMPno` int(4) NOT NULL,
 PRIMARY KEY (`EMPno`)
 );
 -- 删除emp02
 DROP TABLE IF EXISTS emp02;
```

#### 2、截断表

**截断表的作用是把原来的表摧毁，重新创建一个结构和原来一模一样的新表**

##### 语法

```
 TRUNCATE TABLE 表名;
```

##### truncate与drop,delete的对比(重要)

**上面说过TRUNCATE与DELETE，DROP很相似，其实这三者还是与很大的不同的，下面简单对比下三者的异同。**

**TRUNCATE与DROP是DDL语句，执行后无法回滚;DELETE是DML语句，可回滚。**

**TRUNCATE只能作用于表；DELETE，DROP可作用于表、视图等。**

**TRUNCATE会清空表中的所有行，但表结构及其约束、索引等保持不变;**

**DROP会删除表的结构及其所依赖的约束、索引等。**

**TRUNCATE会重置表的自增；DELETE不会。**

**TRUNCATE不会激活与表有关的删除触发器;DELETE可以。**

**TRUNCATE后会使表和索引所占用的空间会恢复到初始大小；**

**DELETE操作不会减少表或索引所占用的空间，DROP语句将表所占用的空间全释放掉。**

#### 3、修改表名

##### 语法1

```
 RENAME TABLE 旧表名 TO 新表名;
```

##### 语法2

```
 ALTER TABLE 旧表名 RENAME TO 新表名;
```

**示例**

```
 RENAME TABLE emp02 TO emp03;
 ALTER TABLE emp03 RENAME emp04;
```

#### 4、修改表选项

**表选项：数据库引擎innerdb，数据编码字符集**

##### 语法

```
 ALTER TABLE [数据库]表名 表选项;
```

##### 修改字符集

```
 ALTER TABLE 表名 CHARACTER SET 新的字符集;
```

**示例**

**修改emp04的字符集为-gbk 并还原成-utf8**

```
 ALTER TABLE emp04 CHARACTER SET gbk;
 ALTER TABLE emp04 CHARACTER SET utf8;
```

**练习**

**1、创建数据库db01**

```
 CREATE DATABASE IF NOT EXISTS db01 CHARACTER SET utf8; 
```

**2、创建表：t01：INT id，VARCHAR sname，CHAR sex**

```
 USE db01;
 CREATE TABLE IF NOT EXISTS t01(
 `id` int(4) NOT NULL,
 `sname` VARCHAR(14) DEFAULT NULL,
 `sex` CHAR(2) DEFAULT NULL,
 PRIMARY KEY (`id`)
 );
```

**复制一个t01到t02**

```
 CREATE TABLE IF NOT EXISTS t02 LIKE t01;
```

**修改t01的名字为t03**

```
 ALTER TABLE t01 RENAME TO t03;
```

**查看所有数据库**

```
 SHOW DATABASES;
```

**使用db01**

```
 USE db01;
```

**查看db01中所有的表名**

```
 SHOW TABLES;
```

#### 5、添加表字段

##### 语法

```
 ALTER TABLE 表名 ADD 字段名字 字段类型;
```

**为emp04添加一个字段 sex varchar**

```
 ALTER TABLE emp04 ADD sex VARCHAR(2);
```

**使用db01数据库，为t03表添加字段**

**-- job varchar(10)**

**-- age int**

```
 USE db01;
 ALTER TABLE t03 ADD job VARCHAR(10);
 ALTER TABLE t03 ADD age int;
```

#### 6、设置字段位置

##### 语法

**第一个：****FIRST** **在某个字段的后面：****AFTER** 已经存在的字段

**向emp04中添加id int 放在第一位**

```
 ALTER TABLE emp04 ADD id INT FIRST;
```

**向emp04中添加phone varchar 放在EMPno后面**

```
 ALTER TABLE emp04 ADD phone VARCHAR(13) AFTER EMPno;
```

**练习**

**向db01数据库的t03中分别向第一个位置和sname后追加两个字段**

```
 USE db01;
 ALTER TABLE t03 ADD phone VARCHAR(13) FIRST;
 ALTER TABLE t03 ADD fff INT AFTER sname;
```

#### 7、修改字段类型

**原字段int->varchar**

##### 语法

```
 ALTER TABLE 表名 MODIFY 列名 新的类型 [字段属性] [位置];
```

**示例**

**修改emp04中的sex修改为int**

```
 ALTER TABLE emp04 MODIFY sex INT;
```

**练习**

**修改db01数据库的t03中的sex修改为varchar，并且放在sname后面**

```
 USE db01;
 ALTER TABLE t03 MODIFY sex VARCHAR AFTER sname;
```

#### 8、修改字段名称

##### 语法

```
 ALTER TABLE 表名 CHANGE 就字段名 新字段名 新字段类型;
```

**9、删除字段**

##### 语法

```
 ALTER TABLE 表名 DROP 字段名;
```

**删除emp04表中的phone**

```
 ALTER TABLE emp04 DROP phone;
```

**练习** **1、新建学生表student，包含字段stu\_id(INT),stu\_name(VARCHAR NOT NULL),stu\_class(INT)**

```
 USE db01;
 CREATE TABLE student(
 `stu_id` INT,
 `stu_name` VARCHAR(14) NOT NULL,
 `stu_class` INT
 )CHARACTER SET utf8;
```

**2、添加字段age(INT)**

```
 ALTER TABLE student ADD age INT;
```

**3、添加字段sex(INT) IDcard(VARCHAR)**

```
 ALTER TABLE student ADD sex INT;
 ALTER TABLE student ADD IDcard VARCHAR(20);
```

**4、修改age字段类型为smallint(4)**

```
 ALTER TABLE student MODIFY age SMALLINT(4);
```

**5、修改sex字段名为gender**

```
 ALTER TABLE student CHANGE sex gender INT;
```

**6、修改表名为studentinfo**

```
 ALTER TABLE student RENAME TO studentinfo;
```

**7、删除age字段**

```
 ALTER TABLE studentinfo DROP age;
```

**8、查看student的建表语句**

```
 SHOW CREATE TABLE studentinfo;
```

## 二、表字段的约束(DDL)

#### 1、常见的约束

#### 2、添加约束的方式

**① 建表时添加约束**

**② 通过ALERT语句添加约束**

**示例**

**在db01中创建student表，包含ID、学号、姓名，姓名为非空**

**练习**

**向db01中创建stu\_info表：用户名、密码、姓名、年龄、注册时间，所有字段不能为空**

#### 3、非空约束

##### **语法**

```
 ALTER TABLE 表名 MODIFY 字段 字段类型 NOT NULL;
```

**修改studentinfo的stu\_name为非空**

```
 USE db01;
 ALTER TABLE studentinfo MODIFY stu_name INT NOT NULL;
 DESC studentinfo;
```

**修改studentinfo的stu\_name为可空**

```
 USE db01;
 ALTER TABLE studentinfo MODIFY stu_name INT NULL;
 DESC studentinfo;
```

#### 4、无符号约束 数值(整数类型 浮点类型)

**关键字：**`UNSIGNED`，默认情况是有符号的。

##### **语法**

```
 ALTER TABLE 表名 MODIFY 字段 字段类型 NOT NULL;
```

**示例**

```
 USE db01;
 ALTER TABLE studentinfo MODIFY gender INT UNSIGNED;
 DESC studentinfo;
```

**创建一个t\_1的表，要求有id非空无符号，sname非空**

#### 5、默认值约束

##### 语法

```
 CREATE TABLE 表名(
 列名1 数据类型,
 列名2 数据类型 DEFAULT 默认值 NOT NULL,
 ...
 );
```

**触发方式**

**1、默认触发**

**2、主动触发**

**示例**

**创建t04：银行账号 非空，身份证号码，姓名，账号余额(0.0)**

**练习**

**创建学生表stu01，包含字段id(主键)、学号、姓名、所在城市(默认沈阳)**

#### 6、主键约束

**作用**：用来唯一标志表中的一条记录

**分类**：

**    ****业务主键**：例如身份证号

**    ****逻辑主键**：无含义不重复的编号

**注意**：

**① 主键在一个表中只能有一个主键**

**② 表中的主键可以由一个字段构成，也可以由多个字段构成**

**③ 如果是由多个字段构成的主键，一般情况下不会超过两个**

**④ 主键不能为NULL**

##### 语法

**单一主键**

```
 CREATE TABLE 表名(
   列名1 数据类型 PRIMARY KEY,
   列名2 数据类型,
   ....
 );
```

**复合主键**

```
 CREATE TABLE 表名(
   列名1 数据类型,
   列名2 数据类型,
   列名3 数据类型,
   ....
   PRIMARY KEY [主键别名] (列名1，列名2...)
 );
```

**示例**

**单一主键：创建学生表02，包含字段ID、学号、姓名，主键为ID**

```
 USE db01;
 CREATE TABLE IF NOT EXISTS stu02(
   id INT PRIMARY KEY,
   sno INT UNIQUE,
   sname VARCHAR(14)
 );
 DESC stu02;
```

**复合主键**

```
 CREATE TABLE IF NOT EXISTS t05(
   account VARCHAR(10),
   ename VARCHAR(20),
   money DECIMAL(10,2) DEFAULT 0.00 NOT NULL,
   PRIMARY KEY prikey (account,ename)
 );
 DESC t05;
```

##### 删除主键

```
 ALTER TABLE 表名 DROP PRIMARY KEY;
```

**示例**：删除t05的主键

```
 ALTER TABLE t05 DROP PRIMARY KEY;
```

##### 追加主键

```
 ALTER TABLE 表名 ADD PRIMARY KEY (字段1,字段2)
```

**示例**：追加t05的主键account

```
 ALTER TABLE t05 ADD PRIMARY KEY (account);
```

##### 修改主键

**先****删除**再**添加**。

##### 主键自增策略

`AUTO_INCREMENT`被修饰的字段在新增时，自动增长数据

**示例**

##### 主键自增长管理

**在某些特殊情况下需要自增长按照需求实现**

```
 ALTER TABLE 表名 AUTO_INCREMENT = 起始值;
```

**练习**

**创建一个person表：ID、身份证号、姓名、住址，id设置为主键**

```
 USE db01;
 CREATE TABLE person(
   id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
   idcard VARCHAR(18) UNIQUE,
   pname VARCHAR(14),
   home VARCHAR(30)
 )CHARSET utf8;
```

**添加两条数据**

```
 INSERT INTO person(idcard,pname,home) VALUES('10001','张三','沈阳');
 INSERT INTO person(idcard,pname,home) VALUES('10002','李四','沈阳');
```

**删除id主键**

```
 ALTER TABLE person CHANGE id id INT UNSIGNED;
 ALTER TABLE person DROP PRIMARY KEY;
```

**注意**：当主键设为`AUTO_INCREMENT`时是不能直接删除主键的，需要先取消其自增的属性。

**主键设置为身份证号**

```
 ALTER TABLE person ADD PRIMARY KEY(idcard);
```

**添加数据**

```
 INSERT INTO person VALUES(3,'10003','王五','沈阳');
```

**删除身份证号的主键**

```
 ALTER TABLE person DROP PRIMARY KEY;
```

**添加id为主键**

```
 ALTER TABLE person ADD PRIMARY KEY(id);
 ALTER TABLE person CHANGE id id INT UNSIGNED AUTO_INCREMENT;
```

**设置主键从2000开始**

```
 ALTER TABLE person AUTO_INCREMENT = 2000;
```

**添加数据**

```
 INSERT INTO person(idcard,pname,home) VALUES('10004','熊大','沈阳');
 DESC person;
 SELECT * FROM person;
```

#### 7、唯一约束

**唯一约束也叫唯一键，可以保证字段值的唯一性，一个表可以有多个唯一约束，主键约束只能有一个**

**唯一键允许有NULL，而且可以有多个NULL**

**复合唯一键：多个字段共同组成**

##### 语法

```
 CREATE TABLE 表名(
   列名1 数据类型 约束,
   列名2 数据类型 约束,
   ...
   UNIQUE KEY [复合唯一键别名] (列名1,列名2,...)
 );
```

**练习**

**创建学生表04，包含字段：ID、学号、姓名、身份证号，要求身份证号唯一**

##### 删除唯一键

```
 ALTER TABLE 表名 DROP INDEX 唯一键名字;
```

##### 新增唯一键

```
 ALTER TABLE 表名 ADD UNIQUE KEY (字段名);
```

**练习**

**删除stu04中的sid、idcard两个唯一键，再添加回来**

```
 ALTER TABLE stu04 DROP INDEX sid;
 ALTER TABLE stu04 DROP INDEX idcard;
 DESC stu04;
 ALTER TABLE stu04 ADD UNIQUE KEY (sid);
 ALTER TABLE stu04 ADD UNIQUE KEY (idcard);
 DESC stu04;
```

**添加sid,idcard复合唯一键**

```
 ALTER TABLE stu04 ADD UNIQUE KEY (sid,idcard);
 DESC stu04;
```

#### 8、COMMENT约束

**作用**：为字段添加备注，查询时不显示

##### 语法

```
 CREATE TABLE 表名(
   列名1 数据类型 [约束],
   列名1 数据类型 [其他约束] COMMENT '描述内容',
   ...
 );
```

**示例**

#### 9、外键约束

`FOREIGN KEY`约束规则

**① 其他表的主键若在此表内有记录则为此表的外键**

**② 若增加记录时没有在外键内包含，则会添加失败**

**③ 若要删除修改外键表，那必须先删除外键绑定的表**

##### 语法

```
 CREATE TABLE 表名(
   列名1 数据类型,
   列名2 数据类型,
   ...
   [CONSTRAINT 外键名] FOREIGN KEY(列名) REFERENCES 外部表(主键字段)
 );
```

**注意**：如果一个字段要定义为外键，那么这个外键的所有约束都要和主表的字段保持一致

**示例**

##### 新增外键

```
 ALTER TABLE 表名 ADD [CONSTRAINT 外键别名] FOREIGN KEY(外键字段) REFERENCES 外键表名(主键) [ON 外键约束]
```

##### 删除外键

```
 ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
```

**练习**

**删除myemp表外键**

```
 SHOW CREATE TABLE myemp;
 ALTER TABLE myemp DROP FOREIGN KEY myemp_ibfk_1;
```

**新增myemp外键**

```
 ALTER TABLE myemp ADD CONSTRAINT dept_deptno FOREIGN KEY(dno) REFERENCES mydept(deptno);
```

#### 10、检查性约束

**检查性约束是确保某个列的所有行数据必须都满足一个表达式**

**例如性别列只能为男或女，年龄列只能在1岁到150岁之间，就可以通过检查性约束来完成。**

##### 语法

```
 CREATE TABLE 表名(
   列名1 数据类型 CHECK (条件表达式),
   列名2 数据类型,
  ...
  );
```

**示例**

**要求性别列上有检查性约束**

**只能为男或女，年龄上有检查性约束，只能在15\~30之间**

##### 删除检查性约束

```
 ALTER TABLE 表名 DROP CONSTRAINT 约束名;
```

## 三、DML操作

**DML操作是只对数据库数据操作的语句。**

#### 1、新增数据

**可以给全部列赋值或者给部分列赋值**

##### 语法

**给部分列赋值**

```
 INSERT INTO 表名 (列名1,列名2,...) VALUES (列名1,列名2,...);
```

**给全部列赋值**

```
 INSERT INTO 表名 VALUES (列名1,列名2,...);
```

**注意**：省略列名时必须按照字段顺序排列。

##### 批量添加数据

##### 利用子查询新增

**语法**

```
 INSERT INTO 表名
 SELECT 字段1,字段2,...
 FROM 表名
 WHERE 约束条件;
```

#### 2、更新数据

##### 语法

```
 UPDATE 表名
 SET 列名1=值1,列名2=值2,...
 WHERE 约束条件;
```

#### 3、删除数据

##### 语法

```
 DELETE FROM 表名
 WHERE 约束条件;
```

**注意**：不加约束条件即为清空表内容

最后修改：2025 年 10 月 22 日

👆请简单粗暴地爱我。

Day11 - DDL操作、外键约束与DML操作

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

Spring框架依赖注入

MySQL知识扩展

Day12 - JDBC驱动安装及其简单使用方法

Java常用注解表

Electron的简介

Oracle异常捕获与动态SQL

Java进程实现多线程

Spring集成MyBatis

Day20 - MySQL触发器及优化、MyBatis基础

[工具类] Collections类常用的方法

Day11 - DDL操作、外键约束与DML操作

发表评论 取消回复 使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

Day11 - DDL操作、外键约束与DML操作

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款