day 3 join 连接与习题

--join连接--

--新建表t1--
drop table sdata.dbo.t1
create table sdata.dbo.t1(
id int,name varchar(255)
)

insert into sdata.dbo.t1(id,name)values('1','lucy')
insert into sdata.dbo.t1(id,name)values('2','lily')
insert into sdata.dbo.t1(id,name)values('3','jack')
select *from sdata.dbo.t1

--新建表t2--
create table sdata.dbo.t2(
id int,score float
)

insert into sdata.dbo.t2(id,score)values(2,20)
insert into sdata.dbo.t2(id,score)values(3,30)
insert into sdata.dbo.t2(id,score)values(4,40)

select *from sdata.dbo.t2

--join模板--
select * from table 1 left join table 2 on t1.id = t2.id

--此次关联是以表一的id为基础做关联-- --null表示没有记录--
select * from sdata.dbo.t1 left join sdata.dbo.t2 on t1.id = t2.id

-- 此次关联显示表t1的id和t1的name 表t2的score--
select
t1.id as student_id --令起字段名可以令起可以不起--
,t1.name as student_name
,t2.score
from sdata.dbo.t1 as t1
left join
sdata.dbo.t2 as t2
on t1.id = t2.id

--此次关联是以表一表2共同的id为基础做关联--
select * from sdata.dbo.t1 inner join sdata.dbo.t2 on t1.id = t2.id

--此次关联两个表里面所有的数据--

select * from sdata.dbo.t1 full join sdata.dbo.t2 on t1.id = t2.id

--此次关联是t2显示--
select * from sdata.dbo.t1 right join sdata.dbo.t2 on t1.id = t2.id

--三表关联模式--

select t1. from
(t1(inner join t2 on t1.id = t2.id)
inner join t3 on t1.id =t3.id
);

--练习题--
create table sdata.dbo.student(Sno int,Sname varchar(255),Sex varchar(255),Sage int,Sdept varchar(255))
create table sdata.dbo.course(Cno int,Cname varchar(255))
create table sdata.dbo.sc(Sno int,Cno int,Grade int)

insert into sdata.dbo.course values('1','数据库 ')
insert into sdata.dbo.course values('2','数学 ')
insert into sdata.dbo.course values('3','信息系统')
insert into sdata.dbo.course values('4','操作系统')
insert into sdata.dbo.course values('5','数据结构')
insert into sdata.dbo.course values('6','数据处理')

insert into sdata.dbo.sc values('95001','1','81')
insert into sdata.dbo.sc values('95001','2','85')
insert into sdata.dbo.sc values('95001','3','88')
insert into sdata.dbo.sc values('95001','4','70')
insert into sdata.dbo.sc values('95002','2','90')
insert into sdata.dbo.sc values('95002','3','80')
insert into sdata.dbo.sc values('95002','4','71')
insert into sdata.dbo.sc values('95002','5','60')
insert into sdata.dbo.sc values('95003','1','82')
insert into sdata.dbo.sc values('95003','3','90')
insert into sdata.dbo.sc values('95003','5','100')
insert into sdata.dbo.sc values('95004','1','80')
insert into sdata.dbo.sc values('95004','2','92')
insert into sdata.dbo.sc values('95004','4','91')
insert into sdata.dbo.sc values('95004','5','70')
insert into sdata.dbo.sc values('95005','1','70')
insert into sdata.dbo.sc values('95005','2','92')
insert into sdata.dbo.sc values('95005','3','99')
insert into sdata.dbo.sc values('95005','6','87')
insert into sdata.dbo.sc values('95006','1','72')
insert into sdata.dbo.sc values('95006','2','62')
insert into sdata.dbo.sc values('95006','3','100')
insert into sdata.dbo.sc values('95006','4','59')
insert into sdata.dbo.sc values('95006','5','60')
insert into sdata.dbo.sc values('95006','6','98')
insert into sdata.dbo.sc values('95007','3','68')
insert into sdata.dbo.sc values('95007','4','91')
insert into sdata.dbo.sc values('95007','5','94')
insert into sdata.dbo.sc values('95007','6','78')
insert into sdata.dbo.sc values('95008','1','98')
insert into sdata.dbo.sc values('95008','3','89')
insert into sdata.dbo.sc values('95008','6','91')
insert into sdata.dbo.sc values('95009','2','81')
insert into sdata.dbo.sc values('95009','4','89')
insert into sdata.dbo.sc values('95009','6','100')
insert into sdata.dbo.sc values('95010','2','98')
insert into sdata.dbo.sc values('95010','5','90')
insert into sdata.dbo.sc values('95010','6','80')
insert into sdata.dbo.sc values('95011','1','81')
insert into sdata.dbo.sc values('95011','2','91')
insert into sdata.dbo.sc values('95011','3','81')
insert into sdata.dbo.sc values('95011','4','86')
insert into sdata.dbo.sc values('95012','1','81')
insert into sdata.dbo.sc values('95012','3','78')
insert into sdata.dbo.sc values('95012','4','85')
insert into sdata.dbo.sc values('95012','6','98')
insert into sdata.dbo.sc values('95013','1','98')
insert into sdata.dbo.sc values('95013','2','58')
insert into sdata.dbo.sc values('95013','4','88')
insert into sdata.dbo.sc values('95013','5','93')
insert into sdata.dbo.sc values('95014','1','91')
insert into sdata.dbo.sc values('95014','2','100')
insert into sdata.dbo.sc values('95014','4','98')
insert into sdata.dbo.sc values('95015','1','91')
insert into sdata.dbo.sc values('95015','3','59')
insert into sdata.dbo.sc values('95015','4','100')
insert into sdata.dbo.sc values('95015','6','95')
insert into sdata.dbo.sc values('95016','1','92')
insert into sdata.dbo.sc values('95016','2','99')
insert into sdata.dbo.sc values('95016','4','82')
insert into sdata.dbo.sc values('95017','4','82')
insert into sdata.dbo.sc values('95017','5','100')
insert into sdata.dbo.sc values('95017','6','58')
insert into sdata.dbo.sc values('95018','1','95')
insert into sdata.dbo.sc values('95018','2','100')
insert into sdata.dbo.sc values('95018','3','67')
insert into sdata.dbo.sc values('95018','4','78')
insert into sdata.dbo.sc values('95019','1','77')
insert into sdata.dbo.sc values('95019','2','90')
insert into sdata.dbo.sc values('95019','3','91')
insert into sdata.dbo.sc values('95019','4','67')
insert into sdata.dbo.sc values('95019','5','87')
insert into sdata.dbo.sc values('95020','1','66')
insert into sdata.dbo.sc values('95020','2','99')
insert into sdata.dbo.sc values('95020','5','93')
insert into sdata.dbo.sc values('95021','2','93')
insert into sdata.dbo.sc values('95021','5','91')
insert into sdata.dbo.sc values('95021','6','99')
insert into sdata.dbo.sc values('95022','3','69')
insert into sdata.dbo.sc values('95022','4','93')
insert into sdata.dbo.sc values('95022','5','82')
insert into sdata.dbo.sc values('95022','6','100')

insert into sdata.dbo.student values('95001','李勇','男','20','CS ')
insert into sdata.dbo.student values('95002','刘晨','女','19','IS ')
insert into sdata.dbo.student values('95003','王敏','女','22','MA ')
insert into sdata.dbo.student values('95004','张立','男','19','IS ')
insert into sdata.dbo.student values('95005','刘刚','男','18','MA ')
insert into sdata.dbo.student values('95006','孙庆','男','23','CS ')
insert into sdata.dbo.student values('95007','易思玲','女','19','MA ')
insert into sdata.dbo.student values('95008','李娜','女','18','CS ')
insert into sdata.dbo.student values('95009','梦圆圆','女','18','MA ')
insert into sdata.dbo.student values('95010','孔小涛','男','19','CS ')
insert into sdata.dbo.student values('95011','包小柏','男','18','MA ')
insert into sdata.dbo.student values('95012','孙花','女','20','CS ')
insert into sdata.dbo.student values('95013','冯伟','男','21','CS ')
insert into sdata.dbo.student values('95014','王小丽','女','19','CS ')
insert into sdata.dbo.student values('95015','王君','男','18','MA ')
insert into sdata.dbo.student values('95016','钱国','男','21','MA ')
insert into sdata.dbo.student values('95017','王风娟','女','18','IS ')
insert into sdata.dbo.student values('95018','王一','女','19','IS ')
insert into sdata.dbo.student values('95019','邢小丽','女','19','IS ')
insert into sdata.dbo.student values('95020','赵钱','男','21','IS ')
insert into sdata.dbo.student values('95021','周二','男','17','MA ')
insert into sdata.dbo.student values('95022','郑明','男','20','MA ')

select *from sdata.dbo.student
select *from sdata.dbo.sc
select *from sdata.dbo.course

--三表关联样例--
select * from
sdata.dbo.student a LEFT join sdata.dbo.sc b on a.Sno = b.Sno
left join sdata.dbo.course c on b.Cno =c.Cno

--除去冗余的字段，只要个别的字段--
select
a.Sno
,a.Sname
,a.Sage
,a.Sdept
,c.Cname
,b.Grade
from
sdata.dbo.student a LEFT join sdata.dbo.sc b on a.Sno = b.Sno
left join sdata.dbo.course c on b.Cno =c.Cno

--在pdata数据库里面建一张student_detail表放总结的数据--
drop table pdata.dbo.student_detail
select
a.Sno
,a.Sname
,a.Sage
,a.Sdept
,c.Cno
,c.Cname
,b.Grade
into pdata.dbo.student_detail
from
sdata.dbo.student a LEFT join sdata.dbo.sc b on a.Sno = b.Sno
left join sdata.dbo.course c on b.Cno =c.Cno

--查询全体学生的学好和姓名--
select Sno,sname from sdata.dbo.student
--查询选修课程的学生姓名--
select *from sdata.dbo.student
select *from sdata.dbo.sc
select *from sdata.dbo.course

select distinct sname from pdata.dbo.student_detail where Sname is not null;
--计算总人数--
select COUNT(distinct Sno) from pdata.dbo.student_detail
--计算一号课的平均成绩--
select AVG(Grade) from pdata.dbo.student_detail where Cno = 1

--计算各个课程及课程的人数--
select cname,COUNT(distinct sno)from pdata.dbo.student_detail
group by Cname

--查选修了3门以上的课程的学生的学号 cname_number代指大于3那列的字段-
select sno from (
select sno,COUNT(distinct cname) cname_number
from pdata.dbo.student_detail
group by Sno) a
where cname_number > 3;

--第二种方法 用having--
select sno,COUNT(distinct cname) cname_number
from pdata.dbo.student_detail
group by Sno
having COUNT (distinct Cname)>3

---查询学生信息，结果按照学号全局有序-
select distinct sno ,sname,sage from pdata.dbo.student_detail
order by sno Asc

--查询选修二号课程且成绩大于90分的所有学生--
select distinct *from pdata.dbo.student_detail where Grade >=90 and cno= 2

--查询与刘晨在同一个系的同学--

select *from pdata.dbo.student_detail where Sname = '刘晨'

--查询与刘晨在同一个系的同学--
select distinct sname,Sno from pdata.dbo.student_detail
where Sdept in(
select distinct Sdept from pdata.dbo.student_detail where sname ='刘晨')
and Sname <> '刘晨'

 

--另外一种方法和刘晨一个系的所有的人--
select distinct sname,Sno,Sdept
from pdata.dbo.student_detail
where exists(
select 1 from pdata.dbo.student_detail a
where pdata.dbo.student_detail.sdept = a.sdept
and a.sname ='刘晨'
)

--case when--
create table sdata.dbo.main(
date_temp date
,if_success varchar(20)
)
insert into sdata.dbo.main values ('2005-05-09','胜')
insert into sdata.dbo.main values ('2005-05-09','胜')
insert into sdata.dbo.main values ('2005-05-09','负')
insert into sdata.dbo.main values ('2005-05-09','负')
insert into sdata.dbo.main values ('2005-05-10','胜')
insert into sdata.dbo.main values ('2005-05-10','负')
insert into sdata.dbo.main values ('2005-05-10','负')
select * from sdata.dbo.main
select date_temp from sdata.dbo.main
,sum(case when if_success ='胜' then 1 else 0 end) 胜
,sum(case when if_success <>'胜' then 1 else 0 end)负
from sdata.dbo.main
group by date_temp