详谈mysql各种常用操作数据表结构的用法【建议收藏】

文章目录

温馨提示
  程序羊十分重视文章文字的排版（好的排版既可以改变一位读者的心情，又可以为读者带来视觉上的冲击），尤其是针对这些很长的阅读类文章，所以本人花了几天的时间排版了文章和标注重要提示，希望能给您一个好的阅读感觉。

适宜阅读人群

1. 需要面试涉及mysql数据库的程序员；
2. 想要查漏补缺的人；
3. 想要不断完善和扩充自己 mysql 技术栈的人；
4. mysql 面试官。

一.修改系列

1.修改表名：

语法：

alter table 旧表名 rename to 新表名;

• to 可以省略不写。

例子：将表aa名修改成test。

altertable aa renameto test;

结果：

[SQL]alter table aa rename to test;
受影响的行: 0
时间: 0.251s

修改前：

修改后：

---

2.修改表的注释：

语法：

alter table 表名 comment '注释';

例子：修改test表的注释。

altertable test comment'测试表';

结果：

[SQL]alter table test comment ‘测试表’;
受影响的行: 0
时间: 0.123s

修改前：

修改后：

---

3.修改表字段名：

语法：

alter table 表名 change column 旧字段名 新字段名 新数据类型;

• column 可以省略不写。

例子：将表test的字段名为name修改成names(这里我也顺带改了类型，不改则写一样)。

altertable test change name names varchar(100);

注意：不管改不改数据类型，后面的数据类型都要写，如果不修改数据类型只需写成原来的数据类型即可。

结果：

[SQL]alter table test change name names varchar(100);
受影响的行: 2
时间: 0.825s

修改前：

修改后：

---

4.修改表字段的数据类型：

语法：

alter table 表名 modify column 字段名 数据类型(长度);

• column 可以省略不写。

例子：修改names字段的数据类型。

altertable test modify sex int(11);

注意：建议带上数据类型长度，比如int类型改varchar类型，如果不带上长度，则无法修改成功。

结果：

[SQL]alter table test modify sex int(11)
受影响的行: 2
时间: 0.961s

修改前：

修改后：

---

5.修改表字段的数据类型长度：

语法：

alter table 表名 modify column 字段名 数据类型(长度);

• column 可以省略不写。

例子：修改names字段的数据类型长度。

altertable test modify names varchar(150);

结果：

[SQL]alter table test modify names varchar(150)
受影响的行: 0
时间: 0.107s

修改前：

修改后：

---

6.修改表字段的默认值：

语法：

alter table 表名 modify column 字段名 数据类型(长度) default 具体值;

• column 可以省略不写。

例子：修改sex字段的默认值。

altertable test modify sex int(11)default0;

结果：

[SQL]alter table test modify sex int(11) default 0;
受影响的行: 0
时间: 0.144s

修改前：

修改后：

---

7.修改表字段的注释：

语法：

alter table 表名 modify column 字段名 数据类型(长度) comment '注释';

• column 可以省略不写。

例子：修改names字段的注释。

altertable test modify names varchar(150)comment'用户姓名';

结果：

[SQL]alter table test modify names varchar(150) comment ‘用户姓名’;
受影响的行: 0
时间: 0.164s

修改前：

修改后：

---

注意：上面的test表中的group字段不够严谨（group是保留关键字），实际应用请不要使用，另外经过刚才的修改测试，使用groups也不行（无法执行alter table语句），因为mysql8中groups是关键字，mysql5.7中不是！

二.创建系列

8.创建表：

8.1.简单创建：

语法：

create table 表名( 字段 数据类型, ... )

例子：创建一张test_new表，并添加对应的字段。

createtable test_new(
id int(11),
content varchar(200),
deptId int(11))

结果：

[SQL]create table test_new(
id int(11),
content varchar(200),
deptId int(11)
)
受影响的行: 0
时间: 0.596s

---

8.2.进阶创建：

语法：

不固定写法，按需求添加，参考以下

例子：创建一张test_new表，并添加对应的字段，添加主键、设置自增、不为空、默认值、添加注释等情况。

createtable test_new(
id int(11)PRIMARYKEYNOTNULLAUTO_INCREMENTCOMMENT'数据ID',
content varchar(200)NOTNULLCOMMENT'内容',
deptId int(11)DEFAULT0COMMENT'部门ID')ENGINE=InnoDBCHARSET=utf8 COMMENT='测试新表'

结果：

[SQL]create table test_new(
id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT ‘数据ID’,
content varchar(200) NOT NULL COMMENT ‘内容’,
deptId int(11) DEFAULT 0 COMMENT ‘部门ID’
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT=‘测试新表’
受影响的行: 0
时间: 0.300s

---

9.插入新字段：

9.1.在指定位置添加字段：

语法：

alter table 表名 add column 字段名 字段类型 是否可为空 comment '注释' after 指定某字段;

• column 可以省略不写。

例子：在content字段后面添加remark字段。

altertable test_new addcolumn remark varchar(100)notnullcomment'备注'after content;

结果：

[SQL]alter table test_new add column remark varchar(100) not null comment ‘备注’ after content;
受影响的行: 0
时间: 0.031s

修改前：

修改后：

---

9.2.在表头添加字段：

语法：

alter table 表名 add column 字段名 数据类型(长度) first;

• column 可以省略不写。
• 添加主键并设置自增则在 first 前面加上 int auto_increment primary key，前提是表还未设定主键。
• 默认情况下添加字段都是添加到表尾，在添加语句后面加上first就能添加到表头（不常用）。

例子：在表头添加sort字段。

altertable test_new addcolumn sort varchar(100)first;

结果：

[SQL]alter table test_new add column sort varchar(100) first;
受影响的行: 0
时间: 0.632s

修改前：

修改后：

---

9.3.增加无完整性约束条件的字段和增加有完整性约束条件的字段的区别：

• 什么叫约束条件？

  约束条件就是控制我们往表字段里插入数据时的一些条件。

• 怎么查看一个表的约束条件？

---

• 增加无完整性约束条件的字段（顾名思义就是没有限制是否为空等情况）

altertable test_new add sex boolean;

PS：此处的sex后面只跟了数据类型，而没有完整性约束条件。

• 增加有完整性约束条件的字段

altertable test_new add age intnotnull;

PS：地处的age字段，后面加上了 not null 完整性约束条件。

---

10.修改表数据从第几开始自动递增：（特殊需求：也可以在创建表时指定）

语法：

alter table 表名 auto_increment=具体数字;

例子：修改表test_new的自增递增（数据id从100开始递增）。

altertable test_new auto_increment=100;

结果：

[SQL]alter table test_new auto_increment=100;
受影响的行: 0
时间: 0.102s

先执行两次新增语句

insertinto test_new(content,remark,deptId)values('这是内容','这是备注',1);

---

三.删除系列

11.删除表字段：

语法：

alter table 表名 drop column 字段名;

• column 可以省略不写。

例子：删除sort字段。

altertable test_new dropcolumn sort;

结果：

[SQL]alter table test_new drop column sort;
受影响的行: 0
时间: 1.006s

修改前：

修改后：

---

四.查询系列

12.查看一个表结构有没有设置约束条件：

语法：

desc 表名

例子：查看表test_new有没有设置约束条件。

desc test_new;

结果：

[SQL]desc test_new
受影响的行: 0
时间: 0.007s

信息：

FieldTypeNullKeyDefaultExtraidint(11)NOPRIauto_incrementcontentvarchar(200)NOremarkvarchar(100)NOdeptIdint(11)NO0
解读：

  前2列是字段名和字段的类型，后4列都属于约束条件，每一列约束的条件都不一样。

• NULL：代表的是允不允许为空，如果NULL里边的值是YES，那就代表这个字段里边可以存空值，空就是没有数据，如果是NO的话，那就不允许存空值，如果我们没设置过约束条件，那默认是允许存空值的。
• Key：代表索引标记，如果给字段设置过索引，那该字段的值会变成MUL。
• Default：代表默认值，如果我们不给字段赋值的话，那默认值就是空，但是默认值我们可以指定，我们在建表的时候可以设置某一个字段的默认值，设置完默认值以后，如果我们在往表里边插入记录的时候，如果不给该字段赋值，那它就用默认值给这个字段赋值，每个字段我们都可以设置默认值，但是默认值不能瞎定义，需要考虑它的合理性，但是如果你的NULL字段设置的不允许为空的话，那default字段设置的默认值它也没用，为什么这么说呢，默认值是你没有赋值的时候它才会用默认值去赋值，如果你没赋值说白了就是空值，而你的NULL字段又设置不允许为空值，那就会发生冲突。

---

13.根据数据库名获取所有表的信息：

语法：

select * from information_schema.TABLES where TABLE_SCHEMA = '数据库名';

例子：查看testdb数据库中所有表的信息。

select*from information_schema.`TABLES`where TABLE_SCHEMA ='testdb';

结果：

[SQL]select * from information_schema.TABLES where TABLE_SCHEMA = ‘testdb’;
受影响的行: 0
时间: 0.003s

---

---

一共21列相关信息，可根据实际需求去组合查询，例如以下：
列名说明TABLE_CATALOG数据表登记目录TABLE_SCHEMA数据库名TABLE_NAME表名TABLE_TYPE表的类型[system view | base table]ENGINE使用的数据库引擎[MyISAM | CSV | InnoDB]VERSION版本，默认值10ROW_FORMAT行格式[Compact | Dynamic | Fixed]TABLE_ROWS表里所存数据总行数AVG_ROW_LENGTH平均行长度DATA_LENGTH数据长度MAX_DATA_LENGTH最大数据长度INDEX_LENGTH索引长度DATA_FREE表/数据空间AUTO_INCREMENT做自增主键的自动增量当前值CREATE_TIME表结构最后更新时间UPDATE_TIME数据最后更新时间CHECK_TIME表的检查时间TABLE_COLLATION表的字符校验编码集CHECKSUM校验和CREATE_OPTIONS创建选项TABLE_COMMENT表的注释

---

例子：根据数据库名获取所有表的名称和表的注释。

select TABLE_NAME,TABLE_COMMENT from information_schema.`TABLES`where TABLE_SCHEMA ='testdb';

结果：

[SQL]select TABLE_NAME,TABLE_COMMENT from information_schema.TABLES where TABLE_SCHEMA = ‘testdb’;
受影响的行: 0
时间: 0.002s

---

14.根据数据库名获取所有字段的信息：

语法：

select * from information_schema.COLUMNS where TABLE_SCHEMA = '数据库名';

例子：查看testdb数据库中所有字段的信息。

select*from information_schema.`COLUMNS`where TABLE_SCHEMA ='testdb';

结果：

[SQL]select * from information_schema.COLUMNS where TABLE_SCHEMA = ‘testdb’;
受影响的行: 0
时间: 0.003s

---

---

一共22列相关信息，可根据实际需求去组合查询，例如以下：
列名说明TABLE_CATALOG数据表登记目录TABLE_SCHEMA数据库名TABLE_NAME表名COLUMN_NAME列名ORDINAL_POSITION字段在表中第几列COLUMN_DEFAULT列的默认数据IS_NULLABLE字段是否可以为空DATA_TYPE数据类型CHARACTER_MAXIMUM_LENGTH字符最大长度CHARACTER_OCTET_LENGTH字节长度NUMERIC_PRECISION数据精度NUMERIC_SCALE数据规模DATETIME_PRECISION日期/时间值的精度CHARACTER_SET_NAME字符集名称COLLATION_NAME字符集校验名称COLUMN_TYPE列类型COLUMN_KEY关键列[NULL | MUL | PRI ]EXTRA额外描述[NULL | on update CURRENT_TIMESTAMP | auto_inerement]PRIVILEGES字段操作权限[select,insert,update,references]COLUMN_COMMENT字段注释GENERATION_EXPRESSION表达式？SRS_ID地理空间索引？

---

例子：根据数据库名获取所有表的名称、字段名和数据类型（长度）。

select TABLE_NAME,COLUMN_NAME,COLUMN_TYPE from information_schema.`COLUMNS`where TABLE_SCHEMA ='testdb';

结果：

[SQL]select TABLE_NAME,COLUMN_NAME,COLUMN_TYPE from information_schema.

COLUMNS

where TABLE_SCHEMA = ‘testdb’;
受影响的行: 0
时间: 0.002s

---

15.根据表名获取所有字段的信息：

语法：

select * from information_schema.COLUMNS where TABLE_SCHEMA='数据库名' and TABLE_NAME = '表名';

• 这里最好指定 TABLE_SCHEMA 数据库名，不然遇到其他数据库也存在同样的表，则也会一起查询出来。

例子：查看testdb数据库中test数据表的所有字段的信息。

select*from information_schema.`COLUMNS`where TABLE_SCHEMA='testdb'and TABLE_NAME ='test';

结果：

[SQL]select * from information_schema.COLUMNS where TABLE_SCHEMA=‘testdb’ and TABLE_NAME = ‘test’;
受影响的行: 0
时间: 0.004s

---

16.根据字段名获取该字段的所有信息：

语法：

select * from information_schema.COLUMNS where TABLE_SCHEMA='数据库名' and TABLE_NAME = '表名' and COLUMN_NAME='字段名';

• 这里最好指定 TABLE_SCHEMA 数据库名和 TABLE_NAME 表名，不然遇到其他数据库也存在同样的表，或者存在同样的字段，比如id，则也会一起查询出来。

例子：查看testdb数据库中test数据表的所有字段的信息。

select*from information_schema.`COLUMNS`where TABLE_SCHEMA='testdb'and TABLE_NAME ='test'and COLUMN_NAME='names';

结果：

[SQL]select * from information_schema.COLUMNS where TABLE_SCHEMA=‘testdb’ and TABLE_NAME = ‘test’ and COLUMN_NAME=‘names’;
受影响的行: 0
时间: 0.002s

---

---

---

未完待续…

• information_schema 数据库是MySQL自带的，它提供了访问数据库元数据的方式。
• 什么是元数据呢？

  元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。有些时候用于表述该信息的其他术语包括“数据词典”和“系统目录”。

  在 MySQL中，把 information_schema 看作是一个数据库，确切说是信息数据库。其中保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等。在 information_schema 中，有数个只读表。它们实际上是视图，而不是基本表，因此，你将无法看到与之相关的任何文件。

创作不易，感谢您的点赞与支持。