第一部分(约束和表设计)

1、DQL查询语句-limit语句(掌握)

LIMIT是限制的意思，所以LIMIT的作用就是限制查询记录的条数

LIMIT语句格式:

SELECT 字段 FROM 表名 LIMIT 索引, 显示条数;
索引：从0开始，一直变化
显示条数：每页显示的行数，固定不变的-- 1表示分页查询的索引，对应数据表是第二行数据(0 1)，4表示每页显示4条数据
select * from 表名 limit 1,4。
1表示索引，注意这里的索引从0开始。对应表中第一行数据
4表示查询记录数。
上述就表示从第2条记录开始查询，一共查询4条，即到第5条。

具体步骤：

准备数据：

CREATE TABLE student3 (id int,name varchar(20),age int,sex varchar(5),address varchar(100),math int,english int
);INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES (1,'马云',55,'男','杭州',66,78),(2,'马化腾',45,'女','深圳',98,87),(3,'马景涛',55,'男','香港',56,77),(4,'柳岩',20,'女','湖南',76,65),(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港',99,99),(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65);

• 查询学生表中数据，跳过前面1条，显示4条

我们可以认为跳过前面1条，取4条数据
SELECT * FROM student3 LIMIT 1,4;

LIMIT的使用场景：分页

比如我们登录京东，淘宝，返回的商品信息可能有几万条，不是一次全部显示出来。是一页显示固定的条数。假设我们一每页显示5条记录的方式来分页。

假设我们一每页显示5条记录的方式来分页，SQL语句如下：

-- 每页显示5条
-- 第一页： LIMIT 0,5;	跳过0条，显示5条
-- 第二页： LIMIT 5,5;  跳过5条，显示5条
-- 第三页： LIMIT 10,5; 跳过10条，显示5条
SELECT * FROM student3 LIMIT 0,5;
SELECT * FROM student3 LIMIT 5,5;
SELECT * FROM student3 LIMIT 10,5;

注意：

• 如果第一个参数是0可以简写：
  SELECT * FROM student3 LIMIT 0,5;
SELECT * FROM student3 LIMIT 5;
• LIMIT 10，5; – 不够5条，有多少显示多少

小结

  SELECT 字段名(5) FROM 表名(1) WHERE 条件(2) GROUP BY 分组列名(3) HAVING 条件(4) ORDER BY 排序列名(6) LIMIT 跳过行数, 返回行数(7);执行顺序：1234567

---

2、数据库约束的概述

数据库约束的作用

​ 对表中的数据进行进一步的限制，保证数据的正确性、有效性和完整性。

约束种类

• PRIMARY KEY: 主键约束
• UNIQUE: 唯一约束
• NOT NULL: 非空约束
• DEFAULT: 默认值 了解
• FOREIGN KEY: 外键约束

---

3、主键约束(掌握)

主键的作用

用来唯一标识一条记录

为什么需要主键约束

有些记录的 name,age,score 字段的值都一样时,那么就没法区分这些数据,造成数据库的记录不唯一,这样就不方便管理数据。

每张表都应该有一个主键，并且每张表只能有一个主键。

哪个字段作为表的主键

​ 通常不用业务字段作为主键，单独给每张表设计一个id的字段，把id作为主键。主键是给数据库和程序使用的，不是给最终的客户使用的。所以主键有没有含义没有关系，只要不重复，非空就行。

创建主键

主键：PRIMARY KEY
主键的特点：

• 主键必须包含唯一的值
• 主键列不能包含NULL值

创建主键方式：

1. 在创建表的时候给字段添加主键

   字段名 字段类型 PRIMARY KEY
   

2. 在已有表中添加主键(了解)

   ALTER TABLE 表名 ADD PRIMARY KEY(字段名);
   

具体操作：

• 创建表学生表st5, 包含字段(id, name, age)将id做为主键

CREATE TABLE st5 (id INT PRIMARY KEY, -- id是主键NAME VARCHAR(20),age INT
);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DlDmoZCr-1665125405023)(imgs/主键03.png)]

• 添加数据

INSERT INTO st5 (id, NAME,age) VALUES (1, '唐伯虎',20);
INSERT INTO st5 (id, NAME,age) VALUES (2, '周文宾',24);
INSERT INTO st5 (id, NAME,age) VALUES (3, '祝枝山',22);
INSERT INTO st5 (id, NAME,age) VALUES (4, '文征明',26);

• 插入重复的主键值

-- 主键是唯一的不能重复：Duplicate entry '1' for key 'PRIMARY'
INSERT INTO st5 (id, NAME,age) VALUES (1, '文征明2',30);

• 插入NULL的主键值

-- 主键是不能为空的：Column 'id' cannot be null
INSERT INTO st5 (id, NAME,age) VALUES (NULL, '文征明3',18);

---

4、主键自增

​ 主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段的值

字段名 字段类型 PRIMARY KEY AUTO_INCREMENT

AUTO_INCREMENT 表示自动增长(字段类型必须是整数类型)

具体操作：

• 创建学生表st6, 包含字段(id, name, age)将id做为主键并自动增长

CREATE TABLE st6 (id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20),age INT
);

• 插入数据

-- 主键默认从1开始自动增长
INSERT INTO st6 (NAME, age) VALUES ('唐僧', 22);
INSERT INTO st6 (NAME, age) VALUES ('孙悟空', 26);
INSERT INTO st6 (NAME, age) VALUES ('猪八戒', 25);
INSERT INTO st6 (NAME, age) VALUES ('沙僧', 20);

DELETE和TRUNCATE的区别

• DELETE 删除表中的数据，但不重置AUTO_INCREMENT的值。

• TRUNCATE 摧毁表，重建表，AUTO_INCREMENT重置为1

---

5、唯一约束

唯一约束的作用

在这张表中这个字段的值不能重复

唯一约束的基本格式

字段名 字段类型 UNIQUE

具体步骤：

• 创建学生表st7, 包含字段(id, name),name这一列设置唯一约束,不能出现同名的学生

CREATE TABLE st7 (id INT,NAME VARCHAR(20) UNIQUE
);

• 添加一些学生

INSERT INTO st7 VALUES (1, '貂蝉');
INSERT INTO st7 VALUES (2, '西施');
INSERT INTO st7 VALUES (3, '王昭君');
INSERT INTO st7 VALUES (4, '杨玉环');-- 插入相同的名字出现name重复: Duplicate entry '貂蝉' for key 'name'
INSERT INTO st7 VALUES (5, '貂蝉');-- 出现多个null的时候会怎样？因为null是没有值，所以不存在重复的问题
INSERT INTO st3 VALUES (5, NULL);
INSERT INTO st3 VALUES (6, NULL);

---

6、非空约束

非空约束的作用

这个字段必须设置值,不能是NULL

非空约束的基本语法格式

字段名 字段类型 NOT NULL

具体操作：

• 创建表学生表st8, 包含字段(id,name,gender)其中name不能为NULL

CREATE TABLE st8 (id INT,NAME VARCHAR(20) NOT NULL,gender CHAR(2)
);

• 添加一些完整的记录

INSERT INTO st8 VALUES (1, '郭富城', '男');
INSERT INTO st8 VALUES (2, '黎明', '男');
INSERT INTO st8 VALUES (3, '张学友', '男');
INSERT INTO st8 VALUES (4, '刘德华', '男');-- 姓名不赋值出现姓名不能为null: Column 'name' cannot be null
INSERT INTO st8 VALUES (5, NULL, '男');

小结

• 主键约束和唯一约束并且非空的区别：
  • id primary key 主键约束 可以实现主键自增，一般数据表只有id是主键约束
  • name unique not null 唯一并且非空 不能实现主键自增 数据表中很多字段都可以是唯一 非空

---

7、默认值(了解)

默认值的作用

往表中添加数据时,如果不指定这个字段的数据,就使用默认值

默认值格式

字段名 字段类型 DEFAULT 默认值

具体步骤：

• 创建一个学生表 st9，包含字段(id,name,address)， 地址默认值是广州

CREATE TABLE st9 (id INT,NAME VARCHAR(20),address VARCHAR(50) DEFAULT '广州'
);

• 添加一条记录,使用默认地址

INSERT INTO st9 (id, NAME) VALUES (1, '刘德华');

• 添加一条记录,不使用默认地址

INSERT INTO st9 VALUES (2, '张学友', '香港'); 

---

8、表关系的概念和外键约束

在真实的开发中，一个项目中的数据，一般都会保存在同一个数据库中，但是不同的数据需要保存在不同的数据表中。这时不能把所有的数据都保存在同一张表中。

那么在设计保存数据的数据表时，我们就要根据具体的数据进行分析，然后把同一类数据保存在同一张表中，不同的数据进行分表处理。

数据之间必然会有一定的联系，我们把不同的数据保存在不同的数据表中之后，同时还要在数据表中维护这些数据之间的关系。这时就会导致表和表之间必然会有一定的联系。这时要求设计表的人员，就需要考虑不同表之间的具体关系。

在数据库中，表总共存在三种关系，这三种关系如下描述：真实的数据表之间的关系：

多对多关系、一对多（多对一）、一对一(极少)(一对一关系就是我们之前学习的Map集合的key-value关系)

多对多(掌握)

例如：程序员和项目的关系、老师和学生，学生和课程，顾客和商品的关系等

分析：

程序员和项目：

一个程序员可以参与多个项目的开发，一个项目可以由多个程序员来开发。这种关系就称为多对多关系。

当我们把数据之间的关系分析清楚之后，一般我们需要通过E-R图来展示(实体关系图)

一个Java对象，可以对应数据库中的一张表，而Java中类的属性，可以对应表中的字段。

而在E-R图中：

一张表，可以称为一个实体，使用矩形表示，每个实体的属性（字段，表的列），使用椭圆表示

表和表之间的关系，使用菱形表示。

实体(程序员)：编号、姓名、薪资。

实体(项目)：编号、名称。

程序员和项目存在关系：一个程序员可以开发多个项目，一个项目可以被多个程序员开发。

说明：如果两张表是多对多的关系，需要创建第三张表，并在第三张表中增加两列，引入其他两张表的主键作为自己的外键。

关系图总结:

创建表的sql语句:

-- 创建程序员表
create table coder(id int primary key auto_increment,name varchar(50),salary double
);
-- 创建项目表
create table project(id int primary key auto_increment,name varchar(50)
);
-- 创建中间关系表
create table coder_project(coder_id int,project_id int
);-- 添加测试数据
insert into coder values(1,'张三',12000);
insert into coder values(2,'李四',15000);
insert into coder values(3,'王五',18000);insert into project values(1,'QQ项目');
insert into project values(2,'微信项目');insert into coder_project values(1,1);
insert into coder_project values(1,2);
insert into coder_project values(2,1);
insert into coder_project values(2,2);
insert into coder_project values(3,2);

---

外键约束(掌握)

创建第三张关系表即中间表，来维护程序员表和项目表之间的关系。

使用中间表的目的是维护两表多对多的关系：

1 中间表插入的数据 必须在多对多的主表中存在。

2 如果主表的记录在中间表维护了关系，就不能随意删除。如果可以删除，中间表就找不到对应的数据了，这样就没有意义了。

上述是中间表存在的意义，可是我们这里所创建的中间表并没有起到上述的作用，而是存在缺点的：

缺点1: 我们是可以向中间表插入不存在的项目编号和程序员编号的。

说明：在程序员和项目表中是不存在编号是30和20的，但是这里依然可以插入不存在的编号。这样做是不可以的，失去了中间表的意义。

缺点2 : 如果中间表存在程序员的编号, 我们是可以删除程序员表对应的记录的。

在中间表中是存在编号为1的程序员的：

可是我们却可以删除程序员表coder中的编号为1的程序员：

编号为1的程序员张三已经被我们删除，但是在中间表coder_project中仍然还存在编号为1的程序员，这样对于中间表没有意义了。

说明：

创建第三张表的语句：

create table coder_project(coder_id int ,--这个外键来自于coder表中的主键project_id int--这个外键来自于project表中的主键);

我们在创建第三张关系表时，表中的每一列，都是在使用其他两张表中的列，这时我们需要对第三张表中的列进行相应的约束。

当前第三张表中的列由于都是引用其他表中的列，我们把第三张表中的这些列称为引用其他表的外键约束。

给某个表中的某一列添加外键约束：

简化语法：

foreign key( 当前表中的列名 ) references 被引用表名(被引用表的列名)
foreign key( coder_id )  references coder(id)

注意：一般在开发中，被引用表的列名都是被引用表中的主键

举例：

constraint [外键约束名称] foreign key(当前表中的列名) references  被引用表名(被引用表的列名)
举例：constraint coder_project_id foreign key(coder_id) references coder(id)

关键字解释：
constraint: 添加约束，可以不写
foreign key(当前表中的列名): 将某个字段作为外键
references 被引用表名(被引用表的列名) : 外键引用主表的主键

给第三张表添加外键约束有两种方式：

第一种方式：给已经存在的表添加外键约束：

-- 来自于程序员表
alter table coder_project add constraint c_id_fk foreign key(coder_id) references coder(id);
-- 来自于项目表
alter table coder_project add constraint p_id_fk foreign key(project_id) references project(id);

第二种方式：创建表时就添加外键约束：

create table coder_project(coder_id int,project_id int,constraint c_id_fk foreign key(coder_id) references coder(id),constraint p_id_fk foreign key(project_id) references project(id)
);

了解完如何给第三张表添加外键约束以后，我们就开始给上述创建好的第三张表添加外键约束。

在添加外键约束之前，由于刚才已经修改了表中的数据，所以我们先清空三张表，然后在添加外键约束。

操作步骤：

1、清空上述三张表：

2、增加外键约束：

3、添加完外键约束以后，就会在可视化工具中的架构设计器上查看表之间的关系

3、向三张表中分别插入数据：

-- 插入数据insert into coder values(null,'张三',12000);
insert into coder values(null,'李四',15000);
insert into coder values(null,'王五',18000);insert into project values(null,'QQ项目');
insert into project values(null,'微信项目');insert into coder_project values(1,1);
insert into coder_project values(1,2);
insert into coder_project values(2,1);
insert into coder_project values(2,2);
insert into coder_project values(3,1);

4、测试外键约束是否起到作用：

A:执行以下语句：

B:执行以下语句：

再次执行上述语句的时候，发现报错了，而数据库表中的数据都没有改变，说明外键约束起到了作用。

小结：

1.为何引入外键约束？

让表的数据有效性，正确性。提高查询效率。

2.添加外键约束语法：

constraint 外键约束名 foreign key(当前表的字段名) references 主表(主键);

3.有了外键约束，那么要求添加数据需要先添加主表，然后添加从表

4.有了外键约束，那么要求删除数据需要先删除从表，然后再删除主表

---

外键的级联(掌握)

在修改和删除主表的主键时，同时更新或删除从表的外键值，称为级联操作
ON UPDATE CASCADE – 级联更新，主键发生更新时，外键也会更新
ON DELETE CASCADE – 级联删除，主键发生删除时，外键也会删除

具体操作：

• 删除三张表coder、coder_project、project表
• 重新创建三张表，添加级联更新和级联删除

-- 创建程序员表
create table coder(id int primary key auto_increment,name varchar(50),salary double
);
-- 创建项目表
create table project(id int primary key auto_increment,name varchar(50)
);
create table coder_project(coder_id int,project_id int,-- 添加外键约束,并且添加级联更新和级联删除constraint c_id_fk foreign key(coder_id) references coder(id) ON UPDATE CASCADE ON DELETE CASCADE,constraint p_id_fk foreign key(project_id) references project(id) ON UPDATE CASCADE ON DELETE CASCADE
);

再次添加数据到三张表：

-- 添加测试数据
insert into coder values(1,'张三',12000);
insert into coder values(2,'李四',15000);
insert into coder values(3,'王五',18000);insert into project values(1,'QQ项目');
insert into project values(2,'微信项目');insert into coder_project values(1,1);
insert into coder_project values(1,2);
insert into coder_project values(2,1);
insert into coder_project values(2,2);
insert into coder_project values(3,2);

需求1：修改主表coder表的id为3变为4.

update coder set id = 4 where id = 3;

需求2：删除主表coder表的id是4的行数据。

delete from coder where id = 4;

小结：

级联更新：ON UPDATE CASCADE 主键修改后,外键也会跟着修改

级联删除：ON DELETE CASCADE 主键删除后,外键对应的数据也会删除

---

一对多(掌握)

一对多的关系表：其中也有2个实体，但是其中A实体中的数据可以对应另外B实体中的多个数据，反过来B实体中的多个数据只能对应A实体中的一个数据。

例如：作者和小说关系,班级和学生，部门和员工，客户和订单

分析：

作者和小说：

一个作者可以写多部小说，但每一部小说，只能对应具体的一个作者。

具体的关系如下图所示：

注意：如果是一对多的关系，那么设计表的时候需要在多的一方增加一列，引入一的一方的主键作为自己的外键。

---

一对一

一对一关系表在实际开发中使用的并不多，其中也是2个实体，其中A实体中的数据只能对应B实体中的一个数据，同时B实体中的数据也只能对应A实体中的一个数据。例如：人和身份证对应关系，老公和老婆的对应关系。

而一对一在建表的时候，可以在任意一方的表中添加另外一方的主键作为外键即可。

建表原理如下图所示：

---

9、表设计案例(练习)

目标

设计学生成绩管理系统数据表(按照给定需求设计即可)

1、每个教师可以教多门课程

2、每个课程由一个老师负责

3、每门课程可以由多个学生选修

4、每个学生可以选修多门课程

5、学生选修课程要有成绩

讲解

具体操作：

1）分析：

​ 当我们拿到一个需求之后，首先应该分析这个需求中到底有多少名词，或者是当前这个需求中可以抽象出具体几个E-R图中的实体对象。

分析需求中存在的实体： 实体使用矩形表示。

学生、课程、老师。

当分析清楚具体的实体之后，接着要分析实体具备哪些属性？属性使用椭圆形表示。

学生：学号、姓名等。

课程：课程编号、课程名称等。

老师：工号、姓名等。

最后就要考虑实体和实体之间的关系问题：

老师和课程之间：一对多关系。一个老师可以教授多门课程，一个课程只能由一个老师负责。

学生和课程之间：多对多关系。每个学生可以选修多门课程，每门课程可以被多个学生来选修。

2）关于设计学生成绩管理系统的数据表的E-R图如下所示：

3）创建表sql语句：

画完E-R图之后，接下来我们就根据E-R图来创建具体的数据库表了。

学生选课管理系统的表创建：

思考：先创建哪张表？

不能先创建课程表，因为课程表需要有教师的工号，也不能创建中间表，因为中间表需要课程表和学生表的id，所以我们可以创建表的顺序如下：

– 1、教师表

– 2、课程表

– 3、学生表

– 4、学生课程表(中间关系表要有成绩属性)

创建表的语句如下：

-- 1、教师表
create table teacher(id int primary key auto_increment,name varchar(50)
);
-- 2、课程表
create table course(id int primary key auto_increment,name varchar(50),teacher_id int,foreign key(teacher_id) references teacher(id)
);
-- 3、学生表
create table student(id int primary key auto_increment,name varchar(50)
);
-- 4、学生课程表(中间关系表要有成绩属性)
create table studentcourse(student_id int,course_id int,score double,foreign key(student_id) references student(id),foreign key(course_id) references course(id)
);

创建表之后的结构：