用两个表(t_worker,t_depart),关联字段t_worker.deptno和t_depart.deptno来演示一下MySQL的等值连接、左连接、右连接、全连接。数据库表:t_worker,t_depart
表1
表2
下面是有关等值连接、左连接、右连接、全连接相关概念
等值连接:
在连接条件中使用等于号(=)运算符比较被连接列的列值,其查询结果中列出被连接表中的所有列,包括其中的重复列。
select * from t_worker t1 INNER JOIN t_depart t2 on t1.deptno= t2.deptno where t2.deptno =t2.deptno;
select * from t_worker t1,t_depart t2 where t2.deptno =t2.deptno;
左连接:
左向外连接的结果集包括 LEFT OUTER子句中指定的左表的所有行,而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配行,则在相关联的结果集行中右表的所有选择列表列均为空值。
select * from t_worker t1 left JOIN t_depart t2 on t1.deptno =t2.deptno where empno>7800;
select * from t_worker t1 left JOIN t_depart t2 on t1.deptno =t2.deptno;
右连接:
RIGHT JOIN 或 RIGHT OUTER JOIN
右向外联接是左向外连接的反向连接。将返回右表的所有行。如果右表的某行在左表中没有匹配行,则将为左表返回空值。
select * from t_worker t1 RIGHT JOIN t_depart t2 on t1.deptno =t2.deptno where empno>7800;
全连接:
MySQL是不支持全外的连接的,这里给出的写法适合Oracle和DB2。但是可以通过左外和右外求合集来获取全外连接的查询结果。
其他的查询语句与问题
-- 1-查询部门编号是30的员工并且薪资大于500的
select * from t_worker where deptno=30 and sal>50;
-- 2-查询薪资最高的前3条记录
select * from t_worker ORDER BY sal desc limit 0,3;
-- 3-查询员工表中有哪些部门
select DISTINCT(deptno) from t_worker;
-- 4-查询员工号大于7800的员工编号和对应的部门名称
select empno,t2.dname from t_worker t1,t_depart t2 where empno>7800 and t1.deptno=t2.deptno;
-- 5-查询薪资在800到1500之间的员工姓名,工号,职位,薪水
select empno,ename,job,sal from t_worker where sal BETWEEN 800 and 1500;
-- 6-查询奖金不为空的且薪资不低于500的
select * from t_worker where comm>0 and sal>500;
select * from t_worker where comm IS NOT NULL and sal>500;
-- 7-每个部门的平均工资
select empno,dname,avg(sal) sal from t_worker t1,t_depart t2 where t1.deptno=t2.deptno GROUP BY dname;
-- 8-每个部门中每个岗位的最高薪资
select job,deptno,MAX(sal)maxsal from t_worker GROUP BY deptno,job ORDER BY deptno,job;
-- 9-平均工资大于2000的部门名称和平均工资
select avg(sal),dname from t_worker t1,t_depart t2 where t1.deptno=t2.deptno GROUP BY dname HAVING avg(sal)>2000;
<li class="tool-item tool-active is-like "><a href="javascript:;"><svg class="icon" aria-hidden="true"> <use xmlns:xlink="http://www.w3.org/1999/xlink" xlink:href="#csdnc-thumbsup"></use> </svg><span class="name">点赞</span> <span class="count">9</span> </a></li> <li class="tool-item tool-active is-collection "><a href="javascript:;" data-report-click="{"mod":"popu_824"}"><svg class="icon" aria-hidden="true"> <use xmlns:xlink="http://www.w3.org/1999/xlink" xlink:href="#icon-csdnc-Collection-G"></use> </svg><span class="name">收藏</span></a></li> <li class="tool-item tool-active is-share"><a href="javascript:;"><svg class="icon" aria-hidden="true"> <use xmlns:xlink="http://www.w3.org/1999/xlink" xlink:href="#icon-csdnc-fenxiang"></use> </svg>分享</a></li> <!--打赏开始--> <!--打赏结束--> <li class="tool-item tool-more"> <a> <svg t="1575545411852" class="icon" viewBox="0 0 1024 1024" version="1.1" xmlns="http://www.w3.org/2000/svg" p-id="5717" xmlns:xlink="http://www.w3.org/1999/xlink" width="200" height="200"><defs><style type="text/css"></style></defs><path d="M179.176 499.222m-113.245 0a113.245 113.245 0 1 0 226.49 0 113.245 113.245 0 1 0-226.49 0Z" p-id="5718"></path><path d="M509.684 499.222m-113.245 0a113.245 113.245 0 1 0 226.49 0 113.245 113.245 0 1 0-226.49 0Z" p-id="5719"></path><path d="M846.175 499.222m-113.245 0a113.245 113.245 0 1 0 226.49 0 113.245 113.245 0 1 0-226.49 0Z" p-id="5720"></path></svg> </a> <ul class="more-box"> <li class="item"><a class="article-report">文章举报</a></li> </ul> </li> </ul> </div> </div> <div class="person-messagebox"> <div class="left-message"><a href="https://blog.csdn.net/wyz0516071128"> <img src="https://profile.csdnimg.cn/D/9/2/3_wyz0516071128" class="avatar_pic" username="wyz0516071128"> <img src="https://g.csdnimg.cn/static/user-reg-year/1x/2.png" class="user-years"> </a></div> <div class="middle-message"> <div class="title"><span class="tit"><a href="https://blog.csdn.net/wyz0516071128" data-report-click="{"mod":"popu_379"}" target="_blank">奔跑的小鲫鱼</a></span> </div> <div class="text"><span>发布了92 篇原创文章</span> · <span>获赞 393</span> · <span>访问量 21万+</span></div> </div> <div class="right-message"> <a href="https://im.csdn.net/im/main.html?userName=wyz0516071128" target="_blank" class="btn btn-sm btn-red-hollow bt-button personal-letter">私信 </a> <a class="btn btn-sm bt-button personal-watch" data-report-click="{"mod":"popu_379"}">关注</a> </div> </div> </div>