首页计算机计算机软件数据库技术 11 第十一讲 SQL语言.pptx

11 第十一讲 SQL语言.pptx

页数:19页文件大小:109.7 KB来源: 数据库技术ppt课件

本课件继续讲解SQL数据定义中的视图与索引,说明视图减少冗余与索引加速检索的作用及语法,随后系统演示SELECT语句的六个子句,并结合示例表说明无条件查询、条件运算符、排序、嵌套查询、聚集函数与分组筛选的写法。

第十一讲封面,继续介绍SQL数据定义部分并讲解查询语句的使用
本讲内容提纲页,明确SQL数据定义部分与查询语句使用两项主题
视图作用说明页,指出视图可减少冗余、简化查询,其数据不实际存储且派生属性列不真实存在
查看本课件的文字版(共 19 页,供检索与复制)

第1页:第十一讲 SQL语言

第十一讲SQL语言

第2页:本讲内容概览

本讲内容

  • 继续介绍SQL语言中的数据定义部分
  • 介绍SQL查询语句的使用

第3页:关于视图

关于视图

  • 视图的主要作用是减少冗余和简化查询。基表只存放基本数据,由基本数据经过各种计算派生出的数据一般是不存储的。
  • 在已有字段上的视图定义当然也不含这些派生数据。
  • 视图中的数据不实际存储。根据基表中的列派生出的属性列不实际存在,故称属性列。

第4页:定义视图

定义视图:

CREATE VIEW <视图名>[(<列名1>[,<列名2>]……)] AS <子查询> [WITH CHECK OPTION];

子查询可以是任意复杂的 SELECT 语句,但通常不允许含有 ORDER BY 子句和 DISTINCT 短语;WITH 子句表示对视图进行 UPDATE、INSERT 和 DELETE 操作时保证更新、插入和删除的行满足视图定义的谓词条件。视图的列名可以省略,但特定情况下需全部指定。

第5页:删除视图

删除视图

  • 格式:DROPVIEW&lt;视图名&gt;;
  • 视图所基的表如果被删除,视图将失效,基于该视图的其它视图也失效。但视图的定义在未执行DROPVIEW语句前始终存在于数据字典

第6页:建立索引

建立索引:索引的作用在于加快表的检索(查询)速度,系统在存取数据时会自动选择合适的索引作为存取路径。

CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名>(<列名>[<次序>][,<列名>[<次序>]]……);

次序有两种,即升序(ASC,缺省值)与降序(DESC)。

第7页:建立索引示例

示例:

--为表 Student 中的列 Sno 建立唯一索引 CREATE UNIQUE INDEX Stusno ON Student(Sno); --为表 Course 中的列 Cno 建立唯一索引 CREATE UNIQUE INDEX Coucno ON Course(Cno); --为表 SC 中的列 Sno 和 Cno 建立唯一索引,Sno 升序,Cno 降序 CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC);

第8页:删除索引

删除索引

  • 一旦建立索引,数据库系统将负责使用、维护,用户不需要干预。使用索引能够减少查询的操作时间,但在数据增、删、改时,系统会花费时间、空间来维护相关索引表中的索引项,又会增加开销,所以索引并不是建得越多越好。有时需要对索引进行调整,如删除索引。
  • 格式:DROPINDEX&lt;索引名&gt;;
  • 例:删除索引Stusname。DROPINDEXStusname;

删除指定的索引后,系统会删除索引内容,并将索引的定义(描述)也从数据字典中删除。

第9页:查询格式

查询格式

  • 格式:SELECT[ALL|DISTINCT]&lt;目标列表达式&gt;[,&lt;目标列表达式&gt;]……FROM&lt;表名或视图名&gt;[,&lt;表名或视图名&gt;]……[WHERE&lt;条件表达式&gt;][GROUPBY&lt;列名1&gt;[HAVING&lt;条件表达式&gt;]][ORDERBY&lt;列名2&gt;[ASC|DESC]];

注:“|”表示选择项,其中排在前面的ALL和ASC为缺省选择项。目标列表达式可以是列名,也可以是算术表达式、字符串常量、函数等。

列名1和列名2都可以出现多个列名

第10页:查询示例用表

示例用表及其关系:学生表 Student、课程表 Course 与选课表 SC。

Student 学生表
学号 Sno姓名 Sname性别 Ssex年龄 Sage所在系 Sdept
95001张三男20CS
96010李思思女19IS
95041王五男17CS
97101郑路路女18MA
96104任西女20MA
96023丁八男19IS
Course 课程表
课程号 Cno课程名 Cname先修课 Cpno学分 Ccredit
1数据库54
2数学—2
3信息系统14
4操作系统—3
5数据结构24
6C语言54
SC 选课表
学号 Sno课程号 Cno成绩 Grade
95001191
95001283
95001386
95041278
95041382
95041692
96010481
96104469
96023494

原课件以图示方式给出三表字段与数据,本表按图中字段顺序整理。

第11页:无条件查询

无条件查询:

SELECT Sname,2004-Sage FROM Student; SELECT Sname,'Year of Birth:',2004-Sage,ISLOWER(Sdept) FROM Student;
查询结果
姓名 Sname出生年份 2004-SageISLOWER(Sdept)
张三1984cs
李思思1985is
王五1987cs
郑路路1986ma
任西1984ma
丁八1985is

Sage 表示年龄,当前年份为 2004 年时,表达式 2004-Sage 即出生年份;字符串常量用于改变列的显示标题,函数作用于每一检索到的元组。

第12页:查询条件运算符

查询满足条件的元组:查询条件用条件表达式表示,可使用下列运算符。

常用查询条件与运算符
查询条件运算符
比较=、>、<、>=、<=、!=、<>、!>、!<、NOT+比较符
确定范围BETWEEN……AND……、NOT BETWEEN……AND……
确定集合IN、NOT IN
字符匹配LIKE、NOT LIKE
空值判断IS NULL、IS NOT NULL
逻辑运算AND、OR、NOT

第13页:条件查询示例

条件查询:

SELECT Sname FROM Student WHERE Sdept='CS'; SELECT Sname,Sdept,Sage FROM Student WHERE Sage BETWEEN 20 AND 23; SELECT Sname,Ssex FROM Student WHERE Sdept IN ('IS','MA','CS'); --找出95级学生的信息 SELECT * FROM Student WHERE Sno LIKE '95%';

学号的前两位表示该学生的所在级号。

第14页:排序查询

排序查询

  • 例:显示所有学生的信息,按系排序(不指定升序或降序,通常使用缺省值,一般升序为SQL语言中的缺省值),同系中按年龄从大到小排列(降序)。SELECT*FROMStudentORDERBYSdept,SageDESC;

在排序时可以指定多个列,系统将按照指定列的顺序来依次列示结果。

有时也可以使用列的序号来指定排序的列,这在列为常量、函数等较长列名时尤其有效。SELECT*FROMStudentORDERBY5,4DESC;

这里的“5”即指列Sdept,“4”即指Sage。SdeptSageSsexSnameSno、CS20男张三95001CS17男王五95041IS19女李思思96010IS19男丁八96023MA20女任西96104MA18女郑路路97101SdeptSageSsexSnameSno、CS20男张三95001IS19女李思思96010CS17男王五95041MA18女郑路路97101MA20女任西96104IS19男丁八96023

第15页:嵌套查询

嵌套查询:

--查询其它系中比 IS 系某一学生年龄小的学生名单 SELECT Sname,Sage FROM Student WHERE Sage<ANY (SELECT Sage FROM Student WHERE Sdept='IS') AND Sdept<>'IS' ORDER BY Sage DESC; --查询所有选修了1号课程的学生姓名 SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno='1');

第16页:聚集函数

计算查询:又称集合函数、组函数、分组函数、聚集函数,与分组短语一起使用,作用于元组集。

常用聚集函数
函数作用
COUNT([DISTINCT|ALL] *)统计元组个数
COUNT([DISTINCT|ALL] <列名>)统计一列中值的个数
SUM([DISTINCT|ALL] <列名>)计算一列值的总和(列须是数值型)
AVG([DISTINCT|ALL] <列名>)计算一列值的平均值(列须是数值型)
MAX([DISTINCT|ALL] <列名>)计算一列值的最大值
MIN([DISTINCT|ALL] <列名>)计算一列值的最小值

第17页:聚集函数示例

示例:

--求总的学生数 SELECT COUNT(*) FROM Student; --求选修了课程的学生数 SELECT COUNT(DISTINCT Sno) FROM SC; --求课程4的平均成绩 SELECT AVG(Grade) FROM SC WHERE Cno='4'; --求课程4的最高分数 SELECT MAX(Grade) FROM SC WHERE Cno='4';
查询结果
统计项结果
COUNT(*)6
COUNT(DISTINCT Sno)5
AVG(Grade)81.333333333
MAX(Grade)94

第18页:分组查询

与分组结合:将查询结果按一列或多列取值相等的原则分组,以细化集合函数的作用对象。

--找出每门的选修人数 SELECT Cno,COUNT(Sno) FROM SC GROUP BY Cno; --查询选修人数超过2人(不含2人)的课程号 SELECT Cno FROM SC GROUP BY Cno HAVING COUNT(*)>2;
各课程选修人数
课程号 Cno选修人数 COUNT(Sno)
11
22
32
61
43

HAVING 子句用于对分组结果进行筛选。

第19页:第十一讲小结

小结

  • 继续介绍了CREATE INDEX和CREATE VIEW两种定义语句。
  • 简单介绍了SQL的查询,主要对其六个子句SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY做了示例说明。
  • 问题
  • SQL查询中WHERE和HAVING子句的差别在哪里?
顶部目录