多表查询:
#建表
mysql>create table 部门(->id int,-> name varchar(20)->);
Query OK, 0 rows affected (0.48sec)
mysql>create table 雇员(->id int primary key auto_increment,-> name varchar(20),-> sex enum('male','female') not null default 'male',->age int,->dep_id int->);
Query OK, 0 rows affected (0.24sec)#插入数据
mysql>insert into 部门 values-> (200,'技术'),-> (201,'人力资源'),-> (202,'销售'),-> (203,'运营');
Query OK,4 rows affected (0.11sec)
Records:4Duplicates: 0 Warnings: 0
mysql>insert into 雇员(name,sex,age,dep_id) values-> ('张明','male',18,200),-> ('李国加','female',48,201),-> ('彭达五','male',38,201),-> ('找刘鹏','female',28,202),-> ('张国民','male',18,200),-> ('两架于','female',18,204);
Query OK,6 rows affected (0.09sec)
Records:6Duplicates: 0 Warnings: 0#查看表结构
mysql>desc 部门;+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.19sec)
mysql>desc 雇员;+--------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int(11) | YES | | NULL | |
| dep_id | int(11) | YES | | NULL | |
+--------+-----------------------+------+-----+---------+----------------+
5 rows in set (0.04sec)#查看整张表
mysql> select * from部门;+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
+------+--------------+
4 rows in set (0.03sec)
mysql> select * from雇员;+----+-----------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+-----------+--------+------+--------+
| 1 | 张明 | male | 18 | 200 |
| 2 | 李国加 | female | 48 | 201 |
| 3 | 彭达五 | male | 38 | 201 |
| 4 | 找刘鹏 | female | 28 | 202 |
| 5 | 张国民 | male | 18 | 200 |
| 6 | 两架于 | female | 18 | 204 |
+----+-----------+--------+------+--------+
6 rows in set (0.01sec)
部门表与雇员表
数据准备
多表连接查询:
外链接语法:
select 字段列表from 表1 INNER|LEFT|RIGHT JOIN 表2
ON 表1.字段= 表2.字段;
1.交叉连接:不适用任何匹配条件
语法select * from 表名1,表名2;
mysql> select * from部门,雇员;+------+--------------+----+-----------+--------+------+--------+
| id | name | id | name | sex | age | dep_id |
+------+--------------+----+-----------+--------+------+--------+
| 200 | 技术 | 1 | 张明 | male | 18 | 200 |
| 201 | 人力资源 | 1 | 张明 | male | 18 | 200 |
| 202 | 销售 | 1 | 张明 | male | 18 | 200 |
| 203 | 运营 | 1 | 张明 | male | 18 | 200 |
| 200 | 技术 | 2 | 李国加 | female | 48 | 201 |
| 201 | 人力资源 | 2 | 李国加 | female | 48 | 201 |
| 202 | 销售 | 2 | 李国加 | female | 48 | 201 |
| 203 | 运营 | 2 | 李国加 | female | 48 | 201 |
| 200 | 技术 | 3 | 彭达五 | male | 38 | 201 |
| 201 | 人力资源 | 3 | 彭达五 | male | 38 | 201 |
| 202 | 销售 | 3 | 彭达五 | male | 38 | 201 |
| 203 | 运营 | 3 | 彭达五 | male | 38 | 201 |
| 200 | 技术 | 4 | 找刘鹏 | female | 28 | 202 |
| 201 | 人力资源 | 4 | 找刘鹏 | female | 28 | 202 |
| 202 | 销售 | 4 | 找刘鹏 | female | 28 | 202 |
| 203 | 运营 | 4 | 找刘鹏 | female | 28 | 202 |
| 200 | 技术 | 5 | 张国民 | male | 18 | 200 |
| 201 | 人力资源 | 5 | 张国民 | male | 18 | 200 |
| 202 | 销售 | 5 | 张国民 | male | 18 | 200 |
| 203 | 运营 | 5 | 张国民 | male | 18 | 200 |
| 200 | 技术 | 6 | 两架于 | female | 18 | 204 |
| 201 | 人力资源 | 6 | 两架于 | female | 18 | 204 |
| 202 | 销售 | 6 | 两架于 | female | 18 | 204 |
| 203 | 运营 | 6 | 两架于 | female | 18 | 204 |
+------+--------------+----+-----------+--------+------+--------+
24 rows in set (0.00 sec)
交叉连接
表已表之间的连接分为两种:内连,外连
内连接:只连接匹配的行
select * from 表1,表2 where 条件
select * from 表1 inner join 表2 on 条件
select * from 部门 inner join 雇员 on 部门 .id = 雇员 .dep_id;
mysql> select * from 部门 inner join 雇员 on 部门.id =雇员.dep_id;+------+--------------+----+-----------+--------+------+--------+
| id | name | id | name | sex | age | dep_id |
+------+--------------+----+-----------+--------+------+--------+
| 200 | 技术 | 1 | 张明 | male | 18 | 200 |
| 201 | 人力资源 | 2 | 李国加 | female | 48 | 201 |
| 201 | 人力资源 | 3 | 彭达五 | male | 38 | 201 |
| 202 | 销售 | 4 | 找刘鹏 | female | 28 | 202 |
| 200 | 技术 | 5 | 张国民 | male | 18 | 200 |
+------+--------------+----+-----------+--------+------+--------+
5 rows in set (0.11 sec)
select * from 部门 as t1 inner join 雇员 as t2 on t1.id = t2.dep_id;
mysql> select * from 部门 as t1 inner join 雇员 as t2 on t1.id =t2.dep_id;+------+--------------+----+-----------+--------+------+--------+
| id | name | id | name | sex | age | dep_id |
+------+--------------+----+-----------+--------+------+--------+
| 200 | 技术 | 1 | 张明 | male | 18 | 200 |
| 201 | 人力资源 | 2 | 李国加 | female | 48 | 201 |
| 201 | 人力资源 | 3 | 彭达五 | male | 38 | 201 |
| 202 | 销售 | 4 | 找刘鹏 | female | 28 | 202 |
| 200 | 技术 | 5 | 张国民 | male | 18 | 200 |
+------+--------------+----+-----------+--------+------+--------+
5 rows in set (0.00 sec)
需求:找出部门表跟雇员表共有的部分
mysql> select 雇员.id,雇员.name,雇员.age,雇员.sex,部门.name from雇员-> inner join 部门 on 雇员.dep_id=部门.id;+----+-----------+------+--------+--------------+
| id | name | age | sex | name |
+----+-----------+------+--------+--------------+
| 1 | 张明 | 18 | male | 技术 |
| 2 | 李国加 | 48 | female | 人力资源 |
| 3 | 彭达五 | 38 | male | 人力资源 |
| 4 | 找刘鹏 | 28 | female | 销售 |
| 5 | 张国民 | 18 | male | 技术 |
+----+-----------+------+--------+--------------+
5 rows in set (0.00sec)#部门表中没有204所有雇员表中关于204的员工信息也不会显示出来#上面sql语句等于:
mysql> select 雇员.id,雇员.name,雇员.age,雇员.sex,部门.name from 雇员,部门where 雇员.dep_id=部门.id;
外连接:左右全连接
左外连接:优先显示左表全部记录 left join ... on ...
select * from 表1 left join 表2 on 条件
select * from department as t1 left join employee as t2 on t1.id = t2.dep_id;
#以左表为准,即找出所有员工信息,当然包括没有部门的员工#本质就是:在内连接的基础上增加左边有右边没有的结果
mysql>select 雇员.id,雇员.name,部门.name as depart_name-> from 雇员 left join 部门 on 雇员.dep_id=部门.id;+----+-----------+--------------+
| id | name | depart_name |
+----+-----------+--------------+
| 1 | 张明 | 技术 |
| 5 | 张国民 | 技术 |
| 2 | 李国加 | 人力资源 |
| 3 | 彭达五 | 人力资源 |
| 4 | 找刘鹏 | 销售 |
| 6 | 两架于 | NULL |
+----+-----------+--------------+
6 rows in set (0.00 sec)
外连接之左连接
右外连接:优先显示右表全部记录 right join ... on ...
select * from 表1 right join 表2 on 条件
select * from department as t1 right join employee as t2 on t1.id = t2.dep_id
#以右表为准,即找出所有部门信息,包括没有员工的部门#本质就是:在内连接的基础上增加右边有左边没有的结果
mysql>select 雇员.id,雇员.name,部门.name as depart_name-> from 雇员 right join 部门 on 雇员.dep_id=部门.id;+------+-----------+--------------+
| id | name | depart_name |
+------+-----------+--------------+
| 1 | 张明 | 技术 |
| 2 | 李国加 | 人力资源 |
| 3 | 彭达五 | 人力资源 |
| 4 | 找刘鹏 | 销售 |
| 5 | 张国民 | 技术 |
| NULL | NULL | 运营 |
+------+-----------+--------------+
6 rows in set (0.00 sec)
外连接之右连接
全外连接:显示左右两个表全部记录full join
select * from department as t1 left join employee as t2 on t1.id = t2.dep_id
union
select * from department as t1 right join employee as t2 on t1.id = t2.dep_id;
#全外连接:在内连接的基础上增加左边有右边没有的和右边有左边没有的结果#注意:mysql不支持全外连接 full JOIN#强调:mysql可以使用此种方式间接实现全外连接
mysql> select * from 雇员 left join 部门 on 雇员.dep_id =部门.id->union-> select * from 雇员 right join 部门 on 雇员.dep_id =部门.id;+------+-----------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+------+-----------+--------+------+--------+------+--------------+
| 1 | 张明 | male | 18 | 200 | 200 | 技术 |
| 5 | 张国民 | male | 18 | 200 | 200 | 技术 |
| 2 | 李国加 | female | 48 | 201 | 201 | 人力资源 |
| 3 | 彭达五 | male | 38 | 201 | 201 | 人力资源 |
| 4 | 找刘鹏 | female | 28 | 202 | 202 | 销售 |
| 6 | 两架于 | female | 18 | 204 | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | 203 | 运营 |
+------+-----------+--------+------+--------+------+--------------+
7 rows in set (0.00sec)#注意 union与union all的区别:union会去掉相同的纪录
外连接之全连接
符合条件连接查询:
需求1:找出年龄大于25岁的员工以及员工所在的部门
mysql> select 雇员.name,部门.name from雇员 inner join 部门-> on 雇员.dep_id =部门.id-> where age > 25;+-----------+--------------+
| name | name |
+-----------+--------------+
| 李国加 | 人力资源 |
| 彭达五 | 人力资源 |
| 找刘鹏 | 销售 |
+-----------+--------------+
3 rows in set (0.00 sec)
需求2:以内连接的方式查询雇员表和部门表,并且以age字段的升序方式显示
mysql> select 雇员.id,部门.name,雇员.age,部门.name from雇员,部门-> where 雇员.dep_id =部门.id-> and age > 25
->order by age asc;+----+--------------+------+--------------+
| id | name | age | name |
+----+--------------+------+--------------+
| 4 | 销售 | 28 | 销售 |
| 3 | 人力资源 | 38 | 人力资源 |
| 2 | 人力资源 | 48 | 人力资源 |
+----+--------------+------+--
所谓连表就是把两张表连接在一起之后 就变成一张大表 从from开始一直到on条件结束就看做一张表,之后 where 条件 group by 分组 order by limit 都正常的使用就可以了
子查询:
1:子查询是将一个查询语句嵌套在另一个查询语句中。
2:内层查询语句的查询结果,可以为外层查询语句提供查询条件。
3:子查询中可以包含:IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
4:还可以包含比较运算符:= 、 !=、> 、
带in关键字的子查询:
需求1:查看平均年龄在25岁以上的部门名
需求2:查看技术部员工姓名
需求3:查看不足1人的部名(子查询得到的是有人的部门id)
#需求1:
mysql> select id,name from部门-> where id in
-> (select dep_id from 雇员 group by dep_id having avg(age) > 25);+------+--------------+
| id | name |
+------+--------------+
| 201 | 人力资源 |
| 202 | 销售 |
+------+--------------+
2 rows in set (0.00sec)#查看技术部员工姓名
mysql> select name from雇员-> where dep_id in
-> (select id from 部门 where name='技术');+-----------+
| name |
+-----------+
| 张明 |
| 张国民 |
+-----------+
2 rows in set (0.00sec)#查看不足1人的部门名(子查询得到的是有人的部门id)
mysql> select name from 部门 where id not in (select distinct dep_id from雇员);+--------+
| name |
+--------+
| 运营 |
+--------+
1 row in set (0.00 sec)
in关键字的子查询
带比较运算符的子查询
比较运算符:= 、 !=、> 、
需求1:查询大于所有人平均年龄的员工名与年龄
需求2:查询大于部门内平均年龄的员工名、年龄
#查询大于所有人平均年龄的员工名与年龄
mysql> select name,age from 雇员 where age > (select avg(age) from雇员);+-----------+------+
| name | age |
+-----------+------+
| 李国加 | 48 |
| 彭达五 | 38 |
+-----------+------+
2 rows in set (0.00sec)#查询大于部门内平均年龄的员工名、年龄
mysql> select name,age from雇员 as t1 inner join-> (select dep_id,avg(age) avg_age from雇员 group by dep_id) as t2-> on t1.dep_id = t2.dep_id where age>avg_age;+-----------+------+
| name | age |
+-----------+------+
| 李国加 | 48 |
+-----------+------+
1 row in set (0.00 sec)
比较运算符子查询
3 带EXISTS关键字的子查询:
EXISTS关字键字表示存在。在使用EXISTS关键字时,内层查询语句不返回查询的记录,而是返回一个真假值。True或False当返回True时,外层查询语句将进行查询;当返回值为False时,外层查询语句不进行查询。
#department表中存在dept_id=203,Ture
mysql> select * from 雇员 where exists (select id from 部门 where id=200);+----+-----------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+-----------+--------+------+--------+
| 1 | 张明 | male | 18 | 200 |
| 2 | 李国加 | female | 48 | 201 |
| 3 | 彭达五 | male | 38 | 201 |
| 4 | 找刘鹏 | female | 28 | 202 |
| 5 | 张国民 | male | 18 | 200 |
| 6 | 两架于 | female | 18 | 204 |
+----+-----------+--------+------+--------+
6 rows in set (0.00sec)#department表中存在dept_id=205,False
mysql> select * from 雇员 where exists (select id from 部门 where id=204);
Empty set (0.00 sec)
带exists关键字子查询