mysql入门(外连接十)
来源:互联网 发布:马赛克软件下载 编辑:程序博客网 时间:2024/06/05 20:05
1:找出每个员工对应的部门名称,要求部门名称全部显示(外连接)
内连接:a和b表能够完全匹配的记录查询出来
外连接:满足内链接外,将其中一张表中的记录全部查询出来,另外一张表如果没有与之匹配的记录,则模拟为null显示
1:右外连接
select e.empno,e.ename,d.dname from emp e left join dept d on e.deptno=d.deptno;
+-------+---------+------------+
| empno | ename | dname |
+-------+---------+------------+
| 7782 | CLARK | ACCOUNTING |
| 7839 | KING | ACCOUNTING |
| 7934 | MILLER | ACCOUNTING |
| 7369 | SMITH | RESEARCH |
| 7566 | JONES | RESEARCH |
| 7788 | SCOLL | RESEARCH |
| 7876 | ADAMS | RESEARCH |
| 7902 | FORD | RESEARCH |
| 7499 | ALLEN | SALES |
| 7521 | WARD | SALES |
| 7654 | MARTIM | SALES |
| 7698 | BLAKE | SALES |
| 7844 | IUSRNER | SALES |
| 7900 | JAMES | SALES |
+-------+---------+------------+
14 rows in set (0.00 sec)
+-------+---------+------------+
| empno | ename | dname |
+-------+---------+------------+
| 7782 | CLARK | ACCOUNTING |
| 7839 | KING | ACCOUNTING |
| 7934 | MILLER | ACCOUNTING |
| 7369 | SMITH | RESEARCH |
| 7566 | JONES | RESEARCH |
| 7788 | SCOLL | RESEARCH |
| 7876 | ADAMS | RESEARCH |
| 7902 | FORD | RESEARCH |
| 7499 | ALLEN | SALES |
| 7521 | WARD | SALES |
| 7654 | MARTIM | SALES |
| 7698 | BLAKE | SALES |
| 7844 | IUSRNER | SALES |
| 7900 | JAMES | SALES |
+-------+---------+------------+
14 rows in set (0.00 sec)
mysql> select e.empno,e.ename,d.dname from emp e right outer join dept d on e.deptno=d.deptno;
+-------+---------+------------+
| empno | ename | dname |
+-------+---------+------------+
| 7369 | SMITH | RESEARCH |
| 7499 | ALLEN | SALES |
| 7521 | WARD | SALES |
| 7566 | JONES | RESEARCH |
| 7654 | MARTIM | SALES |
| 7698 | BLAKE | SALES |
| 7782 | CLARK | ACCOUNTING |
| 7788 | SCOLL | RESEARCH |
| 7839 | KING | ACCOUNTING |
| 7844 | IUSRNER | SALES |
| 7876 | ADAMS | RESEARCH |
| 7900 | JAMES | SALES |
| 7902 | FORD | RESEARCH |
| 7934 | MILLER | ACCOUNTING |
| NULL | NULL | OPERATIONS |
+-------+---------+------------+
15 rows in set (0.00 sec)
+-------+---------+------------+
| empno | ename | dname |
+-------+---------+------------+
| 7369 | SMITH | RESEARCH |
| 7499 | ALLEN | SALES |
| 7521 | WARD | SALES |
| 7566 | JONES | RESEARCH |
| 7654 | MARTIM | SALES |
| 7698 | BLAKE | SALES |
| 7782 | CLARK | ACCOUNTING |
| 7788 | SCOLL | RESEARCH |
| 7839 | KING | ACCOUNTING |
| 7844 | IUSRNER | SALES |
| 7876 | ADAMS | RESEARCH |
| 7900 | JAMES | SALES |
| 7902 | FORD | RESEARCH |
| 7934 | MILLER | ACCOUNTING |
| NULL | NULL | OPERATIONS |
+-------+---------+------------+
15 rows in set (0.00 sec)
2:左外连接
select e.empno,e.ename,d.dname from dept d left outer join emp e on e.deptno=d.deptno;
+-------+---------+------------+
| empno | ename | dname |
+-------+---------+------------+
| 7369 | SMITH | RESEARCH |
| 7499 | ALLEN | SALES |
| 7521 | WARD | SALES |
| 7566 | JONES | RESEARCH |
| 7654 | MARTIM | SALES |
| 7698 | BLAKE | SALES |
| 7782 | CLARK | ACCOUNTING |
| 7788 | SCOLL | RESEARCH |
| 7839 | KING | ACCOUNTING |
| 7844 | IUSRNER | SALES |
| 7876 | ADAMS | RESEARCH |
| 7900 | JAMES | SALES |
| 7902 | FORD | RESEARCH |
| 7934 | MILLER | ACCOUNTING |
| NULL | NULL | OPERATIONS |
+-------+---------+------------+
15 rows in set (0.00 sec)
+-------+---------+------------+
| empno | ename | dname |
+-------+---------+------------+
| 7369 | SMITH | RESEARCH |
| 7499 | ALLEN | SALES |
| 7521 | WARD | SALES |
| 7566 | JONES | RESEARCH |
| 7654 | MARTIM | SALES |
| 7698 | BLAKE | SALES |
| 7782 | CLARK | ACCOUNTING |
| 7788 | SCOLL | RESEARCH |
| 7839 | KING | ACCOUNTING |
| 7844 | IUSRNER | SALES |
| 7876 | ADAMS | RESEARCH |
| 7900 | JAMES | SALES |
| 7902 | FORD | RESEARCH |
| 7934 | MILLER | ACCOUNTING |
| NULL | NULL | OPERATIONS |
+-------+---------+------------+
15 rows in set (0.00 sec)
阅读全文
0 0
- mysql入门(外连接十)
- mysql入门(二十)
- JDBC连接MySql入门
- mysql入门的十条语句
- Mysql入门(十)之事务
- MySQL基础入门之连接
- Matlab连接MySQL数据库入门
- mysql入门连接查询(九)
- jdbc连接mysql入门程序
- PHP和Mysql连接入门
- MFC连接mysql数据库(十分钟搞定)
- python 连接MYSQL数据库(入门帖)
- MySql-从安装到连接轻松入门
- Clojure 学习入门(7)- 连接mysql
- JDBC入门——mysql连接方法
- MYSQL入门学习之十:视图的基本操作
- MYSQL入门学习之十:视图的基本操作
- MYSQL入门学习之二十:MYSQLIMPORT命令参数详解
- 模拟基于ajax调后台接口的json数据
- python基础学习(3)
- POJ2153
- eclipse启动了tomcat,浏览器可以访问tomcat下的项目但不能访问tomcat主页
- gym100206E (n*n)的马遍历问题 贪心
- mysql入门(外连接十)
- Pandas Index 属性
- Redis常用操作命令
- 使用node-inspector调试NodeJS代码
- Linux下VI的基本操作实验
- 必须掌握的技术知识点
- struts2 中redirectAction如何传递参数!
- Problems when installing R and Rstudio in Ubuntu:
- 今天做到一道面试题:线程安全问题,类锁与对象锁的区别