【MySQL篇】表的操作

用户11719958

发布于 2025-12-30 15:07:04

2260

1，创建表

语法：

create table ( field1 datatype, field2 datatype, field3 datatype )charset 字符集 collate 校验规则 engine 存储引擎;

说明：

field表示列名
datatype表示列的类型
charset字符集，如果没有指明，则以所在数据库的字符集为准
collate校验规则，如果没有指明，则以所在数据库的校验规则为准

示例：

mysql> use study1;//进入数据库 Database changed mysql> create table user1( //建表 -> id int, -> name varchar(20), -> password varchar(32), -> birthday date -> )charset utf8 engine MyISAM; Query OK, 0 rows affected, 1 warning (0.01 sec)

不同的存储引擎，创建表的文件不一样。user1表的存储引擎是MyISAM，在数据目录中的文件如下图：

root@hcss-ecs-982b:/var/lib/mysql/study1# ll total 16 drwxr-x--- 2 mysql mysql 4096 Feb 26 15:32 ./ drwx------ 8 mysql mysql 4096 Feb 26 15:29 ../ -rw-r----- 1 mysql mysql 3954 Feb 26 15:32 user1_365.sdi -rw-r----- 1 mysql mysql 0 Feb 26 15:32 user1.MYD -rw-r----- 1 mysql mysql 1024 Feb 26 15:32 user1.MY

2，查看表结构

desc 表名;

mysql> desc user1;//查看表            索引类型      扩充
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| password | varchar(32) | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
 // 字段名字  字段类型    是否允许为空     默认值

3，修改表

修改表的结构，比如字段名称，字段大小，字段类型，表的字符集，表的存储引擎等等。还有需求添加字段，删除字段等。

先往user1表中插入一些数据：

mysql> insert into user1 values (1,'a','b','2025-01-01'), (2,'b','c','2025-022-26');//插入数据
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from user1;
+------+------+----------+------------+
| id   | name | password | birthday   |
+------+------+----------+------------+
|    1 | a    | b        | 2025-01-01 |
|    2 | b    | c        | 2025-02-26 |
+------+------+----------+------------+
2 rows in set (0.00 sec)

在user表中增加一个字段，来保存图片路径：

mysql> desc user1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| password | varchar(32) | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

mysql> alter table user1 add assets varchar(100) after birthday;//birthday后添加一个assets字段
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc user1;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | YES  |     | NULL    |       |
| name     | varchar(20)  | YES  |     | NULL    |       |
| password | varchar(32)  | YES  |     | NULL    |       |
| birthday | date         | YES  |     | NULL    |       |
| assets   | varchar(100) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

修改name，将其长度改为60

mysql> alter table user1 modify name varchar(60);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc user1;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | YES  |     | NULL    |       |
| name     | varchar(60)  | YES  |     | NULL    |       |
| password | varchar(32)  | YES  |     | NULL    |       |
| birthday | date         | YES  |     | NULL    |       |
| assets   | varchar(100) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

删除password列

注意：删除字段，其对应的列信息也就没有了

mysql> alter table user1 drop password;
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc user1;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | YES  |     | NULL    |       |
| name     | varchar(60)  | YES  |     | NULL    |       |
| birthday | date         | YES  |     | NULL    |       |
| assets   | varchar(100) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

修改表明为empoyee

mysql> alter table user1 rename to employee;
Query OK, 0 rows affected (0.01 sec)

mysql> desc employee;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | YES  |     | NULL    |       |
| name     | varchar(60)  | YES  |     | NULL    |       |
| birthday | date         | YES  |     | NULL    |       |
| assets   | varchar(100) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

注意：to可以省略掉

将name列需改为xingming

mysql> alter table employee change name xingming varchar(20);//需要完整的定义
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc employee;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | YES  |     | NULL    |       |
| xingming | varchar(20)  | YES  |     | NULL    |       |
| birthday | date         | YES  |     | NULL    |       |
| assets   | varchar(100) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

4，删除表

drop table 表名;

mysql> drop table employee;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
Empty set (0.00 sec)

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2025-02-26，如有侵权请联系 cloudcommunity@tencent.com 删除

数据

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度

【MySQL篇】表的操作

【MySQL篇】表的操作

1，创建表

2，查看表结构

3，修改表

4，删除表

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐