第九單元 連接查詢

請問上圖中顯示的數據是一張表中的嗎？

1. 連接查詢的分類

1. 內連接

2. 外連接（左外連接，右外連接，全連接）

3. 自連接

4. 交差連接（不講）

2. 內連接

註意:Join 與 inner join等效(預設為內連接)

語法格式： select 欄位列表 from 表1 inner join 表2 on 表1.外鍵=表2.外鍵 內連接：會把兩表中匹配上的數據進行連接顯示 inner join:內連接 on:當...時候 在使用連接時，儘量避免使用 * 獲取欄位

部門表(Dept)

員工表(Emp)

create database unit10;
go
use unit10;
go
create table Dept
(
    Id int primary key identity,
    DName varchar(30) not null unique
);
go
create table Emp
(
    Id int primary key identity,
    EName varchar(30) not null unique,
    Sex char(4) not null default '男',
    Age tinyint not null default 10,
    Sal decimal(10,2) not null default 0,
    Did int not null
);
go
insert into Dept values('開發部'),('測試部'),('市場部'),('銷售部'),('賬務部');
go
insert into Emp values
('任我行','男',30,8000,1),
('李換英','女',20,3100,3),
('張三','男',25,6000,2),
('李國華','男',30,3000,4),
('徐媛媛','女',20,7200,1),
('帥天行','男',32,9000,1),
('曲芙蓉','女',22,3100,3),
('李四','男',25,6000,2),
('王五','男',28,4000,4),
('姖倩倩','女',23,5200,1);
 

--2.查詢部門編號、部門名
select Id,DName from Dept
​
--3.查詢員工姓名，薪資
select * from Emp
​
--4.查詢員工姓名，薪資，所在部門
select  * from Emp inner join Dept on Emp.Did=Dept.Id
​
--5.查詢分配了部門的員工信息和相應的部門信息(內連接)
-- Dept:部門表   Emp:員工表
select a.*,b.DName  from Emp a join Dept b on a.Did=b.Id -- 為什麼取別名，因為下麵可能還會用到這個表
​
-- 1.重覆意思的欄位不要查詢出來
-- 2.重名的欄位，但是欄位意思不同，此時應該把欄位取個別名
​
use step2;
-- 查詢學生的姓名，性別，成績
select stuName,stuSex,skillScore  from StudentInfo 
inner join StudentScore on StudentInfo.stuId=StudentScore.stuId
--查詢學生的學生編號，姓名，性別，成績，課程
--  涉及到2張表
select a.stuId,stuName,stuSex,skillScore,CourseName
from StudentInfo a join  StudentScore b on a.stuId=b.stuId
​
​
-- 查詢學生在20歲以上的 學生編號，姓名，性別，成績，課程
-- 連接查詢+條件 需要把where 放到最後面
select a.stuId,stuName,stuSex,skillScore,CourseName
from StudentInfo a join  StudentScore b on a.stuId=b.stuId
where (year(getdate())-year(stuBirthday))>20

​

等值內連接

select * from  表1  （inner） join  表2  on  表1的主鍵列 = 表2的外鍵列
註：表1是主鍵表，表2是外鍵表
use step2;
​
-- 1.查詢出分配了班級的學生和班級信息
select a.*,b.Name,b.College from StudentInfo a inner join ClassInfo b on a.ClassId=b.Id
​
-- 2.查詢出軟體技術1班的學生和班級信息
select a.*,b.Name,b.College from StudentInfo a inner join ClassInfo b on a.ClassId=b.Id
where b.Name='軟體技術1班'
​
-- 3.查詢分配了班級，年齡又在20歲以上的學生和班級信息
select a.*,b.Name,b.College from StudentInfo a inner join ClassInfo b on a.ClassId=b.Id
where (year(getdate())-year(stuBirthday))>20
​
-- 4.查詢分配了班級，年齡又在20-24之間的學生和班級信息
select a.*,b.Name,b.College from StudentInfo a inner join ClassInfo b on a.ClassId=b.Id
where (year(getdate())-year(stuBirthday)) between 20 and 24
​
-- 5.查詢沒有學生的班級名稱
-- 如果這個班有學生，是不是一定會在學生中有classId
-- 如果說在學生表中沒有出現的classId,是不是就表示 那個班沒有學生呢？
select Name from ClassInfo where id not in
(
    select classId from StudentInfo
)
​
-- 6.查詢分配了班級，性別又為女的學生和班級信息
select stuName,stuBirthday,Name,College from StudentInfo a inner join ClassInfo b on a.ClassId=b.Id
where stuSex='女'
不等連接
定義:是使用除(=)以外的比較運算符查詢的內連接, 如:>，>=，<=，<，!>，!<，<> ,!=。

-- 每位同學除了自己所屬的班級外查詢出其餘可選擇的班級
select * from StudentInfo a join ClassInfo b on a.ClassId!=b.Id
​
-- 查詢其他同學的成績
select * from StudentInfo a join StudentScore b on a.stuId!=b.stuId
 

3. 自連接

select * from  表1  （inner） join  表2  on  表1的主鍵列 = 表2的外鍵列
註：表1和表2是一張表
​
create table menu
(
    Id int primary key identity,
    Name varchar(30), -- 菜單名稱
    ParentId int  -- 上級菜單
) ;
​
insert into menu values('商品管理',0),('系統管理',0),('訂單管理',0) ;
insert into menu values('商品列表',1),('商品分類',1),('發佈商品',1) ;
insert into menu values('許可權管理',2),('用戶管理',2),('角色管理',2) ;
insert into menu values('訂單列表',3),('運費模板',3),('物流跟蹤',3) ;

-- 查詢所有菜單信息以及它的上級菜單名稱
-- 自連接一定要取別名
select a.*,b.Name from menu a inner join menu b  on a.ParentId=b.Id
​

4. 外連接

外連接是以一張表為基表，返回基表中所有記錄及連接表中 符合條件的記錄的連接查詢。

• 左外連接

• 右外連接

• 全外連接

1. 左外連接

是以左表為基表，返回左表中所有記錄及連接表中符合條件的記錄的外連接。

1、以左表為基表 2、返回左表中所有數據 3、連接表不符合條件以NULL填充

use step2;
-- 1.查詢所有學生信息以及對應的班級信息（要求顯示學生編號，學生姓名，班級編號，班級名稱）
-- 左外連接：left outer join
select * from StudentInfo a left outer join  ClassInfo b on a.ClassId =b.Id
-- 左連接：以左表主表，不管是否匹配上，都會把左表中的數據都顯示出來,未匹配的數據會以NULL進行填充
​
​
-- 執行結果
180325011   任我行 5   13823204456 男   1999-09-09 00:00:00.000 5   會計3班    經濟管理系
180325012   張三  4   13823204452 女   1998-08-08 00:00:00.000 4   歐美軟體外包班 電腦系
180325013   李四  2   18899251152 男   1997-07-07 00:00:00.000 2   會計1班    經濟管理系
180325014   王五  1   13597445645 女   1998-08-08 00:00:00.000 1   軟體技術1班  電腦系
180325015   帥天行 5   13814204456 男   1998-06-06 00:00:00.000 5   會計3班    經濟管理系
180325016   葉星辰 5   17623204936 男   1998-05-05 00:00:00.000 5   會計3班    經濟管理系
180325017   趙日天 0   13922044932 男   1997-07-15 00:00:00.000 NULL    NULL    NULL
 

2. 右外連接

是以右表為基表，返回右表中所有記錄及連接表中符合條件的記錄的外連接。

1、以右表為基表 2、返回右表中所有數據 3、連接表不符合條件以NULL填充

-- 1.查詢所有班級信息以及對應的學生信息（要求顯示學生編號，學生姓名，班級編號，班級名稱）
select * from StudentInfo a right outer join ClassInfo b on a.ClassId=b.Id
-- 右連接：以右表為主表，不管是否匹配上，都會把右表中的數據都顯示出來，未匹配上的數據會以NULL進行填充

​

-- 執行結果
180325014   王五  1   13597445645 女   1998-08-08 00:00:00.000 1   軟體技術1班  電腦系
180325013   李四  2   18899251152 男   1997-07-07 00:00:00.000 2   會計1班    經濟管理系
NULL       NULL  NULL   NULL    NULL    NULL      3               會計2班    經濟管理系
180325012   張三  4   13823204452 女   1998-08-08 00:00:00.000 4   歐美軟體外包班 電腦系
180325011   任我行 5   13823204456 男   1999-09-09 00:00:00.000 5   會計3班    經濟管理系
180325015   帥天行 5   13814204456 男   1998-06-06 00:00:00.000 5   會計3班    經濟管理系
180325016   葉星辰 5   17623204936 男   1998-05-05 00:00:00.000 5   會計3班    經濟管理系

3. 全外連接

是分別以左右表為基表的外連接。

1、將左表和右表所有的記錄都顯示 2、連接表不符合條件以NULL填充

-- 1.查詢所有學生信息以及所有班級信息（要求顯示學生編號，學生姓名，班級編號，班級名稱）
-- 全連接：full  join,不管是否有沒有匹配上，都會顯示
select * from StudentInfo a full outer join ClassInfo b on a.ClassId=b.Id
​
180325011   任我行 5   13823204456 男   1999-09-09 00:00:00.000 5   會計3班    經濟管理系
180325012   張三  4   13823204452 女   1998-08-08 00:00:00.000 4   歐美軟體外包班 電腦系
180325013   李四  2   18899251152 男   1997-07-07 00:00:00.000 2   會計1班    經濟管理系
180325014   王五  1   13597445645 女   1998-08-08 00:00:00.000 1   軟體技術1班  電腦系
180325015   帥天行 5   13814204456 男   1998-06-06 00:00:00.000 5   會計3班    經濟管理系
180325016   葉星辰 5   17623204936 男   1998-05-05 00:00:00.000 5   會計3班    經濟管理系
180325017   趙日天 0   13922044932 男   1997-07-15 00:00:00.000