93结构化查询语言SQL

数据库系统及应用第5章结构化查询语言SQL主编：赖申江数据库系统及应用本章学习导航结构化查询语言SQL是一种功能齐全的数据库语言，包含数据定义功能、数据查询功能、数据操纵功能和数据控制功能。其中最主要的功能就是查询。通过本章学习，学生应掌握SQL语言的使用方法和技巧，提高知识综合运用能力，写出高质量的SQL查询命令。数据库系统及应用本章学习要点SQL的数据定义：熟练运用CREATETABLE、ALTERTABLET和DROPTABLE语句完成表的创建、修改和删除。SQL的数据操纵：熟练运用INSERT、DELETE和UPDATE语句添加、删除和更新记录。SQL的数据查询：熟练运用SELECT语句完成简单查询、嵌套查询、连接查询、分组与计算查询。教学课时：4课时数据库系统及应用第5章结构化查询语言SQL5.1SQL概述结构化查询语言（StructuredQueryLanguage，简称SQL）是一种介于关系代数和关系演算之间的语言。它于1974年由Boyce和Chamberlin提出，由于具有语言简洁、方便实用、功能齐全等优点，很快得到推广和应用。随着关系数据库的流行，SQL语言最终发展成为关系数据库的标准语言和数据库领域中一个主流语言。SQL已经被ANSI（美国国家标准化组织）确定为数据库系统的工业标准，它是数据库系统的通用语言。利用SQL，用户可以用几乎相同的语句在不同的数据库系统上执行同样的操作。数据库系统及应用第5章结构化查询语言SQL5.1.1SQL语言的特点SQL语言之所以能够成为国际标准，是因为它是一个综合的、通用的、功能极强，同时又简单易学的语言。（1）功能强大SQL语言集数据查询、数据操纵、数据定义和数据控制功能于一体。（2）高度非过程化用户只要提出“做什么”，而无需指明“怎么做”，存取路径的选择以及SQL语言的操作过程由系统自动完成，不但大大减轻了用户负担，而且有利于提高数据的独立性。（3）简单易用SQL语言十分简洁，实现核心功能，一般只要用到下面9个命令动词，如表5-1所示。因此容易学习和掌握。（4）有联机交互和嵌入两种使用方式SQL语言支持两种不同的使用方式。一种是联机交互使用，即用户能在终端直接输入SQL命令对数据库进行操作；另一种是嵌入式，即将SQL语句嵌入到其他高级语言中，以便程序员在设计时使用。数据库系统及应用第5章结构化查询语言SQL5.1.2SQL数据定义功能关系数据库的基本对象是表、视图和索引。因此SQL的数据定义功能包括定义数据库、表、视图和索引，如表5-2所示。由于视图是基于表的虚表，索引是依附于表的，因此SQL通常不提供修改视图和索引的操作。1．数据库的创建和删除关系数据库被定义为关系（表）、索引和视图的集合。在创建表、视图和索引前必须首先创建数据库。例如，创建学生选课数据库stsc，可以用CREATEDATABASEstsc语句来实现。数据库系统及应用第5章结构化查询语言SQL2．基本表的创建、修改和删除用CREATEDATABASE建立的数据库是一个空的数据库，接下来应该在这个数据库中建立表。（1）创建基本表表的创建既可以通过表设计器或VisualFoxPro的新建表命令实现，也可以使用SQL中的相应命令创建。命令格式：CREATETABLE|DBF表名[FREE]（字段名1字段类型(宽度[,小数位数])[,字段名2字段类型(宽度[,小数位数]),…])功能：建立一个由表名指定的表结构。说明：①表名：指定要创建的表的名称。②FREE：指定创建的表是一个自由表，不被添加到数据库中。③字段名1字段类型(宽度[,小数位数])：指定相应字段的名称、类型、宽度及小数位数。数据库系统及应用第5章结构化查询语言SQL例5-1在学生选课数据库stsc中创建一个学生表student。CREATETABLEstudent(学号c(8),姓名c(6),性别c(2),院系c(8),出生日期D,入学成绩N(4,1))功能：用CREATETABLE语句创建数据库表student。该表中包含了学号、姓名、性别、院系、出生日期和入学成绩6个字段。数据库系统及应用第5章结构化查询语言SQL（2）修改表结构修改表结构命令以ALTERTABLE开头，后面根据不同的操作使用不同的命令动词。①增加字段格式：ALTERTABLE表名ADD字段名字段类型(宽度)功能：对指定的表增加一个字段。例5-２为学生student表增加一个照片字段。ALTERTABLEstudentADD照片G②删除字段格式：ALTERTABLE表名DROP字段名功能：在指定的表中删除指定字段。例5-３删除学生student表中的照片字段。ALTERTABLEstudentDROP照片数据库系统及应用第5章结构化查询语言SQL③修改字段格式：ALTERTABLE表名ALTER字段名字段类型(宽度)功能：在指定的表中修改指定字段的类型和宽度，不能修改字段名。例5-４把学生student表中的姓名字段宽度由6改为10。ALTERTABLEstudentALTER姓名c(10)④修改字段名格式：ALTERTABLE表名RENAME字段名１TO字段名２功能：在指定表中将字段名１更名为字段名２。例5-５把课程表course中的课名改为课程名称。ALTERTABLEcourseRENAME课名TO课程名称说明：执行一条命令只能对一个字段进行一种操作。数据库系统及应用第5章结构化查询语言SQL（3）删除表DROPTABLE语句用于删除表的定义和表中的数据、建立在表上的索引、视图等。格式：DROPTABLE表名[/?][RECYCLE]功能：删除指定的表。说明：[/?]：将显示“移去”对话框，从中可删除指定的表。[RECYCLE]：将删除的表文件放到回收站，以后可以恢复。数据库系统及应用第5章结构化查询语言SQL3．视图的创建和删除视图是一个虚表，是由一个或者几个基本表导出的表。它本身不独立存储在数据库中，即数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在对应的表中。当基本表中的数据发生了变化，视图中对应的数据也随之改变。数据库系统及应用第5章结构化查询语言SQL（1）创建视图视图一经创建，就可以通过它来查询数据库中数据，又可以用来定义新的视图。格式：CREATEVIEW视图名[(列名1[,列名2]…)])ASSELECT查询语句功能：创建一个由视图名指定的视图。用视图可以简化基本表的浏览。例如只想了解学生表中的学号、姓名和院系，可以从学生表student创建一个视图：CREATEVIEWstuyxASSELECT学号，姓名，院系FROMstudent其中，stuyx是视图名，省略了视图的列名，隐含是SELECT查询语句中的列名。例如，可以通过创建stu_jsj视图查看金融系的学生内容，语句如下：CREATEVIEWstu_jsjASSELECT*FROMstudentWHERE院系＝“金融”数据库系统及应用第5章结构化查询语言SQL（2）查询视图视图定义后，用户就可以像基本表一样对视图进行查询。格式：SELECT[(列名1[,列名2]…)])FROM视图名（3）删除视图格式：DROPVIEW视图名视图删除后，其定义的以及在它基础上再建立的其他视图将自动删除。由于视图是从基本表中衍生出来的，所以不能进行结构修改。若要改变视图结构，则只能删除后重新定义视图。数据库系统及应用第5章结构化查询语言SQL5.1.3SQL数据查询功能数据查询是数据库的核心操作。数据查询是指根据用户的需要，从数据库中提取所需的数据。SQL提供了SELECT语句实现查询，该语句具有灵活的使用方式和丰富的功能，既可以完成相对简单的单表查询，又可以完成复杂的多表连接查询和嵌套查询。数据库系统及应用第5章结构化查询语言SQL1．SQL查询命令一般格式：SELECT[ALL|DISTINCT]目标列表达式[,目标列表达式]…；FROM表名或视图名[,表名或视图名]…；[WHERE条件表达式]；[GROUPBY分组列名[HAVING条件表达式]]；[ORDERBY排序字段[ASC|DESC]]功能：根据WHERE子句的条件表达式，从FROM子句指定的基本表或视图中，找出满足条件的记录；再按SELECT子句中的目标表达式，选出记录中的属性值，形成结果表。如果有GROUPBY子句，则将结果按分组列名的值进行分组，该属性列值相等的记录为一个组。如果GROUPBY子句还带有HAVING短语，则只有满足指定条件的组才能输出。如果有ORDERBY子句，则结果还要按排序字段的值进行升序或降序排列。数据库系统及应用第5章结构化查询语言SQL说明：（1）在使用SQL查询语句时，无论数据库是否打开，FROM子句中指定的表所在数据库将自动打开。（2）由于FoxPro系统中将Enter键解释为一条语句的结束，在整个语句结束前只能连续写，不能用Enter键来换行。在SQL中，为了使查询结构清晰，往往把SELECT子句、FROM子句及各层嵌套以分号作为分隔符，表示下一行为本行的后续。为了便于理解，还是以学生选课数据库stsc为例说明SELECT语句的基本用法。学生选课数据库stsc由三个基本表组成：学生表student（学号、姓名、性别、出生日期、院系、入学成绩）；成绩表score（学号、课程编号、成绩）；课程表course（课程编号、课程名称、学分）。数据库系统及应用第5章结构化查询语言SQL2．简单查询简单查询是基于单表的查询，由SELECT和FROM短语构成无条件查询或者由SELECT、FROM和WHERE短语构成有条件查询。（1）选择表中的若干列例5-6查询学生表student中所有院系。SELECT院系FROMstudent该结果中有重复值，若要去掉重复值只需加上DISTINCT短语：SELECTDISTINCT院系FROMstudent例5-7查询全体学生的详细记录。SELECT*FROMstudent其中的“*”表示要查询所有的列。例5-8查询全体学生情况，并将结果按入学成绩升序排序。SELECT*FROMstudentORDERBY入学成绩ASC结果表中的记录自动按入学成绩的升序排序。数据库系统及应用第5章结构化查询语言SQL（2）用WHERE子句选择满足条件的记录例5-9查找中文系全体学生的名单。SELECT姓名FROMstudentWHERE院系=中文例5-10查找缺少成绩的学生学号和课程号。SELECT学号,课程编号FROMscoreWHERE成绩ISNULL例5-11查找姓“王”的所有学生姓名和院系。SELECT姓名,院系FROMstudentWHERE姓名LIKE王%数据库系统及应用第5章结构化查询语言SQL3．连接查询若查询涉及到两个或两个以上的表，就要用到连接查询。（1）连接由于SQL的高度非过程化，用户只需要在FROM子句中指出要用到的表名，在WHERE中指出连接条件，连接过程将由系统自动完成。①连接条件用来连接两个表的条件称为连接条件，其一般格式为：[表名1.]列名1比较运算符[表名2.]列名2此外，连接条件还可以用下面的形式：[表名1.]列名1BETWEEN[表名2.]列名2AND[表名3.]列名3连接条件中的列名称为连接字段，条件中的各连接字段必须是可比的。②等值连接和非等值连接当连接运算符为“=”时，称为等值连接。使用其他运算符称为非等值连接。数据库系统及应用第5章结构化查询语言SQL