数据库之数据表基本操作

目录

一、创建数据表

1.创建表的语法形式

2.使用SQL语句设置约束条件

1.设置主键约束

2.设置自增约束

3.设置非空约束

4.设置唯一性约束

5.设置无符号约束

6.设置默认约束

7.设置外键约束

8.设置表的存储引擎

二、查看表结构

1.查看表基本结构

2.查看建表语句

三、修改表

1.修改表名

2.修改字段数据类型

3.修改字段名

4.添加字段

5.删除字段

6.修改字段顺序

7.修改存储引擎

四、删除表

1.删除没有被关联的表

2.删除被其他表关联的主表

---

一、创建数据表

1.创建表的语法形式

创建数据表，实际上是规定列属性和实现数据完整性约束的过程，基本语法形式如下：

CREATE TABLE table_name(

Col_name1 data_type [Constraints],

Col_name2 data_type [Constraints],

......

Col_namen data_type [Constraints],

);

---

上述语句中，CREATE为创建数据表的关键字，table_name表示数据表的名称，表的结构在括号中定义，col_name表示字段名，data_type表示数据类型，Constraints表示约束条件，各个字段之间使用（，）进行分隔，语句的最后以分号（；）结束。

注：一行叫做一个记录，一列叫做一个字段。

数据表命名应遵循以下原则：

长度最好不要超过30个字符；

多个单词之间使用下划线“_”分隔，不允许有空格；

不允许为MySQL关键字；

不允许与同一数据库中的其他数据表同名。

创建数据库table_name：

CREATE DATABASE table_name;

选择数据库table_name：

USE table_name;

查看当前数据库中所有的数据表：

SHOW TABLES;

---

2.使用SQL语句设置约束条件

在实际应用中，一张完整的数据表会包含各种约束条件。设置约束条件后，系统会在数据插入数据库之前根据约束条件对其合法性进行检查，保证数据的准确性和一致性。

1.设置主键约束

主键，也称主码，用于标识表中唯一的一条记录。一张表中只能有一个主键，且主键值不能为空。

主键约束是最常用的一种约束，设置主键约束的关键字为PRIMARY KEY，使用SQL语句可以在定义字段时设置主键约束，也可以在定义好表中所有字段后再设置主键约束。

①定义字段时设置主键约束。

语法形式为：

Col_name data_type PRIMARY KEY

②定义所有字段后设置主键约束。

语法形式为：

PRIMARY KEY(col_name)

2.设置自增约束

设置自增约束的关键字为AUTO_INCREMENT。

语法形式如下：

Col_name data_type AUTO_INCREMENT

设置自增约束应注意以下几点：

一张表中只能设置一个字段为自增约束，并且该字段必须为主键。

默认的初始值为1，每增加一条记录，字段值自动增加1。

字段类型必须为整数类型。

3.设置非空约束

设置非空约束的关键字为NOT NULL，作用是规定字段的值不能为空，用户在向数据表中插入数据时，如果设置非空约束的字段没有指定值，系统就会报错。

语法形式如下：

Col_name data_type NOT NULL

4.设置唯一性约束

当数据表中某个字段的值不允许重复时，可以使用唯一性约束。当用户在设置了唯一性约束的字段中插入的数据与数据表中已存在的数据相同时，系统会报错。

设置唯一性约束的关键字为UNIQUE，同主键约束一样，唯一性约束也有两种设置方法，一种是在定义字段时设置，还有一种定义好表中所有字段后再设置。

①定义字段时设置唯一性约束。

语法形式为：

Col_name data_type UNIQUE

②定义所有字段后设置唯一性约束

语法形式为：

UNIQUE KEY(col_name)

5.设置无符号约束

为字段设置无符号约束的关键字为UNSIGNED，其作用是规定此列所存储的数据不为负数。

语法形式如下：

Col_name data_type UNSIGNED

6.设置默认约束

当向数据表中插入一条记录时，如果没有为某个字段赋值，那么系统就会将其值设置为NULL，如果为字段设置默认约束，则系统会自动将默认值插入到此字段中。

设置默认约束的关键字为DEFAULT。

语法形式如下：

Col_name data_type DEFAYLT value

7.设置外键约束

在关系数据库中，数据表之间是由关系来联系彼此的，一张数据表中的某个字段可能是另外一张数据表的主键，为这个字段设置外键约束，就可以将这两张表关联在一起。设置外键约束的主要作用是保证数据的完整性。

外键可以不是所属数据表的主键，但会对应着另外一张数据表的主键。

语法形式如下：

CONSTRAINT key_name FOREIGN KEY(child_col_name)

REFERENCES parent_table_name(parent_col_name)

---

CONSTRAINT，FOREIGN KEY和REFERENCES为设置外键约束的关键字，key_name表示外键名，child_col_name表示从表中需要设置外键约束的字段名，parent_table_name表示主表名，parent_col_name表示主表中主键的字段名。

设置外键约束时应注意以下几点：

主表和从表必须使用InnoDB存储引擎。

设置外键约束的字段和关联的主键必须具有相同的数据类型。整数类型的大小和符号必须相同；字符串类型的长度可以不相同，但对于非二进制字符串，字符集和排序规则必须相同。

8.设置表的存储引擎

在创建表时，如果不使用默认存储引擎，就需要单独设置存储引擎。

CREATE TABLE table_name(

Col_name1 data_type [Constraints],

Col_name2 data_type [Constraints],

......

Col_namen data_type [Constraints],

)ENGINE=存储引擎;

二、查看表结构

1.查看表基本结构

查看表结构的关键字为DESCRIBE。

语法形式如下：

DESCRIBE table_name;

查询结构中的参数及其意义：

Field：表示字段名称。

Type：表述数据类型。

Null：表示是否可以存储空值。

Key：表示是否创建索引，PRI表示主键索引，UNI表示唯一索引。

Default：表示默认值。

Extra：表示与字段有关的附加信息。

2.查看建表语句

使用SHOW CREATE TABLE语句可以查看表的建表语句。

语法形式如下：

SHOW CREATE TABLE goods \G

三、修改表

1.修改表名

修改数据表名的关键字为RENAME。

语法形式如下：

ALTER TABLE old_table_name RENAME new_table_name;

2.修改字段数据类型

修改字段数据类型的关键字为MODIFY。

语法形式如下：

ALTER TABLE table_name MODIFY col_name new_data_type;

3.修改字段名

修改数据表字段名称的关键字为CHANGE。

语法形式如下：

ALTER TABLE table_name CHANGE old_col_name new_col_name data_type;

4.添加字段

①在表的最后一列添加字段

添加字段的关键字为ADD。

语法形式如下：

ALTER TABLE table_name ADD col_name data_type;

②在表的第一列添加字段。

语法形式如下：

ALTER TABLE table_name ADD col_name data_type FIRST;

③在表的指定列之后添加字段。

语法形式如下：

ALTER TABLE table_name ADD col_name1 data_type AFTER col_name2;

5.删除字段

删除数据表字段的关键字为DROP。

语法形式如下：

ALTER TABLE table_name DROP col_name;

6.修改字段顺序

修改字段顺序的关键字为MODIFY。

语法形式如下：

ALTER TABLE table_name MODIFY col_name data_type FIRST|AFTER col_name2;

---

其中col_name表示要修改的字段，data_type表示字段数据类型，FIRST和AFTER col_name2分别表示修改为表的第一个字段和修改为col_name2字段之后。

7.修改存储引擎

用户可以在创建表时设置存储引擎，也可以在表创建完成之后修改表的存储引擎。

语法形式如下：

ALTER TABLE table_name ENGINE=e_name;

在修改存储引擎之前，查看当前表的存储引擎。

语法形式如下：

SHOW CREATE TABLE table_name \G

四、删除表

1.删除没有被关联的表

使用DROP关键字可以一次性删除一个或多个没有被其他表关联的表。

语法形式如下：

DROP TABLE [IF EXISTS] table_name1,table_name2,......,table_namen;

注：如果系统删除的数据表不存在，系统会提示出错误信息并中断执行，加上“IF EXISTS”参数后，系统会在执行删除命令之前判断表是否存在，如果表不存在，命令仍可执行，但系统会提示警告。

2.删除被其他表关联的主表

如果数据表之间存在外键关联，那么直接删除主表，系统会提示错误信息，这种情况下，可以先删除与它关联的从表，再删除主表。但有时需要保留从表中的数据，此时需要解除主表和从表之间的关联，即删除从表中的外键约束。

删除外键的语法形式如下：

ALTER TABLE table_name DROP FOREIGN KEY key_name;