sqlquery2.sql

一个简单的网上书店

/*select [name],address,phone,linkman from publishers where id=1
select r.id,bookid,r.userid,u.[name],comment,date from readercomments as r,users as u where bookid=2
and r.userid=u.id
insert into readercomments (bookid,userid,comment) values
(1,1,'好书真的是好书')
update readercomments set bookid=2,userid=1,comment='奇书' where id=1

select * from readercomments
select * from users

select * from orders

insert into orders (userid,totalprice) values (1,200)
update orders set state=0 where id=1
delete from orders where id=1

select o.id,o.orderdate,o.userid,u.[name],o.Totalprice,o.state 
from orders as o,users as u where o.userid=u.id

select top 1 o.orderid,o.bookid,o.quantity,o.unitprice,o.price 
from orderbook as o,books as b where o.bookid=b.id and o.orderid=1 and o.id not in 
(select top 0 o.id from orderbook where o.orderid=1)


insert into orderbook (orderid,bookid,quantity,unitprice,price)
values (1,1,1,20,20)



delete from orderbook where id=1

select * from books

insert into books 
(Title,Author,PublishDate,ISBN,WordsCount,UnitPrice,ContentDescription,AuthorDescription,EditorComment,TOC,TypeId,PublisherId,Img,SimplePY)
values
('程序员','MM',2007-03-12,12000,5,20,'讲述作为程序员的 一些建议','有多年编程经验的专家','zero','1 作为程序员的准备',1,1,'120.img','ZXY')

select 
b.id,b.Title,b.Author,b.PublishDate,b.ISBN,b.WordsCount,b.UnitPrice,
b.ContentDescription,b.AuthorDescription,b.EditorComment,b.TOC,b.Clicks,
b.TypeId,bt.[name],b.PublisherId,p.[name],b.Img,b.SimplePY,b.pj
from books as b,booktype as bt,Publishers as p
where b.typeid=bt.id and b.PublisherId=p.id 
and b.id not in 
(select top 0 b.id from books)
group by 
b.id,b.Title,b.Author,b.PublishDate,b.ISBN,b.WordsCount,b.UnitPrice,
b.ContentDescription,b.AuthorDescription,b.EditorComment,b.TOC,b.Clicks,
b.TypeId,bt.[name],b.PublisherId,p.[name],b.Img,b.SimplePY,b.pj
having clicks in (select max(b.id) from books)
*/

select u.id,u.[name],ur.[name]
from users as u,userRoles as ur
where 
u.UserRoleId=ur.id and loginId='111' and loginPwd='111'

select * from users
select * from userRoles