1. 确定表结构:desc
desc emp
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
EMPNO NUMBER(4)
ENAME VARCHAR2(10) Y
JOB VARCHAR2(9) Y
MGR NUMBER(4) Y
HIREDATE DATE Y
SAL NUMBER(7,2) Y
COMM NUMBER(7,2) Y
DEPTNO NUMBER(2) Y
2. 检索日期:默认是以”DD-MON-YY”的方式检索显示。如果想以其他方式显示则需要使用TO_CHAR函数。
select ename,TO_CHAR(hiredate,'YYYY-MM-DD') FROM emp
ENAME TO_CHAR(HIREDATE,'YYYY-MM-DD')
---------- ------------------------------
SMITH 1980-12-17
ALLEN 1981-02-20
WARD 1981-02-22
JONES 1981-04-02
3. 可以为检索出的列设置别名:注别名为双引号
select ename AS "姓名",sal AS "工资" From emp
姓名 工资
---------- ---------
SMITH 2000.00
ALLEN 1600.00
WARD 1250.00
JONES 2975.00
4. 处理NULL值:如果没有给某列提供数值则其默认为NULL
NVL(exp1,exp2)如果exp1为NULL则返回exp2
NVL2(exp1,exp2,exp3)如果exp1不是NULL则返回exp2,否则返回exp3
5. 显示有意义的查询结果可以连接字符串
SELECT ename||' '||'is a '||' '||job AS "Employee detail" FROM emp;
Employee detail
--------------------------
SMITH is a CLERK
ALLEN is a SALESMAN
WARD is a SALESMAN
JONES is a MANAGER
6. where子查询中使用日期
以下使符合默认日期格式的。
select ename,sal,hiredate FROM emp
2 WHERE hiredate > '01-1月-82';
ENAME SAL HIREDATE
---------- --------- -----------
MILLER 1300.00 1982-1-23
MARY 1983-10-20
以下是使用特定日期格式的
SELECT ename,sal,hiredate FROM emp
WHERE hiredate > TO_DATE('1982-01-01','YYYY-MM-DD');
ENAME SAL HIREDATE
---------- --------- -----------
MILLER 1300.00 1982-1-23
MARY 1983-10-20
7. 在where中使用IN操作符
SQL> SELECT ename,sal FROM emp WHERE sal IN (800,1250);
ENAME SAL
---------- ---------
WARD 1250.00
MARTIN 1250.00
使用IS NULL ,逻辑操作符。
8. order by 子句:默认为升序排列
使用多列排列
普通排列:
SELECT ename,sal,comm FROM emp WHERE deptno=30
order by sal ASC;
ENAME SAL COMM
---------- --------- ---------
JAMES 950.00
WARD 1250.00 500.00
MARTIN 1250.00 1400.00
TURNER 1500.00 0.00
ALLEN 1600.00 300.00
多列排列
SELECT ename,sal,comm FROM emp WHERE deptno=30
order by sal ASC,comm DESC;
ENAME SAL COMM
---------- --------- ---------
JAMES 950.00
MARTIN 1250.00 1400.00
WARD 1250.00 500.00
TURNER 1500.00 0.00
ALLEN 1600.00 300.00
注:还可选择非检索项,列别名,列位置编号进行排序。
Insert语句
9. 使用DEFAULT提供数据:存在默认值则使用默认值不存在默认值则使用NULL
INSERT INTO dept VALUES(60,'MARKET',DEFAULT)
10. 使用子查讯插入数据,用VALUES子句插入数据只能插入一行内容,而使用子查讯语句则可以插入多行内容。
INSERT INTO employee (empno,ename,sal,deptno)
SELECT empno,ename,sal,deptno FROM emp
WHERE deptno=20
直接装载
INSERT /*+APPEND*/INTO employee (empno,ename,sal,deptno)
SELECT empno,ename,sal,deptno FROM emp
WHERE deptno=20;
In direct-path INSERT, data is appended to the end of the table, rather than using existing space currently allocated to the table. As a result, direct-path INSERT can be considerably faster than conventional INSERT.
11. 多表插入
使用ALL提供多表插入
INSERT ALL
WHEN deptno=10 THEN INTO dept10
WHEN deptno=20 THEN INTO dept20
ELSE INTO other
SELECT * FROM emp
使用FIRST提供多表插入:如果数据满足先前条件并且已经被插入则不会被插入。
更新数据:
12. 更新多行数据
UPDATE emp SET sal=sal*1.1,comm=sal*0.1
WHERE deptno=20;
更新日期:
UPDATE emp SET hiredate=TO_DATE('1987-02-02','YYYY-MM-DD');
更新关联数据:使用子查询可以降低网络开销
UPDATE emp SET (job,sal,comm)=(
SELECT job,sal,comm FROM emp WHERE ename='SMITH')
WHERE ename='SCOTT';
复制表数据:当时用触发器时当表A数据发生改变,表B数据也需要修改
UPDATE employee SET deptno=
(SELECT deptno FROM emp WHERE empno=7788)
WHERE job=(SELECT job FROM emp WHERE empno=7788)
13. 删除数据:使用DELETE删除表的所有数据时并不会释放表所占用的空间,使用TRUNCATE TABLE不仅会删除表的数据,还会释放表段所所占用的空间。
TRUNCATE TABLE emp
14. 删除数据子查询
DELETE FROM emp WHERE empno=
(select empno FROM emp WHERE dname=’SALES’);
使用事务控制语句:
14. 设置保存点
savepoint a;
exec dbms_transaction.savepoint(‘a’);
15. 执行回退到a
Rollback to a;
exec dbms_transaction.rollback_savepoint(‘a’);
16. 取消全部事务
rollback;
exec dbms_transaction.rollback;
分组函数:
17. GROUP BY指定分组。分组函数用于显示,having 用于限制分组显示结果。
注:如果选择列表中同时包含列,表达式,分组函数,那么这些列表达式分组函数必须在group by 子句中。当同时存在GROUP BY ,ORDER BY,HAVING子句时,ORDER BY 子句放在末尾。
显示方差与标准偏差
SELECT variance(sal),stddev(sal) FROM emp;
VARIANCE(SAL) STDDEV(SAL)
------------- -----------
1473364.15719 1213.822127
取消重复值
SELECT count(distinct deptno) AS distinct_dept FROM emp;
DISTINCT_DEPT
-------------
3
多列分组每个部门,每个岗位的平均工资
SELECT deptno,job,avg(sal) FROM emp
GROUP BY deptno,job;
DEPTNO JOB AVG(SAL)
------ --------- ----------
20 CLERK 3000
30 SALESMAN 1400
20 MANAGER 3272.5
30 CLERK 950
10 PRESIDENT 5000
CLERK
30 MANAGER 2850
10 CLERK 1300
10 MANAGER 2450
20 ANALYST 3300
18.产生横向及纵向的统计结果使用ROLLUP和CUBE
ROLLUP在原有统计结果上增加横向统计结果
SELECT deptno,job,avg(sal) FROM emp
GROUP BY ROLLUP (deptno,job);
DEPTNO JOB AVG(SAL)
------ --------- ----------
CLERK
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 2916.66666
20 CLERK 3000
20 ANALYST 3300
20 MANAGER 3272.5
20 3190.83333
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 1400
30 1566.66666
2310.20833
CUBE在原有ROLLUP结果的基础上在增加纵向统计结果
SELECT deptno,job,avg(sal) FROM emp
GROUP BY CUBE (deptno,job);
DEPTNO JOB AVG(SAL)
------ --------- ----------
2310.20833
CLERK
CLERK 1750
ANALYST 3300
MANAGER 2857.5
SALESMAN 1400
PRESIDENT 5000
10 2916.66666
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 3190.83333
20 CLERK 3000
20 ANALYST 3300
20 MANAGER 3272.5
30 1566.66666
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 1400
GROUP SETS:合并多个分组的结果】
1.SELECT deptno,avg(sal) FROM emp GROUP BY deptno;
DEPTNO AVG(SAL)
------ ----------
30 1566.66666
20 3190.83333
10 2916.66666
2.SQL> SELECT job,avg(sal) FROM emp GROUP BY job;
JOB AVG(SAL)
--------- ----------
CLERK 1750
SALESMAN 1400
PRESIDENT 5000
MANAGER 2857.5
ANALYST 3300
3. SELECT job,avg(sal) FROM emp
GROUP BY GROUPING SETS(deptno,job);
JOB AVG(SAL)
--------- ----------
CLERK 1750
SALESMAN 1400
PRESIDENT 5000
MANAGER 2857.5
ANALYST 3300
1566.66666
3190.83333
2916.66666
连接查询:基于两个或两个以上表的查询。FROM子句指定两个或两个以上的表。当表之间存在重名字段时应加上表名为前缀。
使用相等连接执行主从查询
SELECT e.ename,e.sal,d.dname FROM emp e,dept d
WHERE e.deptno=d.deptno;
ENAME SAL DNAME
---------- --------- --------------
SMITH 3000.00 RESEARCH
ALLEN 1600.00 SALES
WARD 1250.00 SALES
JONES 3272.50 RESEARCH
MARTIN 1250.00 SALES
BLAKE 2850.00 SALES
CLARK 2450.00 ACCOUNTING
KING 5000.00 ACCOUNTING
TURNER 1500.00 SALES
JAMES 950.00 SALES
FORD 3300.00 RESEARCH
MILLER 1300.00 ACCOUNTING
使用不等连接执行查询
SELECT a.ename,a.sal,b.grade FROM emp a,salgrade b
WHERE a.sal BETWEEN b.losal AND b.hisal;
自连接:主要用于显示自参照表中的上下级关系,或层次关系.
SELECT manager.ename FROM emp manager ,emp worker
WHERE manager.empno=worker.mgr
AND worker.ename='BLAKE';
ENAME
----------
KING
左连接,右连接,全连接,内连接,交叉连接,自连接
左右连接实际说是我们联合查询的结果以哪个表为准~
SELECT e.ename,d.dname from emp e right join dept d
on e.deptno=d.deptno;
以dept表为准。Dept表中有的deptno才会被显示出来。
SELECT e.ename,d.dname from emp e left join dept d
on e.deptno=d.deptno;
以emp表为准。emp表中有的deptno才会被显示出来。
全连接 full join 或者 full outer join,为二个表中的数据都出来,这里演示效果与上一样!
http://www.cnblogs.com/eflylab/archive/2007/06/25/794278.html
内连接 inner join 或者 join;它为返回字段ID同时存在于表emp 和 dept中的记录
子查讯:
多行子查询:
IN匹配子查询中的任何一个即可。
SELECT ename,job,sal,deptno FROM emp WHERE job IN
(SELECT distinct job FROM emp WHERE deptno=10);
ALL匹配子查询中的所有值
SELECT ename,sal,deptno FROM emp WHERE sal>all
(SELECT sal FROM emp WHERE deptno=30);
ENAME SAL DEPTNO
---------- --------- ------
SMITH 3000.00 20
JONES 3272.50 20
KING 5000.00 10
FORD 3300.00 20
ANY匹配子查询中任何一个
SELECT ename,sal,deptno FROM emp WHERE sal>any
(SELECT sal FROM emp WHERE deptno=30);
ENAME SAL DEPTNO
---------- --------- ------
KING 5000.00 10
FORD 3300.00 20
JONES 3272.50 20
SMITH 3000.00 20
BLAKE 2850.00 30
CLARK 2450.00 10
ALLEN 1600.00 30
TURNER 1500.00 30
MILLER 1300.00 10
WARD 1250.00 30
MARTIN 1250.00 30
多列子查询:
成对比较、非成对比较
SELECT ename,job,sal,deptno FROM emp WHERE (deptno,job)=
(SELECT deptno,job FROM emp WHERE ename='SMITH');
ENAME JOB SAL DEPTNO
---------- --------- --------- ------
SMITH CLERK 3000.00 20
相关子查询:需要引用主查询表列的子查询语句
SELECT ename,job,sal,deptno FROM emp WHERE EXISTS
(SELECT 1 FROM dept WHERE dept.deptno=emp.deptno
AND dept.loc=' NEW YORK ');
ENAME JOB SAL DEPTNO
---------- --------- --------- ------
MILLER CLERK 1300.00 10
KING PRESIDENT 5000.00 10
CLARK MANAGER 2450.00 10
在FORM子句中使用子查询
显示高于部门平均工资的雇员信息
SELECT ename,job,sal FROM emp,
(SELECT deptno,avg(sal) avgsal FROM emp
GROUP BY deptno) dept
WHERE emp.deptno=dept.deptno AND sal>dept.avgsal;
ENAME JOB SAL
---------- --------- ---------
ALLEN SALESMAN 1600.00
JONES MANAGER 3272.50
BLAKE MANAGER 2850.00
KING PRESIDENT 5000.00
FORD ANALYST 3300.00
合并查询:合并多个SELECT语句的结果,使用UNION(取消重复值),UNION ALL(不取消重复值),INTERSECT,MINUS
对于LOB,VARRAY,嵌套表列无效
对于LONG列,无效
如果选择列表包含了表达式,则必须要为其指定列别名。
SELECT ename,sal,job FROM emp WHERE sal>2500
UNION
SELECT ename,sal,job FROM emp WHERE job='MANAGER';
ENAME SAL JOB
---------- --------- ---------
BLAKE 2850.00 MANAGER
CLARK 2450.00 MANAGER
FORD 3300.00 ANALYST
JONES 3272.50 MANAGER
KING 5000.00 PRESIDENT
复杂查询:
使用CASE表达式
SELECT ename,sal,CASE WHEN sal>3000 THEN 3
WHEN sal > 2000 THEN 2 ELSE 1 END GRADE
FROM emp WHERE deptno=10;
ENAME SAL GRADE
---------- --------- ----------
CLARK 2450.00 2
KING 5000.00 3
MILLER 1300.00 1
使用WITH子句重用子查询
查询部门工资总和高于雇员工资总和1/3的部门名及工资总和
SELECT dname,SUM(sal) AS dept_total FROM emp,dept
WHERE emp.deptno=dept.deptno GROUP BY dname
HAVING SUM(sal) >
(SELECT SUM(sal)*1/3 FROM emp,dept
WHERE emp.deptno=dept.deptno);
DNAME DEPT_TOTAL
-------------- ----------
RESEARCH 9572.5
SALES 9400
WITH summary AS(
SELECT dname,SUM(sal) AS dept_total FROM emp,dept
WHERE emp.deptno=dept.deptno GROUP BY dname
)
SELECT dname,dept_total FROM summary WHERE dept_total>
(SELECT SUM(dept_total)*1/3 FROM summary);
DNAME DEPT_TOTAL
-------------- ----------
RESEARCH 9572.5
SALES 9400