四、数据表的基本操作

在数据库中，数据表是数据库中最重要、最基本的操作对象，是数据存储的基本单位。数据表被定义为列的集合，数据在表中按照行和列的各式来存储的。每一行代表一条唯一的记录，每一列代表记录中的一个域。

一，创建数据表

创建数据表的过程是规定数据列的属性的过程，同时也是实施数据完整性（包括实体完整性、引用完整性和域完整性等）约束的过程。
创建数据表之前，先使用"use <database_name>"指定操作是在哪个数据库进行，如果没有选择，会抛出“No database selected”的错误。

1.1 创建数据表的语句为 create table,语法规则如下：

create table <表名>

(

字段名1，数据类型[列级别约束条件][默认值],

字段名2，数据类型[列级别约束条件][默认值],

.......

[表级别约束条件]

）;

可以看出使用create table创建表时，必须指定要创建表的名称（不区分大小写），数据表中每一列（字段）的名称和数据类型（多个列之间用逗号隔开）。

使用 show tables可以查看当前数据库下面的所有表。

1.2 使用主键约束

  主键，又称主码，是表中一列或多列的组合。主键约束（Primary Key Constraint）要求主键列的数据唯一，并且不允许为空。主键能够唯一地标识表中的一条数据，可以接和外键来定义不同数据表之间的关系，并且可以加快数据库的查询速度。

主键分为两种类型：

---

单字段主键：

SQL语句格式：

在定义列的同时指定主键：字段名 数据类型  primary key [默认值]    如：  id  int(11） primary key

在定义完所有列之后指定主键： [constraint<约束名>] primary key [字段名]  如：  primary key (id)

CREATE TABLE tb_emp2
(
id INT(11)PRIMARY KEY,
NAME VARCHAR(25),
deptId INT(11),
salary FLOAT
);

CREATE TABLE tb_emp3
(
id INT (11),
`NAME` VARCHAR(25),
deptId INT(11),
salary FLOAT,
PRIMARY KEY(id)
)

 

---

多字段联合主键:

语法规则如下：primary key[字段1,字段2,...,字段n]

---

1.3 使用外键约束

外键用来在两个表的数据之间建立连接，它可以是一列或多列。一个表可以有一个或多个外键。外键对应的是参照完整性，一个表的外键可以是空值，若不为空值，则每一个外键值必须等于另一个表中主键的某个值。

外键：首先它是表中的一个字段，它可以不是本表的主键，但对应另外一个表的主键。外键主要作用是保证数据引用的完整性，定义外键后，不允许删除在另一个表中具有关联关系的行。外键的作用是保证数据的一致性、完整性。

主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的那个表即是主表。

从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的那个表即是从表。

创建外键的语法规则：

[constaraint <外键名>] foreign key (字段名1[,字段名2,……])

references<主表名> 主键列1[,主键列2,……]

“外键名”为定义的外键约束的名称，一个表中不能有相同名称的外键名；“字段名”表示子表需要添加外键约束的字段列；“主表名”即被子表外键所依赖的表的名称；“主键列”表示主表中定义的主键列，或者列组合。

CREATE TABLE tb_dept1
(
 id INT(11) PRIMARY KEY,
 NAME VARCHAR(22) NOT NULL,
 location VARCHAR(50)
 );
 
 CREATE TABLE tb_emp5
 (
  id INT(11) PRIMARY KEY,
  NAME VARCHAR(25),
  deptId INT(11),
  salary FLOAT,
  CONSTRAINT fk_emp_dept1 FOREIGN KEY(deptId)REFERENCES tb_dept1(id)
  );

以上语句执行成功之后，在表tb_emp5上添加了名称为fk_emp_dept1的外键约束，外键名称为deptId，其依赖于表tb_dept1的主键id.

注意：   
子表的外键必须关联父表的主键，且关联字段的数据类型必须匹配(长度无所谓)，如果类型不一样，则创建子表时，就会出现错误"ERROR 1005（HY000）：Can't create table 'database.tablename'(error:150)".

1.4 使用非空约束

非空约束（Not Null Constraint）指字段的值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统会报错。

语法格式： 字段名 数据类型 not null

1.5 使用唯一性约束

  唯一性约束（Unique Constraint）要求该列唯一，允许为空，但是只能出现一个空值。唯一约束可以确保一列或几列不出现重复值。

非空约束的语法规则如下：

---

在定义完列之后直接指定唯一约束：  字段名 数据类型 unique

---

在定义完所有列之后指定唯一约束： [constraint <约束名>] unique（<字段名>）

---

create table tb_dept2
(
id int(11) primary key,
name varchar(22) unique,
location varchar(50)
);

create table tb_dept3
(
   id int(11) primary key,
   name varchar(22),
   location varchar(50),
   constraint sth unique(name)
);

unique和primary key的区别：一个表中可以有多个字段声明为unique，但是只能有一个primary key声明；声明为primary key的列不允许为空值，但是声明为unique的字段允许空值（null）的存在。

1.6 使用默认约束

 默认约束（default constraint）指定某列的默认值，如男性同学较多，性别就可以默认为“男”。如果插入一条新记录时没有为这个字段赋值，那么系统会自动为这个字段赋值为“男”。

默认约束的语法规则： 字段名 数据类型 default 默认值

1.7 设置表的属性值自动增加

  默认的，在MySQL中auto_increment的初始值为1，每新增一条记录，字段值自动加1.一个表只能有一个字段使用auto_increment约束，且该字段必须为字段的一部分。auto_increment约束的字段可以是任何整数类型（tinyint,smallin,int,bigint等），设置唯一性约束的语法：字段名 数据类型  auto_increment

 

二. 查看数据表结构

查看表结构可以使用describe和show create table语句

1.describe/desc语句可以查看表的字段信息，语法：describe 表名 or desc  表名

2. show create table语句可以显示创建表时的create table详细语句，还可以查看存储引擎和字符编码，语法： show create table <表名\G>;（如果不加"\G"参数，显示的结果可能非常混乱）

三. 修改数据表

 修改数据表指的是修改数据库中已经存在的数据表的结构

1. 修改表名。修改表名并不修改表的结构
   alter table <旧表名> rename[to] <新表名>;
   【其中to为可选参数，使用与否均不影响结果】
2. 修改字段的数据类型
   alter table <表名> modify <字段名> <数据类型>
   【其中“表名”指要修改数据类型的zidua所在表的名称，“字段名”指需要修改的字段，“数据类型”指修改后字段的新数据类型】
3. 修改字段名
   alter table <表名> change <旧字段名> <新字段名><新数据类型>；
   【其中，“旧字段名”指修改前的字段名，“新字段名”指修改后的字段名，“新数据类型”指修改后的数据类型，如果不需要修改字段的数据类型，可以将新数据类型设置成与原来一样即可，但数据类型不能为空】
     change也可以只修改数据类型，只需要将“新字段名”和“旧字段名”设置为相同的名称，只改变“数据类型”，就可以实现和modify同样的效果。{由于不同类型的数据在机器中存储的方式及长度并不相同，修改数据类型可能会影响到数据表中已有的数据记录。因此，当数据库表中已经有数据时，不要轻易修改数据类型}
4. 添加字段
   alter table <表名> add <新字段名> <数据类型> [约束条件][first|after 已存在字段名]
   【新字段名为需要添加的字段的名称，first为可选参数，其作用是将新添加的字段设置为表的第一个字段，after为可选参数，其作用是将新添加的字段添加到指定的‘已存在字段名’的后面，如果没有没有这两个参数，则默认将新添加的字段设置为数据表的最后列】
5. 删除字段
   alter table <表名> drop <字段名>;
   【字段名为需要从表中删除的字段的名称】
6. 修改字段的排列位置
   alter table<表名> modify <字段名1><数据类型> first| after <字段2>；
   【字段1之要修改位置的字段，数据类型指字段1的数据类型，first为可选参数，指将字段1修改为表的第一个字段，after字段2指将字段1插入到字段2后面】
7. 更改表的存储引擎
    alter table <表名> engine=<更改后的存储引擎名>
8. 删除表的外键约束
   alter table <表名> drop foreign key <外键约束名>
   外键一旦删除，就会解除主表和从表间的关联关系
9. 为表新增外键约束
   alter table <表名> add constraint <约束名> foreign key (字段名1[,字段名2,……])

   references<主表名> 主键列1[,主键列2,……]

四. 删除数据表

   1.删除没有被关联的表
      使用drop table可以一次删除一个或多个没有被其他表关联的数据表。语法：
      drop table [if exists]表1，表2，...，表n

  2.删除被其他表关联的主表
     数据表之间存在外键关联的情况下，如果直接删除主表，结果会显示失败。原因是直接删除，将破坏表的参照性。
     这个时候只需将关联的表的外键约束条件取消，然后就可以删除父表。