第5章 结构化查询语言SQL课件.ppt

《第5章 结构化查询语言SQL课件.ppt》由会员分享，可在线阅读，更多相关《第5章 结构化查询语言SQL课件.ppt（77页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、数据库案例教程数据库案例教程第第5章章 结构化查询语言结构化查询语言SQL本章选修本章选修学习要点（学习要点（本章内容学生可以自学本章内容学生可以自学） SQL语言的基本概念、特点语言的基本概念、特点 SQL语言的功能语言的功能 SQL语言的用法语言的用法 通过本章的学习，了解通过本章的学习，了解SQL语言及其标语言及其标准的发展、准的发展、SQL语言的特点及分类、视图相语言的特点及分类、视图相关语句，熟悉关语句，熟悉SQL语言中各种语句的语法，语言中各种语句的语法，熟悉熟悉SQL数据定义语言（数据定义语言（DDL）语句，掌）语句，掌握握SQL语言中数据查询、数据操纵语言的详语言中数据查询、数

2、据操纵语言的详细语法，并能深刻理解、综合应用，以便为细语法，并能深刻理解、综合应用，以便为今后深层次的学习打下更加坚实的基础。今后深层次的学习打下更加坚实的基础。学习目标学习目标重点难点v 重点：重点：v 1、SELECT命令的使用，如：使用SELECT命令实现基本查询、条件查询、嵌套查询、多表查询和连接查询。v 2、查询结果的处理，输出重定向，查询结果的排序、查询分组和统计等。v 3、用查询设计器构造SELECT语句。v 难点：难点：v 1、SELETE命令的使用。特别是条件查询、嵌套查询、多表查询和连接查询的实现。v 2、查询结果的排序、分组和统计处理。 5.1 SQL语言概述vSQLSt

3、ructured Query Language，结构化查询语言 1986年，由美国ANSI确定为关系数据库的标准语言。 1987年，ISO将其定为国际标准。 1989年，ISO提出了具有完整性特征的SQL，成为SQL89 1992年公布了SQL的新标准，即SQL-92。 SQL99SQL3SQL语言的特点vSQL是一种结构一体化的语言。它包括了数据定义、数据查询、数据操纵和数据控制等功能，可以完成数据库活动中的全部工作。SQL语言的组成v数据定义：CREATE、DROP(删除)、 ALTER(修改) (删除某记录)v数据操纵：INSERT、UPDATE、DELETEv数据控制：GRANT、RE

4、VOKEv数据查询：SELECT(选择工作区)SQL语言的组成(1)数据定义语言(Data Definition Language，简称DDL) 用于定义SQL模式、基本表、视图和索引。(2)查询语言(Query Language，简称QL) 用于数据查询。(3)数据操纵语言(Data Manipulation Language，简称DML) 用于数据的增、删、修改。(4)数据控制语言(Data Control Language，简称DCL) 用于数据访问权限的控制。5.2 数据定义语言vCREATE vALTERvDROP创建表v在CREATE TABLE中可以使用的数据类型及说明：vC、N

5、 、 D 、 T 、L、M、G创建表CREATE TABLE定义基本表的语句格式为 :CREATE TABLE( NOT NULL UNIQUE ,属性名2类型2 NOT NULL UNIQUE ) 其他参数；v 例5-1：学生成绩数据库含有三张表：v 学生关系：S(SNO,SNAME,SEX, AGE, DNAME)v 课程关系：C(CNO, CNAME, CREDIT, PRE_CNO)v 选课关系：SC(SNO, CNO, SCORE)v 可用下列SQL语句来实现： 创建表CREATE TABLE CREATE TABLE S ( SNO CHAR(6) PRIMARY KEY， SNA

6、ME CHAR(8) NOT NULL， AGE SMALLINT， SEX CHAR(1)， DNAME VARCHAR(12)；CREATE TABLE C ( CNO CHAR(2) NOT NULL, CNAME VARCHAR(24) NOT NULL, CREDIT SMALLINT, PRE_CNO CHAR(2), PRIMARY KEY(CNO);CREATE TABLE SC ( SNO CHAR(6) NOT NULL, CNO CHAR(2) NOT NULL, SCORE SMALLINT, PRIMARY KEY(SNO,CNO), FOREIGN KEY(SNO)

7、 REFERENCES S ON DELETE CASCADE, FOREIGN KEY(CNO) REFERENCES C ON DELETE NO ACTION);创建表v 例1、使用命令建立数据库XSK，用SQL命令在该库中建立STUDENT表，结构及要如下。字段名字段类型字段长度特殊要求学号C7主索引姓名C8不能为空性别C2年龄N3入学年月D是否党员L备注M创建表创建表v例2、在XSK中建立score表，结构如下。字段名字段类型字段长度小数位数学号C7课号C5期末N52修改表结构ALTER TABLEv添加字段： ALTER TABLE 表名 ADD COLUMN 字段名 字段类型 (

8、宽度)v例：为SCORE表添加添加两个字段，“平时(N(5,1)”和“期中(N(5,1)”。修改表结构v修改字段 ALTER TABLE 表名 ALTER COLUMN 字段名 字段类型(宽度)v例：在score中，修改“期末”字段为N(5，1)。修改表结构v删除字段 ALTER TABLE 表名 DROP COLUMN 字段名v例：在student表中，删除“是否党员”字段。修改表结构v字段更名 ALTER TABLE 表名 RENAME COLUMN 原字段名 TO 新字段名v例：在STUDENT表中，把“备注”字段名改为“其它情况”。修改表结构v改变表的结构时，索引表达式、字段和表的有效

9、性规则、命令、函数等等可能仍会引用原始字段名，因此有时不能执行命令。删除表DROP TABLEvDROP TABLE 表名v执行了 DROP TABLE 之后，所有与被删除表有关信息都将丢失。v例：删除SCORE表。5.3 数据操纵语言vINSERTvDELETEvUPDATE插入记录INSERT v INSERT INTO 表名 字段名1，字段名2 VALUE(表达式1,表达式2)v 如果要插入表中所有字段的数据，可省略字段名。v 如果省略了字段名，则必须按照表结构定义字段的顺序来指定字段值。v 例1、向STUDENT表中添加记录。插入记录v INSERT INTO 表名 FROM ARRA

10、Y 数组名v INSERT INTO 表名 FROM MEMVARv 把内存变量的内容插入到与它同名的字段中。如果某一字段不存在同名的内存变量，则该字段为空。v 例1、从数组向表中添加记录。v例2、利用内存变量向表中添加记录。删除记录DELETEv DELETE FROM 数据库名 表名WHERE 逻辑表达式1 AND|OR 逻辑表达式2v 为指定的表中的记录加删除标记。如果要删除当前数据库中的表的记录，可省略数据库名，否则必须加上包含有该表的数据库名。在数据库名的后面、表名的前面包含感叹号 (!) 分隔符。v 例：将STUDENT表中所有男生的记录删除。UPDATEvUPDATE 数据库名！

11、表名 SET 字段名1=表达式1,字段名2=表达式2WHERE 逻辑表达式1 AND|OR 逻辑表达式2v如果省略了 WHERE 子句，在列中的每一行都用相同的值更新。v例：将STUDENT表中所有99级学生的“入学年月”改为“1999/09/10”。 索引的建立和删除 建立索引的语句格式为： CREATE UNIQUE INDEX 索引名 ON 基本表名(ASC|DESC, ASC|DESC)；例如，对表S建立以下索引 CREATE UNIQUE INDEX SNO_INDEX ON S (SNO)； CREATE UNIQUE INDEX SNAME_ADDR_INDEX ON S (SN

12、AME ASC，HOSTADDR DESC)； 删除索引的语句格式： DROP INDEX 索引名；5.4 数据查询SELECTv SELECT FROMWHEREv 功能：从一个表或多个表中查询数据。 SELECT指定查询哪些字段 使用WHERE子句限定记录 使用FROM子句限定表名v 含义：根据WHERE子句的条件表达式，从FROM子句指定的表中找出满足条件的记录，再按SELECT子句中的字段表达式，选出记录形成结果表。v在VFP中，查询就是一个扩展名为QPR的查询文件，其中的内容就是一条SELECT语句。5.4 数据查询SELECT5.4.1 基本查询基本查询就是单表查询，：SELECT

13、 ALL|DISTINCT *|列表达式 FROM 学生nALL:查询结果中包含所有行 ( 包括重复值 ), 是默认设置。nDISTINCT:在查询结果中剔除重复的行。n注意 每一个 SELECT 子句只能使用一次DISTINCT。n*表示所有字段。n列表达式可以是字段名，表达式，字符串常量、函数等。基本查询v例1:列出“学生”表中所有同学名单 SELECT * FROM 学生v例2:列出成绩表中所有学生的学号，去掉重复值。 SELECT DISTINCT 学号 FROM 成绩SELECT命令中的函数v例3、列出所有学生的学号，姓名和入学成绩，其中入学成绩四舍五入保留整数。 SELECT 学号

14、,姓名,ROUND(入学成绩,0) AS “入学成绩” FROM 学生vAS :指定查询结果中列的标题。可省略。v例4、列出学生表中所有学生的年龄。 SELECT 姓名, YEAR(DATE()-YEAR(出生日期) AS 年龄 FROM 学生v例5、求出学生总数。 SELECT COUNT（*） FROM 学生v例6、查询选修了课程的学生人数。 SELECT COUNT(DISTINCT 学号) FROM 成绩课堂练习v1、查询全体学生的姓名、学号、专业。v2、查全体学生的姓名及其出生年份。v3、查询成绩表中的课号，去掉重复值。v4、查询成绩表中平时，期中，期末的平均成绩。5.4.2 带条件

15、查询vWHERE子句可指定查询的条件。v格式:WHERE 条件表达式 AND|OR条件表达式2WHERE比较操作符n例1、查询所有的男生记录。nSELECT * FROM 学生 WHERE 性别=“男”n例2、求出计算机专业学生入学成绩的平均分。nSELECT 专业,AVG(入学成绩) AS 入学成绩平均分 FROM 学生 WHERE 专业=“计算机” v例3、列出非计算机专业的学生名单。 SELECT * FROM 学生 WHERE 专业“计算机”v例4、统计计算机专业入学成绩在600分以上的学生的人数。 SELECT COUNT(姓名) 人数 FROM 学生 WHERE 入学成绩=600

16、AND 专业=“计算机”WHEREINvWHERE子句中的IN谓词用来确定查询的集合。v例1、查询计算机专业和外语专业的学生姓名和专业。 SELECT 姓名,专业 FROM 学生 WHERE 专业 IN (“计算机”,“外语”) 相当于 SELECT 姓名,专业 FROM 学生 WHERE 专业 =“计算机” OR 专业=“外语”)v例2、查询既不是计算机专业也不是外语专业的学生姓名和专业。 SELECT 姓名,专业 FROM 学生 WHERE 专业 NOT IN (“计算机”,“外语”)WHEREBETWEENvWHERE子句中的BETWEEN运算符用来确定范围。v格式： BETWEENAN

17、D或NOT BETWEENAND 其中BETWEEN后是范围的下限(低值),AND后是范围的上限(高值)。v例1、列出入学成绩在560分到600分之间的学生名单和成绩。 SELECT 姓名,入学成绩 FROM 学生 WHERE 入学成绩 BETWEEN 560 AND 600 上述命令还可写成如下形式： SELECT 姓名,入学成绩 FROM 学生 WHERE 入学成绩=560 AND 入学成绩=600WHERE LIKEv WHERE中的LIKE子句用来进行字符串的匹配。v 格式：NOT LIKE “字符串” v 含义:查找指定的字段值中与匹配符相匹配的记录。v 匹配符可以是字符串，也可以含

18、有通配符。v 例:查询学号为0108015的同学的详细情况 SELECT * FROM 学生 WHERE 学号 LIKE “0108015”LIKE中的通配符v% 0任意多个字符v_ 任意单个字符va%b可代表ab，acb，addgbvA_b可代表acb,afb,akb等。LIKE中的通配符v例1、列出学生表中所有姓李的同学。 SELECT 学号,姓名 FROM 学生 WHERE 姓名 LIKE “李%”v例2、列出学生表中所有不姓李且姓名是两个字的同学名单 SELECT 学号,姓名 FROM 学生 WHERE 姓名 NOT LIKE “李%” AND 姓名 LIKE “_ _”LIKE中的通

19、配符v如果要查询的字符串本身就含有%或_，就要使用ESCAPE “换码字符”对通配符进行转义。v例：查询以DB_开头的课程的课程号和学分。vSELECT 课号,学分 FROM 课程 WHERE 课名 LIKE “DB_%” ESCAPE v如果LIKE后面的匹配串中不含通配符,则可以用=取代LIKE,用取代NOT LIKE涉及空值的查询v 空值指不确定的值, 查询空值要使用 IS NULL,不能用比较运算符=NULLv 例1、列出成绩表缺少期末成绩的学生学号和课号 SELECT 学号,课号 FROM 成绩 WHERE 期末 IS NULLv 例2、查询所有有期末成绩的学生学号和课号 SELEC

20、T 学号,课号 FROM 成绩 WHERE 期末 IS NOT NULL对查询结果排序v使用ORDER BY子句可以对查询结果按照一个或多个字段的值排序输出v语法:ORDER BY 排序选项1 ASC|DESC,排序选项2 ASC|DESCv例1、按专业顺序列出学生的学号，姓名和专业，同一专业按学号排列。 SELECT 专业,学号,姓名 FROM 学生 ORDER BY 专业,学号只显示查询的前几项记录v 使用TOP 表达式 PERCENT短语可以列出满足条件的前几个或前百分之几记录v TOP短语要与ORDER BY同时使用才有效v 例1、显示入学成绩最高的前五名同学的记录 SELECT *

21、FROM 学生 ORDER BY 入学成绩TOP 5 DCSCv 例2、显示入学成绩最高的前50%同学的记录 SELECT * TOP 50 PERCENT FROM 学生 ORDER BY 入学成绩 DESC对查询结果分组v使用GROUP BY子句可以对查询结果进行分组,分组的目的是为了细化统计函数的作用对象v语法:GROUP BY 分组选项 HAVING 条件v例1、统计各个课号及相应的选课人数 SELECT 课号,COUNT(课号) 选课人数 FROM 成绩 GROUP BY 课号vHAVING条件与WHERE子句的区别:作用对象不同,WHERE子句作用于表或视图;HAVING短语作用于

22、组.v例2、查询选修了3门以上课程的学生学号 SELECT 学号 FROM 成绩 GROUP BY 学号 HAVING COUNT(*)3连接查询v若一个查询同时涉及两个或两个以上的表,则称之为连接查询。v连接查询是关系数据库中最主要的查询，包括等值连接、自然连接、非等值连接、自身连接、外连接和复合条件连接查询。v 等值与非等值连接查询：连接查询中用来连接两个表的条件称为连接条件或连接谓词，连接谓词为=时，称为等值连接；连接谓词为其它比较运算符时，为非等值连接。v 形式：表名1.列名1 连接谓词 表名2.列名2v 上述的列名称为连接字段,各连接字段必须有可比性,但不一定相同。v DBMS执行连

23、接操作的过程是：先在表1中找到第1条记录，然后从头开始扫描表2，逐一查找满足连接条件的记录，找到后就将表1中的第1条记录与该记录拼接起来，形成结果表中的一条记录。表2全部查找完后，再找表1中的第2条记录，然后再从头开始扫描表2，逐一查找满足连接条件的记录，找到后就将表1中的第2条记录与该记录拼接起来，形成结果表中的一条记录。重复上述过程，直到表1中的全部记录都处理完为止。v 例1、查询每个学生及其选课情况 SELECT 学生.*,成绩.* FROM 学生,成绩 WHERE 学生.学号=成绩.学号v 在等值连接中把目标列中重复的字段去掉则为自然连接。v 例2、对例1用自然连接完成。 SELECT

24、 学生.学号,姓名,性别,专业,出生日期,入学成绩,课号,平时,期中,期末 FROM 学生,成绩 WHERE 学生.学号=成绩.学号v例3、查询所有学生的成绩单，要求输出学号、姓名、课号、课名和总成绩。 SELECT a.学号,姓名,b.课号,课名,(平时+期中+期末)/3 AS 总成绩 FROM 学生 a,成绩 b,课程 c WHERE a.学号=b.学号 AND b.课号=c.课号 v例3中给每个表定义了别名,以方便在查询的其它部分使用。自身连接v 一个表与其自己进行连接,称为表的自身连接。v 自身连接必须指定别名。v 例4、列出选修“09001”课的学生中，期末成绩大于学号为“01010

25、19”的同学该门课期末成绩的那些同学的学号及其期末成绩。 SELECT a.学号,a.期末 FROM 成绩 a,成绩 b WHERE a.课号=“09001” AND b.学号=“0101019” AND a.期末b.期末 AND a.课号=b.课号内部连接(Inner Join)v在通常的连接中,将所有满足条件的记录都作为结果输出,称为内连接。v内连接的方式：Inner Join ONv例:查询所有学生的成绩单，要求输出学号、姓名、课号、课名和总成绩。 SELECT a.学号,姓名,b.课号,课名,(平时+期中+期末)/3 AS 总成绩 FROM 学生 a INNER JOIN 成绩 b I

26、NNER JOIN 课程 c ON b.课号=c.课号 ON a.学号=b.学号v内连接中JOIN的顺序和ON的顺序至关重要。JOIN连接的顺序和ON条件的顺序给出正好相反。vInner Join等价于Join。v内连接可使用WHERE 子句代替。外连接（Outer Join）v在通常的连接中，都是把满足连接条件的记录添加到结果表中，在VFP中，还可以使不满足条件的记录也出现在结果表中，称为外连接。v外连接包括： 左连接LEFT JOIN 右连接RIGHT JOIN 全连接FULL JOINv例1、列出每个学生的基本情况及其选课情况。 SELECT 学生.学号,姓名,性别,专业,课号,期末 F

27、ROM 学生 LEFT JOIN 成绩 ON 学生.学号=成绩.学号v在上例中使用右连接和全连接,加以比较。查询嵌套v 在SQL中，一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。v 例1、查询选修 “09001”课的学生名单。 SELECT 姓名 FROM 学生,成绩 WHERE 学生.学号=成绩.学号 AND 课号=“09001” 上述查询还可写成如下形式: SELECT 姓名 FROM 学生 WHERE 学号 IN (SELECT 学号 FROM 成绩 WHERE 课号=“09001”)

28、查询嵌套v 嵌套查询要求子SELECT的查询结果必须是确定的内容。v 嵌套查询一般是由里向外处理，即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。v SQL允许多层嵌套查询。但VFP只能支持单层嵌套。v 子查询中不能使用ORDER BY子句，即ORDER BY只能对最终查询结果排序。查询嵌套v嵌套查询使我们可以用多个简单查询构成复杂的查询，从而增强SQL的查询能力。v以层层嵌套的方式来构造程序正是SQL中“结构化”的含义所在。查询嵌套v 例1、查询与“夏天”同在一个专业学习的学生名单。 先分步来完成此查询 第一步:确定夏天所在的专业 SELECT 专业 FROM

29、学生 WHERE 姓名=“夏天” 结果为“计算机” 第二步:查找所有在“计算机”专业的学生名单 SELECT 学号,姓名,专业 FROM 学生 WHERE 专业=“计算机” 将第一步查询嵌入到第二步查询的条件中,构造嵌套查询的SQL语句如下: SELECT 学号,姓名,专业 FROM 学生 WHERE 专业 IN (SELECT 专业 FROM 学生 WHERE 姓名=“夏天”)v上例查询也可以用自身连接来完成 SELECT a.学号,a.姓名,a.专业 FROM 学生 a,学生 b WHERE a.专业=b.专业 AND b.姓名=“夏天”v可见在SQL中,实现同一个查询可以有多种方法。带谓

30、词的子查询v 子查询返回单值时可以使用比较运算符。还可使用ANY、ALL或EXISTS谓词。 ALL：满足子查询中的所有值的记录。 ANY(SOME)：满足子查询中任何一个值的记录。 EXISTS:检查子查询中是否存在记录,返回值为逻辑值。v 例1、查询其它专业比计算机系所有学生年龄都小的学生姓名及年龄。 SELECT 姓名,年龄 FROM 学生 WHERE 年龄ALL (SELECT 年龄 FROM 学生 WHERE 专业=“计算机”) 上述查询也可写成如下形式: SELECT 姓名,年龄 FROM 学生 WHERE 年龄 (SELECT MIN(年龄) FROM 学生 WHERE 专业=“

31、计算机”) v例2、查询其它系中比计算机系某一学生年龄小的学生姓名和年龄 SELECT 姓名,年龄 FROM 学生 WHERE 年龄ANY (SELECT 年龄 FROM 学生 WHERE 专业=“计算机”) 上述查询也可以写成如下形式 SELECT 姓名,年龄 FROM 学生 WHERE 年龄 (SELECT MAX(年龄) FROM 学生 WHERE 专业=“计算机”) v例3、查询没有选修“09001”课程的学生姓名. SELECT 姓名 FROM 学生 WHERE NOT EXISTS (SELECT * FROM 成绩 WHERE 学号=学生.学号 AND 课号=“09001”)集合

32、查询vSELECT语句的查询结果是记录的集合,所以多个SQL语句的结果可进行集合操作.标准SQL语言提供了集合并操作UNION，也可称为输出合并。v例1、查询计算机系学生及年龄不大于20岁的学生。 SELECT * FROM 学生 WHERE 专业=“计算机” UNION SELECT * FROM 学生 WHERE 年龄=20v使用UNION时,系统默认会自动去掉重复的记录.但要求各结果表的列数必须相同,对应项的数据类型也必须相同。输出重定向（INTO）vINTO表示查询结果可以重定方向。v格式：INTO 目标|TO FILE 文件名 ADDITIVE|TO PRINTER INTO 目标：

33、 ARRAY CURSOR DBF TO 文件名：将结果输出到指定的文本文件 TO FILE：输出到文本文件查询设计器v查询设计器是对SQL语言的可视化操作，v启动查询设计器 命令方式：CREATE QUERY 查询文件名 交互方式：菜单、工具栏 MODIFY QUERY 查询文件名数据操纵功能添加记录v 语句格式语句格式 Insert Into Insert Into ( ( , , , 2, ) Values ( Values ( , , , 2, ););任务任务3-13数据操纵功能修改记录v 语句格式语句格式Update Update Set Set = = , , = = , 2, Where Where ;数据操纵功能删除记录v 语句格式语句格式 Delete From Delete From Where Where ; ;