oracle基础知识2.docx
《oracle基础知识2.docx》由会员分享，可在线阅读，更多相关《oracle基础知识2.docx（20页珍藏版）》请在冰点文库上搜索。
oracle基础知识2
Oracle基础
一,oracle的安装与卸载
二,一些基本操作:
1,登录命令:
sqlplus,sqlplusw.
以sqlplusw登录后,首先进行环境设置.
清理显示页面:
clearscr;
设置每行显示的长度,
setlinesize200;（每行显示200个字符）
设置每页显示数据行数;
setpagesize40;（每页显示40行数据）
2,Sql语句编辑:
1）命令:
ed+文件名（如:
aa）;
可以在文件中编写要执行的sql语句;
2）命令:
@aa执行文件aa中编写的sql语句;
命令@d:
/bb.txt或者@d:
bb.txt可以执行d盘bb.txt中编写的sql语句;
命令@d:
/bb或者@d:
bb可以执行d盘bb.sql中编写的sql语句;
3,变换用户连接数据库:
一般用户（scott）和普通管理员（system）:
conn用户名/密码;
超级管理员（sys）:
conn用户名/密码assysdba;
在sys用户下查询scott用户下的emp表中的信息:
select*fromscott.emp;
显示当前连接数据库的用户:
showuser;
查询当前用户的所有表:
select*fromtab;
查看表结构:
desc表名;
再次执行前一次运行的SQL语句:
/;
三,scott用户下的表
1,员工表emp
2,部门表dept:
3,工资等级表salgrade:
4,奖金表bonus:
四,简单的sql语句
1,简单查询语句
SELECT列名别名FROMemp;
问题1:
查询所有的员工的,姓名,工作
问题2:
查询所有工作?
2,去掉重复内容DISTINCT关键字
SELECTDISTINCT*FROMemp;
问题3:
查询所有员工的编号和工作,是否还能去掉重复?
问题4:
查询所有员工的编号和工作,查询结果显示为:
谁的工作是什么.
3,字符串连接操作符"||".
SELECTename||'的工作是'||jobFROMemp;
问题5:
查询所有员工的姓名,和工资,并算出年薪
4,四则运算
SELECTename,sal,sal*12FROMemp;
问题6:
查询工资大于2000的员工的所有信息.
5,限定查询WHERE语句
SELECT*FROMempWHEREsal>2000;
问题7:
查询每个月有奖金的员工的所有信息.
6,ISNOTNULL修饰
SELECT*FROMempWHEREcommISNOTNULL;
问题8:
查询每个月没有奖金的员工的所有信息.
7,ISNULL修饰
SELECT*FROMempWHEREcommISNULL;
问题9:
查询工资大于1000的,并且每月有奖金的员工的所有信息.
8,多条件查询AND操作符
SELECT*FROMempWHEREsal>1000ANDcommISNOTNULL;
问题10:
查询工资大于1000的,或者每月有奖金的员工的所有信息.
9,多条件查询OR操作符
SELECT*FROMempWHEREsal>1000ORcommISNOTNULL;
问题10:
查询工资不大于1000的,并且每月没有奖金的员工的所有信息.
10,NOT修饰
SELECT*FROMempWHERENOT（sal>1000ANDcommISNOTNULL）;
问题11:
查询工资大于等于1500的,并且小于等于3000的员工的所有信息.
问题12:
查询81年入职的员工的所有信息.
11,BETWEEN...AND...语句
SELECT*FROMempWHEREsalBETWEEN1500AND3000;
SELECT*FROMempWHEREhiredateBETWEEN'1-1月-81'AND'31-12月-81';
问题13:
查询编号是7844,7698,7900的员工的所有信息.
12,IN修饰
SELECT*FROMempWHEREempnoIN（7844,7698,7900）;
问题14:
查询姓名不是SMITH,KING的员工的所有信息.
13,NOTIN修饰
SELECT*FROMempWHEREenameNOTIN（'SMITH','KING'）;
问题15:
查询姓名中带I字母的员工的所有信息.
问题16:
查询姓名中第二个字母是I的员工的所有信息.
14,LIKE修饰
SELECT*FROMempWHEREenameLIKE'%I%'
SELECT*FROMempWHEREenameLIKE'_I%'
问题17:
查询编号不是7844的员工的所有信息.
15,不等于符号!
=,<>
SELECT*FROMempWHEREempno!
=7844;
问题18:
查询所有员工信息,按照工资由低到高排序.
问题19:
查询所有员工信息,按照工资由高到低排序.如果工资相等,按照入职时间由早到晚排序.
16,ORDERBY语句
SELECT*FROMempORDERBYsalASC;
SELECT*FROMempORDERBYsalDESC,hiredateASC;
四,单行函数
1,字符函数
UPPER（字符串）把字符串表示为大写.
问题1:
查询员工表中姓名是SMITH的员工信息.要求不考虑参数的大小写.
SELECT*FROMempWHEREename=UPPER（'smith'）;
LOWER（字符串）把字符串表示为小写.
INITCAP（参数）把参数中的单词的首字母表示为大写,其他字母小写.
SELECTINITCAP（ename）FROMemp;
CONCAT（字符串1,字符串2,...）把字符串连接成一个字符串
SELECT*FROMempWHEREename=CONCAT（'SMI','TH'）;
SUBSTR（字符串,下标）把字符串从下标开始截取
SUBSTR（字符串,下标,长度）把字符串从下标开始截取成一定长度的字符串
注意:
这个函数的下标是从0开始呢,还是从1开始?
问题2:
查询员工的姓名,不显示姓名的前2个字母.
SELECTename,SUBSTR（ename,3）FROMemp;
问题3:
查询员工的姓名,和姓名的前3个字母.
SELECTename,SUBSTR（ename,1,3）FROMemp;
LENGTH（字符串）求字符串的长度.
问题4:
查询员工的姓名,和姓名的长度.
SELECTename,LENGTH（ename）FROMemp;
问题5:
查询员工的姓名,和姓名的后3个字母.
SELECTename,SUBSTR（ename,LENGTH（ename）-2）FROMemp;
SELECTename,SUBSTR（ename,-3,3）FROMemp;
REPLACE（字符串,原字符,新字符）
问题6:
查询员工的姓名,把姓名中的M字母换成X字母.
SELECTename,REPLACE（ename,'M','X'）FROMempWHEREename='SMITH';
2,数值函数
假设数据库里面有一条数据的值是8765.5678,这里可用dual表模拟.
ROUND（数据）默认个位数四舍五入.
SELECTROUND（8765.5678）FROMdual;
ROUND（数据,保留小数位数）保留小数位数四舍五入.
SELECTROUND（8765.5678,2）FROMdual;
问题1:
把8765.5678这个数按照十位数四舍五入.
SELECTROUND（8765.5678,-2）FROMdual;
TRUNC（数据）去掉小数位,不进行四舍五入
SELECTTRUNC（8765.5678）FROMdual;
SELECTTRUNC（8765.5678,2）FROMdual;
SELECTTRUNC（8765.5678,-2）FROMdual;
MOD（被除数,除数）取余（取模）
SELECTMOD（5,2）FROMdual;
3,日期函数
Oracle中的日期是可以进行加减等一些运算的.
计算规律:
日期-日期=天数（数字）
日期-数字=日期
日期+数字=日期
SYSDATE表示当前日期
SELECTSYSDATEFROMdual;
问题1:
求出SMITH入职时到现在的星期数.
SELECT（SYSDATE-hiredate）/7FROMempWHEREename='SMITH';
问题2:
求出SMITH入职时到现在的月数.
SELECT（SYSDATE-hiredate）/?
FROMempWHEREename='SMITH';
MONTHS_BETWEEN（日期,日期）求出两个日期范围的月数.
SELECTMONTHS_BETWEEN（SYSDATE,hiredate）
FROMempWHEREename='SMITH';
问题3:
求两个月后的日期是多少.
ADD_MONTHS（日期,月数）求出日期+月数后的日期.
SELECTADD_MONTHS（SYSDATE,2）FROMdual;
问题4:
下一个星期二的日期是多少.
NEXT_DAY（日期,'星期几'）求日期后的星期几的日期
SELECTNEXT_DAY（SYSDATE,'星期二'）FROMdual;
问题5,求出当前月的最后一天的日期.
LAST_DAY（日期）求出日期所在月份的最后一天的日期
SELECTLAST_DAY（SYSDATE）FROMdual;
4,转换函数
日期转换时的一些格式:
年:
y
月:
m
日:
d
数字转换时的格式:
'9':
表示一位数字（可以是任意数）
数字金额区域格式:
$:
美元
L:
local的缩写,以本地语言显示金额.
TO_CHAR（参数,格式）把参数转换为指定格式的字符串
问题1:
查询员工的入职日期,把年月日分开显示.
SELECTTO_CHAR（hiredate,'yyyy'）年,
TO_CHAR（hiredate,'mm'）月,
TO_CHAR（hiredate,'dd'）日
FROMemp;
问题2:
查询员工的入职日期,以如2010-10-01格式显示.
SELECTTO_CHAR（hiredate,'yyyy-mm-dd'）FROMemp;
问题3:
查询员工的入职日期,以如2010-10-1格式显示.
SELECTTO_CHAR（hiredate,'fmyyyy-mm-dd'）FROMemp;
问题4:
查询员工的工资,以如1,500格式显示;
SELECTTO_CHAR（sal,'9,999'）FROMemp;
问题5:
查询员工的工资,以如$1,500格式显示;
SELECTTO_CHAR（sal,'$9,999'）FROMemp;
问题6:
查询员工的工资,以如￥1,500格式显示;
SELECTTO_CHAR（sal,'L9,999'）FROMemp;
TO_NUMBER（数字字符串）将数字字符串转换为数字类型
SELECTTO_NUMBER（'111'）+TO_NUMBER（'222'）FROMdual;
TO_DATE（字符串日期,格式）将字符串日期按照格式转换成日期类型
SELECTTO_DATE（'2010-10-10','yyyy-mm-dd'）FROMdual;
5,通用函数
问题1:
查询员工的姓名,年薪（sal+comm）*12
SELECTename,（sal+comm）*12年薪FROMemp;
NVL（参数1,参数2）如果参数1的值为null,则把参数2的值赋给参数1.
SELECTename,（sal+NVL（comm,0））*12年薪FROMemp;
DECODE（参数0,参数a1,参数a2,参数b1,参数b2,参数c1参数,c2...）
如果参数0的值=参数a1的值那么返回参数a2的值
如果参数0的值=参数b1的值那么返回参数b2的值
如果参数0的值=参数c1的值那么返回参数c2的值
以此类推.
例:
SELECTDECODE（1,1,1,2,2,3,3）FROMdual;
问题2:
查询员工信息,要求显示结果如下:
SELECTempno编号,
ename姓名,
hiredate入职日期,
DECODE（job,
'CLERK','接待员',
'ANALYST','分析员',
'SALESMAN','销售员',
'PRESIDENT','总经理',
'MANAGER','经理'
）职位
FROMemp;
五,多表查询
例1:
同时查询emp表和dept表中的所有数据.
SELECT*FROMemp,dept;
SELECTCOUNT（*）FROMemp;
SELECTCOUNT（*）FROMdept;
会发现查询结果出现了笛卡尔积.怎么消除呢?
SELECT*FROMempe,deptdWHEREe.deptno=d.deptno;
例2:
查询所有员工编号,姓名,所有部门编号,部门名称,部门所在位置.
SELECTe.empno,e.ename,d.deptno,d.dname,d.loc
FROMempe,deptd
WHEREe.deptno=d.deptno;
例3:
查询员工姓名,和其领导的姓名.（自关联）
SELECTe.ename,m.ename
FROMempe,empm
WHEREe.mgr=m.empno;
例4:
查询员工姓名,和其领导的姓名,及其所在部门的名称.
SELECTe.ename,m.ename,d.dname
FROMempe,empm,deptd
WHEREe.mgr=m.empnoANDe.deptno=d.deptno;
例5,查询员工的姓名,工资,所在部门名称,工资的等级.
SELECTe.ename,e.sal,d.dname,s.grade
FROMempe,deptd,salgrades
WHEREe.deptno=d.deptnoANDe.salBETWEENs.losalANDs.hisal;
例6,查询员工的姓名,工资,所在部门名称,工资的等级,及其领导的姓名,工资和工资等级
SELECTe.ename,e.sal,d.dname,s.grade,m.ename,m.sal,ms.grade
FROMempe,deptd,salgrades,empm,salgradems
WHEREe.deptno=d.deptnoAND
e.salBETWEENs.losalANDs.hisalAND
e.mgr=m.empnoAND
m.salBETWEENms.losalANDms.hisal;
扩展;把例6中的工资等级显示成一等二等....五等.
例7,重新运行例2,显示结果中为什么没有40部门的信息?
SELECTe.empno,e.ename,d.deptno,d.dname,d.loc
FROMempe,deptd
WHEREe.deptno（+）=d.deptno;
（+）在左边叫右连接,意思是说查询后的结果以后边表为准.
例8:
查询员工编号,姓名,和其领导的编号,姓名.
SELECTe.empno,e.ename,m.empno,m.ename
FROMempe,empm
WHEREe.mgr=m.empno;
会发现结果中没有7839号员工的信息,为什么呢?
SELECTe.empno,e.ename,m.empno,m.ename
FROMempe,empm
WHEREe.mgr=m.empno（+）;
（+）在右边叫左连接,意思是说查询后的结果以左边表为准.
例9:
交叉连接（CROSSJOIN）:
会产生笛卡尔积.
SELECT*FROMempCROSSJOINdept;
例10:
自然连接（NATURALJOIN）:
自动匹配关联字段.
SELECT*FROMempNATURALJOINdept;
例11:
USING子句:
直接关联操作列.
SELECT*FROMempeJOINdeptdUSING（deptno）;
例12:
ON子句:
手动编写连接条件.
SELECT*FROMempeJOINdeptdON（e.deptno=d.deptno）;
例13:
RIGHTOUTERJOIN右连接（也叫右外连接）.
参照例7:
SELECTe.empno,e.ename,d.deptno,d.dname,d.loc
FROMempeRIGHTOUTERJOINdeptdON（e.deptno=d.deptno）;
例14:
LEFTOUTERJOIN左连接（也叫左外连接）.
参照例8:
SELECTe.empno,e.ename,m.empno,m.ename
FROMempeLEFTOUTERJOINempmON（e.mgr=m.empno）;
六,分组函数,分组查询
1,分组函数
COUNT（）:
求一组记录的总条数.
MAX（）:
求一组数值记录中的最大值
MIN（）:
求一组数值记录中的最小值
AVG（）:
求一组数值记录的平均值
SUM（）:
求一组数值记录的和
例1:
SELECTCOUNT（empno）FROMemp;
例2:
SELECTMAX（sal）FROMemp;
例3:
SELECTMIN（sal）FROMemp;
例4:
SELECTAVG（sal）FROMemp;
例5:
SELECTSUM（sal）FROMemp;
2,分组查询
GROUPBY语句,用在WHERE语句之后,ORDERBY语句之前
例1:
求出每个部门的员工人数.显示部门编号,和人数.
SELECTdeptno,COUNT（empno）FROMempGROUPBYdeptno;
例2:
求出每个部门的平均工资,显示部门编号,和平均工资.;
SELECTdeptno,AVG（sal）FROMempGROUPBYdeptno;
思考1:
SELECTdeptno,AVG（sal）FROMemp;能否运行成功?
使用分组函数要注意:
1）,如果使用了分组函数,也使用了GROUPBY语句,并指定了分组的条件,那么在查询参数里可以将分组条件一起查询出来.
如:
例1中,分组条件是deptno,那么可以在查询时一起查询出来.
2）,如果查询中使用了分组函数,但没有使用GROUPBY语句分组,那么在查询参数里不可以查询其他信息.也就是说此时的分组函数只能单独使用.
如:
思考1中去不能查询deptno.
3）,使用分组函数,和GROUPBY语句进行分组查询时,不能出现分组条件之外的字段.
如:
SELECTempno,deptno,COUNT（empno）FROMempGROUPBYdeptno;
例3:
按部门分组,并显示部门名称和每个部门的员工人数.
SELECTd.dname,COUNT（e.empno）
FROMdeptd,empe
WHEREd.deptno=e.deptno
GROUPBYd.dname;
例4:
显示平均工资大于2000的部门的编号和平均工资.
SELECTdeptno,AVG（sal）
FROMemp
WHEREAVG（sal）>2000
GROUPBYdeptno;
运行此语句出错.
因为:
分组函数只能在分组语句中使用,不能在WHERE语句中使用.
如果分组中要有条件,使用HAVING子句.
SELECTdeptno,AVG（sal）
FROMemp
GROUPBYdeptnoHAVINGAVG（sal）>2000;
思考2:
显示非销售人员的工作名称以及从事同一工作的员工的月工资总和,要求工资总和大于5000,按工资总和降序排序.
1,显示非销售人员
SELECT*FROMempWHEREjob!
='SALESMAN';
2,按照工作分组,求出月工资总和.
SELECTjob,SUM（sal）
FROMemp
WHEREjob!
='SALESMAN'
GROUPBYjob;
3,月工资总和>5000,说明要进行分组条件限制.
SELECTjob,SUM（sal）
FROMemp
WHEREjob!
='SALESMAN'
GROUPBYjobHAVINGSUM（sal）>5000;
4,按工资总和降序排序.
SELECTjob,SUM（sal）ss
FROMemp
WHEREjob!
='SALESMAN'
GROUPBYjobHAVINGSUM（sal）>5000
ORDERBYssdesc;
思考3:
求出平均工资最高的部门的平均工资.（分组函数嵌套）
SELECTdeptno,MAX（AVG（sal））
FROMemp
GROUPBYdeptno;
此语句运行错误,
因为:
分组函数嵌套使用时.不能出现分组条件的查询.
SELECTMAX（AVG（sal））
FROMemp
GROUPBYdeptno;
七,子查询
例1:
查询工资大于7934的员工的所有的员工信息.
SELECT*
FROMemp
WHEREsal>（
SELECTsal
FR
升级会员 