第十三單元 多表聯(lián)查和子查詢

13.1 多表聯(lián)查

13.1.1 一對一

一張表的一條記錄一定只能與另外一張表的一條記錄進(jìn)行對應(yīng)知染，反之亦然肋僧。

有時候，為了業(yè)務(wù)控淡，或者避免一張表中數(shù)據(jù)量過大嫌吠，過復(fù)雜，在開發(fā)中會進(jìn)行一對一方式來設(shè)計表掺炭。

13.1.2 一對多（1方建主表(id為主鍵字段), 多方建外鍵字段）

一個實體的某個數(shù)據(jù)與另外一個實體的多個數(shù)據(jù)有關(guān)聯(lián)關(guān)系居兆， 一對多的關(guān)系在設(shè)計的時候，需要設(shè)計表

的外鍵竹伸。

1. 班級表和學(xué)生表設(shè)計

部門表和員工表設(shè)計

2. 創(chuàng)建數(shù)據(jù)庫表

constraint 約束

foreign key就是表與表之間的某種約定的關(guān)系，由于這種關(guān)系的存在簇宽，能夠讓表與表之間的數(shù)據(jù)勋篓，

更加的完整，關(guān)連性更強(qiáng)魏割。

foreign key語句的式例：FOREIGN KEY(Sno) REFERENCES Student(Sno)

注意：表的外鍵必須是另一張表的主鍵

//創(chuàng)建班級表

create table class(id int primary key auto_increment,name varchar(20));

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

create table student(id int primary key auto_increment,name varchar(20),sex

varchar(20),class_id int,constraint foreign key(class_id) references

class(id));

//插入班級數(shù)據(jù)

insert into class values(1,'ceshiban');

insert into class values(2,'kaifa');

//插入學(xué)生數(shù)據(jù)

insert into student values(1,'zhangsan','nan',1);

insert into student values(2,'lisi','nan',2);

insert into student values(3,'jingjing','nan',2);

//聯(lián)查

select * from student where class_id=(select id from class where id=2);

補(bǔ)一個外鍵的概念（默認(rèn)是約束): 刪除主鍵信息時譬嚣，當(dāng)該主鍵字段值在外鍵表中存在時，該記錄是不能

刪除的钞它。---要把外鍵表是的相關(guān)信息刪除之后拜银，才能刪除殊鞭。

13.3.3 多對多（ 3個表= 2個實體表 + 1個關(guān)系表 )

一個實體的數(shù)據(jù)對應(yīng)另外一個實體的多個數(shù)據(jù)，另外實體的數(shù)據(jù)也同樣對應(yīng)當(dāng)前實體的多個數(shù)據(jù)尼桶。

一個學(xué)生可以有多個老師操灿，一個老師可以教多個學(xué)生

解決方案：創(chuàng)建一個中間表，專門用來維護(hù)多表之間的對應(yīng)關(guān)系泵督，通常是能夠唯一標(biāo)識出數(shù)據(jù)的字段

（主鍵）

create table teacher(id int primary key,name varchar(100));

create table student (id int primary key,name varchar(100));

create table teacher_student(teacher_id int,student_id int,constraint foreign

key(teacher_id) references teacher(id),constraint foreign key(student_id)

references student(id));

insert into teacher values(1,'梁老師');

insert into teacher values(2,'李老師');

insert into student values(1,”張三”);

insert into student values(2,”李四”);

insert into teacher_student values(1,1);

insert into teacher_student values(1,2);

insert into teacher_student values(2,1);

insert into teacher_student values(2,2);

//查詢李老師所教的學(xué)生

select id from teacher where name=’李老師’

select student_id from teacher_student where teacher_id=id

select * from student where id in(select student_id from teacher_student where

teacher_id =(select id from teacher where name='李老師'));

//查詢張三的所有老師

select * from teacher where id in(select teacher_id from teacher_student where

student_id=(select id from student where name='張三'));

13.2 連表查詢

分類：內(nèi)連接趾盐、外連接、交叉連接

初始定義表結(jié)構(gòu)

create table customer(id int primary key auto_increment,name varchar(20),city

varchar(20));

create table orders(id int primary key auto_increment,good_name varchar(20),price

float(8,2),customer_id int);

insert into customer (name,city) values('李老師','東北');

insert into customer (name,city) values('崔老師','山西');

insert into customer (name,city) values('張老師','內(nèi)蒙');

insert into customer (name,city) values('閆老師','天津');

insert into orders(good_name,price,customer_id) values('電腦',59,1);

insert into orders(good_name,price,customer_id) values('筆記本',88,2);

insert into orders(good_name,price,customer_id) values('吹風(fēng)機(jī)',99,1);

insert into orders(good_name,price,customer_id) values('香水',300,3);

insert into orders(good_name,price,customer_id) values('牛奶',100,6);

13.2.1 交叉查詢

交叉查詢小腊，又叫笛卡爾積查詢救鲤，會將左表和右表的信息，做一個乘積將所有信息查詢出來秩冈，會產(chǎn)生臨時

表本缠，比較占用內(nèi)存，生成的記錄數(shù)=表1 X表2

select * from customer,orders;

select * from customer cross join orders;

13.2.2 內(nèi)連接查詢

內(nèi)連接入问，inner join on 查詢兩張表丹锹，設(shè)定條件，將兩張表中對應(yīng)的數(shù)據(jù)查詢出來

不會產(chǎn)生笛卡爾積队他，不會產(chǎn)生臨時表卷仑，性能高

select * from customer c inner join orders o on c.id=o.customer_id;

select * from customer,orders where customer.id=orders.customer_id;

select * from customer c,orders o where c.id=o.customer_id;

13.2.3 左外連接

左外連接 left join on 設(shè)定條件，將兩張表對應(yīng)的數(shù)據(jù)查詢出來麸折，同時將左表自己沒有關(guān)聯(lián)的數(shù)據(jù)也查

詢出來

注意：join前面是左锡凝，后面是右

select * from customer c left join orders o on c.id=o.customer_id;

13.2.4 右外連接

右外連接 right join on 設(shè)定條件，將兩張表對應(yīng)的數(shù)據(jù)查詢出來垢啼，同時將右表自己沒有關(guān)聯(lián)的所有數(shù)據(jù)

查詢出來

select * from customer c right join orders o on c.id=o.customer_id;

練習(xí) 聯(lián)合查詢

select * from customer left join orders on customer.id=orders.customer_id

having price>20;

13.3 MySQL圖形化工具navicat

13.3.1 安裝介紹

MySQL常見的圖形化工具

13.3.2. Navicat工具使用

1. 鏈接窜锯，mysql，輸入用戶名芭析，密碼

2.新建庫锚扎，鼠標(biāo)點擊右鍵

3.新建表