结构化查询语言SQL

1、数据库系统及应用数据库系统及应用第5章结构化查询语言SQL 主编：赖申江数据库系统及应用数据库系统及应用本章学习导航本章学习导航v 结构化查询语言结构化查询语言SQLSQL是一种功能是一种功能齐全的数据库语言，包含数据定义齐全的数据库语言，包含数据定义功能、数据查询功能、数据操纵功功能、数据查询功能、数据操纵功能和数据控制功能。其中最主要的能和数据控制功能。其中最主要的功能就是查询。通过本章学习，学功能就是查询。通过本章学习，学生应掌握生应掌握SQLSQL语言的使用方法和技巧，语言的使用方法和技巧，提高知识综合运用能力，写出高质提高知识综合运用能力，写出高质量的量的SQLSQL查询命令。查询命

2、令。 数据库系统及应用数据库系统及应用本章学习要点本章学习要点lSQL的数据定义 ：熟练运用CREATE TABLE、ALTER TABLET和DROP TABLE语句完成表的创建、修改和删除 。l SQL的数据操纵 ：熟练运用INSERT、DELETE和UPDATE语句添加、删除和更新记录 。l SQL的数据查询 ：熟练运用SELECT语句完成简单查询、嵌套查询、连接查询、分组与计算查询 。 教学课时：教学课时：4课时课时数据库系统及应用数据库系统及应用第5章 结构化查询语言SQL v5.1 SQL5.1 SQL概述概述v结构化查询语言（结构化查询语言（Structured Query La

3、nguageStructured Query Language，简称简称SQLSQL）是一种介于关系代数和关系演算之间的）是一种介于关系代数和关系演算之间的语言。它于语言。它于19741974年由年由BoyceBoyce和和ChamberlinChamberlin提出，由提出，由于具有语言简洁、方便实用、功能齐全等优点，于具有语言简洁、方便实用、功能齐全等优点，很快得到推广和应用。随着关系数据库的流行，很快得到推广和应用。随着关系数据库的流行，SQLSQL语言最终发展成为关系数据库的标准语言和数语言最终发展成为关系数据库的标准语言和数据库领域中一个主流语言。据库领域中一个主流语言。SQLSQL

4、已经被已经被ANSIANSI（美国（美国国家标准化组织）确定为数据库系统的工业标准，国家标准化组织）确定为数据库系统的工业标准，它是数据库系统的通用语言。利用它是数据库系统的通用语言。利用SQLSQL，用户可以，用户可以用几乎相同的语句在不同的数据库系统上执行同用几乎相同的语句在不同的数据库系统上执行同样的操作。样的操作。数据库系统及应用数据库系统及应用第5章 结构化查询语言SQL v 5.1.1 SQL5.1.1 SQL语言的特点语言的特点v SQLSQL语言之所以能够成为国际标准，是因为它是一个综合的、通用的、语言之所以能够成为国际标准，是因为它是一个综合的、通用的、功能极强，同时又简单易

5、学的语言。功能极强，同时又简单易学的语言。v （1 1）功能强大）功能强大v SQLSQL语言集数据查询、数据操纵、数据定义和数据控制功能于一体。语言集数据查询、数据操纵、数据定义和数据控制功能于一体。v （2 2）高度非过程化）高度非过程化v 用户只要提出用户只要提出“做什么做什么”，而无需指明，而无需指明“怎么做怎么做”，存取路径的选择，存取路径的选择以及以及SQLSQL语言的操作过程由系统自动完成，不但大大减轻了用户负担，语言的操作过程由系统自动完成，不但大大减轻了用户负担，而且有利于提高数据的独立性。而且有利于提高数据的独立性。v （3 3）简单易用）简单易用v SQLSQL语言十分简

6、洁，实现核心功能，一般只要用到下面语言十分简洁，实现核心功能，一般只要用到下面9 9个命令动词，个命令动词，如表如表5-15-1所示。因此容易学习和掌握。所示。因此容易学习和掌握。v （4 4）有联机交互和嵌入两种使用方式）有联机交互和嵌入两种使用方式v SQLSQL语言支持两种不同的使用方式。一种是联机交互使用，即用户能在语言支持两种不同的使用方式。一种是联机交互使用，即用户能在终端直接输入终端直接输入SQLSQL命令对数据库进行操作；另一种是嵌入式，即将命令对数据库进行操作；另一种是嵌入式，即将SQLSQL语句嵌入到其他高级语言中，以便程序员在设计时使用。语句嵌入到其他高级语言中，以便程序

7、员在设计时使用。数据库系统及应用数据库系统及应用第5章 结构化查询语言SQL v 5.1.2 SQL5.1.2 SQL数据定义功能数据定义功能v 关系数据库的基本对象是表、视图和索引。因此关系数据库的基本对象是表、视图和索引。因此SQLSQL的数的数据定义功能包括定义数据库、表、视图和索引，如表据定义功能包括定义数据库、表、视图和索引，如表5-25-2所示。由于视图是基于表的虚表，索引是依附于表的，因所示。由于视图是基于表的虚表，索引是依附于表的，因此此SQLSQL通常不提供修改视图和索引的操作。通常不提供修改视图和索引的操作。v 1 1数据库的创建和删除数据库的创建和删除v 关系数据库被定义

8、为关系（表）、索引和视图的集合。在关系数据库被定义为关系（表）、索引和视图的集合。在创建表、视图和索引前必须首先创建数据库。创建表、视图和索引前必须首先创建数据库。v 例如，创建学生选课数据库例如，创建学生选课数据库stscstsc，可以用，可以用CREATECREATEDATABASEDATABASEstscstsc语句来实现。语句来实现。数据库系统及应用数据库系统及应用第5章 结构化查询语言SQL v 2 2基本表的创建、修改和删除基本表的创建、修改和删除v 用用CREATECREATEDATABASEDATABASE建立的数据库是一个空的数据库，接下来应该在建立的数据库是一个空的数据库，

9、接下来应该在这个数据库中建立表。这个数据库中建立表。v （1 1）创建基本表）创建基本表v 表的创建既可以通过表设计器或表的创建既可以通过表设计器或Visual FoxProVisual FoxPro的新建表命令实现，也的新建表命令实现，也可以使用可以使用SQLSQL中的相应命令创建。中的相应命令创建。v 命令格式：命令格式：CREATE TABLE|DBF CREATE TABLE|DBF FREE FREE（ (宽度宽度,),), (宽度宽度,),),)v 功能：建立一个由功能：建立一个由 指定的表结构。指定的表结构。v 说明：说明：v ：指定要创建的表的名称。：指定要创建的表的名称。v

10、FREEFREE：指定创建的表是一个自由表，不被添加到数据库中。：指定创建的表是一个自由表，不被添加到数据库中。v (宽度宽度,)：指定相应字段的名：指定相应字段的名称、类型、宽度及小数位数。称、类型、宽度及小数位数。 数据库系统及应用数据库系统及应用第5章 结构化查询语言SQL v例例5-1 5-1 在学生选课数据库在学生选课数据库stscstsc中创建一个学生表中创建一个学生表studentstudent。v CREATE TABLE student (CREATE TABLE student (学号学号 c(8),c(8),姓名姓名 c(6),c(6),性别性别 c(2),c(2),院系

11、院系 c(8),c(8),出生日期出生日期 D,D,入学成绩入学成绩 N(4,1)N(4,1)v功能：用功能：用CREATE TABLECREATE TABLE语句创建数据库表语句创建数据库表studentstudent。该表中包含了学号、姓名、性别、院系、出生日该表中包含了学号、姓名、性别、院系、出生日期和入学成绩期和入学成绩6 6个字段。个字段。数据库系统及应用数据库系统及应用第5章 结构化查询语言SQL v （2 2）修改表结构）修改表结构v 修改表结构命令以修改表结构命令以ALTER TABLEALTER TABLE开头，后面根据不同的操作使用不同的开头，后面根据不同的操作使用不同的命

12、令动词。命令动词。v 增加字段增加字段v 格式：格式：ALTER TABLE ALTER TABLE ADD ADD )v 功能：对指定的表增加一个字段。功能：对指定的表增加一个字段。v 例例5-5-为学生为学生studentstudent表增加一个照片字段。表增加一个照片字段。v ALTER TABLE student ADD ALTER TABLE student ADD 照片照片 G Gv 删除字段删除字段v 格式：格式：ALTER TABLE ALTER TABLE DROP DROP v 功能：在指定的表中删除指定字段。功能：在指定的表中删除指定字段。v 例例5-5-删除学生删除学生

13、studentstudent表中的照片字段。表中的照片字段。v ALTER TABLE student DROP ALTER TABLE student DROP 照片照片数据库系统及应用数据库系统及应用第5章 结构化查询语言SQL v 修改字段修改字段v 格式：格式：ALTER TABLE ALTER TABLE ALTER ALTER ()v 功能：在指定的表中修改指定字段的类型和宽度，不能修功能：在指定的表中修改指定字段的类型和宽度，不能修改字段名。改字段名。v 例例5-5-把学生把学生studentstudent表中的姓名字段宽度由表中的姓名字段宽度由6 6改为改为1010。v ALT

14、ER TABLE student ALTER ALTER TABLE student ALTER 姓名姓名 c(10)c(10)v 修改字段名修改字段名v 格式：格式：ALTER TABLE ALTER TABLE RENAME RENAME TO TO v 功能：在指定表中将字段名更名为字段名。功能：在指定表中将字段名更名为字段名。v 例例5-5-把课程表把课程表coursecourse中的课名改为课程名称。中的课名改为课程名称。v ALTER TABLE course RENAME ALTER TABLE course RENAME 课名课名 TO TO 课程名称课程名称v 说明：执行一条

15、命令只能对一个字段进行一种操作。说明：执行一条命令只能对一个字段进行一种操作。 数据库系统及应用数据库系统及应用第5章 结构化查询语言SQL v（3 3）删除表）删除表vDROP TABLEDROP TABLE语句用于删除表的定义和表中的数据、语句用于删除表的定义和表中的数据、建立在表上的索引、视图等。建立在表上的索引、视图等。v格式：格式： DROP TABLE DROP TABLE /?RECYCLE/?RECYCLEv功能：删除指定的表。功能：删除指定的表。v说明：说明：v/?/?：将显示：将显示“移去移去”对话框，从中可删除指定对话框，从中可删除指定的表。的表。vRECYCLERECY

16、CLE：将删除的表文件放到回收站，以后可：将删除的表文件放到回收站，以后可以恢复。以恢复。数据库系统及应用数据库系统及应用第5章 结构化查询语言SQL v3 3视图的创建和删除视图的创建和删除v视图是一个虚表，是由一个或者几个基本表导出视图是一个虚表，是由一个或者几个基本表导出的表。它本身不独立存储在数据库中，即数据库的表。它本身不独立存储在数据库中，即数据库中只存放视图的定义而不存放视图对应的数据，中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在对应的表中。当基本表中的数这些数据仍存放在对应的表中。当基本表中的数据发生了变化，视图中对应的数据也随之改变。据发生了变化，视图中对应的数

17、据也随之改变。数据库系统及应用数据库系统及应用第5章 结构化查询语言SQL v （1 1）创建视图）创建视图v 视图一经创建，就可以通过它来查询数据库中数据，又可以用来定义视图一经创建，就可以通过它来查询数据库中数据，又可以用来定义新的视图。新的视图。v 格式：格式：CREATE VIEW CREATE VIEW ( ( , ,2) AS ) AS SELECT v 功能：创建一个由视图名指定的视图。功能：创建一个由视图名指定的视图。v 用视图可以简化基本表的浏览。例如只想了解学生表中的学号、姓名用视图可以简化基本表的浏览。例如只想了解学生表中的学号、姓名和院系，可以从学生表和院系，可以从学生

18、表studentstudent创建一个视图：创建一个视图：v CREATE VIEW stuyx AS SELECT CREATE VIEW stuyx AS SELECT 学号，姓名，院系学号，姓名，院系 FROM studentFROM studentv 其中，其中，stuyxstuyx是视图名，省略了视图的列名，隐含是是视图名，省略了视图的列名，隐含是SELECTSELECT查询语句中查询语句中的列名。的列名。v 例如，可以通过创建例如，可以通过创建stu_jsjstu_jsj视图查看金融系的学生内容，语句如下：视图查看金融系的学生内容，语句如下：v CREATE VIEW stu_js

19、j AS SELECT CREATE VIEW stu_jsj AS SELECT * * FROM student WHERE FROM student WHERE 院院系系“金融金融”数据库系统及应用数据库系统及应用第5章 结构化查询语言SQL v （2 2）查询视图）查询视图v 视图定义后，用户就可以像基本表一样对视图进行查询。视图定义后，用户就可以像基本表一样对视图进行查询。v 格式：格式：SELECT (SELECT ( , ,2) FROM ) FROM v （3 3）删除视图）删除视图v 格式：格式：DROP VIEW DROP VIEW v 视图删除后，其定义的以及在它基础上再

20、建立的其他视图视图删除后，其定义的以及在它基础上再建立的其他视图将自动删除。将自动删除。v 由于视图是从基本表中衍生出来的，所以不能进行结构修由于视图是从基本表中衍生出来的，所以不能进行结构修改。若要改变视图结构，则只能删除后重新定义视图。改。若要改变视图结构，则只能删除后重新定义视图。数据库系统及应用数据库系统及应用第5章 结构化查询语言SQL v5.1.3 SQL5.1.3 SQL数据查询功能数据查询功能v数据查询是数据库的核心操作。数据查询是指根数据查询是数据库的核心操作。数据查询是指根据用户的需要，从数据库中提取所需的数据。据用户的需要，从数据库中提取所需的数据。SQLSQL提供了提供

21、了SELECTSELECT语句实现查询，该语句具有灵活的语句实现查询，该语句具有灵活的使用方式和丰富的功能，既可以完成相对简单的使用方式和丰富的功能，既可以完成相对简单的单表查询，又可以完成复杂的多表连接查询和嵌单表查询，又可以完成复杂的多表连接查询和嵌套查询。套查询。 数据库系统及应用数据库系统及应用第5章 结构化查询语言SQL v 1 1SQLSQL查询命令查询命令v 一般格式：一般格式：v SELECT ALL|DISTINCTSELECT ALL|DISTINCT,；v FROM FROM ,；v WHERE WHERE ；v GROUP BY GROUP BY HAVING HAVI

22、NG ；v ORDER BYORDER BYASC|DESCASC|DESCv 功能：功能：v 根据根据WHEREWHERE子句的条件表达式，从子句的条件表达式，从FROMFROM子句指定的基本表或视图中，找子句指定的基本表或视图中，找出满足条件的记录；再按出满足条件的记录；再按SELECTSELECT子句中的目标表达式，选出记录中的子句中的目标表达式，选出记录中的属性值，形成结果表。如果有属性值，形成结果表。如果有GROUP BYGROUP BY子句，则将结果按分组列名的子句，则将结果按分组列名的值进行分组，该属性列值相等的记录为一个组。如果值进行分组，该属性列值相等的记录为一个组。如果GR

23、OUP BYGROUP BY子句还子句还带有带有HAVINGHAVING短语，则只有满足指定条件的组才能输出。如果有短语，则只有满足指定条件的组才能输出。如果有ORDER ORDER BYBY子句，则结果还要按排序字段的值进行升序或降序排列。子句，则结果还要按排序字段的值进行升序或降序排列。数据库系统及应用数据库系统及应用第5章 结构化查询语言SQL v 说明：说明：v （1 1）在使用）在使用SQLSQL查询语句时，无论数据库是否打开，查询语句时，无论数据库是否打开，FROMFROM子句中指定的表所在数据库将自动打开。子句中指定的表所在数据库将自动打开。v （2 2）由于）由于FoxProF

24、oxPro系统中将系统中将EnterEnter键解释为一条语句的结束，键解释为一条语句的结束，在整个语句结束前只能连续写，不能用在整个语句结束前只能连续写，不能用EnterEnter键来换行。键来换行。在在SQLSQL中，为了使查询结构清晰，往往把中，为了使查询结构清晰，往往把SELECTSELECT子句、子句、FROMFROM子句及各层嵌套以分号作为分隔符，表示下一行为本子句及各层嵌套以分号作为分隔符，表示下一行为本行的后续。行的后续。v 为了便于理解，还是以学生选课数据库为了便于理解，还是以学生选课数据库stscstsc为例说明为例说明SELECTSELECT语句的基本用法。学生选课数据库

25、语句的基本用法。学生选课数据库stscstsc由三个基本由三个基本表组成：表组成：v 学生表学生表studentstudent（学号学号、姓名、性别、出生日期、院系、姓名、性别、出生日期、院系、入学成绩）；入学成绩）；v 成绩表成绩表scorescore（学号学号、课程编号课程编号、成绩）；、成绩）；v 课程表课程表coursecourse（课程编号课程编号、课程名称、学分）。、课程名称、学分）。 数据库系统及应用数据库系统及应用第5章 结构化查询语言SQL v 2 2简单查询简单查询v 简单查询是基于单表的查询，由简单查询是基于单表的查询，由SELECTSELECT和和FROMFROM短语构

26、成无条件查询或短语构成无条件查询或者由者由SELECTSELECT、FROMFROM和和WHEREWHERE短语构成有条件查询。短语构成有条件查询。v （1 1）选择表中的若干列）选择表中的若干列v 例例5-6 5-6 查询学生表查询学生表studentstudent中所有院系。中所有院系。v SELECT SELECT 院系院系 FROM studentFROM studentv 该结果中有重复值，若要去掉重复值只需加上该结果中有重复值，若要去掉重复值只需加上DISTINCTDISTINCT短语：短语：v SELECT DISTINCT SELECT DISTINCT 院系院系 FROM s

27、tudentFROM studentv 例例5-7 5-7 查询全体学生的详细记录。查询全体学生的详细记录。v SELECT SELECT * * FROM student FROM studentv 其中的其中的“* *”表示要查询所有的列。表示要查询所有的列。v 例例5-8 5-8 查询全体学生情况，并将结果按入学成绩升序排序。查询全体学生情况，并将结果按入学成绩升序排序。v SELECT SELECT * * FROM student ORDER BY FROM student ORDER BY 入学成绩入学成绩 ASCASCv 结果表中的记录自动按入学成绩的升序排序。结果表中的记录自动

28、按入学成绩的升序排序。数据库系统及应用数据库系统及应用第5章 结构化查询语言SQL v（2 2）用）用WHEREWHERE子句选择满足条件的记录子句选择满足条件的记录v例例5-9 5-9 查找中文系全体学生的名单。查找中文系全体学生的名单。vSELECT SELECT 姓名姓名 FROM student WHERE FROM student WHERE 院系院系=中文中文 v例例5-10 5-10 查找缺少成绩的学生学号和课程号。查找缺少成绩的学生学号和课程号。vSELECT SELECT 学号学号, ,课程编号课程编号 FROM score WHERE FROM score WHERE 成绩

29、成绩 IS NULLIS NULLv例例5-11 5-11 查找姓查找姓“王王”的所有学生姓名和院系。的所有学生姓名和院系。vSELECT SELECT 姓名姓名, ,院系院系 FROM student WHERE FROM student WHERE 姓名姓名 LIKE LIKE 王王% % 数据库系统及应用数据库系统及应用第5章 结构化查询语言SQL v 3 3连接查询连接查询v 若查询涉及到两个或两个以上的表，就要用到连接查询。若查询涉及到两个或两个以上的表，就要用到连接查询。v （1 1）连接）连接v 由于由于SQLSQL的高度非过程化，用户只需要在的高度非过程化，用户只需要在FROM

30、FROM子句中指出要用到的表名，子句中指出要用到的表名，在在WHEREWHERE中指出连接条件，连接过程将由系统自动完成。中指出连接条件，连接过程将由系统自动完成。v 连接条件连接条件v 用来连接两个表的条件称为连接条件，其一般格式为：用来连接两个表的条件称为连接条件，其一般格式为：v . .2v 此外，连接条件还可以用下面的形式：此外，连接条件还可以用下面的形式：v . BETWEEN BETWEEN . AND AND .3v 连接条件中的列名称为连接字段，条件中的各连接字段必须是可比的。连接条件中的列名称为连接字段，条件中的各连接字段必须是可比的。v 等值连接和非等值连接等值连接和非等值

31、连接v 当连接运算符为当连接运算符为“= =”时，称为等值连接。使用其他运算符称为非等值时，称为等值连接。使用其他运算符称为非等值连接。连接。数据库系统及应用数据库系统及应用第5章 结构化查询语言SQL v 例例5-12 5-12 查询选修课程编号为查询选修课程编号为“90019001”的学生学号和姓名。的学生学号和姓名。v 学生的姓名在学生的姓名在studentstudent表中，学生选课信息在表中，学生选课信息在scorescore成绩表成绩表中，所以本查询实际上同时中，所以本查询实际上同时studentstudent和和scorescore两个表中的数两个表中的数据。这两个表之间的联系是

32、通过两个表中都有据。这两个表之间的联系是通过两个表中都有“学号学号”字字段实现的。要查询学生及其选修课程的情况，就必须将这段实现的。要查询学生及其选修课程的情况，就必须将这两个表中学号相同的记录连接起来。这是一个等值连接。两个表中学号相同的记录连接起来。这是一个等值连接。SQLSQL语句为：语句为：v SELECT student.SELECT student.学号学号, ,姓名姓名 FROM student, score ;FROM student, score ;v WHERE student.WHERE student.学号学号=score.=score.学号学号 AND AND 课程编

33、号课程编号=9001=9001v 注意：注意：studentstudent表与表与scorescore表中均包含学号字段，访问时要表中均包含学号字段，访问时要加前缀。加前缀。数据库系统及应用数据库系统及应用第5章 结构化查询语言SQL v 4 4嵌套查询嵌套查询v 在在SQLSQL语言中，一个语言中，一个SELECTSELECTFROMFROMWHEREWHERE语句称为一个查语句称为一个查询块。将一个查询块嵌套在另一个查询块的询块。将一个查询块嵌套在另一个查询块的WHEREWHERE子句或子句或HANVIGHANVIG短语中的查询称为嵌套查询。嵌套查询使用户可以短语中的查询称为嵌套查询。嵌套

34、查询使用户可以用多个简单查询构造复杂的查询，从而增强用多个简单查询构造复杂的查询，从而增强SQLSQL语言的查语言的查询能力。询能力。v 例例5-13 5-13 用嵌套查询实现例用嵌套查询实现例5-125-12的语句是：的语句是：v SELECT SELECT 学号学号, ,姓名姓名 FROM student ;FROM student ;v WHERE WHERE 学号学号 IN(SELECT IN(SELECT 学号学号 FROM score WHERE FROM score WHERE 课程编课程编号号=9001)=9001)v 在本例中，下层查询块在本例中，下层查询块SELECTSEL

35、ECT学号学号FROM score WHEREFROM score WHERE课程课程编号编号“90019001”是嵌套在查询块是嵌套在查询块SELECTSELECT学号学号, ,姓名姓名 FROM FROM student WHEREstudent WHERE学号学号 ININ中的。上层查询块又称为中的。上层查询块又称为“外层查外层查询询”或或“父查询父查询”，下层查询块称为，下层查询块称为“内层查询内层查询”或或“子子查询查询”。一个子查询还可以嵌套其他子查询。一个子查询还可以嵌套其他子查询。数据库系统及应用数据库系统及应用第5章 结构化查询语言SQL v 5 5计算查询计算查询v SQL

36、SQL提供了称为库函数的常用统计函数，这些库函数增强提供了称为库函数的常用统计函数，这些库函数增强了查询功能，进一步方便了用户。这些库函数及其功能如了查询功能，进一步方便了用户。这些库函数及其功能如下：下：v COUNT(COUNT()：对指定字段进行计数：对指定字段进行计数v SUM(SUM()：求指定字段值的总和（该字段必须为：求指定字段值的总和（该字段必须为数值）数值）v AVG(AVG()：求指定字段值的平均值（该字段必须：求指定字段值的平均值（该字段必须为数值）为数值）v MAX(MAX()：求指定字段中的最大值：求指定字段中的最大值v MIN(MIN()：求指定字段中的最小值：求指

37、定字段中的最小值v 以上函数不可以嵌套使用。在使用库函数查询时，常用以上函数不可以嵌套使用。在使用库函数查询时，常用ASAS来指定列名。来指定列名。数据库系统及应用数据库系统及应用第5章 结构化查询语言SQL v 例例5-14 5-14 查询课程总数。查询课程总数。v SELECT COUNT(SELECT COUNT(* *)AS )AS 课程总数课程总数 FROM courseFROM coursev 在在coursecourse表中课程编号的值唯一，所以统计课程总数就是表中课程编号的值唯一，所以统计课程总数就是统计表中的记录数。统计表中的记录数。v 例例5-15 5-15 查询选修了课程

38、学生人数。查询选修了课程学生人数。v SELECT COUNT(DISTINCT SELECT COUNT(DISTINCT 学号学号)AS )AS 选课人数选课人数 FROM scoreFROM scorev 由于每个学生选课不止一门课，由于每个学生选课不止一门课，scorescore表中一个学生可能表中一个学生可能有多个记录，因此在有多个记录，因此在COUNTCOUNT函数中用函数中用DISTINCTDISTINCT去掉重复记去掉重复记录。录。v 例例5-16 5-16 统计各课程选修的学生人数。统计各课程选修的学生人数。v SELECT SELECT 课程编号课程编号,COUNT(,CO

39、UNT(学号学号)AS )AS 学生人数学生人数 FROM score FROM score GROUP BY GROUP BY 课程编号课程编号v 本例对本例对scorescore按课程编号分组，对每一组用函数按课程编号分组，对每一组用函数COUNTCOUNT求出求出学生人数。学生人数。数据库系统及应用数据库系统及应用第5章 结构化查询语言SQL v 6 6查询结果输出查询结果输出v ）输出部分结果）输出部分结果v 在在SELECTSELECT语句中常使用语句中常使用TOP n PERCENTTOP n PERCENT短语来显示满足条件的前几条短语来显示满足条件的前几条记录。不带记录。不带P

40、ERCENTPERCENT参数时，参数时，n n是是132767132767之间的整数，说明显示前之间的整数，说明显示前n n条条记录；使用记录；使用PERCENTPERCENT参数时，参数时，n n是是0.0199.990.0199.99之间的实数，说明显示查之间的实数，说明显示查询结果中前百分之多少的记录。询结果中前百分之多少的记录。v TOPTOP短语要与短语要与ORDER BYORDER BY短语同时使用才有效。短语同时使用才有效。v 例例5-17 5-17 查询输出入学成绩在前查询输出入学成绩在前3 3名的学生名的学生v SELECT SELECT * * TOP 3 FROM st

41、udent ORDER BY TOP 3 FROM student ORDER BY 入学成绩入学成绩 DESCDESCv 例例5-18 5-18 查询输出入学成绩在前查询输出入学成绩在前3%3%的学生的学生v SELECT SELECT * * TOP 3 PERCENT FROM student ORDER BY TOP 3 PERCENT FROM student ORDER BY 入学成绩入学成绩 DESCDESCv ）输出去向）输出去向v 在在SELECTSELECT语句中语句中INTOINTO或或TOTO短语，可以指定查询结果的输出去向。短语，可以指定查询结果的输出去向。数据库系统

42、及应用数据库系统及应用第5章 结构化查询语言SQL v例例5-19 5-19 将所有女同学的信息保存于临时表将所有女同学的信息保存于临时表TEMPTEMP中。中。vSELECT SELECT * * FROM student WHERE FROM student WHERE 性别性别=女女 INTO INTO CURSOR TEMPCURSOR TEMPv例例5-20 5-20 将入学成绩大于将入学成绩大于450450分的学生信息保存于分的学生信息保存于高分高分.DBF.DBF表中。表中。vSELECT SELECT * * FROM student WHERE FROM student WH

43、ERE 入学成绩入学成绩450 450 INTO DBF INTO DBF 高分高分v例例5-21 5-21 将中文系的学生信息存放到文件名为将中文系的学生信息存放到文件名为“中中文文”的文本文件中。的文本文件中。vSELECT SELECT * * FROM student WHERE FROM student WHERE 院系院系=中文中文 TO TO FILE FILE 中文中文数据库系统及应用数据库系统及应用第5章 结构化查询语言SQL v5.1.4 SQL5.1.4 SQL操纵功能操纵功能vSQLSQL的数据操作功能是指对已经存在的数据表进行的数据操作功能是指对已经存在的数据表进行记

44、录的插入、删除和修改操作。记录的插入、删除和修改操作。SQLSQL的数据操作包的数据操作包括三个语句：括三个语句：INSERT(INSERT(插入记录插入记录) )、DELETE(DELETE(删除记删除记录录) )和和UPDATE(UPDATE(修改记录修改记录) )。数据库系统及应用数据库系统及应用第5章 结构化查询语言SQL v 1 1插入数据插入数据v （1 1）命令格式：）命令格式：v INSERT INTOINSERT INTO(字段名字段名 ，字段名，字段名)VALUES()VALUES(表达式表达式11，表达式，表达式2 2)v 功能功能: :在表尾追加一条包含指定字段值的记录

45、。在表尾追加一条包含指定字段值的记录。v 例例5-22 5-22 给成绩表添加一条选课记录。给成绩表添加一条选课记录。v INSERT INTO score (INSERT INTO score (学号学号, ,课程编号课程编号, ,成绩成绩) VALUES (03036003,001,89) VALUES (03036003,001,89)v 说明：说明：v 命令中的各表达式和各字段之间相对应的，其数据类型应相同。命令中的各表达式和各字段之间相对应的，其数据类型应相同。v 若插入命令中表的每一个字段都有具体的值，那么字段名列表可以省略。若若插入命令中表的每一个字段都有具体的值，那么字段名列表

46、可以省略。若只给出部分字段的值，那么必须在命令中列出对应的字段名。只给出部分字段的值，那么必须在命令中列出对应的字段名。v 如上列的命令可改为：如上列的命令可改为：v INSERT INTO score VALUES (INSERT INTO score VALUES (“0303600303036003”, ,”001001”,89),89)v （2 2）命令格式）命令格式2 2：v INSERT INTOINSERT INTO FROM ARRAY FROM ARRAY| v 功能：从数组或内存变量中导入记录。功能：从数组或内存变量中导入记录。 数据库系统及应用数据库系统及应用第5章 结构

47、化查询语言SQL v 2 2删除数据删除数据v 命令格式：命令格式：v DELETE FROMDELETE FROMWHERE WHERE v 功能：从指定的表中对满足条件的那些记录作删除标记。功能：从指定的表中对满足条件的那些记录作删除标记。v 说明：说明：v 该命令执行的是逻辑删除，若要彻底删除记录，还需要使该命令执行的是逻辑删除，若要彻底删除记录，还需要使用用PACKPACK命令。命令。v 如果省略条件子句，表示删除表中所有记录，但是该表的如果省略条件子句，表示删除表中所有记录，但是该表的结构仍然存在。结构仍然存在。v 例例5-23 5-23 从课程表中删除课程号为从课程表中删除课程号为

48、”001001”的记录。的记录。v DELETE FROM course WHERE DELETE FROM course WHERE 课程编号课程编号=001=001。数据库系统及应用数据库系统及应用第5章 结构化查询语言SQL v 3 3修改数据修改数据v 修改数据是指修改指定表中满足条件的记录。修改数据是指修改指定表中满足条件的记录。v 命令格式：命令格式：v UPDATE UPDATE v SET SET =,=2v WHERE WHERE v 功能：按功能：按SETSET子句中的表达式修改记录的相应的字段值。如果省略条件，子句中的表达式修改记录的相应的字段值。如果省略条件，表示表中所

49、有记录都要修改，否则仅修改满足条件的部分记录。表示表中所有记录都要修改，否则仅修改满足条件的部分记录。v 例例5-24 5-24 将学生表中学号为将学生表中学号为”0303600303036003”的学生姓名改为的学生姓名改为“王平王平”。v UPDATE student SET UPDATE student SET 姓名姓名=王平王平 WHERE WHERE 学号学号=03036003=03036003v 例例5-25 5-25 将所有学生的入学成绩加上将所有学生的入学成绩加上5 5分。分。v UPDATE student SET UPDATE student SET 入学成绩入学成绩= =

50、入学成绩入学成绩+5+5数据库系统及应用数据库系统及应用第5章 结构化查询语言SQL v 5.2 5.2 用用SQLSQL进行表的基本操作案例进行表的基本操作案例 v 案例说明案例说明 v 本案例要求运用本案例要求运用SQLSQL语句的定义功能，创建语句的定义功能，创建“学生学生- -成绩成绩- -课程课程”数据库数据库StscStsc，然后在该数据库中创建表，并建立表，然后在该数据库中创建表，并建立表间联系。基本要求如下：间联系。基本要求如下：v （1 1）用）用SQLSQL语句创建数据库语句创建数据库stscstsc。v （2 2）在该数据库下创建）在该数据库下创建3 3张数据库表，分别为

51、：学生表张数据库表，分别为：学生表studentstudent、成绩表、成绩表scorescore、课程表、课程表coursecourse。v （3 3）设置学生表的学号为主索引、成绩表的学号和课程）设置学生表的学号为主索引、成绩表的学号和课程编号为候选索引、课程表的课程编号为主索引。编号为候选索引、课程表的课程编号为主索引。v （4 4）建立学生表和成绩表、课程表和成绩表一对多关联。）建立学生表和成绩表、课程表和成绩表一对多关联。v （5 5）设置数据库表的完整约束性条件。）设置数据库表的完整约束性条件。v （6 6）向数据库表中添加记录。）向数据库表中添加记录。数据库系统及应用数据库系统及

52、应用第5章 结构化查询语言SQL v 5.2.1 5.2.1 操作步骤操作步骤v （1 1）创建数据库）创建数据库stscstscv （2 2）创建数据库表）创建数据库表v 在命令窗口中输入在命令窗口中输入CREATE TABLECREATE TABLE命令，分别建立学生表命令，分别建立学生表studentstudent、成绩表、成绩表scorescore、课程表、课程表cousecouse，并要求对学生表，并要求对学生表中的中的“学号学号”和课程表中的和课程表中的“课程编号课程编号”字段建立主索引，字段建立主索引，对成绩表中的对成绩表中的“学号学号”和和“课程编号课程编号”字段建立候选索引。

53、字段建立候选索引。在新建成绩表（在新建成绩表（scorescore）命令中用）命令中用REFERENCESREFERENCES子句建立了子句建立了该表与其他两个表的关联。该表与其他两个表的关联。v （3 3）修改表结构）修改表结构v （4 4）给成绩表添加一条记录或从数组中导入记录。）给成绩表添加一条记录或从数组中导入记录。 数据库系统及应用数据库系统及应用第5章 结构化查询语言SQL v 5.2.2 5.2.2 本节知识点本节知识点v1 1完整性约束条件完整性约束条件v在案例中用到了在案例中用到了SQLSQL数据定义功能中的完整性约束数据定义功能中的完整性约束条件的使用。条件的使用。 v2

54、2使用使用SQLSQL插入命令插入命令v当表中定义了主索引或候选索引，相应的字段设当表中定义了主索引或候选索引，相应的字段设置成不能为空时，置成不能为空时，Visuai FoxProVisuai FoxPro的的INSERTINSERT或者或者APPENDAPPEND命令就无法使用了，因此这里使用命令就无法使用了，因此这里使用SQLSQL的插的插入命令。入命令。数据库系统及应用数据库系统及应用第5章 结构化查询语言SQL v 5.3 5.3 用用SQLSQL查询命令查询数据案例查询命令查询数据案例 v 案例说明案例说明 v 本案例要求在本案例要求在“学生学生- -成绩成绩- -课程课程”数据库

55、基础上，运用数据库基础上，运用SQLSQL查询命令，对查询命令，对stscstsc数据库中数据实现单表的简单查询、数据库中数据实现单表的简单查询、多表的连接查询和复杂的嵌套查询，灵活地运用数据库内多表的连接查询和复杂的嵌套查询，灵活地运用数据库内的数据为实际应用服务。基本要求如下：的数据为实际应用服务。基本要求如下：v （1 1）用）用SQLSQL的查询命令有选择地显示表中的部分列。的查询命令有选择地显示表中的部分列。v （2 2）利用）利用SQLSQL查询功能实现计算查询和分组计算查询。查询功能实现计算查询和分组计算查询。v （3 3）用）用SELECTSELECT命令进行多表的连接查询。命

56、令进行多表的连接查询。v （4 4）用嵌套查询构造复杂的查询。）用嵌套查询构造复杂的查询。数据库系统及应用数据库系统及应用第5章 结构化查询语言SQL v5.3.1 5.3.1 操作步骤操作步骤v1 1用简单查询实现表中部分列和记录的显示。用简单查询实现表中部分列和记录的显示。v（1 1）查询全体学生的学号和姓名。）查询全体学生的学号和姓名。v在命令窗口中键入下列命令：在命令窗口中键入下列命令：vSELECT SELECT 学号学号, ,姓名姓名 FROM studentFROM studentv（2 2）查询全体学生的姓名和年龄。）查询全体学生的姓名和年龄。v由于表中没有直接的年龄字段，因此

57、可以在由于表中没有直接的年龄字段，因此可以在SQLSQL查查询命令中运用表达式来实现。在命令窗口中键入询命令中运用表达式来实现。在命令窗口中键入下列命令：下列命令：vSELECT SELECT 姓名姓名,year(2003-09-01)-year(,year(2003-09-01)-year(出生日出生日期期) as ) as 年龄年龄 FROM studentFROM student数据库系统及应用数据库系统及应用第5章 结构化查询语言SQL v （3 3）查询学校的院系名称。）查询学校的院系名称。v 由于通过学生表可以看到学校的院系名单，但是会出现院由于通过学生表可以看到学校的院系名单，但

58、是会出现院系名重复，因此用系名重复，因此用DISTINCTDISTINCT子句去掉重复。命令如下：子句去掉重复。命令如下：v SELECT SELECT 院系院系 DISTINCT FROM studentDISTINCT FROM studentv （4 4）查找入学成绩在）查找入学成绩在460460分至分至500500分之间的学生信息，并分之间的学生信息，并按入学成绩降序排序。按入学成绩降序排序。v 在命令窗口中输入下列命令：在命令窗口中输入下列命令：v SELECT SELECT * * FROM student ; FROM student ;v WHERE WHERE 入学成绩入学成

59、绩 BETWEEN 460 AND 500;BETWEEN 460 AND 500;v ORDER BY ORDER BY 入学成绩入学成绩 DESCDESCv （5 5）查找金融系和建筑系学生的姓名和出生日期。命令）查找金融系和建筑系学生的姓名和出生日期。命令如下：如下：v SELECT SELECT 姓名姓名, ,出生日期出生日期, ,院系院系 FROM student; FROM student; v WHERE WHERE 院系院系 in(in(金融金融 , ,建筑建筑)数据库系统及应用数据库系统及应用第5章 结构化查询语言SQL v 2 2利用利用SQLSQL查询功能实现实现计算查询

60、和分组计算查询。查询功能实现实现计算查询和分组计算查询。v （1 1）求女生的人数、平均入学成绩、往常入学成绩最高分和入学成绩最低分。）求女生的人数、平均入学成绩、往常入学成绩最高分和入学成绩最低分。在在SQLSQL中通过计算查询可以直接对查询的结果进行计算。命令如下：中通过计算查询可以直接对查询的结果进行计算。命令如下：v SELECT COUNT(SELECT COUNT(* *) AS ) AS 女生人数女生人数,AVG(,AVG(入学成绩入学成绩) AS ) AS 平均入学成绩平均入学成绩,;,;v MAX(MAX(入学成绩入学成绩) AS ) AS 最高分入学成绩最高分最高分入学成绩