1.连接操作符
select ‘姓名为‘|| ename||‘工作为‘||‘job‘||‘工资为‘|| sal as info from emp
2.将字符串转为小写
select lower（ename）as name from emp
3.逐值替换
select decode(deptno,‘10‘,‘开发部‘,‘20‘,‘产品部‘,‘30‘,‘维护部‘)from emp
4.当前系统日期的年份
select extract(year from sysdate) from dual
5.查询每个员工的工龄
select extract(year from sysdate) - extract(year from hiredate) as age from emp
6.转换函数
select to_char(0.123,‘$0.9999‘)from dual
7.将日期对象转成字符串
SELECT TO_CHAR(sysdate,‘YYYY"年"fmMM"月"fmDD"日" HH24:MI:SS‘)FROM dual;
8.查询员工工资和（工资+奖金）
select ename,sal+nvl(comm,0) from emp select ename,sal+nvl2(comm,comm,0) from emp
9.如果二个值不一样则结果为第一个值，如果两个值一样则结果为null
select nullif(200,200) from dual
10.分析函数（看后面num‘值）
-- row_number()连续排位
select emp.*, row_number() over(order by sal desc ) as num from emp
--rank
select emp.*, rank() over(order by sal desc ) as num from emp
-- dense rank
11.创建一个用户
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;
12.创建同义词
CREATE SYNONYM e FOR SCOTT.emp; select * from e
13.创建公有同义词
CREATE PUBLIC SYNONYM pub_emp FOR SCOTT.emp; select * from pub_emp
14.创建序列
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
15.授权
grant create view to scott
16.创建视图
create view dept_emp as select dept.deptno,dname,loc,empno,ename,job,mgr,hiredate from emp join dept on emp.deptno=dept.deptno
欢迎各位大神指点和评论；