200 likes | 358 Views
第四章 关系数据库标准语言 SQL 及数据查询. 1 .学习目标 * 掌握 SQL 语言的基本功能 * 使用 SQL 语言产生和修改数据库表 * 使用 SQL 语言操纵数据库数据 2 .学习指南 * 通过档案表、课程表、选课表练习各种 SQL 语句操作 * 完成 P127 习题四 1 ~ 9 题,并将对应的 SQL 操作语句 保留在一个文件中,作为作业提交。 3 .难点重点 * 掌握 SQL 语句的各种基本操作语句的格式和使用方法 , 包括带有各种集合函数、分组排序的查询操作; * 给定关系表和查询要求,会写出正确的 SQL 语句.
E N D
第四章 关系数据库标准语言SQL及数据查询 1.学习目标 * 掌握SQL语言的基本功能 * 使用SQL语言产生和修改数据库表 * 使用SQL语言操纵数据库数据2.学习指南 * 通过档案表、课程表、选课表练习各种SQL语句操作 * 完成P127习题四1~9题,并将对应的SQL操作语句 保留在一个文件中,作为作业提交。3.难点重点 * 掌握SQL语句的各种基本操作语句的格式和使用方法 , 包括带有各种集合函数、分组排序的查询操作; * 给定关系表和查询要求,会写出正确的SQL语句
第二讲 查询结果的分组计算与排序 • SELECT 查询 • 简单计算与聚合函数 • 分组计算 • 空值查询 • 别名 • 量词 ANY SOME ALL • 谓词 [ NOT ] EXISTS • 超连接查询 • 几个特殊选项 • 数据操纵DML:INSERT UPDATE DELETE • 数据定义DDL: CREATE ALTER DROP
SELECT 语法回顾 SELECT [ DISTINCT ] <目标字段表>FROM 表名1 [,表名2] [ , … ][WHERE 表连接条件 [ AND 记录筛选条件 ] ] [ GROUP BY 分组字段表 ][ HAVING 组过滤条件] [ORDER BY 排序字段1[ASC | DESC] [ 排序字段2 … ] ]
4.2.6 简单的计算查询 SQL 不但有查询功能,而且还有计算方式的查询。用于计算查询的函数有: (1)COUNT---计数 (2)SUM---求和 (3)AVG---计算平均值 (4)MAX---求最大值 (5)MIN---求最小值 例4.12 求出学生来自多少个城市。 例4.13 求出来自“北京”的学生的总成绩。 例4.14 检索学时数最多的课程名和学分。 例4.15 检索出除北京之外的其他学生的平均分。
4.2.7 分组与计算查询 使用GROUP BY,短语格式如下: GROUP BY 字段名1 [, 字段名2 , .... ] [ HAVING <条件表达式> ] 例4.20 求出每门课程的平均分。 例4.21 计算选修了两门课以上学生的平均分 例 计算每个学生所得学分(注意不及格者) 先分组,再按组计算. GROUP BY子句一般放在WHERE子句后面,如果没有WHERE子句时,跟在FROM子句之后。
4.2.8 利用空值查询 空值是数据库中的一个重要的概念,它与空字符串和数值 0 等的含义不同,空值就是缺值或还没有确定的值。 注意:查询空值用 IS NULL,而不能用 =NULL,空值是一个不确定的值,所以不能用“=”这样的运算符来进行比较。 例4.22 检索出尚未填写家庭住址的学生信息
4.2.9 别名 在联接查询中,SQL SELECT 允许在FROM短语中为文件名定义别名。 例4.23 检索出选修了会计学的学生姓名的信息。 SELECT 姓名 FROM 档案表,选课表,; 课程表 WHERE 课程名=’会计学’; AND 课程表.课程号=选课表.课程号; AND 选课表.学号=档案表.学号 用别名: SELECT 姓名 FROM 档案表 D,选课表 X , ; 课程表 K WHERE 课程名=’会计学’ ; AND K.课程号=X.课程号 AND X.学号=D.学号 结果: 王倩 刘留
4.2.11 使用量词和谓词的查询 量词和谓词是SELECT语句中使用的词,表示如下: <表达式><比较运算符>[ANY|ALL|SOME] (子查询) [ NOT ] EXISTS (子查询) ANY、ALL和SOME 是量词,ANY和SOME在进行比较运算时,只要子查询中有一行为真,则结果为真;而ALL则要求子查询中所有行都为真时,结果才为真。 [ NOT ] EXISTS是谓词,用来检查在子查询中是否有结果返回。 例4.25 检索出哪些学生没有选修课程。 例4.26 检索出至少选修了一门课程的学生的信息。 例4.28 检索出成绩大于2004150101号学生所有成绩的 学生的学号。
4.2.12 超联接查询 VFP中联接查询使用的短语有以下四个: INNER JOIN (等价于JOIN):普通联接 LEFT JOIN:左联接 RIGHT JOIN:右联接 FULL JOIN:全联接 超联接查询的基本语法格式: SELECT-FROM 表1 JOIN 表2 [ JOIN 表3 … ] ON 联接条件1 [ ON 联接条件2 … ] 例4.29 用普通联接检索出成绩大于70的学生的学号、 姓名,性别和选修的课程号、成绩等信息。 例4.30 找出选修了“数据库应用”或“计算机基础”的学生 的学号、姓名、专业、课程号、课程名和成绩。 例4.31 用左连接检索出所有学生的学号、姓名、专业、 课程号、课程名和成绩等信息。
4.2.14 SELECT 中的几个特殊选项 1、显示部分结果——TOP n [ PERCENT ] … ORDER BY… 例4.32 显示成绩最高的前四条记录。 例4.33 显示成绩最低的30%的记录。
2、将查询的结果存放到永久表(.dbf文件)中 ——INTO DBF | TABLE 表名 例 将成绩最高的前四条记录存放到BFB1表中 SELECT * TOP 4 FROM 选课表 INTO; DBF BFB1 ORDER BY 成绩 DESC 3、将查询结果存放到数组中 ——INTO ARRAY 数组名 查询结果存放到数组(一般是二维数组) 后,在程序中可以很方便的使用。 例 将选课表的信息放到二维数组SZ1 中。 SELECT * FROM 选课表 INTO ARRAY SZ1
4、将查询结果存放到临时文件中 ——INTO CURSOR 临时表 该短语产生的临时表文件是一个只读的.dbf文件,会自动打开成为当前工作区中的表,使用结束将自动删除。 例 将档案表的信息放到临时表 LSB1 中 SELECT * FROM 档案表 INTO ; CURSOR LSB1 临时表常用于存放一些临时结果,比如一些复杂的汇总可能需要分阶段完成,最后再根据几个中间结果来汇总等,这时的阶段性的中间结果即可利用临时表存放,汇总完后这些临时文件会自动删除。
SQL-SELECT的语法回顾 SELECT [ ALL | DISTINCT ] [TOP n [ Percent ] ] * |表.*| [表.]字段1 [AS 别名1] [,[表.]字段2[AS 别名2] [,…]] FROM [数据库!] 表1[ [ INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL ] JOIN [ 数据库!]表2 ...] [ ON 联接条件 … ] [ WHERE 条件1 [AND 条件2 ] … ] [ GROUP BY 字段1 [, 字段2] [, … ][ HAVING BY 过滤条件 ] ][ ORDER BY 字段1 [ ASC | DESC ] [, 字段2 [ … ] ] [, …]
DML 数据操作语言 4.3 SQL的操作功能 包括数据的插入、修改和删除 4.3.1 SQL的插入 VFP支持两种SQL的插入命令,标准的、VFP特色的 SQL标准格式: INSERT INTO 数据表 [ 字段名表 ] ; VALUES ( 数据列表 ) VFP特有格式: INSERT INTO 数据表 FROM ARRAY 数组名 | ; FROM MEMVAR FROM MEMVAR说明根据同名内存变量插入记录,若无,则插入空值。
例 向档案表加入一条新记录。 INSERT INTO 档案表 VALUES; ('2004160101','张亮','男','信管','四川') 例 向选课表加入“张亮”一条没有成绩 的新记录。 INSERT INTO 选课表(学号,课程号); VALUES('2004160101','1001')
4.3.2 修改(更新) SQL的修改命令格式如下: UPDATE 数据表; SET 字段名1 = 表达式1 [, 字段名2 = 表达式2 ... ]; WHERE <条件表达式> 例 给所有学生的成绩增加2分 UPDATE 选课表 SET 成绩=成绩+2 例 给所有选修了“数据库应用”的学生的成绩增加3分 UPDATE 选课表 SET 成绩=成绩+3 ; WHERE 课程号='1003'
5.3.3 删除 SQL中的删除命令格式如下: DELETE FROM 数据表 [WHERE <条件表达式>] 例 删除李洪昆选修的数据库应用的课程。 DELETE FROM 选课表 ; WHERE (学号='2004130101' ; AND 课程号='1003') 注意:在VFP中,SQL的DELETE命令也是逻辑删除记录,若要物理删除记录,需要继续使用PACK命令
DDL 数据定义语言 请自行复习: 教材 P33: CREATE TABLE教材 Pp60:ALTER TABLE 5.3.4 表的删除命令: DROP TABLE 数据表 5.3.5 视图的定义 在VFP中视图是订制的一个虚拟表,它可以是本地表,也可以是远程表。 建视图的SQL命令如下: CREATE VIEW 视图名 AS <select语句> 例 建立一个只含学号、姓名、专业的视图 CREAT VIEW ST1 AS ; SELECT 学号,姓名,专业 FROM 档案表
本讲重点回顾 • SELECT [ DISTINCT ] <目标字段表>FROM 表名1 [,表名2] [ , … ][WHERE 表连接条件[ AND 记录筛选条件 ] ] • [ GROUP BY 分组字段表 ][ HAVING 组过滤条件] [ORDER BY 排序字段1[ASC | DESC] [ 排序字段2 … ] ] • 分组计算 • 空值/量词/谓词/超连接 • 结果的提交: ARRAY/FILE/DBF/CURSOR/PRINTER • DML DDL
作业:1、本章中例题2、本章习题P127 第1—9题 3、本章实验