六关系数据库标准语言.pptx

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,#,第,1,节,SQL,概述,第,2,节,数据定义,第,3,节,数据修改,第,4,节,数据查询,本章内容,第,1,节,SQL,概述,一、,SQL,语言简介,SQL,（,Structured Query Language,）结构化查询语言，其主要功能是同各种数据库建立联系，进行沟通。目前，绝大多数流行的关系型数据库管理系统，如,Oracle,，,Sybase,，,Microsoft SQL Server,，,Access,等都采用了,SQL,语言标准。,二、,SQL,语言的特点：,（,1,）是一种一体化的语言，包括数据定义、数据查询、数据操纵和数据控制功能。,（,2,）是一种高度非过程化的语言；,SQL,语言非常简洁,。,（,3,）,SQL,既是自含式语言，又是嵌入式语言，可以用同一种语法结构提供两种使用方式,。,（,4,）,SQL,语言采用面向集合的操作方式；核心是查询。,第,1,节,SQL,概述,SQL,语言提供的功能：,（,1,）数据定义,（,2,）数据操作,（,3,）数据检索（数据查询）,（,4,）存储控制,（,5,）数据完整性检验,本章介绍前三种功能,一、表的定义,第,2,节 数据定义,例如：用,SQL,命令建立销售记录数据库。,1.,创建数据库,命令,：,CREATE DATABASE,销售记录,2.,创建商品信息表,命令,：,CREATE TABLE,商品信息,(,商品号,C(3)PRIMARY KEY,商品名,C(15),;,单价,N(8,2)NULL,数量,I,厂名,C(20),产地,C(8)DEFAULT,中国北京,),说明,：上述命令创建了“商品信息”表，“商品号”字段为字符型，宽度为,3,，,PRIMARY KEY,指定“商品号”,为主关键字；“单价”字段为数值型，宽度为,8,位，小数位数,2,位，并且允许其值为空值；“数量”字段为整型,I,；“产,地”字段用,DEFAULT,设置了默认值。,3.,创建购买信息表，并建立和商品信息表的联系,命令,：,CREATE TABLE,购买信息,(,会员号,C(2),商品号,C(3),单价,N(8,2),;,数量,I CHECK(,数量,0)ERROR,数量不能为,0!,日期,D,;,FOREIGN KEY,商品号,TAG,商品号,REFERENCES,商品信息,),说明,：上述命令创建了“购买信息”表，用,CHECK,为“数量”字段设置了有效性规则，用,ERROR,为规则设置,提示信息；用“,FOREIGN KEY,商品号,TAG,商品号”在该表的“商品号”字段上建立了一个普通索引，同时说明该,字段是联接字段，通过引用商品信息表的主索引“商品号”与商品信息表建立了联系。,第,2,节 数据定义,P128,三、上机操作题,1.,建立数据库,STUDENT.DBC,CREATE DATABASE STUDENT,2.,建立学生（学号,C4,，姓名,C8,，出生日期,D,）学号主索引,CREATE TABLE,学生,(,学号,C(4)PRIMARY KEY,姓名,C(8),出生日期,D),3.,建立课程（课程号,C6,，课程名,C16,）课程号为主索引,CREATE TABLE,课程,(,课程号,C(6)PRIMARY KEY,课程名,C(16),4.,建立成绩（学号,C4,，课程号,C6,，成绩,I,）学号、课程号的候选索引,CREATE TABLE,成绩,(,学号,C(4)UNIQUE,课程号,C(6),UNIQUE,成绩,I,;,Foreign key,学号,tag,学号,references,学生,),第,2,节 数据定义,二、表的删除,在第,4,章中介绍用,DELETE FILE,来删除表及其他类型文件，用,REMOVE,TABLE,命令来移去数据库表。在,SQL,中删除表的命令为：,DROP TABLE,表名,这种删除是直接删除，所以读者在操作时要慎重。且如果要删除的表属于某个数据,库，应先打开该数据库再进行删除，以避免数据库出现不必要的错误提示。,例如：删除销售记录数据库中的商品信息表，命令序列为：,OPEN DATABASE,销售记录,DROP TABLE,商品信息,1,增加字段或修改已有字段类型或宽度的命令,格式：,ALTER TABLE,表名,ADD|ALTER,字段名,其中,ADD,用于增加新的字段，,ALTER,用于修改现有字段。,例如：为购买信息表增加一个折扣字段，折扣范围在（,0,，,1,之间，命令为：,ALTER TABLE,购买信息,ADD,折扣,N(4,2);,CHECK(,折扣,0 AND,折扣,=0.3 AND,折扣,0,注意,：我们在第,3,章中学习过的,Replace With,命令与,UNPDATE,语句的功能是一样的，,都用于更新表中数据，所以初学者很容易混淆，读者要熟记各自的语法以免出错。,第,4,节 数据查询,一、,SELECT,语句的语法,SELECT,命令的常用格式如下：,SELECT-FROM-WHERE-GROUP BY-ORDER BY-INTO,SELECT,子句：,说明要查询的数据，对应于,【,查询设计器,】,中的,【,字段,】,选项卡。,FROM,子句：,说明要查询的数据来自哪个表或哪些表，对应于,【,查询设计器,】,中,【,添加表或视图,】,操作。,WHERE,子句：,说明查询条件，对应于,【,查询设计器,】,中的,【,筛选,】,选项卡。,GROUP BY,子句：,用于对查询结果进行分组，对应于,【,查询设计器,】,中的,【,分组,】,选项卡。,第,4,节 数据查询,查询结果输出选项,：来指定查询结果的输出去向，对应于,【,查询设计器,】,中,【,查询去向,】,对话框。,查询结果显示选项：,ALL|DISTINCT TOP nPERCENT,关键词用来控制查询结果的显示情况，对应于,【,查询设计器,】,中的,【,杂项,】,选项卡。,一、,SELECT,语句的语法（续）,第,4,节 数据查询,二、简单查询,【,例,6-1】,检索公寓表中的所有记录。,命令：,select*from,公寓,说明：,通配符“*”来设定返回表中的所有列,【,例,6-2】,从学生表中检索所有系。,命令,1,：,select,系,from,学生,命令,2,：,select DISTINCT,系,AS,系部名称,from,学生,说明：,DISTINCT,关键词的作用是去掉重复值，,AS,指定了输出字段的标题。,【,例,6-3】,检索学费不低于,4000,元的专业简称及其收费情况。,命令,：,select,专业简称,学费,from,专业,where,学费,=4000,第,4,节 数据查询,二、简单查询,【,例,6-4】,检索出国贸系和信息系贫困学生的学号、姓名和所在班级。,命令,：,select,学号,姓名,班级,from,学生,;,where,家庭情况,=“,贫困”,AND(,系,=“,国贸系,OR,系,=,信息系,),说明：这个,SQL,语句较长，分为两行来写，“,;”,为续行符；这个查询的条件很简单，但初学者很容易出错，往往把查询要求中的“和”理解成逻辑运算符中的“,AND”,，最后的查询条件写为“,where,系,=”,信息系“,AND,系,=”,国贸系,”,，从而查不到符合条件的记录。,三、简单的联接查询,【,例,6-5】,检索欠费学生的基本情况和所欠金额。,分析：,查询需要输出的信息：学生情况和欠费金额，分别来源于学生表和交费表，分析得知两表之间存在一对一的联系，可通过公共字段“学号”建立。这样的查询一般用联接查询来实现。,命令,：,select,学生,.*,欠交金额,from,学生,交费,;,where,学生,.,学号,=,交费,.,学号,AND,欠交金额,0,说明,：“学生,.*”,代表学生表中所有的字段；当,from,之后有多个表时，含有公共字段的表达式，必须用表名前缀直接指明公共字段所属的表，如“学生,.,学号,=,交费,.,学号”，而非公共字段则可省略表前缀，如“欠交金额”。,第,4,节 数据查询,三、简单的联接查询,【,例,6-6】,检索每个学生所在专业与应交的学费，所住公寓与住宿费的情况。,命令,：,select,学号,姓名,专业,.,专业简称,学费,公寓,.,公寓类别,住宿费,;,from,学生,专业,公寓,;,where,学生,.,专业,=,专业,.,专业简称,AND,学生,.,公寓类别,=,公寓,.,公寓类别,第,4,节 数据查询,所谓嵌套查询就是，查询所输出的信息来自一个表，而查询的条件却涉及到一个或多个表。,四、嵌套查询,【,例,6-7】,检索至少有一个学生欠费的系。,命令：,select DISTINCT,系,from,学生,where,学号,IN;,(select,学号,from,交费,where,欠交金额,0),说明,：这个检索命令由两个,SELECT,子句构成，即内层查询（子查询）和外层查询，子查询查到的结果是欠费学生的学号，外层查询在利用这个学号在学生表中查找欠费学生所在的系，因为查询的结果有重复，而我们只对欠费的系感兴趣，所以，用,DISTINCT,关键词去掉了重复值。,注意：,子查询用一对圆括号括起来，且子查询不能嵌套。,第,2,节 数据查询,第,2,节 数据查询,在查询中，还可以加入运算符，如算术运算符和函数运算符，对原始表中的数据进行计算。常用的统计函数有：计数函数,COUNT,（）、求和函数,SUM,（）、求平均值函数,AVG,（）、求最大值函数,MAX,（）、求最小值函数,MIN,（）。,五、简单的计算查询,【,例,6-9】,输出每个学生的学号，姓名和年龄。,分析：,表中并没有年龄字段，需要根据出生日期计算每个人的年龄。,命令：,select,学号,姓名,YEAR(DATE()-YEAR(,出生日期,)AS,年龄,from,学生,【,例,6-10】,算出生源所在地的数目。,命令,：,select COUNT(DISTINCT,籍贯,)from,学生,第,4,节 数据查询,五、简单的计算查询,COUNT,（）函数的使用要点：,如要对表中记录个数进行计数，一般用,COUNT,（*），而对其他字段的统计则用,COUNT,（,DISTINCT,列名）。,【,例,6-11】,计算所有学生的人数。,命令,1,：,select COUNT(*)from,学生,命令,2,：,select COUNT(*)AS,总人数,from,学生,说明,：第二条命令修改了查询结果的列名，这样方便于对查询结果的浏览。,第,4,节 数据查询,【,例,6-12】,计算学生所欠的总金额数。,命令,：,select sum(,欠交金额,)from,交费,SUM,（）,函数,【,例,6-13】,计算信息系和经贸系学生所欠的金额数。,命令,：,select sum(,欠交金额,)from,交费,where,学号,IN;,(select,学号,from,学生,where,系,=,信息系,OR,系,=,经贸系,),【,例,6-14】,计算人均欠费金额。,命令,：,select AVG(,欠交金额,)from,交费,AVG,（）,函数,MAX,（）,函数,第,4,节 数据查询,【,例,6-15】,查找信息系欠费金额最高的学生学号、姓名及所欠金额。,命令,：,select,学生,.,学号,姓名,欠交金额,from,学生,交费,;,where,学生,.,学号,=,交费,.,学号,AND,系,=,信息系,AND,欠交金额,=;,(select MAX(,欠交金额,)from,学生,交费,where,学生,.,学号,=,交费,.,学号,AND,系,=,信息系,),【,例,6-16】,查找学费最低的专业。,命令,：,select,专业简称,学费,from,专业,where,学费,=(select MIN(,学费,)from,专业,),MIN,（）,函数,第,4,节 数据查询,六、分组与计算查询,有时需要对查询结果进行分类统计，这就要用到,GROUP BY,子句。,GROUP BY,子句可对数据进行分组，若要限定分组的条件，则需启用,HAVING,子句。,【,例,6-17】,统计各系欠费学生的人数，并按欠费人数降序排序。,命令,：,select,系,AS,系部名称,COUNT(*)AS,欠费人数,from,学生,group by,系,;,where,学号,IN(select,学号,from,交费,where,欠交金额,0);,ORDER BY,欠费人数,DESC,第,4,节 数据查询,六、分组与计算查询,【,例,6-18】,列出欠费学生人数超过,2,人的系部。,命令,：,select,系,AS,系部名称,COUNT(*)AS,人数,from,学生,;,group by,系,having(COUNT(*)2);,where,学号,IN;,(select,学号,from,交费,where,欠交金额,0),说明,：,HAVING,子句总是跟在,GROUP BY,子句之后，不可以单独使用，也不可以在其后使用子查询,第,4,节 数据查询,为了方便浏览，常常要把查询结果按一定的标准排序，在,SQL,语言中，可以用,ORDER BY,子句按一列或多列对查询结果进行升序（,ASC,）或降序（,DESC,）排列。,七、排序,【,例,6-19】,按学费从低到高的顺序列出全部专业信息。,命令,：,select*from,专业,order by,学费,说明,：如省略,ASC|DESC,关键字，查询结果默认按升序（,ASC,）进行排列,第,4,节 数据查询,【,例,6-20】,输出学生的学号，姓名，性别，籍贯，班级信息，查询结果按籍贯排 序，籍贯相同的再按性别排序。,命令,：,select,学号,姓名,性别,班级,籍贯,from,学生,order by 5,3,说明,：排序关键字可以是一个字段或字段表达式，也可以是一个数值表达式，这个数值是表或查询结果中列的位置，其中最左边的列的编号为,1,。,【,例,6-21】,按系列出欠费学生的学号，姓名，系，班级，欠交金额，同一个系的按欠交金额降序排列。,命令：,select,学生,.,学号,姓名,系,班级,欠交金额,from,学生,交费,where,学生,.,学号,=,交费,.,学号,AND,学生,.,学号,IN;,(select,学号,from,交费,where,欠交金额,0);,order by,系,欠交金额,DESC,第,4,节 数据查询,八、查询结果的显示和输出,1.,显示部分查询结果（,TOP,n,Percent,）,使用,TOP n PERCENT,短语限制返回的记录行数，,TOP n,说明返回,n,行，而,TOP n PERCENT,时，说明,n,是一个百分数，指定返回的行数等于总行数的百分之几。,【,例,6-22】,显示学费最高的前,5,个专业的信息。,命令,1,：,select*TOP 5 from,专业,order by,学费,DESC,专业简称,说明,：如果排序关键字存在重复值，则,TOP,关键字将失效，记录并没有按指定的数目显示。为了避免这种情况出现，可以在其后加入一个没有重复值的主键字段。,第,4,节 数据查询,八、查询结果的显示和输出,【,例,6-23】,列出,84,年以后出生的学生信息，只显示其中,50%,的记录即可。,命令,：,select*TOP 50 percent from,学生,;,where,出生日期,1983-12-31order by,出生日期,2.,将查询结果存放到永久表文件（,DBF|TABLE,）中,在,SELECT,语句中使用短语,INTO DBF|TABLE,，可将查询结果存放到永久表。例如：将,【,例,6-21】,的查询结果按降序排列存放到“各系学生欠费金额一览表”中，命令为：,select,学生,.,学号,姓名,系,班级,欠交金额,from,学生,交费,;,where,学生,.,学号,=,交费,.,学号,AND,学生,.,学号,IN;,(select,学号,from,交费,where,欠交金额,0);,order by,系,欠交金额,DESC;,into table,各系学生欠费金额一览表,第,4,节 数据查询,3.,将查询结果存放到临时表文件（,CURSOR,）中,使用短语,INTO CURSOR,，可以将查询结果存放到临时表文件中。当查询结束后，该临时表是当前文件，但仅是只读的,dbf,文件，关闭文件时该临时表将自动删除。临时表常在程序设计中作为表格、列表框或组合框的数据源。,例如：将查询到的专业信息存放到临时表文件,ZYtemp,中，命令为：,select*from,专业,into cursor ZYtemp,4.,将查询结果存放到数组（,ARRAY,）中,Visual FoxPro,程序设计中经常将表中的数据和数组数据进行交换。我们可以用,INTO ARRAY,将查询结果存放到一个二维数组中，数组的每一行对应一条记录，每一列对应查询结果中的一列。,第,4,节 数据查询,5.,将查询结果存放到文本文件（,FILE,）中,可以用,TO FILE,短语将查询结果存放到文本文件中（同时显示在屏幕上），默认是,.TXT,文件。,例如：将公寓表中的信息存放到文本文件,gy,中，命令如下：,select*from,公寓,to file gy,例如：将公寓表中的记录存放到数组,GYarray,中，命令如下：,select*from,公寓,into array GYarray,第,4,节 数据查询,6.,将查询结果输出到屏幕（,SCREEN,）,例如：将公寓表中的信息显示在屏幕上，命令如下：,select*from,公寓,to screen,7.,将查询结果输出到打印机（,PRINTER,）,使用短语,TO PRINTER PROMPT,可以将查询结果输出到打印机，如果使用了,PROMPT,，在开始之前会打开“打印设置”对话框。,查询去向一览表,去向,INTO,短语,去向,TO,短语,永久表,TABLE|DBF,文本文件,TO FILE,临时表,INTO CURSOR,屏幕,TO SCREEN,数组,INTO ARRAY,打印机,TO PRINTER,默认：显示在浏览窗口中,如果,TO,短语和,INTO,短语同时使用，则忽略,TO,短语,第,4,节 数据查询,十、集合并运算,有时需要把两个或两个以上的查询结果合并到一个结果中，这就要用到,UNION,操作，也即集合并运算。进行并运算，要求两个查询结果具有相同的字段个，并且对应字段应具有相同的数据类型和取值范围。,第,4,节 数据查询,十、集合并运算,【,例,6-26】,把,score,表和,new_score,表的数据合并存放到临时表,temp,中。,命令,：,select*from score;,UNION;,select*from new_score;,into cursor hebing,第,4,节 数据查询,十一、特殊运算符的使用,SQL,支持的运算符如下：,逻辑运算符,(,用于多条件的逻辑连接,),：,NOT,（非）、,AND,（与）、,OR,（或）,比较运算符,(,大小比较,),：,、,=,、,=,、,、,、,!=19 AND,年龄,1980-12-31,2,小题,select,学号,姓名,出生年月,from,学生,where,学号,in;,(select,学号,from,成绩,where,成绩,85)to screen,4,小题,select,课程号,avg(,成绩,)as,平均分,from,成绩,group by,课程号,5,小题,6,小题,select,学号,课程号,成绩,from,成绩,where,成绩,(select avg(,成绩,)from,成绩,),3.,1,小题,insert into,学生,values(“0406”,”,李浩”，,1980-12-31),2,小题,update,成绩,set,成绩,=,成绩*,1.1 where,成绩,between 55 and 59,3,小题,Delete from,成绩,where,成绩,=90,的选课记录的学号和课程编号。,SELECT,学号，课程编号,FROM,成绩,WHERE,成绩,=90,3,查询年龄大于,20,岁的学生信息。,SELECT*FROM,学生,WHERE YEAR(DATE()-YEAR(,出生日期,)20,4,查询会计系的学生人数,。,SELECT COUNT(*)FROM,学生；,WHERE,院系,=,会计,5,查询每个学生所选课程的平均成绩，要求得到的信息包括学号，平均成绩。,SELECT,学号,AVG(,成绩,),平均成绩,FROM,成绩；,GROUP BY,学号,6,用,SQL,语言创建学生表，要说明主码为学号。,CREATE TABLE,学生,(,学号,C(8)PRIMARY KEY,；姓名,C(12)NOT NULL,性别,C(2),出生日期,D,；,院系,C(8),7,查询所有学生的姓名、性别和年龄。,SELECT,姓名，,性别,，,YEAR(DATE()-YEAR(,出生日期,)AS,年龄,FROM,学生,8,查询学生表中的学生来自哪些系，去掉重复的系名。,SELECT DISTINCT(,院系,)FROM,学生,9,查询成绩低于,60,分的学生的学号、课程编号和成绩。,SELECT,学号,课程编号,成绩,FROM,成绩,WHERE,成绩,80,16,统计只有,2,名以下（含,2,名）学生选修的课程情况，包括课程名称、开课院系和选修人数，并按选课人数统计结果中的信息排序。,SELECT,课程名称,开课院系,COUNT(,学号,)AS,选修人数,;,FROM,成绩,课程,WHERE,课程,.,课程编号,=,成绩,.,课程编号,;,GROUP BY,课程名称,HAVING COUNT(,学号,)0 ERROR,成绩不能为负,23,将所有学生的,计算机基础,成绩加上,10,分,UPDATE,成绩,SET,成绩,=,成绩,+10 WHERE,课程编号,=,；,(SELECT,课程编号,FROM,课程,WHERE,课程名称,=,计算机基础,),24,删除成绩在,40,分以下的成绩记录。,DELETE FROM,成绩,WHERE,成绩,40,25,利用,SQL,派生一个包含学号、姓名、课程编号和成绩的视图。,CREATE VIEW v_view AS;,SELECT,学生,.,学号，姓名，课程编号，成绩,FROM,学生，成绩,;,WHERE,成绩,.,学号,=,学生,.,学号,26,为“课程”表增加一个“先修课”新字段,类型为,C(5),。,ALTER TABLE,课程,ADD,先修课,C(5),27,删除“成绩”表的“成绩”的默认值。,ALTER TABLE,成绩,ALTER,成绩,DROP DEFAULT,28,用,SQL,命令创建,”,课程,”,表。,CREATE TABLE,课程,(,课程编号,C(4),;,课程名称,C(10),开课院系,C(8),29,用,SQL,命令创建,”,成绩,”,表。,CREATE TABLE,成绩,(,学号,C(8),课程代号,C(4),;,成绩,I),30,将学生表删除。,DROP TABLE,学生,31,将课程表删除。,DROP TABLE,课程,32,将成绩表删除。,DROP TABLE,成绩,习题,第,1,18,题使用教师管理数据库中的,3,个数据表：,教师：（教师号,C,（,4,）、姓名,C,（,8,）、性别,C,（,2,）、职称,C,（,6,）、工资,N,（,4,）、所在系,C,（,8,）,授课：（教师号,C,（,5,）、课程号,C,（,4,）,课程：（课程号,C,（,4,）、课程名,C,（,10,）、学时,N,（,2,）,1,查询职称为“教授”的教师信息。,2,查询每类职称教师的平均工资。,3,查询学时大于,80,的课程信息。,4,查询每位教师的姓名及所授课程的课程名。,5,查询所授课程门数为,2,的教师号,6,查询所有姓李的教师信息。,7,查询所有课程的信息，结果按学时降序排列。,8,查询每个系的教师人数。,9,统计职称为教授的人数。,10,查询“陈文”教师所讲授的课程名称。,11,用子查询的方式查询与“陈文”教师职称相同的教师姓名、性别及职称。,12,统计教师号为,T1101,的教师授课的门数。,13,将教师表中工资小于或等于,2000,元的讲师的工资提高,20%,。,14,插入一条授课记录（,T1102,C003,）,15,将学时数小于,10,的课程信息彻底删除。,16,将所有教授的工资加上,500,。,17,将所有课程的学时数减去,5,。,18,查询没有授课的教师信息。,