oracle增删改查详细图文

oracle 增删改查详细图文目录1用户模式 (2)2 查找数据(select) (3)2.1简单查询 (4)2.2 分组查询 (8)2.21 group by函数: 分类 (8)2.22 having子句 (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查询表select table_name from user_tables;如何在system模式下查询scott模式所拥有的数据表使用dba_tables数据表进入system模式conn system/passwordselect table_name from dba_tables where owner=’SCOTT’;部门表雇员表emp工资登记表salgrade工资补贴表bonus2 查找数据(select)selcet {[distinct|all] columns *}from {tables|views|other select} 用于指定数据来源，包括表，视图，和其他select语句[where conditions] 用于对检索的数据进行筛选，字句中不能用聚集函数如：avg(),max(),sum() [group by columns] 对检索结果分组显示与聚集函数一起使用时，group by创建组，聚集函数运算每组值[having conditions] 用于从使用group by 自居分组后的查询结果中筛选数据行，经常包括统计函数[order by columns [ASC|DESC]]对结果进行排序查询单个表中所有列在system模式下查询表表名前面要加上改表模式所属名称conn system/passwdselect * from scott.emp查询多个表中所有列scott 模式eg 同时查询dept和salgrade 表select * from dept,salgrade查询特定列select后面加想要查询的列名，用‘，’隔开eg:在scott模式下检索emp表中指定列(job,ename,empno)select job,ename,empno from emp为列指定别名eg：检索emp表员工编号，姓名和年基本公司，日基本工资信息。

sal = 月基本工资年基本工资= sal*12日基本工资= sal/30查询语句select empno,ename,sal*12,sal/30 from emp;使用列别名列名[AS]列别名ename as 员工姓名empno 员工编号select empno as “员工编号”,ename as “员工名称”’,sal*12 as ”年基本工资”sal/30 as “日基本工资”from emp;（注意引号为双引号）as可省略带有表达式的select子句select 语句中对于数字数据和日期都可以使用算术表达式，包括+-*/()eg:检索emp表的sal列，吧其值调整为原来的1.1倍select ename.sal,sal*(1+0.1) from emp;查询结果中包含重复行eg 显示emp表中job职务列消除重复:distinct 作用是消除重复内容，即一条完整数据全部是重复的，如果多行记录只有一列重复而其他列不重复，那么也是无法消除的；查询比较大的表时候尽量避免使用distinct关键字，因为结果进行排序，效率低egselect distinct deptno from emp;select deptno from dept;2.2 筛选查询选择行1 表达式比较where比较运算符格式: expression{=|<|<=|>|>=|<>|!=} expressioneg:查询emp中工资大于1500的数据记录2 模式匹配like谓词格式：string_expression [not] like string_expression[escape ‘escape_character’] like 运算符可以使用通配符’%’,’_”其中：‘%’:代表0个或者多个字符。

‘_’:代表一个且只能是一个字符。

注意（单引号）3 范围比较关键字：in (not in )between between and (not between and)在…中间4 空值比较查询emp表中无奖金的人select empno,ename,sal,job from emp where comm is null;2.2 分组查询2.21 group by函数: 分类经常与聚集函数一起使用，使用group by 子句和聚合函数可以实现对查询结果中每一组数据进行分类统计，所以在结果中每个数据都有一个与之对应的统计值select job,avg(sal),sum(sal),max(sal),count(job) from emp group by job;注意：select子句可以是被分组的列名和统计函数，不能是其他列名2.22 having子句having 子句通常与group by 子句一起使用，在完成对分组结果统计后，可以使用having子句对分组结果做进一步筛选eg：emp表中分组方式计算出每个部门平均工资，然后再通过having子句过滤出平局工资大于2000的记录信息排序order byorder by (order_by_expression [ASC](升序)|DESC(降序)])eg:select deptno,empno,ename from emp order by deptno,empno;2.3 连接2.3.1 表别名使用简短的表别名代替原有较长的表名称，这样就可以大大缩减语句的长度eg：通过deptno列来关联emp表和dept表，并检索这两个表中相关字段的信息别名设置在from 后面一旦使用别名就不能用表名了2.3.2 内连接内连接是一种常用的多表查询方式，一般使用关键字inner join 来实现，其中Inner关键字可以省略，当使用join关键字是语句指标是内连接操作，on 做连接条件2.3.3 外连接共分为三种：左外连接letf outer join 或left join又外连接right outer join 或者right join完全外连接full outer join 或full join2.3.4 左外连接左外连接的查询结果中不仅包含了满足连接条件的数据行，还包含了左边中不满足条件的数据行。

（右表需满足条件）eg:首先使用insert在emp表中插入新纪录（注意没有为deptno和dname列插入值，即他们的值为null），然后实现emp表和dept表之间通过deptno列进行左外连接结果如下图所示：左表中不满足条件的9527也显示出来了2.3.5 又外连接同理显示右表的全部信息dname为operations的右表数据数据在左表中没有记录，不满足条件，也显示出来了egfull join2.3.6 完全外连接将左右两表数据全部显示出来，将查询结果合并，并消除重复行。

如下图所示9527，operations数据行都显示出来了2.3.7 自然连接与内连接相似，检索多个表时，orcale会将第一个表列与第二个表中具有相同名称的列进行自动连接，自然连接不需要指定进行连接的列，由系统自动完成，自然连接使用”natural join’关键字自然连接中不能使用别名eg：在emp表中检索工资大于2000的记录，并实现emp表和dept表的自然连接2.3.8 自连接用于自参照表上显示上下级关系或层次关系，指在同一表中的不同列之间具有参照关系或者主从关系的表，例如emp表包含empno mgr列两者参照关系eg：查出所有管理者所管理的下属员工信息2.3.9交叉连接交叉连接就是不需要任何连接条件的连接，他是用cross join 关键字来实现。

他的结果集是笛卡尔积1 a2 b34该结果集为（1a 1b 2a 2b 3a 3b 4a 4b）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 返回字符串长度lower upper 将字符串全部换成小写，大写ltrim(s1,s2)函数，删除左边字符串s2rtrim(s1,s2)函数删除右边字符串s2trim(s1,s2)函数删除两边字符串s2replace(s1,s2[,s3] 函数将s1中的s2函数替换成s3；substr(s,I,[j])截取字符串从s中的l位置截取数据长度为j2.4.2 数字类函数abs(n)返回绝对值ceil(n) 返回大于或者等于数值n的最小整数floor(n)返回小鱼或者等于n的最大整数cos(n)返回n的余弦值，n为弧度exp(n)返回e的n次幂e=2.71828183log(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 则返回0sig(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);ineg:在emp表中，查询不是销售部门（sales）的员工信息any：只要匹配一个查询结果即可eg:查询工资大于部门编号是10的任意的一个员工的员工工资的员工信息；select empno,ename,salfrom empwhere sal > any (select sal from emp where deptno =10) and deptno <> 10;and 所有，满足所有条件eg：查询工资大于部门编号30的所有员工工资的员工信息select empno,ename,salfrom empwhere sal > all (select max(sal) from emp where deptno=30);2.5.3 关联子查询有一些查询中内外查询需要相互依赖。