MySQL數據庫多表關聯的示例

138次閱讀

沒有評論

共計 5900 個字符，預計需要花費 15 分鐘才能閱讀完成。

自動寫代碼機器人，免費開通

這篇文章將為大家詳細講解有關 MySQL 數據庫多表關聯的示例，丸趣 TV 小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。

多對一關聯

多對多關聯

一對一關聯

模糊查詢

表的約束

表之間的關聯

模糊查詢

可以根據大致提供的內容，找到我們想要的數據，它與 = 查詢不同，拿 char 類型數據和 varchar 類型數據舉例：

create table c1(x char(10));create table c2(x varchar(10));insert c1 values(io insert c2 values( io

模糊查詢使用到的是 like

select * from c1 where x like  io select * from c2 where x like  io

可以發現，c1 里面的 x 為 char 類型，我們通過模糊查詢是否有 io 這個數據，無法顯示出來，而我們通過 = 卻可以查詢出來

模糊查詢比較精準，這種方式查詢，必須要輸入這個字段的全部內容，才可以查詢出來，而這里 char 類型存儲的數據，長度不滿 10 為，所以使用了空格補充，所以查詢的時候，需要把空格帶上才可以；

我們也可以使用模糊查詢提供給我們的查詢方式，% 表示任意 0 個或多個字符。

select * from c1 where x like  io%

如果我們只知道第二位是一個 o，不知道開頭和結尾，可以使用：_ 表示任意單個字符，再配合 % 匹配后面的多個字符

select * from c1 where x like  _o%

SQL 模糊查詢的語法為
“SELECT column FROM table WHERE column LIKE‘;pattern’;”。

SQL 提供了四種匹配模式：

% 表示任意 0 個或多個 bai 字符。如下語句：
SELECT * FROM user WHERE name LIKE‘;% 三 %’;
將會把 name 為“張三”，“三腳貓”，“唐三藏”等等有“三”的全找出來；

_ 表示任意單個字符。語句：
SELECT * FROM user WHERE name LIKE‘; 三’;
只找出“唐三藏”這樣 name 為三個字且中間一個字是“三”的；
SELECT * FROM user WHERE name LIKE‘; 三__’;
只找出“三腳貓”這樣 name 為三個字且第一個字是“三”的；

[] 表示括號內所列字符中的一個（類似與正則表達式）。語句：
SELECT * FROM user WHERE name LIKE‘;[張李王] 三’;
將找出“張三”、“李三”、“王三”（而不是“張李王三”）；
如 [] 內有一系列字符（01234、abcde 之類的）則可略寫為“0-4”、“a-e”
SELECT * FROM user WHERE name LIKE‘; 老 [1-9]’;
將找出“老 1”、“老 2”、……、“老 9”；
如要找“-”字符請將其放在首位：’; 張三 [-1-9]’;

[^] 表示不在括號所列之內的單個字符。語句：
SELECT * FROM user WHERE name LIKE‘;[^ 張李王] 三’;
將找出不姓“張”、“李”、“王”的“趙三”、“孫三”等；
SELECT * FROM user WHERE name LIKE‘; 老 [^1-4]’;
將排除“老 1”到“老 4”尋找“老 5”、“老 6”、……、“老 9”。

表的約束

介紹：

約束條件與數據類型的寬度一樣，都是可選參數

作用：用于保證數據的完整性和一致性

主要分為：

PRIMARY KEY (PK)  標識該字段為該表的主鍵，可以唯一的標識記錄 FOREIGN KEY (FK)  標識該字段為該表的外鍵 NOT NULL  標識該字段不能為空 UNIQUE KEY (UK)  標識該字段的值是唯一的 AUTO_INCREMENT  標識該字段的值自動增長（整數類型，而且為主鍵）DEFAULT  為該字段設置默認值 UNSIGNED  無符號
ZEROFILL  使用 0 填充

not null：字面意思就說明了，設置后，每次插入值時，必須為該字段設置值

default：如果沒有為該字段設置值，則使用我們定義在 default 后面的一個默認值

UNIQUE KEY：某個字段設置這個約束后，那么它設置的值，在整個表中這個字段只能存在一個（唯一）

PRIMARY KEY：主鍵 primary key 是 innodb 存儲引擎組織數據的依據，innodb 稱之為索引組織表，一張表中必須有且只有一個主鍵。主鍵是能確定一條記錄的唯一標識

AUTO_INCREMENT：當設置以后，每次向表插入值時，這個字段會自動增長一個數字，但是這個字段必須是整數類型，而且還要是主鍵

FOREIGN KEY：外鍵，將該表的某個字段關聯另一張表的某個字段，關聯后這個字段的值必須對應關聯字段的值。

我們創建表，通常會有一個 id 字段作為索引標識作用，并且會將它設置為主鍵和自增。

實例：

create table test(
 id int primary key auto_increment,
 identity varchar(18) not null unique key, -- 身份證必須唯一
 gender varchar(18) default  男 insert test(identity) values(123456789012345678

當身份字段插入相同值，則會報錯，因為字段設置了唯一值

insert test(identity,gender) values(0123456789012345678 , 女

我們會發現，id 不對勁啊，那是因為筆者之前進行兩次插入值操作，但是值并沒有成功插入進去，但是這個自增卻受到了影響.

這個時候，我們進行兩部操作就可以解決這個問題。

alter table test drop id;alter table test add id int primary key auto_increment first;

刪除 id 字段，再重新設置。

很神奇是不是，這個 MySQL 的底層機制。vary 良心

還需要注意的是：我們使用 delete 刪除一條記錄時，并不會影響自增

delete from test where id = 2;insert test(identity,gender) values(111111111111111111 , 男

關于這個操作，如果我們只是刪除單條記錄的話，可以使用上序提供的方法還調整自增的值，而如果是刪除整個表記錄的話，使用以下方法：

truncate test;

效果演示：delete 刪除整個表記錄

效果演示：truncate 刪除整個表記錄

聯合主鍵

確保設置為主鍵的某幾個字段的數據相同

主鍵的一個目的就是確定數據的唯一性，它跟唯一約束的區別就是，唯一約束可以有一個 NULL 值，但是主鍵不能有 NULL 值，再說聯合主鍵，聯合主鍵就是說，當一個字段可能存在重復值，無法確定這條數據的唯一性時，再加上一個字，兩個字段聯合起來確定這條數據的唯一性。比如你提到的 id 和 name 為聯合主鍵, 在插入數據時，當 id 相同，name 不同，或者 id 不同，name 相同時數據是允許被插入的，但是當 id 和 name 都相同時，數據是不允許被插入的。

實例：

create table test(
 id int,
 name varchar(10),
 primary key(id,name));
 insert test values(1,1);

如果再次插入兩個主鍵相同的數據，則會報錯

只要設置主鍵的兩個字段，在一條記錄內，數據不完全相同就沒有問題。

外鍵的話，我們在表之間的關聯進行演示

表之間的關聯

我們這里先介紹表之間的關聯，后面再學習聯表查詢

通過某一個字段，或者通過某一張表，將多個表關聯起來。

我們一張表處理好不行嗎，為什么要關聯，像這樣？

有沒有發現一個問題，有些員工它們對應的是相同部門，一張表就重復了很多次記錄，隨著員工數量的增加，就會出現越來越多個重復記錄，相對更占用空間了。

那么我們需要將部門單獨使用一張表，再將員工這個使用一個字段關聯到另一個表內，我們可以使用外鍵，也可以不使用外鍵，先來演示外鍵的好處吧

多對一關聯

如：多個員工對應一個部門。

員工表，先別急著創建，請向下看

create table emp(
 id int primary key auto_increment,
 name varchar(10) not null,
 dep_id int,
 foreign key(dep_id) references dep(id) 
 on update cascade #  級聯更新
 on delete cascade); #  級聯刪除

上面外鍵的作用就是：

dep_id 字段關聯了 dep 表的 id 字段：
當 dep 表的 id 字段值修改后，該表的 dep_id 字段下面如果有和 dep 表 id 相同值的則會一起更改。
如果 dep 表刪除了某一條記錄，當 emp 表的 dep_id 與 dep 表刪除記錄的 id 值對上以后，emp 表這條記錄也會被隨之刪除。

注意：必須是外鍵已存在，所以需要先創建部門表，再創建員工表

部門表

create table dep(
 id int primary key auto_increment,
 name varchar(16) not null unique key,
 task varchar(16) not null);

emp 表的 dep_id 字段設置的數據必須是 dep 表已存在的 id

所以我們需要先向 dep 表插入記錄

insert dep(name,task) values(IT , 技術),(HR , 招聘),(sale , 銷售

員工表插入記錄

insert emp(name,dep_id) values
 (jack ,1),
 (tom ,2),
 (jams ,1),
 (rouse ,3),
 (curry ,2);
 # (go ,4)  報錯，在關聯外鍵的 id 字段中找不到

注意：如果我們 emp 表的 dep_id 字段插入的數據，在 dep 表中的 id 字段不存在該數據時，就會報錯。

查詢我們創建后的效果

這樣就把這兩個表關聯起來了，目前我們先不了解多表查詢，這個先了解的是，表之間的關聯。

我們再來看一下同步更新以及刪除，外鍵的改動被關聯表會受到影響

update dep set id=33333 where id = 3;

再來體驗一下同步刪除

delete from dep where id = 33333;

這就是外鍵帶給我們的效果，有利也有弊：

優點：關聯性強，只能設置已存在的內容，并且同步更新與刪除

缺點：當刪除外鍵表的某一條記錄，關聯表中有關聯性的記錄會被全部刪除

多對多關聯

多張表互相關聯

如：一個作者可以寫多本書，一本書也可以有多個作者，雙向的一對多，即多對多

這時使用外鍵會出現一個弊端，那就是先創建哪張表呢？它們都互相對應，是不是很矛盾呢？解決辦法：第三張表，關聯書的 id 與作者的 id

book 表

create table book(
 id int primary key auto_increment,
 name varchar(30));

author 表

create table author(
 id int primary key auto_increment,
 name varchar(30));

中間表：負責將兩張表進行關聯

create table authorRbook(
 id int primary key auto_increment,
 author_id int,
 book_id int,
 foreign key(book_id) references book(id)
 on update cascade
 on delete cascade,
 foreign key(author_id) references author(id)
 on update cascade
 on delete cascade);

多名作者關聯一本書，或者一名作者關聯多本書，書也要體現出誰關聯了它

book 表插入數據：

insert book(name) values
 (斗破蒼穹),
 (斗羅大陸),
 (武動乾坤

author 表插入數據：

insert author(name) values
 (jack),
 (tom),
 (jams),
 (rouse),
 (curry),
 (john

關聯表插入數據：

insert authorRbook(author_id,book_id) values
 (1,1),
 (1,2),
 (1,3),
 (2,1),
 (2,3),
 (3,2),
 (4,1),
 (5,1),
 (5,3),
 (6,2);

目前的對應關系就是：

jack：斗破蒼穹、斗羅大陸、武動乾坤
tom：斗破蒼穹、武動乾坤
jams：斗羅大陸
rouse：斗破蒼穹
curry：斗破蒼穹、武動乾坤
jhon：斗羅大陸

一個作者可以產于多本書的編寫，同時，每本書都會標明產于的作者

一對一關聯

路人有可能變成某個學校的學生，即一對一關系。

在這之前，路人不屬于學校。

原理就是：學校通過廣告，或者通過電話邀請，將路人變成了學生。

路人表

create table passers_by(
 id int primary key auto_increment,
 name varchar(10),
 age int);
 insert passers_by(name,age) values
 (jack ,18),
 (tom ,19),
 (jams ,23);

學校表

create table school(
 id int primary key auto_increment,
 class varchar(10),
 student_id int unique key,
 foreign key(student_id) references passers_by(id)
 on update cascade
 on delete cascade);insert school(class,student_id) values
 (Mysql 入門到放棄 ,1),
 (Python 入門到運維 ,3),
 (Java 從入門到音樂 ,2);

數據存儲的設計，需要提前設計好表的關聯關系，將關系全部設計好以后，剩下的只是往里存數據了，后續我們會了解到聯表查詢相關內容，將有關聯性的內容，以虛擬表的形式查詢出來，查詢出來的數據可能來自多個表。

表的關聯，建議使用以下方式

多對多多對一一對一

關于“MySQL 數據庫多表關聯的示例”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，使各位可以學到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。

向 AI 問一下細節

正文完