前言

前篇介紹了函數(shù)和查詢的基礎(chǔ)用法，此篇著重介紹查詢的一些進(jìn)階用法，重點(diǎn)在連接查詢里面的多表查詢。若有遺漏或者錯(cuò)誤，歡迎大家在評(píng)論區(qū)補(bǔ)充。

一、條件約束類型

1.主鍵約束(primary key)

表的唯一標(biāo)識(shí)符且不允許有null值，且一個(gè)表只能有一個(gè)主鍵

create table 表名 (列名 數(shù)據(jù)類型 primary key，列名...);

將學(xué)生表里面的id設(shè)置為了主鍵

若在插入數(shù)據(jù)時(shí)有空值或者重復(fù)的值都會(huì)報(bào)錯(cuò)

2.外鍵約束(foreign key)

介于多表之間的約束，確保一個(gè)表中的數(shù)據(jù)與另一個(gè)表中的數(shù)據(jù)匹配

注意：常用于多表之間的約束，需要依托父表主鍵才能使用

create table 表名(列名 數(shù)據(jù)類型，列名 數(shù)據(jù)類型...,foreign key(列名1) references 父表(列名2));
列名1：子表的列
列名2：父表的列

創(chuàng)建父表并插入數(shù)據(jù)，主鍵為班級(jí)id

創(chuàng)建子表并插入數(shù)據(jù)，外鍵為class_id，當(dāng)插入數(shù)據(jù)是父表中具有的則能正常插入

此時(shí)當(dāng)班級(jí)id為004時(shí),插入數(shù)據(jù)是父表中不具有的時(shí)候則會(huì)報(bào)錯(cuò)

若想要子表的外鍵具有該數(shù)據(jù)必須先在父表主鍵中插入該數(shù)據(jù)，子表才可以插入

同樣的操作如果是刪除父表中的某個(gè)數(shù)據(jù)，若該數(shù)據(jù)正好有主鍵與外鍵的約束且子表正好用到了，父表則會(huì)提示刪除失敗，要想刪除父表中已被約束的數(shù)據(jù)需要先將子表中的數(shù)據(jù)刪掉

注意：外鍵列和參照列的數(shù)據(jù)類型必須一致

3.唯一約束(unique)

確保列中的所有值都是唯一的，允許null值

create table 表名(列名 數(shù)據(jù)類型 unique，列名 數(shù)據(jù)類型...);

將學(xué)生的id設(shè)置為unique，當(dāng)插入的數(shù)據(jù)包含相同值時(shí)則會(huì)報(bào)錯(cuò)

4.非空約束(not null)

強(qiáng)制列不接受null值

create table 表名(列名 數(shù)據(jù)類型 not null,列名 數(shù)據(jù)類型...);

5.默認(rèn)值約束(default)

為列提供默認(rèn)值，當(dāng)指定插入時(shí)，沒有被選中的列會(huì)被填充默認(rèn)值(每一列的固定默認(rèn)值原本是null)

create table 表名 (列名 數(shù)據(jù)類型 default value1,列名 數(shù)據(jù)類型...);

將性別一列的默認(rèn)值設(shè)置為女，當(dāng)沒有指定插入name時(shí)gender會(huì)被賦予默認(rèn)即就是

補(bǔ)充：還可以通過添加auto_increment來為特定列進(jìn)行自動(dòng)填充數(shù)據(jù)

當(dāng)插入的數(shù)據(jù)是空值時(shí)，會(huì)為其自動(dòng)填充值

二、查詢操作的進(jìn)階用法

1.將查詢的結(jié)果作為數(shù)據(jù)插入表

insert into 表1 select * from 表2 where (限制條件);
表1：被插入表
表2：提供數(shù)據(jù)表

將scores表中的數(shù)學(xué)成績(jī)大于90的插入scores1表中

2.聚合查詢(通過聚合函數(shù)實(shí)現(xiàn))

聚合查詢是MySQL對(duì)于數(shù)據(jù)進(jìn)行列上的一種計(jì)算方式，常見的聚合函數(shù)包括統(tǒng)計(jì)數(shù)量、求和、平均值、最大值、最小值。（前篇文章函數(shù)章節(jié)有對(duì)于聚合函數(shù)的詳細(xì)介紹）

關(guān)于scores表查詢物理成績(jī)大于90分的總和以及大于90分的平均成績(jī)

3.連接查詢

內(nèi)連接

返回兩個(gè)表中匹配條件的行，在沒有連接條件的前提下類似于給兩個(gè)表做笛卡爾積。如果某一行在表中沒有匹配項(xiàng)看，則不會(huì)出現(xiàn)在結(jié)果中

select * from 表1,表2 where (限制條件);
select * from 表1 inner join 表2 on (限制條件);

在沒有限制條件的前提下，內(nèi)連接實(shí)則就是將兩張表的數(shù)據(jù)進(jìn)行笛卡爾積。但實(shí)際上連接起來的數(shù)據(jù)有些并非我們想要的，所以就還要加上連接條件對(duì)連接后的數(shù)據(jù)進(jìn)行進(jìn)一步篩選。

如上表所示，兩張表相關(guān)聯(lián)的數(shù)據(jù)就是學(xué)生表的id和分?jǐn)?shù)表的student_id，因此只需要在限制條件加上這倆數(shù)據(jù)相等則能篩選出目標(biāo)數(shù)據(jù)

多表查詢(重點(diǎn)?。。?

除此之外，還可以進(jìn)行多表連接更進(jìn)一步篩選出需要的數(shù)據(jù)

多表查詢的做題步驟

1.求出原始數(shù)據(jù)的笛卡爾積

2.加上連接條件

3.結(jié)合需求，進(jìn)一步增加條件，對(duì)結(jié)果更進(jìn)一步篩選(條件限制后加and...)

4.針對(duì)查詢的結(jié)果精簡(jiǎn)(將*改為所求的列名)

student表

grades表

course表

要求：查詢出每個(gè)學(xué)生姓名對(duì)應(yīng)的學(xué)科考了多少分

1.根據(jù)grades表可知，我們需要的數(shù)據(jù)分布在三個(gè)表中，因此我們要對(duì)三個(gè)表進(jìn)行笛卡爾積

數(shù)據(jù)過于龐大，共計(jì)生成13 * 5 * 7 = 455條數(shù)據(jù)

2.根據(jù)題意，加上連接條件，由于是三個(gè)表相連接所以至少都有兩條連接條件

根據(jù)grades表可知，里面包含的student_id和course_id都在其他兩個(gè)表中

因此連接條件為grades.student_id = student.id and grades.course_id = course.id

3.根據(jù)題意，我們將連接條件加上后已經(jīng)符合題意要求求出學(xué)生的每一科的成績(jī)因此不必再加限制條件

4.針對(duì)題目要求所求進(jìn)行精簡(jiǎn)，要求求出學(xué)生姓名，考試科目以及對(duì)應(yīng)的分?jǐn)?shù)。將*改為所求的表中的列名即可

綜上：求出了三個(gè)表連接以后并加以連接條件限制求出的最終結(jié)果

左(右)外連接

select * from 表1 left join 表2 on(限制條件);
left join 以左表為基礎(chǔ)，即表1
select * from 表1 right join 表2 on(限制條件);
right join 以右表為基礎(chǔ)，即表2
select * from 表1 left join 表2 = select * from 表2 right join 表1

左外連接以左表為基礎(chǔ)，返回左表中的所有記錄，即使右表中沒有匹配的記錄，右表中沒有匹配的記錄以null填充，反之右外連接

以test1為基礎(chǔ)輸出值，沒有匹配的部分填充null

以test2為基礎(chǔ)輸出值，沒有匹配的部分填充null

自連接

自己與自己進(jìn)行笛卡爾積

select * from 表名 別名1,表名 別名2;
兩個(gè)表名是同一個(gè)表名，為了方便自連接，所以要取兩個(gè)不同的別名才能進(jìn)行自連接

要求：查詢哪些人的物理成績(jī)比September高

通過兩表的自連接后在進(jìn)行s1與s2的比較即可求出結(jié)果

4.子查詢(本質(zhì)嵌套)

select * from 表名 where 列名 = (嵌套子句);
嵌套子句：select * from 表名 where (限制條件);

注意：主句的限制條件的數(shù)據(jù)類型要與子句的查詢的數(shù)據(jù)類型要一致，且子句的查詢出的結(jié)果只能有唯一值才能用子查詢

總結(jié)

此篇重點(diǎn)介紹了查詢的進(jìn)階用法，重點(diǎn)掌握內(nèi)連接和多表查詢的思路和方法。

到此這篇關(guān)于MySQL中查詢進(jìn)階運(yùn)用方法的文章就介紹到這了,更多相關(guān)MySQL查詢進(jìn)階運(yùn)用內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論