-- 表链接
-- 内链接
use test1;
-- 第一中表示方式(没有外键约束的两张表一般使用第一种):
select ename, dname from emp, dept where emp.deptno = dept.deptno;
-- 第二种表示方式(有外键约束)
select ename,dname from emp inner join dept on emp.deptno = dept.deptno;
-- 列出员工的姓名,工资和工资等级编号
select ename, sal, grade from emp,salgrade where emp.sal > salgrade.LOSAL and emp.sal < salgrade.HISAL;
-- 查询员工的编号,姓名,领导的编号和姓名(一张emp表的内连接)
select t1.empno, t1.ename, t1.MGR, t2.ename from emp as t1 join emp as t2 on t1.mgr = t2.empno;
select t1.empno, t1.ename, t1.mgr,t2.ename from emp as t1,emp as t2 where t1.mgr = t2.empno;
-- 查询所有用户的姓名,部门编号和部门名称
select emp.ename, emp.deptno, dept.dname from emp, dept where emp.deptno = dept.deptno;
select t1.ename,t2.deptno,t2.dname from emp as t1 join dept as t2 on t1.deptno = t2.deptno;
-- 查看10部门员工的姓名,薪水和薪水等级
select emp.ename, emp.sal, salgrade.grade from emp,salgrade where emp.deptno = 10 and emp.sal <HISAL and emp.sal > LOSAL;
-- 外连接
-- 左外连接,右外连接
-- 查询所有部门的部门名字和该部门下的员工名字,如果该部门没有员工,也要列出部门名字
select t2.dname,t1.ename from emp as t1 right join dept as t2 on t1.deptno = t2.deptno;
-- 查询所有的员工的编号,姓名,有领导的显示领导的编号和姓名,没有领导的只显示员工信息
select t1.empno, t1.ename, t2.empno, t2.ename from emp as t1 left join emp as t2 on t1.MGR = t2.empno;
-- 查询所有部门的详细信息及每个部门的平均工资,包含没有员工的部门
select t2.*, avg(t1.sal) from emp as t1 right join dept as t2 on t1.deptno = t2.deptno group by t2.deptno;
-- 内链接
use test1;
-- 第一中表示方式(没有外键约束的两张表一般使用第一种):
select ename, dname from emp, dept where emp.deptno = dept.deptno;
-- 第二种表示方式(有外键约束)
select ename,dname from emp inner join dept on emp.deptno = dept.deptno;
-- 列出员工的姓名,工资和工资等级编号
select ename, sal, grade from emp,salgrade where emp.sal > salgrade.LOSAL and emp.sal < salgrade.HISAL;
-- 查询员工的编号,姓名,领导的编号和姓名(一张emp表的内连接)
select t1.empno, t1.ename, t1.MGR, t2.ename from emp as t1 join emp as t2 on t1.mgr = t2.empno;
select t1.empno, t1.ename, t1.mgr,t2.ename from emp as t1,emp as t2 where t1.mgr = t2.empno;
-- 查询所有用户的姓名,部门编号和部门名称
select emp.ename, emp.deptno, dept.dname from emp, dept where emp.deptno = dept.deptno;
select t1.ename,t2.deptno,t2.dname from emp as t1 join dept as t2 on t1.deptno = t2.deptno;
-- 查看10部门员工的姓名,薪水和薪水等级
select emp.ename, emp.sal, salgrade.grade from emp,salgrade where emp.deptno = 10 and emp.sal <HISAL and emp.sal > LOSAL;
-- 外连接
-- 左外连接,右外连接
-- 查询所有部门的部门名字和该部门下的员工名字,如果该部门没有员工,也要列出部门名字
select t2.dname,t1.ename from emp as t1 right join dept as t2 on t1.deptno = t2.deptno;
-- 查询所有的员工的编号,姓名,有领导的显示领导的编号和姓名,没有领导的只显示员工信息
select t1.empno, t1.ename, t2.empno, t2.ename from emp as t1 left join emp as t2 on t1.MGR = t2.empno;
-- 查询所有部门的详细信息及每个部门的平均工资,包含没有员工的部门
select t2.*, avg(t1.sal) from emp as t1 right join dept as t2 on t1.deptno = t2.deptno group by t2.deptno;