mysql表管理

4.1 查看所有表

查看所有表语句：

show tables;

例：

mysql> show tables;

+-----------------+

| Tables_in_emp |

+-----------------+

| student |

+-----------------+

1 row in set (0.00 sec)

4.2 创建表

创建表语句：

CREATE TABLE table_name

(

field1 datatype,

field2 datatype,

field3 datatype

)

--field：指定列名　datatype：指定列类型

注意（创建表前，要先使用use db语句使用库）

例：

mysql> create table student[A1] (

-> sid[A2] int[A3] ,

-> sname varchar(20)[A4] ,

-> sage int

-> );

Query OK, 0 rows affected (0.01 sec)

4.3 查看表结构

mysql> desc student;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| sid | int(11) | YES | | NULL | |

| sage | int(11) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

3 rows in set (0.01 sec)

4.4 删除表

mysql> drop table student;

Query OK, 0 rows affected (0.01 sec)

4.5 修改表

1）添加字段

mysql> alter table student add column sgender varchar(2);

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

2）

删除字段

mysql> alter table student drop column sgender;

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

3）修改字段类型

mysql> alter table student modify column remark varchar(100);

Query OK, 0 rows affected (0.07 sec)

Records: 0 Duplicates: 0 Warnings: 0

4）修改字段名称

mysql> alter table student change column sgender gender varchar(2);

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

5）修改表名称

mysql> alter table student rename to teacher;

Query OK, 0 rows affected (0.01 sec)