SQL語言之查詢（二）

前言

本章我們將學(xué)習(xí)SQL查詢中的高級(jí)部分，如內(nèi)連接、外連接和子查詢，通過這些查詢技術(shù)我們將能夠解決項(xiàng)目中復(fù)雜的查詢問題。

外鍵約束

MySQL屬于關(guān)系型的數(shù)據(jù)庫，表之間可以建立關(guān)系，如：學(xué)生表和成績表，在成績表中添加學(xué)生編號(hào)引用學(xué)生表中的學(xué)生編號(hào)，這樣在成績表中就不用添加重復(fù)的學(xué)生信息了，這種關(guān)系也叫主外鍵關(guān)系，可以通過設(shè)置外鍵約束實(shí)現(xiàn)。

可以在創(chuàng)建表時(shí)，添加外鍵約束來保證表和表之間引用完整性，添加外鍵后：

在插入外鍵表數(shù)據(jù)前，必須先插入主表數(shù)據(jù)

在刪除主表數(shù)據(jù)前，必須先刪除外鍵表數(shù)據(jù)

語法：

create table 表名

(

字段名 類型 約束,

... ,

constraint 外鍵名稱 foreign key (外鍵列) references 主表(主鍵)

);

代碼示例：

use mysql_db;

-- 創(chuàng)建成績表

drop table if exists tb_score;

create table tb_score

(

score_id int primary key auto_increment,

score_stu_id int,

score int,

score_course varchar(20),

constraint fk_score_stu_id foreign key(score_stu_id) references tb_student(stu_id)

);

內(nèi)連接查詢

在查詢時(shí)我們經(jīng)常要把相關(guān)的多張表的字段，一起查詢出來，如查詢學(xué)生成績時(shí)，要顯示分?jǐn)?shù)和學(xué)生姓名。這個(gè)時(shí)候我們就需要連接查詢了，連接查詢分為內(nèi)連接和外連接，我們先學(xué)習(xí)內(nèi)連接查詢。

內(nèi)連接查詢的特點(diǎn)是：會(huì)查詢出相關(guān)表中都存在的數(shù)據(jù)。

語法有兩種實(shí)現(xiàn)方法：

1）select 字段..... from 表1 inner join 表2

on 表1.主鍵 = 表2.外鍵;

注意：這里假設(shè)表1是主表，內(nèi)連接表的前后順序無關(guān)

2）select 字段..... from 表1 , 表2

where 表1.主鍵 = 表2.外鍵;

代碼示例：

-- 查詢學(xué)生姓名和成績 方式1

select s.stu_id ,s.stu_name,c.score_course,c.score from tb_score c inner join tb_student s on s.stu_id = c.score_stu_id;

-- 方式2

select s.stu_id ,s.stu_name,c.score_course,c.score from tb_score c , tb_student s where s.stu_id = c.score_stu_id;

效果相同：

外連接查詢

外連接分為左外連接和右外連接：

1) 左外連接

連接查詢多張表的數(shù)據(jù)，顯示所有左表的數(shù)據(jù)，右表存在不相符的數(shù)據(jù)補(bǔ)null。

語法：

select 字段... from 左表 left join 右表

on 主表.主鍵 = 子表.外鍵;

代碼示例：

-- 左外連接，查詢學(xué)生姓名和成績

select s.stu_id,s.stu_name,c.score_course,c.score from tb_student s left join tb_score c on s.stu_id = c.score_stu_id;

-- 查詢所有參加過考試的同學(xué)

select s.stu_id,s.stu_name,c.score_course,c.score from tb_student s left join tb_score c on s.stu_id = c.score_stu_id where c.score is not null;

-- 查詢所有沒參加過考試的同學(xué)

select s.stu_id,s.stu_name,c.score_course,c.score from tb_student s left join tb_score c on s.stu_id = c.score_stu_id where c.score is null;

2）右外連接

與左連接相反，顯示所有右表數(shù)據(jù)，左表中不相符的數(shù)據(jù)補(bǔ)null。

語法：

select 字段... from 左表 right join 右表

on 主表.主鍵 = 子表.外鍵;

代碼示例：

select s.stu_id,s.stu_name,c.score_course,c.score from tb_score c right join tb_student s on s.stu_id = c.score_stu_id;

子查詢

在查詢語句中還可以嵌入查詢語句，嵌入的查詢也叫子查詢，子查詢將先執(zhí)行，查詢到結(jié)果后，父查詢可以將此結(jié)果作為查詢條件再進(jìn)行一次查詢，這樣可以解決比較復(fù)雜的查詢問題。

語法：

select ... from 表 where 字段 比較運(yùn)算符 (select ... from 表 where 條件);

代碼示例：

-- 查詢年齡比李四大的學(xué)生

select * from tb_student where stu_age > (select stu_age from tb_student where stu_name = '李四');

-- 查詢李四的老鄉(xiāng)

select * from tb_student where stu_address = (select stu_address from tb_student where stu_name = '李四');

子查詢之IN

有時(shí)候當(dāng)子查詢中查詢結(jié)果不止一個(gè)的情況下，使用比較運(yùn)算符會(huì)出現(xiàn)錯(cuò)誤，這時(shí)候我們就需要使用一些關(guān)鍵字來幫助篩選結(jié)果。

in關(guān)鍵字的作用是在字段和數(shù)據(jù)列表中任意一個(gè)相等，條件就成立。

代碼示例：

-- 查詢語文分?jǐn)?shù)考相同的學(xué)生，先用子查詢查語文的成績，在用內(nèi)連接查考過語文的學(xué)生姓名和成績，把成績進(jìn)行比較

select stu_name,score_course,score from tb_student inner join tb_score on tb_student.stu_id = tb_score.score_stu_id where score_course='語文' and score in(select score from tb_score where score_course = '語文');

子查詢之ALL

all和比較運(yùn)算符配合使用，如果字段和所有的查詢結(jié)果都比較成立，結(jié)果才成立。

語法：

字段 比較運(yùn)算 all(查詢結(jié)果)

代碼示例：

-- 查詢比所有男學(xué)生小的女學(xué)生，先查所有男學(xué)生的年齡，如果女學(xué)生年齡比所有這些年齡大，就查出來

select stu_name,stu_age,stu_gender from tb_student where stu_gender = '女' and stu_age < all(select stu_age from tb_student where stu_gender = '男');

子查詢之ANY

any和比較運(yùn)算符配合使用，如果字段和任意一個(gè)查詢結(jié)果比較成立，則結(jié)果成立。

語法：

字段 比較運(yùn)算 any(查詢結(jié)果)

代碼示例：

-- 查詢只要比一個(gè)南京學(xué)生大的武漢學(xué)生

select stu_name,stu_address from tb_student where stu_address = '武漢'

and stu_age > any(select stu_age from tb_student where stu_address='南京');

子查詢之Exists

exists表示是否有查詢結(jié)果，如果沒有結(jié)果，返回false，有結(jié)果則返回true

語法：

exists(查詢結(jié)果)

-- 查詢考過英語的同學(xué)，在子查詢中需要判斷父查詢中的學(xué)生id是否在子查詢中存在

select * from tb_student where

exists(select score_stu_id from tb_score where tb_student.stu_id = tb_score.score_stu_id and score_course = '英語');

總結(jié)

本章我們學(xué)習(xí)了內(nèi)連接、外連接、子查詢等高級(jí)查詢方法，有時(shí)候這些查詢方法需要綜合運(yùn)用起來，當(dāng)我們熟悉了它們后，查詢數(shù)據(jù)就不是難事了。