--==========使用其他子句=============
--首先是 order by  功能 - 排序
select * from studio order by st_name
--多排序条件
select * from studio order by st_name DESC,st_age DESC,st_sex DESC
--有条件,主要是看下条件和子句的位置
select * from studio where cl_id=1 order by st_name



--GROUP BY 子句  功能 - 分组统计
select cl_id as '班级编号',count(*) as '人数' from studio group by cl_id
--按宿舍统计年龄平均值
select ho_id as '宿舍编号',avg(st_age) as '平均年龄' from studio group by ho_id 
--多分组
select ho_id as '宿舍编号',cl_id as '班级编号',avg(st_age) as '平均年龄' from studio group by ho_id,cl_id 
--有条件,主要是看下条件和子句的位置
select ho_id as '宿舍编号',avg(st_age) as '平均年龄' from studio where cl_id=1 group by ho_id 


--使用 having 子句 功能 - 指定组或者聚合的搜索条件，通常与group by 子句一起使用，完成分组查询后再进步筛选
select ho_id as '宿舍编号',avg(st_age) as '平均年龄' from studio group by ho_id   having avg(st_age)>35
--多条件
select ho_id as '宿舍编号',avg(st_age) as '平均年龄' from studio group by ho_id   having avg(st_age)>35 and ho_id>2


--===========联合查询=============
--使用union子句的查询称为联合查询，功能：将两个以上的查询结果集组合为一个单个结果集，该集中包括所有集中的全部行数据
--下面我们尝试将多个查询联合起来
select * from studio where cl_id=1

union
select * from studio where ho_id=1


union
select * from studio where st_age>=30

--下面我们继续利用上面的例题，增加上 All 看下效果

select * from studio where cl_id=1

union all
select * from studio where ho_id=1


union all
select * from studio where st_age>=30

--再继续利用，给他加上排序

select * from studio where cl_id=1

union all
select * from studio where ho_id=1


union all
select * from studio where st_age>=30

order by st_id

--===========连接查询==================
--连接查询，功能 - 将多个表中的数据查询出来放在一起
--内连接：使用比较运算符=><....等进行表间某些数据库的比较操作，并列出这些表中与连接条件相匹配的数据行
--等值连接，当然就是用等号了，毛病，这也要问
select * from studio inner join class on studio.cl_id = class.cl_id 
--指明要查询的列（江湖上又称自然连接），并排序
select st_id as '编号',st_name as '学生姓名',cl_class as '班级名称' from studio inner join class on studio.cl_id = class.cl_id order by st_id
--使用表别名
select st.st_name as '学生姓名',st.cl_id as '班级编号',cl.cl_class as '班级名称' from studio as st inner join class as cl on st.cl_id = cl.cl_id

--不等连接，这个问题很好笑，既然使用等号的是等值连接，那么不等值你说是不是应该是非等于以外的呢？
--下面我们再连接第三个表，看下是怎么搞滴
select st.st_name as '学生姓名',st.cl_id as '班级编号',cl.cl_class as '班级名称' ,ho.ho_coding as '所在宿舍编号'
from studio as st inner join class as cl 

on st.cl_id = cl.cl_id


inner join hostel as ho

on st.ho_id=ho.ho_id
--我们再给他加个条件看下
--where st.cl_id>2
--再给他个排序
--order by st.st_id


--外连接：
--与内连接不同的是，内连接至少要有一个同属于两个表的行符合连接条件时才会返回行，外连接会返回符合任意条件的行
--他的表有主从之分，他用主表中的每行去匹配从表中的，与内连不同的是，他不会丢弃没有匹配的行，而是填充null给从结果集

--左外连接
select st.st_id as '学生编号', st.st_name as '学生姓名',cl.cl_id as '班级编号',cl_class as '班级名称'
from studio as st left outer join class as cl

on st.cl_id=cl.cl_id
where cl.cl_id>2

--多表
select tka.te_co_id as '课程安排编号'

,cl.cl_id as '班级编号',cl.cl_class as '班级名称'

,co.co_id as '课程ID',co.co_name as '课程名称',co.co_num as '课时数'

,te.te_name as '老师姓名'
from te_kc_ap as tka left outer join class as cl

on tka.cl_id=cl.cl_id
left outer join

course as co

on tka.co_id=co.co_id
left outer join

teacher as te

on tka.te_id=te.te_id

--====================右外连结  =======================
select st.st_id as '学生编号', st.st_name as '学生姓名',cl.cl_id as '班级编号',cl_class as '班级名称'
from studio as st right outer join class as cl

on st.cl_id=cl.cl_id
where cl.cl_id>2

--多表
select tka.te_co_id as '课程安排编号'

,cl.cl_id as '班级编号',cl.cl_class as '班级名称'

,co.co_id as '课程ID',co.co_name as '课程名称',co.co_num as '课时数'

,te.te_name as '老师姓名'
from te_kc_ap as tka
right outer join class as cl

on
tka.cl_id=cl.cl_id
right outer join teacher te

on
tka.te_id=te.te_id
right outer join course co

on
tka.co_id=co.co_id


--========完全连接==============
select st.st_id as '学生编号', st.st_name as '学生姓名',cl.cl_id as '班级编号',cl_class as '班级名称'
from studio as st full outer join class as cl

on st.cl_id=cl.cl_id
order by st.st_id

--多表
select tka.te_co_id as '课程安排编号'

,cl.cl_id as '班级编号',cl.cl_class as '班级名称'

,co.co_id as '课程ID',co.co_name as '课程名称',co.co_num as '课时数'

,te.te_name as '老师姓名'
from te_kc_ap as tka
full outer join class as cl

on
tka.cl_id=cl.cl_id
full outer join teacher te

on
tka.te_id=te.te_id
full outer join course co

on
tka.co_id=co.co_id


--==========交叉连接================
--该方式在不带where子句时，返回的是两个表中所有数据行的笛卡尔积（第一个表中的行乘以第二个表中的行）
--用学生和班级表做交叉查询
select st_name,cl_class from studio cross join class
select st_name,cl_class from studio,class

select st_name,cl_class from studio cross join class

--=========自连接===
-----------------先临时创建一个表-------------
create table zone(

id int primary key identity(1,1) not null,

z_zone varchar(30),

z_id int references zone(id))
--大家试下，这里是否可以给个默认值



select * from zone

insert into zone(z_zone) values('北京')
insert into zone(z_zone,z_id) values('北京',4)
insert into zone(z_zone) values('四川')
insert into zone(z_zone,z_id) values('成都',6)
insert into zone(z_zone,z_id) values('绵阳',6)
insert into zone(z_zone) values('江苏')
insert into zone(z_zone,z_id) values('南京',10)
insert into zone(z_zone,z_id) values('苏州',10)
insert into zone(z_zone,z_id) values('无锡',10)
insert into zone(z_zone,z_id) values('常州',10)

----------------------------------------------
--看下自连接的一般用处
select a.z_zone,b.z_zone from zone as a inner join zone as b on a.z_id=b.id 
--扩展应用下
select b.z_zone,count(a.z_zone) as '辖区数' from zone as a inner join zone as b on a.z_id=b.id group by b.z_zone


--简单说就是自己连接自己，换言之对同一个表进行连接操作
select a.st_name,a.st_add,b.st_name,b.st_add from studio as a inner join studio as b on a.st_add=b.st_add
--我们发现有人等于自己，那么增加一个条件
select a.st_name,a.st_add,b.st_name,b.st_add from studio as a inner join studio as b on a.st_add=b.st_add and a.st_name!=b.st_name