表的建立,并插入数据
create table T_Product
(
P_Id int primary key identity,
p_Name varchar(32) not null
)
create table T_Order
(
O_Id int primary key identity,
P_Id int not null,
O_Numb int not null,
O_Date date not null
)
insert into T_Product(p_Name) values('螺丝'),('钉子'),('漏电保护器'),('有机玻璃')
insert into T_Order(P_Id,O_Numb,O_Date) values(2,1500,'2011-02-01'),(1,2000,'2011-02-02'),
(2,1600,'2011-03-01'),(3,3000,'2012-01-01'),(1,1300,'2012-02-01'),(3,4000,'2012-01-07'),
(2,2000,'2011-03-01'),(1,1030,'2011-02-02'),(4,2200,'2013-05-01')
数据的连接查询
select * from T_Order
select * from T_Product
--group by,having
select P_Id,SUM(O_Numb) from T_Order group by(P_Id) having(SUM(O_Numb))>5000
select P_Id,COUNT(*) from T_Order group by(P_Id) having COUNT(*)>2
select * from (select P_Id,COUNT(*) as countnumb from T_Order group by(P_Id) having COUNT(*)>2)t where t.countnumb>2
--连接查询,inner join内连接,outer join(right,left,full)外连接,cross join交叉连接
select * from T_Product p inner join T_Order o on p.p_Name='钉子' and o.P_Id=2
select * from T_Product p inner join T_Order o on p.p_Name='钉子' and o.P_Id=2 order by o.O_Numb desc
select p.P_Id,p_Name,O_Id from T_Product p inner join T_Order o on p.P_Id=o.P_Id
select p.P_Id,p_Name,O_Id from T_Product p left join T_Order o on p.P_Id=o.P_Id
select p.P_Id,p_Name,O_Id from T_Product p right join T_Order o on p.P_Id=o.P_Id
select T_Product.p_Name,O_Numb,O_Id from T_Product cross join T_Order
--order by 对字段的内容进行排序 order by +字段名
select p.p_Name,O_Numb,O_Id from T_Product p cross join T_Order o order by O_Numb ASC
select p.p_Name,O_Numb,O_Id from T_Product p cross join T_Order o order by p_Name asc