关系数据库查询语言SQL.docx
《关系数据库查询语言SQL.docx》由会员分享，可在线阅读，更多相关《关系数据库查询语言SQL.docx（35页珍藏版）》请在冰豆网上搜索。
关系数据库查询语言SQL
授课方式：
以下所有的例子都在sqlserver中进行现场调试其执行结果，或者让学生上讲台练习，让学生体会其具体的含义
第四章关系数据库查询语言SQL（8学时）
第1节关系数据库标准语言SQL——查询部分
一、SQL概述
1．SQL标准
SQL（StructuredQueryLanguage）：
结构化数据库查询语言。
SQL作为一个ANSI标准，现在最新的标准是SQL99！
是介于关系代数和关系演算之间的结构化查询语言，功能包括数据查询（DataQuery
）、数据操纵（DataManipulation）、数据定义（DataDefinition）、数据控制（DataControl）
2．SQL的特点
1）综合统一
SQL语言集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL功能于一体，语言风格统一。
可独立完成数据库生命周期中的全部活动，包括：
定义关系模式、建立数据库、插入数据、查询、更新、维护、数据库重构、数据库安全性控制等一系列操作要求，为数据库应用系统开发提供了良好的环境。
由于关系模型中实体间连续军用关系表示，这种数据结构单一性带来了数据操作的统一，查找、插入、删除、修改等每一种操作只需要一种操作符，从而克服了非关系系统由于信息表示方式多样性带来的操作复杂性。
2）高度非过程化
SQL语言只要提出“做什么”，无须指明“怎么做”！
！
用户无须了解存取路径，存取路径的选择及SQL语句的操作过程由系统自动完成。
减轻了用户的负担，提高了数据独立性。
3）面向集合的操作方式
Sql语言采用集合操作方式，不仅操作对象、查询结果可以是元组集合，而且一次插入、删除、更新操作的对象可是元组的集合。
4）以同一种语法结构提供两种使用方式
既是自含式语言，又是嵌入式语言。
自含式语言：
能独立的用于联机交互的使用方式，用户可以在终端键盘上直接键入SQL命令对数据库进行操作。
在SQLSERVER2000种现场演示SQL语句的操作。
嵌入式语言：
SQL语句能嵌入到高级语言程序中供程序员设计时使用。
5）语言简洁，易学易用
功能强大，但是语言十分简捷，完成核心功能共用了9个动词。
因此其使用技巧上要多练习。
SQL语言的动词如下：
SQL功能
动词
数据查询
SELECT
数据定义
CREATE，DROP，ALTER
数据操纵
INSERT，UPDATE，DELETE
数据控制
GRANT，REMOVE
附：
SQL语言功能表
3．SQL语言的基本概念
SQL语言支持关系数据库三级模式结构：
外模式对应于视图（VIEW）和部分基本表（BASETable），模式对应于基本表，内模式对应于存储文件。
基本表：
本身独立存在的表，在SQL中一个关系对应一个表。
一个（或多个）基本表对应一个存储文件，一个表壳带若干个索引，索引也存放在存储文件中。
视图：
从一个或几个基本表导出的表。
本身不独立存储在数据库中，即数据库中只存放视图的定义不存放视图对应的数据，这些数据仍存放在导出这个视图的基本表中，因此视图是一个虚表。
视图与基本表等同，可在视图上再定义视图。
SQL语言可对基本表和视图进行各种操作，二者都是关系。
二、数据定义
关系数据库三级模式：
模式、外模式、内模式中对应的基本对象为：
表，视图和索引。
故数据定义功能包括定义表、定义视图、定义索引。
由于视图和索引是依附于基本表的，故不提供修改操作，如需修改可删除后再建立！
数据定义语句如下：
1．定义、删除与修改基本表
1）定义基本表（CREATETABLE）
CTEATETABLE<表名>（<列名><数据类型>[列级完整性约束条件]
[，<列名><数据类型>[列级完整性约束条件]……
[，[表级完整性约束条件]]；
<>是标识符，实际书写时其内容必须有，但是尖括号不能有
[]表示其内部的项目可以有，也可没有。
例：
创建一个“学生”表student，由学号sno、姓名sname、性别ssex、年龄sage、所在系sdept五个属性组成，其中学号不能为空，值是唯一的，且姓名的值也唯一。
CREATETABLEstudent（snochar（5）notnulluique,
snamechar（20）unique,
ssexchar
（1）,
sageint,
sdeptchar（15））;
定义表的各个属性要指明数据类型和长度。
不同的数据库支持的数据类型不同。
SQLSERVER中的数据类型如下：
Binary：
最多8000个二进制字节；bit：
整数0或1；char：
定长字符串
Datetime：
日期和时间值，范围1/1/1753到12/31/9999
Float：
浮点小数，范围是-1.79e+308~1.79e+308~；image：
变长位串，最大值为2^31
……
2）修改基本表
格式：
ALTERTABLE<表名>
[ADD<新列名><数据类型>[完整性约束]]//添加新列
[DROP<完整性约束>]//删除完整性约束条件
[MODIFY<列名><数据类型>];//修改原来定义的列
例：
向学生表Student表中增加“入学时间”列，数据类型为日期型，将年龄的数据类型改为半字长整型，删除学生姓名必须取唯一值约束。
ALTERTABLEStudentADDScomeDATE;
//MODIFYSageSMALLINT未调试成功
//DROPUNIQUE（Sname）
思考如何实现删除一定的列？
？
？
？
3）删除基本表（物理删除）
DROPTABLE<表名>
例：
删除student表
DROPTABLEstudent;
4）如何通过已建立的表复制到一个新表中
*只复制表的结构
SELECTTOP0*INTOnewtableFROMoldtable；
*同时将数据也复制进去，去掉top0即可
SELECT*INTOjiamengFROMhezuo
思考：
能否只复制部分列及其数据
2．建立和删除索引
索引是提高查询速度的有效手段，由数据库管理员DBA或表的属主负责完成。
1）索引的建立
格式：
CREATE[UNIQUE][CLUSTER]INDEX<索引名>
ON<表名>（<列名>[<次序>][,<列名>次序>]]…）；
说明：
a）索引可以建立在一列或多个列上，各列名之间用逗号隔开；
b）每个<列名>可用<次序>指定索引值排列顺序，可选ASC（升序）或DESC（降序），缺省为ASC；
c）UNIQUE表明此索引每一个索引值只能对应唯一的数据记录值
d）CLUSTER表示要建立的索引是聚簇索引，一个表最多只能建立一个聚簇索引。
补充：
索引与主键的区别
主键：
能够唯一的标识每行的一列或列的集合。
索引：
提高查询速度的方式。
很多数据库自动为表的主键建立索引。
索引种类：
a）唯一索引：
不允许两行有相同的索引。
B）聚簇（群集）索引：
在内部排序值得索引，索引排序结构规定了行的存储结构。
必须唯一。
即索引项的顺序与表中记录的物理顺序是一致的索引组织。
C）非群集索引：
对表中的行成索引，但并不改变它们的存储顺序。
不要求唯一
例：
为学生-课程数据库中的student，course，sc三个表建立索引。
其中student按学号升序建立唯一索引，course按课程号升序建立唯一索引，sc按学号升序和课程号降序建立唯一索引。
CREATEUNIQUEINDEXStusnoONStudent（Sno）;
CREATEUNIQUEINDEXCoucnoONCourse（Cno）;
CREATEUNIQUEINDEXScnoONSC（SnoASC，CnoDESC）;
2）删除索引
格式：
DROPINDEX<索引名>;
例：
DROPINDEXStusname;
三、查询
使用如下的三个表
学生表：
Student（Sno,Ssex,Sage,Sdept）Sno为主码
课程表：
Course（Cno,Cname,Cpno,Ccredit）Cno为主码
课程号、课程名、选修课号、学分组成
学生选课表：
SC（Sno,Cno,Grade）（Sno，Cno）为主码
查询的种类（四类）：
A）单表查询b）连接查询c）嵌套查询d）集合查询
查询的一般格式：
SELECT[ALL|DISTINCT]<目标列表达式>[,<目标列表达式>]…
FROM<表名或视图名>[,<表名或视图名>]…
[WHERE<条件表达式>]
[GROUPBY<列名1>[HAVING<条件表达式>]]
[ORDERBY<列名2>[ASC|DESC]];
（一）单表查询：
仅涉及一个表的查询
1．选择一个表的若干列
1）查询指定列
例：
查询全体学生的学号与姓名
SELECTSno,snameFROMStudent;
练习：
查询全体学生的姓名、学号、所在系
SELECTsname,sno,sdeptFROMstudent
2）查询全部列
将表中的所有属性列都选出来，方法有二：
将各列名都列出来；使用*代替目标列
如：
查询全体学生的详细记录
SELECT*FROMStudent;等价于SELECTSno,Ssex,Sage,SdeptFROMStudent
3）查询经过计算的值
SELECT子句的<目标列表达式>不仅可是表中的属性列，也可是表达式。
例：
查询全体学生的姓名及其出生年份
SELECTSname,2006-sageFROMStudent
目标列的内容：
属性列，表达式，字符串常量，函数等。
例：
查询全体学生的姓名、出生年份和所有系，要求用小写字母标示所有系名。
SELECTSname,'YearofBirth:
',2006-Sage,LOWER（Sdept）FROMStudent;
可以指定别名来改变查询结果的列标题
SELECTSnameNAME,'YearofBirth:
'BIRTH,2006-SageBIRTHDAY,LOWER（Sdept）DEPARTMENTFROMStudent;
2.选择表中若干元组
1）消除取值重复的行——DISTINCT
例：
SELECTDISTINCTSnoFROMSC;缺省为ALL，即保留表中取值重复的行。
2）查询满足条件的元组——WHERE子句
查询条件共有六大类，功能如表
查询条件
谓词
功能
比较
=，>,<,>=,<=,!
=,<>,!
=,!
>,!
<;not+比较运算符
用于比较，not用于对比较运算符取反
确定范围
BETWEENAND，NOTBETWEENAND
查询属性值在或不在指定范围内的元组
确定集合
IN，NOTIN
查找属性值属于指定集合的元组
字符匹配
LIKE，NOTLIKE
用来进行字符串的匹配
空值
ISNULL，ISNOTNULL
空值查询
多重条件
AND，OR
连接多个查询条件
A）比较大小
例：
查询计算机系全体学生的名单
SELECTSnameFROMStudentWHERESdept='计算机‘;
例：
查询所有年龄在20岁以下的学生姓名及其年龄
SELECTSname,SageFROMStudentWHERESage<20;
例：
查询考试成绩有不及格的学生的学号
SELECTDISTINCTSnoFROMscWHEREgrade<60;
B）确定范围
谓词BETWEEN…AND…和NOTBETWEEN…AND…可以用来查找属性值在（或不在）指定范围内的元组，其中BETWEEN后面是范围的下限，AND后面是范围的上限。
例：
查询年龄在20至23岁之间的学生姓名、性别和年龄
SELECTSname,Ssex,SageFROMStudentWHERESageBETWEEN20AND23
C）确定集合
谓词IN用来查找属性值属于指定集合的元组。
例：
查询信息系（IS）、数学系（MA）和计算机系（CS）的学生的姓名和性别
SELECTSname,SsexFROMStudentWHERESdeptIN（'信息系','数学系','计算机'）;
D）字符匹配
谓词LIKE可以用来进行字符串的匹配。
格式：
[NOT]LIKE’<匹配串>’[ESCAPE’<换码字符>’]
含义：
查找指定的属性列值与<匹配串>相匹配的元组。
<匹配串>可以是完整的字符串，也可含有通配符%和_.
%代表任意长度（可为0）的字符串。
例对于a%b，串acb，adcb，ab都匹配。
_代表任意单个字符。
对于a_b，串acb，afb都匹配。
例：
查询学号为95001的学生的详细情况
SELECT*FROMStudentWHERESnoLIKE‘95001’;
等价于SELECT*FROMStudentWHERESno=‘95001’;
如果LIKE后面的匹配串中不含通配符，则可用=运算符替代LIKE
例：
查询所有姓张的学生的姓名、学号和性别
SELECTSname,Sno,SsexFROMStudentWHERESnameLIKE'张%'
查询名字中第二个字为“华”字的学生的姓名和学号
SELECTSname，SnoFROMStudentWHERESnameLIKE‘__阳%’；//一个汉字占两个字节
查询DB_Design课程的课程号和学分
SELECTCno，CcreditFROMCourseWHERECnameLIKE‘DB\_Design’ESCAPE’\’
ESCAPE’\’表示\是转义字符，使得_不再是通配符，转义为普通字符“_”
E）涉及空值的查询
例：
查询缺少成绩的学生的学号和相应的课程号
SELECTSno,CnoFROMSCWHEREGradeISNULL;
F）多重条件查询
逻辑运算符AND和OR用来连接多个查询条件。
AND的优先级高于OR，可用括号改变优先级。
例：
查询传媒技术系年龄在22岁以下的学生姓名
SELECTSnameFROMStudent
WHERESage<22ANDSdept='传媒技术系';
3.对查询结果排序
用ORDERBY子句对查询结果按照一个或多个属性列的升序或降序排序，缺省为ASC
例：
查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。
SELECTSno,GradeFROMSCWHERECno='3'ORDERBYGradeDESC
4．集函数的使用
集函数功能如下
COUNT（[DISTINCT|ALL]*）统计元组个数
COUNT（DISTINCT|ALL]<列名>）统计一列中值得个数
SUM（[DISTINCT|ALL]<列名>）统计一列中值得总合（此列必须是数值型）
AVG（[DISTINCT|ALL]<列名>）计算一列值得平均值（此列必须是数值型）
MAX（[DISTINCT|ALL]<列名>）求一列中的最大值
MIN（[DISTINCT|ALL]<列名>）求一列的中的最小值
例：
查询学生总数
SELECTCOUNT（*）FROMStudent
查询选修了课程的学生人数
SELECTCOUNT（DISTINCTSno）FROMSC；//避免重复计算选课的学生人数
计算1号课程的学生平均成绩
SELECTAVG（Grade）FROMSCWHERECno=’1’;
查询选修1号课程的学生的最高分
SELECTMAX（Grade）FROMSCWHERECn0=‘1’；
5．对查询结果分组
GROUPBY子句将查询结果表按某一列或多列值分组，值相等的为一组。
例：
求各个课程号及相应的选课人数
SELECTCno,COUNT（Sno）FROMSCGROUPBYCno
如果分组后还要求按一定条件对这些组进行筛选，最终只输出满足指定条件的组，则可以使用HAVING短语指定筛选条件。
例：
查询选修了3门以上课程的学生的学号
SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT（*）>0;//这里是先按Sno进行分组，然后再对每一组进行统计。
WHERE和HAVING区别：
在于作用对象不同：
WHERE子句作用于基本表或视图，从中选择满足条件的元组。
HAVING作用于组，从中选择满足条件的组。
作业：
课本第149页习题3、4、5
第2节关系数据库标准语言查询部分
（二）
前面的查询都是对单表的查询，本节主要是关于两个或两个以上表的查询
一、连接查询
包括：
等值连接、自然廉洁、非等值连接查询、自身连接查询、外连接查询和复合条件查询。
（一）等值与非等值连接查询
1．基本概念
连接条件（连接谓词）：
格式：
[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>
比较运算符主要有：
=、>、<、>=、<=、!
=
连接谓词还可使用下面形式：
[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名2>
等值连接：
比较运算符为=.使用其他运算符称为非等值连接。
连接字段：
连接谓词中的列名。
连接条件的各连接字段类型必须是可比的，但不必相同。
2.例：
查询每个学生及其选修课程的情况
SELECTStudent.*,SC.*FROMStudent,SCWHEREStudent.Sno=SC.Sno
Student表和SC表数据同上一节
查询结果上机现场测试
自然连接：
等值连接中把目标列中重复的属性列去掉
例：
把上例用自然连接完成
SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROMStudent,SCWHEREStudent.Sno=SC.Sno;
在表中唯一的属性列前面可以省略表名前缀。
（二）自身连接
定义：
连接操作不仅可在两表间进行，也可是一个表与其自己进行连接，成为自身连接。
例：
查询每门课的间接先修课
方法：
将表Course起两个名字，一个是FIRST，另一个是SECOND。
完成查询语句：
SELECTFIRST.Cno,SECOND.Cpreo
FROMCourseFIRST,CourseSECOND
WHEREFIRST.Cpreo=SECOND.Cno
（三）外连接
以Student表为主体列出每个学生的基本情况及其选课情况，若某个学生没有选课，指输出其基本情况信息，其选课信息谓空值即可。
使用外连接
SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudent,SC
WHEREStudent.Sno=SC.Sno（*）;
表示方法：
在连接谓词的某一边加符号*。
好比为符号所在边的表加一个“万能行”，这个行由空值组成。
右连接：
外连接符号出现在连接条件的右边。
左连接：
外连接符号出现在连接条件的左边。
（四）复合条件连接
WHERE子句中有多个连接条件
例：
查询选修2号课程且成绩在90分以上的所有学生
SELECTStudent.Sno,SnameFROMStudent,SC
WHEREStudent.Sno=SC.SnoANDSC.Cno='2'ANDSC.Grade>90;
例：
查询每个学生的学号，姓名，选修的课程名及成绩。
SELECTStudent.Sno,Sname,Cname,GradeFROMStudent,SC,Course
WHEREStudent.Sno=SC.SnoandSC.Cno=Course.Cno;
二、嵌套查询
一个SELEC-FROM-WHERE语句称为一个查询块。
将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。
例：
SELECTSnameFROMStudent
WHERESnoIN
（SELECTSnoFROMSCWHERECno='2'）
说明：
1）允许多重查询，即一个子查询中还允许有其它子查询。
2）自查询的SELECT语句中不能使用ORDERBY子句，ORDERBY子句只能对最终查询结果进行排序。
3）嵌套查询求解方法是由里向外处理。
即子查询的结果作为父查询的查询条件。
4）嵌套查询使得可以利用简单查询完成复杂查询，增强了SQL的查询能力。
（一）带有IN谓词的子查询
嵌套查询中，子查询的结果往往是一个集合，故IN常使用
1例：
查询与“刘晨”在同一个系学习的学生。
思考：
第一步查询刘晨所在的系；第二步查找所有在IS系学习的学生
实现：
SELECTSdeptSELECTSno,Sname,Sdept
FROMStudentFROMStudent
WHERESname=’刘晨’;WHERESdept=’IS’;
使用嵌套：
SELECTSno,Sname,SdeptFROMStudent
WHERESdeptIN
（SELECTSdeptFROMStudentWHERESname='刘晨'）
例：
查询选修了课程名为“信息系统”的学生的学号和姓名
SELECTSno,SnameFROMStudentWHERESnoIN
（SELECTSnoFROMSCWHERECnoIN
（SELECTCnoFROMCourseWHERECname='信息系统'））
练习改用连接查询实现上面的查询：
SELECTstudent.Sno,SnameFROMStudent,SC,Course
WHEREStudent.Sno=SC.SnoAND
SC.Cno=Course.CnoANDCourse.Cname='信息系统'
（二）带有比较运算符的子查询
父查询与子查询之间用比较运算符进行连接的查询为带有比较运算符的子查询。
例：
将上面的使用IN的例子改为=，必须是子查询的结果是唯一的值
SELECTSno,Sname,SdeptFROMStudent
WHERESdept=（SELECTSdeptFROMStudentWHERESname='刘晨'）
子查询必须跟在比较运算符之后，如改为如下是错误的，但是在sqlserver中是正确的
SELECTSno,Sname,SdeptFROMStudent
WHERE（SELECTSdeptFROMStudentWHERESname='刘晨'）=Sdept
（三）带有ANY和ALL谓词的子查询
子查询返回单值时可以用比较运算符，使用ANY或ALL谓词时必须同时使用比较运算符
语义如下：
谓词
功能
>ANY
大于子
升级会员 