结构化查询语言SQL补充.ppt

Access Access 数据库与程序设计数据库与程序设计 第5章 结构化查询语言SQL oSQL语言简介 oSQL语言的数据定义功能 n建立表结构、修改表结构、删除数据表 oSQL语言的数据操纵功能 n记录的插入、记录的删除、记录的更新 oSQL语言的查询功能 n简单查询 n条件查询 n连接查询 n嵌套查询 n统计查询 n分组查询 n查询排序 n合并查询 n查询的输出 第5章 结构化查询语言SQL o结构化查询语言SQL（Structured Query Language），是关系数据库的标准语言，它具 有数据定义、数据操纵、数据查询和数据控制 等功能，并且其语言简洁、功能强大，目前广 泛用于各大、中、小型关系数据库，已成为世 界上最流行的数据库语言之一。 结构化查询语言SQL SQL语言的产生和发展 o1974年由Boyce和Chamberlin提出，并作为IBM公司研制的关系数 据库管理系统原型SYSTEM R的查询语言文本。 o1982年，ANSI（美国国家标准局）成立SQL标准委员会。 o1986年10月，ANSI SQL标准被批准作为关系数据库语言的美国标 准。公布了SQL标准文本（SQL-86）。 o1987年6月，ISO（国际标准化组织）将其采纳为国际标准。 oSQL-89、SQL92和SQL99等。 o目前SQL已被许多DBMS产品如，DB2、INGRES、SYSBASE、 SQLServer、VFP和ORACLE等所采用，它已成为关系数据库领域中 一个主流语言。 结构化查询语言SQL oSQL语言的特点 o一体化 n将所有的对数据库的定义、查询、更新、控制、维护、 恢复、安全等一系列操作要求统一为一种语言。 o非过程化 n交互式SQL是一种自含式语言，可独立使用，用于一般 用户建立中、小型管理信息系统 n嵌入式SQL是将SQL语言嵌入到高级语言中，用户可利 用高级语言灵活地表达计算功能和SQL数据处理功能， 以便完成各种复杂的事务处理。 结构化查询语言SQL o简洁化 nSQL语言功能强大并且只用几条命令来完成，其 语法也非常简单，很接近自然语言模式。SQL的 命令有：数据查询命令（SELECT）、数据定义 命令（CREATE、DROP、ALTER）、数据操纵 命令（INSERT、UPDATE、DELETE）和数据 控制命令（GRANT、REVOKE）。 5.2 SQL语言的数据定义功能 o打开数据库。 o在“设计视图” 新建查询，在弹出的“显示表”对话框中直接点击“关闭”。 “查询”“SQL特定查询”- “数据定义查询” 5.2 SQL语言的数据定义功能 建立数据表结构 定义数据表的结构。 oCREATE TABLE ( () NOT NULL PRIMARY KEY | UNIQUE|REFERENCES 外部表 (外部关键字) ， () NOT NULL PRIMARY KEY | UNIQUE|REFERENCES 外部表 (外部关键字) ，.) SQL-建立表结构 o建立一个“学生名册”表结构 （CREATE TABLE命令） oCREATE TABLE 学生名册 (学号 TEXT(10) PRIMARY KEY, o 姓名 TEXT(8),性别 TEXT(2), o 出生日期 DATE,院系 TEXT(12), o 总分 INTEGER, 奖学金 LOGICAL, o 简历 MEMO, 照片 OLEOBJECT) o 选择“数据定义查询”类型 写入SQL语句 运行查询 使用SQL创建的表结构 oCREATE TABLE 课程表 o ( 课程号 TEXT(6) PRIMARY KEY, o 课程名 TEXT(20), o 开课院系 TEXT(12), o 学分 INTEGER, o 学时 TEXT(2) ) SQL-建立表结构 o建立“成绩表”,并与“学生名册”建立关 系 oCREATE TABLE 成绩 o (学号 TEXT(10) REFERENCES 学生名册(学号), o 课程号 TEXT(6),课序号 TEXT(2), 成绩 INTEGER) 2、修改基本表 o增加字段 ALTER TABLE 表名 ADD 字段名 数据类型 o修改字段 ALTER TABLE 表名 ALTER 字段名 数据类型 o删除字段 ALTER TABLE 表名 DROP 字段名 SQL-修改表结构 SQL-修改表结构 o例：为“学生名册”增加字段一个“电子邮件”字段 ALTER TABLE 学生名册 ADD 电子邮件 TEXT(20) 增加字段SQL查询 5.3 SQL语言的数据操纵功能 o1 SQL-插入数据 INSERT INTO 表名 （列名1，列名2） VALUES (表达式1，表达式2) 【例】向“学生情况”表中插入一个新的学生记录 INSERT INTO 学生情况2 VALUES (“112“, “王汉“, “男“, #10/21/1980#, “586“, TRUE, “足球“, “1“) 5.3 SQL语言的数据操纵功能 o【例】 使用SQL命令向“课程”表中追加插入一条包 含部分信息的新记录。 nINSERT INTO 课程 (课程号,课程名称,学分) VALUES(“201003“,“CAD设计“,2) n如果仅为表中的部分字段赋值，应注意字段名称与 字段值要一一对应。 5.3 SQL语言的数据操纵功能 o2SQL-修改数据 UPDATE 表名 SET 列名1表达式1，列名2表达式2 WHERE 条件 UPDATE 学生情况2 SET 性别“女” WHERE 姓名“王汉” 数据操纵 o3SQL-删除数据 DELETE FROM 表名 WHERE 条件 5.4 SQL语言的查询功能 oSELECT ALL|DISTINCT 表别名. 字段名AS 列名,. FROM 数据库名!表名 WHERE 条件 GROUP BY 分组名 HAVING 条件 UNIONALLSELECT语句 ORDER BY 排序项 分组中的条件 o【命令】 o SELECT ALL | DISTINCT TOP PERCENT o AS INTO o FROM AS , AS o WHERE AND . o AND | OR AND | OR . o GROUP BY , . oHAVING o UNION ALL o ORDER BY ASC | DESC , ASC | DESC . 查询实例 o相关表： o学生信息表 o学生成绩表 o课程信息表 o单表查询 o例1：例出全部学生信息 SELECT * FROM 学生信息表 “*” 全部数据 SELECT-SQL简单查询 【例5-11】查询“学生信息表”中所有学生的姓名 、性别、出生日期。 SELECT 姓名 AS 学生姓名,性别, 出生日期 AS 生日 FROM 学生信息表 SELECT-SQL简单查询 o单表查询 o例2：例出所有女生的学号及姓名 SELECT 学号,姓名 FROM 学生信息表 WHERE 性别=“女” o单表查询 o例3：例出所有学生的性别 SELECT DISTINCT 性别 FROM 学生信息表 * 列出性别为“男”的学生 SELECT 姓名，性别 FROM 学生情况 WHERE 性别=“男” SELECT-SQL简单查询 o单表查询 o例4：例出课程号为”303001”所有成绩大于80分的学生学号及 成绩，并按成绩由高到低排序。 SELECT 学号，成绩 FROM 学生成绩表 WHERE 课号=“303001” AND 成绩=80 ORDER BY 成绩 DESC SELECT-SQL简单查询 SELECT-SQL简单查询 o单表查询 o例5：查询84年以后出生的学生。 SELECT 姓名，出生日期 FROM 学生信息表 WHERE 出生日期=#1984-01-01# 注意日期的写法 SELECT-SQL简单查询 o单表查询 o例6：查询不是“艺术学院”的学生。 SELECT 姓名，院系名称 FROM 学生信息表 WHERE 院系名称 =70 AND 成绩=600 AND 性别=男 oSELECT COUNT (姓名) AS 合格人数 from 学生信息表 WHERE 入学总分 =600 AND 性别=男 SELECT-SQL统计查询 例10：求全体学生的入校总分、最高分、最低分和平均分 SELECT SUM(入学总分) AS 新生入校总分, MAX(入学总分) AS 新生最高分, MIN(入学总分) AS 新生最低分, AVG(入学总分) AS 新生平均分 FROM 学生信息表 SELECT-SQL统计查询 例11：求入校总分在580分以上的学生人数 SELECT COUNT(*) AS 总分大于580的人数 FROM 学生信息表 WHERE 入学总分 580 SELECT-SQL统计查询 例13：查出学生成绩表中选修“101002”课程的最高分、最低分和相差分数 SELECT MAX(成绩) AS 最高分, MIN(成绩) AS 最低分, MAX(成绩)- MIN(成绩) AS 相差分数 FROM 学生成绩表 WHERE 学生成绩表.课程号 = “101002“; SELECT-SQL连接查询 o例14.查询成绩在90分以上的学生记录，并显示该生的学号、姓名 、课程号和成绩相关表：学生情况、选课表 o Where 连接: 只连接相等的记录 o 连接条件： WHERE 学生信息表.学号=学生成绩表.学号 AND 成绩=90 SELECT 学生信息表.学号,学生信息表.姓名,学生成绩表.课程号,学生成绩表.成绩 from 学生信息表, 学生成绩表 where 学生信息表.学号=学生成绩表.学号 AND 成绩=90 SELECT a.学号,姓名,课程号,成绩 from 学生信息表 a, 学生成绩表 b where a.学号=b.学号 AND 成绩=90 临时定义别名 o INNER JOIN 连接 oSELECT 学生信息表2.姓名, 学生成绩表2.成绩 oFROM 学生信息表2 INNER JOIN 学生成绩表2 ON 学生信息表2.学 号 = 学生成绩表2.学号 oWHERE (学生成绩表2.成绩)=90); SELECT 学生信息表2.姓名, 学生成绩表2.成绩 FROM 学生信息表2 LEFT JOIN 学生成绩表2 ON 学生信息表2.学号 = 学生成绩表2.学号 WHERE (学生成绩表2.成绩)=90); LEFT JOIN 连接 RIGHT JOIN 连接 SELECT 学生信息表2.姓名, 学生成绩表2.成绩 FROM 学生信息表2 RIGHT JOIN 学生成绩表2 ON 学生信息表2.学号 = 学生成绩表2.学号 WHERE (学生成绩表2.成绩)=90); SELECT-SQL连接查询 o例15. 列出选修1号课的学生姓名及成绩。 n相关表：学生情况 n 选课表 o 连接条件： oWHERE 学生名册.学号=选课表.学号 AND 课号=“1” o SELECT * o FROM 学生情况 AS S, 选课表 AS C o WHERE S.学号=C.学号 AND 课号=“1“; o临时定义表的别名S和C可以减少书写长的表名 分组查询:GROUP BY o例18. 列出各门课的平均成绩、最高成绩、最低成绩和 选课人数。 oSELECT 学生成绩表2.课程号, Avg(学生成绩表2.成绩) AS 成绩之平均值, Max(学生 成绩表2.成绩) AS 成绩之最大值, Min(学生成绩表2.成绩) AS 成绩之最小值, Count(学生成绩表2.学号) AS 学号之计数 oFROM 学生成绩表2 oGROUP BY 学生成绩表2.课程号; oSELECT 学生成绩表2.课程号, ROUND(Avg(学生成绩表2.成绩),2) AS 平 均分, Max(学生成绩表2.成绩) AS 最高分, Min(学生成绩表2.成绩) AS 最低分, Count(学生成绩表2.学号) AS 选课计数 oFROM 学生成绩表2 oGROUP BY 学生成绩表2.课程号; ROUND(X,N) :N给出小数位数 HAVING 子句(函数) oHAVING函数与GROUP BY子句联合使用,可以对分组后的 结果作进一步的限制. o例18a. 列出选课人数超过3人的各门课的平均成绩、 最高成绩、最低成绩和选课人数。 oSELECT 学生成绩表2.课程号, Round(Avg(学生成绩表2.成绩),2) AS 平均分, Max(学生成绩表2.成绩) AS 最高分, Min(学生成绩 表2.成绩) AS 最低分, Count(学生成绩表2.学号) AS 选课计数 oFROM 学生成绩表2 oGROUP BY 学生成绩表2.课程号 oHAVING Count(学生成绩表2.学号)3 o计算： oSELECT 学生信息表2.姓名, 学生信息表2.性别, Year(Date()-Year(学生信息表2!出生日期) AS 年龄 oFROM 学生信息表2; WHERE Year(Date()-Year(学生信息表2!出生日期)=20 SQL嵌套查询 o一个SELECT-FROM-WHERE语句中嵌套进另外一个 SELECT-FROM-WHERE称为嵌套查询。上层查询称为外 层查询或父查询，下层查询被称为内层查询或子查 询。 o子查询中不能使用ORDER BY语句，因为该语句是对 最终结果进行排序。 o嵌套查询的运行方式是由里向外，也就是说，每个 子查询都先于它的父查询执行，而子查询的结果作 为其父查询的条件。 嵌套查询 如果查询所要求的结果出自一个关系，而相关的条件却涉及多 个关系，则可以利用嵌套查询来完成。 o嵌套查询一般有2种形式： o ANY|ALL|SOME () oNOT EXISTS () o说明： （1）其中比较运算符可以为 NOTIN/NOTBETWEENAND/NOTLIKE （2）ANY和SOME是同义词，在进行比较运算时，只要子查询中 有一条为真，则结果为真；而ALL则要求子查询中全部记录 都为真，结果才为真。 （3）EXISTS是谓词，检查子查询中是否有结果返回。 o【例】查询哪些专业至少有一学生成绩等于100分 oSELECT DISTINCT 专业代号 FROM 学生 WHERE 学生.学号 IN （SELECT 学号 FROM 成绩表 WHERE 成绩表.成绩=100） o注意： o(1)这里所要查询的结果是“学生表”的专业信息，而相关的条件却是 “成绩表”的成绩字段的值，因此使用嵌套查询； o(2) “SELECT 学号 FROM 成绩表 WHERE 成绩表.成绩=100） ”是 子查询，且用括号包含。 【例】查询与“张三”专业相同的学生姓名 oSELECT 姓名 FROM 学生; WHERE 学生.专业代码= (SELECT 专业代码 FROM 学生 WHERE 姓名 in (“张三”) 【例】在成绩表中检索选修2号课程的学生中比选修4号课程的最低分要 高的学生学号和成绩。 oSELECT