第07章 关系数据库标准语言SQL

第07章关系数据库标准语言内容提要认识SQL语言创建SQL查询SQL数据更新SQL数据查询7.1认识SQL语言SQL:StructuredQueryLanguage，结构化查询语言是用户和DBMS通信的语言和工具发展：1974年提出1986年被美国国家标准局批准成为关系型数据库语言的标准各种类型的RDBMS都采用作为其存取语言和标准接口7.1认识SQL语言特点：功能丰富、使用灵活、语言简捷易学高度的综合：数据操作、数据定义、数据控制等非过程化：命令方式，提出“做什么”面向集合：操作对象和结果是元组的集合两种使用方式，可嵌入语言结构简单、易学易用功能：数据定义、数据查询、数据更新、数据控制7.2创建SQL查询课堂演示7.3数据更新数据插入INSERTINTO…数据修改UPDAE…数据删除DELETE…7.3SQL数据更新数据插入给新记录的字段赋值来添加INSERTINTO表名

VALUES(值1,值2,....)

为表中所有字段指定值，或者：INSERTINTO表名(字段1,字段2,...)

VALUES(值1,值2,....)

指定所要插入数据的字段7.3SQL数据更新举例：“订单状态”表如图，添加新记录（4，已回款）。说明：“状态ID”是表的主键INSERTINTO订单状态(状态ID,状态名)

VALUES(4,”已回款”)由于是给表中所有字段添加值，SQL语句也可以写成：INSERTINTO订单状态 VALUES(4,”已回款”)提问：当该语言已经执行成功后，再执行会出现什么情况？7.3SQL数据更新数据修改：将表中字段的值更改为指定的值，通过UPDATE语句实现。语法：

UPDATE表名 SET字段1=新值[,字段1=新值,…] [WHERE条件表达式]说明：中括号部分表示可选项，一次可以更新一个字段，也可以更新多个字段，多字段之间用英文逗号分隔；WHERE条件表达式限制仅更新满足条件的记录，没有该子句，则更新全部记录。7.3SQL数据更新举例：更新“客户”表中ID为4的客户电子邮件地址为“MrHuang@”，并将职务修改为“销售经理”。UPDATE客户

SET电子邮件地址=”MrHuang@”,职务=”销售经理”

WHEREID=47.3SQL数据更新数据删除：将指定表的记录删除，通过DELETE语句实现。语法： DELETEFROM表名 [WHERE条件表达式]举例：删除“订单状态”表状态ID为4的记录。 DELETEFROM订单状态

WHERE状态ID=4

提示：如果没有WHERE条件表达式，将可能删除所有记录。课堂练习在右图所示表中完成：表字段说明：“货号”字段类型是自动编号，“货名”和“规格”文本型，“进货价格”是货币型，进货数量是数字型。“货号”是主键添加新记录，货名：贝雷帽，规格：顶，进货价格：98，进货数量：6。将“货号”是8的货名改成“普通长裤”。将表中规格是“顶”的商品进货价格改为以前的9折。删除进货数量在10以上记录。7.4数据查询7.4数据查询Select[All|Distinct][Topn[Percent]][<表名|表别名>.]<字段名1>[As<别名>][,[<表名|表别名.>]<字段名2>[As<别名>]][,…][Into<新表名>]From<表名或查询名>[<表名或查询名>…][Inner/Left/Right/FullJoin[<数据库名!>]<表名>[On<连接条件>…]][Where<条件表达式>][Groupby<字段名1>[,<字段名2>…][Having<筛选条件表达式>]][Orderby<字段名1>[Asc/Desc][,<字段名2[Asc/Desc]…]]7.4数据查询例7-4查询罗斯文经营的所有产品，即检索“产品”表中的所有行和列。Select[供应商ID],ID,产品代码,产品名称,说明,标准成本,列出价格,再订购水平,目标水平,单位数量,中断,最小再订购数量,类别,附件From

产品如果查询某表的所有列时，可用*代替所有字段名，即SQL语句又可写成： Select

*

From

产品字段名中有特殊符号时，如空格等，字段名加中括号7.4数据查询例7-5查询罗斯文经营的所有产品的ID、产品代码和产品名称，即检索“产品”表中的部分字段。 Select

ID,产品代码,产品名称

From

产品7.4数据查询例7-6查询罗斯文经营的前4种产品的ID、产品名称。

Select

top4ID,产品名称

From

产品例7-7查询罗斯文经营的前10%产品的ID、产品名称。

Select

top10PercentID,产品名称

From

产品7.4数据查询Select类别from产品例7-8查询产品中出现的不同类别。

Select

distinct

类别

From

产品7.4数据查询字段需要计算后输出计算订单明细的金额selectID

,[订单ID]

,数量,单价,数量*单价

from订单明细7.4数据查询统计的需求统计有多少种产品计算平均价格统计总数量查找最高单价和

最低单价7.4数据查询数据查询还可以使用集合函数实现数据统计的功能函数名功

能COUNT(*)计算出所有记录数SUM(字段名)计算指定的字段的数值总和AVG(字段名)计算一个数值型字段的平均值MAX(字段名)计算指定的字段中的最大值MIN(字段名)计算指定的字段中的最小值7.4数据查询例7-10查询罗斯文经营的产品数目，即统计“产品”表一共有多少行记录。

Select

count(*)

From

产品例7-11查询罗斯文经营的产品中的最高列出价格和最低列出价格。

Select

max(列出价格),min(列出价格)

From

产品7.4数据查询别名列名不能表达含义时，通过AS起别名 Select

count(*)as

产品数目

From

产品同样适用于其他查询

SelectID,产品名称as

名称

From

产品课堂练习查看“学生”表的学号、姓名、出生日期，其中出生日期字段显示为生日。统计表中入学成绩的平均分。查看表中有哪些民族（即只显示不同的民族）。查看表中的学号、姓名和年龄学号姓名性别出生日期民族入学成绩专业2014999001王梅女1996.10.4汉523大气2014990030张鹏飞男1995.12.5彝480网络安全2014992043欧阳洋男1996.1.23汉511计算机应用2014999024卓玛女1995.11.2藏478大气学生(学号,姓名,性别,出生日期,民族,入学成绩,专业)7.4数据查询条件：只查看某些记录查看“麻油”和“酱油”的产品信息查看价格低于30元的产品查看转入库存的采购订单明细记录查看2006年6月30日之前接收的采购订单明细where子句7.4数据查询例如：查看价格低于30元的产品信息

select*

from

产品

where

列出价格<30例如：查看酱油的产品信息

select*

from

产品

where

产品名称="酱油“7.4数据查询例如：查看2006年6月30日之前接收的采购订单

select*

from

采购订单明细

where

接收日期<#2006/06/30#例如：查看转入库存的采购订单明细记录

select*

from

采购订单明细

where

转入库存=truewhere

转入库存7.4数据查询查询条件运算符实例比较（比较运算符）=、>、<、>=、<=、<>标准成本>10确定范围Between…And…标准成本Between10And20

（包含边界值10和20）确定集合In、NotIn产品名称In(“啤酒”,”茶”)字符匹配Like、NotLike公司like“*公司”空值ISNull、IsNotNull单位数量ISNull嵌套查询All、Any（见7.4.4嵌套查询）多重条件（逻辑运算符）And、Or、Not标准成本<20And标准成本>10字符说明?任意单字符*零个或多个字符#任意一位数字(0-9)[字符列表]字符列表中的任意单字符[!字符列表]不在字符列表中的任意单字符7.4数据查询例7-13查询罗斯文经营的产品名称中第3个字为“粉”或“糕”的产品代码和产品名称。

Select

产品代码,产品名称

From

产品

Where

产品名称like

“??[粉糕]”7.4数据查询Where子句为多重条件时，必须使用AND或OR来表达整体逻辑含义。Where

<条件表达式1>AND<条件表达式2>：表示既满足第一个条件，又满足第二个条件，即同时满足。Where

<条件表达式1>OR<条件表达式2>：表示满足第一个条件或者第二个条件，即满足其一。7.4数据查询例如SQL语句：

Select

类别,ID,产品代码,产品名称,标准成本

From

产品

Where

类别="调味品"AND标准成本<107.4数据查询例如SQL语句：

Select

类别,ID,产品代码,产品名称,标准成本,再订购水平

From

产品

Where

类别="焙烤食品"OR

类别="谷类"提示：每个条件表达式一定要完整，如：

Where类别="焙烤食品"OR类别="谷类"，不能写成：Where类别="焙烤食品"OR"谷类"。课堂练习查找表中姓张的学生信息查找表中入学成绩500~550的学生信息查找大气专业藏族的学生信息查找1996年以前出生的女同学学号姓名性别出生日期民族入学成绩专业2014999001王梅女1996.10.4汉523大气2014990030张鹏飞男1995.12.5彝480网络安全2014992043欧阳洋男1996.1.23汉511计算机应用2014999024卓玛女1995.11.2藏478大气学生(学号,姓名,性别,出生日期,民族,入学成绩,专业)7.4数据查询排序Orderby字段1[Asc|Desc][,字段2[Asc|Desc]][,…]Asc：指定的排序项按升序排列。Desc：指定的排序项按降序排列。排序字段通常写为字段名，有时也用字段序号代替，第1个字段为1，第2个字段为2，…。默认情况下相当于使用Asc，Orderby按升序进行排序，如果用户特别要求按降序进行排序，必须使用Desc。7.4数据查询例7-14先按“类别”升序、再按“再订购水平”降序查询“产品”表中的类别、产品代码、产品名称和再订购水平。

Select

类别,产品代码,产品名称,再订购水平

From

产品

Orderby类别,再订购水平Desc7.4数据查询例如：查询大气专业的学生信息，要求按入学成绩从高到低的顺序显示。

select*

from

学生

where

专业="大气"

orderby入学成绩desc学号姓名性别出生日期民族入学成绩专业2014999001王梅女1996.10.4汉523大气2014990030张鹏飞男1995.12.5彝480网络安全2014992043欧阳洋男1996.1.23汉511计算机应用2014999024卓玛女1995.11.2藏478大气学生(学号,姓名,性别,出生日期,民族,入学成绩,专业)7.4数据查询重定向输出Into新表名用于把查询结果存放到一个新表中例7-15将“产品”表的所有调味品记录存放在新表“调味品产品”中。

Select类别,产品代码,产品名称,再订购水平

Into

调味品产品

From

产品

Where

类别="调味品"7.4数据查询7.4数据查询分组查询查找结果如右图，统计不同类别的产品数Select

类别,count(*)as产品数From

产品Groupby

类别只查找产品数少于3？Select

类别,count(*)as产品数From

产品Groupby

类别Havingcount(*)<3重要提示：SELECT字段列表中的所有字段都必须包括在GROUPBY子句中，或作为SQL聚合函数的参数包括在内。7.4数据查询Having子句与Where子句都是条件限定子句，但其作用对象不同，Having子句需要使用集合函数来限定查询结果。对比项Where子句Having子句作用对象表字段计算字段与分组的关系无关只能用于分组查询中，必须在Groupby之后7.4数据查询例7-17按“订单ID”统计“订单明细”中已开票的每张订单金额，只返回订单金额在500元以下的订单ID和订单金额。

Select

[订单ID],Sum(数量*单价)as订单金额

From

订单明细

Where

[状态ID]=2

Groupby[订单ID]

Having

Sum(数量*单价)<500课堂练习统计学生表中不同民族的学生人数。计算学生表中男生和女生的平均入学成绩。统计平均入学成绩在498分以上的专业。学号姓名性别出生日期民族入学成绩专业2014999001王梅女1996.10.4汉523大气2014990030张鹏飞男1995.12.5彝480网络安全2014992043欧阳洋男1996.1.23汉511计算机应用2014999024卓玛女1995.11.2藏478大气学生(学号,姓名,性别,出生日期,民族,入学成绩,专业)7.4数据查询7.4数据查询连接查询又称为多表查询，用于检索来自于多个数据源的查询三种内连接（INNERJOIN）外连接（OUTERJOIN）交叉连接（CROSSJOIN）建立连接的方法Where子句From子句7.4数据查询例7-18查询“订单明细”表中产品的发货城市。订单(订单ID,发货城市,…)订单明细(ID,订单ID,产品ID,数量,单价)

Select

发货城市,[产品ID],数量,单价

From

订单,订单明细

Where

订单.[订单ID]=订单明细.[订单ID]7.4数据查询Where

订单.[订单ID]=订单明细.[订单ID]提示1：参与连接运算的多个表具有相同的字段名，必须在字段名前加上所属的表名作为前缀，格式：

表名.字段名表名字段名表名字段名7.4数据查询提示2：为了方便的引用表（或查询），会给表（或查询）起别名。

Select

发货城市,[产品ID],数量,单价

From

订单ASa,订单明细ASb

Where

a.[订单ID]=b.[订单ID]

通过AS关键字起别名，AS可以省略。7.4数据查询使用From子句建立连接查询语法格式：From表1[连接类型]表2On连接条件连接类型，即指是内连接（INNERJOIN）、外连接（LEFT|RIGHTJOIN）还是交叉连接（CROSSJOIN）。表1、表2分别指连接的两个表对象（或查询），如果连接的是同一个表，称为内连接，内连接时，表必须起别名。连接条件，同上例中WHERE子句条件连接表达式，由被连接表中的列和比较运算符、逻辑运算符等构成。7.4数据查询两种不同的写法对比：

Select

发货城市,[产品ID],数量,单价

From

订单,订单明细

Where

订单.[订单ID]=订单明细.[订单ID]

Select

发货城市,[产品ID],数量,单价

From

订单InnerJoin

订单明细

On

订单.[订单ID]=订单明细.[订单ID]7.4数据查询例7-19查找来自“江苏”的订单信息。

Select

订单.*

From

订单InnerJoin客户

On订单.[客户ID]=客户.[ID]

Where

客户.[省/市/自治区]="江苏"7.4数据查询例7-20“客户”表左外连接“订单”表，查找所有客户公司对应的订单信息。

Select

客户.公司,[订单ID],[员工ID],

订单日期,发货日期,[状态ID]

From

客户LeftJoin订单

On订单.[客户ID]=客户.[ID]7.4数据查询嵌套查询例7-21查找“已分派”的订单明细信息。

Select

[订单ID],[产品ID],数量,单价,[状态ID]

From

订单明细

Where

[状态ID]=(Select[状态ID] From订单明细状态 Where状态名="已分派")父查询子查询7.4数据查询例7-22查找“东旗”和“广通”公司的部分订单信息。

Select

[订单ID],[员工ID],[客户ID],订单日期,发货日期

From

订单

Where

[客户ID]in(SelectID From客户 Where公司="东旗"

or公司="广通")7.4数据查询7.4数据查询单值嵌套返回结果是一个值的嵌套查询比较运算符：=、>、<、>=、<=、<>多值嵌套子查询的返回结果是一列值的嵌套查询比较运算符：ANY（或SOME）、ALL和IN7.4数据查询例7-23查找“产品”表中标准成本比所有“调味品”的标准成本还低的产品信息。

Select

ID,产品代码,产品名称,标准成本,类别

From

产品

Where

标准成本<ALL(Select标准成本

From产品

Where类别="调味品")7.4数据查询例7-24查询客户ID为“28”的订单明细。

Select

[员工ID],[产品ID],数量,单价

From

订单明细innerjoin

(Select[订单ID],[员工ID] From订单 Where[客户ID]=28)ASa ON订单明细.[订单ID]=a.[订单ID]7.4数据查询例7-25把供应商ID为“1”的采购订单添加到“供应商佳佳乐的订单”表中。

insertinto供应商佳佳乐的订单

Select* From采购订单 Where[供应商ID]=1提示：“供应商佳佳乐的订单”的表结构一定和返回的结果记录集结构一致，列数相同、数据类型相同。7.4数据查询例7-26供应商ID为8的采购订单数量统一上调40。

update

采购订单明细

set

数量=数量+40

Where

[采购订单ID]In(Select[采购订单ID] From采购订单 Where[供应商ID]=8)7.4数据查询例7-27将“供应商佳佳乐的订单”表中的创建者“王伟”的订单删除。