当前位置：首页 > news >正文

SQL Server基本使用示例三

news 来源：原创 2024/5/16 10:44:18

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 ;