oracle笔记.docx
- 文档编号:30720147
- 上传时间:2023-08-19
- 格式:DOCX
- 页数:44
- 大小:30KB
oracle笔记.docx
《oracle笔记.docx》由会员分享,可在线阅读,更多相关《oracle笔记.docx(44页珍藏版)》请在冰豆网上搜索。
oracle笔记
1、Oracle默认账户、密码
syschange_on_install
systemmanger
scotttiger
2、Oracle服务说明
OracleServiceORCL:
数据库的服务,如果创建两个数据库就会有两个服务,ORCL是数据库名。
OracleOraDb10g_home1TNSListener:
监听服务,jdbc和plsql远程连接服务,端口号为:
1521
OracleOraDb10g_home1iSQL*Plus:
Web管理服务,例如:
http:
//127.0.0.1:
5560/isqlplus,端口号为:
5560
注意事项:
如果OracleOraDb10g_home1TNSListener启动出错,找到NetConfigurationAssistant重新配置监听即可。
默认启动:
OracleServiceORCL和OracleOraDb10g_home1TNSListener就足够了。
3、加锁、解锁用户(管理员"sys""system"命令)
加锁:
alteruser用户accountlock;
解锁:
alteruser用户accountunlock;
4、登录命令、切换用户命令
1.connect用户/密码
2.conn用户/密码
========不想被别人看到明文密码情况下========
3.SQL>conn
请输入用户名:
scott
输入口令:
*****
4.SQL>conn
请输入用户名:
scott
输入口令:
*****
5、查询当前登录用户
showuser;
6、查询、设置“行宽”和“显示条数”
查询行宽:
showlinesize;//默认80
设置行宽:
setlinesize120;
查询显示条数:
showpagesize;//默认14
设置显示条数:
setpagesize100;
1.字符串指定列宽:
column列名formata号;
例如:
columnENAMEformata10;
2.数字指定列宽:
column列名format9999;
说明:
一个9代表一个数字。
12的缩写格式:
col列名for指令;
=====================================================================
例如我添加的内容如下:
--指定行宽
setlinesize150
--指定每页显示条数
setpagesize100
--指定oracle默认显示时间
altersessionsetnls_date_format='yyyy-MM-dd';
=====================================================================
永久保存设置配置:
找到oracle的安装目录,例如:
D:
\Software\oracle\product\10.2.0\db_1\sqlplus\admin\glogin.sql设置写到最下面即可。
//测试是否是jdbc语句
1、jdbc语句必须需要分号,否则会换行让你继续输入。
2、使用/来测试,如果是jdbc输入完毕,再输入/如果成功执行上句的话,证明是jdbc语句。
//查询当前用户所有表
select*fromtab;
//查看表结构
desc表名;
//打开查询耗时
settimingon;
//关闭查询耗时
settimingoff;
//sqlplus(黑窗口)清屏,sqlplusw中无效
Windows:
hostcls
Linux中:
hostclear
plsql中:
clear
//多行输错,修改命令
edit或者ed,修改完输入/,文件中不要有问号
//处理null值函数(vnl函数)
nvl(字段,值)
例如:
selectnvl(comm,0)fromemp;
nvl2(字段,不为空返回值,为空返回值)
例如:
selectnvl2(NULL,2,3)//返回3
//别名大小写
使用别名,如果不加""默认大写。
加了""就会按""里显示。
asxx;--转为:
XX
as"xx";--等于:
xx
注意:
当别名含有"空格"、"字符串"时一定要加""号;
//取消重复行关键字:
(distinct)
selectdistinct重复字段from表名;
注意:
如果查询多列,distinct作用于多列,多列有重复才算重复。
//拼接字符串--查询格式:
ENAME的工资是:
SAL
selectename||'的工资是:
'||salfromemp;
//拼接字符串((oncat)函数
selectconcat('我叫','小童鞋_成er')fromdual;
拼接多个:
selectconcat(concat('我叫','小童鞋_成er'),'啊!
')fromdual;
//oracle的虚表,oracle查询必须指定form关键字
select'Hello'||'World'fromdual;
dual是一个虚拟表:
比如得到当前时间:
selectsysdatefromdual;
计算数值:
select3+2fromdual;
//关于(like)查询特殊字符用法:
IDNAME
------------
1dd%
2%xx
1、我们进行查询,select*from表名where字段like'%%';那么就是查询所有了。
//定义转移字符:
select*from表名where字段like'\%%'escape'\';--把\%转义,escpae指定哪个是转义符。
比如:
select*from表名where字段like'x%%'escape'x';//把x转义
2、查询第三个字母为I的:
select*from表名where字段like'__I';
//查询~到~范围(between)
比如查询工资1500~6000之间的。
select*from表名where(工资字段between1500and6000);
//查询id为1234的用户(in查询)
第一种写法:
select*from表名whereID字段=1orID字段=2orID字段=3orID字段=4;
批处理写法:
select*from表名whereID字段in(1,2,3,4);
注意:
select*from表名where字段in(xx,xx,null);//没有影响
select*from表名where字段notin(xx,xx,null);//如果notin含有null,则不返回任何结果。
//字符串转为日期(to_date)
to_date('2014-10-02','yyyy-MM-dd');//年-月-日
to_date('2014-10-0212:
00:
02','yyyy-MM-ddHH24:
mi:
ss');//年-月-日-时-分-秒
//日期转为指定格式(to_char)
to_char(日期字段,'yyyy-MM-dd');//年-月-日
to_char(日期字段,'yyyy-MM-ddHH24:
mi:
ss');//年-月-日-时-分-秒
//查询Oracle指定的格式(v$nls_parameters)
select*fromv$nls_parameters;//只能查看,不能修改
//修改当前会话的格式默认时间格式:
DD-MON-YY
altersessionsetnls_date_format='yyyy-MM-dd';//只对当前会话有效
//排序(orderby)
升序:
select*from表名orderby要排序字段asc;//asc默认也可不写,从高到底
降序:
select*from表名orderby要排序字段desc;//降序,从低到高
NULL值排序始终在下面:
select*from表名orderby含有NULL字段descnullslast;
NULL值排序始终在上面:
select*from表名orderby含有NULL字段descnullsfirst;
select*from表名orderbynvl(含有NULL字段,-1)asc;
根据字段位置排序:
selectid,namefromxxorderby2desc;//2是name的位置
//分组
分组函数:
max(字段);//最大值
min(字段);//最小值
avg(字段);//平均值
count(字段);//总条数
sum(字段);//总和
分组方法(groupby):
selectmax(sal),deptnofromempgroupbydeptno;//查询每个部门工资最高的人,如果groupby中没有这个字段,就不能显示这个字段
对两个字段进行排序:
selectmax(sal),deptno,jobfromempgroupbydeptno,job;//根据“部门(deptno)”和“工作(job)”进行排序。
对分组条件进行筛选(groupby字段having条件),同时进行排序(orderby):
selectdeptno,count(*)fromempgroupbydeptnohavingcount(*)>4orderbydeptnodesc;
注意:
groupby和having都不可以使用“别名”;
orderby可以使用“别名”;
某些情况下,优先使用where,而不使用having。
例子,查询“部门”,“部门人数”,取消10号部门信息:
效率高:
selectdeptno,count(*)fromempwheredeptno<>10groupbydeptno;
效率低:
selectdeptno,count(*)fromempgroupbydeptnohavingdeptno<>10;
//字符函数
LOWER(String)//将字段转换为“小写”;
selectLOWER('ABC')fromdual;--结果:
abc
UPPER(String)//将字段转换为“大写”;
selectLOWER('abc')fromdual;--结果:
ABC
INITCAP(String)//每个单词首字母转为“大写”;\
selectINITCAP('helloword.day')fromdual;--结果:
HelloWord.Day
--首写字母为大写
selectinitcap(ename)fromemp;
//拼接字符串
CONCAT('a','b');//跟||一样
//截取函数:
SUBSTR(String,index,index)//要截取的字符串,从第几个开始,此位置往后截取几个(从1下标开始)
selectSUBSTR('abc',1,2)fromdual;--结果:
ab
USBSTR(String,index)//如果不指定,就从1位置,截取到字符串结束
selectSUBSTR('abc',2)fromdual;--结果:
bc
//字段长度
LENGTH(String)//返回字段长度
selectLENGTH('abc')fromdual;--结果:
3
//查找字符串位置
INSTR(String,String)//返回字符串位置下标
selectINSTR('abcdefg','c')fromdual;--结果:
3
//补齐函数LPAD(String,indexOf,char)和RPAD(String,indexOf,char)
LPAD:
selectlpad('abc',5,'*')fromdual;--结果:
**abc
RPAD:
selectrpad('abc',5,'*')fromdual;--结果:
abc**
//可以匿名,比如:
selectenameas姓名,substr(ename,1,1)||replace(rpad('',length(ename)-1,'*'),'','')||substr(ename,length(ename))as匿名fromempwhereename='SCOTT';
姓名匿名
---------------
SCOTTS***T
//去掉前后空格TRIM(''fromString)
TRIM(String)//去掉前后空格
TRIM('a'from'abc')//去掉a,注意:
只能为前、后替换
//替换函数replace(String,String,String)
select*fromreplace('abc','a','1')fromdual;--结果:
1bc
//数学函数:
MOD(number,nunber)//求余数
ROUND(number)//四舍五入
ROUND(number,number);//截取小数点第几位,比如:
12.91结果:
12没有小数;如果是-1,比如:
12.22,-1结果:
10
TRUNC(number)
跟ROUND()函数一样,只是不四舍五入
TRUNC(number,number)
//操作时间
sysdate-1:
减一天
sysdate-1/24:
减一天1小时
sysdate-1/24/24:
减一天1小时1分钟
last_day(date):
获取当月最后一天
比如:
selectto_char(last_day(to_date('11','mm')),'yyyy-mm-dd')fromdual;//返回:
2014-11-30
add_months(date,number):
当前日期减一天或加一天
比如:
selectto_char(add_months(last_day(sysdate),-1)+1,'yyyy-mm-dd')fromdual;//返回当月的1号
months_between:
计算两个时间相差“月份”
比如;selectmonths_between(sysdate,to_date('2014-12-19','yyyy-mm-dd'))fromdual;//返回-1
round(date,'xx'):
///对日期四舍五入,比如:
round(to_date('2014-10-1022:
00:
00','yyyy-mm-ddhh24:
mi:
ss'),'dd')返回:
2014-10-1100:
00:
00
trunct(date,'xx'):
///对日期进行截断,比如:
trunc(to_date('2014-10-1022:
00:
00','yyyy-mm-ddhh24:
mi:
ss'),'month'))返回:
2014-10-0100:
00:
00
上面两个函数指定xx:
yyyymonthddhh24miss
//根据“星期几”获取下个“星期几”的日期
next_day(sysdate,'星期三');
//to_char(number,'xx')转换数字
to_char(sal,'L9999'):
转换为本地数字格式,9999表示显示位数
$999,9999.00:
0000:
0补齐
//字符串转数字
to_number('3');
//判断,类似switchcasecase
CASE字段
WHENxx--如果是xx值
THENxx--替换成xx值
ELSE--否则
xx--返回xx值
END
------------------------------------------------
CASE字段
WHENxx--如果是xx值
THENxx--替换成xx值
WHENxx--如果是xx值
THENxx--替换成xx值
ELSE--否则
xx--返回xx值
END
//判读,类似ifelse
case
whenxx=xx
thenYes--是
elseNo--不是
end
------------------------------------------------
//判断(oracle独有)decode函数
select
decode(ename,'是SCOTT','Yes','不是SCOTT','No','都不是')
fromemp
whereename=upper('scott');
//集合操作
union:
取并集,比如:
A集合有13,B集合有14;并集结果:
134
select*fromempwhereenamein('SCOTT','CLARK')unionselect*fromempwhereenamein('SMITH','ALLEN','SCOTT');
unionall:
取合集,比如:
A集合有13,B集合有14;合集结果:
1134
select*fromempwhereenamein('SCOTT','CLARK')unionallselect*fromempwhereenamein('SMITH','ALLEN','SCOTT');
intersect:
取交集,比如:
A集合有13,B集合有14;交集结果:
1
select*fromempwhereenamein('SCOTT','CLARK')intersectselect*fromempwhereenamein('SMITH','ALLEN','SCOTT');
minus:
取差集,比如:
A集合有13,B集合有14;差集结果:
3
例如:
selectenamefromempwhereenamein('SCOTT','ALLEN')minusselectenamefromempwhereenamein('SCOTT','ALLEN','SMITH');
结果:
“无结果”
例如:
selectenamefromempwhereenamein('SCOTT','ALLEN','SMITH')minusselectenamefromempwhereenamein('SCOTT','ALLEN');
结果:
SMITH
总结:
以“第一个”集合为中心,取两结果的相差
/*
注意事项:
1、如果是两个查询设计分组;
2、orderby必须在最后一个集合,groupby无限制
3、集合数量必须一样,类型必须一样,其它字段名字不同可以。
*/
//左连接、右连接
--左连接:
/*Oracle专用:
*/selectd.deptnoas"部门编号",d.dnameas"部门名称",count(e.deptno)as"总人数"fromempe,deptdwhered.deptno=e.deptno(+)groupbyd.deptno,d.dnameorderbycount(e.deptno);
/*通用:
*/SELECTd.deptnoAS"部门编号",d.dnameAS"部门名称",COUNT(e.deptno)AS"部门总人数"FROMempeLEFTJOINdeptdONe.deptno=d.deptnoGROUPBYd.deptno,d.dnameORDERBY部门总人数
--右连接:
/*Oracle专用:
*/selectd.deptnoas"部门编号",d.dnameas"部门名称",count(e.deptno)as"总人数"fromempe,deptdwhered.deptno=e.deptno(+)groupbyd.deptno,d.dnameorderbycount(e.deptno);
/*通用:
*/SELECTd.deptnoAS"部门编号",d.dnameAS"部门名称",COUNT(e.deptno)AS"部门总人数"FROMempeRIGHTJOINdeptdONe.deptno=d.deptnoGROUPBYd.deptno,d.dnameORDERBY部门总人数
//单表“自连接”
/*方法1:
*/selecta.ename,b.enamefromempa,empbwherea.mgr=b.empno;
/*内链接通用:
*/SELECTa.ename,b.enameFROMempaINNERJOINempbONa.mgr=b.empno;//inner可以省略不写
//满外连接
/*mysql不能用:
*/SELECTe.ename,e.deptno,d.dname,d.deptnoFROMempeFULLJOINdeptdONe.deptno=d.deptno;
//返回笛卡尔集
SELECTe.ename,e.deptno,d.dname,d.deptnoFROMempeCROSSJOINdeptd;
==============================================Oracle分页==============================================================
//第一页
select*from(selectrownumrn,a.*from(select*fromemp)awhererownum<=10)wherern>=1;
//第二页
select*from(selectrownumrn,a.*from(select*fromemp)awhererownum<=20)wherern>=11;
==============================================DML操作==============================================================
//临时表创建
全
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 笔记