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)

转载于:https://www.cnblogs.com/gaowc/p/9950424.html