-- 显示不带有'R'的员工姓名
--select ename from emp where ename not like '%R%';
-- 多表连接
select table1.column ,table2.column
from table1,table2
where table1.column=table2.column;
//在where子句中书写连接条件，如果在多个表中出现相同的列名，则需要使用表名作为来自该表的列名的前缀，N个表相连时，至少需要N-1个连接条件
-- 连接类型 按连接条件分 等值连接 和非等值连接
-- 按其他连接方法分 外部连接和内部连接
---多表连接写法
1.分析要查询的列都来自于哪些表，构成from子句
2.分析这些表之间的关联关系，如果表之间没有直接关联关系，而是通过另一个中间表关联，则也要在from子句中补充中间关联表
3.接下来在where子句中补充表之间的关联关系，通常N个表，至少需要N-1个关联关系
4.分析是否还有其它限制条件，补充到where子句的表关联的关系后，作为限制条件
5.根据用户想要显示的信息，补充select子句
6.分析是否有排列要求，如果排序要求中还涉及到其它表，则也要进行第二步补充排序字段所在的表，并且添加表之间的关联关系
--笛卡尔积
--笛卡尔积就是第一个表中所有行和第二个表中所有行都发生连接
--一般会出现笛卡尔积的情况：连接条件被忽略或者连接条件无效
--为了避免笛卡尔积的产生，通常需要在where子句中包含一个有效的连接条件
select emp.empno,emo.ename,emp.deptno
from emp,dept;
就相当于交叉连接
select *from emp cross join dept;
-- 等值连接
select emp.empno,emp.ename,emp.deptno,dept.loc
from emp,dept
where emp.deptno=dept.deptno;
--使用and运算符增加其它查询条件
--在用到多个表时可以使用表名作为前缀来限定列
--通过使用表前缀可以提高性能
--通过使用表的别名可以区分来自不同表但是名字相同的列
使用表的别名
select e.empno,e.ename,e.deptno,d.loc
from emp e,dept d
where e.deptno=d.deptno;
--非等值连接
格式 例子查询每个员工的姓名，工资，工资等级
select e.ename,e.sal,s.grade
from emp e,salgrade s
where e.sal
between s.losal and s.hisal;
--例子:
--查询每个员工的编号，姓名，工资，工资等级，所在工作城市，按照工资等级进行升序排序。
select emp.empno,emp.ename,emp.sal,salgrade.grade,dept.loc
from emp,dept,salgrade
where emp.deptno=dept.deptno
and ( emp.sal between salgrade.losal and salgrade.hisal)
order by salgrade.grade;
-- 查询所有工作在NEW YORK和CHICAGO的员工姓名，员工编号，以及他们的经理姓名，经理编号。
select w.ename,w.empno,m.ename,m.empno
from emp w,emp m,dept d
where w.deptno =d.deptno and w.mgr =m.empno
and d.loc in('NEWYORK','CHICAGO');//in()在这里面选择
--自身连接
自身连接，也叫自连接，是一个表通过某种条件和本身进行连接的一种方式，就如同多个表连接一样
select worker.ename 'WNAKE',manager.ename 'LNAME'
from emp worker,emp manager
where worker.mgr= manager.empno;
---ANSI SQL:标准的连接语法
----- 交叉连接
交叉连接会产生两个表的交叉乘积，和两个表之间的笛卡尔积是一样的；
--使用cross join子句完成
select emp.empno,emp.ename,emp.deptno,dept.loc
from emp
cross join dept;
-----自然连接
自然连接是对两个表之间相同名字和数据类型的列进行的等值连接;如果两个表之间相同名称的列的数据类型不同，则会产生错误
--使用 natural join子句来完成
select empno,ename,sal,deptno,loc
from emp
natural join dept;
-----using 子句
自然连接是使用所有名称和数据类型相匹配的列作为连接条件，而using子句可以指定用某个或者某几个相同名字和数据类型的列作为连接条件
select e.ename,e.empno,e.sal,deptno,d.loc
from emp e join dept d using(deptno)
where deptno=20;
--使用using子句创建连接时，应注意以下几点:
如果有若干个列名称相同但数据类型不同，自然连接子句可以用using子句来替换，以指定产生等值连接的列
如果有多于一个列都匹配的情况，使用using子句只能指定其中的一列
using子句中的用到的列不能使用表名和别名作为前缀
natural join 子句和using 子句是相互排斥的，不能同时使用
-----on子句
自然连接条件基本上是具有相同列名的表之间的等值连接
如果要指定任意连接条件，或指定要连接的列，则可以使用on子句
用on将连接条件和其它检索条件分隔开，用其它检索条件写在where子句
on子句可以提高代码的可读性
select e.empno,e.ename,d.loc,m.ename
from emp e
join dept d
on e.deptno=d.deptno
join emp m
on e.mgr=m.empno;
-----外部连接
在多表连接时，可以使用外部连接来查看哪些行，按照链接条件没有被匹配上
左外连接以from子句中的左边表为基表，该表所有行数据按照连接条件无论是否与右边表能匹配上都会被显示出来//就是左边表全显示
右外连接以from子句中的右边表为基表吗，该表所有行数据按照连接条件无论是否与左边表能匹配上都会被显示出来
---左外连接
查询所有雇员姓名，部门编号，部门名称，包括没有部门的员工也要显示出来
select e.ename,e.deptno,d.loc
from emp e
left (outer) join dept d
on e.deptno= d.deptno;
---右外连接
查询所有雇员姓名，部门编号，部门名称，包括没有员工的部门也要显示出来
select e.ename,e.deptno,d.loc
from emp e
right (outer) join dept d
on e.deptno= d.deptno;
练习练习
1.创建一个员工表和部门表的交叉连接。
select *from emp
cross join dept
2.使用自然连接，显示入职日期在80年5月1日之后的员工姓名，部门名称，入职日期
select emp.ename,dept.dname,emp.hiredate
from emp natural join dept
where emp.hiredate>'1980-5-1';
3.使用USING子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点
select emp.ename,dept.dname,dept.loc
from emp join dept using(deptno)
where dept.loc='CHICAGO';
4.使用ON子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点，薪资等级
select e.ename,d.dname,d.loc,s.grade
from emp e
join dept d on e.deptno=d.deptno
join salgrade s on sal between losal and hisal
where d.loc='CHICAGO';
5.使用左连接，查询每个员工的姓名，经理姓名没有经理的King也要显示出来。
select w.ename,m.ename
from emp w
left join emp m on w.mgr=m.empno;
6.使用右连接，查询每个员工的姓名，经理姓名,没有经理的King也要显示出来。
select w.ename,m.ename
from emp m
right join emp w on w.mgr=m.empno;
