第5章 关系数据库标准语言SQL 主讲：张丽芳.

Presentation on theme: "第5章 关系数据库标准语言SQL 主讲：张丽芳."— Presentation transcript:

1 第5章 关系数据库标准语言SQL 主讲：张丽芳

2 新课导入： 如何在一个表或多个表中查询所有符合条件的记录？---查询 如何将用户所需的数据集中在一块，且对其修改也可以达到修改原来数据的目的？ ---视图 问题:在一个表或多个表中查询所有符合条件的记录可不可以用命令来实现? 示例1：查询学生学号及其所选课程的门数 在查询设计器中设计该查询,显示SQL窗口：显示该查询所对应的SQL命令 sql命令：SELECT 学号，COUNT(*) AS 选课门数; FROM 选课; GROUP BY 学号

3 练习：在以下表中完成查询并查看相应的SQL命令：
学生（学号，姓名，性别，出生年月，三好生，特长，照片，入校总分） 课程 （课程号，课程名称） 教师 （教师号，姓名，性别，职称，工资，政府津贴） 选课 （学号，课程号，成绩） 授课 （教师号，课程号） 数据的分组查询： 1：查询学生学号及其所选课程的门数 2：查询教师号及其所任课程的门数 3：查询课程平均成绩在75分以上的学生的学号及其所选课程的门数 排序查询: 4：查询选修了课程C140的学生学号和成绩,并按成绩降序排列 5：查询选修了课程C130,C140,C150的学生学号,课程号和成绩,并按课程号升序排列, 课程相同在按成绩降序排列 连接查询: 6：查询学生学号,姓名及其所选课程的成绩 7：查询选课成绩在80分以上的学生学号,姓名及其所选课程的成绩

4 教学目标：熟练运用用SQL语言的查询和操作功能，体会查询的实际运用 。 教学内容： 一、SQL语言简介 ；
五、视 图。 重点及难点：SQL语言的查询和操作功能

5 一、SQL语言简介 1、SQL是结构化查询语言(Structured Query Language的缩写) 2、特点： 是一种一体化语言
是一种高度非过程化语言 非常简洁 可以以命令方式交互使用（也可以作为程序代码） 3、SQL的功能: 数据查询 数据定义 数据操纵 数据控制(此功能VFP没有)

6 二、 SQL的数据查询 (一)命令格式 SELECT 目标字段名表； FROM 表名 [关联语句]; WHERE 筛选条件；
ORDER BY 排序依据； GROUP BY 分组依据 ； HAVING 分组限制条件； INTO /TO 查询去向 [NOCONSOLE][PLAIN][NOWAIT] 含义讲析： 1、目标字段名表选项 （1）符合条件的记录数： ◇ ALL 所有符合条件的记录（默认） ◇DISTINCT 符合条件者重复的只出现一次 ◇ TOP n 符合条件者的前n 个记录 [必须有ORDER BY子句] ◇ TOP n PERCENT符合条件者前n%个记录[必须有ORDER BY子句]

7 二、 SQL的数据查询 含义讲析： 1、目标字段名表选项 （1）符合条件的记录数： ALL 所有符合条件的记录（默认）
DISTINCT 符合条件者重复的只出现一次 TOP n 符合条件者的前n 个记录 [必须有ORDER BY子句] TOP n PERCENT符合条件者前n%个记录[必须有ORDER BY子句] 例1: 若将某数据库中的“学生”表按成绩降序显示前2名的学生信息，应使用SQL语句______。 A.SELECT * TOP 2 FROM 学生 DESC B.SELECT * TOP 2 PERCENT FROM 学生 ORDER BY 成绩DESC C.SELECT * TOP 2 FROM 学生 ORDER BY 成绩DESC D.SELECT * TOP 2 PERCENT FROM 学生DESC 例2:查询有选课记录的学生的学号. SELECT DISTINCT 选课.学号; FROM 选课管理!选课

8 二、 SQL的数据查询 含义讲析： 1、目标字段名表选项 （2） 所需字段 n * 表示所有字段
n       表名.字段名 [AS 标题],表名.字段名 [AS 标题] n       函数(字段名),支持函数为:count,sum,avg,max,min （称为计算查询） 例3：在“学生”数据表中查询计算机专业所有男学生的姓名，应使用SQL语句 。 A.SELECT 姓名 FROM 学生 B.SELECT 姓名 FROM 学生 WHERE 专业=”计算机”.OR.性别=”男” C.SELECT * FROM 学生 WHERE专业=”计算机”.OR.性别=”男” D.SELECT 姓名 FROM 学生 WHERE 专业=”计算机”.AND．性别=”男” 例4：查询学生学号及其所选课程的门数 命令：SELECT 学号，COUNT(*) AS 选课门数; FROM 选课; GROUP BY 学号 指定字段或要计算的结果 COUNT：对一列中的值计算个数；COUNT（*）：计算表中记录的个数； SUM 、AVG ：求某一列值的总和和平均值（此列必须是数值型） MAX、 MIN ：求某一列值的最大值、最小值（可以为字符型、数值、日期）

9 二、 SQL的数据查询 2、联接查询 联接是关系的横向结合。 A）关系联接的类别 n 等值联接:按字段值对应相等为条件进行的联接
B）联接查询的实现 ①等值联接:将联接条件写入WHERE子句中，from后写要联接的两个表名  例5：查询学生学号,姓名及其所选课程的成绩 命令A：SELECT *; FROM 学生,选课; WHERE 学生.学号=选课.学号   ②自然联接:将联接条件写入WHERE子句中，指定字段，from后写要联接的两个表名 例6：查询学生学号,姓名及其所选课程的成绩 命令A：SELECT 学生.学号，姓名,成绩; WHERE 学生.学号=选课.学号

10 二、 SQL的数据查询 2、联接查询 联接是关系的横向结合。 C）超联接查询 超联接的四种形式:
左联接:结果包含第一个表中所有的记录,用LEFT JOIN表示 右联接:结果包含第二个表中所有的记录,用RIGHT JOIN表示 内部联接:取两个表中的公共记录,用INNER JOIN表示 完全联接:取两个表中的记录本的并集,用FULL JOIN表示 超联接语句: SELECT …. FROM 表名1 联接形式 表名2 on 联接条件 示例7：查询学生学号,姓名及其所选课程的成绩 命令：SELECT 学生.学号，姓名,成绩; FROM 学生; INNER JOIN 选课 ON 学生.学号=选课.学号  // 请大家自己改写左连接,右连接和完全连接 练习： 只有满足连接条件的记录才包含在查询结果中，这种连接为______。 A.左连接 B.右连接 C.内部连接 D.完全连接 命令A：SELECT 学生.学号，姓名,成绩; FROM 学生,选课; WHERE 学生.学号=选课.学号

11 二、 SQL的数据查询 3、筛选条件选项 由where引导 条件可以是: v 关系运算 v 逻辑值 v 字段名 BETWEEN …AND …
v      字段名 LIKE 通配符 (%代表一串字符，_表示一个字符) v      字段名 IS NULL 例8：查询选课成绩在80分以上的学生学号,姓名及其所选课程的成绩 命令：SELECT 学生.学号，姓名,成绩; FROM 学生,选课; WHERE 学生.学号=选课.学号 AND 成绩>=80 练习1： 在某数据库中的“学生”表中，要查询成绩不在80~100之间的记录，应使用命令______。 A.SELECT * FROM 学生WHERE 成绩 BETWEEN 80 AND 100 B.SELECT * FROM 学生 WHERE 成绩BETWEEN NOT 80 AND 100 C.SELECT * FROM 学生 WHERE 成绩BETWEEN 80—100 D.SELECT * FROM学生WHERE 成绩NOT BETWEEN 80 AND 100

12 二、 SQL的数据查询 3、筛选条件选项 由where引导 条件可以是: v 关系运算 v 逻辑值 v 字段名 BETWEEN …AND …
v      字段名 LIKE 通配符 (%代表一串字符，_表示一个字符) v      字段名 IS NULL 练习2. 假设学生表已经打开，要查询学号（C型）为“0001和“0002”的学生成绩及名次，应使用命令______。 A.SEKECT成绩，名次 FROM 学生 WHERE 学号IN（0001，0002） B.SELECT成绩，名次 FROM 学生 WHERE 学号AT（“0001”，“0002”） C.SELECT成绩，名次 FROM 学生 WHERE 学号IN（“0001”，“0002”） D.SELECT成绩，名次 FROM 学生 WHERE 学号LIKE（0001，0002） 练习3. 在“学生”数据表中查询计算机专业所有男学生的姓名，应使用SQL语句 。 A.SELECT 姓名FROM 学生 B.SELECT 姓名FROM 学生 WHERE 专业=”计算机”.OR.性别=”男” C.SELECT * FROM 学生 WHERE专业=”计算机”.OR.性别=”男” D.SELECT 姓名FROM 学生 WHERE 专业=”计算机”.AND．性别=”男”

13 二、 SQL的数据查询 4、排序依据选项 由order by 引导 ORDER BY 字段表:按指定的字段排序,可用多个字段进行多重排序
例9：查询选修了课程C140的学生学号和成绩,并按成绩降序排列 命令：SELECT 学号,成绩; FROM 选课; WHERE 课程号="C140"; ORDER BY 成绩 DESC 例10：查询选修了课程C130,C140,C150的学生学号,课程号和成绩,并按课程号升序排列, 课程相同在按成绩降序排列 命令：SELECT 学号,课程号,成绩; WHERE 课程号 IN ("C130","C140","C150"); ORDER BY 课程号,成绩 DESC

14 二、 SQL的数据查询 5、分组条件选项 由group by 引导
HAVING 条件:只显示符合条件的组 使用分组统计时,一般要用GROUP BY 子句 提醒注意：若只有HAVING语句而无GROUP BY语句，则HAVING语句相当于WHERE语句。 例11：查询选课两门以上的学生学号及其所选课程的门数 命令：SELECT 学号，COUNT(*) AS 选课门数; FROM 选课; GROUP BY 学号; HAVING COUNT(*)>2 例12：查询课程平均成绩在75分以上的学生的学号及其所选课程的门数 HAVING AVG(成绩)>=75

15 二、 SQL的数据查询 5、分组条件选项 由group by 引导
HAVING 条件:只显示符合条件的组 例13：在课程C120,C140,C150中查询课程平均成绩在75分以上的学生的学号,课程平均成绩极其选课门数 命令：SELECT 学号，AVG(成绩) AS 平均成绩,COUNT(*) AS 选课门数; FROM 选课; WHERE 课程号 IN("C120","C140","C150"); GROUP BY 学号; HAVING AVG(成绩)>=75  例14：查询选课两门以上而且各门课程均及格的学生学号及其所选课程的门数 命令：SELECT 学号，COUNT(*) AS 选课门数; WHERE 成绩>=60; HAVING COUNT(*)>2

16 二、 SQL的数据查询 6、查询去向选项 INTO ARRAY 数组名——送数组 INTO CURSOR 临时表名——送临时表
INTO TABLE 表名——送表 TO FILE 文件名 [ADDITIVE]——送文本文件 TO PRINT ——送打印机 TO SCREEN——送屏幕 例15：查询学生学号,姓名,性别并将结果输出到数据表DATAFORM中 命令：SELECT 学号，姓名,性别; FROM 学生; INTO TABLE DATAFORM 例16：查询学生学号,姓名,性别并将结果输出到临时表DATAFORM中 INTO CURSOR DATAFORM

17 二、 SQL的数据查询 6、查询去向选项 例17：查询学生学号,姓名,性别并将结果输出到文本文件TXTFILE中
命令：SELECT 学号，姓名,性别; FROM 学生; TO FILE TXTFILE 例18：查询学生学号,姓名,性别并将结果输出到打印机中打印 TO PRINT  例19：查询学生学号,姓名,性别并将结果直接输出到屏幕窗口上 TO SCREEN

18 二、 SQL的数据查询 6、查询去向选项 练习1： 假设学生表已经打开，将查询到的学生信息全部放到数组SZ中，应使用语句______。
A.SELECT * FROM 学生INTO CURSOR SZ B.SELECT * FROM 学生INTO ARRAY SZ C.SELECT * FROM 学生 INTO TABLE SZ D.SELECT * FROM 学生 TO FILE SZ ADDITIVE 练习2： 如果要将某数据库中的“学生”表中查询的成绩信息存储到文本文件的尾部，应使用命令______。 A.SELECT * FROM 学生 TO tmp ORDER 成绩 B.SELECT * FROM 学生 TO tmp ORDER BY 成绩 C.SELECT * FROM 学生 INTO tmp ORDER BY成绩 D.SELECT * FROM 学生 TO tmp ORDER BY 成绩 ADDITIVE

19 二、 SQL的数据查询 7、其他子句 NOCONSOLE：关闭对话模式。 PLAIN：用于在显示查询结果时忽略列标题。
NOWAIT：表示程序不等待浏览窗口的关闭。在查询结果被导向浏览窗口时，程序继续往下运行SELECT语句的下一行。 UNION子句：用于连接多个SELECT语句的查询结果

20 二、 SQL的数据查询 （二）嵌套查询 分析特点：在SQL语句内还有SQL语句的，称为嵌套查询。 书写要求：内层查询要用括号括起来。
查询设计器一次不能解决的问题： 例1：查询和周密职称相同的教师的姓名,性别和职称 命令：SELECT 姓名,性别,职称; FROM 教师 ; WHERE 职称=(; SELECT 职称; FROM 教师; WHERE 姓名="周密"; ) 练习： 假设学生表已经打开，若用SQL命令检索所有比“郭佳”年龄大的同学的记录，应使用命令_____。 A.SELECT * FROM 学生 WHERE 年龄>”郭佳”) B.SELECT * FROM 学生 WHERE 姓名=”郭佳” C.SELECT * FROM 学生 WHERE>(SELECT年龄WHERE 姓名=”郭佳”) D.SELECT * FROM 学生 WHERE 年龄>(SELECT年龄FROM 学生WHERE 姓名=”郭佳”)

21 二、 SQL的数据查询 （三）量词与谓词查询 o 在其中：IN （运算符） o 不在其中：NOT IN （运算符）
o      之一：SOME或ANY （量词） o      全部：ALL （量词） o      存在于：EXIST （谓词） o      不存在于：NOT EXIST （谓词） 例1：查询讲授课程号为C140的教师的姓名,性别和职称 命令：SELECT 姓名,性别,职称; FROM 教师 ; WHERE 教师号=ANY(; SELECT 教师号; FROM 授课; WHERE 课程号="C140"; )

22 二、 SQL的数据查询 （三）量词与谓词查询 o 在其中：IN （运算符） o 不在其中：NOT IN （运算符）
o      之一：SOME或ANY （量词） o      全部：ALL （量词） o      存在于：EXIST （谓词） o      不存在于：NOT EXIST （谓词） 例2：查询讲授课程号为C140的教师的姓名,性别和职称 命令：SELECT 姓名,性别,职称; FROM 教师 ; WHERE 教师号=IN(; SELECT 教师号; FROM 授课; WHERE 课程号="C140"; );

23 二、 SQL的数据查询 （三）量词与谓词查询 o 例3：查询比所有男生入校总分高的女生的姓名和入校总分 命令：SELECT 姓名,入校总分;
FROM 学生 ; WHERE 性别="女" AND 入校总分>ALL(; SELECT 入校总分; FROM 学生; WHERE 性别="男"; ) 例4：查询讲授课程号为C140的教师的姓名,性别和职称 命令：SELECT 姓名,性别,职称; FROM 教师 ; WHERE EXISTS(; SELECT *; FROM 授课; WHERE 教师.教师号=授课.教师号 AND 课程号="C140";

24 二、 SQL的数据查询 （四）合并查询 当查询结果的字段相同时,可使用UNION进行结果合并
练习： 在SQL命令中，支持集合的并运算符是MERGE。 A.对 B.错

25 三、 SQL的数据定义 1、定义功能包括的内容 1）表结构的定义 2）表结构的修改 3）表的删除 4）视图的定义

26 三、 SQL的数据定义 2、表结构的定义 例1： CREATE DATABASE 成绩管理1 CREATE TABLE 表名 [FREE]；
(字段名 类型(宽度,小数位) [NULL] ; [CHECK 检查条件][ERROR 出错表达式]; [DEFAULT 默认值] [PRIMARY KEY /UNIQUE]; FOREIGN KEY 字段名 TAG 索引名 REFERENCES 另表名… ; 字段名2…… ) 例1： CREATE DATABASE 成绩管理1 CREATE TABLE 学生1 ; (学号 C(9) PRIMARY KEY,姓名 C(8),出生日期 D,入学成绩 N(8,2) ) CREATE TABLE 成绩1 ; (学号C(9) PRIMARY KEY，语文N(6,1)，数学N(6,1)，英语N(6,1) ) 练习1： SQL语句建立表时将属性定义为主关键字，应使用短语______。 A.CHECK B.PRIMARY KEY C.ORDER D.UNIQUE 练习2： 建立表结构的SQL命令是______。 A.CREATE CURSOR B.CREATE TABLE C.CREATE INDEX D.CERATE VIEW

27 三、 SQL的数据定义 3、表结构的修改 主要功能格式： ALTER TABLE 表名ADD 字段名(字段属性)——新增字段
ALTER TABLE 表名ALTER 字段名(字段属性)——修改字段 ALTER TABLE 表名DROP 字段名——删除字段 ALTER TABLE 表名RENAME 旧名 TO 新名 ——字段更名

28 三、 SQL的数据定义 4、表的删除 命令格式：DROP TABLE 表名 5、视图的定义
命令格式：CREATE VIEW 视图名 [字段名清单] AS 查询语句 6、视图的删除 命令格式：DROP VIEW 视图名 实例分析：要删除数据库“LIBRARY.DBC”中的视图V_BOOKS： OPEN DATABASE LIBRARY DROP VIEW SAA

29 四、 SQL的数据操作 1、操作功能包括的内容 1）表记录的追加 2）表记录的删除 3）表记录的修改 2、操作功能命令格式
1）表记录的追加(插入)：INSERT INTO 表名 [字段名1,字段名2,…]; VALUES （表达式1,表达式2,…） 或者 INSERT INTO 表名 FROM ARRAY 数组名/FROM 内存变量 2）表记录的删除：DELETE FROM [数据库名！]表名 WHERE 过滤条件 3）表记录的修改(更新)：UPDATE [数据库名！]表名1； SET 字段名1=表达式1,字段名2=表达式2,… WHERE 过滤条件 例1：往学生表中插入一条新记录 命令：INSERT INTO 学生(学号,姓名,性别,出生年月,入校总分,三好生); VALUES("S ","许昌","女",CTOD("02/05/84"),588,T)

30 四、 SQL的数据操作 例2：修改记录把程进老师的职称改为副教授 命令：UPDATE 教师; SET 职称="副教授";
WHERE 姓名="程进" 例3：修改记录把程进老师的职称改为副教授,工资改为1800 SET 职称="副教授",工资=1800; 例4：修改记录把所有讲师的工资提升20% 命令：UPDATE 教师; SET 工资=工资*0.2; WHERE 职称="讲师"    例5：删除刘伟教师的记录  命令：DELETE FROM 教师 WHERE 姓名="刘伟" 例6：删除所有教师的记录 命令：DELETE FROM 教师

31 四、 SQL的数据操作 练习： 1. UPDATE—SQL语句的功能是______。 A.数据定义功能 B.数据查询功能
C.修改表中某些列的属性 D.修改表中某些列的内容 2. 向表中插入数据的SQL命令是______。 A.INSERT B.INSERT INTO C.INSERT IN D.INSERT BEFORE 3. 语句DELETE FROM 学生 WHERE成绩<70的功能是______。 A.物理删除成绩在70分以下的记录 B.彻底删除成绩在70分以下的记录 C.删除考生成绩表 D.为表中成绩小于70分的记录添加删除标记 4. 利用SQL语句为某数据库中的“学生”表中所有学生的成绩增加10，应使用命令______。 A.UPDATE 学生 成绩=成绩+10 B.UPDATE成绩=成绩+10 C.SET成绩=成绩+10 D.UPDATE 学生SET成绩=成绩+10

32 五、视 图 1、 什么是视图？ 视图是由一个表或多个表中符合条件的数据产生的虚表。
五、视 图 1、 什么是视图？ 视图是由一个表或多个表中符合条件的数据产生的虚表。 特点：（1）它是Visual FoxPro 6.0提供的一种定制的、可更改的数据集合，它集合了表和查询的特点。 （ 2 ）视图存在于数据库中。（建立视图先要打开数据库）。 （ 3）视图可以用来从一个或多个相关联的表中提取有用的信息；与表相类似的地方是，可用来更新得到的结果数据，并将更新后的数据反馈到数据源上。 （4）视图建立后，可以象使用一般表的方法一样使用视图。 （5）视图可分为本地视图和远程视图两类。 视图的功能：通过视图可以从一个或多个相关联的表中提取有用信息；利用视图可以更新数据表中的数据。 视图的优点：提高操作效率；增强了操作的安全性。

33 小结 1、SQL语句及其特点 2、查询功能 n 命令格式 目标子句 联接子句 筛选子句 排序子句 n 分组子句 量词使用 合并使用 嵌套查询
3、操作功能 表记录的追加:INSERT INTO表名 (字段名集) VALUE (表达式集) 表记录的删除:DELETE FROM 表名 表记录的修改:UPDATE 表名 SET 字段名=表达式 4、定义功能 表结构的定义： CREATE TABLE 表结构修改：ALTER TABLE 表结构删除：DROP TABLE

34 课堂练习 上机完成课本例题