IT博客汇 | 数据库练习

数据库练习

abilitytao发表于 2014-10-15 15:39:00

计算两日期间隔天数
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

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

abilitytao 2014-10-15 23:39 发表评论