第5章sql的应用

Visual FoxPro程序设计教程 （第3版）,北京邮电大学出版社,刘卫国,本章要点： SQL的基本概念。 SELECT查询命令的格式及应用。 SQL数据定义命令的格式及应用。 SQL数据操纵命令的格式及应用。,第5章 SQL的应用,2,SQL(Structured Query Language，结构化查询语言)是通用的关系数据库标准语言，可以用来执行数据查询、数据定义、数据操纵和数据控制等操作。SQL最早是在20世纪70年代由IBM公司开发出来的，并被应用在DB2关系数据库系统中。SQL语言提出以后，由于它具有功能丰富、使用灵活、语言简洁易学等突出优点，在计算机工业界和计算机用户中备受欢迎。 当用户需要检索数据库中的数据时，就可以通过SQL语言发出请求，数据库管理系统对SQL请求进行处理，检索到所要求的数据，并将其返回给用户。,5.1 SQL概述,3,4,按照其实现的功能可以将SQL命令划分为4类： （1）数据查询语言（Data Query Language，DQL)：按一定的查询条件从数据库对象中检索符合条件的数据。例如SELECT命令。 （2）数据定义语言（Data Definition Language，DDL)：用于定义数据的逻辑结构以及数据项之间的关系。例如CREATE、DROP、ALTER命令等。 （3）数据操纵语言（Data Manipulation Language，DML)：用于增加、删除、修改数据等。例如INSERT、UPDATE、DELETE命令等。 （4）数据控制语言（Data Control Language，DCL)：在数据库系统中，具有不同角色的用户执行不同的任务，并且应该被给予不同的权限。数据控制语言用于设置或更改用户的数据库操作权限。例如GRANT、REVOKE命令等。,5,SQL数据查询通过SELECT命令实现，其语法格式是： SELECT ALL|DISTINCT|TOP n PERCENT WITH TIES 别名.选项AS 显示列名,别名.选项AS 显示列名 FROM 数据库名!表名AS 本地别名 INNER | LEFT OUTER | RIGHTOUTER|FULL OUTER JOIN 数据库名!表名AS本地别名ON 连接条件 INTO 目标|TO FILE文件名ADDITIVE |TO PRINTER PROMPT|TO SCREEN PREFERENCE 参照名NOCONSOLEPLAINNOWAIT WHERE 连接条件1AND 连接条件2 AND|OR 过滤条件1AND|OR 过滤条件2 GROUP BY 分组列名1,分组列名2HAVING 过滤条件 UNIONALL SELECT命令 ORDER BY 排序选项1ASC|DESC,排序选项2ASC|DESC,5.2 数据查询,6,5.2.1 基本查询 1简单的查询命令 简单的SELECT命令只包含SELECT子句和FROM子句，其常用格式是： SELECT ALL|DISTINCT 别名.选项AS 显示列名,别名.选项AS 显示列名 FROM 表名1 别名1,表名2 别名2 例5-1 写出对学生表进行如下操作的命令。 （1）列出全部学生信息。 （2）列出全部学生的姓名和年龄。 （3）列出选修了课程的学生的学号，去掉重复行。,7,操作1： OPEN DATABASE 教学管理 SELECT * FROM 学生 操作2： SELECT 姓名 AS 学生姓名,YEAR(DATE()-YEAR(出生日期) AS 年龄 FROM 学生 操作3： SELECT DISTINCT 学号 FROM 选课,8,常用的统计函数，除COUNT(*)函数外，其他函数在计算过程中均忽略NULL值。,9,例5-2 写出对学生表进行如下操作的命令。 （1）求出所有学生的入学成绩平均分。 （2）统计选修了课程的学生的人数。 操作1： SELECT AVG(入学成绩) AS 入学成绩平均分 FROM 学生 操作2： SELECT COUNT(DISTINCT 学号) AS 选课人数 FROM 选课,10,2带条件查询 WHERE 子句用于指定查询条件，其格式是： WHERE 条件表达式 其中条件表达式可以是单表的条件表达式，也可以是多表之间的条件表达式，表达式用的比较符为：=（等于）、！ =（不等于）、=（精确等于）、（大于）、=（大于等于）、（小于）、=（小于等于）。,11,例5-3 写出对学生表进行如下操作的命令。 （1）列出入学成绩在560分以上的学生记录。 （2）求出湖南学生入学成绩平均分。 操作1： SELECT * FROM 学生 WHERE 入学成绩560 操作2： SELECT 籍贯,AVG(入学成绩) AS 入学成绩平均分 FROM 学生 WHERE 籍贯=“湖南“,12,可用于条件表达式中几个特殊运算符的意义和使用方法。这种条件运算的基本要领是：左边是一个字段，右边是一个集合，在集合中测定字段值是否满足条件。,13,例5-4 写出对教学管理数据库进行如下操作的命令。 （1）列出非湖南籍的学生名单。 （2）列出江苏籍和贵州籍的学生名单。 （3）列出入学成绩在560分到650分之间的学生名单。 （4）列出所有的姓赵的学生名单。 （5）列出所有成绩为空值的学生学号和课程号。,14,操作1： SELECT 学号,姓名,籍贯 FROM 学生 WHERE 籍贯“湖南“ 命令中的WHERE子句还有等价的形式： WHERE 籍贯!=“湖南“ WHERE NOT(籍贯=“湖南“) 操作2： SELECT 学号,姓名,籍贯 FROM 学生 WHERE 籍贯 IN (“江苏“,“贵州“) 命令中的WHERE子句还有等价的形式： WHERE 籍贯=“江苏“ OR 籍贯=“贵州“,15,操作3： SELECT 学号,姓名,入学成绩 FROM 学生 WHERE 入学成绩 BETWEEN 560 AND 650 命令中的WHERE子句还有等价的形式： WHERE 入学成绩=560 AND 入学成绩=650 操作4： SELECT 学号,姓名 FROM 学生 WHERE 姓名 LIKE “赵%“ 命令中的WHERE子句还有等价的形式： WHERE 姓名=“赵“ WHERE AT(“赵“,姓名)=1 WHERE LEFT(姓名,2)= “赵“,16,操作5： SELECT 学号,课程号 FROM 选课 WHERE 成绩 IS NULL 命令中使用了运算符IS NULL，该运算符是测试字段值是否为空值，在查询时用“字段名IS NOTNULL”的形式，而不能写成“字段名=NULL”或“字段名!=NULL”。,17,5.2.2 嵌套查询 1返回单值的子查询 例5-5 对教学管理数据库，列出选修“数据库原理”的所有学生的学号。 SELECT 学号 FROM 选课 WHERE 课程号=(SELECT 课程号 FROM 课程 WHERE 课程名=“数据库原理“),18,2返回一组值的子查询 若某个子查询返回值不止一个，则必须指明在WHERE子句中应怎样使用这些返回值。通常使用条件运算符ANY（或SOME）、ALL和IN。 （1）ANY运算符的用法 例5-6 对教学管理数据库，列出选修“01101”课的学生中成绩比选修“01102”的最低成绩高的学生的学号和成绩。 SELECT 学号,成绩 FROM 选课 WHERE 课程号=“01101“ AND 成绩ANY(SELECT 成绩 FROM 选课 WHERE 课程号=“01102“),19,（2）ALL运算符的用法 例5-7 对教学管理数据库，列出选修“01101”课的学生，这些学生的成绩比选修“01102”课的最高成绩还要高的学生的学号和成绩。 SELECT 学号,成绩 FROM 选课 WHERE 课程号=“01101“ AND 成绩ALL(SELECT 成绩 FROM 选课 WHERE 课程号=“01102“),20,（3）IN运算符的用法 例5-8 对教学管理数据库，列出选修“数据库原理”或“软件工程”的所有学生的学号。 SELECT 学号 FROM 选课 WHERE 课程号 IN(SELECT 课程号 FROM 课程 WHERE 课程名=“数据库原理“ OR 课程名=“软件工程“),21,5.2.3 多表查询 1等值连接 等值连接是按对应字段的共同值将一个表中的记录与另一个表中的记录相连接。 例5-9 写出对教学管理数据库进行如下操作的命令。 （1）输出所有学生的成绩单，要求给出学号、姓名、课程号、课程名和成绩。 （2）列出男生的选课情况，要求列出学号、姓名、课程号、课程名和学分数。 （3）列出至少选修“01101”课和“01103”课程学生的学号。,22,操作1： SELECT a.学号,姓名,b.课程号,课程名,成绩 FROM 学生 a,选课 b,课程 c WHERE a.学号=b.学号 AND b.课程号=c.课程号 操作2： SELECT a.学号,a.姓名,b.课程号,课程名,学分 FROM 学生 a,选课 b,课程 c WHERE a.学号=b.学号 AND b.课程号=c.课程号 AND 性别=“男“ 操作3： SELECT a.学号 FROM 选课 a,选课 b WHERE a.学号=b.学号 AND b.课程号=“01101“ AND a.课程号=“01103“,23,2非等值连接 例5-10 对教学管理数据库，列出选修“01102”课的学生中，成绩大于学号为“200109”的学生该门课成绩的那些学生的学号及其成绩。 SELECT a.学号,a.成绩 FROM 选课 a,选课 b WHERE a.成绩b.成绩 AND a.课程号=b.课程号 AND b.课程号=“01102“ AND b.学号=“200109“,24,5.2.4 连接查询 1内部连接（Inner Join） 所谓内部连接是指包括符合条件的每个表中的记录。也就是说是所有满足连接条件的记录都包含在查询结果中。 例5-11 对教学管理数据库，列出少数民族学生的学号、课程号及成绩。 SELECT a.学号,b.课程号,成绩 FROM 学生 a,选课 b WHERE a.学号=b.学号 AND 少数民族否 如果采用内部连接方式，则命令 SELECT a.学号,b.课程号,成绩 FROM 学生 a INNER JOIN 选课 b ON a.学号=b.学号 WHERE 少数民族否 所得到的结果完全相同。,25,2外部连接（Outer Join） (1)左外连接 也叫左连接（Left Outer Join），其系统执行过程是左表的某条记录与右表的所有记录依次比较，若有满足连接条件的，则产生一个真实值记录。若都不满足，则产生一个含有NULL值的记录。接着，左表的下一记录与右表的所有记录依次比较字段值，重复上述过程，直到左表所有记录都比较完为止。连接结果的记录个数与左表的记录个数一致。,26,（2）右外连接 也叫右连接（Right Outer Join），其系统执行过程是右表的某条记录与左表的所有记录依次比较，若有满足连接条件的，则产生一个真实值记录；若都不满足，则产生一个含有NULL值的记录。接着，右表的下一记录与左表的所有记录依次比较字段值，重复上述过程，直到左表所有记录都比较完为止。连接结果的记录个数与右表的记录个数一致。 （3）全外连接 也叫完全连接（Full Join），其系统执行过程是先按右连接比较字段值，然后按左连接比较字段值，重复记录不记入