mysql 表连接 on_mysql 表连接

表连接

当需同时显示多个表中的字段时，就可以用表连接来实现这样的功能。表连接分为内连接和外连接。它们区别在于内连接仅选出两张表中互相匹配的记录。而外连接会显示其他不匹配的记录。

列如有一张表存着员工姓名以及入职日期还有工资和部门id ，另一张表存着部门id和部门名

首先我们先创建这2个表。

创建员工信息表：

创建员工信息表：

create table emp(

ename varchar(20),

hiredate date,

sal decimal(10,2),

deptno int(3));

#创建部门信息表：

create table dept(

deptno int(3),

deptname varchar(20));

#插入多条信息成如下：

mysql> select * from emp;

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

| ename | hiredate | sal | deptno |

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

| zzx | 2000-01-01 | 2000.00 | 1 |

| lisa | 2003-02-01 | 4000.00 | 2 |

| bjguan | 2004-04-02 | 5000.00 | 1 |

| bzshen | 2005-04-01 | 4000.00 | 3 |

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

mysql> select * from dept;

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

| deptno | deptname |

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

| 1 | tech |

| 2 | sale |

| 3 | hr |

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

3 rows in set (0.00 sec)

笛卡尔积

笛卡尔积是不加任何约束连接的多表查询，语法：select * from 表1，表2 ；显示的行数是表一查询出来的行数x表二的行数：

mysql> select * from emp,dept;

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

| ename | hiredate | sal | deptno | deptno | deptname |

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

| zzx | 2000-01-01 | 2000.00 | 1 | 1 | tech |

| zzx | 2000-01-01 | 2000.00 | 1 | 2 | sale |

| zzx | 2000-01-01 | 2000.00 | 1 | 3 | hr |

| lisa | 2003-02-01 | 4000.00 | 2 | 1 | tech |

| lisa | 2003-02-01 | 4000.00 | 2 | 2 | sale |

| lisa | 2003-02-01 | 4000.00 | 2 | 3 | hr |

| bjguan | 2004-04-02 | 5000.00 | 1 | 1 | tech |

| bjguan | 2004-04-02 | 5000.00 | 1 | 2 | sale |

| bjguan | 2004-04-02 | 5000.00 | 1 | 3 | hr |

| bzshen | 2005-04-01 | 4000.00 | 3 | 1 | tech |

| bzshen | 2005-04-01 | 4000.00 | 3 | 2 | sale |

| bzshen | 2005-04-01 | 4000.00 | 3 | 3 | hr |

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

12 rows in set (0.00 sec)

内连接

语法：

select d1.ename,d2.deptname from emp d1 inner join dept d2 on d1.deptno=d2.deptno;

例如查询出所有员工的名字和部门名称：

#d1 d2是分别给两张表起的别名语法：表名+空格+别名

select d1.ename,d2.deptname from emp d1,dept d2 where d1.deptno=d2.deptno;

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

| ename | deptname |

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

| zzx | tech |

| lisa | sale |

| bjguan | tech |

| bzshen | hr |

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

4 rows in set (0.00 sec)

外连接

外连接又分左连接和右连接，具体定义如下：

1、左连接：包含所有左边表的记录甚至是右边表没有和它匹配的记录

2、右连接：包含所有右边表的记录甚至是左边表没有和它匹配的记录

左连接

我先在员工信息表中插入一条这样的信息：

insert into emp values('dony','2005-2-5',2000,4) ;#注意4这个部门id在部门表中是没有的

mysql> select * from emp;

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

| ename | hiredate | sal | deptno |

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

| zzx | 2000-01-01 | 2000.00 | 1 |

| lisa | 2003-02-01 | 4000.00 | 2 |

| bjguan | 2004-04-02 | 5000.00 | 1 |

| bzshen | 2005-04-01 | 4000.00 | 3 |

| dony | 2005-02-05 | 2000.00 | 4 |

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

5 rows in set (0.00 sec)

mysql> select * from dept;

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

| deptno | deptname |

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

| 1 | tech |

| 2 | sale |

| 3 | hr |

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

3 rows in set (0.00 sec)

左连接查询

语法：select 字段名 from 表1 left join 表2 on 连接条件；

select d1.ename,d2.deptname from emp d1 left join dept d2 on d1.deptno=d2.deptno;

mysql> select d1.ename,d2.deptname from emp d1 left join dept d2 on d1.deptno=d2.deptno;

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

| ename | deptname |

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

| zzx | tech |

| bjguan | tech |

| lisa | sale |

| bzshen | hr |

| dony | NULL |

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

5 rows in set (0.00 sec)

右连接

语法：select 字段名 from 表1 right join 表2 on 连接条件

select d1.ename ,d2.deptname from emp d1 right join dept d2 on d1.deptno = d2.deptno;

mysql> select d1.ename ,d2.deptname from emp d1 right join dept d2 on d1.deptno = d2.deptno;

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

| ename | deptname |

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

| zzx | tech |

| lisa | sale |

| bjguan | tech |

| bzshen | hr |

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

4 rows in set (0.00 sec)

总结：以谁为连接就以谁为准优先显示全它的信息决定行数，另一张表只显示匹配上的信息，匹配不上的根据行数显示去掉或者不空；

子查询

某些情况下，当进行查询的时候，需要的条件是另一个select语句的结果，这个时候就要用到子查询，用于子查询的关键字主要包括in、not in、=、！=、exists、not exists等。

例如，从emp表中查询出所有部门在dept表中的所有记录：

语法:select 字段 from 表1 where 字段 in (select 语句)

select * from emp where deptno in (select deptno from dept);

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

| ename | hiredate | sal | deptno |

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

| zzx | 2000-01-01 | 2000.00 | 1 |

| lisa | 2003-02-01 | 4000.00 | 2 |

| bjguan | 2004-04-02 | 5000.00 | 1 |

| bzshen | 2005-04-01 | 4000.00 | 3 |

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

4 rows in set (0.00 sec)

如果子查询记录数唯一，还可以用=代替 in

select * from emp where deptno=(select deptno from dept where deptno=1);

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

| ename | hiredate | sal | deptno |

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

| zzx | 2000-01-01 | 2000.00 | 1 |

| bjguan | 2004-04-02 | 5000.00 | 1 |

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

2 rows in set (0.00 sec)