--select <列名> from 表名
--[where 条件表达式]--进行数据源的过滤
--[group by 分组字段]--将结果集进行分组,与聚合函数一起实现分组统计查询。
--[having 包含聚合函数的条件表达式] --对分组后的结果进行统计
--order by 排序字段 [desc|asc] --按照某个字段将结果集进行排序

use TestDB;
go
----------------------------------
--连接查询
--1.内连接:inner join 
--2.外部连接:left/right/full outer join
--3.交叉连接 :cross join
----------------------------------

-----------------------------------------------------
--简单的条件连接查询：在两表查询语句后面加上where条件语句，实现两表的连接查询，其条件为两表的关联字段。
-----------------------------------------------------
--查询学生的学号，姓名，所属班级编号和班级名称信息
select * from StuInfo
select * from ClassInfo
select * from Scores
select * from CourseInfo
select stuid 学号,stuname 姓名,StuInfo.classid 班级编号,classname 班级名称 from StuInfo,ClassInfo 
where StuInfo.ClassId=ClassInfo.ClassId
-------------------------------------------------------
--1.内连接：内连接仅仅返回存在字段匹配的记录,即符合连接条件的数据，无法满足的数据被过滤。
--------------------------------------------------------
--查询学生的班级信息：班级编号和班级名称，学号，姓名

select ClassInfo.classid,classname,stuid,stuname from ClassInfo inner join StuInfo on ClassInfo.classid=StuInfo.classid
--查询学生的成绩信息：学号，姓名，课程编号，成绩

select Scores.stuid 学号,StuInfo.stuname 姓名,CourseInfo.CourseId 课程编号,Score 成绩 from Scores inner join StuInfo on Scores.stuid=StuInfo.stuid
inner join CourseInfo on CourseInfo.CourseId=Scores.CourseId
--查询学生选修的课程信息：学号，姓名，选修课程名称

select StuInfo.stuid 学号,stuname 姓名,CourseInfo.Coursename 选修课程名称 from StuInfo inner join Scores on Scores.stuid=StuInfo.stuid
inner join CourseInfo on CourseInfo.CourseId=Scores.CourseId
--查询学生的成绩信息：学号，姓名，课程名称，成绩

select StuInfo.stuid 学号,stuname 姓名,CourseInfo.Coursename 课程名称,scores.Score 成绩
 from StuInfo inner join Scores on Scores.stuid=StuInfo.stuid
inner join CourseInfo on CourseInfo.CourseId=Scores.CourseId
--查询选修了‘计算机基础’的学生信息：学号，姓名，课程名称，成绩

select StuInfo.stuid 学号,stuname 姓名,CourseInfo.Coursename 课程名称,scores.Score 成绩 
from StuInfo 
inner join Scores on Scores.stuid=StuInfo.stuid
inner join CourseInfo on CourseInfo.CourseId=Scores.CourseId
where CourseInfo.Coursename='计算机基础'

----------------------------------------------------------------------
----------------------------------------------------------------------
go
use students
go
--1.查询学生的姓名，年龄，笔试成绩和机试成绩

select * from couse
select stuName 姓名,stuAge 年龄,wittenExam as 笔试,机试=labExam 
from student inner join couse on student.stuNO=couse.stuNo

--2.查询笔试和机试成绩都在60分以上的学生的学号，姓名，笔试成绩和机试成绩
go
select stuName 姓名,stuAge 年龄,wittenExam as 笔试,机试=labExam 
from student inner join couse on student.stuNO=couse.stuNo
where labExam>60 and wittenExam>60
go
--3.查询所有学生的学号，姓名，笔试成绩，机试成绩，没有参加考试的学生的成绩以NULL值填充
--left join可以显示未考人员的成绩以NULL填充
select student.stuno 学好,stuName 姓名,wittenExam as 笔试,机试=labExam 
from student left join couse on student.stuNO=couse.stuNo
--4.查询年龄在20以上（包括20）的学生的姓名，年龄，笔试成绩和机试成绩，并按笔试成绩降序排列
go
select stuName 姓名,stuAge 年龄,wittenExam as 笔试,机试=labExam 
from student inner join couse on student.stuNO=couse.stuNo
where stuage>=20 order by wittenExam desc
--5.查询男女生的机试平均分
go
select (case when stusex=1 then '男' else '女' end) 男女,avg(labExam) 机试平均分 
from student inner join couse on student.stuNO=couse.stuNo
group by stusex
--6.查询男女生的笔试总分
go
select (case when stusex=1 then '男' else '女' end) 男女,sum(wittenExam) 笔试总分 
from student inner join couse on student.stuNO=couse.stuNo
group by stusex

--------------------------------------------------------------------------------------------
go
	use girl
go
--1.查询所有的订单的订单的编号，订单日期，订购产品的类别和订购的产品名称，订购数量和订购单价

select orders.orderld 订单编号,orderdate 订单日期,itemtype 产品类别,itemname 产品名称,thenumber 订购数量,themoney 订购单价
from orders inner join oederltem on orders.orderld=oederltem.orderld
--2.查询订购数量大于50的订单的编号，订单日期，订购产品的类别和订购的产品名称

select orders.orderld 订单编号,orderdate 订单日期,itemtype 产品类别,itemname 产品名称
from orders inner join oederltem on orders.orderld=oederltem.orderld where thenumber>50
--3.查询所有的订单的订单的编号，订单日期，订购产品的类别和订购的产品名称，订购数量和订购单价以及订购总价

select orders.orderld 订单编号,orderdate 订单日期,itemtype 产品类别,
itemname 产品名称,thenumber 订购数量,themoney 订购单价,thenumber*themoney 订购总价
from orders inner join oederltem on orders.orderld=oederltem.orderld
--4.查询单价大于等于5并且数量大于等于50的订单的订单的编号，订单日期，订购产品的类别和订购的产品名称，订购数量和订购单价以及订购总价

select orders.orderld 订单编号,orderdate 订单日期,itemtype 产品类别,
itemname 产品名称,thenumber 订购数量,themoney 订购单价,thenumber*themoney 订购总价
from orders inner join oederltem on orders.orderld=oederltem.orderld where themoney>=5 and thenumber>=50
--5.查询每个订单分别订购了几个产品，例如：
--			编号   订购产品数
select orders.orderld 订单编号,count(itemname) 订购产品数 from orders
inner join oederltem on orders.orderld=oederltem.orderld
group by orders.orderld
--6.查询每个订单里的每个类别的产品分别订购了几次和总数量，例如：

-- 订单编号       产品类别     订购次数     总数量
select orders.orderld 订单编号,itemtype 产品类别,count(itemname) 订购次数,sum(thenumber) 总数量 
from orders inner join oederltem on orders.orderld=oederltem.orderld
group by orders.orderld,itemtype order by orders.orderld asc

----------------------------------------------------------------------
----------------------------------------------------------------------

go
	use bbs
go
--	在论坛数据库中完成以下题目
--1.查询出每个版块的版主编号，版主姓名和版块名称

select sUid 版主编号,uName 版主姓名,sName 版块名称
from bbsSection inner join bbsUsers on bbsSection.sUid=bbsUsers.UID
--2.查询出主贴的发帖时间在2008-9-15以后的主贴的发帖人编号，发帖人姓名，帖子的标题，帖子的内容和发帖时间

select tUID 发帖人编号,uname 发帖人姓名,tTitle 帖子的标题,tMsg 帖子的内容,tTime 发帖时间
from bbsTopic inner join bbsUsers on bbsTopic.tUID=bbsUsers.UID
where tTime>'2008-9-15'
--3.查询出年龄在20以下的版主的编号，版主的名称和版块的名称

select sUid 版主的编号,uName 版主的姓名,sName 版块的名称 from bbsSection
inner join bbsUsers on bbsSection.sUid=bbsUsers.UID
where uAge<20
--4.查询出回复数量最多的主贴的发帖人编号，发帖人姓名，主贴标题，主贴内容和回复数量

select top 1 tUID 发帖人编号,uName 发帖人姓名,tTitle 主贴标题,tMsg 主贴内容,tCount 回复数量
from bbsTopic
inner join bbsUsers on bbsTopic.tUID=bbsUsers.UID
order by tCount desc
--5.在主贴表中查询-/-每个版块中-/-每个用户-/-的发帖总数

select tSID 版块,tUID 用户,count(tUID) 发帖总数 from bbsTopic
group by tSID,tUID order by tSID asc