mysql——多表——外连接查询——左连接、右连接、复合条件查询-蒲公英云

create table employee ( num int(50),
                        d_id int(50),
                        name varchar(50),
                        age int(50),
                        sex varchar(50),
                        homeadd varchar(50)
                       );
insert into employee values(1,1001,'zhangsan',26,'nan','beijing');
insert into employee values(2,1001,'lisi',24,'nv','hunan');
insert into employee values(3,1002,'wangwu',25,'nan','jiangsu');
insert into employee values(4,1004,'aric',15,'nan','yingguo');
select * from employee;
create table department ( d_id int(50),
                          d_name varchar(50),
                          functione varchar(50),
                          address varchar(50)
                        );
insert into department values(1001,'keyanbu','yanfachanpin','3lou5hao');
insert into department values(1002,'shengchanbu','shengchanchanp','5louyiceng');
insert into department values(1003,'xiaoshoubu','cehuaxiaoshou','1louxiaoshoudating');
select * from department;
select num,name,employee.d_id,age,sex,d_name,functione from employee,department where employee.d_id = department.d_id;
内连接查询：可以查询两个或者两个以上的表，当两个表中存在表示相同意义的字段时，可以通过该字段来连接这两个表；
             当该字段的值相等时，就查询出该记录。
======================================================================================================================
外连接查询：可以查询两个或者两个以上的表，外连接查询也需要通过指定字段来进行连。
            当该字段取值相等时，可以查询出该记。
            而且该字段取值不相等的记录也可以查询出来。
外连接包括：左连接、右连接
语法：
        select 属性列表 from 表名1 left | right join 表名2 on 表名1.属性名1 = 表名2.属性名2；
属性列表表示要查询的字段的名称，这些字段可以来自不同的表；
Left表示左连接查询；
rigth表示右连接查询；
on后面接的是连接的条件；
1、左连接查询
进行左连接查询时，可以查询出表名1所指的表中的所有记录。而表名2所指的表中，只能查询出匹配的记录
select * from employee;
select * from department;
select num,name,employee.d_id,age,sex,d_name,functione from employee left join department on employee.d_id = department.d_id;
2、右连接查询
进行右连接查询时，可以查询出表名2所指的表中的所有记录。而表名1所指的表中，只能查询出匹配的记录
select * from employee;
select * from department;
select num,name,employee.d_id,age,sex,d_name,functione from employee right join department on employee.d_id = department.d_id;
复合条件查询
在连接查询时，通过增加其他的限制条件，可以使查询结果更加准确
select * from employee;
select * from department;
select num,name,employee.d_id,age,sex,d_name,functione from employee,department where employee.d_id = department.d_id;
select num,name,employee.d_id,age,sex,d_name,functione from employee,department where employee.d_id = department.d_id and age > 24;
select num,name,employee.d_id,age,sex,d_name,functione from employee,department where employee.d_id = department.d_id order by age asc;
select 语句先按照内连接的方式从employee和department表中查询出数据。然后查询结果按照age字段从小到大的顺序进行排序。
=================================================================================================================================

前期准备表语句：

create table employee ( num int(50),
                        d_id int(50),
                        name varchar(50),
                        age int(50),
                        sex varchar(50),
                        homeadd varchar(50)
                       );
insert into employee values(1,1001,'zhangsan',26,'nan','beijing');
insert into employee values(2,1001,'lisi',24,'nv','hunan');
insert into employee values(3,1002,'wangwu',25,'nan','jiangsu');
insert into employee values(4,1004,'aric',15,'nan','yingguo');
select * from employee;
create table department ( d_id int(50),
                          d_name varchar(50),
                          functione varchar(50),
                          address varchar(50)
                        );
insert into department values(1001,'keyanbu','yanfachanpin','3lou5hao');
insert into department values(1002,'shengchanbu','shengchanchanp','5louyiceng');
insert into department values(1003,'xiaoshoubu','cehuaxiaoshou','1louxiaoshoudating');
select * from department;

select * from employee;

select * from department;

左连接查询：

语法：

select 属性列表 from 表名1 left | right join 表名2 on 表名1.属性名1 = 表名2.属性名2；

属性列表表示要查询的字段的名称，这些字段可以来自不同的表；

Left表示左连接查询；

rigth表示右连接查询；

on后面接的是连接的条件；

1、左连接查询

进行左连接查询时，可以查询出表名1所指的表中的所有记录。而表名2所指的表中，只能查询出匹配的记录

select num,name,employee.d_id,age,sex,d_name,functione from employee left join department on employee.d_id = department.d_id;

2、右连接查询

进行右连接查询时，可以查询出表名2所指的表中的所有记录。而表名1所指的表中，只能查询出匹配的记录

select num,name,employee.d_id,age,sex,d_name,functione from employee right join department on employee.d_id = department.d_id;

3、复合条件查询

在连接查询时，通过增加其他的限制条件，可以使查询结果更加准确

select num,name,employee.d_id,age,sex,d_name,functione from employee,department where employee.d_id = department.d_id;
select num,name,employee.d_id,age,sex,d_name,functione from employee,department where employee.d_id = department.d_id and age > 24;
select num,name,employee.d_id,age,sex,d_name,functione from employee,department where employee.d_id = department.d_id order by age asc;