数据库建表和修改表

Halfup
333 阅读6分钟

highlight: a11y-dark

本章主要讲解数据库建表以及修改表和约束

查看表 show table

图片.png

当前是空的

创建表 create table 表名()

创建表格式

创建一个表时至少需要完成下列事情：

  1. 给表起个名。
  2. 给表定义一些列，并且给这些列都起个名。
  3. 每一个列都需要定义一种数据类型。
  4. 如果有需要的话，可以给这些列定义一些列的属性，比如不许存储NULL，设置默认值等等，具体列可以设置哪些属性我们稍后详细唠叨。

所以语法基本是这样的:

create table 表名 (
 列名1    数据类型    [列的属性],
 列名2    数据类型    [列的属性],
 ....
 )

创建表的列的属性和约束等

表名注释 comment

这里正好跟创建表一起创建个简单的表：

CREATE TABLE first_table (
    first_column INT,
    second_column VARCHAR(100)
) COMMENT '第一个表';

可以事后添加 注释：

 alter table first_table comment '第一个表';

图片.png

后面 查看注释时候 :

可以使用这几个命令：

 show create table first_table;

图片.png 或者

show full columns from first_table;

图片.png

IF NOT EXISTS 防止重复定义

和重复创建数据库一样，如果创建一个已经存在的表的话是会报错的，我们来试试重复创建一下first_table表：

图片.png

加入if net exists 防止重复定义

 create table if not exists first_table(
   first_column INT,
    second_column VARCHAR(100)
) COMMENT '第一个表';

图片.png

查看表内部结构 等

有时候我们可能忘记了自己定义的表的结构，可以使用下边这些语句来查看，它们起到的效果都是一样的：

DESCRIBE 表名;
DESC 表名;
EXPLAIN 表名;
SHOW COLUMNS FROM 表名;
SHOW FIELDS FROM 表名;

图片.png

或者你喜欢这种形式可以使用：

show create table first_table\G;     //G是以垂直的方式展示每一列数据

图片.png

可以看到，使用SHOW CREATE TABLE这个语句展示出来的表结构就是我们平时创建表的语句，而且为各个列自动的添加了一些我们还没有唠叨过的属性（现在不要纠结那些属性的含义，我们稍后再讲）。

没有选择数据库时候需要 .数据库操作

有时候我们并没有使用USE语句来选择当前数据库，或者在一条语句中遇到的表分散在不同的数据库中，如果我们想在语句中使用这些表，那么就必须显式的指定这些表所属的数据库了。

  • 展示数据库test 中的表：
show tables from test;

图片.png

  • 使用其他数据库中表 等 ， 数据库名.表名
show create table test.first_table\G

图片.png

删除表 drop

DROP TABLE 表1, 表2, ..., 表n;

也就是说我们可以同时删除多个表。我们现在把first_table表给删掉看看：

DROP TABLE first_table;

IF EXISTS 防止重复删除

DROP TABLE IF EXISTS 表名;

举例：

mysql> DROP TABLE IF EXISTS first_table;

这样就不报错了～

修改表 alter

修改表名

举例把first_table改名为 table1

  • 方式一: alter table first_table rename to table1;

图片.png

  • 方式二: rename table first_table to table1;

这种改名方式的牛逼之处就是它可以在一条语句中修改多个表的名称。

RENAME TABLE 旧表名1 TO 新表名1, 旧表名2 TO 新表名2, ... 旧表名n TO 新表名n;
  • 如果在修改表名的时候指定了数据库名，还可以将该表转移到对应的数据库下

比方说我们先再创建一个数据库test1：

 CREATE DATABASE test1;

然后把table1表转移到这个数据库下：

ALTER TABLE table1 RENAME TO test1.first_table1;

这样变相改名了。

图片.png

增加列

我们可以使用下边的语句来增加表中的列：

ALTER TABLE 表名 ADD COLUMN 列名 数据类型 [列的属性];

举例：first_table1表中添加一列third_column的列

ALTER TABLE first_table1 ADD COLUMN third_column  char(4) not null;

图片.png

增加列到特定位置

默认的情况下都是添加到最后一列后面，如果我们要指定位置。

添加到第一列:

ALTER TABLE 表名 ADD COLUMN 列名 列的类型 [列的属性] FIRST;

让我们添加fourth_column到第一列

alter table first_table1 add column fourth_column char(4) first;

图片.png

  • 添加到指定列的后面 after 前面类似 before
ALTER TABLE 表名 ADD COLUMN 列名 列的类型 [列的属性] AFTER 指定列名;

再插入一个fifth_column到first_column后边瞅瞅：

 ALTER TABLE first_table1 ADD COLUMN fifth_column CHAR(4) AFTER first_column;

图片.png

删除列

我们可以使用下边的语句来删除表中的列：

ALTER TABLE 表名 DROP COLUMN 列名;

删除 fourth_column列试试：

ALTER TABLE first_table1 DROP COLUMN fourth_column;

图片.png

修改列信息

修改列信息有以下这二种方式:

方法一 ： modify

ALTER TABLE 表名 MODIFY 列名 新数据类型 [新属性];

我们来修改一下first_table1表的second_column列，把它的数据类型修改为VARCHAR(2):

alter table first_table1 modify second_column VARCHAR(2);

图片.png

可以看到second_column的数据类型就已经被修改为VARCHAR(2)了。

不过在修改列信息的时候需要注意：修改后的数据类型和属性一定要兼容表中现有的数据！比方说原先first_table表的类型是VARCHAR(100)，该类型最多能存储100个字符，如果表中的某条记录的second_column列值为'aaa'，也就是占用了3个字符，而此时我们尝试使用上边的语句将second_column列的数据类型修改为VARCHAR(2)，那么此时就会报错，因为VARCHAR(2)并不能存储3个字符。

方法二: change

ALTER TABLE 表名 CHANGE 旧列名 新列名 新数据类型 [新属性];

可以看到这种修改方式需要我们填两个列名，也就是说在修改数据类型和属性的同时也可以修改列名！比如我们修改second_column的列名为second_column1：

ALTER TABLE first_table1 CHANGE second_column second_column1 VARCHAR(2);

图片.png

可以看到结果中second_column的列名已经被修改为了second_column1，不过我们并没有改动该列的数据类型和属性，所以直接把旧的数据类型和属性抄过来就好了。

修改列排列位置

如果我们觉得当前列的顺序有问题的话，可以使用下边这几条语句进行修改：

modify first/

ALTER TABLE 表名 MODIFY 列名 列的类型 列的属性 FIRST;

modify after/before

ALTER TABLE 表名 MODIFY 列名 列的类型 列的属性 AFTER 指定列名;

一条语句中包含多个修改操作 ,

如果对同一个表有多个修改操作的话，我们可以把它们放到一条语句中执行，就像这样：

ALTER TABLE 表名 操作1, 操作2, ..., 操作n;

上边我们在演示删除列操作的时候用三条语句连着删了third_column、fourth_column和fifth_column这三个列，其实这三条语句可以合并为一条：

ALTER TABLE first_table DROP COLUMN third_column, 
DROP COLUMN fourth_column, DROP COLUMN fifth_column;
avatar
文章
阅读
粉丝