第二講函數(shù)PPT學(xué)習(xí)教案

1、會(huì)計(jì)學(xué)1第二講函數(shù)第二講函數(shù)第1頁(yè)/共68頁(yè)第2頁(yè)/共68頁(yè)function_name (arg1, arg2,.)第3頁(yè)/共68頁(yè)第4頁(yè)/共68頁(yè)LOWERUPPERINITCAPCONCATSUBSTRLENGTHINSTRLPAD | RPADTRIMREPLACE第5頁(yè)/共68頁(yè)LOWERUPPERINITCAPCONCATSUBSTRLENGTHINSTRLPAD | RPADTRIMREPLACE第6頁(yè)/共68頁(yè)第7頁(yè)/共68頁(yè)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頁(yè)/共68頁(yè)第9頁(yè)/共68頁(yè)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頁(yè)/共68頁(yè)第11頁(yè)/共68頁(yè)SELE

3、CT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1)FROM DUAL;DUAL是一個(gè)虛擬表，你可以用它來(lái)查看函數(shù)和計(jì)算的結(jié)果123312第12頁(yè)/共68頁(yè)SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-2)FROM DUAL;312123第13頁(yè)/共68頁(yè)SELECT last_name, salary, MOD(salary, 5000)FROM employeesWHERE job_id = SA_REP;第14頁(yè)/共68頁(yè)SELECT last_name, hire_date

4、FROM employeesWHERE last_name like G%第15頁(yè)/共68頁(yè)第16頁(yè)/共68頁(yè)第17頁(yè)/共68頁(yè)SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKSFROM employeesWHERE department_id = 90;第18頁(yè)/共68頁(yè)兩個(gè)日期之間的月數(shù)兩個(gè)日期之間的月數(shù)MONTHS_BETWEENADD_MONTHSNEXT_DAYLAST_DAYROUNDTRUNC 加日歷月到日期加日歷月到日期下一個(gè)星期幾是幾號(hào)下一個(gè)星期幾是幾號(hào)指定月的最后一天指定月的最后一天四舍五入日期四舍五入日期截?cái)嗳掌诮財(cái)嗳掌贔un

5、ctionDescription第19頁(yè)/共68頁(yè) 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頁(yè)/共68頁(yè) 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頁(yè)/共68頁(yè)第22頁(yè)/共68頁(yè)第23頁(yè)/共68頁(yè)TO_CHAR(date, format_model)第24頁(yè)/共68頁(yè)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頁(yè)/共68頁(yè)TO_CHAR(number, format_model)90$L.,代表一個(gè)數(shù)字代表一個(gè)數(shù)字強(qiáng)迫顯示強(qiáng)迫顯示0放置一美元符放置一美元符使用本地貨幣符號(hào)使用本地貨幣符號(hào)顯示小數(shù)點(diǎn)顯示小數(shù)點(diǎn)顯示千位標(biāo)示符顯示千位標(biāo)示符第26頁(yè)/共68頁(yè)SELECT TO_CHAR(salary, $99,999.00) SALARYFROM employeesWHERE last_name = Ernst;第27頁(yè)/

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

9、04904950995099日期在當(dāng)前世紀(jì)日期在當(dāng)前世紀(jì)日期在當(dāng)前世紀(jì)的下一個(gè)世紀(jì)日期在當(dāng)前世紀(jì)的下一個(gè)世紀(jì)日期在當(dāng)前世紀(jì)的上一個(gè)世紀(jì)日期在當(dāng)前世紀(jì)的上一個(gè)世紀(jì)日期在當(dāng)前世紀(jì)日期在當(dāng)前世紀(jì)If the specified two-digit year is:第30頁(yè)/共68頁(yè)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頁(yè)/共68頁(yè)SELECTcolumn, group_functionFROMtableWHEREconditionGROUP BYgroup_by_expressionHAVINGgroup_conditionORDER BYcolumn;第62頁(yè)/共68頁(yè)SELECT department_id, MAX(salary)FROM employeesGROUP BY department_idHAVING MAX(salary)10000 ;第63頁(yè)/共68頁(yè)The maximumsalaryper departmentwhen it isgreater than$10,000EMPLOYEES第64頁(yè)/共68頁(yè)SELECT job_id, SUM(salary) PAYROLLFROM employeesWHERE jo