?這篇博文的均是以?SQL server 數據庫為基礎的基本查詢語句的實現。涉及到學生表選課表和課程表，基本操作都附加在查詢語句后，還請讀者留意。

?

?

查詢分析器下的語句實現：

?

create database scs; use SCS; create table student (sno char(7) primary key,sname char(10) not null,ssex char(2) ,sage tinyint,sdept char(20)); create table course (cno char(6) primary key,cname char(20) not null,credit tinyint ,semster tinyint );create table sc (sno char(7) ,cno char(6) ,grade smallint,primary key (sno,cno), ---主鍵為兩個的時候，不能用列級約束foreign key(sno) references student(sno), ---此時約束應該為表級約束，且foreign key（sno/cno），（sno/cno）在列級約束的時候可以省略，在表級約束不行foreign key(cno) references course(cno) ); select *from student insert into student values('1512101','李勇','男',19,'計算機系'), ('1512102','劉晨','男',20,'計算機系'), ('1512103','王敏','女',18,'計算機系'), ('1512104','李小玲','女',19,'計算機系'), ('1521101','張立','男',22,'信息系'), ('1521102','吳賓','女',21,'信息系'), ('1521103','張海','男',20,'信息系'), ('1531101','錢小平','女',18,'數學系'), ('1531102','王大力','男',19,'數學系')select * from course insert into course values('c001','計算機文化學',3,1), ('c002','高等數學',6,1), ('c003','高等數學',3,2), ('c004','大學英語',6,2), ('c005','java',2,3), ('c006','程序設計',3,3), ('c007','數據結構',5,4), ('c008','操作系統',4,4), ('c009','數據庫基礎',4,5)select *from sc insert into sc values ('1512101','c001',90), ('1512101','c002',86), ('1512101','c003',92), ('1512101','c005',88), ('1512101','c006',null), ('1512102','c001',76), ('1512102','c002',78), ('1512102','c005',66), ('1512104','c002',66), ('1512104','c005',78), ('1512104','c008',66), ('1521102','c001',82), ('1521102','c005',75), ('1521102','c007',92), ('1521102','c009',50), ('1521103','c002',68), ('1521103','c006',null), ('1521103','c007',null), ('1521103','c008',78), ('1531101','c001',80), ('1531101','c005',50), ('1531101','c007',45), ('1531102','c001',80), ('1531102','c002',75), ('1531102','c005',85), ('1531102','c009',88)select sno as 學號,cno as 課程號,grade as 成績 from sc ---查詢學生選課表的所有信息select sname as 姓名,sage as 年齡 ,sdept as 系名 from student where sdept='計算機系' --查詢計算機系的學生的姓名和年齡select sno as 學號,cno as 課程號, grade as 成績 from sc where grade between 70 and 80 --查詢成績在70-80之間的學生的學號課程號和成績select sname as 姓名,sage as 年齡,ssex as 性別, sdept as 系名 from student where sdept='計算機系' and ssex='男' and sage between 18 and 20 ---查詢計算機系年齡在18-20歲之間的且性別為男的學生的姓名和年齡select MAX(grade) as 最高分數 from sc where cno='c001' --查詢課程號c001的課程的最高分數 select MAX(sage) as 最大年齡,MIN(sage) as 最小年齡 from student where sdept='計算機系' ---查詢計算機系學生的最大年齡和最小年齡select sdept as 系名,count(*) as 人數 from student group by sdept --統計每個系的學生人數select cno as 課程號, COUNT(*) as 選課人數,MAX(grade) as 考試最高分 from sc group by cno ---統計每門課程的選課人數和考試的最高分select sno as 學號, COUNT(*)as 選課門數,sum(grade) as 考試總成績 from sc group by sno order by 選課門數 ---統計每個學生的選課門數和考試的總成績，并按選課門數的升序顯示結果select sno as 學號, grade =SUM(grade)from sc group by sno having SUM(grade)>200 ---10、查詢總成績超過200分的學生，要求列出學號，總成績select sname as 姓名,sdept as 所在系 from student join sc on student.sno=sc.sno where cno='c002' --查詢選修了c002號課程的學生的姓名和所在系 select sname as 姓名,cno as 課程,grade as 成績 from student join sc on student.sno=sc.sno where grade>80 order by grade desc ---查詢成績在80分以上的學生的姓名、課程號、成績，并按成績的降序排列結果 select sname as 姓名,student.sno as 學號,sdept as 所在系 from student join sc on student.sno=sc.sno join course on sc.cno=course.cno where course.cno=null ---查詢哪些學生沒有選課，要求列出其學號，姓名和所在系select cname as 課程名,semster as 開課學期 from course where semster = (select semster from course where cname='java' ---查詢與java在同一學期開設的課程的課程名和開課學期 )select sname as 姓名,sdept as 所在系,sage as 年齡 from student where sage=(select sage from student where sname='李勇') ---查詢和李勇年齡相同的學生的學號姓名和所在系select sname as 姓名,sdept as 所在系 from student join sc on student.sno=sc.sno join course on sc.cno=course.cnowhere course.cno='c001' ---查詢選修了c001課程的學生的姓名和所在系 ,這是連接查詢select sname as 姓名,sdept as 所在系 from student where sno=where (select cno from sc)----實驗三實驗四內容部分 select sname as 姓名,sdept as 所在系 from student where sno in (select sno from sc where cno='c001' -----查詢選修了“c001”號課程的學生的姓名和所在系 )---或者用到exists select sname ,sdept from student where exists (select *from sc where sno=student.sno and cno='c001' )select student.sno ,sname ,cno,grade from student join sc on student.sno=sc.sno where grade>80 and sc.sno in (select sno from student where sdept='數學系')-----查詢數學系成績80分以上的學生的學號、姓名、課程號和成績。select sname from student where sno in ( select sno from sc where grade=(select MAX(grade) from sc) ----查詢計算機系考試成績最高的學生的姓名 )and sdept='計算機系' select sname ,sdept from student where sno in (select sno from sc join course on sc.cno=course.cno where grade=(select MAX(grade) from sc) and cname='數據結構'------查詢數據結構考試成績最高的學生的姓名和所在系。 子查詢和連接查詢套用 )select sname ,sdept from student where sno in (select sno from sc where cno in (select cno from course where cname='數據結構' ------查詢數據結構考試成績最高的學生的姓名和所在系) and grade= (select MAX(grade) from sc)） ----1、建立一個存儲過程proc_ student，要求該存儲過程實現：查詢某一位學生是否選修某門課程， --若沒有選修，則該生對這門課程進行選修（即在SC表中插入相應的學生學號和課程號）；若已選修，則顯示學生學號、課程名稱及成績。 --執行存儲過程proc_ student。 ---、刪除所有新建的存儲過程。if exists( select * from sysobjects where name='proc_student' and type='p' ) drop proc proc_studentgo create proc proc_student (@no char(7),@no1 char(6)) as beginif exists (select * from student join sc on sc.sno=student.sno where sc.sno=@no and cno=@no1)select * from sc where sno=@no and cno =@no1else insert into sc(sno,cno)values(@no,@no1) endexec proc_student '1512101','c004'select *from scdrop proc proc_student

?

?

所有作品均為作者原創，正處在學習數據庫的道路上，希望學習數據庫的你們，可以從我的

分享中有所收獲。個人能力畢竟有限，如果大家有的地方可以進行查詢語句的優化，歡迎大家留言指正。

copyright@作者

?

?

更多精彩內容請關注公眾號：干貨分享錄

總結

以上是生活随笔為你收集整理的SQL server 数据库查询语句的基本实现的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。