关系数据库标准语言SQL浙大城院数据库.ppt

第10章关系数据库标准语言SQL,教学目标,掌握SQL修改表结构的规则与方法；掌握SQL修改数据的规则与方法；掌握SQL数据查询功能的语句规则和特点。,重点与难点：表定义中数据完整性规则的设定、多表连接查询的方法与使用规则、在分组查询中使用函数的规则和方法、使用嵌套子查询的方法和规则、综合应用SQL数据查询功能完成实际问题的能力。,10.1 SQL 概述,SQL是Structured Query Language的缩写，即结构化查询语言，是关系数据库的标准语言。主要特点：SQL是一种功能齐全的一体化语言。SQL是一种高度非过程化的语言。SQL语言简洁，易学易用。语言共享。,SQL的历史 SQL语言最初是由IBM公司1974年在原型的关系数据库管理系统SYSTEM R上开发的语言。1979年，ORACLE公司首家推出商业上可执行的SQL。如今，SQL成为国际上标准的数据库存取语言。泛应用的各种大型数据库，如SYBASE、INFORMIX、ORACLE、DB2、INGRES等，也用于各种小型数据库，如FOXPRO、ACCESS。,SQL的标准随着关系数据库系统和SQL语言应用的日益广泛，SQL语言的标准化工作也在紧张革进行着，十多年来已制订了多个SQL标准；1.1982年，美国国家标准化局（AMERICAN NATIONAL STANDARD INSTITUTE，简称ANSI）开始制定SQL标准；2.1986年，美国国家标准化协会公布了SQL语言的第一个标准SQL86；3.1987年，国际标准化组织（ISO）通过了SQL86标准；4.1989年，国际标准化组织（ISO）对SQL86进行了补充，推出了SQL89标准；5.1992年，ISO又推出了SQL92标准，也称为SQL2；6.目前SQL99（也称为SQL3）在起草中，增加了面向对象的功能。,SQL语言可以分成以下几组：数据定义语言DDL(Data Definition Language)：包括定义、修改与删除基本表及建立与删除索引等。数据操纵语言DML(Data Manipulation Language)：包括插入、修改与删除数据等。数据查询语言DQL(Data Query Language)：包括单表查询、连接查询、嵌套查询等各种查询功能。数据控制语言DCL(Data Control Language)：包括数据的安全性控制、数据的完整性控制、数据库的恢复及并发控制等功能。,其作用是建立和使用数据库。,SQL语言的基本概念首先介绍两个基本概念：基本表和视图。基本表（BASE TABLE）：是独立存在的表，不是由其它的表导出的表。一个关系对应一个基本表，一个或多个基本表对应一个存储文件。视图（VIEW）：是一个虚拟的表，是从一个或几个基本表导出的表。它本身不独立存在于数据库中，数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中。当基本表中的数据发生变化时，从视图中查询出来的数据也随之改变。在ACCESS中查询所建立起来的”表”就是视图.,用户1,用户2,用户3,用户4,视图3,视图1,视图2,基本表1,基本表2,基本表3,基本表4,存储文件1,存储文件1,SQL用户,视图,基本表,存储文件,外模式,模式,内模式,表,10.2数据定义,一.创建表,格式：CREATE TABLE(长度)PRIMARY KEY NOT NULL，(长度)NOT NULL),功能：创建一个数据表的结构。说明：要创建的数据表的名字。：要创建的数据表的字段名和字段类型。字段长度仅限于文本及二进制字段。PRIMARY KEY：表示将该字段定义为主键。NOT NULL：不允许字段值为空，而NULL允许字段值为空。,例10.1 在职工管理数据库中建立一个数据表“职工”，表结构由职工号、姓名、性别、职称、部门、出生日期、婚否等字段组成。并设置“职工号”为主键。操作步骤如下：创建“职工管理”数据库。在“职工管理”数据库窗口中选择“查询”对象。双击“在设计视图中创建查询”，关闭弹出的“显示表”对话框，打开查询设计视图窗口。选择“查询”菜单中“SQL特定查询”下的“数据定义”命令项，打开“数据定义查询”窗口。在“数据定义查询”窗口中输入SQL语句，每个数据定义查询只能包含一条数据定义语句。,CREATE DATABASE 职工管理 CREATE TABLE 职工(职工号 TEXT(5)PRIMARY KEY,姓名 TEXT(4),性别 TEXT(1),职称 TEXT(4),部门 TEXT(8),出生日期 DATE,婚否 LOGICAL)保存查询为“数据表定义查询（职工）”，查询建立完毕。运行查询。在设计视图中，单击工具栏上的“运行”按钮，执行SQL语句，完成创建表的操作。在数据库窗口中单击“表”对象，可以看到在“表”列表框中多了一个“职工”表，这就是用SQL的定义查询创建的表。,例10.2在职工管理数据库中建立一个数据表“工资”，并通过“职工号”字段建立与“职工”表的关系。,CREATE TABLE 工资(职工号 TEXT(5)PRIMARY KEY REFERENCES 职工,工资 Single,津贴 Single,所得税 Single,公积金 Single,水电费 Single，应扣 Single,实发 Single)其中的“REFERENCES 职工”表示与“职工”表建立关系。,格式：DROP TABLE,功能：删除指定的数据表文件。说明：一定要慎用DROP TABLE语句，一旦使用以后就无法恢复表或其中的数据，此表上建立的索引也将自动删除，并且无法恢复。,二.删除表,例10.3 删除例10.2建立的工资表。操作步骤如下：打开“数据定义查询”窗口。输入删除表的SQL语句：DROP TABLE 工资 单击工具栏上的“运行”按钮，完成删除表的操作，“工资”表将从“职工管理”数据库窗口消失。,三、修改表的结构,格式一：ALTER TABLE ADD(长度)，(长度)DROP，ALTER(长度)，(长度),功能：修改指定的数据表的结构。说明：要修改的数据表的名字。ADD子句用于增加新的字段。DROP子句用于删除指定的字段。ALTER子句用于修改原有字段的定义，包括字段名、数据类型和字段的长度。应注意ADD子句、DROP子句和ALTER子句不能同时使用。,例10.4 为职工表增加一个电话号码字段。操作步骤如下：在“职工管理”数据库窗口中选择“查询”对象。双击“在设计视图中创建查询”，关闭弹出的“显示表”对话框，打开查询设计视图窗口。选择“查询”菜单中“SQL特定查询”下的“数据定义”命令项，打开“数据定义查询”窗口。在“数据定义查询”窗口中，输入修改表结构的SQL语句：ALTER TABLE 职工 ADD 电话号码 Text(8)单击工具栏上的“运行”按钮，完成修改表结构的操作。,例10.5 将职工表的姓名字段的宽度由原来的6改为8，SQL语句如下：ALTER TABLE 职工 ALTER 姓名 Text(8)例10.6 删除职工表“电话号码”字段，SQL语句如下：ALTER TABLE 职工 DROP 电话号码,10.3 数据操作,一.插入记录,格式：INSERT INTO()VALUES(）,功能：在指定的数据表的尾部添加一条新记录。说明：要插入数据的表的名字。为数据表要插入新值的字段。VALUES（表达式清单）为数据表要插入新值的各字段的数据值。和VALUES子句中（表达式清单）的个数和数据类型要完全一致。若省略，则数据表中的所有字段必须在VALUES子句中都有相应的值。,例10.7 在职工表尾部添加一条新记录。操作步骤如下：在“职工管理”数据库窗口中，打开“数据定义查询”窗口。输入SQL语句：INSERT INTO 职工(职工号,姓名,性别,职称,部门,出生日期,婚否)VALUES(01001,陈周,男,教授,计算机,#1958-03-05#,yes)单击工具栏上的“运行”按钮，完成插入数据的操作。例10.8 在职工表尾部插入第二条记录，SQL语句如下：INSERT INTO 职工 VALUES(03021,刘杨,女,副教授,管理,#1962-06-18#,no),格式：UPDATE SET=表达式1,=表达式2WHERE,功能：根据WHERE子句指定的条件，对指定记录的字段值进行更新。说明：要更新数据的表的名字。=是指用的值替代的值，一次可更新多个字段的值。若省略WHERE子句，则更新全部记录。一次只能在单一的表中更新记录。,二.更新数据,例10.9 计算工资表中的应扣和实发数。在“职工管理”数据库窗口中，打开“数据定义查询”窗口。输入SQL语句：UPDATE 工资 SET 应扣=所得税+公积金+水电费,实发=工资+补贴-应扣 单击工具栏上的“运行”按钮，完成更新数据的操作。,格式：DELETE FROM WHERE,功能：根据WHERE子句指定的条件，删除表中指定的记录。说明：要删除数据的表的名字。若省略WHERE子句，则删除表中全部记录。DELETE语句删除的只是表中的数据，而不是表的结构。,三.删除数据,例10.10将职工表中职工号为“03021”的记录删除。在“职工管理”数据库窗口中，打开“数据定义查询”窗口。输入SQL语句：DELETE FROM 职工 WHERE 职工号=03021 单击工具栏上的“运行”按钮，完成删除数据的操作。,10.4 数据查询,一.SELECT查询命令,格式：SELECT ALL|DISTINCT|TOP n PERCENT|,FROM WHEREGROUP BY HAVING ORDER BY ASC|DESC,ASC|DESC,功能：,从FROM子句列出的表或查询中，选择满足WHERE子句中给出的条件的记录，然后按GROUP BY子句（分组子句）中指定字段的值分组，再提取满足HAVING 子句中过滤条件的那些组，按SELECT子句给出的字段名或字段表达式求值输出。ORDER BY子句（排序子句）是对输出的目标表进行重新排序，并可附加说明ASC（升序）或DESC（降序）排列。,说明：ALL：查询的结果中包含数据源中的所有记录。DISTINCT：查询的结果中不包含数据源中重复行的记录。：指定查询结果输出的字段，如果要包含数据源中的所有字段，可以使用通配符“*”。AS：如果在输出时不希望使用原来的字段名，可以用列名称重新设置。FROM：指出查询的数据来源。WHERE：说明查询条件，即选择记录的条件。,二.简单查询,格式：SELECT ALLDISTINCT AS,AS FROM WHERE,1.基本查询,例10.11查询学生表的全部字段。操作步骤如下：在“学生成绩管理”数据库窗口中选择“查询”对象。双击“在设计视图中创建查询”，关闭弹出的“显示表”对话框，打开查询设计视图窗口。选择“视图”菜单的“SQL视图”命令项，打开“选择查询”窗口。输入SQL语句：SELECT*FROM 学生 保存查询，查询建立完毕。运行查询。,例10.12 查询学生表中所有学生的姓名和截至统计时的年龄，去掉重名。SQL语句如下：SELECT DISTINCT 姓名,YEAR(DATE()-YEAR(出生日期)AS 年龄 FROM 学生例10.13 查询学生表中所有已通过四级的男生记录。SQL语句如下：SELECT*FROM 学生 WHERE 性别=男 AND 四级通过=yes,2.带特殊运算符的条件查询,SELECT语句可以使用的特殊运算符有：（1）BETWEENAND运算符格式：NOT BETWEEN AND BETWEEN运算符用于检测字段的值是否介于指定的范围内。（2）IN 运算符格式：NOT IN(,)IN运算符用于检测字段的值是否属于表达式集合或子查询。（3）LIKE运算符格式：LIKE LIKE运算符用于检测字段的值是否与样式字符串匹配。,例10.14:查询学生表中入学成绩在550570之间的学号、姓名、入学成绩。SQL语句如下：SELECT 学号,姓名,入学成绩 FROM 学生 WHERE 入学成绩 BETWEEN 550 AND 570上述语句的功能相当于：SELECT 学号,姓名,入学成绩 FROM 学生 WHERE 入学成绩=550 AND 入学成绩=570,例10.15：查询学生表中学号为070102和070401的记录。SQL语句如下：SELECT*FROM 学生 WHERE 学号 IN(070102,070401)上述语句的功能相当于：SELECT*FROM 学生 WHERE 学号=070102 OR 学号=070401,例10.16：查询学生表中姓“王”的学生的记录。SQL语句如下：SELECT*FROM 学生 WHERE 姓名 LIKE 王*,通配符“*”表示零个或多个字符“？”表示一个字符,3.计算查询,说明：（字段名）可以是字段名，也可以是SQL表达式。上述聚合函数可以用在SELECT 短语中对查询结果进行计算，也可以在HAVING子句中构造分组筛选条件。,例10.17：在学生表中统计学生人数。SQL语句如下：SELECT COUNT(*)AS 学生人数 FROM 学生例10.18：查询学生表中男生入学成绩字段的平均值、最大值和最小值。SQL语句如下：SELECT“男”AS 性别，AVG(入学成绩)AS 入学平均分 MAX(入学成绩)AS 入学最高分,MIN(入学成绩)AS 入学最低分 FROM 学生 WHERE 性别=男,4.分组与计算查询,格式：GROUP BY,HAVING 说明：分组关键字是分组的依据,可以是字段名，也可以是SQL函数表达式，还可以是字段序号（从1开始）。HAVING是对分组进行筛选的条件。HAVING只能与GROUP BY一起出现，不能单独使用。,例10.19：分别统计男、女学生人数和入学成绩的最高分和平均分。SELECT 性别,COUNT(性别)AS 人数，MAX(入学成绩)AS 入学最高分,AVG(入学成绩)AS 入学平均分 FROM 学生 GROUP BY 性别例10.20：在成绩表中统计有6个以上学生选修的课程。SQL语句如下：SELECT 课程号,COUNT(*)AS 选课人数 FROM 成绩 GROUP BY 课程号 HAVING COUNT(*)=6,例10.21：对1988年以后出生的学生分别按专业统计入学成绩，并输出入学平均成绩在560分以上的组。SELECT 专业,AVG(入学成绩)AS 入学平均分 FROM 学生 WHERE 出生日期=#1988-01-01#GROUP BY 专业 HAVING AVG(入学成绩)=560,说明：HAVING与WHERE的区别在于：WHERE是对表中所有记录进行筛选，HAVING是对分组结果进行筛选。在分组查询中如果既选用了WHERE，又选用了HAVING，执行的顺序是先用WHERE限定记录，然后对筛选后的记录按GROUP BY指定的分组关键字分组，最后用HAVING子句限定分组。,5.排序,格式：ORDER BY ASC|DESC,ASC|DESC TOP PERCENT说明：ASC表示对查询结果按指定字段升序排序。DESC表示对查询结果按指定字段降序排序；ASC|DESC缺省时默认值是升序。TOP必须与ORDER BY短语同时使用，表示从第一条记录开始，显示满足条件的前N个记录。选择PERCENT短语时，数值表达式表示百分比。,例10.22：在学生表中查询入学成绩在前3名的学生信息。SQL语句如下：SELECT TOP 3*FROM 学生 ORDER BY 入学成绩 DESC例10.23：显示年龄最小的30%的学生的信息。SQL语句如下：SELECT TOP 20 PERCENT*FROM 学生 ORDER BY 出生日期 DESC,SELECT命令支持多表之间的连接查询，并提供了专门的JOIN子句。格式：SELECT FROM INNER JOIN ON WHERE,其中：INNER JOIN用来连接左右两个指定的表，ON用来指定连接条件。,三.连接查询,例10.24：在职工管理数据库中查询高级职称（教授或副教授）教师的姓名、基本工资、津贴和所得税。SQL语句如下：SELECT 姓名,工资,津贴,所得税 FROM 职工 INNER JOIN 工资 ON 职工.职工号=工资.职工号 WHERE 职称 IN（“教授”，“副教授”）或者：SELECT 姓名,工资,津贴,所得税 FROM 职工,工资 WHERE 职工.职工号=工资.职工号 AND 职称 IN（“教授”，“副教授”）“职工号”是职工表和工资表的公共字段，“职工.职工号=工资.职工号”是连接条件。INNER JOIN 子句还可以嵌套，即在一个 INNER JOIN 之中，可以嵌套多个INNER ON 子句。,例10.25：输出所有学生每门课程的综合成绩单，要求给出学号、姓名、课程名和综合成绩信息。SQL语句如下：SELECT 学生.学号,姓名,平时*0.1+期中*0.2+期末*0.7 AS 综合成绩 FROM 学生 INNER JOIN(成绩 INNER JOIN 课程 ON 成绩.课程号=课程.课程号)ON 学生.学号=成绩.学号 或者：SELECT 学生.学号,姓名,课程名,平时*0.1+期中*0.2+期末*0.7 AS 综合成绩 FROM 学生,成绩,课程 WHERE 学生.学号=成绩.学号 AND 成绩.课程号=课程.课程号说明：由于学号字段在两个表中都出现，为了防止二义性，在其列名前加上表名作为前缀，以示区别。如果列名是唯一的，则不必加前缀。,例10.26：按各门课程期末平均成绩的降序输出每位学生的学号和期末平均成绩（保留小数1位）。SQL语句如下：SELECT 成绩.学号,ROUND(Avg(成绩.期末),1)AS 期末平均成绩 FROM 学生 INNER JOIN 成绩 ON 学生.学号=成绩.学号 GROUP BY 成绩.学号 ORDER BY 2 DESC,在SQL语言中，当一个查询是另一个查询的条件时，即在一个SELECT语句的WHERE子句中出现另一个SELECT语句，这种查询称为嵌套查询。通常把内层的查询语句称为子查询，调用子查询的查询语句称为父查询。SQL语言允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询。子查询的SELECT语句中不能使用ORDER BY子句，ORDER BY子句只能对最终查询结果排序。,四.嵌套查询,1带有比较运算符的子查询 带有比较运算符的子查询是指父查询与子查询之间用比较运算符（、=、）进行连接。例10.27：查询所有参加“计算机”课程考试的学生的学号。SELECT 学号 FROM 成绩 WHERE 课程号=(SELECT 课程号 FROM 课程 WHERE 课程名=计算机),例10.28：检索所有入学成绩高于于海涛的学生的学号、姓名、性别和入学成绩。SELECT 姓名,性别,入学成绩 FROM 学生 WHERE 入学成绩(SELECT 入学成绩 FROM 学生 WHERE 姓名=于海涛)例10.29：显示入学成绩高于男生平均入学成绩的女生的学号、姓名和平均成绩。SELECT 学号,姓名,入学成绩 FROM 学生 WHERE 性别=“女”AND入学成绩=(SELECT AVG(入学成绩)FROM 学生 WHERE 性别=“男”),2带有IN谓词的子查询格式：NOT IN()IN是属于的意思，指定的字段内容属于子查询中任何一个值，运算结果都为真。例10.30：查询所有参加“计算机”课程考试的学生的学号、姓名和性别。SELECT 学号,姓名,性别 FROM 学生 WHERE 学号 IN(SELECT 学号 FROM 成绩 WHERE 课程号=(SELECT 课程号 FROM 课程 WHERE 课程名=计算机),3带有ANY或ALL谓词的子查询格式：ANY|ALL（）注意：使用ANY或ALL谓词时必须同时使用比较运算符。例10.31：查询入学成绩高于女生最低入学成绩的男生的学号、姓名和入学成绩，SQL语句如下：SELECT 学号,姓名,性别,入学成绩 FROM 学生 WHERE 性别=“男”AND 入学成绩ANY(SELECT 入学成绩 FROM 学生 WHERE 性别=“女”）,用聚合函数来实现：SELECT 学号,姓名,性别,入学成绩 FROM 学生WHERE 性别=“男”AND 入学成绩=(SELECT MIN(入学成绩)FROM 学生 WHERE 性别=“女”)用聚合函数实现子查询通常比直接用ANY或ALL查询效率要高。,4带有EXISTS谓词的子查询格式：NOT EXISTS()带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值（true）或逻辑假值（false），即是否存在相应的记录。例10.32：查询参加了A02课程考试的学生学号和姓名。SQL语句如下：SELECT 学号,姓名 FROM 学生WHERE EXISTS(SELECT*FROM 成绩 WHERE 成绩.学号=学生.学号 AND 成绩.课程号=A02);带EXISTS的子查询只返回真值或假值，其输出项通常都用*。,例10.33：查询参加了全部课程考试的学生学号和姓名。可将题目的意思转换成：查询这样的学生，没有一门课程他没有成绩。SELECT 学号,姓名 FROM 学生WHERE NOT EXISTS(SELECT*FROM 课程 WHERE NOT EXISTS(SELECT*FROM 成绩 WHERE 成绩.学号=学生.学号 and 成绩.课程号=课程.课程号),格式：UNION ALL 说明：ALL缺省时，自动去掉重复记录，否则合并全部结果。例10.34：创建联合查询，将已经建立的查询“例10_14”的记录与学生表中入学成绩在550以下的记录合并。SELECT 学号,姓名,入学成绩 FROM 例10_14 UNION SELECT 学号,姓名,入学成绩 FROM 学生 WHERE 入学成绩550注意：要求合并的两个SELECT语句必须输出相同的字段个数，对应字段必须具有相同的数据类型和长度。,五.联合查询,例10.35：合并学生表和成绩表中的学号。SELECT 学号FROM 学生 UNION SELECT 学号 FROM 成绩在成绩表中，尽管学号比较多，但大部分与学生表重复，合并后，重复的内容就自动去掉了，所以结果输出的学号最多与学生表中的学号个数一致。,Access的传递查询是自己并不执行，而是传递给另一个数据库执行。创建传递查询，一般要完成两项工作，一是设置要连接的数据库，二是在SQL窗口中输入SQL语句。整个操作分成3个阶段：（1）打开查询属性对话框 在数据库窗口中选择“查询”对象 双击“在设计视图中创建查询”，关闭弹出的“显示表”对话框 选择“查询”菜单中“SQL特定查询”下的“传递”命令项，打开传递查询设置窗口 单击工具栏上的“属性”按钮，打开“查询属性”对话框。,六.传递查询,（2）设置要连接的数据库 在“查询属性”对话框中，单击“ODBC连接字符串”的生成器按钮 打开“选择数据源”对话框，单击“机器数据源”选项卡 如果要选择的数据源已经在列表框中，则直接在列表框中选择，如不存在，则单击“新建”按钮，在新打开的各个对话框中输入要连接的服务器信息。（3）建立传递查询 在SQL传递查询窗口中输入SQL查询命令，单击工具栏上的“运行”按钮，得到查询结果。,本章小结,本章较全面的介绍了关系数据库标准语言SQL，包括数据定义、数据查询和数据操作功能，以及SQL在Access中的应用。SQL已经成为Access的基本内容，不掌握SQL要用好Access基本是不可能的。,