四章节结构化查询语言SQL.ppt

第四章 结构化查询语言SQL,掌握数据模式的建立和删除的命令格式与作用；掌握基本表结构的建立、修改与删除的命令格式与作用；掌握基本表内容的插入、修改与删除的命令格式与作用；掌握视图的建立、修改与删除的命令格式与作用；熟练掌握查询语句的格式与各个选项的作用，能够按照查询要求写出相应的查询语句。,SQL语言的功能：SQL是结构化查询语言（Structured Query Language）的缩写，具有数据定义（DDL）、数据操纵（DML）和数据控制（DCL）、数据查询四个部分。,SQL数据定义功能：能够定义数据库的三级模式结构，即外模式、全局模式和内模式结构。在SQL中，外模式有叫做视图（View），全局模式简称模式（Schema）或数据库（Database），内模式由系统根据数据库模式自动实现，一般无需用户过问。,基本表（表）、属性（字段）、元组（行）的概念,SQL数据操纵功能：包括对基本表和视图的数据查询、插入、删除和修改，特别是具有很强的数据查询功能。,SQL的数据控制功能：主要是对用户的访问权限加以控制，以保证系统的安全性。,SQL,视图V1,视图V2,基本表B4,基本表B3,基本表B2,基本表B1,外模式,模式,存储模式,SQL支持的数据库的体系结构,一、数据库模式的建立和删除 1、建立数据库模式 语句格式：CREATESCHEMA|DATABASE AUTHORIZATION 语句功能：在计算机系统中建立一个只有名字的空数据库，并定义出它的所有者名。,语句举例：（1）create schema xuesh authorization 刘勇（2）create database 数据库,2、删除数据库模式 语句格式：DROP SCHEMA|DATABASE 数据库名 语句功能：从计算机系统中删除（撤销）一个数据库。当然会同时把该库中的所有信息一并删除掉。语句举例：drop database xuesh,二、表结构的建立、修改和删除 1、建立表结构 语句格式：CREATE TABLE.（，,)语句功能：在当前或给定的数据库中定义一个表的结构（即关系模式)。,语句说明：a.若省略和则在当前数据库中建立一个表，否则在指定数据库中建立一个表。使用户给所定义的表所起的名字。可以在一个表定义中出现一次或多次，每个包括列名（即属性名)、相应数据类型和该列的完整性约束等内容。在所有列定义之后可以给出表级完整性约束。,b.可使用的数据类型主要有以下四种：char(n)定长字符型 int 整型 float 浮点型，又称实数型 date 日期型,c.列级完整性约束有以下六种:1.DEFAULT 默认值约束。2.NULL/NOT NULL 空值/非空值约束。注明每行上的该列值为空。3.PRIMARY KEY 主码约束。注明该列为关系的主码。4.UNIQUE单值约束，又称唯一值约束。必须不相同,d.表级完整性约束包括以下四种：1.PRIMARY KEY(,）主码约束。注明一列或同时多个列为关系的主码。2.UNIQUE单值约束。一个或同时若干个列为单值。3.FOREIGN KEY()REFERENCES(,)外码约束。4.CHECK()检查约束。,5.REFERENCES()外码约束。6.CHECK()检查约束。注明该列的取值条件，或称取值限制。,注：若只涉及到一个列时，则既可以作为列级完整性约束，又可以作为表级完整性约束，当然只取其一。,语句举例：(1）create table 学生(学生号 char(7)primary key,姓名 char(6)not null unique,性别 char(2)not null chech(性别=男 or 性别=女),出生日期 datetime check(出生日期=1 and 年级=4),(2)Create table 课程(课程号 char(4)primary key,课程名 char(10)not null unique,课程学分 int check(课程学分=1 and 课程学分=6),Create table 选课(学生号 char(7),课程号 char(4),成绩 int check(成绩=0 and 成绩=100),primary key(学生号,课程号),foreign key(学生号)references 学生(学生号),foreign key(课程号)references 课程(课程号),三、表内容的插入、修改和删除 1、插入记录 向一个表中插入记录有两种语句格式，一种是单行插入，另一种是多行插入。单行插入：格式：INSERT INTO.(,)VALUES 多行插入：格式：INSERT(INTO).(,)语句功能：向一个表中所指定的若干列插入一行或多行数据。注意：当列值为字符串或日期时，必须用单引号括起来，以区别于数值数据。语句举例：p79,2、修改表结构 语句格式：ALTER TABLE.ADD,|DROP COLUMN,|DROP,语句功能：向已定义过的表中添加一些列的定义或一些表级完整性约束，或者从已定义过的表中删除一些列或一些完整性约束。举例：（1）alter table 学生 add 籍贯 char(6)（2）alter table 学生 drop column 籍贯,3、删除表结构 语句格式：DROP TABLE.语句功能：从当前或给定的数据库中删除一个表。举例：drop table 学生1,2、修改记录 语句格式：UPDATE.SET=,FROM,WHERE 语句功能：按条件修改一个表中一些列的值、语句举例：(1)update 职工 set 年龄=年龄+1(2)Update 职工 set 基本工资=职工1.基本工资+职工1.职务津贴 from 职工1 where 职工.职工号=职工1.职工号(3)Update 职工 set 基本工资=基本工资*1.2 where 职工号=010405,3、删除记录 语句格式：DELETE FROM.FROM,WHERE,语句功能：删除一个表中满足条件的所有行语句举例：(1)delete from 职工 where 年龄45(2)Delete 职工 from 职工1 where 职工.职工号=职工1.职工号(3)Delete 职工,四、视图的建立、修改和删除 视图是在基本表之上建立的表，它的结构(即所有列定义)和内容(即所有数据行)都来自基本表，它依据基本表存在而存在。每个视图的列可以来自同一个基本表，也可以来自多个不同的基本表。它是基本表的抽象和在逻辑意义上建立的新关系。对视图只能进行修改和查询操作。,1、建立视图 语句格式：CREATE VIEW(,)AS 功能：在当前库中根据SELECT子局的查询结果建立一个视图，包括视图的结构和内容。,语句举例：create view 成绩视图表(学生号,姓名,课程号,课程名,成绩)as select 选课.学生号,姓名,选课.课程号,课程名,成绩 from 学号,课程,选课 where 学生.学生号=选课.学生号 and 课程.课程号=选课.课程号 and 专业=电子,2、修改视图内容 语句格式：UPDATE.SET=,FROM,WHERE 语句功能：按照一定条件对当前或指定数据库中的一些列值进行修改。语句举例：update 成绩视图表 set 成绩=80 where 学生号=0102005 and 课程号=E002,3、修改视图定义：语句格式：ALTER VIEW(,)AS 语句功能：在当前数据库中修改已知视图的列,它与SELECT子句的查询结果相对应。语句举例：Create view 学生视图(学生号,姓名)as select 学生号，姓名 from 学生Alter view 学生视图(学生号,专业)as select 学生号,专业 from 学生,4、删除视图 语句格式：DROP VIEW 语句功能：删除当前数据库中一个视图。语句举例：drop view 成绩视图表,五、SQL查询1、SELECT语句 格式：SELECT ALL|DISTINCT AS,|*|.*|INTO FROM AS,AS WHERE GROUP BY,HAVING ORDER BY ASC|DESC,ASC|DESC 功能：根据一个或多个表按条件进行查询，产生出一个新表（即查询结果），该新表被显示出来或者被命名保存起来。,语句说明：a.Select选项给处在查询结果中每一行(即每一条记录)所包含的列，以及决定是否允许在查询结果中出现重复行（即内容完全相同的记录）；b.into 选项决定是否把查询结果以基本表的形式保存起来，若需要则应带有该选项;c.from选项提供用于查询的基本表和视图，它们均可以带有表别名，称这些表为源表，而把查询结果称为目的表;d.where 选项用来指定不同源表之间记录的连接条件和每个源表中记录的筛选（选择）条件，只有满足所给连接条件和筛选条件的记录才能被写入到目的表中；e.group by 选项用于使查询结果只包含按指定列的值进行分组的统计信息；f.having 子句通常同group by 选项一起使用，筛选出符合条件的分组统计信息；g.order by 选项用于将查询结果按指定列值的升序或降序排序。在查询语句中，通过select 选项实现投影运算，通过from选项和where选项是实现连接和选择运算（在SQL新版本中，用from 选项专门实现连接运算，用where 选项专门实现选择运算）,2、SELECT 选项 在该选项中，ALL/DISTING为任选项，若选择ALL,则允许在查询结果中出现内容重复的行（记录），若选择DISTINCT,则在查询结果中不允许出现内容重复的行，即只有内容互不相同的记录才能被写入到查询结果中，若省略该选项，则隐含为ALL.,应用举例：P89 例4-14-10,3、FROM选项 例4-11,4、WHERE选项 例4-124-16,SPK1（商品代号 C（8），分类名 C（6），单价 N（8，2），数量 N（3）,一、简单查询,例1 从SPK1中查询出每个记录的分类名字段的值。,SELECT ALL 分类名 FROM SPK1,例2 从SPK1中查询出所有商品的不同分类名。,SELECT DISTINCT 分类名 FROM SPK1,练习：1、从SPK1中查询出单价低于2000元的商品代号、分类名和单价。2、从SPK1中查询出单价在1000元至2500元之间的所有商品。,例3 从SPK1中查询出分类名为“电视机”的所有商品,SELECT*；FROM SPK1；WHERE 分类名=“电视机”,用AS指出字段别名：,例4 从SPK1中查询出每一种商品的最高价值、最低价值。,SELECT MAX（单价*数量）AS 最高价值，MIN（单价*数量）AS 最低价值；FROM SPK1,在SQL-SELECT命令中，使用的字段函数有：COUNT（字段名|*）统计出对应字段的个数，它也就是相应的记录数，通常使用*表示任一字段。MAX（字段名）求出最大值。MIN（字段名）求出最小值。AVG（字段名）求出对应的数值字段的平均值。SMU（字段名）求出对应的数值字段的总和。,SPK2（商品代号C（8），产地C（8），品牌C（8）,练习 3、从SPK1中查询出每一种商品的价值。4、查询出SPK1库中分类名为“电视机”的商品种数、最高价、最低价及平均价。,练习题答案：1、SELECT 商品代号，分类名，单价；FROM SPK1；WHERE 单价1000 AND 单价2500 3、SELECT 商品代号，单价*数量 AS 价值；FROM SPK1 4、SELECT 分类名，COUNT（*）AS 种数；MAX（单价）AS 最高价，MIN（单价）AS 最低价；AVG（单价）AS 平均价；FROM SPK1；WHERE 分类名=“电视机”,SELECT 商品代号，品牌；FROM SPK2；WHERE 产地 IN（南京，北京）,例 查找SPK1中所有商品中数量大于10的分类号及单价，并把单价按降续排序。,用ORDER BY 对查询结果排序,在WHERE中使用谓词 IN,例 查找SPK2中产地是南京或北京的商品的商品代号、品牌。,SELECT 分类号，单价；FROM SPK1；WHERE 数量=10；ORDER BY 单价 DESC,在WHERE中BETWEENAND和NOT BETWEEND 使用,例 从SPK1中查询出单价在1000元至2500元之间的所有商品。,SELECT 商品代号，分类名，单价；FROM SPK1；WHERE 单价 BETWEEN 1000 2500,例4-11 从教学库中查询出每个学生选修每门课程的学生号、姓名、课程号、课程名、成绩等数据,Select x.学生号,姓名,y.课程号,课程名,成绩From 学生 x,课程 y,选课 zWhere x.学生号=z.学生号 and y.课程号=z.课程号,例4-12 从商品表1种查询出单价大于1500，同时数量大于等于10的商品。,Select*From 商品表1Where 单价=1500 and 数量=10,例13 从商品库中查询出产地为南京或无锡的所有商品的商品代号、分类名、产地和品牌。,Select x.商品代号,分类名,产地,品牌From 商品表1 x,商品表2 yWhere x.商品代号=y.商品代号 and(产地=南京 or 产地=无锡),例 14 从教学库中查询出选修至少两门课程的学生学号。,Select distinct x.学号From 选课 x,选课 yWhere x.学生号=y.学生号 and x.课程号y.课程号,例 15 从教学库中查询出选修了课程名为“操作系统”课程每个学生的姓名。,Select 姓名,课程From 学生 x,课程 y,选课 zWhere x.学生号=z.学生号 and y.课程号=z.课程号 and 课程名=操作系统,注意：在新版的SQL中，为了使查询语句更加结构化，已经把查询连接条件从WHERE选项中转移到FROM选项中，并且还丰富了连接的功能，除了上述介绍的一般连接（在新版本中称作中间连接）外，还增加了左连接和右连接的功能。在FROM选相中的相应语法格式分别为：,中间连接 FROM INNER JOIN ON.左连接 FROM LEFT JOIN ON.右连接 FROM RIGHT JOIN ON.,每一种连接都隐含着双重循环的执行过程：对于中间连接，外循环依次扫描第一个表中的每个元组，内循环依次扫描第二个表中的每个元组，当满足连接条件时就连接起来形成中间表中的一个新元组；对于左连接，除了按中间连接形成中间表中的新元组外，还把第一个表中的没有形成连接的所有元组也加入到中间表中，这些元组在第二个表上所对应的列值被自动置为空；对于右连接，除了按中间连接形成中间表中的新元组外，还把第二个表中的没有形成连接的所有元组也加入到中间表中，这些元组在第一个表上所对应的列值被自动置为空。完成连接后，查询语句再根据WHERE选项中提供的筛选条件从中间表中选择出元组，然后再根据SELECT选项投影出所需要的列形成结果表。,例 16.a 从教学库中查询出所有学生的选课情况，要求没选修任何课程的学生信息也要反映出来,Select*From 学生 left join 选课 on 学生.学生号=选课.学生号,例 16.b 从学生库中查询出所有课程被学生选修的情况,Select*From 课程 left join(选课 inner join 学生 on选修.学生号=选课.学生号)on 课程.课程号=选课.课程号,例 16.c 从教学库中查询出所有电子专业的学生选课的全部情况,Select*From 学生 inner join(选课 inner join 课程 on 选课.课程号=课程.课程号)on 学生.学生号=选课.学生号Where 专业=电子(与传统查询语句等效),用于查询语句中的专门比较式又叫判断式，它实现单值与集合数据之间的比较。常用的有以下六种格式。,格式1：ALL()功能：是一条完整的SELECT语句，被嵌套在该比较式中使用。当的查询结果中的每一个值都满足所给的比较条件时，此比较式的值才为真，否则为假。,例 17 从商品表1中查询出单价比分类名为“洗衣机”的所有商品的单价都高的商品。,Select*From 商品表1Where 单价all(select 单价 from 商品表1 where 分类名=“洗衣机”,例 18 查询出数量小于分类名为“洗衣机”或“微波炉”的每一个商品数量的所有元组(包括 产地和品牌),Select x.*,产地,品牌From 商品表1 x inner join 商品表2 y on x.商品代号=y.商品代号 Where 数量all(select 数量 from 商品表1 where 分类名=洗衣机 or 分类名=微波炉),格式2 ANY|SOME(),当子查询的查询结果中的任一个值满足所给的比较条件时，此比较式为真，否则为假。该格式中的两个关键字ANY和SOME具有同样的作用，选用任一个即可。,例 19 从商品库中查询出产地与品牌为“春兰”的商品的产地相同的所有商品的商品代号、分类名、品牌、产地等属性的值,Select x.商品代号,分类名,品牌,产地From 商品表1 x inner join 商品表2 x on x.商品代号=y.商品代号Where 产地=some(select 产地 from 商品表2 where 品牌=春兰),例 20 从教学库中查询出选修了课程名为“C+语言”的所有学生的姓名和成绩。,第一种方法：使用单重查询语句处理 select 姓名,成绩 from 学生 x,课程 y,选课 z where x.学生号=z.学生号 and y.课程号=z.课程号,第二种方法：使用双重查询语句处理 select 姓名,成绩 from 学生 inner join 选课 on 学生.学生号=选课.学生号 where 课程号=some(select 课程号from 课程=C+语言),例 21 从商品库中查询出所有商品中单价最高的商品和单价最低的商品,Select*From 商品表1Where 单价=any(select max(单价)from 商品表1)or 单价=any(select min(单价)from 商品表1),或：select*from 商品表1 where 单价=any(select max(单价)from 商品表1 union select min(单价)from 商品表1),格式3：NOT BETWEEN AND,例 22 从商品表1中查询出单价在1000到2000元之间的所有商品,Select*From 商品表1Where 单价 between 1000 and 2000,格式4：EXISTS(),当子查询结果中至少存在着一个元组时，表明查询结果非空，则此判断式为真，否则为假。但当判断式中带有NOT关键字时，情况正好相反，即当子查询结果为空时，判断式为真，否则为假。,例 23 从教学库中查询出选修至少一门课程的所有学生,Select*From 学生Where exists(select*from 选课 where 学生.学生号=选课.学生号),例 24 从教学库中查询出与姓名为“王明”的学生选课至少有一门相同的所有学生,Select*From 学生 xWhere x.姓名王明 and exists(select y.课程号 from 选课 y where y.学生号=x.学生号 and y.课程号=any(select w.课程号 from 学生 z，选课 w where z.学生号=w.学生号 and z.姓名=王明),格式5：NOT IN()|(),使用逗号分开的若干个常量。当所制定列的当前值包括在由所给定的值之内时，则此判断式为真，否则为假。,例 26 从学生表中查询出专业为计算机、电气、通信的所有学生。,Select*From 学生Where 专业 in(计算机,电气,通信),例 27 从教学库中查询出选修了课程名为“操作系统”的所有学生。,Select*From 学生Where 学生号 in(select 学生号 from 选课,课程 where 选课.课程号=课程.课程号 and 课程名=操作系统),格式6：NOT,当的当前值与的值相匹配时，此判断时为真。,例 28 从商品表1中查询出商品代号以字符串“dsj”开头的所有商品,Select*From 商品表1Where 商品代号 like dsj%,5、GROUP BY选项 该选项中的,等必须是出现在SELECT选项中的被投影的表达式所指定的列名。通常在SELECT选项中使用列函数对列值相同的每一组进行有关统计,例 29 从学生表中查询出每个专业的学生数,Select 专业 专业名,count(专业)学生数From 学生Group by 专业,例 30 从教学库中查询出每个学生的学生号，姓名及所选课程的门数,Select x.学生号,姓名,count(x.学生号)所选门数From 选课 x,学生 yWhere x.学生号=y.学生号Group by x.学生号,y.姓名,例 31 从商品表1种查询出每一类（即分类名相同）商品的最高价、最低价和平均价。,Select 分类名,max(单价)as 最高价,min(单价)as 最低价,avg(单价)as 平均价From 商品表1Group by 分类名,6、HAVING 选项 该选项的是一个筛选条件。该选项通常跟在GROUP BY子句后面用来从分组统计中筛选出部分统计结果，因此该选项中的逻辑表达式通常带有字段函数,例 32 从学生表中查询出专业的学生数多于1人的专业名及人数,Select 专业 as 专业名,count(专业)as 学生数From 学生Group by 专业Having count(专业)1,例 33 从教学库中查询出选修课程超过1门的学生。,Select*From 学生Where 学生号 in(select 学生号 from 选课 group by 学生号 having count(学生号)1),例 34 从教学库中查询出选课门数超过学生号为0101001学生的选课门数的所有学生。,Select*From 学生Where 学生号 in(select 学生号 from 选课 group by 学生号 having count(学生号)(select count(*)from 选课 where 学生号=0101001),7、ORDER BY 选项,此选项中的,等是需要是查询结果按其进行排序的列。他们可以是原表中的列名，也可以是SELECT选项中所给表达式的顺序号（即对应查询结果中的列号）或定义的列名。若其后带有ASC关键字，则将按值的升序排序查询结果，若其后带有DESC关键字，则将按值的降序排序查询结果，若不指定排序方式，则默认按升序排序。注意：该选项只能用在最外层的查询语句中，不能在子查询中使用。,例 35 从商品表中按单价升序查询出所有商品记录。,Select*From 商品表1Order by 单价,例 36 从商品表1中查询出单价比平均单价高的所有商品，并使结果按降序排列。,Select*From 商品表1Where 单价all(select avg(单价)from 商品表1)Order by 单价 desc,例 37 从教学库中查询出所有学生的学号及所选课程的门数，按门数升序排列结果,Select x.学生号,count(x.学生号)as 选课门数From 学生 x,选课 yWhere x.学生号=y.学生号Group by x.学生号Order by 选课门数,例 38 从教学库中查询出所有学生的信息及所选课程的门数，按门数升序排列结果。,Select x.*,count(x.学生号)as 选课门数From 学生 x,选课 yWhere x.学生号=选课.学生号Group by x.学生号,姓名,性别,专业Order by 选课门数,8、INTO 选项 此选项能够根据查询结果自动建立一个基本表，通常作为临时表使用，可以根据需要任意建立和删除。,例 39 从教学库中查询出每个学生的学生号、选课门数和总成绩，把查询结果保存到学生选课门数及总成绩统计表中。,Select x.学生号,count(x.学生号)as 选课门数,sum(成绩)as 总成绩 into 学生选课门数及总成绩统计表From 学生x,选课 yWhere x.学生号=y.学生号Group by x.学生号,本章小结,SQL是国际通用的关系数据库查询语言，几乎被每一种实际的关系数据库管理系统所采用，各系统都根据本身特点对其进行了修改和扩充，在不同程度上增强了数据处理和有关方面的功能。一个数据库由基本表和视图等组成，每个基本表的结构和内容是分别建立的，每个视图是根据相应的基本表（也可仍为视图_)而建立的，它是相应表中的一些结构和一些行内容的映象。建立一个基本表结构，就是定义表中包含的每个列，集中包括列明、类型、长度、完整性约束等。当表中的一个完整性约束涉及到多个列时，则必须在所有列定义之后给出相应的表及完整性约束。SELECT查询语句具有丰富的数据查询功能，能够实现关系运算中的大多数运算，如选择、投影、连接、并等，并且还带有分组、排序、统计等数据处理功能。随着SQL版本的不断更新，必将会增加越来越多的数据处理功能。,SELECT 语句的查询结果有多种可能，有的为空，有的为一个单值元组，有的为一个多值（即含有多个列）元组，有的为单值的多个元组，有的为多值的多个元组等。若为单值元组时，此查询可以作为一个数据项出现在任何表达式中。SELECT语句可以作为一个语句成分（即子查询）出现在各种语句中。若在SELECT语句的WHERE选项中仍使用一个SELECT语句，则成为SELECT语句的嵌套，SELECT语句可以嵌套任意深度，但一般嵌套为二至三层。学习SQL还要靠上机实践，为此需要针对具体的关系数据库管理系统。在本书的第8、9章介绍了Microsoft SQL Server 2000数据库管理系统，届时同学们一定要结合试验教材进行上机训练，巩固和提高在本章所学的理论知识。,写出下面的SQL语句的查询结果：1.select count(*);from 图书；where 单价 between 10 and 25 2.select MAX(单价）as 最高价 MIN(单价)as 最低价,AVG(单价)as 平均价；from 图书 3.select 借书证号，count(*);from 借阅；where 借书日期=3,