oracle增删改查详细图文.docx
《oracle增删改查详细图文.docx》由会员分享，可在线阅读，更多相关《oracle增删改查详细图文.docx（23页珍藏版）》请在冰豆网上搜索。
oracle增删改查详细图文
oracle增删改查详细图文
目录
1用户模式2
2查找数据（select）3
2.1简单查询4
2.2分组查询8
2.21groupby函数:
分类8
2.22having子句9
2.3连接9
2.3.1表别名9
2.3.2内连接10
2.3.3外连接10
2.3.4左外连接10
2.3.5又外连接11
2.3.6完全外连接11
2.3.7自然连接12
2.3.8自连接12
2.3.9交叉连接13
2.4常用系统函数13
2.4.1字符类函数13
2.4.2数字类函数15
2.4.3日期和时间类函数16
2.4.4转换类函数16
2.4.5聚集类函数17
2.5子查询17
2.5.1单行子查询17
2.5.2多行子查询17
2.5.3关联子查询18
3插入数据（insert）19
4更新数据（update）20
5删除数据（delete和truncate）20
1用户模式
1模式和模式对象
2示例模式scott
模式：
是一系列逻辑结构或对象的集合
模式对象是由用户创建的逻辑结构
示例模式scott
查询表selecttable_namefromuser_tables;
如何在system模式下查询scott模式所拥有的数据表
使用dba_tables数据表
进入system模式
connsystem/password
selecttable_namefromdba_tableswhereowner=’SCOTT’;
部门表
雇员表emp
工资登记表salgrade
工资补贴表bonus
2查找数据（select）
selcet{[distinct|all]columns*}
from{tables|views|otherselect}用于指定数据来源，包括表，视图，和其他select语句
[whereconditions]用于对检索的数据进行筛选，字句中不能用聚集函数如：
avg（）,max（）,sum（）
[groupbycolumns]对检索结果分组显示与聚集函数一起使用时，groupby创建组，聚集函数运算每组值
[havingconditions]用于从使用groupby自居分组后的查询结果中筛选数据行，经常包括统计函数
[orderbycolumns[ASC|DESC]]对结果进行排序
2.1简单查询
查询单个表中所有列
在system模式下查询表
表名前面要加上改表模式所属名称
connsystem/passwd
select*fromscott.emp
查询多个表中所有列
scott模式
eg同时查询dept和salgrade表
select*fromdept,salgrade
查询特定列
select后面加想要查询的列名，用‘，’隔开
eg:
在scott模式下检索emp表中指定列（job,ename,empno）
selectjob,ename,empnofromemp
为列指定别名
eg：
检索emp表员工编号，姓名和年基本公司，日基本工资信息。
sal=月基本工资
年基本工资=sal*12
日基本工资=sal/30
查询语句
selectempno,ename,sal*12,sal/30fromemp;
使用列别名
列名[AS]列别名
enameas员工姓名
empno员工编号
selectempnoas“员工编号”,enameas“员工名称”’,sal*12as”年基本工资”sal/30as“日基本工资”fromemp;（注意引号为双引号）
as可省略
带有表达式的select子句
select语句中对于数字数据和日期都可以使用算术表达式，包括+-*/（）
eg:
检索emp表的sal列，吧其值调整为原来的1.1倍
selectename.sal,sal*（1+0.1）fromemp;
查询结果中包含重复行
eg显示emp表中job职务列
消除重复:
distinct作用是消除重复内容，即一条完整数据全部是重复的，如果多行记录只有一列重复而其他列不重复，那么也是无法消除的；
查询比较大的表时候尽量避免使用distinct关键字，因为结果进行排序，效率低
eg
selectdistinctdeptnofromemp;
selectdeptnofromdept;
2.2筛选查询
选择行
1表达式比较
where
比较运算符格式:
expression{=|<|<=|>|>=|<>|!
=}expression
eg:
查询emp中工资大于1500的数据记录
2模式匹配like谓词
格式：
string_expression[not]likestring_expression[escape‘escape_character’]
like运算符可以使用通配符’%’,’_”其中：
‘%’:
代表0个或者多个字符。
‘_’:
代表一个且只能是一个字符。
注意（单引号）
3范围比较
关键字：
in（notin）
betweenbetweenand（notbetweenand）在…中间
4空值比较
查询emp表中无奖金的人
selectempno,ename,sal,jobfromempwherecommisnull;
2.2分组查询
2.21groupby函数:
分类
经常与聚集函数一起使用，使用groupby子句和聚合函数可以实现对查询结果中每一组数据进行分类统计，所以在结果中每个数据都有一个与之对应的统计值
selectjob,avg（sal）,sum（sal）,max（sal）,count（job）fromempgroupbyjob;
注意：
select子句可以是被分组的列名和统计函数，不能是其他列名
2.22having子句
having子句通常与groupby子句一起使用，在完成对分组结果统计后，可以使用having子句对分组结果做进一步筛选
eg：
emp表中分组方式计算出每个部门平均工资，然后再通过having子句过滤出平局工资大于2000的记录信息
排序
orderby
orderby（order_by_expression[ASC]（升序）|DESC（降序）]）
eg:
selectdeptno,empno,enamefromemporderbydeptno,empno;
2.3连接
2.3.1表别名
使用简短的表别名代替原有较长的表名称，这样就可以大大缩减语句的长度
eg：
通过deptno列来关联emp表和dept表，并检索这两个表中相关字段的信息
别名设置在from后面
一旦使用别名就不能用表名了
2.3.2内连接
内连接是一种常用的多表查询方式，一般使用关键字innerjoin来实现，其中Inner关键字可以省略，当使用join关键字是语句指标是内连接操作，on做连接条件
2.3.3外连接
共分为三种：
左外连接letfouterjoin或leftjoin
又外连接rightouterjoin或者rightjoin
完全外连接fullouterjoin或fulljoin
2.3.4左外连接
左外连接的查询结果中不仅包含了满足连接条件的数据行，还包含了左边中不满足条件的数据行。
（右表需满足条件）
eg:
首先使用insert在emp表中插入新纪录（注意没有为deptno和dname列插入值，即他们的值为null），然后实现emp表和dept表之间通过deptno列进行左外连接
结果如下图所示：
左表中不满足条件的9527也显示出来了
2.3.5又外连接
同理显示右表的全部信息
dname为operations的右表数据数据在左表中没有记录，不满足条件，也显示出来了
eg
fulljoin
2.3.6完全外连接
将左右两表数据全部显示出来，将查询结果合并，并消除重复行。
如下图所示9527，operations数据行都显示出来了
2.3.7自然连接
与内连接相似，检索多个表时，orcale会将第一个表列与第二个表中具有相同名称的列进行自动连接，自然连接不需要指定进行连接的列，由系统自动完成，自然连接使用”naturaljoin’关键字
自然连接中不能使用别名
eg：
在emp表中检索工资大于2000的记录，并实现emp表和dept表的自然连接
2.3.8自连接
用于自参照表上显示上下级关系或层次关系，指在同一表中的不同列之间具有参照关系或者主从关系的表，例如emp表包含empnomgr列两者参照关系
eg：
查出所有管理者所管理的下属员工信息
2.3.9交叉连接
交叉连接就是不需要任何连接条件的连接，他是用crossjoin关键字来实现。
他的结果集是笛卡尔积
1a
2b
3
4
该结果集为（1a1b2a2b3a3b4a4b）4*2=8（行数）
2.4常用系统函数
2.4.1字符类函数
ASCLL（C）函数和CHR（i）函数
concat（s1,s2）函数s2连接到s1后面
INITCAP（s）函数首字母大写函数
INSTR（S1,S2[,I][,J]）函数
LENGTH返回字符串长度
lowerupper将字符串全部换成小写，大写
ltrim（s1,s2）函数，删除左边字符串s2
rtrim（s1,s2）函数删除右边字符串s2
trim（s1,s2）函数删除两边字符串s2
replace（s1,s2[,s3]函数将s1中的s2函数替换成s3；
substr（s,I,[j]）
截取字符串从s中的l位置截取数据长度为j
2.4.2数字类函数
abs（n）返回绝对值
ceil（n）返回大于或者等于数值n的最小整数
floor（n）返回小鱼或者等于n的最大整数
cos（n）返回n的余弦值，n为弧度
exp（n）返回e的n次幂e=2.71828183
log（n1,n2）返回以n1为底n2的对数
mod（n1,n2）返回n1/n2的余数
power（n1,n2）返回n1的n2次方
round（n1,n2）返回n1小说点后n2位
sign（n）若n为负数则返回-1，若n为正数，则返回1，若n=0则返回0
sig（n）返回n的正弦值，n为弧度
sqrt（n）返回n的平方根n为弧度
trunc（n1,n2）返回结尾到n2位小数的n1的值
2.4.3日期和时间类函数
add_month（n1,n2）返回当前日期加上n2个月后的日期
last_day（n）返回当前月最后一天
month_between（d1,d2）返回d1和d2中间的数目
new_time（d1,t1,t2）
sysdate返回系统当前日期
2.4.4转换类函数
to_char（x[,format]）将表达式转换成字符串
to_date[s,format[lan]]将字符串转换成date类型，format表示字符串格式lan
表示使用的语言
to_number（s,format[lan]）返回字符串s代表的数字，返回值按照format格式显示
format表示字符串格式，lan表示语言
2.4.5聚集类函数
avg（x[distinct|all]）计算选择列表的平均值
count（）查询记录数
max（）查询最大值
min（）最小值
sum（）返回总和
variance（）统计方差
stddev（）返回列表项目的标准方差
2.5子查询
2.5.1单行子查询
单行子查询指返回一行数据的子查询语句，当在where子句中引用单行子查询时候，可以使用单行比较运算符（=,>,<,>=,<=,<>）
eg：
在emp表中，查询出既不是最高工资，也不是最低工资的员工信息
2.5.2多行子查询
多行子查询指返回多行数据的子查询语句，当在where子句中使用多行子查询时候，必须使用多行比较符（in,any,all）;
in
eg:
在emp表中，查询不是销售部门（sales）的员工信息
any：
只要匹配一个查询结果即可
eg:
查询工资大于部门编号是10的任意的一个员工的员工工资的员工信息；
selectempno,ename,sal
fromemp
wheresal>any（selectsalfromempwheredeptno=10）anddeptno<>10;
and所有，满足所有条件
eg：
查询工资大于部门编号30的所有员工工资的员工信息
selectempno,ename,sal
fromemp
wheresal>all（selectmax（sal）fromempwheredeptno=30）;
2.5.3关联子查询
有一些查询中内外查询需要相互依赖。
这种子查询叫关联子查询
eg:
在enp表查询工资大于同职位的平均工资的员工信息
selectempno,ename,salfromemp
wheresal>（selectavg（sal）fromempwherejob=emp.job）;
3插入数据（insert）
语法格式：
insertintotable_name[（column_name[,column_name2]…）]
values（express1[,express2]…）
commit提交
eg:
向部门表中插入数据
insertintodept（deptno,dname,loc）values（88,’design’,’beijing’）;
commit提交
创建一个表
4更新数据（update）
updatetable_name
set{column_name1=express1[,column_name2=express2…]
|（column_name1[,column_name2…]）=（selectSubquery）}
[wherecondition]
eg:
把销售员工资上调20%
5删除数据（delete和truncate）
1deletefromtable_name[wherecondition]
2truncatetabletable_name用truncate删除速度快没有回滚记录
删除表:
drop
升级会员 