oracle函数
select table_name from all_tables where owner ='dog' create user yyy identified by 123 --授权 grant connect,resource to yyy create table emple ( empId number primary key, empName nvarchar2(32) ) grant connect,resource to scott --解er s锁 alter user scott account unlock; //查询news表 --获取5到9的数据 select * from ( select temp.*,rownum rn from ( select emp.* from emp order by sal desc ) temp where rownum<=9 ) where rn>=5 SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (SELECT * FROM emp) A ) WHERE RN BETWEEN 21 AND 40 --大小写控制函数 --转换大写 select upper('scott') from dual; --转换小写 select lower('Aa') from dual; --首字母大写 select initcap('aa') from dual; --字符控制函数 --拼接 select concat('happy','boy') from dual; --截取 select substr('happy',1) from dual; --字符字数统计 select length('abbbb') from dual; --字节字数统计 select lengthb('ab') from dual; --从第三个位置找or第二次出现的位置 select instr('corporate floor','or',3,2) "Instring" from dual; --从倒数第三个位置开始找or第二次出现的位置 select instr('corporate floor','or',-3,2) "Instring" from dual; select instrb('corporate floor','or',5,2) "Instring in bytes" from dual; --lpad()和rpad() select lpad('happy',10,'*') from dual; --trim()删除两边的字符串,该字符串可以指定 select trim('t' from 'techt') from dual; select trim(trailing '0' from '00100') from dual; --日期函数 --获取两日期之间的 select months_between (to_date('02-02-1995','mm-dd-yyyy'),to_date('01-02-1995','mm-dd-yyyy')) "Day" from dual; --两个日期之间的天数 select floor(sysdate-to_date('20040202','yyyymmdd')) from dual; --两个日期之间的月份 select months_between(sysdate,to_date('20140204','yyyymmdd')) from dual;、 --转换函数(隐式转化和显式转化) --查询emp表中的 入职日期d-m-y select * from emp where hiredate='20-2月-81'; --转化成字符(sysdate 指的是当前时间) select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; --将emp表中的所有sal工资,转换成某种形式,第二个参数为形式 select to_char(sal,'99,999.99') from emp; select sal from emp; --round(第二个参数为小数点后的第几位,第一位下标为1)四舍五入 select round(12.46,1) from dual; --通用函数 --滤空函数 select sal*12工资,comm 奖金,sal*12+nvl(comm,0) 哈哈 from emp; select sal*12工资,comm 奖金,sal*12+nvl2(comm,comm,0)as 哈哈 from emp; --decode函数 select product_id,decode(warehouse_id,1,'Southlake', 2,'San Francisco', 3,'New Jersey', 4,'Seattle', 'Non domestic') "location" from emp
select table_name from all_tables where owner ='dog'create user yyy identified by 123--授权grant connect,resource to yyy
create table emple( empId number primary key, empName nvarchar2(32))
grant connect,resource to scott
--解er s锁alter user scott account unlock;
//查询news表--获取5到9的数据select * from( select temp.*,rownum rn from ( select emp.* from emp order by sal desc ) tempwhere rownum<=9)where rn>=5
SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM emp) A )WHERE RN BETWEEN 21 AND 40--大小写控制函数--转换大写select upper('scott') from dual;--转换小写select lower('Aa') from dual;--首字母大写select initcap('aa') from dual;--字符控制函数--拼接select concat('happy','boy') from dual;--截取select substr('happy',1) from dual;--字符字数统计select length('abbbb') from dual;--字节字数统计select lengthb('ab') from dual;--从第三个位置找or第二次出现的位置select instr('corporate floor','or',3,2) "Instring" from dual;--从倒数第三个位置开始找or第二次出现的位置select instr('corporate floor','or',-3,2) "Instring" from dual;select instrb('corporate floor','or',5,2) "Instring in bytes" from dual;--lpad()和rpad() select lpad('happy',10,'*') from dual;--trim()删除两边的字符串,该字符串可以指定select trim('t' from 'techt') from dual;select trim(trailing '0' from '00100') from dual;
--日期函数--获取两日期之间的select months_between (to_date('02-02-1995','mm-dd-yyyy'),to_date('01-02-1995','mm-dd-yyyy')) "Day"from dual;
--两个日期之间的天数select floor(sysdate-to_date('20040202','yyyymmdd')) from dual;--两个日期之间的月份select months_between(sysdate,to_date('20140204','yyyymmdd')) from dual;、--转换函数(隐式转化和显式转化)
--查询emp表中的 入职日期d-m-yselect * from emp where hiredate='20-2月-81';--转化成字符(sysdate 指的是当前时间)select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;--将emp表中的所有sal工资,转换成某种形式,第二个参数为形式select to_char(sal,'99,999.99') from emp;select sal from emp;--round(第二个参数为小数点后的第几位,第一位下标为1)四舍五入select round(12.46,1) from dual;--通用函数--滤空函数
select sal*12工资,comm 奖金,sal*12+nvl(comm,0) 哈哈 from emp;
select sal*12工资,comm 奖金,sal*12+nvl2(comm,comm,0)as 哈哈 from emp;
--decode函数select product_id,decode(warehouse_id,1,'Southlake',2,'San Francisco',3,'New Jersey',4,'Seattle','Non domestic') "location"from emp