数据表的基本操作

创建数据库
mysql> CREATE DATABASE shang;
Query OK, 1 row affected
使用数据库
USE 数据库名
mysql> USE shang;
Database changed
在数据库里创建数据表
CREATE TABLE 数据表名
-> (
-> 字段名1 数据类型[完整性约束条件],
-> 字段名2 数据类型[完整性约束条件],
-> 字段名3 数据类型[完整性约束条件],
-> );
[   ]里的可有可无
mysql> CREATE TABLE xx_tb
-> (
-> id INT(20),
-> name CHAR(20),
-> grade FLOAT
-> );
Query OK, 0 rows affected
验证数据表是否创建成功
mysql> SHOW TABLES;
+-----------------+
| Tables_in_shang |
+-----------------+
| xx_tb |
+-----------------+
1 row in set
查看数据表的两种方法
1.SHOW CREATE TABLE 数据表的名称;
mysql> SHOW CREATE TABLE xx_tb;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| xx_tb | CREATE TABLE `xx_tb` (
`id` int(20) DEFAULT NULL,
`name` char(20) DEFAULT NULL,
`grade` float DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
2.DESCRIBE 数据表的名称;
mysql> DESCRIBE xx_tb;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(20) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
| grade | float | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in se
DESCRIBE也可以缩写为DESC
修改数据表的名称
ALTER TABLE 旧数据表名称 RENAME [TO] 新数据表名称;
mysql> ALTER TABLE xx_tb RENAME TO name_tb;
Query OK, 0 rows affected
查看数据表的名称是否修改成功
mysql> SHOW TABLES;
+-----------------+
| Tables_in_shang |
+-----------------+
| name_tb |
+-----------------+
1 row in set
修改数据表的字段名
ALTER TABLE 数据表名称 CHANGE 旧字段名 新字段名 新的数据类型;
mysql> ALTER TABLE name_tb CHANGE id newid INT(20);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
查看是否修改成功
mysql> DESC name_tb;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| newid | int(20) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
| grade | float | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set
修改字段的数据类型
ALTER TABLE 数据表名称 MODIFY 字段名 新数据类型;
mysql> ALTER TABLE name_tb MODIFY newid INT(10);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
查看是否修改成功
mysql> DESC name_tb;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| newid | int(10) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
| grade | float | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set
添加字段（最后一行）
ALTER TABLE 数据表名称 ADD 新字段名 数据类型;
mysql> ALTER TABLE name_tb ADD address CHAR;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
查看是否添加成功
mysql> DESC name_tb;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| newid | int(10) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
| grade | float | YES | | NULL | |
| address | char(1) | YES | | NULL | |
+---------+----------+------+-----+---------+-------+
4 rows in set
添加字段（第一行）
ALTER TABLE 数据表名称 ADD 新字段名 数据类型 FIRST;
mysql> ALTER TABLE name_tb ADD xuehao CHAR FIRST;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
查看是否添加成功
mysql> DESC name_tb;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| xuehao | char(1) | YES | | NULL | |
| newid | int(10) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
| grade | float | YES | | NULL | |
| address | char(1) | YES | | NULL | |
+---------+----------+------+-----+---------+-------+
5 rows in set
添加字段（一个字段名后面）
ALTER TABLE 数据表名称 ADD 新字段名 数据类型 AFTER 已有的字段名;
mysql> ALTER TABLE name_tb ADD aihao CHAR AFTER name;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
查看是否添加成功
mysql> DESC name_tb;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| xuehao | char(1) | YES | | NULL | |
| newid | int(10) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
| aihao | char(1) | YES | | NULL | |
| grade | float | YES | | NULL | |
| address | char(1) | YES | | NULL | |
+---------+----------+------+-----+---------+-------+
6 rows in set
删除字段
ALTER TABLE 数据表名 DROP 字段名;
mysql> ALTER TABLE name_tb DROP address;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
查看是否删除成功
mysql> DESC name_tb;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| xuehao | char(1) | YES | | NULL | |
| newid | int(10) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
| aihao | char(1) | YES | | NULL | |
| grade | float | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
5 rows in set
修改数据表字段的位置（把字段移到第一个）
ALTER TABLE 数据表名称 MODIFY 字段名 数据类型 FIRST;
mysql> ALTER TABLE name_tb MODIFY name CHAR FIRST;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
查看是否移动成功
mysql> DESC name_tb;
+--------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| name | char(1) | YES | | NULL | |
| xuehao | char(1) | YES | | NULL | |
| newid | int(10) | YES | | NULL | |
| aihao | char(1) | YES | | NULL | |
| grade | float | YES | | NULL | |
+--------+---------+------+-----+---------+-------+
5 rows in set
修改数据表字段的位置（字段1插入字段2后面）
ALTER TABLE 数据表名称 MODIFY 字段1 数据类型 AFTER 字段2;
mysql> ALTER TABLE name_tb MODIFY name CHAR AFTER aihao;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
查看是否移动成功
mysql> DESC name_tb;
+--------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| xuehao | char(1) | YES | | NULL | |
| newid | int(10) | YES | | NULL | |
| aihao | char(1) | YES | | NULL | |
| name | char(1) | YES | | NULL | |
| grade | float | YES | | NULL | |
+--------+---------+------+-----+---------+-------+
5 rows in set
删除数据表
DROP TABLE 数据表名称;
mysql> DROP TABLE name_tb;
Query OK, 0 rows affected
查看是否删除成功
mysql> DESC name_tb;
1146 - Table 'shang.name_tb' doesn't exist