数据库笔记4_sql中丈夫表和妻子表-CSDN博客

本文链接：https://blog.csdn.net/q369323345/article/details/19932269

最后一个重要的约束外键约束，用于表示多个表之间的关联关系，一般情况下，使用外键的场合是至少两个表以上，但在特殊情况下，在一个表中也可以有外键。

Sql中的表关系有三种：

1、一对一关系，如丈夫表和妻子表，使用非常少；记为1：1

2、一对多关系，如父亲表和儿子表，商品表和销售表，使用比较多；记为1：m

3、多对多关系，如师生关系,记为m：n，使用最多。

对于多对多关系的处理是将其转换为多个一对多关系来处理的。

商品表和销售表：

模拟一个场景，有一个小超市，日常工作为进货和卖货两件事。在此情况下，第二天要卖的货，肯定是前一天进来的。

在有多对多关系的多个表中，根据逻辑关系的先后顺序，分为主表和从表。

先来创建商品表Item：

create table item(
itemid char(3) primary key,
itemname nvarchar(20) not null unique,
quantity int not null check(quantity>0),
price money not null check(price>0),
)

添加记录：

insert into item values('001','方便面',100,2.5)
insert into item values('002','香肠',90,1)
insert into item values('003','榨菜',80,2)

要想两个表中的数据具有一致性，需要使用外键约束，将两个表关联起来。

外键约束，它要建在哪个表？主表还是从表？

建在从表中！

从表中的外键约束列正好对应主表中的主键列。

外键所在列与主键所在列名可以不同，但一般情况下我们设计为一样。

create table sale(
saleid int identity primary key,
itemname nvarchar(20) not null foreign key references item(itemname) on delete cascade on update cascade,
quantity int not null check(quantity>0),
sale_time datetime default getdate(),
)

向表中添加记录：

insert into sale(itemname,quantity) values('方便面',2)
insert into sale(itemname,quantity) values('方便面',1)
insert into sale(itemname,quantity) values('香肠',2)
insert into sale(itemname,quantity) values('榨菜',1)
insert into sale(itemname,quantity) values('香肠',3)

测试主表与从表的关系之级联更新和级联删除：

删除主表中一列 itemname='方便面'

delete from item where itemname='方便面'

此时sales中的信息：

可知从表中与方便面有关的行记录也被删除了，这就是“级联删除”的作用。

销售表只能销售商品中存在的商品，一旦主表中的某种商品不存在，销售表也不会存在。

在任何情况下，从表都是依赖于主表的。

表的多对多关系，是将一个多对多关系，转换为多个一对多关系进行处理。处理方式为添加中间表，用来表示“多对多”。以师生关系为例来说明这个问题。

学生表，教师表，师生关系表；

教师表teacher:

create table teacher(
tid char(3) primary key,
tname varchar(6) not null,
)

学生表student:

create table student(
sid char(3) primary key,
sname varchar(6) not null,
)

中间表teacher_student：（处理多对多关系的核心表）

create table teacher_student(
tsid int identity primary key,
teacher_id char(3) not null references teacher(tid) on delete cascade on update cascade,
student_id char(3) not null references student(sid) on delete cascade on update cascade,

)

以上三表中，何为主表和从表？

教师表和学生表都是主表，师生关系表teacher_student是从表。即两个主表，一个从表。

向表teacher中添加记录：

insert into teacher values('t01','张老师')
insert into teacher values('t02','李老师')
insert into teacher values('t03','王老师')

向学生表中添加记录：

insert into student values('s01','路飞')
insert into student values('s02','索隆')
insert into student values('s03','娜美')
insert into student values('s04','乌索普')
insert into student values('s05','香吉士')
insert into student values('s06','乔巴')
insert into student values('s07','罗宾')

向老师学生表中添加记录

张老师与学生之间的“一对多”关系：

insert into teacher_student values('t01','s01')
insert into teacher_student values('t01','s02')
insert into teacher_student values('t01','s03')
insert into teacher_student values('t01','s04')
insert into teacher_student values('t01','s05')
insert into teacher_student values('t01','s06')
insert into teacher_student values('t01','s07')

李老师与学生之间的“一对多”关系：

insert into teacher_student values('t02','s01')
insert into teacher_student values('t02','s02')
insert into teacher_student values('t02','s03')
insert into teacher_student values('t02','s04')
insert into teacher_student values('t02','s05')

王老师与学生之间的“一对多”关系：

insert into teacher_student values('t03','s04')
insert into teacher_student values('t03','s05')
insert into teacher_student values('t03','s06')
insert into teacher_student values('t03','s07')

查询：李老师所教的学生信息，因为老师信息在教师表中，学生信息在学生表中，师生之间的关系情况在教师学生表中。要完成以上操作，需要使用多表联合查询。

查询时所需要的条件就是关系！

查询时给表或列重新命名（别名）的场合：

1、如果表名比较长，要起别名，因为条件语句构造时，可以少写一些代码；

2、在特定情况下，一定要起别名（比如在一个表时作复杂查询时，一个表作为两个表使用，只能用别名区分）

select sid,sname from student,teacher,teacher_student
where teacher.tname='李老师'and teacher.tid=teacher_student.teacher_id and student.sid=teacher_student.student_id
go