文章目录

一、表相关操作（一）表介绍（二）创建表（1）语法及注意事项（2）代码示例： （三）查看表结构（四）修改表（1）修改表名（2）修改字段（3）增加字段（4）删除字段（5）代码示例 （五）表字段的数据类型（1）介绍（2）数值类型1.整数类型2.浮点型 （3）日期类型（4）字符串类型1.基本情况2.基本使用验证3.总结 （5）枚举类型与集合类型 （六）复制表（七）删除表

一、表相关操作

（一）表介绍

​ 表相当于文件，表中的一条记录就相当于文件的一行内容，不同的是，表中的一条记录有对应的标题，称为表的字段。

idnameage字段：一条记录对应的标题称为一个字段，如id，name，age1kiessling37记录：一行内容称为一条记录2lars303leno28

（二）创建表

（1）语法及注意事项

create table 表名( 字段名1 类型[(宽度) 约束条件], 字段名2 类型[(宽度) 约束条件], 字段名3 类型[(宽度) 约束条件] );

注意：

1.在同一张表中，字段名是不能相同的；

2.宽度和约束条件可选；

3.字段名和类型是必须的；

4.表中的最后一个字段不要加逗号！！

（2）代码示例：

mysql> create database db1 charset utf8; # 创建库并指定字符编码（文件夹） mysql> use db1; # 切换文件夹 mysql> create table t1( # 创建表，并指定字段，数据类型 -> id int, # 一般创建表必指定的字段 -> name varchar(50), -> sex enum('male','female'), -> age int(3) # 整型字段后面指定的是显示长度，不是数据实际的存储长度 -> ); mysql> show tables; #查看db1库下所有表名 mysql> desc t1; # 查看表的结构 mysql> select id,name,sex,age from t1; # 查看指定字段的内容 mysql> select * from t1; # 查看表的所有内容 mysql> insert into t1 values # into可省略 -> (1,'egon',18,'male'), -> (2,'alex',81,'female') -> ; mysql> insert into t1(id) values # 插入值全为NULL -> (3), -> (4);

（三）查看表结构

MariaDB[db1]> describe t1; # 查看表结构，可简写为desc 表名 +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | age | int(3) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ MariaDB [db1]> show create table t1\G; # 查看表详细结构，可加\G

（四）修改表

（1）修改表名

alter table 表名 rename 新表名;

（2）修改字段

alter table 表名 modify 字段名 数据类型 [完整性约束条件]; alter table 表名 change 旧字段名 新字段名 旧数据类型 [完整性约束条件]; alter table 表名 change 旧字段名 新字段名 新数据类型 [完整性约束条件];

（3）增加字段

alter table 表名 add 字段名 数据类型 [完整性约束条件], add 字段名 数据类型 [完整性约束条件]; alter table 表名 add 字段名 数据类型 [完整性约束条件] first; alter table 表名 add 字段名 数据类型 [完整性约束条件] after 字段名;

（4）删除字段

alter table 表名 drop 字段名;

（5）代码示例

1) 先创建库，在新建表 mysql> create database db1; mysql> user db1 mysql> create table t1(id int,name char(4)); # mysql> alter table t1 rename tt1; # 表名的修改 2) 修改存储引擎 mysql> alter table t1 engine=innodb; 3) 修改字段 mysql> alter table t1 modify id tinyint; # 修改字段的数据类型 mysql> alter table t1 change id ID tinyint; # 修改字段名及数据类型 mysql> alter table t1 change ID id tinyint,change name NAME char(4); # 修改多个字段名及数据类型 4) 增加字段 mysql> alter table t1 add gender char(4); mysql> desc t1; +--------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+------------+------+-----+---------+-------+ | id | tinyint(4) | YES | | NULL | | | NAME | char(4) | YES | | NULL | | | gender | char(4) | YES | | NULL | | +--------+------------+------+-----+---------+-------+ mysql> alter table t1 add gender char(4) first; mysql> alter table t1 add level int after ID; 5) 删除字段 alter table t1 drop gender; mysql> desc t1; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | id | tinyint(4) | YES | | NULL | | | NAME | char(4) | YES | | NULL | | +-------+------------+------+-----+---------+-------+

（五）表字段的数据类型

（1）介绍

​ 存储引擎决定了表的类型，而表内存放的数据也要有不同的类型，每种数据类型都有自己的宽度，但是宽度是可选的。

1.mysql常用数据类型概览：

#1. 数字： 整型：tinyinit int bigint 小数： float ：在位数比较短的情况下不精准 double ：在位数比较长的情况下不精准 0.000001230123123123 存成：0.000001230000 decimal：（如果用小数，则用推荐使用decimal） 精准 内部原理是以字符串形式去存 #2. 字符串： char（10）：简单粗暴，浪费空间，存取速度快 root存成root000000 varchar：精准，节省空间，存取速度慢 sql优化：创建表时，定长的类型往前放，变长的往后放 比如性别 比如地址或描述信息 >255个字符，超了就把文件路径存放到数据库中。 比如图片，视频等找一个文件服务器，数据库中只存路径或url。 #3. 时间类型： 最常用：datetime #4. 枚举类型与集合类型

（2）数值类型

1.整数类型

​ 整数类型：TINYINT SMALLINT MEDIUMINT INT BIGINT

​ 作用：存储年龄，等级，id，各种号码等。

​ 强调：整型的宽度是显示宽度（如tinyint(4) ），无需设置，存储宽度是固定死的。默认的显示宽度，都是在最大值的基础上加1。

# 常用类型详解 ======================================== tinyint[(m)] [unsigned] [zerofill] 小整数，数据类型用于保存一些范围的整数数值范围： 有符号： -128 ～ 127 无符号： 0 ～ 255 PS： MySQL中无布尔值，使用tinyint(1)构造。 ======================================== int[(m)][unsigned][zerofill] 整数，数据类型用于保存一些范围的整数数值范围： 有符号： -2147483648 ～ 2147483647 无符号： 0 ～ 4294967295 ======================================== bigint[(m)][unsigned][zerofill] 大整数，数据类型用于保存一些范围的整数数值范围： 有符号： -9223372036854775808 ～ 9223372036854775807 无符号： 0 ～ 18446744073709551615

​ 代码验证：

# 1、=====================表字段类型之整型======================= # =========有符号和无符号tinyint========== # tinyint默认为有符号 mysql> insert t4 values(128); ERROR 1264 (22003): Out of range value for column 'id' at row 1 # 严格模式，超出范围会报错，若不是严格模式，则会把数字存成最近的极值。 mysql> insert t4 values(127); Query OK, 1 row affected (0.01 sec) mysql> select * from t4; +------+ | id | +------+ | 127 | +------+ 1 row in set (0.00 sec) 类型大小范围（有符号）范围（无符号）用途TINYINT1 字节(-128，127)(0，255)小整数值SMALLINT2 字节(-32 768，32 767)(0，65 535)大整数值MEDIUMINT3 字节(-8 388 608，8 388 607)(0，16 777 215)大整数值INT或INTEGER4 字节(-2 147 483 648，2 147 483 647)(0，4 294 967 295)大整数值BIGINT8 字节(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)(0，18 446 744 073 709 551 615)极大整数值FLOAT4 字节(-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)0，(1.175 494 351 E-38，3.402 823 466 E+38)单精度，浮点数值DOUBLE8 字节(1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)双精度，浮点数值DECIMAL对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2依赖于M和D的值依赖于M和D的值小数值 1) int的存储宽度是4Bytes，即32个bit，即2**32； 2) 无符号最大值：4294967296-1； 3) 有符号最大值：2147483648-1； 4) 有符号和无符号的最大数字需要的显示宽度均为10，而针对有符号的最小值则需要11位才能完全显示，所以int类型默认的显示宽度为11是非常合理的。 5) 最后，整型类型，其实没有必要指定显示宽度，使用默认的就OK。

2.浮点型

定点数类型 DEC等同于DECIMAL

浮点类型：float double

作用：存储薪资、身高、体重、体质参数等

# 常用类型详解 ====================================== float[(M,D) [unsigned] [zerofill]] 定义： 单精度浮点数（非准确小数），m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30。 有符号： -3.402823466E+38 to -1.175494351E-38, 1.175494351E-38 to 3.402823466E+38 无符号： 1.175494351E-38 to 3.402823466E+38 精确度： **** 随着小数的增多，精度变得不准确 **** ====================================== double[(M,D) [unsigned] [zerofill]] 定义： 双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30。 有符号： -1.7976931348623157E+308 to -2.2250738585072014E-308 2.2250738585072014E-308 to 1.7976931348623157E+308 无符号： 2.2250738585072014E-308 to 1.7976931348623157E+308 精确度： ****随着小数的增多，精度比float要高，但也会变得不准确 **** ====================================== decimal[(M,D) [unsigned] [zerofill]] 定义： 准确的小数值，m是数字总个数（符号不算），d是小数点后个数。m最大值为65，d最大值为30。 精确度： **** 随着小数的增多，精度始终准确 **** 对于精确数值计算时需要用此类型 decaimal能够存储精确值的原因在于其内部按照字符串存储。 # 2、=====================表字段类型之浮点类型======================= mysql> create table t7(x float(255,30),y double(255,30),z decimal(65,30)); mysql> insert t7 values(1.11111111111111111111,1.11111111111111111111,1.11111111111111111111); mysql> select * from t7; # 显示的精度问题，decimal精度最高 +----------------------------------+----------------------------------+----------------------------------+ | x | y | z | +----------------------------------+----------------------------------+----------------------------------+ | 1.111111164093017600000000000000 | 1.111111111111111200000000000000 | 1.111111111111111111110000000000 | +----------------------------------+----------------------------------+----------------------------------+ 1 row in set (0.00 sec)

（3）日期类型

​ date time datetime timestamp year

​ 作用：存储用户注册时间，文章发布时间，员工入职时间，出生时间，过期时间等等。

year（1901/2155） time 时:分:秒 （'-838:59:59'/'838:59:59'） date 年:月:日 （1000-01-01/9999-12-31） datetime 年:月:日 时:分:秒 1000-01-01 00:00:00/9999-12-31 23:59:59 timestamp 年:月:日 时:分:秒 1970-01-01 00:00:00/2037 # 3、=====================表字段类型之日期类型====================== # 使用函数now() mysql> create table t8(y year,t time,d date,dt datetime,ts timestamp); Query OK, 0 rows affected (0.03 sec) mysql> insert t8 values(now(),now(),now(),now(),now()); Query OK, 1 row affected, 1 warning (0.01 sec) mysql> select * from t8; +------+----------+------------+---------------------+---------------------+ | y | t | d | dt | ts | +------+----------+------------+---------------------+---------------------+ | 2020 | 11:09:30 | 2020-09-02 | 2020-09-02 11:09:30 | 2020-09-02 11:09:30 | +------+----------+------------+---------------------+---------------------+ # 手动传时间参数 mysql> create table student(id int,name char(10),born_year year,birth date,reg_time datetime); Query OK, 0 rows affected (0.03 sec) mysql> insert student values(1,"xxx","1911","1911-11-11","1911-11-11 11:11:11"); Query OK, 1 row affected (0.01 sec) mysql> select * from student; +------+------+-----------+------------+---------------------+ | id | name | born_year | birth | reg_time | +------+------+-----------+------------+---------------------+ | 1 | xxx | 1911 | 1911-11-11 | 1911-11-11 11:11:11 | +------+------+-----------+------------+---------------------+ 1 row in set (0.00 sec) ============！！！注意！！！！！！！=========== 1) 单独插入时间是，需要以字符串的显露出，按照对应的格式插入 2) 插入年份时，尽量使用4位值 3) 插入两位年份时，<=69，以20开头，比如50，结果2050 >=70，以19开头，比如71，结果1971

datetime与timestamp的区别

# 在实际应用的很多场景中，MySQL的这两种日期类型都能够满足我们的需要，存储精度都为秒，但在某些情况下，会展现出他们各自的优劣。下面就来总结一下两种日期类型的区别。 1) DATETIME的日期范围是1001——9999年，TIMESTAMP的时间范围是1970——2038年。 2) datetime存储时间与失去无关，timestamp存储时间与失去有关，显示的值也依赖于时区。在MySQL服务器，操作系统以及客户端连接都有时区的设置。 3) datetime使用8字节存储时间，timestamp的搓出空间为4字节。因此，timestamp比datetime的空间利用率更高。 4) datetime的默认值为null，timestamp的字段默认不为空（not null），默认值为当前时间（current_timestamp），如果不做特殊处理，并且update语句中没有指定该列的更新值，则默认更新至为当前时间。

**！！！注意：**针对datetime或者timestamp如果是用作注册时间，那么指定not null default now()自动填充时间即可，如果是用作更新时间那么需要额外指定on update now()，该配置timestamp自带。

# 测试1：基础性质 mysql> create table t9(reg_time datetime not null default now()); mysql> create table t10(reg_time timestamp); mysql> insert t9 values(); mysql> insert t10 values(); mysql> select * from t9; +---------------------+ | reg_time | +---------------------+ | 2020-09-02 11:28:40 | +---------------------+ mysql> select * from t10; +---------------------+ | reg_time | +---------------------+ | 2020-09-02 11:28:49 | +---------------------+ mysql> desc t9; # 用于记录注册时间， +----------+----------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+-------------------+-------+ | reg_time | datetime | NO | | CURRENT_TIMESTAMP | | +----------+----------+------+-----+-------------------+-------+ mysql> desc t10; # 用于记录更新时间 +----------+-----------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +----------+-----------+------+-----+-------------------+-----------------------------+ | reg_time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +----------+-----------+------+-----+-------------------+-----------------------------+ # 测试2： mysql> create table t9( -> id int, -> name varchar(16), -> -- update_time datetime not null default now() on update now() # --是注释的意思, -> update_time timestamp, -> reg_time datetime not null default now() -> ); mysql> insert into t9(id,name) values(1,"xxx"); # 测试效果 mysql> select * from t9; +------+------+---------------------+---------------------+ | id | name | update_time | reg_time | +------+------+---------------------+---------------------+ | 1 | xxx | 2020-09-02 11:37:21 | 2020-09-02 11:37:21 | +------+------+---------------------+---------------------+ mysql> update t9 set name="XXXX" where id=1; mysql> select * from t9; +------+------+---------------------+---------------------+ | id | name | update_time | reg_time | +------+------+---------------------+---------------------+ | 1 | XXXX | 2020-09-02 11:39:13 | 2020-09-02 11:37:21 | +------+------+---------------------+---------------------+

（4）字符串类型

1.基本情况

# 注意：char和varchar括号内的参数指的都是字符的长度 1) char类型：定长，简单粗暴，浪费空间，存取速度快 字符长度范围：0-255（一个波斯文是一个字符，是utf8编码的3个字节） 存储： 存储char类型的值时，会往右填充空格来满足长度（底层逻辑，查询时查不到） 例如：指定长度为10，存>10个字符则报错，存<10个字符则用空格填充直到凑够10个存储字符。 检索： 在检索或者说查询时，查处的结果会自动删除尾部的空格，除非我们打开pad_char_to_full_length SQL模式(SET sql_mode='PAD_CHAR_TO_FULL_LENGTH';)。 2) varchar类型：变长，精准，节省空间，存取速度慢 字符长度范围：0-65535（如果大于21845会提示用其他类型。MySQL行最大限制为65535字节，字符编码为utf-8：https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html） 存储 varchar类型存储数据的真实内容，不会用空格填充，如果'ab '，尾部空格也会被存起来； 强调！！：varchar类型的会在真实数据前加1-2Bytes前缀，该前缀用来表示真实数据的Bytes字节数（1-2Bytes最大表示65535个数字，正好符合MySQL对roe的最大字节限制数，即已足够使用）； 如果真实数据<255Bytes则需要1Bytes的前缀（1Bytes=8bit 2**8最大表示的数字为255）； 如果真实数据>255Bytes则需要2Bytes的前缀（2Bytes=16bit 2**16最大表示的数字为65535）； 检索：尾部有空格会保存下来，在检索或者说查询时，也会正常显示包含空格在内的内容；

**示例：**以4字节为例，对比char与varchar存储空间数量。**特殊情况：**在接近数据存储满的时候，varchar反而更费空间。

ValueCHAR(4)Storage RequiredVARCHAR(4)Storage Required''' '4 bytes''1 byte'ab''ab '4 bytes'ab'3 bytes'abcd''abcd'4 bytes'abcd'5 bytes'abcdefgh''abcd'4 bytes'abcd'5 bytes

2.基本使用验证

# 函数了解 length: 查看字节数 char_length: 查看字符数 # 验证 create table t11(x char(5)); create table t12(x varchar(5)); # 实际数字后面加一个空格 insert t11 values("我擦嘞 "); -- "我擦嘞 " insert t12 values("我擦嘞 "); -- "我擦嘞 " t11=>字符个数 5 字节个数 11 # 实际的字符、字节数 t11=>字符个数 3 # char显示的字符 t12=>字符个数 4 字节个数 10 # varchar可以显示实际的字符、字节数（字节数上添加了一个位数）varchar认为末尾的空格不是数据的一部分。 # 查询当前的模式 show variables like "sql_mode" set sql_mode="pad_char_to_full_length"; # 严格模式下可以看见实际的字符和字节个数，默认的模式下不是这样显示 select char_length(x) from t11; select char_length(x) from t12; select length(x) from t11; select length(x) from t12; # 查询时默认把末尾的空格都去掉 like模糊匹配，=匹配 select * from 表名 where 字段 like "r%" # like模糊匹配，r开头的数据，后面字符数不限 select * from 表名 where 字段 like "r_" # r开头的数据，后面只有一个字符。

3.总结

# InnoDB存储引擎：建议使用VARCHAR数据类型 单从数据类型的实现机制去考虑，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。 但对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在本质上，使用固定长度的CHAR列不一定比使用可变长度的VARCHAR列性能要好。因而，主要的性能因素是数据行使用的存储总量。由于CHAR平均占用的空间多于VARCHAR，因此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I\O是比较好的。

（5）枚举类型与集合类型

​ 字段的值只能在给定范围中选择，如单选框，多选框；

​ enum 单选，只能在给定的范围内选一个值，如性别 sex 男male/女female

​ set 多选，在给定的范围内可以选择一个或一个以上的值（爱好1,爱好2,爱好3…）

# 示例enum: mysql> create table shirts( -> name varchar(40), -> size enum('x-small', 'small', 'medium', 'large', 'x-large') -> ); # 正常插入值 mysql> insert shirts(name,size) values('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small'); mysql> select * from shirts; +-------------+--------+ | name | size | +-------------+--------+ | dress shirt | large | | t-shirt | medium | | polo shirt | small | +-------------+--------+ # 非正常插入值 mysql> insert shirts values("abc shirts","xxxxxx"); mysql> select * from shirts; +-------------+--------+ | name | size | +-------------+--------+ | dress shirt | large | | t-shirt | medium | | polo shirt | small | | abc shirts | | +-------------+--------+ # 如果是严格模式，则会报错，不允许传入值； # 示例：set mysql> create table user( -> name varchar(16), -> hobbies set("read","chou","drink","tang") -> ); # 正常传值 mysql> insert user values("xxx","tang,chou"); mysql> select * from user; +------+-----------+ | name | hobbies | +------+-----------+ | xxx | chou,tang | +------+-----------+ # 非正常传值 mysql> insert user values("qqq","tangchou"); mysql> select * from user; +------+-----------+ | name | hobbies | +------+-----------+ | xxx | chou,tang | | qqq | | +------+-----------+ # 非正常传值，空

（六）复制表

# 复制表结构+记录（key不会复制：主键，外键和索引）, # select语句查询的内容保存到表t2 全部复制将字段改为* create table t2 select user,host,password from mysql.user; # 只复制表结构， # select语句查询的表结构保存到表t3 全部复制使用* create table t3 select user,host,password from mysql.user where 1!=1;

PS：关于虚拟表：硬盘上不存在表，表存在于内存中，表是硬盘上数据的被select语句组织出来的。

（七）删除表

drop table 表名;