use shop
create table shop_jb
(
id int primary key identity(1,1),
namel char(30),
spec varchar(20),
stock int,
price float,
datel datetime
)

create table shop_yw
(
ywid int primary key identity(1,1),
name2 varchar(20),
sex char(2) check(sex='男' or sex='女'),
age int,
tel varchar(20) unique,
address varchar(200)
)
create table shop_xs
(
id int,
sale float,
quantity int,
date2 datetime,
ywid int,
foreign key (xsid) references shop_jb,
foreign key (ywid) references shop_yw
)

select * from shop_jb
insert into shop_jb(namel,spec,stock,price,datel) values('电视','合格',100,2000,'2010-8-2')
insert into shop_jb(namel,spec,stock,price,datel) values('电脑','sp-A8',50,2000,'2010-7-5')
insert into shop_jb(namel,spec,stock,price,datel) values('洗衣机','sp-400',100,900,'2010-7-2')
insert into shop_jb(namel,spec,stock,price,datel) values('风扇','合格',1000,110,'2010-5-2')
insert into shop_jb(namel,spec,stock,price,datel) values('饮水机','合格',100,160,'2010-8-2')
insert into shop_jb(namel,spec,stock,price,datel) values('空调','sp-3',400,3000,'2010-8-2')
insert into shop_jb(namel,spec,stock,price,datel) values('电脑桌','f-100',5000,200,'2010-7-16')
insert into shop_jb(namel,spec,stock,price,datel) values('电灯','合格',100,20,'2010-8-2')
insert into shop_jb(namel,spec,stock,price,datel) values('面包','合格',1000,2,'2010-7-2')
insert into shop_jb(namel,spec,stock,price,datel) values('汽水','合格',10000,2,'2010-6-2')

select * from shop_yw

insert into shop_yw values('任洪建','男',21,'15811115080','中南海')
insert into shop_yw values('姜鹏飞','男',18,'15811115081','清华园')
insert into shop_yw values('周艳','女',20,'15811115082','昌平')
insert into shop_yw values('王洪刚','男',22,'15811115083','中南海')

update shop_yw set age=18 where ywid=2

select * from shop_jb
select * from shop_xs
select * from shop_yw

insert into shop_xs values(2,3400,3,'2010-7-20',1)
insert into shop_xs values(5,200,4,'2010-8-2',1)
insert into shop_xs values(2,3000,3,'2010-7-20',2)
insert into shop_xs values(2,3000,3,'2010-7-20',3)
insert into shop_xs values(6,5000,3,'2010-8-2',4)
insert into shop_xs values(7,300,30,'2010-7-10',4)

--联合查询
--内连接(自然连接)
--第一种格式:
--select 字段 from 表名1 join 表名2 on 关联条件 where 条件
select * from shop_jb join shop_xs on shop_jb.id=shop_xs.id  join shop_yw on shop_xs.ywid=shop_yw.ywid
select * from shop_jb a,shop_xs b,shop_yw c where a.id=b.id and b.ywid=c.ywid

--查询一号业务员所卖商品,并显示商品的名称,进货价格,商品的销售单价,销售量
select shop_jb.namel,shop_jb.price,shop_xs.sale,shop_xs.quantity from shop_jb  join shop_xs on shop_jb.id=shop_xs.id where shop_xs.ywid=1
select a.namel,a.price,b.sale,b.quantity from shop_jb a join shop_xs b on a.id=b.id where b.ywid=1

select a.namel,a.price,b.sale,b.quantity from shop_jb a,shop_xs b,shop_yw c where a.id=b.id and b.ywid=c.ywid and b.ywid=1

--查询任洪建所卖商品,并显示商品的名称,进货价格,商品的销售单价,销售量,业务员的名字
select shop_jb.namel,shop_jb.price,shop_xs.sale,shop_xs.quantity,shop_yw.name2 from shop_jb  join shop_xs on shop_jb.id=shop_xs.id join shop_yw on shop_yw.ywid=shop_xs.ywid  where shop_yw.name2='任洪建'

--字段起别名 as  (as可省)
select * from shop_jb a join shop_xs b on a.id=b.id

--查看二号业务员商品销售金额,并显示商品名称,销售金额,业务员名
select a.namel,(b.sale*b.quantity) as 销售金额,c.name2 from shop_jb a join shop_xs b on a.id=b.id
join shop_yw c on b.ywid=c.ywid

--第二种格式:
--select 字段 from 表1,表2,表n where 关联条件 and 关联条件 and 条件

--1、查询销售单价大于50的商品编号,商品名称及销售单价
--2、查询库存量大于200且销售量小于50的商品名称及销售量,库存量
--3、查询住址中含‘中’字的业务员名字,销售单价,住址及销售量
--4、查询2010年8月1日以后的销售商品名称,业务员名及销售单价,销售金额
select a.namel 销售商品名称,c.name2 业务员名,b.sale 销售单价,(b.sale*b.quantity) 销售金额 from shop_jb a,shop_xs b,shop_yw c where a.id=b.id and b.ywid=c.ywid and
b.date2>'2010-7-15'

--外连接
--左连接 left join on
----select 字段 from 表名1 left join 表名2 on 关联条件 where 条件

select * from shop_jb a left join shop_xs b on a.id=b.id where b.sale is null

--右连接 right join on
----select 字段 from 表名1 right join 表名2 on 关联条件 where 条件
select * from shop_xs b right join shop_jb a on a.id=b.id where b.sale is null

--去掉重复记录distinct
--查看商品销售情况
select distinct a.namel,b.id from shop_jb a left join shop_xs b on a.id=b.id

--排序order by 字段  asc升序   desc降序
select distinct a.namel,b.id from shop_jb a left join shop_xs b on a.id=b.id order by b.id asc

--全外连接 full join on
----select 字段 from 表名1 full join 表名2 on 关联条件 where 条件

select * from shop_jb a full join shop_xs b on a.id=b.id

--自连接
--格式:select 字段 from 表名1 a join 表名1 b on 关联条件 where 条件
select * from shop_jb a join shop_jb b on a.id=b.id

---自连接:在连接过程中为同名表起别名
---实例:
create table 学校
(员工编号 int primary key,
员工姓名 varchar(20),
员工职务 varchar(20),
上级编号 int foreign key(上级编号) references 学校(员工编号))
insert into 学校 values(1,'邓美燕','校长',null),(2,'赵剑','院长',1),(3,'王刚','主任',2),(4,'郭佳','教师',3)
----实例:查询员工编号,员工姓名,员工职务,上级姓名
---2,赵剑,院长,邓美燕
select * from 学校
select b.员工编号 ,b.员工姓名,b.员工职务,a.员工姓名 from 学校 a ,学校  b where a.上级编号=b.员工编号

--分组group by 字段
--一般与聚合函数联用

select * from shop_xs

select ywid,b.namel,avg(quantity) from shop_xs a,shop_jb b where a.id=b.id group by ywid,b.namel

select * from student111

select * from score
select id,AVG(math),AVG(en),AVG(ch) from score group by id

select id,COUNT(*) from score group by id

--having用法
select id,AVG(math),AVG(en),AVG(ch) from score group by id
select id from score group by id having AVG(math)<80 and AVG(en)<79 and AVG(ch)<80

--实例:要求列出所有学生成绩表中几次考试的数学平均成绩大于80分的学生姓名
exec sp_helpconstraint score
select * from student111
select * from score
select a.name from student111 a join score b on a.id=b.id group by a.id,a.name
having AVG(b.math)>80

--嵌套查询
--in
--格式:
--select 字段 from 表名 where 列名 in(select 同一个列名 from 表名 where 条件)
--查询考试中数学成绩大于80分的学生姓名
select name from student111 where id in(select id from score where math>97)

select name from student111 where id in(1,2)

select a.name from student111 a,score b where a.id=b.id and b.math>97