mysql多表查询 3.关联查询

使用的数据库为mysql多表查询 2.建立多表数据库案例中的数据库。

1.1内连接查询

使用内连接查询，只有满足条件的结果才会显示。

1）内连接(第一种语法)

select i.name,b.class,s.python from scores as s,infor as i,branch as b where s.nameid=i.id and s.classid=b.id;

2）内连接(第二种语法)

select i.name,b.class,s.python from scores as s

inner join infor as i on s.nameid=i.id

inner join branch as b on s.classid=b.id;

1.2外连接查询

为了便于测试观看，给branch表插入一行其他表没有的数据：

insert into branch values(1111,"天山派");

1）左连接查询left

使用左边表的数据去匹配右边表的数据，如果符合连接条件的结果则显示，如果不符合连接条件则显示null。（左表的数据一定会完成显示）。

select*from scores

left join branch on scores.classid=branch.id;

2）右连接查询right

使用右边表的数据去匹配左边表的数据，如果符合连接条件的结果则显示，如果不符合连接条件则显示null。（右表的数据一定会完成显示）。

select*from scores

right join branch on scores.classid=branch.id;

举例

需求1：查询每一个部门的人数，以及有哪些人？

select class as “部门”, count(*) as “人数” from scores

inner join branch on scores.classid=branch.id

group by class;

需求2：查询每一个人的总成绩，并且降序排列。

select name,(python+java+c) as “总成绩” from scores

inner join infor on scores.nameid=infor.id

group by infor.id ;

1.3自关联

自关联就是表中的某一项关联了这个表中的另外一项，它们的业务逻辑是不一样的，如下面城市信息中的proid引用了省信息中的id。

1）创建表address

create table address(

id int primary key not null,

title varchar(15),

proid int,

constraint address_fk foreign key(proid) references address(id) on update cascade on delete cascade

);

2）插入数据省和城市

insert into address values(1011,”陕西省”,null);

insert into address values(1022,”浙江省”,null);

insert into address values(1,”西安市”,1011);

insert into address values(2,”安康市”,1011);

insert into address values(3,”延安市”,1011);

insert into address values(4,”榆林市”,1011);

insert into address values(5,”杭州市”,1022);

insert into address values(6,”慈溪市”,1022);

insert into address values(7,”诸暨市”,1022);

insert into address values(8,”宁波市”,1022);

3）查询

（1）查询有多少个省

select count(*) as “省份个数” from address where proid is null;

（2）查询“陕西省”有哪些市

select city.* from address as city

inner join address as province on city.proid=province.id

where province.title=”陕西省”;

（3）查询所有的市和对应的省

select city.id,city.title as “市”,prov.title as “省” from address as city

inner join address as prov on city.proid=prov.id;

1.4视图

视图是用来对查询进行封装，可以方便的多次使用，也更加便于维护。

（1）定义视图

最好在视图名前加v容易区分。

create view v_scores as

select name, class, python,java,c from scores

inner join infor on scores.nameid=infor.id

inner join branch on scores.classid=branch.id;

（2）查询视图

select*from v_scores;

定义与查询如下图：