实验4 SQL 语言——SELECT 查询操作1实实验验44 S S Q Q L L 语语言言————S S E E L L E E C C T T 查查询询操操作作实验示例实验示例中要使用包括如下三个表的“教学管理”数据库JXGL ：(1)学生表Student ，由学号(Sno)、姓名(Sname)、性别(Ssex)、年龄(Sage)、所在系(Sdept)五个属性组成，记作：Student(Sno,Sname,Ssex,Sage,Sdept)，其中主码为Sno 。

(2)课程表Course ，由课程号(Cno)、课程名(Cname)、先修课号(Cpno)、学分(Ccredit)四个属性组成，记作：Course(Cno,Cname,Cpno,Ccredit)，其中主码为Cno 。

(3)学生选课SC ，由学号(Sno)、课程号(Cno)、成绩(Grade)三个属性组成，记作：SC(Sno,Cno,Grade)，其中主码为(SNO,CNO)。

1、在SQL SERVER 查询分析器或企业管理器(以具有相应操作权限的某用户登录)的SQL 操作窗口中执行如下命令创建数据库。

需要说明的是不同数据库系统其创建数据库的命令或方式有所不同。

CREATE DATABASE JXGL2、刷新数据库目录后，选择新出现的JXGL 数据库，在SQL 操作窗口中，创建Student 、SC 、Course 三表及表记录插入命令如下：Create Table Student( Sno CHAR(5) NOT NULL PRIMARY KEY(Sno),Sname VARCHAR(20),Sage SMALLINT CHECK(Sage>=15 AND Sage<=45),Ssex CHAR(2) DEFAULT '男' CHECK (Ssex='男' OR Ssex='女'),Sdept CHAR(2));Create Table Course( Cno CHAR(2) NOT NULL PRIMARY KEY(Cno),Cname VARCHAR(20),Cpno CHAR(2),Ccredit SMALLINT);Create Table SC( Sno CHAR(5) NOT NULL CONSTRAINT S_F FOREIGN KEY REFERENCES Student(Sno), Cno CHAR(2) NOT NULL,Grade SMALLINT CHECK ((Grade IS NULL) OR (Grade BETWEEN 0 AND 100)),PRIMARY KEY(Sno,Cno),数据库原理与应用实验指导2FOREIGN KEY(Cno) REFERENCES Course(Cno));INSERT INTO Student VALUES('98001','钱横',18,'男','CS');INSERT INTO Student VALUES('98002','王林',19,'女','CS');INSERT INTO Student VALUES('98003','李民',20,'男','IS');INSERT INTO Student VALUES('98004','赵三',16,'女','MA');INSERT INTO Course VALUES('1','数据库系统', '5',4);INSERT INTO Course VALUES('2','数学分析',null ,2);INSERT INTO Course VALUES('3','信息系统导论','1',3);INSERT INTO Course VALUES('4','操作系统原理','6',3);INSERT INTO Course VALUES('5','数据结构','7',4);INSERT INTO Course VALUES('6','数据处理基础',null,4);INSERT INTO Course VALUES('7','C 语言','6',3);INSERT INTO SC VALUES('98001','1',87);INSERT INTO SC VALUES('98001','2',67);INSERT INTO SC VALUES('98001','3',90);INSERT INTO SC VALUES('98002','2',95);INSERT INTO SC VALUES('98002','3',88);[例1] 查考试成绩大于等于90的学生的学号。

SELECT DISTINCT SnoFROM SCWHERE Grade>=90;这里使用了DISTINCT 短语，当一个学生有多门课程成绩大于等于90时，他的学号也只列一次。

执行结果如图4-1所示。

图4-1 在查询分析器中的查询执行情况[例2] 查年龄大于18，并不是信息系（IS ）与数学系（MA ）的学生的姓名和性别。

SELECT Sname,SsexFROM StudentWHERE Sage>18 AND Sdept NOT IN ('IS', 'MA');在企业管理器中的执行情况如图4-2所示。

图4-2 在企业管理器中的查询执行情况[说明]（1）在企业管理器中打开子窗口的方法：先选中数据库→选中表→在任一表上，按鼠标右键→从快捷菜单中选择“打开表”→点击“返回所有行”子菜单项→在打开返回表内容的子窗口后，按企业管理器工具栏上的“SQL ”图标，即能把子窗口分为上下两部分，上面部分能输入不同的SQL 命令来执行，执行时按企业管理器工具栏上的“运行”图标即可；（2）限于篇幅，其它查询命令的执行窗口与运行情况类似于上两图，原则将实验4 SQL 语言——SELECT 查询操作3不再列出。

[例3] 查以”MIS_”开头，且倒数第二个汉字为“导”字的课程的详细情况。

SELECT * FROM CourseWHERE Cname LIKE ’MIS#_%导_’ ESCAPE ’#’;[例4] 查询选修了课程的学生人数。

SELECT COUNT(DISTINCT Sno) /* 加DISTINCT 去掉重复值后计数 */FROM SC;[例5] 查询计算机系（CS ）选修了2门及以上课程的学生的学号。

SELECT Student.SnoFROM Student,SCWHERE Sdept='CS' AND Student.Sno=SC.SnoGROUP BY Student.Sno HAVING COUNT(*)>=2;[例6] 查询Student 表与SC 表的广义笛卡尔积。

Select Student.*,SC.*From Student,SC;或 Select Student.*,SC.*From Student Cross Join SC;[例7] 查询Student 表与SC 表基于学号SNO 的等值连接。

Select *From Student,SCWHERE Student.Sno=SC.Sno;[例8] 查询Student 表与SC 表基于学号SNO 的自然连接。

SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, GradeFROM Student, SC WHERE Student.Sno=SC.Sno;或 SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, GradeFROM Student INNER JOIN SC ON Student.Sno=SC.Sno;[例9] 查询课程之先修课的先修课(自身连接例)。

SELECT o, SECOND.PcnoFROM Course FIRST, Course SECONDWHERE FIRST.Pcno=o;我们为Course 表取两个别名FIRST 与SECOND ，这样就可以在SELECT 子句和WHERE 子句中的属性名前分别用这两个别名加以区分。

[例10] 查询学生及其课程、成绩等情况（不管是否选课，均需列出学生信息）。

SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, GradeFROM Student Left Outer JOIN SC ON Student.Sno=SC.Sno;数据库原理与应用实验指导4[例11] 查询学生及其课程成绩与课程及其学生选修成绩的明细情况（要求学生与课程均需全部列出）。

SELECT Student.Sno, Sname, Ssex, Sage, Sdept, o, Grade, cname, cpno, ccreditFROM Student Left Outer JOIN SC ON Student.Sno=SC.Sno Full Outer join Course on o=o;[例12] 查询性别为男、课程成绩及格的学生信息及课程号、成绩。

SELECT Student.*,Cno,GradeFROM STUDENT INNER JOIN ON Student.Sno=SC.SnoWHERE SSEX=’男’ AND GRADE >=60[例13] 查询与“钱横”在同一个系学习的学生信息。

SELECT * FROM StudentWHERE Sdept IN(SELECT SdeptFROM StudentWHERE Sname='钱横');或 SELECT * FROM StudentWHERE Sdept =( SELECT SdeptFROM StudentWHERE Sname='钱横'); -- 当子查询为单列单行值时可以用“=”或 SELECT S1.*FROM Student S1,Student S2WHERE S1.Sdept=S2.Sdept AND S2.Sname='钱横';一般来说，连接查询可以替换大多数的嵌套子查询。