《四章节结构化查询语言SQL.ppt》由会员分享，可在线阅读，更多相关《四章节结构化查询语言SQL.ppt（47页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、四章节结构化查询语言SQL Still waters run deep.流静水深流静水深,人静心深人静心深 Where there is life,there is hope。有生命必有希望。有生命必有希望SQL语言的功能：SQL SQL是结构化查询语言（是结构化查询语言（Structured Query Structured Query LanguageLanguage）的缩写，）的缩写，具有数据具有数据定义定义（DDLDDL）、数据数据操纵操纵（DMLDML）和和数据数据控制控制（DCLDCL）、数据）、数据查询四个部分。查询四个部分。SQL数据定义功能：能够定义数据库的三级模式结构，即外
2、模式、全局模式和内模式结构。在SQL中，外模式有叫做视图（View），全局模式简称模式（Schema）或数据库（Database），内模式由系统根据数据库模式自动实现，一般无需用户过问。基本表（表）、属性（字段）、元组（行）的概念SQL数据操纵功能：包括对基本表和视图的数据查询、插入、删除和修改，特别是具有很强的数据查询功能。SQL的数据控制功能：主要是对用户的访问权限加以控制，以保证系统的安全性。SQL视图V1视图V2基本表B4基本表B3基本表B2基本表B1分区1文件1文件2分区2文件3文件4外模式模式存储模式SQL支持的数据支持的数据库的体系的体系结构构一、数据库模式的建立和删除1、建立数
3、据库模式语句格式：CREATESCHEMA|DATABASEAUTHORIZATION语句功能：在计算机系统中建立一个只有名字的空数据库，并定义出它的所有者名。语句举例：（1）createschemaxueshauthorization刘勇（2）createdatabase数据库2、删除数据库模式语句格式：DROPSCHEMA|DATABASE数据库名语句功能：从计算机系统中删除（撤销）一个数据库。当然会同时把该库中的所有信息一并删除掉。语句举例：dropdatabasexuesh二、表结构的建立、修改和删除1、建立表结构语句格式：CREATETABLE.（，,)语句功能：在当前或给定的数据库
4、中定义一个表的结构（即关系模式)。语句说明：a.若省略和则在当前数据库中建立一个表，否则在指定数据库中建立一个表。使用户给所定义的表所起的名字。可以在一个表定义中出现一次或多次，每个包括列名（即属性名)、相应数据类型和该列的完整性约束等内容。在所有列定义之后可以给出表级完整性约束。b.可使用的数据类型主要有以下四种：char(n)定长字符型int整型float浮点型，又称实数型date日期型c.列级完整性约束有以下六种:1.DEFAULT默认值约束。2.NULL/NOTNULL空值/非空值约束。注明每行上的该列值为空。3.PRIMARYKEY主码约束。注明该列为关系的主码。4.UNIQUE单值
5、约束，又称唯一值约束。必须不相同d.表级完整性约束包括以下四种：1.PRIMARYKEY(,）主码约束。注明一列或同时多个列为关系的主码。2.UNIQUE单值约束。一个或同时若干个列为单值。3.FOREIGNKEY()REFERENCES(,)外码约束。4.CHECK()检查约束。5.REFERENCES()外码约束。6.CHECK()检查约束。注明该列的取值条件，或称取值限制。注：若只涉及到一个列时，则既可以作为列级完整性约束，又可以作为表级完整性约束，当然只取其一。语句举例：(1）createtable学生(学生号char(7)primarykey,姓名char(6)notnulluniq
6、ue,性别char(2)notnullchech(性别=男or性别=女),出生日期datetimecheck(出生日期=1and年级=1and课程学分=0and成绩=100),(7)primarykey(学生号,课程号),(8)foreignkey(学生号)references学生(学生号),(9)foreignkey(课程号)references课程(课程号)(10)三、表内容的插入、修改和删除1、插入记录向一个表中插入记录有两种语句格式，一种是单行插入，另一种是多行插入。单行插入：格式：INSERTINTO.(,)VALUES多行插入：格式：INSERT(INTO).(,)语句功能：向一个
7、表中所指定的若干列插入一行或多行数据。注意：当列值为字符串或日期时，必须用单引号括起来，以区别于数值数据。语句举例：p792、修改表结构语句格式：ALTERTABLE.ADD列定义|ADD,|DROPCOLUMN,|DROP,语句功能：向已定义过的表中添加一些列的定义或一些表级完整性约束，或者从已定义过的表中删除一些列或一些完整性约束。举例：（1）altertable学生add籍贯char(6)（2）altertable学生dropcolumn籍贯3、删除表结构语句格式：DROPTABLE.语句功能：从当前或给定的数据库中删除一个表。举例：droptable学生12、修改记录语句格式：UPDA
8、TE.SET=,FROM,WHERE语句功能：按条件修改一个表中一些列的值、语句举例：(1)update职工set年龄=年龄+1(2)Update职工set基本工资=职工1.基本工资+职工1.职务津贴from职工1where职工.职工号=职工1.职工号(3)Update职工set基本工资=基本工资*1.2where职工号=0104053、删除记录语句格式：DELETEFROM.FROM,WHERE语句功能：删除一个表中满足条件的所有行语句举例：(1)deletefrom职工where年龄45(2)Delete职工from职工1where职工.职工号=职工1.职工号(3)Delete职工四、视图
9、的建立、修改和删除视图是在基本表之上建立的表，它的结构(即所有列定义)和内容(即所有数据行)都来自基本表，它依据基本表存在而存在。每个视图的列可以来自同一个基本表，也可以来自多个不同的基本表。它是基本表的抽象和在逻辑意义上建立的新关系。对视图只能进行修改和查询操作。1、建立视图语句格式：CREATEVIEW(,)AS功能：在当前库中根据SELECT子局的查询结果建立一个视图，包括视图的结构和内容。语句举例：createview成绩视图表(学生号,姓名,课程号,课程名,成绩)asselect选课.学生号,姓名,选课.课程号,课程名,成绩from学号,课程,选课where学生.学生号=选课.学生号
10、and课程.课程号=选课.课程号and专业=电子2、修改视图内容语句格式：UPDATE.SET=,FROM,WHERE语句功能：按照一定条件对当前或指定数据库中的一些列值进行修改。语句举例：update成绩视图表set成绩=80where学生号=0102005and课程号=E0023、修改视图定义：语句格式：ALTERVIEW(,)AS语句功能：在当前数据库中修改已知视图的列,它与SELECT子句的查询结果相对应。语句举例：(1)Createview学生视图(学生号,姓名)asselect学生号，姓名from学生(2)Alterview学生视图(学生号,专业)asselect学生号,专业fro
11、m学生4、删除视图语句格式：DROPVIEW语句功能：删除当前数据库中一个视图。语句举例：dropview成绩视图表五、SQL查询1、SELECT语句格式：SELECTALL|DISTINCTAS,表达式2AS|*|.*|INTOFROMAS,ASWHEREGROUPBY,HAVINGORDERBYASC|DESC,ASC|DESC功能：根据一个或多个表按条件进行查询，产生出一个新表（即查询结果），该新表被显示出来或者被命名保存起来。语句说明：a.Select选项给处在查询结果中每一行(即每一条记录)所包含的列，以及决定是否允许在查询结果中出现重复行（即内容完全相同的记录）；b.into选项决
12、定是否把查询结果以基本表的形式保存起来，若需要则应带有该选项;c.from选项提供用于查询的基本表和视图，它们均可以带有表别名，称这些表为源表，而把查询结果称为目的表;d.where选项用来指定不同源表之间记录的连接条件和每个源表中记录的筛选（选择）条件，只有满足所给连接条件和筛选条件的记录才能被写入到目的表中；e.groupby选项用于使查询结果只包含按指定列的值进行分组的统计信息；f.having子句通常同groupby选项一起使用，筛选出符合条件的分组统计信息；g.orderby选项用于将查询结果按指定列值的升序或降序排序。在查询语句中，通过select选项实现投影运算，通过from选项
13、和where选项是实现连接和选择运算（在SQL新版本中，用from选项专门实现连接运算，用where选项专门实现选择运算）2、SELECT选项在该选项中，ALL/DISTING为任选项，若选择ALL,则允许在查询结果中出现内容重复的行（记录），若选择DISTINCT,则在查询结果中不允许出现内容重复的行，即只有内容互不相同的记录才能被写入到查询结果中，若省略该选项，则隐含为ALL.应用举例：P89例4-14-103、FROM选项例4-114、WHERE选项例4-124-16Record#商品代号分类名单价数量1DSJ-120电视机1865.00152DSJ180电视机2073.00103XYJ
14、-13洗衣机486.00204XYJ-20洗衣机873.00125DBX-134电冰箱1456.0086DSJ340电视机3726.0057WBL-6微波炉640.00108KTQ-12空调器2800.0012SPK1（商品代号C（8），分类名C（6），单价N（8，2），数量N（3）一、简单查询例1从SPK1中查询出每个记录的分类名字段的值。SELECTALL分类名FROMSPK1例2从SPK1中查询出所有商品的不同分类名。SELECTDISTINCT分类名FROMSPK1练习：1、从SPK1中查询出单价低于2000元的商品代号、分类名和单价。2、从SPK1中查询出单价在1000元至2500元
15、之间的所有商品。例3从SPK1中查询出分类名为“电视机”的所有商品SELECT*；FROMSPK1；WHERE分类名=“电视机”用AS指出字段别名：例4从SPK1中查询出每一种商品的最高价值、最低价值。SELECTMAX（单价*数量）AS最高价值，MIN（单价*数量）AS最低价值；FROMSPK1在SQL-SELECT命令中，使用的字段函数有：COUNT（字段名|*）统计出对应字段的个数，它也就是相应的记录数，通常使用*表示任一字段。MAX（字段名）求出最大值。MIN（字段名）求出最小值。AVG（字段名）求出对应的数值字段的平均值。SMU（字段名）求出对应的数值字段的总和。Record#商品代
16、号产地品牌12345678DSJ-120DSJ-180XYJ-13XYJ-20DBX-134DSJ-340WBL-6KTQ-12南京南京无锡山西北京北京青岛无锡熊猫熊猫小天鹅海棠雪花牡丹海信春兰SPK2（商品代号C（8），产地C（8），品牌C（8）练习3、从SPK1中查询出每一种商品的价值。4、查询出SPK1库中分类名为“电视机”的商品种数、最高价、最低价及平均价。练习题答案：1、SELECT商品代号，分类名，单价；FROMSPK1；WHERE单价1000AND单价=10；ORDERBY单价DESC在WHERE中BETWEENAND和NOTBETWEEND使用例从SPK1中查询出单价在1000
17、元至2500元之间的所有商品。SELECT商品代号，分类名，单价；FROMSPK1；WHERE单价BETWEEN10002500例4-11从教学库中查询出每个学生选修每门课程的学生号、姓名、课程号、课程名、成绩等数据Selectx.学生号,姓名,y.课程号,课程名,成绩From学生x,课程y,选课zWherex.学生号=z.学生号andy.课程号=z.课程号例4-12从商品表1种查询出单价大于1500，同时数量大于等于10的商品。Select*From商品表1Where单价=1500and数量=10例13从商品库中查询出产地为南京或无锡的所有商品的商品代号、分类名、产地和品牌。Selectx.
18、商品代号,分类名,产地,品牌From商品表1x,商品表2yWherex.商品代号=y.商品代号and(产地=南京or产地=无锡)例14从教学库中查询出选修至少两门课程的学生学号。Selectdistinctx.学号From选课x,选课yWherex.学生号=y.学生号andx.课程号y.课程号例15从教学库中查询出选修了课程名为“操作系统”课程每个学生的姓名。Select姓名,课程From学生x,课程y,选课zWherex.学生号=z.学生号andy.课程号=z.课程号and课程名=操作系统注意：在新版的SQL中，为了使查询语句更加结构化，已经把查询连接条件从WHERE选项中转移到FROM选项
19、中，并且还丰富了连接的功能，除了上述介绍的一般连接（在新版本中称作中间连接）外，还增加了左连接和右连接的功能。在FROM选相中的相应语法格式分别为：中间连接FROMINNERJOINON.左连接FROMLEFTJOINON.右连接FROMRIGHTJOINON.每一种连接都隐含着双重循环的执行过程：对于中间连接，外循环依次扫描第一个表中的每个元组，内循环依次扫描第二个表中的每个元组，当满足连接条件时就连接起来形成中间表中的一个新元组；对于左连接，除了按中间连接形成中间表中的新元组外，还把第一个表中的没有形成连接的所有元组也加入到中间表中，这些元组在第二个表上所对应的列值被自动置为空；对于右连接
20、，除了按中间连接形成中间表中的新元组外，还把第二个表中的没有形成连接的所有元组也加入到中间表中，这些元组在第一个表上所对应的列值被自动置为空。完成连接后，查询语句再根据WHERE选项中提供的筛选条件从中间表中选择出元组，然后再根据SELECT选项投影出所需要的列形成结果表。例16.a从教学库中查询出所有学生的选课情况，要求没选修任何课程的学生信息也要反映出来Select*From学生leftjoin选课on学生.学生号=选课.学生号例16.b从学生库中查询出所有课程被学生选修的情况Select*From课程leftjoin(选课innerjoin学生on选修.学生号=选课.学生号)on课程.课
21、程号=选课.课程号例16.c从教学库中查询出所有电子专业的学生选课的全部情况Select*From学生innerjoin(选课innerjoin课程on选课.课程号=课程.课程号)on学生.学生号=选课.学生号Where专业=电子(与传统查询语句等效)用于查询语句中的专门比较式又叫判断式，它实现单值与集合数据之间的比较。常用的有以下六种格式。格式1：ALL()功能：是一条完整的SELECT语句，被嵌套在该比较式中使用。当的查询结果中的每一个值都满足所给的比较条件时，此比较式的值才为真，否则为假。例17从商品表1中查询出单价比分类名为“洗衣机”的所有商品的单价都高的商品。Select*From商
22、品表1Where单价all(select单价from商品表1where分类名=“洗衣机”例18查询出数量小于分类名为“洗衣机”或“微波炉”的每一个商品数量的所有元组(包括产地和品牌)Selectx.*,产地,品牌From商品表1xinnerjoin商品表2yonx.商品代号=y.商品代号Where数量all(select数量from商品表1where分类名=洗衣机or分类名=微波炉)格式2ANY|SOME()当子查询的查询结果中的任一个值满足所给的比较条件时，此比较式为真，否则为假。该格式中的两个关键字ANY和SOME具有同样的作用，选用任一个即可。例19从商品库中查询出产地与品牌为“春兰”的
23、商品的产地相同的所有商品的商品代号、分类名、品牌、产地等属性的值Selectx.商品代号,分类名,品牌,产地From商品表1xinnerjoin商品表2xonx.商品代号=y.商品代号Where产地=some(select产地from商品表2where品牌=春兰)例20从教学库中查询出选修了课程名为“C+语言”的所有学生的姓名和成绩。第一种方法：使用单重查询语句处理select姓名,成绩from学生x,课程y,选课zwherex.学生号=z.学生号andy.课程号=z.课程号第二种方法：使用双重查询语句处理select姓名,成绩from学生innerjoin选课on学生.学生号=选课.学生号w
24、here课程号=some(select课程号from课程=C+语言)例21从商品库中查询出所有商品中单价最高的商品和单价最低的商品Select*From商品表1Where单价=any(selectmax(单价)from商品表1)or单价=any(selectmin(单价)from商品表1)或：select*from商品表1where单价=any(selectmax(单价)from商品表1unionselectmin(单价)from商品表1)格式3：NOTBETWEENAND例22从商品表1中查询出单价在1000到2000元之间的所有商品Select*From商品表1Where单价between
25、1000and2000格式4：EXISTS()当子查询结果中至少存在着一个元组时，表明查询结果非空，则此判断式为真，否则为假。但当判断式中带有NOT关键字时，情况正好相反，即当子查询结果为空时，判断式为真，否则为假。例23从教学库中查询出选修至少一门课程的所有学生Select*From学生Whereexists(select*from选课where学生.学生号=选课.学生号)例24从教学库中查询出与姓名为“王明”的学生选课至少有一门相同的所有学生Select*From学生xWherex.姓名王明andexists(selecty.课程号from选课ywherey.学生号=x.学生号andy.课
26、程号=any(selectw.课程号from学生z，选课wwherez.学生号=w.学生号andz.姓名=王明)格式5：NOTIN()|()使用逗号分开的若干个常量。当所制定列的当前值包括在由所给定的值之内时，则此判断式为真，否则为假。例26从学生表中查询出专业为计算机、电气、通信的所有学生。Select*From学生Where专业in(计算机,电气,通信)例27从教学库中查询出选修了课程名为“操作系统”的所有学生。Select*From学生Where学生号in(select学生号from选课,课程where选课.课程号=课程.课程号and课程名=操作系统)格式6：NOT当的当前值与的值相匹配
27、时，此判断时为真。例28从商品表1中查询出商品代号以字符串“dsj”开头的所有商品Select*From商品表1Where商品代号likedsj%5、GROUPBY选项该选项中的,等必须是出现在SELECT选项中的被投影的表达式所指定的列名。通常在SELECT选项中使用列函数对列值相同的每一组进行有关统计例29从学生表中查询出每个专业的学生数Select专业专业名,count(专业)学生数From学生Groupby专业例30从教学库中查询出每个学生的学生号，姓名及所选课程的门数Selectx.学生号,姓名,count(x.学生号)所选门数From选课x,学生yWherex.学生号=y.学生号G
28、roupbyx.学生号,y.姓名例31从商品表1种查询出每一类（即分类名相同）商品的最高价、最低价和平均价。Select分类名,max(单价)as最高价,min(单价)as最低价,avg(单价)as平均价From商品表1Groupby分类名6、HAVING选项该选项的是一个筛选条件。该选项通常跟在GROUPBY子句后面用来从分组统计中筛选出部分统计结果，因此该选项中的逻辑表达式通常带有字段函数例32从学生表中查询出专业的学生数多于1人的专业名及人数Select专业as专业名,count(专业)as学生数From学生Groupby专业Havingcount(专业)1例33从教学库中查询出选修课程
29、超过1门的学生。Select*From学生Where学生号in(select学生号from选课groupby学生号havingcount(学生号)1)例34从教学库中查询出选课门数超过学生号为0101001学生的选课门数的所有学生。Select*From学生Where学生号in(select学生号from选课groupby学生号havingcount(学生号)(selectcount(*)from选课where学生号=0101001)7、ORDERBY选项此选项中的,等是需要是查询结果按其进行排序的列。他们可以是原表中的列名，也可以是SELECT选项中所给表达式的顺序号（即对应查询结果中的列号
30、）或定义的列名。若其后带有ASC关键字，则将按值的升序排序查询结果，若其后带有DESC关键字，则将按值的降序排序查询结果，若不指定排序方式，则默认按升序排序。注意：该选项只能用在最外层的查询语句中，不能在子查询中使用。例35从商品表中按单价升序查询出所有商品记录。Select*From商品表1Orderby单价例36从商品表1中查询出单价比平均单价高的所有商品，并使结果按降序排列。Select*From商品表1Where单价all(selectavg(单价)from商品表1)Orderby单价desc例37从教学库中查询出所有学生的学号及所选课程的门数，按门数升序排列结果Selectx.学生号
31、,count(x.学生号)as选课门数From学生x,选课yWherex.学生号=y.学生号Groupbyx.学生号Orderby选课门数例38从教学库中查询出所有学生的信息及所选课程的门数，按门数升序排列结果。Selectx.*,count(x.学生号)as选课门数From学生x,选课yWherex.学生号=选课.学生号Groupbyx.学生号,姓名,性别,专业Orderby选课门数8、INTO选项此选项能够根据查询结果自动建立一个基本表，通常作为临时表使用，可以根据需要任意建立和删除。例39从教学库中查询出每个学生的学生号、选课门数和总成绩，把查询结果保存到学生选课门数及总成绩统计表中。S
32、electx.学生号,count(x.学生号)as选课门数,sum(成绩)as总成绩into学生选课门数及总成绩统计表From学生x,选课yWherex.学生号=y.学生号Groupbyx.学生号本章小结SQLSQL是国际通用的关系数据库查询语言，几乎被每一种实际的关是国际通用的关系数据库查询语言，几乎被每一种实际的关系数据库管理系统所采用，各系统都根据本身特点对其进行了修系数据库管理系统所采用，各系统都根据本身特点对其进行了修改和扩充，在不同程度上增强了数据处理和有关方面的功能。改和扩充，在不同程度上增强了数据处理和有关方面的功能。一个数据库由基本表和视图等组成，每个基本表的结构和内容是一个
33、数据库由基本表和视图等组成，每个基本表的结构和内容是分别建立的，每个视图是根据相应的基本表（也可仍为视图分别建立的，每个视图是根据相应的基本表（也可仍为视图_)_)而而建立的，它是相应表中的一些结构和一些行内容的映象。建立的，它是相应表中的一些结构和一些行内容的映象。建立一个基本表结构，就是定义表中包含的每个列，集中包括列建立一个基本表结构，就是定义表中包含的每个列，集中包括列明、类型、长度、完整性约束等。当表中的一个完整性约束涉及明、类型、长度、完整性约束等。当表中的一个完整性约束涉及到多个列时，则必须在所有列定义之后给出相应的表及完整性约到多个列时，则必须在所有列定义之后给出相应的表及完整
34、性约束。束。SELECTSELECT查询语句具有丰富的数据查询功能，能够实现关系运算查询语句具有丰富的数据查询功能，能够实现关系运算中的大多数运算，如选择、投影、连接、并等，并且还带有分组、中的大多数运算，如选择、投影、连接、并等，并且还带有分组、排序、统计等数据处理功能。随着排序、统计等数据处理功能。随着SQLSQL版本的不断更新，必将会版本的不断更新，必将会增加越来越多的数据处理功能。增加越来越多的数据处理功能。SELECT SELECT 语句的查询结果有多种可能，有的为空，有的为一个单语句的查询结果有多种可能，有的为空，有的为一个单值元组，有的为一个多值（即含有多个列）元组，有的为单值的
35、值元组，有的为一个多值（即含有多个列）元组，有的为单值的多个元组，有的为多值的多个元组等。若为单值元组时，此查询多个元组，有的为多值的多个元组等。若为单值元组时，此查询可以作为一个数据项出现在任何表达式中。可以作为一个数据项出现在任何表达式中。SELECTSELECT语句可以作为一个语句成分（即子查询）出现在各种语语句可以作为一个语句成分（即子查询）出现在各种语句中。若在句中。若在SELECTSELECT语句的语句的WHEREWHERE选项中仍使用一个选项中仍使用一个SELECTSELECT语句，语句，则成为则成为SELECTSELECT语句的嵌套，语句的嵌套，SELECTSELECT语句可以
36、嵌套任意深度，但语句可以嵌套任意深度，但一般嵌套为二至三层。一般嵌套为二至三层。学习学习SQLSQL还要靠上机实践，为此需要针对具体的关系数据库管理还要靠上机实践，为此需要针对具体的关系数据库管理系统。在本书的第系统。在本书的第8 8、9 9章介绍了章介绍了Microsoft SQL Server 2000Microsoft SQL Server 2000数据数据库管理系统，届时同学们一定要结合试验教材进行上机训练，巩库管理系统，届时同学们一定要结合试验教材进行上机训练，巩固和提高在本章所学的理论知识。固和提高在本章所学的理论知识。写出下面的写出下面的SQL语句的句的查询结果果：1.selectcount(*);from图书；where单价between10and252.selectMAX(单价）as最高价MIN(单价)as最低价,AVG(单价)as平均价；from图书3.select借书证号，count(*);from借阅；where借书日期=3