首先,我们先创建一个部门编号表dept,字段为deptno(部门编号)、dname(部门名称)、loc(部门所在地),将emp_deptno重命名为deptno,并设置表emp中字段deptno为表dept外键。
create table dept(
deptno int(2) primary key,
dname varchar(20),
loc varchar(20)
);
insert dept values
(10,'Accounting','New York'),
(20,'Research','Dallas'),
(30,'Sales','Chicago');
alter table emp add foreign key emp(emp_deptno) references dept(deptno);
alter table emp change emp_deptno deptno int(2);
1.交叉连接查询:
select * from emp join dept;
2.自然连接查询:
select * from dept natural join emp;
3.内连接查询:
1.等值查询:
select * from dept join emp on emp.deptno = dept.deptno;
select * from dept join emp on emp.deptno = dept.deptno where emp.emp_sal>1000;
2.非等值查询:
select * from dept join emp on emp.deptno = dept.deptno and emp.emp_id>emp.emp_mgr order by dept.deptno;#查询员工工号大于领导工号
4.外连接查询:
1.左外连接:
select e1.emp_name,e1.emp_job ejob,e2.emp_name lname,e2.emp_job ljob from emp e1 left join emp e2 on e1.emp_mgr = e2.emp_id;
2.右外连接:
select e1.emp_name,e1.emp_job ejob,e2.emp_name lname,e2.emp_job ljob from emp e1 right join emp e2 on e1.emp_id = e2.emp_mgr;
两个表查询显示的结果相似,有兴趣的可以试试,个人感觉了解即可。
5.子查询:
1.标量子查询:
select * from emp where emp_sal>(select emp_sal from emp where emp_name = 'Clark');
2.行子查询:
select * from emp where emp_name = (select emp_name from emp where emp_name = 'Allen');
3.列子查询:
1->:select * from emp where deptno in (select deptno from dept where loc in ('New York','Dallas'));#使用in关键
2->:select * from emp where emp_sal
3->:select * from emp where emp_sal >all(select emp_sal from emp where emp_job='Clerk');#使用all关键字
4->:select * from emp where exists (select deptno from dept where deptno = emp.deptno);使用exists关键字
4.表子查询:
select deptno,avg(emp_sal) avg_sal from emp group by deptno having avg_sal like(select max(avg_sal1) from (select avg(emp_sal) avg_sal1 from emp group by deptno) new_emp);#查询月薪最高的部门