The Fourth Dimension Space

枯叶北风寒,忽然年以残,念往昔,语默心酸。二十光阴无一物,韶光贱,寐难安; 不畏形影单,道途阻且慢,哪曲折,如渡飞湍。斩浪劈波酬壮志,同把酒,共言欢! -如梦令

数据库练习

计算两日期间隔天数
values (days(current date) - days('2014-09-01'))

去出某一字符串的的一个单词,空格为分隔符
select substr(title,1,instr(title,' ')-1) from titles

将字符串的一个空格替换为'--hello--'
select substr(title,1,locate(' ',title)-1)  || '--hello--' ||  substr(title,locate(' ',title)+1) from titles

db2生成随机数
select case when price is null then INT(rand()*100) else price end from titles

两日期相差多少月,64代表月。参数可以更改求出周,小时等等。
select TIMESTAMPDIFF(64,char(TIMESTAMP(CURRENT DATE) - TIMESTAMP(pubdate)) )from titles

有关db2日期操作可参考此博客。
http://www.cnblogs.com/wanghonghu/archive/2012/05/25/2518604.html

建立数据表,有generated always列
create table t1
(
c1 char(30),
c2 double,
c3 int not null generated always as identity
      (start with 100, increment by 5)
)
第一次更新用insert into t1(c1,c2,c3) values('123',34,default)
以后用 insert into t1(c1,c2) values('hi',45)

如果是generated by default
create table t1
(
c1 char(30),
c2 double,
c3 int not null generated by default as identity
(start with 100, increment by 5)
)
如果插入时只有两个参数,就按默认的来
如果三个参数,就是插入的那个数
默认生成的第三个数可以修改,系统会有记录,即使修改了也会从上一次的默认位置开始叠加。不会再回到100.

--select * from authors

--select au_lname, au_fname from authors where state ='CA'

--select * from publishers

--select distinct state from publishers

--select *from titles

--select * from titles where price is null

-- select case when price is null then 0 else price end from titles-- it works!!!!!!!!!!

-- 4 Functions

--select varchar_format(current date - date('2011-01-01') + date, 'DD-MM-YY') as newDate,

-- varchar_format(date,'DD-MM-YY') as olddate from sales

--values current date - date('2014-01-01') + date('2014-01-02')

--select *from roysched

--select hex(lorange) from roysched

--select * from titles where price in (20,19) -- in one shot ZANZANZANZANZAN!!!

select case type when 'business' then Title else 'other' end from titles 联系条件语句并显示成不同的属性

-----------------------------------------------------------------------------------------------

--select * from authors

--select au_lname, au_fname from authors where state ='CA'

--select * from publishers

--select distinct state from publishers

--select *from titles

--select * from titles where price is null

-- select case when price is null then 0 else price end from titles-- it works!!!!!!!!!!

-- 4 Functions

--select varchar_format(current date - date('2011-01-01') + date, 'DD-MM-YY') as newDate,

-- varchar_format(date,'DD-MM-YY') as olddate from sales

--values current date - date('2014-01-01') + date('2014-01-02')

--select *from roysched

--select hex(lorange) from roysched

--select * from titles where price in (20,19) -- in one shot ZANZANZANZANZAN!!!

--select case type when 'business' then Title else 'other' end from titles

--select syscolumns.length from syscolumns where syscolumns.id = 'titles' and syscolumns.name = 'title'

--5 Grouping

--select (days(max(pubdate)) - days(min(pubdate)) )/365 from titles

--select title from titles order by length(title)

--select *from titles

--select count(*) from authors group by city

--select title from titles where length(title) = (select max(length(title)) from titles)

--select coalesce(price,0) from titles -- yuan lai bushi yong is null shi yong zhe ge a!!!!

--select length(price) from titles

/*

select title from titles where length(title) >= all

(

select length(title) from titles

)

*/

--select pubdate from titles

/*

select title,pubdate from titles where pubdate >= all

(

select pubdate from titles

)

*/

--select count(*) from sales group by stor_num

--select * from sales

--select * from publishers

--select * from titles as a inner join publishers as b on a.pub_id = b.pub_id

--select title,city,a.pub_id from titles as a inner join publishers as b on a.pub_id = b.pub_id where city not in ('Boston')

--select * from psales

/*

select b.ord_num,b.discount,d.discounttype from psales as a inner join salesdetail as b on a.stor_id = b. stor_id and a.ord_num = b.ord_num

inner join stores as c on a.stor_id = c.stor_id inner join discounts as d on c.stor_id = d.stor_id order by b.ord_num

*/

--select * from authors

--select * from publishers

--select * from authors as a inner join publishers as b on a.city = b.city

--select sum(qty) from salesdetail

--select pub_name, count(distinct type) from publishers as a inner join titles as b on a.pub_id = b.pub_id group by pub_name

--select *from titleauthor

--7 Sub-Queries

/*

select pub_name from publishers where pub_id in

(

select pub_id from titles

)

*/

--select * from publishers

--nice !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

/*

select type,title,t1.price

from titles as t1

where price > (select avg(price) from titles as t2 where t2.type = t1.type)

*/

-- nice !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 

select title,t1.price

from titles as t1

where price > all(select coalesce(price,0) from titles as t2 where t2.type != t1.type)

 

--select * from titles

--select t1.type,t1.title,t1.price,(select avg(price) from titles as t2 where t2.type = t1.type)

--from titles as t1

--where price > (select avg(price) from titles as t2 where t2.type = t1.type)

 

--select avg(price) from titles

--select abs(13.93750000000000000000000000- price) from titles

--select coalesce(abs((select avg(price) from titles) - price),0) from titles


--select count(*) as ANS from SB_AUCTION where (days(enddate) - days(startdate) < 10)

/*

select description, enddate, reserve from SB_Auction inner join sb_item on SB_Auction.itemid = sb_item.itemid

where (date('2011-02-20') >= startdate and date('2011-02-20') <= enddate) order by reserve desc

*/

 

--select * from sb_bid

--select name from (sb_bid as a inner join sb_auction as b on a.bidder = b.userid) inner join sb_user as c on b.userid = c.userid group by name

--select * from authors

--select newA.name from

--(

--select name,c.itemid from (sb_user as a inner join sb_auction as b on a.userid = b.userid)

-- inner join sb_item as c on b.itemid = c.itemid

--) AS newA

--select * from sb_auction

/*

create view weitaol_view

as

select c.auctionid, max(amount) as winning_bid from sb_auction as a inner join sb_user as b on a.winnerid = b.userid

inner join sb_bid as c on a.auctionid = c.auctionid where a.state = 'sold' group by c.auctionid

*/

--select * from weitaol_view

 

select enddate, description,coalesce(name,'==') as name ,coalesce(c.winning_bid,0) as winning_bid

from sb_auction as a inner join sb_item as b on a.itemid = b.itemid

left join sb_user on a.winnerid = sb_user.userid

left join weitaol_view as c on a.auctionid = c.auctionid

/*

select * from sb_auction

*/




posted on 2014-10-15 23:39 abilitytao 阅读(256) 评论(0)  编辑 收藏 引用


只有注册用户登录后才能发表评论。
【推荐】超50万行VC++源码: 大型组态工控、电力仿真CAD与GIS源码库
网站导航: 博客园   IT新闻   BlogJava   知识库   博问   管理