第3章关系数据库标准语言SQL(3.16)

,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,第三章 关系数据库标准语言,SQL,SQL,概述,SQL,数据定义功能,SQL,数据查询功能,(,重点,),SQL,数据更新功能,SQL,中的视图,(,重点,),SQL,数据控制功能,3.1 SQL,概述,SQL,的,历史,SQL,的特点,SQL,数据库体系结构,1.,历史,SQL: Structured Query Language,1974,年，由,Boyce,和,Chamber,提出,1975-1979,年，在,System R,上实现，由,IBM,的,San Jose,研究室研制，称为,Sequel,3.1 SQL,概述,2.,标准化,有关组织,ANSI(American Natural Standard Institute),ISO(International Organization for Standardization),有关标准,SQL-86,“,数据库语言,SQL,”,SQL-89,“,具有完整性增强的数据库语言,SQL,”,，,增加了对完整性约束的支持,SQL-92,“,数据库语言,SQL,”,，是,SQL-89,的超集，增加了许多新特性，如新的数据类型，更丰富的数据操作，更强的完整性、安全性支持等。,SQL-2003,增加对面向对象模型的支持,3.1 SQL,概述,3.,特点,综合统一,高度非过程化,面向集合的操作方式,两种使用方式，统一的语法结构,自含式语言（用户使用），,又是嵌入式语言（程序员使用）,语言简洁，易学易用,3.1 SQL,概述,SQL,功能,操作符,数据查询,SELECT,数据定义,CREATE，ALTER，DROP,数据操纵,INSERT，UPDATE，DELETE,数据控制,GRANT，REVOKE,9,个动词,并且语法简单,4 .SQL,的功能,用户,1,用户,2,用户,4,视图,V1,视图,V1,用户,3,基本表,B1,基本表,B2,基本表,B4,基本表,B3,存储文件,S1,存储文件,S2,存储文件,S4,存储文件,S3,SQL,数据库体系结构,SQL,用户,外模式,模式,内模式,5 SQL,的基本概念,SQL,概述,SQL,数据定义功能,SQL,数据查询功能,(,重点,),SQL,数据更新功能,SQL,中的视图,(,重点,),SQL,数据控制功能,3.2 SQL,的数据定义功能,主要为应用系统定义数据库上的整体结构模式，包括三个层次：,1.,上层,-,模式层,关系数据库管理系统可以定义多个模式,每个模式对应一个应用系统,模式由若干个表、视图及索引组成,3.2 SQL,的数据定义功能,2.,中层,-,表结构层,基表,创建表,修改表,删除表,视图,视图是建立在基表上的虚表,创建视图,删除视图,索引,创建索引,删除索引,3.2 SQL,的数据定义功能,3.,底层,-,列定义层,表中属性的定义,如列名的定义,列数据类型的定义,列的完整性约束定义,如列是否是主键或外键,列值是否允许为空,列间的约束表达式,学生,-,课程 数据库,学生,-,课程 数据库,STCDB,:,学生表：,Student(,Sno,Sname,Ssex,Sage,Sdept),课程表：,Course(,Cno,Cname,Cpno,Ccredit),学生选课表：,SC(,Sno,Cno,Grade),Student,表,学 号,Sno,姓 名,Sname,性 别,Ssex,年 龄,Sage,所 在 系,Sdept,200215121,200215122,200215123,200515125,李勇,刘晨,王敏,张立,男,女,女,男,20,19,18,19,CS,CS,MA,IS,Course,表,课程号,Cno,课程名,Cname,先行课,Cpno,学分,Ccredit,1,2,3,4,5,6,7,数据库,数学,信息系统,操作系统,数据结构,数据处理,PASCAL,语言,5,1,6,7,6,4,2,4,3,4,2,4,SC,表,学 号,Sno,课程号,Cno,成绩,Grade,200215121,200215121,200215121,200215122,200215122,1,2,3,2,3,92,85,88,90,80,3.3,数据定义,SQL,的数据定义功能,:,模式,定义、表定义、视图和索引的定义,3.3.1,数据库的创建,数据库可以被看成是包含表、视图、索引以及触发器等数据库对象的容器。,创建数据库前必须先确定数据库的名称，所有者、大小以及用于该数据库的文件等,SQL Server 2005,管理界面的启动及使用：,单击“开始”,|,所有程序,|Microsoft SQL Server 2005|,SQL Server Management Studio,弹出“连接到服务器”对话框，单击“连接”，服务器的名字选取为“本地机名字”（机器编号）,SQL Server 2005,的使用,例,3.1,创建学生课程数据库，各项参数取系统默认值。,在管理界面单击“新建查询”，输入下面的,T-SQL,语句,Create Database STCDB,；,按“执行”或“,ctrl +E”,快捷健,返回管理界面就能看到数据库名字,3.3.1,数据库的创建,一,.,基本表的定义（,CREATE,）,格式,一般格式如下：,CREATE,TABLE,(, ,列级完整性约束条件,，, ,列级完整性约束条件,.,),;,3.3.2,基本表的定义删除与修改,基本表的定义,列级完整性约束条件,:,指非空、唯一、主码、缺省值等。,not null,UNIQUE,default,缺省值,表级完整性约束条件,:,包括主码、外码、用户自定义商业规则或完整性约束，可以用以下语法来描述：,，,primary key,（,列名,，列名,）,，,foreign key,（,列名,，列名,）,references,表名 （列名,，列名,）,，,check,（,条件）,基本表的定义,NOT NULL,列级,(,不,),允许存在空值,(,主属性,),UNIQUE,列级,唯一性约束,DEFAULT,列级,默认值约束,CHECK,表级,检验约束,为插入列中的数据指定约束条件,PRIMARY KEY,表级,主键约束,(,不空,唯一,),FOREIGN KEY,表级,外键约束,(,用于参照完整性,),完整性约束条件,例,3.2,建立“学生”表,Use STCDB;,CREATE TABLE,STUDENT,( SNO,CHAR,（,8,）,SNAME,CHAR,（,8,）,NOT NULL,SAGE,SMALLINT,SSEX,CHAR,（,2,）,DEFAULT ,男,PRIMARY KEY,(SNO),CHECK,(SEX=,男,OR SEX=,女,),),基本表的定义,Use STCDB;,CREATE TABLE,SC,(SNO,CHAR,（,8,）,CNO,CHAR,（,4,）,GRADE,SMALLINT,PRIMARY KEY,(SNO,，,CNO),FOREIGN KEY,(SNO),REFERENCES,STUDENT(SNO),FOREIGN KEY,(CNO),REFERENCES,COURSE(CNO),CHECK,(GRADE,IS NULL,),OR,GRADE,BETWEEN,0,AND,100),例,3.3,建立选修表,课程表,Course,例,3.4,建立一个,“,课程,”,表,Course,CREATE TABLE Course,(,Cno,CHAR(4) PRIMARY KEY,，,Cname,CHAR(40),，,Cpno,CHAR(4),，,Ccredit,SMALLINT,，,FOREIGN KEY (,Cpno,) REFERENCES,Course(Cno,),);,先修课,Cpno,是外码,被参照表是,Course,被参照列是,Cno,允许的常用类型为：,CHAR,（,n),长度为,n,的定长字符串,VARCHAR(n,),最大长度为,n,的变长字符串,INTEGER,（,INT,）全字长二进制整数,SMALLINT,半字长二进制整数,FLOAT,双字长浮点数,DATE,日期型，格式为,YYYY-MM-DD,TIME,时间性,格式为,HH.MM.SS,基本表的定义,基本表的定义,练习：,建立一个“学生”表,Student,，,它由学号,Sno,、,姓名,Sname,、,性别,Ssex,、,年龄,Sage,、,所在系,Sdept,五个属性组成，其中学号属性不能为空，并且其值是唯一的,CREATE TABLE,Student,(,Sno,CHAR(5),NOT NULL UNIQUE,Sname,CHAR(20),Ssex,CHAR(2),Sage INT,Sdept,CHAR(15),);,二、修改基本表定义（,ALTER,）,格式为：,ALTER,TABLE, ADD ,完整性约束, , DROP, MODIFY ;,3.3.2,基本表的定义、修改与删除,注意！,（,1,）只能间接删除属性列,把表中要保留的列及其内容复制到一个新表中,删除原表,再将新表重命名为原表名,（,2,）不能修改完整性约束,（,3,）不能为已有列增加完整性约束,基本表的修改,基本表的修改,例,3.5,向,Student,表增加“入学时间”列，其数据类型为日期型,ALTER,TABLE,Student,ADD,Scome,DATE;,注意：,不论基本表中原来是否已有数据，,新增加的列一律为空值,基本表的修改,例,3.6,:,将年龄的数据类型改为半字长整数,ALTER,TABLE,Student,MODIFY,Sage SMALLINT;,注意： 修改原有的列定义,有可能会破坏已有数据,基本表的修改,例,3.7,删除关于学号必须取唯一值的约束,ALTER TABLE,Student,DROP,UNIQUE(Sno,);,三,.,删除基本表定义（,drop,）,格式,drop table,表名,系统从数据字典中删去,:,该基本表的描述,该基本表上的所有索引的描述,系统从文件中删去表中的数据,表上的视图往往仍然保留，但无法引用,3.3.2,基本表的定义、修改与删除,DROP TABLE ,RESTRICT| CASCADE,；,RESTRICT,：,删除表是有,限制,的。,欲删除的基本表不能被其他表的约束所引用,如果存在依赖该表的对象，则此表不能被删除,CASCADE,：,删除该表,没有限制,。,在删除基本表的同时，相关的依赖对象一起删除,基本表的删除,例,3.8,删除,Student,表,drop table student;,危险！,删除基本表后，基本表的定义、表中数据、索引、以及由此表导出的视图的定义都被删除,基本表的删除,一,.,索引的定义,.,索引的概念,-,对表中数据提供,“,逻辑顺序,”,3.3.3,索引的建立与删除,建立索引是加快查询速度的有效手段,建立索引,DBMS,自动建立,DBA,或表的属主（即建立表的人）根据需要建立,维护索引,DBMS,自动完成,使用索引,DBMS,自动选择是否使用索引以及使用哪些索引,二,.,索引的建立,CREATE,UNIQUE CLUSTER,INDEX,ON ,(,.);,用,指定要建索引的基本表名字,索引可以建立在该表的一,列,或多列上，各列名之间用,逗号,分隔,用,指定索引值的排列次序，升序：,ASC,，降序：,DESC,。缺省值：,ASC,UNIQUE,表明此索引的每一个索引值只对应唯一的数据记录,CLUSTER,表示要建立的索引是聚簇索引,-,索引的顺序与表中记录的物理顺序一致,(,一个,在最常用的列,),索引的建立与删除,索引的建立与删除,例,3.9,为学生,-,课程数据库中的,Student,、,Course,、,SC,三个表建立索引。其中,Student,表按学号,升序建唯一索引,Stusno,;, Course,表按课程号升序建唯一索引,Coucno,;, SC,表按学号升序和课程号降序建唯一索引,Scno,CREATE UNIQUE INDEX,Stusno,ON,Student(Sno,),;, CREATE UNIQUE INDEX,Coucno,ON,Couse(Cno,);, CREATE UNIQUE INDEX,Scno,ON,SC(Sno,ASC ,Cno,DESC);,以下情况适合建立索引,1.,经常被查询的列,2.,用,ORDER BY,子句的列,3.,是主码或外码的列,4.,列值是,UNIQUE,唯一的列,5.,经常出现在查询或连接中的,两个或多个列,以下情况不适合建立索引,1.,查询中极少用到的列,2.,包含太多重复行的列,(,性别,班,号,),索引的建立与删除,三,.,索引的删除,格式,drop index,索引名,例,3.10,删除,Student,表的,Stusname,索引,DROP INDEX,Stusname,;,索引的建立与删除,索引一经建立，就由系统使用和维护它，不需用户干预。建立索引是为了减少查询操作的时间，但如果数据增删改频繁，系统会花费许多时间来维护索引。这时，可以删除一些不必要的索引。删除索引时，系统会同时从数据字典中删去有关该索引的描述,索引的建立与删除,注意： 索引的有关说明,可以动态地定义索引，即可以随时建立和删除索引,不允许用户在数据操作中引用索引。索引如何使用完全由系统决定，这支持了数据的物理独立性,应该在使用频率高经常用于连接的列上建索引,一个表上可建多个索引。索引可以提高查询效率，但索引过多耗费空间，且降低了插入、删除、更新的效率,索引的建立与删除,SQL,概述,SQL,数据定义功能,SQL,数据查询功能,(,重点,),SQL,数据更新功能,SQL,中的视图,(,重点,),SQL,数据控制功能,3.4 SQL,数据查询功能,单表查询,连接查询,嵌套查询,集合查询,SQL,的数据查询功能,一般格式为,:,S,ELECT, ALL | DISTINCT,.,FROM, .,WHERE,GROUP BY HAVING ,ORDER BY , ASC | DESC,;,SELECT,子句,：指定要显示的属性列,FROM,子句,：指定查询对象,(,基本表或视图,),WHERE,子句,：指定查询条件,GROUP BY,子句,：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。,HAVING,短语,：筛选出满足指定条件的组,ORDER BY,子句,：对查询结果表按指定列值的升序或降序排序,SQL,的数据查询功能,示例数据库,学生,-,课程数据库,学生表：,Student(,Sno,，,Sname,，,Ssex,，,Sage,，,Sdept,),课程表：,Course(,Cno,，,Cname,，,Cpno,，,Ccredit,),学生选课表：,SC(,Sno,，,Cno,，,Grade),SQL,的数据查询功能,3.4.1,、单表查询,查询仅涉及一个表，是一种最简单的查询操作,选择表中的若干列,选择表中的若干元组,对查询结果排序,使用集函数,对查询结果分组,SQL,的数据查询功能,一,.,选择表中的若干列,1,、查询指定列,方法,在,SELECT,子句的,中指定要查询的属性,注意,：,中各个列的先后顺序可以与表中的逻辑顺序不一致。即用户可以根据应用的需要改变列的显示顺序,SQL,的数据查询功能,例,1,查询,Student,表中,所有,学生的,学号,与,姓名,SELECT,Sno,Sname,FROM Student;,SQL,的数据查询功能,例,2,查全体学生的姓名、学号、系别,SELECT,Sname,Sno,Sdept,FROM Student;,SQL,的数据查询功能,2,、查询全部列,方法,在,SELECT,关键字后面列出所有列名,当列的显示顺序与其在基表中的顺序相同时，也可以简单地将,指定,为 *,SQL,的数据查询功能,例,3,查询全体学生的信息,SELECT,Sno,，,Sname,，,Ssex,，,Sage,，,Sdept,FROM Student,；,或,SELECT *,FROM Student,；,-,全表查询,SQL,的数据查询功能,3,、查询经过计算的列,方法,SELECT,子句的,为表达式,算术表达式,字符串常量,函数,列别名,学 号,Sno,姓 名,Sname,性 别,Ssex,年 龄,Sage,所 在 系,Sdept,95001,李勇,男,20,CS,95002,刘晨,女,19,IS,95003,王敏,女,18,MA,95004,张立,男,19,IS,Student,课程号,课程名,先行课,学分,Cno,Cname,Cpno,Ccredit,1,数据库,5,4,2,数学,2,3,信息系统,1,4,4,操作系统,6,3,5,数据结构,7,4,6,数据处理,2,7,PASCAL,语言,6,4,学 号,课 程 号,成 绩,Sno,Cno,Grade,95001,1,92,95001,2,85,95001,3,88,95002,2,90,95002,3,80,Course,SC,SQL,的数据查询功能,例,4,查全体学生的姓名及其,出生年份。,SELECT,Sname,，,2012-Sage,FROM Student,；,输出结果：,Sname,2012-Sage,- -,李勇,1990,刘晨,1991,王敏,1992,张立,1991,SQL,的数据查询功能,例,4.1,指出下列语句的含义,(1),SELECT,Sname, Year of Birth:, 2012-Sage,，,ISLOWER(Sdept,),FROM Student,;,-,查全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名,例题（续）,输出结果：,Sname,Year of Birth: 2012-Sage,ISLOWER(Sdept,),- - - -,李勇,Year of Birth: 1990,cs,刘晨,Year of Birth: 1991 is,王敏,Year of Birth: 1992 ma,张立,Year of Birth: 1991 is,(2)SELECT PNAME，SAL,0.95,FROM PROF,-,给出所有老师的姓名及税后工资额,SQL,的数据查询功能,例题（续）,例,4.2,使用列别名改变查询结果的列标题,SELECT,Sname,NAME,，,Year of Birth: BIRTH,，,2012-Sage BIRTHDAY,，,ISLOWER(Sdept,) DEPARTMENT,FROM Student,；,输出结果：,NAME BIRTH BIRTHDAY DEPARTMENT,- - - -,李勇,Year of Birth: 1990,cs,刘晨,Year of Birth: 1991 is,王名,Year of Birth: 1992 ma,张立,Year of Birth: 1991 is,SQL Server,的使用,对于有的数据库系统支持多库，需要对数据库进行操作：,数据库的建立与撤消,建立一个新数据库,create database,数据库名,撤消一个数据库,drop database,数据库名,关闭当前数据库,close database,数据库名,在,SQL Server2000,中进行,SQL,语句的实验，可以采取下列步骤：,（,1,）打开企业管理器，双击选定的数据库。在菜单“工具”中，单击“,SQL,查询分析器”，打开查询分析器,（,2,）在查询分析器中输入响应的,SQL,语句,（,3,）按,F5,键或单击“执行”按钮（右键）,单表查询还可以使用：,（,1,）打开企业管理器，双击选定的数据库。,（,2,）选定需要更新的基本表，单击右键，选择快捷菜单中的“打开表”，单击“查询”按钮，进入,SQL,语句的编辑界面,(3),按照提示，写出,WHERE,子句，单击“运行”按钮执行,SQL,的数据查询功能,二、 选择表中的若干元组,1,、,消除取值重复的行,两个本来不相同的元组，投影到某些列上后，可能相同。,例,5,查所有选修课的学生的学号,SELECT,Sno,FROM SC;,SQL,的数据查询功能,设有数据,Sno,Cno,Grade,- - -,95001 1 92,95001 2 85,95001 3 88,95002 2 90,95002 3 80,执行上面的,SELECT,语句后，结果为：,Sno,-,95001,95001,95001,95002,95002,该查询结果里包含了许多重复的行。,如果想去掉结果表中的重复行，,必须指定,DISTINCT,短语：,SQL,的数据查询功能,SELECT,DISTINCT,Sno,FROM SC;,执行结果为：,Sno,95001,95002,SQL,的数据查询功能,2,、查询满足条件的元组,属于选择运算,通过,WHERE,子句实现,比较大小,确定范围,确定集合,字符串匹配,涉及空值的查询,多重条件查询,WHERE,子句常用的查询条件有：,查询条件 谓词,比较,=,，, .=, , !, !,确定范围,BETWEEN AND,，,NOT BETWEEN AND,确定集合,IN NOT IN,字符匹配,LIKE NOT LIKE,空 值,IS NULL,，,IS NOT NULL,多重条件,AND OR,SQL,的数据查询功能,SQL,的数据查询功能,比较大小,例,6,查计算机系全体学生的名单，学号,SELECT,Sname,，,Sno,FROM Student,WHERE,Sdept,= CS,;,SQL,的数据查询功能,例,7,查所有年龄在,20,岁以下的学生姓名及其年龄,SELECT,Sname, Sage,FROM Student,WHERE,Sage = 20;,例,8,查考试成绩有不及格的学生的学号,SELECT,DISTINCT,Sno,FROM SC,WHERE Grade =20 AND Sage=23 ),SQL,的数据查询功能,SQL,的数据查询功能,例,10,查询年龄不在,20,至,23,岁之间的学生姓名、系别和年龄。,SELECT,Sname,Sdept, Sage,FROM Student,WHERE Sage,NOT BETWEEN 20 AND 23;,SQL,的数据查询功能,确定集合,用,IN,可以用来查询属性值属于指定集合的元组,例,11,查信息系（,IS,）、,数学系（,MA,）,和计算机科学系（,CS,）,的学生的姓名和性别,SELECT,Sname,Ssex,FROM Student,WHERE,Sdept,IN,(IS, MA, CS),相当,(,Sdept,= IS OR,Sdept,= MA OR,Sdept,= CS,）,SQL,的数据查询功能,例,12,查既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别,SELECT,Sname,Ssex,FROM Student,WHERE,Sdept,NOT IN (IS, MA, CS),SQL,的数据查询功能,字符匹配,谓词,LIKE,可以用来进行字符串的匹配。,方法,使用谓词,LIKE,或,NOT LIKE,NOT LIKE ESCAPE ,：指定匹配模板,匹配模板：,固定字符串或含通配符的字符串,字符串匹配,(,续,),通配符,% (,百分号,),代表任意长度（长度可以为,0,）的字符串。,例：,a%b,表示以,a,开头，以,b,结尾的任意长度的字符串。如,acb,，,addgb,，,ab,等都满足该匹配串。,_ (,下横线,),代表任意,单个字符,。,例：,a_b,表示以,a,开头，以,b,结尾的长度为,3,的任意字符串。如,acb,，,afb,等都满足该匹配串。,字符串匹配,(,续,),匹配模板为固定字符串,例如：,查询学号为,95001,的学生的详细情况。,SELECT *,FROM Student,WHERE,Sno,LIKE 95001,；,等价于：,SELECT *,FROM Student,WHERE,Sno,= 95001,；,当匹配模板为固定字符串时，可以用,=,运算符取代,LIKE,谓词，用,!=,或,运算符取代,NOT LIKE,谓词,SQL,的数据查询功能,匹配模板为含通配符的字符串,例,13,查所有姓刘的学生的姓名、学号和性别,SELECT,Sname,Sno,Ssex,FROM,Student,WHERE,Sname,LIKE ,刘,%;,SQL,的数据查询功能,例,14,查姓“欧阳”且全名为三个汉字的学生的姓名,SELECT,Sname,FROM Student,WHERE,Sname,LIKE ,欧阳,_ _;,注意，由于一个汉字占两个字符的位置，,所以匹配串欧阳后面需要跟个,_,。,SQL,的数据查询功能,例,15,查名字中第二字为“阳”字的学生的姓名和学号,SELECT,Sname,Sno,FROM Student,WHERE,Sname,LIKE _ _,阳,%;,SQL,的数据查询功能,例,16,查所有不姓刘的学生姓名 、学号及性别,SELECT,Sname,Sno,Ssex,FROM Student,WHERE,Sname,NOT LIKE ,刘,%;,SELECT,Cno,Ccredit,FROM,Course,WHERE,Cname,LIKE DB _Design ESCAPE;,SQL,的数据查询功能,SQL,的数据查询功能,涉及空值的查询,例,17,某些学生选修某门课程后没有参加考试，所以有选课记录，但没有考试成绩，下面我们来查一下缺少成绩的学生的学号和相应的课程号,SELECT,Sno,Cno,FROM SC,WHERE Grade,IS,NULL;,注意这里的,IS,不能用等号（,=,） 代替。,SQL,的数据查询功能,SQL,的数据查询功能,例,18,查所有有成绩记录的学生学号和课程号,SELECT,Sno,Cno,FROM SC,WHERE Grade IS NOT NULL;,SQL,的数据查询功能,多重条件查询,逻辑运算符,AND,和,OR,可用来联结多个查询条件。,如果这两个运算符同时出现在同一,个,WHERE,条件子句中，则,AND,的优先级高于,OR,，,但用户可以用括号改变优先级。,SQL,的数据查询功能,例,19,查,CS,系年龄在,20,岁以下的学生姓名,SELECT,Sname,FROM Student,WHERE,Sdept,=CS,AND,Sage20;,SQL,的数据查询功能,3,、 对查询结果排序,可以用,ORDER BY,子句指定按照一个 或多个属性列的升序（,ASC,）,或降序（,DESC,）,重新排列查询结果，,其中升序,ASC,为缺省值,SQL,的数据查询功能,例,20,查询选修了,3,号课程的学生的学号及其成绩，查询结果按分数的,降序,排列,SELECT,Sno, Grade,FROM SC,WHERE,Cno,=3,ORDER BY,Grade DESC;,SQL,的数据查询功能,例,21,查询全体学生情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列,SELECT *,FROM Student,ORDER BY,Sdept, Sage DESC;,SQL,的计算、统计，分类,4. SQL,的计算、统计，分类功能（,使用集函数,）,为了进一步方便用户，增强检索功能，,SQL,提供了许多集函数，用以完成,计算、统计，分类,主要包括：,SQL,的计算、统计，分类,COUNT,( DISTINCT|ALL *),统计元组个数,COUNT,(DISTINCT|ALL ),统计一列中值的个数,SUM,( DISTINCT|ALL ),计算一列值的总和（此列必须是数值型）,SQL,的计算、统计，分类,AVG,(DISTINCT|ALL ),计算一列值的平均值（此列必须是数值型）,MAX,(DISTINCT|ALL ),求一列值中的最大值,MIN,(DISTINCT|ALL ),求一列值中的最小值,SQL,的计算、统计，分类,(1),统计功能,例,22,查询学生总人数,SELECT COUNT(*),FROM Student ;,SQL,的计算、统计，分类,例,23,查询选修了课程的学生人数,SELECT COUNT ( DISTINCT,Sno,),FROM SC;,SQL,的计算、统计，分类,例,24,计算,1,号课程的学生平均成绩,SELECT,AVG(Grade),FROM SC WHERE,Cno,=1;,例,25,查询学习,1,号课程的学生最高分数,SELECT,MAX(Grade),FROM SC WHERE,Cno,=1;,SQL,的计算、统计，分类,(2),计算功能,例,26,统计计算机系下一年度学生的姓名和年龄,SELECT,Sname,Sage+1,FROM STUDENT,WHERE,Sdep,= CS,SQL,的计算、统计，分类,(3),分类功能（对查询结果分组）,GROUP BY,子句可以将查询结果表的各行按一列或多列取值相等的原则进行分组。,SQL,的计算、统计，分类,例,27,查询各个课程号与相应的选课人数,SELECT,Cno,COUNT(Sno,),FROM SC,GROUP BY,Cno,;,该,SELECT,语句对,SC,表按,Cno,的取值进行分组，,所有具有相同,Cno,值的元组为一组，,然后对每一组作用集函数,COUNT,以求得该组的学生人数。查询结果为：,Cno,COUNT(Sno,),- -,1 22,2 34,3 44,4 33,5 48,SQL,的计算、统计，分类,例,28,指出下列语句的含义,(1) SELECT,Sno,COUNT(Cno,),FROM SC,GROP BY,Sno,-,给出每个学生选修的课程门数,SQL,的计算、统计，分类,(2),SELECT,Sno,FROM SC,WHERE,Sdept,=IS ,GROUP BY,Sno,HAVING COUNT(*)3;,单表查询结束,-,查询信息系选修了,3,门以上课程的学生的学号,SQL,的计算、统计，分类,二、连接查询,一个数据库中的多个表之间一般都存在某种内在联系，它们共同提供有用的信息。前面的查询都是针对一个表进行的。,若一个查询同时涉及两个以上的表，则称之为连接查询。,连接查询,连接查询,连接查询主要包括,等值连接,自然连接,非等值连接查询,自身连接查询,外连接查询,复合条件连接查询,连接查询,1, 等值与非等值连接查询,用来连接两个表的条件称为连接条件或连接谓词，其一般格式为：,. .,其中比较运算符主要有：,=,、,、,=,、,=,、,!=,当连接运算符为,=,时，称为 等值连接。,使用其它运算符称为 非等值连接。,连接谓词中的列名称为 连接字段。,连接条件中的各连接字段类型必须是可比的，但不必是相同的。,连接查询,此外连接谓词词还可以使用下面形式：,. BETWEEN . AND .,连接查询,设图书管理关系数据模型包括以下三个关系：,图书,book(,总编号，分类号，书名，作者，出版单位，单价,),读者,reader(,借书证号，姓名，性别，单位，职称，地址,),借阅,lend,（,借书证号，总编号，借阅日期，备注）,例,29,查找所有借阅了图书的读者姓名及所在单位,SELECT,DISTINCT,姓名，单位,FROM reader , lend,WHERE,reader.,借书证号,lend.,借书证号,;,连接查询,例,30,查询每个学生及其选修课程的情况,学生情况存放,在,Student,表中，学生选课情况存放,在,SC,表中，所以本查询实际上同时涉及,Student,与,SC,两个表中的数据,这两个表之间的联系是通过两个表都具有的属性,Sno,实现的。要查询学生及其选修课程的情况，就必须将这两个表中学号相同的元组连接起来。这是一个等值连接,。,连接查询,完成本查询,的,SQL,语句为：,SELECT Student.* , SC.*,FROM Student , SC,WHERE,Student.Sno,=,SC.Sno,;,连接查询,设有数据：,Student,SC,学号 姓名 性别 年龄 所在系,学号 课程号 成绩,Sno,Sname,Ssex,Sage,Sdept,Sno,Cno,Grade,95001,李勇 男,20 CS,95001 1 92,95002,刘晨 女,19 IS,95001 2 85,95003,王敏 女,18 MA,95001 3 88,95004,张立 男,19 IS,95002 2 90,95002 3 80,连接查询,结果为：,Student.sno,Sname,Ssex,Sage,Sdept,SC.Sno,Cno,Grade,95001,李勇 男,20 CS 95001 1 92,95001,李勇 男,20 CS 95001 2 85,95001,李勇 男,20 CS 95001 3 88,95002,刘晨 女,19 IS 95002 2 90,95002,刘晨 女,19 IS 95002 2 80,如果是按照两个表中的相同属性进行等值连接，且目标列中去掉了重复的属性列，但保留了所有不重复的属性列,-,自然连接,连接查询,连接查询,例,31,自然连接,Student,和,SC,表,SELECT,Student.Sno,Sname,Ssex, Sage,Sdept,Cno, Grade,FROM Student , SC,WHERE,Student.Sno,=,SC.Sno,;,连接查询,在本查询中，由于,Sname,、,Ssex,、,Sage,、,Sdept,、,Cno,和,Grade,属性,列在,Student,与,SC,表中是唯一的，因此引用时可以去掉表名前缀,。而,Sno,在两个表都出现了，因此引用时必须加上表名前缀。该查询的执行结果不再出现,SC.Sno,列。,连接查询,Student.sno,Sname,Ssex,Sage,Sdept,Cno,Grade,95001,李勇 男,20 CS 1 92,95001,李勇 男,20 CS 2 85,95001,李勇 男,20 CS 3 88,95002,刘晨 女,19 IS 2 90,95002,刘晨 女,19 IS 2 88,例,:,设有关系,CAVE,Cno,AveScore,1 80,2 91,指出下列语句的含义,Select,Sno,SC.Cno, Grade ,AveScore,From SC ,CAVE,Where,SC.Cno,=,CAVE.Cno,AND,Grade ,AveScore,;,-,查找成绩低于平均分的同学,-,非等值连接,连接查询,2.,自身连接,连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，这种连接称为表的自身连接。,例,32:,在关系,Course (,Cno,Cname,Cpno,Credit,),查询每一门课的间接先修课（即先修课的先修课）,课程号 课程名 先行课 学分,Cno,Cname,Cpno,Credit,1,数据库,5,4,2,数学,2,3,信息系统,1 4,4,操作系统,6 3,5,数据结构,7,4,6,数据处理,2,7 PASCAL 6 4,连接查询,*,我们先来分析一下，题目要求查询每一门课程的先修课的先修课，在“课程,”表即,Course,关系中，只有每门课的直接先修课信息，而没有先修课的先修课，要得到这个信息，必须先对一门课找,到其先修课,，再按此先修课的课程号，,查找它的先修课程，,这相当于,将,Course,表与其自身连接后，取第一个副本的课程号与第二个副本的先修课号做为目标列中的属性。具体写,SQL,语句时,连接查询,连接查询,清楚起见，我们可以,为,Course,表取两个别名，一个,是,FIRST,，,另一个是,SECOND.,连接查询,完成该查询,的,SQL,语句为：,SELECT,FIRST.Cno, SECOND.,Pcno,FROM Course FIRST, Course SECOND,WHERE,FIRST.Pcno,=,SECOND.Cno,;,连接查询,Cno,Pcno,1 7,3 5,5 6,连接查询,思考：在例,31,中，为什么没有,95003,95004,的信息？,Student.sno,Sname,Ssex,Sage,Sdept,Cno,Grade,95001,李勇 男,20 CS 1 92,95001,李勇 男,20 CS 2 85,95001,李勇 男,20 CS 3 88,95002,刘晨 女,19 IS 2 90,95002,刘晨 女,19 IS 2 88,在通常的连接操作中，只有满足连接条件的元组才能作为结果输出，如在例,30,和例,31,的结果表中没有关于,95003,和,95004,两个学生的信息，原因在于他们没有选课，在,SC,表中没有相应的元组。,但是有时我们想,以,Student,表为主体列出每个学生的基本情况及其选课情况，,若某个学生没有选课，则只输出其基本情况信息，其选课信息为空值即可，这时就需要使用外连接,（,Outer Join,）。,连接查询,连接查询,再来看例,31,，要列出每个学生的选课情况及基本情况，如何考虑,设有数据：,Student,SC,学号 姓名 性别 年龄 所在系,学号 课程号 成绩,Sno,Sname,Ssex,Sage,Sdept,Sno,Cno,Grade,95001,李勇 男,20 CS,95001 1 92,95002,刘晨 女,19 IS,95001 2 85,95003,王敏 女,18 MA,95001 3 88,95004,张立 男,19 IS,95002 2 90,95002 3 80,连接查询,结果为：,Student.sno,Sname,Ssex,Sage,Sdept,SC.Sno,Cno,Grade,95001,李勇 男,20 CS 95001 1 92,95001,李勇 男,20 CS 95001 2 85,95001,李勇