SQL时间查询和子查询

来源：互联网发布：mac口红免税店价格编辑：程序博客网时间：2024/06/05 09:59

1，插入记录使用INSERT 语句。

INSERT INTOtableName(列名列表) VALUES(值列表)

tableName--表明给哪个表中插入记录

列名列表--表明要给哪几个列插入值

值列表--插入的具体值

插入新记录后记得执行以下SQL语句：

COMMIT;

【示例】给顾客表中插入一条新记录：7，张三,1989-09-02,029-9878998

对于出生日期的值，如果直接采用字符串的形式，那么日期格式要符合当前会话的nls_date_language和nls_date_format的要求。

可通过select *from v$nls_parameters;查看当前会话的参数信息；

可以采用DATE关键字后跟日期字面量，如：DATE‘1989-09-02’。这样能够显式将字符串转换为日期值，要求日期格式必须是’YYYY-MM-DD’的形式。

eg:

SQL>insert into customers (customer_id, first_name, last_name, dob,phone) values(6, 'cissst', 'cissst', to_date('1992-01-01','yyyy-mm-dd'), 888-888-8888);

(日期格式要匹配注意字符串和日期类型数字类型和字符串类型的区别)

已创建 1行。

SQL>select * from customers;

CUSTOMER_IDFIRST_NAME LAST_NAME DOB PHONE

--------------------- ---------- -------------- ------------

6 cissst cissst 01-1月 -92 -8888

1 John Brown 01-1月 -65 800-555-1211

2 Cynthia Green 05-2月 -68 800-555-1212

3 Steve White 16-3月 -71 800-555-1213

4 Gail BLACK 800-555-1214

5 Doreen Blue 20-5月 -70

1，使用TO_CHAR(x[,format])将日期值转换为字符串

x --一个日期值

format字符串用来说明转换后的字符串的格式，如果没有该参数，则使用当前会话中的日期语言和日期格式进行转换。

format中可使用的格式化参数有：

CC--两位的世纪

YYYY --四位的年份

YY --两位的年份

MM --两位的月份

MONTH--月份的完整单词，全大写(如果当前会话的日期语言是中文，则显示：x月)

Month--月份的完整单词，首字母大写

MON--月份单词的前三个字母，全大写 Mon --月份单词的前三个字母，首字母大写

DD --本月中的第几天

DAY --周几的完整单词，全大写

Day -- 周几的完整单词，首字母大写

Dy --周几单词的前三个字母，首字母大写

HH24 --24小时格式的小时

HH --12小时格式的小时

MI --分

SS--秒

【示例】显示顾客表中的信息，并将出生日期列的值转换为”YYYY/MM/DDDy”的形式

eg：

SQL>select sysdate from dual;

SYSDATE

--------------

15-5月 -15

SQL>select to_char(sysdate) from dual;

TO_CHAR(SYSDAT

--------------

15-5月 -15

SQL>select to_char(sysdate, 'cc') from dual;

SQL>select to_char(sysdate, 'cc yyyy') from dual;

TO_CHAR

-------

21 2015

SQL>select to_char(sysdate, 'cc yy') from dual;

TO_CH

-----

21 15

SQL>select to_char(sysdate, 'cc yyyy') from dual;

TO_CHAR

-------

21 2015

SQL>select to_char(sysdate, 'cc yyyy-mm') from dual;

TO_CHAR(SY

----------

21 2015-05

SQL>select to_char(sysdate, 'cc yyyy-mon') from dual;

TO_CHAR(SYSDATE,

----------------

21 2015-5月

SQL>select to_char(sysdate, 'cc yyyy-month') fromdual;

TO_CHAR(SYSDAT

--------------

21 2015-5月

SQL>select to_char(sysdate, 'cc yyyy-month-dd') fromdual;

TO_CHAR(SYSDATE,'

-----------------

21 2015-5月-15

SQL>select to_char(sysdate, 'cc yyyy-dd-mm') fromdual;

TO_CHAR(SYSDA

-------------

212015-15-05

SQL>select to_char(sysdate, 'cc dd/mm/yyyy') fromdual;

TO_CHAR(SYSDA

-------------

2115/05/2015

SQL>select to_char(sysdate, 'cc dd/mm/yyyy day') fromdual;

TO_CHAR(SYSDATE,'CCDD/M

-----------------------

2115/05/2015 星期五

SQL>select to_char(sysdate, 'cc dd/mm/yyyy day dy') fromdual;

TO_CHAR(SYSDATE,'CCDD/MM/YYYYDAYDY')

------------------------------------

2115/05/2015 星期五星期五

SQL>select to_char(sysdate, 'cc dd/mm/yyyy day dy hh:mi:ss') fromdual;

TO_CHAR(SYSDATE,'CCDD/MM/YYYYDAYDYHH:MI:SS')

---------------------------------------------

2115/05/2015 星期五星期五 09:28:32

SQL>select to_char(sysdate, 'cc dd/mm/yyyy day dy h:mi:s') fromdual;

selectto_char(sysdate, 'cc dd/mm/yyyy day dy h:mi:s') fromdual

第 1行出现错误:

ORA-01821:日期格式无法识别 (必须双写不能单写)

SQL>select to_char(sysdate, 'cc dd/mm/yyyy day dy h:mi:ss') fromdual;

selectto_char(sysdate, 'cc dd/mm/yyyy day dy h:mi:ss') fromdual

第 1行出现错误:

ORA-01821:日期格式无法识别

SQL>select to_char(sysdate, 'cc dd/mm/yyyy day dy hh:mi:ss') fromdual;

TO_CHAR(SYSDATE,'CCDD/MM/YYYYDAYDYHH:MI:SS')

---------------------------------------------

2115/05/2015 星期五星期五 09:29:13

SQL>select customer_id, to_char(dob, 'yyyy/mm/dd dy') from customersorder by customer_id;

CUSTOMER_IDTO_CHAR(DOB,'YYYY/MM/DD

----------------------------------

11965/01/01 星期五

21968/02/05 星期一

31971/03/16 星期二

51970/05/20 星期三

61992/01/01 星期三

71992/01/01 星期三

81992/01/01 星期三

2，使用TO_DATE(x[,format])函数将字符串转换为日期值

x--一个日期字符串

format--用来说明字符串中的日期格式。格式字符串中可使用的参数和前面一样。如果没有该参数，则默认使用当前会话中的日期语言和日期格式进行转换。

【示例】给顾客表中插入一条新记录：8，李四，1988-08-1119:08:23，029-87668899

SQL>insert into customers values(9, 'jinping', 'xi',to_date('2015-05-14', 'yyyy-mm-dd'),'999-999-9999');

注：

(1)转换时，实际日期值月和日对应的字符位数可以不足，但不能超过宽度。

SQL>insert into customers values(10, 'jinping', 'xi',to_date('2015-5-14', 'yyyy-mm-dd'),'999-999-9999');

(2)如果实际日期值年份对应的值只有两位，而格式字符串中是’YYYY’，则转换后的日期年份前两位补零。

SQL>insert into customers values(9, 'jinping', 'xi',to_date('15-05-14', 'yyyy-mm-dd'),'999-999-9999');

9 jinping xi 2014-5月-15 999-999-9999 ；

如果格式字符串中是’YY’，则转换后的日期年份前两位和当前系统日期的世纪相同。

SQL>insert into customers values(9, 'jinping', 'xi',to_date('15-05-14', 'yy-mm-dd'), '999-999-9999');

9 jinping xi 2014-5月-15 999-999-9999 ；

如果实际日期值年份对应的值只有两位，而格式字符串中是’RRRR’或’RR’，则转换后的日期年份前两位根据如下规则进行解释：数据不见啦百度吧

00-49 年转换的年份 00-49 转换结果（转换后的年份前两位和系统相同） 50- 99 转换结果（当前系统年份前两位减一

）

系统 50-99 年转换的年份 00-49 转换结果（当前系统年份前两位加一） 50- 99 转换结果（转换后的年份前两位和系统相同

）

eg：

系统 00-49 年转换的年份 00-49 转换结果 50- 99 转换结果

2014 20142014 20651965

系统 50-99 年转换的年份 00-49 转换结果 50- 99 转换结果

）

2065 20142114 20442044

三、日期值相关函数

1，ADD_MONTHS(x,y)

返回日期值x加上y个月后的结果。如果y为负值，则表示减去y个月。

eg：

SQL>select add_months(sysdate, 1) from dual; （如果大于12个月则向前加一年）

ADD_MONTHS(SYS

--------------

15-6月 -15

eg：

SQL>select add_months(sysdate, -26) from dual;

ADD_MONTHS(SYS

-------------- （小于12个月，向后减去一年）

15-3月 -13

2，LAST_DAY(x)

返回日期值x当月的最后一天的日期。

SQL>select last_day(sysdate) from dual;

LAST_DAY(SYSDA

--------------

31-5月 -15

eg：

SQL>select to_char(last_day(add_months(sysdate, -3)), 'yyyy-mm-dd')from dual;

TO_CHAR(LA

---------- （按照格式可以数个函数相叠加）

2015-02-28

3，MONTHS_BETWEEN(x,y)

返回日期x减去日期y后的月份差。

SQL>select months_between(date'1990-1-1', sysdate) fromdual;

4，NEXT_DAY(x,day)

day--是一个表示星期几的字符串(和nls_date_language有关，如’saturday’,’星期六’)

该函数返回从日期x开始，到达下个指定的星期几的日期

eg：SQL>select next_day(sysdate, 'sat') from dual;

NEXT_DAY(S

----------

2015-05-16

eg：（写在中间的小乱入）

SQL>select to_char(add_months(sysdate, -4), 'mon') fromdual;

TO_CHA

------

jan

SQL>select to_char(add_months(sysdate, -4), 'month') fromdual;

TO_CHAR(ADD_M

-------------

january

5，ROUND(x[,unit])

对日期进行舍入取整，如果没有参数unit，则将日期舍入为最近的一天。如果有参数unit，它表示对指定的单元进行操作。

例如，unit取’YYYY’则表示舍入到最近的年份；‘MM’表示舍入到最近的月份；’HH’表示舍入到最近的分钟。。。。。。

eg:

SQL>select round(date'2015-5-19', 'mm') from dual;

ROUND(DATE

---------- (四舍五入到6月)

2015-06-01

SQL>select round(date'2015-5-19', 'yyyy') from dual;

ROUND(DATE

---------- （四舍五入到最近的一年）

2015-01-01

SQL>select round(date'2015-8-19', 'yyyy') from dual;

ROUND(DATE

---------- （四舍五入到最近的一年）

2016-01-01

6，TRUNC(x[,unit])

对日期值x进行截断操作。如果没有参数unit，则将日期截断为当天的开始时间，即00:00:00.

如果参数unit为‘YYYY’,则表示返回当年的第一天；

如果unit为‘MM’，则表示返回当月的第一天;

如果unit为’HH’，则表示将分和秒置为0.

eg：

SQL>select trunc(sysdate, 'yyyy') from dual;

TRUNC(SYSD

----------

2015-01-01

四.时间戳

时间戳(timestamp)是一种数据类型，该类型可以存储一个比较精确的时间点。世纪、四位年份、月、日、小时、分、带小数位的秒。

TIMESTAMP(second_precision)

second_precision代表秒的小数位宽度，取值在0和9之间。

store模式中的表purchases_with_timestamp中含有时间戳类型的列。

eg：

SQL>select product_id, customer_id, to_char(made_on, 'cc yyyy-mm-ddhh:mi:ss') frompurchases_with_timestamp;（select后的选择内容中可以进行列的tochar操作）

要设置当前会话中的时间戳数据的显示格式，可以使用如下设置语句：

altersession setnls_timestamp_format=‘HH24:MI:SS.FF9’;

1,要给表中插入时间戳类型的数据时，可以在字符串形式的值前面使用TIMESTAMP关键字。

例如：TIMESTAMP’2012-09-1118:23:56.324523321’

当表中该时间戳列的精度小于实际值时，实际值会被自动进行舍入操作。

2,字符串和时间戳值之间的转换可以使用以下函数：

TO_TIMESTAMP(x,format)

示例：

TO_TIMESTAMP('2012-09-1119:09:45.9876543','YYYY-MM-DD HH24:MI:SS.FF9')

eg：

SQL>select systimestamp from dual;

SYSTIMESTAMP

---------------------------------------------------------------------------

15-MAY-1510.10.05.296000 AM +08:00

eg：（可以选择中间的一部分这个可以注意下选择少数数据的一种写法）

SQL>select to_char(systimestamp, 'mi:ss.ff9') fromdual;

TO_CHAR(SYSTIME

---------------

10:35.234000000

五、时间间隔

时间间隔数据类型用来存储一段时间长度。Oracle中提供了两种表示时间间隔的数据类型：

1,INTERVALYEAR(precision) TO MONTH (用于表示n年m月的间隔)

precision表示年数的位数，取值范围0~9，默认为2

2,INTERVALDAY(precision1) TO SECOND(precision2) (用于表示n天m秒的间隔)

precision1表示天数的位数，取值范围0~9，默认为2

precision2表示秒的小数部分的位数，取值范围0~9，默认为6

时间间隔可以是正数，也可以是负数。

coupons表中存储了优惠券信息，其中的duration列(INTERVALYEAR(3) TO MONTH)用来记录优惠券的有效时间间隔。

如果要给coupons表中duration列插入数据，可使用以下方式得到一个时间间隔值：

1，使用关键字：INTERVAL ‘n-m’ YEAR(x) TO MONTH

示例：

INTERVAL ‘2’YEAR --年份默认位数为2

INTERVAL ’2’YEAR(3) --年份实际值可以小于声明的位数，但不能超过

INTERVAL ‘3’MONTH

INTERVAL‘2-4’ YEAR TO MONTH

INTERVAL’23-10’ YEAR(3) TO MONTH

INTERVAL’234-5’ YEAR(3) TO MONTH

eg：

SQL>insert into coupons values(7, '$99 off test',to_yminterval('2-3'));

注：年份的位数不能超过表中列的实际类型宽度，否则出错

2，使用TO_YMINTERVAL(x)函数

x--是形如’y-m‘的字符串

示例：TO_YMINTERVAL(’23-5’)

3，使用NUMTOYMINTERVAL(x,unit)函数

x--数值

unit--转换单位，可取值’YEAR’或‘MONTH’

示例：NUMTOYMINTERVAL(2.5,’YEAR’)

promotions表用来存储促销信息，其中的duration列(INTERVALDAY(3) TO SECOND(4))记录促销的时间间隔。

如果要给promotions表的duration列插入数据：

1，使用关键字：INTERVAL ‘d h:m:s’ DAY(x) TOSECOND(y)

示例：

INTERVAL ‘1’DAY --天的默认位数为2

INTERVAL ‘4’MINUTE

INTERVAL‘34’ SECOND

INTERVAL ‘23’ DAY TO HOUR

INTERVAL ‘12:2’ DAY TO MINUTE

INTERVAL ‘20:0:34’ DAY TO SECOND

INTERVAL‘123 2:23:45’ DAY(3) TO SECOND

INTERVAL‘123 2:23:45.678’ DAY(3) TO SECOND(3)

2，使用TO_DSINTERVAL(x)函数

x--是形如’dh:m:s.f‘的字符串

示例：TO_DSINTERVAL('232:23:56.23');

3，使用NUMTODSINTERVAL(x,unit)函数

x--数值

unit--转换单位，可取值’DAY’、‘HOUR’、’MINUTE’、’SECOND’.

示例：NUMTODSINTERVAL(2.5,’MINUTE’)

0 0