mysql学習3日目の練習(複数表接続)
13738 ワード
原文のリンク:http://www.cnblogs.com/makangning/p/9403683.html
転載先:https://www.cnblogs.com/makangning/p/9403683.html
--
-- , 、 、
select ename,dname,loc
from emp,dept
where emp.deptno = dept.deptno
-- 1
-- 1、 , 、 、
select ename,dept.deptno,dname
from emp,dept
where emp.deptno = dept.deptno
-- 2、 , CHICAGO
-- , ,
select ename,loc,comm
from emp,dept
where emp.deptno = dept.deptno
and loc = 'CHICAGO'
and comm is not null
-- 3、 , A 、
select ename,loc
from emp,dept
where emp.deptno = dept.deptno
and ename like '%A%'
-- , ,
select ename,sal,grade
from emp,salgrade
where sal>=losal
and sal<=hisal
select ename,sal,grade
from emp,salgrade
where sal between losal and hisal
-- 2
-- 1、 , , ,
--
select empno,ename,grade,loc
from emp,dept,salgrade
where emp.deptno = dept.deptno
and sal>=losal
and sal<=hisal
order by grade
-- 2、
--
select t1.ename ,t2.ename
from emp t1,emp t2
where t1.mgr = t2.empno
-- 3
-- YORK CHICAGO , ,
-- ,
select t1.ename,t1.empno,t2.ename ,t2.empno
from emp t1,emp t2,dept
where t1.mgr = t2.empno
and t1.deptno = dept.deptno
and loc in('NEW YORK','CHICAGO')
-- 3、
select *
from emp
cross join dept
-- 4、
select *
from emp
natural join dept
-- 5、using
select *
from emp
join dept
using(deptno)
-- 6、on
select *
from emp
join dept
on emp.deptno = dept.deptno
join salgrade
on sal between losal and hisal
where emp.deptno = 10
-- ,
select *
from emp
join dept
on emp.deptno = dept.deptno
select *
from emp,dept
where emp.deptno = dept.deptno
-- 7、 ,
select *
from dept
left join emp
on dept.deptno = emp.deptno
select *
from emp
right join dept
on emp.deptno = dept.deptno
-- ,
select t1.ename ,t2.ename
from emp t1
left join emp t2
on t1.mgr = t2.empno
-- 4
-- sql-99 ,
-- 1、
select *
from emp
cross join dept
-- 2、 , 80 5 1
-- 、 、
select ename,dname,hiredate
from emp
natural join dept
where hiredate >= '1980-05-01'
-- 3、 using , CHICAGO 、 、
select ename,dname,loc
from emp
join dept
using(deptno)
where loc = 'CHICAGO'
-- 4、 on , CHICAGO 、 、 、
select ename,dname,loc,grade
from emp
join dept
on emp.deptno = dept.deptno
join salgrade
on sal between losal and hisal
where loc = 'CHICAGO'
-- 5、 , , , king
select e.ename,m.ename
from emp e
left join emp m
on e.mgr = m.empno
転載先:https://www.cnblogs.com/makangning/p/9403683.html