开发笔记:oracle数据库查询2

select \'姓名为\'|| ename||\'工作为\'||\'job\'||\'工资为\'|| sal as info from emp

select lower（ename）as name from emp

select decode(deptno,\'10\',\'开发部\',\'20\',\'产品部\',\'30\',\'维护部\')from emp

select extract(year from sysdate) from dual

select extract(year from sysdate) - extract(year from hiredate) as age from emp

select to_char(0.123,\'$0.9999\')from dual

SELECT TO_CHAR(sysdate,\'YYYY"年"fmMM"月"fmDD"日" HH24:MI:SS\')FROM dual;

select ename,sal+nvl(comm,0) from emp
select ename,sal+nvl2(comm,comm,0) from emp

select nullif(200,200) from dual

select emp.*, row_number() over(order by sal desc ) as num from emp

select emp.*, rank() over(order by sal desc ) as num from emp

CREATE USER test IDENTIFIED BY test;
GRANT CONNECT , CREATE SYNONYM TO test;
GRANT SELECT ON SCOTT.EMP TO test;
GRANT DELETE ON SCOTT.EMP TO test;
GRANT UPDATE ON SCOTT.EMP TO test;

CREATE SYNONYM e FOR SCOTT.emp;
select * from e

CREATE PUBLIC SYNONYM pub_emp FOR SCOTT.emp;
select * from pub_emp

CREATE sequence mysql
start with 1
increment by 1
create table student(
sid int primary key,
sname varchar(20)
)
insert into student values(mysql.nextval,\'张三\')
select mysql.currval from dual
select * from student

grant create view to scott

create view dept_emp
as
select dept.deptno,dname,loc,empno,ename,job,mgr,hiredate from emp join dept on emp.deptno=dept.deptno