交通信息系统第八章结构化查询语言SQL1

1第8章结构化查询语言SQL

主要内容SQL概述SQL的数据定义SQL的数据查询数据更新嵌入式SQLTransactSQL语言概述

2本章概述

结构化查询语言SQL（StructuredQueryLanguage）是关系数据库的标准语言，对关系模型的发展和商用DBMS的研制起着重要的作用。SQL语言是介乎于关系代数和元组演算之间的一种语言。本章详细介绍SQL的核心部分内容：数据定义、数据查询、数据更新和嵌入式SQL。34.1SQL概述

主要内容SQL的产生与发展SQL的组成及特点44.1.1SQL的产生与发展

1972年，IBM公司开始研制实验型关系数据库管理系统SYSTEMR，其配备的查询语言称为SQUARE(SpecifyingQueriesAsRelationalExpression)语言，语言中使用了较多的数学符号。1974年，Boyce和Chamberlin把SQUARE修改为SEQUEL(StructuredEnglishQueryLanguage)语言。后来SEQUEL简称为SQL(StructuredQueryLanguage)，即“结构式查询语言”，SQL的发音仍“sequel”。现在SQL已经成为一个标准。

SQL有两个标准：ANSISQL和1992年通过的修改版本SQL-92（简称

SQL2）。还有一个新的标准SQL3，它扩充了SQL2，引入了递归、触发器和对象等概念和机制。SQL简介SQL语言是应用于数据库的语言，本身是不能独立存在的。它是一种非过程性(non-procedural)语言，与一般的高级语言(如C、JAVA)不同。一般的高级语言在存取数据库时，需要依照每一行程序的顺序处理许多的动作。但是使用SQL时，只需告诉数据库需要什么数据，怎么显示就可以了。具体的内部操作则由数据库系统来完成。64.1.2SQL的组成及特点

SQL语言从功能上可以分为四部分：数据查询（DataQuery）、数据操纵（DataManipulation）、数据定义（DataDefinition）和数据控制（DataControl）。数据定义语言，即SQLDDL，用于定义SQL模式、基本表、视图、索引等结构。数据操纵语言，即SQLDML。数据操纵分成数据查询和数据更新两类。其中数据更新又分成插入、删除和修改三种操作。数据控制语言，即SQLDCL，这一部分包括对基本表和视图的授权、完整性规则的描述、事务控制等内容。

SQL的核心部分相当于关系代数，同时又具有关系代数所没有的许多特点，如聚集、数据库更新等。7特点：综合统一高度非过程化SQL是一种第四代语言（4GL），用户只需提出“干什么”，无需具体指明“怎么干”，像存取路径选择和具体处理操作等，均有系统自动完成。统一的语法结构SQL有两种使用方式：一是联机交互使用，另一种是嵌入到某种高级语言程序设计语言中去使用。语言简洁8SQL的核心动词

功能动词数据库查询SELECT数据定义CREATE，DROP数据操纵INSERT，UPDATE，DELECT数据控制GRANT，REVOKE9操作对象操作方式创建删除修改数据库CREATEDATABASEDROPDATABASE表CREATETABLEDROPTABLEALTERTABLE索引CREATEINDEXDROPINDEX视图CREATEVIEWDROPVIEWSQL的数据定义功能包括数据库定义、表定义、视图和索引的定义。

10学号SNO姓名SNAME性别SEX年龄AGE所在系SDEPT200915121200915122200915123200915125李小勇刘晨王洪敏张力MFFM20191819CSCSMAIS学生表：S(SNO,SNAME,SEX,AGE,SDEPT)课程表：C(CNO,CNAME,CPNO,CCREDIT)学生选课表：SC(SNO,CNO,GRADE)学生表实例例4.1

定义一个教务管理数据库JWGL，它包含3个表：本章数据库示例（教务管理数据库）11课程号CNO课程名CNAME先修课CPNO学分CCREDIT1234567数据库数学信息系统操作系统数据结构数据处理C语言516764243424课程表实例学生表选课表实例学号SNO课程号CNO成绩GRADE200915121200915121200915121200915122200915122123239285889080124.3SQL的数据查询

主要内容SELECT语句格式单表查询聚集函数数据分组多表查询集合操作134.3.1SELECT语句格式1、SELECT—FROM—WHERE句型 在关系代数中最常用的式子是下列表达式：

πA1,…,An(σF(R1×…×Rm))

这里R1、…、Rm为关系，F是公式，A1、…、An为属性。 针对上述表达式，SQL为此设计了SELECT—FROM—WHERE句型：

SELECTA1，…，An FROMR1，…，Rm WHEREF

这个句型是从关系代数表达式演变来的，但WHERE子句中的条件表达式F要比关系代数中公式更灵活。14在WHERE子句的条件表达式F中可使用下列运算符算术比较运算符：<、<=、>、>=、<>、!=逻辑运算符：AND、OR、NOT集合成员资格运算符：IN、NOTIN谓词：EXISTS、ALL、SOME、UNIQUE 聚合函数：AVG、MIN、MAX、SUM、COUNTF中运算对象还可以是另一个SELECT语句，即SELECT句型可以嵌套2、SELECT语句完整的句法如下：

SELECT

目标表的列名或列表达式序列

FROM

基本表名和（或）视图序列

[WHERE

行条件表达式][GROUPBY

列名序列

[HAVING

组条件表达式]][ORDERBY

列名[ASC|DESC]，…]

主语句SELECT-FROM-WHERE的含义是：

根据WHERE子句的条件表达式，从FROM子句指定的基本表或视图中找出满足条件的元组，再按SELECT子句中的目标列表达式，选出元组中的属性值形成结果表。16整个语句的执行过程如下：(1)读取FROM子句中基本表、视图的数据，执行笛卡尔积操作。(2)选取满足WHERE子句中给出的条件表达式的元组。(3)按GROUP子句中指定列的值分组，同时提取满足HAVING子句中组条件表达式的那些组。(4)按SELECT子句中给出的列名或列表达式求值输出。(5)ORDER子句对输出的目标表进行排序，按附加说明ASC升序排列，或按DESC降序排列。17根据查询条件不同，可分为：单表查询连接查询嵌套查询集合查询184.3.2单表查询查询仅涉及一个表，是一种最简单的查询操作。一、选择表中的若干列二、选择表中的若干元组三、对查询结果排序四、使用聚合函数五、对查询结果分组

191、查询表中的若干列⑴查询指定列

在很多情况下，用户只对表中的一部分属性列感兴趣，这时可以通过在SELECT子句的<目标列表达式>中指定要查询的属性列。 例4.21查询全体学生的学号与姓名。

SELECTSNO,SNAMEFROMS;

执行过程：从S表中取出一个元组，再取出该元组在属性SNO和SNAME上的值，形成一个新的元组作为输出。对S表中的所有元组做相同的处理，最后形成一个结果关系作为输出。20⑵查询全部列

将表中的所有属性列都选出来，可以有两种方法。一种方法就是在SELECT关键字后面列出所有列名；另一种方法是如果列的显示顺序与其在基表中的顺序相同，也可以简单地将<目标列表达式>指定为“*”。

例4.23查询全体学生的详细记录。

SELECT*FROMS;

等价于：

SELECTSNO,SNAME,SEX,AGE,SDEPTFROMS;21(3)查询经过计算的值

SELECT子句的<目标列表达式>不仅可以是表中的属性列，也可以是表达式。例4.24查询全体学生的姓名及其出生年份。

SELECTSNAME,2009-AGEFROMS;

输出结果：

Sname_________________

李小勇1989

刘晨1990

王名1991

张立1990222、选择表中的若干元组

⑴消除取值重复的行

两个本来并不完全相同的元组，投影到指定的某些列上后，可能变成相同的行了，可以用DISTINCT取消它们。例4.26查询选修了课程的学生学号。

SELECTSNOFROMSC;该查询结果里包含了许多重复的行。如果想去掉结果表中的重复行，必须指定DISTINCT关键词：

SELECTDISTINCTSNOFROMSC;23例(1)SELECTSnoFROMSC;

或(默认ALL)SELECTALLSnoFROMSC;

结果：Sno-------9500195001950019500295002(2)SELECTDISTINCTSnoFROMSC;

结果：

Sno-------950019500224

⑵查询满足条件的元组查询满足指定条件的元组可以通过WHERE子句实现。查询条件谓词比较确定范围确定集合字符匹配空值多重条件（逻辑运算）=，>，<，>=，<=，!=，<>，!>，!<，NOT+上述运算符BETWEENAND，NOTBETWEENANDIN，NOTINLIKE，NOTLIKEISNULL，ISNOTNULLAND，OR，NOT

例4.27查询计算机科学系全体学生的名单。

SELECTSNAMEFROMSWHERESDEPT=’CS’;25范围查询谓词BETWEEN…AND…和NOTBETWEEN…AND…可以用来查找属性值在（或不在）指定范围内的元组，其中BETWEEN后是范围的下限（即低值），AND后是范围的上限（即高值）。例4.30

查询年龄在20～23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。SELECTSNAME,SDEPT,AGEFROMSWHEREAGEBETWEEN20AND23;与BETWEEN…AND…相对的谓词是NOTBETWEEN…AND…。例4.31

查询年龄不在20～23岁之间的学生姓名、系别和年龄。SELECTSNAME,SDEPT,AGEFROMSWHEREAGENOTBETWEEN20AND23;26

例4.30可改写为：

SELECTSNAME,SDEPT,AGEFROMSWHEREAGE>=20ANDAGE<=23；27确定集合谓词IN可以用来查找属性值属于指定集合的元组。

例4.32

查询计算机科学系（CS）、数学系（MA）和信息系（IS）学生的姓名和性别。

SELECTSNAME,SEXFROMSWHERESDEPTIN(’CS’,’MA’,’IS’);

与IN相对的谓词是NOTIN，用于查找属性值不属于指定集合的元组。

例4.33

查询既不是计算机科学系、数学系，也不是信息系的学生的姓名和性别。

SELECTSNAME,SEXFROMSWHERESDEPTNOTIN(’CS’,’MA’,’IS’);28字符匹配谓词LIKE可以用来进行字符串的匹配。其一般语法格式如下：

[NOT]LIKE‘<匹配串>’[ESCAPE‘<换码字符>’]其含义是查找指定的属性列值与<匹配串>相匹配的元组。<匹配串>可以是一个完整的字符串，也可以含有通配符%和_。其中：%（百分号）代表任意长度（长度可以为0）的字符串。例如a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab等都满足该匹配串。_（下划线）代表任意单个字符。例如a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串。29例4.34

查询学号为200915121的学生的详细情况。

SELECT*FROMSWHERESNOLIKE’200915121’;

等价于：

SELECT*FROMSWHERESNO=’200915121’;如果LIKE后面的匹配串中不含通配符，则可以用=（等于）运算符取代LIKE谓词，用!=或<>（不等于）运算符取代NOTLIKE谓词。例4.35

查询所有姓刘的学生的姓名、学号和性别。

SELECTSNAME,SNO,SEXFROMSWHERESNAMELIKE’刘%’;30

涉及空值的查询例例4.41

某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。

SELECTSNO,CNOFROMSCWHEREGRADEISNULL;/*分数GRADE是空值*/多重条件查询可用逻辑运算符AND和OR来联结多个查询条件。AND的优先级高于OR，但可以用括号改变优先级。例4.43

查询计算机科学系年龄在20岁以下的学生姓名。

SELECTSNAMEFROMSWHERESDEPT=’CS’ANDAGE<20;313、ORDERBY子句（对查询结果排序）使用ORDERBY子句可以按一个或多个属性列排序升序：ASC；降序：DESC；缺省值为升序当排序列含空值时ASC：排序列为空值的元组最后显示DESC：排序列为空值的元组最先显示32

例4.44

查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列。

SELECTSNO,GRADEFROMSCWHERECNO=’3’ORDERBYGRADEDESC;

SnoGrade--------------9501095024950079295003829501082950097595014619500255查询结果33

聚合函数是涉及整个关系的另一类运算操作,通过聚合函数，可以把某一列中的值形成单个值。

5类主要聚合函数：计数COUNT（[DISTINCT|ALL]*）COUNT（[DISTINCT|ALL]<列名>）计算总和SUM（[DISTINCT|ALL]<列名>） 计算平均值AVG（[DISTINCT|ALL]<列名>）4.3.3聚合函数34求最大值MAX（[DISTINCT|ALL]<列名>） 求最小值

MIN（[DISTINCT|ALL]<列名>）

DISTINCT短语：在计算时要取消指定列中的重复值ALL短语：不取消重复值ALL为缺省值35例4.47

查询选修了课程的学生人数。

SELECTCOUNT(DISTINCTSNO)FROMSC;学生每选修一门课，在SC中都有一条相应的记录。一个学生要选修多门课程，为避免重复计算学生人数，必须在COUNT函数中用DISTINCT短语。例4.48

计算选修l号课程的学生平均成绩。

SELECTAVG(GRADE)FROMSCWHERECNO=’1’;例4.49

查询选修l号课程的学生最高分数。

SELECTMAX(GRADE)FROMSCWHERCNO=’1’;36使用GROUPBY子句分组 细化集函数的作用对象未对查询结果分组，聚合函数将作用于整个查询结果对查询结果分组后，聚合函数将分别作用于每个组4.3.4对查询结果分组37这三个数取平均值第4-6分数取平均值最后三个数取平均值有一个学员参加考试例如，求每门课的平均成绩。38分组查询—GROUPBYSELECT

Cours