目录
- 👉表的操作👈
- 创建表
- 查看表
- 修改表
- 删除表
👉表的操作👈
创建表
create tabletable_name (
field1 datatype,
field2 datatype,
field3 datatype
) character set 字符集 collate 校验规则 engine 存储引擎;
说明:
- field 表示列名
- datatype 表示列的类型
- character set 字符集,如果没有指定字符集,则以所在数据库的字符集为准
- collate 校验规则,如果没有指定校验规则,则以所在数据库的校验规则为准
- engine 存储引擎,如果没有指定存储引擎,则以配置文件中的存储引擎为准
示例:
mysql> create table if not exists `users`(
-> id int comment '用户的id',
-> name varchar(20) comment '用户的姓名',
-> password varchar(32) comment '用户的登录密码',
-> birthday date comment '用户的生日'
-> ) character set utf8 collate utf8_general_ci engine MyISAM;
使用 MyISAM 存储引擎创建表时,会生成三个磁盘文件:
- .frm 文件:该文件存储表的结构定义,包括字段名、类型、长度、索引等信息。
- .MYD 文件:该文件存储表的数据,也就是表中的记录。
- .MYI 文件:该文件存储表的索引,加速数据的检索。
- 其中,.MYD 文件和 .MYI 文件是 MyISAM 存储引擎的两个核心文件,用于存储表的数据和索引。而 .frm 文件则是所有存储引擎都需要的,用于存储表的结构定义。在 MyISAM 存储引擎中,数据和索引是分开存储的,因此数据和索引都有自己的文件。
需要注意的是,MyISAM 存储引擎不支持事务,因此在数据写入时不会有类似 InnoDB 存储引擎的 redo log 和 undo log 进行数据恢复。如果在写入数据时 MySQL 出现故障,可能会导致数据丢失或者索引损坏。因此,在使用 MyISAM 存储引擎时需要注意备份数据的重要性。
mysql> create table if not exists students(
-> name varchar(20),
-> age int,
-> number varchar(32),
-> sex char(1)
-> )engine=InnoDB default charset=utf8;
使用 InnoDB 存储引擎创建表时,将生成两个磁盘文件:
- table_name.frm:此文件是表结构的定义文件,其中包含表名、列名、数据类型、约束、索引等元数据信息。frm 文件是跨存储引擎共享的,因此它并不依赖于存储引擎类型。
- table_name.ibd:此文件包含表中的实际数据。每个 InnoDB 表都有一个单独的 .ibd 文件,其中包含表的数据和索引。.ibd 文件是 InnoDB 独有的,它将数据和索引存储在同一个文件中,可以实现高效的事务处理和并发控制。
在创建表时,如果没有指定数据文件的路径和名称,InnoDB 存储引擎会将 .frm 和 .ibd 文件存储在 MySQL 数据目录下的与表名同名的子目录中。因此,如果要备份或恢复 InnoDB 表,需要同时备份和恢复 .frm 和 .ibd 文件。
查看表
desc table_name;
- Field:列名;
- Type:列的数据类型;
- Null:列是否允许为空;
- Key:列是否为索引(PRI 为主键,UNI 为唯一键,MUL 为普通索引);
- Default:列的默认值;
- Extra:额外信息(例如,自增属性、字符集等)。
修改表
在项目实际开发中,经常修改某个表的结构,比如字段名字,字段大小,字段类型,表的字符集类型,表的存储引擎等等。我们还有需求,添加字段,删除字段等等。这时我们就需要修改表。
为了方便演示,我们先向表中插入两条数据。
mysql> insert into users (id, name, password, birthday)
-> values ('1', '张三', '1234', '2001-01-01');
Query OK, 1 row affected (0.00 sec)
mysql> insert into users values (2, '李四', '4321', '2001-01-02');
Query OK, 1 row affected (0.00 sec)
mysql> select * from users;
+------+--------+----------+------------+
| id | name | password | birthday |
+------+--------+----------+------------+
| 1 | 张三 | 1234 | 2001-01-01 |
| 2 | 李四 | 4321 | 2001-01-02 |
+------+--------+----------+------------+
2 rows in set (0.01 sec)
添加列
alter table table_name add column column_name column_definition;
示例:
mysql> alter table users add image_path varchar(128) comment '用户头像的路径'
-> after birthday;
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from users;
+------+--------+----------+------------+------------+
| id | name | password | birthday | image_path |
+------+--------+----------+------------+------------+
| 1 | 张三 | 1234 | 2001-01-01 | NULL |
| 2 | 李四 | 4321 | 2001-01-02 | NULL |
+------+--------+----------+------------+------------+
2 rows in set (0.00 sec)
在 MySQL 中,NULL 和空串是不同的概念。
- NULL 表示未知或不存在的值。NULL 不等于任何值,甚至不等于 NULL 本身,可以使用 IS NULL 或 IS NOT NULL 判断一个值是否为 NULL。
- 空串是一个长度为 0 的字符串,表示一个存在但没有值的字符串。空串可以与空串相等,但不等于 NULL。
修改列
# 修改列属性
alter table table_name modify column column_name column_definition;
# 修改列名和属性
alter table table_name change column old_column_name new_column_name column_definition;
示例:
mysql> alter table users modify column name varchar(60);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
- Records:表示在修改表时,受到影响的行数。
- Duplicates:表示在修改表时,由于添加了唯一索引或主键约束,导致出现重复数据的行数。如果没有出现重复数据,该值为 0。
- Warnings:表示在修改表时,可能发生了警告的数量。例如,如果使用 alter ignore 语句来忽略某些错误,则会生成警告。
删除列
alter table table_name drop column column_name;
示例:
注:使用该语句无法删除所有的列,如果想要删除所有的列,可以使用 drop table table_name 语句。不管是删列信息还是删除表,都需要谨慎,考虑清楚!!!
修改表名
alter table old_table_name RENAME new_table_name;
示例:
修改表的存储引擎
alter table table_name engine=engine_name;
其中,engine_name 为要修改为的存储引擎,例如InnoDB、MyISAM 等。
修改表的字符集和校验规则
alter table table_name convert to character set charset_name collate collation_name;
其中,charset_name 为要修改为的字符集,collation_name 为要修改为的校对规则,例如 utf8、utf8_general_ci 等。
删除表
drop table [if exists] table_name;
其中,if exists 是可选的,表示如果表不存在,不会返回错误信息。
执行该语句将会删除指定的表,同时删除表所占用的磁盘空间。如果该表有关联的索引、触发器、约束等对象,也会一并被删除。
需要注意的是,一旦表被删除,其中的数据也将永久删除,因此在执行该语句前应该先备份表数据以防数据丢失。
示例: