Visual FoxPro数据库技术及应用第7章SQL结构化查询语言.ppt

1,Visual FoxPro数据库技术及应用第 7 章,SQL结构化查询语言,2,第 7 章 SQL结构化查询语言,7.0 简介7.1SQL的数据定义功能7.2 SQL的数据操作功能7.3 SQL的数据查询功能,3,简介,SQL是结构化查询语言，SQL语言已成为关系型数据库的标准查询语言SQL语句的功能分类 数据定义：DDL CREATE、ALTER 数据操纵：DML UPDATE、DELETE和INSERT 数据控制（应用于网络数据库）：DCL GRANT(授权)和REVOKE（收回授权)数据查询（SQL的核心）：DSL SELECT,4,简介,SQL语言的主要特点是（1）SQL语言是一种一体化的语言，提供了完整的数据定义和操纵功能。（2）SQL语言具有完备的查询功能。（3）SQL语言非常简洁，易学易用。（4）SQL语言是一种高度非过程化的语言。（5）SQL语言的执行方式多样。（6）SQL语言不仅能对数据表进行各种操作，也可对视图进行操作。,5,简介,1、数据定义语句（一）创建 CREATE TABLE 创建数据表 CREATE VIEW 创建视图 CREATE DADABASE创建数据库（二）删除 DELETE DATABASE 删除数据库（三）修改 ALTER TABLE修改数据表结构,6,简介,2、数据操纵语句（一）修改数据表记录UPDATE UPDATE SET=表达式；WHERE（二）删除数据表记录DELETE DELETE FROM WHERE（三）插入数据表记录 INSERT INTO;(字段名列表)VALUES()INSERT INTO；FROM ARRAY INSERT INTO；FROM MEMVAR,7,简介,3、数据控制语句有GRANT和REVOKE两个语句。命令对运行于网络上的数据库进行操作。GRANT用来授予用户对数据库的操作权限REVOKE用来收回用户对数据库的操作权限,8,7.1SQL的数据定义功能,7.1.1 创建和删除数据库表7.1.2 创建和删除视图7.1.3 修改数据库表,9,7.1.1创建和删除数据库表,7.1.1创建和删除数据库表1、创建数据库表CREATE TABLE|DBF 表名1(字段名1 字段类型(字段宽度,小数位)NULL|NOT NULL CHECK 逻辑表达式1 ERROR 文本信息1 DEFAULT 表达式1 PRIMARY KEY|UNIQUE REFEENCES 表名2 TAG 标识名1,字段名2),PRIMARY KEY 表达式2 TAG 标识2|,UNIQUE 表达式3 TAG 标识3,FOREIGN KEY 表达式4 TAG 标识4 NODUP REFERENCES 表名3 TAG 标识5,CHECK 逻辑表达式2 ERROR 文本信息2),10,7.1.1创建和删除数据库表,【例7.2】在stu1数据库中，建立学生信息表 OPEN DATABASE STU1 CREATE TABLE 学生信息(学号 C(6)PRIMARY KEY,姓名 C(8)NOT NULL,性别 C(2),年龄 N(3)CHECK 年龄10 AND 年龄40 ERROR“年龄范围在1045，请输入正确的年龄”,是否团员 L,入学年月 D DEFAULT CTOD(“09/012007”),11,7.1.1创建和删除数据库表,【例7.3】在stu1数据库中，建立课程信息表OPEN DATABASE STU1CREATE TABLE 课程信息(课程号 C(4)PRIMARY KEY,课程名 C(10),学分 N(2)NULL,备注 M NOCPTRANS),12,7.1.1创建和删除数据库表,【例7.4】在stu1数据库中，建立选课信息表 OPEN DATABASE STU1CREATE TABLE 选课信息(学号 C(6),课程号 C(4),成绩 N(3)NULL,PRIMARY KEY 学号+课程号 TAG 学号课程号,FOREIGN KEY 学号 TAG 学号 REFERENCES 学生信息,FOREIGN KEY 课程号 TAG 课程号 REFERENCES 课程信息),13,7.1.1创建和删除数据库表,例7.2、例7.3、例7.4完成后，在STU1数据库设计器中，它们如下图所示。,14,7.1.1创建和删除数据库表,2、删除数据库表 DROP TABLE 表名【例7.5】在stu1数据库中，删除选课信息表。OPEN DATABASE STU1DROP TABLE 选课信息,15,7.1.2创建和删除视图,1、创建视图在VFP中可以在命令窗口中使用CREATE VIEW命令打开“视图设计器”窗口，另外，VFP中还提供了直接利用SQL语言创建视图的命令。命令格式为：CREATE VIEW 视图名 AS SQL-SELECT命令,16,7.1.2创建和删除视图,（1）基于单个表的视图【例7.6】在stu1数据库中，基于“学生”表建立一个“学生基本信息”视图，包括“学号”、“姓名”、“性别”、“专业”、“年级”字段。CREATE VIEW 学生基本信息 AS;SELECT 学号,姓名,性别,专业,年级;FROM 学生,17,7.1.2创建和删除视图,视图一经定义，就可以和基本表一样进行种查询，也可以进行一些修改操作。对于最终用户来说，有时并不需要知道操作的是基本表还是视图。这样建立视图之后，查询学生基本信息，可以使用命令：SELECT*FROM 学生基本信息或 SELECT 学号,姓名,性别,专业,年级 FROM 学生基本信息或 SELECT 学号,姓名,性别,专业,年级 FROM 学生,18,7.1.2创建和删除视图,【例7.7】在stu1数据库中，基于“学生”表建立一个“学生基本信息2”视图，只输出土木工程系的学生，字段包括“学号”、“姓名”、“性别”、“专业”、“年级”字段。CREATE VIEW 学生基本信息2 AS;SELECT 学号,姓名,性别,专业,年级;FROM 学生 WHERE 专业=“土木工程”,19,7.1.2创建和删除视图,（2）基于多个表的视图【例7.8】在stu1数据库中，基于“a班成绩表”和“a班学生信息”两张表建立一个“学生成绩”视图，在视图中要求按姓名显示各同学的“数学”、“英语”和“计算机”成绩。CREATE VIEW 学生成绩 AS;SELECT a班学生信息.姓名,a班成绩表.数学,a班成绩表.英语,a班成绩表.计算机;FROM a班成绩表,a班学生信息;WHERE a班成绩表.学号=a班学生信息.学号,20,7.1.2创建和删除视图,【例7.9】在stu1数据库中，基于“学生”、“课程”、“学生选课”三张表建立一个“学生成绩2”视图，在视图中要求按姓名和课程名显示所有成绩。CREATE VIEW 学生成绩2 AS;SELECT a.姓名,b.课程名,c.成绩;FROM 学生 a,课程 b,学生选课 c;WHERE a.学号=c.学号 AND b.课程ID=c.课程ID,21,7.1.2创建和删除视图,（3）视图中的计算字段【例7.10】在stu1数据库中，基于“教师信息表”建立一个“平均工资”视图，在视图中要求按职称和平均工资显示各职称的平均工资。CREATE VIEW 平均工资 AS;SELECT 职称,AVG(基础工资)AS 平均工资;FROM 教师信息表 GROUP BY 职称 SELECT*FROM平均工资,22,7.1.2创建和删除视图,2、删除视图在SQL中删除视图的命令格式是：DROP VIEW视图名如果要删除视图“学生成绩”，只要键入命令DROP VIEW 学生成绩,23,7.1.3 修改数据库表,1、语句格式1（增加新字段、更改指定字段）ALTER TABLE 表名1 ADD|ALTER COLUMN 字段名 字段类型(字段宽度,小数位)NULL|NOT NULLCHECK 逻辑表达式1 ERROR 文本信息1DEFAULT 表达式1PRIMARY KEY|UNIQUEREFERENCES 表名2TAG 标识名1NOCPTRANS,24,7.1.3 修改数据库表,【例7.11】在例7.1创建的“研究生”表中增加“年龄”字段，类型为字符型，宽度为3。ALTER TABLE 研究生 ADD 年龄 C(3)【例7.12】修改例7.11“研究生”表中的“年龄”字段，类型为数值型，要求年龄必须限定在2050岁之间，并将“入学日期”的缺省值修改为“09/01/2008”。ALTER TABLE 研究生 ALTER 年龄 N(3)CHECK 年龄20 AND 年龄50ALTER TABLE 研究生 ALTER 入学日期 D DEFAULT CTOD(09/01/2008),25,7.1.3 修改数据库表,2、语句格式2（修改指定表中指定字段的DEFAULT、CHECK约束规则，但不影响原有表的数据）ALTER TABLE 表名1 ALTER COLUMN 字段名2 NULL|NOT NULL SET DEFAULT 表达式2 SET CHECK 逻辑表达式2 ERROR 文本信息2 DROP DEFAULT DROP CHECK,26,7.1.3 修改数据库表,例7.13】修改例7.12“研究生”表中的“年龄”字段，要求年龄必须限定在1845岁之间，并将“入学日期”的缺省值修改为“09/01/2007”。ALTER TABLE 研究生 ALTER 年龄 SET CHECK 年龄18 AND 年龄45ALTER TABLE 研究生 ALTER 入学日期 SET DEFAULT CTOD(09/01/2007)【例7.14】删除例7.13“研究生”表中对“年龄”字段的CHECK约束。ALTER TABLE 研究生 ALTER 年龄 DROP CHECK,27,7.1.3 修改数据库表,3、语句格式3（删除指定表中的指定字段、修改字段名、修改指定表的完整性规则，包括主索引、外索引、候选索引及表的合法值限定的添加或删除）ALTER TABLE 表名1 DROP COLUMN 字段名3 SET CHECK 逻辑表达式3 ERROR 文本信息3 DROP CHECK ADD PRIMARY KEY 表达式3 TAG 标识2 DROP PRIMARY KEY ADD UNIQUE 表达式4 TAG 标识3 DROP UNIQUE TAG 标识4 ADD FOREIGN KEY 表达式5 TAG 标识4 REFERENCES表名2 TAG 标识5 DROP FOREIGN KEY TAG 标识6 SAVE RENAME COLUMN 字段名4 TO字段名5 NOVALIDATE,28,7.1.3 修改数据库表,【例7.15】删除例7.2中建立的“学生信息”表的主索引。ALTER TABLE 学生信息 DROP PRIMARY KEY【例7.16】用ALTER TABLE语句重新建立例7.15中被删除的“学生信息”表主索引。ALTER TABLE 学生信息 ADD PRIMARY KEY 学号 TAG 学号【例7.17】删除例7.4中建立的“选课信息”表的外索引“学号”。ALTER TABLE 选课信息 DROP FOREIGN KEY TAG 学号,29,7.1.3 修改数据库表,【例7-18】用ALTER TABLE语句重新建立例7.17中被删除的“选课信息”表外索引“学号”。ALTER TABLE 选课信息 ADD FOREIGN KEY 学号 TAG 学号 REFERENCES 学生信息 TAG 学号【例7.19】将“学生信息”表中的“入学年月”字段名修改为“入学日期”。ALTER TABLE 学生信息 RENAME COLUMN 入学年月 TO 入学日期,30,7.2 SQL的数据操作功能,7.2.1记录的插入7.2.2记录的修改7.2.3记录的删除,31,7.2.1记录的插入,1、语句格式1INSERT INTO 表名字段名1,字段名2,VALUES(表达式1表达式2,)在指定表的表尾添加一条新记录，其值为VALUES后面的值。当需要插入表所有字段的数据时，表名后面的字段可以缺省，但插入数据的格式必须与表的结构完全吻合。若只需要插入表中某些字段的数据，那么就需要列出插入数据的字段名，且位置应与表对应。,32,7.2.1记录的插入,【例7.20】向“学生信息”表插入数据。INSERT INTO 学生信息 VALUES(081001,张小四,男,20,.T.,CTOD(09/01/2007)如果只想插入部分数据，则SQL语句为：INSERT INTO 学生信息(学号,姓名,性别,年龄);VALUES(081002,周倩雯,女,21),33,7.2.1记录的插入,2、语句格式2INSERT INTO 表名 FROM ARRAY 数组名添加一条新记录到指定的表中。数组中元素与表中各字段顺序对应。如果数组中元素的数据类型与其对应的字段类型不一致，则新记录对应的字段为空值。如果表中字段个数大于数组元素的个数，则多出的字段为空值。,34,7.2.1记录的插入,【例7.21】先定义一个数组A(6)，并赋予一组合适的值。再利用SQL命令将此数组的值作为新记录插入到“学生信息”表中。DIMENSION A(6)A(1)=081003A(2)=张华A(3)=女A(4)=19A(5)=.F.A(6)=CTOD(09/01/2008)INSERT INTO 学生信息 FROM ARRAY A,35,7.2.2记录的修改,语句格式为：UPDATE 数据库名!表名 SET 列名1=表达式1,列名2=表达式2 WHERE 条件表达式1AND|OR条件表达式2UPDATE 指明将要修改的记录所在的表名和数据名。SET 指明被修改的字段及该字段的新值。如果省略WHERE子句，则该字段每一行都用同样的值来更新。WHERE指明将要修改的记录，即表中符合条件表达式的记录。,36,7.2.2记录的修改,【例7.23】将“选课信息”表中的所有课程号为“1001”成绩都分别提高5分，课程号为“1006”成绩置为空值。UPDATE 选课信息SET 成绩=成绩+5WHERE 课程号=”1001”UPDATE 选课信息SET 成绩=NULLWHERE 课程号=1006,37,7.2.3记录的删除,语句格式为：DELETE FROM 数据库名!表名WHERE 条件表达式1AND|OR 条件表达式2 WHERE指明VFP只对满足条件的记录加删除标记。置了删除标记的记录并没有从物理上删除，只有执行了PACK命令，才能真正从物理上删除。置了删除标记的记录可以用RECALL命令取消删除标记。,38,7.2.3记录的删除,【例7.24】将“选课信息”表中的学号为“081004”的记录全部逻辑删除，然后彻底删除。DELETE FROM 选课信息;WHERE 学号=081004PACK,39,7.3 SQL的数据查询功能,7.3.1简单查询7.3.2嵌套查询7.3.3连接查询7.3.4分组及使用库函数查询7.3.5查询结果的合并,40,7.3 SQL的数据查询功能,SELECT-SQL语句格式：SELECT ALL|DISTINCT TOP 数值表达式 PERCENT 表别名.检索项 AS 列名,表别名.检索项 AS列名.FROM 数据库名!表名AS 逻辑别名INTO 目标|TO FILE 文件名|TO PRINTER|TO SCREENWHERE 连接条件 AND连接条件.AND|OR 条件表达式 AND|OR 条件表达式.GROUP BY 列名,列名.HAVING 条件表达式UNION ALL SELECT语句ORDER BY 排序项 ASC|DESC,排序项ASC|DESC.,41,7.3 SQL的数据查询功能,整个语句的含义为：根据WHERE子句中的条件表达式，从一个或多个表中找出满足条件的记录，按SELECT子句中的目标列，选出记录中的分量形成结果表。如果有ORDER子句，则结果表要根据指定的表达式按升序（ASC）或降序（DESC）排序。如果有GROUP子句，则将结果按列名分组，根据HAVING指出的条件，选取满足该条件的组予以输出。,42,实例,如：SELECT*FROM STUDENT 该语句将查询输出数据表STUDENT的所有记录如：SELECT 学号，姓名 FROM 学生；WHERE 性别=男 该语句将查询输出学生表所有男生的学号和姓名如：SELECT*FROM 学生选课;ORDER BY 学号;GROUP BY 学号 该语句将根据学生选课表查询输出已选了课的学生的信息,43,7.3.1简单查询,格式：Select All|Distinct|From AS 别名 Where AND连接条件.示例：SELECT*FROM 学生SELECT 姓名,年龄,性别 FROM 学生表SELECT avg(年龄)as 平均年龄 FROM 学生表SELECT 学号,姓名,数学+英语+政治+计算机 as 总分 FROM 成绩表,44,7.3.1简单查询,使用谓词筛选记录1.ALL 全部记录SELECT ALL*FROM 学生选课2.DISTINCT 过滤重复记录列出所有有选修课的学号SELECT DISTINCT 学号 FROM 学生选课3.TOP 仅取前N 条列出学生表前3个学生的全部信息SELECT TOP 3*FROM 学生 ORDER BY 学号,注意：1）TOP是指逻辑头，若存在排序，则是排序后的TOP2）使用TOP短语时，要求同时带有ORDER BY短语。,45,7.3.1简单查询,【例7.26】列出计算机专业学生的学号和姓名。SELECT 学号,姓名;FROM 学生;WHERE 专业=“计算机”使用WHERE子句作为查询的限制条件，只选择出满足条件的那些行中的相应数据。【例7.27】在“学生选课”表中列出所有选课的学生学号。SELECT DISTINCT 学号;FROM 学生选课“学生选课”表中存储着选课学生的学号，有些学生同时选了几门课，如果直接用SELECT选取就会出现重复记录。因此，加DISTINCT可去掉重复行。,46,7.3.1简单查询,【例7.28】根据“学生”表查询所有入学成绩大于500分的女生的姓名、性别、专业和入学成绩，并按入学成绩升序排列记录。SELECT 学号,性别,专业,入学成绩 FROM 学生;WHERE 性别=女 AND 入学成绩 500;ORDER BY 入学成绩 ASCORDER BY子句中的ASC是用来指明显示结果的顺序，在此处可以不加，因为ORDER BY默认为升序排序。此外，多个查询条件可以用逻辑运算符AND、OR、NOT连接。,47,7.3.1简单查询,【例7.29】在“学生”表中列出非计算机专业学生的学号、姓名及专业。SELECT 学号,姓名,专业 FROM 学生;WHERE 专业 计算机或SELECT 学号,姓名,专业 FROM 学生;WHERE 专业!=计算机或SELECT 学号,姓名,专业 FROM 学生;WHERE NOT(专业=计算机),48,7.3.1简单查询,SELECT-SQL语句的查询方式很丰富，在WHERE子句中可以用关系运算符、逻辑运算符及特殊运算符构成较复杂的条件表达式。关系运算符：=!=.逻辑运算符：AND、OR、NOT特殊运算符：BETWEENAND、IN、IS NULL和LIKE。,49,7.3.1简单查询,（1）BETWEENAND运算符在查找中，如果要求某列的数据在某个区间内，可以使用该运算符。【例7.30】查找成绩在80到90分（包括80和90）之间的学生选课情况。SELECT*FROM 学生选课;WHERE 成绩 BETWEEN 80 AND 90它等价于：SELECT*FROM 学生选课;WHERE 成绩=80 AND 成绩=90 若要查找成绩不在80分到90分之间的学生选课情况，则：SELECT*FROM 学生选课;WHERE 成绩 NOT BETWEEN 80 AND 90,注意：BETWEEN包括边界值，而NOT BETWEEN不包括边界值,50,7.3.1简单查询,（2）IN运算符在查找中，经常会遇到要求表的列值是某几个值中的一个。此时，用IN运算符。【例7.31】列出选修了课程号为“2001”和“2053”的学生的学号和成绩。SELECT 学号,成绩 FROM 学生选课;WHERE 课程ID IN(2001,2053)它等价于：SELECT 学号,成绩 FROM 学生选课;WHERE 课程ID=2001 OR课程ID=2053 可以用NOT IN来表示与IN相反的含义,51,7.3.1简单查询,（3）IS NULL运算符IS NULL的功能是测试属性值是否为空值。在查询时用“列名 IS NOT NULL”的形式。【例7.32】先将学号为“1161009”课程号为“2009”的课程成绩置为NULL，然后查找成绩为空的学生的学号和课程号。UPDATE 学生选课;SET 成绩=NULL;WHERE 学号=1161009 AND 课程ID=2009SELECT 学号,课程ID FROM 学生选课;WHERE 成绩 IS NULL注意WHERE不要写成“WHERE 成绩=NULL”,52,7.3.1简单查询,（4）LIKE运算符语法格式：字符串表达式 NOT LIKE 2种通配符：,53,7.3.1简单查询,【例7.33】从学生表中查找姓名中第2个字是“文”的学生.SELECT*FROM 学生 WHERE 姓名 LIKE _文%【例7.34】从“学生”表中查找姓“李”的学生。SELECT*FROM 学生 WHERE(姓名 LIKE 李%)或:WHERE(姓名 LIKE 李_),54,7.3.2嵌套查询,在一个SELECT-SQL语句中又出现了另一个SELECT语句的情形，我们称之为SELECT嵌套。对于嵌套的查询，通常利用一个SELECT查询（内层查询）的结果来筑构另一个SELECT查询（外层查询）的筛选条件。在嵌套查询中，内层的SELECT语句称为子查询，而把包含有子查询的SELECT语句称为双亲语句。用简单的SELECT语句实现不了查询时用嵌套查询。VFP只支持单层嵌套查询,55,7.3.2嵌套查询,1、返回单值的子查询返回单值的子查询，即为子查询的结果只返回一个值，如：查询学号为“1161001”的同学的性别，只有一个值；【例7.35】列出选修了生活英语的所有学生的学号。SELECT 学号 FROM 学生选课 WHERE 课程ID=;(SELECT 课程ID FROM 课程;WHERE 课程名=生活英语)由于生活英语的“课程ID”只有一个（返回结果不只一个时，不能用“=”号）,56,7.3.2嵌套查询,【例】在数据表“学生”中查询与陈静同乡的学生情况SELECT*FROM 学生;WHERE 籍贯=(SELECT 籍贯 FROM 学生;WHERE 姓名=“陈静”)【例】列出学生选课中2009号课程成绩高于该课程平均成绩的学生信息SELECT 学号,成绩 FROM 学生选课;WHERE 课程ID=2009 AND 成绩;(SELECT AVG(成绩);FROM 学生选课;WHERE 课程ID=2009),子查询中，计算出2009号课程的平均成绩，作为主查询的比较条件,57,7.3.2嵌套查询,【例】根据学生表查询入学成绩小于600但最接近600的学生情况,包括姓名、入学日期和入学成绩。SELECT 姓名,出生日期,入学成绩;FROM 学生 WHERE 入学成绩=;(SELECT MAX(入学成绩);FROM 学生;WHERE 入学成绩600)子查询从学生表中取出入学成绩低于600分的学生之最高成绩,58,7.3.2嵌套查询,2、返回一组值的子查询如果某个子查询的返回值不止一个，则必须指明在WHERE子句中应怎样使用这些返回值。通常使用谓词ANY、ALL和IN。（1）ANY谓词的用法。使用ANY谓词表示查询结果中的任意一个值。【例7.36】求选修2009号课程的学生中成绩比选修了2053号课程的最低成绩要高的学生的学号和成绩。SELECT 学号,成绩 FROM 学生选课;WHERE 课程ID=2009 AND 成绩 ANY;(SELECT 成绩 FROM 学生选课;WHERE 课程ID=2053),59,7.3.2嵌套查询,（2）ALL谓词的用法。使用ALL谓词表示查询结果中的所有值或每一个值。【例7.37】在“学生”表中查询出生年份最早的学生情况。SELECT*FROM学生;WHERE YEAR(出生日期)=ALL;(SELECT YEAR(出生日期)FROM 学生)出生年份最早的学生一定比其他年份出生的学生都要小，所以，该查询首先找出所有学生的出生年份，。然后在全部记录中找出生年份小于等于前面所有年份的记录。,60,7.3.2嵌套查询,（3）IN谓词的用法。使用IN谓词表示包含于查询结果中。IN谓词前面已有介绍，这里再举一例。【例7.38】列出“学生”表中“计算机”专业未选课的学生。SELECT*FROM 学生;WHERE 专业=计算机 and 学号 NOT IN;(SELECT 学号 FROM 学生选课),61,7.3.2嵌套查询,【例】列出“土木工程”专业学生的选课情况SELECT*FROM 学生选课;WHERE 学号 IN;(SELECT 学号 FROM 学生;WHERE 专业=土木工程)【例】列出学生表中“计算机”专业选课的学生SELECT*FROM 学生;WHERE 专业=计算机 and 学号 IN;(SELECT 学号 FROM 学生选课),使用IN运算符时，子查询字段表中只能指定一个表达式,62,7.3.2嵌套查询,【例】使用滤波条件字构造SELECT语句，根据课程和学生选课表，查询选修了“孙子兵法”的学生的学号、课程名、成绩，SELECT b.学号,课程名,成绩;FROM 课程 a,学生选课 b;WHERE a.课程ID=b.课程ID and 课程名 IN;(SELE 课程名 FROM 课程;WHERE 课程名=孙子兵法),63,7.3.2嵌套查询,【例】在学生表中查询出生年份最早的学生情况。SELE*FROM 学生;WHERE YEAR(出生日期)=ALL(SELE YEAR(出生日期)FROM 学生)或：SELE*FROM 学生;WHERE YEAR(出生日期)=;(SELE MIN(YEAR(出生日期)FROM 学生)或：SELE*FROM 学生;WHERE YEAR(出生日期)IN;(SELE MIN(YEAR(出生日期)FROM 学生),64,7.3.3连接查询,1、简单连接查询【例7.39】列出选修了“2001”号课程的学生姓名及成绩。SELECT 姓名,成绩 FROM 学生,学生选课;WHERE 学生.学号=学生选课.学号 AND 课程ID=2001“姓名”和“成绩”两个字段不在一张表中，其中“学生.学号=学生选课.学号”是这两张表的连接条件。引入“别名”，也可如下实现：SELECT 姓名,成绩 FROM 学生 S,学生选课 SC;WHERE S.学号=SC.学号 AND 课程ID=2001,65,7.3.3连接查询,2、自连接查询SQL不仅可以对多个数据表实行连接操作，也可以将同一数据表与其自身进行连接，这种连接称为自连接。【例7.40】列出同时选修了“2001”号课程和“2006”号课程的学生学号。SELECT X.学号 FROM 学生选课 X,学生选课 Y;WHERE X.学号=Y.学号 AND X.课程ID=2001;AND Y.课程ID=2006,66,7.3.3连接查询,【例7.41】列出所有选修“2053”号课程的学生中，成绩大于学号为“1161004”的学生“2053”号课程成绩的学号和成绩。SELECT X.学号,X.成绩 FROM 学生选课 X,学生选课 Y;WHERE X.课程ID=2053 AND X.课程ID=Y.课程ID;AND Y.学号=1161004 AND X.成绩 Y.成绩也可以以如下嵌套查询的方式实现：SELECT 学号,成绩 FROM 学生选课;WHERE 课程ID=2053 AND 成绩;(SELECT 成绩 FROM 学生选课;WHERE 学号=1161004),67,7.3.3连接查询,3、超连接查询超连接查询也是一种将多表进行连接的查询，其格式为：FROM 表名1 INNER|LEFT|RIGHT|FULL JOIN 表名2 ON 连接条件其中：INNER JOIN 等价于 JOIN，为普通连接，亦称内部连接。LEFT JOIN为左连接，RIGHT JOIN 为右连接，FULL JOIN 为全连接，这几种连接不太常用。ON 连接条件：用来指定连接条件。由于此子句已经给出连接条件，所以不再在WHERE子句中给出。,68,7.3.3连接查询,【例7.42】列出“马大大”同学所选修的课程ID和成绩。SELECT 课程ID,成绩;FROM 学生 S JOIN 学生选课 SC ON S.学号=SC.学号;WHERE S.姓名=马大大它等价于：SELECT 课程ID,成绩;FROM 学生 S,学生选课 SC;WHERE S.姓名=马大大 AND S.学号=SC.学号,69,7.3.3连接查询,【例7.43】根据“学生”、“课程”和“学生选课”表，查询选修成绩不低于85分的学生的学号、姓名、所选课程的课程名、成绩，并按成绩降序排列记录SELECT b.学号,姓名,课程名,成绩;FROM 课程 a INNER JOIN 学生选课 b ON a.课程ID=b.课程ID;INNER JOIN 学生 c ON b.学号=c.学号;WHERE 成绩=85;ORDER BY 成绩 DESCJOIN可以进行多个表的连接。在连接时，JOIN的顺序和ON的顺序（相应的连接条件）正好相反：SELECT b.学号,姓名,课程名,成绩;FROM 课程 a JOIN 学生选课 b JOIN 学生 c;ON b.学号=c.学号 ON a.课程ID=b.课程ID;WHERE 成绩=85;ORDER BY 成绩 desc,注意书写顺序，先为后连接的表指定连接条件,70,7.3.4分组及使用库函数查询,可以通过库函数对满足条件的记录进行最大值、最小值、平均值、总和、计数等运算。常用库函数有：（1）MIN()：求（字符、日期、数值等）最小值。（2）MAX()：求（字符、日期、数值等）最大值。（3）COUNT()：计算所选数据的行数。（4）SUM()：计算数值列的总和。（5）AVG()：计算数值列的平均值。这些库函数一般是从一组值中计算出一个汇总信息。用GROUP BY子句来定义或划分字段的值成为多个组，它能控制和影响查询的结果。可以用上述的库函数来对分组后的数据进行各种运算。,71,7.3.4分组及使用库函数查询,【例7.44】列出各门课的平均成绩、最高成绩、最低成绩、选课人数。SELECT 课程ID,AVG(成绩)AS 平均分,;MAX(成绩)AS 最高分,;MIN(成绩)AS 最低分,COUNT(学号)AS 人数;FROM 学生选课;GROUP BY 课程ID【例7.45】查询计算机专业的学生人数。SELECT COUNT(*)FROM 学生;WHERE 专业=计算机COUNT的特殊形式是COUNT(*)，统计满足WHERE子句中逻辑表达式的记录的行数。,72,7.3.4分组及使用库函数查询,【例7.46】列出至少选修了两门课程的学生的学号。SELECT 学号 FROM 学生选课 GROUP BY 学号 HAVING COUNT(*)=2 先在“学生选课”表中按学号进行分组，然后，对每个分组检测其元组个数是否大于等于2，如果满足条件，则显示其学号，否则不显示。HAVING子句和WHERE子句的区别：WHERE子句是用来指定表中各行所应满足的条件，而HAVING子句是用来指定每一分组所应满足的条件，只有满足HAVING条件的那些组才能在结果中被显示。,73,7.3.5查询结果的合并,使用UNION可以把两个SELECT查询结果合并起来，形成一个新的查询结果。要求参与UNION合并的两个查询结果必须具有相同的输出项。除非UNION后带有ALL，否则合并结果将不会有重复的记录。,74,7.3.5查询结果的合并,【例7.48】列出单科成绩最高和最低的学生的选课情况。SELECT*FROM 学生选课;WHERE 成绩=(SELECT MAX(成绩)FROM 学生选课);UNION;SELECT*FROM 学生选课;WHERE 成绩=(SELECT MIN(成绩)FROM 学生选课),75,单科成绩最高和最低的学生,76,7.3.5查询结果的合并,【例】将学生和教师信息表合并，列出姓名,性别,出生日期,和身份。SELECT 姓名,性别,出生日期,学生 AS 身份;FROM 学生;UNION;SELECT 姓名,性别,出生日期,教师 AS 身份;FROM 教师信息表,77,查询结果合并的后的效果,78,