MySQL数据库语法-多表查询练习一

一.数据表和测试数据准备

[root@iso-all ~]# cat yhh.sql 
#!/bin/bash
/*
@author :yanhuihuang
EMAIL:981654601@qq.com
*/
create database yanhuihuang;
use yanhuihuang;
create table chen (did int primary key auto_increment,dname varchar(50))engine=innodb default  charset=utf8;
create table huihuang ( eid int primary key auto_increment,name varchar(50),salary int,dno int,foreign key huihuang(dno) references chen(did)) engine=innodb default  charset=utf8;
insert into chen values(null,'市场部');
insert into chen values(null,'研发部');
insert into chen values(null,'财务部');
insert into chen values(null,'后勤部');
insert into huihuang values(null,'孙悟空',15000,1);
insert into huihuang values(null,'猪八戒',8000,2);
insert into huihuang values(null,'沙僧',7000,2);
insert into huihuang values(null,'唐僧',100000000,3);
insert into huihuang values(null,'太上老君',10000,1);
insert into huihuang values(null,'嫦娥',9800,1);
insert into huihuang values (null,'后羿',13000,null);
insert into huihuang values (null,'菩提老祖',15000,null);

二.内连接与外链接

1>.普通内链接(使用关键字)

mysql> select * from chen join huihuang on chen.did = huihuang.dno;  #关键字inner,join链接两张表，关键字on过滤条件
+-----+-----------+-----+--------------+-----------+------+
| did | dname     | eid | name         | salary    | dno  |
+-----+-----------+-----+--------------+-----------+------+
|   1 | 市场部    |   1 | 孙悟空       |     15000 |    1 |
|   1 | 市场部    |   5 | 太上老君     |     10000 |    1 |
|   1 | 市场部    |   6 | 嫦娥         |      9800 |    1 |
|   2 | 研发部    |   2 | 猪八戒       |      8000 |    2 |
|   2 | 研发部    |   3 | 沙僧         |      7000 |    2 |
|   3 | 财务部    |   4 | 唐僧         | 100000000 |    3 |
+-----+-----------+-----+--------------+-----------+------+
6 rows in set (0.00 sec)
mysql> select name,dname from chen inner join huihuang  on chen.did = huihuang.dno;          #我们配陪我们先看到的字节段
+--------------+-----------+
| name         | dname     |
+--------------+-----------+
| 孙悟空       | 市场部    |
| 太上老君     | 市场部    |
| 嫦娥         | 市场部    |
| 猪八戒       | 研发部    |
| 沙僧         | 研发部    |
| 唐僧         | 财务部    |
+--------------+-----------+
6 rows in set (0.00 sec)

2>.隐含内链接（不使用关键字，常用）

mysql> select * from chen,huihuang where chen.did = huihuang.dno;  #多个表格用逗号分隔
+-----+-----------+-----+--------------+-----------+------+
| did | dname     | eid | name         | salary    | dno  |
+-----+-----------+-----+--------------+-----------+------+
|   1 | 市场部    |   1 | 孙悟空       |     15000 |    1 |
|   1 | 市场部    |   5 | 太上老君     |     10000 |    1 |
|   1 | 市场部    |   6 | 嫦娥         |      9800 |    1 |
|   2 | 研发部    |   2 | 猪八戒       |      8000 |    2 |
|   2 | 研发部    |   3 | 沙僧         |      7000 |    2 |
|   3 | 财务部    |   4 | 唐僧         | 100000000 |    3 |
+-----+-----------+-----+--------------+-----------+------+
6 rows in set (0.00 sec)

mysql> select name,dname from chen,huihuang where chen.did = huihuang.dno;#过滤我们先要的字段
+--------------+-----------+
| name         | dname     |
+--------------+-----------+
| 孙悟空       | 市场部    |
| 太上老君     | 市场部    |
| 嫦娥         | 市场部    |
| 猪八戒       | 研发部    |
| 沙僧         | 研发部    |
| 唐僧         | 财务部    |
+--------------+-----------+
6 rows in set (0.00 sec)

3>.左外链接

mysql> select * from chen left outer join huihuang on chen.did = huihuang.dno;
+-----+-----------+------+--------------+-----------+------+
| did | dname     | eid  | name         | salary    | dno  |
+-----+-----------+------+--------------+-----------+------+
|   1 | 市场部    |    1 | 孙悟空       |     15000 |    1 |
|   1 | 市场部    |    5 | 太上老君     |     10000 |    1 |
|   1 | 市场部    |    6 | 嫦娥         |      9800 |    1 |
|   2 | 研发部    |    2 | 猪八戒       |      8000 |    2 |
|   2 | 研发部    |    3 | 沙僧         |      7000 |    2 |
|   3 | 财务部    |    4 | 唐僧         | 100000000 |    3 |
|   4 | 后勤部    | NULL | NULL         |      NULL | NULL |
+-----+-----------+------+--------------+-----------+------+
7 rows in set (0.00 sec)

mysql> select * from chen left join huihuang on chen.did = huihuang.dno;
+-----+-----------+------+--------------+-----------+------+
| did | dname     | eid  | name         | salary    | dno  |
+-----+-----------+------+--------------+-----------+------+
|   1 | 市场部    |    1 | 孙悟空       |     15000 |    1 |
|   1 | 市场部    |    5 | 太上老君     |     10000 |    1 |
|   1 | 市场部    |    6 | 嫦娥         |      9800 |    1 |
|   2 | 研发部    |    2 | 猪八戒       |      8000 |    2 |
|   2 | 研发部    |    3 | 沙僧         |      7000 |    2 |
|   3 | 财务部    |    4 | 唐僧         | 100000000 |    3 |
|   4 | 后勤部    | NULL | NULL         |      NULL | NULL |
+-----+-----------+------+--------------+-----------+------+

4>.右外链接

mysql> select * from chen right join huihuang on chen.did = huihuang.dno;
+------+-----------+-----+--------------+-----------+------+
| did  | dname     | eid | name         | salary    | dno  |
+------+-----------+-----+--------------+-----------+------+
|    1 | 市场部    |   1 | 孙悟空       |     15000 |    1 |
|    1 | 市场部    |   5 | 太上老君     |     10000 |    1 |
|    1 | 市场部    |   6 | 嫦娥         |      9800 |    1 |
|    2 | 研发部    |   2 | 猪八戒       |      8000 |    2 |
|    2 | 研发部    |   3 | 沙僧         |      7000 |    2 |
|    3 | 财务部    |   4 | 唐僧         | 100000000 |    3 |
| NULL | NULL      |   7 | 后羿         |     13000 | NULL |
| NULL | NULL      |   8 | 菩提老祖     |     15000 | NULL |
+------+-----------+-----+--------------+-----------+------+
8 rows in set (0.00 sec)

mysql> select * from chen right outer  join huihuang on chen.did = huihuang.dno;
+------+-----------+-----+--------------+-----------+------+
| did  | dname     | eid | name         | salary    | dno  |
+------+-----------+-----+--------------+-----------+------+
|    1 | 市场部    |   1 | 孙悟空       |     15000 |    1 |
|    1 | 市场部    |   5 | 太上老君     |     10000 |    1 |
|    1 | 市场部    |   6 | 嫦娥         |      9800 |    1 |
|    2 | 研发部    |   2 | 猪八戒       |      8000 |    2 |
|    2 | 研发部    |   3 | 沙僧         |      7000 |    2 |
|    3 | 财务部    |   4 | 唐僧         | 100000000 |    3 |
| NULL | NULL      |   7 | 后羿         |     13000 | NULL |
| NULL | NULL      |   8 | 菩提老祖     |     15000 | NULL |
+------+-----------+-----+--------------+-----------+------+
8 rows in set (0.00 sec)

三.小试牛刀

1>.查询所有人的所属部门和员工名称

mysql> select name as 姓名,dname as 部门 from chen,huihuang where chen.did = huihuang.dno;
+--------------+-----------+
| 姓名         | 部门      |
+--------------+-----------+
| 孙悟空       | 市场部    |
| 太上老君     | 市场部    |
| 嫦娥         | 市场部    |
| 猪八戒       | 研发部    |
| 沙僧         | 研发部    |
| 唐僧         | 财务部    |
+--------------+-----------+
6 rows in set (0.01 sec)
#查询含有部门的员工

2>.统计每个部门的人数(group by)

mysql> select chen.dname as 所属部门,count(*) as 部门人数 from chen,huihuang where  chen.did = huihuang.dno group by chen.dname;
+--------------+--------------+
| 所属部门     | 部门人数     |
+--------------+--------------+
| 市场部       |            3 |
| 研发部       |            2 |
| 财务部       |            1 |
+--------------+--------------+
3 rows in set (0.00 sec)

3>.统计每个部门的平均工资(group by)

mysql> select chen.dname as 所属部门,avg(huihuang.salary) as 平均薪资 from chen,huihuang where chen.did = huihuang.dno group by chen.dname;
+--------------+----------------+
| 所属部门     | 平均薪资       |
+--------------+----------------+
| 市场部       |     11600.0000 |
| 研发部       |      7500.0000 |
| 财务部       | 100000000.0000 |
+--------------+----------------+
3 rows in set (0.01 sec)

4>.统计部门的平均工资大于公司平均工资的部门

mysql> select chen.dname 所属部门,avg(huihuang.salary) 部门薪资 from chen,huihuang where chen.did = huihuang.dno group by chen.dname having 部门薪资 > (select avg(salary) from huihuang);
+--------------+----------------+
| 所属部门     | 部门薪资       |
+--------------+----------------+
| 财务部       | 100000000.0000 |
+--------------+----------------+
1 row in set (0.02 sec)