创建表(表数据类型、表数据类型选项、表选项、列选项、if not exists选项)、临时表操作

最新推荐文章于 2024-11-13 13:58:58 发布

我说，你好

最新推荐文章于 2024-11-13 13:58:58 发布

阅读量854

点赞数 1

CC 4.0 BY-SA版权

分类专栏： MYSQL开发学习

本文链接：https://blog.youkuaiyun.com/weixin_44569143/article/details/88917906

MYSQL开发学习专栏收录该内容

18 篇文章

订阅专栏

本文详细介绍了MySQL中创建表的要素，包括数据类型如整数、定点小数和浮点数、字符串类型，以及日期/时间类型。此外，还讲解了数据类型选项，如unsigned和zerofill，列选项如默认值和注释，表选项如存储引擎和自增列设置，以及if not exists和临时表的概念。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

对于一张表来说，最基本的是列标题，列的数据类型。通过这二者可以建最基本的表，在此基础上还可以延伸出很多选项

一、列的数据类型(常用)

一、数值类型

1.整数类型(M表示最小显示宽度，将存储值超过显示宽度，显示宽度会自动增加)

1.INT[(M)] [UNSIGNED] [ZEROFILL]

The signed range is -2147483648 to 2147483647.

The unsigned range is 0 to 4294967295.

2.TINYINT[(M)] [UNSIGNED] [ZEROFILL]

A very small integer. The signed range is -128 to 127. The unsigned

range is 0 to 255.

3.SMALLINT[(M)] [UNSIGNED] [ZEROFILL]

A small integer. The signed range is -32768 to 32767. The unsigned

range is 0 to 65535.

4.BIGINT[(M)] [UNSIGNED] [ZEROFILL]

A large integer. The signed range is -9223372036854775808 to

9223372036854775807. The unsigned range is 0 to 18446744073709551615.

5.MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]

medium-sized integer. The signed range is -8388608 to 8388607. The

unsigned range is 0 to 16777215.

举例：

mysql> create table t1(id int(3));------------显示宽度是3位

Query OK, 0 rows affected (0.09 sec)



mysql> insert into t1 values(3),(30),(3000),(3000.5),(3000.4);

Query OK, 5 rows affected (0.01 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from t1;

+------+

| id |

+------+

| 3 |

| 30 |

| 3000 |

| 3001 |

| 3000 |

+------+

5 rows in set (0.00 sec)

2.定点小数类型(M表示最多能存的总长度，D表示小数点的位数，可以存储D位小数的M位数，最多是小数位是30的65位数，默认是存储小数位是0的10位数)

只想要小数：M=D;

只想要整数：D=0;允许插入小数，但是会对第一位小数四舍五入取整数

小数位固定，不会存储近似值

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

举例：

mysql> create table t2(id decimal(4,2));

Query OK, 0 rows affected (0.04 sec)

mysql> insert into t2 values(111);------------------------------整数位数只能小于等于2

ERROR 1264 (22003): Out of range value for column 'id' at row 1



mysql> insert into t2 values(11),(11.55),(11.45),(11.456),(11.554);

Query OK, 5 rows affected, 2 warnings (0.00 sec)

Records: 5 Duplicates: 0 Warnings: 2



mysql> select * from t2;

+-------+

| id |

+-------+

| 11.00 |

| 11.55 |

| 11.45 |

| 11.46 |------------------允许插入小数位多于三位的数，但是多出来的部分，取第一位去四舍五入

| 11.55 |

+-------+

3.浮点小数类型

小数位数不固定，假设超出存储范围，近似值进行存储

float(0)-float(24) 精确存储6位

float(25)-float(53) 精确存储15位

1.FLOAT[(M,D)] [UNSIGNED] [ZEROFILL] (单精度浮点类型)数据精确到小数点后7位，对第8位进行四舍五入

2.DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL] (双精度浮点类型)数据精确到小数点后15位，

二、字符串类型(M表示最多存的字符数目，汉字一般占三个字节，字母一般占一个字节)

文本字符串

1.char[(length)]:固定长度字符串。值少于长度则在尾部自动填充空格。length的单位是字符(当长度为0时，只能插入null值或者空字符串)

同义词：text

2.varchar[(length)]:可变长度字符串，取值是0-65535

3.longvarchar:0-（2^24-1）

同义词：mediumtext

枚举类型(enum、set)-----------只能是字符串类型的

当列使用枚举时，注意事项：排序问题

三、.日期/时间类型

1.date: 3字节

2.time: 3字节

3.datetime: 8字节

4.year: 1字节

5.timestamp: 4字节

二、添加数据类型选项(可以通过制定一个数据类型选项，以改变数据类型的属性和功能)

对于字符串类型，可以添加两个类型选项：character set和collate

对于bit之外的所有数字类型，可以添加一个或几个类型选项：unsigned、zerofill、auto_increment、serial default value(是非空、自增、唯一键的集合缩写)

1.unsigned:无符号的，表示只显示正数

2.zerofill：填充0

影响数字的显示方式：如果一个数字的宽度小于所允许的最大宽度，这个值前面会用0填充，如果声明了zerofill,该列会自动设置unsigned

3.auto_increment：自动增长。只适应于整数类型。一个表中只有一个列可以是自动增长的。

mysql> show variables like '%increment%';

+-----------------------------+-------+

| Variable_name | Value |

+-----------------------------+-------+

| auto_increment_increment | 1 |--------------------每次增长间隔是多少(自己设定变量)

| auto_increment_offset | 1 | --------------------从那个数字开始增长

| div_precision_increment | 4 |

| innodb_autoextend_increment | 64 |

+-----------------------------+-------+

可以通过设定两个系统变量来改变默认行为：

1、set @@auto_incerment_increment=

2、set @@auto_increment_offset=

举例

mysql> create table test(id int(5) zerofill,id1 int unsigned,id3 int unsigned auto_increment primary key );



1.会自动记住最大值，当再次向该列输入而没有赋值时，就用最大值+1(即使删除了最大值那一行，也会记住最大值是多少)

2.当自己输入时，可以不按照排序一个一个输入，前面值是2，后面一个值也可以输入10(但是如果是自动增长的---是主键的，自己插入的值一定不能跟表内存在的值重复)





mysql> insert into test values(1,1001,1);

Query OK, 1 row affected (0.00 sec)



mysql> insert into test values(6,1006,6);

Query OK, 1 row affected (0.01 sec)



mysql> insert into test values(7,1007,null);

Query OK, 1 row affected (0.00 sec)



mysql> select * from test;

+-------+------+-----+

| id | id1 | id3 |

+-------+------+-----+

| 00001 | 1001 | 1 |

| 00006 | 1006 | 6 |

| 00007 | 1007 | 7 |

+-------+------+-----+



删除表中id3列最大的一个值

mysql> delete from test where id3=7;

Query OK, 1 row affected (0.00 sec)



mysql> insert into test values(10,1010,null);

Query OK, 1 row affected (0.00 sec)



mysql> select * from test;

+-------+------+-----+

| id | id1 | id3 |

+-------+------+-----+

| 00001 | 1001 | 1 |

| 00006 | 1006 | 6 |

| 00010 | 1010 | 8 |

+-------+------+-----+

三、列选项：

定义列时，除了指定列名字、数据类型、大小之外，还可以制定列选项

1、给列指定默认值

注意：当给表指定默认值时，如果每插入一行数据，而默认值那一列的数据没有指定时，会自动插入默认值，因此可能会大量插入默认值而导致出错

1、也可以在insert和update语句中使用default关键字显式的给列赋默认值

update t1 set sex=default;

mysql> insert into t1 values(1,default);

Query OK, 1 row affected (0.00 sec)



mysql> select * from t1;

+------+------+

| id | sex |

+------+------+

| 1 | 男 |

+------+------+

1 row in set (0.00 sec)



mysql> insert into t1(sex) values('女');

Query OK, 1 row affected (0.00 sec)



mysql> select * from t1;

+------+------+

| id | sex |

+------+------+

| 1 | 男 |

| NULL | 女 |

+------+------+

2 rows in set (0.00 sec)

2、函数default(column)可以得到一个列的函数值

mysql> create table t1(id int,birth date default '20190319');

Query OK, 0 rows affected (0.05 sec)



mysql> insert into t1 values(1,default);

Query OK, 1 row affected (0.00 sec)



mysql> insert into t1 values(2,default(birth));

Query OK, 1 row affected (0.00 sec)



mysql> select * from t1;

+------+------------+

| id | birth |

+------+------------+

| 1 | 2019-03-19 |

| 2 | 2019-03-19 |

+------+------------+

2 rows in set (0.00 sec)

2、comment

给列添加注释。最多255个字符，注释会保存到数据字典中

四、表选项：

1、engine：指定表使用的存储引擎

存储引擎决定了该数据如何存储以及如何访问，还有事务如何处理

2、auto_increment:该选项决定了向表中插入第一行时，自增列得到的第一个值是多少

mysql> create table t1(id int auto_increment primary key) auto_increment=10;-----------------从10开始增长

mysql> insert into t1 values(null);

Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;

+----+

| id |

+----+

| 10 |

| 11 |

+----+

3、comment：给表添加注释(查询information_schema.tables)

select table_name,table_comment from information_schema.tables where table_name= and table_schema;

五、if not exists选项

当创建表时，如果表已经存在，则Mysql会返回出错消息。添加 if not exist选项，则强制不显示出错消息。语句执行失败

mysql> create table stu(id int,name varchar(20));

ERROR 1050 (42S01): Table 'stu' already exists



mysql> create table if not exists stu(id int,name varchar(20)); --------没有报错，但是语句也没有执行

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;

+-------+------+----------------------------+

| Level | Code | Message |

+-------+------+----------------------------+

| Note | 1050 | Table 'stu' already exists

六、创建临时表

临时表是一张表，用来临时保存一些数据，消耗资源非常小。它只对创建它的用户可见。当会话结束时，Mysql自动删除临时表

临时表：消耗资源非常小(在建立临时表、删除临时表以及里面的数据时)

create temporary table table_name();





mysql> create table t1(id int,name varchar(10));------------创建普通表t1

Query OK, 0 rows affected (0.03 sec)



mysql> create temporary table t1(id int);------------创建临时表t1

Query OK, 0 rows affected (0.00 sec)

mysql> desc t1;-------------------------------------------在使用的时候，用的首先是临时表t1

+-------+---------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

+-------+---------+------+-----+---------+-------+

1 row in set (0.00 sec)