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;

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

| Field | Type        | Null | Key | Default | Extra |

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

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

| sname | varchar(20) | 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)

posted @ 2018-11-13 09:08  G大调  阅读(159)  评论(0编辑  收藏  举报