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

1、Access2010数据库案例教程第5章 结构化查询语言SQL本章选修学习要点（本章内容学生可以自学） SQL语言的基本概念、特点 SQL语言的功能 SQL语言的用法 通过本章的学习，了解SQL语言及其标准的发展、SQL语言的特点及分类、视图相关语句，熟悉SQL语言中各种语句的语法，熟悉SQL数据定义语言（DDL）语句，掌握SQL语言中数据查询、数据操纵语言的详细语法，并能深刻理解、综合应用，以便为今后深层次的学习打下更加坚实的基础。学习目标重点难点重点：1、SELECT命令的使用，如：使用SELECT命令实现基本查询、条件查询、嵌套查询、多表查询和连接查询。2、查询结果的处理，输出重定向，查

2、询结果的排序、查询分组和统计等。3、用查询设计器构造SELECT语句。难点：1、SELETE命令的使用。特别是条件查询、嵌套查询、多表查询和连接查询的实现。2、查询结果的排序、分组和统计处理。 5.1 SQL语言概述SQLStructured Query Language，结构化查询语言1986年，由美国ANSI确定为关系数据库的标准语言。1987年，ISO将其定为国际标准。1989年，ISO提出了具有完整性特征的SQL，成为SQL891992年公布了SQL的新标准，即SQL-92。SQL99SQL3SQL语言的特点SQL是一种结构一体化的语言。它包括了数据定义、数据查询、数据操纵和数据控制等

3、功能，可以完成数据库活动中的全部工作。SQL语言的组成数据定义：CREATE、DROP(删除)、 ALTER(修改) (删除某记录)数据操纵：INSERT、UPDATE、DELETE数据控制：GRANT、REVOKE数据查询：SELECT(选择工作区)SQL语言的组成(1)数据定义语言(Data Definition Language，简称DDL) 用于定义SQL模式、基本表、视图和索引。(2)查询语言(Query Language，简称QL) 用于数据查询。(3)数据操纵语言(Data Manipulation Language，简称DML) 用于数据的增、删、修改。(4)数据控制语言(Da

4、ta Control Language，简称DCL) 用于数据访问权限的控制。5.2 数据定义语言CREATE ALTERDROP创建表在CREATE TABLE中可以使用的数据类型及说明：C、N 、 D 、 T 、L、M、G创建表CREATE TABLE定义基本表的语句格式为 :CREATE TABLE( NOT NULL UNIQUE ,属性名2类型2 NOT NULL UNIQUE ) 其他参数；例5-1：学生成绩数据库含有三张表：学生关系：S(SNO,SNAME,SEX, AGE, DNAME)课程关系：C(CNO, CNAME, CREDIT, PRE_CNO)选课关系：SC(SNO

5、, CNO, SCORE) 可用下列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、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);创建表例1、使用命令建立数据库XSK，用SQL命令在该库中建立STUDENT表，结构及要如下。字段名字段类型字段长度特殊要求学号C7主索引姓名C8不能为空性别C2年龄N3入学年月D是否党员L备注M创建表例2、在XSK中建立score表，结构如下。字段名字

7、段类型字段长度小数位数学号C7课号C5期末N52修改表结构ALTER TABLE添加字段：ALTER TABLE 表名 ADD COLUMN 字段名 字段类型 (宽度)例：为SCORE表添加添加两个字段，“平时(N(5,1)”和“期中(N(5,1)”。修改表结构修改字段ALTER TABLE 表名 ALTER COLUMN 字段名 字段类型(宽度)例：在score中，修改“期末”字段为N(5，1)。修改表结构删除字段ALTER TABLE 表名 DROP COLUMN 字段名例：在student表中，删除“是否党员”字段。修改表结构字段更名ALTER TABLE 表名 RENAME COLUM

8、N 原字段名 TO 新字段名例：在STUDENT表中，把“备注”字段名改为“其它情况”。修改表结构改变表的结构时，索引表达式、字段和表的有效性规则、命令、函数等等可能仍会引用原始字段名，因此有时不能执行命令。删除表DROP TABLEDROP TABLE 表名执行了 DROP TABLE 之后，所有与被删除表有关信息都将丢失。例：删除SCORE表。5.3 数据操纵语言INSERTDELETEUPDATE插入记录INSERT INSERT INTO 表名 字段名1，字段名2 VALUE(表达式1,表达式2)如果要插入表中所有字段的数据，可省略字段名。如果省略了字段名，则必须按照表结构定义字段的顺

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

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

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

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

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

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

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

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

17、ELECT * FROM 学生 WHERE 学号 LIKE “0108015”LIKE中的通配符% 0任意多个字符_ 任意单个字符a%b可代表ab，acb，addgbA_b可代表acb,afb,akb等。LIKE中的通配符例1、列出学生表中所有姓李的同学。SELECT 学号,姓名 FROM 学生 WHERE 姓名 LIKE “李%”例2、列出学生表中所有不姓李且姓名是两个字的同学名单SELECT 学号,姓名 FROM 学生 WHERE 姓名 NOT LIKE “李%” AND 姓名 LIKE “_ _”LIKE中的通配符如果要查询的字符串本身就含有%或_，就要使用ESCAPE “换码字符”对通

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

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

20、OP 50 PERCENT FROM 学生 ORDER BY 入学成绩 DESC对查询结果分组使用GROUP BY子句可以对查询结果进行分组,分组的目的是为了细化统计函数的作用对象语法:GROUP BY 分组选项 HAVING 条件例1、统计各个课号及相应的选课人数SELECT 课号,COUNT(课号) 选课人数 FROM 成绩 GROUP BY 课号HAVING条件与WHERE子句的区别:作用对象不同,WHERE子句作用于表或视图;HAVING短语作用于组.例2、查询选修了3门以上课程的学生学号SELECT 学号 FROM 成绩 GROUP BY 学号 HAVING COUNT(*)3连接查

21、询若一个查询同时涉及两个或两个以上的表,则称之为连接查询。连接查询是关系数据库中最主要的查询，包括等值连接、自然连接、非等值连接、自身连接、外连接和复合条件连接查询。等值与非等值连接查询：连接查询中用来连接两个表的条件称为连接条件或连接谓词，连接谓词为=时，称为等值连接；连接谓词为其它比较运算符时，为非等值连接。形式：表名1.列名1 连接谓词 表名2.列名2上述的列名称为连接字段,各连接字段必须有可比性,但不一定相同。DBMS执行连接操作的过程是：先在表1中找到第1条记录，然后从头开始扫描表2，逐一查找满足连接条件的记录，找到后就将表1中的第1条记录与该记录拼接起来，形成结果表中的一条记录。表

22、2全部查找完后，再找表1中的第2条记录，然后再从头开始扫描表2，逐一查找满足连接条件的记录，找到后就将表1中的第2条记录与该记录拼接起来，形成结果表中的一条记录。重复上述过程，直到表1中的全部记录都处理完为止。例1、查询每个学生及其选课情况SELECT 学生.*,成绩.* FROM 学生,成绩 WHERE 学生.学号=成绩.学号在等值连接中把目标列中重复的字段去掉则为自然连接。例2、对例1用自然连接完成。SELECT 学生.学号,姓名,性别,专业,出生日期,入学成绩,课号,平时,期中,期末 FROM 学生,成绩 WHERE 学生.学号=成绩.学号例3、查询所有学生的成绩单，要求输出学号、姓名、

23、课号、课名和总成绩。SELECT a.学号,姓名,b.课号,课名,(平时+期中+期末)/3 AS 总成绩 FROM 学生 a,成绩 b,课程 c WHERE a.学号=b.学号 AND b.课号=c.课号 例3中给每个表定义了别名,以方便在查询的其它部分使用。自身连接一个表与其自己进行连接,称为表的自身连接。自身连接必须指定别名。例4、列出选修“09001”课的学生中，期末成绩大于学号为“0101019”的同学该门课期末成绩的那些同学的学号及其期末成绩。SELECT a.学号,a.期末 FROM 成绩 a,成绩 b WHERE a.课号=“09001” AND b.学号=“0101019” A

24、ND a.期末b.期末 AND a.课号=b.课号内部连接(Inner Join)在通常的连接中,将所有满足条件的记录都作为结果输出,称为内连接。内连接的方式：Inner Join ON例:查询所有学生的成绩单，要求输出学号、姓名、课号、课名和总成绩。SELECT a.学号,姓名,b.课号,课名,(平时+期中+期末)/3 AS 总成绩 FROM 学生 a INNER JOIN 成绩 b INNER JOIN 课程 c ON b.课号=c.课号 ON a.学号=b.学号内连接中JOIN的顺序和ON的顺序至关重要。JOIN连接的顺序和ON条件的顺序给出正好相反。Inner Join等价于Join。

25、内连接可使用WHERE 子句代替。外连接（Outer Join）在通常的连接中，都是把满足连接条件的记录添加到结果表中，在VFP中，还可以使不满足条件的记录也出现在结果表中，称为外连接。外连接包括：左连接LEFT JOIN右连接RIGHT JOIN全连接FULL JOIN例1、列出每个学生的基本情况及其选课情况。SELECT 学生.学号,姓名,性别,专业,课号,期末 FROM 学生 LEFT JOIN 成绩 ON 学生.学号=成绩.学号在上例中使用右连接和全连接,加以比较。查询嵌套在SQL中，一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子

26、句或HAVING短语的条件中的查询称为嵌套查询。例1、查询选修 “09001”课的学生名单。SELECT 姓名 FROM 学生,成绩 WHERE 学生.学号=成绩.学号 AND 课号=“09001”上述查询还可写成如下形式:SELECT 姓名 FROM 学生 WHERE 学号 IN (SELECT 学号 FROM 成绩 WHERE 课号=“09001”)查询嵌套嵌套查询要求子SELECT的查询结果必须是确定的内容。嵌套查询一般是由里向外处理，即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。SQL允许多层嵌套查询。但VFP只能支持单层嵌套。子查询中不能使用ORDER

27、 BY子句，即ORDER BY只能对最终查询结果排序。查询嵌套嵌套查询使我们可以用多个简单查询构成复杂的查询，从而增强SQL的查询能力。以层层嵌套的方式来构造程序正是SQL中“结构化”的含义所在。查询嵌套例1、查询与“夏天”同在一个专业学习的学生名单。先分步来完成此查询第一步:确定夏天所在的专业SELECT 专业 FROM 学生 WHERE 姓名=“夏天”结果为“计算机”第二步:查找所有在“计算机”专业的学生名单SELECT 学号,姓名,专业 FROM 学生 WHERE 专业=“计算机”将第一步查询嵌入到第二步查询的条件中,构造嵌套查询的SQL语句如下:SELECT 学号,姓名,专业 FROM

28、 学生 WHERE 专业 IN (SELECT 专业 FROM 学生 WHERE 姓名=“夏天”)上例查询也可以用自身连接来完成SELECT a.学号,a.姓名,a.专业 FROM 学生 a,学生 b WHERE a.专业=b.专业 AND b.姓名=“夏天”可见在SQL中,实现同一个查询可以有多种方法。带谓词的子查询子查询返回单值时可以使用比较运算符。还可使用ANY、ALL或EXISTS谓词。ALL：满足子查询中的所有值的记录。ANY(SOME)：满足子查询中任何一个值的记录。EXISTS:检查子查询中是否存在记录,返回值为逻辑值。例1、查询其它专业比计算机系所有学生年龄都小的学生姓名及年龄。SELECT 姓名,年龄 FROM 学生 WHERE 年龄ALL (SELECT 年龄 FROM 学生 WHERE