Oracle.docx
《Oracle.docx》由会员分享，可在线阅读，更多相关《Oracle.docx（36页珍藏版）》请在冰豆网上搜索。
Oracle
Oracle服务：
其中只需要启动OracleServiceSID,Listener就可以了
在sqlplus中注视用”--”
Oracle数据类型：
在Oracle数据库中，本身提供的关键字是不区分大小写，但是再Oracle中所有的都默认大写；
Char
12，如果没有填满，那么用空格补充
NChar
最多可以存储2000个字节信息，只是记录符合长度范围内的数据，不会填充空格
Varchar2变长字符串
默认的是12，最多可以存储4000个字节信息
NVarchar
Unicode
Number
（p,s）例如：
Number（7,2）
Date
时间类型
No1
No2
No3
No4
RowId
SqlPlus：
cmd—sqlplus
用户名：
scott
密码：
tiger
ERR:
accountislocked;
切换登录：
conn
先用sys账户登录:
sys/systemassysdba
然后执行命令：
alteruserscottaccountunlock;
SQL语句：
*常用DML操作
SELECT（查找）
设置窗口的属性：
setlinesize300;
setpagesize30;
查看当前用户下有哪些表：
SELECT*FROMtab;
查询完后，显示的效果为上图；
列出某个表中的所有字段描述：
DESC“tablname”；
雇员表（EMP）
字段名称
类型
说明
EMPNO
NUMBER（4）
雇员编号（pk）
ENAME
VARCHAR2（10）
雇员姓名
JOB
VARCHAR2（9）
职位
MGR
NUMBER（4）
领导
HIREDATE
DATE
受雇日期
SAL
NUMBER（7,2）
工资
COMM
NUMBER（7,2）
佣金（奖金）
DEPTNO
NUMBER
（2）
部门编号（fK）
部门表（DEPT）
字段名称
类型
说明
DEPTNO
NUMBER
（2）
部门编号（pk）
DNAME
VARCHAR2（14）
部门名称
LOC
VARCHAR2（13）
地址
查询emp表中的所有内容：
select*fromemp;
想按照字段去查询：
select{empno,ename,hiredate}|*fromemp;
想给每个字段设置查询展示别名：
selectempnoas雇员编号,enameas雇员姓名,hiredateas受雇日期fromemp;
说明：
”as”是可以省略的。
限制约束WHERE
查询工资大于2000的雇员的编号，姓名和工资；
selectempno,ename,salfromempwheresal>2000;
查询工资大于2000，并且是部门编号为20的所有雇员
说明：
多个限制条件用and连接
select*fromempwheresal>2000anddeptno=20;
如果屏幕显示：
未选定行说没有符合条件的数据，
Oracle对值的大小写是敏感的
查询所有领导和所有的职员，计算“和”用OR
select*fromempwherejob='MANAGER'orjob='CLERK';
去重复关键字：
DISTINCT
查询雇员里面所有描述的部门编号
selectdistinctdeptnofromemp;
找出佣金高于薪金60%的员工（comm>sal*0.6）
select*fromempwherecomm>sal*0.6;
ISNULL/ISNOTNULL
selectdistinctjob,enamefromempwherecommisnotnull;
模糊查询：
LIKE：
模糊查询，分为左、右、全模糊
模糊的方向是有“%”决定的：
例如：
‘S%’右模糊
‘%S’左模糊
‘%S%’全模糊
下划线：
“_”属于展位，占一个字符
排序ORDERBY（升序：
asc默认，降序：
desc）
说明：
默认的为asc
select*fromemporderbysaldesc;
select*fromemporderbysalasc;
DUAL哑元表
在oracle中用dual去确定一个变量查询时所引用的实体（Entity）
函数在查询中的应用
时间类型函数
sysdate：
返回当前系统时间
selectadd_months（sysdate,5）fromdual;--增月
selectadd_months（sysdate,-2）fromdual;--减月
受雇日期：
select*fromempwherehiredate=last_day（hiredate）-1;
字符串函数
在oracle中连接通常不会concat，一般直接使用“||”符号
特殊函数：
casewhenthen：
selecttable_name,
CASE
WHENowner='SYS'THEN'TheownerisSYS'
WHENowner='SYSTEM'THEN'TheownerisSYSTEM'
ELSE'Theownerisanothervalue'
END
fromall_tables;
DECODE
selectdecode（job,'MANAGER','经理','CLERK','职员','普通员工'）fromemp;
基本的嵌套查询：
遗留问题：
Using
知识点回顾
sysassysdbc/orcl
setlinesizenum;
setpagesizenum;
切换连接：
conn（connection）username/password;
常用的数据类型
CHAR
12，如果没有填满，那么用空格补充
VARCHAR2
默认的是12，最多可以存储4000个字节信息
NUMBER
（p,s）例如：
Number（7,2）
TIMESTAMP
DATE
时间类型
常用函数
时间类型：
Sysdate
Add_months
Last_day
Months_between
Trunc
字符类型
UPPER
返回字符串,并将所有的字符大写
LOWER
返回字符串,并将所有的字符小写
INITCAP
返回字符串并将字符串的第一个字母变为大写
SUBSTR
SUBSTR（string,start,count）取子字符串,从start开始,取count个
LENGTH
返回字符串的长度
INSTR
INSTR（C1,C2,I,J）
在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
C1被搜索的字符串
C2希望搜索的字符串
I搜索的开始位置,默认为1
J出现的位置,默认为1
TRIM
TRIM（'s'from'string'）
LEADING剪掉前面的字符
TRAILING剪掉后面的字符
如果不指定,默认为空格符
REPALCE
REPLACE（'string','s1','s2'）
string希望被替换的字符或变量
s1被替换的字符串
s2要替换的字符串
数字类型
dbms_random.value（p,s）
round返回舍入小数点右边n2位的n1的值，n2的缺省值为0，这回将小数点最接近的整数，如果n2为负数就舍入到小数点左边相应的位上，n2必须是整数
mod返回一个n1除以n2的余数
其他的类型
TO_DATE
TO_NUMBER
TO_CHAR
CASEWHENTHEN
…..
ELSE
END
DECODE（columnName,exp,’value’,exp,’’,default）
基本查询语句：
Select*fromtablename;
ORACLE复杂查询
单行查询
多表查询
关联查询
分组统计
ORDERBY排序：
DESC:
降序ASC（默认）:
升序
多表查询
查询员工名称为SMITH的部门编号，部门名称，工资，部门位置
第一步：
查询出SMITH的信息
SELECT*FROMEMPWHEREENAME='smith'
SELECT*FROMemp,dept;
运行结果为56条=雇员表中的数据总数*部门表中的数据总数
上述计算成为笛卡尔乘积
聚合函数：
Count:
计算总数
Max：
计算最大值
Min：
计算最小值
Avg：
取平均值
count（*|columnname）
selectcount（*）fromemp;查询emp表中一共有多少条数据
selectcount（*）fromdept;
为了规避大量运算，在笛卡尔乘积的基础上加上过滤运算
select*fromemp,dept
whereemp.deptno=dept.deptno;
意思就是：
雇员表中的编号值=部门表中的编号值
Max取得最大值
selectmax（sal）fromemp;
Min
selectmin（sal）fromemp;
Avg
selectavg（sal）fromemp;
查询所有雇员的姓名，工资，部门编号，部门名称：
selectename,sal,emp.deptno,dept.dname
fromemp,dept
whereemp.deptno=dept.deptno;
上述引用字段名称的表名比较长的时候，最好是给每个表取别名：
取别名要比不取别名的效率高。
selecte.ename,e.sal,d.deptno,d.dname
fromempe,deptd
wheree.deptno=d.deptno;
selecte.ename雇员姓名,e.sal工资,d.deptno部门编号,d.dname部门名称
fromempe,deptd
wheree.deptno=d.deptno;
关联查询
内联
外联
交叉
查处所有的人员信息和所有的部门信息，显示效果：
有部门的职员列出部门信息，有职员的部门列出部门下所有的职员，如果某一部门没有职员同一出现在列表中;
内联：
关联表：
innerjoin（innser可以省略）+on+关联表之间的关系
过滤数据的原则：
完全符合条件的数据
select*fromempinnerjoindeptonemp.deptno=dept.deptno;
外联：
左联（左外联），右联（右外联），全联
左联：
select*fromempe,deptdwheree.deptno=d.deptno（+）;
“+”在那一遍，说明是被连接的对象（table）,是以连接一方为基准
右联：
select*fromempe,deptdwheree.deptno（+）=d.deptno;
Sql1999标准中对连接进行关键字定义
左联：
LEFTJOINtable+on+关联条件
select*fromempeleftjoindeptdone.deptno=d.deptno;
右联：
RIGHTJOINtable+on+关联条件
select*fromemperightjoindeptdone.deptno=d.deptno;
全联：
fulljoin
select*fromempefulljoindeptdone.deptno=d.deptno;
查询’10’部门的所有领导和’20’部门所有办事员的姓名，部门名称，工资
selecte.ename姓名,d.dname部门名称,e.sal
fromempeleftjoindeptdone.deptno=d.deptno
where（e.deptno=10ande.job='MANAGER'）or（e.deptno=20ande.job='CLERK'）;
交叉连接：
CROSSJOIN
Select*fromempecorssjoindeptd.?
分组
Groupby：
分组
查询每个部门都有多少人
selectcount（*）,deptno
fromemp
wheredeptnoisnotnull
groupbydeptno;
限制条件
Where
Where限制的条件中是不允许使用聚合函数
Having
运算是在基本运算之后执行，在having中可以使用聚合函数进行运算
查询平均工资大于2000的部门
selectavg（sal）,deptno
fromemp
groupbydeptno
havingavg（sal）>2000;
Orderby拼接
selectavg（sal）,deptno
fromemp
groupbydeptno
havingavg（sal）>2000
orderbydeptnoasc;
执行顺序：
groupby-having--orderby
groupby-orderby-having:
在过滤脏数据之前，不允许排序
数据的修改
添加
删除
更新
Update:
更新
updatedeptsetdname='RESEARCH'wheredeptno=20;
update
updateempsetsal=decode（deptno,10,sal*1.1,20,sal*1.15,30,sal*1.2,sal*1.18）;
查找雇员姓名，工资，部门名称，工资级别，所属领导，领导的工资级别
1.查找雇员姓名，工资，部门名称，工资级别
selecte.ename,e.sal,d.dname,s.grade
fromempe,deptd,salgrades
wheree.deptno=d.deptnoande.salbetweens.losalands.hisal;
2.加上领导和对应的工资级别
selecte.ename,e.sal,d.dname,s.grade,m.ename,ms.grade
fromempe,deptd,salgrades,empm,salgradems
wheree.deptno=d.deptnoande.salbetweens.losalands.hisal
ande.mgr=m.empnoandm.salbetweenms.losalandms.hisal;
显示结果：
要求：
grade=1:
第五级别工资
grade=2:
第四级别工资
grade=3:
第三级别工资
grade=4:
第二级别工资
grade=5:
第一级别工资
selecte.ename,e.sal,d.dname,
decode（s.grade,1,'第五级别',2,'第四级别',3,'第三级别',4,'第二界别',5,'第一级别'）,
m.ename,ms.grade
fromempe,deptd,salgrades,empm,salgradems
wheree.deptno=d.deptnoande.salbetweens.losalands.hisal
ande.mgr=m.empnoandm.salbetweenms.losalandms.hisal;
子查询
子查询就是在当前查询中包含其他查询
要查询比7654工资还要高的员工
1.查询7654相关信息
select*fromempwheresal>（selecte.salfromempewheree.empno=7654）
2.要求查询不比7654工资高，同时与7788从事相同工作的全部雇员的信息
select*fromempwheresal<=（
selectsalfromempwhereempno=7654）
andjob=（
selectjobfromempwhereempno=7844）
3.要求查询出工资最低的雇员的姓名、工作、工资
selecte.ename姓名,e.job工作,e.sal工资fromempewheresal=（
selectmin（sal）fromemp）
4.要求查询出部门名称，部门的员工数、部门的平均工资、部门的最低收工资和最高的工资
selectmxiDept.dname,mxiDept.ag,mxiDept.mi,mxiDept.ma,count（oute.empno）
fromempoute,
（
selectd.deptno,d.dname,min（e.sal）mi,max（e.sal）ma,avg（e.sal）ag
fromempe,deptd
wheree.deptno=d.deptno
groupbyd.deptno,d.dname
）mxiDept
whereoute.deptno=mxiDept.deptno
groupbymxiDept.dname,mxiDept.ag,mxiDept.mi,mxiDept.ma
子查询中有相应的操作符：
IN
ANY
ALL
IN：
规范的跟已有的结果集对应的值相同的范围
select*from
empwheresalin
（selectsalfromempewheree.ename=upper（'smith'））
select*fromempwheredeptnoin（10,20）
ANY:
>any:
比较的是最小的
select*from
empwheresal>any
（selectsalfromempewheree.enamein（upper（'smith'）,upper（'MARTIN'）））
select*fromempwheredeptnoin（10,20）
比较的是最大的
select*from
empwheresal（selectsalfromempewheree.enamein（upper（'smith'）,upper（'MARTIN'）））
ALL
>All比最大的都大
对数据的更改：
--提交
commit;
--回滚
rollback;
rollback;
拷贝表：
createtableMYEMPasselect*fromemp;
插入数据：
/*
insertintotablename（字段1，字段2，字段3....字段n）values（字段1的值，字段2的值，字段3的值....字段n的值）
在values中的值必须是符合字段对应类型的
*/
insertintomyemp--（empno,ename,job,mgr,hiredate,sal,comm,deptno）
values（8001,'JACK','ANALYST',7566,to_date（'1985-03-04','yyyy-MM-dd'）,1900,0,20）
insertintomyemp（empno）values（8003）
修改数据
/*
updatetablenameset字段1=值1,字段2=值2......
{where条件字段=条件值......}
*/
updatemyempsetename='HUGO'whereempno=8003
删除数据
/*
delete{from}tablename{where条件}
*/
deletefrommyempwhereempno=7369
Where1=1用法
UpdatetablenamesetcolumnName=valuewhere1=1
表管理和约束
表的创建和管理
约束管理
表的创建（重点）
复制表
（1）:
复制了表的结构同时复制了表中的数据
createtablemyempasselect*fromempwhere1=1
复制表
（2）复制表结构，而不复制表中数据，其实就是用where进行限制
createtabletestempasselect*fromempwhere1=2
创建表：
createtable表明（
字段1类型default默认值,
字段2类型default默认值,
.....
字段n类型default默认值,
）;
USER不能作为数据库的表名称
创建一个org_user表，其中的字段为
用户表（ORG_USER）
No.
ColumnName
Type
Desc
1
userid
NUMBER（10）,
用户ID
2
username
VARCHAR2（50）
用户姓名
3
login
VARCHAR2（15）
用户登录名
4
pass
Varchar2（10）
登录密码
5
uidentity
varchar2（20）
身份证号
createtableorg_user（
useridNUMBER（10）,
usernameVARCHAR2（50）,
loginVARCHAR2（15）,
passVarchar2（10）,
uidentityvarchar2（20）
）;
表删除
如果当前表创建失误，那么就需要删除
关键字：
DORPTABLETABLENAME
droptabletestemp;
这样删除会出现一个亿“BIN”开头的垃圾表，原因是oracle为了防止用户将数据库表误删除，将所删除的表放入到垃圾回收站中，这样用户就可以通过回闪（flashback）将所删
升级会员 