第8章 视图、查询与SQL

1、1第8章 查询、视图与SQL2本章要点概要1、查询的概念及设计方法、查询的概念及设计方法2、视图的概念及设计方法、视图的概念及设计方法3、结构化查询语言、结构化查询语言SQL的使用的使用n数据定义（数据定义（DDL，Data Definition Languege）n数据操纵（数据操纵（DML，Data Munipunation Languege）n数据查询（数据查询（DCL，Data Control Languege）3第一部分第一部分 查询的创建与设计查询的创建与设计思考：根据已学知识完成下列任务思考：根据已学知识完成下列任务查询查询student与与score中所有中所有“01”系学生的

2、学号、姓系学生的学号、姓名和计算机成绩。名和计算机成绩。4一、一个查询设计的简单实例例例:查询查询student中所有男生的学号、姓名、性别与入中所有男生的学号、姓名、性别与入学成绩学成绩5二、查询设计器各选项卡的使用1、字段选项卡：设置查询结果包含的字段或表达式、字段选项卡：设置查询结果包含的字段或表达式若查询数据不是表中的一个实际字段，而是由表中字段若查询数据不是表中的一个实际字段，而是由表中字段组成的表达式，则通过字段选项卡下的表达式来设置。组成的表达式，则通过字段选项卡下的表达式来设置。例例:查询查询student中男生的姓名，性别与年龄中男生的姓名，性别与年龄若要使用指定字段对查询结

3、果进行排序，该字段必须添若要使用指定字段对查询结果进行排序，该字段必须添加到选定字段中加到选定字段中例例:查询查询student中入学成绩中入学成绩500分以上同学姓名、学号、性分以上同学姓名、学号、性别，并按入学成绩降序排列。别，并按入学成绩降序排列。62、筛选选项卡：设置查询的筛选条件、筛选选项卡：设置查询的筛选条件若有多个筛选条件，则可以通过筛选选项卡的逻辑运算若有多个筛选条件，则可以通过筛选选项卡的逻辑运算符进行连接，但是要注意逻辑运算符的优先级从高到低依符进行连接，但是要注意逻辑运算符的优先级从高到低依次为为次为为not、and、or。例例:查询查询student中所有女生或者男团员

4、的姓名、学号。中所有女生或者男团员的姓名、学号。若筛选的条件不是表中某一个字段而是一个表达式，可若筛选的条件不是表中某一个字段而是一个表达式，可通过筛选字段下拉列表中的表达式设置筛选条件。通过筛选字段下拉列表中的表达式设置筛选条件。例例:查询查询student中所有年龄在中所有年龄在28岁以上学生的姓名与年龄。岁以上学生的姓名与年龄。73、排序依据选项卡：设置查询结果的排序依据、排序依据选项卡：设置查询结果的排序依据设置排序步骤设置排序步骤:选择排序字段选择排序字段单击添加按钮添加到排序单击添加按钮添加到排序条件中条件中选择排序条件并设置顺序。选择排序条件并设置顺序。若对查询结果进行多重排序，

5、则必须首先添加第一排序若对查询结果进行多重排序，则必须首先添加第一排序字段、再添加第二排序字段字段、再添加第二排序字段，顺序不能交换；然后在，顺序不能交换；然后在分别对每个排序字段设置顺序。分别对每个排序字段设置顺序。例例:查询查询student中所有男生的系别号、姓名、入学成绩，并中所有男生的系别号、姓名、入学成绩，并先按照系别号升序排列，若系别号相同再按照入学成绩降先按照系别号升序排列，若系别号相同再按照入学成绩降序排序。序排序。84、分组依据选项卡：设置查询前分组的依据、分组依据选项卡：设置查询前分组的依据用于对表数据先根据分组字段值分组，再分别对每组中指用于对表数据先根据分组字段值分组

6、，再分别对每组中指定的汇总字段进行计算统计。汇总时常用的统计函数有：定的汇总字段进行计算统计。汇总时常用的统计函数有：nsum(汇总字段汇总字段):求该字段的算术和求该字段的算术和navg(汇总字段汇总字段):求该字段的平均值求该字段的平均值ncount(*):求满足条件的记录总条数求满足条件的记录总条数例例1:查询查询student中各系的系别号，入学成绩总和、入学成中各系的系别号，入学成绩总和、入学成绩平均值。绩平均值。例例2:查询查询student中男女生的总人数中男女生的总人数95、杂项选项卡：设置查询结果包含的记录数目、杂项选项卡：设置查询结果包含的记录数目6、查询去向设置：设置查询

7、结果的保存形式、查询去向设置：设置查询结果的保存形式说明：说明：设置查询结果包含记录数目前，必须选定查询结果设置查询结果包含记录数目前，必须选定查询结果的排序依据。的排序依据。说明：说明：若未设置查询去向，查询结果默认为临时表形式，若未设置查询去向，查询结果默认为临时表形式，并自动以浏览窗口形式显示该临时表的数据。并自动以浏览窗口形式显示该临时表的数据。10三、多表查询利用查询来查找多表中的数据时，必须在创建查询利用查询来查找多表中的数据时，必须在创建查询时添加要查询的表（添加次序任意），且设置两表间时添加要查询的表（添加次序任意），且设置两表间的连接方式，一般默认为内部链接的连接方式，一般默

8、认为内部链接inner join。例例:查询查询student与与score表中所有男生的学号、姓名、和计算机表中所有男生的学号、姓名、和计算机成绩，并将结果按照计算机成绩降序排列。成绩，并将结果按照计算机成绩降序排列。11第二部分 视图的创建与设计视图与查询的主要区别视图与查询的主要区别1、视图必须依附于数据库，查询不必依附数据库，但它们都、视图必须依附于数据库，查询不必依附数据库，但它们都可以查询自由表和数据库表。可以查询自由表和数据库表。2、视图不能设置查询去向，默认为虚拟表形式；查询可以设、视图不能设置查询去向，默认为虚拟表形式；查询可以设置查询去向。置查询去向。3、视图有更新功能，可

9、以更新源表数据，查询不能、视图有更新功能，可以更新源表数据，查询不能4、视图主要通过视图设计器设计，用法和查询设计器基本一、视图主要通过视图设计器设计，用法和查询设计器基本一致，仅仅多了一个更新选项卡。致，仅仅多了一个更新选项卡。12视图的更新功能利用视图的更新可以再查询数据时，同步更新源表，利用视图的更新可以再查询数据时，同步更新源表，主要步骤为：主要步骤为：n设置关键字段设置关键字段n设置要修改数据的字段为可修改字段设置要修改数据的字段为可修改字段n勾选勾选”发送发送SQL更新更新”n运行视图，更新结果运行视图，更新结果n再次运行视图，发送更新结果至数据源表再次运行视图，发送更新结果至数据

10、源表例例:利用视图查询利用视图查询student与与score中中01系的同学的学号、姓名和系的同学的学号、姓名和计算机成绩，并利用更新功能更新其中任意一位同学的计算机计算机成绩，并利用更新功能更新其中任意一位同学的计算机成绩。成绩。13第三部分 结构化查询语言SQLSQL(Structured Query Language )的基本特点的基本特点:关系数关系数据库的标准语言，可以对表进行数据查询和程序设据库的标准语言，可以对表进行数据查询和程序设计，几乎所有关系数据库系统都支持计，几乎所有关系数据库系统都支持SQL。SQL的基本功能的基本功能n数据定义语言数据定义语言DDL:生成、修改、删除

11、数据库生成、修改、删除数据库n数据库操纵语言数据库操纵语言DML:操作、运算数据库操作、运算数据库n数据控制语言数据控制语言DCL:数据库的安全防护数据库的安全防护SQL与与VFP的关系的关系:VFP支持支持SQL大部分重要功能，但大部分重要功能，但不支持不支持SQL所有语句。所有语句。14一、一、SQL的数据定义功能的数据定义功能1、创建表、创建表create table 表名表名 (字段名字段名 类型类型(长度长度)）说明：说明：1、若新建表有多个字段，则各字段间以逗号隔开、若新建表有多个字段，则各字段间以逗号隔开2、字段名列表前后必须以括号定界、字段名列表前后必须以括号定界3、字段名与其

12、类型间必须空格、字段名与其类型间必须空格4、字段长度必须以括号定界、字段长度必须以括号定界例例:通过通过SQL建立一张表建立一张表TS，表结构为，表结构为:图书名图书名(c,20)，出版社，出版社(c,20)，单价，单价(n,5,1)，库存量，库存量(i)152、为表增加字段、为表增加字段格式：格式：alter table 表名表名 add 新字段名新字段名 类型类型(宽度宽度)说明：说明：若要为一个表同时添加多个新字段，则必须在命令中对每个添若要为一个表同时添加多个新字段，则必须在命令中对每个添加的字段使用加的字段使用add子句进行定义。子句进行定义。例例:为为TS表添加新字段图书编号表添加

13、新字段图书编号(c,8)，出版日期，出版日期(d)163、修改表中字段、修改表中字段格式格式:alter table 表名表名 alter 字段名字段名 类型类型(宽度宽度)说明：说明：1、若要同时对多个字段进行修改，则必须分别对每个字段使、若要同时对多个字段进行修改，则必须分别对每个字段使用用alter子句进行编辑。子句进行编辑。2、该命令只能修改表字段的类型和宽度，不能修改该字段的、该命令只能修改表字段的类型和宽度，不能修改该字段的名称。名称。例例:修改修改TS表的图书编号为表的图书编号为c(6)，库存量为，库存量为n(3)174、删除表中字段、删除表中字段格式格式: alter tabl

14、e 表名表名 drop 字段名字段名1 drop 字段名字段名2.说明：说明：若要同时删除表中多个指定的字段，则必须对每个要删除的字若要同时删除表中多个指定的字段，则必须对每个要删除的字段使用段使用drop子句进行删除子句进行删除。例例:删除删除TS表中的库存量、出版日期字段。表中的库存量、出版日期字段。185、删除表、删除表格式格式:drop table 表名表名recycle说明：说明：1、删除指定的表文件，一般情况不能恢复。、删除指定的表文件，一般情况不能恢复。2、recycle指定是否将删除的表放置入回收站，若缺省，则默指定是否将删除的表放置入回收站，若缺省，则默认直接删除，不放进回收

15、站。认直接删除，不放进回收站。例例:删除删除TS，并将其放入回收站。，并将其放入回收站。196、重命名表字段、重命名表字段格式格式:alter table 表名表名 rename 字段旧名字段旧名 to 字段新名字段新名7、设置表的字段有效性规则、设置表的字段有效性规则格式格式:alter table 表名表名 alter 字段名字段名 set check 规则规则说明：说明：重命名后，原字段的索引、关系等将无效。重命名后，原字段的索引、关系等将无效。例：例：将将TS表中出版社字段改名为出版单位表中出版社字段改名为出版单位说明：说明：设置规则有效性的表必须是数据库表。设置规则有效性的表必须是数

16、据库表。例：例：设置设置TS的图书编号的有效性规则为前两位为的图书编号的有效性规则为前两位为”00”208、设置字段的默认值、设置字段的默认值格式格式:alter table 表名表名 alter 字段字段 set default 默认值默认值9、为表中字段设置主索引、为表中字段设置主索引格式格式:alter table 表名表名 add primary key 索引字段名索引字段名 tag 索引标识索引标识说明：说明：设置规则有效性的表必须是数据库表。设置规则有效性的表必须是数据库表。例：例：设置设置TS的图书编号默认值为为的图书编号默认值为为”000000”说明：说明：设置主索引的表必须是

17、数据库表。设置主索引的表必须是数据库表。例：例：为为TS的图书编号设置主索引的图书编号设置主索引2110、删除表字段的有效性规则及默认值、删除表字段的有效性规则及默认值格式格式:alter table 表名表名 alter 字段字段 drop check|default说明：说明：1、drop check 用于删除该字段的有效性规则，用于删除该字段的有效性规则，2、drop default用于删除该字段的默认值。用于删除该字段的默认值。例：例：删除删除TS中图书编号字段的有效性规则与单价字段的默认中图书编号字段的有效性规则与单价字段的默认值设置值设置2211、*创建两表的永久关系创建两表的永久

18、关系格式格式: alter table 子表名子表名 add foreign key 索引字段索引字段 tag 索引名索引名 reference 主表名主表名说明：说明：n两表必须是都是数据库表。两表必须是都是数据库表。nadd foreign key 用于为子表建立普通索引。用于为子表建立普通索引。nreference 用于指定主表的名称，主表必须已对共同字段建用于指定主表的名称，主表必须已对共同字段建立主索引。立主索引。n该命令必须同时使用该命令必须同时使用add foreign与与reference。n该命令建立的永久关系为一对多关系。该命令建立的永久关系为一对多关系。23例：例：根据根

19、据student与与department表的系别号建立永久关系，表的系别号建立永久关系，department为子表，为子表， student为子表，且两表均已添加到数据为子表，且两表均已添加到数据库库st1中。中。为主表为主表department的系别号建立主索引的系别号建立主索引alter table department;add primary key 系别号系别号 tag xbh为为student的系别号建立普通索引，并为两表建立的系别号建立普通索引，并为两表建立永久关系永久关系alter table student;add foreign key 系别号系别号 tag xh;refer

20、ence department24二、 SQL的数据更新功能1、插入记录、插入记录作用作用:在表的末尾追加一条指定值的记录。在表的末尾追加一条指定值的记录。格式格式:insert into 表名表名 (字段列表字段列表) values (值列表值列表)说明：说明：1、字段列表中的字段顺序可和表中原顺序不一致、字段列表中的字段顺序可和表中原顺序不一致2、字段列表中的字段顺序必须和值列表中的顺序一致、字段列表中的字段顺序必须和值列表中的顺序一致例：例：为为student表增加在末尾增加一条新记录，内容为：姓名表增加在末尾增加一条新记录，内容为：姓名 王亚楠王亚楠;入学成绩入学成绩 550；性别；性

21、别 女女;学号学号 20020109252、更新记录、更新记录作用作用:修改表中指定字段的值。修改表中指定字段的值。格式格式:update 表名表名 set 字段字段1=值值1 where 条件条件说明：说明：where指定仅对满足条件的记录更新值，若省略指定仅对满足条件的记录更新值，若省略where子子句，将把所有记录中指定字段的数据都更新为指定值。句，将把所有记录中指定字段的数据都更新为指定值。例：例：将将student中所有中所有“01”系的学生入学成绩提高系的学生入学成绩提高10%263、删除记录、删除记录作用作用:对指定记录进行逻辑删除对指定记录进行逻辑删除格式格式:delete f

22、rom 表名表名 where 条件条件说明：说明：1、若无、若无where子句，则表示逻辑删除所有记录；否则仅对满足子句，则表示逻辑删除所有记录；否则仅对满足条件的记录做逻辑删除。条件的记录做逻辑删除。2、若、若set deleted on|off设置为设置为on，则所有命令都将忽略逻辑删，则所有命令都将忽略逻辑删除的记录，设置为除的记录，设置为off，将不忽略逻辑删除的记录。，将不忽略逻辑删除的记录。27三、SQL的数据查询SQL-select子句组成子句组成查询设计器各选项卡查询设计器各选项卡Select 查询字段查询字段From 查询源表查询源表Where 查询条件查询条件Order b

23、y 字段字段Group by 字段字段Top n字段选项卡字段选项卡连接选项卡连接选项卡筛选条件选项卡筛选条件选项卡排序选项卡排序选项卡分组依据选项卡分组依据选项卡杂项选项卡杂项选项卡In /into 去向去向查询去向对话框查询去向对话框281、select 查询字段查询字段说明：说明：将要查找的字段列出于将要查找的字段列出于select后，若有多个字段，则每后，若有多个字段，则每个字段以逗号隔开；若查询表中所有字段，则以个字段以逗号隔开；若查询表中所有字段，则以select *表示。表示。例：例：查询查询student中男生的所有字段信息中男生的所有字段信息说明：说明：若查找数据不是表中实际

24、字段，则可以使用表达式表示若查找数据不是表中实际字段，则可以使用表达式表示该数据并且可使用该数据并且可使用as子句将该表达式数据生成一个虚拟字段显子句将该表达式数据生成一个虚拟字段显示于查询结果中。示于查询结果中。例：例：查询查询student中所有男生的学号、姓名和年龄。中所有男生的学号、姓名和年龄。292、from查询源表查询源表说明：说明：1、from后列出查找数据所在的表名，若是多表查询，则必须后列出查找数据所在的表名，若是多表查询，则必须使用使用 表表1 inner join 表表2 on 连接条件连接条件命令为表间建立联接；或命令为表间建立联接；或者在者在from后列出数据来源的表

25、名，并使用后列出数据来源的表名，并使用where表示两表连接表示两表连接的方式，一般为的方式，一般为表表1.共同字段共同字段=表表2.共同字段。共同字段。2、若使用两表的共同字段，则必须在该字段前加上其中任意一、若使用两表的共同字段，则必须在该字段前加上其中任意一个表的名称。个表的名称。例：例：查询查询student与与score表所有女生学号、姓名和计算机。表所有女生学号、姓名和计算机。303、where 查询条件查询条件说明：说明：where表示查询结果仅包含满足条件的记录，若查询条表示查询结果仅包含满足条件的记录，若查询条件有多个，可以通过逻辑连接词件有多个，可以通过逻辑连接词or或者或

26、者and连接，但是要注意连接，但是要注意逻辑运算符的优先级为逻辑运算符的优先级为notandor，缺省则表示对表中所有，缺省则表示对表中所有数据都进行查询。数据都进行查询。例：例：查询查询student中所有入学成绩中所有入学成绩500以上学生或者男团员的姓以上学生或者男团员的姓名、年龄和团员信息。名、年龄和团员信息。314、group by 分组字段分组字段说明：说明：group by 在查询前将所有记录按照指定字段进行分组。在查询前将所有记录按照指定字段进行分组。group by 通常用于对表中数据进行统计，常用的统计函数如下通常用于对表中数据进行统计，常用的统计函数如下:sum(字段字段

27、):统计指定字段的算术和统计指定字段的算术和avg(字段字段):统计指定字段的平均值统计指定字段的平均值count(*):统计表中满足条件的记录总数统计表中满足条件的记录总数max(字段字段):统计指定字段中的最大值统计指定字段中的最大值min(字段字段):统计指定字段中的最小值统计指定字段中的最小值例：例：统计各系的系别号、系名称、学生人数、入学成绩平均统计各系的系别号、系名称、学生人数、入学成绩平均分和总分、最高分、最低分。分和总分、最高分、最低分。325、order by 排序字段排序字段 DESC/ASCE1、将排序结果按照指定字段进行排序，、将排序结果按照指定字段进行排序，desc、

28、asc分别表示降分别表示降序和升序，缺省默认为升序。序和升序，缺省默认为升序。2、若要多重排序，则依次写出排序的各个字段，并以逗号隔、若要多重排序，则依次写出排序的各个字段，并以逗号隔开，顺序不能交换。开，顺序不能交换。3、order by 后有时通过正整数后有时通过正整数n表示根据查询字段中的第表示根据查询字段中的第n个个字段进行排序。字段进行排序。例：例：查询查询student与与score表中女的学号、姓名、性别、系别号表中女的学号、姓名、性别、系别号,计算机成绩，并将按照系别号降序排列，再按照学号升序排列。计算机成绩，并将按照系别号降序排列，再按照学号升序排列。336、into/in

29、查询去向查询去向ninto table 表名表名:将查询结果保存至指定的表文件将查询结果保存至指定的表文件ninto cursor 表名表名:将查询结果保存至一个临时表文件，该临将查询结果保存至一个临时表文件，该临时表将自动打开，但退出系统后临时表将释放。时表将自动打开，但退出系统后临时表将释放。ninto array 数组名数组名:将查询结果保存至一个指定数组中。将查询结果保存至一个指定数组中。nto file 文本文件名文本文件名:将查询结果保存至一个文本文件中。将查询结果保存至一个文本文件中。n若未指定查询去向，结果默认以浏览窗口显示若未指定查询去向，结果默认以浏览窗口显示347、限制查询结果数目、限制查询结果数目 top n说明说明:显示或保存查询结果的前显示或保存查询结果的前n条记录，条记录，top子句必须和子句必须和order by 子句连用。子句连用。例：例：查询查询student与与score表中计算机成绩前表中计算机成绩前3名学生的学号、姓名学生的学号、姓名、性别、计算机成绩，并将结果保存至临时表名、性别、计算机成绩，并将结果保存至临时表c1中。中。358、嵌套查询、嵌套查询定义定义:使用使用select命令进行查询时，当前命令进行查询时，当前