SQL操作与日期函数应用-优快云博客

本文链接：https://blog.youkuaiyun.com/iteye_20481/article/details/82106130

本文介绍了使用SQL创建表格并插入数据的过程，重点展示了如何利用DATE类型进行日期范围查询，以及在不同领域的书籍信息管理中SQL的应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQL>
SQL> CREATE TABLE titles(
2 title_id CHAR(3) NOT NULL,
3 title_name VARCHAR(40) NOT NULL,
4 type VARCHAR(10) NULL ,
5 pub_id CHAR(3) NOT NULL,
6 pages INTEGER NULL ,
7 price DECIMAL(5,2) NULL ,
8 sales INTEGER NULL ,
9 pubdate DATE NULL ,
10 contract SMALLINT NOT NULL
11 );

Table created.

SQL>
SQL>
SQL>
SQL>
SQL> INSERT INTO titles VALUES('T01','Java','history','P01',111,21.99,566,DATE '2000-08-01',1);

1 row created.

SQL> INSERT INTO titles VALUES('T02','Oracle','history','P03', 114,19.95,9566,DATE '1998-04-01',1);

1 row created.

SQL> INSERT INTO titles VALUES('T03','SQL','computer','P02', 122,39.95,25667,DATE '2000-09-01',1);

1 row created.

SQL> INSERT INTO titles VALUES('T04','C++','psychology','P04', 511,12.99,13001,DATE '1999-05-31',1);

1 row created.

SQL> INSERT INTO titles VALUES('T05','Python','psychology','P04', 101,6.95,201440,DATE '2001-01-01',1);

1 row created.

SQL> INSERT INTO titles VALUES('T06','JavaScript','biography','P01', 173,19.95,11320,DATE '2000-07-31',1);

1 row created.

SQL> INSERT INTO titles VALUES('T07','LINQ','biography','P03', 331,23.95,1500200,DATE '1999-10-01',1);

1 row created.

SQL> INSERT INTO titles VALUES('T08','C#','children','P04', 861,10.00,4095,DATE '2001-06-01',1);

1 row created.

SQL> INSERT INTO titles VALUES('T09','SQL Server','children','P04', 212,13.95,5000,DATE '2002-05-31',1);

1 row created.

SQL> INSERT INTO titles VALUES('T10','AJAX','biography','P01', NULL,NULL,NULL,NULL,0);

1 row created.

SQL> INSERT INTO titles VALUES('T11','VB','psychology','P04', 821,7.99,94123,DATE '2000-11-30',1);

1 row created.

SQL> INSERT INTO titles VALUES('T12','Office','biography','P01', 507,12.99,100001,DATE '2000-08-31',1);

1 row created.

SQL> INSERT INTO titles VALUES('T13','VBA','history','P03', 812,29.99,10467,DATE '1999-05-31',1);

1 row created.

SQL>
SQL>
SQL>
SQL> SELECT title_id, pubdate
2 FROM titles
3 WHERE pubdate BETWEEN DATE '2000-01-01'
4 AND DATE '2000-12-31';

TIT PUBDATE
--- ---------
T01 01-AUG-00
T03 01-SEP-00
T06 31-JUL-00
T11 30-NOV-00
T12 31-AUG-00

SQL>
SQL> drop table titles;

Table dropped.

SQL>

才发现DATE的好用法

DATE小用法