連接查詢

通過查詢多張表，用連接查詢進(jìn)行多表聯(lián)合查詢

關(guān)鍵字：inner join 內(nèi)連接

? ? ? ? ? ? ? ?left join 左連接

? ? ? ? ? ? ? ?right join 右連接?

數(shù)據(jù)準(zhǔn)備

創(chuàng)建新的數(shù)據(jù)庫(kù)：create database 數(shù)據(jù)庫(kù)名;

create database db_test2;

使用數(shù)據(jù)庫(kù)：use 數(shù)據(jù)庫(kù)名;

use db_test2;

創(chuàng)建班級(jí)信息表：

create table 表名（字段名1，字段名2，......);

代碼實(shí)現(xiàn)

create table classes(class_id int primary key auto_increment,#主鍵自增class_name varchar(40) not null unique,class_remark varchar(200)
);

創(chuàng)建學(xué)生表：

create table 表名（字段名1，字段名2，......);

代碼實(shí)現(xiàn)

create table students(stu_num char(8) primary key ,stu_name varchar(20) not null,stu_gender char(2) not null,stu_age int not null,cid int,constraint FK_STUDENTS_CLASSES foreign key(cid) references classes(class_id)on update cascade ON DELETE CASCADE
);

添加班級(jí)信息

insert into 表名 （字段名）values(添加的數(shù)據(jù));

代碼實(shí)現(xiàn)

insert into classes (class_name,class_remark)values('Java2204','......');insert into classes (class_name,class_remark)values('Java2205','......');insert into classes (class_name,class_remark)values('Java2206','......');insert into classes (class_name,class_remark)values('Python','......');

運(yùn)行結(jié)果

添加學(xué)生信息

以下三個(gè)信息，屬于class_id=1 的班級(jí) （Java2204）

代碼實(shí)現(xiàn)

insert into students (stu_num,stu_name,stu_gender,stu_age,cid)
values('20220101','張三','男',20,1);insert into students (stu_num,stu_name,stu_gender,stu_age,cid)
values('20220102','李四','女',20,1);insert into students (stu_num,stu_name,stu_gender,stu_age,cid)
values('20220103','王五','男',20,1);

以下兩個(gè)學(xué)生信息，屬于class_id=2 的班級(jí) （Java2205）

代碼實(shí)現(xiàn)

insert into students (stu_num,stu_name,stu_gender,stu_age,cid)
values('20220104','趙婷','女',20,1);insert into students (stu_num,stu_name,stu_gender,stu_age,cid)
values('20220105','孫七','男',20,2);

小紅和小明沒有設(shè)置班級(jí)信息

代碼實(shí)現(xiàn)

insert into students (stu_num,stu_name,stu_gender,stu_age)
values('20220106','小紅','女',20);insert into students (stu_num,stu_name,stu_gender,stu_age)
values('20220107','小明','男',20);

運(yùn)行結(jié)果

select * from 表名；查詢表

代碼實(shí)現(xiàn)

select * from students s ;
select * from classes c ;

運(yùn)行結(jié)果

內(nèi)連接

語(yǔ)法

select ...... from 表名1 inner join 表名2 on 匹配條件 [where 條件]；

經(jīng)過內(nèi)連接（表連接）之后，將兩張表的數(shù)據(jù)以笛卡爾積的效果進(jìn)行連接

代碼實(shí)現(xiàn)

select * from 表名1?inner join 表名2;

select * from students inner join classes;

?運(yùn)行結(jié)果

產(chǎn)生笛卡爾積，效果如下，將表1中的每個(gè)數(shù)據(jù)與第二個(gè)表中的每個(gè)數(shù)據(jù)都進(jìn)行匹配

7 * 3 = 28，共有28個(gè)數(shù)據(jù)

消除笛卡爾積，在前綴后加上 on 匹配條件 [where 條件];

select ...... from 表名1 inner join 表名2 on 匹配條件 [where 條件]；?

內(nèi)連接條件

兩張表使用inner join連接查詢之后生產(chǎn)的笛卡爾積數(shù)據(jù)中很多數(shù)據(jù)都是無(wú)意義的，我們?nèi)绾蜗裏o(wú)意義的數(shù)據(jù)呢？——添加兩張進(jìn)行連接查詢時(shí)的匹配條件

使用 on 設(shè)置兩張表連接查詢時(shí)的匹配條件

兩張表連接查詢+條件

代碼實(shí)現(xiàn)

使用where進(jìn)行兩個(gè)表之間的連接

select * from students inner join classes where students.cid=classes.class_id ;

運(yùn)行結(jié)果

代碼實(shí)現(xiàn)

使用on進(jìn)行兩個(gè)表之間的連接

select * from students inner join classes on students.cid=classes.class_id ;

運(yùn)行結(jié)果

on連接查詢和where連接查詢的區(qū)別

where篩選：先生成笛卡爾積后進(jìn)行判斷連接條件是否成立

on篩選：先進(jìn)行判斷連接條件是否成立，如果成立后，再會(huì)進(jìn)行組合，就不會(huì)有笛卡爾積的結(jié)果

左連接 LEFT JOIN

左連接定義

左連接顯示左表中的所有數(shù)據(jù)，如果在右表中存在與左表記錄滿足匹配條件的數(shù)據(jù)，則進(jìn)行匹配，如果右表中不存在匹配數(shù)據(jù)，則顯示為NULL；

語(yǔ)法

select * from 左表名 left join 右表名 on 匹配條件 [where 條件]；

作用

左連接：顯示左表中的所有記錄

例

需求

請(qǐng)查詢出所有學(xué)生信息，如果有學(xué)生有對(duì)應(yīng)的班級(jí)信息，則將對(duì)應(yīng)的班級(jí)信息也查詢出來

代碼實(shí)現(xiàn)

select * from students left join classes on students.cid=classes.class_id ;

運(yùn)行結(jié)果

右連接 RIGHT JOIN

右連接定義

右連接顯示右表中的所有數(shù)據(jù)，如果在左表中存在與右表記錄滿足匹配條件的數(shù)據(jù)，則進(jìn)行匹配，如果左表中不存在匹配數(shù)據(jù)，則顯示為NULL；

語(yǔ)法

select * from 表名1?RIGHT JOIN 表名2 ON 表名1與表名2的關(guān)聯(lián)字符；

作用

右連接：顯示右表中的所有記錄

例

需求

將右表中的所有數(shù)據(jù)顯示出來

代碼實(shí)現(xiàn)

select * from students right join classes on students.cid=classes.class_id ;

運(yùn)行結(jié)果

左連接、右連接與內(nèi)連接的區(qū)別

內(nèi)連接：只會(huì)顯示出兩表中有關(guān)聯(lián)的數(shù)據(jù)

左連接：顯示出左表中的所有數(shù)據(jù)，右表中只寫有關(guān)聯(lián)的數(shù)據(jù)

右連接：顯示出右表中的所有數(shù)據(jù)，左表中只寫有關(guān)聯(lián)的數(shù)據(jù)

數(shù)據(jù)表別名

語(yǔ)法

alter table 表名?rename column 列名 to 新列名；

案例

代碼實(shí)現(xiàn)

修改列名

alter table students rename column stu_name to name;
alter table classes rename column class_name to name;

運(yùn)行結(jié)果

當(dāng)兩個(gè)表的字段名稱相同時(shí)，如何進(jìn)行查詢字段

代碼實(shí)現(xiàn)

select students.name,classes.name from students inner join classes on students.cid = classes.class_id ;

運(yùn)行結(jié)果

使用別名查詢字段

代碼實(shí)現(xiàn)

select s.name,c.name from students s inner join classes c on s.cid=c.class_id ;

運(yùn)行結(jié)果?

子查詢/嵌套查詢?

定義

子查詢——先進(jìn)行一次查詢，第一次查詢的結(jié)果作為第二次查詢的源/條件（第二次查詢是基于第一次的查詢結(jié)果來進(jìn)行的）

子查詢返回單個(gè)值——單行單列

案例?

查詢班級(jí)表中字段名為Java2204的數(shù)據(jù)，?查詢所有Java2204班級(jí)中選課副碼為1的學(xué)生信息

代碼實(shí)現(xiàn)

查詢班級(jí)表中字段名為Java2204的數(shù)據(jù)

select class_id from classes c where name ='Java2204';

?查詢所有Java2204班級(jí)中選課副碼為1的學(xué)生信息

select * from students s where cid=1;

運(yùn)行結(jié)果

查詢所有Java班級(jí)中的學(xué)生信息?單列多行查詢

代碼實(shí)現(xiàn)

select class_id from classes c where name like 'Java%' ;

運(yùn)行結(jié)果

顯示三條查詢語(yǔ)句 union連接關(guān)鍵字

代碼實(shí)現(xiàn)

#顯示三條查詢語(yǔ)句 union連接關(guān)鍵字
select * from students s where cid=1
union
select * from students s where cid=2
union
select * from students s where cid=3;

運(yùn)行結(jié)果

子查詢，in關(guān)鍵字 單列多行查詢

如果查詢結(jié)果是單列多行，要有關(guān)鍵字in

in代表的是包含，not in代表不包含

代碼實(shí)現(xiàn)

select * from students s where cid in(select class_id from classes c where name like 'Java%');

運(yùn)行結(jié)果

?查詢cid=1的班級(jí)中性別為男的學(xué)生信息

語(yǔ)法

select * from (select * from 表名 where 限制) 別名 where 別名.列名 = 限制；

將第一步查詢語(yǔ)句當(dāng)作一個(gè)虛擬表（限制信息）查詢第二個(gè)表

代碼實(shí)現(xiàn)

select * from (select * from students where cid = 1) t where t.stu_gender='男';

運(yùn)行結(jié)果