同一个需求,不同数据的SQL写法 经典电子书 (sql cookbook)
示例:
Recipe 8.1. Adding and Subtracting Days, Months, and Years
Problem
You need to add or subtract some number of days, months, or years from a date. For example, using the HIREDATE for employee CLARK you want to return six different dates: five days before and after CLARK was hired, five months before and after CLARK was hired, and, finally, five years before and after CLARK was hired. CLARK was hired on "09-JUN-1981", so you want to return the following result set:
HD_MINUS_5D HD_PLUS_5D HD_MINUS_5M HD_PLUS_5M HD_MINUS_5Y HD_PLUS_5Y
----------- ----------- ----------- ----------- ----------- -----------
04-JUN-1981 14-JUN-1981 09-JAN-1981 09-NOV-1981 09-JUN-1976 09-JUN-1986
12-NOV-1981 22-NOV-1981 17-JUN-1981 17-APR-1982 17-NOV-1976 17-NOV-1986
18-JAN-1982 28-JAN-1982 23-AUG-1981 23-JUN-1982 23-JAN-1977 23-JAN-1987
Solution
DB2
Standard addition and subtraction is allowed on date values, but any value that you add to or subtract from a date must be followed by the unit of time it represents:
1 select hiredate -5 day as hd_minus_5D,
2 hiredate +5 day as hd_plus_5D,
3 hiredate -5 month as hd_minus_5M,
4 hiredate +5 month as hd_plus_5M,
5 hiredate -5 year as hd_minus_5Y,
6 hiredate +5 year as hd_plus_5Y
7 from emp
8 where deptno = 10
Oracle
Use standard addition and subtraction for days, and use the ADD_MONTHS function to add and subtract months and years:
1 select hiredate-5 as hd_minus_5D,
2 hiredate+5 as hd_plus_5D,
3 add_months(hiredate,-5) as hd_minus_5M,
4 add_months(hiredate,5) as hd_plus_5M,
5 add_months(hiredate,-5*12) as hd_minus_5Y,
6 add_months(hiredate,5*12) as hd_plus_5Y
7 from emp
8 where deptno = 10
PostgreSQL
Use standard addition and subtraction with the INTERVAL keyword specifying the unit of time to add or subtract. Single quotes are required when specifying an INTERVAL value:
1 select hiredate - interval '5 day' as hd_minus_5D,
2 hiredate + interval '5 day' as hd_plus_5D,
3 hiredate - interval '5 month' as hd_minus_5M,
4 hiredate + interval '5 month' as hd_plus_5M,
5 hiredate - interval '5 year' as hd_minus_5Y,
6 hiredate + interval '5 year' as hd_plus_5Y
7 from emp
8 where deptno=10
MySQL
Use standard addition and subtraction with the INTERVAL keyword specifying the unit of time to add or subtract. Unlike the PostgreSQL solution, you do not place single quotes around the INTERVAL value:
1 select hiredate - interval 5 day as hd_minus_5D,
2 hiredate + interval 5 day as hd_plus_5D,
3 hiredate - interval 5 month as hd_minus_5M,
4 hiredate + interval 5 month as hd_plus_5M,
5 hiredate - interval 5 year as hd_minus_5Y,
6 hiredate + interval 5 year as hd_plus_5Y
7 from emp
8 where deptno=10
Alternatively, you can use the DATE_ADD function, which is shown below:
1 select date_add(hiredate,interval -5 day) as hd_minus_5D,
2 date_add(hiredate,interval 5 day) as hd_plus_5D,
3 date_add(hiredate,interval -5 month) as hd_minus_5M,
4 date_add(hiredate,interval 5 month) as hd_plus_5M,
5 date_add(hiredate,interval -5 year) as hd_minus_5Y,
6 date_add(hiredate,interval 5 year) as hd_plus_5DY
7 from emp
8 where deptno=10
SQL Server
Use the DATEADD function to add or subtract different units of time to/from a date:
1 select dateadd(day,-5,hiredate) as hd_minus_5D,
2 dateadd(day,5,hiredate) as hd_plus_5D,
3 dateadd(month,-5,hiredate) as hd_minus_5M,
4 dateadd(month,5,hiredate) as hd_plus_5M,
5 dateadd(year,-5,hiredate) as hd_minus_5Y,
6 dateadd(year,5,hiredate) as hd_plus_5Y
7 from emp
8 where deptno = 10
Discussion
The Oracle solution takes advantage of the fact that integer values represent days when performing date arithmetic. However, that's true only of arithmetic with DATE types. Oracle9 i Database introduced TIMESTAMP types. For those, you should use the INTERVAL solution shown for PostgreSQL. Beware too, of passing TIMESTAMPs to old-style date functions such as ADD_MONTHS. By doing so, you can lose any fractional seconds that such TIMESTAMP values may contain.
The INTERVAL keyword and the string literals that go with it represent ISO-standard SQL syntax. The standard requires that interval values be enclosed within single quotes. PostgreSQL (and Oracle9 i Database and later) complies with the standard. MySQL deviates somewhat by omitting support for the quotes.
Ebcdic-ascii 对照表.
Ebcdic-ascii 对照表.
UTF-EBCDIC is a character encoding used to represent Unicode characters. It is meant to be EBCDIC-friendly, so that legacy EBCDIC applications on mainframes may process the characters without much difficulty. Its advantages for existing EBCDIC-based systems are similar to UTF-8's advantages for existing ASCII-based systems. Details on UTF-EBCDIC are defined in Unicode Technical Report #16.
oracle 11g overview
数据库重放
探究数据库重放,这是一个新工具,它允许您捕获 SQL 语句并可随时重放这些语句。
分区
了解引用分区、间隔分区和虚拟列分区;新的子分区选项;等等。
事务管理
介绍闪回数据存档并探究企业管理器的 LogMiner 接口。
模式管理
轻松添加具有默认值的列,浏览不可见的索引、虚拟列和只读表。
SQL 计划管理
使用在每次选择正确计划的绑定变量,并确保新的执行计划在使用前已经过完善。
SQL Performance Analyzer
准确评估重新编写 SQL 语句的影响,并获得改进建议。
SQL Access Advisor
获得关于基于表(而不仅仅是数据)的实际使用的最佳表设计的建议。
PL/SQL:高效的编码
在不同的事件处触发几次的触发器以及迫使相同类型的触发器排队的能力是一些新增亮点。
RMAN
探究 Data Recovery Advisor,对相同文件进行并行备份,并且创建和管理虚拟目录。
安全性
了解表空间加密、区分大小写的口令、数据屏蔽以及其他特性。
自动存储管理
了解新的 SYSASM 角色、可变的区大小以及其他 ASM 改进。
可管理性
探究自动内存管理、多列统计信息、联机修补等更多特性。
缓存和连接池
探究 SQL 结果缓存、PL/SQL 函数缓存以及数据库驻留连接池。
SQL 操作:Pivot 和 Unpivot
使用简单的 SQL 以电子表格类型的交叉表报表显示任何关系表中的信息,并将交叉表中的所有数据存储到关系表中。
SecureFiles
探究下一代 LOB:LOB 加密、压缩、消除重复和异步性。
弹性
探究自动运行情况监视、自动诊断信息库以及其他新的弹性特性。
Data Guard
实时查询物理备用数据库,而无需停止恢复操作(针对初学者)。
PL/SQL 性能
探究代码内联、“真正的”原生编译、PLS 计时器、简单整数的使用等内容。
数据仓库和 OLAP
浏览这些领域的新特性,包括按多维数据集组织的物化视图。