数据表基本操作

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

创建数据表

创建数据表，指的是在已创建好的数据库中建立新表。创建的过程是规定数据列的属性的过程，同时也是实施数据完整性（包括实体完整性、引用完整性和域完整性等）约束过程。

普通创建表

create table 表名(
    字段名 数据类型 [列级别约束条件],
    字段名 字段类型 [列级别约束条件],
    [表级别约束条件]
);

• 将表挂入到指定的数据库下：

1. 在数据表名字前面加上数据库名字，用“.”连接即可：数据库.数据表
2. 在创建数据表之前先进入至某个数据库

• 表级别约束条件：与数据库选项似

1. Engine：存储引擎，mysql提供的具体存储数据的方，默认有一个innodb（5.5以前默认是myisam）
2. charset：字符集，只对当前自己表有效（级别比数据库高）
3. collate：校对集

复制已有表结构
从已经存在的表复制一份（只复制结构 ：如果表中有数据不复制）

create table 新表名 like 表名;

• 表名：使用数据库.表名，就可以在任何数据库下访问其他数据库的数据表

-- 复制mydatabase.teacher表结构到teacher
create table teacher like mydatabase.teacher;

使用主键约束

主键约束（Primary Key Constraint）主键和记录之间的关系是一一对应的。

作用：

• 要求主键列的数据唯一
• 不允许为空
• 能够唯一的标识表中的一条记录
• 可以结合外键来定义不同数据表之间的关系
• 可以加快数据库查询的速度。

单字段主键

1. 在定义列的同时指定主键

字段名 数据类型 primary key [默认值]

-- 将id设置为主键
create table emp(
id int(11) primary key,
name varchar(25)
);

2. 定义完所有列之后指定主键。

constraint 约束名 primary key [字段名]

• constraint 约束名：可选参数

-- 将id设置为主键
create table emp{
    id int(11),
    name varchar(25),
    primary key(id)
}

多字段联合主键
由多个字段联合组成一个主键

primary key 字段1,字段2,..;

-- 将name和id设置为主键
create table emp(
Id int(11),
name varchar(25),
primary key(name,id)
);

使用外键约束

作用： 用来在两个表的数据之间建立关联，保证数据引用的完整性，一致性

关联： 指的是在关系型数据库中，相关表之间的联系，它是通过相容或相同的属性或属性组来表示。子表的外键必须关联父表的主键，且关联字段的数据类型必须匹配。

使用：

• 它可以是一列或者多列。
• 一个表可以有一个或多个外键，但对应另外一个表的主键
• 定义外键后，不允许删除在另一个表中具有关联关系的行
• 可以不是本表的主键
• 外键值必须等于另一个表中主键的某个值
• 外键可以为空值
• 外键必须与主键的数据类型匹配

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

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

constraint 外键名 
forelgn key(外键字段名1,外键字段名2,...) 
references 主表名(主键列1,主键列2,..);

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

-- 创建主表
create table dept(
    id int(11) primary key,
    name varchar(22) not null,
    location varchar(50)
);
-- 创建从表
create table emp(
    id int(11) primary key,
    name varchar(25),
    deptId int(11),
    constraint fk_emp_dept1 foreign key(deptId) references dept(id)
);

使用非空约束

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

字段名 数据类型 not null

create table emp(
    id int(11) primary key,
    name varchar(25) not null,
    deptId int(11)
);

使用唯一性约束

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

unique和primary key的区别：

• unique：一个表中可以有多个字段声明为unique，字段允许为空值null的存在。
• primary key：只能有一个primary key，不允许有空值

1. 在定义完列之后直接唯一约束，

字段名 数据类型 unique

-- 保证name的唯一性
create table dept(
    id int(11) primary key,
    name varchar(22) unique,
    location varchar(50)
);

2. 在定义完所有列之后指定唯一约束

constraint 约束名 unique(<字段名>)

• constraint 约束名：可选参数

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

使用默认约束

默认约束（Default Constraint）指定某列的默认值。

字段名 数据类型 default 默认值

-- 设置sex默认为男
create table emp(
    id int(11) primary key,
    name varchar(25) not null,
    sex varchar(2) default 男
);

设置属性值的自动增加

作用： 每次插入新记录时，系统自动生成字段的主键值

使用：

• 该字段必须为主键的一部分
• 一个表只能有一个字段使用
• 默认的初始值是1，新增一条记录自动加1
• 字段可以是任何整数类型

字段名 数据类型 auto_increment

-- 给id增加自动增加
create table emp(
    id int(11) primary key auto_increment,
    name varchar(25) not null,
    deptId int(11),
    salary float
);

查看数据表

查看数据表列表

显示所有表

show table

匹配显示表

show tables like '匹配模式';

查看表基本结构语句describe

describe/desc语句可以查看表的字段信息

describe 表名;
-- 简写
desc 表名;

mysql> desc emp;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(10)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

• null：表示该列是否可以存储null值
• key：表示该列是否已编制索引。PRI表示该列是主键的一部分;UNI表示该列是unique索引的一部分；MUL表示在列中基本个给定值允许出现多次。
• default：表示该列是否有默认值，如果有的话值是什么。
• extra：表示可获取的与给定列有关的附加信息，例如auto_increment等。

查看表详细结构语句show create table

show create table语句可以用来显示创建表时的create table语句。

show create table <表名/G>;

mysql> show create table emp\G;
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `id` int(10) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `emp_dept` FOREIGN KEY (`id`) REFERENCES `dept` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

修改数据表

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

修改表名

alter table 旧表名 rename to 新表名;

• to：为可选参数，使用与否均不影响结果。

修改字段的数据类型

alter table 表名 modify 字段名 数据类型;

• 表名：指要修改数据类型的字段所有表的名称
• 字段名：指需要修改的字段
• 数据类型：指修改后字段后字段的新数据类型

修改字段名

alter table 表名 change 旧字段名 新字段名 新数据类型;

• 旧字段名：指修改前的字段名
• 新字段名：指修改后的字段名
• 新数据类型：指修改后的数据类型，如果不需要修改字段的数据类型，可以将新数类型设置成与原来一样，即可，但不能为空。

修改字段的排列位置

alter table 表名 modify 字段1 数据类型 first | after 字段2;

• 字段1：指要修改位置的字段，
• 数据类型：指“字段1”的数据类型，
• first：为可选参数，指将“字段1”修改为表的第一个字段，
• after 字段2：指将“字段1”插入到“字段2”后面。

1. 修改字段表的第一个字段

alter table dept modify varchar(12) first;

2. 修改字段列表的指定列之后

alter table dept modify column1 varchar(12) after location;

添加字段

alter table 表名 add 新字段名 数据类型 约束条件 first | after 已存在字段名;

• first：为可选参数，其作用是将新添加的字段设置为表的第一个字段；
• after：为可选参数，其作用是将新添加的字段添加到指定的“已存在的字段名”的后面。

1. 添加无完整性约束条件的字段

alter table dept add managerId int(10);

2. 添加有完整性约束条件的字段

alter table dept add column1 varchar(12) not null;

3. 在表的第一列添加一个字段

alter table dept add column2 int(11) first;

4. 在表的指定列之后添加一个字段

alter table dept add column3 int(11) after name;

删除字段

alter table 表名 drop 字段名;

更改表的属性

alter table 表名 属性名 更改后的存储引擎名;

• 属性名：engine、charset和collate

-- 更表字符集
alter table student charset gbk;

注意： 如果数据库已有很多数据，不要轻易修改表选项（字符集影响不大）

删除表的外键约束

alter table 表名 drop foreign key 外键约束名;

删除数据表

删除没有被关联的表

drop table if exists 表1,表2,表n;

删除被其他表关联的主表

1. 直接删除父表，结果会显示失败，因为直接删除将会在坏表的参照完整性。
2. 先删除与它关联的子表，再删除父表，但子表也会被删除
3. 单独删除父表，只需要将关联的表的外键约束条件取消。

<center color="gray">感谢阅读，修改建议请下方评论或联系作者微信17679391061</center>

primary key约束

• primary key约束唯一标识数据库表中的每条记录。
• 主键必须包含唯一的值。
• 主键列不能包含null值。
• 每个表都应该有一个主键，并且每个表只能有一个主键。

主键

顾名思义：主要的键，primary key，在一张表中，有且只有一个字段，里面的值具有唯一性

创建主键

随表创建

系统提个了两种增加主键的方式

1. 直接在需要当做主键的字段之后，增加primary key 属性来确定主键

-- 在字段之后增加主键属性
create table my_pri1(
username varchar(10) primary key
)charset utf8;

2. 有所有字段之后增加 primary key选项：primary key（字段信息）

create table my_pri2(
username varchar(10),
primary key(username)
)charset utf8;

表后增加

alter table my_pri3(
username varchar(10)
)charset utf8;

-- 增加主键
alter table my_pri3 add primary key(username);

查看主键

1. 查看表结构
2. 查看表的创建语句

删除主键

alter table 表名 drop primary key;

复合主键

案例：有一张学生选修课表，一个学生可以选修多个选修课，一个选修课也可以由多个学生来选，但是一个学生在一个选修课中只有一个成绩

create table my_score(
student_no char(10),
course_no char(10),
score tinyint not null,
primary key(student_no,course_no)
)charset utf8;
Query OK, 0 rows affected (0.34 sec)

mysql> desc my_score;
+------------+------------+------+-----+---------+-------+
| Field      | Type       | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| student_no | char(10)   | NO   | PRI | NULL    |       |
| course_no  | char(10)   | NO   | PRI | NULL    |       |
| score      | tinyint(4) | NO   |     | NULL    |       |
+------------+------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

主键约束

主键一旦增加，那么对对应的字段有数据要求

1. 当前字段对应的数据不能为空；
2. 不前字段对应的数据不能有任何重复

主键分类

主键分类采用的是主键所对应的字段的业务意义分类

• 业务主键：主键所在的字段，具有业务意义（学生ID，课程ID）
• 逻辑主键：自然增长的整形（应用广泛）

外键概念

如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的相关联系，以另一个关系的外键作为主关键字的表被称为主表，具有此外键的表被称为主表的从表。外键又称作为关键字。

外键：foreign key
一张表（A）中有一个字段，保存的值指向另外一张表（B）的主键。
B：主表
A：从表

外键操作

增加外键

mysql中提供了两种方式增加外键

1. 在创建表的时候增加外键（类似主键）

-- 在字段之后增加外键（类似主键）
[constraint `外键名`] foreign key(外键字段) references 主表(主键);

2. 在创建表后增加外键

alter table 从表 add [constraint `外键名`] foreign key(外键字段) references 主表(主键);

修改&删除外键

外键不允许修改，只能删除后增加

alter table my_student drop foreign key 外键名字;

如果想删除对应的索引：alter table 表名 drop index 索引名字

外键基本要求

1. 外键字段需要保证与关联的主表的主键字段类型完全一致
2. 基本属性也要相同
3. 如果是在表后增加外键，对数据还有一定的要求（从表数据与主表的关联关系）
4. 外键只能使用innodb存储引擎：myisam不支持

外键约束

外键约束：通过建立外键关系之后，对主表和从表都会有一定的数约束效率。

约束的基本概念

1. 当一个外键产生时：外键所在的表（从表）会受制于刘青青数据的存在从而导致数据不能进行某些不符合规范的操作（不能插入主给存在的数据）：
2. 如果一张表被其他表外键引入，那么该表的数据操作就不能随意：必须保证从表数据的有效性（不能随便删除一个被从表引入的记录）

外键约束的概念

可以创建外键的，对外键约束进行选择性的操作。

add foreign key(外键字段) refereces 主表(主键) on 约束模式;

约束模式有三种：

1. districr：严格模式，默认的，不允许操作
2. cascade：级联模式，一起操作，主要变化，从表数据跟着变化
3. set null：置空模式，主表变化（删除），从表对应记录设置为空，前提是从表中对应的外键字段允许为空

外键约束主要约束的对象是主表操作：从表就是不能插入主表不存在的数据

通常在进行约束的时候，需要指定操作：update和delete
常用的约束模式：on update cascade,on delete set null,更新级联，删除置空

约束作用

保证数据的完整性：主表与从表的数据要一致。

正是因为外键有非常强大的数据约束作用，而且可能导致数据在后台变化的不可控。导致程序在进行设计开发逻辑的时候，没有办法去很好的把握数据（业务），所以外键较少使用。

表关系：表与表之间（实体）有什么样的关系，每种关系应该如何设计表结构

一对一

一对一，一张表中的一条记录与另外一张表中最多有一条明确的关系：通常，此设计方案保证两张表中使用同样的主键即可

表的使用过程中，常用的信息会经常去查询，而不常用的信息会偶尔才会用到
解决方案：将两张表拆分，常见的放一张表，不常见的放一张表

一对多（多对一）

一对多，通常也叫作多对一关系，通常一对多的关系设计的方案，在“多”关系的表中去维护一个字段，这个字段是“一”关系的主键

多对多

多对多:一张表中的一条记录在另外一张表中可以匹配到多条记录，反过来也一样

多对多的关系如果按照多对一关系维护：就会出现一样字段中有多个其他表的主键，在访问的时候就会带来不便

即然通过两张表自己增加字段解决不了问题，那么就通过第三张表来解决

师生关系：

1. 一个老师教过多个班级的学生
2. 一个学生听过多个老师讲的课

首先得有两个实体：老师表和学生表

从中间设计一张表：维护两张表对应的联系：每一种联系都包含

多对多解决方案：增加一个中间表，让中间表与对应的其他表形成两个多对一的关系：多对一的解决文字是在“多”表中增加“一”表对应的主键字段。

唯一键：unique key,用来保证对应的字段中的数据唯一的。
主键也可以用来保证字段唯一性，但是一张表只有一个主键

1. 唯一键在一张表中可以有多个
2. 唯一键允许字段数据为null，null可以有多个（null不参与比较）

创建唯一键

创建唯一键与创建一主键非常类似

1. 直接在表字段之后增加唯一键标识 符：unique[key]

create table my_uniquel(
id int primary key auto_increment,
username varchar(10) unique
)charset utf8;

2. 在所的字段之后使用unique key[字段列表]；

create table my_uniquel(
id int primary key auto_increment,
username varchar(10),
unique key(username)
)charset utf8;

3. 在创建完表之后也可以增加唯一键

create table my_uniquel(
id int primary key auto_increment,
username varchar(10)
)charset utf8;

alter table my_uniquel add unique key(username);

查看唯一键

唯一键的属性，可以通过查看表结构来实现
唯一键效果：在不为空的情况下，不允许重复

删除唯一键

alter table 表名 drop index 唯一键名字;

修改唯一键：先删除后增加

复合唯一键

唯一键与主键一样可以使用多个字段来共同保证唯一性
一般主键都单一字段（逻辑主键），而其他需要唯一性的内容都是由唯一键来处理。

自动增长：auto increment，当给定某个字段该属性之后，该列的数据在没有提供确定数据的时候，系统会根据之前已经存在的数据进行自动增加后，填充数据

原理

自动增长的原理

1. 在系统中有维护一组数据，用来保存当前使用了自动增长属性的字段，记住当前对应的数据值，再给定一个指定的步长
2. 当用户进行数据插入的时候，如果没有给定值，系统在原始值上再加上步长变成新的数据
3. 自动增长的触发，给定属性的字段没有提供值
4. 自动增长只适用于数值

使用自动增长

基本语法：在字段之后增加一个属性auto increment

create table my_auto(id int primary key auto_increment)charset utf8;

插入数据，触发自动增长，插入空值

insert into my_auto values(null);

修改自动增长

1. 查看自增长：自增长一旦触发使用之后，会自动的在表选项中增加一个选项（一张表最多只能拥有一个自增长）

alter table my_auto auto_increment = 10;

删除自增长

删除自增长：就是在字段发展之后不再保留 auto_increment，当用户修改自增长所在字段时，如果没有看到auto_increment属性，系统会自动清除该自增长

alter table my_auto modify id int;

初始设置

在系统中，有一组变量用来维护自增长的初始值和步长
show variables like 'auto_increment%';

细节问题

1. 一张表只有一个自增长，自增长会上升到选项中
2. 如果数据插入中没有触发自增长（给定了数据），那么自增长不会表现
3. 自增长修改的时候，值可以较大，但是不能比当前已有的自增长字段的值小