oracle 函数、聚焦函数

oracle 常用的函数 以及 聚焦函数

1  
  2 
  3 --1,字符函数
  4 
  5 --当没有表可以用个的时候oracle自带一个虚表dual
  6 
  7 -- || 表示连接符号  将字符串连接到一起
  8 
  9 式显示
 10 
 11 Lower(char):将字符串转化为小写格式
 12 
 13 ?将所有员工的名字按小写的方
 14 
 15 select  lower(ename),lower(job) from emp;
 16 
 17  
 18 
 19 Upper(char):将字符转化为大写的格式
 20 
 21 select  upper('abcd')  from dual;
 22 
 23  
 24 
 25 Length(char):返回字符串的长度
 26 
 27 ?显示正好为5个字符员工的姓名
 28 
 29 select * from emp   where  length(ename)=5;
 30 
 31  
 32 
 33 Substr(char,m,n):取字符串的子串，m：表示开始的位置； n：要截取的字符个数
 34 
 35 ?显示所有员工姓名的前三个字符
 36 
 37 select ename ,substr(ename,1,3) from emp;
 38 
 39  
 40 
 41 Trim,Ltrim,Rtrim:去掉查询结果中的空格（全，左，右）
 42 
 43 select   '=='||  trim('    abc   ')  ||'==' from dual;
 44 
 45 ?以首字母大写的方式显示所有员工
 46 
 47 select ename, 
 48 
 49 substr(ename,0,1)||lower( substr(ename,2,length(ename)-1) ) 
 50 
 51 from emp;
 52 
 53  
 54 
 55  
 56 
 57 Replace (char1,search_string,replace_string): 查询替换
 58 
 59  ? 显示所有的员工姓名将A替换成“我是A”
 60 
 61 select  ename, replace(ename,'A','我是A')as result from emp;
 62 
 63  
 64 
 65  
 66 
 67 Instr(char1,char2,[,n[,m]])：取字符串的位置；n：开始位置，m：第m个char2的位置
 68 
 69  ? 从第1个字符开始找到员工姓名中第二个A所在的位置
 70 
 71 select ename,  Instr(ename,'A',1,2) from emp;
 72 
 73  
 74 
 75  
 76 
 77  
 78 
 79  
 80 
 81 --2,数学函数
 82 
 83 Round(n,[m])：四舍五入。
 84 
 85 如果省掉m，则四舍五入到整数；如果m是整数，则四舍五入到小数点后的m位后，如果是m负数，则 四舍五入到小数点m位前
 86 
 87 ?  73.2564  四舍五入到小数点后2位
 88 
 89 select  round(73.2564,2)   from dual;
 90 
 91  
 92 
 93 trunc(n,[m])：用户截取数字。
 94 
 95 如果省略m，就截去小数部分，如果m是整数就截取到小数点后m位后，如果m是负数，则截取到小数点的前m位
 96 
 97 ? 73.2564  截取到小数点后3位
 98 
 99 select  trunc(73.2564,3)  from dual;
100 
101  
102 
103 Mod(n,[m])：取模
104 
105 ?   8%3=?
106 
107 select mod(8,3) from dual;
108 
109  
110 
111 Floor(n)：向下取整
112 
113 ? 7.5846 向下取整
114 
115  
116 
117 Ceil(n)：向上取整
118 
119 ?7.5846 向上取整
120 
121 select  floor(7.1289) ,Ceil(7.1289) from dual;
122 
123  
124 
125 --2,其他函数
126 
127 　　Abs(n) 返回数字n的绝对值
128 
129 　　Acos(n) 返回n的反余弦值
130 
131 　　Asin(n) 返回n的反正弦值
132 
133 　　Atan(n) 返回n的反正切值
134 
135 　　Cos(n) 返回n的余弦
136 
137 　　Exp(n) 返回e的次幂
138 
139 　　Log(m,n) 返回对数
140 
141 　　Power(m,n) 返回m的n次幂
142 
143  
144 
145  
146 
147  
148 
149 --3,日期函数
150 
151 1 sysdate : 返回系统时间
152 
153 select  sysdate from dual;
154 
155  
156 
157 2 current_date ：返回当前时间
158 
159 select  current_date from dual;
160 
161  
162 
163 3 Next_day(date,week) ：给定时间的下一个星期是几号   /*week 数字和英文*/
164 
165  select  next_day(sysdate,'MONDAY') from dual;   
166 
167  
168 
169 4 add_months(d,n)：返回在时间d上加n个月的时间
170 
171 select add_months( sysdate,12 ) from dual;
172 
173  
174 
175 5 last_day(d): 返回指定日期所在月份的最后一天
176 
177  select last_day( sysdate) from dual;
178 
179  
180 
181  练习：
182 
183 ？查找已经入职362个月多的员工
184 
185 ？显示满30年份服务年限的员工的姓名和受雇日期
186 
187 ？每个员工加入公司的天数
188 
189 ？下个星期一是多少号
190 
191 ？找出各月倒数第三天受雇的所有员工
192 
193  
194 
195  
196 
197 --4,转换函数
198 
199 　　时间---->字符
200 
201 　　字符---->数字
202 
203 　　字符---->时间
204 
205  
206 
207 --4.1、to_char( 时间,格式)
208 
209 --时间格式：
210 
211 　　yyyy :显示年份
212 
213 　　yy :显示年份后2位
214 
215 　　mm :月份
216 
217 　　dd :号
218 
219 　　hh /hh24 :小时　　
220 
221 　　mi :分
222 
223 　　ss ：秒
224 
225 Eg：select to_char(sysdate,'yy-mm-dd hh24:mi:ss') from dual;
226 
227  
228 
229  
230 
231 --货币格式：
232 
233 　　9 ：显示数字
234 
235 　　0 ：不足则补0
236 
237 　　. ：显示小数点
238 
239 　　，:  显示分隔符
240 
241 　　$ :  显示美元符号
242 
243 　　L :  显示本地货币
244 
245 　　C : 显示国际货币符号
246 
247 　　G : 制定位置显示分割符号
248 
249 　　D : 制定位置显示小数点符号
250 
251 　　？薪水指定货币符号
252 
253  
254 
255 Eg:
256 
257 8756984.555
258 
259 $8,756,984.555
260 
261 $9,999,999.99
262 
263 L9,999,999.99
264 
265 select to_char(8756984.5,'L9,999,999.99') from dual;
266 
267 select to_char(8756984.5,'$9G999G999D99') from dual;
268 
269  
270 
271  
272 
273 --4.2、字符转日期 to_date('2010-10-10','yyyy-mm-dd')-----------------
274 
275 Eg: select to_date('2017-2-27','yyyy-mm-dd') from dual;
276 
277 (insert into  emp values( 9900,
278 
279 'ZHANGSAN','CLERK',7698,to_date('2010-10-10','yyyy-mm-dd'),2500.00,
280 
281 0.00,30);)
282 
283  
284 
285 --4.3、字符转数字  To_number(‘’)-------------------------------------
286 
287 select to_number('001')*8 from dual;
288 
289  
290 
291  
292 
293  
294 
295 --5，聚集函数  count,sum,avg,max,min --------------------------------
296 
297 --5.1、count ---统计总行数
298 
299 select count(*) from emp;
300 
301 select count(empno) from emp; --统计员工总人数
302 
303  
304 
305 --5.2、sum-----统计总和
306 
307 select sum(sal) as total from emp;  --统计所有人员的工资总和
308 
309  
310 
311 --5.3、avg-----统计平均数
312 
313 select avg(sal) from emp; --统计所有人员的工资平均值
314 
315  
316 
317 --5.4、max--最大值---min最小值
318 
319 select max(sal) as maxSal ,min(sal) as minSal from emp;
320 
321  
322 
323  
324 
325 ------聚合函数不能放到where 作为条件
326 
327 select * from emp where sal  avg(sal)； --错;
328 
329 select * from emp where sal>(select avg(sal) from emp)；---对
330 
331  
332 
333 --6，显示当前用户-
334 
335  select user from dual;
336 
337  
338 
339 --7，decode 布尔判断函数 ------
340 
341 --eg：查询出CLERK 的人员数量（如果job是clerk则输出1）
342 
343  select  sum( decode(job,'CLERK',1,0) ) from emp;
344 
345  
346 
347  --eg：查询各个工种有多少人
348 
349  select sum( decode(job,'CLERK',1,0) ) as CLERK,
350 
351         sum( decode(job,'SALESMAN',1,0) ) as  SALESMAN,
352 
353         sum( decode(job,'MANAGER',1,0) ) as  MANAGER,
354 
355         sum( decode(job,'ANALYST',1,0) ) as  ANALYST,
356 
357         sum( decode(job,'PRESIDENT',1,0) ) as  PRESIDENT,count(*) as total
358 
359         from emp;
360 
361  
362 
363 --8，nvl(column,default) 非空-----------------------------------
364 
365 --eg：emp表中所有员工在元奖金的基础上增加50元奖金
366 
367 update emp set comm=nvl(comm ,0 ) +50 ; --如果comm为空，就输出0，不为空就输出本身值
368 
369