Sqlserver多表查詢

標籤：io   ar   os   sp   on   資料   2014   bs   ad   

作者: gw

------------------------資料查詢----------------------------

 

--(select sno ,avg(grade) as avg_grade from sc group by sno) as st2

--select student.sno from student left outer join sc on(student.sno=sc.cno);

--select student.sno,sname,ssex,sage,sdept,sc.cno,sc.grade from student left outer join sc on(student.sno=sc.sno);

--select student.sno,sname,ssex,sage,sdept,sc.cno,sc.grade from student , sc where student.sno=sc.sno;

--select student.sno ,sname,cno,grade from student ,sc where student.sno=sc.sno and sc.cno=‘2‘ and sc.grade>80

--查詢每個學生的學號,姓名,選修的課程名及成績
--select student.sno, sname ,course.cname, sc.grade from sc,student,course where student.sno=sc.sno and sc.cno=course.cno

--select sno,sname,sdept from student where sdept in( select sdept from student where sname=‘何大勇‘);

--查詢每個學生大於它的平均成績的的課程的課程名 1
--select distinct sno ,cno from sc x where grade >= (select avg(grade) from sc y where y.sno=x.sno )

-- 用查詢的結果作為一個新的集合
--查詢每個學生大於它的平均成績的的課程的課程名 2
--select st1.sno,st1.cno from
-- sc st1,(select sno ,avg(grade) as avg_grade from sc group by sno) as st2
-- where st1.sno=st2.sno and st1.grade>=st2.avg_grade;

 

--查詢每個學生的平均成績
--select sno,avg(grade) as avg_grade from sc x group by sno;

--查詢平均成績大於80的學生的學號
--select sno,avg(grade) as avg_grade from sc x group by sno having avg(grade)>80;

--select distinct sno ,cno from sc x where x.grade>=(select avg(y.grade) from sc y where y.cno = x.cno);

 

--select cno,avg(grade )as m_grade from sc group by cno ;

-- 查詢大於該課程平均成績的學生的學號,成績,課程號
--select distinct sno,grade,cno from sc x where x.grade>=(select avg(y.grade) from sc y where x.cno=y.cno );

--select sname , sage,sdept from student where sage<any (select sage from student where sdept=‘cs‘) and sdept!=‘cs‘;

--select sname , sage,sdept from student where sage>any (select sage from student where sdept=‘cs‘) and sdept!=‘cs‘;

--利用top只返回前三行
--select top 3 * from student;

--select b.sname ,a.cno , a.sno from
-- student b, (select cno,sno from sc) as a
-- where b.sno=a.sno;

--建立系部資料庫
--create table dept(dno int ,dname varchar(20),dmoster varchar(10),primary key(dno));

--drop table dept;

--insert into table dept (dno,dmoster) values(10299,‘113‘);

--select * from student;

--sname,cno from student,sc
--where not exists (select * from sc where sno=student.sno and cno=‘1‘)and sc.sno=student.sno

--臃腫的查詢語句
--select distinct sname from student,sc
--where exists (select * from sc b where sno=student.sno and b.cno=‘1‘)and sc.sno=student.sno

--select sname from student
--where exists (select * from sc b where sno=student.sno and b.cno=‘1‘)

--下面這句話是直接做一個笛卡爾積
--select sname,cno from student,sc

 

--查詢和高漸離在一個系的其他學生 1
--select sno,sname,sdept from student s1 where exists
--(select * from student s2 where s2.sdept=s1.sdept and s2.sname=‘高漸離‘) and s1.sname!=‘高漸離‘

--select* from student ;

-- 查詢和高漸離在一個系的其他學生 2
--select sno,sname,sdept from student where sdept in(select sdept from student s2 where s2.sname=‘高漸離‘) and sname !=‘高漸離‘

-- 查詢和高漸離在一個系的其他學生 3
--select sno,sname,sdept from student where sdept=(select sdept from student s2 where s2.sname=‘高漸離‘) and sname !=‘高漸離‘

 

--select sname from student
-- where not exists (select * from course
-- where not exists (select * from sc
-- where sno=student.sno and cno=course.cno))

--
--
--
--

-- 一直不理解exists這個語句 下面是從百度知道上搜尋出來的
--EXISTS或者NOT EXISTS是把主查詢的欄位傳到後邊的查詢中作為條件，傳回值是TRUE或者FALSE。
--EXISTS TRUE，那麼就是查詢條件成立，結果會顯示出來。
--NOT EXISTS TRUE，則為FALSE，查詢串連條件不成立。
--select * from course where not exists(select * from grade where grade.課程代號=course.課程代號)
--這個語句，是查詢course表中課程代號在grade中沒有出現的資料。
--看看grade表，課程編號有01到06，而COURSE表，有01到07，那麼07在GRADE表是不存在的,那麼,是合格。
--同樣select * from course where exists(select * from grade where grade.課程代號=course.課程代號)
--則是查詢COURSE的記錄條件為編號在GRADE中存在。那麼很明顯，結果是K01到K06的資料。
--另外，EXISTS和NOT EXISTS的作用可以用IN或NOT IN實現，但是exists效率要高。
-- 因為EXISTS和NOT EXISTS返回的結果是TRUE或者FALSE，那麼則在子查詢中，遇到第一個合格結果，
--就會退出查詢，而不會進行全表的檢索。
--而NOT IN或者IN，要把子查詢中的SELECT字句全部查詢出來才行。

--子查詢中，遇到第一個合格結果，就會退出查詢，而不會進行全表的檢索。

--select * from sc where sno in(‘2014003‘);

--select sno from sc s1 where cno =all (select * from sc where sno in(‘2014003‘));

/*
例1：查詢所有選修了1號課程的學生的姓名。
解法1：利用exists
首先取Student表中的一個元組，然後在SC表中依次找SC.Sno=該元組的Sno，並且對應的Cno=‘1‘，
如果存在，則外層查詢的where子句返回為真，則Student表中的該元組可以輸出。然後依次遍曆
Student表中的其他元組。
舉個例子：對於在學生表中學號等於2002151121這個元組，在SC表中第一條記錄即符合條件，
然後where 子句返回 true，所以該條元組可以輸出。然後依次遍曆。
select Sname
from Student
where exists
(
select *
from SC
where Sno = Student.Sno AND Cno=‘1‘
);
解法2：利用串連查詢
select Sname
from Student,SC
where Student.Sno=SC.Sno AND SC.Cno=‘1‘;

例2：查詢沒有選修1號課程的學生的姓名。
解：
select Sname
from Student
where not exists
(
select *
from SC
where Sno=Student.Sno AND Cno=‘1‘
);

例3：查詢選修了全部課程的學生姓名。
select Sname
from Student
where not exists
(
select *
from Course
where not exists
(
select *
from SC
where Sno=Student.Sno AND
Cno=Course.Cno
) );

--!!!在exists(not exists) 子句中 如果有多條記錄 依次對每條記錄進行判斷
--!!!某條記錄滿足條件就返回 true 剩下的就不用查詢了 在結合外層的exists(not exists)
--!!!如果外層為exists 該條記錄被選中 如果外層為not exists ,not true該條記錄被捨棄

 

尋找語義：查詢這樣的學生，沒有一門課程是他不選修的。
尋找過程：
首先，選取Student表中的一個元組，然後在依次判斷Course表中的每個元組是否可以輸出，只要有
一個課程可以輸出，則最外層查詢的where子句返回為false；而在判斷某個課程是否可以輸出時，
則要利用第三層查詢，利用當前的學號和當前的課程號，在SC表中查詢，如果存在，則第二層查詢
where子句返回false。至此，每一門課程都不可以輸出時，這個學號對應的元組才可以輸出。表示
這個學生選修了全部的課程。

例4：至少選修了學生2014006選修的全部課程的學生號碼。
--
select distinct Sno
from SC SCX
where not exists
( select *
from SC SCY
where SCY.Sno=‘2014006‘ AND
not exists
( select *
from SC SCZ
where SCZ.Sno=SCX.Sno AND
SCZ.Cno=SCY.Cno));
*/

/*
select distinct sno from sc s1
where not exists
(select * from sc s2 where s2.sno=‘2014006‘and not exists
(select * from sc s3 where s1.sno=s3.sno and s2.cno=s3.cno)
)

*/

/*
查詢語義：不存在這樣的課程y，學生2014006選修了y，而學生x沒選。
查詢過程：先在SCX表中選一條記錄，比方說第一條，然後再看SCY表中，只有SCY表中全部不能
輸出，第一層查詢的where子句才返回true，第一條記錄就可以輸出；所以就要一次查看SCY表
中的每一個元組，前三個，因為學號首先不滿足=200215122所以必然不能輸出，第四個的話，
就要看其AND後面的not exists返回什麼值，而這又取決於第三層查詢中是否存在滿足學號
等於SCX.Sno且課程號=SCY.Cno的元組，經查看，有 ，則返回false，所以第四個也不能輸
出，第五個類似，所以，第一層查詢的not exists返回true。所以第一條記錄可以輸出。
*/

/*
--如何判斷兩個集合 是否 A包含B
--依次判斷B的每一個元素是否在A中存在
--B中沒有一個元素 不在A中存在
select * from K where not exists
(select * from B where not exists
(select * from A where A.filed=b.filed and K...) )

*/

--集合的並操作可以用or來代替
--select distinct sno from sc where cno=‘4‘ or cno=‘5‘

--select sno from sc where cno=‘4‘ union select sno from sc where cno=‘5‘

--集合的交操作可以用and來代替
--select * from student where sage>=22 and sdept=‘cs‘

--集合的差操作可以用not in來代替 not in 後面是要除去的集合
--select * from student where sage>=22 and sdept not in
-- (select sdept from student s1 where sdept=‘cs‘)

--查詢電腦系 所有學生中除去年齡大於21歲的
--select * from student s1 where sdept=‘cs‘ and sage not in
-- (select sage from student s2
-- where s2.sage>=22 and s1.sno=s2.sno )

 

Sqlserver多表查詢