有关数据库表的操作

注意：以下所有命令在cmd环境下都是以“；”结束当前命令

查看某数据库下所有的数据库表：use studentcourse

                                                      show tables;

mysql> use studentcourse
Database changed
mysql> show tables;
+-------------------------+
| Tables_in_studentcourse |
+-------------------------+
| c_student               |
| course                  |
| sc                      |
| student                 |
| test_log                |
+-------------------------+
5 rows in set (0.00 sec)

创建数据库表： 

create table course(
    -> Sid int,
    -> Sname varchar(10),
    -> class varchar(20));

mysql> use student;
Database changed
mysql> create table course(
    -> Sid int,
    -> Sname varchar(10),
    -> class varchar(20));
Query OK, 0 rows affected (0.53 sec)

mysql>

查看表结构：desc 表名

mysql> desc course;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Sid   | int(11)     | YES  |     | NULL    |       |
| Sname | varchar(10) | YES  |     | NULL    |       |
| class | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.16 sec)

添加字段：alter table 表名 add 字段名 字段类型;

mysql> alter table course add courseid int;
Query OK, 0 rows affected (0.84 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc course;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| Sid      | int(11)     | YES  |     | NULL    |       |
| Sname    | varchar(10) | YES  |     | NULL    |       |
| class    | varchar(20) | YES  |     | NULL    |       |
| courseid | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.10 sec)

删除字段：alter table 表名 drop 字段名

mysql> alter table course drop courseid;
Query OK, 0 rows affected (0.63 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc course;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Sid   | int(11)     | YES  |     | NULL    |       |
| Sname | varchar(10) | YES  |     | NULL    |       |
| class | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.03 sec)

修改表的字段类型：alter table 表名 modify 字段名 字段类型;

mysql> alter table course modify class int;
Query OK, 0 rows affected (0.92 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc course;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Sid   | int(11)     | YES  |     | NULL    |       |
| Sname | varchar(10) | YES  |     | NULL    |       |
| class | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.04 sec)

修改字段名： alter table 表名 change 旧字段名 新字段名 新字段类型;

mysql> alter table course change class Scourse varchar(10);
Query OK, 0 rows affected (0.84 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc course;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| Sid     | int(11)     | YES  |     | NULL    |       |
| Sname   | varchar(10) | YES  |     | NULL    |       |
| Scourse | varchar(10) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.04 sec)

修改表名：alter table 旧名 rename to 新名；

mysql> alter table course rename to class;
Query OK, 0 rows affected (0.22 sec)

mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| class             |
+-------------------+
1 row in set (0.00 sec)

删除数据库表：drop table 数据库表名；

mysql> drop table class;
Query OK, 0 rows affected (0.19 sec)

mysql> show tables;
Empty set (0.00 sec)