pl/sql sql语句

最新推荐文章于 2019-06-15 17:50:07 发布

ar7043

最新推荐文章于 2019-06-15 17:50:07 发布

阅读量687

点赞数

文章标签： sql manager insert join null delete

本文链接：https://blog.csdn.net/ar7043/article/details/3572570

版权

rel="File-List" href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml">

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

-------------

多列分组每个部门，每个岗位的平均工资

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

ar7043

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
pl/sql sql语句

Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4 <object classid
复制链接

扫一扫