MySQL关于 表 的常用SQL语句

1:查看数据库下所有表

               SHOW TABLES;

2:查看表的创建语句

              SHOW CREATE TABLE table_name;

3:查看表结构

            desc table_name;

4:查看表的状态

           SHOW TABLE STATUS;

5：类型

               NULL  空值

               AUTO_INCREMENT 自增长

               PRIMARY KEY  主键约束

               DEFAULT    默认值

6：创建表的语句：

CREATE  TABLE  teacher (id int NOT NULL  AUTO_INCREMENT,

                                                  name  varchar(50)  NOT NULL,   

                                                  email varchar(60)  NOT NULL,   

                                                  address  varchar(200)  NULL  default"北京",  

                                                 PRIMARY  KEY(id));

CREATE  TABLE student (stu_id int  NOT NULL  PRIMARY KEY  AUTO_INCREMENT,

                                                 name  varchar(50)  NOT NULL,   

                                                 sex  varchar(2)  NOT NULL,   

                                                 age  int(2)  NOT NULL,);

7:往表里添加字段ADD

                     ALTER TABLE student ADD phone int(11) NOT NULL;

8:删除表字段DROP

                     ALTER TABLE student DROP COLUMN age;

9:修改表子段

                     ALTER TABLE student MODIFY phone varchar(11) NULL;

                     AFTER TABLE STUDENT CHANGE phone  varchar(11)  NULL;

10:删除表

                     DROP TABLE student;

11:修改表名称

                    rename  table  原表名  to  新表名;