如下面语句，我们查询后能够看到只有列D,其内容为X：
例如: 显示1+1的结果,可以看出,dual很多时候是为了构成select的标准语法 select 1+1 from dual;
结果为2
1、字符函数
LOWER Converts to lowercase UPPER Converts to uppercase INITCAP Converts to initial capitalization CONCAT Concatenates values SUBSTR Returns substring LENGTH Returns number of characters,length():字符数,lengthb():字节数 NVL Converts a null value INSTR():在母串中找到子串的位置 LPAD|RPAD 左右填充 TRIM():去掉前后指定的字符 REPLACE()替换
lower 把字符转为小写 例如:把'HELLO'转换为小写 select lower('HELLO') from dual; 例如:把s_emp表中的last_name列的值转换为小写 select lower(last_name) from s_emp; upper 把字符转换为大写 例如:把'world'转换为大写 select upper('world') from dual; 例如:把s_emp表中的last_name列的值转换为大写 select upper(last_name) from s_emp; 例如:查询s_emp表中名字为Ngao的人信息 这样是查不到: select last_name,salary,dept_id from s_emp where last_name='NGAO'; 这样就可以查询到了，因为表中没有这样的数据NGAO，但是表中有Ngao字符，则可以将表中内容转换为大写再查询: select last_name,salary,dept_id from s_emp where upper(last_name)='NGAO'; initcap 把字符串首字母转换为大写 例如:把'hELLO'转换为首字母大写,其余字母小写 select initcap('hELLO') from dual; concat 把俩个字符串连接在一起(类似之前的||的作用) 例如:把'hello'和'world'俩个字符串连接到一起,并且起个别名为msg select concat('hello','world') msg from dual; 例如:把first_name和last_name俩个列的值连接到一起 select concat(first_name,last_name) as name from s_emp; substr 截取字符串 例如:截取'hello'字符串,从第2个字符开始(包含第二个字符),截取后面连续的3个字符 select substr('hello',2,3) from dual; length 获得字符串长度 例如:获得'world'字符串的长度 select length('world') from dual; 例如:获得s_emp表中last_name列的每个值的字符长度 select length(last_name) from s_emp; nvl 替换列中为null的值 在前面的章节已经使用过了 select last_name,nvl(commission_pct,0) from s_emp;INSTR():在母串中找到子串的位置
SQL> select instr('hello','llo') from dual; INSTR('HELLO','LLO') -------------------- 3LPAD|RPAD 左右填充
select lpad('123410',20,'*') from dual SQL> / LPAD('123410',20,'*') ------------------------------------------------------------ **************123410其中20表示字符的总个数，*为要填充的字符，lpad表示左填充TRIM():去掉前后指定的字符
SQL> select trim('H' from 'Hello world') from dual; TRIM('H'FROM'HELLOWORLD') ------------------------------ ello world REPLACE()替换 SQL>select replace('Hello world','l','*') from dual; REPLACE('HELLOWORLD','L','*') --------------------------------- He**o wor*d2、数字函数
ROUND Rounds value to specified decimal TRUNC Truncates value to specified decimal MOD Returns remainder of division round 四舍五入 round(arg1,arg2) 第一个参数表示要进行四舍五入操作的数字 第二个参数表示保留到哪一位 例如: 保留到小数点后面2位 select round(45.923,2) from dual; 保留到个位 (个十百千万...) select round(45.923,0) from dual; 保留到十位 (个十百千万...) select round(45.923,-1) from dual;如下所示演示：
SQL> select round(55.925,-2) from dual; 结果为： ROUND(55.925,-2) ---------------- 100 SQL> SQL> select round(45.926,-2) from dual; 结果为： ROUND(45.926,-2) ---------------- 0SQL>
trunc 截取到某一位
trunc(arg1,arg2) 和round的用法一样,但是trunc只舍去不进位 例如: 截取到小数点后面2位 select trunc(45.929,2) from dual; 截取到个位 (个十百千万...) select trunc(45.923,0) from dual; 截取到十位 (个十百千万...) select trunc(45.923,-1) from dual; mod 取余 mod(arg1,arg2) 第一个参数表示要进行取余操作的数字 第二个参数表示参数1和谁取余 例如: 把10和3进行取余 (10除以3然后获取余数) select mod(10,3) from dual;3、日期函数
MONTHS_BETWEEN Number of months between two dates ADD_MONTHS Add calendar months to date NEXT_DAY Next day of the date specified LAST_DAY Last day of the month ROUND Round to date at midnight TRUNC Remove time portion from date sysdate关键字 表示系统的当前时间 例如: 显示时间:当前时间 select sysdate from dual; 注意:sysdate进行加减操作的时候,单位是天 例如: 显示时间:明天的这个时候 select sysdate+1 from dual; 例如: 显示时间:昨天的这个时候 select sysdate-1 from dual; 例如: 显示时间:1小时之后的这个日期 select sysdate+1/24 from dual; months_between 俩个日期之间相差多少个月(单位是月) 例如: 30天之后和现在相差多少个月 select months_between(sysdate+30,sysdate) from dual; add_months 返回一个日期数据:表示一个时间点,往后推x月的日期 例如: '01-2月-2016'往后推2个月 select add_months('01-2月-2016',2) from dual; 例如: 当前时间往后推4个月 select add_months(sysdate,4) from dual; 注意:这个数字也可以是负数,表示往前推x月 next_day 返回一个日期数据:表示一个时间点后的下一个星期几在哪一天 例如: 离当前时间最近的下一个星期5是哪一个天 select next_day(sysdate,'星期五') from dual; 注意: 如果要使用'FRIDAY',那么需要把当前会话的语言环境修改为英文 last_day 返回一个日期数据:表示一个日期所在月份的最后一天 例如: 当前日期所在月份的最后一天(月底) select last_day(sysdate) from dual; round 对日期进四舍五入,返回操作后的日期数据 例如: 把当前日期四舍五入到月 select round(sysdate,'MONTH') from dual;测试:15号16号分别是舍弃还是进位，测试时我们首先测试当天日期，然后在测试16号的日期，在测试15号的日期，测试过程如下所示：
select sysdate from dual;//获取当前日期 select round(sysdate,'month') from dual;//由于当天日期为16号，故使用当前日期进行测试 select round(sysdate-1,'month') from dual;//由于当前日期为16号，故将其减一，使其为15号，然后输出测试结果测试结果如下所示：
把当前日期四舍五入到年 select round(sysdate,'YEAR') from dual; //下面这个写法是错误的 ，数字函数也有一个round，两个ronnd函数有冲突，所以这里不能使用默认的日期格式 select round('01-2月-2016','MONTH') from dual;
我们可以使用一个转化函数进行转化，但是我们也可以使用add_months函数进行不专业的转换：
select round(add_months('01-2月-2016',1),'MONTH') from dual; trunc 对日期进行截取 和round类似,但是只舍弃不进位在此处就不一一列出，唯一的不同就是只舍不进位
4、类型转换函数 TO_CHAR converts a number or date string to a character string. TO_NUMBER converts a character string containing digits to a number. TO_DATE converts a character string of a date to a date value. to_char 把日期转换为字符 例如: 把当前日期按照指定格式转换为字符串
select to_char(sysdate,'yyyy') from dual;
日期格式： yyyy：四位数的年份 rrrr：四位数的年份 yy：两位数的年份 rr：两位数的年份 mm：两位数的月份（数字） D：一周的星期几 DD：一月的第几天 DDD ：一年的第几天 YEAR：英文的年份 MONTH：英文全称的月份 mon：英文简写的月份 ddsp：英文的第几天(一个月的) ddspth：英文序列数的第几天(一个月的) DAY：全英文的星期 DY：简写的英文星期 hh：小时 mi：分钟 ss：秒
如下图所示：
例如: 测试常见的一些日期数据转换为字符串的格式
select to_char(sysdate,'yyyy MM D DD DDD YEAR MONTH ddsp ddspth DAY DY') from dual;select to_char(sysdate,'dd-mm-yy') from dual;
select to_char(sysdate,'yyyy-mm-dd') from dual;
select to_char(sysdate,'dd-mm-yy HH24:MI:SS AM') from dual;
to_char 把数字转换为字符 例如: 其中9代表按实际出现，在这个位数上有的话就出现，没有数字的话就不用出现，而对于0，有的话就用指明的数字，没有的话就用0替代出现
select to_char(salary,'$999,999.00') from s_emp;fm表示去除结果显示中的开始的空格
select to_char(salary,'fm$999,999.00') from s_emp; L表示系统本地的货币符号 select to_char(salary,'fmL999,999.00') from s_emp;例如，我们在转换时还能够加入自己想要加入的字符串，中间添加自定义修饰语“今天是”
select to_char(sysdate,'yyyy-month-dd hh24:mi:ss "今天是" day') from dual;结果如下所示：TO_CHAR(SYSDATE,'YYYY-MONTH-DDHH24:MI:SS"今天是"DAY') -------------------------------------------------------------------------------- 2016-10月-04 12:25:14 今天是 星期二 to_number 把字符转换为数字
例如: select to_number('1000') from dual;//这个写法是错的 abc不能转换为数字 select to_number('abc') from dual; to_date 把字符转换为日期 例如:
select to_date('10-12-2016','dd-mm-yyyy') from dual;select to_date('25-5月-95','dd-month-yy') from dual; //中文环境下的设置，在这里就不演示了 select to_char(to_date('25-may-12','dd-month-rr'),'yyyy-mm-dd') from dual; //这个问题涉及千年虫问题，再下面的博客中会有专门讲解
select to_date('95/may/25','yy/month/dd') from dual;
下面的语句是错误的，不能够正确匹配
select to_date(1000,'yy/month/dd') from dual; 函数之间的嵌套 格式:F3(F2(F1(arg0,arg1),arg2),arg3) 例如: 先把'hello'和'world'连接起来,再转换为字母大写然后再从第4个字符开始,连着截取4个字符
select substr(upper(concat('hello','world')),4,4) from dual;结果如下图所示：
特殊SQL语句： 假如我们在公司，有时会遇到加薪问题，加薪时可能会有区别，比如经理加1000，普通人员加400，等问题，那么我们该怎么解决？？这时我们就需要用到case...when...then..语句
select last_name,title,salary "涨前的工资",case title when 'Stock Clerk' then salary+1000 when 'manager' then salary+2000 when 'President' then salary+5000 else salary+300 end "涨后的薪水" from s_emp; 当然我们也可以使用decode函数：： select last_name,title,salary 涨前工资,decode( title,'Stock Clerk',salary+1000, 'manager',salary+2000, 'President',salary+5000, salary+400) 涨后的工资 from s_emp;