您好,登錄后才能下訂單哦!
這篇文章將為大家詳細(xì)講解有關(guān)MySQL數(shù)據(jù)庫多表關(guān)聯(lián)的示例,小編覺得挺實(shí)用的,因此分享給大家做個參考,希望大家閱讀完這篇文章后可以有所收獲。
目錄
多對一關(guān)聯(lián)
多對多關(guān)聯(lián)
一對一關(guān)聯(lián)
模糊查詢
表的約束
表之間的關(guān)聯(lián)
模糊查詢
可以根據(jù)大致提供的內(nèi)容,找到我們想要的數(shù)據(jù),它與=查詢不同,拿char類型數(shù)據(jù)和varchar類型數(shù)據(jù)舉例:
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';
可以發(fā)現(xiàn),c1里面的x為char類型,我們通過模糊查詢是否有io這個數(shù)據(jù),無法顯示出來,而我們通過=卻可以查詢出來
模糊查詢比較精準(zhǔn),這種方式查詢,必須要輸入這個字段的全部內(nèi)容,才可以查詢出來,而這里char類型存儲的數(shù)據(jù),長度不滿10為,所以使用了空格補(bǔ)充,所以查詢的時候,需要把空格帶上才可以;
我們也可以使用模糊查詢提供給我們的查詢方式,% 表示任意0個或多個字符。
select * from c1 where x like 'io%';
如果我們只知道第二位是一個o,不知道開頭和結(jié)尾,可以使用:_ 表示任意單個字符,再配合%匹配后面的多個字符
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為三個字且第一個字是“三”的;[]表示括號內(nèi)所列字符中的一個(類似與正則表達(dá)式)。語句:
SELECT * FROM user WHERE name LIKE ‘;[張李王]三’;
將找出“張三”、“李三”、“王三”(而不是“張李王三”);
如 [ ] 內(nèi)有一系列字符(01234、abcde之類的)則可略寫為“0-4”、“a-e”
SELECT * FROM user WHERE name LIKE ‘;老[1-9]’;
將找出“老1”、“老2”、……、“老9”;
如要找“-”字符請將其放在首位:’;張三[-1-9]’;[^ ] 表示不在括號所列之內(nèi)的單個字符。語句:
SELECT * FROM user WHERE name LIKE ‘;[^張李王]三’;
將找出不姓“張”、“李”、“王”的“趙三”、“孫三”等;
SELECT * FROM user WHERE name LIKE ‘;老[^1-4]’;
將排除“老1”到“老4”尋找“老5”、“老6”、……、“老9”。
表的約束
介紹:
約束條件與數(shù)據(jù)類型的寬度一樣,都是可選參數(shù)
作用:用于保證數(shù)據(jù)的完整性和一致性
主要分為:
PRIMARY KEY (PK) 標(biāo)識該字段為該表的主鍵,可以唯一的標(biāo)識記錄FOREIGN KEY (FK) 標(biāo)識該字段為該表的外鍵NOT NULL 標(biāo)識該字段不能為空UNIQUE KEY (UK) 標(biāo)識該字段的值是唯一的AUTO_INCREMENT 標(biāo)識該字段的值自動增長(整數(shù)類型,而且為主鍵)DEFAULT 為該字段設(shè)置默認(rèn)值UNSIGNED 無符號 ZEROFILL 使用0填充
not null:字面意思就說明了,設(shè)置后,每次插入值時,必須為該字段設(shè)置值
default:如果沒有為該字段設(shè)置值,則使用我們定義在default后面的一個默認(rèn)值
UNIQUE KEY:某個字段設(shè)置這個約束后,那么它設(shè)置的值,在整個表中這個字段只能存在一個(唯一)
PRIMARY KEY:主鍵primary key是innodb存儲引擎組織數(shù)據(jù)的依據(jù),innodb稱之為索引組織表,一張表中必須有且只有一個主鍵。主鍵是能確定一條記錄的唯一標(biāo)識
AUTO_INCREMENT:當(dāng)設(shè)置以后,每次向表插入值時,這個字段會自動增長一個數(shù)字,但是這個字段必須是整數(shù)類型,而且還要是主鍵
FOREIGN KEY:外鍵,將該表的某個字段關(guān)聯(lián)另一張表的某個字段,關(guān)聯(lián)后這個字段的值必須對應(yīng)關(guān)聯(lián)字段的值。
我們創(chuàng)建表,通常會有一個id字段作為索引標(biāo)識作用,并且會將它設(shè)置為主鍵和自增。
實(shí)例:
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');
當(dāng)身份字段插入相同值,則會報(bào)錯,因?yàn)樽侄卧O(shè)置了唯一值
insert test(identity,gender) values('0123456789012345678','女');
我們會發(fā)現(xiàn),id不對勁啊,那是因?yàn)楣P者之前進(jìn)行兩次插入值操作,但是值并沒有成功插入進(jìn)去,但是這個自增卻受到了影響.
這個時候,我們進(jìn)行兩部操作就可以解決這個問題。
alter table test drop id;alter table test add id int primary key auto_increment first;
刪除id字段,再重新設(shè)置。
很神奇是不是,這個MySQL的底層機(jī)制。vary 良心
還需要注意的是:我們使用delete刪除一條記錄時,并不會影響自增
delete from test where id = 2;insert test(identity,gender) values('111111111111111111','男');
關(guān)于這個操作,如果我們只是刪除單條記錄的話,可以使用上序提供的方法還調(diào)整自增的值,而如果是刪除整個表記錄的話,使用以下方法:
truncate test;
效果演示:delete刪除整個表記錄
效果演示:truncate刪除整個表記錄
聯(lián)合主鍵
確保設(shè)置為主鍵的某幾個字段的數(shù)據(jù)相同
主鍵的一個目的就是確定數(shù)據(jù)的唯一性,它跟唯一約束的區(qū)別就是,唯一約束可以有一個NULL值,但是主鍵不能有NULL值,再說聯(lián)合主鍵,聯(lián)合主鍵就是說,當(dāng)一個字段可能存在重復(fù)值,無法確定這條數(shù)據(jù)的唯一性時,再加上一個字,兩個字段聯(lián)合起來確定這條數(shù)據(jù)的唯一性。比如你提到的id和name為聯(lián)合主鍵,在插入數(shù)據(jù)時,當(dāng)id相同,name不同,或者id不同,name相同時數(shù)據(jù)是允許被插入的,但是當(dāng)id和name都相同時,數(shù)據(jù)是不允許被插入的。
實(shí)例:
create table test( id int, name varchar(10), primary key(id,name)); insert test values(1,1);
如果再次插入兩個主鍵相同的數(shù)據(jù),則會報(bào)錯
只要設(shè)置主鍵的兩個字段,在一條記錄內(nèi),數(shù)據(jù)不完全相同就沒有問題。
外鍵的話,我們在表之間的關(guān)聯(lián)進(jìn)行演示
表之間的關(guān)聯(lián)
我們這里先介紹表之間的關(guān)聯(lián),后面再學(xué)習(xí)聯(lián)表查詢
通過某一個字段,或者通過某一張表,將多個表關(guān)聯(lián)起來。
我們一張表處理好不行嗎,為什么要關(guān)聯(lián),像這樣?
有沒有發(fā)現(xiàn)一個問題,有些員工它們對應(yīng)的是相同部門,一張表就重復(fù)了很多次記錄,隨著員工數(shù)量的增加,就會出現(xiàn)越來越多個重復(fù)記錄,相對更占用空間了。
那么我們需要將部門單獨(dú)使用一張表,再將員工這個使用一個字段關(guān)聯(lián)到另一個表內(nèi),我們可以使用外鍵,也可以不使用外鍵,先來演示外鍵的好處吧
多對一關(guān)聯(lián)
如:多個員工對應(yīng)一個部門。
員工表,先別急著創(chuàng)建,請向下看
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 # 級聯(lián)更新 on delete cascade); # 級聯(lián)刪除
上面外鍵的作用就是:
dep_id字段關(guān)聯(lián)了dep表的id字段:
當(dāng)dep表的id字段值修改后,該表的dep_id字段下面如果有和dep表id相同值的則會一起更改。
如果dep表刪除了某一條記錄,當(dāng)emp表的dep_id與dep表刪除記錄的id值對上以后,emp表這條記錄也會被隨之刪除。
注意:必須是外鍵已存在,所以需要先創(chuàng)建部門表,再創(chuàng)建員工表
部門表
create table dep( id int primary key auto_increment, name varchar(16) not null unique key, task varchar(16) not null);
emp表的dep_id字段設(shè)置的數(shù)據(jù)必須是dep表已存在的id
所以我們需要先向dep表插入記錄
insert dep(name,task) values('IT','技術(shù)'),('HR','招聘'),('sale','銷售');
員工表插入記錄
insert emp(name,dep_id) values ('jack',1), ('tom',2), ('jams',1), ('rouse',3), ('curry',2); # ('go',4) 報(bào)錯,在關(guān)聯(lián)外鍵的id字段中找不到
注意:如果我們emp表的dep_id字段插入的數(shù)據(jù),在dep表中的id字段不存在該數(shù)據(jù)時,就會報(bào)錯。
查詢我們創(chuàng)建后的效果
這樣就把這兩個表關(guān)聯(lián)起來了,目前我們先不了解多表查詢,這個先了解的是,表之間的關(guān)聯(lián)。
我們再來看一下同步更新以及刪除,外鍵的改動被關(guān)聯(lián)表會受到影響
update dep set id=33333 where id = 3;
再來體驗(yàn)一下同步刪除
delete from dep where id = 33333;
這就是外鍵帶給我們的效果,有利也有弊:
優(yōu)點(diǎn):關(guān)聯(lián)性強(qiáng),只能設(shè)置已存在的內(nèi)容,并且同步更新與刪除
缺點(diǎn):當(dāng)刪除外鍵表的某一條記錄,關(guān)聯(lián)表中有關(guān)聯(lián)性的記錄會被全部刪除
多對多關(guān)聯(lián)
多張表互相關(guān)聯(lián)
如:一個作者可以寫多本書,一本書也可以有多個作者,雙向的一對多,即多對多
這時使用外鍵會出現(xiàn)一個弊端,那就是先創(chuàng)建哪張表呢?它們都互相對應(yīng),是不是很矛盾呢?解決辦法:第三張表,關(guān)聯(lián)書的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));
中間表:負(fù)責(zé)將兩張表進(jìn)行關(guān)聯(lián)
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);
多名作者關(guān)聯(lián)一本書,或者一名作者關(guān)聯(lián)多本書,書也要體現(xiàn)出誰關(guān)聯(lián)了它
book表插入數(shù)據(jù):
insert book(name) values ('斗破蒼穹'), ('斗羅大陸'), ('武動乾坤');
author表插入數(shù)據(jù):
insert author(name) values ('jack'), ('tom'), ('jams'), ('rouse'), ('curry'), ('john');
關(guān)聯(lián)表插入數(shù)據(jù):
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);
目前的對應(yīng)關(guān)系就是:
jack:斗破蒼穹、斗羅大陸、武動乾坤
tom:斗破蒼穹、武動乾坤
jams:斗羅大陸
rouse:斗破蒼穹
curry:斗破蒼穹、武動乾坤
jhon:斗羅大陸
一個作者可以產(chǎn)于多本書的編寫,同時,每本書都會標(biāo)明產(chǎn)于的作者
一對一關(guān)聯(lián)
路人有可能變成某個學(xué)校的學(xué)生,即一對一關(guān)系。
在這之前,路人不屬于學(xué)校。
原理就是:學(xué)校通過廣告,或者通過電話邀請,將路人變成了學(xué)生。
路人表
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);
學(xué)校表
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入門到運(yùn)維',3), ('Java從入門到音樂',2);
數(shù)據(jù)存儲的設(shè)計(jì),需要提前設(shè)計(jì)好表的關(guān)聯(lián) 關(guān)系,將關(guān)系全部設(shè)計(jì)好以后,剩下的只是往里存數(shù)據(jù)了,后續(xù)我們會了解到聯(lián)表查詢相關(guān)內(nèi)容,將有關(guān)聯(lián)性的內(nèi)容,以虛擬表的形式查詢出來,查詢出來的數(shù)據(jù)可能來自多個表。
表的關(guān)聯(lián),建議使用以下方式
多對多 > 多對一 > 一對一
關(guān)于“MySQL數(shù)據(jù)庫多表關(guān)聯(lián)的示例”這篇文章就分享到這里了,希望以上內(nèi)容可以對大家有一定的幫助,使各位可以學(xué)到更多知識,如果覺得文章不錯,請把它分享出去讓更多的人看到。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。