Oracle学习笔记：SQL更新数据和一些常用函数

数据库中的数据变更，主要有以下三种：插入数据（insert）、修改数据（update）、删除数据（delete）。这几种操作都是开发人员常用的操作。本章将针对这几种操作进行简要讲解。本章的主要内容包括：insert操作及注意事项update操作及注意事项delete操作及注意事项通过本章的学习，读者既可以学习insert/update/delete操作的基本语法，同时，还可以了解实际开发中的注意事项。1 插入数据——insert操作insert操作用于向表中插入新的数据。insert操作既可以单条插入，也可以与子查询结合使用实现批量插入。本章将讲述insert操作的语法及使用。 1.1 单条插入对于insert操作来说，单条插入是最常用的方式，其语法形式如下所示。insert into 表名（列名1, 列名2, 列名3…列名n）values（值1, 值2, 值3…值n）在insert操作中，列名列表中的各列需要以逗号进行分隔；而值列表指定各列的值，列名与值需要一一对应 1.2 批量插入表c_students的数据结构及内容如下所示。SQL> select * from c_students; STUDENT_ID STUDENT_NAME我们可以利用如下SQL语句向其中插入新的数据，新数据来自表students中student_id处于前十的学生信息。SQL> insert into c_students（student_id, student_name） select student_id, student_name from studentswhere student_id<=10; 1.3 注意事项与技巧insert操作语法简单，也是广大开发人员掌握的最熟练的SQL语句之一。在实际开发过程中，有以下问题和技巧需要注意。1．应该养成使用列名列表的习惯2．快速获得列名列表2 更新数据——update操作update操作用于更新已有数据。 2.1 update更新单列update操作的语法形式如下所示。update 表名 set 列 = 新值其中update命令用于更新表中数据，其后紧跟表名；set命令用于重新设置列值，其后紧跟列名，并用等号指定新值。在表students中，列status的值均为小写形式。我们可以利用update语句将其转换为大写形式。SQL> update students set status = upper（status）;2.2 update更新多列使用update语句，同样可以更新多列，其语法如下所示。update 表名 set 列1=新值1, 列2=新值2, …在set命令之后，可以为多列同时赋值，而这些列之间使用逗号进行分隔。对于表students中的学生信息，可以在修改列status的同时，修改列student_age的值。例如，除了将status进行大写转换之外，还需要将学生年龄student_age增加1。相应的SQL语句如下所示。SQL> update students set student_age = student_age+1, status = upper（status）;2.3 注意事项对于update操作，最容易被用户忽视的就是添加where条件。在示例5-3及示例5-4所进行的操作实际是非常危险的。因为没有添加任何限制条件，因此Oracle将更新表中所有数据。而实际应用中，往往只需更新部分数据。因此，对于重要数据的update操作，首先添加where关键字是一个好的习惯，尽管有时候并不需要过滤条件。为update操作添加where条件。SQL> update students set student_age = student_age+1 where student_age > 20;3 删除数据——delete操作delete操作用于删除表中数据。除了delete语句之外，truncate命令同样可以删除表中数据。 3.1 delete操作delete操作用于删除表中数据，其使用语法如下所示。delete from 表名delete from指定从哪个表中删除数据。因为删除动作的作用对象为记录级别，因此无需指定列名信息。我们可以利用delete命令删除表students中、employee_id大于10的记录，相应的SQL语句如下所示。SQL> delete from students where student_id>10; 3.2 delete操作与truncate table操作除了delete命令，Oracle还可以利用truncate table命令来删除表中数据。但是truncate table语句与delete语句是有着本质区别的。delete语句与insert、update语句同属于DML——数据操作语言的范畴，当数据修改之后，可以通过回滚操作，来忽略所做的数据修改。而truncate table语句则是属于DDL——数据定义语言的范畴，当数据被删除之后，无法回滚。 SQL>rollback ; lower函数：
此函数将全部的大写字母都可以变为小写字母
SELECT lower（"ABCDE"） FROM dual ;
? dual表为一个虚拟表，以后介绍upper函数：将输入的字符串变为大写字母
SELECT upper（"abcd"） FROM dual ;例如：之前查询的时候区分大小写，所以此处如果输入的是小写字母，就可以通过upper函数进行转换。
查询雇员姓名内容为smith的全部员工。
SELECT * FROM emp WHERE ename=UPPER（"smith"） ;initcap函数：将每个字符串的首字母大写
例如：要求将雇员表中的全部雇员信息的首字母大写。
SELECT initcap（lower（ename）） FROM emp ;
函数可以进行嵌套。concat函数：字符串连接，可以连接两个字符串
SELECT concat（"hello"," world！！！"） FROM dual ;
实际上使用“||”也可以完成两个字符串的连接操作。substr函数：字符串截取
截取的时候需要注意：要从那里开始截取，之后取多长的长度。
SELECT substr（"hello",1,2） FROM dual ;
注意：substr函数的开始点是从1开始的。length：取出字符串的长度，例如，取出每一个雇员的姓名的长度：
SELECT ename||" 姓名的长度为："||length（ename） FROM emp ;instr函数：查找在一个字符串中是否有指定的字符串，如果有，则返回其位置
SELECT instr（"hello","x"） FROM dual ;
? 如果有此字符串，则返回位置，如果没有，则返回0。replace函数：替换，可以将一个字符串中的指定字符串替换为其他内容：
SELECT replace（"hello","l","x"） FROM dual ;trim函数：去掉左右空格的函数
SELECT trim（" hello "） FROM dual ;查询：
1、检索姓名最后一个字母为N的雇员
? SELECT * FROM emp WHERE substr（ename,-1,1）="N";
2、检索职务为“SALE”的全部员工信息
? SELECT * FROM emp WHERE substr（job,1,4）="SALE";ROUND表示四舍五入
?SELECT ROUND（34.56,-1） FROM dual ;
TRUNC函数：表示截取函数
? SELECT TRUNC（34.56,-2） FROM dual ;
MOD函数：取余数
?SELECT MOD（10,3） FROM dual ;取得当前日期：
在Oracle中可以通过查询sysdate取得当前的日期：
? SELECT sysdate FROM dual ;
例如：计算10部门中的员工进入公司的日期数：
肯定使用当前日期-雇佣日期（hiredate） = 天数，天数/7为日期数。
SELECT ename,round（（sysdate-hiredate）/7） from emp ;
例如：
求出所有员工的受雇月数：months_between，使用当前日期与雇佣日期比较。
? SELECT ename,round（MONTHS_BETWEEN（sysdate,hiredate）） from emp ;ADD_MONTHS：表示在日期的基础上增加几个月之后的日期：
求出，三个月之后的日期是那一天：
SELECT ADD_MONTHS（sysdate,3） FROM dual ;NEXT_DAY：表示下一个这天（星期几）
SELECT NEXT_DAY（sysdate,"星期一"） FROM dual ;LAST_DAY：求出当前日期所在月的最后一天：
SELECT LAST_DAY（sysdate） FROM dual ;例如：
显示受雇时间不满320-个月的雇员的编号、受雇日期、受雇的月数、满六个月的复审日期、受雇后的第一个星期五以及受雇当月的最后一天
求出雇佣月数大于320的雇员信息：
SELECT * FROM emp WHERE MONTHS_BETWEEN（sysdate,hiredate）>320 ;
综合：
SELECT empno 雇员编号,hiredate 雇佣日期,
round（MONTHS_BETWEEN（sysdate,hiredate））雇佣月数 ,
add_months（hiredate,6）复审日期,next_day（hiredate,"星期五"）受雇后到第一个星期五,
last_day（hiredate）最后一天
FROM emp WHERE MONTHS_BETWEEN（sysdate,hiredate）>320 ;
例如：现在要求取出全部员工受雇的年份
实际上要对hiredate中的内容进行分离，取出年份。
SELECT empno,ename,TO_CHAR（hiredate,"YYYY"） FROM emp ;
取出雇佣的月份和日：
SELECT empno,ename,TO_CHAR（hiredate,"YYYY"）年,
TO_CHAR（hiredate,"MM"）月,TO_CHAR（hiredate,"DD"）月 FROM emp ;也可以通过此函数把显示的日期的格式进行修正：
年-月-日：2008-4-7
SELECT empno,ename,TO_CHAR（hiredate,"YYYY-MM-DD"） FROM emp ;正常情况下的日期，如果是月应该是两位，如果现在是2月，则会显示02月，如果现在不希望显示出前导0，则可以加入一个fm
SELECT empno,ename,TO_CHAR（hiredate,"fmYYYY-MM-DD"） FROM emp ;
可以将一个数值按照指定的格式输出。
SELECT empno,sal FROM emp ; ? 此时不是很明确的知道查询出的工资，而且工资的位数也不标准。
使用to_char完成：SELECT empno,to_char（sal,"L99,999"） FROM emp ;to_date函数：
可以将一个字符串变为一个日期类型。
SELECT to_date（"2007-4-7","YYYY-MM-DD"） FROM dual ;要求：
查询出全部雇员的年薪（基本工资和佣金）。
SELECT empno,（sal+comm）*12 FROM emp ;
如果没有奖金的雇员，则年薪也没了，因为是null值，所以最后的计算结果仍然是null。
希望如果最后的值为null，则应该按0进行计算，此时就需要NVL函数的支持。Nvl函数可以将空值变为一个特定的数值
SELECT empno,sal,NVL（comm,0） FROM emp ;
使用此函数修改上面的查询：
SELECT empno,（sal+NVL（comm,0））*12 FROM emp ;
select empno,ename,job from emp ;
希望：
? 如果工作是CLERK：则显示办事员
? 如果工作是SALESMAN：则显示为销售人员
? 如果工作是MANAGER，则显示为经理
SELECT empno,ename,DECODE（job,"CLERK","办事员","SALESMAN","销售人员","MANAGER","经理"） FROM emp ;Oracle 11.1.0.7.3 升级 9352179，9776431 补丁Oracle学习笔记：SQL查询总结相关资讯 Oracle教程

Oracle中纯数字的varchar2类型和（07/29/2015 07:20:43）
Oracle教程：Oracle中查看DBLink密（07/29/2015 07:16:55）
[Oracle] SQL*Loader 详细使用教程（08/11/2013 21:30:36）

Oracle教程：Oracle中kill死锁进程（07/29/2015 07:18:28）
Oracle教程：ORA-25153 临时表空间（07/29/2015 07:13:37）
Oracle教程之管理安全和资源（04/08/2013 11:39:32）

本文评论查看全部评论（0）