免費(fèi)學(xué)習(xí)推薦：mysql視頻教程

目錄

• • 模糊查詢(xún)
  • 表的約束
  • 表之間的關(guān)聯(lián)
  • • 多對(duì)一關(guān)聯(lián)
    • 多對(duì)多關(guān)聯(lián)
    • 一對(duì)一關(guān)聯(lián)

模糊查詢(xún)

可以根據(jù)大致提供的內(nèi)容，找到我們想要的數(shù)據(jù)，它與=查詢(xún)不同，拿char類(lèi)型數(shù)據(jù)和varchar類(lèi)型數(shù)據(jù)舉例：

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

模糊查詢(xún)使用到的是like

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

可以發(fā)現(xiàn)，c1里面的x為char類(lèi)型，我們通過(guò)模糊查詢(xún)是否有io這個(gè)數(shù)據(jù)，無(wú)法顯示出來(lái)，而我們通過(guò)=卻可以查詢(xún)出來(lái)

模糊查詢(xún)比較精準(zhǔn)，這種方式查詢(xún)，必須要輸入這個(gè)字段的全部?jī)?nèi)容，才可以查詢(xún)出來(lái)，而這里char類(lèi)型存儲(chǔ)的數(shù)據(jù)，長(zhǎng)度不滿(mǎn)10為，所以使用了空格補(bǔ)充，所以查詢(xún)的時(shí)候，需要把空格帶上才可以；

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

select * from c1 where x like 'io%';

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

select * from c1 where x like '_o%';

SQL模糊查詢(xún)的語(yǔ)法為
“SELECT column FROM table WHERE column LIKE ‘;pattern’;”。

SQL提供了四種匹配模式：

1. % 表示任意0個(gè)或多個(gè)bai字符。如下語(yǔ)句：
   SELECT * FROM user WHERE name LIKE ‘;%三%’;
   將會(huì)把name為“張三”，“三腳貓”，“唐三藏”等等有“三”的全找出來(lái)；
2. _ 表示任意單個(gè)字符。語(yǔ)句：
   SELECT * FROM user WHERE name LIKE ‘;三’;
   只找出“唐三藏”這樣name為三個(gè)字且中間一個(gè)字是“三”的；
   SELECT * FROM user WHERE name LIKE ‘;三__’;
   只找出“三腳貓”這樣name為三個(gè)字且第一個(gè)字是“三”的；
3. []表示括號(hào)內(nèi)所列字符中的一個(gè)（類(lèi)似與正則表達(dá)式）。語(yǔ)句：
   SELECT * FROM user WHERE name LIKE ‘;[張李王]三’;
   將找出“張三”、“李三”、“王三”（而不是“張李王三”）；
   如 [ ] 內(nèi)有一系列字符（01234、abcde之類(lèi)的）則可略寫(xiě)為“0-4”、“a-e”
   SELECT * FROM user WHERE name LIKE ‘;老[1-9]’;
   將找出“老1”、“老2”、……、“老9”；
   如要找“-”字符請(qǐng)將其放在首位：’;張三[-1-9]’;
4. [^ ] 表示不在括號(hào)所列之內(nèi)的單個(gè)字符。語(yǔ)句：
   SELECT * FROM user WHERE name LIKE ‘;[^張李王]三’;
   將找出不姓“張”、“李”、“王”的“趙三”、“孫三”等；
   SELECT * FROM user WHERE name LIKE ‘;老[^1-4]’;
   將排除“老1”到“老4”尋找“老5”、“老6”、……、“老9”。

表的約束

介紹：

• 約束條件與數(shù)據(jù)類(lèi)型的寬度一樣，都是可選參數(shù)

• 作用：用于保證數(shù)據(jù)的完整性和一致性

主要分為：

PRIMARY KEY (PK)    標(biāo)識(shí)該字段為該表的主鍵，可以唯一的標(biāo)識(shí)記錄FOREIGN KEY (FK)    標(biāo)識(shí)該字段為該表的外鍵NOT NULL    標(biāo)識(shí)該字段不能為空UNIQUE KEY (UK)    標(biāo)識(shí)該字段的值是唯一的AUTO_INCREMENT    標(biāo)識(shí)該字段的值自動(dòng)增長(zhǎng)（整數(shù)類(lèi)型，而且為主鍵）DEFAULT    為該字段設(shè)置默認(rèn)值UNSIGNED 無(wú)符號(hào) ZEROFILL 使用0填充

not null：字面意思就說(shuō)明了，設(shè)置后，每次插入值時(shí)，必須為該字段設(shè)置值

default：如果沒(méi)有為該字段設(shè)置值，則使用我們定義在default后面的一個(gè)默認(rèn)值

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

PRIMARY KEY：主鍵primary key是innodb存儲(chǔ)引擎組織數(shù)據(jù)的依據(jù)，innodb稱(chēng)之為索引組織表，一張表中必須有且只有一個(gè)主鍵。主鍵是能確定一條記錄的唯一標(biāo)識(shí)

AUTO_INCREMENT：當(dāng)設(shè)置以后，每次向表插入值時(shí)，這個(gè)字段會(huì)自動(dòng)增長(zhǎng)一個(gè)數(shù)字，但是這個(gè)字段必須是整數(shù)類(lèi)型，而且還要是主鍵

FOREIGN KEY：外鍵，將該表的某個(gè)字段關(guān)聯(lián)另一張表的某個(gè)字段，關(guān)聯(lián)后這個(gè)字段的值必須對(duì)應(yīng)關(guān)聯(lián)字段的值。

我們創(chuàng)建表，通常會(huì)有一個(gè)id字段作為索引標(biāo)識(shí)作用，并且會(huì)將它設(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)身份字段插入相同值，則會(huì)報(bào)錯(cuò)，因?yàn)樽侄卧O(shè)置了唯一值

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

我們會(huì)發(fā)現(xiàn)，id不對(duì)勁啊，那是因?yàn)楣P者之前進(jìn)行兩次插入值操作，但是值并沒(méi)有成功插入進(jìn)去，但是這個(gè)自增卻受到了影響.

這個(gè)時(shí)候，我們進(jìn)行兩部操作就可以解決這個(gè)問(wèn)題。

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

刪除id字段，再重新設(shè)置。

很神奇是不是，這個(gè)MySQL的底層機(jī)制。vary 良心

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

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

關(guān)于這個(gè)操作，如果我們只是刪除單條記錄的話(huà)，可以使用上序提供的方法還調(diào)整自增的值，而如果是刪除整個(gè)表記錄的話(huà)，使用以下方法：

truncate test;

效果演示：delete刪除整個(gè)表記錄

效果演示：truncate刪除整個(gè)表記錄

聯(lián)合主鍵

確保設(shè)置為主鍵的某幾個(gè)字段的數(shù)據(jù)相同

主鍵的一個(gè)目的就是確定數(shù)據(jù)的唯一性，它跟唯一約束的區(qū)別就是，唯一約束可以有一個(gè)NULL值，但是主鍵不能有NULL值，再說(shuō)聯(lián)合主鍵，聯(lián)合主鍵就是說(shuō)，當(dāng)一個(gè)字段可能存在重復(fù)值，無(wú)法確定這條數(shù)據(jù)的唯一性時(shí)，再加上一個(gè)字，兩個(gè)字段聯(lián)合起來(lái)確定這條數(shù)據(jù)的唯一性。比如你提到的id和name為聯(lián)合主鍵,在插入數(shù)據(jù)時(shí)，當(dāng)id相同，name不同，或者id不同，name相同時(shí)數(shù)據(jù)是允許被插入的，但是當(dāng)id和name都相同時(shí)，數(shù)據(jù)是不允許被插入的。

實(shí)例：

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

如果再次插入兩個(gè)主鍵相同的數(shù)據(jù)，則會(huì)報(bào)錯(cuò)

只要設(shè)置主鍵的兩個(gè)字段，在一條記錄內(nèi)，數(shù)據(jù)不完全相同就沒(méi)有問(wèn)題。

外鍵的話(huà)，我們?cè)诒碇g的關(guān)聯(lián)進(jìn)行演示

表之間的關(guān)聯(lián)

我們這里先介紹表之間的關(guān)聯(lián)，后面再學(xué)習(xí)聯(lián)表查詢(xún)

通過(guò)某一個(gè)字段，或者通過(guò)某一張表，將多個(gè)表關(guān)聯(lián)起來(lái)。

我們一張表處理好不行嗎，為什么要關(guān)聯(lián)，像這樣？

有沒(méi)有發(fā)現(xiàn)一個(gè)問(wèn)題，有些員工它們對(duì)應(yīng)的是相同部門(mén)，一張表就重復(fù)了很多次記錄，隨著員工數(shù)量的增加，就會(huì)出現(xiàn)越來(lái)越多個(gè)重復(fù)記錄，相對(duì)更占用空間了。

那么我們需要將部門(mén)單獨(dú)使用一張表，再將員工這個(gè)使用一個(gè)字段關(guān)聯(lián)到另一個(gè)表內(nèi)，我們可以使用外鍵，也可以不使用外鍵，先來(lái)演示外鍵的好處吧

多對(duì)一關(guān)聯(lián)

如：多個(gè)員工對(duì)應(yīng)一個(gè)部門(mén)。

員工表，先別急著創(chuàng)建，請(qǐ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   # 級(jí)聯(lián)更新 	on delete cascade); # 級(jí)聯(lián)刪除

上面外鍵的作用就是：

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

注意：必須是外鍵已存在，所以需要先創(chuàng)建部門(mén)表，再創(chuàng)建員工表

部門(mén)表

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','銷(xiāo)售');

員工表插入記錄

insert emp(name,dep_id) values 	('jack',1), 	('tom',2), 	('jams',1), 	('rouse',3), 	('curry',2); 	# ('go',4) 報(bào)錯(cuò)，在關(guān)聯(lián)外鍵的id字段中找不到

注意：如果我們emp表的dep_id字段插入的數(shù)據(jù)，在dep表中的id字段不存在該數(shù)據(jù)時(shí)，就會(huì)報(bào)錯(cuò)。

查詢(xún)我們創(chuàng)建后的效果

這樣就把這兩個(gè)表關(guān)聯(lián)起來(lái)了，目前我們先不了解多表查詢(xún)，這個(gè)先了解的是，表之間的關(guān)聯(lián)。

我們?cè)賮?lái)看一下同步更新以及刪除，外鍵的改動(dòng)被關(guān)聯(lián)表會(huì)受到影響

update dep set id=33333 where id = 3;

再來(lái)體驗(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)性的記錄會(huì)被全部刪除

多對(duì)多關(guān)聯(lián)

多張表互相關(guān)聯(lián)

如：一個(gè)作者可以寫(xiě)多本書(shū)，一本書(shū)也可以有多個(gè)作者，雙向的一對(duì)多，即多對(duì)多

這時(shí)使用外鍵會(huì)出現(xiàn)一個(gè)弊端，那就是先創(chuàng)建哪張表呢？它們都互相對(duì)應(yīng)，是不是很矛盾呢？解決辦法：第三張表，關(guān)聯(lián)書(shū)的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)一本書(shū)，或者一名作者關(guān)聯(lián)多本書(shū)，書(shū)也要體現(xiàn)出誰(shuí)關(guān)聯(lián)了它

book表插入數(shù)據(jù)：

insert book(name) values 	('斗破蒼穹'), 	('斗羅大陸'), 	('武動(dòng)乾坤');

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);

目前的對(duì)應(yīng)關(guān)系就是：

jack：斗破蒼穹、斗羅大陸、武動(dòng)乾坤
tom：斗破蒼穹、武動(dòng)乾坤
jams：斗羅大陸
rouse：斗破蒼穹
curry：斗破蒼穹、武動(dòng)乾坤
jhon：斗羅大陸

一個(gè)作者可以產(chǎn)于多本書(shū)的編寫(xiě)，同時(shí)，每本書(shū)都會(huì)標(biāo)明產(chǎn)于的作者

一對(duì)一關(guān)聯(lián)

路人有可能變成某個(gè)學(xué)校的學(xué)生，即一對(duì)一關(guān)系。

在這之前，路人不屬于學(xué)校。

原理就是：學(xué)校通過(guò)廣告，或者通過(guò)電話(huà)邀請(qǐng)，將路人變成了學(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入門(mén)到放棄',1), 	('Python入門(mén)到運(yùn)維',3), 	('Java從入門(mén)到音樂(lè)',2);

數(shù)據(jù)存儲(chǔ)的設(shè)計(jì)，需要提前設(shè)計(jì)好表的關(guān)聯(lián) 關(guān)系，將關(guān)系全部設(shè)計(jì)好以后，剩下的只是往里存數(shù)據(jù)了，后續(xù)我們會(huì)了解到聯(lián)表查詢(xún)相關(guān)內(nèi)容，將有關(guān)聯(lián)性的內(nèi)容，以虛擬表的形式查詢(xún)出來(lái)，查詢(xún)出來(lái)的數(shù)據(jù)可能來(lái)自多個(gè)表。

表的關(guān)聯(lián)，建議使用以下方式

• 多對(duì)多 > 多對(duì)一 > 一對(duì)一

相關(guān)免費(fèi)學(xué)習(xí)推薦：mysql數(shù)據(jù)庫(kù)(視頻)