oracle数据库常识

join和Where的效率对比

db引擎会吧Where 解析为 join on

使用Join可以帮助检查语句中的无效或者误写的关联条件
join 的效率不必 where 条件的效率差

join on 是sql中的标准

on和where对比 见博客 （http://www.cnblogs.com/smallidea/p/3609382.html）

左连接或者右连接

相当于天平倾斜了，少的那边需要加砝码

下面是我练习的，嘿嘿，先放这了，以后会去掉

SELECT hiredate
FROM emp
WHERE hiredate>any(SELECT hiredate
                from emp
                WHERE ename='KING')
--在NEW YORK工作的员工，注：可能多行
SELECT ename
FROM emp
WHERE deptno in(SELECT deptno
                FROM dept
                WHERE loc='NEW YORK')


SELECT deptno,AVG(sal)
FROM emp
GROUP BY deptno
HAVING AVG(sal)=(SELECT max(AVG(sal))
                  FROM emp
                  GROUP BY deptno)


SELECT deptno,AVG(sal)
FROM emp
GROUP BY deptno
HAVING AVG(sal)>=ALL(SELECT AVG(sal)
                  FROM emp
                  GROUP BY deptno)


SELECT d.dname,AVG(e.sal)
FROM emp e JOIN dept d
ON e.deptno=d.deptno
GROUP BY d.dname
HAVING AVG(e.sal)>=ALL(SELECT AVG(sal)
                  FROM emp
                  GROUP BY deptno)


SELECT ename,empno
FROM emp
WHERE empno IN(SELECT mgr
               FROM emp)


--子查询中包含null，主查询没有返回结果
SELECT ename,empno
FROM emp
WHERE empno NOT IN(SELECT mgr
                   FROM emp
                   WHERE mgr IS NOT NULL)


SELECT e.ename,e.sal,e.deptno
FROM emp e,(SELECT AVG(sal) avgsal,deptno
            FROM emp
            GROUP BY deptno) a
WHERE e.deptno=a.deptno
AND e.sal>a.avgsal


SELECT ename,sal,deptno
FROM emp
WHERE sal>(SELECT AVG(sal)
           from emp)


SELECT ename,sal,deptno
FROM emp e
WHERE sal>(SELECT AVG(sal)
           FROM emp
           WHERE deptno=e.deptno)








INSERT INTO copy_emp (empno,hiredate,ename,deptno)
VALUES (1,'01-1月-00','aa',10)


INSERT INTO copy_emp 
VALUES (2,'bb',3000,NULL,20)


INSERT INTO copy_emp
SELECT empno,ename,sal,hiredate,deptno 
FROM emp
WHERE deptno=10


UPDATE copy_emp SET sal=(SELECT avg(sal)
                          FROM emp)
WHERE sal is NULL


UPDATE copy_emp SET sal= NULL
WHERE sal=(SELECT round(avg(sal),2)
           FROM emp) /*select * from emp where  sal <>1250 AND sal<>3000*/


/*SELECT * FROM emp WHERE ename LIKE '__A%'*/
/*SELECT * FROM emp WHERE ename IS NOT NULL*/
/*SELECT * FROM emp WHERE ename LIKE '__L_%'*/
/*SELECT ename,LOWER(ename) FROM emp WHERE LOWER(ename)='king'*/
/*SELECT INITCAP('aBc deF') FROM dual*/
/*SELECT CONCAT('abdsdfsdfasfsdfsd',concat('a','b')) FROM dual*/
/*SELECT SUBSTR('sjdflsdkf',3,1) FROM dual*/
/*SELECT LENGTHb('你好吗') FROM dual*/
/*\*SELECT INSTR('lvajdkksdk','a',3,1) FROM dual*\
SELECT INSTR('lvdjfksdjfn','n',LENGTH('lvdjfksdjfn'),1) FROM dual*/
/*SELECT * FROM emp WHERE INSTR(ename,'N',LENGTH(ename),1)=LENGTH(ename)*/
/*SELECT * FROM emp WHERE INSTR(ename,'A',3,1)=3*/
/*SELECT  SUBSTR('djkjfkdkd',3,4) FROM dual*/
/*SELECT * FROM emp WHERE ename LIKE '%N'*/
/*SELECT ename,SUBSTR(ename,-1,1) FROM emp WHERE SUBSTR(ename,-1,1)='N'*/
/*SELECT LPAD(ename,10,'*') FROM emp
*/
/*SELECT length(TRIM('     a b')) FROM dual*/


/*SELECT TRIM('m' FROM 'mqambmmq') FROM dual*/
/*SELECT replace(trim('c' FROM REPLACE('mqamqbmmq','mq','c') ),'c','mq')FROM dual*/
/*SELECT REPLACE (SELECT salary FROM emp WHERE */
/*SELECT INSTR('lsastll','l',1,3) FROM dual*/
/*SELECT ename ,INSTR(ename,'L',1,2)
FROM emp WHERE INSTR(ename,'L',1,2)>0 AND INSTR(ename,'L',1,3)=0*/
/*SELECT ROUND(55.926,-2) FROM dual*/
/*SELECT next_day(Sysdate,'month') FROM dual*/
/*SELECT ROUND (to_date('15-2月-14'),'month') FROM dual*/
/*SELECT EXTRACT(YEAR FROM to_date('15-2月-14'))
from dual*/
/*SELECT 12+'34' FROM dual*/
/* SELECT to_char(SYSDATE,'yyyy-mm-dd') FROM dual
*/
/*SELECT to_char(SYSDATE,'year-month-dd') FROM dual*/
/*SELECT to_char(SYSDATE,'year-month-day hh24:mi:ss am') FROM dual*/
/*SELECT to_char(SYSDATE,'d') FROM dual*/
/*SELECT to_char(SYSDATE,'yy') FROM dual*/
/*SELECT to_number('') FROM dual*/
/*SELECT  MONTHS_BETWEEN (SYSDATE,'01-1月-2001') FROM dual*/
/*SELECT ROUND(SYSDATE-to_date('01-1月-2001'))/7 FROM dual*/
/*SELECT sal,to_char(sal,'9999.99l') FROM emp*/
/*SELECT to_char(0.15,'0.00') FROM dual*/
/*SELECT NVL(to_char(comm),'no comm') FROM emp*/
/*SELECT NVL(to_char(sal),'nono') FROM emp*/
/*SELECT sal, (CASE 


WHEN sal<1000 THEN sal*1.1
WHEN sal>=100 AND sal<2000 THEN sal*1.3
WHEN sal>=2000 AND sal<3000 THEN sal*1.5
 
ELSE
  sal*2
  END) salllll  FROM emp*/
  
 /* SELECT ename,sal,(CASE TRUNC(sal/1000)
WHEN 0 THEN sal*1.1
WHEN 1 THEN sal*1.3
WHEN 2 THEN sal*1.5
ELSE
  sal*2
  END) salllll  FROM emp*/
 /* SELECT ename ,sal,sal/1000 salll FROM emp*/
/* SELECT ename,sal,
 DECODE(Trunc(sal/1000),
 0,sal*1.1,
 1,sal*1.3,
 2,sal*1.5,
 sal*2)sall
 FROM emp*/
 
/* 作业*/
/*SELECT * FROM emp,dept WHERE (dept.deptno=20 AND emp.job='CLERK')OR (dept.deptno=10 AND emp.job='MANAGER')
SELECT * FROM emp,dept WHERE emp.sal>2000 AND (dept.deptno=20 OR dept.deptno=10) AND( emp.job<>'MANAGER' AND emp.job<>'CLERK')
SELECT ename,job FROM emp WHERE INSTR(job,'MAN',1,1)>0


SELECT dname||'-'||loc FROM dept


SELECT ename,INSTR(ename,'S') position FROM emp


SELECT ename,INSTR(ename,'T',1,2) position FROM emp*/
/*多表查询*/


/*SELECT ename,dname,emp.deptno FROM emp,dept WHERE emp.deptno=dept.deptno*/
/*表别名*/
/*SELECT ename,dname,e.deptno FROM emp e,dept d WHERE e.deptno=d.deptno*/
/*所有字段加上表别名加快表的执行效率*/
/*SELECT e.ename,d.dname,e.deptno FROM emp e,dept d WHERE e.deptno=d.deptno*/
/*连接条件写前面
SELECT e.ename,d.loc FROM emp e,dept d WHERE e.deptno=d.deptno AND d.loc='NEW YORK'*/
/*账户登时下面的锁住神马的都不用写*/
/*SELECT e.first_name||' '||e.last_name,e.department_id ,d.department_name FROM employees e,departments d WHERE e.department_id=d.department_id
*/
/*SELECT e.first_name||' '|| e.last_name,l.city  FROM employees e,locations l,departments d  WHERE e.department_id=d.department_id AND d.location_id=l.location_id AND l.city='London'
*/
/*SELECT e.employee_id,d.department_id  FROM employees e,departments d WHERE e.department_id=d.department_id(+)*/
/*SELECT * FROM departments 27行*/
/*SELECT * FROM employees 107*/


/*SELECT e.employee_id,d.department_id  FROM employees e,departments d WHERE e.department_id(+)=d.department_id
*/
/*SELECT e.salary,CASE
(e.salary
WHEN 3000 THEN '***'


)
  FROM employees e 
*/


/*主键第一个，外键最后一个，规范
n个表,至少n-1个连接关系，笛卡尔积会不会变
*/


/*SELECT e.first_name||' '|| e.last_name,l.city, c.country_name FROM employees e,locations l,departments d,countries c  WHERE e.department_id=d.department_id AND d.location_id=l.location_id AND l.country_id=c.country_id AND c.region_id=1
*/
/*SELECT l.city,r.region_name,c.country_name FROM Locations l,regions r,countries c
 WHERE r.region_id=c.region_id AND r.region_name='Europe' AND l.country_id=c.country_id
 
SELECT l.city,r.region_name,c.country_name  FROM Locations l
JOIN regions r ON(r.region_name='Europe' )
JOIN Countries c ON(l.country_id=c.country_id AND r.region_id=c.region_id)*/


/*等号等值连接*/


/*SELECT  e.sal,s.grade,d.dname FROM emp e,salgrade s,dept d  WHERE e.deptno=d.deptno  AND e.sal BETWEEN s.losal AND s.hisal 
SELECT  e.sal,s.grade,d.dname FROM emp e JOIN salgrade s ON(e.sal BETWEEN s.losal AND s.hisal)
JOIN dept d ON(e.deptno=d.deptno )*/
/*外连接 有两张表a和d*/
/*左连接 不管b中有没有值，a中所有的值都要
右外连，不管a中有没有值，b中的所有值都要
专业术语
在LEFT OUTER JOIN中，会返回所有左边表中的行，即使在
右边的表中没有可对应的列值


RIGHT OUTER JOIN，会返回所有右边表中的行，即使在左
边的表中没有可对应的列值


全外连接


全外连接（FULL OUTER JOIN）主要功能是返回两表连接中等
值连接结果，及两个表中所有等值连接失败的记录
少的以d为准，所以在e中写（+）


天平砝码加在少的那边


*/


/*自连接
SELECT e.ename,d.deptno FROM emp e,dept d WHERE e.deptno(+)=d.deptno
SELECT e.ename,d.deptno FROM emp e JOIN dept d ON(e.deptno(+)=d.deptno)*/
/*SELECT  e.ename FROM emp e,emp em WHERE e.empno=em.mgr AND em.hiredate<e.hiredate
SELECT e.ename FROM emp e JOIN emp em ON(e.empno=em.mgr AND em.hiredate<e.hiredate)*/
SELECT * FROM emp
/*natural join 表中必须有相同的字段且数据类型相同
自然连接相同的字段不能写别名
*/
SELECT e.ename,d.dname ,deptno FROM emp e NATURAL JOIN dept d 


/*下午练习join ON
加表 见上面
已练习完


*/

CREATE VIEW emp_copy AS SELECT ename,sal FROM emp
DELETE emp_copy WHERE ename='aa'

Top_N

分页
SELECT ename,sal,r1,R2, ROWNUM r3 FROM (SELECT ename,sal,r1,ROWNUM R2 FROM (SELECT ename,sal,ROWNUM r1
FROM emp_copy ORDER BY sal DESC) WHERE ROWNUM<11) WHERE  R2>6