数据库原理及应用第4章关系数据库语言SQL关系数据库语言(StructuredQueryLanguage,简称SQL)是一种介 于关系代数和元组演算之间的关系数据库语言。SQL语言从1974年提出以来,先后被确定为关系数据库语言的美国国家标准、国际标准和中 国国家标准。随着SQL语言的进一步发展和完善,全世界绝大多数的关系数据库都采用了SQL语言,极大地推进了数据库技术的发展 和广泛应用,也凸显了学习SQL语言的重要性。主要内容4.1SQL的功能与特点4.2表的基本操作4.3SQL的数 据查询4.4SQL的视图操作4.5SQL中带有子查询的数据更新4.6嵌入式SQL与游标应用第4章关系数据库语言 SQL4.1SQL的功能与特点SQL语言有三大功能:1、数据定义功能2、数据操纵功能3、数据控制功能一、S QL的功能1.数据定义功能用于定义基本表、定义视图、定义索引等。一、SQL的功能2.数据操纵功能用于实施对数据库的 数据查询和数据更新。数据查询是指按查询条件从数据库中检索出数据,并按输出要求对其进行排序、分组和统计等。数据更新包括数据的插入 、删除、修改等。一、SQL的功能3.数据控制功能用于实现对数据库对象的控制。包括:用户授权、基本表和视图授权、事务控制 、数据完整性控制和安全性控制等。一、SQL的功能SQL语言功能的实现:数据定义功能由数据库定义类语句实现;数据查询功能 由数据库查询类语句实现;数据更新功能由数据库更新(插入、删除、修改)类语句实现;数据控制功能由数据库控制类语句实现。二、S QL的特点1.SQL具有交互式命令和嵌入式2种工作方式在交互式命令工作方式下,用户可以联机在系统提供的查询编辑器窗口上, 通过直接键入SQL命令(语句)对数据库进行操作,系统然后会把处理结果显示给用户。二、SQL的特点交互式命令格式:查询编辑器窗口 消息窗口二、SQL的特点1.SQL具有交互式命令和嵌入式2种工作方式在嵌入式工作方式下,SQL语句可以被嵌入到某种高级 语言(比如,C语言)程序中实现对数据库的操作,并利用主语言的强大计算功能、逻辑判断功能、屏幕控制及输出功能等,实现对数据的处理和输 入输出控制等。二、SQL的特点嵌入式工作方式(应用程序):SQL外模式视图1视图2模式基本表1基本表2基本表3基本表4内模式存 储文件1存储文件2二、SQL的特点2.SQL支持数据库的三级模式结构##第4章关系数据库语言SQL4.2表的基本操 作一、表的创建、修改与撤消1.创建表语句格式:CREATETABLE<表名>(<列名1><数据类型>[<列1的 完整性约束>][,<列名2><数据类型>[<列2的完整性约束>],……,<列名n><数据类型>[<列n的完整性约 束>],[<表的完整性约束>]]);一、表的创建、修改与撤消1.创建表语句格式:CREATETABLE<表名> (<列名1><数据类型>[<列1的完整性约束>][,<列名2><数据类型>[<列2的完整性约束>],……,<列名 n><数据类型>[<列n的完整性约束>],[<表的完整性约束>]]);“<>”表示该项是必选项。一、表的创建、修改与撤 消1.创建表表的定义语句格式为:CREATETABLE<表名>(<列名1><数据类型>[<列1的完整性约束>][ ,<列名2><数据类型>[<列2的完整性约束>],……,<列名n><数据类型>[<列n的完整性约束>],[<表 的完整性约束>]]);“[]”表示该项是可选项。一、表的创建、修改与撤消1.创建表表的定义语句格式为:CREATE TABLE<表名>(<列名1><数据类型>[<列1的完整性约束>][,<列名2><数据类型>[<列2的完整性约束>] ,……,<列名n><数据类型>[<列n的完整性约束>],[<表的完整性约束>]]);SQL语言中的语句的结束符号 “;”。表名和列名是以字母开头,由字母、数字和下划线“_”组成的字符串,长度不超过30个字符。表名不能与SQL语言中的保留字同 名,不能与其它表名或视图名同名。一、表的创建、修改与撤消1.创建表表的定义语句格式为:CREATETABLE<表名> (<列名1><数据类型>[<列1的完整性约束>][,<列名2><数据类型>[<列2的完整性约束>],……,<列名 n><数据类型>[<列n的完整性约束>],[<表的完整性约束>]]);一、表的创建、修改与撤消1.创建表表的定义语句 格式为:CREATETABLE<表名>(<列名1><数据类型>[<列1的完整性约束>][,<列名2><数据类型> [<列2的完整性约束>],……,<列名n><数据类型>[<列n的完整性约束>],[<表的完整性约束>]]);一个 表中至少要有一列,且不能重名。一、表的创建、修改与撤消1.创建表表的定义语句格式为:CREATETABLE<表名>( <列名1><数据类型>[<列1的完整性约束>][,<列名2><数据类型>[<列2的完整性约束>],……,<列名n ><数据类型>[<列n的完整性约束>],[<表的完整性约束>]]);数据类型完整性约束列名示例:教学管理数据库系统中的学习 关系SC字段名类型宽度小数位数主键外键NULL其它S#char9YYN相对于学生关系C#char7YYN相对于课程关系GRADEi nt0-100一、表的创建、修改与撤消其中:●典型的<数据类型>(a)CHAR(m):长度为m的字符(串)型数据,长度不够时 用空白字符补充,不超过240。(b)VARCHAR(m):长度小于等于m的字符(串)型数据,长度不够时不补充其它字符。(c) INT/INTEGER:长整型数据。(d)DATE:日期型数据,形式为YYYY-MM-DD,分别表示年、月、日。一、表的创建 、修改与撤消其中:●典型的<列的完整性约束>①NULL:指出该列可以为空值。②NOTNULL:指出该列不能为空值。每一 个表中至少应有一个列的可选项为NOTNULL。③PRIMARYKEY:指出该列名为表的主键。一、表的创建、修改与撤消例4. 1图1.11所示的大学教学管理数据库中的专业关系SS,可用如下的表定义语句定义:CREATETABLESS(SCODE# CHAR(5)PRIMARYKEY,SSNAMEVARCHAR(30)NOTNULL);一、表的创建、修改与撤消 其中:●典型的<列的完整性约束>④DEFAULT:给所在的列设置一个缺省值。DEFAULT(<缺省值>)⑤CHECK :指出该列的值只能取CHECK约束条件范围的值。CHECK(<值的约束条件>)例4.2图1.11所示的大学教学管理数据库中 的学生关系表S,可用如下的表定义语句定义:CREATETABLES(S#CHAR(9)PRIMARYKEY,S NAMECHAR(16)NOTNULL,SSEXCHAR(2)CHECK(SSEXIN(′男′,′女′)),SBI RTHINDATENOTNULL,PLACEOFBCHAR(16),SCODE#CHAR(5)NOTNULL, CLASSCHAR(5)NOTNULL);一、表的创建、修改与撤消利用SQLServer2012的查询编辑器,实现创建 例4.2的表S的过程如下:利用SQLServer2012查询编辑器创建表S过程:(a)启动SQLServerMan agementStudio工具,并连接到当前服务器。单击工具栏中“新建查询(N)”按钮(也可以在“文件”菜单中选择“新建”->“ 使用当前连接查询”),如图4.2所示。利用SQLServer2012查询编辑器创建表S过程:(b)在“可用数据库”中选择当 前操作的数据库为“JXGL”数据库,如图4.3所示。利用SQLServer2012查询编辑器创建表S过程:(c)在查询编辑 器中输入例4.2的创建表S的SQL语句,然后单击“!执行(X)”按钮(也可以按下F5键执行,或者在“查询”菜单上单击“执行”选项) ,在“消息”栏中显示“命令已成功完成”。一、表的创建、修改与撤消其中:●<表的完整性约束>①表的主键约束子句,格式为P RIMARYKEY(<主键列名1>[,<主键列名2>,…,<主键列名r>])一、表的创建、修改与撤消例4.3大学教学管 理数据库中的学习关系SC,可用如下的表定义语句定义:CREATETABLESC(S#CHAR(9),C# CHAR(7),GRADESMALLINTDEFAULT(0),PRIMARYKEY(S#,C#));一、表的 创建、修改与撤消●<表的完整性约束>②表的外键约束格式为:FOREIGNKEY(<列名1>)REFERE NCE<表名>(<列名2>)本子句定义了一个列名为“<列名1>”的外键,它与表“<表名>”中的“<列名2>”相对应。显然 ,“<列名2>”在表“<表名>”中是主键。一、表的创建、修改与撤消例4.4大学教学管理数据库中的学习关系SC,可重新用如下的表 定义语句定义如下:CREATETABLESC(S#CHAR(9),C#CHAR(7),GRADE SMALLINTDEFAULT(0),PRIMARYKEY(S#,C#),FOREIGNKEY(C#)REF ERENCESC(C#));一、表的创建、修改与撤消●<表的完整性约束>③表检验CHECK约束语句格式:C HECK(<值的约束条件>)指出该表中某列的值只能取CHECK约束条件范围的值。与列检验约束CHECK所不同的是,表检验约 束CHECK子句是一个独立的子句而不是子句中的一部分。表检验约束CHECK子句中的<值的约束条件>不仅可以是一个条件表达式,而且 还可以是一个包含SELECT语句。一、表的创建、修改与撤消例4.5大学教学管理数据库中的学习关系SC,还可用如下的表定义语句定 义如下:CREATETABLESC(S#CHAR(9),C#CHAR(7),GRADESMALLINTDEF AULT(0),PRIMARYKEY(S#,C#),FOREIGNKEY(C#)REFERENCESC(C#),C HECK(GRADEBETWEEN0AND100));一、表的创建、修改与撤消2.表的修改(1)改变表明 语句格式:SP_RENAME<原表名>,<新表名>例4.6将SS改为SS1。SP_RENAMESS,SS1;一、 表的创建、修改与撤消2.表的修改(2)增加列在表的最后一列后面增加新的一列。语句格式:ALTERTABLE<表 名>ADD<增加的列名><数据类型>;注意:不允许将一个列插入到原表的中间。一、表的创建、修改与撤消例4.7给专业表SS增加 一个新属性NOUSE_COLUMN,设其数据类型为NUMERIC(8,1)。语句应为:ALTERTABLESSADD NOUSE_COLUMNNUMERIC(8,1);一、表的创建、修改与撤消2.表的修改(8)删除列语句格式:ALTE RTABLE<表名>DROP<删除的列名>[CASCADE|RESTRICT];其中,可选项“[CASCADE| RESTRICT]”是删除方式。当选择CASCADE时,表示在删除名为“<表名>”的表中的列“<删除的列名>”时,所有引用到该 列的视图或有关约束也一起被删除。当选择RESTRICT时,表示当没有视图或有关约束引用列“<删除的列名>”时,该列才能被删除,否则 拒绝该删除操作。一、表的创建、修改与撤消例4.8删除专业表SS中增加的属性NOUSE_COLUMN的两种删除语句形式分别为: ALTERTABLESSDROPNOUSE_COLUMNCASCADE;ALTERTABLESSDROPNOU SE_COLUMNRESTRICT;一、表的创建、修改与撤消2.表的修改(4)修改列的定义语句格式:ALTER TABLE<表名>MODIFY<列名><新的数据类型及其长度>;注意:对列定义的修改只适用于修改列的类型和长度,列名不能 修改。当表中已有数据时,只能增加列的长度,不能缩短列的长度。一、表的创建、修改与撤消例4.9将专业表SS中的专业名称SSNAM E(30)修改为SSNAME(40)(即长度增加10)的列修改语句:ALTERTABLESSMODIFYSSNAMEV ARCHAR(40);一、表的创建、修改与撤消3.表的撤销将不再需要的表或定义有错误的表删除掉。语句格式:DROP TABLE<表名>[CASCADE|RESTRICT];其中,CASCADE表示在撤消表“<表名>”时,所有引用这个表的 视图或有关约束也一起被撤消;RESTRICT表示在没有视图或有关约束引用该表的属性列时,表“<表名>”才能被撤消,否则拒绝该撤消 操作。二、表中数据的插入、修改与删除1.数据的插入插入语句格式为:INSERTINTO<表名>[(<列名表>)] VALUES(<值表>);功能:向表中插入一行数据(一个数据记录)。二、表中数据的插入、修改与删除INSERTINTO <表名>[(<列名表>)]VALUES(<值表>);其中:(1)<列名表>格式为:<列名1>[,<列名2>,……,<列 名m>]。(2)<值表>的格式为:<常量1>[,<常量2>,……,<常量m>]用于指出要插入列的具体值。二、表中数据 的插入、修改与删除例4.10写出给学习关系SC中插入王丽丽同学(学号为200401003)学习计算机网络课(课程号为C4030 01)的成绩(89分)的插入语句。INSERTINTOSC(S#,C#,GRADE)VALUES(′20140100 3′,′C403001′,89);或:INSERTINTOSCVALUES(′201401003′,′C40300 1′,89);二、表中数据的插入、修改与删除例4.11如果在创建学习关系SC时已经把分数属性GRADE的值缺省定义成0,那么 在学生的考试成绩出来之前就可输入学生的学号S#和课程号C#信息,等考试成绩出来后再通过修改表内容来输入成绩。INSERTIN TOSC(S#,C#)VALUES(′201401003′,′C403001′);二、表中数据的插入、修改与删除2. 数据的修改语句格式为:UPDATE<表名>SET<列名1>=<表达式1>[,<列名2>=<表达式2>,…,<列名n >=<表达式n>][WHERE<条件>]其中,“<列名i>=<表达式i>”指出将列“<列名i>”的值修改成<表达式i>。 可选项“[WHERE<条件>]”中的<条件>指定修改有关列的数据时所应满足的条件。当不选择该选项时,表示修改表中全部元组中相应 列的数据。二、表中数据的插入、修改与删除例4.12写出将学生关系S中的学生名字“王丽丽”(学号为201401003)改为“ 王黎丽”的数据修改语句。UPDATESSETSNAME=′王黎丽′WHERES#=′201401003′;二 、表中数据的插入、修改与删除例4.13写出将所有女同学的专业改为S0404的数据修改语句。UPDATESSETSCO DE#=′S0404′WHERESSEX=′女′;二、表中数据的插入、修改与删除3.数据的删除语句格式:DE LETEFROM<表名>[WHERE<条件>]其中,可选项“[WHERE<条件>]”中的<条件>指定所删元组应满足的 条件。当不选该可选项时,表示删除表中全部数据。二、表中数据的插入、修改与删除例4.14从学生关系S中删除学号为20140300 1的学生的信息。DELETEFROMSWHERES#=′201403001′;例4.15删除专业关系中的全部信 息。DELETEFROMSS;##第4章关系数据库语言SQL4.3SQL数据查询一、投影查询投影查询的语 句格式为:SELECT<列名表>FROM<表名表>其中:(1)<列名表>的格式为“<列名1>[,<列名2>,…,<列名n >]”,用于指出查询结果记录中的各个列及其排列顺序。(2)若设<列名表>为A1,A2,…,An;<表名>为,则上述查询语句的意 义可用关系代数表达式表示为。一、投影查询例4.16查询教学管理数据库中全部学生的基本信息。SELECTFROM S;下面,介绍利用SQLServer2012的“查询编辑器”,实现例4.16查询的过程。利用“查询编辑器”实现例4.1 6的查询的步骤:(1)打开SQLServer2012的“查询编辑器”,选择“JXGL”数据库,键入查询语句:利用“查 询编辑器”实现例4.16的查询的步骤:(2)单击“!执行(X)”按钮,{对于图1.8中给出的关系的当前值来说,}得到如下图的查 询结果。一、投影查询例4.17查询教学管理数据库中全部教师的教职工编号、姓名、职称和所属教研室。SELECTT#,T NAME,TITLEOF,TRSECTIONFROMT;一、投影查询例4.18查询课程关系C中的记录数,也即开 课的总门数。课程关系模式:C(C#,CNAME,CLASSH)SELECTCOUNT()FROMC;聚合函数 SELECTCOUNT()SELECTCOUNT()AS记录数FROMC;FROMC; 一、投影查询例4.18查询课程关系C中的记录数,也即开课的总门数。一、投影查询给列名起别名的语句格式为:<函数或原列名> AS<函数或该列的别名>}也即:<函数>AS<该函数的别名><原列名>AS<该列的别名>聚合函数 ……...一、投影查询SQL语言中常用的聚合函数主要有:(1)COUNT()计算元组的个数(2)COUNT(列名)计算 列名所在列的值的个数(3)COUNTDISTINCT(列名)计算列名所在列中不同值的个数(4)SUM(列名)计算该列名所在数据 列的值的总和(5)AVG(列名)计算该列名所在数据列的值的平均值(6)MIN(列名)求该列名所在(字符、日期、属性)列的最小值(7 )MAX(列名)求该列名所在(字符、日期、属性)列的最大值一、投影查询例4.19查询所有学生所学课程的最高分数、最低分数和平 均分数。SELECTMAX(GRADE)AS最高分数,MIN(GRADE)AS最低分数,AVG(GRADE)AS 平均分数FROMSC;学习关系模式:SC(S#,C#,GRADE)二、选择查询选择查询的语句格式:SELECT <列名表>FROM<表名表>WHERE<条件>其中:(1)当WHERE子句仅由一个关系表达式组成时,称为单条件选择查 询;当WHERE子句由多个关系表达式通过逻辑运算符连接而成时,称为多条件选择查询。(2)若设F为条件表达式,则上述查询语句的意义 为。二、选择查询1.单条件查询例4.20写出查询所有学习了计算机网 络课(课程号为C403001)的学生的学号和成绩的查询语句。学习关系模式:SC(S#,C#,GRADE)SELECTS#, GRADEFROMSCWHEREC#=′C403001′;条件表达式中的关系比较符二、选择查询表4.3条件 表达式中的关系比较符运算符含义=!=或<>>>=<<=ISNULLISNOTNULL等于不等于大于大于等于小于小 于等于是空值不是空值二、选择查询2.多条件查询例4.21查询选修了计算机网络课(课程号为C403001)或通信原理课(课程 号为C403002)的学生的学号。SELECTS#FROMSCWHEREC#=′C403001′ORC# =′C403002′;条件表达式中的逻辑运算符二、选择查询表4.4逻辑运算符运算符含义NOTANDOR逻辑非逻辑与逻辑 或二、选择查询例4.22查询年龄在21岁至28岁之间学生的基本信息。SELECTFROMSWHEREYEAR (GETDATE())-YEAR(SBIRTHIN)BETWEEN21AND28;year()为取时间的年份函数二、 选择查询象上例中这一类:把某数值型列的值限定在某个数值区间的比较查询条件(<、<=、>、>=),均可以用比较运算符“BETWE EN…AND”来表示。格式为:<数值型列名>BETWEEN<数值区间下限值>AND<数值区间上限值>三、分组查询 在SQL语言中,把元组按某个或某些列上相同的值分组,然后再对各组进行相应操作的查询方式称为分组查询。语句格式:SELECT <列名表>FROM<表名表>[WHERE<条件>][GROUPBY<列名表>[HAVING<分组条件>]];三 、分组查询分组查询语句:SELECT<列名表>FROM<表名表>[WHERE<条件>][GROUPBY<列名表 >[HAVING<分组条件>]];(1)GROUPBY子句GROUPBY子句用来将列的值分成若干组,从而控制查询的 结果排序。三、分组查询例4.23查询各个同学所学课程的平均分数。SELECTS#,AVG(GRADE)AS平均分 数FROMSCGROUPBYS#;学习关系模式:SC(S#,C#,GRADE)三、分组查询例4.24查询每 个专业男、女生的人数。SELECTSCODE#AS专业代码,SSEXAS性别,COUNT()AS人数FRO MSGROUPBYSSEX,SCODE#;学生关系模式:S(S#,SNAME,SSEX,SBIRTHIN,PLACE OFB,SCODE#,CLASS)三、分组查询(2)HAVING子句在数据查询中,有时只希望选择满足一定条件的分组。一般是利用 GROUPBY子句进行分组,利用HAVING子句判断分组应满足的条件。例4.25查询学生总数超过300人的专业及其具体 的总人数。SELECTSCODE#,COUNT()FROMSGROUPBYSCODE#HAVINGCOUN T()>300;四、结果的排序查询通常,SELECT的查询结果是按元组在数据库中的存储顺序给出的。有时用户希望按照某种约定的 顺序给出查询结果,ORDERBY子句用于实现查询结果的排序显示。语句格式为:SELECT<列名表>FROM<表名表> [WHERE<条件>]ORDERBY<列名>[ASC/DESC][,<列名>[ASC/DESC]…];四、结果 的排序查询例4.26按学号递增的顺序(查询)显示学生的基本信息。SELECTFROMSORDERBYS#A SC;四、结果的排序查询例4.27按学号递增、课程成绩递减的顺序(查询)显示学生的课程成绩。SELECTS#,C#, GRADEFROMSCORDERBYS#ASC,GRADEDESC;五、模糊查询(字符串匹配)在SQL语句中 ,两个字符串的部分字符的相等比较是在WHERE子句的条件表达式中实现的。进行字符串匹配的条件表达式格式为:<列名>LIKE ‘[字符串1]通配符[字符串2]'通配符:(1)下划线_。在字符串比较时,“_”表示其对应位置的那个字符可以任意。比如: SNAMELIKE‘李__平''两个字符表示一个汉字的位置五、模糊查询(字符串匹配)在SQL语句中,两个字符串的部分字 符的相等比较是在WHERE子句的条件表达式中实现的。条件表达式格式为:<列名>LIKE‘[字符串1]通配符[字符串2 ]'通配符:(2)百分号%。在字符串比较时,“%”表示从其对应位置的那个字符开始,一个长度大于等于零的子字符串可以任意。五、模 糊查询(字符串匹配)例4.28查询学生关系S中姓李的学生的学号和姓名。SELECTS#,SNAMEFROMSWH ERESNAMELIKE′李%′;五、模糊查询(字符串匹配)汇总可有,一个比较完整的SELECT查询语句SELECT <列名或列表达式序列>FROM<表名表>[WHERE<条件>][GROUPBY<列名表>[HAVING<分组条件 >]][ORDERBY<列名>[ASC/DESC][,<列名>[ASC/DESC]]];六、SQL语言中的常用函数1 .日期和时间型函数日期是SQL语言中的标准数据类型(DATE)。六、SQL语言中的常用函数表4.6常用的日期函数 函数功能dateadd(datepart,n,date)datediff(datepart,startdate,end date)datename(datepart,date)datepart(datepart,date)?给指定日期date按 日期组成部分datepart加上一个时间间隔n后的新时间值按日期组成部分datepart返回enddate减去startdate 的值返回指定日期date的日期组成部分datepart的字符串返回指定日期date的日期组成部分datepart的整数六、 SQL语言中的常用函数表5.6常用的日期函数—续函数功能day(date)getdate()getutcda te()month(date)year(date)?返回一个整数,表示指定日期date的“天”部分返回当前系统日期和时间 返回表示当前的UTC时间(通用协调时间或格林尼治标准时间)值返回一个整数,表示指定日期date的“月”部分返回一个整数,表示指定 日期date的“年”部分datepart的取值可以有:year,quarter,month,dayofyear,day,we ek,weekday,hour,minute,second,millisecond。六、SQL语言中的常用函数例4.29查询 出生日期在1996年1月1日到1996年12月31日之间的所有学生的学号、姓名和出生日期,并按日期的递增顺序排列。SELECT S#,SNAME,SBIRTHINFROMSWHERESBIRTHINBETWEEN′1996-1-1′AND ′1996-12-31′ORDERBYSBIRTHIN;六、SQL语言中的常用函数例4.30查询所有学生当前的年龄, 并按年龄递增顺序排列。SELECTS#,SNAME,year(getdate())-year(SBIRTHIN)AS AGEFROMSORDERBYAGE;六、SQL语言中的常用函数2.字符串函数表4.7常用的字符串函数函 数功能ascii(string)返回字符串string中最左侧字符的ASCII代码值left(string,n)返回字符串s tring中从左边开始n个的字符len(string)返回指定字符串string的字符(而不是字节)数,其中不包含尾随空格lowe r(string)将字符串string中的大写字符转换为小写字符ltrim(string)返回删除了前导空格之后的字符表达式rep lace(string1,string2,string3)用第3个字符串string3替换第1个字符串string1中出现的所有第 2个字符串string2的匹配项六、SQL语言中的常用函数2.字符串函数表4.7常用的字符串函数——续函数功能rep licate(string,n)以指定的次数n重复字符表达式stringright(string,n)返回字符串string中从右 边开始n个的字符space(n)返回由n个空格组成的字符串str(num[,m,n])将数值数据num按总长度为m,小数位数为n转 换为字符串数据,参数m和n可省略substring(string,m,n)返回字符串string的第m个字符开始取出的n个字符up per(string)将字符串string中的小写字符转换为大写字符课程关系C课程号 课程名 学时 C401001 数据结 构70 C401002 操作系统60 C402001 计算机原理60 C402002 通信原理60 C403001 计算机网络…例 4.31:统计各教研室开设课程的门数。分析课程关系模式及其当前值可知,各门课程号的第1位由字符C开头,第2至第4位为教研室编号 ,第5至第7位是该教研室所开课程的序号。所以仅由课程关系就可以统计出各教研室所开设课程的门数,其查询语句如下:SELECTsu bstring(c#,2,3)AS教研室,count(c#)AS开课门数FROMCGROUPB Ysubstring(c#,2,3);六、SQL语言中的常用函数3.数学函数表4.8常用的数学函数函数功能a bs(n)返回数n的绝对值ceiling(n)返回大于或等于所给数值n的最大整数floor(n)返回小于或等于所给数值n的最大整数 power(m,n)返回数值m的n次幂的值round(n,m)对n的小数部分进行四舍五入,使其具备m的精度sign(n)根据参数n 是正还是负,返回正号(+1)、负号(-1)和0rand()返回从0到1之间的随机float值,sqrt(n)返回指定数 值n的平方根六、SQL语言中的常用函数3.数学函数表4.8常用的数学函数——续函数功能square(n)返回指定 数值n的平方exp(n)返回指定的float类型的数值n的指数值pi()返回PI的常量值sin(n)以近似数值(float型) 返回指定角度n(以弧度为单位)的三角正弦值cos(n)返回以弧度表示的指定角度n的三角余弦值tan(n)返回以弧度表示的指定角度n 的正切值cot(n)返回以弧度表示的指定角度n的三角余切值七、表的连结查询SQL语言允许在同一查询语句中从二个或多个表中查询数 据,一般称为二元查询或多元查询。语句格式为:SELECT<列名表>FROM<表名表>WHERE<基于多表的连接条件> 其中:(1)<表名表>的格式为:“<表名1>[,<表名2>,…,<表名m>]”。(2)若设<列名表>为A1,A2,…,An; <表名表>为R1,R2,…,Rm,则上述查询语句的意义可表示成如下的关系代数表达式:。七、表的连结查询例4.32查询所有学习 了数据结构课(课程号为C401001)的学生的学号和姓名。S(S#,SNAME,SSEX,SBIRTHIN,PLACEOFB,SC ODE#,CLASS)SC(S#,C#,DRADE)SELECTS.S#,SNAMEFROMS,SCWHERES.S #=SC.S#ANDC#=′C401001′;七、表的连结查询例4.33查询选修了“通信原理”课程的学生的学号与 姓名。S(S#,SNAME,SSEX,SBIRTHIN,PLACEOFB,SCODE#,CLASS)SC(S#,C#,DRADE) C(C#,CNAME,CLASSH)SELECTS.S#,SNAMEFROMS,SC,CWHERES.S#= SC.S#ANDSC.C#=C.C#ANDCNAME=′通信原理′;上节课教学内容回顾上节课教学内容回顾1、投影 查询SELECT<列名表>FROM<表名表(单个表)>对应于:上节课教学内容回顾2、选择查询SELECT<列名表 >FROM<表名(单个表)>WHERE<条件表>对应于:上节课教学内容回顾3、表的连结查询SELECT<列名表 >FROM<表名表(多个表)>WHERE<基于多表的连接条件>对应于:上节课教学内容回顾4、一个完整的查询语句格式 SELECT<列名或列表达式序列>FROM<表名表>[WHERE<条件>][GROUPBY<列名表>[ HAVING<分组条件>]][ORDERBY<列名>[ASC/DESC][,<列名>[ASC/DESC]]]; ##八、嵌套查询在SQL语言中,如果在一个SELECT语句的WHERE子句中嵌入了另一个SELECT语句,则称为嵌套查询。 WHERE子句中的SELECT语句称为子查询。八、嵌套查询例4.34查询张华同学(学号为201401001)的那个班的女同学的 基本信息。八、嵌套查询例4.34查询张华同学(学号为201401001)的那个班的女同学的基本信息。SELECTFRO MSWHERECLASS=′张华同学(学号为201401001)的那个班′ANDSSEX=′女′S(S#, SNAME,SSEX,SBIRTHIN,PLACEOFB,SCODE#,CLASS)八、嵌套查询例4.34查询张华同学(学号为 201401001)的那个班的女同学的基本信息。′张华同学(学号为201401001)的那个班′SELECTCLASSFR OMSWHERES#=′201401001′八、嵌套查询例4.34查询张华同学(学号为201401001)的那个班的 女同学的基本信息。CLASS=′张华同学(学号为201401001)的那个班′ANDSSEX=′女′SELECT FROMSWHERECLASS=(SELECTCLASSFROMSWHERES#=′201401001′) ANDSSEX=′女′;八、嵌套查询查询中的表更名:在嵌套查询中,当同一个关系既出现在父查询中,又出现在子查询中,且 子查询的条件涉及到父查询的属性时,为了避免描述上的混淆,一般需要对该关系进行更名描述。更名描述的格式为:旧表名AS新表 名八、嵌套查询例4.35检索考试成绩比该课程平均成绩低的学生的成绩。八、嵌套查询例4.35检索考试成绩比该课程平均成绩 低的学生的成绩。SELECTS#,C#,GRADEFROMSCWHEREGRADE<“该课程平均成绩” SC(S#,C#,DRADE)八、嵌套查询例4.35检索考试成绩比该课程平均成绩低的学生的成绩。SELECTS#,C# ,GRADEFROMSCWHEREGRADE<(SELECTAVG(GRADE)FROMSCAS XWHEREX.C#=SC.C#);SC.C#=SC.C#?“该课程平均成绩”九、谓词演算查询表4.9常用的谓 词操作符操作符说明betweenAandBnotbetweenAandBlikeinnotinany someallexistsnotexists某列的数值区间是[A,B]某列的数值区间在[A,B]外两个字符串的部分字符相等,其 余可以任意某列的某个值属于集合成员中的一个成员某列的值不属于集合成员中的任何一个成员某列的值满足一个条件即可满足集合中的某些值某列 的值满足子查询中所有值的记录总存在一个值满足条件不存在任何值满足条件九、谓词演算查询1.IN和NOTIN谓词条件表达式 格式为:<列值>IN<数据集合><列值>NOTIN<数据集合>前者的含义:如果列值(数据项)是该数据集合中的成员, 那么逻辑值为true,否则为false。后者的含义:如果列值(数据项)不是该数据集合中的成员,那么逻辑值为true,否则为fal se。九、谓词演算查询例4.36查询所有学习了数据结构课(课程号为C401001)的学生的学号和姓名。九、谓词演算查询例4. 36查询所有学习了数据结构课(课程号为C401001)的学生的学号和姓名。SELECTS#,SNAMEFROM SWHERES#IN“学习了数据结构课(C#为C401001)的学生的学号集合”S(S#,SNAME,SSEX,SBIRT HIN,PLACEOFB,SCODE#,CLASS)九、谓词演算查询例4.36查询所有学习了数据结构课(课程号为C401001 )的学生的学号和姓名。“学习了数据结构课(C#为C401001)的学生的学号集合”SELECTS#FROMSCW HEREC#=′C401001′SC(S#,C#,DRADE)九、谓词演算查询例4.36查询所有学习了数据结构课(课程号为 C401001)的学生的学号和姓名。SELECTS#,SNAMEFROMSWHERES#IN(SELEC TS#FROMSCWHEREC#=′C401001′);九、谓词演算查询例4.37利用嵌套查询实现例5.33 ,也即查询选修了“通信原理”课程的学生的学号与姓名。九、谓词演算查询例4.37利用嵌套查询实现例5.33,也即查询选修了“通信 原理”课程的学生的学号与姓名。思路1:找出选修了“通信原理”课的课程号SELECTC#FROMCWHERECNAME =′通信原理′;C(C#,CNAME,CLASSH)九、谓词演算查询例4.37利用嵌套查询实现例5.33,也即查询选修了“通信 原理”课程的学生的学号与姓名。思路1:找出选修了“通信原理”课的课程号SELECTC#FROMCWHERECNAME =′通信原理′;思路2:找出选修了该课程(课程号为通信原理的课程号)的学生的学号SELECTS#FROMSCWHERE C#IN“通信原理课程的课程号”;九、谓词演算查询例4.37利用嵌套查询实现例5.33,也即查询选修了“通信原理”课程的 学生的学号与姓名。思路:选修了“通信原理”课的课程号;选修了该课程(号)的学生的学号SELECTS#,SNAMEFROMSW HERES#IN(SELECTS#FROMSCWHEREC#IN(SELECTC#FROMCWHE RECNAME=′通信原理′));九、谓词演算查询4.谓词演算查询(2)ANY和SOME谓词条件表达式格式为:<列值> θANY<数据集合><数据项>θSOME<数据集合>其含义是:比较运算符θ(<、<=、>、>=、=、!=)左边的 列值(数据项)与右边数据集合中的某个或某些元素是否满足θ运算,满足则为真,不满足则为假。在SQL语言中,ANY和SOME具有相 同的含义,早期的版本用的是ANY,新的版本都改为SOME,有些商用数据库版本的SQL语言中,同时保存了ANY和SOME两个谓词。 九、谓词演算查询例4.38查询所有学习了数据结构课(课程号为C401001)的学生的学号和姓名。SELECTS#,SNA MEFROMSWHERES#=ANY(SELECTS#FROMSCWHEREC#=′C4010 01′);其中,“=ANY”的作用相当于IN。九、谓词演算查询4.谓词演算查询(3)ALL谓词条件表达式格式为:<列 值>θALL<集合>含义是:比较运算符θ左边的列值(数据项)与右边集合中的所有元素是否满足θ运算,若满足逻辑值为true, 若不满足逻辑值为false。九、谓词演算查询例4.39查询考试成绩大于网络工程专业(专业代码为S0403)所有学生的课程成绩 的学生的基本信息。九、谓词演算查询例4.39查询考试成绩大于网络工程专业(专业代码为S0403)所有学生的课程成绩的学生的基本信 息。查询所有学生的基本信息:SELECTS#,SNAME,SSEX,SBIRTHIN,PLACEOFB,SCODE# ,CLASSFROMS九、谓词演算查询例4.39查询考试成绩大于网络工程专业(专业代码为S0403)所有学生的课程成绩的学 生的基本信息。考试成绩大于网络工程专业所有学生的成绩,说明他也学习了该课程。进而考虑:查询所有学习了XX课程的信息,就应该再加上 SC表。SELECTS.S#,SNAME,SSEX,SBIRTHIN,PLACEOFB,SCODE#,CLASS FROMS,SCWHERES.S#=SC.S#ANDGRADE>ALL“网络工程专业学生的课程成绩”九、谓词演 算查询例4.39查询考试成绩大于网络工程专业(专业代码为S0403)所有学生的课程成绩的学生的基本信息。“网络工程专业学生的课程 成绩”SELECTGRADEFROMS,SCWHERES.S#=SC.S#ANDSCODE#=′S040 3′);S(S#,SNAME,SSEX,SBIRTHIN,PLACEOFB,SCODE#,CLASS)SC(S#,C#,DRAD E)九、谓词演算查询例4.39查询考试成绩大于网络工程专业(专业代码为S0403)所有学生的课程成绩的学生的基本信息。SELEC TS.S#,SNAME,SSEX,SBIRTHIN,PLACEOFB,SCODE#,CLASSFROMS,SC WHERES.S#=SC.S#ANDGRADE>ALL(SELECTGRADEFROMS,SCWHER ES.S#=SC.S#ANDSCODE#=′S0403′);九、谓词演算查询4.谓词演算查询(4)EXISTS和 NOTEXISTS谓词条件表达式格式为:EXISTS(<集合>)NOTEXISTS(<集合>)前者的含义是:当集 合中至少存在一个元素(非空)时,其逻辑值为true,否则为false;后者的含义是:当集合中不存在任何元素(为空)时,其逻辑值为 true,否则为false。通常用于测试子查询是否有返回结果。九、谓词演算查询例4.40查询所有学习了数据结构课(课程号为C 401001)的学生的学号和姓名。九、谓词演算查询例4.40查询所有学习了数据结构课(课程号为C401001)的学生的学号和姓 名。SELECTS#,SNAMEFROMSWHEREEXISTS(学习了数据结构课的学生的成绩′);直观的意 义为:如果存在某个学生学习了数据结构课的成绩,说明他/她学习了数据结构课程了。九、谓词演算查询例4.40查询所有学习了数据结构 课(课程号为C401001)的学生的学号和姓名。学习了数据结构课的学生的成绩:SELECTGRADEFROMSCW HEREC#=′C401001′;九、谓词演算查询例4.40查询所有学习了数据结构课(课程号为C401001)的学生的学 号和姓名。SELECTS.S#,SNAMEFROMSWHEREEXISTS(SELECTFROMSC WHERESC.S#=S.S#ANDC#=′C401001′);直观的意义为:查询的是那些,在学习关系中存在所学 课程为数据结构的学生的学号和姓名。九、谓词演算查询例4.41查询没有学习数据结构课(课程号为C401001)的学生的学号和姓名 。SELECTS.S#,SNAMEFROMSWHERENOTEXISTS(SELECTFROM SCWHERESC.S#=S.S#ANDC#=′C401001′);十、并、交、差运算查询1.并运算查询 十、并、交、差运算查询1.并运算查询即,指将两个或多个SELECT语句的查询结果组合在一起作为总的查询结果输出。语句格式为: SELECT<列名表>FROM<表名表>[WHERE<条件>][UNION[ALL]{SELECT语句}… ];其中,如果不选择可选项ALL,则在输出总查询结果时重复的行会自动被取掉。如果选择可选项ALL,则表示将全部行合并输出, 也即不取掉重复行。十、并、交、差运算查询例4.42合并学生关系和专业关系中的专业代码。SELECTSCODE#FROM SUNIONSELECTSCODE#FROMSS;十、并、交、差运算查询2.交运算查询查询结果的交操作是指将 同时属于两个或多个SELECT语句的查询结果作为总的查询结果输出。查询结果交操作的基本数据单位是行。语句格式为:SELECT<列 名表>FROM<表名表>[WHERE<条件>][INTERSECT{SELECT语句}…];十、并、交、差运算 查询例4.43查询有成绩的学生的学号。SELECTS#FROMSINTERSECTSELECTS#FROM SCWHEREGRADEISNOTNULL;十、并、交、差运算查询3.差运算查询是指从第一个SELECT语句的查询 结果中去掉属于第二个SELECT语句查询结果的行作为总的查询结果输出。语句格式为:SELECT<列名表>FROM<表 名表>[WHERE<条件>][EXCEPT{SELECT语句}];例4.44查询没有成绩的学生的学号。图1.8中的 学生都有学习成绩,根据图1.8的大学教学管理数据库的当前值可知,本例中第一个子句和第二个子句查询出来的学号都是7个,且都分别相同, 所以本例的查询结果应为图5.28(a)。SELECTS#FROMSEXCEPTSELECTS#FROMSCWHE REGRADEISNOTNULL;(a)例4.44查询没有成绩的学生的学号。为了进一步验证本例的结果,假设再给学生表S 中插入一个新学生的信息,但并不给其插入课程成绩:(201403002,王伟,男,1996-05-15,广州,S0403,20140 3)SELECTS#FROMSEXCEPTSELECTS#FROMSCWHEREGRADEISNOTNUL L;第4章关系数据库语言SQL4.4SQL的视图操作Ο、视图的概念视图是由数据库中满足一定条件约束的数据组成的“虚表” 。视图可以由某个表中满足一定条件的行组成,也可以由某个(或某些)表的某些列组成,还可以由若干个表中的数据经过某种运算处理而形成。 视图一旦定义,就可以把它看作表一样在其上进行查询操作。一、视图的创建创建视图语句格式为:CREATEVIEW<视图名> [(<列名表>)]AS |
|