vfp结构化查询语言(SQL)PPT课件

.,1,第五章结构化查询语言(SQL),.,2,本章目录,第一节SQL概述第二节数据定义功能第三节数据操纵功能第四节数据查询功能,.,3,一、SQL语言的特点1综合统一SQL语言集数据定义（DDL）、数据操纵（DML）、数据管理（DCL）的功能于一体，语言风格统一，可以独立完成数据库的全部操作，包括定义关系模式、录入数据及建立数据库、查询、更新、维护数据、数据库的重新构造、数据库安全性等一系列操作的要求，为数据库应用系统开发者提供了良好的环境。2高度非过程化3面向集合的操作方式4以同一种语法结构提供两种使用方式5语言简洁，易学易用,第一节SQL概述,.,4,二、SQL语言的基本概念SQL语言支持关系型数据库的三级模式结构。其中外模式对应于视图（View）和部分基本表（BaseTable），模式对应于基本表，内模式对应于存储文件。基本表是本身独立存在的表，在SQL语言中一个关系对应一个表。一些基本表对应一个存储文件，一个表可以带若干索引，索引存放在存储文件中。存储文件的逻辑结构组成了关系型数据库的内模式。而存储文件的物理文件结构是任意的。视图是从基本表或其他视图中导出的表，它本身不独立存储在数据库中，也就是说数据库只存放在视力的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中，因此视图是一个虚表。,.,5,数据定义语言DDL用于执行数据定义的操作，如创建或删除表、索引和视图之类的对象。由CREATE、DROP、ALTER命令组成，完成数据库对象的建立（CREATE)、删除（DROP）和修改（ALTER）。一、定义（创建）表【格式】CREATETABLE（，）完整性约束NULL|NOTNULL，）PRIMARYKEY|UNIQUEDEFAULTREFERENCESTAG【功能】定义（也称创建）一个表。,第二节数据定义功能,.,6,数据类型说明,.,7,【例5-1】创建一个表STUD（学生信息表），它由以下字段组成：学号（C，10）；姓名（C，8）；性别（C，2）；班级名（C，10）；系别代号（C，2）；地址（C，50）；出生日期（D）；是否团员（L）；备注（M）。CREATETABLESTUD（学号C（10），姓名C（10），性别C（2），班级名C（10）,系别代号C（2）,地址C（50）,出生日期D,是否团员L,备注M,照片G）LISTSTRUCTURE【例5-2】创建一个表SC（课程成绩表），它由以下字段组成：学号（C，10）；课程号（C，2）。CREATETABLESC（学号C（10），课程号C（2）)LISTSTRUCTURE,.,8,【例5-3】利用SQL命令建立“图书管理”数据库，其中包含个表：读者表、作者表、图书表、借书表。操作步骤如下：1创建数据库:CREATEDATABASE图书管理2用SQL命令建立“读者”表：CREATETABLE读者(读者编号C(9)NOTNULL;PRIMARYKEY,姓名C(8),性别C(2)CHECK(性别=“;男”OR性别=“女”)DEFAULT“男”,出身日期D,少;数民族否L,籍贯C(10),联系电话C(8),照片G)说明：该命令在当前打开的图书管理数据库中建立读者表，其中读者编号为主关键字，用CHECK对性别字段值说明了有效性规则，并设定了性别字段默认值为“男”。,.,9,3.用SQL语句建立“作者”表：CREATETABLE作者(作者编号C(2)PRIMARYKEY,;作者姓名C(10),职称C(10),联系电话C(10),备注M)4.用SQL语句建立“图书”表：CREATETABLE图书(书号C(6)NOTNULLPRIMARYKEY,;作者编号C(2)NOTNULL,书名C(14),价格N(8,2),;数量I,出版社C(18),;FOREIGNKEY作者编号TAG作者编号REFERENCES作者)说明：该命令在当前打开的图书管理数据库中建立图书表，其中书号为主关键字，“FOREIGNKEY作者编号”表示在该表的“作者编号”字段上建立了一个普通索引，同时说明该字段是联接字段，通过引用作者表中的主索引“作者编号”（TAG作者编号REFERENCES作者）与作者表建立联系。,.,10,5.用SQL语句建立“借书”表：CREATETABLE借书(读者编号C(6)NOTNULL,读者姓名C(10),书号C(6),借书日期DDEFAULTDATE(),还书日;期DNULL,;FOREIGNKEY读者编号TAG读者编号REFERENCES读者;FOREIGNKEY书号TAG书号REFERENCES图书)说明：该命令在当前打开的图书管理数据库中建立借书表，其中“FOREIGNKEY读者编号”在该表的“读者编号”字段上建立了一个普通索引，同时说明该字段是联接字段，通过引用读者表中的主索引“读者编号”（TAG读者编号REFERENCES读者）与读者表建立联系。这里要注意：“FOREIGNKEY读者编号TAG读者编号REFERENCES读者”语句中前后两个“读者编号”的含义不同。,.,11,6.执行完以上命令后可以在数据库设计器中看到如图5-1所示界面。,图5-1数据库设计器图书管理,.,12,【格式】ALTERTABLEADD（，）完整性约束NULL|NOTNULLDROP完整性约束MODIFY【功能】修改表结构。【说明】ADD子句用于增加指定表的字段变量名、数据类型、宽度和完整性约束条件；DROP子句用于删除指定的的完整性约束条件；MODIFY子句用于修改原有的字段变量的值。【例5-4】在课程成绩表SC中,增加一个成绩字段变量(N，3）。ALTERTABLESCADD成绩N(3)LISTSTRUCTURE,二、修改表,.,13,【例5-5】修改表结构综合练习。（1）创建数据库和数据库表CREATEDATABASEtempbaseCREATETABLEtemp1(aaC(2),bbN(5,2),ddD)CREATETABLEtemp2(aa1C(2),bb1N(5,2),dd1I)（2）以字段aa为索引表达式为表temp1增加一个主索引，索引标识为aa_keyALTERTABLEtemp1ADDPRIMARYKEYaaTAGaa_key（3）以字段aa1为索引表达式给表temp2增加一个普通索引，索引标识aa1_key；并且将表temp1的索引aa_key作为temp2关联字段。ALTERTABLEtemp2ADDFOREIGNKEYaa1TAGaa1_key;REFERENCEStemp1TAGaa_key,.,14,（4）将表temp1中“dd”的字段名改为“cc”ALTERTABLEtemp1RENAMECOLUMNddTOcc（5）删除表temp2中字段名为“dd1”的字段ALTERTABLEtemp2DROPdd1（6）将（3）所建的普通索引的关联字段删除。ALTERTABLEtemp2DROPFOREIGNKEYTAGaa1_key;SAVE（7）将（3）所建的普通索引删除。ALTERTABLEtemp2DROPFOREIGNKEYTAGaa1_key注意：增加普通索引时，必须同时指明与它关联的字段。,.,15,【格式】DROPDATABASE【功能】删除指定数据库的结构和数据。【说明】谨慎使用。四、删除表【格式】DROPTALBE【功能】删除指定表的结构和内容（包括在此表上建立的索引）。【说明】如果只是想删除一个表中的所有记录，则应使用DELETE语句。,三、删除数据库,.,16,数据操纵语言是完成数据操作的命令，一般分为两种类型的数据操纵，它们统称为DML：数据检索（常称为查询）：寻找所需的具体数据。数据修改：添加、删除和改变数据。数据操纵语言一般由INSERT（插入）、DELETE（删除）、UPDATE（更新），SELETE（检索，又称查询）等组成，由于SELETE比较特殊，所以一般又将它以查询（检索）语言单独出现。,第三节数据操纵功能,.,17,【格式1】INSERTINTOVALUES（）【格式2】INSERTINTOFROMARRAY|FROMMEMVAR【功能】在指定的表文件末尾追加一条记录。格式1用表达式表中的各表达式值赋值给中的相应的各字段。格式2用数组或内存变量的值赋值给表文件中各字段。【说明】如果某些字段名在INTO子句中没有出现，则新记录在这些字段名上将取空值（或默认值）。但必须注意的是，在表定义说明了NOTNULL的字段名不能取空值。,一、插入记录,.,18,：指定表文件中的字段，缺省时，按表文件字段的顺序依次赋值。：指定要追加的记录各个字段的值。【例5-6】在表文件STUD的末尾追加三条记录。*用表达式方式追加第一条记录*INSERTINTOSTUD(学号，姓名，性别，班级名，系别代号，地址，出生日期，是否团员);VALUES(011110，李建国，男，计0121，01，湖北武汉，09/28/02，.T.),.,19,DIMENSIONDATA8DATA（1）=011103DATA（2）=李宁DATA（3）=女DATA（4）=电0134DATA（5）=02DATA（6）=江西九江DATA（7）=05/06/85DATA（8）=.F.INSERTINTOSTUDFROMARRAYDATA,*用数组方式追加第二条记录*,.,20,学号=011202姓名=赵娜性别=女班级名=英0112系别代号=03地址=广西南宁出生日期=02/21/84是否团员=.F.INSERTINTOSTUDFROMMEMVARLIST,*用内存变量方式追加第三条记录*,.,21,【格式】UPDATESET=，=WHERE【功能】更新指定表文件中满足WHERE条件子句的数据。其中SET子句用于指定列和修改的值，WHERE用于指定更新的行，如果省略WHERE子句，则表示表中所有行。【说明】更新操作又称为修改操作。【例5-7】将成绩表（SC）中，所有课程号为02的成绩各加5分。UPDATESCSET成绩=成绩+5;WHERE课程号=02,二、更新记录命令,.,22,三、删除记录,【格式】DELETEFROMWHERE【功能】从指定的表中删除满足WHERE子句条件的所有记录。如果在DELETE语句中没有WHERE子句，则该表中的所有记录都将被删除。【说明】这里的删除是逻辑删除，即在删除的记录前加上一个删除标记“*”。【例5-8】删除STUD表中所有性别为男的记录。DELETEFROMSTUD;WHERE性别=“男”,.,23,第四节数据查询功能,数据库中的数据很多时侯是为了查询的，因此，数据查询是数据库的核心操作。而在SQL语言中，查询语言中有一条查询命令，即SELECT语句。一、基本查询语句【格式】SELECTALL|DISTINCTFROM【功能】无条件查询。【说明】ALL：表示显示全部查询记录，包括重复记录。DISTINCT：表示显示无重复结果的记录。,.,24,【例5-9】显示STUD(学生信息数据表)中的所有记录。SELECT*;FROMSTUD命令中的*表示输出显示所有的字段，数据来源是STUD表，表中的内容以浏览方式显示。【例5-10】显示STUD（学生信息数据表）中的所有的学号及与之对应的姓名，同时能去除重名。SELECTDISTINCT学号，姓名;FROMSTUD,.,25,【例5-11】显示SC（课程成绩表）中的所有记录，并将成绩一项乘以0.7。SELECT学号，课程号，成绩*0.7AS成绩;FROMSC二、带条件(WHERE)的查询语句【格式】SELECTALL|DISTINCTFROMWHERE【功能】从一个表中查询满足条件的数据。【说明】由一系列用AND或OR连接的条件表达式组成，条件表达式的格式可以是以下几种：,.,26,（1）。（2）。（3）ALL（）（4）ANY|SOME（）（5）NOTBETWEENAND（6）NOTEXISTS（）（7）NOTIN（8）NOTIN（）（9）NOTLINK,.,27,WHERE子句中的条件运算符,.,28,SQL支持的关系运算符如下：、！、。【例5-12】显示STUD表中所有男生记录的学号，姓名和性别字段值。SELECT学号，姓名，性别;FROMSTUDWHERE性别=男【例5-13】显示STUD表中出生日期在85年之间的学生的学号，姓名，出生日期。SELECT学号，姓名，出生日期;FROMSTUD;WHERE出生日期BETWEEN01/01/85AND12/31/86,.,29,【例5-14】显示STUD表中姓李的学生的学号，姓名，出生日期。SELECT学号，姓名，出生日期;FROMSTUD;WHERE姓名LIKE“李%”,.,30,三、SQL的复杂查询,1连接查询【说明】在一个数据库中的多个表之间一般都存在着某些联系，在一个查询语句中同时涉及到两个或两个以上的表时，这种查询称之为连接查询（也称为多表查询）。在多表之间查询必须处理表与表之间的连接关系。SELECTALL|DISTINCTFROM，表2.WHERE,.,31,【例5-15】查询并显示各个学生的学号，姓名，各科成绩及课程名。SELECTa.学号，a.姓名，b.课程名，c.成绩;FROMSTUDa，COURSEb，SCc;WHEREa.学号=c.学号.AND.b.课程号=c.课程号【例5-16】查询并显示各个学生所学课程的情况。SELECTSTUD.学号，STUD.姓名，COURSE.课程名;FROMSTUD，SC，COURSE;WHERESTUD.学号=SC.学号.AND.SC.课程号=COURSE.；课程号,.,32,2连接问题在SQL语句中，在FROM子句中提供了一种称之为连接的子句，连接分为内连接和外连接，外连接又可分为左外连接、右外连接和全外连接。（1）内连接内连接是指包括符合条件的每个表的记录，也称之为全记录操作。而上面两个例子就是内连接。【例5-17】查询并显示各个学生的学号，所学课程及课程成绩。SELECTSC.学号，COURSE.课程名，SC.成绩;FROMSC，COURSE;WHERESC.课程号=COURSE.课程号,.,33,如果采用内连接方式，则命令如下：SELECTa.学号，b.课程名，成绩;FROMSCaINNERJOINCOURSEbONa.课;程号=b.课程号将会得到完全相同的结果。（2）外连接外连接是指把两个表分为左右两个表。右外连接是指连接满足条件右侧表的全部记录。左外连接是指连接满足条件左侧表的全部记录。全外连接是指连接满足条件表的全部记录。,.,34,3嵌套查询在SQL语句中，一个SELECTFROMWHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询或子查询。【例5-18】显示“李宁”所在班级的学生名单。SELECT学号，姓名，班级名;FROMSTUD;WHERE班级名=（SELECT班级名FROMSTUD;WHERE姓名=李宁）,.,35,4分组与计算查询【格式】SELECTALL|DISTINCTFROMWHEREGROUPBYHAVINGORDERBYASC|DESC【功能】包括有排序、函数运算和谓词演算,.,36,【例5-19】按出生日期降序显示STUD表中的学号，姓名，出生日期。SELECT学号，姓名，出生日期;FROMSTUD;ORDERBY出生日期DESC【例5-20】按成绩升序显示SC表中的学号，课程号，成绩。SELECT学号，课程号，成绩;FROMSC;ORDERBY成绩,.,37,【例5-21】按班级分类显示学生的姓名，课程名，成绩，同一班级按分数排序。SELECTa.姓名，a.班级名，c.课程名，b.成绩;FROMSTUDa，SCb，COURSEc;WHEREa.学号=b.学号ANDb.课程号=c.课程号;ORDERBYa.班级名，b.成绩;【例5-22】显示成绩在80至90之间的学号，姓名，课程名和成绩。SELECTa.姓名，c.课程名，b.成绩;FROMSTUDa，SCb，COURSEc;WHEREa.学号=b.学号ANDb.课程号=c.课程号;ANDb.成绩BETWEEN80AND90,.,38,【例5-23】显示计算机系学生的成绩。SELECTa.姓名，a.系别代号，c.课程名，b.成绩;FROMSTUDa，SCb，COURSEc;WHEREa.学号=b.学号ANDb.课程号=c.课程号;ANDa.系别代号=“01”【例5-24】显示各班总人数。SELECT班级名，COUNT（班级名）AS总人数;FROMSTUD;GROUPBY班级名,.,39,【例5-25】显示计算机系的学生及所有男学生。SELECT学号，姓名，系别代号，性别;FROMSTUD;WHERE系别代号=01UNION;SELECT学号，姓名，系别代号，性别;FROMSTUD;WHERE性别=男【例5-26】显示选修了英语或数学科目的学生学号。SELECT学号，课程号FROMSC;WHERE课程号=“01”UNION;SELECT学号，课程号;FROMSC;WHERE课程号=“02”,.,40,【例5-27】显示计算机系男生的名单。SELECT学号，姓名，系别代号，性别;FROMSTUD;WHERE系别代号=“01”AND性别=“男”【例5-28】显示既选修了01课程又选修了02课程学生的名单。SELECT学号;FROMSC;WHERE课程号=“01”AND学号IN;（SELECT学号FROMSCWHERE课程号=“02”）,.,41,【例5-29】显示计算机系非男生的名单。SELECT学号，姓名，系别代号，性别;FROMSTUD;WHERE系别代号=“01”AND性别=“女”【例5-30】显示选修了01课程而没有选修02课程学生的名单。