第5章-结构化查询语言SQLppt课件(全).ppt

Access2010数据库案例教程数据库案例教程第第5章章 结构化查询语言结构化查询语言SQL本章选修本章选修学习要点（学习要点（本章内容学生可以自学本章内容学生可以自学）SQL语言的基本概念、特点语言的基本概念、特点 SQL语言的功能语言的功能 SQL语言的用法语言的用法 通通过过本本章章的的学学习习，了了解解SQL语语言言及及其其标标准准的的发发展展、SQL语语言言的的特特点点及及分分类类、视视图图相相关关语语句句，熟熟悉悉SQL语语言言中中各各种种语语句句的的语语法法，熟熟悉悉SQL数数据据定定义义语语言言（DDL）语语句句，掌掌握握SQL语语言言中中数数据据查查询询、数数据据操操纵纵语语言言的的详详细细语语法法，并并能能深深刻刻理理解解、综综合合应应用用，以以便便为为今后深层次的学习打下更加坚实的基础。今后深层次的学习打下更加坚实的基础。学习目标学习目标重点难点v重点：重点：v1、SELECT命令的使用，如：使用SELECT命令实现基本查询、条件查询、嵌套查询、多表查询和连接查询。v2、查询结果的处理，输出重定向，查询结果的排序、查询分组和统计等。v3、用查询设计器构造SELECT语句。v难点：难点：v1、SELETE命令的使用。特别是条件查询、嵌套查询、多表查询和连接查询的实现。v2、查询结果的排序、分组和统计处理。5.1 SQL语言概述vSQLStructured Query Language，结构化查询语言1986年，由美国ANSI确定为关系数据库的标准语言。1987年，ISO将其定为国际标准。1989年，ISO提出了具有完整性特征的SQL，成为SQL891992年公布了SQL的新标准，即SQL-92。SQL99SQL3SQL语言的特点vSQL是一种结构一体化的语言。它包括了数据定义、数据查询、数据操纵和数据控制等功能，可以完成数据库活动中的全部工作。SQL语言的组成v数据定义：CREATE、DROP(删除)、ALTER(修改)(删除某记录)v数据操纵：INSERT、UPDATE、DELETEv数据控制：GRANT、REVOKEv数据查询：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、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，SNAME 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)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 字段名 字段类型(宽度)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改变表的结构时，索引表达式、字段和表的有效性规则、命令、函数等等可能仍会引用原始字段名，因此有时不能执行命令。删除表DROP TABLEvDROP TABLE 表名v执行了 DROP TABLE 之后，所有与被删除表有关信息都将丢失。v例：删除SCORE表。5.3 数据操纵语言vINSERTvDELETEvUPDATE插入记录INSERT vINSERT INTO 表名 字段名1，字段名2 VALUE(表达式1,表达式2)v如果要插入表中所有字段的数据，可省略字段名。v如果省略了字段名，则必须按照表结构定义字段的顺序来指定字段值。v例1、向STUDENT表中添加记录。插入记录vINSERT INTO 表名 FROM ARRAY 数组名vINSERT INTO 表名 FROM MEMVARv把内存变量的内容插入到与它同名的字段中。如果某一字段不存在同名的内存变量，则该字段为空。v例1、从数组向表中添加记录。v例2、利用内存变量向表中添加记录。删除记录DELETEvDELETE FROM 数据库名 表名WHERE 逻辑表达式1 AND|OR 逻辑表达式2v为指定的表中的记录加删除标记。如果要删除当前数据库中的表的记录，可省略数据库名，否则必须加上包含有该表的数据库名。在数据库名的后面、表名的前面包含感叹号(!)分隔符。v例：将STUDENT表中所有男生的记录删除。UPDATEvUPDATE 数据库名！表名 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(SNAME ASC，HOSTADDR DESC)；删除索引的语句格式：DROP INDEX 索引名；5.4 数据查询SELECTvSELECT FROMWHEREv功能：从一个表或多个表中查询数据。SELECT指定查询哪些字段使用WHERE子句限定记录使用FROM子句限定表名v含义：根据WHERE子句的条件表达式，从FROM子句指定的表中找出满足条件的记录，再按SELECT子句中的字段表达式，选出记录形成结果表。v在VFP中，查询就是一个扩展名为QPR的查询文件，其中的内容就是一条SELECT语句。5.4 数据查询SELECT5.4.1 基本查询基本查询就是单表查询，：SELECT ALL|DISTINCT*|列表达式 FROM 学生nALL:查询结果中包含所有行(包括重复值),是默认设置。nDISTINCT:在查询结果中剔除重复的行。n注意 每一个 SELECT 子句只能使用一次DISTINCT。n*表示所有字段。n列表达式可以是字段名，表达式，字符串常量、函数等。基本查询v例1:列出“学生”表中所有同学名单SELECT *FROM 学生v例2:列出成绩表中所有学生的学号，去掉重复值。SELECT DISTINCT 学号 FROM 成绩SELECT命令中的函数v例3、列出所有学生的学号，姓名和入学成绩，其中入学成绩四舍五入保留整数。SELECT 学号,姓名,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 带条件查询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 AND 专业=“计算机”WHEREINvWHERE子句中的IN谓词用来确定查询的集合。v例1、查询计算机专业和外语专业的学生姓名和专业。SELECT 姓名,专业 FROM 学生 WHERE 专业 IN(“计算机”,“外语”)相当于 SELECT 姓名,专业 FROM 学生 WHERE 专业=“计算机”OR 专业=“外语”)v例2、查询既不是计算机专业也不是外语专业的学生姓名和专业。SELECT 姓名,专业 FROM 学生 WHERE 专业 NOT IN(“计算机”,“外语”)WHEREBETWEENvWHERE子句中的BETWEEN运算符用来确定范围。v格式：BETWEENAND或NOT BETWEENAND其中BETWEEN后是范围的下限(低值),AND后是范围的上限(高值)。v例1、列出入学成绩在560分到600分之间的学生名单和成绩。SELECT 姓名,入学成绩 FROM 学生 WHERE 入学成绩 BETWEEN 560 AND 600上述命令还可写成如下形式：SELECT 姓名,入学成绩 FROM 学生 WHERE 入学成绩=560 AND 入学成绩=600WHERE LIKEvWHERE中的LIKE子句用来进行字符串的匹配。v格式：NOT LIKE“字符串”v含义:查找指定的字段值中与匹配符相匹配的记录。v匹配符可以是字符串，也可以含有通配符。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中的通配符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、查询所有有期末成绩的学生学号和课号SELECT 学号,课号 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短语可以列出满足条件的前几个或前百分之几记录vTOP短语要与ORDER BY同时使用才有效v例1、显示入学成绩最高的前五名同学的记录SELECT*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短语作用于组.v例2、查询选修了3门以上课程的学生学号SELECT 学号 FROM 成绩 GROUP BY 学号 HAVING COUNT(*)3连接查询v若一个查询同时涉及两个或两个以上的表,则称之为连接查询。v连接查询是关系数据库中最主要的查询，包括等值连接、自然连接、非等值连接、自身连接、外连接和复合条件连接查询。v等值与非等值连接查询：连接查询中用来连接两个表的条件称为连接条件或连接谓词，连接谓词为=时，称为等值连接；连接谓词为其它比较运算符时，为非等值连接。v形式：表名1.列名1 连接谓词 表名2.列名2v上述的列名称为连接字段,各连接字段必须有可比性,但不一定相同。vDBMS执行连接操作的过程是：先在表1中找到第1条记录，然后从头开始扫描表2，逐一查找满足连接条件的记录，找到后就将表1中的第1条记录与该记录拼接起来，形成结果表中的一条记录。表2全部查找完后，再找表1中的第2条记录，然后再从头开始扫描表2，逐一查找满足连接条件的记录，找到后就将表1中的第2条记录与该记录拼接起来，形成结果表中的一条记录。重复上述过程，直到表1中的全部记录都处理完为止。v例1、查询每个学生及其选课情况SELECT 学生.*,成绩.*FROM 学生,成绩 WHERE 学生.学号=成绩.学号v在等值连接中把目标列中重复的字段去掉则为自然连接。v例2、对例1用自然连接完成。SELECT 学生.学号,姓名,性别,专业,出生日期,入学成绩,课号,平时,期中,期末 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”课的学生中，期末成绩大于学号为“0101019”的同学该门课期末成绩的那些同学的学号及其期末成绩。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 INNER 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 学生.学号,姓名,性别,专业,课号,期末 FROM 学生 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”)查询嵌套v嵌套查询要求子SELECT的查询结果必须是确定的内容。v嵌套查询一般是由里向外处理，即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。vSQL允许多层嵌套查询。但VFP只能支持单层嵌套。v子查询中不能使用ORDER BY子句，即ORDER BY只能对最终查询结果排序。查询嵌套v嵌套查询使我们可以用多个简单查询构成复杂的查询，从而增强SQL的查询能力。v以层层嵌套的方式来构造程序正是SQL中“结构化”的含义所在。查询嵌套v例1、查询与“夏天”同在一个专业学习的学生名单。先分步来完成此查询第一步:确定夏天所在的专业SELECT 专业 FROM 学生 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中,实现同一个查询可以有多种方法。带谓词的子查询v子查询返回单值时可以使用比较运算符。还可使用ANY、ALL或EXISTS谓词。ALL：满足子查询中的所有值的记录。ANY(SOME)：满足子查询中任何一个值的记录。EXISTS:检查子查询中是否存在记录,返回值为逻辑值。v例1、查询其它专业比计算机系所有学生年龄都小的学生姓名及年龄。SELECT 姓名,年龄 FROM 学生 WHERE 年龄ALL(SELECT 年龄 FROM 学生 WHERE 专业=“计算机”)上述查询也可写成如下形式:SELECT 姓名,年龄 FROM 学生 WHERE 年龄(SELECT MIN(年龄)FROM 学生 WHERE 专业=“计算机”)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”)集合查询vSELECT语句的查询结果是记录的集合,所以多个SQL语句的结果可进行集合操作.标准SQL语言提供了集合并操作UNION，也可称为输出合并。v例1、查询计算机系学生及年龄不大于20岁的学生。SELECT*FROM 学生 WHERE 专业=“计算机”UNION SELECT*FROM 学生 WHERE 年龄=20v使用UNION时,系统默认会自动去掉重复的记录.但要求各结果表的列数必须相同,对应项的数据类型也必须相同。输出重定向（INTO）vINTO表示查询结果可以重定方向。v格式：INTO 目标|TO FILE 文件名 ADDITIVE|TO PRINTERINTO 目标：ARRAYCURSORDBF 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 ;