MySQL基础操作用法介绍
🚘正片开始
修改表名语法
alter table 旧表名 rename to 新表名；
展示效果
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test |
+----------------+
1 row in set (0.00 sec)
mysql> alter table test rename to temp;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| temp | #表明已被修改
+----------------+
1 row in set (0.00 sec)
mysql>
删除表语法
drop table 表名;
展示效果
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| students |
| test |
+----------------+
2 rows in set (0.00 sec)
mysql> drop table test;
Query OK, 0 rows affected (0.01 sec)
#此时该数据库中test表已被删除
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| students |
+----------------+
1 row in set (0.00 sec)
mysql>
清空表中数据语法
truncate table 表名;
展示效果
mysql> select * from students; #这条语句可以查看表中所有字段中的内容
+--------+------+---------+
| name | age | address |
+--------+------+---------+
| 张三 | 18 | 四川 |
| 李四 | 20 | 非洲 |
| 老六 | 48 | 美国 |
| 王汉桑 | 68 | 日本 |
+--------+------+---------+
4 rows in set (0.00 sec)
mysql> truncate table students;
Query OK, 0 rows affected (0.02 sec)
#可以看见再次显示表中的内容则已经没有数据了
mysql> select * from students;
Empty set (0.00 sec) #空表
mysql>
头部添加字段语法
alter table 表名 add 新字段名 字段类型 first;
展示效果
mysql> desc students; #显示表结构
+---------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| name | varchar(10) | YES | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| address | varchar(25) | YES | | NULL | |
+---------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> alter table students add id tinyint first;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
#表中name前面已经插入了id
mysql> desc students;
+---------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| id | tinyint | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| address | varchar(25) | YES | | NULL | |
+---------+------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql>
指定字段后添加新字段语法
alter table 表名 add 新字段名 字段类型 after 需要插入到哪个字段后面的字段名;
展示效果
mysql> desc students;
+---------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| id | tinyint | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| address | varchar(25) | YES | | NULL | |
+---------+------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table students add sex char(1) after age;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
#表中在指定字段age后面已经插入sex
mysql> desc students;
+---------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| id | tinyint | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| address | varchar(25) | YES | | NULL | |
+---------+------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql>
尾部添加字段语法
alter table 表名 add 新字段名 字段类型;
展示效果
mysql> desc students;
+---------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| id | tinyint | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| address | varchar(25) | YES | | NULL | |
+---------+------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> alter table students add tel char(11);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
#表中尾部已成功插入tel字段
mysql> desc students;
+---------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| id | tinyint | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| address | varchar(25) | YES | | NULL | |
| tel | char(11) | YES | | NULL | |
+---------+------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql>
删除字段语法
alter table 表名 drop 需要删除的字段名;
展示效果
mysql> desc students;
+---------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| id | tinyint | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| address | varchar(25) | YES | | NULL | |
| tel | char(11) | YES | | NULL | |
+---------+------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> alter table students drop tel;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
#观察下面表中tel字段已被成功删除
mysql> desc students;
+---------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| id | tinyint | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| address | varchar(25) | YES | | NULL | |
+---------+------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql>
修改字段的字段名语法
alter table 表名 change 被修改的字段名 新字段名 被修改的字段类型(长度);
展示效果
mysql> desc students;
+---------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| id | tinyint | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| address | varchar(25) | YES | | NULL | |
+---------+------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)```
mysql> alter table students change address pos varchar(25);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
#观察下面表中已经没有了address,变程了pos说明已经修改成功
mysql> desc students;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id | tinyint | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| pos | varchar(25) | YES | | NULL | |
+-------+------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql>
修改字段的类型语法
alter table 表名 modify 被修改的字段名 新字段类型;
展示效果
mysql> desc students;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id | tinyint | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| pos | varchar(25) | YES | | NULL | |
+-------+------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> alter table students modify pos char(25);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
#观察下面表中字段pos的类型已经被改为了char(25)
mysql> desc students;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id | tinyint | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| pos | char(25) | YES | | NULL | |
+-------+------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql>
完结🎉🎉🎉
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
