使用高级查询语言SQL

,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二层,第三层,第四层,第五层,*,第,11,章 使用高级查询语言,SQL,11.1 SQL,语言概述,11.2 SQL,的数据定义功能,11.3 SQL,的数据操纵功能,11.4 Access,中,SQL,查询的设计,11.1 SQL,概述,SQL(Structured Query Language),结构化查询语言是一种在关系数据库中，定义和操纵的标准语言。,它最早是,1974,年由,Boyce,和,Chamberlin,提出的（当时称作,SEQUEL,语言），后来在,1976,年，由,IBM,公司的,San Jose,研究所在研制关系数据库管理系统,System R,时修改为,SEQUEL2,，,也就是目前的,SQL,。,由于它功能丰富，语言简洁，使用方法灵活，倍受用户及计算机工业界欢迎。,如今无论是,Oracle,SQL Server,等大型的,DBMS,，,还是像,Access,，,Visual FoxPro,，,PowerBuilder,这些微机上常用的,DBMS,，,都支持,SQL,查询语言。,SQL,的体系结构,用户,1,用户,2,用户,3,基本表,B1,基本表,B2,基本表,B3,存储文件,S1,存储文件,S2,存储文件,S3,视图,V1,视图,V2,一个,SQL,数据库是表的汇集,它用一个或若干个,SQL,模式定义。,基表是实际存储的数据库中的表，而视图是若干个基表或其他视图构成的集合的子集。,一般情况下，一个基表与存储文件相对应，一个存储文件与外部存储器上一个物理文件相对应。,11.2 SQL,的数据定义功能,数据定义,(DDL),语句可以用于创建用户和重建数据库对象。下面是,DDL,命令：,1,）,CREATE TABLE,；,2,）,ALTER TABLE,；,3,）,DROP TABLE,；,4,）,CREATE INDEX,；,5,）,DROP INDEX,。,1.,基表的创建、删除和修改,1,）定义基本表,定义一个基表相当于建立一个新的关系模式，也就是定义一个关系的基本框架，此时基表中还没有数据。,定义基表就是对基表的名称，以及基表中的各个字段以及数据类型作出具体规定。,语句格式：,CREATE TABLE,(,完整性约束条件,完整性约束条件,完整性约束条件,.,完整性约束条件,);,功能：,用于创建一个新的表。,SQL,语言的数据类型,INTEGER,全字长整数，范围从负,10,位整数到正,10,位整数。,SMALLINT,半字长整数，范围从,-99999,带,99999,DECIMAL(m,n,),十进制数，共,m,个数字位，小数点后有,n,位，另外可带,+,，,-,号。,CHAR(n,),固定长度的字符串，长度为,n,VARCHAR(n,),可变长度的字符串，长度最大为,n,FLOAT,双字长浮点数。,DATE,存储日期时间值，最多,8,个字节。,CREATE TABLE student,&,创建基本表,student,(,st_class,CHAR(8),&CHAR(8),为长度,8,的定长字符串,st_no,CHAR(10)NOT NULL UNIQUE,st_name,CHAR(8)NOT NULL,st_sex,CHAR(2),st_age,SMALLINT);,&SMALLINT,数据类型为半字长的二进制整数即短整型,例,11-1,建立学生表,student,，,它由班级,st_class,、,学号,st_no,、,姓名,st_name,、,性别,st_sex,、,年龄,st_age,5,个属性组成，其中学号属性不能为空，且其值必须惟一；姓名属性也不能为空。,说明：,SQL,语言支持空，即,NULL,的概念，空值是不知道或不能用的值，如果基表中某一列的定义不允许出现空值，则要加上,NOT NULL,。,UNIQUE,表示取值唯一。,CREATE TABLE subject,(,su_no,CHAR(4)NOT NULL,su_subject,CHAR(20)NOT NULL,su_credit,INTEGER,&INTEGER,为全字长的二,进制整数即长整型,su_peried,INTEGER,su_preno,CHAR(4);,例,11-2,要建立课程设置表,subject,。,它由课程号,su_no,、,课程名,su_subject,等,5,个属性组成，其中的课程号,su_no,与课程名,su_subjec,属性不能为空。,CREATE TABLE score,(,st_no,CHAR(10),su_no,CHAR(4),sc_score INTEGER,FOREIGN KEY(,st_no,)REFERENCES student);,&,从表,student,中引入外码,st_no,，,以确定本表与表,student,的关联与同步,例,11-3,要建立学生选课表,score,。,它由学号,st_no,、,课程号,su_no,、,成绩,sc_score,等,3,个属性组成。,ALTER TABLE,ADD ,完整性约束条件,DROP ,MODIFY ,;,功能,：修改表的属性。其中,ADD,子句用于,增加,新列和新的完整性约束条件；,DROP,子句用于,删除,指定的完整性约束条件；,MODIFY,子句用于,修改,原有的列定义。,）修改基本表,基本表的修改：在基本表建立并使用一段时间后，可能需要增加新的属性或删除属性。,语句格式为：,ALTER TABLE student ADD(,st_born,DATE NOT NULL);,例,11-5,将基本表,student,中的列,st_age,删除。,ALTER TABLE student DROP,st_age,;,例,11-4,在基本表,student,中加入列,st_born,(,出生日期），数据类型为,DATE,，,且不能为空值。,）删除基本表,语句格式为：,DROP TABLE;,功能：,从数据库中删除一个基本表及其全部内容。,例,11-6,将学生表,student,删除。,DROP TABLE student;&,应先备份学生表，然后再删除,.,索引的创建和删除,基表或视图中可能存放有大量的记录，此时查找满足条件的记录可能要花很长时间，为了提高数据的检索速度，可以根据实际应用情况为一个基表建立若干个索引。,索引属于物理存储概念，而不是逻辑概念。在,SQL,中抛弃了索引概念，直接使用主键概念。值得一提的是，有些关系,DBMS,同时包括索引机制和主键机制，这里我们推荐使用主键机制，因为它对系统资源占用较低且效率较高。,）索引的创建,语句格式为：,CREATE UNIQUE INDEX ON,（,ASC|DESE,ASC|DESC);,功能,:,在指定的基本表之上建立由“索引名”命名的索引。,这里，保留字,UNIQUE,表示基本表中的索引值不允许重复，若缺省则表示索引值在表中允许重复；,DESC,表示按索引关键字值降序排序，若缺省或,ASC,表示升序排列；若有选择项列名,2,时，则先按列名,1,值索引，对相同列名,1,值的记录再按列名,2,值索引。,例,11-7,对基本表,student,中的,st_no,和,st_age,建立索引,stindex,，,分别为升序与降序，且索引值不允许重复。,CREATE UNIQUE INDEX,stindex,ON,Student(,st_no,ASC,st_age,DESC);,&,对,student,表中的字段,st_no,和,st_age,建立索引,2,）索引的删除,语句格式为：,DROP INDEX;,功能：,删除由“索引名”指定的索引。,例,11-8,删除上例中建立的索引,stindex,。,DROP INDEX,stindex,;,视图的定义和取消,数据库系统中一般都有若干个基表。在基表中保存着多个用户共享的数据。某一个具体应用可能只使用其中一部分数据，基表的字段有时也不能直接满足用户的具体要求。我们可以从一个或多个基表以及现有的视图导出适合具体应用的视图。,基表和视图都是关系，但视图是虚表，不对应于一个存储的数据文件。,建立和使用视图，可以简化查询命令。,将视图授权给用户，可以避免暴露全部的基表。,建立,08102011,班的学生视图，视图名为,c_student,.,CREATE VIEW,c_student,AS,SELECT,st_class,st_no,st_name,st_sex,st_age,FROM student,WHERE,st_class,=“08102011”,11.3 SQL,的数据操纵功能,1.,查询语句,SQL,是一种查询功能很强的语言，,SQL,语言中最主要、最核心的部分是它的查询功能。查询语句只有一个，,SELECT,，,它可与其他语句配合完成所有的查询功能。,通常，一个,SQL,命令可以分解成三个部分：,查找什么数据？从哪里查找？查找条件是什么？,格式如下：,SELECT,&,相邻列名或表达式之间用逗号分隔,FROM,&,当多于一个表或视图时，相邻表或视图之间用逗号分隔,WHERE,GROUP BY,HAVING,ORDER BY ASC|DESC;,功能：,从,FROM,子句中列出的表中，选择满足,WHERE,子句中给出的条件表达式的元组（记录），然后按,GROUP BY,子句中指定的列（字段）值分组。,再提取满足,HAVING,子句中分组条件表达式的那些组，按,SELECT,子句给出的列名或列表达式值输出。,ORDER,子句（排序子句）是对输出的目标表进行重新排序，并可附加说明,ASC(,升序,),或,DESC(,降序,),排列。,其中，,SELECT,子句中用逗号分开的表达式为查询目标。,FROM,子句指出查询目标及下面,WHERE,子句的条件中涉及的所有关系的关系名。,WHERE,子句指出查询目标必须满足的条件。,WHERE,中运算对象还可以是另一个,SELECT,语句，即,SELECT,语句可以嵌套。,在条件表达式中，有以下运算符：,算术运算符：“,”,，“,”,，“,=”,，“,=”,，“,”,。,逻辑运算符：“,AND”,，“,OR”,，“,NOT”,。,集合运算符：,UNION,（并），,INTERSECT,（交），,EXCEPT,（差）。,集合成员资格运算符：,IN,，,NOT IN,，,谓词：,EXISTS,（存在量词），,ALL,，,SOME,，,UNIQUE,。,聚合函数：,AVG,（平均值），,MIN,（最小值），,MAX,（最大值），,SUM,（和），,COUNT,（计数）。,1,）无条件查询,例,11-9,查询所有学生的选课情况。,SELECT,st_no,su_no,FROM score;,例,11-10,查询所有学生的情况。,SELECT*FROM student;,“*”,为通配符，表示查找,FROM,中所指出关系的所有属性值。,2,）条件查询,条件查询即带有,WHERE,子句的查询，所要查询的对象必须满足,WHERE,子句给出的条件。,SELECT,student.st_class,student.st_no,student.st_name,，,student.st_sex,student.st_age,score.su_no,score.score,FROM student,score,WHERE,score.st_no,=,student.st_no,AND,score.score=70;,这里若在,SELECT,后使用,DISTINCT,则会去掉重复行。另外逻辑运算符的优先从高到低顺序为,NOTANDOR,。,例,11-12,查询课程号为,c03,的考试成绩不及格的学生。,SELECT,st_no,FROM score WHERE,su_no,=c03 AND score60;,）排序查询,排序查询是指将查询结果按指定属性的升