第4章 数据库标准语言SQL

,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,单击此处编辑母版标题样式,第,4,章 数据库标准语言,SQL,本章概要,SQL,是结构化查询语言（,Structured Query Language,）,的缩写，其功能包括数据查询、数据操纵、数据定义和数据控制四个部分。,SQL,语言简洁、方便实用、功能齐全，已成为目前应用最广的关系数据库语言。,4.1 SQL,概述,SQL,语言发展主要经历了以下几个阶段：,1974,年由,CHAMBERLIN,和,BOYEE,提出，当时称为,SEQUEL(STUCTURED ENGLISH QUERY LANGUAGE),；,IBM,公司对其进行了修改，并用于其,SYSTEM R,关系数据库系统中；,1981,年,IBM,推出其商用关系关系数据库,SQL/DS,，,并将其名字改为,SQL,，,由于,SQL,语言功能强大，简洁易用，因此得到了广泛的使用；,今天广泛应用于各种大型数据库，如,SYBASE,、,INFORMIX,、,ORACLE,、,DB2,、,INGRES,等，也用于各种小型数据库，如,FOXPRO,、,ACCESS,。,SQL,概述及特点,字面看,SQL,只是一个查询语言，而实际上,SQL,作为一种标准数据库语言，从对数据库的随机查询到数据库的管理和程序设计，,SQL,几乎无所不能，功能十分丰富。,SQL,语言是一种关系数据库语言，提供数据的定义、查询、更新和控制等功能。,SQL,语言不是一个应用程序开发语言，只提供对数据库的操作能力，不能完成屏幕控制、菜单管理、报表生成等功能，可成为应用开发语言的一部分。,SQL,语言不是一个,DBMS,，,它属于,DBMS,语言处理程序。,大部分,DBMS,产品都支持,SQL,，,成为操作数据库的标准语言。,4.1.2 SQL,的主要功能,数据定义,数据操纵,数据控制,1,2,3,4.1.3 SQL,语言的主要特点,综合统一：集,DDL,、,DML,、,DCL,功能于一体，可独立完成数据库生命周期中的全部活动，语言风格统一。,语言简捷，易学易用。,高度非过程化：用户只需提出“做什,么”，而无需指明“怎么做,”。,面向集合的操作方式：操作对象、查询结果、更新数据均可以是元组的集合。,以同一种语法格式提供两种使用方式：自含式、嵌入式。,SQL,支持关系数据库的三级模式结构。,4.1.3 SQL,语言的主要特点,4.1.3 SQL,语言的主要特点,三级模式结构,存储文件：组成关系数据库的内模式，对用户透明。,基表,(Base Table),：,组成关系数据库的模式，一个关系对应一个基表，一或多个基表对应一个存储文件。,视图,(View),：,组成关系数据库的外模式，从一个或多个基表中导出，不独立存储在数据库中。,SQL,视图,1,视图,2,基本表,1,基本表,2,基本表,3,基本表,4,存储文件,1,存储文件,2,外模式,模式,内模式,4.1.4 SQL,语法,约定符号,意义, ,（方括号）, ,中的内容为任选项，根据具体情况决定是否选用。,|,（竖线）,必选项，表示两边的部分只能选用其中的一个。,（尖括号）,中的内容为实际语义，表示其中内容要以实际名称或参数输入，为必选内容。, ,（大括号）,必选项，表示必须至少选其中的一个选项。,（）（小括号）,是语句的组成部分，一定要输入,（省略号）,表示可以任意多次地重复前面的单元,.n,指示前面的项可以重复,n,次，具体值由逗号分隔,4.2 SQL,数据定义,SQL,语言使用,数据定义语言,（,DATA DEFINITION LANGUAGE,，,简称,DDL,）,实现其数据定义功能。,操作,对象,操作对象,创建,删除,修改,表,Create table,Drop table,Alter table,视图,Create view,Drop view,索引,Create index,Drop index,数据库,Create database,Drop database,Alter database,4.2.1,数据库,定义数据库包括定义数据库名、确定数据库文件及其大小、确认日志文件的位置和大小。定义数据库使用,CREATE DATABASE,语句，其语法格式为：,CREATE DATABASE ,ON PRIMARY ( NAME = ,FILENAME =, SIZE = , MAXSIZE = , FILEGROWTH = ) ,n,LOG ON (NAME=,逻辑日志文件名,，,FILENAME= ,操作日志文件路径和文件名, SIZE=,文件长度, ) ,n,FOR RESTORE,1,、,创建数据表：数据表是关系数据库的基本组成单位，它物理地存储于数据库的存储文件中。,CREATE TABLE ,（, ,列级完整性约束条件, ,列级完整性约束条件, n, n,）,4.2.2,基本表,（,1,）表名：是所要定义的基本表的名字，同一个数据库中，不允许基本表重名，命名格式遵从标识符的命名规则，如,SC,，,Student,。,（,2,）列名：表示基本表的各个属性，同一个基本表中不允许出现重复的属性名，命名格式遵从标识符的命名规则，字母开头，可含字母、数字、,$,、,_,等。,4.2.2,基本表,例：建立表,Student,。,CREATE TABLE Student,(,Sno,CHAR(5),Sname,VARCHAR(20),Sex CHAR(1),Sdept,VARCHAR(15),Smon,CHAR(5),SbirthDay,SMALLDATETIME),执行该语句后，便产生了学生基本表的空表。,4.2.2,基本表,上列为创建基本表的最简单形式，还可以对表进一步定义，如主码,、,空值的设定，使数据库用户能够根据应用的需要对基本表的定义做出更为详尽的规定。,在,SQL SERVER,中，对于基本表的约束分为列约束和表约束。,4.2.2,基本表,列约束：是对某一个特定列的约束，包含在列定义中，直接跟在该列的其他定义之后，用空格分隔，不必指定列名；,表约束：与列定义相互独立，不包括在列定义中，通常用于对多个列一起进行约束，定义表约束时必须指出要约束的那些列的名称。完整性约束的基本语法格式为：, CONSTRAINT ,约束名：约束不指定名称时，系统会给定一个名称。,完整性约束定义,约束类型：在定义完整性约束时必须指定完整性约束的类型。在,SQL SERVER,中可以定义,5,种类型的完整性约束。,（,1,）,NULL/NOT NULL,：是否允许该字段的值为,NULL,。,NULL,值不是,0,也不是空白，更不是填入字符串“,NULL”,，,而是表示“不知道”、“不确定”或“没有数据”的意思。,当某一字段的值一定要输入才有意义的时候，则可以设置为,NOT NULL,。,如主码列就不允许出现空值，否则就失去了唯一标识一条记录的作用，只能用于定义列约束，,其语法格式如下：,CONSTRAINT NULL|NOT NULL,实例,例：建立,Student,表，对,Sno,字段进行,NOT NULL,约束。,CREATE TABLE Student,(,Sno,CHAR(5) NOT NULL,Sname,VARCHAR(20),Sex CHAR(1),Sdept,VARCHAR(15),Smon,CHAR(5),SBirthDay,SMALLDATETIME ),当,SNO,为空值时，系统给出错误信息，无,NOT NULL,约束时，系统缺省为,NULL,。,其中,S_CONS,为指定的约束名称，当约束名称省略时，系统自动产生一个名字。,完整性约束定义,（,2,）,UNIQUE,约束：,UNIQUE,约束用于指明基本表在某一列或多个列的组合上的取值必须唯一。,定义了,UNIQUE,约束的那些列称为唯一键，系统自动为唯一键建立唯一索引，从而保证了唯一键的唯一性。,唯一键允许为空，但系统为保证其唯一性，最多只可以出现一个,NULL,值。,UNIQUE,既可用于列约束，也可用于表约束。,UNIQUE,用于定义列约束时，其语法格式如下：,CONSTRAINT UNIQUE,举例,例：建立一个,Student,表，定义,Sname,为唯一码。,CREATE TABLE Student,(,Sno,CHAR(5) NOT NULL,Sname,VARCHAR(20)UNIQUE,Sex CHAR(1) ,Sdept,VARCHAR(15) ,Smon,CHAR(5) ,SBirthDay,SMALLDATETIME,),完整性约束定义,UNIQUE,用于定义表约束时，其语法格式如下：,CONSTRAINT UNIQUE,（,）,例：建立一个,Student,表，定义,Sname,+ Sex,为唯一码。,CREATE TABLE Student,(,Sno,CHAR(5) NOT NULL,Sname,VARCHAR(20),Sex CHAR(1),Sdept,VARCHAR(15),Smon,CHAR(5),SBirthDay,SMALLDATETIME,CONSTRAINT S_UNIQ,UNIQUE(Sname,，,Sex) ),完整性约束定义,（,3,）,PRIMARY KEY,约束：用于定义基本表的主码，起唯一标识作用，其值不能为,NULL,，,也不能重复，以此来保证实体的完整性。,PRIMARY KEY,与,UNIQUE,约束类似，通过建立唯一索引来保证基本表在主码列取值的唯一性，主要区别在于：,在一个基本表中只能定义一个,PRIMARY KEY,约束，但可定义多个,UNIQUE,约束；,对于指定为,PRIMARY KEY,的一个列或多个列的组合，其中任何一个列都不能出现空值，而对于,UNIQUE,所约束的唯一键，则允许为空。,注意：,不能为同一个列或一组列既定义,UNIQUE,约束，又定义,PRIMARY KEY,约束。,PRIMARY KEY,既可用于列约束，也可用于表约束。,PRIMARY KEY,用于定义列约束时，其语法格式如下：,CONSTRAINT PRIMARY KEY,完整性约束定义,举例,建立一个,Student,表，定义,Sno,为该表的主码。,CREATE TABLE Student,(,Sno,CHAR(5) PRIMARY KEY,Sname VARCHAR(20) UNIQUE,Sex CHAR(1),Sdept,VARCHAR(15),Smon,Char(5),SBirthDay,SMALLDATETIME ),PRIMARY KEY,用于定义,表约束,时，即将某些列的组合定义为主码，其语法格式如下：,CONSTRAINT PRIMARY KEY (),例：建立一个,SC,表，定义,SNO+CNO,为,SC,的主码,CREATE TABLE SC,( SNO CHAR(5) NOT NULL,CNO CHAR(5) NOT NULL,SCORE NUMERIC(3),CONSTRAINT SC_PRIM PRIMARY KEY(SNO,CNO) );,完整性约束定义,（,4,）,FOREIGN KEY,约束：指定某一个列或一组列作为外码。如果关系,R2,的一个或一组属性,X,不是,R2,的主码，而是另一关系,R1,的主码，则该属性或属性组,X,称为关系,R2,的,外码,或,外部关系键,。并称关系,R2,为,参照关系,，关系,R1,为,被参照关系,。,系统保证参照关系在外码上的取值要么是被参照关系中某一个主码值，要么取空值。以此保证两个表之间的连接，确保了实体的参照完整性。,FOREIGN KEY,既可用于列约束，也可用于表约束，其语法格式为：,完整性约束定义,CONSTRAINT FOREIGN KEY,（,）,REFERENCES (),举例,例：建立一个,SC,表，定义,Sno,，,Cno,为,SC,的外码。,CREATE TABLE SC,(,Sno,CHAR(5) FOREIGN KEY (SNO),REFERENCES Student (,Sno,),Cno,CHAR(5) FOREIGN KEY (CNO),REFERENCES Course (,Cno,),SCORE DECIMAL(4,1),CONSTRAINT S_C_PRIM PRIMARY KEY (,Sno,Cno,),),完整性约束定义,（,5,）,CHECK,约束：,CHECK,约束用来检查字段值所允许的范围，如，一个字段只能输入整数，而且限定在,0-100,的整数，以此来保证域的完整性。,CHECK,既可用于列约束，也可用于表约束，,其语法格式为：,CONSTRAINT CHECK (),举例,例：建立一个,SC,表，定义,SCORE,的取值范围为,0,100,之间,CREATE TABLE SC,(,Sno,CHAR(5),Cno,CHAR(5),SCORE DECIMAL(4,1),CHECK(SCORE=0 AND SCORE =100),),修改基本表,由于应用环境和应用需求的变化，经常需要修改基本表的结构，比如，增加新列和完整性约束、修改原有的列定义和完整性约束等。,SQL,语言使用,ALTER TABLE,命令来完成这一功能,其语法格式为：,ALTER TABLE,表名,ADD,新列名,数据类型,列完整性约束, ,DROP ,完整性约束名,MODIFY,列名,数据类型,注意：使用此方式增加的新列,自动填充,NULL,值,，所以不能为增加的新列指定,NOT NULL,约束。,例,:,在,SC,表中增加完整性约束定义，使,SCORE,在,0,100,之间。,ALTER TABLE SC,ADD CONSTRAINT SCORE_CHK,CHECK ( SCORE BETWEEN 0 AND 100 ),1. ADD,方式,修改基本表,2.DROP,方式：,使用,DROP,语句删除指定的完整性约束条件,例：,删除,SC,表中的,SCORE_CHK,约束。,ALTER TABLE SC DROP SCORE_CHK,修改基本表,修改基本表,3. MODIFY,方式,例：把,S,表中的,SNO,列加宽到,8,位字符宽度,ALTER TABLE S MODIFY COLUMN SNO CHAR(8),注意：使用此方式有如下一些限制：,不能改变列名；,不能将含有空值的列的定义修改为,NOT NULL,约束；,若列中已有数据，则不能减少该列的宽度，也不能改变其数据类型；,只能修改,NULL|NOT NULL,约束，其它类型的约束在修改之前必须先删除，然后再重新添加修改过的约束定义。,删除基本表,当某个基本表无用时，可将其删除。删除后，该表中的数据和在此表上所建的索引都被删除，而建立在该表上的视图不会随之删除，系统将继续保留其定义，但已无法使用。,如果重新恢复该表，这些视图可重新使用。,删除表的语法格式：,DROP TABLE ,例：删除表,STUDENT,。,DROP TABLE STUDENT,注意：只能删除自己建立的表，不能删除其他用户所建的表。,4.3,数据查询,数据查询是数据库中最常见的操作。,SQL,语言提供,SELECT,语句，通过查询操作可得到所需的信息。,SELECT,语句的一般格式为：,Select ,指定希望查看的列,From ,指定要查询的表,Where ,指定查询条件,Group By ,指定要分组的列,Having ,指定分组的条件,Order By ,指定如何排序,查询的结果是仍是一个，执行过程如下,:,根据,WHERE,子句的检索条件，从,FROM,子句指定的基本表或视图中选取满足条件的元组，再按照,SELECT,子句中指定的列，投影得到结果表。,如果有,GROUP,子句，则将查询结果按照,相同的值进行分组。,如果,GROUP,子句后有,HAVING,短语，则只输出满足,HAVING,条件的元组。,如果有,ORDER,子句，查询结果还要按照,的值进行排序。,Select ,指定希望查看的列,From ,指定要查询的表,Where ,指定查询条件,Group By ,指定要分组的列,Having ,指定分组的条件,Order By ,指定如何排序,4.3,数据查询,*：字段组的省略写法,.,表示表中全部字段,按关系模式中属性的顺序排列,.,ALL,：,全部操作符。说明在查询结果中保留重复值，如果查询中有统计字段，要求计算重复值，格式为：,ALL ,或,ALL,DISTINCT:,去掉重复操作,格式为：,DISTINCT ,或,DISTINCT ,Select ,指定希望查看的列,From ,指定要查询的表,例,查询学生的全部信息,。,例,查询选修了课程的学生号。,另外，利用投影查询可控制列名的顺序，并可通过指定,别名,改变查询结果的列标题的名字。,SELECT * FROM Student,SELECT DISTINCT SNO FROM SC,4.3,数据查询,例：,查询全体学生的姓名、学号和出生日期。,SELECT,Sname,姓名,Sno,学号,SBirthDay,AS,出生日期,FROM Student,上述查询均为不使用,WHERE,子句的无条件查询，也称作,投影查询。,条件查询,当要在表中找出满足某些条件的行时，则需使用,WHERE,子句指定查询条件。,WHERE,子句中，条件通常通过三部分来描述：,1,列名；,2,比较运算符；,3,列名、常数。,运算符,含义,=, , =, 85,多重条件查询,当,WHERE,子句需要指定一个以上的查询条件时，则需要使用逻辑运算符,AND,、,OR,和,NOT,将其连结成复合的逻辑表达式。,其优先级由高到低为：,NOT,、,AND,、,OR,，,用户可以使用括号改变优先级。,例：查询选修,C1,或,C2,且分数大于等于,85,分学生的的学号、课程号和成绩。,SELECT,Sno,Cno, Score FROM SC,WHERE,（,Cno,=C1 OR,Cno,=C2,）,AND Score=85,确定范围,例： 查询成绩在,90,至,100,之间的学生的学号以及课程号。,SELECT,Sno,Cno,FROM SC,WHERE Score BETWEEN 90 AND 100,等价于,SELECT,Sno,Cno,FROM student,WHERE,？,确定集合,利用“,IN”,操作可以查询属性值属于指定集合的元组。,例：查询选修,C1,或,C2,的学生的学号、课程号和成绩。,SELECT SNO, CNO, SCORE FROM SC,WHERE CNO IN(C1, C2),此语句也可以使用逻辑运算符“,OR”,实现。,SELECT SNO, CNO, SCORE,FROM SC,WHERE CNO=C1 OR CNO= C2,确定集合,利用“,NOT IN”,可以查询指定集合外的元组。,例：查询没有选修,C1,，,也没有选修,C2,的学生的学号、课程号和成绩。,SELECT SNO, CNO, SCORE FROM SC,WHERE CNO NOT IN(C1, C2),等价于：,SELECT SNO, CNO, SCORE FROM SC,WHERE CNO!=C1 AND CNO!= C2,部分匹配查询,上例均属于完全匹配查询，当不知道完全精确的值时，用户还可以使用,LIKE,或,NOT LIKE,进行部分匹配查询（也称模糊查询）。,LIKE,定义的一般格式为：, LIKE ,属性名必须为字符型，字符串常量的字符可以包含如下两个特殊符号：,%,：表示任意知长度的字符串；,_,：表示任意单个字符。,部分匹配查询,例：查询所有姓张的学生的学号和姓名。,SELECT SNO,SName,FROM student,WHERE,SName,LIKE ,张,%,例：查询姓名中第二个汉字是“力”的学生学号和姓名。,SELECT SNO,SName,FROM Student,WHERE,TName,LIKE _ _,力,%,注：一个汉字占两个字符。,思考,:,查询姓名的第一个字母为,R,并且倒数第二个字母为,S,的学生,空值查询,IS NULL,某个字段没有值称之为具有空值（,NULL,）。通常没有为一个列输入值时，该列的值就是空值。空值不同于零和空格，它不占任何存储空间。,例：某些学生选课后没有参加考试，有选课记录，但没有考试成绩，考试成绩为空值，这与参加考试，成绩为零分的不同。,insert,SC(Sno,Cno,) values(02,c2),例：查询没有考试成绩的学生的学号和相应的课程号。,SELECT SNO, CNO FROM SC WHERE SCORE IS NULL,注意：这里的空值条件为,IS NULL,，,不能写成,SCORE=NULL,。,常用聚集函数及,GROUP,查询,SQL,提供了许多聚集函数，增强了基本检索能力。,常用的聚集函数,函数名称,功能,AVG,按列计算平均值,SUM,按列计算值的总和,MAX,求一列中的最大值,MIN,求一列中的最小值,COUNT,按列值计个数,例：求学号为,S1,学生的总分和平均分。,SELECT SUM(SCORE) AS,TotalScore,AVG(SCORE) AS,AveScore,FROM SC WHERE (SNO = S1),注意：函数,SUM,和,AVG,只能对数值型字段进行计算。,单独使用聚集函数时（,Select,子句中的列名都是聚集函数形式），表示对所有记录进行聚集,例：求选修,C1,号课程的最高分、最低分及之间相差的分数,SELECT MAX(SCORE) AS,MaxScore, MIN(SCORE) AS,MinScore, MAX(SCORE) - MIN(SCORE) AS Diff,FROM SC WHERE (CNO = C1),例：求学生总数,SELECT COUNT(SNO) FROM Student,实例,举例,例： 求参加学修课程的学生总数,SELECT COUNT( distinct SNO) FROM SC,注意：加入关键字,DISTINCT,后表示消去重复行，,COUNT,函数对空值不计算，但对零进行计算。,例： 统计有成绩同学的人数,SELECT COUNT (SCORE) FROM SC,上例中成绩为零的同学计算在内，没有成绩（即为空值）的不计算。,举例,例：求学生总数,SELECT COUNT(*) FROM Student,COUNT,（*）,用来统计元组的个数,不消除重复行，不允许使用,DISTINCT,关键字。,分组查询,GROUP BY,子句：结果集按某一列或多列的值分组，值相等的为一组，一个分组以一个元组的形式出现,聚集函数一般与分组操作一起使用,查询男生和女生的平均年龄,Select sex,AVG(age,) as,Average_age,From Student,Group By sex,除聚集函数外的属性必须全部出现在,Group By,子句中,举例,例：查询每位学生的学号以及该学生选修课程的门数。,SELECT SNO,COUNT(*) AS C_NUM,FROM SC GROUP BY SNO,GROUP BY,子句按,SNO,的值分组，所有具有相同,SNO,的元组为一组，对每一组使用函数,COUNT,进行计算，统计出各位教师任课的门数。,HAVING,子句：针对聚合函数的结果值进行筛选，它作用于分组计算结果集，跟在,Group By,子句的后面，与,Group By,成对出现，没有,Group By,则针对全表。,举例,例：查询选修两门以上课程的学生学号和选课门数,SELECT SNO,COUNT(*) AS SC_NUM,FROM SC GROUP BY SNO,HAVING COUNT(*)=2,GROUP BY,子句按,SNO,的值分组，所有具有相同,SNO,的元组为一组，对每一组使用函数,COUNT,进行计算，统计出每位学生选课的门数。,HAVING,子句去掉不满足,COUNT,（*）,=2,的组。,说明,Having,子句中必须聚集函数的比较式，且聚集函数的比较式也只能通过,Having,子句给出,Having,中的聚集函数可与,Select,中的不同,例,:,查询至少选修,2,门课的学生的学号及平均成绩,Select,Sno, AVG(Score),From SC,Group By,SNo,Having COUNT(*) 2,说明,当在一个,SQL,查询中同时使用,WHERE,子句，,GROUP BY,子句和,HAVING,子句时，其顺序是,WHERE,GROUP BY,HAVING,WHERE,与,HAVING,子句的根本区别在于作用对象不同。,1)Where,决定哪些元组被选择参加运算，作用于关系中的元组,2)Having,决定哪些分组符合要求，作用于分组,3),聚合函数的条件关系必须用,Having,，,Where,中不应出现聚合函数,查询的排序,当需要对查询结果排序时，应该使用,ORDER BY,子句，,ORDER BY,子句必须出现在其他子句之后，排序方式可以指定，,DESC,为降序，,ASC,为升序，缺省时为升序,例： 查询选修,C1,的学生学号和成绩，并按成绩降序排列。,SELECT SNO, SCORE FROM SC,WHERE CNO=C1,ORDER BY SCORE DESC,举例,例： 求选课在三门以上且各门课程均及格的学生的学号及其总成绩，查询结果按总成绩降序列出。,SELECT SNO,SUM(SCORE) AS,TotalScore,FROM SC,WHERE SCORE=60,GROUP BY SNO HAVING COUNT(*)=3,ORDER BY SUM(SCORE) DESC,此语句为,分组排序,，执行过程如下：,1.,（,FROM,）,取出整个,SC,2.,（,WHERE,）,筛选,SCORE=60,的元组,3.,（,GROUP BY,）,将选出的元组按,SNO,分组,4.,（,HAVING,）,筛选选课三门以上的分组,5.,（,SELECT,）,以剩下的组中提取学号和总成绩,6.,（,ORDER BY,）,将选取结果排序,SELECT SNO,SUM(SCORE) AS,TotalScore,FROM SC,WHERE SCORE=60,GROUP BY SNO,HAVING COUNT(*)=3,ORDER BY SUM(SCORE) DESC,数据表连接及连接查询,数据表之间的联系是通过表的字段值来体现的，这种字段称为连接字段。,连接操作的目的就是通过加在连接字段的条件将多个表连接起来，以便从多个表中查询数据。,前面的查询都是针对一个表进行的，当查询同时涉及两个以上的表时，称为连接查询。,表的连接方法有两种：,方法,1,：,表之间满足一定的条件的行进行连接，此时,FROM,子句中指明进行连接的表名，,WHERE,子句指明连接的列名及其连接条件。,方法,2,：,利用关键字,JOIN,进行连接。,数据表连接及连接查询,具体分为以下几种：,INNER JOIN,：,显示符合条件的记录，此为默认值；,LEFT,（,OUTER,）,JOIN,：,显示符合条件的数据行以及左边表中不符合条件的数据行，此时右边数据行会以,NULL,来显示，此称为左连接；,RIGHT,（,OUTER,）,JOIN,：,显示符合条件的数据行以及右边表中不符合条件的数据行，此时左边数据行会以,NULL,来显示，此称为右连接；,FULL,（,OUTER,）,JOIN,：,显示符合条件的数据行以及左边表和右边表中不符合条件的数据行，此时缺乏数据的数据行会以,NULL,来显示；,CROSS JOIN,：,会将一个表的每一笔数据和另一表的每笔数据匹配成新的数据行。,等值连接与非等值连接,例：查询刘伟老师所讲授的课程。,方法,1,：,SELECT T.TNO ,TN,CNO,FROM T,TC,WHERE (T.TNO = TC. TNO) AND (TN=,刘伟,),这里，,TN=,刘伟为查询条件，而,T.TNO = TC.TNO,为连接条件，,TNO,为连接字段。连接条件的一般格式为：,. . ,其中,比较运算符主要有：、！。,当比较运算符为“时，称为等值连接，其他情况为非等值连接。,等值连接与非等值连接,引用列名,TNO,时要加上表名前缀，是因为两个表中的列名相同，必须用表名前缀来确切说明所指列属于哪个表，以避免二义性。如果列名是唯一的，比如,TN,，,就不必须加前缀。,上面的操作是将,T,表中的,TNO,和,TC,表中的,TNO,相等的行连接，同时选取,TN,为“刘伟“的行，然后再在,TN,，,CNO,列上投影，这是连接、选取和投影的操作组合。,举例,例：查询所有选课学生的学号、姓名、选课名称及成绩。,SELECT S.SNO,SN,CN,SCORE,FROM S,C,SC,WHERE S.SNO=SC.SNO AND SC.CNO=C.CNO,本例涉及三个表，,WHERE,子句中有两个连接条件。当有两个以上的表进行连接时，称为多表连接。,外连接,在上面的连接操作中，不满足连接条件的元组不能作为查询结果输出，如果有需求，可以进行外连接。,例：查询所有学生的学号、姓名、选课名称及成绩。（没有选课的同学的选课信息显示为空）,SELECT S.SNO,SN,CN,SCORE FROM S,LEFT OUTER JOIN SC ON S.SNO=SC.SNO,LEFT OUTER JOIN C ON C.CNO=SC.CNO,则查询结果只包括所有的学生，没有选课的吴丽同学的选课信息显示为空。,子查询,在,WHERE,子句中包含一个形如,SELECT-FROM-WHERE,的查询块，此查询块称为子查询或嵌套查询，包含子查询的语句称为父查询或外部查询。,嵌套查询可以将一系列简单查询构成复杂查询，增强查询能力。,子查询的嵌套层次最多可达到,255,层，以层层嵌套的方式构造查询充分体现了,SQL“,结构化”的特点。,嵌套查询在执行时由里向外处理，每个子查询是在上一级外部查询处理之前完成，父查询要用到子查询的结果。,返回一个值的子查询,当子查询的返回值只有一个时，可以使用比较运算符（,=, , =, ANY,大于子查询结果中任意值, ALL,大于子查询结果中所有值,= ALL,小于等于子查询结果中的任意值, ANY,小于子查询结果中任意值,= ANY,等于子查询结果中的任意值,= ANY,大于等于子查询结果中任意值, ANY,不等于子查询结果中的任意值,= ALL,大于等于子查询结果中所有值, ALL,不等于子查询结果中的所有值,先执行子查询，找到讲授课程号为,C5,的教师号，为一组值构成的集合,(T2,T3,T5),；,再执行父查询，其中,ANY,的含义为任意一个，查询教师号为,T2,、,T3,、,T5,的教师的姓名。,SELECT TN FROM T WHERE TNO=ANY,( SELECT TNO,FROM TC,WHERE CNO=C5),举例,上例也可以使用前面所讲的连接操作来实现：,SELECT TN FROM T,TC,WHERE T.TNO=TC.TNO AND TC.CNO=C5,可见，对于同一查询可使用子查询和连接两种方法来解决，可根据习惯任意选用。,举例,例：查询其他系中比计算机系某一教师工资高的教师的姓名和工资。,SELECT TN,SAL FROM T WHERE SALANY,(SELECT SAL FROM T,WHERE DEPT=,计算机,),AND DEPT!= ,计算机,先执行子查询，找到计算机系中所有教师的工资集合,(1500,900),；,再执行父查询，查询所有不是计算机系且工资高于,1500,或,900,的教师姓名和工资。,举例,此查询也可以写成：,SELECT TN,SAL FROM T WHERE SAL,(SELECT MIN(SAL ) FROM T,WHERE DEPT=,计算机,),AND DEPT!= ,计算机,先执行子查询，利用库函数,MIN,找到计算机系中所有教师的最低工资,900,；,再执行父查询，查询所有不是计算机系且工资高于,900,的教师。,使用,ALL,ALL,的含义为全部。,例：查询其他系中比计算机系所有教师工资都高的教师的姓名和工资。,SELECT TN,SAL FROM T WHERE SALALL,(SELECT SAL FROM T WHERE DEPT=,计算机,),AND DEPT!= ,计算机,子查询找到计算机系中所有教师的工资集合,(1500,900),；,父查询找到所有不是计算机系且工资高于,1500,的教师姓名和工资。,举例,此查询也可以写成：,SELECT TN,SAL FROM T WHERE SAL,(SELECT MAX(SAL ) FROM T,WHERE DEPT=,计算机,),AND DEPT!= ,计算机,库函数,MAX,的作用是找到计算机系中所有教师的最高工资,1500,。,举例,例： 查询不讲授课程号为,C5,的教师姓名。,SELECT DISTINCT TN FROM T WHERE C5 !=ALL (SELECT CNO FROM TC WHERE TNO=T.TNO),!=ALL,的含义为不等于子查询结果中的任何一个值，也可使用,NOT IN,代替,!=ALL,。,子查询包含普通子查询和相关子查询。,前面所讲的子查询均为普通子查询，而本例中子查询的查询条件引用了父查询表中的属性值（,T,表的,TNO,值），我们把这类查询称为相关子查询。,4.2,数据更新,SQL,语言的数据更新语句,DML,主要包括插入数据、修改数据和删除数据三种语句。,插入数据记录,：插入数据是把新的记录插入到一个存在的表中。,学号,姓名,性别,年龄,S1,张力,男,21,4.4.1,插入数据,插入数据使用语句,INSERT INTO,，可分为以下几种情况。,1,、插入,一行新记录,，语法格式为：,INSERT INTO (,),VALUES(,),其中，,是指要插入新记录的表,是可选项，指定待添加数据的列,VALUES,子句指定待添加数据的具体值。,4.4.1,插入数据,注意：必须用逗号将各个数据分开，字符型数据要用单引号括起来。,例,1:,在学生表,S,中插入一条学生记录,（学号：,S7,；姓名：郑冬；性别：女；年龄：,21,）。,学号,姓名,性别,年龄,S1,张力,男,21,INSERT INTO S (,学号,姓名,性别,年龄,),VALUES (s7,郑冬,女,21),4.4.1,插入数据,注意：,1,、,列名的排列顺序不一定要和表定义时的顺序一致。,INSERT INTO S(,学号,性别,姓名,年龄,),VALUES,INSERT INTO (,) VALUES(),2,、,VALUES,子句值的排列顺序必须和列名表中的列名排列顺序一致，个数相等，数据类型一一对应。,INSERT INTO S(,学号,姓名,) VALUES (,s7,郑冬,),学号,姓名,性别,年龄,S1,张力,男,21,(s7 ,女,郑冬,21),4.4.1,插入数据,3,、,INTO,子句中没有指定列名，则新插入的记录必须在每个属性列上均有值，且,VALUES,子句中值的排列顺序要和表中各属性列的排列顺序一致。,INSERT INTO S VALUES (s7,郑冬,女,21),学号,姓名,性别,年龄,S1,张力,男,21,INSERT INTO S (,学号,姓名,性别,年龄,),VALUES (s7,郑冬,女,21),4.4.1,插入数据,2,、插入多行记录,用于表间的拷贝，将一个表中的数据抽取数行插入另一表中，可以通过子查询来实现。,插入数据的命令语法格式为：,INSERT INTO (,),子查询,举例,例：求出各系教师的平均工资，把结果存放在新表,AVGSAL,中。,1,）首先建立新表,AVGSAL,。,CREATE TABLE AVGSAL,(DEPARTMENT VARCHAR(20),AVGSAL SMALLINT),2,）然后利用子查询求出,T,表中各系的平均工资，把结果存放在新表,AVGSAL,中。,INSERT INTO AVGSAL,SELECT DEPT,AVG(SAL) FROM T GROUP BY DEPT,4.4.2,修改数据,SQL,语言可以使用,UPDATE,语句对表中的一行或多行记录的某些列值进行修改，其语法格式为：,UPDATE ,SET = ,=,WHERE ,其中：,是指要修改的表；,SET,子句给出要修改的列及其修改后的值；,WHERE,子句指定待修改的记录应当满足的条件，,WHERE,子句省略时，则修改表中的所有记录。,4.4.2,修改数据,修改一行，例把张力教师转到信息系。,修改多行：,例将所有老师年龄增加,1,岁,教师工号,姓名,性别,年龄,系别,职称,工资,S1,张力,男,30,计算机,助教,2000,UPDATE T SET DEPT=,信息, WHERE TN = ,刘伟,Teacher,UPDATE Teacher SET AGE = AGE+1,用子查询选择要修改的行,例：把讲授,C5,课程的教师的岗位津贴增加,100,元。,UPDATE T SET COMN=COMN+100,WHERE TNO IN,(SELECT T.TNO,FROM T,TC,WHERE T.TNO=TC.TNO AND TC.CNO=C5,),子查询的作用是得到讲授,C5,课程的教师号。,4.4.3,删除数据,使用,DELETE,语句可以删除表中的一行或多行记录，其语法格式为：,DELETE FROM WHERE ,其中，,是指要删除数据的表。,WHERE,子句指定待删除的记录应当满足的条件，,WHERE,子句省略时，则删除表中的所有记录。,4.4.3,删除数据,删除一行记录，例如：删除张力教师的记录。,删除多行记录，例如删除所有教师的授课记录,执行此语句后，,Teacher,表即为一个空表，但其定义仍存在数据字典中。,DELETE FROM Teacher WHERE TN=,张力,DELETE FROM Teacher,4.5,视图,视图是虚表，其数据不存储，其记录来自基本表，只在数据库中存储其定义。,视图在概念上与基本表等同，用户可以在视图上再定义视图，可以对视图进行查询删除更新等操作。,1,、定义视图：定义视图使用语句,CREATE VIEW,，,其语法格式为：,CREATE VIEW (),AS ,其中，,为可选项，省略时，视图的列名由子查询的结果决定。,4.5,视图,以下两种情况下，视图列名不可省略：,1.,视图由多个表连接得到，在不同的表中存在同名列，则需指定列名；,2.,当视图的列名为表达式或库函数的计算结果时，而不是单纯的属性名时，则需指明列名。,在子查询中不许使用,ORDER BY,子句和,DISTINCT,短语，如果需要排序，则可在视图定义后，对视图查询时再进行排序。,例：,创建一个计算机系教师情况的视图,SUB_T,。,CREATE VIEW SUB_T AS SELECT TNO,TN,PROF,FROM T WHERE DEPT =,计算机,其中：视图名字为,SUB_T,，,省略了视图列表,;,视图由子查询中的三列,TNO,TN,PROF,组成,;,视图创建后，对视图,SUB_T,的数据的访问只限制在计算机系内，且只能访问,TNO,TN,PROF,三列的内容，从而达到了数据保密的目的。,视图创建后，只在数据字典中存放,视图的定义,，而其中的子查询,SELECT,语句并不执行。,只有当用户对视图进行操作时，才按照视图的定义将数据从基本表中取出。,4.5,视图,例：,创建一学生情况视图,S_SC_C,（,包括学号、姓名、课程名及成绩）。,CREATE VIEW S_SC_C(SNO, SN, CN, SCORE),AS SELECT S.SNO, SN, CN, SCORE,FROM S, C, SC,WHERE S.SNO = SC.SNO AND,SC.CNO = C.CNO,此视图由三个表连接得到，在,S,表和,SC,表中均存在,SNO,列，则需指定视图列名。,4.5,视图,删除视图：视图定义后可随时删除，删除视图的语法格式为：,DROP VIEW ,例：删除计算机系教师情况的视图,SUB_T,。,DROP VIEW SUB_T,视图删除后，只会删除该视图在数据字典中的定义，而与该视图有关的基本表中的数据不会受任何影响，由此视图导出的其他视图的定义不会删除，但已无任何意义。用户应该把这些视图删除。,4.5,视图,查询视图：视图定义后，对视图的查询操作如同对基本表的查询操作一样。,例：查找视图,SUB_T,中职称为教授的教师号和姓名。,SELECT TNO,TN,FROM SUB_T,WHERE PROF=,教授,4.5,视图,此查询的执行过程是系统首先从数据字典中找到,SUB_T,的定义，然后把此定义和用户的查询结合起来，转换成等价的对基本表,T,的查询，这一转换过程称为视图消解（,View Resolution,）,相当于执行以下查询：,SELECT TNO,TN FROM T,WHERE DEPT =,计算机,AND PROF=,教授,由上例可以看出，