前言

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

一、條件約束類型

1.主鍵約束(primary key)

表的唯一標識符且不允許有null值，且一個表只能有一個主鍵

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

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

若在插入數(shù)據(jù)時有空值或者重復的值都會報錯

2.外鍵約束(foreign key)

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

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

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

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

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

此時當班級id為004時,插入數(shù)據(jù)是父表中不具有的時候則會報錯

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

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

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

3.唯一約束(unique)

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

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

將學生的id設(shè)置為unique，當插入的數(shù)據(jù)包含相同值時則會報錯

4.非空約束(not null)

強制列不接受null值

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

5.默認值約束(default)

為列提供默認值，當指定插入時，沒有被選中的列會被填充默認值(每一列的固定默認值原本是null)

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

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

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

當插入的數(shù)據(jù)是空值時，會為其自動填充值

二、查詢操作的進階用法

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

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

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

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

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

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

3.連接查詢

內(nèi)連接

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

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

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

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

多表查詢(重點?。?！)

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

多表查詢的做題步驟

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

2.加上連接條件

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

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

student表

grades表

course表

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

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

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

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

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

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

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

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

綜上：求出了三個表連接以后并加以連接條件限制求出的最終結(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

自連接

自己與自己進行笛卡爾積

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

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

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

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

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

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

總結(jié)

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

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

最新評論