第四章 检索数据

null数据库原理与SQL Server数据库原理与SQL Server 第4章 检索数据第4章 检索数据 第4章 检索数据 4.1了解数据运算— Transact-SQL运算符和函数 4.2 实现简单数据查询— SELECT语句 4.3 实现复杂数据查询—高级SELECT语句 4.4 数据库原理(二)—关系运算 实训 检索数据4.1 了解数据运算 — Transact-SQL语言数据运算 4.1 了解数据运算 — Transact-SQL语言数据运算 4.1.1 Transact-SQL运算符 (1)算术运算符：+、-、*、/、% 注：null与任何值运算结果为null; +、- 运算可用于datetime型数据。 (2)字符串运算符：+ (3)比较运算符：=、>、<、>=、<=、<>、!=、!>、!< (4)逻辑运算符：not、and、or、between(指定范围)、 like(模糊匹配)、all(所有)、in(包含于)、any(任意一个)、some(部分)、exists(存在)。 (5)逻辑常量：ture、false。 (6)赋值运算符：=4.1.2 Transact-SQL函数4.1.2 Transact-SQL函数1．数学函数 2．字符串函数 3．日期时间函数 4．类型转换函数 1. 数学函数 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. 字符串函数 2. 字符串函数 str(n,n1,n2)（数值转换为字符串） left(s,n)（左取子串） right(s,n)（右取子串） substring(s,n1,n2)（取子串） lower(s)（转小写） upper(s)（转大写） ltrim(s)（删除左空格） rtrim(s)（删除右空格） space(n)（产生空空格） reverse(s)（反转字符串） len(s)（求串长） charindex(s1,s2)（字符串s1在字符串s2中的起始位置） 3. 日期时间函数 3. 日期时间函数getdate()、year(d)、month(d)、 day(d) datepart(datepart,d)（日期的datepart部分，datepart为日期类型，参见 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 4-4） dateadd(datepart,n,d) (日期加，即日期d的datepart部分加数值n后的新日期) datediff (datepart,d1,d2) (日期减，即日期d1与d2的datepart部分相差的值) 例4-1 计算香港回归已经有多少年、多少天，今天以后15个月是哪一天。例4-1 计算香港回归已经有多少年、多少天，今天以后15个月是哪一天。 脚本： SELECT GETDATE(), DATEDIFF(YEAR,'1997-7-1',GETDATE()), DATEDIFF(DAY,'1997-7-1',GETDATE()), DATEADD(MONTH,15, GETDATE()) 4. 类型转换函数 4. 类型转换函数convert(data_type,expression[,style]) cast(expression as data_type) 其中，style(日期样式)取值参见表4-5。null 例4-2 将当前时间日期转换为美国格式(mm/dd/yyyy及mm-dd-yyyy)、ANSI (yyyy.mm.dd)，并将当前时间的时间部分转换为字符串。 脚本： select getdate(), convert(char(10),getdate(),101), convert(char(10),getdate(),102), convert(char(10),getdate(),110), convert(char(10),getdate(),114)4.2 实现简单数据查询— SELECT语句 4.2 实现简单数据查询— SELECT语句 在SQL语句中，SELECT语句是最频繁使用的也是最重要的语句。 SELECT <表达式> [AS <别名>] [INTO <目标表名>] FROM <源表名> [WHERE <条件>] [GROUP BY <列> [HAVING <条件>]] [ORDER BY <列> [DESC]] 4.2.1 操纵列4.2.1 操纵列1. 计算表达式值 例4-1、例4-2 2. 输出列 例4-3 检索所有学生的所有信息。 脚本：select * from s 3. 设置列标题 例4-4 检索所有学生的所有信息。 脚本：select sname as name, 'is', year(getdate())-year(birthday) as age from s 4.2.2 操纵行4.2.2 操纵行1.普通查询 例4-5 检索所有1985年12月31日以后以及 1982年12月31日以前出生的女生的姓名和出生 日期。 脚本： select sname birthday from s where sex='女' and (year(birthday)>=1986 or year(birthday)<=1982) 4.2.2 操纵行4.2.2 操纵行2. 模糊查询 模糊匹配：like 通配符：%(*)、_(?)、[ ](指定范围)。 例4-6 检索所有姓李以及第二个字为李的住址在西安的学生的姓名、性别和住址。 select sname,sex,address from s where (sname like ‘李%’ or sname like ‘_李%’) and address like '%西安%' 注：SQL语言中将一个汉字视为一个字符而非2个字符。4.2.3 汇总和排序4.2.3 汇总和排序1.聚合函数 count( [distinct] <字段表达式>|*) max ( [distinct] <字段表达式>) min ( [distinct] <字段表达式>) sum ( [distinct] <字段表达式>) avg ( [distinct] <字段表达式>) 其中，distinct为取消重复记录。null2.分类 group by <列> 注：group by子句可以使用表达式，但不能使用text、image、bit类型数据。 例4-7 查询每个学生所选课程的数量、总分及最高和最低分。 脚本： select sno,count(*) as num,sum(score), max(score), min(score) from sc group by snonull 3.分类后过滤记录 having <条件> 例4-8 查询平均成绩及格的学生所选课程的数量、总分及最高和最低分。 脚本： select sno,count(*) as num,sum(score), max(score), min(score) from sc group by sno having avg(score)>=60 having与where功能相同，不同的是having在group by后执行，可以使用聚合函数。4.2.4 排序4.2.4 排序order by <列> [desc] 例4-9 检索每名学生所选课程的数量、总分、平均分及最高和最低分，并按平均分排名次。规定当平均分相等时，最高分高的学生排名在前。 脚本： select sno,count(*) as num, sum(score), avg(score),max(score),min(score) from sc group by sno order by avg(score) desc,max(score) desc 4.3 实现复杂数据查询 —高级SELECT语句 4.3 实现复杂数据查询 —高级SELECT语句 一、连接查询 二、子查询 三、集合运算 四、生成新表 五、嵌入式Transact-SQL4.3.1 连接查询4.3.1 连接查询所谓多表查询就是从几个表中检索信息，这种操作通常可以通过表的连接实现。 实际上，连接操作是区别关系数据库管理系统与非关系数据库管理系统的最重要的标志。 1.无限制连接——笛卡儿积 1.无限制连接——笛卡儿积 无where子句，一般无实际意义。 例4-10 求表s与表sc的笛卡儿积 。 脚本： select * from s,sc from后有多个表，where子句为一逻辑表达式。 注：当from后有多个表时，where应包含这多个表的关系(一般是字段的相等关系)，否则即成为了笛卡儿积。 2.内连接——F连接 2.内连接——F连接 例4-11 检索选修了数据库应用课程及VB程序 设计 领导形象设计圆作业设计ao工艺污水处理厂设计附属工程施工组织设计清扫机器人结构设计 课程的学生的学号、姓名、课程名、成绩。 脚本一： 脚本二： 3.自连接 3.自连接 连接不仅可以在表之间进行，也可以使一个表同其自身进行连接，称为自连接。 例4-12 检索所有选修了课程编号为c001及c003的学生的学号。 脚本： select sc1.sno from sc as sc1,sc as sc2 where sc1.sno=sc2.sno and sc1.cno='c001' and sc2.cno='c003'4.3.2 子查询4.3.2 子查询 如果一个SELECT语句嵌套在WHERE子句中，则称这个SELECT语句为子查询或内层查询，而包含子查询的SELECT语句称为主查询或外查询。 为了区别主查询与子查询，子查询应加小括号。 1. 不相关子查询 1. 不相关子查询 所谓不相关子查询是指子查询的查询条件不依赖于主查询，此类查询在执行时首先执行子查询，然后执行主查询。 逻辑运算符：in(包含于)、any(某个值)、some(某些值)、all(所有值)、exists(存在结果)null例4-13 检索选修了数据库应用课程的学生的学号、姓名、成绩。 脚本： select sc.sno,sname,score from s,sc where sc.sno=s.sno and cno= (select cno from c where cname='数据库应用') 例4-14 检索选修了数据库应用及VB程序设计课程的学生的学号、姓名、课程名、成绩。 脚本： select sc.sno,sname,cname,score from s,c,sc where sc.sno=s.sno and sc.cno=c.cno and sc.cno in (select cno from c where cname=‘数据库应用’ or cname='VB程序设计') 2. 相关子查询 2. 相关子查询 所谓相关子查询是指子查询的查询条件依赖于主查询，此类查询在执行时首先执行主查询得到第一个元组，再根据主查询第一个元组的值执行子查询，依此类推，直至全部查询执行完毕。 例4-15 检索平均成绩及格的学生的学号、姓名。 脚本： select sno,sname from s where exists (select sno from sc where s.sno=sc.sno group by sno having avg(score)>=60)4.3.3 集合运算 4.3.3 集合运算 使用union运算符可以将两个或两个以上的查询结 果合并为一个结果集。 例4-16 查询表s和表s_bak中所有学生的所有信息（假设表s_bak已存在，且结构与表s相同）。 脚本： (select * from s) union (select * from s_bak)4.3.4 生成新表4.3.4 生成新表1.生成临时表 INTO #/##<新表> 临时表保存在临时数据库Tempdb中，并由SQL Server 2000负责删除。 例4-17 查询平均成绩超过总平均成绩的学生的 学号、姓名、平均成绩。 脚本： select sno,avg(score) as avg into #temp from sc group by sno having avg(score)>=(select avg(score) from sc) select #temp. sno, ,sname, avg from sc,s where #temp. sno= s. snonull2.生成永久表 INTO <新表> 例4-18 创建一个包含信息501班学生的学 号、姓名、性别和出生日期的表。 脚本： select sno,sname,sex,birthday into temp from s where class='信息501'4.3.5 嵌入式Transact-SQL4.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 数据库原理(二)—关系运算 4.4.1 关系模型 1.关系的数学定义 定义4.1 域(Domain)是一组具有相同数据类型的值的集合。 定义4.2 给定一组域D1,D2,…,Dn。D1,D2,…,Dn的笛卡儿积为D1×D2 ×… ×Dn＝{(d1,d2,…,dn)|diDi,i=1,…,n} 其中，每一个元素(d1,d2,…,dn)称为一个元组（简称元组），元素中每一个值di称为一个分量。null例4-19 给定3个域：姓名＝{张三,李四}、性别＝{男,女}、课程＝{数据库,软工}。 则域上的笛卡儿积为： 姓名×性别×课程＝{(张三,男,数据库),(张三,男,软工),(张三,女,数据库),(张三,女,软工),(李四,男,数据库),(李四,男,软工),(李四,女,数据库),(李四,女,软工)}null定义4.3 域D1,D2,…,Dn上的笛卡儿积的子集称为在域D1,D2,…,Dn上的关系，用R(D1,D2,…,Dn)表示。其中，R表示关系名，n为关系的度或目或元数。 例4-20 给定三个域：姓名＝{张三,李四}、性别＝{男,女}、课程＝{数据库,软工}。 则域上的教师授课关系为： 教师授课(姓名,性别,课程)={(张三,男,数据库),(李四,女,软工)}，其中n＝3。 null2.关系的性质 (1)属性取自同一个域。 (2)属性是原子的，且属性名不能相同。 (3)没有重复的元组。 (4)没有行序。 (5)理论上没有列序，为方便可以有列序。 4.4.2 关系代数 4.4.2 关系代数 1.传统的集合运算 (1)并（Union）:R∪S＝{t|tR∨tS} (2)交（Intersection）:R∩S＝{t|tR∧tS} (3)差（Difference）: R－S＝{t|tR∧tS} (4)笛卡儿积（Cartesian Product）: R×S＝{t|t=(tm,tn)∧tm R∧tnS} null例4-21 设关系R和关系S具有相同的关系模式，分别求出关系R和S的并、交和差。 例4-22 由学生关系S和课程关系C，求出其广义笛卡儿积。例4-22 由学生关系S和课程关系C，求出其广义笛卡儿积。null2.专门的关系运算 (1)选择（Selection）:σF(R)＝{t|t R∧F(t)=true} (2)投影（Projection）:ΠA(R)＝{t[A]|t R} (3)连接（Join）:将两个关系连在一起，形成一个新的关系 。 包括：θ连接、等值连接 、F连接、自然连接 。 (4)除（Division） : R(X,Y)÷S(Y,Z)=ΠX(R) －ΠX((ΠX(R) ×ΠY(S))－R) 或 R(X,Y)÷S(Y,Z)={tr[X]|tr R∧∏Y(S) Yx} null例4-23 由学生关系S，查询所有女生的信息。 关系代数表达式：σSEX='女' (S) 或σ3='女' (S) 例4-24 由学生关系S，查询所有学生的姓名、性别信息。 关系代数表达式：ΠSNAME,SEX(S) 或Π2,3(S) 例4-25 由学生关系S、课程关系C和选课关系SC，求 、 和 。 nullnull例4-26 由选课关系SC、课程关系C，求SC÷C。 方法一： 按公式R(X,Y)÷S(Y,Z)=ΠX(R) － ΠX(ΠX(R) ×ΠY(S)) －R)计算SC(X,Y)÷C(Y)，即 X=(SNO)，Y=(CNO)，Z=(CNAME,CREDIT)。 ①计算T1＝ΠSNO (SC) 。 ②计算T2＝T1×ΠCNO (C) 。 ③计算T3＝T2－SC 。 ④计算T4＝ΠSNO(T3) 。 ⑤计算SC÷C＝T1 － T4 。 null方法二： 按公式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)} null3.关系代数表达式 在关系代数中介绍了9种关系代数运算，其中并、差、笛卡儿积、选择和投影是基本运算，交、连接、自然连接、除可以用这5种基本运算经过有限次复合来表达。 由关系运算有限次复合组成的式子称为关系代数表达式。 关系代数表达式的运算结果仍为关系。实际上，可以用关系代数表达式表示各种数据的查询操作。 null例4-27 对“学生选课”关系模型： S(SNO,SNAME,SEX) C(CNO,CNAME,CREDIT) SC(SNO,CNO,SCORE) 用关系代数表达式表示以下查询并写出相应的T-SQL语句。 (1)选修了课程编号为c001的课程的学生的学号。 关系代数表达式： ∏SNO(σCNO='c001'(SC)) T-SQL语句： select sno from sc where cno='c001'null(2)没有选修编号c01的课程的学生的学号。 关系代数表达式： ∏SNO(R)－∏SNO(σCNO='c001'(SC)) 注意不能写为：∏SNO(σCNO<>'c001'(SC)) T-SQL语句： select sno from s where not exists (select sno from sc where s.sno=sc.sno and cno='c001') (3)选修了数据库应用课程的学生的学号。 关系代数表达式： ∏SNO(σCNAME='数据库应用'(SC∞C)) T-SQL语句： select sc.sno from c,sc where sc.cno=c.cno and cname='数据库应用'null(4) 选修了数据库应用课程的学生的学号、姓名。 关系代数表达式： ∏SNO,SNAME(σCNAME='数据库应用'(SC∞C)∞S) 注意三个关系的连接顺序。 T-SQL语句： select sc.sno,sname,score from s,c,sc where sc.sno=s.sno and sc.cno=c.cno and cname=‘数据库应用’ (5) 选修了所有课程的学生的学号。 关系代数表达式： ∏SNO,CNO(SC)÷∏CNO(C) T-SQL语句： select sno from s where not exists (select * from c where not exists (select * from sc where sc.sno=s.sno and sc.cno=c.cno)) null(6)至少选修了两门课程的学生的学号。 关系代数表达式： ∏1(σ1=4∧2<>5(SC ×SC)) T-SQL语句： select sc1.sno from sc as sc1,sc as sc2 where sc1.sno=sc2.sno and sc1.cno<>sc2.cno 实训 检索数据实训 检索数据 实验名称：检索表中的数据 目的要求：掌握SQL Server 2000检索表中记录的方法 操作步骤： (1) 实现以上所有实例。 (2) 自行设计5条检索实例。