decade是orcale自己的判断语句
格式 decade（col，条件一，结果一， --col表示要判断的列数据
条件二，结果二， --如果数据满足条件，就执行对应条件后边的结果
条件三，结果三，
结果四 --如果上面的条件都不是，则执行结果4，表示其他条件应该执行的结果
）
SQL99语法的判断语句
case col when 条件一 then 结果一 --col表示要判断的列数据
when 条件二 then 结果二 when 满足条件二，then执行结果二
else 结果三 否则执行结果三
end； 结束标志
decade语句练习：
select ename,job ,sal ,decode(job,'PRESIDENT',sal+1000,
'MANAGER',sal+800,
sal+400) 涨后
from emp
ENAME JOB SAL 涨后
---------- --------- ---------- ----------
SMITH CLERK 800 1200
ALLEN SALESMAN 1600 2000
WARD SALESMAN 1250 1650
JONES MANAGER 2975 3775
MARTIN SALESMAN 1250 1650
BLAKE MANAGER 2850 3650
CLARK MANAGER 2450 3250
SCOTT ANALYST 3000 3400
KING PRESIDENT 5000 6000
TURNER SALESMAN 1500 1900
ADAMS CLERK 1100 1500
ENAME JOB SAL 涨后
---------- --------- ---------- ----------
JAMES CLERK 950 1350
FORD ANALYST 3000 3400
MILLER CLERK 1300 1700
Tom_AB 3000 3400
case练习
select ename,job ,sal ,case job when 'PRESIDENT' then sal+1000
when 'MANAGER' then sal+800
else sal+400
end 涨后
from emp；
ENAME JOB SAL 涨后
---------- --------- ---------- ----------
SMITH CLERK 800 1200
ALLEN SALESMAN 1600 2000
WARD SALESMAN 1250 1650
JONES MANAGER 2975 3775
MARTIN SALESMAN 1250 1650
BLAKE MANAGER 2850 3650
CLARK MANAGER 2450 3250
SCOTT ANALYST 3000 3400
KING PRESIDENT 5000 6000
TURNER SALESMAN 1500 1900
ADAMS CLERK 1100 1500
ENAME JOB SAL 涨后
---------- --------- ---------- ----------
JAMES CLERK 950 1350
FORD ANALYST 3000 3400
MILLER CLERK 1300 1700
Tom_AB 3000 3400
规则
1.可以单一列和多个列分组，按照先后顺序，如果第一个列相同，则按照第二个列排序，一次类推
2.如果select中包含函数，例如sum、avg等函数，那么分组函数的其他列必须进行分组
3.如果查询条件需要使用分组函数，则需要使用 having 作为条件筛选
4.group by 增强语句 group by rollup（a,b,c...） 报表格式
select ename,job ,sal from emp group by job,sal,ename; 分组必须包含所有的类名，可以颠倒顺序。
select ename,job ,avg(sal) from emp group by job,ename; 分组必须包含所有的类名，组函数不用分组
SQL> select job ,avg(sal) from emp group by job;
JOB AVG(SAL)
--------- ----------
3000
CLERK 1037.5
SALESMAN 1400
PRESIDENT 5000
MANAGER 2758.33333
ANALYST 3000
where 和 having 的区别：
where 效率更高
having 可以使用组函数
首先驱动
与eclipse结合，orcale安装自带jdbc的jar包，位置oracle\product\10.2.0\db_1\jdbc\lib中的取出来
编写JDBCUtils.java的代码片段与以前一样，详细见下方
编写调用存储过程的java代码，详细见下方
注意： 1.oracle的预处理对象Statement子类使用的是CallableStatement。
2.预处理sql语句的编写格式：存储过程： { call <procedure-name>[(<arg1>,<arg2>...)]} 例：String sql = "{ call queryEmpInfo(?,?,?,?)}";
存储函数： { ?=call <procedure-name>[(<arg1>,<arg2>...)]} 例：String sql = "{ ? = call queryEmpInfo(?,?,?,?)}";
3.对未知参数？的设置与声明
in 表示输入参数，所以表示查询之前我们就知道的，所以使用预处理对象直接设置
call.setInt(1, 7839); 或者设置字符串 call.setString(1, "7839");
out表示输出参数，在查询之前我们并不知道，所以声明他的类型，声明类型的对象OracleTypes里的静态变量
call.registerOutParameter(2, OracleTypes.VARCHAR);
call.registerOutParameter(3, OracleTypes.NUMBER);
call.registerOutParameter(4, OracleTypes.VARCHAR);
编写调用oracle存储过程和存储函数的java代码的步骤：
1.获取connection
2.获取sql语句
3.获取预处理对象
4.对预处理的sql设置？的对应值和参数
5.获取结果
6.结果处理
7.释放资源
例 编写JDBCUtils.java的代码片段
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCutils {
private static String driver = "oracle.jdbc.OracleDriver";
private static String url = "jdbc:oracle:thin:@192.168.56.102:1521/orcl";
private static String user = "scott";
private static String password = "tiger";
// 静态代码块,默认注册DriverManager
static {
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//获取connection链接
public static Connection getConnection() {
try {
return DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
//关闭资源
public static void release(Connection conn ,Statement st ,ResultSet rs){
if (rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
rs=null;
}
}
if (st!=null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
st=null;
}
}
if (conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
conn=null;
}
}
}
}
例：调用存储过程的java代码
//存储过程
/*create or replace procedure queryEmpInfo(eno in number,
pename out varchar2,
psal out number,
pjob out varchar2
)
*/
@Test
public void testProducedure(){
Connection conn = null;
CallableStatement call = null;
//调用存储过程的sql格式 { call <procedure-name>[(<arg1>,<arg2>...)]}
String sql = "{ call queryEmpInfo(?,?,?,?)}";
try {
conn = JDBCutils.getConnection();
call = conn.prepareCall(sql);
//对于in的 参数赋值的方法
call.setInt(1, 7839);
//对于out参数，目前没有值，需要声明
call.registerOutParameter(2, OracleTypes.VARCHAR);
call.registerOutParameter(3, OracleTypes.NUMBER);
call.registerOutParameter(4, OracleTypes.VARCHAR);
//执行
call.execute();
//取出结果
String name = call.getString(2);
double sal = call.getInt(3);
String job = call.getString(4);
//打印结果
System.out.println("7839号员工姓名："+name+" 工资："+sal+" 工作："+job);
} catch (SQLException e) {
e.printStackTrace();
}finally{
JDBCutils.release(conn, call, null);
}
}
深度遍历 层次查询语句：主要是避免自连接的缺点
select cmpon,cname,marager --选择的数据列
from emp --选择表
connect by prior compno=mgr --connect by 表示深度遍历的形成条件，例如按照 prior compno 上一级的compno是当前用户的mgrager形成层次数
start with empno = 7839; --开始遍历的调条件
SQL中的null值的处理
1. 包含null的表达式都为null --100*null值为null; 100+null值为null
2. null永远！=null --null值排序默认是最大值，且null和null不相等
3. 如果集合中 含有null值，不能使用not in；但可以使用in
4. 空值的排序
null默认为最大值，所以排序时会当做该行中最大值比较排序。
可以使用 nulls last;
5. 多行函数（组函数）自动滤空；可以嵌套滤空函数来屏蔽他的滤空功能(例如使用nvl())
详细解释：
1. 包含null的表达式都为null --100*null值为null; 100+null值为null
错误例子：SQL> select ename,sal,sal*12,comm,sal*12+comm from emp;
结果：当comm为null是sal*12+comm为null值，逻辑上不正确，使用nvl（a,b）a表示要该列的null，b表示替换成的值。
改正例句：SQL> select ename,sal,sal*12,comm,sal*12+nvl(comm,0) from emp; --当a列中是null是，替换为0，参与运算
2. null永远！=null --查询奖金为null的员工
错误实例：SQL> select * from emp where comm=null; conn列中的null！=null，所以报错。
错误结果：未选定行
4. 空值的排序：null默认为最大值，所以使用asc是排序null值放在最后，desc排序时null值排在最前，为了更符合我们的阅读习惯，希望null总是在走后边，可以使用 nulls last;
例句：SQL> select * from emp order by comm desc nulls last;
登录
本机登录命令 SQL>sqlplus scott/tiger
指明数据库管理权限 SQL>sqlplus scott/tiger as SYSDBA
AS SYSDBA 和 AS SYSOPER 选项是数据库管理权限。
退出
exit
操作录屏
spool D:11111.txt --创建录屏并指定保存的文件路径和文件名，可以直接创建新的问价你
spool off --停止录屏
spool d:\a.txt append --文件中追加录屏信息 append 命令
显示当前登录用户
SQL>show user ;
清屏
SQL>host cls
当前用户下的表
SQL> select * from tab;
员工表的结构
SQL> desc emp
信息格式显示和设置：
显示行宽
SQL> show linesize 查询结果linesize 80 表示行宽80
设置行宽
SQL> set linesize 200
设置列宽
SQL> col ename for a8 （占8个字符）
SQL> col sal for 9999 （一个9代表占一位数字）
显示行数（加上标题，横线，空行的总行数）
SQL> show pagesize;
SQL> set pagesize 20;
SQL执行的时间
set timing on
set timing off
设置回显信息
set feedback on 默认打开
set feedback off
执行Sql的脚本
@+脚本路径 --例如 @d:\temp\testdelete.sql
看看执行计划
explain plan for select * from emp where deptno=10; --编译select * from emp where deptno=10;的执行计划
select * from table(dbms_xplan.display); --查看执行计划的结果（固定写法）
通过列名查询
SQL> select ename,cal from emp; 查询多列使用逗号" , "隔开
使用运算符查询年薪
SQL> select ename,sal M,sal*12 Y --sal*12 计算年薪
2 form emp;
别名使用规则：
完整形式 ：as “别名” --as是关键字，别名使用“”双引号
简写形式：as可以忽略，当别名中不包含关键字和空格式可以不添加“”。
去掉重复值 distinct
不去重语句：SQL> select deptno from emp;
去重语句：SQL> select distinct deptno from emp;
组合去重语句：SQL> select distinct deptno,job from emp; 去重关键字修饰多个字段 distinct作用于后面所有的列
查询规则：将修饰的多个字段看成整体，只有所有字段组成的结果相同才需要被去重，其中一个字段重复，其他不重复则不算重复数据
连接词concat 和||
错误使用：select concat('Hello',' World') --没有from，不符合Oecale语法，不许要添加表
不推荐使用：select concat('Hello',' World') from emp; --查询到的每条数据修改成连接的字符串，不符合使用规则。
正确使用：select concat('Hello',' World') from dual; --dual 伪表，一张数据库中没有的表，知识一个Orcale提供的表，只是为了符合语法，没有实际意义
select 3+2 from dual; --结果列名3+2，数据一条结果是5
||的正确使用：select 'Hello'||' World' from dual；
select ename||'的薪水是'||sal 信息 from emp; --结果SMITH的薪水是800
where条件中
字符串大小写敏感
SQL> --查询名叫KING的员工
错误实例：select * from emp where ename='King'; --King !=KING
正确实例：select * from emp where ename='KING'; --KING=KING
日期格式敏感 Orcale默认的日期格式 dd-mon-yy 例如 '17-11月-81
SQL> --查询入职日期是17-11月-81的员工
错误实例：select * from emp where hiredate='1981-11-17'; 错误提示 ORA-01756: 引号内的字符串没有正确结束
正确实例：select * from emp where hiredate='17-11月-81';
修改日期格式
查询所有的参数命令：SQL> select * from v$nls_parameters;
日期参数的参数名：NLS_TIMESTAMP_FORMAT 格式：DD-MON-RR HH.MI.SSXFF AM TZR
修改日期方法：SQL> alter session set NLS_DATE_FORMAT='yyyy-mm-dd';
alter 表示设置 session表示改变当前的控制台的date格式，关闭之后无效
条件查询范围之间表现形式：
between and:在。。。。之间
员工工资在2000-3000之间，包含边界,下面两句相等
原始语句 select * from emp where sal >=2000 and sal <=3000;
between and 语句 :select * from emp where sal between 2000 and 3000;
between A and B 规则：1.包含边界
2.A>=B，较小值放在前边
in(a,b,c,..) 在集合中
例子：查询部门号是10和20号的员工
普通语句：SQL> select * from emp where deptno=10 or deptno=20;
in语句：SQL> select * from emp where deptno in(10,20);
not in(a,b,c,..) 不在给出的集合中
not in语句：SQL> select * from emp where deptno not in(30,null); --in中可以使用null值
错误语句：select * from emp where comm not in(300,null) --not in中不可以使用null值
like 模糊查询
% 任意长度的任意字符
_ 任意的一个字符
例句： select * from emp where ename like '%S' ; 查询以S结尾的所有满足条件的数据
select * from emp where ename like '___D' ; 查询4位字符并且第四位为D的数据
转意字符 查询数据中包含 %或者_时，使用 escape '\'; escape是关键字，'\'自定义转义的标识字符，可任意选择，一般使用\
SQL> select * from emp where ename like '%\_%' escape '\';
order by 排序 asc（默认升序） desc（降序）
例句：select * from emp order by sal desc;
order by 后面可以添加参数有[ 列名，表达式，别名，序号]
列名：查询的一 列名字；
例句：select empno,ename,sal,sal*12 年薪 from emp order by sal*12 desc;
别名：给列名使用as的 别名；
例句：select empno,ename,sal,sal*12 年薪 from emp order by 年薪 desc;
序号：select后列名的第几个，就是序号，从1开始，小于总列数；
例句：select empno,ename,sal,sal*12 年薪 from emp order by 4 desc;
表达式：
多个排序列：（上述4中排序方法可以混合使用）
首先按照第一个列排序，如果相等，则按照第二个列排序，以此类推
例句： select empno,ename,sal,sal*12 年薪 from emp order by 3,年薪 desc
多排规则： 1. 列名，表达式，别名，序号可以混合使用。
2.desc和asc只会作用在提前面的这一个列上，如果想使用不同的列不同的排序，请每个列上添加一个 desc或者asc（asc可以省略，默认）。
例句比较： select empno,ename,sal,deptno from emp order by deptno,sal desc --deptno默认升序排列，sal降序排列
select empno,ename,sal,deptno from emp order by deptno desc,sal desc ----deptno降序排列，sal降序排列
where和having的区别：where不能使用多行函数
单行字符函数
lower('Hello World') 转小写
upper('Hello World') 转大写
initcap('hello world') 每个单词首字母转大写
substr(a,b) 从a中，第b位开始取，获取子串 --substr('Hello World',3) 从1开始算起
substr(a,b,c) 从a中，第b位开始取，取c位 --substr('Hello World',3,4)
length 字符数长度 --length('Hello World')
lengthb 字节数长度 --lengthb('中国')
instr(a,b) 在a中，查找b，返回起始位置，没有找到返回0 --instr('Hello World','ll')
lpad 左填充 --lpad('abcd',10,'*')
rpad右填充 --rpad('abcd',10,'*')
trim(a from b) 去掉b前后指定的a字符 --trim('H' from 'Hello WorldH')
replace(a,b,c) 替换,在a中用c替换b --replace('Hello World','l','*')
round(num,n)根据n确定保留小数，对num四舍五入 --round(45.926,-2)小数点左边两位四舍五入
trunc(num,n)根据n确定保留小数，对num截取trunc(45.926,-1) 40
sysdate 当前系统时间 --格式化显示的时间to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
systimestamp时间戳
to_char（格式化薪水）
nvl2(a,b,c) 当a=null的时候，返回c；否则返回b
nullif(a,b) 当a=b的时候，返回null；否则返回a
coalesce 从左到右找到第一个不为null的值
decode（com,a1,b1,a2,b2,a3,b3,b4）判断语句
多行函数
sum()
max()
min()
count()
avg()
group by 分组
level伪列 深度
回滚，自己没有手动使用commit提交，就可以使用rollback回滚。
API位置（两本）：在orcale文档中的books-pl/下
PL/SQL Packages and Types Reference
PL/SQL User's Guide and Reference
plsql语言：面向过程的语言，数对sql99语法的扩展，可以的使用sql99语法，曾加了一些过程处理语句，例如分支、循环等
基本格式：
declare
--说明部分（变量说明、光标声明、例外说明）
begin
--语句序列（DML语句）...
exception
--例外处理语句
end；
程序包：
开启程序包 set serveroutput on；
dbms_output 输出的包
dbms_output.put_line（'hello Wolld!'); 输出一句文字
例1 输出hello world！
set serveroutput on;
declare
--说明部分
begin
dbms_output.put_line('hello World!');
end;
变量和常量声明
可以定义数据库的常用变量 cahr、 varchar2、number、boolean、date、long 等
例 pname char(15);
married boolean:=true -- :=表示赋值，相等于java中的=，表示married表示boolean类型，初始值为true
pasl number(7,2) --表示长度为7位，保留两位小数
可以定义数据库的引用类型：应用某个类的数据类型作为当前变量的数据类型 %
例 my_name emp.ename%type --表示使用emp表中的ename类的类型作为当前my_name的数据类型
记录型变量，类似于引用型变量的一个数组，引用该行上的所有列类型
例 emp_rec emp%rowtype --记录一行的变量类型
定义变量赋值into
into 后边的变量顺序应该与前面的查询数据顺序相同
例 定义变量接受数据，查询员工号为7839的员工的薪水和姓名，并打印出来
--开启输出包
set serveroutput on;
declare
--定义panme、sal的变量
pname emp.ename%type; --声明pname的变量类型引用ename的数据类型
psal emp.sal%type; --声明psal的变量类型引用sal的数据类型
begin
--选择出员工号7839的员工姓名和薪水，并赋值给变量打印出来
select ename,sal into pname,psal from emp where empno=7839;
dbms_output.put_line(pname||'的薪水是'||psal);
end;
/
例 定义航引用接收数据，查询员工号为7839的员工的薪水和姓名，并打印出来
--开启输出包
set serveroutput on;
declare
emp_rec emp%rowtype; --将emp标的一行的所有类类型赋给变量数组
begin
--选择出员工号7839的员工姓名和薪水，并赋值给变量打印出
select * into emp_rec from emp where empno=7839;
dbms_output.put_line(emp_rec.ename||'的薪水是'||emp_rec.sal);
end;
/
if else 语句
if 条件 then 处理结果；
elsif 条件 then 处理结果；
else 其他情况处理结果；
end if；
例 输入一个数字，判断是几并输出
--开启输出包
set serveroutput on;
accept num prompt '请输入一个数字'; --键盘输入一个数字，num是数字保存的地址值，prompt后边提示信息
declare
pnum number := # --将地址中的数值赋给变量
begin
if pnum=0 then dbms_output.put_line('您输入的是0');
elsif pnum=1 then dbms_output.put_line('您输入的是1');
elsif pnum=2 then dbms_output.put_line('您输入的是2');
else dbms_output.put_line('您输入的是其他数字');
end if;
end;
/
循环语句
第一种：当满足判断条件时执行循环LOOP中的代码，不满足则跳出循环
while tatol<=2500
LOOP
...循环体
tatol ：= total+salary；--改变循环条件
end LOOP;
第二种：当满足判断条件时跳出循环，不满足则执行循环LOOP中的代码
LOOP
EXIT [when 条件]；
...循环体
end LOOP;
例 循环打印从1到10的数字，然后退出
--开启输出包
set serveroutput on;
declare
pnum number := 1;
begin
loop
exit when pnum > 10;
DBMS_OUTPUT.PUT_LINE(pnum);
pnum := pnum + 1;
end loop;
end;
/
第三种：for循环，当满足判断条件时执行循环LOOP中的代码，不满足则跳出循环
FOR num1 IN 1..20 --IN 表示条件范围，1..20表示1到20之间的整数简写
LOOP
语句序列；
end LOOP;
链接orcale数据库
左侧+号或者右键链接-->新建链接
链接名：一个显示名字，随便起，有意义
用户名：连接的用户名，例如scott、hr
口令：连接的用户密码，例如tiger、hr
保存口令选项勾选，自己根据需要是否勾选
主机名：本地可以使用localhost，其他机器使用orcale安装机器的IP地址
端口：数据库端口号，orcale默认为1521
sid：连接数据库实例的名字，例如 orcl
完成链接，测试成功即可
添加mysql数据库管理
1.添加链接数据库的驱动包
工具-->首选项-->Data MOdeler-->第三方 JDBC 驱动程序-->右侧 +号-->文件选择mysql的驱动包
2.链接mysql数据库
未查询
显示行号
工具-->首选项-->代码编辑器-->行装订线-->显示行数
1.格式化日期 详见 日期详解
2.格式化数字
位数占位符 9，表示一位数；L表示显示本地货币 常见格式 L9,999.99 对应格式的薪水：￥1,600.00
select to_char(sal,'l9,999.99') from emp;
TO_CHAR(SAL,'L9,999
-------------------
￥800.00
￥1,600.00
触发器：
数据库触发器是一个和表相关联的、存储的pl/sql程序。每当一个特定的数据库操作语句（insert、update、delete）在指定的表上发出时，Oracle自动地执行触发器中定义的语句序列。
类型：
语句级触发器：在特定的操作语言执行之前或者之后操作一次，不过这条语句影响多少行
行级触发器（for each for）：触发语句作用在每一条记录上，在行级触发器中使用：old和：new伪记录变量，识别值得状态
| 触发语句 | ：old | ：new |
| insert | 所有字段都是nuLl | 将要插入的数据 |
| update | 更新以前该行的值 | 更新后该行的值 |
| delete | 删除以前该行的值 | 所有字段都是null |
作用
数据确认 --
实施复杂的安全性检查 --
做审计，跟踪表上所做的数据操作等 --记录日志；oracle自己拥有强大的审计功能，可以直接调用
数据的备份和同步 --特定时间自动调用同步或者备份的函数
触发器完整格式：
create [or replace] trigger 触发器名
{ before | after}
{delete | insert | update [ of 列名]}
on 表名
[for each row [when(条件)]]
plsql 程序块
例1.禁止在非工作时间插入新用户。 --使用语句级触发器方式
create or replace trigger addUser
before insert
on emp
begin
if TO_CHAR(SYSDATE,'day') in ('星期日','星期六')
or
(TO_NUMBER(TO_CHAR(SYSDATE,'hh24'))) between 9 and 17
then
raise_application_error(-20001,'禁止在非工作时间添加员工信息');
end if ;
end;
例2.涨工资之后的工资不能比涨之前少。 --使用行级行级触发
create or replace trigger updateSal
before update
on emp
for each row
begin
if :old.sal > :new.sal
then
raise_application_error(-20002,'不能减工资');
end if ;
end;
指存储在数据库中提供所有用户程序调用的子程序叫做 存储过程、存储函数。 使用plsql语言编写。
存储函数可以通过return子句返回一个结果集。存储过程不可以。
一般不再存储过程中提交，更多的是让调用者提交。
in 和 out
1.过程没有返回值，函数有返回值
2.过程和函数都可以使用in和out定义输入参数和输出参数
3.in和out可以在一个子程序中定义多个，几可以使用out输出多个结果，等同于return返回值
语法：存储过程
create [ or replace ] PROCEDURE 存储过程名（参数类表）
AS
plsql子程序体；
例：输出helloword的一个存储过程的程序。
create or replace procedure sayhellaword
as
begin
SYS.DBMS_OUTPUT.PUT_LINE('hello world!');
end;
例：给指定员工涨100块钱，并打印涨前和涨后的工资。
create or replace procedure raiseSalary(pno in number) --存储过程带输入参数 in 表示，调用时传入具体参数值
as
psal emp.sal%TYPE;
begin
select sal into psal from emp where empno = pno;
update emp set sal=sal+100 where empno = pno;
SYS.DBMS_OUTPUT.PUT_LINE('涨前:'||psal||' 涨后:'||(psal+100));
end;
语法：存储函数
create [ or replace ] PROCEDURE 存储过程名（参数类表）
RETURN 函数值类型
AS
plsql子程序体；
调用存储过程方法：
第一种：exec 存储过程名字（）； 例如exec SAYHELLAWORD();直接执行
第二种：放在plsql的begin/end里面，可以调用多次
begin
SAYHELLAWORD();
SAYHELLAWORD();
end
--oracle分页(Pageing Query)
原理：通过rownum产生的序号，不会随着查询的改变而改变，所以可以通过选择rownum来选择
rownum只能选择小于某个特定值的数据，不能使用大于摸个值得数据，因为他是从一涨上去的，所以大于的会报错
第一步：原表数据排序
第二步：添加伪列rownum列，并添加条件 >=页数结束值的数据
第三步：在选择 第二步产生的表中 rownum列作为本表的普通列，选择条件 rownum>=页数起始条数据
select *
from (select rownum r,e1.*
from (select * from emp order by sal) e1
where rownum <=8
)
where r >=5;
或者（不建议使用，效率低）
select *
from (select rownum r,e1.*
from (select * from emp order by sal) e1
)
where r >=5 and r <=8
R EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
5 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
6 7934 MILLER CLERK 7782 23-1月 -82 1300 10
7 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
8 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
SQL>
光标Cursor：用于一个查询语句赶回的多行数据
是类似于ResultSet的结果集合
光标的属性：
%isopen 光标是否打开
%rowcount 当前影响的行数，取出的行数
%found 可以取出数据，不是走后一行数据
%notfound 不能再取出数据，已经是最后一行数据
光标的语法：
cursor 光标名 [(参数名 数据类型[,参数名 数据类型]...)]
IS SELECT 语句；
例：cursor c1 is select ename from emp; --选择emp表中所有的ename数据，将c1指向这个结果集合的第一个位置
打开光标： open c1； （打开光标执行光标上面的查询语句，获得结果集）
取一行光标的值： fetch c1 into pename;（取一行数据到变量中，注意：pename必须与emp中ename的数据类型一致）
关闭光标： close c1； （关闭游标释放资源）
例：查询并打印emp表中所有用户的名称和对应薪水。打印格式 SMITH的薪水是800
--查询并打印员工的姓名和薪水
set serveroutput on
declare
--定义光标
cursor names is select ename,sal from emp;
--定义变量、
pename emp.ename%type;
psal emp.sal%type;
begin
open names;
Loop
exit when names%notfound;
fetch names into pename,psal;
dbms_output.put_line(pename||'的薪水是'||psal);
end loop;
close names;
end;
orcale的plsql程序的crud操作自动开启事务，需要手动提交事务。
例：查询10号部门所有员工的姓名，使用带参数的光标，参数时10号部门
--打印10号部门的所有员工姓名
set serveroutput on
declare
--定义光标
cursor names(dno number) is select ename from emp where deptno=dno;
--定义变量、
pename emp.ename%type;
begin
open names(10);
Loop
fetch names into pename;
exit when names%notfound;
dbms_output.put_line(pename);
end loop;
close names;
end;
注意：管理员没有回收站，只有非管理员的普通用户才有回收站。
drop table emp; 并不会删除表数据，而是将表方法哦了回收站里，
SQL> drop table emp20; --删除表
SQL> select * from tab; --查询表
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
BIN$eK6vh/mPRQCNRRBaKiPQww==$0 TABLE --回收站中保存
查看回收站
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP20 BIN$eK6vh/mPRQCNRRBaKiPQww==$0 TABLE 2016-02-03:16:28:44
清空回收站
SQL> purge recyclebin;
回收站已清空。
恢复回收站中的表
闪回删除
flashback table TESTSAVEPOINT to before drop;
1. 参与运算的各个集合必须列数相同 且类型一致
2. 采用第一个集合作为最后的表头
3. orderby永远在最后
4. 括号
1-1
1-2. 并集：union all 表示交集取两次，保留各自交集部分，费交集部分全要
2.
3.
plsql语句格式中exception例外处理机制： 类似于java的异常catch语句
格式：
exception
when 例外1 then 处理语句1； --when 捕获bengin中产生的例外异常
处理语句2； --then 对不活的当前例外的处理语句，可多条语句
when 例外2 then 处理语句3；
when others then 处理语句4； --others 其他的例外全部捕获，我们应该捕获所有例外，如果不捕获，抛给数据库可能会出异常。
系统定义好的例外：
No_data_found （没有找到数据）
Too_many_tows （select ... into 语句匹配多个行）
Zero_Divide （别零除）
Value_error （算数或者转换错误）
Timeout_on_resource （在等待资源时发生超时）【常见场景：分布式数据库访问时】
例 编写一个被零除的例外。
set serveroutput on
declare
pnum number;
begin
pnum := 1/0 ;
exception
when zero_divide then dbms_output.put_line('0不能作为除数');
dbms_output.put_line('赋予了pnum非法值');
when value_error then dbms_output.put_line('转换错误');
when others then dbms_output.put_line('其他的错误');
end;
/
自定义例外：当做变量来处理，在declare重定义，使用exception声明 No_date exception；
raise抛出异常，raise no_data;
例 查询50号员工部门的员工姓名。
set serveroutput on
declare
cursor names(dno number) is select ename from emp where deptno=dno;
pename emp.ename%type;
No_data_found exception; --定义例外
begin
open names(50);
fetch names into pename;
if names%notfound then raise No_data_found; --判断并抛出例外
end if;
CLOSE names;
dbms_output.put_line('完成'); --完成提示信息
exception --例外
when No_data_found then dbms_output.put_line('没有发现该部门');
when others then dbms_output.put_line('其他的错误');
end;
/
sysdate 系统当前时间，跟随orcale安装的系统时间而定。
默认执行的时间显示样式：
SQL> select sysdate from dual;
SYSDATE
--------------
03-2月 -16
格式化日期
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') 24小时格式
to_char(sysdate,'yyyy-mm-dd hh:mi:ss')12小时格式
执行结果：
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') formartDate from dual;
FORMARTDATE
-------------------
2016-02-03 11:31:30
补充：timestamp 时间戳 比时间更精确的表示方式，将秒分成了1,000,000份
系统时间戳格式化：to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss:ff')
SQL> select to_char(systimestamp,'yyyy-mm-dd hh:mi:ss:ff') from dual;
TO_CHAR(SYSTIMESTAMP,'YYYY-MM
-----------------------------
2016-02-03 11:38:18:342000
日期的数学运算
直接使用 ‘+’‘ -’
1.日期+（-）整天数，获取对应的日期
SQL> select sysdate-1 昨天,sysdate 今天,sysdate+1 明天,sysdate+7 下星期 from dual;
昨天 今天 明天 下星期
-------------- -------------- -------------- --------------
02-2月 -16 03-2月 -16 04-2月 -16 10-2月 -16
2.日期-日期，获取两个日期相差天数，不区分前后日期，会得出正负天数，前边-后边，得出天数
SQL> select sysdate-hiredate from emp where ename = 'KING';
SYSDATE-HIREDATE
----------------
12496.5031
3.日期+日期，没有意义，查询抱错
SQL> select sysdate+hiredate from emp where ename = 'KING';
select sysdate + hiredate from emp where ename = 'KING'
*
第 1 行出现错误:
ORA-00975: 不允许日期 + 日期
4.months_between(a,b) a与b之间相差月数
SQL> select months_between(sysdate,hiredate) from emp where ename = 'KING';
MONTHS_BETWEEN(SYSDATE,HIREDATE)
--------------------------------
410.564735
5.add_months（date,n）date日期加上n个月之后的具体时间
SQL> select add_months(sysdate,100) from dual;
当前时间的100个月之后是什么时间？
ADD_MONTHS(SYS
--------------
03-6月 -24
6.last_day（date） 日期所在月份的最后一天
SQL> select last_day(sysdate) from dual;
LAST_DAY(SYSDA
--------------
29-2月 -16
7.next_day(date,'星期四') 日期所在的下一个礼拜四 默认日期格式：星期日
SQL> select next_day(sysdate,'星期日') from dual;
NEXT_DAY(SYSDA
--------------
07-2月 -16
8.round(date,a) 按照a对日期四舍五入，四舍五入的a可以使按照 month 月 、year 年、day 星期、空表示天
SQL> select round(sysdate,'month') from dual; 按月
ROUND(SYSDATE,
--------------
01-2月 -16
SQL> select round(sysdate) from dual; 按天
ROUND(SYSDATE)
--------------
04-2月 -16
SQL> select round(sysdate,'day') from dual; 按星期
ROUND(SYSDATE,
--------------
07-2月 -16
SQL> select round(sysdate,'year') from dual; 按年
ROUND(SYSDATE,
--------------
01-1月 -16
9.格式化日期中有字符 使用“”标出 to_char(sysdate,'yyyy-mm-dd hh24:mi:ss"今天是"day')
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss "今天是"day') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:
-----------------------------------
2016-02-03 12:56:53 今天是星期三
应用：
1.自动备份 可以使用next_day 表示每个星期自动备份。
异地容灾
1. 分布式数据库
2. 快照 触发器（后面介绍）
Oracle中事务的标志
1. 起始标志：事务中第一条DML语句
2. 结束标志： 提交 显式commit
隐式：正常退出exit， DDL DCL
回滚 显式 rollback
隐式 非正常退出，掉电，宕机
定义保存点
savepoint a;
回滚到保存点
rollback to savepoint a;
设置为只读
set transaction read only;
SQL的类型
1. DML（Data Manipulation Language 数据操作语言）:insert update delete select
2. DDL(Data Definition Language 数据定义语言): create table,alter table,drop table,truncate table
create/drop view,sequence,index,synonym(同义词)
3. DCL(Data Control Language 数据控制语言): grant(授权) revoke（撤销权限）
地址符 & 通过引用&名字输入值，可以使用在 任何位置 select、from、where、having后边都可以使用
insert into emp(empno,ename,sal,deptno) values(&empno,&ename,&sal,&deptno);
输入 empno 的值: 1002
输入 ename 的值: 'Mary'
输入 sal 的值: 3000
输入 deptno 的值: 10
批处理
create table emp10 as select * from emp where 1=2; 1=2，fals，所以只创建表结构，并不会复制数据
insert into emp10 select * from emp where deptno=10; 一次性将emp中，所有10号部门的员工插入到emp10中
delete 和truncate区别
1. delete逐条删除；truncate先摧毁表 再重建
2. (根本)delete是DML truncate是DDL
（可以回滚） （不可以回滚）
3. delete不会释放空间 truncate会
4. delete可以闪回(flashback) truncate不可以
5. delete会产生碎片 truncate不会
rowid 行地址 这一行数据唯一地址
1.表
2.视图(虚表） 从表中抽取出来的逻辑上相关的数据集合,有点：简化查询，不能提高性能
create view 权限 grant create view 头scott；
create ciew empview as (select * from emp);
3.序列 sequenec -----> 相当于数组，在内存中产生，默认20 ，造成序列的裂缝的原因：断电、宕机（内存中的序列丢失，从下一个20倍数开始取值）、多表示用同一个序列、回滚，
4.索引 index 原理：对某列的数据添加索引，创建新的索引列，将原列数据变成排序的平衡二叉树数据，查询变快。可以创建多个索引
crreate index myindexName on emp(deptno,job);
5.同义词 （起别名）
6.存储过程
7.存储函数
8.包头
9.包体
10.触发器
原因：每一条数据都是有指针添加的，添加上的数据不会随着该条数据的删除而移动，会形成不连续的表数据，这样不连续的数据就会形成碎片化
解决办法： 1.alert table 表名 move；
2.数据先导出，再导入之后
修改上一句SQL语句的方法
SQL> select ename,sal --SQL语句第1行
forn emp; --SQL语句第2行
方法一：c命令 change
SQL> 2 --使用数字选择你最近一次提交的sql语句的第（2）N行
2* form emp --显示你要修改并选择的那行语句
SQL> c /form/from --(多个相同错误只修改第一个)修改格式 c命令 /要修改的错误字段/修改后的正确字段，直接替换
2* from emp --显示你修改之后的该行语句
SQL> / --使用/执行修改之后的SQL语句
方法二：ed命令edit -已写入 file afiedt.buf
执行上一条命令，想要修改使用ed命令
SQL> ed --弹出txt文件，里面是上一条sql命令，你可以修改txt文件，简单，修改完毕后保存，关掉txt文件。控制台显示修改完毕的SQL语句。
SQL> / --使用斜线执行该命令
SQL的优化原则：
1.尽可能多使用类名查询，最好不要使用*号查询。
原因：*号查询会查询所有数据，类名查询可以直接锁定列名，不必查询不相干的数据。
2. where解析的顺序：右---> 左 (了解：SQL的执行计划)
当where条件选择时，如果是and 时，将尽可能为假的条件放在右边，一假则假
如果是or链接条件是，将尽可能真的条件放在右边，一真则真
不局限在and和or，所有条件都算上，竟可能把可以确定结果的条件放在右边，先执行。
3. 尽量使用where
4. 尽量使用多表查询
5. 尽量使用union all
6. 尽量不要使用集合运算
orcale 的约束 两种
行约束 联合主键
列约束 一般的
not null 非空
unique
primary key 主键
foreign key 外键
check 检查性约束
级联删除
on delete cascade：级联删除子表数据
on delete set null ；子表相关依赖设置为null
check 检查性约束
格式 sal number check (sal > 0) --列名 数据类型 constraint 约束别名 check 检查的条件
例如：
由于光标可以表示一组数据，所以当有多个输出时可以定义一个光标，最好定义在包结构中
包结构格式：
包头 create or replace package mypackage_name as --mypackage_name 表示包头的名字
type empcursor is ref cursor; --type 自定义类型关键字；表示自定义empcursor类型，是引用cursor的类型
procedure queryEmplist(dno in number,emplist out empcursor); --定义的存储过程或者函数
end mypackage_name; --表示包头结束
包头可以使用plsql软件的程序包-->新建程序包-->起名字确定
包体
create or replace package body mypackage_name as
procedure queryEmplist(dno in number,emplist out empcursor) as
--定义变量
begin
open empList for select * from emp where deptno=dno;
end queryEmplist;
end mypackage_name;
包体可以使用plsql软件的程序包-->新建程序包-->起名字确定
1.plsql包结构编写
包头：
CREATE OR REPLACE
PACKAGE MYPACKAGE AS
type mycursor is ref cursor;
PROCEDURE queryEmpList(dno in number,mylist out mycursor);
END MYPACKAGE;
包体：
CREATE OR REPLACE
PACKAGE BODY MYPACKAGE AS
PROCEDURE queryEmpList(dno in number,mylist out mycursor) AS
BEGIN
open mylist for select * from emp where deptno = dno;
END queryEmpList;
END MYPACKAGE;
2.java调用代码编写
//带光标的包结构的的存储过程 查询某个部门的说有员工信息。
@Test
public void testpackger(){
Connection conn = null;
CallableStatement call = null;
ResultSet rs = null;
//调用存储过程的sql格式 { call <procedure-name>[(<arg1>,<arg2>...)]}
String sql = "{ call MYPACKAGE.queryEmpList(?,?)}";
try {
conn = JDBCutils.getConnection();
call = conn.prepareCall(sql);
//对于in的 参数赋值的方法
call.setInt(1, 10);
//对于out参数，目前没有值，需要声明
call.registerOutParameter(2, OracleTypes.CURSOR);
//执行
call.execute();
//取出结果 此安装换成oracle的实体对象，才能调用光标的get获取结果集resultSet子类
rs = ((OracleCallableStatement)call).getCursor(2);
//打印结果
while(rs.next()){
String name = rs.getString("ename");
String job = rs.getString("job");
int sal = rs.getInt("sal");
System.out.println("7839号员工姓名："+name+" 工资："+sal+" 工作："+job);
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
JDBCutils.release(conn, call, rs);
}
}
注意的问题：
1. 括号
2. 合理的书写风格
3. 可以在主查询的where select having from后都可以使用子查询
4. 不可以group by后面使用子查询
5. 强调from后面的子查询
6. 主查询和子查询可以不是同一张表；只要子查询返回的结果主查询可以使用
7. 一般不在子查询中排序；但在top-n分析问题中，必须对子查询排序
8. 一般先执行子查询，再执行主查询；但相关子查询例外
9. 单行子查询只能使用单行操作符；多行子查询只能使用多行操作符
10. 子查询中的null值
any 和 all
any表示 满足之中任何一个就可以
all表示 必须满足其中每一个才可以
扫一扫
675
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
