oracle-操作语言

超管登录：sqlplus / as sys(用户名)dba(角色)
解锁用户:alter user scott account unlock;
设置密码:alter user scott identified by tiger;
解锁用户并设置密码:alter user scott identified by tiger account unlock;
显示表结构: desc 表名;
普通用户登录：sqlplus scott(用户名)/tiger(密码)
显示当前用户:show user
更改密码：password
退出sqlplus:exit
打开实时监控时间
set time on;
set time off;
打开sql每执行一次命令时间
set timing on;
set timing off;
sqlplus默认不显示程序执行结果，set serveroutput on/off
设置显示列宽:column 列名 format 9;(表示数字型宽度，一个9一个宽度)
column 列名 format a12;(忽略大小写，表示字符型宽度，12代表12个宽度)

设置一页最多显示80条数据记录:set pagesize 80;

执行最近一次sql语句：/

清屏(sqlplus工具)：host cls；

结果为null，sqlplus工具不显示，null与数值进行运算，结果为null。

解决null的问题：使用NVL(a,b)，a为null，b替代。反之亦然。

列别名：as 别名。（as忽略大小写，不加双引号的别名中间不能空格。添加双引号则可以。不能添加单引号，
因为oracle单引号代表字符串或日期）

dual：表示哑表或者伪表

||符号：连接字符串

使用哑表和||输出helloword，oracle中from必须写。select ‘HELLO’||’WORLD’ from dual;

显示本地时间：select sysdate from dual;只显示日期，不显示时间，默认显示格式为：30-3月-16

ename的薪水为：salary美元
select ename || ‘的薪水为：’ || salary || ‘美元’ from tablename;

使用sqlplus工具命令，保存sql语句到硬盘文件e：/oracle.sql
spool e:/oracle.sql;
写入e：/oracle.sql;
spool off;

使用命令，读取sql文件到orcl实例中，并执行。
@e：/oracle.sql;

注释://单行注释 , /多行注释/

查看表结构：
desc 表名

SQL语句的特点
1）是SQL92/99的ANSI官方标准，只要按照该标准来写，在任何的关系型数据库中都可以直接执行
2）SQL语句的关健字不能简写，例如：select，where，from
3）大小写不敏感，提倡大写
4）能够对表数据进行增删改查操作
5）必须以分号结束
6）通常称做语句

SQLPLUS命令的特点
1）是oracle自带的一款工具，在该工具中执行的命令叫SQLPLUS命令
2）SQLPLUS工具的命令中的关健字可以简写，也可以不简写，例如：col ename for a10;
3）大小写不敏感，提倡大写
4）不能够对表数据进行增删改查操作，只能完成显示格式控制，例如：设置显示列宽，清屏，记录执行结果
5）可以不用分号结束，也可以用分号结束，个人提倡不管SQL或SQLPLUS，都以分号结束
6）通常称做命令，是SQLPLUS工具中的命令
注意：SQLPLUS命令是SQLPLUS工具中特有的语句

单引号出现地方：
1:字符型：’hello’||’world’
2:日期型：’30-3月-16’
3:to_char/to date(日期,’yyyy-mm-dd hh12:mi:ss’)

双引号出现地方：
1:列别名
2:to_char//to date(日期,’yyyy”年”mm”月”dd”日”hh12:mi:ss’)

select * from table_Name where ename=’大小写敏感’
！=和<>意思一样
对于日期和数值型，where时，小数值在前，大数值在后。

模糊查询
like ‘%n’:查询以n结尾
like ‘n%’:查询以n开始
like ‘m%n’:查询第一个字母为m，最后一个字母为n
like ‘n_‘：查询长度是4个字符，且第二个为n的员工，一个_表示一个字符

查询table所有信息。
select * from table;
select * from table where column_Name like ‘%’;
select * from table where column_Name like ‘%_%’;

查询员工姓名中含有’_’的员工,’%_%’ escape ‘\’:让(可任意符号)后面的字符恢复本来意思
select * from table where ename like ‘%_%’ escape ‘\’;

插入姓名为‘的员工,2个单引号表示一个单引号
insert into table(name) values (””)

使用order by语句时null看成最大值

列是数值型，过滤条件也要是数值型，字符串里面是数值可隐式转换。
列是字符型，过滤条件也要字符型。
单行函数
lower(全小写)/upper(全大写)/initcap(首字母大写)
select upper(‘HeLlo’) from dual;
select lower(‘HeLlo’) from dual;
select initcap(‘heLlo.cOm’) from dual;

concat(连接字符，只能连接2个)/substr(截取字符)
select concat(‘hello’,’world’) from dual;
select substr(‘helloWorld’,6,5)from dual;//从第6位开始，取5位字符，中英文统一处理。

length/lengthb,测试字符(字节)长度。
select length(‘你好World’) from dual;//7,字符长度。
select lengthb(‘你好World’) from dual;//11，字节长度，与编码有关系，gbk：2字节/utf-8：3字节

instr/lpad/rpad
select instr(‘hellooo’,’o’)from dual;查询o第一次首次出现位置。找不到返回0，大小写敏感。
select lpad(‘hello’,10,’‘)from dual;//长度不足10位，从左到右用补到10位,rpad则从右边补。
select rpad(‘hello’,3,’*’)from dual;//长度不足补，足则从左截取到3位。

trim/replace
select trim(’ ’ from ’ he llo ‘) from dual;//只去掉左右2边指定的，中间不去掉。
select replace(‘hello’,’l’,’L’) from dual;//全部替换为LL，没有找到相同的则原样输出.

round/trunc/mod
作用数值:
select round(3.145,2) from dual;//截取2位小数，四舍五入。
select trunc(3.145,2) from dual;//截取2位小数。
select mod (10,3) from dual;//返回取模的值

作用日期:
round//1-6上半年，7-12下半年。1-15上半月，16-月末下半月。
2016-7-14
舍年，看月。属于下半年，2017-1-01
舍月，看日。属于上半月,2016-7-01
2016-3-30
属于上半年，2016-1-01
属于下半月,2016-4-01
select round(sysdate,’year’)from dual;
select round(sysdate,’month’)from dual;
trunc//
2016-7-14
舍年，看月。更改为当前年第一天。
舍月，看日。更改为当前月第一天。
select trunc(sysdate,’year’)from dual;
select trunc(sysdate,’month’)from dual;

显示昨天今天明天
select sysdate-1 “昨天”,sysdate “今天”,sysdate+1 “明天” from dual;

日期也能进行运算
select (sysdate - sysdate+1) from dual;

months_between:精确运算相差多少天,注意months_between(a,b)表示b到a相差多少天。a>b则正数，反之负数。
select months_between(‘31-12月-16’,sysdate)from dual;//今天距离今年最后一天相差多少天。

add_months:表示几月前/后今天多少号,2月无30号。
select add_months(sysdate,-1)from dual;

next_day:从今天起，下个星期几是多少号(支持中文平台)
select next_day(next_day(sysdate,’星期三’) ,’星期日’)from dual;

last_day:本月最后一天是多少号
select last_day(sysdate)from dual;

日期-日期=天数
日期+-天数=日期

三大类型转换
字符串(char/varchar)，日期(date)，数值(number)
除了数值不能转换日期，其他都能相互转换。
oracle如何隐式转换
1：=号2边类型是否相同
2：如果=号2边类型不相同，尝试转换。
3：转换时，确保合理，否则失败。如12月不会有37天，一年不会有13月。

日期->字符串:to_char(‘日期’,’格式’)year(年的英文全称)/month(月全称,几月)/dd和day代表星期
select to_char(sysdate,’yyyy “年” mm “月” dd “日” day’)from dual;//2016 年 03 月 30 日 星期三
select to_char(sysdate,’yyyy-mm-dd “今天是”day hh24:mi:ss AM’)from dual;//2016-03-30 今天是星期三 18:32:31,hh表示24/12进制

数值->字符串:to_char(‘数值’,’格式’)
9:0-9任意值
0:0
$:美元符号 L:本地货币符号 .:小数点 ,:千位符 select to_char(100,’999’) from dual; select to_char(1.00,’L9.99’) from dual;//￥1.00 select to_char(100,’$9,99') from dual;// $1,00

字符串->日期:to_date(‘字符串’,’格式’)
select * from table_Name where column_Name=to_date(‘1996年12月2日’,’yyyy”年”mm”月”dd”日”’);
select * from table_Name where column_Name=to_date(‘1996-12-02’,’yyyy-mm-dd’);
select to_date(‘1996-12-2’,’yyyy-mm-dd’) from dual;
select to_date(‘1996年12月2日’,’yyyy”年”mm”月”dd”日”’) from dual;
select to_date(‘1996*12*2*’,’yyyy”“mm”“dd”*”’) from dual;
select to_date(‘1996*12*2*’,’yyyy*mm*dd*’) from dual;//添不添加双引号不影响

字符串->数值:to_number(‘字符串’);
select to_number(‘120’)from dual;

面试题：
select 132+’123’ from dual;//255,四则运算
select 123||’132’from dual;//123132,字符串连接

通用函数
NVL(a,b):a为null取b值，否则a值。
NVL2(a,b,c):a不为null取b，否则取c
NULLIF(a,b):a=b返回null，否则a，大小写和数据类型敏感。
coalesce(expr1,expr2)：返回该函数中第一个不为null的表达式的值

流程控制
sql99:case语句可看做一个字段
case 字段
when 条件1 then 表达式1
when 条件2 then 表达式2
else 表达式3
end (可放列名)

select
case job
when ‘BOSS’ then salary+1000
when ‘TFBOSS’ then salary+8000
else sal+400;
end “涨后工资”
from table_Name;
oracle特有条件运算:decode，判断job是否是boss，是返回salary+1000，不是，判断是否tfboos…都不相等，默认返回default
select decode(job, ‘BOSS’ ,salary+1000, ‘TFBOSS’ ,salary+8000, sal+400)”涨后工资” from table_Name;

多行函数
单行函数：输入一个参数，输出一个结果。如：upper
多行函数：输入多个参数或内部扫面多次，输出一个结果。如：count，avg，sum

多行函数不统计null值
max/min可比较日期
count/avg/sum/max/min/group by having

函数：oracle服务器事先写好的一段具有一定功能的程序片段，内置于oracle服务器，供用户调用。

统计emp员工表总人数
select count(*)from emp;
*号适合用于表字段较少的情况，如果字段较多，扫描时间长，效率低。推荐某一个非null唯一的字段，通常主键。

统计不重复的部门
select count(distinct deptno)from emp;

having和where区别
where
1:行过滤器
2：针对原始的记录
3：跟在from后面
4：where可省
5：先执行
having
1：组过滤器
2：针对分组后的记录
3：跟在group by后面
4：可省
5:后执行

oracle综合语法
1：select子句必须
2：from必须
3：where可选
4：group by可选
5：having可选
6：order by可选
group by细节：
1：在selece子句中出现的非多行函数的所有列，必须出现在group by后面。
2：在group by子句中出现的所有列，可不出现在select子句中。

多表查询
笛卡尔积：多表查询理论基础
多张表
内连接(只能查询符合条件的记录):
1:等值连接,只能用=号
2：非等值连接,非=号其他符号
外连接(能查出符合条件的记录，也能根据一方强行将另一方查询出来):左/右外连接

单张表:自连接

内连接
等值连接(=):select a.* ,b.* from a.deptno=b.deptno;
非等值连接(除=外所有符号，>=,<=,<>,!=,betwen,and等):
select a.,b. from a.salary betwen b.区间1 and b.区间2;
外连接
+出现在=号左边，右外连接，反之亦然。本质一样，只是+号位置不同，且+只能出现一次。oracle专用
+放在少的一边。
按部门号查出部门总人数。
select dept.deptNo “部门号”, deptno.dname “部门名”,count(emp.empNo) “人数”
from dept,emp
where dept.deptNo=emp.deptNo(+)
group by dept.deptno,deptno.dname;

自连接,也用到内连接+外连接
select user.name,boss.name
from emp user,emp boss
where user.mgr=boss.empno;

子查询
子查询：查询条件未知的事物
子查询别名不能添加引号

子查询细节：
1）子查询与父查询可以针对同一张表
2）子查询与父查询可以针对不同张表
3) 子查询与父查询在传统参数时，数量要相同
4) 子查询与父查询在传统参数时，类型要相同
5) 子查询与父查询在传统参数时，含义要相同

单行子查询：子查询返回一个结果，父查询用= < <= >= 来比较
多行子查询：子查询返回多个结果，父查询用any/in/all
select * from emp where emp.id in(10,20);
select * from emp where emp.salary

/

declare
re number;
begin
hello(10,20,re);
dbms_output.put_line(re||’哈哈’);
end;
/

exec适合过程无返回值
plsql适合过程有返回值，不限个数。

存储函数
必须带返回值，已经返回类型。
创建无参函数
create or replace function ger_Result return number
as
begin
return 10;
end;
/

删除函数
drop function get_Result;

调用函数
method1:
declare
re number;
begin
re:=ger_Result();–括号可省略
dbms_output.put_line(re);
end;
/
method2:其他类似java程序

创建有参函数
create or replace function ger_Result(num1 in number) return number
as
begin
return num1;
end;

/

declare
re number;
begin
re:=ger_Result(10);
dbms_output.put_line(re);
end;
/

创建有参，多返回值函数
create or replace function ger_Result(num1 in number,num2 in number,resu out number) return varchar2
as
te varchar2(9):=’结果是’;
begin
resu:=num1+num2;
return te;
end;
/

declare
re number;
tem varchar2(12);
begin
tem:=ger_Result(10,20,re);
dbms_output.put_line(tem||re);
end;
/

过程函数适合场景

声明：适合不是强行要你使用，只是优先考虑

什么情况下【适合使用】存储过程？什么情况下【适合使用】存储函数？

【适合使用】存储过程：

【适合使用】存储函数：

什么情况【适合使用】过程函数，什么情况【适合使用】SQL？

【适合使用】过程函数：
》需要长期保存在数据库中
》需要被多个用户重复调用
》业务逻辑相同，只是参数不一样
》批操作大量数据，例如：批量插入很多数据

【适合使用】SQL：
》凡是上述反面，都可使用SQL
》对表，视图，序列，索引，等这些还是要用SQL

触发器
create or replace trigger 触发器名
before/after –操作前/操作后
insert/delete –语句级，拦截那个操作
update of 列名 –行级，拦截那个操作的列
on 表名
for each row –扫描每行
plsql块

创建触发器，对表增加，显示hello
create or replace trigger insertEmp
before
insert
on emp
begin
dbms_output.put_line(‘hello’);
end;
/

删除触发器
drop trigger 触发器名

一次插入或删除多条时，也只会拦截一次

删除表后，显示hello
create or replace trigger deletetEmp
after
delete
on emp
begin
dbms_output.put_line(‘hello’);
end;
/

星期一到星期五，且7-23点能向数据库插入数据，否则异常
create or replace trigger insertEmp
before
insert
on emp
delare
pday varchar2(10);
phour number(2);
begin
–获取星期
select to_char(sysdate,’day’)into pday from dual;
–获取时间
select to_char(sysdate,’hh24’)into phour from dual;
if pday in (‘星期六’，’星期日’) or hour not between 7 and 23 then
–抛出例外，参数1编号，参数2例外原因
raise_application_error(‘-2000000’,’非工作时间，添加失败’);
end if;
end;
/

创建行级触发器，确保涨后工资>涨前工资
触发语句 ：old ：new
insert 所有字段都空null 将要插入的数据
update 更新以前该行的值 更新后该行的值
delete 删除以前该行的值 所有字段都空null

create or replace trigger checkSalary
after
update of sal
on emp
for each row
begin
if :new.sal<= :old.sal the
raise_application_error(‘-222200’,’工资不能越涨越低’);
end if;
end;
/

表丢到回收站，闪回后，触发器依旧正常工作。
彻底删除表，触发器也删。新的同样名字表，不会继承原来的触发器。

笔试题3：有一个员工表empinfo结构如下
create table empinfo(
fempno varchar2(10) primary key,
fempname varchar2(20) not null,
fage number(2) not null,
fsalary number(10,2) not null
);
insert into empinfo(fempno,fempname,fage,fsalary) values(‘1’,’AA’,30,7000);
insert into empinfo(fempno,fempname,fage,fsalary) values(‘2’,’BB’,31,8000);
insert into empinfo(fempno,fempname,fage,fsalary) values(‘3’,’CC’,32,9000);
insert into empinfo(fempno,fempname,fage,fsalary) values(‘4’,’DD’,33,10000);
insert into empinfo(fempno,fempname,fage,fsalary) values(‘5’,’EE’,34,11000);
insert into empinfo(fempno,fempname,fage,fsalary) values(‘6’,’FF’,35,12000);
insert into empinfo(fempno,fempname,fage,fsalary) values(‘7’,’GG’,36,13000);
insert into empinfo(fempno,fempname,fage,fsalary) values(‘8’,’FF’,37,14000);

  假如该表有大约1000万条记录，写一句最高效的SQL语句，计算以下4种人中每种员工的数量 
  第1种人：fsalary>9999 and fage>35
  第2种人：fsalary>9999 and fage<35
  第3种人：fsalary<9999 and fage>35
  第4种人：fsalary<9999 and fage<35

  提示：只用一条SQL搞定

*/

select 
   sum(case when e.fsalary>9999 and e.fage>35 then 1 else 0 end) "第1种人",
   sum(case when e.fsalary>9999 and e.fage<35 then 1 else 0 end) "第2种人",
   sum(case when e.fsalary<9999 and e.fage>35 then 1 else 0 end) "第3种人",
   sum(case when e.fsalary<9999 and e.fage<35 then 1 else 0 end) "第4种人"
from empinfo e;