第二讲函数PPT学习教案

1、会计学1第二讲函数第二讲函数第1页/共68页第2页/共68页function_name (arg1, arg2,.)第3页/共68页第4页/共68页LOWERUPPERINITCAPCONCATSUBSTRLENGTHINSTRLPAD | RPADTRIMREPLACE第5页/共68页LOWERUPPERINITCAPCONCATSUBSTRLENGTHINSTRLPAD | RPADTRIMREPLACE第6页/共68页第7页/共68页SELECT employee_id, last_name, department_idFROM employeesWHERE last_name = hi

2、ggins;SELECT employee_id, last_name, department_idFROM employeesWHERE LOWER(last_name) = higgins;第8页/共68页第9页/共68页SELECT employee_id, CONCAT(first_name, last_name) NAME, job_id, LENGTH (last_name), INSTR(last_name, a) Contains a?FROM employeesWHERE SUBSTR(job_id, 4) = REP;123123第10页/共68页第11页/共68页SELE

3、CT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1)FROM DUAL;DUAL是一个虚拟表，你可以用它来查看函数和计算的结果123312第12页/共68页SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-2)FROM DUAL;312123第13页/共68页SELECT last_name, salary, MOD(salary, 5000)FROM employeesWHERE job_id = SA_REP;第14页/共68页SELECT last_name, hire_date

4、FROM employeesWHERE last_name like G%第15页/共68页第16页/共68页第17页/共68页SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKSFROM employeesWHERE department_id = 90;第18页/共68页两个日期之间的月数两个日期之间的月数MONTHS_BETWEENADD_MONTHSNEXT_DAYLAST_DAYROUNDTRUNC 加日历月到日期加日历月到日期下一个星期几是几号下一个星期几是几号指定月的最后一天指定月的最后一天四舍五入日期四舍五入日期截断日期截断日期Fun

5、ctionDescription第19页/共68页 MONTHS_BETWEEN (01-SEP-95,11-JAN-94) ADD_MONTHS (11-JAN-94,6) NEXT_DAY (01-SEP-95,FRIDAY) LAST_DAY(01-FEB-95) 19.677419411-JUL-9408-SEP-9528-FEB-95第20页/共68页 ROUND(SYSDATE,MONTH) 01-AUG-95 ROUND(SYSDATE ,YEAR) 01-JAN-96 TRUNC(SYSDATE ,MONTH) 01-JUL-95 TRUNC(TO_DATE(25-JUL-95

6、) ,YEAR) 01-JAN-95 Assume SYSDATE = 25-JUL-95: TRUNC(SYSDATE ,YEAR) 01-JAN-95第21页/共68页第22页/共68页第23页/共68页TO_CHAR(date, format_model)第24页/共68页YYYYYEARMMMONTHDYDAYFull year in numbersYear spelled outTwo-digit value for monthThree-letter abbreviation of the day of the weekFull name of the day of the wee

7、kFull name of the monthMONThree-letter abbreviation of the monthDDNumeric day of the month第25页/共68页TO_CHAR(number, format_model)90$L.,代表一个数字代表一个数字强迫显示强迫显示0放置一美元符放置一美元符使用本地货币符号使用本地货币符号显示小数点显示小数点显示千位标示符显示千位标示符第26页/共68页SELECT TO_CHAR(salary, $99,999.00) SALARYFROM employeesWHERE last_name = Ernst;第27页/

8、共68页TO_NUMBER(char, format_model)TO_DATE(char, format_model)第28页/共68页TO_NUMBER(char, format_model)TO_DATE(char, format_model)第29页/共68页Current Year1995199520012001Specified Date27-OCT-9527-OCT-1727-OCT-1727-OCT-95RR Format1995201720171995YY Format1995191720172095If two digits of the current year are:

9、04904950995099日期在当前世纪日期在当前世纪日期在当前世纪的下一个世纪日期在当前世纪的下一个世纪日期在当前世纪的上一个世纪日期在当前世纪的上一个世纪日期在当前世纪日期在当前世纪If the specified two-digit year is:第30页/共68页SELECT last_name, TO_CHAR(hire_date, DD-Mon-YYYY)FROM employeesWHERE hire_date 8000GROUP BY department_id;WHERE AVG(salary) 8000 *ERROR at line 3:ORA-00934: group

10、 function is not allowed hereCannot use the WHERE clause to restrict groups第61页/共68页SELECTcolumn, group_functionFROMtableWHEREconditionGROUP BYgroup_by_expressionHAVINGgroup_conditionORDER BYcolumn;第62页/共68页SELECT department_id, MAX(salary)FROM employeesGROUP BY department_idHAVING MAX(salary)10000 ;第63页/共68页The maximumsalaryper departmentwhen it isgreater than$10,000EMPLOYEES第64页/共68页SELECT job_id, SUM(salary) PAYROLLFROM employeesWHERE jo