mysql 外连接条件查询_MYSQL内连接，外连接，复合条件查询

内连接查询

create table department(d_id int(10) not null unique primary

key auto_increment,d_name varchar(10),

function varchar(20),address varchar(30));

insert into department

values(1001,'科研部','研发产品','3号楼5层');

insert into department

values(1002,'生产部','生产产品','5号楼1层');

insert into department

values(1003,'销售部','策划销售','1号楼销售大厅');

mysql> select * from employee;

+-----+------+------+-----+-----+--------------+

| num | d_id | name | age | sex | homeaddr  |

+-----+------+------+-----+-----+--------------+

|  1 | 1001 | 张三 |  26 | 男

| 北京市海淀区 |

|  2 | 1001 | 李四 |  24 | 女

| 北京市昌平区 |

|  3 | 1002 | 王五 |  25 | 男

| 湖南省长沙市 |

|  4 | 1004 | Aric |  15 |

男  | England  |

+-----+------+------+-----+-----+--------------+

mysql> select * from department;

+------+--------+----------+---------------+

| d_id | d_name | function | address  |

+------+--------+----------+---------------+

| 1001 | 科研部 | 研发产品 | 3号楼5层  |

| 1002 | 生产部 | 生产产品 | 5号楼1层  |

| 1003 | 销售部 | 策划销售 | 1号楼销售大厅 |

+------+--------+----------+---------------+

(1)select num,name,employee.d_id,age,sex,d_name,function from

employee,department where

employee.d_id=department.d_id;

+-----+------+------+-----+-----+--------+----------+

| num | d_id | name | age | sex | d_name | function |

+-----+------+------+-----+-----+--------+----------+

|  1 | 1001 | 张三 |  26 | 男

| 科研部 | 研发产品 |

|  2 | 1001 | 李四 |  24 | 女

| 科研部 | 研发产品 |

|  3 | 1002 | 王五 |  25 | 男

| 生产部 | 生产产品 |

+-----+------+------+-----+-----+--------+----------+

外连接查询

(1)左连接查询

select num,employee.d_id,name,age,sex,d_name,function from

employee left join department on

employee.d_id=department.d_id;

+-----+------+------+-----+-----+--------+----------+

| num | d_id | name | age | sex | d_name | function |

+-----+------+------+-----+-----+--------+----------+

|  1 | 1001 | 张三 |  26 | 男

| 科研部 | 研发产品 |

|  2 | 1001 | 李四 |  24 | 女

| 科研部 | 研发产品 |

|  3 | 1002 | 王五 |  25 | 男

| 生产部 | 生产产品 |

|  4 | 1004 | Aric |  15 |

男  | NULL  | NULL

|

+-----+------+------+-----+-----+--------+----------+

(2)右连接

mysql> select

num,employee.d_id,name,age,sex,d_name,function from employee right

join department on employee.d_id=department.d_id;

+------+------+------+------+------+--------

+----------+

| num  | d_id | name | age

| sex  | d_name |

function

|

+------+------+------+------+------+--------

+----------+

|  1 | 1001 | 张三 |

26 | 男  | 科研部 |

研发产品

|

|  2 | 1001 | 李四 |

24 | 女  | 科研部 |

研发产品

|

|  3 | 1002 | 王五 |

25 | 男  | 生产部 |

生产产品

|

| NULL | NULL | NULL | NULL | NULL | 销售部 |

策划销售

|

+------+------+------+------+------+--------

+----------+

复合条件查询

(1)

select num,employee.d_id,name,age,sex,d_name,function from

employee ,department where

employee.d_id=department.d_id and age>24;

+-----+------+------+-----+-----+--------+----------+

| num | d_id | name | age | sex | d_name | function |

+-----+------+------+-----+-----+--------+----------+

|  1 | 1001 | 张三 |  26 | 男

| 科研部 | 研发产品 |

|  3 | 1002 | 王五 |  25 | 男

| 生产部 | 生产产品 |

+-----+------+------+-----+-----+--------+----------+

(2)select num,employee.d_id,name,sex,age,d_name,function from

employee,department where

employee.d_id=department.d_id order by age asc;

+-----+------+------+-----+-----+--------+----------+

| num | d_id | name | sex | age | d_name | function |

+-----+------+------+-----+-----+--------+----------+

|  2 | 1001 | 李四 | 女  |

24 | 科研部 | 研发产品 |

|  3 | 1002 | 王五 | 男  |

25 | 生产部 | 生产产品 |

|  1 | 1001 | 张三 | 男  |

26 | 科研部 | 研发产品 |

+-----+------+------+-----+-----+--------+----------+