结构化查询语言SQL解析课件

《结构化查询语言SQL解析课件》由会员分享，可在线阅读，更多相关《结构化查询语言SQL解析课件（47页珍藏版）》请在装配图网上搜索。

1、SQL数据定义功能：能够定义数据库的三级模式结构，即外模式、全局模式和内模式结构。在SQL中，外模式有叫做视图（View），全局模式简称模式（Schema）或数据库（Database），内模式由系统根据数据库模式自动实现，一般无需用户过问。基本表（表）、属性（字段）、元组（行）的概念SQL数据操纵功能：包括对基本表和视图的数据查询、插入、删除和修改，特别是具有很强的数据查询功能。SQL的数据控制功能：主要是对用户的访问权限加以控制，以保证系统的安全性。SQL视图V1视图V2基本表B4基本表B3基本表B2基本表B1分区1文件1文件2分区2文件3文件4外模式模式存储模式SQL支持的数据库的体系结构

2、支持的数据库的体系结构一、数据库模式的建立和删除 1、建立数据库模式 语句格式：CREATESCHEMA|DATABASE AUTHORIZATION 语句功能：在计算机系统中建立一个只有名字的空数据库，并定义出它的所有者名。语句举例：（1）create schema xuesh authorization 刘勇（2）create database 数据库2、删除数据库模式 语句格式：DROP SCHEMA|DATABASE 数据库名 语句功能：从计算机系统中删除（撤销）一个数据库。当然会同时把该库中的所有信息一并删除掉。语句举例：drop database xuesh二、表结构的建立、修改和

3、删除 1、建立表结构 语句格式：CREATE TABLE .（，,)语句功能：在当前或给定的数据库中定义一个表的结构（即关系模式)。语句说明：a.若省略和则在当前数据库中建立一个表，否则在指定数据库中建立一个表。使用户给所定义的表所起的名字。可以在一个表定义中出现一次或多次，每个包括列名（即属性名)、相应数据类型和该列的完整性约束等内容。在所有列定义之后可以给出表级完整性约束。b.可使用的数据类型主要有以下四种：char(n)定长字符型 int 整型 float 浮点型，又称实数型 date 日期型 c.列级完整性约束有以下六种:1.DEFAULT 默认值约束。2.NULL/NOT NULL

4、空值/非空值约束。注明每行上的该列值为空。3.PRIMARY KEY 主码约束。注明该列为关系的主码。4.UNIQUE单值约束，又称唯一值约束。必须不相同d.表级完整性约束包括以下四种：1.PRIMARY KEY (,）主码约束。注明一列或同时多个列为关系的主码。2.UNIQUE单值约束。一个或同时若干个列为单值。3.FOREIGN KEY()REFERENCES (,)外码约束。4.CHECK()检查约束。5.REFERENCES ()外码约束。6.CHECK ()检查约束。注明该列的取值条件，或称取值限制。注：若只涉及到一个列时，则既可以作为列级完整性约束，又可以作为表级完整性约束，当然只

5、取其一。语句举例：(1）create table 学生(学生号 char(7)primary key,姓名 char(6)not null unique,性别 char(2)not null chech(性别=男 or 性别=女),出生日期 datetime check(出生日期=1 and 年级=1 and 课程学分=0 and 成绩=100),primary key(学生号,课程号),foreign key(学生号)references 学生 (学生号),foreign key(课程号)references 课程 (课程号)三、表内容的插入、修改和删除 1、插入记录 向一个表中插入记录有两

6、种语句格式，一种是单行插入，另一种是多行插入。单行插入：格式：INSERT INTO.(,)VALUES 多行插入：格式：INSERT(INTO).(,)语句功能：向一个表中所指定的若干列插入一行或多行数据。注意：当列值为字符串或日期时，必须用单引号括起来，以区别于数值数据。语句举例：p792、修改表结构 语句格式：ALTER TABLE.ADD 列定义|ADD,|DROP COLUMN,|DROP,语句功能：向已定义过的表中添加一些列的定义或一些表级完整性约束，或者从已定义过的表中删除一些列或一些完整性约束。举例：（1）alter table 学生 add 籍贯 char(6)（2）alte

7、r table 学生 drop column 籍贯 3、删除表结构 语句格式：DROP TABLE.语句功能：从当前或给定的数据库中删除一个表。举例：drop table 学生12、修改记录 语句格式：UPDATE.SET=,FROM,WHERE 语句功能：按条件修改一个表中一些列的值、语句举例：(1)update 职工 set 年龄=年龄+1(2)Update 职工 set 基本工资=职工1.基本工资+职工1.职务津贴 from 职工1 where 职工.职工号=职工1.职工号(3)Update 职工 set 基本工资=基本工资*1.2 where 职工号=0104053、删除记录 语句格式

8、：DELETE FROM.FROM,WHERE 语句功能：删除一个表中满足条件的所有行语句举例：(1)delete from 职工 where 年龄45(2)Delete 职工 from 职工1 where 职工.职工号=职工1.职工号(3)Delete 职工四、视图的建立、修改和删除 视图是在基本表之上建立的表，它的结构(即所有列定义)和内容(即所有数据行)都来自基本表，它依据基本表存在而存在。每个视图的列可以来自同一个基本表，也可以来自多个不同的基本表。它是基本表的抽象和在逻辑意义上建立的新关系。对视图只能进行修改和查询操作。1、建立视图 语句格式：CREATE VIEW (,)AS 功能

9、：在当前库中根据SELECT子局的查询结果建立一个视图，包括视图的结构和内容。语句举例：create view 成绩视图表(学生号,姓名,课程号,课程名,成绩)as select 选课.学生号,姓名,选课.课程号,课程名,成绩 from 学号,课程,选课 where 学生.学生号=选课.学生号 and 课程.课程号=选课.课程号 and 专业=电子2、修改视图内容 语句格式：UPDATE.SET=,FROM,WHERE 语句功能：按照一定条件对当前或指定数据库中的一些列值进行修改。语句举例：update 成绩视图表 set 成绩=80 where 学生号=0102005 and 课程号=E00

10、23、修改视图定义：语句格式：ALTER VIEW (,)AS 语句功能：在当前数据库中修改已知视图的列,它与SELECT子句的查询结果相对应。语句举例：(1)Create view 学生视图(学生号,姓名)as select 学生号，姓名 from 学生(2)Alter view 学生视图(学生号,专业)as select 学生号,专业 from 学生4、删除视图 语句格式：DROP VIEW 语句功能：删除当前数据库中一个视图。语句举例：drop view 成绩视图表 五、SQL查询1、SELECT语句 格式：SELECT ALL|DISTINCT AS ,表达式2AS|*|.*|INTO

11、 FROM AS,AS WHERE GROUP BY,HAVING ORDER BY ASC|DESC,ASC|DESC 功能：根据一个或多个表按条件进行查询，产生出一个新表（即查询结果），该新表被显示出来或者被命名保存起来。语句说明：a.Select选项给处在查询结果中每一行(即每一条记录)所包含的列，以及决定是否允许在查询结果中出现重复行（即内容完全相同的记录）；b.into 选项决定是否把查询结果以基本表的形式保存起来，若需要则应带有该选项;c.from选项提供用于查询的基本表和视图，它们均可以带有表别名，称这些表为源表，而把查询结果称为目的表;d.where 选项用来指定不同源表之间记

12、录的连接条件和每个源表中记录的筛选（选择）条件，只有满足所给连接条件和筛选条件的记录才能被写入到目的表中；e.group by 选项用于使查询结果只包含按指定列的值进行分组的统计信息；f.having 子句通常同group by 选项一起使用，筛选出符合条件的分组统计信息；g.order by 选项用于将查询结果按指定列值的升序或降序排序。在查询语句中，通过select 选项实现投影运算，通过from选项和where选项是实现连接和选择运算（在SQL新版本中，用from 选项专门实现连接运算，用where 选项专门实现选择运算）2、SELECT 选项 在该选项中，ALL/DISTING为任选项

13、，若选择ALL,则允许在查询结果中出现内容重复的行（记录），若选择DISTINCT,则在查询结果中不允许出现内容重复的行，即只有内容互不相同的记录才能被写入到查询结果中，若省略该选项，则隐含为ALL.应用举例：P89 例4-14-103、FROM选项 例4-114、WHERE选项 例4-124-16Record#商品代号分类名单价数量1DSJ-120电视机1865.00152DSJ180电视机2073.00103XYJ-13洗衣机486.00204XYJ-20洗衣机873.00125DBX-134电冰箱1456.0086DSJ340电视机3726.0057WBL-6微波炉640.00108KT

14、Q-12空调器2800.0012SPK1（商品代号 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 从

15、SPK1中查询出每一种商品的最高价值、最低价值。SELECT MAX（单价*数量）AS 最高价值，MIN（单价*数量）AS 最低价值；FROM SPK1在SQL-SELECT命令中，使用的字段函数有：COUNT（字段名|*）统计出对应字段的个数，它也就是相应的记录数，通常使用*表示任一字段。MAX（字段名）求出最大值。MIN（字段名）求出最小值。AVG（字段名）求出对应的数值字段的平均值。SMU（字段名）求出对应的数值字段的总和。Record#商品代号产地品牌12345678DSJ-120DSJ-180XYJ-13XYJ-20DBX-134DSJ-340WBL-6KTQ-12南京南京无锡山西北

16、京北京青岛无锡熊猫熊猫小天鹅海棠雪花牡丹海信春兰SPK2（商品代号C（8），产地C（8），品牌C（8）练习 3、从SPK1中查询出每一种商品的价值。4、查询出SPK1库中分类名为“电视机”的商品种数、最高价、最低价及平均价。练习题答案：1、SELECT 商品代号，分类名，单价；FROM SPK1；WHERE 单价1000 AND 单价=10；ORDER BY 单价 DESC在WHERE中BETWEENAND和NOT BETWEEND 使用例 从SPK1中查询出单价在1000元至2500元之间的所有商品。SELECT 商品代号，分类名，单价；FROM SPK1；WHERE 单价 BETWEEN

17、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

18、.商品代号=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选项中，并且还丰富了连

19、接的功能，除了上述介绍的一般连接（在新版本中称作中间连接）外，还增加了左连接和右连接的功能。在FROM选相中的相应语法格式分别为：中间连接 FROM INNER JOIN ON .左连接 FROM LEFT JOIN ON .右连接 FROM RIGHT JOIN ON .每一种连接都隐含着双重循环的执行过程：对于中间连接，外循环依次扫描第一个表中的每个元组，内循环依次扫描第二个表中的每个元组，当满足连接条件时就连接起来形成中间表中的一个新元组；对于左连接，除了按中间连接形成中间表中的新元组外，还把第一个表中的没有形成连接的所有元组也加入到中间表中，这些元组在第二个表上所对应的列值被自动置为空

20、；对于右连接，除了按中间连接形成中间表中的新元组外，还把第二个表中的没有形成连接的所有元组也加入到中间表中，这些元组在第一个表上所对应的列值被自动置为空。完成连接后，查询语句再根据WHERE选项中提供的筛选条件从中间表中选择出元组，然后再根据SELECT选项投影出所需要的列形成结果表。例 16.a 从教学库中查询出所有学生的选课情况，要求没选修任何课程的学生信息也要反映出来Select *From 学生 left join 选课 on 学生.学生号=选课.学生号例 16.b 从学生库中查询出所有课程被学生选修的情况Select*From 课程 left join(选课 inner join 学

21、生 on选修.学生号=选课.学生号)on 课程.课程号=选课.课程号 例 16.c 从教学库中查询出所有电子专业的学生选课的全部情况Select *From 学生 inner join (选课 inner join 课程 on 选课.课程号=课程.课程号)on 学生.学生号=选课.学生号Where 专业=电子 (与传统查询语句等效)用于查询语句中的专门比较式又叫判断式，它实现单值与集合数据之间的比较。常用的有以下六种格式。格式1：ALL ()功能：是一条完整的SELECT语句，被嵌套在该比较式中使用。当的查询结果中的每一个值都满足所给的比较条件时，此比较式的值才为真，否则为假。例 17 从商品

22、表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()当子查询的查询结果中的任一个值满足所给

23、的比较条件时，此比较式为真，否则为假。该格式中的两个关键字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 学生

24、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 单价=

25、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 学生Whe

26、re 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 ()|()使用逗号分开的若干个常量。当所制定列的当前值包括在由所给定的值之内时，则此判断式为真，否则为假

27、。例 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选项 该选

28、项中的,等必须是出现在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 分类名,ma

29、x(单价)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 学生

30、号 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选项中所给表达式的顺序号（即对应查询结果中的列号）或定义的列名。若其后带

31、有ASC关键字，则将按值的升序排序查询结果，若其后带有DESC关键字，则将按值的降序排序查询结果，若不指定排序方式，则默认按升序排序。注意：该选项只能用在最外层的查询语句中，不能在子查询中使用。例 35 从商品表中按单价升序查询出所有商品记录。Select*From 商品表1Order by 单价例 36 从商品表1中查询出单价比平均单价高的所有商品，并使结果按降序排列。Select*From 商品表1Where 单价all(select avg(单价)from 商品表1)Order by 单价 desc例 37 从教学库中查询出所有学生的学号及所选课程的门数，按门数升序排列结果Select

32、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 从教学库中查询出每个学生的学生号、选课门

33、数和总成绩，把查询结果保存到学生选课门数及总成绩统计表中。Select x.学生号,count(x.学生号)as 选课门数,sum(成绩)as 总成绩 into 学生选课门数及总成绩统计表From 学生x,选课 yWhere x.学生号=y.学生号Group by x.学生号写出下面的写出下面的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