select concat('Hello','World'),substr('HelloWorld',1,5), length('HelloWorld'),instr('HelloWorld','W') from dual
select employee_id,last_name,lpad(salary,10,'*') from employees
select employee_id,last_name,rpad(salary,10,'*') from employees
select trim('h' from 'hfjdkahfdh') from dual
select trim('H' from 'HfjdkahfdHHH') from dual
trim:表示取出两端所有指定的字符,区分大小写
select replace('abcd','b','m') from dual
select replace('abbcdb','b','m') from dual
replace:替换
select round(45.923,2),round(45.923,0),round(45.923,-1) from dual
第二个参数表示保留多少位小数,可以为负数,也不可以不写,不写默认是0
select trunc(45.923,2),trunc(45.923,0),trunc(45.923,-1) from dual
第二个参数表示保留 多少位数,多余的不要
select mod(1100,200) from dual
表示1100模于200
select last_name,salary,mod(salary,5000) from employees where job_id='SA_REP'
select last_name,months_between(sysdate,hire_date) from employees
select add_months(sysdate,2),add_months(sysdate,-3),next_day(sysdate,'星期日') from dual
select last_day(sysdate) from dual
select round(sysdate,'month'),round(sysdate,'mm'),trunc(sysdate,'hh') from dual
select to_char(sysdate,'yyyy-mm-dd hh:mm:ss') from dual
select last_name,to_char(hire_date,'yyyy"年"mm"月"dd"日"') from employees where to_char(hire_date,'yyyy"年"mm"月"dd"日"')='1994年06月07日'
select to_char(1234567.54,'999,999,999.99') from dual
select to_char(1234567.54,'000,000,000.00') from dual
格式化的时候加上货币符号
select last_name,to_char(salary,'$000,000,000.00') from employees
select last_name,to_char(salary,'L000,000,000.00') from employees
select employee_id,last_name,salary*12*(1+nvl(commission_pct,0)) "annual_sal" from employees
select last_name,nvl(to_char(department_id,'999999'),'没有部门') from employees
select last_name,salary,commission_pct, nvl2(commission_pct,'sal+comm','sal') "income" from employees where department_id in (50,80)
select last_name,salary,commission_pct, nvl2(commission_pct,commission_pct+0.15,0.01) "income" from employees where department_id in (50,80)
select first_name,length(first_name) "expr1", last_name,length(last_name) "expr2", nullif(length(first_name),length(last_name)) result from employees
select last_name,coalesce(commission_pct,salary,10) comm from employees order by commission_pct
select employee_id,last_name,department_id,case department_id when 10 then salary*1.1 when 20 then salary*1.2 else salary*1.3 end new_sal from employees where department_id in(10,20,30)
select last_name,job_id,salary, case job_id when 'IT_PROG' then 1.10*salary when 'ST_CLERK' then 1.15*salary when 'SA_REP' then 1.20*salary else salary end "revised_salary" from employees
select employee_id,last_name,department_id,decode(department_id, 10,salary*1.1, 20,salary*1.2, salary) new_sal from employees where department_id in(10,20,30)
打印出 "2009年10月14日 9:25:40" 格式的当前系统的日期和时间.(注意: 使用双引号向日期中添加字符)
select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh:mi:ss') from dual
格式化数字: 1234567.89 为 1,234,567.89
select to_char(1234567,'999,999,999.99') from dual
字符串转为数字时
1). 若字符串中没有特殊字符, 可以进行隐式转换:
select '12345.432'+100 from dual
2). 若字符串中有特殊字符, 例如 '1,234,567.89', 则无法进行隐式转换, 需要使用 to_number() 来完成
select to_number('1,234,567.89','999,999,999.99')+100 from dual
对于把日期作为查询条件的查询, 一般都使用 to_date() 把一个字符串转为日期, 这样可以不必关注日期格式
select last_name, hire_date from employees where hire_date = to_date('1998-5-23', 'yyyy-mm-dd') -- where to_char(hire_date,'yyyy-mm-dd') = '1998-5-23'
转换函数: to_char(), to_number(), to_date()
查询每个月倒数第 2 天入职的员工的信息.
select last_name, hire_date from employees where hire_date = last_day(hire_date) - 1
计算公司员工的年薪
--错误写法: 因为空值计算的结果还是空值 select last_name, salary * 12 * (1 + commission_pct) year_sal from employees
--正确写法 select last_name, salary * 12 * (1 + nvl(commission_pct, 0)) year_sal from employees
查询部门号为 10, 20, 30 的员工信息, 若部门号为 10, 则打印其工资的 1.1 倍, 20 号部门, 则打印其工资的 1.2 倍, 30 号部门打印其工资的 1.3 倍数
--使用 case-when-then-else-end select last_name, department_id, salary, case department_id when 10 then salary * 1.1 when 20 then salary * 1.2 when 30 then salary * 1.3 end new_sal from employees where department_id in (10, 20, 30)
--使用 decode select last_name, department_id, salary, decode(department_id, 10, salary * 1.1, 20, salary * 1.2, 30, salary * 1.3 ) new_sal from employees where department_id in (10, 20, 30)
显示系统时间(注:日期+时间)
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual
--查询员工号,姓名,工资,以及工资提高百分之20%后的结果(new salary) select employee_id,last_name,salary,salary*1.2 "new salary" from employees
--将员工的姓名按首字母排序,并写出姓名的长度(length) select last_name,length(last_name) from employees order by last_name asc
--查询各员工的姓名,并显示出各员工在公司工作的月份数(worked_month) select last_name,hire_date,round(months_between(sysdate,hire_date),1) "worked_month" from employees
--查询员工的姓名,以及在公司工作的月份数(worked_month),并按月份数降序排列 select last_name,hire_date,round(months_between(sysdate,hire_date),1) "worked_month" from employees order by "worked_month" desc
注意:需要加上双引号
select last_name || ' earsn ' || to_char(salary,'$999999') || ' monthly but wants ' || to_char(3*salary,'$999999') "Dream Salary" from employees
select last_name "Last_name",job_id "Job_id", decode(job_id, 'AD_PRES','A', 'ST_MAN','B', 'IT_PROG','C', 'SA_REP','D', 'ST_CLERK','E') "Grade" from employees
用case函数
select last_name "Last_name",job_id "Job_id",case job_id when 'AD_PRES'then 'A' when 'ST_MAN' then 'B' when 'IT_PROG' then 'C' when 'SA_REP' then 'D' when 'ST_CLERK' then'E' end "Grade" from employees