MySQL常用数据类型和表的操作

(一)常用数据类型

1.数值类

BIT([M]) 大小:bit M表示每个数的位数，取值范围为1~64,若M忽略则默认为1。

TINYINT([M]) 大小：byte 取值范围为-2^7
~2^7-1 无符号取值范围为0~2^8-1。

INT([M]) 大小:4byte 取值范围为-2^31
~2^31-1 无符号取值范围为:2^32-1

BIGINT([M]) 大小:8byte 取值范围为-2^63
~2^63-1 无符号取值范围为0~2^64-1

DECIMAL[(M,D)] 大小:动态 M为总位数，D为小数点后面的位数，DECIMAL最大位数（M）为65，D最大为30,
如果省略M则默认为10,如果省略D则默认为0,

DECIMAL大小为动态：可以由其他数值类组成例如一个数123456789123456789123456789.12345678963就可以由4个INT与1个BIT组成(INT可以储存10位数，这里储存38位数就可以由4个INT与1个BIT组成减少空间浪费)

2.字符串类型

CHAR[(M)] 固定长度字符串，M表示长度，以字符为单位，取值范围为0~255，若M省略则默认为1

VARCHAR[(M)] 可变长度字符串，M表示最大长度范围为0~65535(2^16-1)个字节,有效字符个数取决于实际字符个数与使用的其字符集（例如使用utf8mb4字符集最多可以保存65535/4=16383个字符）

TEXT[(M)] 文本类型,最大长度为2^16-1个字节，有效字符个数为使用的字符集

3.二进制类型

BINARY[(M)] 固定长度二进制字节与CHAR类似 储存二进制字节取值范围为0~255，若M省略则默认为1

VARBINARY[(M)] 可变长度二进制字节与VARCHAR类似 M表示长度

BLOB[(M)] 二进制字节类型(存储二进制文件的字段类型), 最大长度为0~2^16-1

4.日期类型

(Byte 是计算机存储和处理信息的基本单位，而 Bytes 通常用来表示多个字节。
Byte（字节）：一个字节由8个二进制位（bit）组成，可以存储一个英文字符或半个汉字。在数据存储和传输中，字节是最常用的单位

Bytes：这个词通常用来表示多个字节，比如在描述数据大小时，我们可能会说 “这个文件有128 Bytes”，意思是文件大小为128字节)

DATETIME[(fsp)] 8bytes 日期类型与时间类型结合，⽀持范围 1000-01-01 00:00:00.000000 ~ 9999-12-31
23:59:59.499999
• 显⽰格式为 YYYY-MM-DD hh:mm:ss[.fraction]
• 0值为 0000-00-00 00:00:00

DATE[(fsp)] 3bytes ⽇期类型
• ⽀持范围 1000-01-01 ~ 9999-12-31
• 显⽰格式为 YYYY-MM-DD
• 0值为 0000-00-00

(二)表的操作

1查看指定库中所有表

语法: show tables;

2.创建表

语法:CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
field datatype [约束] [comment ‘注解内容’]
[, field datatype [约束] [comment ‘注解内容’]] …
) [engine 存储引擎] [character set 字符集] [collate 排序规则];

TEMPORARY： 表⽰创建的是⼀个临时表
• field：列名
• datatype：数据类型
• comment：对列的描述或说明
• engine：存储引擎，不指定则使⽤默认存储引擎
• character set：字符集，不指定则使⽤默认字符集
• collate：排序规则，不指定则使⽤默认排序规则
详细的创建表语法参考官⽅⽹站:
MySQL :: MySQL 8.0 Reference Manual :: 15.1.20 CREATE TABLE Statement

展示表在磁盘上的扩展文件：
创建⼀个存储引擎为 InnoDB 的表时，会在对应的数据库⽬录下⽣成⼀个⽤来存储真实数据的物
理⽂件，命名格式为 表名.ibd ，以当前为例会在 java ⽬录下⽣成⼀个 t_student.ibd 的数据
⽂件
• 创建⼀个存储引擎为 MyISAM 的表时，会在对应的数据库⽬录下分别⽣成三个以不同后缀名结尾
的⽂件，分别是 表名.MYD ( MYData )的数据⽂件， 表名.MYI ( MYIndex ) 的索引⽂件，以表名.sdi 的表信息描述⽂件(JSON格式)
在8.0以前的版本中表信息描述⽂件是以.frm为后缀的⼆进制⽂件

3.查看表结构和查看表的创建语句

查看表结构语法:desc 表名;
在这里插入图片描述
查看表的创建语句语法：show create table 表名;

4.修改表

语法:5.1 语法
ALTER TABLE tbl_name [alter_option [, alter_option] …];
alter_option: {
table_options
| ADD [COLUMN] col_name column_definition [FIRST | AFTER（注释：FIRST指表中的第一列，AFTER指定列的后一列） col_name]
| MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
| DROP [COLUMN] col_name
| RENAME COLUMN old_col_name TO new_col_name
| RENAME [TO | AS] new_tbl_name

• tbl_name：要修改的表名
• ADD：向表中添加列
• MODIFY：修改表中现有的列
• DROP：删除表中现有的列
• RENAME COLUMN：重命名表中现有的列
• RENAME [TO | AS] new_tbl_name：重命名当前的表

详细的创建表语法参考官⽅⽹站：MySQL :: MySQL 8.0 Reference Manual :: 15.1.9 ALTER TABLE Statement

ADD示例演示:

MODIFY实例演示：

DROP实例演示:
– 删除最后表中age列
alter table t_student drop age;

RENAME COLUMN实例演示:

RENAME实例演示:

5.删除表

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [,tbl_name]

TEMPORARY：表⽰临时表
tbl_name：将要删除的表名

• 删除表是⼀个危险操作，执⾏删除语句时⼀定要谨慎
• 删除表成功后，磁盘上对应的数据⽂件也会被删除
• ⼀次可以删除多个表，表与表之间⽤逗号隔开

(三)总代码

create table if not exists t_student (
  id bigint comment '编号',
 `name` varchar(15) comment '姓名',
  age int comment '年龄'
  )character set utf8mb4 collate utf8mb4_0900_ai_ci;
  
  #单行注释
  -- 单行注释（--后需要加空格）
  /*
  
  */多行注释
  #添加表中第一列来演示
  alter table t_student add `first` int comment'添加第一行' FIRST;
  #往id后一列添加
  alter table t_student add ids bigint comment'添加到id后一行' after id;
  #修改name这行来演示
  alter table t_student modify `name` varchar(25) comment '姓名' after ids; 
  -- 删除最后表中age列 
  alter table t_student drop age;
  #将表中第一列的first重命名为one
  alter table t_student rename column `first` to one;
  #修改表名
  alter table t_student tbl_name t_newstudent;
  create table if not exists t_engine(
  id bigint comment '编号',
  `engine` varchar(20) comment '引擎'
  )engine MyISAM;
  #删除t_engine这个表
  drop table t_engine;