04.第四章数据库原理与SQL-Server

数据库原理与SQLServer第四课检索数据第四课检索数据

4.1Transact-SQL运算符和函数

4.2SELECT语句

4.3高级SELECT语句

4.4关系运算实训四检索数据4.1Transact-SQL语言数据运算

4.1.1Transact-SQL运算符(1)算术运算符：+、-、*、/、%注：null与任何值运算结果为null;+、-运算可用于datetime型数据。(2)字符串运算符：+(3)比较运算符：=、>、<、>=、<=、<>、!=、!>、!<(4)逻辑运算符:not、and、or、between(指定范围)、

like(模糊匹配)、all(所有)、in(包含于)、any(任意一个)、some(部分)、exists(存在)。

逻辑常量：ture、false。(6)赋值运算符：=4.1.2Transact-SQL函数1、数学函数2、字符串函数3、日期时间函数4、类型转换函数

1.数学函数sin(n)（正弦）

asin(n)（反正弦）pi()（圆周率）abs(n)（绝对值）exp(n)（指数）log(n)（自然对数）power(n,m)(nm)round(n,m)（四舍五入）sign(n)（符号）sqrt(n)（平方根）rand([n])（随机数）

2.字符串函数str(n,n1,n2)（数值转换为字符串）len(s)（求串长）left(s,n)（左取子串）right(s,n)（右取子串）substring(s,n1,n2)（取子串）lower(s)（转小写）upper(s)（转大写）ltrim(s)（删除左空格）rtrim(s)（删除右空格）space(n)（产生空空格）reverse(s)（反转字符串）charindex(s1,s2)（字符串s1在字符串s2中的起始位置）

3.日期时间函数getdate()、year(d)、month(d)day(d)datepart(datepart,d)（日期的datepart部分，datepart为日期类型，参见表4-4）dateadd(datepart,n,d)(日期加，即日期d的datepart部分加数值n后的新日期)datediff(datepart,d1,d2)(日期减，即日期d1与d2的datepart部分相差的值)例4-1计算香港回归已经有多少年、多少天，今天以后15个月是哪一天。脚本：

SELECTGETDATE(),DATEDIFF(YEAR,'1997-7-1',GETDATE()), DATEDIFF(DAY,'1997-7-1',GETDATE()),DATEADD(MONTH,15,GETDATE())

4.类型转换函数convert(data_type,expression[,style])cast(expressionASdata_type)

其中，Style(日期样式)取值参见表4-5。例4-2将当前时间日期转换为美国格式(mm/dd/yyyy及mm-dd-yyyy)、ANSI(yyyy.mm.dd)，并将当前时间的时间部分转换为字符串。

脚本：selectgetdate(),convert(char(10),getdate(),101),convert(char(10),getdate(),102),convert(char(10),getdate(),110),convert(char(10),getdate(),114)4.2SELECT语句

在SQL语句中，SELECT语句是最频繁使用的也是最重要的语句。SELECT<表达式>[AS<别名>][INTO<目标表名>]FROM<源表名>[WHERE<条件>][GROUPBY<列>[HAVING<条件>]][ORDERBY<列>[DESC]]4.2.1操纵列1.计算表达式值例4-1、例4-22.输出列例4-3

检索所有学生的所有信息。脚本：select*froms3、设置列标题例4-4

检索所有学生的所有信息。脚本：selectsnameasname,'is',

year(getdate())-year(birthday)asagefroms4.1.2操纵行1.普通查询例4-5

检索所有1985年12月31日以后以及1982年12月31日以前出生的女生的姓名和出生日期。脚本：

selectsnamebirthdayfromswheresex=‘女'and(year(birthday)>=1986oryear(birthday)<=1982)

2.模糊查询模糊匹配：like

通配符：%(*)、_(?)、[](指定范围)。

例4-6

检索所有姓李以及第二个字为李的住址在西安的学生的姓名、性别和住址。

selectsname,sex,addressfromswhere(snamelike'李%'orsnamelike'_李%')andaddresslike'%西安%'注：SQL语言中将一个汉字视为一个字符而非2个字符。4.1.2操纵行4.2.3汇总和排序1.聚合函数

count([distinct]<字段表达式>|*)max([distinct]<字段表达式>)min([distinct]<字段表达式>)sum([distinct]<字段表达式>)

avg([distinct]<字段表达式>)其中，distinct为取消重复记录。2.分类

groupby<列>注：groupby子句可以使用表达式，但不能使用text、image、bit类型数据。例4-7

查询每个学生所选课程的数量、总分及最高、最低分。脚本：

selectsno,count(*)asnum,sum(score),max(score),min(score)fromscgroupbysno3.分类后过滤记录

having<条件>

例4-8

查询平均成绩及格的学生所选课程的数量、总分及最高、最低分。脚本：

selectsno,count(*)asnum,sum(score),max(score),min(score)fromscgroupbysnohavingavg(score)>=60having与where功能相同，不同的是having在groupby后执行，可以使用聚合函数。4.2.4排序orderby<列>[desc]例4-9

检索每个学生所选课程的数量、总分、平均分及最高、最低分，并按平均分排名次。规定当平均分相等时，最高分高在前。脚本：

selectsno,count(*)asnum,sum(score),avg(score),max(score),min(score)fromscgroupbysno

orderbyavg(score)desc,max(score)desc4.3高级SELECT语句

一、连接查询二、子查询三、集合运算四、生成新表五、嵌入式Transact-SQL4.3.1连接查询

所谓多表查询就是从几个表中检索信息，这种操作通常可以通过表的连接实现。实际上，连接操作是区别关系数据库管理系统与非关系数据库管理系统的最重要的标志。

1.无限制连接——笛卡尔积无where子句，一般无实际意义。

例4-10

求表s与表sc的笛卡儿积。

脚：

select*froms,sc

from后有多个表，where子句为一F表达式。注：当from后有多个表时，where应包含这多个表的关系(一般是字段的相等关系)，否则即成为了笛卡尔积。

2.内连接——F连接

例4-11

检索选修了数据库应用课程及VB程序设计课程的学生的学号、姓名、课程名、成绩。脚本一：脚本二：3.自连接

连接不仅可以在表之间进行，也可以使一个表同其自身进行连接，称为自连接。

例4-12

检索所有选修了课程编号为c001及c003的学生的学号。脚本：

selectsc1.snofromscassc1,scassc2wheresc1.sno=sc2.snoando='c001'ando='c003'4.3.2子查询

如果一个SELECT语句嵌套在WHERE子句中，则称这个SELECT语句为子查询或内层查询，而包含子查询的SELECT语句称为主查询或外查询。为了区别主、子查询，子查询应加小括号。

1.不相关子查询所谓不相关子查询是指子查询的查询条件不依赖于主查询，此类查询在执行时首先执行子查询，然后执行主查询。

逻辑运算符：in(包含于)、any(某个值)、some(某些值)、all(所有值)、exists(存在结果)例4-13检索选修了数据库应用课程的学生的学号、姓名、成绩。脚本：selectsc.sno,sname,scorefroms,scwheresc.sno=s.snoandcno=(selectcnofromcwherecname='数据库应用')例4-14检索选修了数据库应用及VB程序设计课程的学生的学号、姓名、课程名、成绩。脚本：selectsc.sno,sname,cname,scorefroms,c,scwheresc.sno=s.snoando=oandoin(selectcnofromcwherecname='数据库应用'orcname='VB程序设计')2.相关子查询

所谓相关子查询是指子查询的查询条件依赖于主查询，此类查询在执行时首先执行主查询得到第一个元组，再根据主查询第一个元组的值执行子查询，依此类推直至全部查询执行完毕。

例4-15

检索平均成绩及格的学生的学号、姓名。脚本：

selectsno,snamefromswhereexists(selectsnofromscwheres.sno=sc.snogroupbysnohavingavg(score)>=60)4.3.3集合运算

使用union运算符可以将两个或两个以上的查询结果合并为一个结果集。

例4-16

查询表s和表s_bak中的所有学生的所有信息（假设表s_bak已存在，且结构与表s相同）。

脚本：

(select*froms)union(select*froms_bak)4.3.4生成新表1.生成临时表

INTO#/##<新表>

临时表保存在临时数据库Tempdb中，并由SQLServer2000负责删除。

例4-17查询平均成绩超过总平均成绩的学生的学号、姓名、平均成绩。

脚本：

selectsno,avg(score)asavginto#tempfromscgroupbysnohavingavg(score)>=(selectavg(score)fromsc)2.生成永久表INTO<新表>

例4-18创建一个包含信息501班学生的学号、姓名、性别以及出生日期的表。脚本：

selectsno,sname,sex,birthdayintotempfromswhereclass='信息501'4.3.5嵌入式Transact-SQL1.概念在宿主语言中使用的T-SQL语言。2.规则

T-SQL语句加标志以示区别，如PB中SQL语句应加“;”。3.共享变量

T-SQL和主语言中均可使用，在主语言中定义，T-SQL中使用时加“:”。4.接口

SQL通讯区(SQLCA)，其中

SQLCode=0操作成功

=-1操作失败

=100操作成功但无返回数据

4.4关系运算

4.4.1关系模型1.关系的数学定义

定义4.1域(Domain)是一组具有相同数据类型的值的集合。定义4.2给定一组域D1、D2、…、Dn，D1、D2、…、Dn的笛卡儿积为D1XD2X…XDn＝{(d1,d2,…,dn)|diDi,i=1,…,n}

其中，每一个元素(d1,d2,…,dn)称为一个元组（简称元组），元素中每一个值di称为一个分量。例4-19

给定三个域：姓名＝{张三,李四}、性别＝{男,女}、课程＝{数据库,软工}则域上的笛卡儿积为：姓名X性别X课程＝{(张三,男,数据库),(张三,男,软工),(张三,女,数据库),(张三,女,软工),(李四,男,数据库),(李四,男,软工),(李四,女,数据库),(李四,女,软工)}定义4.3域D1、D2、…、Dn上的笛卡儿积的子集称为在域D1、D2、…、Dn上的关系，用R(D1,D2,…,Dn)表示。其中，R表示关系名，n为关系的度或目或元数。例4-20给定三个域：姓名＝{张三,李四}、性别＝{男,女}、课程＝{数据库,软工}

则域上的教师授课关系为：教师授课(姓名,性别,课程)={(张三,男,数据库),(李四,女,软工)}，其中n＝3。

2.关系的性质

(1)属性取自同一个域。

(2)属性是原子的，且属性名不能相同。

(3)没有重复的元组。

(4)没有行序。

(5)理论上没有列序，为方便可以有列序。4.4.2关系代数

1.传统的集合运算(1)并（Union）:R∪S＝{t|tR∨tS}(2)交（Intersection）:R∩S＝{t|tR∧tS}(3)差（Difference）:R－S＝{t|tR∧tS}(4)笛卡儿积（CartesianProduct）:

RXS＝{t|t=(tm,tn)∧tmR∧tnS}

例4-21设关系R和关系S具有相同的关系模式，分别求出关系R和S的并、交和差。例4-22由学生关系S和课程关系C，求出其广义笛卡儿积。2.专门的关系运算

(1)选择（Selection）:σF(R)＝{t|tR∧F(t)=true}(2)投影（Projection）:ΠA(R)＝{t[A]|tR}(3)连接（Join）:将两个关系连在一起，形成一个新的关系。

包括：θ连接、等值连接、F连接、自然连接。(4)除（Division）

:R(X,Y)÷S(Y,Z)=ΠX(R)—ΠX((ΠX(R)XΠY(S))－R)

或

R(X,Y)÷S(Y,Z)={tr[X]|trR∧∏Y(S)Yx}例4-23由学生关系S，查询所有女生的信息。关系代数表达式：σSEX='女'(S)或σ3='女'(S)例4-24由学生关系S，查询所有学生的姓名、性别信息。关系代数表达式：ΠSNAME,SEX(S)或Π2,3(S)例4-25由学生关系S、课程关系C和选课关系SC，求、和。

例4-26由选课关系SC、课程关系C，求SC÷C。方法一：按公式R(X,Y)÷S(Y,Z)=ΠX(R)—ΠX(ΠX(R)XΠY(S))－R)计算SC(X,Y)÷C(Y)，即X=(SNO)，Y=(CNO)，Z=(CNAME,CREDIT)。①计算T1＝ΠSNO(SC)②计算T2＝T1XΠCNO(C)③计算T3＝T2－SC④计算T4＝ΠSNO(T3)⑤计算SC÷C＝T1－T4

方法二：按公式R(X,Y)÷S(Y,Z)={tr[X]|trR∧∏Y(S)Yx}。①

求R中X的各分量的象集Yx。

SC中，X＝(SNO)，其值有：{(1001),(1002),(1003),(2001),(2002)}，其象集为：

Y(1001)={(c001),(c003)}Y(1002)={(c001)}Y(1003)={(c001)}Y(2001)={(c001),(c003)}Y(2002)={(c003)}②

求出S在Y上的投影ΠY(S)。

ΠCNO(C)＝{(c001),(c003)}③

选取∏Y(S)Yx的分量x。显然Y(1001)和Y(2001)包含{(c001),(c003)}所以SC÷C＝{(1001),(2001)}3.关系代数表达式在关系代数中介绍了9种关系代数运算，其中并、差、笛卡儿积、选择和投影是基本运算，交、连接、自然连接、除可以用这5种基本运算经过有限次复合来表达。由关系运算有限次复合组成的式子称为关系代数表达式。关系代数表达式的运算结果仍为关系。实际上，可以用关系代数表达式表示各种数据查询操作。例4-27

对“学生选课”关系模型：

S(SNO,SNAME,SEX)C(CNO,CNAME,CREDIT)SC(SNO,CNO,SCORE)用关系代数表达式表示以下查询并写出相应T-SQL语句。

(1)选修了课程编号为c001的课程的学生的学号。关系代数表达式：

∏SNO(σCNO='c001'(SC))T-SQL语句：

selectsnofromscwherecno='c001‘(2)没有选修编号c01的课程的学生的学号。关系代数表达式：

∏SNO(R)－∏SNO(σCNO='c001'(SC))

注意不能写为：∏SNO(σCNO<>'c001'(SC))T-SQL语句：