程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> MSSQL >> 數據庫表的查詢操作（試驗二）

數據庫表的查詢操作（試驗二）

編輯：MSSQL

數據庫表的查詢操作（試驗二）。本站提示廣大學習愛好者：（數據庫表的查詢操作（試驗二））文章只能為提供參考，不一定能成為您想要的結果。以下是數據庫表的查詢操作（試驗二）正文

【試驗目標】:懂得SQL說話的應用，進一步懂得關系運算，穩固數據庫的基本常識。
【試驗請求】:控制應用Select語句停止各類查詢操作：單表查詢、多表銜接及查詢、嵌套查詢、聚集查詢等。
【試驗內容】
1、單表查詢
1．簡略查詢
翻開查詢剖析器，根樹立teacher表，並參加數據。從teacher表平分別檢索出教員的一切信息，和僅查詢教工號、姓名和職稱。語句以下：

select * from teacher
select tno, tname from teacher

如要查詢時轉變列題目的顯示，則從teacher表平分別檢索出教員教工號、姓名、電子郵箱信息並分離加上‘教員姓名'、‘教工號'、'電子郵箱'等題目信息。

select tno 工號, tname 姓名, temail 電子郵箱 from teacher

應用TOP症結字：分離從teacher中檢索出前2條及後面67%的教員的信息。

select top 2 * from teacher
select top 67 percent * from teacher

應用DISTINCT症結字：從teacher表中檢索出教員的職稱而且請求顯示的職稱不反復。select distinct tposition from teacher

2．用盤算列：將teacher表中各教員的姓名、教工號及工資按95%發放的信息，第2條語句將工資按95%發放後列名該為‘預發工資'。語句以下：

select tno tname ,tsalary*0.95 from teacher
select tno工號, tname姓名, tsalary*0.95 AS 預發工資 from teacher

3．應用ORDER BY子句對查詢的成果停止排序
應用ORDER BY語句可以對查詢的成果停止排序，ASC、DESC分離是升序和降序分列的症結字，體系默許的是升序分列。從teacher表中查詢工資年夜於2800的教員的教工號、姓名，並按升序分列，語句以下：

select tno, tname from teacher
WHERE tsalary>2800 order by tsalary ASC

4．前提查詢
(1)應用關系運算符：從teacher表中查詢收工作量年夜於288的教員材料，語句以下：

select * from teacherWHERE tamount>288 order by tamount DESC

(2)應用BETWEEN　AND謂詞：從teacher表中查詢收工作量界於144和288之間的教員材料，語句以下：

select * from teacher WHERE tamount between 144 and 288

(3) 應用IN謂詞：從teacher表中查詢出職稱為“傳授”或“副傳授”的教員的教工號、教員姓名、職稱及家庭住址，語句以下：

select tno,tname,tposition, taddress from teacher
WHERE tposition in ('傳授','副傳授')

(4)應用LIKE謂詞：從teacher表平分別檢索出姓'王'的教員的材料，或許姓名的第2個字是'莉'或'軒'的教員的材料，語句以下：

select * from teacher WHERE tname like '王%'
select * from teacher WHERE tname like '_[莉，軒]%'

2、多表查詢
數據庫各表中寄存著分歧的數據，用戶常常須要用多個表中的數據來組合提煉出所須要的信息，假如一個查詢須要對多個表停止操作，就稱為聯系關系查詢，聯系關系查詢的成果集或成果表稱為表之間的銜接。聯系關系查詢現實上是經由過程各各表之間配合列的聯系關系來查詢數據的，它是關系數據庫查詢最根本的特點。
1．SQL 2000兼容2種銜接情勢：用於FROM子句的ANSI銜接語法和用於WHERE子句的SQL SERVER銜接語法情勢。
從student、course和SC三張表中檢索先生的學號、姓名、進修課程號、進修課程名及課程成就，語句以下：

select student.sno, sname, cno, grade
from student inner join SC on student.sno=SC.sno

select student.sname,sc.grade
from student,sc
WHERE student.sno=sc.sno 

select student.sno,student.sname,sc.cno,course.cname,sc.grade
from student,sc,course 
WHERE student.sno=sc.sno and sc.cno=course.cno

２．應用UNION子句停止查詢
應用UNION子句可以將一個或許多個表的某些數據類型雷同的列顯示在統一列上。如從teacher表中列出教工號、姓名並從student表中列出學號及先生姓名，語句及查詢成果以下：

select sno AS 學號或工號, sname AS 姓名 from student 
union 
select tno, tname from teacher

3．用GROUP子句停止查詢
假如要在數據檢索時對表中數據依照必定前提停止分組匯總或求均勻值，就要在SELECT語句中與GROUP BY子句一路應用聚集函數。應用GROUP BY子句停止數據檢索可獲得數據分類的匯總統計、均勻值或其他統計信息。
(1) 應用不帶HAVING的GROUP BY子句。
應用不帶HAVING 的GROUP BY子句匯總出SC表中的先生的學號及總成就，語句以下：

select '學號'=sno,'總成就'=SUM(Grade) 
from SC
group by Sno

(2)應用帶HAVING 的GROUP BY子句。
應用帶HAVING 的GROUP BY子句匯總出SC表中總分年夜於450分的先生的學號及總成就，語句以下：

select '學號'=sno,'總成就'=SUM(Grade) from SC
group by Sno 
Having SUM(Grade)>160

4．用COMPUTE和COMPUTE BY子句停止查詢
應用COMPUTE和COMPUTE BY 既能閱讀數據又能看到統計的成果。
(1) 用COMPUTE子句匯總出SC表中每一個先生的學號及總成就，語句以下：

select '學號'=sno,'成就'=Grade from SC 
order by sno COMPUTE SUM(Grade)

(2)用COMPUTE BY 子句按學號匯總出SC表中每一個先生的學號及總成就，語句以下：

select '學號'=sno,'成就'=Grade from SC 
ORDER BY Sno COMPUTE SUM(Grade) by sno

不雅察履行COMPUTE和COMPUTE BY子句的成果有甚麼分歧？

5．嵌套查詢
(1) 應用IN或NOT IN症結字
應用IN症結字查詢出j10011班一切男生的學號、課程號及響應的成就，語句以下：

select SC.sno , SC.cno,SC.grade
FROM SC 
WHERE sno IN 
 ( SELECT sno FROM student
 WHERE Sclass='j10011' AND Ssex='男')

應用IN症結字查詢出與教員分歧名的先生(學號，姓名)，語句以下：

select sno 學號, sname 姓名 
from student 
where sname not in (select tname from teacher)

(2) 應用EXISTS 或NOT EXISTS症結字。

應用EXISTS症結字查詢出‘j10011'班的先生的學號、課程號及響應的成就，語句以下：

SELECT SC.sno，SC.cno，SC.grade 
FROM SC
WHERE EXISTS 
（ SELECT * FROM student
 WHERE SC.sno=student.sno AND student.sclass=‘j10011' ）

以上就是表的查詢操作試驗全體內容，願望對年夜家的進修有所贊助，年夜家親身著手理論一下。

）查詢選修了課程的先生人數；
select count(distinct sno)人數from Score;
（36）在一切課程中查詢最高分的先生學號和成就；
select sno,grade from Score where grade =(select max(grade)from Score )

select distinct a.* from Score a where a.sno IN (select top 1 Score.sno from Score where Score.cno = a.cno order by grade desc)
（37）查詢進修“C001”課程的先生最高分數；
select max(grade)最高分數from Score where cno='C001'
（38）盤算各個課程號與響應的選課人數；
select count(sno) 選課人數from Score group by cno;
（39）查詢“盤算機系”選修了兩門課程以上的先生學號、姓名；
select Student.sno,sname from Student where Student.sno in
(select Student.sno from Student,Score where
sdept='盤算機系'and Student.sno=Score.sno group by Student.sno having count(cno)>=2);
（40）天然銜接student和score表；
select student.*,Score.grade from student ,Score where student.sno=Score.sno;
（41）應用本身銜接查詢每門課程的直接先行課（即先行課的先行課）
select a.cno,b.cpno from Course a,Course b where a.cpno=b.cno;
（42）應用復合前提銜接查詢選修“c001”號課程且成就在90分以上的一切同窗；
select sname,grade from student,Score where Student.sno=Score.sno and cno='C001' and grade>=90;
（43）應用復合前提銜接查詢每一個先生選修的課程名及其成就；
select Student.sno,sname,cname,grade from Course,Score,Student where Course.cno=Score.cno and student.sno=Score.sno;
（44）查詢選修了全體課程的先生；
select Sname from Student where not exists (select * from Course where not exists(select * from Score where Sno=Student.Sno and Cno=Course.Cno))
（45）查詢一切選修了C001號課程的先生學號、姓名；
select student.sno,sname from student,Score where student.sno=Score.sno and cno='C001';
（46）查詢選修了課程C001或C007的先生學號、姓名；
[code]select student.sno,sname,cno from student,Score where student.sno=Score.sno and cno in ('C001','C007');[/code]
（47）查詢“盤算機系”的先生及年紀不年夜於23歲的先生；
select sno ,sname,2014-year(sbirth) age ,sclass from student where sdept='盤算機系' or 2014-year(sbirth)<=23;
（48）查詢既選修了課程C001又選修了課程C007的一切先生學號、姓名；
select student.sno,sname from student,Score where student.sno=Score.sno and cno='C001' and student.sno in (select student.sno from student,Score where student.sno=Score.sno and cno='C007')
（49）查詢選修了課程名為“數據庫道理”的先生的學號、姓名、性別、年紀；
select student.sno ,sname,ssex,cname,2011-year(sbirth) age from student,Score,Course where student.sno=Score.sno and Score.cno=Course.cno and cname='數據庫道理';
（50）查詢其他班中比“盤算機系”一切先生年紀都小的先生名單；
select sno,sname ,2014-year(sbirth) age from student where 2014-year(sbirth)<(select min(2014-year(sbirth)) from student where sclass='計61')and sclass !='計61';
（51）查詢與“炎天”在統一個系進修的先生學號、姓名、性別、年紀；
select sno,sname,ssex,2014-year(sbirth) age from student where sdept=(select sdept from student where sname='炎天') and sname!='炎天'
（52）樹立“盤算機系”先生的視圖1；
create view view_student
as select sno,sname,ssex,sbirth,sclass from student where sclass='13z收集'
（53）樹立“盤算機系”先生的視圖2，並請求停止修正與拔出時，仍須包管該視圖只要“盤算機系”班先生；
create view view_student2
as select sno,sname,ssex,sbirth,sclass from student where sclass='13z收集' with check option;
（54）樹立“盤算機系”選修了“C001”課程的先生的視圖，界說視圖名為“v_cs_C001_student1”；
create view v_cs_C001_student1
as select student.sno,sname,ssex,sbirth,sclass from Student ,Score where
student.sno=Score.sno and sclass='13z收集' and cno='C001';
（55）樹立“盤算機系”班選修了“C001”課程且成就在90分以上的先生的視圖，界說視圖名為“cs_c001_student2”；
create view cs_c001_student2
as
select student.sno,sname ,ssex,sbirth,sclass,cno from student,Score where
student.sno=Score.sno and cno='C001' and sclass='13z收集'and student.sno in (select student.sno from student,Score where student.sno=Score.sno and grade>90)
（56）界說一個反應先生年紀的視圖，界說視圖名為“v_birth_student”；
create view v_birth_student
as
select sno,sname,2014-year(sbirth) age from student
（57）將先生表中一切女生記載界說為一個視圖，視圖名為“v_female_student”；
create view v_female_student
as
select * from student where ssex='女';
（58）將先生的學號及其均勻成就界說為一個視圖，視圖名為“v_average_student”；
create view v_average_student
as
select sno,avg(grade) avscore from Score group by sno;
（59）在“盤算機系”先生視圖中找出年紀小於22歲的先生；
select * from view_student where 2014-year(sbirth)<=22;
（60）應用視圖查詢“盤算機系”選修了“C001”課程的先生；
select * from v_cs_C001_student1;
（61）經由過程（52）中的“盤算機系”視圖修正某個先生的名字；
update view_student set sname='王某某'where sno=04261001;
（62）經由過程（53）中的“盤算機系”視圖，拔出一個新先生記載。
insert into view_student2(sno,sname,ssex,sbirth,sclass) values ('04262004','張某某','男','1987/11/09','計');
（63）經由過程（53）中的“盤算機系”視圖，刪除一個先生記載。
delete from view_student2 where sno='04262004'and sname='張某某';