计算机二级课件.ppt

1、第4章 数据处理与SQL,4.1 查询文件的创建和使用 4.2 结构化查询语言（SQL） 4.3 视图的创建和使用,4.1 查询文件的创建和使用,4.1.1 利用查询设计器创建查询 4.1.2 查询文件的修改 4.1.3 利用查询向导创建查询,三种方法创建查询文件： 使用查询设计器 使用查询向导 直接编写SELECT-SQL语句（4.2介绍）,4.1.1 利用查询设计器创建查询,(1)启动查询设计器 常用方法： 使用项目管理器：打开项目管理器，在“数据”选项卡中选定“查询”后单击“新建” 从“文件”菜单或常用工具栏选择“新建” 使用命令：CREATE QUERY,4.1.1 利用查询设计器创建

2、查询,(2) 添加数据源 查询设计器被启动的同时弹出“添加表或视图”对话框，用于添加查询的数据源。,(3)在查询设计器中建立查询 “字段”选项卡：指定查询输出列 “联接”选项卡：设置表之间的联接关系 “筛选”选项卡：指定记录筛选的条件 “排序依据”选项卡：指定记录排序的依据 “分组依据”选项卡：指定记录分组的依据 “杂项”选项卡：设置查询结果中可否包含重复的记录，以及对查询记录的数目进行限制,4.1.1 利用查询设计器创建查询,4.1.1 利用查询设计器创建查询,(4)保存查询文件（扩展名为QPR） 常用方法：从“文件”菜单或常用工具栏选择“保存” (5)运行查询文件，查看查询结果 常用方法：

3、 “查询，运行查询” 或常用工具栏中的“运行” 使用项目管理器 使用命令：DO QUERY ,4.1.1 利用查询设计器创建查询,例4.1（单表查询） 查询student表中各专业非定向学生的 入学平均分，并按专业的升序显示。 例4.2（多表查询） 根据student表和grade表，查询选修 “02001”课程的所有学生的学号，姓名和 成绩，并按成绩的降序显示。,4.1.2 查询文件的修改,打开查询文件的常用方法 使用项目管理器（若查询文件已建在某一项目中） 从“文件”菜单或常用工具栏选择“打开” 使用命令：MODIFY QUERY ,4.1.2 查询文件的修改,选择查询结果的输出去向 常用

4、方法：“查询，查询去向” 或: “查询设计器”工具栏中的 “查询去向” 七种输出去向：,4.1.3 利用查询向导创建查询,查询向导类型 查询向导：创建标准的单表或多表查询，在浏览窗口中显示查询结果 打开查询向导常用操作： 使用项目管理器 使用“文件”菜单、“工具”菜单或常用工具栏 交叉表向导 （了解） 图形向导 （了解）,结构化查询语言SQL -Structured Query Language） SQL特点： 一体化 高度非过程化 简洁但功能强大 关系数据库系统的国际标准语言,4.2 结构化查询语言（SQL）,4.2.1 数据查询功能及语句 4.2.2 数据定义功能及语句 4.2.3 数据操

5、纵功能及语句,4.2 结构化查询语言（SQL）,SQL的核心是数据查询语句SELECT-SQL。 通过“查询设计器”或“查询向导”生成的查询文件内容就是一条SELECT-SQL语句。 在“查询设计器”环境中查看当前查询文件内 SECTCT-SQL语句的方法： “查询，查看SQL” 或 “查询设计器”工具栏中的 “显示SQL窗口”,4.2.1 数据查询功能及语句,4.2.1 数据查询功能及语句,例4.1的SQL语句,例4.2的SQL语句,4.2.1 数据查询功能及语句,SELECT-SQL语句常用格式： SELECT /* FROM WHERE ORDER BY ASC/DESC GROUP B

6、Y HAVING TO|INTO ,注: 该命令若分行输入,需在每行(结束行除外)行末加续行标记“;”,4.2.1 数据查询功能及语句,SELECT /* 指定查询输出列, “*”表示所有字段 在查询设计器方法中，用“字段”选项卡实现 FROM 指定查询数据所在表 在查询设计器方法中，用“添加表或视图”对话框实现,4.2.1 数据查询功能及语句,WHERE 用于指定记录筛选的条件,无where子句表示无条件查询 在查询设计器方法中，用“筛选”选项卡实现 ORDER BY ASC/DESC 用于指定记录排序的依据,无order by子句表示无排序要求 在查询设计器方法中，用“排序依据”选项卡实现

7、,4.2.1 数据查询功能及语句,GROUP BY HAVING 指定记录分组的依据,无group by子句表示无分组要求 在查询设计器方法中，用“分组依据”选项卡实现 TO|INTO 指定查询结果的输出去向, 默认输出为浏览窗口-名为“查询”的只读临时表 在查询设计器方法中,用“查询去向”对话框实现,简单查询 SELECT |* FROM 1)查询全部信息：用“*”表示全部字段。 例4.4 查询STUDENT表的全部信息。 SELECT * FROM STUDENT 2)查询部分字段内容：逐一列出所需字段并以“,”分隔。 例4.5 查询STUDENT表中所有学生的学号、姓名和专业。 SELE

8、CT 学号,姓名,专业 FROM STUDENT,4.2.1 数据查询功能及语句,3)查询结果不包含重复记录： 在SELECT后使用DISTINCT选项。 例4.6 根据GRADE表查询课程号（要求课程号不重复）。 SELECT DISTINCT 课程号 FROM GRADE,4.2.1 数据查询功能及语句,4)查询输出常量、表达式的值，可以为常量或表达式取一个有含义的名称作为查询结果的列标题。,4.2.1 数据查询功能及语句,例4.7 查询显示STUDENT表中所有学生的学校、学号、姓名和年龄。假设学校为“福州大学”。 SELECT “福州大学” AS 学校,学号,姓名,; YEAR(DAT

9、E()-YEAR(出生日期) AS 年龄; FROM STUDENT 或缺省AS： SELECT “福州大学” 学校,学号,姓名,; YEAR(DATE()-YEAR(出生日期) 年龄; FROM STUDENT,4.2.1 数据查询功能及语句,4.2.1 数据查询功能及语句,不指定列标题的查询结果,指定列标题的查询结果,5)简单统计查询：输出列使用了统计函数,4.2.1 数据查询功能及语句,例4.8 查询显示STUDENT表中所有学生的平均入学总分、最高入学总分和最低入学总分。 SELECT AVG(入学总分) AS 平均入学总分,; MAX(入学总分) AS 最高入学总分,; MIN(入学

10、总分) AS 最低入学总分 ; FROM STUDENT,4.2.1 数据查询功能及语句,条件查询 SELECT FROM WHERE 条件的表达形式有： NOT LIKE NOT BETWEEN AND IS NOT NULL NOT IN (/),4.2.1 数据查询功能及语句,1)关系运算符的使用 例4.9 查询STUDENT表中入学总分大于等于570分的学生学号、姓名和入学总分。 SELECT 学号,姓名,入学总分 ; FROM STUDENT WHERE 入学总分=570 例4.10 查询STUDENT表中男生的学号和姓名。 SELECT 学号,姓名 ; FROM student W

11、HERE 性别=男,4.2.1 数据查询功能及语句,2)逻辑运算符的使用 例4.11 查询STUDENT表中所有“工商管理”专业男生的学号和姓名。 SELECT 学号,姓名 FROM STUDENT ; WHERE 专业=”工商管理” AND 性别=”男”,4.2.1 数据查询功能及语句,2)逻辑运算符的使用 例4.12 查询STUDENT表中所有“工商管理”专业和“社会学”专业男生的学号、姓名及专业。 SELECT 学号,姓名,专业 FROM STUDENT ; WHERE (专业=”工商管理” OR 专业=”社会学”); AND 性别=男,4.2.1 数据查询功能及语句,3) LIKE运算

12、符的使用 例4.13 查询STUDENT表中“01”学院学生（即学号以“01”打头）的信息。 SELECT * FROM STUDENT WHERE 学号 LIKE 01%,4.2.1 数据查询功能及语句,通配式中的%表示0到任意个任意字符,3) LIKE运算符的使用 例4.14 查询STUDENT表中姓“陈”的05级学生（即学号的第3-4为为“05”）的信息。 SELECT * FROM STUDENT ; WHERE 姓名 LIKE 陈% AND 学号 LIKE _05%,4.2.1 数据查询功能及语句,_表示单个任意字符,4) BETWEEN运算符的使用 例4.15 查STUDENT表中

13、86年上半年出生的学生人数。 SELECT COUNT(*) FROM STUDENT; WHERE 出生日期 BETWEEN; 1986/01/01 AND 1986/6/30,4.2.1 数据查询功能及语句,例4.16 查询STUDENT表中入学总分低于550分或高于585分的学生信息。 SELECT * FROM STUDENT; WHERE 入学总分 NOT BETWEEN 550 AND 585 或用BETWEEN()函数： SELECT * FROM STUDENT; WHERE NOT BETWEEN(入学总分,550,585),4.2.1 数据查询功能及语句,5) IN运算符的

14、使用 例4.17 查询STUDENT表中名为李和、陈红或许文强的学生的信息。 SELECT * FROM STUDENT ; WHERE 姓名 IN(李和,陈红,许文强),4.2.1 数据查询功能及语句,6) IS NULL运算符的使用 例4.18 查询GRADE表中已考试课程的课程号。 SELECT DISTINCT 课程号 FROM GRADE ; WHERE 成绩 IS NOT NULL,4.2.1 数据查询功能及语句,对查询结果排序 SELECT TOP n PERCENT FROM WHERE ORDER BY ASC|DESC 排序依据有以下几种形式： ,4.2.1 数据查询功能及

15、语句,1)排序输出查询到的所有记录 例4.19 查询STUDENT表中的学生信息，并按入学总分降序、入学总分相同者按出生日期升序显示。 SELECT * FROM STUDENT; ORDER BY 入学总分 DESC,出生日期 ASC 例4.20 查询STUDENT表中所有学生的学号、姓名和年龄，并按年龄由小到大的顺序排列。 SELECT 学号,姓名,; YEAR(DATE()-YEAR(出生日期) AS 年龄 ; FROM STUDENT ORDER BY 年龄,4.2.1 数据查询功能及语句,升序ASC可缺省,表达式不能直接写在ORDER BY子句中,2)用TOP选项限制输出的记录数 例

16、4.21 查询STUDENT表中的入学总分排在前3位的学生信息。 SELECT TOP 3 * FROM STUDENT ; ORDER BY 入学总分 DESC 若将TOP 3 改为TOP 3 PERCENT，即： SELECT TOP 3 PERCENT * FROM STUDENT ; ORDER BY 入学总分 DESC 则表示查询STUDENT表中的入学总分排在前3% 的学生信息。,4.2.1 数据查询功能及语句,分组统计查询 SELECT FROM WHERE GROUP BY HAVING 分组依据有以下几种形式： ,4.2.1 数据查询功能及语句,例4.22 根据STUDENT

17、表统计各专业学生的人数和平均入学总分并输出。 SELECT 专业, COUNT(*) AS 学生数,; AVG(入学总分) AS 平均入学总分; FROM STUDENT GROUP BY 专业,4.2.1 数据查询功能及语句,例4.23 统计各专业学生的人数和平均入学总分，仅要求输出平均入学总分大于等于560分的信息。 SELECT 专业,COUNT(*) AS 学生数,; AVG(入学总分) AS 平均入学总分 FROM STUDENT; GROUP BY 专业 HAVING 平均入学总分=560,4.2.1 数据查询功能及语句,HAVING子句指定的是分组后哪些记录能作为查询的最终结果输

18、出,例4.24 统计各专业中入学总分大于等于560分的学生的人数和平均入学总分并输出。 SELECT 专业,COUNT(*) AS 学生数,; AVG(入学总分) AS 平均入学总分 FROM STUDENT; GROUP BY 专业 WHERE 入学总分=560,4.2.1 数据查询功能及语句,WHERE子句指定哪些记录能参加分组,联接查询 (四种类型)： 内部联接：连接结果由左表和右表中满足联接条件的记录组成，是最常用的联接类型 左联接： 包含左表全部记录及右表中满足联接条件的记录 右联接： 包含右表全部记录及左表中满足联接条件的记录 完全联接：包含两个表的全部记录,4.2.1 数据查询功

19、能及语句,(1)两表内部联接 实现1）SELECT FROM INNER JOIN ON 实现2）SELECT FROM , WHERE 例: SELECT * FROM STUDENT INNER JOIN GRADE; ON STUDENT.学号=GRADE.学号 或 SELECT * FROM STUDENT,GRADE ; WHERE STUDENT.学号=GRADE.学号,4.2.1 数据查询功能及语句,例4.25 根据STUDENT表和GRADE表，查询选修“02001”课程的所有学生的学号、姓名和成绩，并按成绩降序显示。 SELECT S.学号,姓名,成绩; FROM STUDE

20、NT S INNER JOIN GRADE G; ON S.学号=G.学号; WHERE 课程号=02001; ORDER BY 成绩 DESC,4.2.1 数据查询功能及语句,可指定S、G分别为表STUDENT、GRADE的别名,注意：学号不唯一，要加前缀(表名或表别名)；姓名、成绩、课程号唯一，则可缺省前缀。,(2)两表左联接、右联接或完全联接查询 SELECT FROM IEFT|RIGHT|FULL JOIN ON 例4.27 根据COLLEGE表和STUDENT表，查询所有学院的学生信息，包括学院名称、学号和姓名。如果某个学院无学生，也要显示该学院的名称。 SELECT 学院名称,学

21、号,姓名; FROM COLLEGE LEFT JOIN STUDENT ; ON 学院代码=LEFT(学号,2),4.2.1 数据查询功能及语句,(3)多表联接查询 例4.28 查询所有学生各门课程的成绩信息，包括学号、姓名、课程号、课程名、学分和成绩，并按学号从小到大顺序排列，学号相同的记录按课程号从小到大顺序排列。,4.2.1 数据查询功能及语句,(3)多表联接查询 SELECT STUDENT.学号,姓名,GRADE.课程号,； 课程名,学分,成绩; FROM STUDENT,GRADE,COURSE; WHERE STUDENT.学号=GRADE.学号 AND ； GRADE.课程号

22、= COURSE.课程号; ORDER BY STUDENT.学号,COURSE.课程号,4.2.1 数据查询功能及语句,例4.28 用INNER JOIN实现： SELECT STUDENT.学号,姓名,GRADE.课程号,; 课程名,学分,成绩; FROM STUDENT; INNER JOIN GRADE; INNER JOIN COURSE; ON GRADE.课程号= COURSE.课程号; ON STUDENT.学号=GRADE.学号; ORDER BY STUDENT.学号,COURSE.课程号,4.2.1 数据查询功能及语句,嵌套查询： 在一个SELECT-SQL语句的WHER

23、E子句中嵌入另一个SELECT-SQL语句。外层查询称为父查询，内层查询称为子查询。 常用格式： SELECT FROM WHERE IN ( SELECT FROM WHERE ) 功能：首先执行子查询，其结果构成一个集合，然后从父表中查询指定字段值属于该集合的记录。,4.2.1 数据查询功能及语句,例4.29 根据STUDENT表和GRADE表，查询有不及格成绩的学生学号和姓名。 SELECT 学号,姓名 FROM STUDENT; WHERE 学号 IN ; (SELECT 学号 FROM GRADE WHERE 成绩60) 可用联接查询实现： SELECT DISTINCT STUDE

24、NT.学号,姓名; FROM STUDENT,GRADE; WHERE STUDENT.学号=GRADE.学号 AND 成绩60,4.2.1 数据查询功能及语句,查询输出去向 SELECT-SQL语句默认输出到浏览窗口,可通过INTO或TO子句改变输出去向。 INTO ARRAY INTO CURSOR INTO TABLE TO FILE ADDITIVE TO PRINTER TO SCREEN,4.2.1 数据查询功能及语句,(1)INTO ARRAY 将查询结果存放到指定的二维数组中，每行存放一条记录，每列对应于查询结果的一列。,例4.30 从STUDENT表中查出所有专业名称（不重复

25、），并将查询结果保存在数组ZY中。 SELECT DISTINCT 专业 FROM STUDENT ; INTO ARRAY ZY 注：若查出4个专业名称，则分别存放在数组元素ZY(1,1)、ZY(1,2)、ZY(1,3)、ZY(1,4)中，也可看作一维数组元素ZY(1)、ZY(2)、ZY(3)、ZY(4) 。,4.2.1 数据查询功能及语句,(2)INTO TABLE 将查询结果保存到指定的自由表文件（.DBF）中。自动作为当前表打开着（但不打开浏览窗口）,例4.31 对STUDENT表按专业统计平均入学总分,并将统计结果保存在ZYHZ表中。 SELECT 专业, AVG(入学总分); FR

26、OM STUDENT; GROUP BY 专业; INTO TABLE ZYHZ,4.2.1 数据查询功能及语句,(3)INTO CURSOR 将查询结果保存到指定的只读临时表中, 自动作为当前表打开在内存（但不打开浏览窗口），一旦关闭该表则自动删除。,4.2.1 数据查询功能及语句,4.2.1 数据查询功能及语句,例4.32 根据STUDENT、COURSE和GRADE表，查询学号为“0305001”的学生的姓名、所学课程名及成绩，并将查询结果保存在临时表TEMP中。 SELECT 姓名,课程名,成绩; FROM STUDENT,COURSE,GRADE ; WHERE STUDENT.学号

27、=GRADE.学号 ; AND COURSE.课程号=GRADE.课程号 ; AND STUDENT.学号=0305001 ; INTO CURSOR TEMP,(4)TO FILE ADDITIVE 将查询结果保存到指定的文本文件（.TXT）中。ADDITIVE选项表示追加式存入。 (5)TO PRINTER 将查询结果直接输出到打印机。 (6)TO SCREEN 将查询结果直接显示在VFP的系统主窗口中。,4.2.1 数据查询功能及语句,1.创建表结构语句: CREATE TABLE-SQL (了解),CREATE TABLE FREE ( (,) , (,) ,),例如: CREATE

28、TABLE STUDENT_NEW ; (学号 C(7),姓名 C(8),出生日期 D,; 定向否 L,入学总分 N(6,1),简历 M),4.2.2 数据定义功能及语句,4.2.2 数据定义功能及语句,4.2.2 数据定义功能及语句,例4.34 在“教学管理”数据库中创建TEST表，表结构如下：学号(字符型,7)、课程号(字符型,5)、考试时间(日期型,可为空)、考试地点(字符型,10,可为空)，并以学号+课程号为索引表达式建立名为SC的候选索引。 OPEN DATABASE 教学管理 CREATE TABLE TEST; (学号 C(7), 课程号 C(5),考试时间 D NULL,; 考

29、试地点 C(10) NULL,; UNIQUE 学号+课程号 TAG SC),4.2.2 数据定义功能及语句,2.修改表结构语句: ALTER TABLE-SQL (了解)(1) 添加新字段 ALTER TABLE ADD (,) (2) 删除或修改字段名 ALTER TABLE DROP RENAME TO ,4.2.2 数据定义功能及语句,(3)重设已有字段的类型、宽度和部分字段级约束 ALTER TABLE ALTER (,) PRIMARY KEY UNIQUE NULL NOT NULL,4.2.2 数据定义功能及语句,(4)重设或删除已有字段的有效性规则和默认值 ALTER TAB

30、LE ALTER SET DEFAULT DROP DEFAULT SET CHECK ERROR DROP CHECK NULL NOT NULL,4.2.2 数据定义功能及语句,(5)重设或删除表级约束 ALTER TABLE SET CHECK ERROR DROP CHECK ADD PRIMARY KEY TAG DROP PRIMARY KEY ADD UNIQUE TAG DROP UNIQUE TAG ,4.2.2 数据定义功能及语句,3.删除表语句: DROP TABLE-SQL,删除表结构和数据，不可恢复 DROP TABLE 例4.38 删除“教学管理”数据库中的TEST

31、表。 OPEN DATABASE 教学管理 DROP TABLE TEST,4.2.2 数据定义功能及语句,1.记录插入语句：INSERT-SQL INSERT INTO (,) VALUES (, ),4.2.3 数据操纵功能及语句,若按表中字段排列顺序依次给新记录各个字段赋值，可缺省字段名表,4.2.3 数据操纵功能及语句,例4.39 向TEACHER表追加一条新记录：教师号“T66101”，姓名“王小明”，职称“讲师”，学院代码“03”。 INSERT INTO TEACHER(教师号,姓名,职称,学院代码); VALUES(T66101,王小明,讲师,03) 或: INSERT INT

32、O TEACHER ; VALUES(T66101,王小明,讲师,03),2. 记录逻辑删除语句: DELETE-SQL DELETE FROM WHERE 例4.40 将TEACHER表中03学院的教师记录逻辑删除。 DELETE FROM TEACHER WHERE 学院代码=03,4.2.3 数据操纵功能及语句,3. 记录更新语句：UPDATE-SQL UPDATE SET = ,=, WHERE 例4.41 将TEACHER表中张明的职称改为“教授”。 UPDATE TEACHER SET 职称=教授; WHERE 姓名=张明,4.2.3 数据操纵功能及语句,视图是从数据表或其他视图中检