DB2常用SQL的写法(持续更新中...)
-- Author: lavasoft
-- Date? :? 2006-12-14
-- Date? :? 2006-12-14
?
-- 创建一个自定义单值类型
create? distinct type var_newtype
?as decimal(5,2) with comparisons;
create? distinct type var_newtype
?as decimal(5,2) with comparisons;
?-- var_newtype 类型名
?-- decimal(5,2) 实际的类型
?-- decimal(5,2) 实际的类型
?
-- 删除一个自定义单值类型
drop distinct type var_newtype;
drop distinct type var_newtype;
?
-- 创建一个自定义结构数据类型
create type my_type as(
?username varchar(20),
?department integer,
?salary decimal(10,2))
?not final
?mode db2sql;
create type my_type as(
?username varchar(20),
?department integer,
?salary decimal(10,2))
?not final
?mode db2sql;
-- 修改自定义结构数据类型,我目前还没有发现删除属性的方法.
alter type my_type
add attribute hiredate date;
?
-- 删除自定义结构数据类型
drop type my_type;
drop type my_type;
-- 获取系统当前日期
select current date from sysibm.sysdummy1;
select current time from sysibm.sysdummy1;
select current timestamp from sysibm.sysdummy1;
?--sysibm.sysdummy1表是一个特殊的内存中的表，用它可以发现如上面演示的 DB2 寄存器的值。您也可以使用关键字 VALUES 来对寄存器或表达式求值。
VALUES current date;
VALUES current time;
VALUES current timestamp;
?
-- VALUES的更多用法
VALUES 2+5;
VALUES 'hello lavasoft!';
?
values 56
union all
values 45;
union all
values 45;
?
values 1,2,3,4,5,6
union all
values 7,8,9,10,11,12
order by 1;
union all
values 7,8,9,10,11,12
order by 1;
-- 更多变态级DB2 SQL写法,AnyOneTable表示任意一个存在的表
select 234 from AnyOneTable;
select distinct 234 from AnyOneTable;
select distinct 234 as 1 from AnyOneTable;
select distinct 234 from AnyOneTable;
select distinct 234 as 1 from AnyOneTable;
?
select 'DB2变态级的SQL哈哈' from AnyOneTable;
select distinct 'DB2变态级的SQL哈哈' from AnyOneTable;
select distinct 'DB2变态级的SQL哈哈' as 1 from AnyOneTable;
select distinct 'DB2变态级的SQL哈哈' from AnyOneTable;
select distinct 'DB2变态级的SQL哈哈' as 1 from AnyOneTable;
???? --(嘿嘿,好玩吧,你可以用任意一个表来当sysibm.sysdummy1用.不过不推荐这么做,除非你不记得sysibm.sysdummy1怎么写了,Oracle中(对应dual)也一样!哈哈哈哈!)
?
-- 定义变量,还可以设定默认值,给变量赋值
declare var1 char(2);
declare var2 int default 0;
declare var1 char(2);
declare var2 int default 0;
set var1 = 'aa';
set var2 =23;
set var2 =23;
?
--创建一个动态游标变量
declare d_cur integer;
declare d_cur integer;
?
-- 给变量赋值的另一种方法
values expr1, expr2, expr3 into a, b, c;
?-- 相当于
set a = expr1;
set b = expr2;
set c = expr3;
values expr1, expr2, expr3 into a, b, c;
?-- 相当于
set a = expr1;
set b = expr2;
set c = expr3;
?
-- 还有一种赋值方式
set prodname = (case
????????????????? when
????????????????? when
