MySQL中的列操作

最新推荐文章于 2024-03-20 00:30:00 发布

PhysicsCoding

最新推荐文章于 2024-03-20 00:30:00 发布

阅读量1.3k

点赞数 1

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/zyp666888/article/details/134494161

版权

修改表

针对表的列结构和数据类型以及数据进行修改。

修改表的几个特点：
修改表的列结构
修改列的数据类型
添加列
修改列的位置
修改列名和数据类型
删除列
设置主键
使列具有自动连续编号功能
使用自动连续编号功能插入记录
设置连续编号的初始值
设置列的默认值
创建索引

1、使用指令来修改表中的列进行修改的操作：

1.1、修改列的数据类型：

mysql_db1>desc tb1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.001 sec)

mysql_db1>alter table tb1 modify name text;
Query OK, 7 rows affected (0.043 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql_db1>desc tb1;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| name  | text | YES  |     | NULL    |       |
| age   | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
2 rows in set (0.001 sec)

根据上述的案例，我们可以使用alter table tableName modify listName DataType来对对应的列的数据类型进行修改。

1.2、添加表中的列的方法：

使用的指令如下：

alter table TableName add listName Prams;

我们在数据库上对其进行操作：

mysql_db1>alter table tb1 add description TEXT;
Query OK, 0 rows affected (0.014 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql_db1>desc tb1;
+-------------+------+------+-----+---------+-------+
| Field       | Type | Null | Key | Default | Extra |
+-------------+------+------+-----+---------+-------+
| name        | text | YES  |     | NULL    |       |
| age         | int  | YES  |     | NULL    |       |
| description | text | YES  |     | NULL    |       |
+-------------+------+------+-----+---------+-------+
3 rows in set (0.001 sec)

对应的指令的操作结果如上所示。

1.3、修改列的位置：

这里的修改位置有两种修改方式：

在进行add操作的时候，对列的位置进行修改。
在使用modify操作的时候，对列的位置进行修改。

1.3.1、在插入操作时进行修改：

1）在表的开头进行插入：

alter table TableName add ListName prams first;

对应的使用案例如下所示：

mysql_db1>alter table tb1 add date datetime first;
Query OK, 0 rows affected (0.013 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql_db1>desc tb1
    -> ;
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| date        | datetime | YES  |     | NULL    |       |
| name        | text     | YES  |     | NULL    |       |
| age         | int      | YES  |     | NULL    |       |
| description | text     | YES  |     | NULL    |       |
+-------------+----------+------+-----+---------+-------+
4 rows in set (0.001 sec)

我们可以发现，我们已经在表的开头插入了这个data属性。

2）在表中的任意位置插入新的列：

使用如下的指令来行对任意位置的列进行插入操作：

alter table TableName add ListName Prams after Listname;

对应的指令如下所示：

mysql_db1>alter table tb1 add new_date datetime after description;
Query OK, 0 rows affected (0.014 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql_db1>desc tb1;
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| date        | datetime | YES  |     | NULL    |       |
| name        | text     | YES  |     | NULL    |       |
| age         | int      | YES  |     | NULL    |       |
| description | text     | YES  |     | NULL    |       |
| new_date    | datetime | YES  |     | NULL    |       |
+-------------+----------+------+-----+---------+-------+
5 rows in set (0.001 sec)

使用上述的指令我们可以发现，我们的new_date数据列被插入在表的最后一列。

1.3.2、在更改操作时，进行修改：

mysql_db1>desc tb1;
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| date        | datetime | YES  |     | NULL    |       |
| name        | text     | YES  |     | NULL    |       |
| age         | int      | YES  |     | NULL    |       |
| description | text     | YES  |     | NULL    |       |
| new_date    | datetime | YES  |     | NULL    |       |
+-------------+----------+------+-----+---------+-------+
5 rows in set (0.002 sec)

针对上述的表中的结构来进行更改。

1）将列更改到表的开头

alter table TableName modify ListName Prams first;

对应的使用结果如下所示：

mysql_db1>alter table tb1 modify new_date datetime first;
Query OK, 0 rows affected (0.037 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql_db1>desc tb1;
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| new_date    | datetime | YES  |     | NULL    |       |
| date        | datetime | YES  |     | NULL    |       |
| name        | text     | YES  |     | NULL    |       |
| age         | int      | YES  |     | NULL    |       |
| description | text     | YES  |     | NULL    |       |
+-------------+----------+------+-----+---------+-------+
5 rows in set (0.001 sec)

2）将列更改到表中的任意位置

我们下面将对应的date列移动到这个表的末尾：

mysql_db1>alter table tb1 modify date datetime after description;
Query OK, 0 rows affected (0.035 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql_db1>desc tb1;
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| new_date    | datetime | YES  |     | NULL    |       |
| name        | text     | YES  |     | NULL    |       |
| age         | int      | YES  |     | NULL    |       |
| description | text     | YES  |     | NULL    |       |
| date        | datetime | YES  |     | NULL    |       |
+-------------+----------+------+-----+---------+-------+
5 rows in set (0.001 sec)

对应的操作都如上所示，我们发现我们都已经成功的将其进行修改。

1.4、修改列名和数据类型：

如上所示，我们已经学会了如何去修改数据类型了位置，在我们进行数据类型和位置的修改的同时，若想改变其列名的话，就需要使用CHANGE操作；

对应的操作方法（移动位置，并修改名称）：

mysql_db1>alter table tb1 change date date_2 datetime after age
    -> ;
Query OK, 0 rows affected (0.035 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql_db1>desc tb1;
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| new_date    | datetime | YES  |     | NULL    |       |
| name        | text     | YES  |     | NULL    |       |
| age         | int      | YES  |     | NULL    |       |
| date_2      | datetime | YES  |     | NULL    |       |
| description | text     | YES  |     | NULL    |       |
+-------------+----------+------+-----+---------+-------+
5 rows in set (0.001 sec)

1.5、删除列：

使用删除列的语句：

alter table TableName drop ListName;

下面，我们使用这句话来删除数据库中的new_date的列：

mysql_db1>desc tb1
    -> ;
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| new_date    | datetime | YES  |     | NULL    |       |
| name        | text     | YES  |     | NULL    |       |
| age         | int      | YES  |     | NULL    |       |
| date_2      | datetime | YES  |     | NULL    |       |
| description | text     | YES  |     | NULL    |       |
+-------------+----------+------+-----+---------+-------+
5 rows in set (0.001 sec)

mysql_db1>alter table tb1 drop new_date;
Query OK, 0 rows affected (0.013 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql_db1>desc tb1;
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| name        | text     | YES  |     | NULL    |       |
| age         | int      | YES  |     | NULL    |       |
| date_2      | datetime | YES  |     | NULL    |       |
| description | text     | YES  |     | NULL    |       |
+-------------+----------+------+-----+---------+-------+
4 rows in set (0.001 sec)

2、列的相关的属性：

我们先从desc TableName来查看，列中都存在哪些相关的属性；

mysql_db1>desc tb1;
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| name        | text     | YES  |     | NULL    |       |
| age         | int      | YES  |     | NULL    |       |
| date_2      | datetime | YES  |     | NULL    |       |
| description | text     | YES  |     | NULL    |       |
+-------------+----------+------+-----+---------+-------+
4 rows in set (0.001 sec)

上述的属性中，Type：描述的是一个列对应的数据类型。Null：是标记着当前的列中是否允许使用NULL来作为自己的值。key：这里就是标识一个列的特定的约束（主键就是其中的约束中的一种）。Default：这里的意思是，我们给对应的列设置的默认值是什么。最后一个Extra：就是额外的附加的属性。

所以，这里的Type、NULL、Key、Default和Extra属性就是我们需要去理解的列的五大属性。

由于之前已经说过了对应的数据类型的相关的内容。所以，下面我们从主键开始，因为主键也是一个非常重要的点。

2.1、主键：

让每个员工都有一个独一无二的会员 ID，或者让一个商品条码仅对应一种价格等。

这种“只会确定一个”的独一无二的状态，称为唯一（unique）

这种绝对的独一无二性，需要保证两点：

主键上的每一个都不会重复。
主键上的值不可以为NULL

当然对应的创建指令还是蛮简单的：

当我们在创建一个列的时候可以使用如下的方式来进行主键的确认：

对应的创建主键的指令：

CREATE TABLE TableName (a INT PRIMARY KEY ,b VARCHAR(10));

对应的使用案例如下所示：

mysql_db1>create table tb3 (a int primary key,b varchar(10));
Query OK, 0 rows affected (0.015 sec)

mysql_db1>desc tb3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a     | int         | NO   | PRI | NULL    |       |
| b     | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.001 sec)

给已经存在的，没有设置主键约束的表设置主键：

【注意点】

如果表中已经存在数据，那么在添加主键约束之前，需要确保指定的列中没有重复的值。否则，添加主键约束会失败。可以先使用SELECT语句来检查指定列中是否存在重复的值，然后根据需要进行数据清理或调整。

需要注意的是，添加主键约束后，表中的数据将会按照指定的列进行唯一性检查。如果存在重复的值，那么添加主键约束也会失败。因此，在执行ALTER TABLE语句之前，需要确保指定的列中不包含重复的值。

如使用如下的语句：

alter table TableName ADD Primary key (Column_name);

下面，看对应的用法：

mysql_db1>select * from tb2;
+------+------+------+
| name | age  | date |
+------+------+------+
| zyp  |   20 | NULL |
| zyp1 |   30 | NULL |
| zyp2 |   40 | NULL |
+------+------+------+
3 rows in set (0.000 sec)

mysql_db1>alter table tb2 add primary key (name);
Query OK, 0 rows affected (0.030 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql_db1>desc tb2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | NO   | PRI | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
| date  | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.001 sec)

我们，发现这里的key关键字上是存在对应的值的：PRI

有此标记的列，便是主键。

2.2、默认值（`Default`值的使用）：

在设置列的默认值时，需要给列加上 DEFAULT关键字。

下面还是从两个方面来学习：

在创建表的时候，进行Default关键字的设置。
给已存在的表添加新的属性。

1、在创建表的时候，进行Default的关键字的设置：

对应的语法为：

create table TableName (column_name DataType Default 默认值);

2、给已存在的表，添加Default关键字的配置：

alter table TableName modify column_name DataType Default 默认值;

2.3、和主键类似的（唯一性）：

唯一键和主键的最大的区别就是：唯一键中可以存在NULL值的。

在创建表的时候对应的设置方法为：

create table TableName (a int unique,b varchar(10));

当一个表存在的时候，对应的属性的设置方法：

对应的语法如下：

alter table TableName add unique (column_Name);

使用案例如下所示：

mysql_db1>alter table tb3 add unique (b);
Query OK, 0 rows affected (0.012 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql_db1>desc tb3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a     | int         | NO   | PRI | NULL    |       |
| b     | varchar(10) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.001 sec)

通过上面的执行结果，我们可以看到项目 NULL 显示为 YES，即允许在列中输入空值但不允许重复。

2.4、`Extra`属性：自动连续编号的功能：

为了能够使列具有自动编号的功能，需要确保其具有如下的三个特点：

数据类型必须为int等整数类型；
加上auto_increment；
使列具有唯一性，最好设置为primary key主键；

创建具有自动连续编号的功能的语法：

CREATE TABLE t_series (a INT AUTO_INCREMENT PRIMARY KEY,b VARCHAR(10));

对应的使用案例：

mysql> CREATE TABLE t_series (a INT AUTO_INCREMENT PRIMARY KEY,b VARCHAR(10));
Query OK, 0 rows affected (0.08 sec)
mysql> DESC t_series;
+-------+-------------+------+-----+---------+----------------+
| Field | 		Type 		| Null | Key | Default | 		Extra				|
+-------+-------------+------+-----+---------+----------------+
| 	a 	| 	int(11)		|  NO  | PRI |	NULL	 | auto_increment |
| 	b	 	| varchar(10) |  YES |		 | 	NULL   | 								|
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)

我们可以发现，我们设置了auto_increment属性的列，被记录在Extra中。

2.4.1、向该表中插入值：

要想在设置了自动连续编号功能的列中自动输入连续编号，就需要采用输入 0，或者输入空值
（输入 NULL）等方法。

或者不在该列中输入任何内容，对应的操作如下所示：

insert into TableName (column_name) values(column_name_value);
insert into TableName (auto_column_name,column_name) values(0,column_name_value);
insert into Tablename (auto_column_name,column_name) values(NULL,column_name_value);

2.4.2、设置连续编号的初始值：

INSERT INTO t_series VALUES(100，' 卯 ');，列 a 中就会输入值

“100”，然后从“101”开始分配连续的编号，即从已经输入的最大值 +1 开始分配值。由于设置了

PRIMARY KEY 属性，所以不能输入重复的值

为什么要有这样的操作？因为假设有一个表，里面存在数据。有一天，我们使用语句将里面的所有的数据全部删除DELETE FROM t_series。然后，再继续向其中插入数据的时候，对应的具有auto_increment属性的列依然会从之前的序号依次的往上递增。

对应的操作如下所示：

mysql> SELECT * FROM t_series;
+---+------+
| a | 	b  |
+---+------+
| 1 |  子  |
| 2 |  丑  |
| 3 |  寅  |
+---+------+
3 rows in set (0.00 sec)
mysql> DELETE FROM t_series;
Query OK, 3 rows affected (0.01 sec)
mysql> INSERT INTO t_series (b) VALUES('XX');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM t_series;
+---+------+
| a |  b   |
+---+------+
| 4 |  XX  |
+---+------+
1 row in set (0.00 sec)

所以，我们的使用方式是：

alter table TableName auto_increment=1;

这样的话可以将表中的所有的 auto_increment 数据来进行初始化。

3、总结：

3.1、数据库的实体是什么？

在Linux中，我们可以看到在/var/lib/mysql/目录中存在的文件：

[zyp@iZ0jl9sgt76d4kh58mvinxZ mysql]$ pwd
/var/lib/mysql
[zyp@iZ0jl9sgt76d4kh58mvinxZ mysql]$ ls
 auto.cnf        binlog.000008   binlog.000016     db1                  mysql                public_key.pem
 binlog.000001   binlog.000009   binlog.000017    '#ib_16384_0.dblwr'   mysql.ibd            server-cert.pem
 binlog.000002   binlog.000010   binlog.000018    '#ib_16384_1.dblwr'   mysql.sock           server-key.pem
 binlog.000003   binlog.000011   binlog.index      ib_buffer_pool       mysql.sock.lock      sys
 binlog.000004   binlog.000012   ca-key.pem        ibdata1              mysqlx.sock          testdb1
 binlog.000005   binlog.000013   ca.pem            ibtmp1               mysqlx.sock.lock     TESTFLASK
 binlog.000006   binlog.000014   client-cert.pem  '#innodb_redo'        performance_schema   undo_001
 binlog.000007   binlog.000015   client-key.pem   '#innodb_temp'        private_key.pem      undo_002

我们创建的数据库文件全部在其中。

其中存在对应的三种文件：

db.opt：记述默认字符编码等选项的文本文件。

MySQL5.7 中会在每个数据库的目录下生成 db.opt 文件用于保存数据库的相关配置。比如：字符集、比较规则。而MySQL8.0不再提供db.opt文件

表名 .frm：保存表的元数据（表定义等）的文件

表名 .ibd：保存在表中的数据的实体

系统表空间： 默认情况下，InnoDB会在数据库目录下创建一个名为ibdata1、大小12M的文件，即为系统表空间在文件系统上的表示。该文件是一个自扩展文件，容量不足会自己扩展。

**独立表空间：**从``MySQL5.6.6开始，InnoDB不再把表数据存储到系统表空间，而实为每个表建立独立表空间。而这个独立表空间对应的文件就是.ibd`文件。其中存储了对应的表中的数据和索引。

对应的，我们可以在mysql中使用如下的指令来查看对应的表空间的类型：

show variables like "innodb_file_per_table"
# 若结果返回值为NO则表示使用独立表空间，如下所示:
MySQL [(none)]> show variables like "innodb_file_per_table";
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.004 sec)

我们，可以在对应的my.cnf文件中对其进行修改：

[server]
innodb_file_per_table=0 # 0：代表使用系统表空间； 1：代表使用独立表空间

最后的输出为：

[root@iZ0jl9sgt76d4kh58mvinxZ mysql]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.35 Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| TESTFLASK          |
| db1                |
| db2                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb1            |
+--------------------+
8 rows in set (0.003 sec)

MySQL [(none)]> show variables like "innodb_file_per_table";
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF   |
+-----------------------+-------+

MySQL8.0由于可能做了一些改动，无法在/var/lib/mysql中创建文件夹，然后默认创建数据库了。

PhysicsCoding

关注

1
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
MySQL中的列操作

在Linux中，我们可以看到在我们创建的数据库文件全部在其中。db.opt：记述默认字符编码等选项的文本文件。 MySQL5.7 中会在每个数据库的目录下生成db.opt文件用于保存数据库的相关配置。比如：字符集、比较规则。而MySQL8.0不再提供db.opt文件表名 .frm：保存表的元数据（表定义等）的文件表名 .ibd：保存在表中的数据的实体默认情况下，InnoDB会在数据库目录下创建一个名为ibdata1、大小12M的文件，即为系统表空间在文件系统上的表示。
复制链接

扫一扫