第九章结构化查询语句(SQL)

第九章结构化查询语句(SQL)本章要点9.1SQL语言的基本概述9.2数据定义9.3数据操纵9.4数据管理9.5SQL中的数据查询语句1．综合统一

SQL语言集数据定义（DDL）、数据操纵（DML）、数据管理（DCL）的功能于一体，语言风格统一，可以独立完成数据库的全部操作，包括定义关系模式、录入数据及建立数据库、查询、更新、维护数据、数据库的重新构造、数据库安全性等一系列操作的要求，为数据库应用系统开发者提供了良好的环境。2．高度非过程化

用户只需提出“做什么”，不必指明“怎么做”3．面向集合的操作方式4．以同一种语法结构提供两种使用方式:交互式或程序中5．语言简洁，易学易用9.1.1SQL语言的特点9.1.2SQL语言的基本概念

SQL语言支持关系型数据库的三级模式结构。其中外模式对应于视图（View）和部分基本表（BaseTable），模式对应于基本表，内模式对应于存储文件。基本表是本身独立存在的表，在SQL语言中一个关系对应一个表。一些基本表对应一个存储文件，一个表可以带若干索引，索引存放在存储文件中。存储文件的逻辑结构组成了关系型数据库的内模式。而存储文件的物理文件结构是任意的。视图是从基本表或其他视图中导出的表，它本身不独立存储在数据库中，也就是说数据库只存放在视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中，因此视图是一个虚表。9.2数据定义数据定义语言DDL用于执行数据定义的操作，如创建或删除表、索引和视图之类的对象。由CREATE、DROP、ALTER命令组成，完成数据库对象的建立（CREATE）、删除（DROP）和修改（ALTER）。9.2.1定义（创建）表【格式】CREATETABLE[FREE]<表名>（<字段名1><数据类型>[（<宽度>[，<小数位数>]）][域完整性约束][NULL|NOTNULL][，<字段名2>…]）【功能】定义（也称创建）一个表。示例【例9-1】创建一个自由表表STUD（学生信息表），它由以下字段组成：学号（C，10），姓名（C，8），性别（C，2)CREATETABLESTUDFREE（学号C（10），姓名C（10），性别C（2）LISTSTRUCTURE【例9-2】创建一个数据库表SC（课程成绩表），它由以下字段组成：学号（C，10）；课程号（C，2）。Opendatabase课程成绩CREATETABLESC（学号C（10），课程号C（2）NULL）LISTSTRUCTURE9.2.2修改表【格式1】ALTERTABLE<表名>ADD<新字段名><数据类型>[（<宽度>[，<小数位数>]）]【格式2】ALTERTABLE<表名>ALTER<字段名><数据类型>[（<宽度>[，<小数位数>]）][有效性规则]【格式3】ALTERTABLE<表名>DROP<字段名>【功能】修改表结构。【例9-3】在课程成绩表SC中，增加一个成绩字段变量（N，3）。ALTERTABLESCADD成绩N(3)ALTERTABLESCALTER成绩N(4)LISTSTRUCTURE【例9-4】在课程成绩表SC中，增加一个成绩字段变量（N，3）。OPENDATABASE课程成绩ALTERTABLESCALTER成绩N(4)CHECK；成绩>520ERROR“成绩大于520分”LISTSTRUCTUREALTERTABLESCALTER成绩DROPCHECKALTERTABLESCALTER成绩SETDEFAULT521ALTERTABLESCDROPCOLUMN成绩【格式】DROPDATABASE<数据库名>【功能】删除指定数据库的结构和数据。【说明】谨慎使用。9.2.4删除表【格式】DROPTABLE<表名>【功能】删除指定表的结构和内容（包括在此表上建立的索引）。【说明】如果只是想删除一个表中的所有记录，则应使用DELETE语句。9.2.3删除数据库数据操纵语言是完成数据操作的命令，一般分为两种类型的数据操纵，它们统称为DML：●数据检索（常称为查询）：寻找所需的具体数据。●数据修改：添加、删除和改变数据。数据操纵语言一般由INSERT（插入）、DELETE（删除）、UPDATE（更新），SELECT（检索，又称查询）等组成，由于SELECT比较特殊，所以一般又将它以查询（检索）语言单独出现。9.3数据操纵【格式1】INSERTINTO<表名>[(<字段名表>)]VALUES（<表达式表>）【格式2】INSERTINTO<表名>FROMARRAY<数组名>|FROMMEMVAR【功能】在指定的表文件末尾追加一条记录。格式1用表达式表中的各表达式值赋值给<字段名表>中的相应的各字段。格式2用数组或内存变量的值赋值给表文件中各字段。【说明】如果某些字段名在INTO子句中没有出现，则新记录在这些字段名上将取空值（或默认值）。但必须注意的是，在表定义说明了NOTNULL的字段名不能取空值。9.3.1插入记录<字段名表>：指定表文件中的字段，缺省时，按表文件字段的顺序依次赋值。<表达式表>：指定要追加的记录各个字段的值。【例9-5】在表文件STUD的末尾追加三条记录。***用表达式方式追加第一条记录***INSERTINTOSTUD(学号，姓名)VALUES(“011110”，“李建国”)DIMENSIONDATA(8)DATA（1）="011103"DATA（2）="李宁"DATA（3）="女"DATA（4）="电0134"DATA（5）="02"DATA（6）="江西九江"DATA（7）=｛^1985/06/15｝DATA（8）=.F.INSERTINTOSTUDFROMARRAYDATA***用数组方式追加第二条记录***学号="011202"姓名="赵娜"性别="女"班级名="英0112"系别代号="03"地址="广西南宁"出生日期={^1984/02/21}是否团员=.F.INSERTINTOSTUDFROMMEMVARLIST***用内存变量方式追加第三条记录***【格式】UPDATE<表文件名>SET<字段名1>=<表达式>[，<字段名2>=<表达式>…][WHERE<条件>]【功能】更新指定表文件中满足WHERE条件子句的数据。其中SET子句用于指定列和修改的值，WHERE用于指定更新的行，如果省略WHERE子句，则表示表中所有行。【说明】更新操作又称为修改操作。【例9-6】将成绩表（SC）中，所有课程号为02的成绩各加5分。UPDATESCSET成绩=成绩+5;WHERE课程号="02"9.3.2更新记录命令9.3.3删除记录【格式】DELETEFROM<表名>WHERE<表达式>【功能】从指定的表中删除满足WHERE子句条件的所有记录。如果在DELETE语句中没有WHERE子句，则该表中的所有记录都将被删除。【说明】这里的删除是逻辑删除，即在删除的记录前加上一个删除标记“*”。【例9-7】删除STUD表中所有性别为男的记录。DELETEFROMSTUDWHERE性别=“男”数据管理（也称数据控制）语言是用来管理（或控制）用户的访问权限的。由GRANT（授权）、REVOTE（回收）命令组成。而VisualFoxPro6没有这种权限管理。9.4数据管理9.5SQL中的数据查询语句数据库中的数据很多时侯是为了查询的，因此，数据查询是数据库的核心操作。而在SQL语言中，查询语言中有一条查询命令，即SELECT语句。9.5.1基本查询语句【格式】SELECT[ALL|DISTINCT]<字段列表>FROM<表>【功能】无条件查询。【说明】ALL：表示显示全部查询记录，包括重复记录。DISTINCT：表示显示无重复结果的记录。9.5.2带条件（WHERE）的查询语句【格式】SELECT[ALL|DISTINCT]<字段列表>FROM<表>[WHERE<条件表达式>]【功能】从一个表中查询满足条件的数据。【例1】查询学生的基本信息。

SELECT*FROM学生【例2】查询学生的年龄信息。

SELECT学号，姓名，year(date())-year(出生日期）

AS年龄FROM学生【例3】显示学生表中所有男生记录的学号，姓名和性别字段值。SELECT学号，姓名，性别FROM学生WHERE性别="男"【例4】显示学生表中出生日期在85年到86年之间的学生的学号，姓名，出生日期。SELECT学号，姓名，出生日期;FROM学生;WHERE出生日期

BETWEEN{^1985-01-01}AND{^1986-12-31}【例5】显示STUD表中姓李的学生的学号，姓名，出生日期。SELECT学号，姓名，出生日期;FROM学生;WHERE姓名

LIKE“李%”9.5.3SQL的复杂查询在一个数据库中的多个表之间一般都存在着某些联系，在一个查询语句中同时涉及到两个或两个以上的表时，这种查询称之为连接查询（也称为多表查询）。在多表之间查询必须处理表与表之间的连接关系。

SELECT[ALL|DISTINCT]<字段列表>FROM<表1>[，表2…..]WHERE<条件表达式>1、简单连接查询【例1】查询男学生的成绩信息，包括学号，姓名，成绩。SELECTa.学号，a.姓名，b.成绩;FROM学生a，成绩b;WHEREa.学号=b.学号AND

a.性别=“男”【例2】查询并显示各个学生所学课程的情况。SELECTa.学号，a.姓名，c.课程名;FROM学生a，成绩b，课程c;WHEREa.学号=c.学号.AND.c.课程号=b.课程号2．嵌套查询在SQL语句中，一个SELECT－FROM－WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询或子查询。【例1】显示“王波”所在班级的学生名单。SELECT学号，姓名，班级;FROM学生;WHERE班级=（SELECT班级FROMSTUDWHERE姓名=“王波”）【例2】查询女生成绩信息，包括学号，课程号，成绩。Sele*from成绩where学号in(sele学号;From学生where性别=“女”3．超联接查询SELE…….FROMTABLEINNER[LEFT][RIGHT][FULL]JOINTABLEON条件WHERE……...（1）内部联接：普通联接，只有满足条件的记录出现在结果。（2）左联接：满足条件的记录出现在结果，同时第一个表不满足条件的记录也出现。（3）右联接：满足条件的记录出现在结果，同时第二个表不满足条件的记录也出现。（4）完全联接：不管是否满足联接条件的记录全出现在结果SELECTa.学号，b.课程名，b.成绩;FROM成绩a[INNER][LEFT][RIGHT][FULL]JOIN

；

课程bONa.课程号=b.课程号4、简单计算查询主要函数：计数COUNT（）；求和SUM（）；平均值AVG（）；最大值MAX（）；最小值MIN（）。[例如]查询最高分的学生信息。4．排序与分组查询【格式】SELECT[ALL|DISTINCT]<字段列表>FROM<表>[WHERE<条件>[GROUPBY<分类字段列表>…][HAVING<过滤条件>][ORDERBY<排序项>[ASC|DESC]【功能】包括有排序、函数运算和谓词演算【例1】按出生日期降序显示STUD表中的学号，姓名，出生日期。SELECT学号，姓名，出生日期;FROMSTUD;ORDERBY

出生日期

DESC【例2】按成绩升序显示SC表中的学号，课程号，成绩。SELECT学号，课程号，成绩;FROMSC;ORDERBY成绩【例3】按班级分类显示学生的姓名，课程名，成绩，同一班级按分数排序。SELECTa.姓名，a.班级名，c.课程名，b.成绩;FROMSTUDa，SCb，COURSEc;WHEREa.学号=b.学号ANDb.课程号=c.课程号;ORDERBYa.班级名，b.成绩;【例4】显示各班总人数。SELECT班级名，COUNT（*）AS总人数;FROMSTUD;GROUPBY班级名5、集合并运算【例】显示计算机系的学生及所有男学生。SELECT学号，姓名，系别代号，性别;FROMSTUD;WHERE系别代号="01"UNION;SELECT学号，姓名，系别代号，性别;FROMSTUD;WHERE性别="男"【例1】显示计算机系男生的名单。SELECT学号，姓名，系别代号，性别;FROMSTUD;WHERE系别代号=“01”AND性别=“男”【例2】显示既选修了01课程又选修了02课程学生的名单。SELECT学号;FROMSC;WHERE课程号=“01”AND学号IN;（SELECT学号FROMSCWHERE课程号=“02”）【例3】显示计算机系非男生的名单。SELECT学号，姓名，系别代号，性别;FROMSTUD;WHERE系别代号=“01”AND性别=“女”【例4】显示选修了01课程而没有选修02课程学生的名单。SELECT学号;FROMSC;WHERE课程号=“01”AND学号NOTIN;（SELECT学号FROMSCWHERE课程号=“02”）7．查询去向默认情况下，查询输出到一个浏览窗口，用户在“SELECT”语句中可使用[INTO<目标>|TOFILE<文件名>|TOSCREEN|TOPRINTER]子句选择查询去向：INTOARRAY数组名：将查询结果保存到一个数组中。CURSOR<临时表名>：将查询结果保存到一个临时表中。DBF|TABLE<表名>：将查询结果保存到一个永久表中。TOFILE<文件名>[ADDITIVE]：将查询结果保存到文本文件中。如果带“ADDITIVE”关键字，查询结果以追加方式添加到<文件名>指定的文件，否则，以新建或覆盖方式添加到<文件名>指定的文件。TOSCREEN：将查询结果保在屏幕上显示。TOPRINTER：将查询结果送打