1、查找员工的编号、姓名、部门和出生日期,如果出生日期为空值,显示日期不详,并按部门排序输出,日期格式为yyyy-mm-dd。
复制代码代码如下:
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)
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
一、字符转换函数
1、ASCII()
返回字符表达式最左端字符的ASCII 码值。在ASCII()函数中,纯数字的字符串可不用‘’括起来,但含其它字符的字符串必须用‘’括起来使用,否则会出错。
2、CHAR()
将ASCII 码转换为字符。如果没有输入0 ~ 255 之间的ASCII 码值,CHAR() 返回NULL 。
3、LOWER()和UPPER()
LOWER()将字符串全部转为小写;UPPER()将字符串全部转为大写。
4、STR()
把数值型数据转换为字符型数据。 STR (<float_expression>[,length[, <decimal>]]) length 指定返回的字符串的长度,decimal 指定返回的小数位数。如果没有指定长度,缺省的length 值为10, decimal 缺省值为0。当length 或者decimal 为负值时,返回NULL;当length 小于小数点左边(包括符号位)的位数时,返回length 个*;先服从length ,再取decimal ;当返回的字符串位数小于length ,左边补足空格。
二、去空格函数
1、LTRIM() 把字符串头部的空格去掉。
2、RTRIM() 把字符串尾部的空格去掉。
三、取子串函数
1、left()
LEFT (<character_expression>, <integer_expression>) 返回character_expression 左起 integer_expression 个字符。
2、RIGHT()
RIGHT (<character_expression>, <integer_expression>) 返回character_expression 右起 integer_expression 个字符。
3、SUBSTRING()
SUBSTRING (<expression>, <starting_ position>, length) 返回从字符串左边第starting_ position 个字符起length个字符的部分。
四、字符串比较函数
1、CHARINDEX()
返回字符串中某个指定的子串出现的开始位置。 CHARINDEX (<’substring_expression’>, <expression>) 其中substring _expression 是所要查找的字符表达式,expression 可为字符串也可为列名表达式。如果没有发现子串,则返回0 值。此函数不能用于TEXT 和IMAGE 数据类型。
2、PATINDEX()
返回字符串中某个指定的子串出现的开始位置。 PATINDEX (<’%substring _expression%’>, <column_ name>)其中子串表达式前后必须有百分号“%”否则返回值为0。与CHARINDEX 函数不同的是,PATINDEX函数的子串中可以使用通配符,且此函数可用于CHAR、 VARCHAR 和TEXT 数据类型。
五、字符串操作函数
1、QUOTENAME()
返回被特定字符括起来的字符串。 QUOTENAME (<’character_expression’>[, quote_ character]) 其中quote_ character 标明括字符串所用的字符,缺省值为“[]”。
2、REPLICATE()
返回一个重复character_expression 指定次数的字符串。 REPLICATE (character_expression integer_expression) 如果integer_expression 值为负值,则返回NULL 。
3、REVERSE()
将指定的字符串的字符排列顺序颠倒。 REVERSE (<character_expression>) 其中character_expression 可以是字符串、常数或一个列的值。
4、REPLACE()
返回被替换了指定子串的字符串。 REPLACE (<string_expression1>, <string_expression2>, <string_expression3>) 用string_expression3 替换在string_expression1 中的子串string_expression2。
4、SPACE()
返回一个有指定长度的空白字符串。 SPACE (<integer_expression>) 如果integer_expression 值为负值,则返回NULL 。
5、STUFF()
用另一子串替换字符串指定位置、长度的子串。 STUFF (<character_expression1>, <start_ position>, <length>,<character_expression2>) 如果起始位置为负或长度值为负,或者起始位置大于character_expression1 的长度,则返回NULL 值。如果length 长度大于character_expression1 中 start_ position 以右的长度,则character_expression1 只保留首字符。
六、数据类型转换函数
1、CAST()
CAST (<expression> AS <data_ type>[ length ])
2、CONVERT()
CONVERT (<data_ type>[ length ], <expression> [, style])
1)data_type为SQL Server系统定义的数据类型,用户自定义的数据类型不能在此使用。
2)length用于指定数据的长度,缺省值为30。
3)把CHAR或VARCHAR类型转换为诸如INT或SAMLLINT这样的INTEGER类型、结果必须是带正号或负号的数值。
4)TEXT类型到CHAR或VARCHAR类型转换最多为8000个字符,即CHAR或VARCHAR数据类型是最大长度。
5)IMAGE类型存储的数据转换到BINARY或VARBINARY类型,最多为8000个字符。
6)把整数值转换为MONEY或SMALLMONEY类型,按定义的国家的货币单位来处理,如人民币、美元、英镑等。
7)BIT类型的转换把非零值转换为1,并仍以BIT类型存储。
8)试图转换到不同长度的数据类型,会截短转换值并在转换值后显示“+”,以标识发生了这种截断。
9)用CONVERT() 函数的style 选项能以不同的格式显示日期和时间。style 是将DATATIME 和SMALLDATETIME 数据转换为字符串时所选用的由SQL Server 系统提供的转换样式编号,不同的样式编号有不同的输出格式。
七、日期函数
1、day(date_expression)
返回date_expression中的日期值
2、month(date_expression)
返回date_expression中的月份值3、year(date_expression)返回date_expression中的年份值
4、DATEADD()
DATEADD (<datepart>, <number>, <date>) 返回指定日期date 加上指定的额外日期间隔number 产生的新日期。
5、DATEDIFF()
DATEDIFF (<datepart>, <date1>, <date2>) 返回两个指定日期在datepart 方面的不同之处,即date2 超过date1的差距值,其结果值是一个带有正负号的整数值。
6、DATENAME()
DATENAME (<datepart>, <date>)以字符串的形式返回日期的指定部分此部分。由datepart 来指定。
7、DATEPART()
DATEPART (<datepart>, <date>) 以整数值的形式返回日期的指定部分。此部分由datepart 来指定。DATEPART (dd, date) 等同于DAY (date) DATEPART (mm, date) 等同于MONTH (date)DATEPART (yy, date) 等同于YEAR (date)
8、GETDATE()
以DATETIME 的缺省格式返回系统当前的日期和时间
9、
Oracle数据类型改变函数:to_char()、to_date()、to_number()等;
如果仅仅取年,月,日等,可以用to_char(sysdate, 'YYYY'),to_char('MM') ,to_char('DD')取得。
只取年月日TRUNC(SYSDATE),取时分秒TO_CHAR(SYSDATE,'HH24:MI:SS')。
DB2数据类型改变函数:char()、varchar()、int()、date()、time()等;
取得年,月,日等的写法: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)。
Char()是定长字符串(1-255),varchar()为非定长字符串(1-32672)日期,时间形态变为字符形态: char(current date),char(current time)
将字符串转换成日期或时间形态:TIMESTAMP('2002-10-2012:00:00'),DATE('2002-10-20'),DATE('10/20/2002'),TIME('12:00:00')
目前DB2 V8也支持to_char和to_date
10. 判断非空字符串
输入参数为字符类型,且允许为空的,可以使用COALESCE(inputParameter,'')把NULL转换成'';
输入类型为整型,且允许为空的,可以使用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)