39286-00何立群-数据库技术应用教程第5章SQL语句

第5章SQL语句,主讲：XXX教学资源邮箱：,重难点,了解SQL语言的定义;掌握数据定义语言、数据操纵语言；熟练掌握Select及其子句。,在Access中，创建和修改查询最方便的方法是使用查询“设计”视图。但是，在创建查询时并不是所有的查询都可以在系统提供的查询设计视图中进行，有的查询只能通过SQL语句来实现。比如通过命令方式创建数据表等。SQL查询是使用SQL语句创建的一种查询。,SQL是StructuredQueryLanguage（结构化查询语言）的缩写。SQL语言集数据定义、数据操纵和数据管理三大功能于一体，是目前关系数据库通用语言。查询是SQL语句的重要组成部分，但不是全部。在Access中，查询对象的本质上是一个用SQL语言编写的命令。当我们使用查询设计器窗口可视化的创建查询时，系统将自动把它转换为SQL语言编写的语句并保存起来，运行一个查询对象实质上就是执行这个SQL命令。,引例子查询,在前面章节中我们介绍了通过查询设计视图创建查询的基本方法，下面我们查询一下“比软件工程贵的图书基本信息”。分析这个查询可以看出要查询比软件工程贵的图书信息，首先需要查询软件工程这本书的价格，然后才能查找出比该书贵的图书信息。,其操作过程如下：（1）通过“图书信息表”查询软件工程图书的价格，如图5.1所示。,图5.1软件工程书籍价格查询,（2）通过查询出的书籍价格，查找比该书贵的其它书籍信息，其查询设计如图5.2所示。,图5.2子查询设计,授课内容,5.1SQL语言概述5.2查询与SQL视图5.3数据定义5.4数据操纵5.5数据查询,5.1SQL语言概述,20世纪80年代初，美国国家标准协会（ANSI）开始着手制定SQL标准，最早的ANSI标准于1986年完成，它也被叫做SQL86。随后，SQL标准几经修改和完善，其间经历了SQL89、SQL92，一直到最近的SQL99等多个版本，每个新版本都较前面的版本有重大改进。目前，各主流数据库产品采用的SQL标准是1992年制定的SQL92。SQL语句可以用来执行各种各样的操作。目前流行的关系数据库管理系统，如Qracle、Sybase、SQLServer、VisualFoxPro、Access等都采用了SQL语言标准，而且很多数据库都对SQL语句进行了再开发和扩展。,SQL语言具有如下特点：（1）SQL是一种一体化的语言。它集数据定义、数据查询、数据操纵和数据控制功能于一体，可以独立完成数据库的全部操作。（2）SQL高度非过程化。它没有必要一步步地告诉计算机“如何”去做，而只需要描述清楚用户要“做什么”，SQL语言就可以将要求交给系统，自动完成全部工作。方式为用户提供了灵活的选择余地。此外，尽管SQL的使用方式不同，但SQL语言的语法基本是一致的。,（3）SQL语言非常简洁。虽然SQL语言功能很强，但它只有为数不多的9条命令：CREATE、DROP、ALTER、SELECT、INSERT、UPDATE、DELETE、GRANT、REVOKE。另外SQL的语法也非常简单，它很接近英语自然语言，因此容易学习和掌握。（4）SQL语言可以直接以命令方式交互使用，也可以嵌入到程序设计语言中以程序方式使用。现在很多数据库应用开发工具都将SQL语言直接融入到自身的语言之中，使用起来更方便，Access就是如此。这些使用方式为用户提供了灵活的选择余地。此外，尽管SQL的使用方式不同，但SQL语言的语法基本是一致的。,Access在SQL方面支持数据定义、数据查询和数据操纵功能，但在具体实现方面也存在一些差异。在Access的查询“设计试图”窗口，为了能够看到查询对象相应的SQL语句或直接编辑SQL语句，用户只要单击“视图”菜单中的“SQL视图”命令，就可以直接编辑或者创建SQL语句。,我们前面在查询设计器中创建的查询，Access将把它们转换为对应的SQL命令，更改SQL命令后，Access也会转化其对应的视图设计。我们学习Access的时候不可避免的要用到SQL语言，因此有些初学者，就会问学Access是不是要再学SQL这个语言啊，其实我们可以不学SQL语言照样用Access做一个不错的软件，但是我们如果学了SQL将会做的更好，事实上在Access中用到SQL相对简单，我们只要了解了SQL语法就完全可以了。,注意：某些SQL查询不能在查询设计视图中进行可视化的创建，这些查询我们称为SQL特定查询。对于传递查询、数据定义查询和联合查询都属于SQL特定查询它们不能通过查询设计器进行可视化设计，必须直接在“SQL视图”中创建SQL语句。,5.2查询与SQL视图,在Access中，任何一个查询都对应着一个SQL语句，可以说查询对象的实质是一条SQL语句。当使用“设计”视图创建一个查询时，就会构造一个等价的SQL语句。查询“设计”视图和相应的“SQL”视图如图5.3所示。,图5.3查询设计视图及SQL视图,图中显示了两个视图，上面是查询的“设计”视图，它反映了某一查询的设计情况。下面是查询的“SQL”视图。视图中显示了一个Select语句，该语句给出了查询需要显示的字段、数据源以及查询条件，两种视图设置的内容是一样的，因此它们是等价的。当在“设计”视图中修改了查询，将直接反应到“SQL”视图中，同样在“SQL”视图中修改了查询语句，这种修改也将直接反应到“设计”视图中。,在建立查询的时候可以切换到SQL视图中，下面看看是怎么切换的。在功能区“创建”选项卡下的“查询”组中，单击“查询设计”按钮，打开查询设计视图，并弹出“显示表”对话框，直接关闭“显示表”对话框，在功能区“查询工具/设计”选项卡下的“结果”组中将出现“SQL视图”按钮，单击该按钮切换到SQL视图。如果是已经建好的查询，可以从“结果”组中的视图下拉列表中选项“SQL视图”进行切换。,5.3数据定义,数据定义用于定义数据库的所有特性和属性，有关数据定义的SQL语言分为三种，它们是建立（CREATE）表、修改（ALTER）表和删除（DROP）表。,定义表结构,（1）用SQL语句创建一个最基本的表结构。【格式】CREATETABLE(（，），）【说明】字段的数据类型采用英文表示字段名和数据类型前要有空格。对于固定宽度的类型如：日期型、日期时间型、备注、通用等类型可省略宽度。参数FREE可省略，如果当前打开了数据库，使用FREE强制创建的表为自由表。,【例5.1】创建一个表Book1（图书信息表1），它由以下字段组成：编号（字符，10）；名称（字符，30）；类别代码（字符，5）；出版社（字符，30）；作者（字符，8）；备注（备注）。CREATETABLEBook1(编号Text(10),名称Text(30),类别代码Text(5),出版社Text(30),作者Text(8),备注Memo),（2）创建一个数据库表，并建立主索引和候选索引。【格式】CREATETABLE(PRIMARYKEYUNIQUE，）【说明】在字段名后面加上参数PRIMARYKEY表示给此字段建立主索引，字段名后加上UNIQUE参数表示建立候选索引。,【例5.2】创建一个表Book2（图书信息表2），它由以下字段组成：编号（字符，10）；名称（字符，30）；类别代码（字符，5）。给编号字段建立主索引，给名称字段建立候选索引。CREATETABLEBook2(编号Text(10)PRIMARYKEY,名称Text(30)UNIQUE,类别代码Text(5),（3）通过查询创建表。【格式】SELECT,INTOFROM【例5.3】通过查询“图书信息表”中的书籍编号、书籍名称、类别代码、出版社和作者创建一个表Book3SELECT书籍编号,书籍名称,类别代码,出版社,作者INTOBook3FROM图书信息表;,修改表结构,（1）给表增加字段。【格式】ALTERTABLEADD(,)【例5.4】在Book1表中增加一个书籍页码字段变量（整形）ALTERTABLEBook1ADD书籍页码Short,（2）册除表中的字段。【格式】ALTERTABLEDROPCOLUMN【例5.5】删除Book1表中书籍页码字段。ALTERTABLEBook1DROPCOLUMN书籍页码,（3）更改字段的数据类型和宽度。【格式】ALTERTABLEALTERCOLUMN(,)【例5.6】修改Book1表编号字段的数据类型为整型。ALTERTABLEBook1ALTERCOLUMN编号Short,删除表,【格式】DROPTALBE【说明】此命令删除的是表文件，而不是仅仅删除表记录。【例5.7】删除Book1表DROPTABLESTUD1,5.4数据操纵,数据操纵是完成数据操作的命令，它由INSERT（插入）、DELETE（删除）、UPDATE（更新）和SELECT（查询）等命令组成。查询也划归为数据操纵范畴，但由于它比较特殊，所以查询语言在第4节单独出现。,插入记录,（1）通过具体的值向表中插入记录。【格式】INSERTINTO()VALUES（）【例5.8】有Book1表(编号Text(10),名称Text(30),类别代码Text(5)写出向该表中插入一条记录的命令。INSERTINTOBook1VALUES(97870001,计算机基础,XC0001)INSERTINTOBook1(编号,名称)VALUES(97870002,大学语文),【注意】当需要插入表中所有字段的数据时，表名后面的字段名可以缺省，但插入数据的格式及顺序必须与表的结构完全吻合；若只需要插入表中某些字段的数据，就需要列出插入数据的字段名，当然相应表达式的数据位置应与之对应。,（2）通过查询向表中插入记录【格式】INSERTINTO()SELECTFROMWHERE【例5.9】把图书信息表中，所有“人民邮电出版社”的图书信息插入到Book2表中。INSERTINTOBook2(编号,名称)SELECT图书信息表.编号,图书信息表.名称FROM图书信息表WHERE(图书信息表.出版社)=人民邮电出版社,删除记录,在SQL语句中通过DELETE命令可以给指定的数据表中的记录予以删除。【格式】DELETEFROMWHERE【注意】该命令将从表中删除满足条件的记录，当不选择WHERE字句时，表示删除表中的全部记录。【例5.10】删除“图书信息表副本”中“人民邮电出版社”出版的图书记录。DELETEFROM图书信息表副本WHERE出版社=人民邮电出版社,更新记录,【格式】UPDATESET=,=WHERE【例5.11】更改“图书信息表副本”中“人民邮电出版社”出版的图书书籍价格为30元。UPDATE图书信息表副本SET书籍价格=30WHERE出版社=人民邮电出版社,5.5数据查询,数据查询是数据库的核心操作。SQL语言提供了SELECT语言进行数据库的查询。该命令的基本框架是SELECT-FROM-WHERE，SELECT-FROM是必备结构。,单表的无条件查询,【格式】SELECTALL|DISTINCT,FROM【说明】（1）ALL:表示显示全部记录，包括重复记录（缺省值）。（2）DISTINCT:表示显示无重复结果的记录。（3）:可以用AS指定标题。（4）也可以用“*”代替表示显示所有的列。,【例5.12】查询图书信息表如下信息。（1）查询图书信息表中全部记录SELECT*FROM图书信息表（2）查询所有图书书籍名称，出版社去掉重名。SELECTDISTINCT书籍名称,出版社FROM图书信息表SELECT命令中的选项，不仅可以是字段名，还可以是表达式，也可以是一些函数。,SELECT命令中可以用到的统计函数如下所述：COUNT(*|)统计记录个数（及查询结果的行数）。SUM()计算一列值的总和AVG()计算一列值的平均MAX()求一列值中最大值MIN()求一列值中最小值,【例5.13】统计函数的应用（1）求所有图书的平均价格。SELECTAVG(书籍价格)AS平均价格FROM图书信息表（2）求馆藏“人民邮电出版社”出版的图书书量。SELECTCOUNT(*)AS藏书量FROM图书信息表WHERE出版社=人民邮电出版社,单表的带条件查询,在SELECT语句中通过WHERE语句指定查询的条件。【格式】SELECTALL|DISTINCT,FROMWHERE,【说明】在SELECT语句中通过WHERE给出查询条件，由一系列用AND或OR连接的条件表达式组成，条件表达式的格式可以是以下几种：（1）。（2）。（3）ALL（）（4）ANY|SOME（）（5）NOTBETWEENAND,（6）NOTEXISTS（）（7）NOTIN（8）NOTIN（）（9）NOTLINKSQL支持的关系运算符如下：、！、。,【例5.14】查询图书信息表中所有价格在30元以上图书的基本信息。SELECT图书信息表.*FROM图书信息表WHERE(书籍价格)30;,【例5.15】查询图书信息表中所有价格在20元到30元的图书信息。方法一：SELECT*FROM图书信息表WHERE书籍价格Between20And30方法二：SELECT*FROM图书信息表WHERE书籍价格20AND书籍价格0;,查询SELECT语句不仅可以通过WHERE之句查找满足条件的记录，还可以通过聚合函数对满足条件的数据进行求和、计数、平均值、最大值、最小值等运算。,分组与计算查询,标准的SQL语句提供了5种常用的聚合函数。COUNT(DISTINCT|ALL*|列名)：统计元组个数。COUNT(DISTINCT|ALL)：统计一列中值的个数。SUM(DISTINCT|ALL)：计算一列值的总和（此列必须是数值型）AVG（DISTINCT|ALL）：计算一列值的平均值（此列必须是数值型）MAX（DISTINCT|ALL）：计算一列值的最大值MIN（DISTINCT|ALL）：计算一列值的最小值这些聚合函数一般用于从一组数值中计算一个汇总值，在SQL语句中通过GROUPBY子句定义字段值的分组。,【格式】SELECT分组表达式，集函数FROM表名WHEREGROUPBYHAVING【说明】（1）GROUPBY：表示分组查询，若查询到的数据里有多个记录的指定字段的值相同，只取一条记录作为查询结果。,（2）HAVING：设置过滤条件，在其后可以使用聚合函数。【例5.17】统计图书信息表中各个出版社出版图书数量。SELECT出版社,Count(书籍编号)AS图书数量FROM图书信息表GROUPBY出版社;,查询结果排序,为了方便按照某个顺序对数据表中的数据进行查找，在查询语句中经常需要进行排序。用户可以用ORDERBY子句指定按照一个或多个属性列的升序（ASC）或降序(DESC)重新排列查询结果，其中升序ASC为缺省值。,【例5.18】查询图书信息表所有图书信息，并按书籍名称排序。SELECT*FROM图书信息表ORDERBY书籍名称;,【例5.19】查询图书信息表所有图书信息，并按书籍价格降序排序。SELECT*FROM图书信息表ORDERBY书籍价格DESC;,【例5.20】图书信息表所有图书信息，并按出版社降序排序，对出版社相同的按照书籍名称升序排序。SELECT*FROM图书信息表ORDERBY出版社DESC,书籍名称ASC;,多表连接查询,在SQL语句中，在FROM子句中提供了一种称之为连接的子句，连接分为内连接、左连接和右连接。在查询设计窗口中，通过双击两个表连接线的中间部分，可以打开【联接属性】对话框，如图5.4所示。通过在此对话框中选择联接的属性的1，2，3分别实现内连接、左连接和右连接。,图5.4联接属性对话框,（1）内连接内连接是指包括符合条件的每个表的记录，也就是说两个表中具有相同值的行会在结果表中显示。,【例5.21】查询并显示书籍编号、书籍名称、书籍类别、最大借出天数。方法一：SELECT图书信息表.书籍编号,图书信息表.书籍名称,图书类别表.书籍类别,图书类别表.最大借出天数FROM图书类别表INNERJOIN图书信息表ON图书类别表.类别代码=图书信息表.类别代码;,方法二：SELECT图书信息表.书籍编号,图书信息表.书籍名称,图书类别表.书籍类别,图书类别表.最大借出天数FROM图书类别表,图书信息表WHERE图书类别表.类别代码=图书信息表.类别代码;我们通过运行查询可以看到上边两种方法得到的结果完全相同。因此内连接可以通过INNERJOINON之句实现也可以通过WHERE之句实现。,（2）左连接左连接是指包含左表中的所有记录和右表中与左表相对应的记录。【例5.22】查询图书信息表中所有图书以及对应图书类别信息。SELECT图书信息表.书籍编号,图书信息表.书籍名称,图书类别表.书籍类别,图书类别表.最大借出天数FROM图书信息表LEFTJOIN图书类别表ON图书类别表.类别代码=图书信息表.类别代码;,（3）右连接右连接和左连接刚好相反，