第6章SQL语言课件

1、2022-6-11第第6 6章章 SQLSQL语言的应用语言的应用 6.1 SQL语言概述 6.2 数据定义 6.3 数据操纵6.4 数据查询2022-6-126.1 SQL语言概述 SQL（Structured Query Language，结构化查询语言）语言是集数据定义、数据查询、数据操纵和数据控制功能于一体的语言，具有功能丰富、使用灵活、语言简捷易学等特点。 SQL语言是一个非过程语言，它的大多数语句都是独立执行的，与上下文无关；通常一个语句完成一个特定的操作功能。 可以使用SQL完成各类查询的创建、表定义、表结构维护、表中记录操作等。实际上Visual FoxPro 的视图和查询就是

2、立足在SQL语言基础上建立的。2022-6-136.1.1 SQL语言的特点1. 1. 高度的综合高度的综合2. 2. 高度非过程化高度非过程化3. 3. 面向集合的操作方式面向集合的操作方式4. 4. 一种语法结构提供两种使用方式一种语法结构提供两种使用方式5. 5. 语言简洁，易学易用语言简洁，易学易用2022-6-146.2 数据定义 数据定义语言由CREATE、DROP和ALTER命令组成。这3个命令关键字针对不同的数据库对象分别有3个命令。例如针对表对象的3个命令是建表结构命令CREATE TABLE、修改表结构命令ALTER TABLE和删除表命令DROP TABLE。2022-6

3、-156.2.1 6.2.1 建立表结构建立表结构 命令格式： CREATE TABLE FREE ( (,) NULL|NOT NULLCHECKERRORDEFAULTPRIMARY KEY|UNIQUE , (,) NULL|NOT NULLCHECKERRORDEFAULT10 AND 年龄45 ERROR “年龄范围在1045,请重输入!”,党员否 L,入学年月 D DEFAULT CTOD(“09/01/2010” )2022-6-196.2.2 修改表结构1.1.语句格式：语句格式：Alter Table Add , DropConstraint|C0lumn, Alter ,；

4、2.2.语句功能：语句功能： 为指定的表的指定字段进行修改或添加、删除指定的字段。2022-6-1103.3.说明说明(1) 是指要修改表结构的表的名字。(2) Add子句用于增加新字段和新的完整性约束条件。(3) Drop子句用于删除指定的字段和完整性约束，当删除某一字段时，所有引用该字段的视图和约束也一起自动被删除。(4) Alter子句用于修改原有字段的定义，包括修改字段名、字段的宽度和字段的数据类型。2022-6-111 【例6.3】为成绩表添加两个字段：“平时”字段N (5,1)和“期中”字段 N (5,1)。ALTER TABLE 成绩 ADD 平时 N (5,1)ALTER TA

5、BLE 成绩 ADD 期中 N (5,1)其他操作案例参照教材P94-952022-6-1126.2.3 6.2.3 删除表删除表 当某个表不再需要时，可以使用Drop Table语句删除。1.1.语句格式：语句格式：Drop Table 2.2.语句功能：语句功能：删除以为名的表。3.3.说明说明 表一旦删除，表中的数据无法恢复，此时系统释放其占用的存储存空间，因此执行删除表的操作一定要格外小心。2022-6-1136.3 数据操纵6.3.1 插入记录 在表操作中，给表添加数据是常用的表操作，这里所介绍的插入数据语句是在表的尾部添加一个记录。在SQL语言中，插入数据使用Insert语句。1.

6、1.语句格式：语句格式：Insert Into (，) Values (,)2.2.语句功能：语句功能： 在指定的表尾添加一条新记录，其值为VALUES后面表达式的值。2022-6-114【例6.8】向学生.DBF表中添加记录。INSERT INTO 学生 (学号, 姓名,年龄); VALUES(“9902101”,”李成功”,17)INSERT INTO 学生 VALUES(“9902102”,;”李明”,”男”,18, .F. ,CTOD(“03/24/2009”) )2022-6-1156.3.2 删除记录1.1.语句格式：语句格式：Delete From Where 2.2.语句功能：

7、语句功能：在以为名的表中，根据指定的条件逻辑删除记录。2022-6-116【例6.9】将学生.DBF表所有男生的记录逻辑删除。 DELETE FROM 学生 WHERE 性别=“男” 完成以上操作后，在学生.DBF表将所有男生的记录逻辑删除了，但没有从物理上删除。只有执行了PACK命令，逻辑删除的记录才真正地从物理上删除。逻辑删除的记录还可以用RECALL命令取消删除。2022-6-1176.3.3 更新记录1.1.语句格式：语句格式：Update Set = ，= , Where 2.2.语句功能：语句功能：更新以为名的表中数据。2022-6-118【例6.10】将学生.DBF表所有99级学

8、生“入学年月”置为1999/09/10。UPDATE 学生 SET 入学年月= 1999/09/10; WHERE LEFT(学号,2) =”99”2022-6-1196.4 数据查询 SQL语言最主要的功能就是查询功能，它提供的Select语句用于检索和显示一个或多个数据库表中数据，Select语句功能强大，使用方式非常灵活，可用一个语句实现关系代数中的选择、投影和连接运算。 Select语句的基本结构的SELECTFROMWHERE，它包含输出字段、数据来源、查询条件等子句。在这种固定格式中，可以不要WHERE，但是SELECT和FROM是必备的。2022-6-1201.1.语句格式：语句

9、格式：Select All|Distinct | , From Left Right Join On Where INTO|TO FILE ADDITIVETO PRINTER PROMPT|TO SCREENGroup By Having UNIONALLSELECT 命令Order By Asc Desc(注:为简明扼要，还有部分选项未列出)2022-6-1212.2.语句功能：语句功能： 从指定的基本表或视图中，创建一个由指定范围内、满足条件、按某字段分组、按某字段排序的指定字段组成的新记录集。3.3.说明说明（请参照（请参照P97P97页）页）2022-6-122查询计算函数的格式及功

10、能查询计算函数的格式及功能 函数格式 函数功能 COUNT(*)计算记录个数SUM（字段名）求字段名所指定字段值的总和AVG（字段名）求字段名所指定字段的平均值MAX（字段名）求字段名所指定字段的最大值MIN（字段名）求字段名所指定字段的最小值2022-6-1236.4.1 基本查询1.1.检索表中所有的记录和字段检索表中所有的记录和字段【例6.11】利用学生表中的数据创建一个查询，其结果包含学生表的所有字段。操作命令如下：SELECT * FROM 学生 命令中的*表示输出所有字段，数据来源是学生.dbf表，表中所有的内容以浏览方式显示。2022-6-1242.2.检索表中指定的字段检索表中

11、指定的字段【例6.12】利用学生表中的数据创建一个查询，其结果包含学号、姓名、性别、出生日期、专业和入学成绩6个字段，即检索数据表中指定的字段。操作命令如下： SELECT 学号,姓名,性别,出生日期,专业,;入学成绩 FROM 学生2022-6-1253.3.检索表中指定的字段和产生新字段检索表中指定的字段和产生新字段【例6.13】利用学生表中的数据创建一个查询，其结果包含学号、姓名、性别，并增加一个年龄字段，即检索数据表中指定的字段和产生新字段。操作命令如下： SELECT 学号,姓名,性别,year(date()-year(出生;日期) AS 年龄 FROM 学生2022-6-1266.

12、4.2 带条件查询WHERE是条件语句关键字，是可选项，其格式如下所示:WHERE AND |OR . 其中，条件表达式可以是单表的条件表达式，也可以是多表之间的条件表达式，表达式用的比较符为：=(等于)、!=(不等于)、=(精确等于)、(大于)、=(大于等于)、(小于)、500 AND 贷款否=.F.2022-6-1293.SQL3.SQL常用函数及常用函数及WHEREWHERE中条件关键字的应用实例中条件关键字的应用实例 计算字段 【例6.15】在学生表中检索计算机专业学生入学成绩平均分。操作命令如下： SELECT 专业,AVG(入学成绩) AS “入学成绩平均分” FROM 学生 WH

13、ERE 专业=“计算机” 排除后列出 、IN运算符 、BETWEEN运算符 、 LIKE运算符 、IS NULL运算符等实例参看教材P102-P103页2022-6-130* *6.4.3 6.4.3 嵌套查询嵌套查询 使用SQL语句中，一个SelectFromWhere语句产生一个新的数据集，一个查询语句完全嵌套到另一个查询语句中的Where或Having的“条件”短语中，这种查询称为嵌套查询。 通常把仅嵌入一层子查询的SELECT命令称为单层嵌套查询，把嵌入子查询多于一层的查询称为多层嵌套查询。Visual FoxPro6.0只支持单层嵌套查询。2022-6-131* *1 1 用于相等用

14、于相等(=)(=)判断的子查询判断的子查询 【例6.21】在成绩.DBF和课程.DBF表中检索选修电脑文秘应用课的所有学生的学号和期末成绩。操作命令如下： SELECT 学号,期末 FROM 成绩 WHERE 课号 = ;(SELECT 课号 FROM 课程 WHERE 课名=电脑文秘应;用”) 2022-6-132* *2.2.用于用于 ANYANY的子查询的子查询 【例6.22】在成绩表中检索出选修课号为“02005”课的学生中期末成绩比选修“09002”的最低期末成绩高的学生的学号和期末成绩。操作命令如下：SELECT 学号,课号,期末 AS 期末成绩 FROM 成绩; WHERE 课号

15、=“02005” AND 期末 ANY ;(SELECT 期末 FROM 成绩 WHERE 课号=“09002”)2022-6-133* *3. 3. 用于用于ALLALL的子查询的子查询 【例6.23】在成绩表中检索出选修课号为“02002”课的学生，这些学生的期末成绩比选修“09002”课的最高期末成绩还要高的学生的学号和期末成绩。操作命令如下：SELECT 学号, 期末 AS 期末成绩 FROM 成绩; WHERE 课号=“02002”AND 期末ALL; (SELECT 期末 FROM 成绩 WHERE 课号=“09002”) 2022-6-134* *4.4.用于用于ININ短语的子

16、查询短语的子查询 【例6.24】在学生.DBF和专业.DBF表中检索出文科类(即专业类别为“A”)学生的学生名单。 操作命令如下：SELECT 学号,姓名 FROM 学生 WHERE 专业 IN ;(SELECT 专业名称 FROM 专业 WHERE 专业类别= “A”) IN是属于的意思，等价于“=ANY”，即等于子查询中任何一个值 2022-6-135* *6.4.4 6.4.4 多表联接查询多表联接查询 把多个表的信息集中在一起，就要用到“连接”操作，SQL的连接操作是通过相关联表之间记录的匹配而产生的结果。2022-6-136* *1 1 等值连接等值连接 等值连接是按对应字段的共同值

17、将一个表中的记录与另一个表中的记录相连接。 【例6.25】利用学生表、成绩表及课程表输出所有学生的成绩单，要求给出学号、姓名、课号、课名和总评成绩(总评成绩=(平时+期中+期末)/3)。操作命令如下：SELECT 学生.学号,学生.姓名,成绩.课号,课程.课名,;(成绩.平时+成绩.期中+成绩.期末)/3 AS 总评成绩;FROM 学生, 成绩, 课程;WHERE 学生.学号=成绩.学号 AND 成绩.课号=课程.课号2022-6-137* *2 2内部连接内部连接(Inner Join)(Inner Join) 所谓内部连接是指包括符合条件的每个表格中的记录。也就是说是所有满足连接条件的记录

18、都包含在查询结果中。 (上面例子就是内部连接 )2022-6-138* *3 3 外部连接外部连接(Outer Join) (Outer Join) 外部连接又分为左外连接，右外连接和全外连接 (1) 左外连接 左外连接也叫左连接(Left Outer Join)，其系统执行过程是左表的某条记录与右表的所有记录依次比较，若有满足连接条件的，则产生一个真实值记录；若都不满足，则产生一个含有NULL值的记录。2022-6-139 (2) 右外连接 右外连接也叫右连接(Right Outer Join)，其系统执行过程是右表的某条记录与左表的所有记录依次比较，若有满足连接条件的，则产生一个真实值记录

19、；若都不满足，则产生一个含有NULL值的记录。接着，右表的下一记录与左表的所有记录依次比较字段值，重复上述过程，直到左表所有记录都比较完为止。连接结果的记录个数与右表的记录个数一致。2022-6-140 (3) 全外连接 全外连接也叫完全连接(Full Join)，其系统执行过程是先按右连接比较字段值，然后按左连接比较字段值，重复记录不列入查询结果中。2022-6-1416.4.5查询结果输出处理 使用SELECT.FROM.WHERE命令完成查询工作后，所查询的结果默认显示在屏幕上，若需要对这些查询结果进行输出处理，则需要SELECT的其它子句配合操作。2022-6-1421.排序输出 SE

20、LECT的查询结果是按查询过程中的自然顺序给出的，因此查询结果通常无序，如果希望查询结果有序输出，需要用到ORDER BY子句 【例6.28】利用学生表中的数据创建一个查询，其结果包含学号、姓名、性别、出生日期、专业和入学成绩6个字段，查询结果按入学成绩由高分到低分排序。操作命令如下：SELECT 学号,姓名性别,出生日期,专业,入学成绩 FROM 学生 ORDER BY 入学成绩 DESC2022-6-143* *2.2.重定向输出重定向输出(INTO)(INTO) 重定向输出(INTO)即是将所查询的结果不一定默认显示在屏幕上，而可以输出到指定的地方。其格式如下： INTO|TO FILE

21、ADDITIVE|TO PRINTER TO FILEADDITIVE：将结果输出到指定文本文件，ADDITIVE表示将结果添加到文件后面。 TO PRINTER：将查询结果送打印机输出； ： 为如下3部分内容： ARRAY：将查询结果存到指定数组名的内存变量数组中； CURSOR：将输出结果存到一个临时表(游标)，这个表的操作与其它表一样，不同的是，一旦被关闭就被删除；2022-6-144 ： 为如下3部分内容： ARRAY：将查询结果存到指定数组名的内存变量数组中； CURSOR：将输出结果存到一个临时表(游标)，这个表的操作与其它表一样，不同的是，一旦被关闭就被删除； DBF|TABLE：将结果存到一个表，如果该表已经打开，则系统自动关闭它； 2022-6-145 【例629】利用学生表中的数据创建一个查询，其结果包含学号、姓名、性别、出生日期、专业和入学成绩6个字段，结果按入学成绩由高分到低分排序。查询结果保存到一个test1.txt文件中。操作命令如下：SELECT 学号,姓名,性别,出生日期,专业,入学成绩; FROM 学生 ORDER BY 入学成绩 DESC TO; F