oracle pns配置,oracle笔记大全

一、in的用法。

select ename,deptno from emp where sal in(800,1250)

in 里面可以是一个结果集，是一个sql语句.

当然也可用not in

二、操作符优先级，* / + - 用括号可以改变这种优先级，

在sql中 .... and (d_isactve is null or d_isactive ==0)

这个在项目有遇到过，一个字段需要两种不同的条件判断，

用可以加个括号。

三、like

%表示零个或者多个字符，_表示一个字符，均可以表示汉字和字母。

查询first的第三个字母是a的员工

select * from emp where first like '__a%';

使用ESCAPE 标识符来查找带特殊符号的字符串

如果需要模糊查询的字符串中包含了作为通配符的”%”和”_”，

在这种情况下需要使用escape标识符来说明哪些是字符串的字符，哪些是通配符号

select * from feng where name like '%\%f%' escape '\' ;

select * from feng where name like '%\%f\_%' escape '\';

--escape ‘\‘ 表示‘’\’后边的是字符串中的内容, ‘’\’ 可以用其它的字符来代替

四、空值，一定是 is null 或者 is not NULL.绝对没有==null这种东西。

五、逻辑运算符：and or not.用括号可以改变其优先级，在项目中遇到过。

not ->and -->or 运算顺序。用括号可以改变其优先级，在项目中遇到过。这点十分重要，

一定要谨记。

select * from emp where dep = 't' or dep = 'q' and salary >= 1500;

一定要明白这个结果是什么，先运算and ，再运算Or。

数学运算符和逻辑运算符，而且括号都可以改变运算顺序，十分好的。

六、著名的 order by

1、它一定是出现select语句的最后，有什么其它条件肯定要插在它的前面。

2、使用ORDER BY 子句根据某个字段所得结果记录排序ASC: 升序,缺省DESC: 降序

3、order by 后面所根据的字段，可以使用列的别名排序。

4、对多字段进行的方式，十分重要，十分好。

在进行排序的时候，也可以按照多个字段进行排序，

但是多字段排序要注意，必须对每个字段设置排序方式，

也就是说，不管是ASC还是DESC，都只是针对单个字段的设置，

所以在多字段排序中，需要设置每个字段的排序方式，

而且每个字段的排序方式不同也是可以的。

SELECT last_name, department_id, salary

FROM employees

ORDER BY department_id desc, salary asc;

首先会根据部门ID把部门结果按照部门的降序排列，

然后在每一个相同的ID部门里面，其工资又是按照升序

排列的，十分好的，十分有效。

六、函数。

当你寻找不到答案的时间，就去看官方的文档 。

常用的分组函数(各个数据库通用)

AVG ([DISTINCT|ALL]n)

COUNT ({ *|[DISTINCT|ALL]expr})--返回的是不重复的，这个字段非空的集合。

MAX ([DISTINCT|ALL]expr)

MIN ([DISTINCT|ALL]expr)

SUM ([DISTINCT|ALL]n)

--**********************************************************************************************************

--聚合函数

--在数字类型数据使用AVG and SUM 函数

--AVG：计算平均值

--SUM：计算总和

--输出员工工资的总和,工资的平均值

select sum(salary),avg(salary)

from employees

--输出员工表10号部门员工工资的总和，工资的平均值

select sum(salary),avg(salary)

from employees

where department_id=10

--MIN and MAX适用于任何数据类型

--MIN： 计算最小值

--MAX：计算最大值

--输出员工表中工资的最大值和最小值

select max(salary),min(salary) from employees

--输出20号部门中工资的最大值和最小值

select max(salary),min(salary) from employees where department_id=20

--COUNT(*)返回表中所有符合条件的记录数

--查询有多少员工

select count(*) from employees

--COUNT(字段) 返回所有符合条件并且字段值非空的记录

--带条件查询

--查询10号部门有多少员工

select count(employee_id) from employees

select count(employee_id) from employees where department_id=10

--count(distinct(expr))返回不重复的，非空值的数量

--查询10号部门的工种数量

select count(distinct(job_id)) from employees where department_id=10

--分组函数在计算时省略列中的空值

--计算30号部门员工工资的平均工资

select avg(salary)

from employees where department_id=30

--NVL函数迫使分组函数包括空值

--计算30号部门员工工资的平均工资

select avg(nvl(salary,0))

from employees where department_id=30

--*****************************************************************************************************************

--分组的语法结构

SELECTcolumn, group_function

FROMtable

[WHEREcondition]

[GROUP BYgroup_by_expression]

[ORDER BYcolumn]; order by 始终是在最后的。

--查询每个部门工资的最大值,最小值

需求:输出结果如下

department_id max(salary),min(salary)

10 5000 1000.00

20 6000 1000.00

30.....

select department_id,max(salary),min(salary)

from employees

group by department_id --你会发现select后面的必须出现在group by后面。

order by department_id asc

--这个语句太厉害了，找出的各个部门中，所有工资的最大值，和最小值。

--pw

--使用groupby子句的注意事项

--1、出现在SELECT列表中的字段，如果出现的位置不是在组函数中，那么必须出现在GROUP BY子句中

select department_id,job_id,max(salary),min(salary)

from employees

group by department_id,job_id --这里必须加上job_id，否则出错，也就是说在select之后的字段，必须要在 group by后面出现。

--2、在GROUP BY 子句中出现的字段，可以不出现在SELECT列表中

select max(salary),min(salary)

from employees

group by department_id

--对多列分组

--一查询部门编号和工种并按 部门编号 和 工种分组

select department_id,job_id

from employees

group by department_id,job_id

--不能在 WHERE 子句中限制组.

--限制组必须使用 HAVING 子句.

--不能在 WHERE 子句中使用组函数

语法结构:

--使用HAVING子句对分组的结果进行限制

SELECTcolumn, group_function

FROMtable

[WHEREcondition]

[GROUP BYgroup_by_expression]

[HAVINGgroup_condition]

[ORDER BYcolumn];

--select语句的执行流程

* 先执行where子句，对数据进行过滤

* 过滤后的数据再用group by子句分组

* 分组后的数据再用 HAVING子句进行组函数过滤

* 最后,对查询的数据排序。

--按部门进行分组,输出部门id,部门的平均工资,要求平均工资>2000

select department_id,avg(salary)

from employees

group by department_id

having avg(salary)>2000

order by department_id

--按部门进行分组,输出部门id,部门的平均工资,要求平均工资>2000，并且部门不为null,并且不是10号部门

select department_id,avg(salary)

from employees

where department_id is not null and department_id<>10

group by department_id

having avg(salary)>2000

order by department_id

大多数集合函数都能在计算时消除空值;COUNT函数则属于例外。

对包含空值的一个列使用COUNT函数，空值会从计算中消除。

但假如COUNT函数使用一个星号，它就计算所有行，而不管是否存在空值。

如果希望COUNT函数对给定列的所有行(包括空值)进行计数，

请使用ISNULL函数。ISNULL函数会将空值替换成有效的值。

事实上，对集合函数来说，如果空值可能导致错误结果，

ISNULL函数就非常有用。记住在使用一个星号时，COUNT函数会对所有行进行计算。

---多表连接和子查询。

--自连接与基本连接是一样的，只是把它看成两张表就OK了。

连接的概念:

连接分为条件连接、等值连接和自然连接三种。

1、条件连接就是在多个表的笛卡尔积中选取满足条件的行的连接，例如 select * from A,B where A.a > A.b 之类的有条件的查询。

2、等值连接就是特殊的条件连接，当条件为某字段=某字段时，即为等值连接。如SELECT ename,sal,dname FROM emp,dept WHERE emp.deptno=dept.deptno;

3、自然连接是一种特殊的等值连接，他要求多个表有相同的属性字段，然后条件为相同的属性字段值相等，

最后再将表中重复的属性字段去掉，即为自然连接。如A中a,b,c字段，B中有c,d字段，

则select * from A natural join B 相当于 select A.a,A.b,A.c,B.d from A.c = B.c 。

内连接与等值连接的区别：

内连接：两个表(或连接)中某一数据项相等的连接称为内连接。等值连接一般用where字句设置条件，内连接一般用on字句设置条件，但内连接与等值连接效果是相同的。

内连接与等值连接其实是一回事情(等效)。

经常有人会问到select a.id,b.name from a,b where a.id=b.pid 与

select a.id,b.name from a inner join b on a.id=b.pid 有什么区别，哪个效率更高一些。

实际上一回事情了。只是内连接是由SQL 1999规则定的书写方式。两个说的是一码事。

--支持SQL1999的新连接标准

包括以下新的TABLE JOIN的句法结构

CROSS　JOIN——它在两个表格中创建了一个笛卡尔积，就象是在Oracle8i中没写WHERE时一样

NATURAL JOIN——它通过从WHERE子句中自动连接标准来改善SQL的稳定性，自然连接。

USING子句——它可以通过名字来具体指定连接

ON子句——这个句法允许在两个表中为连接具体指定列名

LEFT OUTER JOIN——它返回表格中左边的行和右边的数值，如果没有搭配的行的话，则返回空

RIGHT OUTER JOIN——它返回表格中右边的行和左边的数值，如果没有搭配的行的话，则返回空

FULL OUTER JOIN——它返回的是两个表格中所有的行，用空填满每一个空格。这在Oracle8i中则没有相应的此种句法

CROSS JOIN产生了一个笛卡尔积，就象是在连接两个表格时忘记加入一个WHERE子句一样，没有什么用处。

NATURAL JOIN 子句基于两个表中列名完全相同的多个列产生连接，必须要有两个重名的字段，才行。平时用的少。

从两个表中选出连接列的值相等的所有行

如果两个列的名称相同,但是具有不同的数据类型，则查询会返回一个错误

自然连接的条件是基于表中所有同名列的等值连接

为了设置任意的连接条件或者指定连接的列，需要使用ON子句

连接条件与其它的查询条件分开书写

使用ON 子句使查询语句更容易理解

select department_name, city

fromdepartment d JOIN location l

ON (d.location_id = l.id);

--子查询

在使用select语句查询数据时,有时候会遇到这样的情况，在where查询条件中的限制条件不是一个确定的值，而是一个来自于另一个查询的结果。

SELECTselect_list

FROMtable

WHEREexpr operator

(SELECTselect_list

FROMtable);

1、子查询在主查询前执行一次--且记，它先执行一次。注意执行顺序，是它先执行一次，然后另外一个才执行。

2、主查询使用子查询的结果

--注意事项

使用子查询的注意事项

1、子查询要用括号括起来

2、将子查询放在比较运算符的右边(增强可读性)

3、只有在执行Top-N分析时，子查询中才需要使用Order by子句，也就是分页。

4、在Oracle8i之前的版本中，子查询不能包含Order by子句

5、对单行子查询使用单行运算符

6、对多行子查询使用多行运算符

--子查询的种类

单行单列子查询：只包含一个字段的查询，返回的查询结果也只包含一行数据，一列数据。

多行单列子查询：只包含了一个字段，但返回的查询结果可能多行或者零行，但只有一列。

多列子查询：包含多个字段的返回，查询结构可能是单行或者多行。

--单行查询，用= => <> 等这个比较符号，因为我们已经知道结果只有一个，如果不确定有几个的话，要用多选子查询运算符号。

SELECT employee_id, last_name

FROM employees

WHERE salary =

(SELECT MIN(salary)

FROM employees

GROUP BY department_id);

ERROR at line 4:

ORA-01427: single-row subquery returns more than one row

错误原因：对多行子查询使用了单行比较操作符

--多行子查询

1、返回多行

2、使用多行比较运算符

IN --与列表中的任意一个值相等

ANY -- 与子查询返回的任意一个值比较

ALL --与子查询返回的每一个值比较

SELECT employee_id, last_name, job_id, salary

FROM employees

WHERE salary < ANY

(SELECT salary

FROM employees

WHERE job_id = 'IT_PROG')

AND job_id <> 'IT_PROG';

ANY 通常与大小写符号搭配使用，不单独使用。可以是ANY ：

分别代表着下面的含义

>ANY 大于子查询数据中的最小值

SELECT employee_id, last_name, job_id, salary

FROM employees

WHERE salary < ALL

(SELECT salary

FROM employees

WHERE job_id = 'IT_PROG')

AND job_id <> 'IT_PROG';

ALL 通常与大小写符号搭配使用，不单独使用。可以是ALL ：分别代表着下面的含义

>ALL 指大于子查询数据中的最大值

--其实，对有些条件，连接查询和子查询是可以互换的。

4.查询在loc为NEW YORK的部门工作的员工的员工号，ename,deptno,job(使用连接查询,子查询两种查询方式)

--子查询

select empno,ename,deptno,job from emp where deptno =

(select deptno from dept where loc='NEW YORK')

--连接查询

select e.empno,e.ename,d.deptno,e.job from emp e join dept d on(e.deptno=d.deptno) and d.loc='NEW YORK'

--总结：在写复杂的SQL语句的时间，要学会分解，把复杂的东西分解到某个子条件中。然后再去查询。

--研究一下SQL语句的执行效率问题？

---事务问题

先来谈一上JDBC的事务

获取连接

Connection conn = null;

...

try{

1、设置连接的提交方式为非自动提交

con.setAutoCommit(false);

创建statement对象

Statement stmt = conn.createStatement();

执行insert

stmt.executeUpdate(insertsql);

2、提交。

conn.commit();

}catch{

3、回滚

conn.rollback();

}finally{

关闭资源。

}

--使用子查询创建表

--******************************************************************************************

使用子查询创建表的语法

CREATE TABLE table

[column(, column...)]

AS subquery;

--带数据的

create table departments01

as

select department_id,department_name,manager_id,location_id from departments

--不带数据的

create table departments02

as

select department_id,department_name,manager_id,location_id from departments where 1>2

select department_id,department_name,manager_id,location_id from departments where 1=1

--不省略字段列表

create table departments03

(

id,

name,

mid,

lid

)

as

select department_id,department_name,manager_id,location_id from departments where 1>2

----省略字段列表

create table departments04

as

select department_id id,department_name name,manager_id mid,location_id lid from departments

--******************************************************************************************

--修改表中的字段(了解)

--在test表中增加字段

alter table test

add sex varchar2(30)

--修改test表中的字段，当表中没有字段的时间，你想怎么做就怎么做，但是当有数据的时间，并且你要缩小就要小心了。增大肯定没有问题。

alter table test

modify sex varchar2(5)

--删除表中的字段

alter table test

drop column sex

--******************************************************************************************

--删除表的内容

1、TRUNCATE TABLE 语句

清除表中所有的记录，delete可以选择删除表中的一部分

是DDL语句，不可以回滚，delete可以使用rollback回滚，放弃修改。

释放表的存储空间，delete不释放空间

2、 是删除数据的方法之一

3、TRUNCATE TABLE table_name;

--删除departments04表中的数据

truncate table departments04

--******************************************************************************************

--删除表

DROP TABLE table_name;

--删除departments04表

drop table departments04

--******************************************************************************************

约束是在表上强制执行的数据校验规则.

当表中数据有相互依赖性时,可以保护相关的数据不被删除.

Oracle 支持下面五类完整性约束:

1、NOT NULL非空

2、UNIQUE Key唯一键

3、PRIMARY KEY主键

4、FOREIGN KEY外键

5、CHECK检察

Check约束条件是一种比较特殊的约束条件，通过check定义，

强制定义在字段上的每一记录都要满足check中定义的条件。

在check中定义检查的条件表达式，进入表中的数据必须符合

check中设置的条件

条件表达式不允许使用:

1、SYSDATE, USER等函数

2、参照其他记录的值

..., salaryNUMBER(2)

CONSTRAINT emp_salary_min

CHECK (salary > 0),...

--********************************************************************--

--数据优化与索引有关？？且记，在优化的时间用。

--视图，十分重要。

视图也就是虚表，实际上视图就是一个命名的查询，用于改变基表数据的显示。

可以限制对数据的访问

可以使复杂的查询变的简单

提供了数据的独立性

提供了对相同数据的不同显示

--视图: --为sql语句起的别名 给予表之上的一个查询语句

--语法:

--在CREATE VIEW语句后加入子查询.

CREATE [OR REPLACE] VIEW view_name

[(alias[, alias]...)]

AS subquery

[WITH READ ONLY];

create or replace view v_emp

as

select * from employees

--查询视图

select * from v_emp;

--它是为一些比较的复杂的sql语句，添加一个别名。这样在程序中就能够直接调用

--一个简单的视图，而不是要写复杂的SQL语句。

--视图的作用

--* select 语句比较复杂

--* select语句在开发的程序中可能多次使用

--* 在程序中直接使用视图 select * from v_emp

create or replace view v_emp

as

select "EMPLOYEE_ID","FIRST_NAME","LAST_NAME","EMAIL","PHONE_NUMBER",

"HIRE_DATE","JOB_ID","SALARY","COMMISSION_PCT","MANAGER_ID","DEPARTMENT_ID" from employees

--描述视图的结构(命令行执行)

desc v_emp

describe v_emp

--创建复杂视图

create or replace view v_emp_dept

as

select d.department_name,min(salary) mins,max(salary) mass,avg(salary) avgs,sum(salary) sums,count(salary) counts

from employees e,departments d

where e.department_id=d.department_id

group by d.department_name

--查询视图

select * from v_emp_dept

--通过视图插入数据到表中

create or replace view v_dept

as

select deptno,dname,loc from dept

--查询视图

select * from v_dept

--通过 v_dept视图插入数据到dept表中

insert into v_dept(deptno,dname,loc) values(89,'xxx','ss')

--通过设置WITH READ ONLY选项可以禁止对视图执行DML操作.

create or replace view v_dept

as

select deptno,dname,loc from dept

with read only

--删除视图

--删掉视图不会导致数据的丢失，因为视图是基于数据库的表之上的一个查询定义.

DROP VIEW view_name;

--删除v_dept视图

drop view v_dept

--******************************************************************************************************

TOP分析法:oracle 分页

行内视图

行内视图是在SQL语句中使用的一个带有别名的子查询.

在主查询FROM 子句中的子查询就是行内视图.

行内视图不是数据库的对象,所以不需要显式的创建.

Top-N分析的语法注意事项:

1、使用了rownum这个伪列，这个伪列将会返回行号，可以作为返回记录的序列号显示。

2、在from后面使用了子查询，这是标准的行内视图的使用。

3、在子查询中使用了order by进行排序，在前面的子查询中不需要使用。

4、在主查询中通过where条件中的rownum伪列定义过滤条件，只返回最什么的前几行数据

-- 查询员工表中 employee_id为10001 10002 10003 不能使用表中的任何字段作为查询条件

select employee_id,first_name from employees where rownum<4

---它只能查询小于的，不能查询大于的，此时里面的已经变成了，逆序的排列，然后再用

-- 查询员工表中 employee_id为100010 10009 10008 不能使用表中的任何字段作为查询条件

select employee_id,first_name from(

select employee_id,first_name from employees order by employee_id asc

)

where rownum < 4

--分页 每页显示3条记录 rownum rank也是oracle中的隐藏字段。

-- 第一页 查询员工表中 employee_id为10001 10002 10003 不能使用表中的任何字段作为查询条件

select employee_id,first_name from(

select rownum rank,employee_id,first_name from (

select employee_id,first_name from employees order by employee_id asc

)

where rownum <4

) where rank>0

-- 第二页 查询员工表中 employee_id为10004 10005 10006 不能使用表中的任何字段作为查询条件

select employee_id,first_name from(

select rownum rank,employee_id,first_name from (

select employee_id,first_name from employees order by employee_id asc

)

where rownum <7

) where rank>3

-- 第三页 查询员工表中 employee_id为10007 10008 10009 不能使用表中的任何字段作为查询条件

select employee_id,first_name from(

select rownum rank,employee_id,first_name from (

select employee_id,first_name from employees order by employee_id asc

)

where rownum <10

) where rank>6

-- 第四页 查询员工表中 employee_id为100010 不能使用表中的任何字段作为查询条件

select employee_id,first_name from(

select rownum rank,employee_id,first_name from (

select employee_id,first_name from employees order by employee_id asc

)

where rownum <13

) where rank>9

--******************************************************************************************************

--同义词:

同义词是数据库中一个对象的别名,可以简化对对象的访问

通过使用同义词，可以:

1、简化了引用另一个用户对象的方法

2、缩短了对象名称的长度

CREATE [PUBLIC] SYNONYM synonym

FOR object;

--创建同义词，其实就是给表起了一个别名。

create synonym xx

for departments

--使用同义词查询

select * from xx

--删除同义词.

DROP SYNONYM s_emp;

--删除xx

drop synonym xx

--*****************************************************************************************************