第四章结构化查询语言

第四章结构化查询语言----SQLSQL是结构化查询语言（StructuredQueryLanguage)的缩写，它包括查询、定义、操纵和控制四部分，是一种功能齐全的数据库语言,已成为关系数据库语言的国际标准。SQL是一种高度非过程化的面向集合的语言SQL的数据定义功能能够定义数据库的三级模式结构，即外模式、全局模式和内模式结构。在SQL中，外模式又叫视图，全局模式简称模式或数据库，内模式由系统概据数据库模式自动实现，一般无需用户过问。一个数据库由若干个基本表(关系）组成。每个视图也是一个关系，它由基本表产生出来，有自己独立的结构定义，但没有独立的数据存在，它的数据来自基本表。所以，又把视图称为虚表表1表2表3…视图1视图2视图3关系：基本表或表属性：字段或列元组：行4.1数据库模式的建立和删除4.2表结构的建立、修改和删除4.3表内容的插入、修改和删除4.4视图的建立、修改和删除4.5SQL查询（p85)本章中使用到的数据库教学库（包括学生、选课、课程三个基本表）学生号姓名性别专业0101001王明男计算机0102005刘芹女电子0202003张鲁男电子0303001赵红女电气0304006刘川男通信0501001张江男通信0502003沈艳女电子学生课程号课程名课程学分C001C++语言4C004操作系统3E002电子技术5X003信号原理4X005软件工程4课程学生号课程号成绩0101001C001780101001C004620102005E002730202003C001940202003C004650202003X003800303001C001760304006E00272选课进入1、定义教学库2、定义学生、选课、课程基本表结构3、向学生、选课、课程基本表中输入内容一、数据库模式的建立和删除1、建立数据库模式CREATE{SCHEMA|DATABASE}<数据库名>[AUTHORIZATION<所有者名>]Createschemaxueshauthorization刘勇Createdatabase教学库2、删除数据库模式DROP{SCHEMA|DATABASE}<数据库名>Dropdatabase教学库返回二、表结构的建立、修改和删除格式：createtable[<数据库名>.<所有者名>.]<基本表名>(<列定义>,…[,<表级完整性约束>,…)功能：在当前或给定的数据库中定义一个表的结构（关系模式）1、建立表结构说明（1）若省略，则在当前数据库建立（2）格式：<列名><数据类型>[长度][列级完整性约束]char(n)intfloatdatedefault<常量表达式>null/notnullprimarykeyuniquereferences<父表名>(<主码>)check(<逻辑表达式>)(3)在所有列定义之后进行primarykey(<列名>,…)主码约束unique(<列名>,…)单值约束Foreignkey(<列名>,…)references<父表名>(<外码>)

外码约束check(<逻辑表达式>)

检查约束Createtable教学库.学生Createtable学生返回createtable学生(学生号char(7)primarykey姓名char(6)notnullunique性别char(2)notnullcheck(性别＝‘男’or性别=‘女’出生日期datetimecheck(出生日期<‘1993-12-31’),专业char(10),年级intcheck(年级>=1and年级<=4))学生号姓名性别出生日期专业年级……学生使用了列级完整性约束Createtable课程（课程号char（4）primarykey,课程名char(10)notmullunique.课程学分intcheck(课程学分>=1and课程学分<=6))课程号课程名课程学分……课程使用了列级完整性约束createtable选课(学生号char(7)课程号char(4)成绩intcheck(成绩>=0and成绩<=100),primarykey(学生号,课程号),foreignkey(学生号)references学生(学生号),foreignkey(课程号)references课程(课程号))学生号课程号成绩……选课使用了表级完整性约束和列级完整性约束商品库（其中包括商品表1和商品表2两个基本表）商品代号分类名单价数量DBX-134电冰箱14568DSJ-120电视机186515DSJ-180电视机207310DSJ-340电视机37265KTQ-12空调器280012WBL-6微波炉64010XYJ-13洗衣机46820XYJ-20洗衣机87312商品表1商品表2商品代号产地品牌DBX-134北京雪花DSJ-120南京熊猫DSJ-180南京熊猫DSJ-340北京牡丹KTQ-12无锡春兰WBL-6青岛海信XYJ-13无锡小天鹅XYJ-20山西海棠createdatabase商品库use商品库createtable商品表1(商品代号char(8)primarykey,分类名char(8),单价float,数量int)createtable商品表2(商品代号char(8)primarykey产地char(6)品牌char(6))商品代号分类名单价数量…商品代号产地品牌…1、在表级完整性约束和列级完整性约束同时存在的四种约束是：单值、主码、外码、检查小结2、每个列级完整性约束只能涉及一个属性，每个表级完整性约束可涉及多个属性（含一个）。若只涉及到一个列（属性）时，可用两种约束中的任一种。3、默认值约束和空值/非空值约束只能在列级完整性约束中存在格式：alter[<数据库名>.<所有者名>.]<基本名>{add<列定义>,…|add<表级完整性约束>,…|dropcolumn<列名>，…|drop<约束名>,…}2、修改表结构功能：向已定义过的表中添加一些列的定义或一些表级完整性约束，或者从已定义过的表中删除一些列或一些完整性约束增加删除altertable学生add籍贯char(6)altertable学生dropcolumn籍贯格式：droptable[<数据库名>.<所有者名>.]<基本名>3、删除表结构功能：从当前或给定的数据库中删除一个表，在删除表结构的同时也删除了全部内容如：droptable学生1教学库（包括学生、选课、课程三个基本表）学生号姓名性别专业课程号课程名课程学分0101001王明男计算机0102005刘芹女电子0202003张鲁男电子0303001赵红女电气0304006刘川男通信0501001张江男通信0502003沈艳女电子学生课程学生号课程号成绩选课C001C++语言4C004操作系统3E002电子技术5X003信号原理4X005软件工程40101001C001780101001C004620102005E002730202003C001940202003C004650202003X003800303001C001760304006E00272三、表内容的插入、修改和删除向基本表中插入数据的命令有两种格式：一种是向具体元组插入常量数据(单行插入）一种是把子查询的结果输入到另一个关系中去（多行插入）1、插入insert[into[<数据库名>.<所有者名>.]<基本表名>（<列名>,…)values(<列值>,…)单行插入格式：createtable职工（职工号char(6)primarykey,姓名char(8)notnull,性别char(2)notnull,年龄int,基本工资float)职工号姓名性别年龄基本工资010405李羽女281560insertinto职工（职工号，姓名，性别，年龄，基本工资）values(‘010405’,’李羽’，‘女’，28，1560)insert[into[<数据库名>.<所有者名>.]<基本表名>（<列名>,…)(select子句)多行插入格式：insert职工(职工号，姓名，性别，年龄，基本工资）select职工号，姓名，性别，年龄，基本工资from职工1where性别=‘男’职工号姓名性别年龄基本工资010405李羽女281560职工职工号姓名性别年龄职务基本工资津贴010203李英女44副处1750450010408王秀男43科员1568231010506刘强男33科员1244332职工1010408王秀男431568010506刘强男3312441、建立/删除数据库Createdatabase/drop2、建立/删除表结构Createtable/droptable3、表内容的插入Insert表级完整性约束和列级完整性约束默认值约束和空值/非空值约束只能在列级完整性约束中存在单值、主码、外码、检查2、修改update[<数据库名>.<所有者名>.]<目的表名>set<列名>=<表达式>，…[from<源表名>…][where<逻辑表达式>]update职工set年龄=年龄+1update职工set基本工资=基本工资*1.2where职工号=‘010405’update职工set基本工资=职工1.基本工资+职工1.津贴from职工1where职工.职工号=职工1.职工号职工号姓名性别年龄基本工资010405李羽女281560职工号姓名性别年龄职务基本工资津贴010203李英女44副处1750450010408王秀男43科员1568231010506刘强男33科员1244332010408王秀男431568010506刘强男331244职工职工1当在一条语句中使用多个表时,若使用的列名有重名,必须在所列名前加上表名和圆点分隔符限定1568+231010408王秀男431799010506刘强男3315761244+3323、删除delete[from][<数据库名>.<所有者名>.]<目的表名>[from<源表名>…][where<逻辑表达式>]deletefrom职工where年龄>45delete职工from职工1where职工.职工号=职工1.职工号delete职工四、视图的建立、修改和删除视图基本表1局部模式中的表，虚表全部模式中的表，实表对视图通常只做修改、查询视图的建立和删除只能影响视图本身，不影响对应的基本表，但对视图内容的更新（插入、删除和修改）直接影响基本表

每个视图的列可以来自同一个基本表，也可以来自多个不同的基本表，视图是基本表的抽象和在逻辑意义上建立的新关系视图来自基本表非主属性基本表21、建立视图格式：createview<视图名>（<列名>,…)as<select子句>功能：在当前数据库中根据select的查询结果建立一个视图，包括视图的结构和内容。学生（学号，姓名，性别，系）建立计算机系的学生视图createview计算机系视图表（学号，姓名，性别）asselect学号，姓名，性别

from学生where系=“计算机”学号姓名性别专业4051王平女经管4052赵路男经管4061邱华女计算机4062宁静女计算机4063张宇男计算机4071刘兵男电子课程号课程名学分C001高等数学6C002会计学5C003管理学4C004程序设计3C005数字电路4学号课程号成绩4051c001784051c002894052c002884052c003854063c00367选课（SC）学生（S）课程（C）createview成绩视图表（学号，姓名，课程号，课程名，成绩）asselect选课.学号，姓名，选课.课程号，课程名，成绩from学生，选课，课程where学生.学号=选课.学号and课程.课程号=选课.课程号and专业=‘经管’学号姓名课程号课程名成绩4051王平c001高等数学784051王平c002会计学894052赵路c002会计学884052赵路c003管理学85特征一：使用视图还能够根据用户的局部应用、根据用户的习惯命名视图中的列名特征二：设计基本表时，不能把通过计算得到的属性作为关系的属性，但在视图中却可以定义。学生成绩（学号，姓名，语文，数学，英语）createview成绩视图（学号，姓名，平均成绩，总成绩）asselect学号，姓名，（语文+数学+英语）/3，语文+数学+英语from学生成绩要求：建立包含平均成绩、总成绩的成绩视图2、修改视图内容格式：update[<数据库名>.<所有者名>.]<视图名>set<列名>=<表达式>,…[from<源表名>，…][where<逻辑表达式>]功能：按照一定条件对当前或指定数据库中的一些列值进行修改。update成绩视图表set成绩=80where学生号=‘0102005’and课程号=‘E002’3、修改视图定义格式：alterview<视图名>(<列名>，…)as<select子句>alterview学生视图（学生号，专业）asselect学生号，专业from学生功能：在当前数据库中修改已知视图的列，它与select子句查询结果相对应createview学生视图（学生号，姓名）asselect学生号，姓名from学生4、删除视图格式:dropview<视图名>功能：删除当前数据库中的一个视图§4.5SQL查询SQL的查询只对应一条语句，即SELECT语句。SQL查询速度快，且只需用户讲清楚“要干什么”，而不需要指出“怎么干”。一、SQL查询的基本结构

SELECT<表达式1>，<表达式2>，…，<表达式n>;FROM<关系1>，<关系2>，...<关系m>;WHERE<条件表达式>你要查询(输出)什么？（查询目标）最常用的格式是用逗号分隔的属性名所查询的目标来自那个表(要使用的关系名)查询的目标要满足什么条件如没有条件，where可省略，表示对所有记录比较运算符：><>=<==<>!=#逻辑运算符：andornot在条件中也经常会用到一些谓词，比如：

all(所有）any(任意）

between…and…(在…

之间）

in(包含）notin(不包含）

exists(存在）notexist（不存在）从学生关系中找出专业是电气的学生学号、姓名

Select

fromwhere学号,姓名；学生；专业=‘电气’*；所有字段from和where实现选择运算投影引用字符型常量Select学号，课程名，成绩From选课，课程Where选课.课程号=课程.课程号课程号课程名课程学分C001C++语言4C004操作系统3E002电子技术5X003信号原理4X005软件工程4课程学号课程号成绩0101001C001780101001C004620102005E002730202003C001940202003C004650202003X003800303001C001760304006E00272from和where实现连接运算From和where实现了连接和选择的运算新版规定P86如果不同的关系具有相同的属性名，必须在前面冠以关系名本章中使用到的数据库教学库（包括学生、选课、课程三个基本表）学生号姓名性别专业0101001王明男计算机0102005刘芹女电子0202003张鲁男电子0303001赵红女电气0304006刘川男通信0501001张江男通信0502003沈艳女电子学生课程号课程名课程学分C001C++语言4C004操作系统3E002电子技术5X003信号原理4X005软件工程4课程学生号课程号成绩0101001C001780101001C004620102005E002730202003C001940202003C004650202003X003800303001C001760304006E00272选课商品库（其中包括商品表1和商品表2两个基本表）商品代号分类名单价数量DBX-134电冰箱14568DSJ-120电视机186515DSJ-180电视机207310DSJ-340电视机37265KTQ-12空调器280012WBL-6微波炉64010XYJ-13洗衣机46820XYJ-20洗衣机87312商品表1商品表2商品代号产地品牌DBX-134北京雪花DSJ-120南京熊猫DSJ-180南京熊猫DSJ-340北京牡丹KTQ-12无锡春兰WBL-6青岛海信XYJ-13无锡小天鹅XYJ-20山西海棠二、SELECT选项其中SELECT子句用逗号分开的表达式为查询目标，可为用逗号分开的属性名,或包含字段名、字段函数的表达式。Select

From商品表1商品代号，单价*数量DISTINCT用于SELECT子句中，使得从查询结果中去掉重复元组。若不使用DISTINCT，则默认为ALL，即无论是否有重复元组都全部输出。1、DISTINCT和ALL的使用例：selectdistinct分类名；From

商品表1商品代号分类名单价数量DBX-134电冰箱14568DSJ-120电视机186515DSJ-180电视机207310DSJ-340电视机37265KTQ-12空调器280012WBL-6微波炉64010XYJ-13洗衣机46820XYJ-20洗衣机87312商品表1分类名电冰箱电视机电视机电视机空调器微波炉洗衣机洗衣机分类名电冰箱电视机空调器微波炉洗衣机П分类名（商品表1）Where数量>10П分类名（δ数量>10（商品表1））商品表2商品代号产地品牌DBX-134北京雪花DSJ-120南京熊猫DSJ-180南京熊猫DSJ-340北京牡丹KTQ-12无锡春兰WBL-6青岛海信XYJ-13无锡小天鹅XYJ-20山西海棠selectdistinct产地；From例：列出商品表2中的所有产品的不同产地商品表22、用AS指定查询结果的自定义列名Select

From学生学生号，性别学生号姓名性别专业0101001王明男计算机0102005刘芹女电子0202003张鲁男电子0303001赵红女电气0304006刘川男通信0501001张江男通信0502003沈艳女电子学生where专业=‘通信’numbersex0304006男0501001男学生号性别0304006男0501001男ASnumberassexП学生号，性别（δ专业=‘通信’（学生））Select

From商品表1商品代号，单价*数量as价值商品代号分类名单价数量DBX-134电冰箱14568DSJ-120电视机186515DSJ-180电视机207310DSJ-340电视机37265KTQ-12空调器280012WBL-6微波炉64010XYJ-13洗衣机46820XYJ-20洗衣机87312商品表1商品代号价值DBX-134…DSJ-120…DSJ-180…DSJ-340…KTQ-12…WBL-6…XYJ-13…XYJ-20…从商品表1中查询出每一种商品的价值3、可使用的列函数count*countall（<列名>）countdistinct（<列名>）max(<列名>)min(<列名>)avg(<列名>)sum(<列名>)数值列商品代号分类名单价数量DBX-134电冰箱14568DSJ-120电视机186515DSJ-180电视机207310DSJ-340电视机37265KTQ-12空调器280012WBL-6微波炉64010XYJ-13洗衣机46820XYJ-20洗衣机87312商品表1从商品表1中查询出不同分类名的个数Select

Fromcount(distinct分类名）as分类种数商品表1分类种数

5count*countall（分类名）countdistinct（分类名）商品代号分类名单价数量DBX-134电冰箱14568DSJ-120电视机186515DSJ-180电视机207310DSJ-340电视机37265KTQ-12空调器280012WBL-6微波炉64010XYJ-13洗衣机46820XYJ-20洗衣机87312商品表1从商品表1中查询出所有商品的最大数量、最小数量、平均数量及数量总和Select

Frommax(数量）as最大数量，min(数量）as最小数量商品表1avg(数量）as平均数量，sum(数量）as总和最大数量最小数量平均数量总和2051192商品代号分类名单价数量DBX-134电冰箱14568DSJ-120电视机186515DSJ-180电视机207310DSJ-340电视机37265KTQ-12空调器280012WBL-6微波炉64010XYJ-13洗衣机46820XYJ-20洗衣机87312商品表1从商品表1中查询出分类名为“电视机”的商品的种数、最高价、最低价及平均价。Select

Fromcount(*）as种数，max(单价）as最高价商品表1min(单价）as最低价，avg(单价）as平均价种数最高价最低价平均价337261865…Where分类名=“电视机”商品代号分类名单价数量DBX-134电冰箱14568DSJ-120电视机186515DSJ-180电视机207310DSJ-340电视机37265KTQ-12空调器280012WBL-6微波炉64010XYJ-13洗衣机46820XYJ-20洗衣机87312商品表1Select

max(单价*数量）,min(单价*数量）,sum（单价*数量）From商品表1写出它的功能。查询出商品的最高价值、最低价值及总价值

SELECT<表达式1>，<表达式2>，…，<表达式n>;FROM<关系1>，<关系2>，...<关系m>;WHERE<条件/连接表达式>复习Distinct/all字符型常量的表达专业=‘电气’新版规定ascount/min/max/sum/avg商品表1.商品代号=商品表2.商品代号三、from选项FROM子句指出查询目标及下面WHERE子句的条件所涉及的所有关系的关系名用户可以自行定义临时别名，在FROM子句中给出，特别是表名比较长时，定义别名作为列名的前缀限定符更为方便1、为关系指定临时别名selectx.学生号，y.学生号from学生基本情况表x，学生基本档案表yWherex.籍贯=‘广东’Select学生基本情况表.学生号，学生基本档案表.学生号from学生基本情况表，学生基本档案表Where学生基本情况表.籍贯=‘广东’２、联接查询

如果查询目标涉及到两个或几个关系，要进行联接运算。由于SQL是高度非过程化的，用户只要在FROM子句中指出各个关系的名称，在WHERE子句里正确指出联接条件即可。联接运算由系统去完成并实现优化。关系1.属性名=关系2.属性名商品代号分类名单价数量DBX-134电冰箱14568DSJ-120电视机186515DSJ-180电视机207310DSJ-340电视机37265KTQ-12空调器280012WBL-6微波炉64010XYJ-13洗衣机46820XYJ-20洗衣机87312商品表1商品表2商品代号产地品牌DBX-134北京雪花DSJ-120南京熊猫DSJ-180南京熊猫DSJ-340北京牡丹KTQ-12无锡春兰WBL-6青岛海信XYJ-13无锡小天鹅XYJ-20山西海棠商品表1商品表2⋈商品代号分类名单价数量产地

品牌………………Select*From商品表1，商品表2where商品表1.商品代号=商品表2.商品代号Select商品表1.*，产地，品牌如果不同的关系具有相同的属性名，必须在前面冠以关系名从商品表1和商品表2中查询出按商品代号进行自然连接的结果三、按下列给出的每项功能写出相应的查询命令1、从商品库中查询出每种商品的商品代号、单价、数量和产地selectfromwhere商品表1（商品代号，分类名，单价，数量）商品表2（商品代号，产地，品牌）商品表1.商品代号=商品表2.商品代号商品表1,商品表2商品表1.商品代号,单价,数量,产地P112学生号姓名性别专业0101001王明男计算机0102005刘芹女电子0202003张鲁男电子0303001赵红女电气0304006刘川男通信0501001张江男通信0502003沈艳女电子学生课程号课程名课程学分C001C++语言4C004操作系统3E002电子技术5X003信号原理4X005软件工程4课程学生号课程号成绩0101001C001780101001C004620102005E002730202003C001940202003C004650202003X003800303001C001760304006E00272选课查询出每个学生选修每门课程的学生号、姓名、课程号、课程名、成绩等数据П学生号,姓名（学生）⋈选课П课程号,课程名(课程)⋈）学生号姓名课程号课程名成绩学生号姓名性别专业0101001王明男计算机0102005刘芹女电子0202003张鲁男电子0303001赵红女电气0304006刘川男通信0501001张江男通信0502003沈艳女电子学生课程号课程名课程学分C001C++语言4C004操作系统3E002电子技术5X003信号原理4X005软件工程4课程学生号课程号成绩0101001C001780101001C004620102005E002730202003C001940202003C004650202003X003800303001C001760304006E00272选课查询出每个学生选修每门课程的学生号、姓名、课程号、课程名、成绩等数据）selectfrom学生x，课程y，选课zx.学生号，x.姓名，y.课程名，z．成绩

wherex.学生号=z.学生号andy.课程号=z.课程号四、where选项WHERE子句指出查询目标必须满足的条件（连接条件、筛选条件），如没有条件，此子句可省略。商品表1.商品代号=商品表2.商品代号专业=‘电气’商品代号分类名单价数量DBX-134电冰箱14568DSJ-120电视机186515DSJ-180电视机207310DSJ-340电视机37265KTQ-12空调器280012WBL-6微波炉64010XYJ-13洗衣机46820XYJ-20洗衣机87312商品表1从商品表1中查询出单价大于1500，同时数量大于等于10的商品Select

fromwhere商品表1单价>1500and数量>=10商品代号，单价，数量商品代号分类名单价数量DBX-134电冰箱14568DSJ-120电视机186515DSJ-180电视机207310DSJ-340电视机37265KTQ-12空调器280012WBL-6微波炉64010XYJ-13洗衣机46820XYJ-20洗衣机87312商品表1商品表2商品代号产地品牌DBX-134北京雪花DSJ-120南京熊猫DSJ-180南京熊猫DSJ-340北京牡丹KTQ-12无锡春兰WBL-6青岛海信XYJ-13无锡小天鹅XYJ-20山西海棠查询出产地为南京或无锡的所有商品的商品代号、分类名、产地和品牌Select

fromwhere商品表1x,商品表2yx.商品代号，分类名，产地，品牌（产地=‘南京’

or产地=‘无锡’）andx.商品代号=y.商品代号

SELECT<表达式1>，<表达式2>，…，<表达式n>;FROM<关系1>，<关系2>，...<关系m>;WHERE<条件/连接表达式>Distinct/all字符型常量的表达专业=‘电气’新版规定ascount/min/max/sum/avg商品表1.商品代号=商品表2.商品代号定义别名学生号课程号成绩0101001C001780101001C004620102005E002730202003C001940202003C004650202003X003800303001C001760304006E00272选课学生号课程号成绩0101001C001780101001C004620102005E002730202003C001940202003C004650202003X003800303001C001760304006E00272选课C1C2Selectdistinctc1.学生号

from选课c1,选课c2wherec1.学生号=c2.学生号andc1.课程号<>c2.课程号对于相同的表可定义不同的别名，以使它们作为不同的表使用查询出选修至少两门课程的学生学号学生号姓名性别专业0101001王明男计算机0102005刘芹女电子0202003张鲁男电子0303001赵红女电气0304006刘川男通信0501001张江男通信0502003沈艳女电子学生课程号课程名课程学分C001C++语言4C004操作系统3E002电子技术5X003信号原理4X005软件工程4课程学生号课程号成绩0101001C001780101001C004620102005E002730202003C001940202003C004650202003X003800303001C001760304006E00272选课Select姓名

from学生x,课程y,选课zwherex.学生号=y.学生号andy.课程号=z.课程号and课程名=‘操作系统’写功能。查询出选修了课程名为‘操作系统’课程的每个学生的姓名P111二、按照下列每条查询命令写出相应的功能。1、selectx.商品代号，分类名，数量，品牌

from商品表1x,商品表2ywherex.商品代号=y.商品代号

从商品库中查询出每一种商品的商品代号、分类名、数量和品牌等信息。and(品牌=‘熊猫’or品牌=‘春兰’）品牌为熊猫或春兰的

商品表1（商品代号，分类名，单价，数量）商品表2（商品代号，产地，品牌）SELECTFROMWHERE从商品库中查询出产地为广州或深圳的所有商品的商品代号、分类名、产地和品牌。(产地=‘广州’or产地=‘深圳’)x.商品代号，分类号，产地，品牌商品表1x,商品表2yx.商品代号=y.商品代号and94从教学库中查询出选修了课程名为“数据库应用”课程的每个学生的学号、姓名和专业。学生(学生号

char(7),姓名char(6),性别char(2),专业char(6))课程(课程号

char(4),课程名char(10),课程学分int)选课(学生号

char(7),课程号

char(4),成绩int)selectX.学生号,姓名,专业

from学生x,课程y,选课zwherex.学生号=z.学生号andy.课程号=z.课程号andy.课程名=’数据库应用’selectcount(*)from商品表1where数量>10从商品库中查询出数量大于10的商品种数

SELECT<表达式1>，<表达式2>，…，<表达式n>;FROM<关系1>，<关系2>，...<关系m>;WHERE<条件/连接表达式>

SELECT<表达式1>，<表达式2>，…，<表达式n>;FROM<关系1>，<关系2>，...<关系m>;WHERE<条件/连接表达式>传统新版投影选择、连接投影连接选择新版SQL中,已经把查询连接条件从where选项中转移到from选项中,并且还丰富了连接功能.一、中间连接、左连接、右连接中间连接From<表名1>innerjoin<表名2>On<表名1>.<连接名1><比较符><表名2>.<连接名2>leftright学生号姓名性别专业0101001王明男计算机0102005刘芹女电子0202003张鲁男电子0303001赵红女电气学生学生号课程号成绩0101001C001780101001C004620102005E00273选课Select*

from学生,选课where学生.学生号=选课.学生号From学生innerjoin选课On学生.学生号=选课.学生号select*Select

fromwhere商品表1,商品表2商品表1.商品代号，分类名，产地，品牌（产地=‘南京’

or产地=‘无锡’）and商品表1.商品代号=商品表2.商品代号Select

fromwhere商品表1innerjoin商品表2商品表1.商品代号，分类名，产地，品牌（产地=‘南京’

or产地=‘无锡’）On商品表1.商品代号=商品表2.商品代号学生号姓名性别专业0101001王明男计算机0102005刘芹女电子0202003张鲁男电子0303001赵红女电气学生学生号课程号成绩0101001C001780101001C004620102005E00273选课From学生leftjoin选课On学生.学生号=选课.学生号select*学生号姓名性别专业学生号课程号成绩0101001王明男计算机0101001C001780101001王明男计算机0101001C004620102005刘芹女电子0102005E002730202003张鲁男电子nullnullnull0303001赵红女电气nullnullnull把第一个表中没有形成连接的所有元组也加入结果中所有学生的选课情况（含没选课的同学）一般连接（中间连接）课程号课程名课程学分C001C++语言4C004操作系统3E002电子技术5X003信号原理4X005软件工程4课程学生号课程号成绩0101001C001780101001C004620102005E00273选课From选课rightjoin课程On选课.课程号=课程.课程号select*学生号课程号成绩课程号课程名课程学分0101001C00178C001C++语言40101001C00462C004操作系统30102005E00273E002电子技术5nullnullnullX003信号原理4nullnullnullX005软件工程4把第二个表中没有形成连接的所有元组也加入结果中

SELECT<表达式1>，<表达式2>，…，<表达式n>;FROM<关系1>，<关系2>，...<关系m>;WHERE<条件/联接表达式>

SELECT<表达式1>，<表达式2>，…，<表达式n>;

WHERE<条件表达式>From<表名1>

inner/right/left

join<表名2>On<表名1>.<连接名1><比较符><表名2>.<连接名2>联接条件Select*From课程leftjoin(选课innerjoin学生on学生.学生号=选课.学生号)On课程.课程号=选课.课程号学生号姓名性别专业0101001王明男计算机0102005刘芹女电子0202003张鲁男电子0303001赵红女电气0304006刘川男通信0501001张江男通信0502003沈艳女电子学生课程号课程名课程学分C001C++语言4C004操作系统3E002电子技术5X003信号原理4X005软件工程4课程学生号课程号成绩0101001C001780101001C004620102005E002730202003C001940202003C004650202003X003800303001C001760304006E00272选课所有课程被学生选修的情况selectx.学生号,y.学生号,y.课程号from选课x,选课ywherex.学生号=@s1andy.学生号=@s2andx.课程号=y.课程号注：@s1和@s2分别是已保存相应学生号的字符型变量从教学库中查询出学生号为@s1的学生和学生号为@s2的学生所选修的共同课程的课程号SELECTDISTINCTx.*FROM学生x,选课y,选课zWHEREy.学生号=z.学生号andy.课程号<>z.课程号andx.学生号=y.学生号从教学库中查询出至少选修了两门课程的全部学生。二、嵌套查询嵌套查询是指在SELECT-FROM-WHERE查询块内部再嵌入另一个查询块，称之为子查询Where子句中嵌套嵌套基本格式SELECTFROMWHEREselectfromwhere1、<列名><比较符>all<子查询>用ALL表示与子查询结果中所有记录的相应值相比较均符合要求才算满足条件商品代号分类名单价数量DBX-134电冰箱14568DSJ-120电视机186515DSJ-180电视机207310DSJ-340电视机37265KTQ-12空调器280012WBL-6微波炉64010XYJ-13洗衣机46820XYJ-20洗衣机87312商品表1select*from商品表1where单价>all(select单价from商品表1where分类名=‘洗衣机’）从商品表1中查询出单价比洗衣机的单价都高的商品单价>(selectmax(单价)from商品表1where分类名=‘洗衣机’）三、按下列给出的每项功能写出相应的查询命令5、从商品库中查询出比所有商品单价的平均值要高的全部商品商品表1（商品代号，分类名，单价，数量）商品表2（商品代号，产地，品牌）(selectavg(单价)from商品表1)where单价>allfrom商品表1select*P1112、<列名><比较符>{any/some}(<子查询>)当子查询的查询结果中的任一个值满足所给的比较条件时,此比较式为真,否则为假.学生号姓名性别专业0101001王明男计算机0102005刘芹女电子0202003张鲁男电子0303001赵红女电气0304006刘川男通信0501001张江男通信0502003沈艳女电子学生课程号课程名课程学分C001C++语言4C004操作系统3E002电子技术5X003信号原理4X005软件工程4课程学生号课程号成绩0101001C001780101001C004620102005E002730202003C001940202003C004650202003X003800303001C001760304006E00272选课SelectfromWhere课程号=any(select课程号from课程where课程名=‘c++语言’)学生xinnerjoin选课yonx.学生号=y.学生号姓名，成绩

可以省略。因为子查询结果只有一个查询出选修了课程名为“c++语言”的所有学生的姓名和成绩Select*From商品表1Where单价=any(selectmax(单价）from商品表1)or单价=any(selectmin(单价）from商品表1)查询出所有商品中单价最高和最低的商品商品代号分类名单价数量DBX-134电冰箱14568DSJ-120电视机186515DSJ-180电视机207310DSJ-340电视机37265KTQ-12空调器280012WBL-6微波炉64010XYJ-13洗衣机46820XYJ-20洗衣机87312商品表1商品表2商品代号产地品牌DBX-134北京雪花DSJ-120南京熊猫DSJ-180南京熊猫DSJ-340北京牡丹KTQ-12无锡春兰WBL-6青岛海信XYJ-13无锡小天鹅XYJ-20山西海棠从商品库中查询出产地与品牌为’春兰’的商品的产地相同的所有商品的商品代号、分类名、品牌、产地。SelectfromWhere产地=some商品表1xinnerjoin商品表2yonx.商品代号=y.商品代号x.商品代号，x.分类号，y.品牌，y.产地

可以省略。因为子查询结果只有一个(select产地from商品表2where品牌=‘春兰’)三、按下列给出的每项功能写出相应的查询命令学生（学生号，姓名，性别，专业）选课（学生号，课程号，成绩）课程（课程号，课程名，课程学分）8、从教学库中查询出至少选修了姓名为@m1学生所选课程中一门课的全部学生select课程号=anyselectdistinct学生.*andwhere学生.学生号=选课.学生号and姓名=@m1from学生,选课wherefrom学生,选课()p112课程号学生.学生号=选课.学生号三、其他查询条件表达1、<列名>[not]between<开始值>and<结束值>在WHERE子句中，条件可以用BETWEEN…AND…表示在二者之间，低值在AND之前，高值在后。

NOTBETWEEN…AND..表示不在其间Select*From商品表1Where单价between1000and2000三、按下列给出的每项功能写出相应的查询命令2、从商品库中查询出数量在10和20之间的商品商品表1（商品代号，分类名，单价，数量）商品表2（商品代号，产地，品牌）where数量between10and20from商品表1select*2、[not]exists(<子查询>)条件可用EXISTS表示存在,如果子查询结果非空，则满足条件；NOTEXISTS正好相反，表示不存在，如果子查询结果为空，则满足条件。从教学库中查询出选修至少一门课程的所有学生Select

fromWhere*

学生（Select*from选课where选课.学生号=学生.学生号）exists?没有选修任何课程的学生notexists学生号姓名性别专业0101001王明男计算机0102005刘芹女电子0202003张鲁男电子0303001赵红女电气0304006刘川男通信0501001张江男通信0502003沈艳女电子学生学生号课程号成绩0101001C001780101001C004620102005E002730202003C001940202003C004650202003X003800303001C001760304006E00272选课7.select*from课程

whereexists(select*from选课

where课程.课程号=选课.课程号）查询出所有已被学生选修的课程P111学生号姓名性别专业0101001王明男计算机0102005刘芹女电子0202003张鲁男电子0303001赵红女电气0304006刘川男通信0501001张江男通信0502003沈艳女电子学生学生号课程号成绩0101001C001780101001C004620102005E002730202003C001940202003C004650202003X003800303001C001760304006E00272选课查询出与姓名为‘王明’的学生选课至少有一门相同的所有学生select选课.课程号from选课，学生where选课.学生号=学生.学生号select选课.课程号from选课，学生where选课.学生号=学生.学生号and学生.姓名=‘王明’学生.姓名<>’王明’（selecty.课程号from选课ywherey.学生号=x.学生号andy.课程号=(selectw.课程号from选课w，学生zwherew.学生号=z.学生号andz.姓名=‘王明’))wherex.姓名<>’王明’andexistselect*from学生xWhere中的嵌套查询

SELECT…FROM…WHERE<条件/联接表达式>(SELECT…FROM…WHERE…)列名>all列名>any/some[not]existsNotbetween／betweem三、按下列给出的每项功能写出相应的查询命令5、从商品库中查询出比所有商品单价的平均值要高的全部商品商品表1（商品代号，分类名，单价，数量）商品表2（商品代号，产地，品牌）(selectavg(单价)from商品表1)where单价>allfrom商品表1select*P111复习从教学库中查询出选修至少一门课程的所有学生Select

fromWhere*

学生（Select*from选课where选课.学生号=学生.学生号）exists?没有选修任何课程的学生notexists学生号姓名性别专业0101001王明男计算机0102005刘芹女电子0202003张鲁男电子0303001赵红女电气0304006刘川男通信0501001张江男通信0502003沈艳女电子学生学生号课程号成绩0101001C001780101001C004620102005E002730202003C001940202003C004650202003X003800303001C001760304006E00272选课复习7.select*from课程

whereexists(select*from选课

where课程.课程号=选课.课程号）查询出所有已被学生选修的课程P111复习3、<列名>[not]in{(<常量表>)|（<子查询>）}在WHERE子句中，条件可以用IN表示包含在其后面括号指定的集合中。括号内的元素可以直接列出，也可以是一个子查询模块的查询结果从学生表中查询出专业为计算机、电气、通信的所有学生select*from学生where专业in（‘计算机’，‘电气’，‘通信’）学生号姓名性别专业0101001王明男计算机0102005刘芹女电子0202003张鲁男电子0303001赵红女电气0304006刘川男通信0501001张江男通信0502003沈艳女电子学生课程号课程名课程学分C001C++语言4C004操作系统3E002电子技术5X003信号原理4X005软件工程4课程学生号课程号成绩0101001C001780101001C004620102005E002730202003C001940202003C004650202003X003800303001C001760304006E00272选课where（选课.课程号=课程.课程号）and（选课.学生号=学生.学生号）and课程名=‘操作系统’select学生.学生号，姓名，性别，专业from学生,选课，课程查询出选修了课程名为‘操作系统’的所有学生查询出选修了课程名为‘操作系统’的所有学生select*from学生where学生号in(select学生号

from选课，课程

where选课.课程号=课程.课程号and

课程名=‘操作系统’）学生(学生号

char(7),姓名char(6),性别char(2),专业char(6))课程(课程号

char(4),课程名char(10),课程学分int)选课(学生号

char(7),课程号

char(4),成绩int)6、selectx.*from课程x,选课ywherex.课程号=y.课程号andy.学生号=@s1andy.课程号notin(select课程号

from选课

where选课.学生号=@s2)查询出学生号为@s1所选修而学生号为@s2的学生没有选修的全部课程信息学生(学生号

char(7),姓名char(6),性别char(2),专业char(6))课程(课程号

char(4),课程名char(10),课程学分int)选课(学生号

char(7),课程号

char(4),成绩int)P111查询学生号为@s2的学生所选修的课程4、<字符型列名>[not]like<字符型表达式>在WHERE子句中，条件可以用LIKE指出字符串模式匹配，其后面必须是字符串常量，其中可以使用两个通配符_：任意一个单字符%

：任意多个（包括零个）任意字符。select*from商品表1where商品代号like商品代号分类名单价数量DBX-134电冰箱14568DSJ-120电视机186515DSJ-180电视机207310DSJ-340电视机37265KTQ-12空调器280012WBL-6微波炉64010XYJ-13洗衣机46820XYJ-20洗衣机87312商品表1‘%3%’‘3%’‘_3’select*from商品表1where商品代号like‘_B%’商品代号分类名单价数量DBX-134电冰箱14568DSJ-120电视机186515DSJ-180电视机207310DSJ-340电视机37265KTQ-12空调器280012WBL-6微波炉64010XYJ-13洗衣机46820XYJ-20洗衣机87312商品表1在where条件中经常用的谓词：

all(所有）any/some(任意）

between…and…(在…

之间）

in(包含）notin(不包含）

exists(存在）notexist（不存在）

like

SELECT查询目标

FROM表1，表2，…[WHERE条件表达式][GROUPBY分组列名][HAVING[组选择条件表达式][ORDERBY排序项[序]…]四、groupby选项GROUPBY子句用于产生列函数的分组统计值。其作用是按指定项目对记录分组，然后对每一组分别使用库函数。注意，如果在SELECT子句中出现库函数，与之并列的其它项目必须也是库函数或GROUPBY的对象。通常分组项目为字段，该字段应出现在查询结果中，否则分不清统计结果属于哪一组。学生号姓名性别专业0101001王明男计算机0102005刘芹女电子0202003张鲁男电子0303001赵红女电气0304006刘川男通信0501001张江男通信0502003沈艳女电子学生Select专业as专业名,count(专业)as学生数from学生gro