首页 > 资讯 > 数据库 >详谈mysql各种常用操作数据表结构的用法【建议收藏】

812

分享到

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

mysql 2023-10-08 11:10:29 812人浏览八月长安

摘要

文章目录一.修改系列1.修改表名：2.修改表的注释：3.修改表字段名：4.修改表字段的数据类型：5.修改表字段的数据类型长度：6.修改表字段的默认值：7.修改表字段的注释：二.创建系列

文章目录

一.修改系列
二.创建系列
三.删除系列
- 11.删除表字段：
四.查询系列

温馨提示

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

适宜阅读人群

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

一.修改系列

1.修改表名：

语法：alter table 旧表名 rename to 新表名;

to 可以省略不写。

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

alter table aa rename to test;

结果：

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

修改前：

在这里插入图片描述

修改后：

在这里插入图片描述

2.修改表的注释：

语法：alter table 表名 comment '注释';

例子：修改test表的注释。

alter table test comment '测试表';

结果：

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

修改前：

在这里插入图片描述

修改后：
在这里插入图片描述

3.修改表字段名：

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

column 可以省略不写。

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

alter table 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字段的数据类型。

alter table 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字段的数据类型长度。

alter table test modify names varchar(150);

结果：

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

修改前：

在这里插入图片描述

修改后：
在这里插入图片描述

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

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

column 可以省略不写。

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

alter table test modify sex int(11) default 0;

结果：

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

修改前：

在这里插入图片描述

修改后：
在这里插入图片描述

7.修改表字段的注释：

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

column 可以省略不写。

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

alter table 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表，并添加对应的字段。

create table 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表，并添加对应的字段，添加主键、设置自增、不为空、默认值、添加注释等情况。

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 CHARSET=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字段。

alter table test_new add column remark varchar(100) not null comment '备注' 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字段。

alter table test_new add column sort varchar(100) first;

结果：

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

修改前：

在这里插入图片描述

修改后：

在这里插入图片描述

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

什么叫约束条件？

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

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

我们可以通过查看表结构来看一个表的约束条件。

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

alter table test_new add sex boolean;

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

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

alter table test_new add age int not null;

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

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

语法：alter table 表名 auto_increment=具体数字;

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

alter table test_new auto_increment=100;

结果：

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

先执行两次新增语句

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

在这里插入图片描述

三.删除系列

11.删除表字段：

语法：alter table 表名 drop column 字段名;

column 可以省略不写。

例子：删除sort字段。

alter table test_new drop column 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

信息：
在这里插入图片描述

Field	Type	Null	Key	Default	Extra
id	int(11)	NO	PRI		auto_increment
content	varchar(200)	NO
remark	varchar(100)	NO
deptId	int(11)	NO		0

解读：

前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	版本，默认值10
ROW_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 中，有数个只读表。它们实际上是视图，而不是基本表，因此，你将无法看到与之相关的任何文件。

在这里插入图片描述