DB2的sql
来源:互联网 发布:淘宝如何修改最低折扣 编辑:程序博客网 时间:2024/06/14 17:59
select emp_no,emp_name,dept,isnull(convert(char(10),birthday,120),'日期不详') birthday
from employee
order by dept
2、查找与喻自强在同一个单位的员工姓名、性别、部门和职称
select emp_no,emp_name,dept,title
from employee
where emp_name<>'喻自强' and dept in
(select dept from employee
where emp_name='喻自强')
3、按部门进行汇总,统计每个部门的总工资
select dept,sum(salary)
from employee
group by dept
4、查找商品名称为14寸显示器商品的销售情况,显示该商品的编号、销售数量、单价和金额
select a.prod_id,qty,unit_price,unit_price*qty totprice
from sale_item a,product b
where a.prod_id=b.prod_id and prod_name='14寸显示器'
5、在销售明细表中按产品编号进行汇总,统计每种产品的销售数量和金额
select prod_id,sum(qty) totqty,sum(qty*unit_price) totprice
from sale_item
group by prod_id
6、使用convert函数按客户编号统计每个客户1996年的订单总金额
select cust_id,sum(tot_amt) totprice
from sales
where convert(char(4),order_date,120)='1996'
group by cust_id
7、查找有销售记录的客户编号、名称和订单总额
select a.cust_id,cust_name,sum(tot_amt) totprice
from customer a,sales b
where a.cust_id=b.cust_id
group by a.cust_id,cust_name
8、查找在1997年中有销售记录的客户编号、名称和订单总额
select a.cust_id,cust_name,sum(tot_amt) totprice
from customer a,sales b
where a.cust_id=b.cust_id and convert(char(4),order_date,120)='1997'
group by a.cust_id,cust_name
9、查找一次销售最大的销售记录
select order_no,cust_id,sale_id,tot_amt
from sales
where tot_amt=
(select max(tot_amt)
from sales)
10、查找至少有3次销售的业务员名单和销售日期
select emp_name,order_date
from employee a,sales b
where emp_no=sale_id and a.emp_no in
(select sale_id
from sales
group by sale_id
having count(*)>=3)
order by emp_name
11、用存在量词查找没有订货记录的客户名称
select cust_name
from customer a
where not exists
(select *
from sales b
where a.cust_id=b.cust_id)
12、使用左外连接查找每个客户的客户编号、名称、订货日期、订单金额订货日期不要显示时间,日期格式为yyyy-mm-dd按客户编号排序,同一客户再按订单降序排序输出
select a.cust_id,cust_name,convert(char(10),order_date,120),tot_amt
from customer a left outer join sales b on a.cust_id=b.cust_id
order by a.cust_id,tot_amt desc
13、查找16M DRAM的销售情况,要求显示相应的销售员的姓名、性别,销售日期、销售数量和金额,其中性别用男、女表示
select emp_name 姓名, 性别= case a.sex when 'm' then '男'
when 'f' then '女'
else '未'
end,
销售日期= isnull(convert(char(10),c.order_date,120),'日期不详'),
qty 数量, qty*unit_price as 金额
from employee a, sales b, sale_item c,product d
where d.prod_name='16M DRAM' and d.prod_id=c.prod_id and
a.emp_no=b.sale_id and b.order_no=c.order_no
14、查找每个人的销售记录,要求显示销售员的编号、姓名、性别、产品名称、数量、单价、金额和销售日期
select emp_no 编号,emp_name 姓名, 性别= case a.sex when 'm' then '男'
when 'f' then '女'
else '未'
end,
prod_name 产品名称,销售日期= isnull(convert(char(10),c.order_date,120),'日期不详'),
qty 数量, qty*unit_price as 金额
from employee a left outer join sales b on a.emp_no=b.sale_id , sale_item c,product d
where d.prod_id=c.prod_id and b.order_no=c.order_no
15、查找销售金额最大的客户名称和总货款
select cust_name,d.cust_sum
from customer a,
(select cust_id,cust_sum
from (select cust_id, sum(tot_amt) as cust_sum
from sales
group by cust_id ) b
where b.cust_sum =
( select max(cust_sum)
from (select cust_id, sum(tot_amt) as cust_sum
from sales
group by cust_id ) c )
) d
where a.cust_id=d.cust_id
16、查找销售总额少于1000元的销售员编号、姓名和销售额
select emp_no,emp_name,d.sale_sum
from employee a,
(select sale_id,sale_sum
from (select sale_id, sum(tot_amt) as sale_sum
from sales
group by sale_id ) b
where b.sale_sum <1000
) d
where a.emp_no=d.sale_id
17、查找至少销售了3种商品的客户编号、客户名称、商品编号、商品名称、数量和金额
select a.cust_id,cust_name,b.prod_id,prod_name,d.qty,d.qty*d.unit_price
from customer a, product b, sales c, sale_item d
where a.cust_id=c.cust_id and d.prod_id=b.prod_id and
c.order_no=d.order_no and a.cust_id in (
select cust_id
from (select cust_id,count(distinct prod_id) prodid
from (select cust_id,prod_id
from sales e,sale_item f
where e.order_no=f.order_no) g
group by cust_id
having count(distinct prod_id)>=3) h )
18、查找至少与世界技术开发公司销售相同的客户编号、名称和商品编号、商品名称、数量和金额
select a.cust_id,cust_name,d.prod_id,prod_name,qty,qty*unit_price
from customer a, product b, sales c, sale_item d
where a.cust_id=c.cust_id and d.prod_id=b.prod_id and
c.order_no=d.order_no and not exists
(select f.*
from customer x ,sales e, sale_item f
where cust_name='世界技术开发公司' and x.cust_id=e.cust_id and
e.order_no=f.order_no and not exists
( select g.*
from sale_item g, sales h
where g.prod_id = f.prod_id and g.order_no=h.order_no and
h.cust_id=a.cust_id)
)
19、查找表中所有姓刘的职工的工号,部门,薪水
select emp_no,emp_name,dept,salary
from employee
where emp_name like '刘%'
20、查找所有定单金额高于2000的所有客户编号
select cust_id
from sales
where tot_amt>2000
21、统计表中员工的薪水在4000-6000之间的人数
select count(*)as 人数
from employee
where salary between 4000 and 6000
22、查询表中的同一部门的职工的平均工资,但只查询"住址"是"上海市"的员工
select avg(salary) avg_sal,dept
from employee
where addr like '上海市%'
group by dept
23、将表中住址为"上海市"的员工住址改为"北京市"
update employee
set addr like '北京市'
where addr like '上海市'
24、查找业务部或会计部的女员工的基本信息。
select emp_no,emp_name,dept
from employee
where sex='F'and dept in ('业务','会计')
25、显示每种产品的销售金额总和,并依销售金额由大到小输出。
select prod_id ,sum(qty*unit_price)
from sale_item
group by prod_id
order by sum(qty*unit_price) desc
26、选取编号界于'C0001'和'C0004'的客户编号、客户名称、客户地址。
select CUST_ID,cust_name,addr
from customer
where cust_id between 'C0001' AND 'C0004'
27、计算出一共销售了几种产品。
select count(distinct prod_id) as '共销售产品数'
from sale_item
28、将业务部员工的薪水上调3%。
update employee
set salary=salary*1.03
where dept='业务'
29、由employee表中查找出薪水最低的员工信息。
select *
from employee
where salary=
(select min(salary )
from employee )
30、使用join查询客户姓名为"客户丙"所购货物的"客户名称","定单金额","定货日期","电话号码"
select a.cust_id,b.tot_amt,b.order_date,a.tel_no
from customer a join sales b
on a.cust_id=b.cust_id and cust_name like '客户丙'
31、由sales表中查找出订单金额大于"E0013业务员在1996/10/15这天所接每一张订单的金额"的所有订单。
select *
from sales
where tot_amt>all
(select tot_amt
from sales
where sale_id='E0013'and order_date='1996/10/15')
order by tot_amt
32、计算'P0001'产品的平均销售单价
select avg(unit_price)
from sale_item
where prod_id='P0001'
33、找出公司女员工所接的定单
select sale_id,tot_amt
from sales
where sale_id in
(select sale_id from employee
where sex='F')
34、找出同一天进入公司服务的员工
select a.emp_no,a.emp_name,a.date_hired
from employee a
join employee b
on (a.emp_no!=b.emp_no and a.date_hired=b.date_hired)
order by a.date_hired
35、找出目前业绩超过232000元的员工编号和姓名。
select emp_no,emp_name
from employee
where emp_no in
(select sale_id
from sales
group by sale_id
having sum(tot_amt)<232000)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
db2的常用sql
1、数据操作语言(DML:select,delete,insert,update)
<1>查询数据库目录:
db2list db directory
<2>查询数据库中表
db2 list tables 当前用户
db2 list tablesfor all 所有表
db2 list tables for schemaschemaname 指定模式的表
<3>显示表结构
db2 describe tabletablename
<4>插入数据
db2 insert intotablename(字段名,字段名...)values (与字段名一一对应的值)
db2 insert intotablename1(字段1,字段2,字段3...)
select 字段1,字段2,字段3...fromtablename2 + 查询条件
<5>更改表或视图数据
db2 updatetablename/viewname set 字段名1='',字段2='',...+查询条件
<6>删除数据
db2delete from tablename where + 条件
<7>导入数据
db2 "import fromE:\name.txt of del insert into tableName"
db2 "import fromE:\name.ixf of ixf commitcount 5000 insert /create/replace into tableName"
db2 "load clientfrom D:\xx.txt of del insert/replace into tabName"(不需要写日志,但插入前表必须存在;不能createtable)
db2 "load clientfrom D:\xx.txt of del restart/terminate into tabName" 当导入数据出现问题被强行中断时,此表会被加锁,通过此命令可以解锁
<8>导出数据
db2"export to E:\name.txt of del select * from tableName"
db2"export to E:\name.txt of del MODIFIED BY NOCHARDEL select * fromtableName"(导出不带分号的数据)
导出表结构和数据
db2"export to E:\name.ixf of ixf MODIFIED BY NOCHARDEL select * fromtableName"
db2"export to E:\name.ixf of ixf MODIFIED BY NOCHARDEL select * fromtableName fetch first (取数+UNM) rows only"(取固定条数)
导出表结构
db2look -d dbName -e -t tableName -o D:\xxx.sql(path) -i userName -w password
db2look -d dbName -z tabSchema -e -c -i userName -w password -o + 路径名
导出存储过程结构
db2"export to xxx.sql of del select text from syscat.procedures whereprocname='大写存储过程名'"
<9>查询表状态
db2 load query table +tableName
<10>查询当前表数据量(数据入库时)
db2 select count(1) from tabwith ur
<11>修改当前表名、模式名
db2 rename table tab1 to tab2
2、数据定义语言(DDL:create,alter)
<1>创建或删除实例
db2icrt instance_name/db2idrop -finstance_name
linux:db2icrt -u user_id instance_name
<2>创建视图、表、模式
db2 create view/table/schema
创建指定用户的模式
db2 create schema schName AUTHORIZATION userName
db2 create schema AUTHORIZATION userName(没有指定模式名时,模式名隐含为用户名userName)
定义含有缺省值的表
db2 create table tableName(column1 数据类型,column2 数据类型 default '缺省值')
基于已存在的表
db2 create table clone_tablename like tablename
db2 create table clone_tablename as (select * fromtablename) definition only
创建物化查询表(MQT)
create table new_table_name as (select * fromtable_name) data initially deferred refresh deferred;
refresh table new_table_name;
注意:物化表类似一个查询,没有真正形成表,类型显示为Query。但它完全可以当表来用。
创建表并指定其索引表空间
db2 create table(.....) in userspace1 INDEX in userspace2
(userspace1是表所在空间,userspace2是表上索引所在空间)
<3>创建视图
db2 create viewviewname
as select 字段名1,字段名2...fromtable where + 条件
with check option 规定一种约束:通过视图插入或更新的每一行都必须符合视图的定义,如:
create viewemp_view2(empno,empname,deptno) as (select id,name,dept from employee wheredept=10)with check option
当此视图用于更新数据或插入新值时,with checkoption 限制了dept列的输入值
<4>修改表(列,主键,唯一约束,检查约束)
1)添加新列 alter table tablename ADD COLUMN columnname 数据类型
2)添加约束
3)修改表中字段 alter table tablename alter columnname set data type 数据类型
4) 添加主键 alter table tablename add primary key(c1,c2)
<5>删除模式、表、视图
drop schema schName <CASCADE|RESTRICT>
CASCADE(级联)表示删除模式的同时删除该模式中所有的数据库对象
RESTRICT(限制)表示该模式下定义了数据库对象时,限制删除;没有任何数据库对象时才能删除
<6>重新组织表及其索引
重组表数据 reorg table tableName index indexName(根据索引)
重组表索引 reorg indexes all for table tableName
<7>重新收集表及其索引统计信息
runstats on table tableName for indexes all(跑批前重新收集所用表信息可以提高效率)
<8>DB2自动增长主键方法
IDENTITY列
generated always as identity(startwith 1,increment by 1)将一个字段指定为自增长型字段,放在数据类型后。
SEQUENCE对象(序列)
3、数据控制语言(DCL:grant,revoke)
将表的特权授予用户
grant select,update,delete on table tableName to useruserName with grant option
将包特权授予同组
grant control on package packageName on group groupName withgrant option
select emp_no,emp_name
from employee
where emp_no in
(select sale_id
from sales
group by sale_id
having sum(tot_amt)<232000)
DB2数据类型改变函数:char()、varchar()、int()、date()、time()等;
目前DB2 V8也支持to_char和to_date
输入类型为整型,且允许为空的,可以使用COALESCE(inputParameter,0),把空转换成0;
输入参数为字符类型,且是非空非空格的,可以使用COALESCE(inputParameter,'')把NULL转换成'',然后判断函数返回值是否为'';
输入类型为整型,且是非空的,不需要使用COALESCE函数,直接使用IS NULL进行非空判断。
对''值的处理,' '或''在数据库中不是NULL而是空格存在需要使用 = ' '验证
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DB2函数大全
AVG()——
返回一组数值的平均值.
SELECT AVG(SALARY) FROM BSEMPMS;
CORR(),CORRELATION()——
返回一对数值的关系系数.
SELECT CORRELATION(SALARY,BONUS) FROM BSEMPMS;
COUNT()——
返回一组行或值的个数.SELECT COUNT(*) FROM BSEMPMS;
COVAR(),COVARIANCE()——
返回一对数值的协方差.
SELECT COVAR(SALARY,BONUS) FROM BSEMPMS;
MAX()——
返回一组数值中的最大值.
SELECT MAX(SALARY) FROM BSEMPMS;
MIN()——
返回一组数值中的最小值.
SELECT MIN(SALARY) FROM BSEMPMS;
STDDEV()——
返回一组数值的标准偏差.
SELECT STDDEV(SALARY) FROM BSEMPMS;
SUM()——
返回一组数据的和.
SELECT SUM(SALARY) FROM BSEMPMS;
VAR(),VARIANCE()——
返回一组数值的方差.
SELECTVARIANCE(SALARY)FROM BSEMPMS;
ABS(),ABSVAL()——
返回参数的绝对值.
SELECTABS(-3.4)FROM BSEMPMS;
ACOS()——
返回参数的反余弦值.
SELECT ACOS(0.9)FROM BSEMPMS;
ASCII()——
返回整数参数最左边的字符的ASCII码.
SELECT ASCII(
'R'
) FROM BSEMPMS;
ASIN()——
返回用弧度表示的角度的参数的反正弦函数.
SELECT ASIN(0.9) FROM BSEMPMS;
ATAN()——
返回参数的反正切值,该参数用弧度表示的角度的参数.
SELECT ATAN(0.9) FROM BSEMPMS;
ATAN2()——
返回用弧度表示的角度的X和Y坐标的反正切值.
SELECT ATAN2(0.5,0.9) FROM BSEMPMS;
BIGINT()——
返回整型常量中的数字或字符串的64位整数表示
SELECT BIGINT(EMP_NO) FROM BSEMPMS;
CEILING() OR CEIL()——
返回比参数大或等于参数的最小的整数值
CHAR()——
返回日期时间型,字符串,整数,十进制或双精度浮点数的字符串表示.
SELECT CHAR(SALARY,
','
) FROM BSEMPMS;
CHR()——
返回具有由参数指定的ASCII码的字符
SELECT CHAR(167) FROM BSEMPMS;
CONCAT()——
返回两个字符串的连接.
SELECT CONCAT(EMP_NO,EMP_NAM) FROM BSEMPMS;
YEAR()——
返回数值的年部分.
SELECT YEAR(
'2003/01/02'
) FROM BSEMPMS;
VARCHAR()——
返回字符串,日期型,图形串的可变长度的字符串表示
SELECT VARCHAR(EMP_NAM,50) FROM BSEMPMS;
UCASE() OR UPPER()——
返回字符串的大写
TRUNCATE() OR TRUNC()——
从表达式小数点右边的位置开始截断并返回该数值.
SELECT TRUNCATE(345.6789,2) FROM BSEMPMS;
返回345.6700(BSEMPMS表中有多少行,就返回多少个345.6700)
注:截断后,保持原数据的精度不变。原数据是四位小数,截断后还是四位,后面用0补齐
TIME()——
返回一个数值中的时间
SELECT TIME(
'2001-03-19 12:55:55'
) FROM BSEMPMS;
返回12:55;55
SELECT DATE('2001-03-19 12:55: 55' ) FROM BSEMPMS;
返回 2001-03-19
SUBSTR(EXP1,EXP2)——
返回EXP1串自EXP2处开始的子串.
SQRT()——
返回该参数的平方根.
SELECT SQRT(36) FROM BSEMPMS;
SPACE()——
返回由参数指定的长度,包含空格在内的字符串.
SELECT SPACE(10) FROM BSEMPMS;
SECOND()——
返回一个数值的秒部分.
SELECT SECOND(
'18:34:32'
) FROM BSEMPMS;
RTRIM()——
删除字符串尾部的空格.
SELECT RTRIM(
'COMMENT'
) FROM BSEMPMS;
ROUND(EXP1,EXP2)——
返回EXP1小数点右边的第EXP2位置处开始的四舍五入值.
SELECT ROUND(2345.6789,2) FROM BSEMPMS
REPLACE(EXP1,EXP2,EXP3)——
用EXP3替代EXP1中所有的EXP2
SELECT CHAR(REPLACE(
'ROMANDD'
,
'NDD'
,
'CCB'
),10)
FROM BSEMPMS;
REPEAT(EXP1,EXP2)——
返回EXP1重复EXP2次后的字符串.
SELECT CHAR(REPEAT(
'REPEAT'
,3),21) FROM
BSEMPMS;
REAL()——
返回一个数值的单精度浮点数表示.
SELECT REAL(10) FROM BSEMPMS;
RAND()——
返回0和1之间的随机浮点数.
SELECT RAND() FROM BSEMPMS;
POWER(EXP1,EXP2)——
返回EXP1的EXP2次幂.
SELECT POWER(2,5) FROM BSEMPMS;
POSSTR(EXP1,EXP2)——
返回EXP2在EXP1中的位置.
SELECT (
'ABCDEFGH'
,
'D'
) FROM BSEMPMS;
NULLIF(EXP1,EXP2)——
如果EXP1=EXP2,则为NULL,否则为EXP1
NODENUMBER()——
返回行的分区号.
SELECT NODENUMBER(EMP_NO) FROM BSEMPMS;
MONTH()——
返回一个数值的月部分.
SELECT MONTH(
'2003/10/20'
) FROM BSEMPMS;
MOD(EXP1,EXP2)——
返回EXP1除以EXP2的余数.
SELECT MOD(20,8) FROM BSEMPMS
MINUTE()——
返回一个数值的分钟部分.
SELECT MINUTE(
'18:34:23'
) FROM BSEMPMS;
LTRIM()——
删除字符串前面的空格.
SELECT LTRIM(
'CDDD'
) FROM BSEMPMS;
HOUR()——
返回一个数值的小时部分.
SELECT HOUR(
'18:34:23'
) FROM BSEMPMS;
DOUBLE()——
如果参数是一个数字表达式,返回与其相对应的浮点数,如果参数是字符串表达式,则返回该数的字符串表达式.
SELECT DOUBLE(
'5678'
) FROM BSEMPMS;
EXP()——
返回参数的指数函数.
SELECT EXP(2) FROM BSEMPMS;
FLOAT()——
返回一个数的浮点表示.
SELECT FLOAT(789) FROM BSEMPMS;
FLOOR()——
返回小于或等于参数的最大整数.
SLECT FLOOR(88.93) FROM BSEMPMS;
HEX()——
返回一个表示为字符串的值的16进制表示.
SELECT HEX(16) FROM BSEMPMS;
DAYNAME——
返回一个大小写混合的字符串,对于参数的日部分,用星期表示这一天的名称(例如,Friday)。
DAYOFWEEK——
返回参数中的星期几,用范围在1-7 的整数值表示,其中1 代表星期日。
DAYOFWEEK_ISO——
返回参数中的星期几,用范围在1-7 的整数值表示,其中1 代表星期一。
DAYOFYEAR——
返回参数中一年中的第几天,用范围在1-366 的整数值表示。
DAYS——
返回日期的整数表示。
JULIAN_DAY——
返回从公元前4712 年1 月1 日(儒略日历的开始日期)到参数中指定日期值之间的天数,用整数值表示。
MIDNIGHT_SECONDS——
返回午夜和参数中指定的时间值之间的秒数,用范围在0 到86400 之间的整数值表示。
MONTHNAME——
对于参数的月部分的月份,返回一个大小写混合的字符串(例如,January)。
TIMESTAMP_ISO——
根据日期、时间或时间戳记参数而返回一个时间戳记值。
TIMESTAMP_FORMAT——
从已使用字符模板解释的字符串返回时间戳记。
TIMESTAMPDIFF——
根据两个时间戳记之间的时差,返回由第一个参数定义的类型表示的估计时差。
TO_CHAR——
返回已用字符模板进行格式化的时间戳记的字符表示。
TO_CHAR 是VARCHAR_FORMAT 的同义词。
TO_DATE——
从已使用字符模板解释过的字符串返回时间戳记。
TO_DATE 是TIMESTAMP_FORMAT 的同义词。
WEEK——
返回参数中一年的第几周,用范围在1-54 的整数值表示。以星期日作为一周的开始。
WEEK_ISO——
返回参数中一年的第几周,用范围在1-53 的整数值表示。
要使当前时间或当前时间戳记调整到GMT/CUT,则把当前的时间或时间戳记减去当前时区寄存器:
current time - current timezone
current timestamp - current timezone
给定了日期、时间或时间戳记,则使用适当的函数可以单独抽取出(如果适用的话)年、月、日、时、分、秒及微秒各部分:
YEAR (current timestamp)
MONTH (current timestamp)
DAY (current timestamp)
HOUR (current timestamp)
MINUTE (current timestamp)
SECOND (current timestamp)
MICROSECOND (current timestamp)
因为没有更好的术语,所以您还可以使用英语来执行日期和时间计算:
current date + 1 YEAR
current date + 3 YEARS + 2 MONTHS + 15 DAYS
current time + 5 HOURS - 3 MINUTES + 10 SECONDS
从时间戳记单独抽取出日期和时间也非常简单:
DATE (current timestamp)
TIME (current timestamp)
而以下示例描述了如何获得微秒部分归零的当前时间戳记:
CURRENT TIMESTAMP - MICROSECOND (current timestamp) MICROSECONDS
如果想将日期或时间值与其它文本相衔接,那么需要先将该值转换成字符串。为此,只要使用CHAR() 函数:
char
(current date)
char
(current time)
char
(current date + 12 hours)
要将字符串转换成日期或时间值,可以使用:
TIMESTAMP (
'2002-10-20-12.00.00.000000'
)
TIMESTAMP (
'2002-10-20 12:00:00'
)
DATE (
'2002-10-20'
)
DATE (
'10/20/2002'
)
TIME (
'12:00:00'
)
TIME (
'12.00.00'
)
TIMESTAMP()、DATE() 和TIME() 函数接受更多种格式。上面几种格式只是示例,我将把它作为一个练习,让读者自己去发现其它格式。
有时,您需要知道两个时间戳记之间的时差。为此,DB2 提供了一个名为TIMESTAMPDIFF() 的内置函数。但该函数返回的是近似值,因为它不考虑闰年,而且假设每个月只有30 天。以下示例描述了如何得到两个日期的近似时差:
timestampdiff (<n>,
char
(
timestamp(
'2002-11-30-00.00.00'
)-
timestamp(
'2002-11-08-00.00.00'
)))
对于<n>,可以使用以下各值来替代,以指出结果的时间单位:
1 = 秒的小数部分
2 = 秒
4 = 分
8 = 时
16 = 天
32 = 周
64 = 月
128 = 季度
256 = 年
当日期很接近时使用timestampdiff() 比日期相差很大时精确。如果需要进行更精确的计算,可以使用以下方法来确定时差(按秒计):
(DAYS(t1) - DAYS(t2)) * 86400 +
(MIDNIGHT_SECONDS(t1) - MIDNIGHT_SECONDS(t2))
为方便起见,还可以对上面的方法创建SQL 用户定义的函数:
CREATE FUNCTION secondsdiff(t1 TIMESTAMP, t2 TIMESTAMP)
RETURNS INT
RETURN (
(DAYS(t1) - DAYS(t2)) * 86400 +
(MIDNIGHT_SECONDS(t1) - MIDNIGHT_SECONDS(t2))
)
@
如果需要确定给定年份是否是闰年,以下是一个很有用的SQL 函数,您可以创建它来确定给定年份的天数:
CREATE FUNCTION daysinyear(yr INT)
RETURNS INT
RETURN (CASE (mod(yr, 400)) WHEN 0 THEN 366 ELSE
CASE (mod(yr, 4)) WHEN 0 THEN
CASE (mod(yr, 100)) WHEN 0 THEN 365 ELSE 366 END
ELSE 365 END
END)@
最后,以下是一张用于日期操作的内置函数表。它旨在帮助您快速确定可能满足您要求的函数,但未提供完整的参考。有关这些函数的更多信息,请参考SQL 参考大全。
SQL 日期和时间函数
DAYNAME 返回一个大小写混合的字符串,对于参数的日部分,用星期表示这一天的名称(例如,Friday)。
DAYOFWEEK 返回参数中的星期几,用范围在1-7 的整数值表示,其中1 代表星期日。
DAYOFWEEK_ISO 返回参数中的星期几,用范围在1-7 的整数值表示,其中1 代表星期一。
DAYOFYEAR 返回参数中一年中的第几天,用范围在1-366 的整数值表示。
DAYS 返回日期的整数表示。
JULIAN_DAY 返回从公元前4712 年1 月1 日(儒略日历的开始日期)到参数中指定日期值之间的天数,用整数值表示。
MIDNIGHT_SECONDS 返回午夜和参数中指定的时间值之间的秒数,用范围在0 到86400 之间的整数值表示。
MONTHNAME 对于参数的月部分的月份,返回一个大小写混合的字符串(例如,January)。
TIMESTAMP_ISO 根据日期、时间或时间戳记参数而返回一个时间戳记值。
TIMESTAMP_FORMAT 从已使用字符模板解释的字符串返回时间戳记。
TIMESTAMPDIFF 根据两个时间戳记之间的时差,返回由第一个参数定义的类型表示的估计时差。
TO_CHAR 返回已用字符模板进行格式化的时间戳记的字符表示。TO_CHAR 是VARCHAR_FORMAT 的同义词。
TO_DATE 从已使用字符模板解释过的字符串返回时间戳记。TO_DATE 是TIMESTAMP_FORMAT 的同义词。
WEEK 返回参数中一年的第几周,用范围在1-54 的整数值表示。以星期日作为一周的开始。
WEEK_ISO 返回参数中一年的第几周,用范围在1-53 的整数值表示。
db2日期操作函数
db2日期操作函数
当前时间:
CURRENT DATE
CURRENT TIME
CURRENT TIMESTAMP
获取时间的年、月、日、时、分、秒及微秒各部分:
YEAR (current timestamp)
MONTH (current timestamp)
DAY (current timestamp)
HOUR (current timestamp)
MINUTE (current timestamp)
SECOND (current timestamp)
MICROSECOND (current timestamp)
时间的加减:
current date + 1 YEAR
current date + 3 YEARS + 2 MONTHS + 15 DAYS
current time + 5 HOURS - 3 MINUTES + 10 SECONDS
要计算两个日期之间的天数,您可以对日期作减法:
days (current date) - days (date(
'1999-10-22'
))
时间转换为字符串:
char
(current date)
字符串转换成时间:
TIMESTAMP()、DATE() 和TIME(), 支持的字符串格式需要参考帮助, 没有oracle的to_date灵活
Data(
'2005-01-01'
)
日期和时间的一些函数:
DAYNAME 返回一个大小写混合的字符串,对于参数的日部分,用星期表示这一天的名称(例如,Friday)。
DAYOFWEEK 返回参数中的星期几,用范围在1-7 的整数值表示,其中1 代表星期日。
DAYOFWEEK_ISO 返回参数中的星期几,用范围在1-7 的整数值表示,其中1 代表星期一。
DAYOFYEAR 返回参数中一年中的第几天,用范围在1-366 的整数值表示。
DAYS 返回日期的整数表示。
JULIAN_DAY 返回从公元前4712 年1 月1 日(儒略日历的开始日期)到参数中指定日期值之间的天数,用整数值表示。
MIDNIGHT_SECONDS 返回午夜和参数中指定的时间值之间的秒数,用范围在0 到86400 之间的整数值表示。
MONTHNAME 对于参数的月部分的月份,返回一个大小写混合的字符串(例如,January)。
TIMESTAMP_ISO 根据日期、时间或时间戳记参数而返回一个时间戳记值。
TIMESTAMP_FORMAT 从已使用字符模板解释的字符串返回时间戳记。
TIMESTAMPDIFF 根据两个时间戳记之间的时差,返回由第一个参数定义的类型表示的估计时差。
TO_CHAR 返回已用字符模板进行格式化的时间戳记的字符表示。TO_CHAR 是VARCHAR_FORMAT 的同义词。
TO_DATE 从已使用字符模板解释过的字符串返回时间戳记。TO_DATE 是TIMESTAMP_FORMAT 的同义词。
WEEK 返回参数中一年的第几周,用范围在1-54 的整数值表示。以星期日作为一周的开始。
WEEK_ISO 返回参数中一年的第几周,用范围在1-53 的整数值表示。
因为没有更好的术语,所以您还可以使用英语来执行日期和时间计算:
current date + 1 YEAR
current date + 3 YEARS + 2 MONTHS + 15 DAYS
current time + 5 HOURS - 3 MINUTES + 10 SECONDS
要计算两个日期之间的天数,您可以对日期作减法,如下所示:
days (current date) - days (date(
'1999-10-22'
))
而以下示例描述了如何获得微秒部分归零的当前时间戳记:
CURRENT TIMESTAMP - MICROSECOND (current timestamp) MICROSECONDS
如果想将日期或时间值与其它文本相衔接,那么需要先将该值转换成字符串。为此,只要使用CHAR() 函数:
char
(current date)
char
(current time)
char
(current date + 12 hours)
要将字符串转换成日期或时间值,可以使用:
TIMESTAMP (
'2002-10-20-12.00.00.000000'
)
TIMESTAMP (
'2002-10-20 12:00:00'
)
DATE (
'2002-10-20'
)
DATE (
'10/20/2002'
)
TIME (
'12:00:00'
)
TIME (
'12.00.00'
)
如果你想将当前日期格式转化成定制的格式(比如‘yyyymmdd’),那又该如何去做呢?按照我的经验,最好的办法就是编写一个自己定制的格式化函数。
下面是这个UDF 的代码:
create function ts_fmt(TS timestamp, fmt varchar(20))
returns varchar(50)
return
with tmp (dd,mm,yyyy,hh,mi,ss,nnnnnn)
as
(
select
substr( digits (day(TS)),9),
substr( digits (month(TS)),9) ,
rtrim(
char
(year(TS))) ,
substr( digits (hour(TS)),9),
substr( digits (minute(TS)),9),
substr( digits (second(TS)),9),
rtrim(
char
(microsecond(TS)))
from sysibm.sysdummy1
)
select
case
fmt
when
'yyyymmdd'
then yyyy || mm || dd
when
'mm/dd/yyyy'
then mm ||
'/'
|| dd ||
'/'
|| yyyy
when
'yyyy/dd/mm hh:mi:ss'
then yyyy ||
'/'
|| mm ||
'/'
|| dd ||
' '
||
hh ||
':'
|| mi ||
':'
|| ss
when
'nnnnnn'
then nnnnnn
else
'date format '
|| coalesce(fmt,
' '
) ||
' not recognized.'
end
from tmp
建议找一个DB2基础的东东看看,比在这里问快多了,虽然别人找的时候也快!!
SQL 日期和时间函数
DAYNAME 返回一个大小写混合的字符串,对于参数的日部分,用星期表示这一天的名称(例如,Friday)。
DAYOFWEEK 返回参数中的星期几,用范围在1-7 的整数值表示,其中1 代表星期日。
DAYOFWEEK_ISO 返回参数中的星期几,用范围在1-7 的整数值表示,其中1 代表星期一。
DAYOFYEAR 返回参数中一年中的第几天,用范围在1-366 的整数值表示。
DAYS 返回日期的整数表示。
JULIAN_DAY 返回从公元前4712 年1 月1 日(儒略日历的开始日期)到参数中指定日期值之间的天数,用整数值表示。
MIDNIGHT_SECONDS 返回午夜和参数中指定的时间值之间的秒数,用范围在0 到86400 之间的整数值表示。
MONTHNAME 对于参数的月部分的月份,返回一个大小写混合的字符串(例如,January)。
TIMESTAMP_ISO 根据日期、时间或时间戳记参数而返回一个时间戳记值。
TIMESTAMP_FORMAT 从已使用字符模板解释的字符串返回时间戳记。
TIMESTAMPDIFF 根据两个时间戳记之间的时差,返回由第一个参数定义的类型表示的估计时差。
TO_CHAR 返回已用字符模板进行格式化的时间戳记的字符表示。TO_CHAR 是VARCHAR_FORMAT 的同义词。
TO_DATE 从已使用字符模板解释过的字符串返回时间戳记。TO_DATE 是TIMESTAMP_FORMAT 的同义词。
WEEK 返回参数中一年的第几周,用范围在1-54 的整数值表示。以星期日作为一周的开始。
WEEK_ISO 返回参数中一年的第几周,用范围在1-53 的整数值表示。
--------------------------------------------------------------------------------
mymm 回复于:2003-08-04 14:22:38
Example:
YEAR (current timestamp)
MONTH (current timestamp)
DAY (current timestamp)
HOUR (current timestamp)
MINUTE (current timestamp)
SECOND (current timestamp)
MICROSECOND (current timestamp)
引用:
mymm 回复于:2003-08-08 17:01:11
DAYS函数返回日期的整数表示。
要计算两个日期之间的天数,可以:
days (current date) - days (date(
'2000-9-1'
))
要使用 SQL 获得当前的日期、时间及时间戳记,请参考适当的DB2 寄存器:
SELECT current date FROM sysibm.sysdummy1
SELECT current time FROM sysibm.sysdummy1
SELECT current timestamp FROM sysibm.sysdummy1
sysibm.sysdummy1 表是一个特殊的内存中的表,用它可以发现如上面演示的DB2 寄存器的值。您也可以使用关键字VALUES 来对寄存器或表达式求值。例如,在DB2 命令行处理器(Command Line Processor,CLP)上,以下SQL 语句揭示了类似信息:
VALUES current date
VALUES current time
VALUES current timestamp
在余下的示例中,我将只提供函数或表达式,而不再重复SELECT ... FROM sysibm.sysdummy1 或使用VALUES 子句。
要使当前时间或当前时间戳记调整到GMT/CUT,则把当前的时间或时间戳记减去当前时区寄存器:
current time - current timezone
current timestamp - current timezone
给定了日期、时间或时间戳记,则使用适当的函数可以单独抽取出(如果适用的话)年、月、日、时、分、秒及微秒各部分:
YEAR (current timestamp)
MONTH (current timestamp)
DAY (current timestamp)
HOUR (current timestamp)
MINUTE (current timestamp)
SECOND (current timestamp)
MICROSECOND (current timestamp)
从时间戳记单独抽取出日期和时间也非常简单:
DATE (current timestamp)
TIME (current timestamp)
因为没有更好的术语,所以您还可以使用英语来执行日期和时间计算:
current date + 1 YEAR
current date + 3 YEARS + 2 MONTHS + 15 DAYS
current time + 5 HOURS - 3 MINUTES + 10 SECONDS
要计算两个日期之间的天数,您可以对日期作减法,如下所示:
days (current date) - days (date(
'1999-10-22'
))
而以下示例描述了如何获得微秒部分归零的当前时间戳记:
CURRENT TIMESTAMP - MICROSECOND (current timestamp) MICROSECONDS
如果想将日期或时间值与其它文本相衔接,那么需要先将该值转换成字符串。为此,只要使用CHAR() 函数:
char
(current date)
char
(current time)
char
(current date + 12 hours)
要将字符串转换成日期或时间值,可以使用:
TIMESTAMP (
'2002-10-20-12.00.00.000000'
)
TIMESTAMP (
'2002-10-20 12:00:00'
)
DATE (
'2002-10-20'
)
DATE (
'10/20/2002'
)
TIME (
'12:00:00'
)
TIME (
'12.00.00'
)
TIMESTAMP()、DATE() 和TIME() 函数接受更多种格式。上面几种格式只是示例,我将把它作为一个练习,让读者自己去发现其它格式。
select emp_no,emp_name
from employee
where emp_no in
(select sale_id
from sales
group by sale_id
having sum(tot_amt)<232000)
- db2分页的sql
- DB2的sql
- DB2的sql函数
- db2,sqlserver的更新sql
- DB2常用SQL的写法
- DB2常用SQL的写法
- DB2一些SQL的用法
- DB2的SQL函数大全
- DB2的基本sql操作
- DB2 SQL语句的优化
- DB2的一些SQL命令
- DB2 与 DB2 之间的远程 SQL 复制
- DB2 与 DB2 之间的远程 SQL 复制
- DB2 sql
- db2 SQL
- DB2 SQL
- db2 sql
- db2 SQL
- Android——build.prop 解析
- 设计模式12--代理模式(Proxy)
- cocos2d-x绑lua的开发环境
- Button或者ImageButton的背景设为透明或者半透明--透明度
- zebra命令行编写(二)
- DB2的sql
- Ubuntu 12.04 安装mysql及mysql-python
- Android实现任务管理器
- 使用logwrapper调试Android程序
- 两个队列模拟一个堆栈
- typeof关键字
- cocos2dx 常用函数和宏定义
- MFC 从文本框里读数据
- Windows Server 2012 Hyper-V虚拟化部署与管理指南