oracle单行函数

技术2025-06-10 29

oracle基础 oracle单行函数 oracle多表查询 oracle分组函数 oracle子查询 oracle创建表和管理表

--查询工资大于12000 员工的姓名和工资 SELECT LAST_NAME as name,SALARY from EMPLOYEES where SALARY > 12000; --查询员工号为176的员工的姓名和部门号 SELECT LAST_NAME , DEPARTMENT_ID FROM EMPLOYEES where EMPLOYEE_ID = 176 --选择工资不在5000到12000的员工的姓名和工资 SELECT LAST_NAME ,SALARY FROM EMPLOYEES --WHERE SALARY < 5000 or SALARY > 12000 WHERE SALARY not BETWEEN 5000 AND 12000 --选择雇佣时间哎1998-02-01 到1998-05-01 之间的员工姓名，job_id 和雇用时间 SELECT LAST_NAME,JOB_ID,HIRE_DATE FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE,'yyyy-mm-dd') BETWEEN '1998-02-01' and '1998-05-01' --选择在20或50号部门工作的员工姓名和部门号 SELECT LAST_NAME,DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID in (20,50) --选择在1994年雇佣的员工的姓名和雇佣时间 SELECT LAST_NAME,HIRE_DATE FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE,'yyyy') = '1994' --选择公司中没有管理者的员工姓名及其job_id SELECT LAST_NAME,JOB_ID FROM EMPLOYEES WHERE MANAGER_ID is null --选择公司中有奖金的元姓名，工资和奖金级别 SELECT LAST_NAME,COMMISSION_PCT FROM EMPLOYEES WHERE COMMISSION_PCT is not null --选择员工姓名的第三个字母是a的员工姓名 SELECT LAST_NAME FROM EMPLOYEES WHERE LAST_NAME like '__a%' --选择姓名中有字母a和e的员工姓名 SELECT LAST_NAME FROM EMPLOYEES WHERE LAST_NAME like '%a%' and LAST_NAME LIKE '%e%' --------------------------------------------------------------------------------------------------- --单行函数 -- lower(ch) 所有字符转小写 -- upper(ch) 所有字符转大写 -- initcap(ch) 首字母大写，其余小写 SELECT LOWER('iloveu'),UPPER('iloveu'),INITCAP('iloveu') FROM dual; SELECT * FROM EMPLOYEES WHERE LOWER(LAST_NAME) = 'king'; --字符操作 --SUBSTR oracle 索引从1开始 --SUBSTR(ch, pos, length) SELECT CONCAT('xwc love','zcc'),SUBSTR('helloworld',1, 5),LENGTH('helloworld') FROM dual; --instr(str, substr) 某字符首次出现的位置类似indexOf,不同的起始索引是1，如果没有该字符则返回-1 SELECT INSTR('helloworld', 'wo') FROM dual; --LPAD(expr1, n, expr2) 左补齐，如果字符没有n位，则往左边补齐expr2，类似python那个。。 SELECT LAST_NAME,LPAD(SALARY, 10,'-') FROM EMPLOYEES; --RPAD(expr1, n, expr2) 右补齐，如果字符没有n位，则往右边补齐expr2 SELECT LAST_NAME,RPAD(SALARY, 10,'-') FROM EMPLOYEES --TRIM(trim_source) 去除字符串中首尾指定字符 js默认是去除空格的，差不多 --ellohhhworld SELECT TRIM('h' FROM 'hhhhellohhhworldhhhh') FROM dual; --REPLACE(source, search_str, replace_str) 将source字符串中所有的search_str字符串替换成replace_str字符串 --amcdm SELECT REPLACE('abcdb', 'b', 'm') FROM dual; --ROUND(number,n) number 四舍五入，保留n位小数可以是负数如果是整数的情况下置0 --123,123.12,120 SELECT ROUND(123.123) || ',' || ROUND(123.123, 2) || ',' ||ROUND(123.123, -1) as 四舍五入 FROM dual; --124 SELECT ROUND(123.66) FROM dual; --TRUNC(number,n) 将number 截断保留n位，默认取整可以是负数如果是整数的情况下置0 --123,123.12,120 SELECT TRUNC(123.123) || ',' || TRUNC(123.123, 2) || ',' ||TRUNC(123.123, -1) as 四舍五入 FROM dual; --MOD(n1, n2) 取余 n1除以n2的余数 -- 0 SELECT MOD(100,10) FROM dual; -- 1 SELECT MOD(1,9) FROM dual; -- 日期函数 sysdate /* 1.在日期上加上或减去一个数字的结果仍然为日期 2.两个日期相减返回日期之间相差的天数 2.1 两个日期不允许做加法运算，无意义 */ SELECT sysdate,sysdate+1,sysdate-4 FROM dual; --公司中的员工来公司多少天了 --trunc取整 SELECT LAST_NAME, trunc(sysdate - HIRE_DATE) as workdate FROM EMPLOYEES; -- MONTHS_BETWEEN(date1, date2) 两个日期相差多少月 --公司中的员工来公司多少月了 SELECT LAST_NAME, trunc(MONTHS_BETWEEN(sysdate, HIRE_DATE)) as workmonth FROM EMPLOYEES; -- ADD_MONTHS(date, int) 向指定日期中加上若干月数 SELECT ADD_MONTHS(sysdate,2) ,ADD_MONTHS(sysdate, -2) FROM dual; --LAST_DAY(date) 一个月的最后一天 --公司员工中是每个月倒数第二天来的员工有哪些 SELECT LAST_NAME,HIRE_DATE FROM EMPLOYEES WHERE HIRE_DATE = LAST_DAY(HIRE_DATE) -1 --这个月倒数第二天是多少号 SELECT LAST_DAY(SYSDATE) -1 FROM dual; ------------------------------------------------------------------- --重点：转换函数 /* 隐式转换 date <--> varchar <--> number (自动转换) 显示转换 TO_DATE(ch, fmt) date TO_CHAR(x) varchar TO_NUMBER(expr, fmt) number */ ------------------------------------------------------------------- -- varchar to number -- 14 SELECT '12' + 2 FROM dual; -- date to number -- 加了两天到了周末可以放假了 SELECT sysdate +2 FROM dual; --显示转换 SELECT TO_NUMBER(TO_CHAR(sysdate,'yyyymmdd')) FROM dual; --公司员工中是94年6月7号来公司的员工有哪些 SELECT LAST_NAME,HIRE_DATE FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE,'yyyymmdd') = '19940607' --varchar 转换成 date SELECT TO_DATE('19950112', 'yyyymmdd') as "birthday" FROM dual; -- number 转换成 char 数字想变成什么格式用9 L当地的符号 SELECT TO_CHAR('1234567.69','L9,999,999.99') FROM dual; -- char 转换成 number SELECT TO_NUMBER('$1,234,567.69','L9,999,999.99') FROM dual; SELECT TO_NUMBER('$1,234,567.69','L9,999,999.99') + 1 FROM dual; --通用函数，适用于任何数据类型 -- NVL(expr1, expr2) 将空值转换成一个已知的值或者赋默认值 -- 求公司员工的年薪包括奖金率(奖金率可能为空，赋默认值0) SELECT LAST_NAME,SALARY,SALARY*12*(1+nvl(COMMISSION_PCT,0)) "annual salary" FROM EMPLOYEES -- 输出 LAST_NAME,DEPARTMENT_ID,当 DEPARTMENT_ID 为null时显示没有部门 -- NVL(expr1, expr2) expr1,和expr2 类型要一致，不一致时需要转换 SELECT LAST_NAME ,nvl(to_char(DEPARTMENT_ID),'没有部门') FROM EMPLOYEES -- NVL2(expr1, expr2, expr3) -- expr1不为null 返回expr2,为null时返回expre3 -- 查询公司员工的奖金率如果为空奖金率+0.1 不为空奖金率+ 0.15 SELECT LAST_NAME, NVL2(COMMISSION_PCT, COMMISSION_PCT+0.15, 0.1) FROM EMPLOYEES -- 条件表达式 java :if...else -- sql case key when ... then ... else ... end -- 类似 case if () {} else {} -- sql DECODE(expr, [search, result]*, default) /* 查询部门号为10,20,30的员工信息若部门号为10，则打印其工资的1.1倍 20号部门，则打印其工资的1.2倍 30号部门, 则打印其工资的1.3倍 */ SELECT LAST_NAME,EMPLOYEE_ID, case DEPARTMENT_ID when 10 then SALARY*1.1 when 20 then SALARY*1.2 else SALARY*1.3 end as new_sala FROM EMPLOYEES WHERE DEPARTMENT_ID in(10,20,30) --跟上面类似多行比较 SELECT LAST_NAME,EMPLOYEE_ID, DECODE(DEPARTMENT_ID , 10 , SALARY*1.1 , 20 , SALARY*1.2 , SALARY*1.3) as new_sala --相当于else 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.89,'9,999,999.99') FROM dual; -- 字符串转为数字时 -- 1). 若字符串中没有特殊字符, 可以进行隐式转换: SELECT '12' + 2 FROM dual; -- 2). 若字符串中有特殊字符, 例如 '1,234,567.89', 则无法进行隐式转换, 需要使用 to_number() 来完成 SELECT TO_NUMBER('1,234,567.89', '9,999,999.99') FROM dual; --对于把日期作为查询条件的查询, 一般都使用 to_date() 把一个字符串转为日期, 这样可以不必关注日期格式 SELECT LAST_NAME,SALARY FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE,'yyyy-mm-dd') = '1994-06-07' -- 查询每个月倒数第 2 天入职的员工的信息. SELECT LAST_NAME,SALARY,HIRE_DATE FROM EMPLOYEES WHERE HIRE_DATE = LAST_DAY(HIRE_DATE)-1 -- 计算公司员工的年薪 SELECT LAST_NAME,SALARY,SALARY * 12 * (1 + NVL(COMMISSION_PCT,0 ) ) year_sal FROM EMPLOYEES --1. 显示系统时间(注：日期+时间) SELECT TO_CHAR(sysdate,'yyyy-mm-dd hh:mi:ss') as "系统时间" FROM dual; --2. 查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary） SELECT EMPLOYEE_ID,LAST_NAME,SALARY,SALARY*1.2 as "new salary" FROM EMPLOYEES --3. 将员工的姓名按首字母排序，并写出姓名的长度（length） SELECT LAST_NAME,LENGTH(LAST_NAME) FROM EMPLOYEES ORDER BY LAST_NAME asc --4. 查询各员工的姓名，并显示出各员工在公司工作的月份数 SELECT LAST_NAME,ROUND(MONTHS_BETWEEN(sysdate,HIRE_DATE)) FROM EMPLOYEES --5. 查询员工的姓名，以及在公司工作的月份数（worked_month），并按月份数降序排列 SELECT LAST_NAME, ROUND(MONTHS_BETWEEN(sysdate,HIRE_DATE)) as months FROM EMPLOYEES order by months desc /* 6. 做一个查询，产生下面的结果 <last_name> earns <salary> monthly but wants <salary*3> Dream Salary King earns $24000 monthly but wants $72000 */ SELECT ( LAST_NAME || ' earns' || TO_CHAR(SALARY, 'L99999') || ' monthly but wants ' || TO_CHAR(SALARY*3 , 'L99999'))as "Dream Salary" FROM EMPLOYEES /* 7. 使用decode函数，按照下面的条件： job grade AD_PRES A ST_MAN B IT_PROG C SA_REP D ST_CLERK E 产生下面的结果 Last_name Job_id Grade king AD_PRES A */ SELECT LAST_NAME,Job_id,NVL(DECODE(JOB_ID, 'AD_PRES', 'A' , 'ST_MAN' , 'B' , 'IT_PROG', 'C' , 'SA_REP' , 'D' , 'ST_CLERK','E'), '无成绩') as "Grade" FROM EMPLOYEES --8. 将第7题的查询用case函数再写一遍。 SELECT LAST_NAME,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' ELSE '无成绩' END as "Grade" FROM EMPLOYEES

Processed: 0.011, SQL: 9