SELECT语句的基本句法.docx
- 文档编号:29328207
- 上传时间:2023-07-22
- 格式:DOCX
- 页数:20
- 大小:23.23KB
SELECT语句的基本句法.docx
《SELECT语句的基本句法.docx》由会员分享,可在线阅读,更多相关《SELECT语句的基本句法.docx(20页珍藏版)》请在冰豆网上搜索。
SELECT语句的基本句法
SELECT语句的基本句法
1关系代数
合并数据集合的理论基础是关系代数,它是由E.F.Codd于1970年提出的。
在关系代数的形式化语言中:
用表、或者数据集合表示关系或者实体。
用行表示元组。
用列表示属性。
关系代数包含以下8个关系运算符
选取――返回满足指定条件的行。
投影――从数据集合中返回指定的列。
笛卡尔积――是关系的乘法,它将分别来自两个数据集合中的行以所有可能的方式进行组合。
并――关系的加法和减法,它可以在行的方向上合并两个表中的数据,就像把一个表垒在另一个表之上一样。
交――返回两个数据集合所共有的行。
差――返回只属于一个数据集合的行。
连接――在水平方向上合并两个表,其方法是:
将两个表中在共同数据项上相互匹配的那些行合并起来。
除――返回两个数据集之间的精确匹配。
此外,作为一种实现现代关系代数运算的方法,SQL还提供了:
子查询――类似于连接,但更灵活;在外部查询中,方式可以使用表达式、列表或者数据集合的地方都可以使用子查询的结果。
2使用连接
2.1连接类型
在关系代数中,连接运算是由一个笛卡尔积运算和一个选取运算构成的。
首先用笛卡尔积完成对两个数据集合的乘运算,然后对生成的结果集合进行选取运算,确保只把分别来自两个数据集合并且具有重叠部分的行合并在一起。
连接的全部意义在于在水平方向上合并两个数据集合(通常是表),并产生一个新的结果集合,其方法是将一个数据源中的行于另一个数据源中和它匹配的行组合成一个新元组。
SQL提供了多种类型的连接方式,它们之间的区别在于:
从相互交叠的不同数据集合中选择用于连接的行时所采用的方法不同。
连接类型 定义
内连接 只连接匹配的行
左外连接 包含左边表的全部行(不管右边的表中是否存在与它们匹配的行),以及右边表中全部匹配的行
右外连接 包含右边表的全部行(不管左边的表中是否存在与它们匹配的行),以及左边表中全部匹配的行
全外连接 包含左、右两个表的全部行,不管另外一边的表中是否存在与它们匹配的行。
(H)(theta)连接使用等值以外的条件来匹配左、右两个表中的行
交叉连接 生成笛卡尔积-它不使用任何匹配或者选取条件,而是直接将一个数据源中的每个行与另一个数据源的每个行都一一匹配
在INFORMIX中连接表的查询
如果FROM子句指定了多于一个表引用,则查询会连接来自多个表的行。
连接条件指定各列之间(每个表至少一列)进行连接的关系。
因为正在比较连接条件中的列,所以它们必须具有一致的数据类型。
SELECT语句的FROM子句可以指定以下几种类型的连接
FROM子句关键字 相应的结果集
CROSSJOIN 笛卡尔乘积(所有可能的行对)
INNERJOIN 仅对满足连接条件的CROSS中的列
LEFTOUTERJOIN 一个表满足条件的行,和另一个表的所有行
RIGHTOUTERJOIN 与LEFT相同,但两个表的角色互换
FULLOUTERJOIN LEFTOUTER和RIGHTOUTER中所有行的超集
2.2内连接(InnerJoin)
内连接是最常见的一种连接,它页被称为普通连接,而E.FCodd最早称之为自然连接。
下面是ANSISQL-92标准
select*
fromt_institutioni
innerjoint_tellert
oni.inst_no=t.inst_no
wherei.inst_no="5801"
其中inner可以省略。
等价于早期的连接语法
select*
fromt_institutioni,t_tellert
wherei.inst_no=t.inst_no
andi.inst_no="5801"
2.3外连接
2.3.1 左外连接(LeftOuterJion)
select*
fromt_institutioni
leftouterjoint_tellert
oni.inst_no=t.inst_no
其中outer可以省略。
2.3.2 右外连接(RigtOuterJion)
select*
fromt_institutioni
rightouterjoint_tellert
oni.inst_no=t.inst_no
2.3.3 全外连接(FullOuter)
全外连接返回参与连接的两个数据集合中的全部数据,无论它们是否具有与之相匹配的行。
在功能上,它等价于对这两个数据集合分别进行左外连接和右外连接,然后再使用消去重复行的并操作将上述两个结果集合并为一个结果集。
在现实生活中,参照完整性约束可以减少对于全外连接的使用,一般情况下左外连接就足够了。
在数据库中没有利用清晰、规范的约束来防范错误数据情况下,全外连接就变得非常有用了,你可以使用它来清理数据库中的数据。
select*
fromt_institutioni
fullouterjoint_tellert
oni.inst_no=t.inst_no
2.3.4 外连接与条件配合使用
当在内连接查询中加入条件是,无论是将它加入到join子句,还是加入到where子句,其效果是完全一样的,但对于外连接情况就不同了。
当把条件加入到join子句时,SQLServer、Informix会返回外连接表的全部行,然后使用指定的条件返回第二个表的行。
如果将条件放到where子句中,SQLServer将会首先进行连接操作,然后使用where子句对连接后的行进行筛选。
下面的两个查询展示了条件放置位子对执行结果的影响:
条件在join子句
select*
fromt_institutioni
leftouterjoint_tellert
oni.inst_no=t.inst_no
andi.inst_no=“5801”
结果是:
inst_no inst_name inst_no teller_noteller_name
5801 天河区 5801 0001 tom
5801 天河区 5801 0002 david
5802 越秀区
5803 白云区
条件在where子句
select*
fromt_institutioni
leftouterjoint_tellert
oni.inst_no=t.inst_no
wherei.inst_no=“5801”
结果是:
inst_no inst_name inst_no teller_noteller_name
5801 天河区 5801 0001 tom
5801 天河区 5801 0002 david
2.4自身连接
自身连接是指同一个表自己与自己进行连接。
这种一元连接通常用于从自反关系(也称作递归关系)中抽取数据。
例如人力资源数据库中雇员与老板的关系。
下面例子是在机构表中查找本机构和上级机构的信息。
selects.inst_nosuperior_inst,s.inst_namesup_inst_name,i.inst_no,i.inst_name
fromt_institutioni
joint_institutions
oni.superior_inst=s.inst_no
结果是:
superior_instsup_inst_name inst_no inst_name
800 广州市 5801 天河区
800 广州市 5802 越秀区
800 广州市 5803 白云区
2.5交叉(无限制)连接
交叉连接用于对两个源表进行纯关系代数的乘运算。
它不使用连接条件来限制结果集合,而是将分别来自两个数据源中的行以所有可能的方式进行组合。
数据集合中一的每个行都要与数据集合二中的每一个行分别组成一个新的行。
例如,如果第一个数据源中有5个行,而第二个数据源中有4个行,那么在它们之间进行交叉连接就会产生20个行。
人们将这种类型的结果集称为笛卡尔乘积。
大多数交叉连接都是由于错误操作而造成的;但是它们却非常适合向数据库中填充例子数据,或者预先创建一些空行以便为程序执行期间所要填充的数据保留空间。
select*
fromt_institutioni
crossjoint_tellert
在交叉连接中没有on条件子句
3SELECT语句的基本句法
3.1SELECT-FROM-WHERE句型使用实例
假设已建立三个表:
student(sno,sname,sex,birth,height,class,address)
course(cno,cname,credit)
elective(sno,cno,grade)
下面给出J-SQL各种形式的SELECT查询语句例子。
除极个别的地方要做小小改动外,这些例子也可在T-SQL中运行通过。
·在结果集中对表达式列指定列别名。
SELECTsno,grade-75ASMarjin
FROMelective;
·列出student表中所有学生的清单。
SELECT*
FROMstudent;
·使用DISTINCT,除去结果集中的重复行。
SELECTDISTINCTsex
FROMstudent;
·在WHERE子句中包含简单的条件‘grade>80’,输出表中行的子集。
SELECT*
FROMelective
WHEREgrade>80;
·在WHERE子句中包含日期比较,本例是列出1982年1月1日前出生的学生。
SELECT*
FROMstudent
WHEREbirth<#01-01-1982#; (注:
在T-SQL应为WHEREbirth<'01-01-1982')
·可用‘isnull’测试null值,用‘isnotnull’测试非空值。
SELECT*
FROMelective
WHEREgradeisnull;
·列出student表address列中以‘g’结尾的所有行。
(注:
T-SQL的匹配符为%和_,在J-SQL,匹配符‘*’与零个或更多的字符匹配;匹配符‘?
’仅和一个字符匹配。
)。
SELECT*
FROMstudent
WHEREaddresslike'*g';
·输出elective表grade列取值在60到75之间(包括60和75)的所有行。
SELECT*
FROMelective
WHEREgrade>=60andgrade<=75;
使用‘BETWEEN…AND…’限定一个值的范围将更易理解,若表示一个值不在某个范围可用‘NOTBETWEEN…AND…’。
因此,本例也可用下列语句实现:
(同上)
SELECT*
FROMelective
WHEREgradebetween60and75;
·输出student表height列取值在176,177,178之中的所有行。
(注:
(集合1)IN(集合2),表示集合1的每个元素均在集合2中则结果为真;把IN改成NOTIN,则意思相反。
)
SELECT*
FROMstudent
WHEREheightin(176,177,178);
·输出所有女生以及“身高超过175且在1982年1月1日以后出生”的男生。
(使用逻辑运算符AND和OR)
SELECT*
FROMstudent
WHEREheight>175andbirth>#01-01-1982#orsex='女';
检索选修了课程号cno为c06的学生学号与姓名。
第一种查询方法(联接查询):
SELECTstudent.sno,sname
FROMstudent,elective
WHEREstudent.sno=elective.snoandcno='c06';
检索选修了课程号cno为c06的学生学号要从elective表搜索,但学生的姓名却要在表student才能查到。
这个语句执行时,先对FROM子句给定的两个表按WHERE的条件进行联接操作,最后按SELECT子句投影出所需的列。
由于两个表都有SNO列,引用时要在SNO前用表名加以限定。
第二种查询方法(嵌套查询):
SELECTsno,sname
FROMstudent
WHEREsnoin
(SELECTsno
FROMelective
WHEREcno='c06');
这里外层WHERE子句中嵌有一个SELECT语句。
执行时,先执行内层的查询,找出选修了课程号为c06的学生学号;然后再从外层查询中按学号找出相应的学生姓名。
当查询涉及多个表时,使用嵌套结构可逐步求解层次分明。
而且,嵌套查询的执行效率也高于需做乘积或联接运算的联接查询。
本例的嵌套查询还可以写成下面的形式:
SELECTsno,sname
FROMstudent
WHERE'c06'in
(SELECTcno
FROMelective
WHEREsno=student.sno);
这里的内层查询称为‘相关查询’,其查询条件依赖于外层查询中的某个值,所以要多次处理,反复求值以供外层查询使用。
第三种查询方法(使用存在量词的嵌套查询):
SELECTsno,sname
FROMstudent
WHEREexists(SELECT*
FROMelective
WHEREstudent.sno=elective.snoandcno='c06');
谓词exists即存在量词($),其语义是内层查询的结果非空(或者说,内层查询结果应包含任何行;又或者说,内层查询结果应至少有一行)时为真。
·检索选修课程名为‘english’的学生学号和姓名。
SELECTsno,sname
FROMstudent
WHEREsnoin
(SELECTsno
FROMelective
WHEREcnoin
(SELECTcno
FROMcourse
WHEREcname='english'));
本例若用联接查询方法可写成如下代码:
SELECTstudent.sno,sname
FROMstudent,course,elective
WHEREstudent.sno=elective.sno
ando=o
andcname='english';
·检索至少同时选修了C03和C06两门课的学生学号。
SELECTA.sno
FROMelectiveASA,electiveASB
WHEREA.sno=B.snoandA.cno='C03'andB.cno='C06';
本例中,在同一层查询内,同一个表elective出现了两次,引入表的别名A和B就是为了便于区分。
若在A中找到C03的行,B中找到C06的行,且两行的SNO相同,那么这个学生就是至少同时选修了指定两门课的学生。
把表的别名A和B看成是定义了两个元组变量将更容易理解。
·检索出没有选修maths课程的学生学号和姓名。
SELECTsno,sname
FROMstudent
WHEREsnonotin
(SELECTsno
FROMelective
WHEREcnoin
(SELECTcno
FROMcourse
WHEREcname='maths'));
请注意例中最外层的WHERE子句是使用了‘notin’,若改为‘notexists’则代码如下:
SELECTsno,sname
FROMstudent
WHEREnotexists
(SELECTsno
FROMelective
WHEREstudent.sno=elective.snoandcnoin
(SELECTcno
FROMcourse
WHEREcname='maths'));
·检索出选修了全部课程的学生学号和姓名。
SELECTsno,sname
FROMstudent
WHEREnotexists
(SELECT*
FROMcourse
WHEREnotexists
(SELECT*
FROMelective
WHEREstudent.sno=elective.sno
ando=o));
本例的查询可以这样理解,在student依次指定每个学生,在course中不存在一门课程是这个学生没有学的。
·外层查询与内层查询可通过三种方式连接:
表达式[NOT]IN(内层查询)
[NOT]EXISTS(内层查询)
标量值q[ANY|ALL|SOME](内层查询) [注:
标量值也称为常量或字面值;q是算术比较运算符(如=,<=,<>等;SQL规定ANY与SOME是同义词,后来的标准都改为SOME。
]
例如,检索选修了课程号为C12的学生学号和姓名可表达为:
SELECTsno,sname
FROMstudent
WHEREsno=SOME
(SELECTsno
FROMelective
WHEREcno='c12');
注意,‘IN’与‘=SOME’等价,故例中的‘=SOME’可换成‘IN’。
但是‘NOTIN’并不等价于‘<>SOME’,与‘NOTIN’等价的是‘<>ALL’。
在使用ANY,ALL,SOME时,一定要仔细地弄清楚他们的确切含义。
·检索至少有一门成绩超过200200105号学生某一门成绩的学生学号。
SELECTDISTINCTsno
FROMelective
WHEREgrade>some
(SELECTgrade
FROMelective
WHEREsno=200200105);
·聚合函数对一组值执行计算并返回单一的值。
统计student表的学生总人数以及平均身高。
SELECTcount(*)AS学生总人数,avg(height)AS平均身高
FROMstudent;
·在elective表统计选修了课程的学生人数以及找出所有成绩中的最高分、最低分。
/*本例用T-SQL语句实现*/
usemydb
go
selectcount(distinctsno)as选课人数,max(grade)as最高分,min(grade)as最低分
fromelective
go
聚合函数
注意各个函数的含义:
COUNT(*)计算元组的个数
COUNT(列名)求一列中值的计算个数
SUM(列名)求一列中值的总和
AVG(列名)求一列中值的平均值
MAX(列名)求一列中值的最大值
MIN(列名)求一列中值的最小值
3.2SELECT语句的完整句法
下面,我们以T-SQL为例,按SELECT语句执行过程的顺序介绍各个子句的语法摘要。
(1)FROM子句
FROM子句的功能是指定SELECT(包括DELETE,UPDATE)语句中使用的表和视图。
多种联接方式
(2)WHERE子句(行条件子句)
WHERE子句的功能是指定选取行子集的条件。
(3)GROUPBY子句(分组子句)
GROUPBY子句的功能是指定分组的条件。
/*本例按学号分组,统计每个学生的平均分,输出时按平均分降序排列*/
usemydb
go
SELECTsno,AVG(grade)as平均分,MAX(grade)as最高分
FROMelective
GROUPBYsno
ORDERBY平均分DESC
(4)HAVING子句(组条件子句)
HAVING子句的功能是在分组的基础上指定选取某些组的条件。
GROUPBY子句
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SELECT 语句 基本 句法