oracle10g ocp认证周老师中文与真题精解

1、nn学完本节课之后,你会明白如下知识使用数据类型相似的一微少的秒和道时区日使学完本节课之后,你会明白如下知识使用数据类型相似的一微少的秒和道时区日使用数据类两个不同的日期使用如下日期函数nCURRENT_DATE,使用下列方法,CURRENT_DATE,使用下列方法,nThe image represents the time each time z timeThe image represents the time each time z timeis12:00.hen nTIME_ZONETIME_ZONE可以被设置为一偏移数据库时TIME_ZONETIME_ZONE可以被设置为一偏移数据

2、库时一个被命名的区nALTERSES ALTERSES ALTERSES ALTER SET TIME_ZONE = -SET TIME_ZONE = dbtimezone; SET TIME_ZONE = local;SET TIME_ZONE = CURRENT_DATE, CURRENT_TIMEST , 从用户会话中返回当前日DATE数据类:CURRENT_DATE, CURRENT_TIMEST , 从用户会话中返回当前日DATE数据类:从用户会话中返回当前日期和时WITH TIME ZONE的数据类:从用户会话中返回当前日期和时的数据类n期和时间的区别nALTER SET NLS_

3、DATE_FORMAT = DD-MON-YYYY HH24:MI:SS; ALTER SESSET TIME_ZONE = -5:00;SELECT TIMEZONE, 期和时间的区别nALTER SET NLS_DATE_FORMAT = DD-MON-YYYY HH24:MI:SS; ALTER SESSET TIME_ZONE = -5:00;SELECT TIMEZONE, CURRENT_DATE FROM SELECT TIMEZONE, FROM SELECT TIMEZONE, FROM 查询结果n查询结果nDBTIMEZONE显示数据库时区显示会话时DBTIMEZONE显示

4、数据库时区显示会话时区nSELECT TIMEZONE FROM SELECT DBTIMEZONE FROM n数据类字年,月,日n数据类字年,月,日,小时,分钟,秒以及秒的小数WITHTIME 和类型相同; 还包含TIMEZONE_HOUR, 和 TIMEZONE_MINUTE或 WITHLOCAL TIMEZONE类型相同; 还包含时区n日期时间字合法4712to9999(excludingyearn日期时间字合法4712to9999(excludingyear01 to 01 to 00 to 00 to 00 to 59.9(N) where 9(N) is 12 to 00 to

5、区别DATE和ABnALTER TABLE employees MODIFY hire_date TIMESTSELECThire_date FROM employees;-区别DATE和ABnALTER TABLE employees MODIFY hire_date TIMESTSELECThire_date FROM employees;-whenhire_dateis of type DATESELECThire_date FROM employees;nSELECT * FROM O web_orders values (current_date, current_timest+2)

6、;CREATETABLEnSELECT * FROM O web_orders values (current_date, current_timest+2);CREATETABLE(order_date TIMESTWITH TIME ZONE, delivery_time TIMESTWITHLOCALTIMEZONE);CURRENT_DATE,ERVAL 数据类使用下列方法,CURRENT_DATE,ERVAL 数据类使用下列方法,nERVAL两个日期时间值之间的差有两类Year-Day-间隔的精度是间字段ERVAL两个日期时间值之间的差有两类Year-Day-间隔的精度是间字段的子在

7、限定词中指nERVAL YEAR TO Year,ERVAL DAY TO Days, Hour, Minute, Second with fractional secondsERVALnERVALValidValuesitiveorERVALnERVALValidValuesitiveor00 to itiveor00 to 00 to 00 to 59.9(N) where 9(N) is ERVALYEARTOMONTH: nERVALYEARTOMONTH: nCREATE TABLE (prod_id number, warranty_timeERVAL YEAR(3) TO O w

8、arranty VALUES ERVAL 8 O warranty VALUES (155,ERVAL200 O warranty VALUES (678, 200-SELECT * FROM ERVALDAYTOSECOND 数据类型: nERVALDAYTOSECOND 数据类型: nSELECT * FROM CREATE TABLE ( exp_id number, ERVAL DAY(2) TO O lab VALUES (100012, 90 00:00:00); O lab VALUES (56098,ERVAL 6 03:30:16 DAY TO CURRENT_DATE,使用

9、下列方法,CURRENT_DATE,使用下列方法,nnSELECT last name,nSELECT last name, hire FROM WHERE manager_id = EXTRACT (MONTH FROM EXTRACT (YEAR FROM FROM Canada/Yukon和Europe/LondonCanada/Yukon和Europe/London时区nSELECT FROM 显示Australia/North时区WITHTIMEZONE值显示Australia/North时区WITHTIMEZONE值的nSELECT 2000-07-12 08:00:00, Aus

10、tralia/North) FROM DUAL;:转换字符串2007-03-11:00:00作为nSELECT (2007-03-06:转换字符串2007-03-11:00:00作为nSELECT (2007-03-06 YYYY-MM-DD FROM 日期nSELECT 日期nSELECT hire_date + TO_YMERVAL(01-02) AS WHERE department_id = 小时的日期nSELECT TO_CHAR(hire_date, mm-dd-yy:hh:mi:ss) hire_date, TO_CHAR(hire_date +小时的日期nSELECT TO_CHAR(hire_date, mm-dd-yy:hh:mi:ss) hire_date, TO_CHAR(hire_date +ERVAL(100 mm-dd-yy:hh:mi:ss) hiredate2 FROM employees;日时间从01:59:59 AM 跳到03:00:00 日时间从01:59:59 AM 跳到03:00:00 时间值从02:00:00 AM到02:59:59 AM 是无效日时间从