查询
连接查询
也叫多表查询，常用于查询表头来自于多张表
通过不同连接方式把多张表临时组成一张表 在临时的表里查找符合条件的数据
内连接查询
语法：
select 表头名 from 表名a inner join 表名b;? ? ? ? 迪卡尔积
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 5? ? ? ? ? ? ? ? *? ? ? ? 5? ? ? ? ? ? ? ? ? ? ? ?25
mysql> select * from departments;
......
......
8 rows in set (0.01 sec)
mysql> select * from employees;
......
......
133 rows in set (0.00 sec)
mysql> select * from employees inner join departments;
......
......
1064 rows in set (0.00 sec)
语法：
select 表头名 from 表名a inner join 表名b on 连接条件 where | group by | having | order by | limit;（符合连接条件的行 才会出现在新表）
等值连接
连接的表里有存储相同数据的表头才能使用相等判断做连接条件
mysql> select * from employees inner join departments on employees.dept_id = departments.dept_id;
......
......
133 rows in set (0.01 sec)
mysql> select * from employees inner join departments on employees.dept_id = departments.dept_id limit 1;
+-------------+--------+------------+------------+------------------+--------------+---------+---------+-----------+
| employee_id | name | hire_date | birth_date | email | phone_number | dept_id | dept_id | dept_name |
+-------------+--------+------------+------------+------------------+--------------+---------+---------+-----------+
| 1 | 梁伟 | 2018-06-21 | 1971-08-19 | liangwei@tedu.cn | 13591491431 | 1 | 1 | 人事部 |
+-------------+--------+------------+------------+------------------+--------------+---------+---------+-----------+
1 row in set (0.00 sec)非等值连接
不使用相等判断做连接条件
表里没有存储相同数据的表头时使用
外链接
左连接查询
左表表头记录全部显示
右表表头只显示与条件匹配的记录，右表比左表少的记录使用NULL匹配
select 表头名 from 表名a left join 表名b on 连接条件 where | group by | having | order by | limit;
环境准备：
# 向departments表里添加3个部门:小卖部 行政部 公关部
mysql> insert into departments(dept_name) values("小卖部"),("行政部"),("公关部");
# 查询部门信息
mysql> select * from departments;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 1 | 人事部 |
| 2 | 财务部 |
| 3 | 运维部 |
| 4 | 开发部 |
| 5 | 测试部 |
| 6 | 市场部 |
| 7 | 销售部 |
| 8 | 法务部 |
| 9 | 小卖部 |
| 10 | 行政部 |
| 11 | 公关部 |
+---------+-----------+
11 rows in set (0.00 sec)
# 输出没有员工的部门名
mysql> select departments.dept_id,departments.dept_name,employees.name from departments left join employees on employees.dept_id = departments.dept_id;
+---------+-----------+-----------+
| dept_id | dept_name | name |
+---------+-----------+-----------+
| 1 | 人事部 | 梁伟 |
| 1 | 人事部 | 郭岩 |
......
......
| 8 | 法务部 | 刘倩 |
| 8 | 法务部 | 杨金凤 |
| 9 | 小卖部 | NULL |
| 10 | 行政部 | NULL |
| 11 | 公关部 | NULL |
+---------+-----------+-----------+
136 rows in set (0.00 sec)
# 输出与筛选条件匹配的行
mysql> select departments.dept_id,departments.dept_name,employees.name from departments left join employees on employees.dept_id = departments.dept_id where name is null;
+---------+-----------+------+
| dept_id | dept_name | name |
+---------+-----------+------+
| 9 | 小卖部 | NULL |
| 10 | 行政部 | NULL |
| 11 | 公关部 | NULL |
+---------+-----------+------+
3 rows in set (0.00 sec)
# 仅显示departments表中dept_name表头
mysql> select departments.dept_name from departments left join employees on employees.dept_id = departments.dept_id where name is null; +-----------+
| dept_name |
+-----------+
| 小卖部 |
| 行政部 |
| 公关部 |
+-----------+
3 rows in set (0.00 sec)
#验证 插入数据
mysql> insert into employees(name,dept_id) values("bob",9);
mysql> select departments.dept_name from departments left join employees on employees.dept_id = departments.dept_id where name is null;
+-----------+
| dept_name |
+-----------+
| 行政部 |
| 公关部 |
+-----------+
2 rows in set (0.00 sec)右连接查询
右表表头记录全显示
左表表头只显示与条件匹配的记录，左表比右表少的记录使用NULL匹配
select 表头名 from 表名a right join 表名b on 连接条件 where | group by | having | order by | limit;
# 环境准备
# 向employees表中添加3个员工 只给name表头赋值
mysql> insert into employees(name) values("john"),("alice"),("jerry");
mysql> select * from employees where name in ("john","alice","jerry");
+-------------+-------+-----------+------------+-------+--------------+---------+
| employee_id | name | hire_date | birth_date | email | phone_number | dept_id |
+-------------+-------+-----------+------------+-------+--------------+---------+
| 135 | john | NULL | NULL | NULL | NULL | NULL |
| 136 | alice | NULL | NULL | NULL | NULL | NULL |
| 137 | jerry | NULL | NULL | NULL | NULL | NULL |
+-------------+-------+-----------+------------+-------+--------------+---------+
3 rows in set (0.00 sec)
# 显示没有部门的员工名
mysql> select name,dept_name from departments right join employees on departments.dept_id = employees.dept_id where dept_name is null;
+-------+-----------+
| name | dept_name |
+-------+-----------+
| john | NULL |
| alice | NULL |
| jerry | NULL |
+-------+-----------+
3 rows in set (0.00 sec)
mysql> select name from departments right join employees on departments.dept_id = employees.dept_id where dept_name is null;
+-------+
| name |
+-------+
| john |
| alice |
| jerry |
+-------+
3 rows in set (0.00 sec)
# 验证 修改john的部门
mysql> update employees set dept_id=11 where name="john";
mysql> select name from departments right join employees on departments.dept_id = employees.dept_id where dept_name is null;
+-------+
| name |
+-------+
| alice |
| jerry |
+-------+
2 rows in set (0.00 sec)
全连接查询
一起输出多个select命令的查询结果
多个select命令查询的表头个数要相同
(select 命令)?union (select 命令) ....? ? ? ? ? ? ? ? 去重
(select 命令) union all (select 命令) ....? ? ? ? ? ?不去重
嵌套查询
select查询命令里 包含 select查询命令
包含的select查询命令要放在 ( ) 里
包含的select查询命令可以出现的位置：
where 命令之后
把查询结果作为查询条件使用
select 表头名 from 库.表 where 表头名 筛选条件 (select查询命令);
# 查询运维部所有员工信息
mysql> select dept_id from departments where dept_name="运维部";
+---------+
| dept_id |
+---------+
| 3 |
+---------+
1 row in set (0.00 sec)
mysql> select * from employees where dept_id=3;
+-------------+-----------+------------+------------+--------------------+--------------+---------+
| employee_id | name | hire_date | birth_date | email | phone_number | dept_id |
+-------------+-----------+------------+------------+--------------------+--------------+---------+
| 14 | 廖娜 | 2012-05-20 | 1982-06-22 | liaona@tarena.com | 15827928192 | 3 |
| 15 | 窦红梅 | 2018-03-16 | 1971-09-09 | douhongmei@tedu.cn | 15004739483 | 3 |
| 16 | 聂想 | 2018-09-09 | 1999-06-05 | niexiang@tedu.cn | 15501892446 | 3 |
| 17 | 陈阳 | 2004-09-16 | 1991-04-10 | chenyang@tedu.cn | 15565662056 | 3 |
| 18 | 戴璐 | 2001-11-30 | 1975-05-16 | dailu@tedu.cn | 13465236095 | 3 |
| 19 | 陈斌 | 2019-07-04 | 2000-01-22 | chenbin@tarena.com | 13621656037 | 3 |
+-------------+-----------+------------+------------+--------------------+--------------+---------+
6 rows in set (0.00 sec)
mysql> select name,dept_id from employees where dept_id=(select dept_id from departments where dept_name="运维部");
+-----------+---------+
| name | dept_id |
+-----------+---------+
| 廖娜 | 3 |
| 窦红梅 | 3 |
| 聂想 | 3 |
| 陈阳 | 3 |
| 戴璐 | 3 |
| 陈斌 | 3 |
+-----------+---------+
6 rows in set (0.00 sec)
# 查询人事部2018年12月所有员工工资
# 查看人事部的部门id
mysql> select dept_id from departments where dept_name="人事部";
+---------+
| dept_id |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)
# 查找employees表里 人事部的员工id
mysql> select employee_id from employees where dept_id = 1; +-------------+
| employee_id |
+-------------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
+-------------+
8 rows in set (0.00 sec)
# 查询人事部2018年12月所有员工工资
mysql> select employee_id,basic,bonus from salary where year(date)=2018 and month(date)=12 and employee_id in (select employee_id from employees where dept_id = (select dept_id from departments where dept_name=" 人事部"));
+-------------+-------+-------+
| employee_id | basic | bonus |
+-------------+-------+-------+
| 1 | 17016 | 7000 |
| 2 | 20662 | 9000 |
| 3 | 9724 | 8000 |
| 4 | 17016 | 2000 |
| 5 | 17016 | 3000 |
| 6 | 17016 | 1000 |
| 7 | 23093 | 4000 |
| 8 | 23093 | 2000 |
+-------------+-------+-------+
8 rows in set (0.00 sec)
# 查询人事部和财务部员工信息
# 查看人事部和财务部的 部门id
mysql> select dept_id from departments where dept_name="人事部" or dept_name="财务部";
+---------+
| dept_id |
+---------+
| 1 |
| 2 |
+---------+
2 rows in set (0.00 sec)
# 查询人事部和财务部员工信息
mysql> select name,dept_id from employees where dept_id in (select dept_id from departments where dept_name="人事部" or dept_name="财务部")
-> ;
+-----------+---------+
| name | dept_id |
+-----------+---------+
| 梁伟 | 1 |
| 郭岩 | 1 |
| 李玉英 | 1 |
| 张健 | 1 |
| 郑静 | 1 |
| 牛建军 | 1 |
| 刘斌 | 1 |
| 汪云 | 1 |
| 张建平 | 2 |
| 郭娟 | 2 |
| 郭兰英 | 2 |
| 王英 | 2 |
| 王楠 | 2 |
+-----------+---------+
13 rows in set (0.00 sec)
# 查询2018年12月所有比100号员工基本工资高的工资信息
# 把100号员工的基本工资查出来
mysql> select basic from salary where year(date)=2018 and month(date)=12 and employee_id=100; +-------+
| basic |
+-------+
| 14585 |
+-------+
1 row in set (0.00 sec)
# 查看比100号员工工资高的工资信息
mysql> select basic,employee_id from salary where year(date)=2018 and month(date)=12 and basic > (select basic from salary where year(date)=2018 and month(date)=12 and employee_id=100);
......
......
65 rows in set (0.00 sec)
having?命令之后
# 查询部门员工总人数比开发部总人数少 的 部门名称和人数
# 统计开发部员工总人数
mysql> select count(name) from employees where dept_id=(select dept_id from departments where dept_name=" 开发部");
+-------------+
| count(name) |
+-------------+
| 55 |
+-------------+
1 row in set (0.00 sec)
# 统计每个部门总人数
mysql> select dept_id,count(name) from employees group by dept_id; +---------+-------------+
| dept_id | count(name) |
+---------+-------------+
| NULL | 2 |
| 1 | 8 |
| 2 | 5 |
| 3 | 6 |
| 4 | 55 |
| 5 | 12 |
| 6 | 9 |
| 7 | 35 |
| 8 | 3 |
| 9 | 1 |
| 11 | 1 |
+---------+-------------+
11 rows in set (0.00 sec)
# 输出总人数比开发部总人数少的部门名及总人数
mysql> select dept_id,count(name) as total from employees group by dept_id having total < (select count(name) from employees where dept_id=(select dept_id from departments where dept_name='开发部'));
+---------+-------+
| dept_id | total |
+---------+-------+
| NULL | 2 |
| 1 | 8 |
| 2 | 5 |
| 3 | 6 |
| 5 | 12 |
| 6 | 9 |
| 7 | 35 |
| 8 | 3 |
| 9 | 1 |
| 11 | 1 |
+---------+-------+
10 rows in set (0.00 sec)from?命令之后
把查询结果当作表使用
select?命令之后
把查询结果当做表头使用
select 表头名,(select查询命令) from 库.表 where 条件;