inner join(内连接查询)
内连接:获取两个表中字段匹配关系的记录
主要语法:INNER JOIN 表名 ON 条件;
查询员工张飞所属部门的所在地址
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| dept |
| employee |
| salgrade |
+----------------+
3 rows in set (0.01 sec)
mysql> select * from dept;
+--------+--------+------+
| deptnu | dname | addr |
+--------+--------+------+
| 10 | 研发部 | 北京 |
| 20 | 工程部 | 上海 |
| 30 | 销售部 | 广州 |
| 40 | 财务部 | 深圳 |
+--------+--------+------+
4 rows in set (0.01 sec)
mysql> select * from employee;
+-------+--------+--------+------+------------+----------+--------+
| empno | ename | job | mgr | hiredate | sal | deptnu |
+-------+--------+--------+------+------------+----------+--------+
| 1004 | 猪八戒 | 经理 | 1009 | 2001-04-02 | 29750.00 | 20 |
| 1006 | 猴子 | 经理 | 1009 | 2011-05-01 | 28500.00 | 30 |
| 1007 | 张飞 | 经理 | 1009 | 2011-09-01 | 24500.00 | 10 |
| 1008 | 诸葛亮 | 分析师 | 1004 | 2017-04-19 | 30000.00 | 20 |
| 1013 | 林俊杰 | 分析师 | 1004 | 2011-12-03 | 30000.00 | 20 |
| 1002 | 牛魔王 | 销售员 | 1006 | 2018-02-20 | 16000.00 | 30 |
| 1003 | 程咬金 | 销售员 | 1006 | 2017-02-22 | 12500.00 | 30 |
| 1005 | 后裔 | 销售员 | 1006 | 2011-09-28 | 12500.00 | 30 |
| 1010 | 韩信 | 销售员 | 1006 | 2018-09-08 | 15000.00 | 30 |
| 1012 | 安琪拉 | 文员 | 1006 | 2011-12-03 | 9500.00 | 30 |
| 1014 | 甄姬 | 文员 | 1007 | 2019-01-23 | 7500.00 | 10 |
| 1011 | 妲己 | 文员 | 1008 | 2018-05-23 | 11000.00 | 20 |
| 1001 | 小乔 | 文员 | 1013 | 2018-12-17 | 8000.00 | 20 |
+-------+--------+--------+------+------------+----------+--------+
13 rows in set (0.03 sec)
mysql> select a.addr from dept a inner join employee b on a.deptnu=b.deptnu and b.ename='张飞';
+------+
| addr |
+------+
| 北京 |
+------+
1 row in set (0.04 sec)
注意:无法使用左右链接查询
原因:左右连接都会将想要查询的字段从表中全部取出展示出来。
mysql> select a.addr from dept a left join employee b on a.deptnu=b.deptnu and b.ename='张飞';
+------+
| addr |
+------+
| 北京 |
| 上海 |
| 广州 |
| 深圳 |
+------+
4 rows in set (0.04 sec)
联合查询
联合查询:就是把多个查询语句的查询结果结合在一起
主要语法1:… UNION … (去除重复) 主要语法2:… UNION ALL …(不去重复)
union查询的注意事项:
(1)两个select语句的查询结果的“字段数”必须一致;
(2)通常,也应该让两个查询语句的字段类型具有一致性;
(3)也可以联合更多的查询结果;
(4)用到order by排序时,需要加上limit(加上最大条数就行),需要对子句用括号括起来
查询文员和销售员的信息并将文员的工资升序排列销售员的工资降序排列。
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| dept |
| employee |
| salgrade |
+----------------+
3 rows in set (0.01 sec)
mysql> select * from dept;
+--------+--------+------+
| deptnu | dname | addr |
+--------+--------+------+
| 10 | 研发部 | 北京 |
| 20 | 工程部 | 上海 |
| 30 | 销售部 | 广州 |
| 40 | 财务部 | 深圳 |
+--------+--------+------+
4 rows in set (0.01 sec)
mysql> select * from employee;
+-------+--------+--------+------+------------+----------+--------+
| empno | ename | job | mgr | hiredate | sal | deptnu |
+-------+--------+--------+------+------------+----------+--------+
| 1004 | 猪八戒 | 经理 | 1009 | 2001-04-02 | 29750.00 | 20 |
| 1006 | 猴子 | 经理 | 1009 | 2011-05-01 | 28500.00 | 30 |
| 1007 | 张飞 | 经理 | 1009 | 2011-09-01 | 24500.00 | 10 |
| 1008 | 诸葛亮 | 分析师 | 1004 | 2017-04-19 | 30000.00 | 20 |
| 1013 | 林俊杰 | 分析师 | 1004 | 2011-12-03 | 30000.00 | 20 |
| 1002 | 牛魔王 | 销售员 | 1006 | 2018-02-20 | 16000.00 | 30 |
| 1003 | 程咬金 | 销售员 | 1006 | 2017-02-22 | 12500.00 | 30 |
| 1005 | 后裔 | 销售员 | 1006 | 2011-09-28 | 12500.00 | 30 |
| 1010 | 韩信 | 销售员 | 1006 | 2018-09-08 | 15000.00 | 30 |
| 1012 | 安琪拉 | 文员 | 1006 | 2011-12-03 | 9500.00 | 30 |
| 1014 | 甄姬 | 文员 | 1007 | 2019-01-23 | 7500.00 | 10 |
| 1011 | 妲己 | 文员 | 1008 | 2018-05-23 | 11000.00 | 20 |
| 1001 | 小乔 | 文员 | 1013 | 2018-12-17 | 8000.00 | 20 |
+-------+--------+--------+------+------------+----------+--------+
13 rows in set (0.03 sec)
mysql> (select * from employee a where a.job='文员' order by a.sal limit 9999) union (select * from employee b where b.job='销售员' order by b.sal desc limit 9999);
+-------+--------+--------+------+------------+----------+--------+
| empno | ename | job | mgr | hiredate | sal | deptnu |
+-------+--------+--------+------+------------+----------+--------+
| 1014 | 甄姬 | 文员 | 1007 | 2019-01-23 | 7500.00 | 10 |
| 1001 | 小乔 | 文员 | 1013 | 2018-12-17 | 8000.00 | 20 |
| 1012 | 安琪拉 | 文员 | 1006 | 2011-12-03 | 9500.00 | 30 |
| 1011 | 妲己 | 文员 | 1008 | 2018-05-23 | 11000.00 | 20 |
| 1002 | 牛魔王 | 销售员 | 1006 | 2018-02-20 | 16000.00 | 30 |
| 1010 | 韩信 | 销售员 | 1006 | 2018-09-08 | 15000.00 | 30 |
| 1003 | 程咬金 | 销售员 | 1006 | 2017-02-22 | 12500.00 | 30 |
| 1005 | 后裔 | 销售员 | 1006 | 2011-09-28 | 12500.00 | 30 |
+-------+--------+--------+------+------------+----------+--------+
8 rows in set (0.04 sec)
union 和 union all 的区别:
union 会去重
union all 不会去重
mysql> create table kk as (select * from employee a where a.job='文员' order by a.sal limit 9999) union (select * from employee b where b.job='销售员' order by b.sal desc limit 9999);
Query OK, 8 rows affected (0.03 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| dept |
| employee |
| kk |
| salgrade |
+----------------+
4 rows in set (0.04 sec)
mysql> select * from kk;
+-------+--------+--------+------+------------+----------+--------+
| empno | ename | job | mgr | hiredate | sal | deptnu |
+-------+--------+--------+------+------------+----------+--------+
| 1014 | 甄姬 | 文员 | 1007 | 2019-01-23 | 7500.00 | 10 |
| 1001 | 小乔 | 文员 | 1013 | 2018-12-17 | 8000.00 | 20 |
| 1012 | 安琪拉 | 文员 | 1006 | 2011-12-03 | 9500.00 | 30 |
| 1011 | 妲己 | 文员 | 1008 | 2018-05-23 | 11000.00 | 20 |
| 1002 | 牛魔王 | 销售员 | 1006 | 2018-02-20 | 16000.00 | 30 |
| 1010 | 韩信 | 销售员 | 1006 | 2018-09-08 | 15000.00 | 30 |
| 1003 | 程咬金 | 销售员 | 1006 | 2017-02-22 | 12500.00 | 30 |
| 1005 | 后裔 | 销售员 | 1006 | 2011-09-28 | 12500.00 | 30 |
+-------+--------+--------+------+------------+----------+--------+
8 rows in set (0.03 sec)
mysql> insert into kk (empno) values('1999');
Query OK, 1 row affected (0.00 sec)
mysql> select * from kk;
+-------+--------+--------+------+------------+----------+--------+
| empno | ename | job | mgr | hiredate | sal | deptnu |
+-------+--------+--------+------+------------+----------+--------+
| 1014 | 甄姬 | 文员 | 1007 | 2019-01-23 | 7500.00 | 10 |
| 1001 | 小乔 | 文员 | 1013 | 2018-12-17 | 8000.00 | 20 |
| 1012 | 安琪拉 | 文员 | 1006 | 2011-12-03 | 9500.00 | 30 |
| 1011 | 妲己 | 文员 | 1008 | 2018-05-23 | 11000.00 | 20 |
| 1002 | 牛魔王 | 销售员 | 1006 | 2018-02-20 | 16000.00 | 30 |
| 1010 | 韩信 | 销售员 | 1006 | 2018-09-08 | 15000.00 | 30 |
| 1003 | 程咬金 | 销售员 | 1006 | 2017-02-22 | 12500.00 | 30 |
| 1005 | 后裔 | 销售员 | 1006 | 2011-09-28 | 12500.00 | 30 |
| 1999 | NULL | NULL | NULL | NULL | NULL | NULL |
+-------+--------+--------+------+------------+----------+--------+
9 rows in set (0.03 sec)
mysql> select * from employee union select * from kk;
+-------+--------+--------+------+------------+----------+--------+
| empno | ename | job | mgr | hiredate | sal | deptnu |
+-------+--------+--------+------+------------+----------+--------+
| 1004 | 猪八戒 | 经理 | 1009 | 2001-04-02 | 29750.00 | 20 |
| 1006 | 猴子 | 经理 | 1009 | 2011-05-01 | 28500.00 | 30 |
| 1007 | 张飞 | 经理 | 1009 | 2011-09-01 | 24500.00 | 10 |
| 1008 | 诸葛亮 | 分析师 | 1004 | 2017-04-19 | 30000.00 | 20 |
| 1013 | 林俊杰 | 分析师 | 1004 | 2011-12-03 | 30000.00 | 20 |
| 1002 | 牛魔王 | 销售员 | 1006 | 2018-02-20 | 16000.00 | 30 |
| 1003 | 程咬金 | 销售员 | 1006 | 2017-02-22 | 12500.00 | 30 |
| 1005 | 后裔 | 销售员 | 1006 | 2011-09-28 | 12500.00 | 30 |
| 1010 | 韩信 | 销售员 | 1006 | 2018-09-08 | 15000.00 | 30 |
| 1012 | 安琪拉 | 文员 | 1006 | 2011-12-03 | 9500.00 | 30 |
| 1014 | 甄姬 | 文员 | 1007 | 2019-01-23 | 7500.00 | 10 |
| 1011 | 妲己 | 文员 | 1008 | 2018-05-23 | 11000.00 | 20 |
| 1001 | 小乔 | 文员 | 1013 | 2018-12-17 | 8000.00 | 20 |
| 1999 | NULL | NULL | NULL | NULL | NULL | NULL |
+-------+--------+--------+------+------------+----------+--------+
14 rows in set (0.04 sec)
mysql> select * from employee union all select * from kk;
+-------+--------+--------+------+------------+----------+--------+
| empno | ename | job | mgr | hiredate | sal | deptnu |
+-------+--------+--------+------+------------+----------+--------+
| 1004 | 猪八戒 | 经理 | 1009 | 2001-04-02 | 29750.00 | 20 |
| 1006 | 猴子 | 经理 | 1009 | 2011-05-01 | 28500.00 | 30 |
| 1007 | 张飞 | 经理 | 1009 | 2011-09-01 | 24500.00 | 10 |
| 1008 | 诸葛亮 | 分析师 | 1004 | 2017-04-19 | 30000.00 | 20 |
| 1013 | 林俊杰 | 分析师 | 1004 | 2011-12-03 | 30000.00 | 20 |
| 1002 | 牛魔王 | 销售员 | 1006 | 2018-02-20 | 16000.00 | 30 |
| 1003 | 程咬金 | 销售员 | 1006 | 2017-02-22 | 12500.00 | 30 |
| 1005 | 后裔 | 销售员 | 1006 | 2011-09-28 | 12500.00 | 30 |
| 1010 | 韩信 | 销售员 | 1006 | 2018-09-08 | 15000.00 | 30 |
| 1012 | 安琪拉 | 文员 | 1006 | 2011-12-03 | 9500.00 | 30 |
| 1014 | 甄姬 | 文员 | 1007 | 2019-01-23 | 7500.00 | 10 |
| 1011 | 妲己 | 文员 | 1008 | 2018-05-23 | 11000.00 | 20 |
| 1001 | 小乔 | 文员 | 1013 | 2018-12-17 | 8000.00 | 20 |
| 1014 | 甄姬 | 文员 | 1007 | 2019-01-23 | 7500.00 | 10 |
| 1001 | 小乔 | 文员 | 1013 | 2018-12-17 | 8000.00 | 20 |
| 1012 | 安琪拉 | 文员 | 1006 | 2011-12-03 | 9500.00 | 30 |
| 1011 | 妲己 | 文员 | 1008 | 2018-05-23 | 11000.00 | 20 |
| 1002 | 牛魔王 | 销售员 | 1006 | 2018-02-20 | 16000.00 | 30 |
| 1010 | 韩信 | 销售员 | 1006 | 2018-09-08 | 15000.00 | 30 |
| 1003 | 程咬金 | 销售员 | 1006 | 2017-02-22 | 12500.00 | 30 |
| 1005 | 后裔 | 销售员 | 1006 | 2011-09-28 | 12500.00 | 30 |
| 1999 | NULL | NULL | NULL | NULL | NULL | NULL |
+-------+--------+--------+------+------------+----------+--------+
22 rows in set (0.06 sec)