超管登录: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;