MySQl交叉查询内连接查询外连接查询

2024 年 05 月 21 日

316 次浏览

3085字数

```sql
-- 1.交叉查询（笛卡尔积）	2.内连接查询 3.外连接查询
-- 交叉查询 
Select * from emp,dept;
-- 内连接 
-- SQL99 显式 select * from emp join dept on ;
-- SQL92 隐式slect * from emp,dept where;

外连接查询
左外: select * from emp,dept outer left where
右边:select * from emp,dept outer right where

数据准备：
create database  test2;
use test2;
-- 创建部门表
create table dept(
	deptno int primary key,  -- 部门编号
	dname varchar(20), -- 部门名称
	loc varchar(20)  -- 部门地址
);
-- 给部门表添加数据
insert into dept values(10,'accounting','new york'),(20,'research','dallas'),(30,'sales','chigago'),(40,'operations','boston');

-- 创建员工表 
create table emp(
	empno int primary key, -- 员工编号 
	ename varchar(20), -- 员工姓名 
	job varchar(9), -- 员工工作 
	mgr int, -- 员工直属领导编号 
	hiredate date, -- 入职时间
	sal double, -- 员工工资 
	comm double, -- 员工奖金 
	deptno int -- 对应dept表的外键
);

-- 添加 部门 和 员工 之间的主外键关系
alter table emp add CONSTRAINT foreign key(deptno) REFERENCES dept(deptno);

-- 给员工表添加数据 
insert into emp values(7369,'smith','clerk',7902,'1980-12-17',800,null,20);
insert into emp values(7499,'allen','salesman', 7698, '1981-02-20',1600,300,30);
insert into emp values(7521,'ward','salesman',7698,'1981-02-22',1250,500,30);
insert into emp values(7566, 'jones','manager' , 7839,'1981-04-02' ,2975,null, 20);
insert into emp values(7654,'martin','salesman', 7698,'1981-09-28',1250,1400,30);
insert into emp values(7698,'blake','manager',7839,'1981-05-01',2850,null,30);
insert into emp values(7782,'clark','manager', 7839, '1981-06-09' , 2450,null,10);
insert into emp values(7788,'scott','analyst',7566,'1987-07-03',3900,null,20);
insert into emp values(7839,'king','president',null,'1981-11-17',5000,null,10);
insert into emp values(7844,'turner','salesman',7698,'1981-09-08',1500,0,30);
insert into emp values(7876,'adams','clerk',7788,'1987-07-13', 1100,null,20);
insert into emp values(7900,'james','clerk',7698,'1981-12-03',950,null,30);
insert into emp values(7902,'ford','analyst',7566,'1981-12-03',3000,null, 20);
insert into emp values(7934, 'miller', 'clerk',7782,'1981-01-23',1300,null,10);

-- 创建工资等级表 
create table salgrade(
	grade int, -- 工资等级 
	losal double, -- 最低工资 
	hisal double  -- 最高工资 
);

-- 给工资等级表添加数据 
insert into salgrade values (1, 700,1200);
insert into salgrade values (2,1201,1400);
insert into salgrade values (3,1401,2000);
insert into salgrade values (4,2001,3000);
insert into salgrade values (5,3001,9999);

-- 练习
-- 1、返回拥有员工的部门名、部门号。
select distinct dname,dept.deptno from dept,emp where dept.deptno=emp.deptno;
select distinct dname,dept.deptno from dept join emp on dept.deptno=emp.deptno;

-- 2、工资水平多于smith的员工信息
select * from emp where sal>(select sal from emp where ename='smith');

-- 3、返回员工和所属经理的姓名。
-- 内连接查询
select a.ename '员工',b.ename '经理' from emp a,emp b where a.mgr=b.empno;

-- 4、返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名

select a.ename '员工',b.ename '经理' from emp a,emp b where a.mgr=b.empno and a.hiredate<b.hiredate;

-- 5、返回员工姓名及其所在的部门名称。
select ename,dname from emp,dept where dept.deptno=emp.deptno;

-- 6、返回从事clerk工作的员工姓名和所在部门名称。
select ename,dname from emp,dept where dept.deptno=emp.deptno and job='clerk';

-- 7、返回部门号及其本部门的最低工资
select deptno,min(sal) from emp group by deptno;

-- 8、返回销售部(sales)所有员工的姓名
select ename from emp where deptno=(select deptno from dept where dname='sales');

-- 9、返回工资水平多于平均工资的员工 
select * from emp where sal>(select avg(sal) from emp);

-- 10、返回与scott从事相同工作的员工 
select * from emp where job=(select job from emp where ename='scott');
```

MySQl交叉查询内连接查询外连接查询