中国六冶的网站谁做的,商丘网站推广公司,电子商务网站建设与管理课程的感想,哪些网站是503错误代码这里写目录标题 函数系统函数转换函数to_date()to_char()将数值转换成字符格式 添加货币符号将日期转换成字符 其他不常用的转换函数 字符型函数连接函数大小写转换函数大写转换小写转换首字母大写#xff0c;其余的小写 替换函数去除空格函数截取函数填充函数获取字符长度函数… 这里写目录标题 函数系统函数转换函数to_date()to_char()将数值转换成字符格式 添加货币符号将日期转换成字符 其他不常用的转换函数 字符型函数连接函数大小写转换函数大写转换小写转换首字母大写其余的小写 替换函数去除空格函数截取函数填充函数获取字符长度函数获取字符位置函数 数值型函数绝对值函数向上取整向下取整取余函数四舍五入函数截取函数判断正负函数返回x的y次幂函数 日期型函数添加月函数该月最后一天函数间隔月函数NEW_TIME()NEXT_DAY()trunc()日期减法 聚合函数分组函数开窗函数分析函数窗口函数--重点其他函数空值替换函数去重条件判断取值函数重点CASE WHEN 函数
函数是已经封装好的程序会返回一个值直接拿来使用就可以了 特点返回一个值
系统函数
系统函数是已经存在的函数比如 聚合函数转换函数to_date()函数。 下面介绍一些常用函数
转换函数
to_date()
to_date(‘目标字符’,‘日期格式’); 功能将目标字符转换成想要的日期格式 返回日期 SYSDATE --系统日期
SELECT SYSDATE FROM dual;to_char()
转换字符函数 to_char(目标数值或日期,[‘格式’]) 功能将数值或者日期按照格式转换成字符 返回字符
将数值转换成字符
SELECT to_char(123.45) FROM dual //没给格式仅仅将数值转换成了字符。格式
0 代表格式 ----当整数位无数字则以0补全 ----当小数位无数字以0补全 ----当小数位精度不足则四舍五入 ----当整数位精度不足则显示成##### 9 代表格式 ----当整数位无数字则格式不生效只有此处与0不同 ----当小数位无数字以0补全 ----当小数位精度不足则四舍五入 ----当整数位精度不足则显示成###### 以上是格式9的用法0与9的区别只有第一条的区别不一样。 如果换成0整数位无数字会被0代替。
添加货币符号
$:美元符号 L:当地货币
将日期转换成字符 d-本周的第几天 注意每周的第几天美国人是按照星期日开始计算的 所以说星期三是本周的第四天 dd-本月的第几天 ddd-本年的第几天 w-本月的第几周 ww-本年的第几周 mm-本年的第几月 q-本年的第几季1.1-4.14.1-7.17.1-10.110.1-1.1 yyyy-年 day-星期几 year-多少年
其他不常用的转换函数
函数名作用chartorowid()字符串转换为rowid值ROWIDTOCHAR()rowid值转换成字符串CONVERT()字符串语言字符集转换HEXTORAW()16进制转换为二进制RAWTOHEX()二进制转换为16进制TO_NUMBER()字符串转换为数字型TO_MULTI_BYTE()半角转换为全角to_single_byte()全角转化为半角nls_charset_id()字符集名称转为IDnls_charset_name()字符集ID转换为名称 字符型函数
针对字符做处理
连接函数
CONCAT(‘字符1’,‘字符2’) 作用将字符1和字符2进行拼接拼接后的结果作字符返回。 返回字符。
SELECT CONCAT(ABC,def) from dual;
SELECT concat(ename,job) FROM emp;
SELECT concat(ename,sal) FROM emp; //sal是数值类型注意下面的解释与 || 的区别 1.CONCAT()里面的参数只有两个。如果连接的字符有三个及以上 ||可以直接拼而CONCAT()需要用到函数嵌套。 函数嵌套里层的函数返回的结果成为了外层函数的一部分 2.concat()是函数存在于多个数据库中但是 || 只能再Oracle数据库中使用。
Oracle数据库会将数值隐性转换成字符。通俗来讲在Oracle中写出来的数字既是数字也是字符。但是在代码中尽量少或者不要出现隐性转换因为会效率低。 大小写转换函数
大写转换
UPPER(字符) 作用将字符变成大写 返回字符
SELECT UPPER(asAADSsaers一二三ad) From dual;汉字没有大小之分如果放入大写字母或者汉字都不会变
小写转换
LOWER(字符) 作用将字符变成小写 返回字符
首字母大写其余的小写
INITCAP(字符) 作用将字符首字母大写其余变成小写。 返回字符
SELECT INITCAP(mkmkADWQdsaMKAdaS) FROM dual;注意如果转变的字符中存在空格或者其他符号被分开的第二段字母首字母同样会大写。 替换函数
REPLACE(目标字符,目标字符中要被替换的部分替换成的值)
SELECT REPLACE(asdwsc,sdw,哈哈哈哈);
SELECT REPLACE(ename,a,K),ename from emp;REPLACE()可以去空格
SELECT REPLACE(errr errw wad, ,哈),REPLACE(errr errw wad, ,) from dual;去除空格函数
Trim() 作用去除字符两侧的空格 返回字符
SELECT TRIM( abc def ) FROM dual;Ltrim():去除左侧空格 Rtrim():去除右侧空格
截取函数
Substr(目标字符截取位置参数n[长度参数l]) 作用从第n个位置截取l个长度。
SELECT ename,SUBSTR(ename,0,2) FROM EMP;SELECT ename,SUBSTR(ename,-2,2) FROM EMP;//位置可以是负数-2是指从倒数第二位开始截截2长度。长度不能为负数如果不写l参数默认截取到最后。 1.在这里面 01没区别 都是从头开始截取。 2.位置可以是负数长度必须的大于0 3.不管位置是不是负数截取都是从前往后截
练习题
1截取字符串’ABCDEF’中的前三个字符 2截取名字中的前两位字符 3从名字中的第二位字符开始截取三位长度
SELECT SUBSTR(ABCDEF,0,3) FROM dual;
SELECT ename,SUBSTR(ename,0,2) FROM emp;
SELECT ename,SUBSTR(ename,2,3) FROM emp;填充函数
lpad(目标字符,长度l,值) 左右向目标左侧填充值使其达到长度L 返回字符
SELECT LPAD(abc,10,*);rpad(目标字符,长度l,值) 左右向目标右侧填充值使其达到长度L 返回字符
SELECT LPAD(abc,10,*);获取字符长度函数
length(字符); 作用返回字符的长度 返回数值
SELECT LENGTH(sadasfasd) FROM dual;练习 1.找出姓名长度是五位 的员工信息 2.查询姓名长度是五位并且首字母是A 3.按照员工姓名长度分组计算每组的人数并且按照姓名长度排序。
SELECT * FROM emp WHERE LENGTH(ename)5;
SELECT * FROM emp WHERE LENGTH(ename)5 AND ename LIKEA%;
SELECT * FROM emp WHERE LENGTH(ename)5 AND SUBSTR(ename,1,1) A;.//第二题第二种写法。
SELECT COUNT(1),LENGTH(ename) FROM emp GROUP BY LENGTH(ename) ORDER BY LENGTH(ename);2
//WHERE后面不能用聚合函数其他函数可以用 获取字符位置函数
INSTR(目标字符,目标字符中要找的字节s,位置参数n,次数参数n1) 作用在目标字符中从第n个位置开始找找到第n1次出现的s返回s所在目标字符中的位置。 返回数值
SELECT INSTR(sjakdjkalsjhfasjdjwa,j,3,2) FROM dual;//从第三位开始找找到j第2次出现的位置找不到值的时候会返回0; 练习 查找姓名中至少有两个A的员工信息 查找姓名中正好有两个A的员工信息
SELECT * FROM emp WHERE INSTR(ename,A)!0;
SELECT * FROM emp WHERE INSTR(ename,A,1,2)!0 AND INSTR(ename,A,1,3)0;函数名作用ASCLL()返回字符的ASCII码CHR()返回ASCII码为的字符NLS_LOWER()把整个字符串转换成小写NLS_UPPER()把整个字符串转换成大写LENGTH()返回字符串的长度LENGTHB()返回字符串的长度全角算字符LTRIM()删除左边字符串RTRIM()删除右边字符串SOUNDEX()字符串语音表示形式TRANSLATE()替换子字符 数值型函数
绝对值函数
abs(数值) 作用返回数值的绝对值 返回数值
SELECT ABS(-516.5),ABS(546),ABS(0) FROM dual;向上取整向下取整
向上取整 ceil(整数) 作用返回离数值较近的较大的整数。 返回数值
SELECT ceil(1.1),ceil(1.9999),ceil(-1.1) FROM dual;向下取整 floor(数值) 作用返回离数值较近的较小的整数。 返回数值
SELECT floor(1.1),floor(1.9999),floor(-1.1) FROM dual;取余函数
mod(x,y) 作用返回x除以y得到的余数 返回数值 练习 查询姓名长度是奇数的员工信息
SELECT * from emp where MOD( LENGTH(ename),2)1;四舍五入函数
ROUND(数值,[精度p]) 作用将数值按照精度p进行四舍五入 返回数值 正数时保留到小数点后几位负数时保留到小数点前几位0是保留整数。位数不足时不会以0补全。 ROUND(123.45,-1)的结果是120 ROUND(123.45,0)的结果是123 不写精度默认保留整数 截取函数
trunc(数值,[截取精度]) 作用直接截取不四舍五入 判断正负函数
sign(数值) 作用判断数值的正负 返回数值
SELEC SIGN(123.45),sign(-123.45),sign(0) FROM dual;–
返回x的y次幂函数
power(x,y) 作用返回x的y次幂 返回数值 日期型函数
添加月函数
ADD_MONTHS(d1,n1) 作用返回在日期d1的基础上再加上n1个月后的新日期 返回日期
SELECT SYSDATE,add_months(sysdate,3) hz FROM dual;该月最后一天函数
last_day(d1) 作用返回日期d1所在月份最后一天的日期。 返回日期 间隔月函数
months_between(d1,d2) 作用返回两个日期之间的月数 返回数值
SELECT SYSDATE,MONTHS_BETWEEN(SYSDATE,TO_DATE(2008/2/3,yyyy/mm/dd)) FROM dual;如果d1d2,则返回正数 如果d2d1,则返回负数 NEW_TIME()
NEW_TIME(dt1,c1,c2) 【功能】给出时间dt1在c1时区对应c2时区的日期和时间 【参数】dt1d2 日期型
【返回】日期时间
【参数】c1,c2对应的 时区及其简写 大西洋标准时间AST或ADT 阿拉斯加_夏威夷时间HST或HDT 英国夏令时BST或BDT 美国山区时间MST或MDT 美国中央时区CST或CDT 新大陆标准时间NST 美国东部时间EST或EDT 太平洋标准时间PST或PDT 格林威治标准时间GMT Yukou标准时间YST或YDT NEXT_DAY()
NEXT_DAY(d1[,c1]) 【功能】返回日期d1在下周星期几(参数c1)的日期 【参数】d1日期型,c1为字符型(参数)c1默认为j即当前 日期) 【参数表】c1对应:星期一星期二星期三……星期日 【返回】日期
trunc()
trunc(d1[,c1]) 【功能】返回日期d1所在期间(参数c1)的第一天日期 【参数】d1日期型,c1为字符型(参数)c1默认为j即当前日期) 【参数表】c1对应的参数表: 最近0点日期: 取消参数c1或j 最近的星期日day或dy或d (每周顺序日一二三四五六 最近月初日期month或mon或mm或rm 最近季日期q 最近年初日期syear或year或yyyy或yyy或yy或y(多个y表示精度) 最近世纪初日期cc或scc 日期减法
日期减日期等于天数 练习 1.查询出入职日期是当月倒数第三天入职的员工信息
SELECT * FROM emp WHERE last_date(hiredate)-hiredate 2;聚合函数分组函数
AVG() SUM() MAX() MIN() COUNT() 等相信大家用的很熟练。
开窗函数分析函数窗口函数–重点
未更新
其他函数
空值替换函数
NVL(列名,值) 作用将该列空值替换成想要的值
SELECT ename,sal,NVL(comm,1) FROM emp;注意保证值和前面列的属性一致。 练习计算每个人的年薪
SELECT ename,sal,comm,(salNVL(comm,0) )*12 年薪 FROM emp;NVL2(列名值1值2) 作用当该列不为空的时候返回值1为空的时候返回值2.
SELECT ename,comm,NVL2(comm,2,4) FROM emp;去重
DISTINCT 作用将查询结果去重 DISTINCT 效率低 /*执行顺序 FROM WHERE GROUP BY HAVING SELECT DISTINCT *** ORDER BY */ (不是函数) 条件判断取值函数重点
DECODE(判断目标,目标返回的值1,重新定义值1,目标返回值2,重新定义值2.......,其他情况定义为)判断奖金当奖金时空值的时候则翻译成没有奖金。当奖金有值的时候翻译成有奖金。
SELECT ename,comm,DECODE(comm,NULL,没有奖金,有奖金) from emp;练习当工资为1000-1999的时候翻译成工资小于2000当工资是2000-2999 翻译成小于3000
SELECT sal,DECODE(floor(sal/1000),1,工资小于2000,2,工资小于3000,3,工资小于4000,工资大于4000);2. 显示员工姓名 姓名长度当姓名长度是奇数的时候返回’奇数‘否则返回’偶数‘
SELECT ename,Length(ename),DECODE(MOD(LENGTH(ename),2),1,奇数,2,偶数,啊) FROM emp;CASE WHEN
写法
CASE WHEN 条件1...... --当满足条件1时THEN 结果1 ----执行结果1WHEN 条件2......THEN 结果2WHEN 条件3......THEN 结果3.....[ELSE 其他结果] ---当不满足以上所有条件则要执行的结果
END ----结束练习当工资为1000-1999的时候翻译成工资小于2000当工资是2000-2999 翻译成小于3000 SELECT ename,sal,CASE WHEN sal1000 AND sal2000THEN 工资小于2000WHEN sal2000 AND sal3000THEN 工资小于3000WHEN sal3000 AND sal4000THEN 工资小于4000ELSE 土豪END AS 工资情况FROM emp;
练习给13季度员工涨薪10%给24季度员工降薪10%
SELECT ename,sal,CASE WHEN to_char(hiredate,q) IN (1,3)THEN sal*1.1WHEN to_char(hiredate,q) IN (2,4)THEN sal*0.9END AS 薪资变化
FROM emp; 函数练习题
--查询当前日期
SELECT SYSDATE FROM DUAL;
--显示员工工资加上$
SELECT SAL, CONCAT(SAL, s) FROM EMP;
--将字符串1980-12-17转成日期
SELECT TO_DATE(1980-12-17, yyyy-mm-dd) FROM DUAL;
--使用case表达式职位是分析员的工资1000职位是经理的工资800职位是其它的工资400
SELECT SAL,ENAME,JOB,CASEWHEN JOB ANALYSE THENSAL 1000WHEN JOB MANAGER THENSAL 800ELSESAL 400END AS 薪资变化FROM EMP;
--使用decode函数职位是分析员的工资1000职位是经理的工资800职位是其它的工资400【oracle专用】
SELECT SAL,JOB,DECODE(JOB, ANALYSE, SAL 1000, MANAGER, SAL 800, SAL 400) 薪资变化FROM EMP;
---从今天开始算下下一个星期三是多少号(next_day)
SELECT NEXT_DAY(NEXT_DAY(SYSDATE, 星期三), 星期三) FROM DUAL;
--员工总工资平均工资四舍五入保留小数点后2位
SELECT ROUND(SUM(SAL), 2), ROUND(AVG(SAL), 2) FROM EMP;
--求入职最早员工和入职最晚员工的入职日期
SELECT MIN(TO_CHAR(HIREDATE, yyyy)) 最早,MAX(TO_CHAR(HIREDATE, yyyy)) 最晚FROM EMP;
--统计有佣金的员工人数
SELECT COUNT(1), DECODE(COMM, NULL, 无奖金, 有奖金) 有无佣金FROM EMPGROUP BY DECODE(COMM, NULL, 无奖金, 有奖金);
--按部门求出部门平均工资且平均工资取整数
SELECT ROUND(AVG(SAL), 0), AVG(SAL), DEPTNO FROM EMP GROUP BY DEPTNO;
--除30号部门外查询部门平均工资大于1500元的部门
SELECT DEPTNOFROM EMPWHERE DEPTNO 30GROUP BY DEPTNO
HAVING AVG(SAL) 1500;
--显示部门平均工资的最大值
SELECT MAX(AVG(SAL)) FROM EMP GROUP BY DEPTNO;
