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)
