《数据库层次结构》PPT课件.ppt

第6章关系数据库标准语言SQL,国家级“十一五”规划教材配套电子教案,第1节SQL概述第2节数据查询第3节数据操纵第4节数据定义,本章内容,第1节SQL概述,一、SQL语言简介SQL（StructuredQueryLanguage）结构化查询语言，其主要功能是同各种数据库建立联系，进行沟通。目前，绝大多数流行的关系型数据库管理系统，如Oracle，Sybase，MicrosoftSQLServer，Access等都采用了SQL语言标准。二、SQL语言的特点：（1）是一种一体化的语言，包括数据定义、数据查询、数据操纵和数据控制功能。（2）是一种高度非过程化的语言；SQL语言非常简洁。（3）SQL既是自含式语言，又是嵌入式语言，可以用同一种语法结构提供两种使用方式。（4）SQL语言采用面向集合的操作方式；核心是查询。,第2节数据查询,一、SELECT语句的语法,SELECT命令的常用格式如下：SELECT-FROM-WHERE-GROUPBY-ORDERBY-INTO,SELECT子句：说明要查询的数据，对应于【查询设计器】中的【字段】选项卡。FROM子句：说明要查询的数据来自哪个表或哪些表，对应于【查询设计器】中【添加表或视图】操作。WHERE子句：说明查询条件，对应于【查询设计器】中的【筛选】选项卡。GROUPBY子句：用于对查询结果进行分组，对应于【查询设计器】中的【分组】选项卡。,第2节数据查询,查询结果输出选项：来指定查询结果的输出去向，对应于【查询设计器】中【查询去向】对话框。查询结果显示选项：ALL|DISTINCTTOPnPERCENT关键词用来控制查询结果的显示情况，对应于【查询设计器】中的【杂项】选项卡。,一、SELECT语句的语法（续）,第2节数据查询,二、简单查询,【例6-1】检索公寓表中的所有记录。命令：select*from公寓说明：通配符“*”来设定返回表中的所有列,【例6-2】从学生表中检索所有系。命令1：select系from学生命令2：selectDISTINCT系AS系部名称from学生说明：DISTINCT关键词的作用是去掉重复值，AS指定了输出字段的标题。,【例6-3】检索学费不低于4000元的专业简称及其收费情况。命令：select专业简称,学费from专业where学费=4000,第2节数据查询,二、简单查询,【例6-4】检索出经贸系和信息系贫困学生的学号、姓名和所在班级。命令：select学号,姓名,班级from学生;where家庭情况=贫困AND(系=经贸系OR系=信息系),说明：这个SQL语句较长，分为两行来写，“;”为续行符；这个查询的条件很简单，但初学者很容易出错，往往把查询要求中的“和”理解成逻辑运算符中的“AND”，最后的查询条件写为“where系=信息系AND系=经贸系”，从而查不到符合条件的记录。,三、简单的联接查询,【例6-5】检索欠费学生的基本情况和所欠金额。分析：查询需要输出的信息：学生情况和欠费金额，分别来源于学生表和交费表，分析得知两表之间存在一对一的联系，可通过公共字段“学号”建立。这样的查询一般用联接查询来实现。命令：select学生.*,欠交金额from学生,交费;where学生.学号=交费.学号AND欠交金额0说明：“学生.*”代表学生表中所有的字段；当from之后有多个表时，含有公共字段的表达式，必须用表名前缀直接指明公共字段所属的表，如“学生.学号=交费.学号”，而非公共字段则可省略表前缀，如“欠交金额”。,第2节数据查询,三、简单的联接查询,【例6-6】检索每个学生所在专业与应交的学费，所住公寓与住宿费的情况。命令：select学号,姓名,专业.专业简称,学费,公寓.公寓类别,住宿费;from学生,专业,公寓;where学生.专业=专业.专业简称AND学生.公寓类别=公寓.公寓类别,第2节数据查询,所谓嵌套查询就是，查询所输出的信息来自一个表，而查询的条件却涉及到一个或多个表。,四、嵌套查询,【例6-7】检索至少有一个学生欠费的系。命令：selectDISTINCT系from学生where学号IN;(select学号from交费where欠交金额0)说明：这个检索命令由两个SELECT子句构成，即内层查询（子查询）和外层查询，子查询查到的结果是欠费学生的学号，外层查询在利用这个学号在学生表中查找欠费学生所在的系，因为查询的结果有重复，而我们只对欠费的系感兴趣，所以，用DISTINCT关键词去掉了重复值。注意：子查询用一对圆括号括起来，且子查询不能嵌套。,第2节数据查询,四、嵌套查询,【例6-8】检索和会计学专业收费相同的专业。命令：select专业简称from专业;where专业简称!=会本AND学费=;(select学费from专业where专业简称=会本)说明：命令用专业简称!=“会本”条件来去掉“会计学”专业本身,第2节数据查询,第2节数据查询,在查询中，还可以加入运算符，如算术运算符和函数运算符，对原始表中的数据进行计算。常用的统计函数有：计数函数COUNT（）、求和函数SUM（）、求平均值函数AVG（）、求最大值函数MAX（）、求最小值函数MIN（）。,五、简单的计算查询,【例6-9】输出每个学生的学号，姓名和年龄。分析：表中并没有年龄字段，需要根据出生日期计算每个人的年龄。命令：select学号,姓名,YEAR(DATE()-YEAR(出生日期)AS年龄from学生,【例6-10】算出生源所在地的数目。命令：selectCOUNT(DISTINCT籍贯)from学生,第2节数据查询,五、简单的计算查询,COUNT（）函数的使用要点：如要对表中记录个数进行计数，一般用COUNT（*），而对其他字段的统计则用COUNT（DISTINCT列名）。,【例6-11】计算所有学生的人数。命令1：selectCOUNT(*)from学生命令2：selectCOUNT(*)AS总人数from学生说明：第二条命令修改了查询结果的列名，这样方便于对查询结果的浏览。,第2节数据查询,【例6-12】计算学生所欠的总金额数。命令：selectsum(欠交金额)from交费,SUM（）函数,【例6-13】计算信息系和经贸系学生所欠的金额数。命令：selectsum(欠交金额)from交费where学号IN;(select学号from学生where系=信息系OR系=经贸系),【例6-14】计算人均欠费金额。命令：selectAVG(欠交金额)from交费,AVG（）函数,MAX（）函数,第2节数据查询,【例6-15】查找信息系欠费金额最高的学生学号、姓名及所欠金额。命令：select学生.学号,姓名,欠交金额from学生,交费;where学生.学号=交费.学号AND系=信息系AND欠交金额=;(selectMAX(欠交金额)from学生,交费where学生.学号=交费.学号AND系=信息系),【例6-16】查找学费最低的专业。命令：select专业简称,学费from专业where学费=(selectMIN(学费)from专业),MIN（）函数,第2节数据查询,六、分组与计算查询,有时需要对查询结果进行分类统计，这就要用到GROUPBY子句。GROUPBY子句可对数据进行分组，若要限定分组的条件，则需启用HAVING子句。,【例6-17】统计各系欠费学生的人数，并按欠费人数降序排序。命令：select系AS系部名称,COUNT(*)AS欠费人数from学生groupby系;where学号IN(select学号from交费where欠交金额0);ORDERBY欠费人数,第2节数据查询,六、分组与计算查询,【例6-18】列出欠费学生人数超过2人的系部。命令：select系AS系部名称,COUNT(*)AS人数from学生;groupby系having(COUNT(*)2);where学号IN;(select学号from交费where欠交金额0),说明：HAVING子句总是跟在GROUPBY子句之后，不可以单独使用，也不可以在其后使用子查询,第2节数据查询,为了方便浏览，常常要把查询结果按一定的标准排序，在SQL语言中，可以用ORDERBY子句按一列或多列对查询结果进行升序（ASC）或降序（DESC）排列。,七、排序,【例6-19】按学费从低到高的顺序列出全部专业信息。命令：select*from专业orderby学费说明：如省略ASC|DESC关键字，查询结果默认按升序（ASC）进行排列,第2节数据查询,【例6-20】输出学生的学号，姓名，性别，籍贯，班级信息，查询结果按籍贯排序，籍贯相同的再按性别排序。命令：select学号,姓名,性别,班级,籍贯from学生orderby5,3说明：排序关键字可以是一个字段或字段表达式，也可以是一个数值表达式，这个数值是表或查询结果中列的位置，其中最左边的列的编号为1。,【例6-21】按系列出欠费学生的学号，姓名，系，班级，欠交金额，同一个系的按欠交金额降序排列。命令：select学生.学号,姓名,系,班级,欠交金额from学生,交费where学生.学号=交费.学号AND学生.学号IN;(select学号from交费where欠交金额0);orderby系,欠交金额DESC,第2节数据查询,八、查询结果的显示和输出,1.显示部分查询结果（TOPnPercent）使用TOPnPERCENT短语限制返回的记录行数，TOPn说明返回n行，而TOPnPERCENT时，说明n是一个百分数，指定返回的行数等于总行数的百分之几。【例6-22】显示学费最高的前5个专业的信息。命令1：select*TOP5from专业orderby学费DESC,专业简称说明：如果排序关键字存在重复值，则TOP关键字将失效，记录并没有按指定的数目显示。为了避免这种情况出现，可以在其后加入一个没有重复值的主键字段。,第2节数据查询,八、查询结果的显示和输出,【例6-23】列出84年以后出生的学生信息，只显示其中50%的记录即可。命令：select*TOP50percentfrom学生;where出生日期1983-12-31orderby出生日期,2.将查询结果存放到永久表文件（DBF|TABLE）中在SELECT语句中使用短语INTODBF|TABLE，可将查询结果存放到永久表。例如：将【例6-21】的查询结果按降序排列存放到“各系学生欠费金额一览表”中，命令为：select学生.学号,姓名,系,班级,欠交金额from学生,交费;where学生.学号=交费.学号AND学生.学号IN;(select学号from交费where欠交金额0);orderby系,欠交金额DESC;intotable各系学生欠费金额一览表,第2节数据查询,3.将查询结果存放到临时表文件（CURSOR）中使用短语INTOCURSOR，可以将查询结果存放到临时表文件中。当查询结束后，该临时表是当前文件，但仅是只读的dbf文件，关闭文件时该临时表将自动删除。临时表常在程序设计中作为表格、列表框或组合框的数据源。例如：将查询到的专业信息存放到临时表文件ZYtemp中，命令为：select*from专业intocursorZYtemp,4.将查询结果存放到数组（ARRAY）中VisualFoxPro程序设计中经常将表中的数据和数组数据进行交换。我们可以用INTOARRAY将查询结果存放到一个二维数组中，数组的每一行对应一条记录，每一列对应查询结果中的一列。,第2节数据查询,5.将查询结果存放到文本文件（FILE）中可以用TOFILE短语将查询结果存放到文本文件中（同时显示在屏幕上），默认是.TXT文件。例如：将公寓表中的信息存放到文本文件gy中，命令如下：select*from公寓tofilegy,例如：将公寓表中的记录存放到数组GYarray中，命令如下：select*from公寓intoarrayGYarray,第2节数据查询,6.将查询结果输出到屏幕（SCREEN）例如：将公寓表中的信息显示在屏幕上，命令如下：select*from公寓toscreen,7.将查询结果输出到打印机（PRINTER）使用短语TOPRINTERPROMPT可以将查询结果输出到打印机，如果使用了PROMPT，在开始之前会打开“打印设置”对话框。,查询去向一览表,第2节数据查询,九、超联接查询,格式：FROMDatabaseName!TableASLocal_AliasINNER|LEFTOUTER|RIGHTOUTER|FULLOUTERJOINDatabaseName!TableASLocal_AliasONJoinCondition,从上面的格式可看出，超联接分为内联接和外联接，外联接又分为左联接、右联接和全联接，且联接的条件在FROM子句中由JOINON短语来指定。在【查询设计器】中的操作见第5章中“图5.15设定联接条件”。,第2节数据查询,九、超联接查询,1.内联接使用INNERJOIN短语的联接称为内联接，与以前的自然联接效果等同，即只有满足联接条件的记录才出现在查询结果中。命令1：select学生.学号,姓名,班级,应交金额,已交金额,欠交金额;from学生INNERJOIN交费ON学生.学号=交费.学号命令2：select学生.学号,姓名,班级,应交金额,已交金额,欠交金额;from学生JOIN交费ON学生.学号=交费.学号命令3：select学生.学号,姓名,班级,应交金额,已交金额,欠交金额;from学生,交费where学生.学号=交费.学号以上3种格式的命令是等价的。,第2节数据查询,2.左联接使用LEFTOUTERJOIN短语的联接称为左联接，即除满足联接条件的记录出现在查询结果中外，第一个表（JOIN左侧的表）中不满足条件的记录也出现在查询结果中。命令：select学生.学号,姓名,班级,应交金额,已交金额,欠交金额;from学生LEFTJOIN交费ON学生.学号=交费.学号,3.右联接使用RIGHTOUTERJOIN短语的联接称为右联接，即除满足联接条件的记录出现在查询结果中外，第二个表（JOIN右侧的表）中不满足条件的记录也出现在查询结果中。,第2节数据查询,命令：select学生.学号,姓名,班级,交费.学号,应交金额,已交金额,欠交金额;from学生RIGHTJOIN交费ON学生.学号=交费.学号,4.全联接使用FULLOUTERJOIN短语的联接称为全联接，即除满足联接条件的记录出现在查询结果中外，两个表中不满足条件的记录也全部出现在查询结果中。命令：select学生.学号,姓名,班级,交费.学号,应交金额,已交金额,欠交金额;from学生FULLJOIN交费ON学生.学号=交费.学号,说明：出现在查询结果中不满足联接条件的记录将另一表中的字段值设置为空值.NULL.。,第2节数据查询,十、别名与自联接查询,多表联接查询中，我们经常用表名作前缀来区分公共字段。但是如果表名较长，联接的表比较多，就会很麻烦。为此，SQL提供了在FROM子句中为表定义别名的功能，格式为：AS例如，要查询每个学生的学号，姓名，所学课程名称，该门课的得分，就要涉及到3个表，2个联接：命令1：selectstudent.学号,姓名,课程名称,成绩;fromstudent,score,course;wherestudent.学号=score.学号ANDscore.课程号=course.课程号如果使用别名，查询命令就会简单一些。,第2节数据查询,十、别名与自联接查询,命令2：selectA.学号,姓名,课程名称,成绩fromstudentA,scoreB,courseC;whereA.学号=B.学号ANDB.课程号=C.课程号上述例子中，别名并不是必须的，但是在表的自联接查询中，别名就是必不可少的。SQL不仅可以对多个表进行联接查询，也可以将同一个表与其自身进行联接，这种联接就称为自联接查询，也称递归查询。【例6-24】根据课程关系列出每门课程以及其先修课程名单。命令：selectA.课程名称,的先修课程是,B.课程名称fromcourseA,courseB;whereB.课程号=A.先修课号;toscreenPLAIN,第2节数据查询,十一、内外层相关嵌套查询,自联接是一种出自同一个表同一值域的两个不同字段联接的递归查询，其实同一个表中的同一字段在特殊情况下也需要进行联接。另外，在6.2.4讲述的嵌套查询都是外层查询依赖于内层查询的结果，而内层查询与外层查询无关。事实上，有时也需要内、外层相关的查询，这时内层查询的条件需要外层查询提供值，而外层查询的条件需要内层查询的结果。【例6-25】检索出每个学生所考课程中得分最高的成绩信息。命令：selectout.*fromscoreoutwhere成绩=;(selectMAX(成绩)fromscoreinner1;whereout.学号=inner1.学号),第2节数据查询,十一、内外层相关嵌套查询,说明：在这个查询中，外层查询和内层查询使用同一个表“成绩.dbf”。外层查询给成绩表指定别名out，内层查询给成绩表指定别名inner1。外层查询提供out表中每个记录的学号给内层查询使用；内层查询利用这个学号，确定该学生所考课程中的最高成绩；随后外层查询再根据out表中的成绩与该成绩进行比较，如果相等，则该记录被选择。,第2节数据查询,十二、集合并运算,有时需要把两个或两个以上的查询结果合并到一个结果中，这就要用到UNION操作，也即集合并运算。进行并运算，要求两个查询结果具有相同的字段个，并且对应字段应具有相同的数据类型和取值范围。,第2节数据查询,十二、集合并运算,【例6-26】把score表和new_score表的数据合并存放到临时表temp中。命令：select*fromscore;UNION;select*fromnew_score;intocursorhebing,第2节数据查询,十三、特殊运算符的使用,SQL支持的运算符如下：逻辑运算符(用于多条件的逻辑连接)：NOT（非）、AND（与）、OR（或）比较运算符(大小比较)：、=、=、!、!=19AND年龄0注意：我们在第3章中学习过的ReplaceWith命令与UNPDATE语句的功能是一样的，都用于更新表中数据，所以初学者很容易混淆，读者要熟记各自的语法以免出错。,三、删除,第3节数据操纵,SQL语言同样用Delete命令来删除表中的记录，不过语法稍有不同。格式：DELETEFROM表名WHERE条件FROM指定要删除的表名，WHERE指定要删除的记录应满足的条件，如果没有WHERE子句，则删除全部记录。例如：删除公寓表中所有记录，命令为：DELETEFROM公寓注意：同第3章学习的DELETE命令一样，SQL的DELETEFROM命令也只是逻辑删除记录，要彻底删除记录还需继续用PACK命令。,一、表的定义,第4节数据定义,例如：用SQL命令建立销售记录数据库。1.创建数据库命令：CREATEDATABASE销售记录2.创建商品信息表命令：CREATETABLE商品信息(商品号C(3)PRIMARYKEY,商品名C(15),;单价N(8,2)NULL,数量I,厂名C(20),产地C(8)DEFAULT中国北京)说明：上述命令创建了“商品信息”表，“商品号”字段为字符型，宽度为3，PRIMARYKEY指定“商品号”为主关键字；“单价”字段为数值型，宽度为8位，小数位数2位，并且允许其值为空值；“数量”字段为整型I；“产地”字段用DEFAULT设置了默认值。3.创建购买信息表，并建立和商品信息表的联系命令：CREATETABLE购买信息(会员号C(2),商品号C(3)单价N(8,2),;数量ICHECK(数量0)ERROR数量不能为0!,日期D,;FOREIGNKEY商品号TAG商品号REFERENCES商品信息)说明：上述命令创建了“购买信息”表，用CHECK为“数量”字段设置了有效性规则，用ERROR为规则设置提示信息；用“FOREIGNKEY商品号TAG商品号”在该表的“商品号”字段上建立了一个普通索引，同时说明该字段是联接字段，通过引用商品信息表的主索引“商品号”与商品信息表建立了联系。,第4节数据定义,二、表的删除,在第3章中介绍用DELETEFILE来删除表及其他类型文件，用REMOVETABLE命令来移去数据库表。在SQL中删除表的命令为：DROPTABLE表名这种删除是直接删除，所以读者在操作时要慎重。且如果要删除的表属于某个数据库，应先打开该数据库再进行删除，以避免数据库出现不必要的错误提示。例如：删除销售记录数据库中的商品信息表，命令序列为：OPENDATABASE销售记录DROPTABLE商品信息,1增加字段或修改已有字段类型或宽度的命令格式：ALTERTABLE表名ADD|ALTER字段名其中ADD用于增加新的字段，ALTER用于修改现有字段。例如：为购买信息表增加一个折扣字段，折扣范围在（0，1之