------------------------------ create table t_user3( id number, username char(20), password varchar2(20), stuno varchar2(20) ); insert into t_user3 (id, useramne, password, stuno) values (1, 'user1001 ', '123456', '110'); ------------------------------ 字符类型分3种:char(n) varchar(n) varchar2(n); char(n)固定长度字符串,假如长度不足n,右边空格补齐; varchar(n)可变长度字符串,假如长度不足n,右边不会补齐; varchar2(n)可变长度字符串,Oracle官方推荐使用,向后兼容性好; char(n) VS varchar2(n) char(n)查询效率相对较高,varchar2(n)存储空间相对较小; SQL> select length(userName) as charlength,length(password) as varchar2length from t_user3; lpad() 向左补全字符串 SQL> select lpad(stuno,6,'0') from t_user3; rpad() 向右补全字符串 SQL> select rpad(stuno,6,'0') from t_user3; lower() 返回字符串小写 SQL> select lower(userName) from t_user3; upper() 返回字符串大写 SQL> select upper(userName) from t_user3; initcap() 单词首字符大写 SQL> select initcap(userName) from t_user3; length() 返回字符串长度 SQL> select length(password) from t_user3; substr() 截取字符串 SQL> select substr(userName,1,2) from t_user3; instr() 获取字符串出现的位置 SQL> select instr(password,'23') from t_user3; SQL> select instr(password,'23',2) from t_user3; SQL> select instr(password,'23',2,1) from t_user3; ltrim() 删除左侧空格 SQL> select ltrim(userName) from t_user3; rtrim() 删除右侧空格 SQL> select rtrim(userName) from t_user3; trim() 删除两侧空格 SQL> select trim(userName) from t_user3; concat() 串联字符串 SQL> select concat(userName,password) from t_user3; reverse() 反转字符串 SQL> select reverse(userName) from t_user3;
2. 数值类型及函数
------------------------------ create table t_number( id number, n1 number(7,3), n2 number(5,-3) ); insert into t_number(id,n1,n2) values (1, -123.456, 0); insert into t_number(id,n1,n2) values (2, 23.4564, 0); insert into t_number(id,n1,n2) values (3, 23.4564, 123456); insert into t_number(id,n1,n2) values (4, 23.4564, 123789); insert into t_number(id,n1,n2) values (5, 23.4567, 123789); insert into t_number(id,n1,n2) values (6, 23.4564, 123789); ------------------------------ number是oracle中的数据类型: number(precision,scale); precision,scale均可选; precision代表精度,sacle代表小数位的位数; precision 范围[1,38] scale 范围[-84,127]; 举例:12345.678中precision是8;scale是3; abs() 求绝对值 SQL> select abs(n1) from t_number; round() 四舍五入 SQL> select round(n1,2) from t_number where id=1; ceil() 向上取整 SQL> select ceil(n1) from t_number where id=2; floor 向下取整 SQL> select floor(n1) from t_number where id=2; mod()取模 SQL> select mod(5,3) from dual; sign()正负性 SQL> select sign(n1) from t_number where id=1; sqrt()求平方根 SQL> select sqrt(9) from dual; power()求乘方 SQL> select power(2,3) from dual; trunc()截取 SQL> select trunc(123.456,2) from dual; to_char() 格式化数值 常见的字符匹配有 0、9、,、$、FM、L、C SQL> select to_char(123.45,'0000.000') from dual; SQL> select to_char(123.45,'9999.999') from dual; SQL> select to_char(123123,'99,999,999.99') from dual; SQL> select to_char(123123.3,'FM99,999,999.99') from dual; SQL> select to_char(123123.3,'$99,999,999.99') from dual; SQL> select to_char(123123.3,'L99,999,999.99') from dual; SQL> select to_char(123123.3,'99,999,999.99C') from dual;
3. 日期类型及函数
------------------------------ create table t_date( id number, d1 date, d2 timestamp(6) ); insert into t_date values(1,sysdate,systimestamp); alter table t_date add(d3 date);??? update t_date set d3=to_date('2016-12-20','YYYY-MM-DD') where id=1; update t_date set d3=to_date('2016-12-20 18:31:34','YYYY-MM-DD HH24:MI:SS') where id=1; ------------------------------ date和timestamp(时间戳) date包含信息century(世纪信息) year年 month月 day日 hour小时 minute分钟 second秒 timestamp一般用于日期时间要求非常精确的情况,精确到毫秒级; date类型的常用函数: SQL> select sysdate from dual; SQL> select systimestamp from dual; add_months添加月份 SQL> select add_months(d1,2) from t_date where id=1; last_day返回指定日期月份的最后一天 SQL> select last_day(d1) from t_date where id=1; months_between返回两个日期的相差月数 SQL> select months_between(d1,d3) from t_date where id=1; next_day返回特定日期之后的一周内的日期 SQL> select next_day(d1,2) from t_date where id=1; trunc截取日期 SQL> select trunc(d1,'YYYY') from t_date where id=1; SQL> select trunc(d1,'MM') from t_date where id=1; SQL> select trunc(d1,'DD') from t_date where id=1; SQL> select trunc(d1,'HH') from t_date where id=1; SQL> select trunc(d1,'MI') from t_date where id=1; extract返回日期的某个域 SQL> select extract(year from sysdate) from dual; SQL> select extract(month from sysdate) from dual; SQL> select extract(day from sysdate) from dual; SQL> select extract(hour from systimestamp) from dual; SQL> select extract(minute from systimestamp) from dual; SQL> select extract(second from systimestamp) from dual; to_char将日期转换成字符串 SQL> select to_char(d1,'YYYY-MM-DD') from t_date where id=1; SQL> select to_char(d1,'YYYY-MM-DD HH24:MI:SS') from t_date where id=1;
4. 其他常用处理函数
max求最大值 SQL> select max(sal) from emp; min求最小值 SQL> select min(sal) from emp; avg求平均值 SQL> select avg(sal) from emp; sum求和 SQL> select sum(sal) from emp; count统计记录数 SQL> select count(ename) from emp; nvl空值处理 SQL> select ename,nvl(sal,0) from emp; rownum行号 SQL> select e.*, rownum from emp e; oracle分页 SQL> select * from (select a.*,rownum rn from (select * from emp) A where rownum<=10) where rn>5; oracle中的运算 SQL> select 2+1 from dual; SQL> select 2-1 from dual; SQL> select 2*1 from dual; SQL> select 2/1 from dual; 条件判断式 between and范围查询 SQL> select * from emp where sal between 900 and 1500; SQL> select * from emp where sal>=900 and sal<=1500; in集合范围 SQL> select ename,hiredate from emp where ename in (select distinct ename from bonus); like模糊查询 SQL> select * from emp where ename like '%M%'; SQL> select * from emp where ename like 'M%'; SQL> select * from emp where ename like '_M%';
评论