第6章 SQL语言 VFP课件

1、1目 录上一页下一页退 出目 录上一页下一页退 出2本本 章章 要要 点点6.1SQL语言的基本概述语言的基本概述6.2数据定义数据定义6.3数据操纵数据操纵6.4数据管理数据管理6.5SQL中的数据查询语句中的数据查询语句目 录上一页下一页退 出31综合统一综合统一 SQL语言集数据定义（DDL）、数据操纵（DML）、数据管理（DCL）的功能于一体，语言风格统一，可以独立完成数据库的全部操作，包括定义关系模式、录入数据及建立数据库、查询、更新、维护数据、数据库的重新构造、数据库安全性等一系列操作的要求，为数据库应用系统开发者提供了良好的环境。 2高度非过程化高度非过程化3面向集合的操作方式面

2、向集合的操作方式4以同一种语法结构提供两种使用方式以同一种语法结构提供两种使用方式5语言简洁，易学易用语言简洁，易学易用6.1.1 SQL语言的特点语言的特点目 录上一页下一页退 出4数据定义语言DDL用于执行数据定义的操作，如创建或删除表、索引和视图之类的对象。由CREATE、DROP、ALTER命令组成，完成数据库对象的建立（CREATE）、删除（DROP）和修改（ALTER）。6.2.1 定义（创建）表定义（创建）表【格式】CREATE TABLE （，）完整性约束完整性约束NULL | NOT NULL，）【功能】定义（也称创建）一个表。目 录上一页下一页退 出5【例【例4-1】创建一

3、个表STUD（学生信息表），它由以下字段组成：学号 （C，10）；姓名（C，8）；性别（C，2）；班级名（C，10）；系别代号（C，2）；地址（C，50）；出生日期 （D）；是否团员 （L）；备注 （M）。CREATE TABLE STUD（学号（学号C（10），姓名），姓名 C（10），性别），性别 C（2），班级名），班级名 C（10）,系别代号系别代号 C（2）,地址地址 C（50）,出生日期出生日期 D,是否团员是否团员 L,备注备注 M,照片照片 G）LIST STRUCTURE【例【例4-2】创建一个表SC（课程成绩表），它由以下字段组成：学号（C，10）；课程号（C，2）。CRE

4、ATE TABLE SC（学号（学号 C（10），课程号），课程号 C（2）LIST STRUCTURE目 录上一页下一页退 出6【格式【格式】ALTER TABLE ADD （，）完整性约束完整性约束NULL | NOT NULLDROP 完整性约束完整性约束MODIFY 【功能】修改表结构。【说明】ADD子句用于增加指定表的字段变量名、数据类型、宽度和完整性约束条件；DROP子句用于删除指定的的完整性约束条件；MODIFY子句用于修改原有的字段变量的值。【例【例4-3】在课程成绩表SC中，增加一个成绩字段变量（N，3）。6.2.2 修改表修改表ALTER TABLE SC ADD 成绩成绩

5、 N(3)LIST STRUCTURE目 录上一页下一页退 出7【格式】DROP DATABASE 【功能】删除指定数据库的结构和数据。【说明】谨慎使用。4.2.4 删除表删除表【格式】DROP TALBE 【功能】删除指定表的结构和内容（包括在此表上建立的索引）。【说明】如果只是想删除一个表中的所有记录，则应使用DELETE语句。6.2.3 删除数据库删除数据库目 录上一页下一页退 出8 数据操纵语言是完成数据操作的命令，一般分为两种类型的数据操纵，它们统称为DML： 数据检索（常称为查询）：寻找所需的具体数据。 数据修改：添加、删除和改变数据。数据操纵语言一般由INSERT（插入）、DEL

6、ETE（删除）、UPDATE（更新），SELETE（检索，又称查询）等组成，由于SELETE比较特殊，所以一般又将它以查询（检索）语言单独出现。6.3 数据操纵数据操纵目 录上一页下一页退 出9【格式1】INSERT INTO VALUES （）【格式2】INSERT INTO FROM ARRAY | FROM MEMVAR【功能】在指定的表文件末尾追加一条记录。格式1用表达式表中的各表达式值赋值给中的相应的各字段。格式2用数组或内存变量的值赋值给表文件中各字段。【说明】如果某些字段名在INTO子句中没有出现，则新记录在这些字段名上将取空值（或默认值）。但必须注意的是，在表定义说明了NOT

7、NULL的字段名不能取空值。6.3.1 插入记录插入记录目 录上一页下一页退 出10：指定表文件中的字段，缺省时，按表文件字段的顺序依次赋值。：指定要追加的记录各个字段的值。【例4-4】在表文件STUD的末尾追加三条记录。*用表达式方式追加第一条记录*INSERT INTO STUD (学号，姓名，性别，班级学号，姓名，性别，班级名，系别代号，地址，出生日期，是否团员名，系别代号，地址，出生日期，是否团员) ; VALUES (011110，李建国李建国，男男，计计0121，01，湖北武汉湖北武汉，09/28/02，.T.)目 录上一页下一页退 出11DIMENSION DATA8DATA（1

8、）=011103DATA（2）=李宁李宁DATA（3）=女女DATA（4）=电电0134DATA（5）=02DATA（6）=江西九江江西九江DATA（7）=05/06/85DATA（8）=.F.INSERT INTO STUD FROM ARRAY DATA*用数组方式追加第二条记录*目 录上一页下一页退 出12学号学号=011202姓名姓名=赵娜赵娜性别性别=女女班级名班级名=英英0112系别代号系别代号=03地址地址=广西南宁广西南宁出生日期出生日期=02/21/84是否团员是否团员=.F.INSERT INTO STUD FROM MEMVARLIST*用内存变量方式追加第三条记录*目

9、录上一页下一页退 出13【格式】UPDATE SET = ，= WHERE 【功能】更新指定表文件中满足WHERE条件子句的数据。其中SET子句用于指定列和修改的值，WHERE用于指定更新的行，如果省略WHERE子句，则表示表中所有行。【说明】更新操作又称为修改操作。【例4-5】将成绩表（SC）中，所有课程号为02的成绩各加5分。 6.3.2 更新记录命令更新记录命令UPDATE SC SET 成绩成绩=成绩成绩+5 ; WHERE 课程号课程号=02目 录上一页下一页退 出146.3.3 删除记录删除记录【格式】DELETE FROM WHERE 【功能】从指定的表中删除满足WHERE子句条

10、件的所有记录。如果在DELETE语句中没有WHERE子句，则该表中的所有记录都将被删除。【说明】这里的删除是逻辑删除，即在删除的记录前加上一个删除标记“*”。【例4-7】删除STUD表中所有性别为男的记录。 DELETE FROM STUD ; WHERE 性别性别=“男男”目 录上一页下一页退 出156.5 SQL中的数据查询语句中的数据查询语句 数据库中的数据很多时侯是为了查询的，因此，数据查询是数据库的核心操作。而在SQL语言中，查询语言中有一条查询命令，即SELECT语句。目 录上一页下一页退 出166.5.1 基本查询语句基本查询语句语句格式SELECT ALL|DISTINCT ，

11、 FROM ， WHERE GROUP BY HAVING ORDER BY ASC|DESC ； 目 录上一页下一页退 出17语句格式 SELECT子句：指定要显示的属性列 FROM子句：指定查询对象(基本表或视图) WHERE子句：指定查询条件 GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。 HAVING短语：筛选出只有满足指定条件的组 ORDER BY子句：对查询结果表按指定列值的升序或降序排序 目 录上一页下一页退 出186.6 查 询 1 单表查询2 连接查询3 嵌套查询4集合查询5 小结 目 录上一页下一页退 出19单表查

12、询 查询仅涉及一个表，是一种最简单的查询操作一、选择表中的若干列二、选择表中的若干元组三、对查询结果排序四、使用集函数五、对查询结果分组 目 录上一页下一页退 出20查询指定列例1 查询全体学生的学号与姓名。 例2 查询全体学生的详细记录。SELECT Sno，SnameFROM Student； SELECT Sno，Sname，Ssex，Sage，Sdept FROM Student； 或SELECT *FROM Student； 目 录上一页下一页退 出213. 查询经过计算的值 SELECT子句的为表达式 算术表达式 字符串常量 函数 列别名 等 目 录上一页下一页退 出223. 查询

13、经过计算的值例4 表中有姓名、年龄，查全体学生的姓名及其出生年份。SELECT Sname，2006-SageFROM Student； 输出结果： Sname 2006-Sage - - 李勇 1976 刘晨 1977 王名 1978 张立 1978 目 录上一页下一页退 出233. 查询经过计算的值例5 查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。SELECT Sname，Year of Birth: ，2000-Sage， ISLOWER(Sdept)FROM Student； 目 录上一页下一页退 出24例题（续）输出结果： Sname Year of Birth

14、: 2000-Sage ISLOWER(Sdept) - - - - 李勇 Year of Birth: 1976 cs 刘晨 Year of Birth: 1977 is 王名 Year of Birth: 1978 ma 张立 Year of Birth: 1977 is 目 录上一页下一页退 出25例5.1 使用列别名改变查询结果的列标题SELECT Sname NAME，Year of Birth: BIRTH， 2000-Sage BIRTHDAY，ISLOWER(Sdept) DEPARTMENTFROM Student；输出结果： NAME BIRTH BIRTHDAY DEPA

15、RTMENT - - - - 李勇 Year of Birth: 1976 cs 刘晨 Year of Birth: 1977 is 王名 Year of Birth: 1978 ma 张立 Year of Birth: 1977 is目 录上一页下一页退 出26二、选择表中的若干元组 消除取值重复的行查询满足条件的元组 目 录上一页下一页退 出271. 消除取值重复的行 在SELECT子句中使用DISTINCT短语假设SC表中有下列数据 Sno Cno Grade - - - 95001 1 92 95001 2 85 95001 3 88 95002 2 90 95002 3 80目 录上

16、一页下一页退 出28ALL 与 DISTINCT 例6 查询选修了课程的学生学号。(1) SELECT Sno FROM SC;或(默认 ALL) SELECT ALL Sno FROM SC; 结果： Sno - 95001 95001 95001 95002 95002 目 录上一页下一页退 出29例题（续）(2) 结果： Sno - 95001 95002 SELECT DISTINCT Sno FROM SC;目 录上一页下一页退 出30例题（续）注意 DISTINCT短语的作用范围是所有目标列例：查询选修课程的各种成绩错误的写法SELECT DISTINCT Cno，DISTINCT

17、 GradeFROM SC;正确的写法 SELECT DISTINCT Cno，Grade FROM SC; 目 录上一页下一页退 出312.查询满足条件的元组WHERE子句常用的查询条件子句常用的查询条件目 录上一页下一页退 出32(1) 比较大小在WHERE子句的中使用比较运算符=，=，=，!= 或 ，!，!， 逻辑运算符NOT + 比较运算符例8 查询所有年龄在20岁以下的学生姓名及其年龄。 SELECT Sname，Sage FROM Student WHERE Sage = 20； 目 录上一页下一页退 出33(2) 确定范围使用谓词 BETWEEN AND NOT BETWEEN

18、AND 例10 查询年龄在2023岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。 SELECT Sname，Sdept，SageFROM StudentWHERE Sage BETWEEN 20 AND 23； 目 录上一页下一页退 出34例题（续）例11 查询年龄不在2023岁之间的学生姓名、系别和年龄。SELECT Sname，Sdept，SageFROM StudentWHERE Sage NOT BETWEEN 20 AND 23； 目 录上一页下一页退 出35(3) 确定集合使用谓词 IN , NOT IN ：用逗号分隔的一组取值例12查询信息系（IS）、数学系（MA）和计

19、算机科学系（CS）学生的姓名和性别。SELECT Sname，SsexFROM StudentWHERE Sdept IN ( IS，MA，CS );目 录上一页下一页退 出36(3) 确定集合例13查询既不是信息系、数学系，也不是计算 机科学系的学生的姓名和性别。SELECT Sname，SsexFROM Student WHERE Sdept NOT IN ( IS，MA，CS );目 录上一页下一页退 出37(4) 字符串匹配 NOT LIKE ESCAPE ：指定匹配模板 匹配模板：固定字符串或含通配符的字符串 当匹配模板为固定字符串时， 可以用 = 运算符取代 LIKE 谓词 用 !

20、= 或 运算符取代 NOT LIKE 谓词目 录上一页下一页退 出38通配符w % (百分号) 代表任意长度（长度可以为0）的字符串 例：a%b表示以a开头，以b结尾的任意长度的字符串。 如acb，addgb，ab 等都满足该匹配串_ (下横线) 代表任意单个字符 例：a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串目 录上一页下一页退 出39例题1) 匹配模板为固定字符串 例14 查询学号为95001的学生的详细情况。 SELECT * FROM Student WHERE Sno LIKE 95001；等价于： SELECT * FROM Student

21、 WHERE Sno = 95001；目 录上一页下一页退 出40例题（续）2) 匹配模板为含通配符的字符串例15 查询所有姓刘学生的姓名、学号和性别。 SELECT Sname，Sno，Ssex FROM Student WHERE Sname LIKE 刘%；目 录上一页下一页退 出41例题（续）匹配模板为含通配符的字符串（续）例17 查询名字中第2个字为阳字的学生的姓名和学号。 SELECT Sname，Sno FROM Student WHERE Sname LIKE _阳%；目 录上一页下一页退 出42例题（续）匹配模板为含通配符的字符串（续）例18 查询所有不姓刘的学生姓名。 SE

22、LECT Sname，Sno，Ssex FROM Student WHERE Sname NOT LIKE 刘%；目 录上一页下一页退 出43ESCAPE 短语： 当用户要查询的字符串本身就含有 % 或 _ 时，要使用ESCAPE 短语对通配符进行转义。目 录上一页下一页退 出44例题（续）3) 使用换码字符将通配符转义为普通字符 例19 查询DB_Design课程的课程号和学分。 SELECT Cno，Ccredit FROM Course WHERE Cname LIKE DB_Design ESCAPE 目 录上一页下一页退 出45(5) 涉及空值的查询 使用谓词 IS NULL 或 I

23、S NOT NULL “IS NULL” 不能用 “= NULL” 代替例21 某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。 SELECT Sno，Cno FROM SC WHERE Grade IS NULL；目 录上一页下一页退 出46例题(续) 例22 查所有有成绩的学生学号和课程号。 SELECT Sno，Cno FROM SC WHERE Grade IS NOT NULL；目 录上一页下一页退 出47(6) 多重条件查询用逻辑运算符AND和 OR来联结多个查询条件 AND的优先级高于OR 可以用括号改变优先级可用来实现多种

24、其他谓词 NOT IN NOT BETWEEN AND 目 录上一页下一页退 出48例题例23 查询计算机系年龄在20岁以下的学生姓名。 SELECT Sname FROM Student WHERE Sdept= CS AND Sage=20 AND Sage=23；目 录上一页下一页退 出51三、对查询结果排序 使用ORDER BY子句 可以按一个或多个属性列排序 升序：ASC；降序：DESC；缺省值为升序当排序列含空值时 ASC：排序列为空值的元组最后显示 DESC：排序列为空值的元组最先显示 目 录上一页下一页退 出52对查询结果排序（续） 例24 查询选修了3号课程的学生的学号及其成

25、绩，查询结果按分数降序排列。 SELECT Sno，Grade FROM SC WHERE Cno= 3 ORDER BY Grade DESC； 目 录上一页下一页退 出53查询结果 Sno Grade - - 95010 95024 95007 92 95003 82 95010 82 95009 75 95014 61 95002 55目 录上一页下一页退 出54对查询结果排序（续） 例25 查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。 SELECT * FROM Student ORDER BY Sdept，Sage DESC； 目 录上一页下一页退

26、 出55四、使用集函数 5类主要集函数 计数COUNT（DISTINCT|ALL *）COUNT（DISTINCT|ALL ） 计算总和SUM（DISTINCT|ALL ） 计算平均值AVG（DISTINCT|ALL ）目 录上一页下一页退 出56使用集函数（续） 求最大值MAX（DISTINCT|ALL ） 求最小值MIN（DISTINCT|ALL ） DISTINCT短语：在计算时要取消指定列中的重复值 ALL短语：不取消重复值 ALL为缺省值目 录上一页下一页退 出57使用集函数 （续）例26 查询学生总人数。 SELECT COUNT(*) FROM Student； 例27 查询选修

27、了课程的学生人数。注：用DISTINCT以避免重复计算学生人数SELECT COUNT(DISTINCT Sno) FROM SC；目 录上一页下一页退 出58使用集函数 （续）例28 计算1号课程的学生平均成绩。 例29 查询选修1号课程的学生最高分数。 SELECT AVG(Grade) FROM SC WHERE Cno= 1 ；SELECT MAX(Grade) FROM SC WHER Cno= 1 ； 目 录上一页下一页退 出59五、对查询结果分组 使用GROUP BY子句分组 GROUP BY子句是分组查询子句。HAVING子句用于指定分组子句的条件。GROUP BY子句、HAV

28、ING子句和集合函数一起可以实现对每个组生成一行和一个汇总值。目 录上一页下一页退 出60使用GROUP BY子句分组例30 求各个课程号及相应的选课人数。结果 Cno COUNT(Sno) 1 22 2 34 3 44 4 33 5 48SELECT Cno，COUNT(Sno) FROM SC GROUP BY Cno； 目 录上一页下一页退 出61对查询结果分组 （续）GROUP BY子句的作用对象是查询的中间结果表分组方法：按指定的一列或多列值分组，值相等的为一组使用GROUP BY子句后，SELECT子句的列名列表中只能出现分组属性和集函数 目 录上一页下一页退 出62使用HAVIN

29、G短语筛选最终输出结果例31 查询选修了3门以上课程的学生学号。 SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) 3；目 录上一页下一页退 出63例题例32 查询有3门以上课程是90分以上的 学生的学号及（90分以上的）课程数 SELECT Sno, COUNT(*) FROM SC WHERE Grade=90 GROUP BY Sno HAVING COUNT(*)=3;目 录上一页下一页退 出64嵌套查询嵌套查询概述嵌套查询分类嵌套查询求解方法引出子查询的谓词 目 录上一页下一页退 出65嵌套查询(续)嵌套查询概述 一个SELECT-FRO

30、M-WHERE语句称为一个查询块 将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询 目 录上一页下一页退 出66嵌套查询(续) SELECT Sname外层查询/父查询 FROM Student WHERE Sno IN （SELECT Sno 内层查询/子查询 FROM SC WHERE Cno= 2 ）；目 录上一页下一页退 出67嵌套查询求解方法 需要特别指出的是需要特别指出的是，子查询的子查询的SELECT语句语句中不能使用中不能使用ORDER BY子句，子句，ORDER BY子子句只能对最终查询结果排序。句只能对最终查询结果排序。 嵌套查询一

31、般的求解方法是由里向外处理。嵌套查询一般的求解方法是由里向外处理。即每个子查询在上一级查询处理即每个子查询在上一级查询处理之前之前求解。求解。子查询的结果用于建立其父查询的查找条件。子查询的结果用于建立其父查询的查找条件。 嵌套嵌套 查询使我们可以用多个简单查询构成查询使我们可以用多个简单查询构成复杂的查询，从而增强复杂的查询，从而增强SQL的查询能力。以的查询能力。以层层嵌套的方式来构选程序正是层层嵌套的方式来构选程序正是SQL中中“结结构化构化“的含义所在。的含义所在。目 录上一页下一页退 出68一、带有IN谓词的子查询例37 查询与“刘晨”在同一个系学习的学生。 此查询要求可以分步来完成

32、 确定“刘晨”所在系名 SELECT Sdept FROM Student WHERE Sname= 刘晨 ；结果为： Sdept IS目 录上一页下一页退 出69带有IN谓词的子查询（续） 查找所有在IS系学习的学生。 SELECT Sno，Sname，Sdept FROM Student WHERE Sdept= IS ； 结果为：Sno Sname Sdept95001 刘晨 IS95004 张立 IS目 录上一页下一页退 出70 构造嵌套查询将第一步查询嵌入到第二步查询的条件中 SELECT Sno，Sname，Sdept FROM Student WHERE Sdept IN (SE

33、LECT Sdept FROM Student WHERE Sname= 刘晨 )；此查询为不相关子查询。DBMS求解该查询时也是分步去做的。目 录上一页下一页退 出71带有IN谓词的子查询（续）父查询和子查询中的表均可以定义别名 SELECT Sno，Sname，Sdept FROM Student S1 WHERE S1.Sdept IN (SELECT Sdept FROM Student S2 WHERE S2.Sname= 刘晨 )；目 录上一页下一页退 出72带有IN谓词的子查询（续）例38查询选修了课程名为“信息系统”的学生学号和姓名 SELECT Sno，Sname 最后在St

34、udent关系中 FROM Student 取出Sno和Sname WHERE Sno IN (SELECT Sno 然后在SC关系中找出选 FROM SC 修了3号课程的学生学号 WHERE Cno IN (SELECT Cno 首先在Course关系中找出“信 FROM Course 息系统”的课程号，结果为3号 WHERE Cname= 信息系统);目 录上一页下一页退 出73带有IN谓词的子查询（续）结果： Sno Sname - - 95001 李勇 95002 刘晨目 录上一页下一页退 出74二、带有比较运算符的子查询 当能确切知道内层查询返回单值时，可用比较运算符（，=，=，!=

35、或）。与ANY或ALL谓词配合使用谓词语义 ANY：任意一个值 ALL：所有值目 录上一页下一页退 出75带有比较运算符的子查询（续） 子查询一定要跟在比较符之后 错误的例子： SELECT Sno，Sname，Sdept FROM Student WHERE ( SELECT Sdept FROM Student WHERE Sname= 刘晨 ) = Sdept；目 录上一页下一页退 出76带有ANY或ALL谓词的子查询（续）例39 查询其他系中比信息系任意一个(其中某一个)学生年龄小的学生姓名和年龄 SELECT Sname，Sage FROM Student WHERE Sage ANY (SELECT Sage FROM Student WHERE Sdept= IS ) AND Sdept IS ; /* 注意这是父查询块中的条件 */执行过程1.DBMS执行此查询时，首先处理子查询，找出 IS系中所有学生的年龄，构成一个集合(19，18)2. 处理父查询，找所有不是IS系且年龄小于 19 或 18的学生目 录上一页下一页退 出77带有ANY或ALL谓词的子查询（续）例40 查询其他系中比信息系所有学生年龄都小的学生姓名及年龄。方法一：用ALL谓词 SELE