结构化查询语言SQL.ppt

1、第5章结构化查询语言SQL,主编：赖申江,本章学习导航,结构化查询语言SQL是一种功能齐全的数据库语言，包含数据定义功能、数据查询功能、数据操纵功能和数据控制功能。其中最主要的功能就是查询。通过本章学习，学生应掌握SQL语言的使用方法和技巧，提高知识综合运用能力，写出高质量的SQL查询命令。,本章学习要点,SQL的数据定义 ：熟练运用CREATE TABLE、ALTER TABLET和DROP TABLE语句完成表的创建、修改和删除 。 SQL的数据操纵 ：熟练运用INSERT、DELETE和UPDATE语句添加、删除和更新记录 。 SQL的数据查询 ：熟练运用SELECT语句完成简单查询、嵌

2、套查询、连接查询、分组与计算查询 。 教学课时：4课时,第5章 结构化查询语言SQL,5.1 SQL概述 结构化查询语言（Structured Query Language，简称SQL）是一种介于关系代数和关系演算之间的语言。它于1974年由Boyce和Chamberlin提出，由于具有语言简洁、方便实用、功能齐全等优点，很快得到推广和应用。随着关系数据库的流行，SQL语言最终发展成为关系数据库的标准语言和数据库领域中一个主流语言。SQL已经被ANSI（美国国家标准化组织）确定为数据库系统的工业标准，它是数据库系统的通用语言。利用SQL，用户可以用几乎相同的语句在不同的数据库系统上执行同样的操

3、作。,第5章 结构化查询语言SQL,5.1.1 SQL语言的特点 SQL语言之所以能够成为国际标准，是因为它是一个综合的、通用的、功能极强，同时又简单易学的语言。 （1）功能强大 SQL语言集数据查询、数据操纵、数据定义和数据控制功能于一体。 （2）高度非过程化 用户只要提出“做什么”，而无需指明“怎么做”，存取路径的选择以及SQL语言的操作过程由系统自动完成，不但大大减轻了用户负担，而且有利于提高数据的独立性。 （3）简单易用 SQL语言十分简洁，实现核心功能，一般只要用到下面9个命令动词，如表5-1所示。因此容易学习和掌握。 （4）有联机交互和嵌入两种使用方式 SQL语言支持两种不同的使用

4、方式。一种是联机交互使用，即用户能在终端直接输入SQL命令对数据库进行操作；另一种是嵌入式，即将SQL语句嵌入到其他高级语言中，以便程序员在设计时使用。,第5章 结构化查询语言SQL,5.1.2 SQL数据定义功能 关系数据库的基本对象是表、视图和索引。因此SQL的数据定义功能包括定义数据库、表、视图和索引，如表5-2所示。由于视图是基于表的虚表，索引是依附于表的，因此SQL通常不提供修改视图和索引的操作。 1数据库的创建和删除 关系数据库被定义为关系（表）、索引和视图的集合。在创建表、视图和索引前必须首先创建数据库。 例如，创建学生选课数据库stsc，可以用CREATEDATABASEsts

5、c语句来实现。,第5章 结构化查询语言SQL,2基本表的创建、修改和删除 用CREATEDATABASE建立的数据库是一个空的数据库，接下来应该在这个数据库中建立表。 （1）创建基本表 表的创建既可以通过表设计器或Visual FoxPro的新建表命令实现，也可以使用SQL中的相应命令创建。 命令格式：CREATE TABLE|DBF FREE（ ), ),) 功能：建立一个由指定的表结构。 说明： ：指定要创建的表的名称。 FREE：指定创建的表是一个自由表，不被添加到数据库中。 )：指定相应字段的名称、类型、宽度及小数位数。,第5章 结构化查询语言SQL,例5-1 在学生选课数据库stsc

6、中创建一个学生表student。 CREATE TABLE student (学号 c(8),姓名 c(6),性别 c(2),院系 c(8),出生日期 D,入学成绩 N(4,1) 功能：用CREATE TABLE语句创建数据库表student。该表中包含了学号、姓名、性别、院系、出生日期和入学成绩6个字段。,第5章 结构化查询语言SQL,（2）修改表结构 修改表结构命令以ALTER TABLE开头，后面根据不同的操作使用不同的命令动词。 增加字段 格式：ALTER TABLE ADD 功能：对指定的表增加一个字段。 例5-为学生student表增加一个照片字段。 ALTER TABLE stu

7、dent ADD 照片 G 删除字段 格式：ALTER TABLE DROP 功能：在指定的表中删除指定字段。 例5-删除学生student表中的照片字段。 ALTER TABLE student DROP 照片,第5章 结构化查询语言SQL, 修改字段 格式：ALTER TABLE ALTER 功能：在指定的表中修改指定字段的类型和宽度，不能修改字段名。 例5-把学生student表中的姓名字段宽度由6改为10。 ALTER TABLE student ALTER 姓名 c(10) 修改字段名 格式：ALTER TABLE RENAME TO 功能：在指定表中将字段名更名为字段名。 例5-把

8、课程表course中的课名改为课程名称。 ALTER TABLE course RENAME 课名 TO 课程名称 说明：执行一条命令只能对一个字段进行一种操作。,第5章 结构化查询语言SQL,（3）删除表 DROP TABLE语句用于删除表的定义和表中的数据、建立在表上的索引、视图等。 格式： DROP TABLE /?RECYCLE 功能：删除指定的表。 说明： /?：将显示“移去”对话框，从中可删除指定的表。 RECYCLE：将删除的表文件放到回收站，以后可以恢复。,第5章 结构化查询语言SQL,3视图的创建和删除 视图是一个虚表，是由一个或者几个基本表导出的表。它本身不独立存储在数据库

9、中，即数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在对应的表中。当基本表中的数据发生了变化，视图中对应的数据也随之改变。,第5章 结构化查询语言SQL,（1）创建视图 视图一经创建，就可以通过它来查询数据库中数据，又可以用来定义新的视图。 格式：CREATE VIEW ( ,) AS 功能：创建一个由视图名指定的视图。 用视图可以简化基本表的浏览。例如只想了解学生表中的学号、姓名和院系，可以从学生表student创建一个视图： CREATE VIEW stuyx AS SELECT 学号，姓名，院系 FROM student 其中，stuyx是视图名，省略了视图的列名，隐含是

10、SELECT查询语句中的列名。 例如，可以通过创建stu_jsj视图查看金融系的学生内容，语句如下： CREATE VIEW stu_jsj AS SELECT * FROM student WHERE 院系“金融”,第5章 结构化查询语言SQL,（2）查询视图 视图定义后，用户就可以像基本表一样对视图进行查询。 格式：SELECT ( ,) FROM （3）删除视图 格式：DROP VIEW 视图删除后，其定义的以及在它基础上再建立的其他视图将自动删除。 由于视图是从基本表中衍生出来的，所以不能进行结构修改。若要改变视图结构，则只能删除后重新定义视图。,第5章 结构化查询语言SQL,5.1.

11、3 SQL数据查询功能 数据查询是数据库的核心操作。数据查询是指根据用户的需要，从数据库中提取所需的数据。SQL提供了SELECT语句实现查询，该语句具有灵活的使用方式和丰富的功能，既可以完成相对简单的单表查询，又可以完成复杂的多表连接查询和嵌套查询。,第5章 结构化查询语言SQL,1SQL查询命令 一般格式： SELECT ALL|DISTINCT,； FROM ,； WHERE ； GROUP BY HAVING ； ORDER BYASC|DESC 功能： 根据WHERE子句的条件表达式，从FROM子句指定的基本表或视图中，找出满足条件的记录；再按SELECT子句中的目标表达式，选出记录

12、中的属性值，形成结果表。如果有GROUP BY子句，则将结果按分组列名的值进行分组，该属性列值相等的记录为一个组。如果GROUP BY子句还带有HAVING短语，则只有满足指定条件的组才能输出。如果有ORDER BY子句，则结果还要按排序字段的值进行升序或降序排列。,第5章 结构化查询语言SQL,说明： （1）在使用SQL查询语句时，无论数据库是否打开，FROM子句中指定的表所在数据库将自动打开。 （2）由于FoxPro系统中将Enter键解释为一条语句的结束，在整个语句结束前只能连续写，不能用Enter键来换行。在SQL中，为了使查询结构清晰，往往把SELECT子句、FROM子句及各层嵌套以

13、分号作为分隔符，表示下一行为本行的后续。 为了便于理解，还是以学生选课数据库stsc为例说明SELECT语句的基本用法。学生选课数据库stsc由三个基本表组成： 学生表student（学号、姓名、性别、出生日期、院系、入学成绩）； 成绩表score（学号、课程编号、成绩）； 课程表course（课程编号、课程名称、学分）。,第5章 结构化查询语言SQL,2简单查询 简单查询是基于单表的查询，由SELECT和FROM短语构成无条件查询或者由SELECT、FROM和WHERE短语构成有条件查询。 （1）选择表中的若干列 例5-6 查询学生表student中所有院系。 SELECT 院系 FROM

14、student 该结果中有重复值，若要去掉重复值只需加上DISTINCT短语： SELECT DISTINCT 院系 FROM student 例5-7 查询全体学生的详细记录。 SELECT * FROM student 其中的“*”表示要查询所有的列。 例5-8 查询全体学生情况，并将结果按入学成绩升序排序。 SELECT * FROM student ORDER BY 入学成绩 ASC 结果表中的记录自动按入学成绩的升序排序。,第5章 结构化查询语言SQL,（2）用WHERE子句选择满足条件的记录 例5-9 查找中文系全体学生的名单。 SELECT 姓名 FROM student WHE

15、RE 院系=中文 例5-10 查找缺少成绩的学生学号和课程号。 SELECT 学号,课程编号 FROM score WHERE 成绩 IS NULL 例5-11 查找姓“王”的所有学生姓名和院系。 SELECT 姓名,院系 FROM student WHERE 姓名 LIKE 王%,第5章 结构化查询语言SQL,3连接查询 若查询涉及到两个或两个以上的表，就要用到连接查询。 （1）连接 由于SQL的高度非过程化，用户只需要在FROM子句中指出要用到的表名，在WHERE中指出连接条件，连接过程将由系统自动完成。 连接条件 用来连接两个表的条件称为连接条件，其一般格式为： . . 此外，连接条件还

16、可以用下面的形式： . BETWEEN . AND . 连接条件中的列名称为连接字段，条件中的各连接字段必须是可比的。 等值连接和非等值连接 当连接运算符为“=”时，称为等值连接。使用其他运算符称为非等值连接。,第5章 结构化查询语言SQL,例5-12 查询选修课程编号为“9001”的学生学号和姓名。 学生的姓名在student表中，学生选课信息在score成绩表中，所以本查询实际上同时student和score两个表中的数据。这两个表之间的联系是通过两个表中都有“学号”字段实现的。要查询学生及其选修课程的情况，就必须将这两个表中学号相同的记录连接起来。这是一个等值连接。SQL语句为： SEL

17、ECT student.学号,姓名 FROM student, score ; WHERE student.学号=score.学号 AND 课程编号=9001 注意：student表与score表中均包含学号字段，访问时要加前缀。,第5章 结构化查询语言SQL,4嵌套查询 在SQL语言中，一个SELECTFROMWHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HANVIG短语中的查询称为嵌套查询。嵌套查询使用户可以用多个简单查询构造复杂的查询，从而增强SQL语言的查询能力。 例5-13 用嵌套查询实现例5-12的语句是： SELECT 学号,姓名 FROM stu

18、dent ; WHERE 学号 IN(SELECT 学号 FROM score WHERE 课程编号=9001) 在本例中，下层查询块SELECT学号FROM score WHERE课程编号“9001”是嵌套在查询块SELECT学号,姓名 FROM student WHERE学号 IN中的。上层查询块又称为“外层查询”或“父查询”，下层查询块称为“内层查询”或“子查询”。一个子查询还可以嵌套其他子查询。,第5章 结构化查询语言SQL,5计算查询 SQL提供了称为库函数的常用统计函数，这些库函数增强了查询功能，进一步方便了用户。这些库函数及其功能如下： COUNT()：对指定字段进行计数 SUM

19、()：求指定字段值的总和（该字段必须为数值） AVG()：求指定字段值的平均值（该字段必须为数值） MAX()：求指定字段中的最大值 MIN()：求指定字段中的最小值 以上函数不可以嵌套使用。在使用库函数查询时，常用AS来指定列名。,第5章 结构化查询语言SQL,例5-14 查询课程总数。 SELECT COUNT(*)AS 课程总数 FROM course 在course表中课程编号的值唯一，所以统计课程总数就是统计表中的记录数。 例5-15 查询选修了课程学生人数。 SELECT COUNT(DISTINCT 学号)AS 选课人数 FROM score 由于每个学生选课不止一门课，scor

20、e表中一个学生可能有多个记录，因此在COUNT函数中用DISTINCT去掉重复记录。 例5-16 统计各课程选修的学生人数。 SELECT 课程编号,COUNT(学号)AS 学生人数 FROM score GROUP BY 课程编号 本例对score按课程编号分组，对每一组用函数COUNT求出学生人数。,第5章 结构化查询语言SQL,6查询结果输出 ）输出部分结果 在SELECT语句中常使用TOP n PERCENT短语来显示满足条件的前几条记录。不带PERCENT参数时，n是132767之间的整数，说明显示前n条记录；使用PERCENT参数时，n是0.0199.99之间的实数，说明显示查询结

21、果中前百分之多少的记录。 TOP短语要与ORDER BY短语同时使用才有效。 例5-17 查询输出入学成绩在前3名的学生 SELECT * TOP 3 FROM student ORDER BY 入学成绩 DESC 例5-18 查询输出入学成绩在前3%的学生 SELECT * TOP 3 PERCENT FROM student ORDER BY 入学成绩 DESC ）输出去向 在SELECT语句中INTO或TO短语，可以指定查询结果的输出去向。,第5章 结构化查询语言SQL,例5-19 将所有女同学的信息保存于临时表TEMP中。 SELECT * FROM student WHERE 性别=

22、女 INTO CURSOR TEMP 例5-20 将入学成绩大于450分的学生信息保存于高分.DBF表中。 SELECT * FROM student WHERE 入学成绩450 INTO DBF 高分 例5-21 将中文系的学生信息存放到文件名为“中文”的文本文件中。 SELECT * FROM student WHERE 院系=中文 TO FILE 中文,第5章 结构化查询语言SQL,5.1.4 SQL操纵功能 SQL的数据操作功能是指对已经存在的数据表进行记录的插入、删除和修改操作。SQL的数据操作包括三个语句：INSERT(插入记录)、DELETE(删除记录)和UPDATE(修改记录)

23、。,第5章 结构化查询语言SQL,1插入数据 （1）命令格式： INSERT INTO(字段名，字段名)VALUES(表达式1，表达式2) 功能:在表尾追加一条包含指定字段值的记录。 例5-22 给成绩表添加一条选课记录。 INSERT INTO score (学号,课程编号,成绩) VALUES (03036003,001,89) 说明： 命令中的各表达式和各字段之间相对应的，其数据类型应相同。 若插入命令中表的每一个字段都有具体的值，那么字段名列表可以省略。若只给出部分字段的值，那么必须在命令中列出对应的字段名。 如上列的命令可改为： INSERT INTO score VALUES (“

24、03036003”,”001”,89) （2）命令格式2： INSERT INTO FROM ARRAY| 功能：从数组或内存变量中导入记录。,第5章 结构化查询语言SQL,2删除数据 命令格式： DELETE FROMWHERE 功能：从指定的表中对满足条件的那些记录作删除标记。 说明： 该命令执行的是逻辑删除，若要彻底删除记录，还需要使用PACK命令。 如果省略条件子句，表示删除表中所有记录，但是该表的结构仍然存在。 例5-23 从课程表中删除课程号为”001”的记录。 DELETE FROM course WHERE 课程编号=001。,第5章 结构化查询语言SQL,3修改数据 修改数据

25、是指修改指定表中满足条件的记录。 命令格式： UPDATE SET =,= WHERE 功能：按SET子句中的表达式修改记录的相应的字段值。如果省略条件，表示表中所有记录都要修改，否则仅修改满足条件的部分记录。 例5-24 将学生表中学号为”03036003”的学生姓名改为“王平”。 UPDATE student SET 姓名=王平 WHERE 学号=03036003 例5-25 将所有学生的入学成绩加上5分。 UPDATE student SET 入学成绩=入学成绩+5,第5章 结构化查询语言SQL,5.2 用SQL进行表的基本操作案例 案例说明 本案例要求运用SQL语句的定义功能，创建“学

26、生-成绩-课程”数据库Stsc，然后在该数据库中创建表，并建立表间联系。基本要求如下： （1）用SQL语句创建数据库stsc。 （2）在该数据库下创建3张数据库表，分别为：学生表student、成绩表score、课程表course。 （3）设置学生表的学号为主索引、成绩表的学号和课程编号为候选索引、课程表的课程编号为主索引。 （4）建立学生表和成绩表、课程表和成绩表一对多关联。 （5）设置数据库表的完整约束性条件。 （6）向数据库表中添加记录。,第5章 结构化查询语言SQL,5.2.1 操作步骤 （1）创建数据库stsc （2）创建数据库表 在命令窗口中输入CREATE TABLE命令，分别建

27、立学生表student、成绩表score、课程表couse，并要求对学生表中的“学号”和课程表中的“课程编号”字段建立主索引，对成绩表中的“学号”和“课程编号”字段建立候选索引。在新建成绩表（score）命令中用REFERENCES子句建立了该表与其他两个表的关联。 （3）修改表结构 （4）给成绩表添加一条记录或从数组中导入记录。,第5章 结构化查询语言SQL,5.2.2 本节知识点 1完整性约束条件 在案例中用到了SQL数据定义功能中的完整性约束条件的使用。 2使用SQL插入命令 当表中定义了主索引或候选索引，相应的字段设置成不能为空时，Visuai FoxPro的INSERT或者APPEN

28、D命令就无法使用了，因此这里使用SQL的插入命令。,第5章 结构化查询语言SQL,5.3 用SQL查询命令查询数据案例 案例说明 本案例要求在“学生-成绩-课程”数据库基础上，运用SQL查询命令，对stsc数据库中数据实现单表的简单查询、多表的连接查询和复杂的嵌套查询，灵活地运用数据库内的数据为实际应用服务。基本要求如下： （1）用SQL的查询命令有选择地显示表中的部分列。 （2）利用SQL查询功能实现计算查询和分组计算查询。 （3）用SELECT命令进行多表的连接查询。 （4）用嵌套查询构造复杂的查询。,第5章 结构化查询语言SQL,5.3.1 操作步骤 1用简单查询实现表中部分列和记录的显

29、示。 （1）查询全体学生的学号和姓名。 在命令窗口中键入下列命令： SELECT 学号,姓名 FROM student （2）查询全体学生的姓名和年龄。 由于表中没有直接的年龄字段，因此可以在SQL查询命令中运用表达式来实现。在命令窗口中键入下列命令： SELECT 姓名,year(2003-09-01)-year(出生日期) as 年龄 FROM student,第5章 结构化查询语言SQL,（3）查询学校的院系名称。 由于通过学生表可以看到学校的院系名单，但是会出现院系名重复，因此用DISTINCT子句去掉重复。命令如下： SELECT 院系 DISTINCT FROM student （

30、4）查找入学成绩在460分至500分之间的学生信息，并按入学成绩降序排序。 在命令窗口中输入下列命令： SELECT * FROM student ; WHERE 入学成绩 BETWEEN 460 AND 500; ORDER BY 入学成绩 DESC （5）查找金融系和建筑系学生的姓名和出生日期。命令如下： SELECT 姓名,出生日期,院系 FROM student; WHERE 院系 in(金融 ,建筑),第5章 结构化查询语言SQL,2利用SQL查询功能实现实现计算查询和分组计算查询。 （1）求女生的人数、平均入学成绩、往常入学成绩最高分和入学成绩最低分。在SQL中通过计算查询可以直接

31、对查询的结果进行计算。命令如下： SELECT COUNT(*) AS 女生人数,AVG(入学成绩) AS 平均入学成绩,; MAX(入学成绩) AS 最高分入学成绩最高分,MIN(入学成绩) AS 入学成绩最低分; FROM student WHERE 性别=女 （2）查询各门课程的平均成绩。 要统计课程的平均分，需要用查询命令中GROUP子句将课程分组，然后计算平均分。命令如下： SELECT 课程编号,AVG(成绩) AS 课程平均分 FROM score GROUP BY 课程编号 （3）查询课程的平均成绩高于70分的课程号和平均成绩. 在分组计算的结果上再进行筛选，可以用HAVING

32、子句实现。HAVING子句总是跟在GROUP BY后面的，不可单独使用。查询命令如下： SELECT 课程编号,AVG(成绩) AS 课程平均分； FROM score GROUP BY 课程编号 HAVING AVG(成绩)=70,第5章 结构化查询语言SQL,3用SELECT命令进行多表查询。 在实际应用中，经常碰到同时要用到两个表或更多表的数据的问题，用SELECT的连接查询就能解决这样的问题。 （1）查询每个学生的选课情况。 学生信息在student表中，选课信息在score表中，所以本查询涉及student与score两个表中的数据。这两个表的联系是通过两个表中都具有的字段“学号”实

33、现的。SQL语句为： SELECT student.*,score.课程编号,score.成绩； FROM student,score WHERE student.学号=score.学号 （2）查询所有学生的平均成绩。 本题需要从score表中查询并统计各个学生的平均成绩，然后按学号进行分组。查询命令： SELECT student.学号,student.姓名,AVG(score.成绩) AS 平均成绩 ; FROM student,score WHERE student.学号=score.学号 GROUP BY score.学号,第5章 结构化查询语言SQL,（3）查询平均成绩在前三名的学生的成绩。 本题不仅要查询出所有学生的平均成绩，而且还要按平均成绩进行降序排序，最后只输出前三名的学生的信息。查询命令为： SELECT student.学号,student.姓名,AVG(score.成绩) AS 平均成绩 ; TOP 3 FROM student,score WHERE student.学号=score.学号; GROUP BY score.学号 ORDER BY 平均成绩 DESC,第5章 结构化查询语言SQL,4用嵌套查询构造新的