Oracle数据库基础二：DQL

数据查询语言（DQL）：Data Query Language

• 列别名
  
  • 当SELECT子句中一个字段是函数或者表达式，那么在结果集中该字段的名字就是这个函数或表达式，可读性差，为此可以为列提供一个别名，这样在结果集中会使用该别名作为该字段的名字。应当养成一个好习惯，即:凡是表达式或函数都指定一个别名。
  • 别名本身不区分大小写，若希望别名区分大小写或者别名中包含空格，那么需 要使用双引号将其括起来。SELECT ename,sal+NVL(comm,0) "sal"FROM emp

查询条件

• OR,AND用于连接多个条件使用
  OR的优先级低于AND，所以若希望提高OR的优先级，可以使用括号。
  
  • 查看工资高于1000，并且职位是CLERK或者SALESMAN的员工?

SELECT ename,job,sal FROM emp WHERE sal>1000AND (job='SALESMAN'OR job='CLERK')

• LIKE用于模糊匹配字符串
  支持两个通配符:
  %:0-多个字符(任意个字符)
  _:单一的一个字符
  
  • 查看名字第二个字母是A的员工?

SELECT ename,sal,deptno FROM emp WHERE ename LIKE '_A%'

• IN(list) 和 NOT IN(listr)
  判断在列表中和不在列表中。IN,NOT IN常被用在子查询上使用。
  
  • 查看职位是CLERK和SALESMAN的员工?

SELECT ename,job,sal FROM emp WHERE job IN('CLERK','SALESMAN')

• BETWEEN…AND…
  判断在一个区间范围内
  
  • 查看工资在1500到3000之间的员工?

SELECT ename,sal FROM emp WHERE sal BETWEEN 1500 AND 3000

• 查看工资在1000-3000之间，20和30号部门名字中含有A的员工?

SELECT ename,sal,deptno,job FROM emp WHERE sal BETWEEN 1000 AND 3000 AND deptno IN (20,30) AND ename LIKE '%A%'

• ANY(list),ALL(list)
  ANY,ALL是配合>,>=,<,<=使用的
  ANY(list):大于列表之一，大于最小
  ALL(list):大于列表所有，大于最大
  ANY(list):小于列表之一，小于最大
  ALL(list):小于列表所有，小于最小
  ANY,ALL的列表中不会给定固定值，没有实际意义，它们通常配合子查询使用。

SELECT empno, ename, job, sal, deptno FROM empWHERE sal>ANY(3500,4000,4500)

• DISTINCT关键字

  • DISTINCT可以将结果集中指定字段值一样的记录去重。
    查看公司有哪些职位?
    SELECT DISTINCT job FROM emp

  • 可以按照多字段去重，那么这些字段值的组合没有重复记录出现在结果集中。
    SELECT DISTINCT deptno,job FROM emp

• ORDER BY子句
  ORDER BY用于对查询的结果集按照指定字段的值进行排序。
  排序分为:升序(ASC),降序(DESC).默认为升序
  ORDER BY子句必须定义在SELECT语句中的最后一个子句上。

  • 查看公司中工资的排名？
    SELECT ename,sal,job FROM emp ORDER BY sal DESC
  • 查看20号部门的工资排名?
    SELECT ename,sal,deptno FROM emp WHERE deptno=20 ORDER BY sal DESC
    多字段排序时，ORDER BY是有排序的优先级的，首先按照第一个字段先进行排序，当该字段中有记录值重复时，再将这些记录按照第二个字段排序。依此类推。
  • SELECT ename,deptno,sal FROM emp ORDER BY deptno DESC,sal DESC
  • 若排序的字段中含有NULL值，NULL被认定为最大值。

• 聚合函数
  聚合函数又称为分组函数，多行函数。聚合函数的作用是对结果集指定字段
  的值进行统计工作的。MAX(),MIN(),SUM(),AVG()求最大值，最小值，总和，平均值，还有一个是对记录数的统计COUNT()

  • 查看公司的最高工资是多少?
    SELECT MAX(sal) FROM emp
    SELECT MAX(sal),MIN(sal), SUM(sal),AVG(sal) FROM emp
  • 查看公司共多少人?
    SELECT COUNT(ename) FROM emp
    SELECT COUNT(*) FROM emp
    SELECT COUNT(1) FROM emp
  • 聚合函数忽略NULL值。
    SELECT SUM(comm),AVG(comm)FROM emp
    SELECT AVG(NVL(comm,0)) FROM emp

• 分组GROUP BY
  GROUP BY子句可以将结果集按照给定的字段值一样的记录进行分组。配合聚合函数使用可以完成分组统计工作。

  • 查看每个部门的平均工资?
    SELECT AVG(sal),deptno FROM emp GROUP BY deptno

  • 当SELECT中含有聚合函数时，那么凡不在聚合函数中的单独字段，都需要出现在GROUP BY子句中。

  • 查看每种职位的最高工资与最低工资是多少?
    SELECT MAX(sal),MIN(sal),job FROM emp GROUP BY job

  • GROUP BY按照多字段分组时，是将这些字段值组合一样的记录看做一组。

    • 统计同部门同职位的员工的各多少人?
      SELECT COUNT(*),deptno,job FROM emp GROUP BY deptno,job

    • 查看部门的平均工资，前提是该部门的平均工资高于2000
      SELECT AVG(sal),deptno FROM emp WHERE AVG(sal)>2000
      GROUP BY deptno
      上面的SQL语句会抛出异常，WHERE子句中是不能使用聚合函数做为过滤条件的，原因在于过滤的时机不对。WHERE是伴随查询表中数据逐行进行过滤使用
      的，用来确定结果集中可以查询出的条目数。而聚合函数是建立在查询出来的数据基础上进行统计才能得到结果，所以使用聚合函数过滤应当是在WHERE之后进行的。

• HAVING子句
  HAVING子句可以根据聚合函数进行过滤，并且是用来根据条件过滤 GROUP BY的分组。HAVING不能单独出现，不定义GROUP BY子句不能单独出现HAVING子句。

  • 查看部门的平均工资，前提是该部门的平均工资高于2000
    SELECT AVG(sal),deptno FROM emp GROUP BY deptno
    HAVING AVG(sal)>2000

  • 查看平均工资高于2000的那些部门的最高工资是多少?
    SELECT MAX(sal),deptno FROM emp GROUP BY deptno
    HAVING AVG(sal)>2000

关联查询

关联查询的结果集中的字段来自多张表。这就需要在查询的时候联合多张表进行查询，根据给定的连接条件将表与表的数据建立对应关系，再分别从这些记录中提取要查询的字段来形成结果集中的每条记录。

• 查看每个员工的名字以及其所在部门的名字?

SELECT ename,dname FROM emp,dept WHERE emp.deptno=dept.deptno

• 当在关联查询中要查询的字段在这些表中都出现时，需要使用表名来明确字段所属表。可以为表名取一个别名来简化复杂度。

SELECT e.ename,e.deptno,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno

• 在进行关联查询时若有过滤要求，那么过滤条件必须与连接条件同时成立。
  
  • 查看在NEW YORK工作的员工都有谁?

SELECT e.ename,e.deptno,d.dname,d.loc FROM emp e,dept dWHERE e.deptno=d.deptno AND d.loc='NEW YORK'

关联查询中不指定连接条件或者连接条件无效时，会产生笛卡尔积，
这通常是一个无意义的结果集。

SELECT e.ename,d.dname FROM emp e, dept d

2表一个连接条件，N表查询至少要有N-1个连接条件。

内连接

内连接也是用来完成关联查询的。采用JOIN…ON…的形式关联多表。

• 查看每个员工的名字以及其所在部门名称?

普通关联写法:SELECT e.ename,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno内连接写法:SELECT e.ename,d.dname FROM emp e JOIN dept d ON e.deptno = d.deptno不满足关联条件的记录不会被查询出来。

外链接

外链接在关联查询中除了可以将满足连接条件的记录查询出来之外，还可以将不满足连接条件的记录也列出来。
外链接分为:
左外连接，右外连接，全外连接
左外连接:以JOIN左侧表做为驱动表,该表记录都要查询出来，当某条记录不满足连接条件时，那么在结果集中该条记录来自JOIN右侧表的字段的值全部为NULL。

2种写法:SELECT e.ename,e.job,e.sal, d.dname,d.loc FROM emp e LEFT|RIGHT|FULL OUTER JOIN dept d ON e.deptno=d.deptnoSELECT e.ename,d.dname FROM emp e,dept d WHERE e.deptno(+)=d.deptno

自连接

自连接指的是当前表的一条数据可以对应当前表自己的多条数据。自连接是为了解决数据的属性相同，但是本身又存在上下级关系的树状结构数据。
- 查看每个员工以及其领导的名字?

SELECT e.ename,m.ename FROM emp e,emp m WHERE e.mgr=m.empno(+)SELECT e.ename,m.ename FROM emp e LEFT OUTER JOIN emp m ON e.mgr=m.empno

视图VIEW

视图是数据库对象之一，在SQL语句中体现的角色与表一致。但是视图并非一张 真实存在的表，它只是一个查询语句对应的结果集。

CREATE VIEW v_emp_10ASSELECT empno, ename, sal, deptno FROM emp WHERE deptno = 10DESC v_emp_10

视图对应的子查询中的字段可以指定别名，这样该视图对应的字段名就是这个别名。当一个字段是函数或者表达式，那么该字段必须指定别名

CREATE OR REPLACE VIEW v_emp_10ASSELECT empno id,ename name,       sal salary,deptnoFROM empWHERE deptno=10

视图分类

• 视图根据对应的子查询不同，分为简单视图和复杂视图
  简单视图:对应的子查询不含有函数，表达式，分组，去重，关联查询。
  除了简单视图就是复杂视图

• 简单视图可以进行DML操作，对该视图的操作就是对该视图数据来源的基础表进行的操作。

• 复杂视图不允许进行DML操作。对简单视图进行DML操作也不能违反基础表的
  约束条件。对视图进行DML操作，视图对基础表操作时，只能对视图可见的字段进行。

  INSERT INTO v_emp_10 (id,name,salary,deptno) VALUES (1001,’JACK’,3000,10)

  UPDATE v_emp_10 SET salary=4000 WHERE id=1001

  DELETE FROM v_emp_10 WHERE id=1001

• 对视图的不当DML操作会污染基表数据即:对视图进行DML操作后，视图对基础表对应数据进行该DML操作，但是操作后视图却对该记录不可见。

INSERT INTO v_emp_10 (id,name,salary,deptno) VALUES (1001,'JACK',3000,20)UPDATE v_emp_10 SET deptno=20DELETE不会产生污染现象。DELETE FROM v_emp_10 WHERE deptno=20

• 为视图添加检查选项，可以避免对视图操作而导致的对基表的数据污染。
  WITH CHECK OPTION
  该选项要求对视图进行DML操作后，该记录必须对视图可见。

CREATE OR REPLACE VIEW v_emp_10ASSELECT empno id,ename name,       sal salary,deptnoFROM empWHERE deptno=10WITH CHECK OPTION

• 只读选项
  WITH READ ONLY
  只读选项要求对视图仅能进行查询操作不能进行任何DML操作。

CREATE OR REPLACE VIEW v_emp_10ASSELECT empno id,ename name,       sal salary,deptnoFROM empWHERE deptno=10WITH READ ONLY

常用的数据库的数据字典

• USER_OBJECTS：记录用户创建过的所有数据库对象

SELECT object_name,object_type  FROM user_objectsWHERE object_type='VIEW' AND object_name LIKE '%FANCQ'

• USER_VIEWS:专门记录曾经创建过的视图信息

SELECT view_name,text FROM user_views

• USER_TABLES:专门记录曾经创建过的表的信息

SELECT table_name FROM user_tables

删除视图

DROP VIEW v_emp_10

创建复杂视图

创建一张视图，包含员工工资及相关部门信息
包含:每个部门的平均工资，最大，最小，工资总和，
以及对应的部门名称，部门编号。

CREATE OR REPLACE VIEW v_emp_salinfoASSELECT AVG(e.sal) avg_sal,       MAX(e.sal) max_sal,       MIN(e.sal) min_sal,       SUM(e.sal) sum_sal,       d.deptno,d.dname,d.locFROM emp e,dept dWHERE e.deptno=d.deptnoGROUP BY d.deptno,d.dname,d.loc

• 查看哪些员工的工资高于其所在部门平均工资?

SELECT e.ename,e.sal,e.deptno FROM emp e,v_emp_salinfo vWHERE e.deptno=v.deptno AND e.sal>v.avg_sal

序列SEQUENCE

序列是数据库对象之一，作用是根据指定的规则生成一系列数字。通常使用序列生成的数字是为表中的主键字段提供值使用。

CREATE SEQUENCE seq_emp_idSTART WITH 1INCREMENT BY 1

• 序列支持两个伪列:

  • NEXTVAL:获取序列的下一个数字，如果是新创建的序列，那么会从START WITH开始返回。之后则是用上次生成的数字加上步长来得到本次生成的数字返回。需要注意，序列是不能后退的。并且不受事务控制。

  • CURRVAL:获取序列最后生成的数字，新创建的序列至少调用NEXTVAL生成一个数字后才可以使用。CURRVAL不会导致序列步进。

SELECT seq_emp_id.NEXTVAL FROM dualSELECT seq_emp_id.CURRVAL FROM dual

• 使用序列为EMP表主键字段提供值

INSERT INTO emp(empno,ename,job,sal,deptno)VALUES(seq_emp_id.NEXTVAL,'JACK','CLERK',3000,10)SELECT * FROM emp

• 删除一个序列
  DROP SEQUENCE seq_emp_id

• 序列的数据字典
  SELECT * FROM USER_SEQUENCES

索引INDEX

索引是数据库对象之一，作用是提高查询效率索引的创建时是数据库自行完成的，并且数据库会在适当的时候自动使用索引。

CREATE INDEX idx_emp_ename  ON emp(ename)

索引适用范围

• 经常出现在WHERE中和ORDER BY中的字段要添加索引。
• 经常出现在DISTINCT后面的字段也可以添加索引。
• 需要注意，对于字符串类型字段，若在WHERE中使用LIKE进行过滤时，是不会用到索引的。

PLSQL

PL/SQL也是一种程序语言，叫做过程化SQL语言（Procedural Language/SQL）。PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点，所以PL/SQL把数据操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算。PL/SQL 只有 Oracle 数据库有。 MySQL 目前不支持 PL/SQL

• 变量声明：
  declare myapple number(10);

• 标识符

  • 变量必须以字母开头，其后可以跟字母，数字 _ $和#
  • 变量长度最多30个字符
  • 变量名中不能有空格

• 变量类型
  目前oracle中varchar和varchar2没区别
  除了Number，varchar2，char，date等常用外
  还有boolean 和对象类型

• 数据类型转换
  To_char():number,date转varchar2
  To_date:将char转date
  To_number:将char转number
  自动类型转换：number->char

基本程序结构和语句

IF

• IF THEN 结构

     IF condition THEN        S;    END IF;

• IF THEN ELSE

IF condition THEN   S1; ELSE    S2;END IF;

• IF THEN ELSIF ELSE

IF(boolean_expression 1)THEN    S1; -- Executes when the boolean expression 1 is true ELSIF( boolean_expression 2) THEN   S2;  -- Executes when the boolean expression 2 is true ELSIF( boolean_expression 3) THEN   S3; -- Executes when the boolean expression 3 is true ELSE    S4; -- executes when the none of the above condition is true END IF;

实例

IF THEN 结构实例

DECLARE   a number(2) := 10;BEGIN   a:= 10;  -- check the boolean condition using if statement    IF( a < 20 ) THEN      -- if condition is true then print the following        dbms_output.put_line('a is less than 20 ' );   END IF;   dbms_output.put_line('value of a is : ' || a);END;

IF THEN ELSIF ELSE 结构实例

DECLARE   a number(3) := 100;BEGIN   -- check the boolean condition using if statement    IF( a < 20 ) THEN      -- if condition is true then print the following        dbms_output.put_line('a is less than 20 ' );   ELSE      dbms_output.put_line('a is not less than 20 ' );   END IF;   dbms_output.put_line('value of a is : ' || a);END;

IF THEN ELSE 结构实例

DECLARE   a number(3) := 100;BEGIN   -- check the boolean condition using if statement    IF( a < 20 ) THEN      -- if condition is true then print the following        dbms_output.put_line('a is less than 20 ' );   ELSE      dbms_output.put_line('a is not less than 20 ' );   END IF;   dbms_output.put_line('value of a is : ' || a);END;

循环结构

• LOOP

LOOP  Sequence of statements;END LOOP;

实例

DECLARE   x number := 10;BEGIN   LOOP      dbms_output.put_line(x);      x := x + 10;      IF x > 50 THEN         exit;      END IF;   END LOOP;   -- after exit, control resumes here   dbms_output.put_line('After Exit x is: ' || x);END;

• FOR

FOR counter IN initial_value .. final_value LOOP   sequence_of_statements;END LOOP;

实例

DECLARE   a number(2);BEGIN   FOR a in 10 .. 20 LOOP       dbms_output.put_line('value of a: ' || a);  END LOOP;END;

• WHILE

语法

WHILE condition LOOP   sequence_of_statementsEND LOOP;

实例

DECLARE   a number(2) := 10;BEGIN   WHILE a < 20 LOOP      dbms_output.put_line('value of a: ' || a);      a := a + 1;   END LOOP;END;

条件结构

• CASE

CASE selector        WHEN 'value1' THEN S1;        WHEN 'value2' THEN S2;        WHEN 'value3' THEN S3;        ...        ELSE Sn;  -- default case    END CASE;

实例

DECLARE   grade char(1) := 'A';BEGIN   CASE grade      when 'A' then dbms_output.put_line('Excellent');      when 'B' then dbms_output.put_line('Very good');      when 'C' then dbms_output.put_line('Well done');      when 'D' then dbms_output.put_line('You passed');      when 'F' then dbms_output.put_line('Better try again');      else dbms_output.put_line('No such grade');   END CASE;END;

存储过程

第一个存储过程

create or replace procedure proc1(para1 varchar2,para2 out varchar2,para3 in out varchar2) asv_name varchar2(20);begin v_name :='zhangsf'; para3 := v_name;dbms_output.put_line('para3:'||para3);end;

上面就是一个最简单的存储过程。一个存储过程大体分为这么几个部分:

• 创建语句：create or replace procedure 存储过程名
  如果没有or replace语句，则仅仅是新建一个存储过程。如果系统存在该存储过程，则会报错。Create or replace procedure 如果系统中没有此存储过程就新建一个，如果系统中有此存储过程则把原来删除掉，重新创建一个存储过程。
• 存储过程名定义：包括存储过程名和参数列表。参数名和参数类型。参数名不能重复， 参数传递方式:IN, OUT, IN OUT
• IN 表示输入参数，按值传递方式。
• OUT 表示输出参数，可以理解为按引用传递方式。可以作为存储过程的输出结果，供外部调用者使用。
• IN OUT 即可作输入参数，也可作输出参数。
  参数的数据类型只需要指明类型名即可，不需要指定宽度。
  参数的宽度由外部调用者决定。
  过程可以有参数，也可以没有参数
• 变量声明块：紧跟着的as (is )关键字，可以理解为pl/sql的declare关键字，用于声明变量。
  变量声明块用于声明该存储过程需要用到的变量，它的作用域为该存储过程。另外这里声明的变量必须指定宽度。遵循PL/SQL的变量声明规范。
• 过程语句块：从begin 关键字开始为过程的语句块。存储过程的具体逻辑在这里来实现。
• 异常处理块：关键字为exception ，为处理语句产生的异常。该部分为可选
• 结束块：由end关键字结果。

存储过程的参数传递方式

存储过程的参数传递有三种方式:IN,OUT,IN OUT .
IN 按值传递，并且它不允许在存储过程中被重新赋值。如果存储过程的参数没有指定存参数传递类型，默认为IN

create or replace procedure proc1(  para1 varchar2,  para2 out varchar2,  para3 in out varchar2) as  v_name varchar2(20);begin  para1 := 'aaa';  para2 := 'bbb';  v_name :='zhangsf';  para3 := v_name  dbms_output.put_line('para3:'||para3);  null;end;

OUT 参数：作为输出参数，需要注意，当一个参数被指定为OUT类型时，就算在调用存储过程之前对该参数进行了赋值，在存储过程中该参数的值仍然是null.

首先，我们要明白，我们无法在存储过程的定义中指定存储参数的宽度，也就导致了我们无法在存储过程中控制传入变量的宽度。这个宽度是完全由外部传入时决定的。
我们再来看看OUT类型的参数的宽度。

create or replace procedure proc2(para1 varchar2,para2 out varchar2,para3 in out varchar2) asv_name varchar2(2);beginpara2 :='aaaaaaaaaaaaaaaaaaaa';end;

–调用proc2
var p1 varchar2(1);
var p2 varchar2(1);
var p3 varchar2(1);
exec :p2 := ‘a’;
exec proc1(:p1,:p2,:p3);

在该过程中，p_para2被赋予了20个字符a.
而在外部的调用过程中，p2这个参数仅仅被定义为varchar2(1).
而把p2作为参数调用这个过程，却并没有报错。而且它的真实值就是20个a

执行这个过程，仍然正确执行。

可见，对于IN参数，其宽度是由外部决定。
对于OUT 和IN OUT 参数，其宽度是由存储过程内部决定。
因此，在写存储过程时，对参数的宽度进行说明是非常有必要的，最明智的方法就是参数的数据类型使用%type。这样双方就达成了一致。

参数的默认值

存储过程的参数可以设置默认值

create or replace procedure procdefault(  p1 varchar2,  p2 varchar2 default 'mark') asbegin  dbms_output.put_line(p2);end;

mark
可以通过default 关键字为存储过程的参数指定默认值。在对存储过程调用时，就可以省略默认值。
需要注意的是：默认值仅仅支持IN传输类型的参数。OUT 和 IN OUT不能指定默认值

对于有默认值的参数不是排在最后的情况。

create or replace procedure procdefault2(  p1 varchar2 default 'remark',  p2 varchar2) asbegin  dbms_output.put_line(p1);end;

第一个参数有默认值，第二个参数没有。如果我们想使用第一个参数的默认值时
exec procdefault2(‘aa’);
这样是会报错的。
那怎么变呢？可以指定参数的值。

SQL> exec procdefault2(p2 =>’aa’);
remark
这样就OK了，指定aa传给参数p2

存储过程内部块

内部块
我们知道了存储过程的结构，语句块由begin开始，以end结束。这些块是可以嵌套。在语句块中可以嵌套任何以下的块。

Declare .. beign ... exception ... end;create or replace procedure innerBlock(  p1 varchar2) as  o1 varchar2(10) := 'out1';begin  dbms_output.put_line(o1);  declare    inner1 varchar2(20);  begin    inner1 := 'inner1';    dbms_output.put_line(inner1);    declare      inner2 varchar2(20);    begin      inner2 := 'inner2';      dbms_output.put_line(inner2);    end;    exception      when others then        null;  end;end;

需要注意变量的作用域。

触发器

传送:
http://www.cnblogs.com/xcnblog3035/p/5236877.html