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 num,name,employee.d_id,age,sex,d_name,functione from employee left join department on employee.d_id = department.d_id;

select num,name,employee.d_id,age,sex,d_name,functione from employee right join department on 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;

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;