select table_name from all_tables where owner =‘dog‘create user yyy identified by 123--授权grant connect,resource to yyycreate 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>=5SELECT * 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

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

oracle函数