oracle-操作语言

Dear 丶 2022-07-16 15:58 200阅读 0赞

超管登录：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:本地货币符号.:小数点,:千位符selecttochar(100,′999′)fromdual;selecttochar(1.00,′L9.99′)fromdual;//￥1.00selecttochar(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;