前言

        實(shí)際開(kāi)發(fā)中，一個(gè)項(xiàng)目通常需要很多張表才能完成。例如:一個(gè)商城項(xiàng)目就需要分類(lèi)表(category)、商品表(products)、訂單表(orders)等多張表。且這些表的數(shù)據(jù)之間存在一定的關(guān)系，接下來(lái)我們將在單表的基礎(chǔ)上，一起學(xué)習(xí)多表方面的知識(shí)。

一、多表關(guān)系

MySQL多表之間的關(guān)系可以概括為：一對(duì)一、一對(duì)多/多對(duì)一、多對(duì)多

1.1一對(duì)一關(guān)系

• 一個(gè)學(xué)生只有一張身份證;一張身份證只能對(duì)應(yīng)一學(xué)生
• 在任一表中添加唯一外鍵，指向另一方主鍵，確保一對(duì)一關(guān)系
• 一般一對(duì)一關(guān)系很少見(jiàn)，遇到一對(duì)一關(guān)系的表最好是合并表

1.2一對(duì)多/多對(duì)一

• 一個(gè)部門(mén)有多個(gè)員工，一個(gè)員工只能對(duì)應(yīng)一個(gè)部門(mén)
• 實(shí)現(xiàn)原則:在多的一方建立外鍵，指向一的一方的主鍵

1.3多對(duì)多

• 一個(gè)學(xué)生可以選擇很多門(mén)課程，一個(gè)課程也可以被很多學(xué)生選擇
• 原則:多對(duì)多關(guān)系實(shí)現(xiàn)需要借助第三張中間表。中間表至少包含兩個(gè)字段，將多對(duì)多的關(guān)系，拆成一對(duì)多的關(guān)系，中間表至少要有兩個(gè)外鍵，這兩個(gè)外鍵分別指向原來(lái)的那兩張表的主鍵

二、外鍵約束

2.1簡(jiǎn)介

        MySQL外鍵約束(FOREIGN KEY)是表的一個(gè)特殊字段，經(jīng)常與主鍵約束一起使用。對(duì)于兩個(gè)具有關(guān)聯(lián)關(guān)系的表而言，相關(guān)聯(lián)字段中主鍵所在的表就是主表(父表)外鍵所在的表就是從表(子表)

        外鍵用來(lái)建立主表與從表的關(guān)聯(lián)關(guān)系，為兩個(gè)表的數(shù)據(jù)建立連接，約束兩個(gè)表中數(shù)據(jù)的一致性和完整性。比如，一個(gè)水果攤，只有蘋(píng)果、桃子、李子、西瓜等4種水果，那么，你來(lái)到水果攤要買(mǎi)水果就只能選擇蘋(píng)果、桃子、李子和西瓜，其它的水果都是不能購(gòu)買(mǎi)的。

2.2特點(diǎn)

        定義一個(gè)外鍵時(shí)，需要遵守下列規(guī)則：

• 主表必須已經(jīng)存在于數(shù)據(jù)庫(kù)中，或者是當(dāng)前正在創(chuàng)建的表。
• 必須為主表定義主鍵。
• 主鍵不能包含空值，但允許在外鍵中出現(xiàn)空值。也就是說(shuō)，只要外鍵的每個(gè)非空值出現(xiàn)在指定的主鍵中，這個(gè)外鍵的內(nèi)容就是正確的。
• 在主表的表名后面指定列名或列名的組合。這個(gè)列或列的組合必須是主表的主鍵或候選鍵。
• 外鍵中列的數(shù)目必須和主表的主鍵中列的數(shù)目相同。
• 外鍵中列的數(shù)據(jù)類(lèi)型必須和主表主鍵中對(duì)應(yīng)列的數(shù)據(jù)類(lèi)型相同。

2.3創(chuàng)建外鍵約束

方式1-在創(chuàng)建表時(shí)設(shè)置外鍵約束

在 create table 語(yǔ)句中，通過(guò)[foreign key|關(guān)鍵字來(lái)指定外鍵，具體的語(yǔ)法格式如下：

[ constraint <外鍵名> ] foreign key 字段名 [,字段名2,..] references <主表名> 主鍵列1 [,主鍵列2,..]

主表-部門(mén)表：

create database mydb3;
use mydb3;
//創(chuàng)建部門(mén)表
create table if not exists dept(
    deptno varchar(20) primary key, -- 部門(mén)號(hào)
    name varchar(20) -- 部門(mén)名字
);

從表-員工表：

create table if not exists emp(
    eid varchar(20) primary key, -- 員工編號(hào)
    ename varhcar(20), -- 員工名字
    age int, -- 員工年齡
    dept_id varchar(20), -- 員工所屬部門(mén)
    constraint emp_fk foreign key (dept_id) references dept (deptno) -- 外鍵約束
);

方式2-在修改表時(shí)添加外鍵約束

外鍵約束也可以在修改表時(shí)添加，但是添加外鍵約束的前提是:從表中外鍵列中的數(shù)據(jù)必須與主表中主鍵列中的數(shù)據(jù)一致或者是沒(méi)有數(shù)據(jù)。

alter table <數(shù)據(jù)表名> add constraint <外鍵名> foreign key (<列名>) references
<主表名> (<列名>);

-- 創(chuàng)建部門(mén)表
create table if not exists dept2(
    deptno varchar(20) primary key, -- 部門(mén)號(hào)
    name varchar(20) -- 部門(mén)名字
);
-- 創(chuàng)建員工表
create table if not exists emp2(
    eid varchar(20) primary key, -- 員工編號(hào)
    ename varhcar(20), -- 員工名字
    age int, -- 員工年齡
    dept_id varchar(20), -- 員工所屬部門(mén)
);
-- 創(chuàng)建外鍵約束
alter table emp2 add constraint dept_id_fk foreign key (dept_id) references dept2 (deptno) 

2.4外鍵約束下的數(shù)據(jù)操作

2.4.1數(shù)據(jù)插入

-- 1、添加主表數(shù)據(jù)
-- 注意必須先給主表添加數(shù)據(jù)
insert into dept values('1001','研發(fā)部');
insert into dept values('1002','銷(xiāo)售部');
insert into dept values('1003'，'財(cái)務(wù)部');
insert into dept values('1004','人事部');
-- 2、添加從表數(shù)據(jù)
-- 注意給從表添加數(shù)據(jù)時(shí)，外鍵列的值不能隨便寫(xiě)，必須依賴主表的主鍵列
insert into emp values('1','喬峰',20，'1001');
insert into emp values('2','段譽(yù)',21，'1001');
insert into emp values('3','虛竹',23，'1001');
insert into emp values('4','阿紫'，18,'1002');
insert into emp values('6','李秋水',33，'1003');
insert into emp values('7','鳩摩智',50，'1003');
insert into emp values('8','天山童姥',60，'1005'); -- 不可以

2.4.2數(shù)據(jù)刪除

-- 1、主表的數(shù)據(jù)被從表依賴時(shí)，不能刪除，否則可以刪除
-- 2、從表的數(shù)據(jù)可以隨便刪除
delete from dept where deptno = '1001'; -- 不可以刪除
delete from dept where deptno = '1004'; -- 可以刪除
delete from emp where eid = '7'; -- 可以刪除
dept from emp; -- 可以刪除

2.5刪除外鍵約束

當(dāng)一個(gè)表中不需要外鍵約束時(shí)，就需要從表中將其刪除。外鍵一旦刪除，就會(huì)解除主表和從表間的關(guān)聯(lián)關(guān)系。

alter table<表名> drop foreign key <外鍵約束名>;

alter table emp2 drop foreign key dept_id_fk;

三、多對(duì)多外鍵約束

在多對(duì)多關(guān)系中，A表的一行對(duì)應(yīng)B的多行，B表的一行對(duì)應(yīng)A表的多行，我們要新增加一個(gè)中間表，來(lái)建立多對(duì)多關(guān)系。

3.1創(chuàng)建學(xué)生表

-- 1、創(chuàng)建學(xué)生表student（左側(cè)主表）
create table if not exists student(
    sid int primary key auto increment,
    name varchar(20)，
    age int,
gender varchar(20)
);

3.2創(chuàng)建課程表

-- 2、創(chuàng)建課程表course（右側(cè)主表）
create table course(
    cid int primary keyauto increment,
    cidname varchar(20)
);

3.3創(chuàng)建中間表

-- 3、創(chuàng)建中間表student_course/score(從表)
create table score(
    sid int,
    cid int,
    score double
);

3.4創(chuàng)建外鍵約束（2次）

-- 4、建立外鍵約束
alter table score add foreign key(sid) references student(sid);
alter table score add foreign key(cid) references course(cid);

3.5添加學(xué)生數(shù)據(jù)

-- 5、添加學(xué)生數(shù)據(jù)
insert into student values
(1,'小龍女',18,'女'),
(2,'阿紫',19，'女'),
(3，'周芷若',20，'男');

3.6添加課表數(shù)據(jù)

insert into course values
(1，'語(yǔ)文'),
(2，'數(shù)學(xué)'),
(3,'英語(yǔ)');

3.7添加中間表數(shù)據(jù)

insert into score values
(1,1,78),
(1,2,79),
(2,1,88),
(2,3,82),
(3,2,90),
(3,3,69);

到此這篇關(guān)于MySQL外鍵約束與多表查詢基礎(chǔ)的文章就介紹到這了,更多相關(guān)mysql外鍵約束內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論