对表的基本操作,这里主要有四个,分别是:
- 创建表
- 修改表(更新表)
- 删除表
- 重命名表
下面就这四种操作来说明一下:
1.表的创建
表的创建主要是利用CREATE语句。
例如创建一个包含name,sex,score属性的students表。
mysql> CREATE TABLE students
-> (
-> name varchar(64) not null,
-> sex char(4) not null,
-> score INT(2)
-> );
Query OK, 0 rows affected (0.38 sec)
下面是创建结果:
mysql> DESC students;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(64) | NO | | NULL | |
| sex | char(4) | NO | | NULL | |
| score | int(2) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.05 sec)
这里回顾一下,VARCHAR(M)中,M(即列长)的取值范围为0-65535。
表的创建:CREATE TABLE 表名(列名1 列1属性,列名2 列2属性,…);
2.表的修改(表的更新)
当我们创建好了一个表,意味着该表的各列属性等已经确定,但是如果出现修改列属性或者其他需要修改列的需求时,我们就应该对表进行恰当的修改(更新)
下面是一个示例,如果对于上面的students表,我们需要添加一个address列:
语法:ALTER TABLE 表名 ADD 列名 列属性;
mysql> ALTER TABLE students ADD address VARCHAR(64);
Query OK, 0 rows affected (0.45 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc students;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(64) | NO | | NULL | |
| sex | char(4) | NO | | NULL | |
| score | int(2) | YES | | NULL | |
| address | varchar(64) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
下面将address属性修改为CHAR型:
语法:ALTER TABLE 表名 MODIFY COLUMN 列名 列的新属性;
mysql> ALTER TABLE students MODIFY COLUMN address TEXT;
Query OK, 0 rows affected (0.64 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC students;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(64) | NO | | NULL | |
| sex | char(4) | NO | | NULL | |
| score | int(2) | YES | | NULL | |
| address | text | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
如果是将address列修改为新的名字location:
语法:ALTER TABLE 表名 CHANGE 旧列名 新列名 新列属性;
mysql> ALTER TABLE students CHANGE address location VARCHAR(20);
Query OK, 0 rows affected (0.53 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC students;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name | varchar(64) | NO | | NULL | |
| sex | char(4) | NO | | NULL | |
| score | int(2) | YES | | NULL | |
| location | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
3.重命名表
下面我们将students改为employee:
语法:ALTER TABLE 表名 RENAME 新表名;
mysql> ALTER TABLE students RENAME employee;
Query OK, 0 rows affected (0.16 sec)
mysql> DESC students;
ERROR 1146 (42S02): Table 'learnsql.students' doesn't exist
mysql> DESC employee;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name | varchar(64) | NO | | NULL | |
| sex | char(4) | NO | | NULL | |
| score | int(2) | YES | | NULL | |
| location | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)
从上面我们可以看到,修改表名之后,利用students查询就会出错,利用employee就可以操作成功。
删除列:
语法:ALTER TABLE 表名 DROP 列名;
mysql> ALTER TABLE employee DROP location;
Query OK, 0 rows affected (0.52 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC employee;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(64) | NO | | NULL | |
| sex | char(4) | NO | | NULL | |
| score | int(2) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
总结:
从上面我们可以总结出,对表的结构进行修改操作,主要用到ALTER TABLE语句,而后面根据操作的不同,又可以有:
- ADD:这是添加列的操作。
- CHANGE:修改列名。
- MODIFY:修改列的属性。
- DROP:删除指定的列。
- RENAME:重命名表。
4.表的删除
语法:DROP TABLE 表名;
下面我们将employee表删除:
mysql> DROP TABLE employee;
Query OK, 0 rows affected (0.13 sec)
mysql> SHOW TABLES;
+--------------------+
| Tables_in_learnsql |
+--------------------+
| tmp10 |
+--------------------+
1 row in set (0.00 sec)
显然,employee表已经不存在。