Oracle第六课课后作业

Oracle第六课课后作业

1. 虚表（dual)：这个表不存在物理的表，只是方便用来测试或诗SQL语句完整

   select * from dual;

2. 内置函数

   • 单行函数

     • 字符函数

       -- 返回字符的ascii值:ascii函数
       SELECT ASCII('a') FROM dual;
       -- 将两个字符或字符串拼接起来：concat函数
       SELECT CONCAT('a','b') FROM dual;
       SELECT CONCAT('my name is ','ruoni') FROM dual;
       -- 返回字符在字符串中的位置(下标从1开始，返回第一个出现的位置)：instr函数
       -- 不指定位置，默认从头开始找
       SELECT INSTR('acdescgs','c') FROM dual;
       -- 指定位置，从第3个字符开始查找
       SELECT INSTR('acdescgs','c',3) FROM dual;
       -- 指定从第2个字符开始，找第2次出现的c
       SELECT INSTR('acdescgs','c',2,2) FROM dual;
       -- 返回字符串的长度：length函数
       SELECT LENGTH('acdescgs') FROM dual;
       -- 将所有字符全部转换为小写：lower函数
       SELECT LOWER('ABCSDG') FROM dual;
       -- 将所有字符全部转换为大写：upper函数
       SELECT UPPER('acdescgs') FROM dual;
       -- 去掉字符串中左边的空格：ltrim函数,去掉字符串中右边的空格：rtrim函数，去掉字符串中左右两边的空格
       SELECT LTRIM('     ABCSE    '),RTRIM('     ABCSE    '),TRIM('     ABCSE    ') FROM dual;
       -- 去掉字符串中左边指定的字符,去掉字符串中右边指定的字符,去掉字符串中左右的字符
       SELECT LTRIM('ABCSE','A'),RTRIM('ABCSE','SE'),TRIM('A' FROM 'ABCSEA') FROM dual;
       -- 将字符串中的字符替换成指定的字符：replace函数
       SELECT REPLACE('山西-运城市','-','省') FROM dual;
       -- 截取字符串中的一段,不指定截取几个字符，会默认截取到末尾
       SELECT SUBSTR('abcdefg',3) FROM dual;
       SELECT SUBSTR('abcdefg',3,1) FROM dual;

     • 数字函数

       -- 求绝对值：abs函数
       SELECT ABS(-1) FROM dual;
       -- 求大于或等于某个数的最小值:ceil函数
       SELECT CEIL(2.3) FROM dual;
       -- 求小于或等于某个数的最大值：floor函数
       SELECT FLOOR(2.3) FROM dual;
       -- 四舍五入，不指定保留几位小数，默认取整,:round函数
       SELECT ROUND(3.356) FROM dual;
       SELECT ROUND(3.356,1) FROM dual;
       -- 截断函数，不指定保留几位小数，默认取整(直接截断，不会四舍五入):trunc函数
       SELECT TRUNC(3.356) FROM dual;
       SELECT TRUNC(3.356,1) FROM dual;
       -- 取余:mod函数
       SELECT MOD(5,2) FROM dual;
       ​

     • 日期函数

       -- 显示当前时间:sysdate关键字
       SELECT SYSDATE FROM dual;
       -- 在当前时间上加一个月：add_months函数
       SELECT add_months(SYSDATE,1) FROM dual;
       -- 求当前月的最后一天是几号：last_day函数
       SELECT last_day(SYSDATE) FROM dual;
       -- 四舍五入日期:round函数
       SELECT ROUND(SYSDATE),ROUND(SYSDATE,'month'),ROUND(SYSDATE,'year') FROM dual;
       -- 员工从入职到现在工作了多少个月了，月份差：months_between函数
       SELECT e.*,months_between(SYSDATE,hiredate) FROM emp e WHERE empno IN(7369,7499,7788);
       -- 求当前日期的下个星期二是几号：next_day函数
       SELECT next_day(SYSDATE,'星期二') FROM dual;
       -- 提取员工的入职月份：extract函数
       SELECT EXTRACT(MONTH FROM hiredate) FROM emp;
       -- 截断日期：trunc函数
       SELECT TRUNC(SYSDATE,'month') FROM dual;

     • 转换函数

       -- 数字到字符串:to_char函数
       SELECT to_char(1) FROM dual;
       -- 字符串到数字
       SELECT to_number('123') FROM dual;
       -- 日期到字符
       SELECT to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss') FROM dual;
       -- 字符到日期
       SELECT to_date('2021-01-20 20:11:35','yyyy-mm-dd hh24:mi:ss') FROM dual;

3. 应用解决

   • 工作年限

     SELECT months_between(SYSDATE,hiredate) FROM emp e;

   • 找到工作年限最长的员工

     SELECT t.* FROM (SELECT e.*,trunc(months_between(SYSDATE,hiredate)) work_date FROM emp e) t WHERE t.work_date IS NOT NULL AND ROWNUM=1 ORDER BY t.work_date DESC;

   • 给工作年限超过两年的员工sal增加100元

     SELECT t.empno,t.work_year 工作年份,t.sal 原始工资,t.sal+100 现在工资 FROM (SELECT e.*,trunc(months_between(SYSDATE,hiredate)/12) work_year FROM emp e) t WHERE t.work_year IS NOT NULL AND t.work_year>2;