我学oracle时的随手笔记数据库面试必备知识.docx
《我学oracle时的随手笔记数据库面试必备知识.docx》由会员分享,可在线阅读,更多相关《我学oracle时的随手笔记数据库面试必备知识.docx(15页珍藏版)》请在冰豆网上搜索。
![我学oracle时的随手笔记数据库面试必备知识.docx](https://file1.bdocx.com/fileroot1/2023-1/31/fb25807c-30f9-4510-8260-32f47b9ba03a/fb25807c-30f9-4510-8260-32f47b9ba03a1.gif)
我学oracle时的随手笔记数据库面试必备知识
我学oracle时的随手笔记(数据库面试必备知识)
都是些基础的,还有提高的。
拿出来给大家分享吧!
!
!
**关于oracle自带的表***********************************8
emp:
empno:
员工编号;ename:
员工名字;job:
员工工种;mgr:
上司;hiredate:
入职时间;sal:
基本工资;comm:
补贴;deptno:
所属部门编号;
dept:
deptno:
部门编号;dname:
部门名称;loc:
地理位置;
salgrade:
grade:
工资等级;losal:
最低限额;hisal:
最高限额;
dual:
系统自带的一张空表;可用于计算数据:
select2*3fromdual;
**sql_function1**********************************************************
selectlower(ename)fromemp;取出的名字全部变成小写。
selectenamefromempwherelower(ename)like'_a%';取出的名字变成小写后不含字母a
selectsubstr(ename,2,3)fromemp;从第二个字符截,截取三个字符。
selectcha(65)fromdual;将数字转化为字符(显示为a)。
selectascii('A')fromdual;将字符转化为数字。
selectround(23.652)fromdual;(显示24)
selectround(23.652,2)fromdual;(显示23.65)
selectround(23.652,-1)fromdual;(显示20)
selectto_char(sal,'$99,999.9999')fromemp;强制转化为指定的格式。
selectto_char(sal,'L0000.0000')fromemp;同上。
selectto_char(hiredate,YYYY-MM-DDHH:
MI:
SS)fromemp;对时间格式显示处理。
selectto_char(sysdate,YYYY-MM-DDHH:
MI:
SS)fromemp;12进制。
selectto_char(sysdate,YYYY-MM-DDHH24:
MI:
SS)fromemp;24进制。
***********************************************************************
**sql_function2*******************************************************
selectename,hiredatefromempwherehiredate>to_date('1981-2-20'12:
34:
52,'YYYY-MM-DDHH24:
MI:
SS');函数to_date将字符转化为时间格式。
selectsalfromempwheresal>to_number('$1,250.00','$9,999.99');函数to_number将字符转化为数字格式,以作比较。
selectenamesal*12+nvl(comm0)fromemp;函数nvl作用为当comm为null的时候当作处理,避免了comm为null给结果带来的不便。
*************************************************************************
**group_function*******************************************************
selectmax(sal)fromemp;输出薪水值最高的。
selectmin(sal)fromemp;输出薪水值最低的。
selectavg(sal)fromemp;输出平均薪水值。
selectto_char(avg(sal),'99999999.99')fromemp;按照指定格式输出平均薪水值。
selectround(avg(sal),2)fromemp;精确到小数点后面2位。
selectsum(sal)fromemp;输出薪水值的总和。
selectcount(*)fromemp;求出一共有多少条记录。
selectcount(*)fromempwheredeptno=10;求部门为10号的记录条数。
selectcount(ename)fromemp;求一共有几个名字。
selectcount(comm)fromemp;求非空comm的记录条数。
selectcount(deptno)fromemp;
selectcount(distinctdeptno)fromemp;
*************************************************************************
**group_by*************************************************************
selectdeptno,avg(sal)fromempgroupbydeptno;将部门薪水平均分组。
selectdeptno,job,max(sal)fromempgroupbydeptno;按组合分组。
selectename,max(sal)fromempwheresal=(selectmax(sal)fromemp);
selectenamemax(sal)fromempgroupbydeptno;这样是错误的。
selectdeptnomax(sal)fromempgroupbydeptno;这样可行。
************************************************************************
**having****************************************************************
selectavg(sal),deptnofromempgroupbydeptno;
selectavg(sal),deptnofromempgroupbydeptnohavingavg(sal)>2000;having是对分组进行限制。
1selectavg(sal)选择
2fromemp表原
3wheresal>1200条件过滤
4groupbydeptno分组
5havingavg(sal)>1500对结果进行限制
6orderbyavg(sal)desc对产生的结果进行排序
**********************************************************************
**子查询**************************************************************
select语句里面套另外一个select语句。
selectename,salfromempwheresal>(selectavg(sal)fromemp);
selectename,salfromempjoin(selectmax(sal)max_sal,deptnofromempgroupbydeptno)ton(emp.sal=t.max_salandemp.deptno=t.deptno);
**self_table**********************************************************
自连接:
selecte1.ename,e2.enamefromempe1,empe2wheree1.mgr=e2.empno;把一个表当成两个来使用。
***********************************************************************
**sql1999_table_connections********************************************
1999年标准:
selectename,dnamefromempcrossjoindept;
旧:
新:
selectename,dnamefromempjoindepton(emp.deptno=deptno);
selectename,dnamefromempjoindeptusing(deptno);(了解即可,不推荐使用)
selectename,gradefromempejoinsalgradeson(e.salbetweens.losalands.hisal);
左外连接:
selecte1.ename,e2.enamefromempe1leftjoinempe2on(e1.mgr=e2.empno);
右外连接:
selectename,dnamefromemperightouterjoindeptd(e.deptno=d.deptno);
全外连接:
selectename,dnamefromempefulljoindeptd(e.deptno=d.deptno);
************************************************************************
**求部门平均薪水等级****************************************************
selectdeptno,avg(grade)from(selectdeptno,ename,gradefromempjoinsalgradeson(t.avg_salbetweens.losalands.hisal))tgroupbydeptno;
*************************************************************************
**部门中那些人是经理人**************************************************
selectenamefromempwhereempnoin(selectdistinctmgrfromemp);
************************************************************************
**不用组函数求薪水的最高值(面试题)************************************
selectdistinctsalfromempwheresalnotin(selectdistincte1.salfromempe1joinempe2on(e1.sal************************************************************************
**平均薪水最高的部门的编号****************************************
selectdeptno,avg_salfrom
(selectavg(sal)avg_sal,deptnofromempgroupbydeptno)whereavg_sal=
(selectmax(avg_sal)from
(selectavg(sal)avg_sal,deptnofromempgroupbydeptno)
)
*********************************************************************
**求平均薪水最高的部门的部门名称
selectdnamefromdeptwheredeptno=
(
selectdeptno,avg_salfrom
(selectavg(sal)avg_sal,deptnofromempgroupbydeptno)whereavg_sal=
(selectmax(avg_sal)from
(selectavg(sal)avg_sal,deptnofromempgroupbydeptno)
)
)
方法二:
selectdnamefromdeptwheredeptno=
(
selectdeptno,avg_salfrom
(selectavg(sal)avg_sal,deptnofromempgroupbydeptno)whereavg_sal=
(selectmax(avg_sal)from
(selectavg(sal)avg_sal,deptnofromempgroupbydeptno)
)
)
**********************************************************************
**求平均薪水的等级最低的部门的部门名称
**************************************
**creatnewuserandinsert****************************************
1--backupscott
2--createuser
createuserwpidentifiedbywpdefaulttablespaceusersquota10Monusers;(创建新用户)
grantcreatesession,createtable,createviewtowangpeng(赋予新用户权限)
3--importthedata
insertintodeptvalues(50,'game''bj');插入数据。
insertintodept(deptno,dname)values(60,'game');同上。
insertintodept2select*fromdept;数据又挨着插了一遍。
rollback;回退命令。
createtabledept2asselect*fromdept;备份数据。
*************************************************************************
**rownum***************************************************************
selectemp,enamefromempwhererownum<=5;取前四行。
rownum只能和<和<=使用,大于号和等于号不支持。
selectename,salfrom
(selectename,salfromemporderbysaldesc)whererownum<=5;
selectename,salfrom
(
selectename,sal,rownumrfrom
(selectename,salfromemporderbysaldesc)
)wherer>=6andr<=10;求薪水最高的第6到第10名雇员。
************************************************************************
**update*******************************************************
updateemp2setsal=sal*2,ename=ename||'-'wheredeptno=10;更改。
deletefromemp2;删除。
****************************************************************
**创建新表与约束****************************************************
createtablestu
(
idnumber(6)primarykey,//主键约束,主键非空且唯一,也可以写在后面constraintstu_id_pkprimarykey(id),
namevarchar2(20)constraintstu_name_nnnotnull,//notnull指定必须为非空
sexnumber
(1),
agenumber(3),
sdatedate,
gradenumber
(2)default1,
classnumber(4),
emailvarchar2(50),
constraintstu_name_email_uniunique(email,name)//email与name组合唯一
)
外键参考的值段必须是主键,加上constraintstu_class_fkforeign(class)referenceclass(id),
*************************************************************************
**alter修改表结构***************************************************
altertablestuadd(addrvarchar2(100));//添加字段
altertablestudrop(asddr);
altertablestumodify(asddrvarchar2(50));
altertablestudropconstraintstu_class_fk;//删除约束条件
altertablestuaddconstraintstu_class_fkforeignkey(class)referenceclass(id);//重新添加约束条件
****************************************************************
**查询系统的表**********************************************
selecttable_namefromuser_tables;//当前用户下所有的表
selectview_namefromuser_views;//当前用户下的视图
selectconstraint_name,table_namefromuser_constraints;
descdictionary//数据字典表
******************************************************************
**索引********************************************************
createindexidx_stu_emailonstu(email);//创建索引
dropindexidx_stu_email;//删除索引
selectindexidx_namefromuser_indexes;
selectview_namefromuser_views;
desc(视图的名称)
createviewv$_stuasselectid,name,agefromstu;只给予查看id,name,age的权力,保护私有数据。
*********************************************************************
**sequence序列*****************************************************
createsequenceseq;//创建序列
selectseq.nextvalfromdual;
insertintoarticlevalues(seq.nextval,'a','b');//添加数据*********************************************************************
**三范式**********************************************************
第一范式:
要有主键且列不可分。
第二范式:
不能存在部分依赖:
非主键的字段不能依赖于组合主键的一部分。
第三范式:
不能存在传递依赖。
*******************************************************************
**PL_SQL语句*************************************************************
简单小程序:
SQL>setserveroutputon;
SQL>begin
dbms_output.put_line('HelloWorld!
');
end;
/
HelloWorld!
(显示的结果)
运行中dcomcnfg命令是查看系统组件服务
变量申明的规则:
变量名不能使用保留字,如from,select等
第一个字符必须是字母。
变量名最多包含30个字符
不要与数据库的表或者列同名
每一行只能申明一个变量
常用变量的类型:
binary_integer:
整数,主要用来计数而不是用来表示字段类型
number:
数字类型
char:
定长字符串
varchar2:
变长字符