《数据库语言SQL》doc版.docVIP

第四章 数据库语言SQL本章的主要内容包括：本章是数据库课程的重点，SQL充分体现了关系数据库语言的特点和优点，是功能强大、通用性较好的一种语言，学习和掌握也比较容易。本章从SQL的特点入手，着重介绍SQL的主要功能和基本用法。本章包括如下知识点：学习要点1、SQL的特点学习要点2、简单查询学习要点3、连接查询学习要点4、嵌套查询学习要点5、数据库更新学习要点6、定义关系模式学习要点7、视图的定义和查询学习要点1 SQL的特点SQL：Structured Query Language。结构化查询语言。一种最常用的关系数据库语言。包含四个主要的部分：数据查询：data query数据操作：data manipulation数据定义：data definition数据控制：data controlSQL的主要特点包括：1高度非过程化2面向集合的操作方式3简洁易学，灵活易用学习要点2 简单查询SQL中最简单的查询就是从某个关系中查找满足某种条件的元组，这类似于关系代数中的选择运算。这种查询的一般格式如下：SELECT *FROM 关系名WHERE 选择条件*代表关系模式中所有的属性列表。关键字ASSELECT 属性的原名 AS别名举例：SELECT StudentNo AS NoFROM Student21 选择条件的构成SQL语句的WHERE子句包括一个关键字WHERE和跟在其后的一个表达式。这个表达式可以使用6种通用的比较运算符对值进行比较比较运算符： 连接运算符：|逻辑运算符：NOT（非） AND（与） OR（或）举例：SELECT StudentNameFROM StudentWHERE Age IS NULL22 字符串的比较字符串具有相等、大于和小于的概念。等于：两个字符串在直观上具有相同的字符序列即表示相等。按照字典顺序排序，与字符串的长度无关。：等价于任意字符组成的任意字符串：任意一个字符：转义符，如果需要查询的内容中本身带有或号，可以在前面加举例：查找姓“王”的学生的属性SELECT *FROM StudentWHERE StudentName LIKE 王；查询名为“FELL 100%”的电影的属性SELCET *FROM MovieWHERE TitleFELL 100%23 日期和时间的比较不同的SQL版本对于时间的表示方式不同，按照SQL2的规定，表示为：YYYY MM DD HH MM SS(.x)时间的大小是用时间的晚和早表示。24 输出的排序ORDER BY属性表 默认的是升序，ASC表示升序，DESC表示降序。举例：查询所有计算机系的学生，查询的结果是按照年龄从大到小的顺序排序。SELECT *FROM StudentWHERE Dept=”计算机系”ORDER BY Age DESC, StudentNo (按照年龄的降序排列，如果相等，按照学号的升序排列)25 聚合运算符SUM：求某列中所有值的和AVG：求某列中所有值的平均值MIN：求某列中的最小值MAX：求某列中的最大值COUNT：求某列中值的个数举例：查找学生的平均成绩。SELECT AVG(score)FROM SC查询所有学生的平均年龄：SELECT AVG(Age)FROM Student；查询学生的最大年龄：SELECT MAX(Age)FROM Student；查询计算机系学生的人数：SELECT COUNT(*)FROM StudentWHERE Dept=计算机系；26 分组在WHERE 后面加入GROUP BYHAVING指明具体条件举例：查询人数大于50的系的具体学生数。SELECT Dept COUNT(*)FROM StudentGROUP BY DeptHAVING COUNT(*)50学习要点3 连接查询连接和笛卡尔积，不需要有额外的关键字，仅仅使用普通的SELECT-FROM-WHERE语句，就可以方便的表示。31 查询的并、交、差：UNION代表并INTERSECT代表交EXCEPT代表差两个查询之间要使用括号分开。举例：列出计算机系年龄大于18岁并且选修了1234号课程的学生学号：SELECT SNoFROM S, SCWHERE S. SNo=SC. SNo AND CNo=1234 AND Age18 AND Dept=计算机系；列出计算机系年龄大于18岁但没有选修1234号课程的学生的学号：（SELECT StudentNoFROM StudentWHERE Age18 AND Dept“计算机系”）EXCEPT（SELECT StudentNoFROM StudentWHERE CourseNo=1234）32 连接与笛卡儿积：不需要特殊的符号，在FROM和WHERE子句中增加相关的关系和属性。例如使用关系名.属性名的表现形式来区分不同关系中有关联的属性。举例：查询计算机系年龄大于18岁并且选修了1234号课程的学生的学号。SELECT StudentNoFROM Student, StudentCourseWHERE Student.StudentNo=StudentCourse.Student AND CourseNo=123433 元组变量：元组变量的一种使用方法是：作为关系的别名。举例：查询同名学生的学号。那么我们面临的是从Student中同时拿出两个元组来对StudentName属性作比较，这样StudentName属性就出现二义性的问题。为了解决这个问题，我们要用元组变量作为关系的别名。SELECT Stud1.StudentNo, Stud2.StudentNo,FROM Student AS Stud1, Student AS Stud2WHERE Stud1.StudentName= Stud2.StudentName AND Stud1.StudentNo Stud2.StudentNo学习要点4 嵌套查询把一个SELECTFROMWHERE查询语句嵌套在另一个SELECTFROMWHERE查询语句中，前者为子查询或内查询，后者为外查询。SQL对嵌套查询的处理方法是从内层向外层处理，即先处理最内层的子查询，然后把查询的结果用于其外查询的查询条件，再层层向外求解，最后得出查询结果。 41 产生单值的子查询：利用SELECTFROMWHERE查询只生成一个元组，然后将这个元组作为“变量”，进行下一步的查询。注意：如果内查询的结果为0或大于1，会出现运行错误。举例：查找与9900011同名的学生姓名。SELECT StudentNoFROM StudentWHERE StudentNo9900011 AND StudentName=(SELECT StudentNameFROM StudentWHERE StudentNo=9900011 )42 涉及到关系的选择条件：相关的运算符包括：1、EXISTS：当且仅当关系R非空时，EXISTS为真。2、IN：当且仅当s和R中的某个值相等时，IN为真。当且仅当s和R中的任何一个值都不相等时，IN为假。3、ALL：当且仅当s和一元关系R中的每个值都满足关系时，s ALL R为真。是6个比较运算符（， ， ， ， ）中的任意一个。4、ANY：当且仅当s和一元关系R中的至少一个值都满足关系时，s ANY R为真。是6个比较运算符（， ， ， ， ）中的任意一个。EXISTS、ALL和ANY运算符都可以通过在整个表达式前面加上NOT来求反，就像其他布尔值的表达式一样。于是，当且仅当R为空时，NOT EXISTS R为真；其他可以依此类推。注意：sANY R含义相同。举例：在学生选课关系中找出平均成绩最好的学生的学号。SELECT StudentNoFROM StudentCourseGROUP BY StudentNoHAVING AVG(Score) ALL(SELECT AVG(Score)FROM StudentCourseGROUP BY StudentNo)43 涉及到元组的选择条件在SQL中的元组是用括号内的标量值列表来表示的，列表中的分量可以是常量也可以是属性。举例：查询选修了“C程序设计”课程的学生名单。SELECT StudentNameFROM StudentWHERE StudentNo IN(SELECT StudentNoFROM StudentCourseWHERE CourseNo IN(SELECT CourseNoFROM CourseWHERE CourseName=“C程序设计”)44 相关子查询：在子查询的过程中多次求值，每次对子查询中来自子查询外部元组变量的某一项赋一个值，这一类子查询称为相关子查询。举例：查询所有选修了课程号为1234课程的学生的姓名。SELECT StudentNameFROM StudentWHERE EXISTS(SELECT *FROM StudentCourseWHERE StudentNo=Student.studentNo AND CourseNo=1234)对于相关子查询来说，外层关系有多少个元组，就要做多少次子查询。而对于非相关子查询，则子查询只做一次，随后对外层关系的每个元组所作的处理均以此为共同的基础。关系代数中的关系是一个严格的集合；而SQL关系可以有副本，即它们是包，而不是集合。在SELECT语句中，默认的情况就是保留副本。如果我们不希望得到重复的查询结果，可以在关键字SELECT后面加上关键字DISTINCT（互异）。需要说明一下，SQL中用包（Bag）而不是集合（Set）作为关系的数据模型，一个重要的原因就是节省时间，提高运算速度。另外还有一个重要原因。就是，用包更能反映现实世界的统计特性。学习要点5 数据库更新插入：基本格式是：INSERT INTO R(A1,A2,An) VALUE (V1,V2,Vn)举例：看下面的插入语句：INSERT INTO Student(StudentNo, StudentName, Age, Dept)VALUE(990015, 王芳，20，计算机系)INSERT INTO StudentVALUE(990015, 王芳，20，计算机系)INSERT INTO Student(TABLE(990015, 王芳，20，计算机系)(990016, 李明，18，计算机系)(990017, 孙华，19，计算机系)）删除：基本格式是：DELETE FROM R WHERE 举例：看下面的删除语句：DELETE FROM StudentWHERE StudentNo=990023DELETE FROM StudentWHERE Dept=“外语系”DELETE FROM StudentCourseWHERE CourseNo IN(SELECT CourseNoFROM CourseWHERE CourseName=“计算机网络”)修改：基本格式是：UPDATE R SET A1=E1, A2=E2,An=En WHERE 举例：将所有计算机系学生的年龄加1，并且把系名改为“计算机科学系”： UPDATE StudentSET Age=Age+1, Dept=“计算机科学系”WHERE Dept=“计算机系”将C程序设计课程所有学生的成绩都加5分。UPDATE StudentCourseSET Score=Score+5WHERE CourseNo IN(SELECT CourseNoFROM CourseWHERE CourseName=C程序设计）学习要点6 定义关系模式对数据库模式中的各个元素进行说明称为数据定义。SQL的数据定义包括定义和撤销模式、表、视图、索引。基本的语句是CREATE TABLE，用于定义关系模式，指定属性和类型，规定各种约束。6.1 属性的数据类型：CHAR(n)：长度为n个字符的定长字符串。VARCHAR(n)表示最多有n个字符的变长字符串。BIT(n)：长度为n的位串。BIT VARYING(n)表示长度最多为n的位串。INT：或INTEGER 表示整型变量。FLOAT：或REAL 表示浮点型变量。DATE：表示日期。YYYY-MM-DDTIME：表示时间。HH：MM：SS(.x)6.2 定义表：在SQL中，定义关系模型称为定义表，即用关键字CREATE TABLE建表。定义一个SQL表的格式如下：CREATE TABLE R( A1， A2， An）；其中：CREATE TABLE是关键字； R 是我们要定义的表的名字； A1、A2 An是关系R的属性名。举例：对于关系Student模式，Student（StudentNo，StudentName，Age，Dept）可以建成如下的表：CREATE TABLE Student (StudentNo INT，StudentName CHAR(8)，Age INT，Dept VARCHAR(20) )；通常要对某些属性作一定的约束，例如规定其不能为空、单值约束或者设定默认值等等。NOT NULL表示某个属性的分量值不能为空；UNIQUE表示对某个属性进行单值约束；DEFAULT用来指定某个属性的分量的默认值。举例：规定学号StudentNo不能为空，且对其进行单值约束；对于年龄Age，默认值是18；则建表语句变为：CREATE TABLE Student (StudentNo INT NOT NULLUNIQUE，StudentName CHAR(8)，Age INT DEFAULT 18，Dept VARCHAR(20) )；6.3 撤销表：撤消一个表的SQL语句的基本格式是：DROP TABLE R； 举例1、将学生关系Student撤消：DROP TABLE Student；6.4 更改关系模式：更改关系模式的SQL语句的基本格式是：ALTER TABLE R 具体的更改操作；具体的更改操作可以分为三种。1、第一种更改是向R中增加一个属性列：ALTER TABLE R ADD A ；A是要增加的属性的名称 举例1、向关系模式Student（StudentNo，StudentName，Age，Dept）中增加一个属性Sex代表女生的性别，假设该属性的取值类型是长度为1的字符串。ALTER TABLE StudentADD Sex CHAR(1) DEFAULTM； 2、第二种更改是从R中删除一个属性列：ALTER TABLE R DROP A；举例2、将关系模式Student（StudentNo，StudentName，Age，Dept）中年龄属性Age删除：ALTER TABLE Student DROP Age；3、第三种更改是修改某个属性的数据类型或者某种约束，格式是：ALTER TABLE R MODIFY A ；举例：将关系模式Student（StudentNo，StudentName，Age，Dept）中将学生系别属性Dept的数据类型CHAR(20)改成CHAR(10)：ALTER TABLE Student MODIFY Dept CHAR(10)；6.5 建立和撤销索引：1、所谓索引（Index）就是一种为关系中的给定属性提供存取路径的数据结构。在某个属性上建立索引可以更有效地查询该属性为给定值的元组。2、建立索引的格式为：CREATE INDEX ON ；举例：在电影关系的年份属性上建立索引名为YearIndex的索引：CREATE INDEX YearIndex ON Movie(year)；电影关系的键码由名称和年份两个属性组成，就可以用如下语句在这两个属性上建立索引：CREATE INDEX KeyIndex ON Movie (title, year)；3、如果想要撤消索引，可用撤消索引语句，其格式如下：DROP INDEX 例如可用如下语句撤消建立在年份上的索引：DROP INDEX YearIndex; 学习要点7 视图的定义和查询“视图”（View）实际上并不存在，只是在逻辑上可以看作是一张表。我们可以把视图当成是普通的关系一样予以建立、查询、修改或者删除。视图是按不同的应用领域或不同的用户群体进行定义，从而使用户从数据库中浩如烟海的数据中超脱出来，只关注自己需要的数据。另外，视图也是对数据库中的数据进行安全保护的一种机制，使数据库中一些保密的数据对无关人员成为不可见的，从而不能随意查询。71定义视图：定义一个视图的一般格式是：CREATE VIEW AS ；举例：把计算机系学生学号和姓名作为视图：CREATE VIEW CSStudent AS SELECT StudentNo, StudentNameFROM Student WHERE Dept=计算机系；可把CSStudent看作是一个虚拟的表。为与这种虚拟表相区别，又把实际存在的表称为基本表。学生关系Student的如下所示：StudentNoStudentNameAgeDept2002101王云19