--select from where having group by order by 的正确执行顺序为：
--from-->where-->group by-->having-->select-->order by
--------------------------------------------------------
--1、去重：distinct 必须放在开头，多字段，每个字段不一样才去重
--2、条件比较：
-- =,!=,<>,<,>,<=,>=,any,some,all
-- is null, is not null
-- between x and y
-- in(list) , not in (list)
-- like _,%,escape '\' _\%escape '\'
-- not, (and,or) and优先级高
-- 按照单个列排序
-- order by col
-- 降序排列：order by col desc
-- 升序排列：order by col asc
-- 按多个列排序：(优先级)
-- order by col1 desc(asc) , col2 desc(asc)
--1.全集：union all
--2.并集：union (去重)
--3.交集：intersect
--4.差集：minus
--5.不懂怎么用，就选择 (*)
-----------------------------------------------------------
--基本查询语句
select * from emp;
select e.ename , e.sal from emp e where e.sal>1500;
select distinct e.deptno , d.dname from emp e , dept d;
---------------------------------------------------------
--组函数又被称作集合函数，用于对多行数据进行操作，并返回一个单一的结果
--组函数仅用于选择列表或查询的having子句单行函数对单个数值进行操作，并
--返回一个值。
--concat(string2,string3)连接两个字符串
--initcap(string) string中每个单词首字母大写
--lower(string) string 中每个单词首字母小写
--lpad , rpad 填充字符型数据
--ltrim/rtrim (string2,string3)
trim(A from B)
--substr() 提取字符串的一部分substr(string,1,2)
--upper(string)以大写形式返回string
--instr() 字符串出现的位置，instr(string,'A')
--length() 字符串长度
-------------------------------------------------------
--round(number,n):返回四舍五入的值
select round(23.434) from dual;
select round(23.434,2) from dual;
select round(23.436,2) from dual;
select round(23.434,-1) from dual;
--trunc(number,n): 返回截取的值
select trunc(23.434) from dual;
select trunc(23.434,2) from dual;
select trunc(23.436,2) from dual;
select trunc(23.434,-1) from dual;
--mod(x,y):求余数
select mod(13,5) from dual;
--ceil(number):向上取整
select ceil (19.2) from dual;
--floor(number):向下取整
select floor(19.2) from dual;
--avg(): 返回某列的平均值
--min(): 返回某列的最小值
--max(): 返回某列的最大值
--sum(): 返回某列值的和
--count(): 返回某列的行数
--组函数仅在选择列表和having子句中有效
--group by 子句
--group by 子句可以包含任意数目的列
--除组函数语句外，select 语句中的每个列都必须在 group by 子句中给出。
--如果分组列中具有null值，则null将作为一个分组返回。如果列中有多行null
--值，他们将分为一组。
--group by 子句必须出现在where 子句之后，order by 子句之前。
--过滤分组(having子句)
--where 过滤行，
--having 过滤分组
--having 支持所有的where操作
--分组和排序
--一般在使用group by 子句时，应该给出 order by 子句
--数据来自于多张表，92表连接
--注意：明确引用同名的列，必须使用表名，或者别名区分
--1.笛卡尔积：
-- select 字段列表 from 表1,表2,表3....
--2.等值连接：取关系列相同的记录
-- select 字段列表 from 表1,表2,表3....
-- where 表1.列 = 表2.列 and 表1.列 =表3.列
--3.非等值连接：取关系列不同的记录 != > < >= <= between and
-- select 字段列表 from 表1,表2,表3....
-- where 表1.列 != 表2.列 and 表1.列 != 表3.列
--4.自连接：(特殊的等值连接) 列来自于同一张表，不同角度看待表
-- select 字段列表 from 表1 e, 表2 d
-- where e.列1 = d.列3
--5.外连接：在等值基础上，确保一张表(主表) 的记录都存在，从
-- 表满足则匹配，不满足补充null
--右外：主表在右边
--左外：主表在左边
--sql1992：的语法规则暴露了这样额缺点：语句过滤条件和表连接的条件
-- 都放到了where子句中。当条件过多时，联结条件多，过滤条件
-- 多时，就容易造成混淆
--sql1999：修正了这个缺点，把联结条件，过滤条件分开来，包括以下新的table
-- join 的句法结构：
--cross join
--natural join
--using 子句
--on 子句
--left outer join
--right outer join
--full outer join
-------------------------------------------------------
--创建视图
--with read only 表示只读
create or replace view v_emp_dept_a as
select e.deptno , e.ename , d.dname from
emp e join dept d on e.deptno= d.deptno
with read only ;
select * from v_emp_dept_a;
--当不再需要视图的时候，用"drop view"撤销。删掉视图不会导致数据的丢失
--因为视图是基于数据库的表之上的一个查询定义
drop view v_emp_dept_a;
--创建视图必须要有主键，不然无法插入数据。
create or replace view v_emp_dept_b as
select e.empno , e.ename , e.sal , e.deptno from emp e;
select * from v_emp_dept_b;
--DML
insert into v_emp_dept_b (empno , ename , sal) values(2000,'zhiyi',3554);
update v_emp_dept_b set ename='weiwo', deptno=20 where ename='zhiyi';
delete from v_emp_dept_b where ename like 'weiwo'
-----------------------------------------------------------------------
--我们要求平均薪水的等级最低的部门，它的部门名称是什么？
--我们完全使用子查询
--求部门的平均薪水
select e.deptno dt , avg(e.sal) avg_sal from emp e group by deptno;
--创建子类视图
create or replace view deptno_avg_sal_a as
select e.deptno dt , avg(e.sal) avg_sal from emp e group by deptno;
select * from deptno_avg_sal_a;
--求部门薪水的等级
select t.dt dl , s.grade gr from deptno_avg_sal_a t join salgrade s on
t.avg_sal between losal and hisal;
--创建子类视图
create or replace view dept_grade_a as
select t.dt dl , s.grade gr from deptno_avg_sal_a t join salgrade s on
t.avg_sal between losal and hisal;
select * from dept_grade_a;
--求出最低等级
select min(gr) from dept_grade_a;
--求出部门名字
select d.dname from dept_grade_a de join dept d on de.dl = d.deptno
where de.gr = (select min(gr) from dept_grade_a);
select * from dept;
--删除视图
drop view dept_grade_a;
drop view deptno_avg_sal_a;
drop view v_emp_dept_b;
----------------------------------------------------------------------
--创建表
create table temp as
select *from emp where 1=2;
----where 1=2 创建的是空表单
select * from temp;
create table temp2 as
select *from emp where 1=1;
select * from temp2;
----where 1=1 创建的是全部数据的表单
--插入数据
insert into temp select * from emp;
--删除表单
drop table temp;
drop table temp2;
---------------------------------------------------------------------
--number(x,y) : 数字类型，最长x x位，y y位小数
--varchar2(maxlength) : 变长字符串，这个参数的上限是32767字节。
-- 声明方式如下 varchar2(L L)，L L 为字符串长度，
-- 没有缺省值，作为变量最大32767个字节
--char(max_length) : 定长字符串 ，最大2000个字节
--date : 日期类型(只能精确到秒)
--timestamp : 时间戳(精确到微秒)
--long : 长字符串，最大 2GB
--了解类型
--clob : 最大长度4G -->大对象很少使用：如果存在大对象，一般的解决方案
-- 存入文件地址(地址为程序所在应用服务器的相对路径)
--blob : 存二进制文件
--主键约束是数据库中最重要的一种约束。在关系中，主键值不可为空，也不
--允许出现重复，即关系要满足实体完整性规则。
--主键从功能上看相当于非空且唯一
--一个表中只允许一个主键
--主键是表中能够唯一确定一个行数据的字段
--主键字段可以是单字段或者多字段的组合
--oracle为主键创建对应的唯一性索引。
-------------------------------------------------------------------
--procedural language和sql的结合体。通过增加变量、控制语句，使我们可以写
-- 一些逻辑更复杂的数据库操作。
--匿名块由一下四部分组成：
--declare (可选)
--声明各种变量或游标的地方
--begin (必要)
--开始执行语句
-- 单行注释用两个连在一起的‘-’表示。
/*多行注释语句，可以换行*/
--exception (可选)
--出错后的处理
--end; (必要)(请注意end后面的分号)
--1.变量必须在 declare 语句块中声明
-- 变量声明的语法规则
--identifier [constant] datatype [not null] [:=| default expr];
--如：
--变量名 类型 := 初始值;
-- v_str varchar2(10) := 'abc';
--2.变量的命名规则
--变量名不能够使用保留字，如from、select等
--变量名最多包含30个字符
--不要与数据库的表或者列同名
--每一行只能声明一个变量
--建议：
--a).普通变量 v_
--b).常量 c_
--3.赋值语句：
--a).PL/SQL中的赋值使用 :=
--b)."=" 被用作比较操作符
--c).赋值语句中有一些规则需要遵守
-- 字符类型必须使用单引号
-- 不可以使用group by
-- 可以使用数学操作符或字符串连接操作符
--常用变量类型：
--普通变量
-- %type
-- %rowtype
-- varray
-- table
-- record
--变量声明规则
-- 变量名不能够使用保留字，如from、select等
-- 第一个字符必须是字母
-- 变量名最多包含30个字符
-- 不要与数据库的表或者列同名
-- 每行只能声明一个变量
-- 普通变量的类型主要有：
-- binary_integer : 整数，主要用来计数而不是用来表示字段类型
-- number(x,y) : 数字类型，最长x位，y位小数
-- varchar2(maxlength) : 变长字符串，这个参数的上限是32767字节
-- 声明方式如下 varchar2(L) , L为字符串长度，
-- 没有缺省值，作为变量最大32767个字节
--char(max_length) : 定长字符串
--date : 日期
--boolean : 布尔类型，可以取值为 true、false和null值
--table 义记录表(或索引表) 数据类型
--先声明table类型(声明具体的table类型)
--type table 类型的名称 is table of data_type [index by binary_integer];
--1.单选择语句：
-- if 条件 then
-- end if;
--2.双重选择：
-- if 条件 then
-- else
-- end if;
--3.多选择：
-- if 条件 then
-- elsif 条件 then
-- elsif 条件 then
-- else
-- end if;
--1.do-while 循环，先执行后判断：
-- loop
-- exit when 条件
-- end loop;
--2.while 循环，先判断后执行：
-- while 条件 loop
-- end loop;
--3.for 循环，能够确定次数，不用声明变量，增长变量
-- for index in[reverse] min..max loop
-- end loop;
-- index 不能手动改变值，内部自动维护 +1 或 -1
------------------------------------------------------------------
--cursor 分类：
-- |--显式 cursor
-- --静态 cursor --|
-- | |--隐式 cursor
--cursor --|
-- | |-- 强类型(限制)，规定返回类型
-- |--动态 cursor --ref cursor --|
-- |-- 弱类型(非限制)，不规定返回类型
-- --可以获取任何结果集
--隐式游标：
-- 可以使用名字sql来访问，但要注意，通过sql游标总是只能访问前一个处理
-- 操作或单行select操作的游标属性。所以通常在刚刚执行完操作之后，立即
-- 使用sql游标来操作属性。游标的属性有4种。分别是：
-- sql %isopen , sql %found , sql %notfound , sql %rowcount
--sql%isopen 返回的类型是布尔型，判断游标是否被打开，如果打开 %ispen 等于
-- true，否则等于false，即执行过程中为真，结束后为假。
--sql%notfound 返回值为布尔型，判断游标所在的行是否有效，如果有效，则
-- %found 等于true，否则等于false，即与%found 属性返回值相反。
--sql%found 返回值的类型为布尔值，值为true代表插入、删除、更新或单行查询
-- 操作成功。
--sql%rowcount 返回值类型为整形，返回当前位置为止游标读取的记录行数，即成功
-- 执行的数据行数。
------------------------------------------------------------------------
--显式游标：
-- 主要是用于对查询语句的处理，尤其是在查询结果为多条记录的情况下；
--隐式游标：
-- 而对于非查询语句，如修改、删除操作，则由oracle系统自动地为这些操作设置
-- 操作设置游标并创建其工作区，这些由系统隐含创建的游标称为：隐式游标。
-- 隐式游标的名字为sql，这是由 oracle 系统定义的。对于隐式游标的操作，如
-- 定义、打开、取值及关闭操作，都由 oracle 系统自动地完成，无需用户进行
-- 处理。用户只要通过隐式游标的相关属性，来完成相应的操作。在隐式游标的
-- 工作区中，所存放的数据是与用户自定义的游标无关的、最新处理的一条sql
-- 语句所包含的数据。
-- 格式调用为：sql%
-- 注： insert , update , delete , select 语句中不必明确定义游标。
--1.游标：cursor
-- 游标就是指在某个结果集上的指针，通过这个指针的移动，我们得以遍历整个结果集
--2.游标的使用步骤(标准)
-- 1).声明游标
-- 2).打开游标
-- 3).处理游标中的数据
-- 4).关闭游标
--ref 游标：动态游标
-- 1).在运行时使不同的语句与之关联
-- 2).ref游标可以使用游标变量
--属性引用类型的一种 ，在运行时动态 的改变游标的指向。for循环中不能使用
--ref游标，因为for自动打开游标
-- 1).弱类型游标：
-- 没有返回类型的游标变量，有动态关联sql字符串
-- type refempcur is ref cursor;
-- 2).强类型游标
-- 有返回类型的游标变量，不能关联sql字符串，只能关联sql命令
-- type refempcur is ref cursor return emp%rowtype;
--- 游标仅能打开emp表的数据。
-- open 游标名： for select_statement
------------------------------------------------------------------
--创建存储过程
create [or replace] procedure procedure_name
([arg1 [in | out | in out]] type1 [default value1] ,
[arg2 [in | out | in out]] type2 [default value2] ,
--.....
[argn [in | out | in out]] typen [default valuen])
[authid definer | current_user]
{is | as}
--<声明部分>
begin
--<执行部分>
exception
--<可选的异常错误处理程序>
end;
----------------------------------------------------------------------
--触发器
-- 触发器是数据库发生某个操作时自动运行的一类的程序
-- 用于保持数据的完整性或记录数据库操作信息方面
-- 触发器不能够被直接调用，只能够在某些事件发生时触发，也就是系统自动调用
--触发器的构建语法：
-- create [or replace] trigger trigger_name
-- before | after
-- event1 [or event2 or event3 ....]
-- on table_name [for each row]
-- begin
-- statement;
-- ...
-- end;
--event通常是 insert 、delete 或 update 等DML操作
--触发器分为语句级触发器和行级触发器
--语句级触发器是指每执行一条DML语句，该触发器就执行一次
--行级触发器是指每个DML操作影响几条记录，就会触发几次 (for each row)
--行级触发器中由于涉及到了每条记录的数据变动，所以对于每条记录的数据来说
--就有新值和旧值之分。
--用关键字 :new 和 :old 来代表新的记录和旧的记录。
--jdbc 调用 存储过程：
public class Test{
public static void main(String[] args){
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
Connection conn = DriverManager.getConnection(url,"scott","tiger");
System.out.println(conn.isClosed());
CallableStatement cs = conn.prepareCall("{call delRepeat(?,?,?)}");
cs.setString(1,"emp");
cs.setString(2,"ename");
cs.registerOutParameter(3,OracleTypes.NUMBER);
cs.executeQuery();
System.out.println(cs.getObject(3));
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
-----------------------------------------------------------------------------
--外传：
--case when 方法
--decode 方法
--为所有人涨工资，标准是：10部门涨10%；20%部门涨15%；30部门涨20%；
--其他部门涨18%。
select * from emp;
select ename, (case deptno when 10 then sal*1.1
when 20 then sal*1.15
when 30 then sal*1.2
else sal*1.18 end) v_sal
from emp e;
--等价于下面的：
select ename, decode(deptno ,10 , sal*1.1,20 ,
sal*1.15 , 30, sal*1.2 ,sal*1.18) v_sal
from emp e;
------------------------------------------------------------------
------------------------------------------------------------------
select * from emp e where sal in (800,2000,3000,5000);
select * from emp e where ename in ('KING','JONES','FORD','MARTIN');
select *from emp e where sal between 1200 and 4000;
select * from emp e where e.deptno not in (20);
select * from emp e where e.deptno != 20;
select * from emp e where (e.deptno = 10 or e.deptno = 30)
and e.sal > 1500;
select * from emp e where e.deptno in (10 , 30) and e.sal >1500;
select * from emp e where e.hiredate > to_date('1981-02-05' , 'yyyy-mm-dd');
-----------------------------------------------------------------------
--1、查询部门编号为10的员工信息
select * from emp where deptno = 10;
--2、查询年薪大于3万的人员的姓名与部门编号
select * from emp e where (e.sal+nvl(comm , 0))*12 > 30000;
--3、查询佣金为null的人员姓名与工资
select * from emp e where comm is null;
--4、查询工资大于1500 且 and 含有佣金的人员姓名
select * from emp e where e.sal > 1500 and comm is not null;
--5、查询工资大于1500 或 or含有佣金的人员姓名
select * from emp e where e.sal > 1500 or comm is not null;
--6、查询姓名里面含有 S 员工信息 工资、名称
select * from emp e where e.ename like '%S%';
--7、求姓名以J开头第二个字符O的员工姓名的与工资
select * from emp e where e.ename like 'JO%';
--8、求包含%的雇员姓名
select * from emp e where e.ename like '%A%%' escape 'A';
--9、使用in查询部门名称为 SALES 和 RESEARCH 的雇员姓名、工资、部门编号
-- 用 distinct去重
select * from emp;
select distinct e.ename , e.sal , e.deptno from emp e , dept d
where d.dname in ('SALES' , 'RESEARCH') and e.deptno = d.deptno;
--10、使用exists查询部门名称为SALES和RESEARCH 的雇员姓名、工资、部门编号。
-- exists是判断exits后面的sql语句是否为真,若为真则整个sql句子成立,
-- 否则没有任何记录。语法如下：
-- ** select * from a where exists (select g from b where a.id = b.id);
select distinct e.ename , e.sal , e.deptno from emp e
where exists (select * from emp e ,dept d
where d.dname in ('SALES' , 'RESEARCH') and d.deptno = e.deptno);
---------------------------------------------------------------------
select * from emp e order by e.sal desc , e.ename asc;
select e.ename || ' is a ' || e.job from emp e;
---------------------------------------------------------------------
--1、使用基本查询语句.
--(1)查询DEPT表显示所有部门名称.
select d.dname from dept d;
--(2)查询EMP表显示所有雇员名及其全年收入(月收入=工资+补助),
--处理NULL行,并指定列别名
--为"年收入"。（NVL(comm,0) comm取空值时用0替代）
select e.ename , (e.sal+nvl(comm,0))*12 sal_all from emp e;
--(3)查询显示不存在雇员的所有部门号。
select d.deptno from dept d
where d.deptno not in (select distinct e.deptno from emp e
where d.deptno = e.deptno);
--2、限制查询数据
--(1)查询EMP表显示工资超过2850的雇员姓名和工资。
select e.ename , e.sal from emp e where e.sal >2850;
--(2)查询EMP表显示工资不在1500～2850之间的所有雇员及工资。
select e.ename , e.sal from emp e where e.sal not in (1500 , 2850);
--(3)查询EMP表显示代码为7566的雇员姓名及所在部门代码。
select e.ename , e.deptno from emp e where e.empno = 7566;
--(4)查询EMP表显示部门10和30中工资超过1500的雇员名及工资。
select e.ename , e.sal from emp e where e.deptno in (10,30) and e.sal >1500;
--(5)查询EMP表显示第2个字符为"A"的所有雇员名其工资。
select e.ename , e.sal from emp e where e.ename like '_A%';
--(6)查询EMP表显示补助非空的所有雇员名及其补助。
select e.ename , e.comm from emp e where comm is not null;
--3、排序数据
--(1)查询EMP表显示所有雇员名、工资、雇佣日期，并以雇员名的升序进行排序。
select e.ename , e.sal , e.hiredate from emp e order by e.ename asc;
--(2)查询EMP表显示在1981年2月1日到1981年5月1日之间雇佣的雇员名、
-- 岗位及雇佣日期，并以雇佣日期进行排序。
select e.ename , e.job , e.hiredate from emp e
where e.hiredate >= to_date('1981-02-01' ,'yyyy-mm-dd') and
e.hiredate <= to_date('1981-05-01' , 'yyyy-mm-dd')
order by e.hiredate desc;
--或
select e.ename , e.job , e.hiredate from emp e
where e.hiredate >= to_date('1981-02-01' ,'yyyy-mm-dd') and
e.hiredate <= to_date('1981-05-01' , 'yyyy-mm-dd')
order by e.hiredate asc;
--(3)查询EMP表显示获得补助的所有雇员名、工资及补助，并以工资升序和
--补助降序排序。
select e.ename , e.sal , e.comm from emp e
where (e.comm is not null and e.comm != 0) order by e.sal , e.comm desc;
-----------------------------------------------------------------------------
select lower(ename) from emp e ;
select upper(lower(ename)) from emp
select substrb('流aa川abc',1,4) from dual;
select ename , substr(ename , 2) from emp e;
select ename , length(ename) from emp
select ename , lpad(ename , '6' , '*') from emp;
select ename , rpad(ename , '6' ,'*') from emp;
select ename , instr(ename , 'A') from emp;
select ename , instr(ename , ename) from emp;
select ename , replace(ename , 'A' , '-') from emp;
select rtrim('xxgao lao shixxx' , 'x') text from dual;
select trim(both 'x' from 'xxxgao lao shixxx') from dual;
select ceil(19.2) from dual;
select floor(19.2) from dual;
select mod(19,2) from dual;
select trunc(23.653) from dual;
select trunc(23.653,2) from dual;
select trunc(23.653,-1) from dual;
select round(23.653) from dual;
select round(23.653,2) from dual;
select round(23.653,-1) from dual;
select sysdate from dual;
select current_date from dual;
select add_months(sysdate , 3) from dual;
select months_between(sysdate,add_months(sysdate,3)) from dual;
select sysdate-2 from dual;
select sysdate+2 from dual;
select ename , round((sysdate-hiredate)/7) weeks from emp e where e.deptno = 10;
select * from emp where empno = to_number('7369');
select * from emp e where hiredate = '20-2月-1981';
select * from emp e where hiredate = to_date('1981-02-20' , 'yyyy-mm-dd');
select '199'-90 from dual;
select to_char(sysdate,'yyyy-mm-dd hh24 : mi : ss') from dual;
select ename , hiredate , to_char(hiredate , 'yyyy-mm-dd hh24:mi:ss') from emp;
select to_char(sal , '$99,999.9999') salary from emp where ename = 'ALLEN';
select to_char(sal , '$00,000.0000') salary from emp where ename = 'ALLEN';
select to_char(sal , 'L00,000.0000') salary from emp where ename = 'ALLEN';
select to_date('04,05,19,23,40' ,'yy,mm,dd,hh24,mi,ss') from dual;
select to_date('2004-09-19' , 'yyyy-mm-dd') from dual;
select to_number('$39343.783' , '$99990.000') from dual;
select to_number('11.231','999.999') from dual;
select e.*, nvl(to_char(mgr) , 'boss') mgr from emp e where mgr is null;
select to_char(next_day(add_months(hiredate , 6) , '星期五'),'yyyy-mm-dd') dat
from emp e order by hiredate;
---------------------------------------------------------------------
--1、查询82年员工
select * from emp e where to_char(e.hiredate ,'yy') like '82';
--2、查询36年工龄的人员
select * from emp e where (sysdate - to_date(hiredate))/365 > 36;
--3、显示员工雇佣期 6 个月后下一个星期一的日期
select e.ename , to_char(next_day(add_months(hiredate , 6),'星期一'),
'yyyy-mm-dd') from emp e order by hiredate asc;
--4、找没有上级的员工，把mgr的字段信息输出为 "boss"
select ename , nvl(to_char(mgr) , 'boss') from emp e where mgr is null;
--5、为所有人长工资，标准是：10部门长10%；20部门长15%；30
--部门长20%其他部门长18%
--case when 方法：
select e.ename , (case deptno when 10 then e.sal*1.1 when 20 then e.sal*1.15
when 30 then e.sal*1.2 else e.sal*1.18 end) v_sal
from emp e;
--decode 方法:
select e.ename , decode(deptno , 10,e.sal*1.1, 20,e.sal*1.15, 30,e.sal*1.2,
e.sal*1.18) v_sal from emp e;
----------------------------------------------------------------
select sum(e.sal) , avg(e.sal) , min(sal) , max(e.sal) from emp e;
select min(hiredate) , max(hiredate) from emp e;
select count(1) from emp e ;
select count(*) from emp e ;
select count(comm) from emp ;
select count(mgr) from emp;
select count(distinct deptno) from emp;
select avg(nvl(comm,0)) from emp e ;
select deptno , avg(sal) from emp group by deptno;
select deptno , avg(sal) from emp group by deptno having avg(sal) > 2000;
--• 求部门下雇员的平均工资>2000 人数
select deptno , count(1) from emp group by deptno
having deptno in (select deptno from emp e
group by deptno having avg(sal) > 2000);
--部门薪水最高
select deptno , max(sal) max_sal from emp e group by deptno;
select max(e.max_sal) from (select max(sal) max_sal from
emp e group by deptno) e
select max(sal) , deptno , job from emp group by deptno , job;
select avg(sal) from emp where sal > 1200
group by deptno having avg(sal) > 1500
order by avg(sal) asc;
-- 部门里面 工龄最小和最大的人找出来
--找出部门里面最大和最小工龄
select deptno , max(hiredate) min_date , min(hiredate) max_date
from emp e group by deptno;
--找出工龄对应的名字
select e.deptno , e.ename eename , e.hiredate from emp e join
(select deptno dt , max(hiredate) min_date , min(hiredate) max_date
from emp e group by deptno) t on (e.hiredate = t.min_date or
e.hiredate = t.max_date) and e.deptno = t.dt order by e.deptno;
---------------------------------------------------------------------
--1、查询10号部门中编号最新入职的员工，工龄最长的员工的个人信息。
select e.* from emp e join
(select deptno dt , max(hiredate) min_date ,min(hiredate) max_date
from emp e group by deptno) t on (e.hiredate = t.min_date or
e.hiredate = t.max_date) and e.deptno = 10;
--2、从“software”找到‘f’的位置，用‘*’左或右填充到15位，去除其中的‘a’。
--–Instr（）字符串出现的位置, instr（ string ，’A‘）
--–lpad,rpad 填充字符型数据
--–ltrim/rtrim （string1,string2）
select instr('software','f') , lpad('software' , 15 , '*'),
rpad('software' , 15 ,'*'),
replace('software','a','') from dual;
--3、查询员工的奖金，如果奖金不为NULL显示‘有奖金’，为null则显示无奖金
select e.ename , nvl2(comm,'有奖金','无奖金') from emp e;
select e.ename , decode(comm,null,'无奖金','有奖金') from emp e;
--4、写一个查询显示当前日期，列标题显示为Date。再显示六个月后的日期，
--下一个星期日的日期，该月最后一天的日期。
select sysdate dat , add_months(sysdate , 6) , next_day(sysdate,'星期日'),
last_day(sysdate) from dual;
--5、查询EMP表按管理者编号升序排列，如果管理者编号为空则把为空的在最前显
--示
select * from emp order by mgr nulls first;
--6、求部门平均薪水
select deptno , avg(sal) from emp e group by deptno;
--7、按部门求出工资大于1300人员的 部门编号、平均工资、最小佣金、
--最大佣金,并且最大佣金大于100
select deptno ,avg(sal) , min(comm),max(comm) from emp e
where sal > 1500 group by deptno having max(comm)>100;
--8、找出每个部门的平均、最小、最大薪水
select deptno , avg(sal) , min(sal) , max(sal) from emp e group by deptno;
--9、查询出雇员名，雇员所在部门名称， 工资等级
select e.ename, d.deptno , s.grade from emp e ,dept d , salgrade s
where sal between losal and hisal and e.deptno = d.deptno;
select * from emp , salgrade where sal between losal and hisal;
select e.empno , e.ename , d.deptno , d.loc from emp e , dept d
where e.deptno = d.deptno and e.ename like 'JAMES';
select e.empno , e.ename ,d.dname , d.loc , d.deptno from emp e, dept d
where e.deptno = d.deptno and e.deptno = 10;
select * from emp e ,salgrade s where sal between losal and hisal;
select e.ename , e.deptno , d.dname from emp e , dept d
where e.deptno = d.deptno(+);
select e.ename , e.deptno , d.dname from emp e , dept d
where e.deptno(+) = d.deptno;
-----------------------------------------------------------------
--99语法
select e.empno , e.ename , e.deptno , d.loc from emp e , dept d
where e.deptno = d.deptno;
select e.empno , e.ename , e.deptno , d.loc from emp e
cross join dept d where e.deptno = d.deptno;
select * from emp e natural join dept d where deptno = 10;
select e.empno , e.ename , e.sal , deptno , d.loc from emp e join dept d
using(deptno) where deptno = 10;
select e.ename , d.dname , s.grade from emp e join dept d on
e.deptno = d.deptno
join salgrade s on e.sal between losal and hisal;
select * from emp e right outer join dept d on e.deptno = d.deptno;
select * from emp e left outer join dept d on e.deptno = d.deptno;
select * from emp e right join dept d on e.deptno = d.deptno;
select * from emp e inner join dept d on e.deptno = d.deptno;
select * from emp e join dept d on e.deptno = d.deptno;
select * from emp e join dept d using(deptno);
--查找那些薪水在整个雇员平均薪水之上的人
select * from emp e where (e.sal+nvl(comm , 0)) > (select
avg(sal+nvl(comm , 0)) from emp);
--找出所有的经理人
select distinct e.ename from emp e , emp em where e.empno = em.mgr;
select *from emp e where empno in (select distinct mgr from emp);
--找出部门20最高收入的职员
select e.ename , e.sal , e.comm from emp e join (select deptno ,
max(sal+nvl(comm,0)) max_sal from emp group by deptno) em
on e.deptno = 20 and e.sal = em.max_sal;
select e.ename , e.sal , e.comm from emp e
where (e.sal+nvl(comm , 0)) >= all(select (sal+nvl(comm , 0))
from emp where deptno =20) and deptno = 20;
--求每个部门平均薪水的平均水平
--求部门平均薪水
select deptno dt , avg(sal) avg_sal from emp e group by e.deptno;
--求等级
select t.dt , t.avg_sal , s.grade from (select deptno dt , avg(sal) avg_sal
from emp e group by e.deptno) t join salgrade s on t.avg_sal between
losal and hisal;
---------------------------------------------------------------------
--1、求平均薪水最高的部门的部门编号
--求部门的平均薪水
select deptno , avg(sal+nvl(comm , 0)) from emp group by deptno ;
--求出最大值
select max(max_avg) from (select deptno dt ,avg(sal+nvl(comm , 0)) max_avg
from emp group by deptno) t
--求出部门编号
select k.dt , k.sal_avg from
(select e.deptno dt , avg(sal+nvl(comm , 0)) sal_avg from emp e
group by deptno) k where k.sal_avg = (select max(max_avg) from
(select deptno dt ,avg(sal+nvl(comm , 0)) max_avg
from emp group by deptno) t )
--2、求部门平均薪水的等级
select k.dt , k.avg_sal , s.grade from (select e.deptno dt , avg(sal) avg_sal
from emp e group by deptno) k join salgrade s on k.avg_sal
between losal and hisal;
--3、求部门平均的薪水等级
--求部门的薪水等级
select e.deptno dt , e.sal sl, s.grade gd from emp e join salgrade s on e.sal
between losal and hisal;
--求平均值
select k.dt , avg(k.sl) , avg(k.gd) from (select e.deptno dt , e.sal sl,
s.grade gd from emp e join salgrade s on e.sal
between losal and hisal) k group by k.dt;
--4、求薪水最高的前5名雇员
--先排列薪水
select * from emp e order by e.sal desc;
--截取前五名
select t.* ,rownum rm from (select * from emp e order by e.sal desc) t
where rownum <= 5;
--5、求薪水最高的第6到10名雇员
select k.* from (select t.* ,rownum rm from (select * from emp e order by
e.sal desc) t where rownum <= 10) k where k.rm >= 6;
---------------------------------------------------------------------
---------------------------------------------------------------------
declare
type tb_mytable is table of dept.deptno%type index by
binary_integer;
v_tb_mytable tb_mytable;
begin
dbms_output.put_line('hello world');
end;
--do while 循环：
declare
v_num number(3,0) := 0;
begin
loop
dbms_output.put_line(v_num);
v_num := v_num + 2;
exit when v_num = 100;
end loop;
end;
--while 循环
declare
v_num number(3,0) := 0;
begin
while v_num != 100 loop
dbms_output.put_line(v_num);
v_num := v_num + 2;
end loop;
end;
--while循环：
declare
type t is table of number(3) index by varchar2(3);
hash_t t;
l_row varchar2(3);
begin
hash_t('a') := 10;
hash_t('b') := 20;
l_row := hash_t.first;
while(l_row is not null) loop
dbms_output.put_line(hash_t(l_row));
l_row := hash_t.next(l_row);
end loop;
end;
--for循环：
declare
type t is table of number(3) index by pls_integer;
hash_t t;
l_row varchar2(3);
begin
hash_t(1) := 10;
hash_t(2) := 20;
for i in hash_t.first..hash_t.last loop
dbms_output.put_line(hash_t(i));
end loop;
end;
--if条件句：
declare
i integer;
b01 boolean := true;
begin
if b01 then
dbms_output.put_line('test');
end if;
end;
declare
v_name varchar2(20);
begin
select ename into v_name from emp e where
e.deptno = 20 and e.empno = &empno;
dbms_output.put_line(v_name);
exception
when others then
dbms_output.put_line('数据未找到');
rollback;
end;
select * from emp;
declare
v_empno emp.empno%type := &empno;
v_comm emp.comm%type;
begin
select comm into v_comm from emp e where e.empno = v_empno;
if(v_comm is null) then
dbms_output.put_line('无奖金');
else
dbms_output.put_line('有奖金');
end if;
exception
when others then
dbms_output.put_line(sqlcode||'-----'||sqlerrm);
rollback;
end;
declare
v_empno emp.empno%type := &empno;
v_comm emp.comm%type;
begin
select comm into v_comm from emp e where e.empno = v_empno;
if(v_comm is null) then
dbms_output.put_line('无奖金');
elsif(v_comm > 0 and v_comm < 800) then
dbms_output.put_line('奖金少的可怜');
else
dbms_output.put_line('奖金可观');
end if;
exception
when others then
dbms_output.put_line(sqlcode||'----'||sqlerrm);
rollback;
end;
-----------------------------------------------------------
-------------------------------------------------------------
--创建表
create table v_temp02(
vno number(2),
vname varchar2(20),
vage number(3)
);
--增
insert into v_temp02 values(10 , '王哥' , 18);
insert into v_temp02 values(11 , '杨哥' , 19);
insert into v_temp02 values(12 , '张哥' , 17);
insert into v_temp02 values(13 , '黄哥' , 20);
insert into v_temp02 values(14 , '文哥' , 21);
--commit提交事务，可加可不加，加了之后，就直接提交，而没机会回滚了
commit;
--查
select * from v_temp02;
--修改数据
begin
update v_temp02 set vname = '忠哥' , vage = 18 where vno = 12;
if sql%found then
dbms_output.put_line('更新了'||sql%rowcount||'记录');
end if;
commit;
if not sql%isopen then
dbms_output.put_line('自动关闭游标');
end if;
end;
--删除数据
delete from v_temp02 where vno = 11;
commit;
--删除表
drop table v_temp02;
-----------------------------------------------------------------
--游标
--如抓取一条数据
declare
cursor my_cursor is select * from dept; --声明游标(定义的游标不能有into句子)
v_dept dept%rowtype; --定义变量
begin
open my_cursor; --打开游标
fetch my_cursor into v_dept; --使用游标
dbms_output.put_line(v_dept.deptno||'----'||v_dept.dname||
'---'||v_dept.loc);
close my_cursor; --关闭游标
end;
--loop循环(do-while)遍历游标，利用loop循环和%notfound属性实现游标遍历
declare
cursor my_cursor is select * from dept; --声明游标
v_dept dept%rowtype; --定义变量
begin
open my_cursor; --打开游标
loop --打开loop(do-while)循环
fetch my_cursor into v_dept; --使用游标
exit when my_cursor%notfound; --定义游标停止条件
dbms_output.put_line(v_dept.deptno||'---'||
v_dept.dname||'---'||v_dept.loc);
end loop; --关闭loop循环
close my_cursor; --关闭游标
exception --一次
when others then
dbms_output.put_line(sqlcode||'---'||sqlerrm);
rollback;
end;
--while循环遍历游标，利用while循环配合%found属性实现游标遍历。
declare
cursor my_cursor is select * from dept; --声明游标
v_dept my_cursor%rowtype; --定义变量
begin
open my_cursor; --打开游标
fetch my_cursor into v_dept; --使用游标
while my_cursor%found loop --打开while循环
dbms_output.put_line(v_dept.deptno||'---'||
v_dept.dname||'---'||v_dept.loc);
fetch my_cursor into v_dept; --使用游标
end loop; --关闭循环
close my_cursor; --关闭游标
exception --异常
when others then
dbms_output.put_line(sqlcode||'---'||sqlerrm);
rollback; --回滚
end;
--for循环遍历游标，利用for循环遍历游标，不需要打开游标，也不需要关闭游标
--甚至不用声明循环变量，不用抓取，最简单。推荐使用
declare
cursor my_cursor is select * from dept;
v_dept dept%rowtype;
begin
for v_dept in my_cursor loop
dbms_output.put_line(v_dept.deptno||'---'||v_dept.dname||
'---'||v_dept.loc);
end loop;
exception
when others then
dbms_output.put_line(sqlcode||'---'||sqlerrm);
rollback;
end;
-------------------------------------------------------------------
--使用游标，更新状态数据，如果年薪 < 5000 低 [5000,8000] 中 >= 8000 高
--复制表
create table copy_emp02 as select * from emp where 1=1;
select * from copy_emp02;
--加入状态列
alter table copy_emp02 add status varchar2(20);
--带参数的游标
declare
cursor my_cursor(v_no dept.deptno%type) is
select * from dept d where deptno = v_no;
v_dept dept%rowtype;
begin
for v_dept in my_cursor(&v_no) loop
dbms_output.put_line('第'||my_cursor%rowcount||'行，数据为：'||
v_dept.deptno||'---'||v_dept.dname||'---'||v_dept.loc);
end loop;
end;
--参数01
declare
cursor my_cursor(v_sal number) is select * from emp e
where e.sal > v_sal;
v_emp emp%rowtype;
begin
for v_emp in my_cursor(1500) loop --sal大于1500的雇员
dbms_output.put_line(v_emp.ename||'---'||v_emp.sal);
end loop;
end;
--参数02
declare
cursor my_cursor(v_sal number default 2000) is select * from emp e
where e.sal > v_sal; --默认值为2000，即sal > 2000的雇员
v_emp emp%rowtype;
begin
for v_emp in my_cursor(v_sal => 1500) loop
dbms_output.put_line(v_emp.ename||'---'||v_emp.sal);
end loop;
exception
when others then
dbms_output.put_line(sqlcode||'---'||sqlerrm);
rollback;
end;
--参数03
declare
cursor my_cursor(v_sal number default 2000) is select * from emp e
where e.sal > v_sal; --默认值为2000，即sal > 2000的雇员
v_emp emp%rowtype;
begin
for v_emp in my_cursor loop --带默认值的参数 ，可不加括号和参数
dbms_output.put_line(v_emp.ename||'---'||v_emp.sal);
end loop;
end;
-------------------------------------------------------------------
--声明游标，有参数有返回值
select * from emp where deptno = 10;
declare
type emp_record is record(
v_ename emp.ename%type,
v_hiredate emp.hiredate%type
);
v_record emp_record;
cursor my_cursor(v_deptno number , v_job varchar2) return emp_record
is select ename , hiredate from emp where deptno = v_deptno
and job = v_job;
v_emp emp%rowtype;
begin
open my_cursor(10 , 'CLERK');
loop
fetch my_cursor into v_record;
if my_cursor%found then
dbms_output.put_line(v_record.v_ename||'的雇佣日期是'||v_record.v_hiredate);
else
dbms_output.put_line('已经处理结束了');
exit;
end if;
end loop;
close my_cursor;
exception
when others then
dbms_output.put_line(sqlcode||'---'||sqlerrm);
rollback;
end;
--基于游标定义记录变量，比声明记录类型变量要方便
declare
cursor my_cursor(v_deptno number , v_job varchar2) is select ename ,
hiredate from emp e where e.deptno = v_deptno and e.job = v_job;
v_cursor my_cursor%rowtype;
v_emp emp%rowtype;
begin
open my_cursor(10 , 'CLERK');
loop
fetch my_cursor into v_cursor;
if my_cursor%found then
dbms_output.put_line(v_cursor.ename||'的雇佣日期是'||v_cursor.hiredate);
else
dbms_output.put_line('已经处理完毕');
exit;
end if;
end loop;
close my_cursor;
end;
--更新或删除
create table emp2 as select * from emp ; --创建新版，不影响旧表
select * from emp2;
--在这个例子中，我们同个select语句后面添加 for update
--来提示oracle锁定记录以便进行更新，然后用 where current of
--来指明操作时添加在当前游标所指向的记录上
declare
cursor my_cursor is select * from emp2 for update;
v_temp my_cursor%rowtype;
begin
for v_emp in my_cursor loop
if (v_emp.sal < 2000) then
update emp2 set sal = sal * 2 where current of my_cursor;
elsif(v_emp.sal = 1500) then
delete from emp2 where current of my_cursor;
end if;
end loop;
commit;
end;
--删除表emp2
drop table emp2;
-------------------------------------------------------------------
--动态游标：弱类型游标
declare
type RefEmpCur is ref cursor;
mycur RefEmpCur;
v_emp emp%rowtype;
begin
open mycur for select * from emp;
loop
fetch mycur into v_emp;
exit when mycur%notfound;
dbms_output.put_line(v_emp.deptno||'---'||v_emp.ename||'---'||
v_emp.sal);
end loop;
close mycur;
end;
--多语句
declare
type RefEmpCur is ref cursor;
mycur RefEmpCur;
v_emp emp%rowtype;
v_dept dept%rowtype;
begin
open mycur for select * from emp;
loop
fetch mycur into v_emp;
exit when mycur%notfound;
dbms_output.put_line(v_emp.deptno||'---'||v_emp.ename||'---'||
v_emp.sal);
end loop;
close mycur;
open mycur for select * from dept;
loop
fetch mycur into v_dept;
exit when mycur%notfound;
dbms_output.put_line(v_dept.deptno||'---'||v_dept.dname||'---'||
v_dept.loc);
end loop;
close mycur;
end;
--sys_refcursor
declare
--type ref_cursor is ref cursor;
--cur01 ref_cursor;
cur01 sys_refcursor;
v_emp emp%rowtype;
v_sql varchar2(100);
begin
v_sql := 'select * from emp';
open cur01 for v_sql;
loop
fetch cur01 into v_emp;
exit when (cur01%notfound);
dbms_output.put_line(v_emp.ename);
end loop;
close cur01;
end;
-------------------------------------------------------------------
select * from student_score;
--创建存储过程
-------------------------------------------------------------------
--实现一个函数判断数据是时间格式类型
create or replace function isDate(in_date in varchar2) return number
is v_result number(2);
v_date date;
begin
v_date := to_date(in_date , 'yyyy-mm-dd');
v_result := case when v_date is not null then 1 else 0 end;
return v_result;
exception
when others then
dbms_output.put_line(sqlcode||'---'||sqlerrm);
rollback;
end;
select isDate('1982-02-10') from dual;
--trigger 触发器例子
create
or replace trigger trig_temp after update on dept for each row
begin
update emp set deptno =: new.deptno where deptno =: old.deptno;
end;
--如果没有触发器，是不能直接更新的
update dept set deptno = 90 where deptno = 30
select * from dept;