|
--==========使用其他子句=============
--首先是 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 子句 功能 - 分組統(tǒng)計(jì)
select cl_id as '班級(jí)編號(hào)',count(*) as '人數(shù)' from studio group by cl_id
--按宿舍統(tǒng)計(jì)年齡平均值
select ho_id as '宿舍編號(hào)',avg(st_age) as '平均年齡' from studio group by ho_id
--多分組
select ho_id as '宿舍編號(hào)',cl_id as '班級(jí)編號(hào)',avg(st_age) as '平均年齡' from studio group by ho_id,cl_id
--有條件,主要是看下條件和子句的位置
select ho_id as '宿舍編號(hào)',avg(st_age) as '平均年齡' from studio where cl_id=1 group by ho_id
--使用 having 子句 功能 - 指定組或者聚合的搜索條件，通常與group by 子句一起使用，完成分組查詢后再進(jìn)步篩選
select ho_id as '宿舍編號(hào)',avg(st_age) as '平均年齡' from studio group by ho_id having avg(st_age)>35
--多條件
select ho_id as '宿舍編號(hào)',avg(st_age) as '平均年齡' from studio group by ho_id having avg(st_age)>35 and ho_id>2
--===========聯(lián)合查詢=============
--使用union子句的查詢稱為聯(lián)合查詢，功能：將兩個(gè)以上的查詢結(jié)果集組合為一個(gè)單個(gè)結(jié)果集，該集中包括所有集中的全部行數(shù)據(jù)
--下面我們嘗試將多個(gè)查詢聯(lián)合起來
select * from studio where cl_id=1
union
select * from studio where ho_id=1
union
select * from studio where st_age>=30
--下面我們繼續(xù)利用上面的例題，增加上 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
--再繼續(xù)利用，給他加上排序
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
--===========連接查詢==================
--連接查詢，功能 - 將多個(gè)表中的數(shù)據(jù)查詢出來放在一起
--內(nèi)連接：使用比較運(yùn)算符=><....等進(jìn)行表間某些數(shù)據(jù)庫(kù)的比較操作，并列出這些表中與連接條件相匹配的數(shù)據(jù)行
--等值連接，當(dāng)然就是用等號(hào)了，毛病，這也要問
select * from studio inner join class on studio.cl_id = class.cl_id
--指明要查詢的列（江湖上又稱自然連接），并排序
select st_id as '編號(hào)',st_name as '學(xué)生姓名',cl_class as '班級(jí)名稱' from studio inner join class on studio.cl_id = class.cl_id order by st_id
--使用表別名
select st.st_name as '學(xué)生姓名',st.cl_id as '班級(jí)編號(hào)',cl.cl_class as '班級(jí)名稱' from studio as st inner join class as cl on st.cl_id = cl.cl_id
--不等連接，這個(gè)問題很好笑，既然使用等號(hào)的是等值連接，那么不等值你說是不是應(yīng)該是非等于以外的呢？
--下面我們?cè)龠B接第三個(gè)表，看下是怎么搞滴
select st.st_name as '學(xué)生姓名',st.cl_id as '班級(jí)編號(hào)',cl.cl_class as '班級(jí)名稱' ,ho.ho_coding as '所在宿舍編號(hào)'
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
--我們?cè)俳o他加個(gè)條件看下
--where st.cl_id>2
--再給他個(gè)排序
--order by st.st_id
--外連接：
--與內(nèi)連接不同的是，內(nèi)連接至少要有一個(gè)同屬于兩個(gè)表的行符合連接條件時(shí)才會(huì)返回行，外連接會(huì)返回符合任意條件的行
--他的表有主從之分，他用主表中的每行去匹配從表中的，與內(nèi)連不同的是，他不會(huì)丟棄沒有匹配的行，而是填充null給從結(jié)果集
--左外連接
select st.st_id as '學(xué)生編號(hào)', st.st_name as '學(xué)生姓名',cl.cl_id as '班級(jí)編號(hào)',cl_class as '班級(jí)名稱'
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 '課程安排編號(hào)'
,cl.cl_id as '班級(jí)編號(hào)',cl.cl_class as '班級(jí)名稱'
,co.co_id as '課程ID',co.co_name as '課程名稱',co.co_num as '課時(shí)數(shù)'
,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
--====================右外連結(jié) =======================
select st.st_id as '學(xué)生編號(hào)', st.st_name as '學(xué)生姓名',cl.cl_id as '班級(jí)編號(hào)',cl_class as '班級(jí)名稱'
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 '課程安排編號(hào)'
,cl.cl_id as '班級(jí)編號(hào)',cl.cl_class as '班級(jí)名稱'
,co.co_id as '課程ID',co.co_name as '課程名稱',co.co_num as '課時(shí)數(shù)'
,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 '學(xué)生編號(hào)', st.st_name as '學(xué)生姓名',cl.cl_id as '班級(jí)編號(hào)',cl_class as '班級(jí)名稱'
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 '課程安排編號(hào)'
,cl.cl_id as '班級(jí)編號(hào)',cl.cl_class as '班級(jí)名稱'
,co.co_id as '課程ID',co.co_name as '課程名稱',co.co_num as '課時(shí)數(shù)'
,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子句時(shí)，返回的是兩個(gè)表中所有數(shù)據(jù)行的笛卡爾積（第一個(gè)表中的行乘以第二個(gè)表中的行）
--用學(xué)生和班級(jí)表做交叉查詢
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
--=========自連接===
-----------------先臨時(shí)創(chuàng)建一個(gè)表-------------
create table zone(
id int primary key identity(1,1) not null,
z_zone varchar(30),
z_id int references zone(id))
--大家試下，這里是否可以給個(gè)默認(rèn)值
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
--擴(kuò)展應(yīng)用下
select b.z_zone,count(a.z_zone) as '轄區(qū)數(shù)' from zone as a inner join zone as b on a.z_id=b.id group by b.z_zone
--簡(jiǎn)單說就是自己連接自己，換言之對(duì)同一個(gè)表進(jìn)行連接操作
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
--我們發(fā)現(xiàn)有人等于自己，那么增加一個(gè)條件
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
【 1 】 【 2 】 【 3 】 【 4 】 【 5 】 |
