字符串函数
1. CONCAT(char1,char2)
连接字符串
SELECT CONCAT(ename,sal) FROM emp SELECT CONCAT(CONCAT(ename,':'),sal) FROM em |
使用"||"可以方便的连接多个字符串
SELECT ename||':'||sal FROM emp SELECT ename|| '的工资是' || sal || '所在部门' || deptno FROM emp |
2. LENGTH(char)
获取指定字符串的长度
查看每个员工名字的字符个数?
SELECT ename,LENGTH(ename) FROM emp |
3. LOWER,UPPER,INITCAP
将字符串转换为全小写,全大写,首字母大写
dual:伪表
当查询的数据与任何表没有关系时,可以查询伪表,这样只会查询出一条记录.
SELECT UPPER('helloworld'), LOWER('HELLOWORLD'), INITCAP('hello world') - - INITCAP为Oracle特有语法,Mysql中不可用 FROM dual |
SELECT ename,sal,deptno FROM emp WHERE UPPER(ename)=UPPER(‘scott’) |
4. TRIM,LTRIM,RTRIM
去除字符串两端的指定内容
SELECT TRIM('e' FROM 'eeeliteee') FROM dual |
--LTRIM,RTRIM为Oracle特有语法,Mysql中不可用
SELECT TRIM('esfffefsfefsfliteseses','efs') FROM dual |
5. LPAD(char1,n,char2),RPAD
补位函数,将char1显示n位,若char1不足n位时左(右)补充若干个char2字符已达到位数
SELECT ename,sal,RPAD(sal,5,'$') FROM emp |
6. SUBSTR(char,m[,n])
截取char字符串,从m处开始,连续截取n个字符.需要注意,数据库中下标都是从1开始的!!n若不指定或指定的数字超过可以截取的实际字符长度时都是截取到当前字符串末尾.若m为负数,则是从倒数位置开始截取.
SELECT SUBSTR('thinking in java',-7,2) FROM dual |
7. INSTR(char1, char2[,n[,m]]) --为Oracle特有语法,Mysql中不可用
查看char2在char1中的位置
n和m不指定默认都是1
n表示从第几个字符开始查找
m表示查看第几次出现
SELECT INSTR('thinking in java','in',4,2) FROM dual |
数字函数
1. ROUND(n[,m])
保留n小数点后m位.
m不写默认为0,即:保留到整数位
m若为负数,则是保留10位以上的数字
SELECT ROUND(45.678, 2) FROM dual SELECT ROUND(45.678, 0) FROM dual SELECT ROUND(55.678, -2) FROM dual |
2. TRUNC(n[,m])
截取数字,参数意义与ROUND一致
--为Oracle特有语法,Mysql中不可用
SELECT TRUNC(45.678, 2) FROM dual SELECT TRUNC(45.678, 0) FROM dual SELECT TRUNC(55.678, -1) FROM dual |
3. MOD(m,n)
m除以n求余数,n若为0则直接返回m
SELECT ename, sal, MOD(sal, 1000) FROM emp |
4.CEIL,FLOOR
向上取整与向下取整
SELECT CEIL(45.678) FROM DUAL SELECT FLOOR(45.678) FROM DUAL |
日期类型相关函数:
日期相关关键字:
1:SYSDATE:Oracle中返回一个表示当前系统时间的DATE值
2:SYSTIMESTAMP:Oracle中返回当前时间的时间戳类型值
3.NOW():Mysql中返回一个表示当前系统时间的DATE值
4.current_timestamp():Mysql中返回当前时间的时间戳类型值
SELECT SYSDATE FROM dual SELECT SYSTIMESTAMP FROM dual |
日期转换函数(Oracle篇)
1. TO_DATE()
将给定字符串按照给定的日期格式解析为一个DATE值
SELECT TO_DATE('1998-05-02 21:15:22', 'YYYY-MM-DD HH24:MI:SS') FROM dual |
日期格式字符串中不是字母或符号的其他字符都需要使用双引号括起来.
SELECT TO_DATE('1998年05月02日 21时15分22秒', 'YYYY"年"MM"月"DD"日" HH24"时"MI"分"SS"秒"') FROM dual |
2. TO_CHAR函数
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') FROM dual |
RR与YY都是用两位数字表示年,但是当使用TO_DATE函数将两位数字解析为实际日期时,RR会自行判定世纪,而YY不会.
SELECT TO_CHAR( TO_DATE('61-08-02','RR-MM-DD'), 'YYYY-MM-DD' ) FROM dual |
日期可以进行计算
1:对一个日期加减一个数字,等同于计算加减天数
2:两个日期相减,差为相差的天数
日期之间比大小,越晚的越大.
查看100天以后是哪天?
SELECT SYSDATE+100 FROM dual |
查看每个员工活到今天为止共多少天?
SELECT ename, SYSDATE-hiredate FROM emp |
日期函数:
1. LAST_DAY(date)
返回给定日期所在月的月底日期
查看当月月底?
SELECT LAST_DAY(SYSDATE) FROM dual |
2. ADD_MONTHS(date,i)
对指定日期加上指定月,若i为负数则是减去
查看每个员工的转正日期?
SELECT ename,ADD_MONTHS(hiredate,3) FROM emp |
3. MONTHS_BETWEEN(date1,date2)
计算两个日期之间相差多少个月,计算方式使用date1-date2得到的
查看每个员工入职到今天为止共多少个月?
SELECT ename,MONTHS_BETWEEN(SYSDATE,hiredate) FROM emp |
4. NEXT_DAY(date,i)
返回给定日期第二天开始算一周之内指定周几对应的日期
SELECT NEXT_DAY(SYSDATE,7) FROM dual |
5. LEAST、GREATEST
最小值与最大值,凡是可以比较大小的数据类型都可以使用这两个函数对于日期当中,最大值即最晚的日期,最小值即最早的日期
SELECT LEAST(SYSDATE, TO_DATE('2008-08-08', 'YYYY-MM-DD') ) FROM dual |
6. EXTRACT
提取一个日期中指定时间分量的值
SELECT EXTRACT(YEAR FROM SYSDATE) FROM dual |
日期转换函数(Mysql篇)
1. str_to_date(str, format)
可以把一些杂乱无章的字符串转换为日期格式。另外，它也可以转换为时间。“format” 可以参看 MySQL 手册。
SELECT str_to_date('08/09/2008', '%m/%d/%Y'); -- 2008-08-09 |
2. date_format(date,format)
date_format(date,format), time_format(time,format) 能够把一个日期/时间转换成各种各样的字符串格式。它是 str_to_date(str,format) 函数的 一个逆转换。
SELECT date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s'); -- 20080808222301
|
3. makdedate(year,dayofyear)
拼凑日期：
select makedate(2001,31); -- '2001-01-31' select makedate(2001,32); -- '2001-02-01' |
4. maketime(hour,minute,second)
时间函数：
Select maketime(12,15,30); -- '12:15:30' |
5. MySQL （Unix 时间戳、日期）转换函数
unix_timestamp(), unix_timestamp(date), from_unixtime(unix_timestamp), from_unixtime(unix_timestamp,format) |
下面是示例：
select unix_timestamp(); -- 1218290027 select unix_timestamp('2008-08-08'); -- 1218124800 select unix_timestamp('2008-08-08 12:30:00'); -- 1218169800 select from_unixtime(1218290027); -- '2008-08-09 21:53:47' select from_unixtime(1218124800); -- '2008-08-08 00:00:00' Select from_unixtime(1218169800); -- '2008-08-08 12:30:00' select from_unixtime(1218169800, '%Y %D %M %h:%i:%s %x'); -- '2008 8th August 12:30:00 2008' |
6. datediff(date1,date2), timediff(time1,time2)
MySQL 日期、时间相减函数：
select datediff('2008-08-08', '2008-08-01'); -- 7 select datediff('2008-08-01', '2008-08-08'); -- -7
select timediff('2008-08-08 08:08:08', '2008-08-08 00:00:00'); -- 08:08:08 select timediff('08:08:08', '00:00:00'); -- 08:08:08 |
空值操作
1. 插入NULL值
CREATE TABLE student( id NUMBER(4), name CHAR(20), gender CHAR(1) );
INSERT INTO student VALUES(1000, '李莫愁', 'F'); INSERT INTO student VALUES(1001, '林平之', NULL); INSERT INTO student(id, name) VALUES(1002, '张无忌');
|
2. 更新NULL值
UPDATE student SET gender=NULL WHERE id=1000 |
作为条件判断NULL值:
判断要使用IS NULL和IS NOT NULL,不能使用"="判断NULL.
DELETE FROM student WHERE gender IS NOT NULL |
3. NULL的运算
NULL与字符串连接等于什么也没干
NULL与数字运算,结果还是NULL
4. 空值函数
1:NVL(a1,a2)
当a1为NULL时,函数返回a2,否则返回a1自身,所以函数作用是将NULL值替换为指定值.
SELECT ename,sal,comm,sal+NVL(comm,0) FROM emp |
2:NVL2(a1,a2,a3)
当a1不为NULL时,函数返回a2
当a1为NULL时,函数返回a3
SELECT ename,comm,NVL2(comm,'有绩效','没有绩效') FROM emp |
