create database Saijie use Saijie create table goods1( 商品编号 int primary key identity(1,1),–主键，自增 商品名称 varchar(20) unique, –唯一键 商品价格 decimal(5,1) check(商品价格>0),
); –drop table member create table member( 会员编号 int primary key identity(100,1), 会员姓名 varchar(20), 性别 varchar(4) default(‘女’) ); –drop table orders create table orders( 订单编号 int primary key identity(0000000,1), 会员编号 int foreign key references member(会员编号), 商品编号 int foreign key references goods1(商品编号), 下单时间 smalldatetime, 数量 int );
insert into goods1 values(‘棒棒糖’,0.5); insert into goods1 values(‘奶茶’,1.0); insert into goods1 values(‘可口可乐’,3.5); insert into goods1 values(‘雪碧’,3.0); insert into goods1 values(‘怡宝’,2.0); insert into goods1 values(‘铅笔’,1.0); insert into goods1 values(‘酸奶’,3.5); insert into goods1 values(‘啤酒’,3.0); insert into goods1 values(‘纸巾（袋装）’,1.0); insert into goods1 values(‘纸巾（卷）’,3.0); insert into goods1 values(‘信纸’,2.0); insert into goods1 values(‘水性笔’,3.5); insert into goods1 values(‘橡皮擦’,1.0);
select * from goods1
insert into member values(‘文雅’,’女’); insert into member values(‘李双龙’,’男’); insert into member values(‘贺志玉’,’女’); insert into member values(‘黄成’,’男’); insert into member values(‘蔡文鹏’,’男’); insert into member values(‘王艳芳’,’女’);
select * from member
insert into orders values (100,1,getdate(),12); insert into orders values (102,1,getdate()-1,1); insert into orders values (100,2,getdate()+1,12); insert into orders values (102,4,getdate()-1,1); insert into orders values (100,7,getdate(),12); insert into orders values (101,10,getdate(),4); insert into orders values (103,9,getdate(),9); insert into orders values (105,6,getdate()-1,17);
select * from orders ;
–多表查询 –1. 交叉查询：查询原理：将A表，B表中d数据相互交叉组合，一共有M*N种结合结果。 –1.1 隐式交叉查询: select goods1.商品编号, 商品名称, 商品价格, 数量,下单时间 from goods1,orders where 会员编号=100;
--1.2 显式交叉查询select goods1.商品编号,商品名称,商品价格,数量,下单时间 from goods1 cross join orders;
–2. 内链接查询:查询原理：将A表，B表中的数据根据某种条件链接起来， –将符合条件的数据查询显示 –2.1 隐式内链接查询 select 会员编号 ,goods1.商品编号,商品名称,商品价格,数量,下单时间 from goods1,orders where goods1.商品编号=orders.商品编号 and 会员编号=101; –2.2 显式内链接查询 select goods1.商品编号, 商品名称,商品价格,数量,下单时间 from goods1 inner join orders on goods1.商品编号=orders.商品编号 where 会员编号=102;
–左链接查询：查询原理：以左边的表为主，右边的表为辅，将左表和右边中的数据根据某种条件链接起来， –将符合条件的数据查询显示。 select goods1.商品编号, 商品名称,商品价格,数量,下单时间 from goods1 left join orders on goods1.商品编号=orders.商品编号 where 会员编号=103;
–查询销售额占前两位的商品编号，名称及销售额 select top 2 goods1.商品编号,商品名称,sum(商品价格*数量) as 消费总额 from goods1 inner join orders on goods1.商品编号=orders.商品编号 group by goods1.商品编号 ,商品名称 order by 消费总额 desc;
–查询会员单次消费的总额 select 会员编号,sum(商品价格*数量) as 消费总额 from goods1 inner join orders on goods1.商品编号=orders.商品编号 group by 商品名称 , 会员编号 ;
–查询会员消费的总额 select 会员编号,sum(商品价格*数量) as 消费总额 from goods1 inner join orders on goods1.商品编号=orders.商品编号 group by 会员编号 ;
–查询最近两天天销售额占前两位的商品编号，名称及销售额 select top 2 goods1.商品编号,商品名称,sum(商品价格*数量) as 消费总额 from goods1 inner join orders on goods1.商品编号=orders.商品编号 where 下单时间 between GETDATE()-2 and GETDATE() group by goods1.商品编号,商品名称 order by 消费总额 desc;
–查询如下信息： /* 1.查询某人的消费总额 */ select sum(商品价格*数量) as 消费总额 from goods1 left join orders on goods1.商品编号=orders.商品编号 where 会员编号=101;
/* 2.查询每个人的消费总额,按照会员编号排序 */ select 会员编号,sum(商品价格*数量) as 消费总额 from goods1 inner join orders on goods1.商品编号=orders.商品编号 group by 会员编号 ;
/* 3.查询某个时间段的消费总额 */ select 会员编号,sum(商品价格*数量) as 消费总额 from goods1 inner join orders on goods1.商品编号=orders.商品编号 where 下单时间 between GETDATE()-2 and GETDATE() group by 会员编号 order by 消费总额 desc;
/* 4.查询某人购买次数最多的商品名称 */ select 商品名称, max(数量) as 数量 from goods1 left join orders on goods1.商品编号=orders.商品编号 where 会员编号=101 group by 商品名称;
/* 5.查询某个商品的销售额 */ select 商品名称,sum(商品价格*数量) as 销售额 from goods1 left join orders on goods1.商品编号=orders.商品编号 where goods1.商品编号=10 group by 商品名称;
/* 6.查询每个商品的销售额，按照商品销售额排序，商品编号排序 */ select goods1.商品编号 ,商品名称, sum(商品价格*数量) as 销售额 from goods1 left join orders on goods1.商品编号=orders.商品编号 group by goods1.商品编号, 商品名称 order by 销售额 desc ,goods1.商品编号 asc ;
