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;

mysql——多表——外连接查询——左连接、右连接、复合条件查询

 select * from department;

mysql——多表——外连接查询——左连接、右连接、复合条件查询

左连接查询:

语法:

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;

mysql——多表——外连接查询——左连接、右连接、复合条件查询

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;

mysql——多表——外连接查询——左连接、右连接、复合条件查询

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;
上一篇:@JsonView注解指定返回的model类中显示的字段


下一篇:eclipse 搭建Swt 环境