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)
posted @ 2018-10-25 19:43  Kingfan  阅读(203)  评论(0编辑  收藏  举报