【MySQL篇】表的操作

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)