数据库命令表操作--创建表,显示表,查看表结构,复制表,删除表,修改表,添加表字段

[]为可省略项

创建表

语法：

create table [if not exists] `表名`(
    `字段名` 数据类型 [null|not null] [default] [auto_increment] [primary key] [comment],
    `字段名 数据类型 …
)[engine=存储引擎] [charset=字符编码]

null|not null     是否为空
default:          默认值
auto_increment    自动增长,默认从1开始，每次递增1
primary key       主键，主键的值不能重复，不能为空,每个表必须只能有一个主键
comment:          备注
engine            引擎决定了数据的存储和查找   myisam、innodb

注意：表名和字段名如果用了mysql关键字，要用反引号引起来;创建数据库也是一样。

案例:

//创建简单表
mysql> create table stu1(
    -> id int auto_increment primary key,
    -> name varchar(20) not null
    -> )engine=innodb charset=gbk;
Query OK, 0 rows affected (0.11 sec)


//创建复杂表
create table stu2(
    -> id int auto_increment primary key comment '主键',
    -> name varchar(20) not null comment '姓名',
    -> `add` varchar(50) not null default '地址不详' comment '地址',
    -> score int comment '成绩,可以为空'
    -> )engine=myisam;

注意:

1、如果不指定引擎，默认是innodb引擎,

拓展阅读:mysql的myisam引擎和innodb引擎以及其区别

2、如果不指定字符编码，默认和数据库编码一致

3、varchar(20) 表示长度是20个字符

显示所有表

语法:

show tables;

案例:

mysql> show tables;
Empty set (0.00 sec)

显示创建表的信息

包含语句和引擎

语法:

show create table 表名;	 -- 结果横着排列
show create table 表名\G  -- 将结果竖着排列

显示表的相关信息

包含引擎,编码,创建时间等

show table status like '表名';
show table status like '表名' \G;  --\G只在原生里面有用,表示为将结果竖着排列,方便查看

案例:

查看表结构

语法:

desc[ribe] 表名

案例:

-- 方法一 全写
mysql> describe stu2;
+-------+-------------+------+-----+----------+----------------+
| Field | Type        | Null | Key | Default  | Extra          |
+-------+-------------+------+-----+----------+----------------+
| id    | int(11)     | NO   | PRI | NULL     | auto_increment |
| name  | varchar(20) | NO   |     | NULL     |                |
| add   | varchar(50) | NO   |     | 地址不详        |                |
| score | int(11)     | YES  |     | NULL     |                |
+-------+-------------+------+-----+----------+----------------+
4 rows in set (0.05 sec)

-- 方法二 简写
mysql> desc stu2;
+-------+-------------+------+-----+----------+----------------+
| Field | Type        | Null | Key | Default  | Extra          |
+-------+-------------+------+-----+----------+----------------+
| id    | int(11)     | NO   | PRI | NULL     | auto_increment |
| name  | varchar(20) | NO   |     | NULL     |                |
| add   | varchar(50) | NO   |     | 地址不详        |                |
| score | int(11)     | YES  |     | NULL     |                |
+-------+-------------+------+-----+----------+----------------+
4 rows in set (0.00 sec)

复制表

语法一：

create table 新表 select 字段 from 旧表

特点：不能复制父表的键(主键和unique)和引擎，能够复制父表的结构数据

语法二:

create table 新表 like 旧表

特点：只能复制表结构(包含主键)，不能复制表数据

删除单表/多表

语法:

drop table [if exists] 表1，表2,… 

drop table if exists stu4;   //例1 删除1个表
drop table stu2,stu3;        //例2 删除多个表

案例:

-- 删除表
mysql> drop table stu4;
Query OK, 0 rows affected (0.06 sec)

-- 如果表存在就删除
mysql> drop table if exists stu4;
Query OK, 0 rows affected, 1 warning (0.00 sec)

-- 一次删除多个表
mysql> drop table stu2,stu3;
Query OK, 0 rows affected (0.03 sec)

删除所有表

需要用到嵌套语句,共分2步

1. 先查询所有表名,批量生成删除语法

   你需要再运行一次这个结果集

   就可以删除所有的表而不删除数据库了;也可自定义删除表

语法:

select concat('drop table if exists ', table_name, ';')
from information_schema.tables
where table_schema = '数据库名';

效果:生成了数据库中所有表的删除语句

drop table if exists dbcgpe_sql;
drop table if exists phome_ecms_article;
drop table if exists phome_ecms_article_data_1;
drop table if exists phome_ecms_article_doc;
drop table if exists phome_ecms_article_doc_data;
drop table if exists phome_ecms_download;
......等等

2.再复制批量sql批量删除语句实现批量删除;一秒删除所有表，如果需要保留几个表也可以实现。

拓展阅读:

sql的information_schema数据库和information_schema.tables表

修改表

(1)添加表字段：

语法:

alter table 表名 add [column]字段名 数据类型 [位置]

案例:

创建初始表:

mysql> create table stu(
    -> id int,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.00 sec)

初始表只有id和name两个字段,然后用alter再添加新字段

mysql> alter table stu add `add` varchar(20);	-- 默认添加字段放在最后
Query OK, 0 rows affected (0.05 sec)

mysql> alter table stu add sex char(1) after name;  -- 在name之后添加sex字段
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table stu add age int first;  -- age放在最前面
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| age   | int(11)     | YES  |     | NULL    |       |
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| sex   | char(1)     | YES  |     | NULL    |       |
| add   | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

(2)删除表字段：

语法:

alter table 表 drop [column] 字段名

案例:

mysql> alter table stu drop age;   -- 删除age字段
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

(3)修改字段(改名改数据类型)：

语法:

alter table 表 change [column] 原字段名 新字段名 数据类型 …

案例:

-- 将name字段更改为stuname varchar(10)
mysql> alter table stu change name stuname varchar(10);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stu;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  |     | NULL    |       |
| stuname | varchar(10) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| add     | varchar(20) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

(4)修改字段(不改名只改数据类型)

语法:

alter table 表 modify 字段名 字段属性…

案例:

-- 将sex数据类型更改为varchar(20)
mysql> alter table stu  modify sex varchar(20);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 将add字段更改为varchar(20) 默认值是‘地址不详’
mysql> alter table stu modify `add` varchar(20) default '地址不详';
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

(5)修改表引擎：

语法:

alter table 表名 engine=引擎名

案例:

mysql> alter table stu engine=myisam;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

(6)修改表名

语法:

alter table 表名 rename to 新表名

案例:

-- 将stu表名改成student
mysql> alter table stu rename to student;
Query OK, 0 rows affected (0.00 sec)

(7)将表移动到其他数据库

语法:

alter table 表名 rename to [新数据库名].[新表名];

案例:

-- 将当前数据库中的student表移动到php74数据库中改名为stu
mysql> alter table student rename to php74.stu;
Query OK, 0 rows affected (0.00 sec)