数据库系统及应用（第5版）课件 第7-12章 SQL查询- 数据库更广泛的应用和研究领域

查询语句基本格式SQL查询命令格式SELECT[ALL|DISTINCT]{*|<表达式>,…,<表达式>}FROM

<数据来源>[WHERE<条件>][GROUPBY

<列名>[,<列名>…][HAVING<谓词>]][ORDERBY

<列名>ASC|DESC],<列名>[ASC|DESC]…][COMPUTE…]SQL查询命令格式SELECT[ALL|DISTINCT]{*|<表达式>,…,<表达式>}

说明要查询的数据；ALL说明不去掉重复元组；

DISTINCT说明要去掉重复元组；<表达式>一般是表中的列名，如果要查询表中的所有列可以使用“*”表示。SQL查询命令格式FROM<数据来源>说明要查询的数据来自哪个（些）表，可以基于单个表或多个表进行查询；可以基于基本表、也可以基于视图，当然也可以两者一起使用。SQL查询命令格式WHERE<逻辑表达式>说明查询条件，即选择元组的条件，可以用于查询条件的运算符也非常丰富，下表列出了常用的运算符。SQL查询命令格式GROUPBY<列名>[,<列名>…][HAVING<谓词>]GROUPBY短语用于对查询结果进行分组，可以利用它进行分组汇总（即对查询结果按组进行计算或汇总）；HAVING短语必须跟随GROUPBY使用，它用来限定分组必须满足的条件。SQL查询命令格式ORDERBY<列名>[ASC|DESC],[<列名>[ASC|DESC]]…

用来对查询的结果进行排序。SQL查询命令格式COMPUTE短语

SQLServer支持的短语，可以进行带明细的汇总。

SQLServer支持的短语，可以进行带明细的分组汇总。COMPUTEBY短语SQL查询命令格式SQLServer

从2005开始还支持查询结果的并（UNION）交（INTERSECT）差（EXCEPT）运算查询的分类简单查询连接查询嵌套查询分组及汇总查询简单查询1.简单无条件查询4.查询结果的排序主要内容2.简单条件查询3.存储查询结果5.TOP短语的作用例：查询全部职工信息

SELECT* FROM基础.职工1.简单无条件查询1.简单无条件查询例题SELECT姓名,工资FROM基础.职工例：查询职工的姓名和工资信息1.简单无条件查询例题

查询职工的姓名,工资和年工资信息(年工资=工资*12)SELECT姓名,工资,工资*12FROM基础.职工1.简单无条件查询例题查询职工的姓名,工资和年工资信息(年工资=工资*12)SELECT姓名,工资,工资*12FROM基础.职工年工资1.简单无条件查询例题SELECTDISTINCT班组长FROM基础.职工检索职工表中的所有班组长要去掉结果表中的重复行，可用DISTINCT实现。班组长NULLE2E72.简单条件查询（1）比较大小(<,>,<=,>=,=,<>)例：查询工资不少于1300元的职工的姓名和工资值。SELECT姓名,工资FROM基础.职工WHERE工资>=13002.简单条件查询（1）比较大小(<,>,<=,>=,=,<>)例：查询工资在1000至2000元的职工信息。SELECT*FROM基础.职工WHERE工资>=1000AND工资<=2000简单条件查询(2)使用[NOT]BETWEEN…AND…的查询例：查询工资在1000至2000元的职工信息。SELECT*FROM基础.职工WHERE工资BETWEEN1000AND2000简单条件查询2.简单条件查询(2)使用[NOT]BETWEEN…AND…的查询例：查询工资不在1000至2000元的职工信息。SELECT*FROM基础.职工WHERE工资NOTBETWEEN1000AND2000等价的表达式?或：SELECT*FROM基础.职工WHERE工资<1000OR工资>2000(3)字符串匹配查询

用来查找指定列名与匹配串常量匹配的元组。

格式为：

列名[NOT]LIKE匹配串

匹配串类型：匹配串可以是字符串常量，也可以含有通配符。

通配符种类：

％（百分号）：匹配0个或多个字符。

＿（下划线）：匹配一个字符。

[]：匹配括号中的字符

[^]：不匹配括号中的字符2.简单条件查询(3)字符串匹配查询例：从器件关系中查找在规格字段值中任意位置包含字符串“DDR”的所有记录。

SELECT*FROM基础.器件WHERE规格LIKE'%DDR%'2.简单条件查询(3)字符串匹配查询例：查找订购单号（长度为4位）为OR开头，最后1位为0的订购单记录SELECT*FROM

订货.订购单WHERE订购单号

LIKE'OR_0'2.简单条件查询(3)字符串匹配查询例：查找订购单号前3位是OR7、最后1位为0~9的所有订购单记录。

SELECT*FROM订货.订购单WHERE订购单号LIKE'OR7[0-9]'2.简单条件查询（4）空值查询空值(NULL)是未确定的值或其值尚不知道例如，学生选课，在开学初学生只有选课记录，没有选修课的成绩，这时成绩项的值就是空值。不能用=或<>，只能用ISNULL或ISNOTNULL2.简单条件查询（4）空值查询例：查询没有确定供货方的订购单信息（供货方字段为空值的记录）SELECT*FROM订货.订购单WHERE供货方ISNULL2.简单条件查询（4）空值查询例：查询已经确定了供货方的订购单信息。SELECT*FROM订货.订购单WHERE供货方ISNOTNULL2.简单条件查询2.简单条件查询

(5)使用[NOT]IN表达式的查询用来确定给定的列或表达式的值是否与指定集合中的值匹配。格式为：

列名或表达式[NOT]IN（常量1,常量2,…常量n）2.简单条件查询

(5)使用[NOT]IN表达式的查询例：查询器件名称为“内存”或“鼠标”的器件信息。SELECT*FROM基础.器件WHERE

器件名称

IN('内存','鼠标’)或：SELECT*FROM基础.器件WHERE

器件名称='内存'OR器件名称='鼠标'以前怎么表示的?2.简单条件查询

(5)使用[NOT]IN表达式的查询例：查询器件名称既不是“内存”也不是“鼠标”的器件信息。SELECT*FROM基础.器件WHERE

器件名称NOTIN('内存','鼠标’)或：SELECT*FROM基础.器件WHERE

器件名称!='内存'AND器件名称!='鼠标'等价的表达式?3.存储查询结果例：查询职工E3经手的订购单的订购单号、供货方和订购日期信息，并将结果存储到“订货”模式下、表名为E3的表中。

SELECT订购单号,供货方,订购日期INTO

订货.E3FROM订货.订购单WHERE经手人='E3'使用INTO短语可以将查询结果存储到指定的新表中。4.查询结果的排序ORDERBYorder_expression[ASC|DESC]例：按单价升序列出所有器件信息。 SELECT*FROM基础.器件

ORDERBY单价SELECT*FROM订货.订购明细ORDERBY订购单号,金额DESC例题下列查询语句的含义是什么？5.TOP短语的作用例：从器件表中查询单价最高的3条器件信息。

SELECTTOP(3)*FROM基础.器件ORDERBY单价DESCTOP(expression)[PERCENT][WITHTIES]5.TOP短语的作用例：从订购明细表中查询订购数量最多的7条订购信息，如果有与第7条记录的数量并列的记录也一起列出。

SELECTTOP(7)WITHTIES*FROM订货.订购明细ORDERBY数量DESC小结简单查询基本格式：SELECT……FROM……WHERE……

连接查询

1.连接查询的语法格式4.自连接查询主要内容2.一般连接3.多个表的连接5.外连接查询1.连接查询的基本语法格式SELECT……FROM<table_source>[INNER|{LEFT|RIGHT|FULL}[OUTER]]JOIN<table_source>ON<joined_condition>[[INNER|{LEFT|RIGHT|FULL}[OUTER]]JOIN<table_source>ON<joined_condition>,n][WHERE<search_condition>]内连接查询的基本语法格式SELECT……FROM<table1>[INNER]JOIN<table2>ON<joined_condition> [[INNER]JOIN<table3>ON<joined_condition>,n][WHERE<search_condition>]2.一般连接例：查询工资多于1250元的职工的姓名、工资和他们所在的城市。SELECT姓名,工资,城市FROM基础.职工JOIN

仓储.仓库ON

职工.仓库号=仓库.仓库号WHERE工资>1250SELECT

姓名,工资,城市FROM

基础.职工

JOIN

仓储.仓库

ON

职工.仓库号=仓库.仓库号WHERE

工资

>1250一般连接举例例：查询在北京或者是上海工作的仓库号，职工号和姓名。SELECT

职工.仓库号,职工号,姓名FROM

基础.职工JOIN

仓储.仓库ON

职工.仓库号=仓库.仓库号WHERE

城市IN(‘北京’,’上海’)一般连接举例例：查询在北京或者是上海工作的仓库号，职工号和姓名。SELECTe.仓库号,职工号,姓名FROM

基础.职工eJOIN

仓储.仓库wONe.仓库号=w.仓库号WHERE

城市IN(‘北京’,’上海’)3.多个表的连接例：查询与华通电子公司有业务联系的职工号和姓名SELECT

职工号,姓名FROM

基础.职工

JOIN

订货.订购单

ON

职工号=经手人JOIN

订货.供应商ON供应商号=供货方WHERE供应商名='华通电子公司'自连接是指相互连接的表物理上为同一张表，但可以通过为表取别名的方法将其在逻辑上成为两张表。使用自连接时必须为表取不同的别名。注意连接条件的写法。4.自连接查询自连接查询举例查询结果王月领导张扬张扬领导陈虻张扬领导方林根据职工关系列出上一级领导及其职员(被其领导)的清单？自连接查询举例例：根据职工关系列出上一级领导及其职员(被其领导)的清单SELECT

领导.姓名,'领导',职员.姓名FROM

基础.职工

领导JOIN基础.职工

职员ON

领导.职工号=职员.班组长查询结果王月领导张扬张扬领导陈虻张扬领导方林5.外连接查询SELECT……FROM<table_source>{LEFT|RIGHT|FULL}[OUTER]JOIN<table_source>ON<joined_condition>[WHERE<search_condition>]外连接的一般语法格式左连接在结果表中包含第一个表中满足条件的所有记录；如果是在连接条件上匹配的元组，则第二个表返回相应值，否则第二个表返回空值。右连接在结果表中包含第二个表中满足条件的所有记录；如果是在连接条件上匹配的元组，则第一个表返回相应值，否则第一个表返回空值。全连接在结果表中包含两个表中满足条件的所有记录；如果是在连接条件上匹配的元组，则另一个表返回相应值，否则另一个表返回空值。外连接查询举例

分别用左连接、右连接和全连接查询订购单及其供应商信息，查询结果包括订购单号、订购日期、供应商名和供应商地址。问题SELECT订购单号,订购日期,供应商名,地址FROM

订货.订购单

JOIN

订货.供应商ON订购单.供货方=供应商.供应商号下列查询语句返回的结果是什么？查询结果是：订购单号

订购日期

供应商名

地址OR67

2011/06/23

爱华电子厂北京OR73

2011/07/28

华通电子公司北京OR76

2011/05/25华通电子公司北京OR79

2011/06/13华通电子公司北京问题查询订购单及其供应商信息，要求查询结果里包括没有确定供货方的订购单信息。（1）左连接查询示例SELECT订购单号,订购日期,供应商名,地址FROM订货.订购单

LEFT

JOIN

订货.供应商ON订购单.供货方=供应商.供应商号查询结果是：订购单号订购日期供应商名地址OR672011/06/23爱华电子厂北京OR732011/07/28华通电子公司北京OR762011/05/25华通电子公司北京OR77 NULLNULL NULLOR792011/06/13华通电子公司北京OR80 NULLNULL NULLOR90 NULLNULL NULL查询订购单及其供应商信息，要求查询结果里包括没有订单的供应商信息。（2）右连接查询示例54SELECT订购单号,订购日期,供应商名,地址FROM订货.订购单RIGHTJOIN订货.供应商ON订购单.供货方=供应商.供应商号查询结果是：订购单号订购日期供应商名地址OR672011/06/23爱华电子厂北京OR73

2011/07/28华通电子公司北京OR76

2011/05/25华通电子公司北京OR792011/06/13华通电子公司北京NULL NULL振华电子厂西安NULL NULL世纪金梦公司郑州（3）全连接查询示例问题查询订购单及其供应商信息，要求查询结果里既要包括没有订单的供应商信息，也要包括没有确定供货方的订购单信息。55SELECT订购单号,订购日期,供应商名,地址FROM订货.订购单FULLJOIN订货.供应商ON订购单.供货方=供应商.供应商号查询结果是：订购单号订购日期供应商名地址OR672011/06/23爱华电子厂北京OR732011/07/28华通电子公司北京OR762011/05/25华通电子公司北京OR77 NULLNULL NULLOR792011/06/13华通电子公司北京OR80 NULLNULL NULLOR90 NULLNULL NULLNULL NULL振华电子厂西安NULL NULL世纪金梦公司郑州分组及汇总查询

1.聚合函数与汇总查询

2.一般汇总查询主要内容

3.带明细的汇总查询常用聚合函数1.聚合函数与汇总查询（1）聚合函数

聚合函数也称为组函数、聚集函数，其作用是对一组值进行计算并返回一个值。COUNT（计数）AVG（计算平均值）MIN（计算最小值）MAX（计算最大值）SUM（求和）CHECKSUM（校验和）STDEV（计算标准差）……1.聚合函数与汇总查询（2）汇总查询一般汇总查询带明细的汇总查询使用GROUPBY的分组汇总查询使用COMPUTEBY的分组汇总查询使用COMPUTEBY和COMPUTE的汇总查询2.一般汇总查询SELECT<聚合函数>(expression)}[,...n]FROMtable_source[WHEREsearch_condition]基本语法格式一般汇总查询举例例：找出供应商所在地的数目。SELECTCOUNT(DISTINCT地址)FROM订货.供应商3一般汇总查询举例例：求支付的工资总数和职工人数，以及所有职工的平均工资、最高工资和最低工资。

SELECTSUM(工资)总工资,COUNT(*)人数,AVG(工资)平均工资,MAX(工资)最高工资,MIN(工资)最低工资FROM基础.职工例：求北京和上海的仓库职工的工资总和。SELECTSUM(工资)总工资FROM

基础.职工JOIN仓储.仓库ON

职工.仓库号=仓库.仓库号WHERE城市='北京'OR城市='上海'一般汇总查询举例COUNT(*)与COUNT(<列名>)有什么区别?SELECTCOUNT(*)职工人数,COUNT(班组长)班组长数FROM

基础.职工职工人数班组长数53注意：在聚合函数遇到空值时，除count(*)外，其它都跳过空值而只处理非空值。3.带明细的汇总查询SELECTselect_listFROMtable_source[WHEREsearch_condition]COMPUTE<聚合函数>(expression)}[,...n]基本语法格式带明细的汇总查询举例

列出供应商的地址，并计算出供应商所在地的数目。SELECTDISTINCT地址FROM订货.供应商COMPUTECOUNT(地址)注意：在COMPUTE的聚合函数中不能使用DISTINCT。列出北京和上海仓库的职工姓名、工资、所在城市信息，并计算他们的职工人数、工资总和以及平均工资。SELECT姓名,工资,城市FROM

基础.职工JOIN

仓储.仓库ON职工.仓库号=仓库.仓库号WHERE

城市='北京'OR城市='上海'COMPUTECOUNT(姓名),SUM(工资),AVG(工资)带明细的汇总查询举例注意1.SQL规定，当使用聚合函数时，列名不能与聚合函数一起使用（除非它们出现在其他聚合函数中,或者是分组依据列）。例：查询工资最高的职工姓名和工资，如下写法是错误的：

SELECT姓名,MAX(工资)FROM

基础.职工2.聚集函数不能出现在WHERE短语中例：查询工资最高的职工姓名，如下写法是错误的：

SELECT姓名FROM

基础.职工

WHERE工资=MAX(工资)3.使用GROUPBY的分组汇总查询SELECTgroup_id,<聚合函数>(expression)}[,...n]FROMtable_source[WHEREsearch_condition]GROUPBYgroup_id[HAVINGsearch_condition]说明：group_id为分组依据列，可以是单列，也可以是多个列SELECT子句中的每个列要么是分组依据列，要么是聚合函数基本语法格式：使用GROUPBY的分组汇总查询举例例：查询每个仓库的职工人数和平均工资。SELECT

仓库号,COUNT(*)人数,AVG(工资)平均工资FROM

基础.职工GROUPBY仓库号使用GROUPBY的分组汇总查询举例例：查询每个仓库工资相同的职工人数，结果按仓库号降序排序。SELECT

仓库号,工资,COUNT(*)人数FROM

基础.职工GROUPBY仓库号,工资ORDERBY仓库号

DESC使用GROUPBY+HAVING的分组汇总查询举例例求至少有4名职工的每个仓库的职工人数和平均工资。使用GROUPBY+HAVING的分组汇总查询举例例：求至少有4名职工的每个仓库的职工人数和平均工资。SELECT

仓库号,COUNT(*)人数,AVG(工资)平均工资FROM

基础.职工GROUPBY仓库号HAVINGCOUNT(*)>=4使用GROUPBY+HAVING的分组汇总查询举例例：查询至少有2名职工的工资大于1250的每个仓库的职工人数和平均工资。

SELECT仓库号,COUNT(*)人数,AVG(工资)平均工资

FROM基础.职工

WHERE

工资>1250

GROUPBY仓库号

HAVINGCOUNT(*)>=2使用GROUPBY+HAVING的分组汇总查询举例SELECT仓库号,COUNT(*)人数,AVG(工资)平均工资

FROM基础.职工

WHERE工资>1250GROUPBY仓库号

HAVINGCOUNT(*)>=2举例说明WHERE短语作用于基本表或视图，从中选择满足条件的元组。注意只有满足HAVING短语指定条件的组才输出HAVING短语与WHERE短语的区别是作用对象不同HAVING短语作用于组，从中选择满足条件的组。SELECTselect_listFROMtable_source[WHEREsearch_condition]ORDERBYorder_expression[ASC|DESC]COMPUTE

<聚合函数>(expression)}[,...n]BYexpression4.使用COMPUTEBY的分组汇总查询ORDERBY的必要性IfORDERBY子句是：ORDERBYa,b,c则COMPUTE…BY子句可以是如下三种形式： COMPUTE…BYa,b,c COMPUTE…BYa,b COMPUTE…BYa使用COMPUTEBY的分组汇总查询举例

列出职工全部记录并计算各仓库的平均工资和工资小计。SELECT*FROM基础.职工ORDERBY仓库号COMPUTEAVG(工资),SUM(工资)BY

仓库号5.使用COMPUTEBy和COMPUTE的汇总查询

一般格式：SELECTselect_listFROMtable_source[WHEREsearch_condition]ORDERBYorder_expression[ASC|DESC]COMPUTE<聚合函数>(expression)}[,...n]BYexpressionCOMPUTE<聚合函数>(expression)}[,...n]列出职工全部记录并计算各仓库的平均工资和工资小计，最后给出全体职工的平均工资和工资总和。SELECT*FROM基础.职工ORDERBY仓库号COMPUTEAVG(工资),SUM(工资)BY

仓库号COMPUTEAVG(工资),SUM(工资)使用COMPUTEBY和COMPUTE的汇总查询举例嵌套查询嵌套查询嵌入的查询语句(子查询)要使用圆括号括起来。如果一个SELECT查询嵌套在一个SELECT、INSERT、UPDATE、DELETE语句中,则称为嵌套查询或子查询;而包含子查询的语句称为外层查询。主要内容1.普通嵌套查询2.使用量词的嵌套查询

3.

内、外层互相关嵌套查询4.使用EXISTS的嵌套查询1.

普通嵌套查询(1)

WHERE[<其他查询条件>AND]<expression1>[NOT]IN (SELECT<expression2>FROM<table_source>[WHERE…])(2)

WHERE[<其他查询条件>AND]<expression1><比较运算符> (SELECT<expression2>FROM<table_source>[WHERE…])说明：当能确切知道子查询返回的是单值时，才可以使用比较运算符（>、<、=、>=、<=、<>）。两种形式使用普通嵌套查询举例例：查询哪些城市至少有一个仓库的职工的工资为1250元？

SELECT

城市FROM

仓储.仓库WHERE

仓库号IN

(SELECT

仓库号FROM

基础.职工

WHERE工资=1250)使用普通嵌套查询举例例：查询哪些城市至少有一个仓库的职工的工资为1250元？

SELECT

城市FROM

仓储.仓库WHERE

仓库号IN(SELECT

仓库号FROM

基础.职工

WHERE工资=1250)也可以用连接查询：SELECT

城市

FROM

仓储.仓库JOIN

基础.职工ON

仓库.仓库号=职工.仓库号WHERE工资=1250使用普通嵌套查询举例例：查询哪些仓库目前还没有职工。SELECT*FROM仓储.仓库WHERE仓库号NOTIN (SELECT

仓库号

FROM

基础.职工)可以用连接查询吗?使用普通嵌套查询举例例：找出哪些城市的仓库向北京的供应商发出了订购单。SELECT

城市FROM

仓储.仓库WHERE

仓库号IN(SELECT仓库号FROM基础.职工

WHERE

职工号IN(SELECT经手人FROM

订货.订购单

WHERE

供货方IN(SELECT供应商号FROM

订货.供应商

WHERE

地址=‘北京’)))2.

使用量词的嵌套查询<表达式><比较运算符>[ANY|ALL|SOME](子查询)使用量词的嵌套查询举例

查询有职工的工资大于或等于WH1仓库中任何一名职工的工资的仓库号。SELECTDISTINCT仓库号FROM基础.职工WHERE工资>=ANY

(SELECT工资FROM基础.职工WHERE仓库号='WH1')等价?使用量词的嵌套查询举例

查询有职工的工资大于或等于WH1仓库中任何一名职工的工资的仓库号。SELECTDISTINCT仓库号FROM基础.职工WHERE工资>=ALL

(SELECT工资FROM基础.职工WHERE仓库号='WH1')等价?3.

内、外层互相关嵌套查询一般的嵌套查询都是外层查询依赖于内层查询的结果，而内层查询与外层查询无关。事实上，有时也需要内、外层互相关的查询，即内层查询需要外层查询提供数据，而外层查询又依赖内层查询的结果。使用内、外层互相关嵌套查询举例例：列出每个职工经手的具有最高金额的订购单信息。SELECT

订购单号,经手人,供货方,订购日期,金额,max(金额)FROM

订货.订购单GROUPBY经手人使用内、外层互相关嵌套查询举例例：列出每个职工经手的具有最高金额的订购单信息。SELECT*FROM订货.订购单outaWHERE金额=(SELECTMAX(金额) FROM订货.订购单innera WHEREinnera.经手人=outa.经手人)4.使用EXISTS的嵌套查询[NOT]EXISTS(子查询)一般格式注意：EXISTS或NOTEXISTS是用来检查在子查询中是否有结果返回（即存在元组或不存在元组）。SELECT…FROM…WHERE[NOT]EXISTS(SELECT…FROM…WHERE…)使用EXISTS的嵌套查询举例例：查询目前没有经手订购单的职工信息。SELECT*FROM基础.职工WHERENOTEXISTS

(SELECT*FROM订货.订购单

WHERE经手人=职工.职工号)等价语句：SELECT*FROM基础.职工WHERE职工号

NOTIN

(SELECT经手人FROM订货.订购单)使用EXISTS的嵌套查询举例例：查询那些目前至少已经经手了1张订购单的职工信息。SELECT*FROM基础.职工WHEREEXISTS(SELECT*FROM订货.订购单

WHERE经手人=职工.职工号)等价语句：SELECT*FROM基础.职工WHERE职工号IN

(SELECT

经手人

FROM

订货.订购单)视图及其操作视图及其操作123视图的概念视图的定义视图的应用4视图的修改和删除5视图的作用视图及其操作123视图的概念视图的定义视图的应用4视图的修改和删除5视图的作用视图的概念视图是从基本表派生出来的虚拟关系、或虚拟表，它并不存储任何物理数据；在数据库中只存放视图的定义。同真实的表一样，视图包含一系列带有名称的列和数据行，其内容由查询定义。从用户的角度，视图和基本表都是关系；对视图的所有操作实际上都要转换成对基本表的操作。101视图及其操作123视图的概念视图的定义视图的应用4视图的修改和删除5视图的作用视图的定义103视图是根据对表的查询定义的，其命令格式如下：CREATEVIEW[<模式名>.]<视图名>[(<列名>[,<列名>……])]AS<SELECT-查询块>[WITHCHECKOPTION]WITHCHECKOPTION的作用：对通过视图操作的数据是否满足定义视图时的条件做检查。示例104（1）定义单源表视图例：定义视图emp_v1，使之只包含职工表的职工号、仓库号和姓名字段。CREATEVIEWemp_v1ASSELECT仓库号,职工号,姓名FROM基础.职工示例105（2）定义多源表视图例：定义视图wh_emp_v1，使之包含仓库号、城市、职工号和姓名信息。

CREATEVIEWwh_emp_v1ASSELECT仓库.仓库号,城市,职工号,姓名FROM基础.职工JOIN仓储.仓库ON职工.仓库号=仓库.仓库号示例106（3）包含虚列的视图例：根据职工表创建包含职工号、姓名、月工资和年工资4列信息的视图。CREATEVIEWv_sal(职工号,姓名,月工资,年工资)ASSELECT职工号,姓名,工资,工资*12FROM基础.职工也可以用如下语句创建：CREATEVIEWv_salASSELECT职工号,姓名,工资月工资,工资*12年工资FROM基础.职工107对通过视图操作的数据是否满足定义视图时的条件做检查。能成功执行吗?WITHCHECKOPTION的作用INSERTINTOwh_v1VALUES('WH9',777)例：定义视图wh_v1，使之只包含城市在北京的仓库号和面积信息。CREATEVIEWwh_v1ASSELECT仓库号,面积FROM仓储.仓库WHERE城市='北京’108例：定义视图wh_v2，使之只包含城市在北京的仓库号和面积信息，定义视图时使用WITHCHECKOPTION选项。CREATEVIEWwh_v2ASSELECT仓库号,面积FROM仓储.仓库WHERE城市='北京’WITHCHECKOPTIONINSERTINTOwh_v2VALUES('WH6',300)能成功执行吗?WITHCHECKOPTION的作用行列子集视图从单个基本表选取某些行和某些列、并且包含基本表中的关键字所定义的视图称作行列子集视图。行列子集视图不仅可以用于查询，原则上也可以进行各种更新操作。视图是虚拟表，所以对视图的所有操作实际上都要转换成对基本表的操作。109行列子集视图举例CREATEVIEWemp_v1ASSELECT职工号,仓库号,姓名FROM基础.职工110注意：对视图的操作会转换成对基本表的操作基于视图的操作：

SELECT*FROMemp_v1INSERTINTOemp_v1VALUES('E13','WH1','郭天华')是行列子集视图吗?视图及其操作123视图的概念视图的定义视图的应用4视图的修改和删除5视图的作用视图的应用视图一经定义就可以像基本表一样进行各种查询，但是要做增、删、改操作则必须要满足一些条件，例如：任何修改（包括UPDATE、INSERT和DELETE语句）都只能引用一个基表的列；视图中被修改的列必须直接引用表列中的基本数据，不能是通过任何其他方式定义的虚列等。112示例——基于视图的查询例：创建视图的语句如下：CREATEVIEWemp_v1ASSELECT仓库号,职工号,姓名

FROM基础.职工

SELECT*FROMemp_v1113示例——基于视图的增、删、改操作114例：创建视图的语句如下：CREATEVIEWemp_v1ASSELECT仓库号,职工号,姓名FROM基础.职工INSERTINTOemp_v1VALUES('WH1','E15','郭天’)UPDATEemp_v1SET姓名=‘张三’WHERE职工号=‘E9’DELETEFROMemp_v1WHERE职工号=‘E15’115基于多个表的视图能进行插、改和删操作吗?INSERTINTOwh_emp_v1VALUES('WH11','杭州','E13','海燕')

?INSERTINTOwh_emp_v1(仓库号,城市)VALUES('WH11','杭州')

?CREATEVIEWwh_emp_v1ASSELECT仓库.仓库号,城市,职工号,姓名FROM基础.职工JOIN仓储.仓库ON职工.仓库号=仓库.仓库号√（1）INSERTINTOv_salVALUES('E15','文文',1450,17400)?（2）INSERTINTOv_sal(职工号,姓名,月工资)VALUES('E15','文文',1450)?116计算列可以进行插入、修改该值的操作吗?CREATEVIEWv_sal(职工号,姓名,月工资,年工资)ASSELECT职工号,姓名,工资,工资*12FROM基础.职工注意：计算列只可查询、不可以给它赋值或修改其值。

√视图及其操作123视图的概念视图的定义视图的应用4视图的修改和删除5视图的作用视图的修改和删除(1)修改视图的命令ALTERVIEW[<模式名>.]<视图名>[(<列名>[,<列名>……])]AS<SELECT-查询块>[WITHCHECKOPTION](2)删除视图的命令 DROPVIEW[<模式名>.]<视图名>118视图及其操作123视图的概念视图的定义视图的应用4视图的修改和删除5视图的作用视图的作用

视图可用作安全机制着重于特定数据简化数据操作可以保证概念数据独立性120T-SQL数据库编程基础T-SQL简介游标与SQL的宿主使用存储过程触发器及其用途动态SQLT-SQL简介SQLServer使用的语言称作Transact-SQL(简称T-SQL)，它不仅包括基本SQL操作和查询的内容(第3章介绍的关系数据库标准语言SQL)，还有一般程序设计的能力。数据类型分类数据类型数值类型整型int,bigint,smallint,tinyint定点数字类型decimal(p,q),numeric(p,q)浮点数字类型float,real货币类型money,smallmoney日期和时间类型datetime,smalldatetime字符串类型ASCIIchar(n),

varchar(n),

textUnicodenchar(n),

nvarchar(n),

ntext二进制类型binary,varbinary,varbinary(max)图像类型image全局唯一标识符uniqueidentifierXML类型xml特殊bit,cursor,timestamp,sysname,table,sql_variant变量

SQLServer允许使用局部变量和全局变量，局部变量用DECLARE语句说明，而全局变量由系统预先定义和维护。局部变量说明DECLARE@<变量名><数据类型>[,@<变量名><数据类型>...]注意：@前缀全局变量全局变量是SQLServer预定义的，用于返回有关配置设置和系统运行的信息，全局变量以@@做前缀。例如，全局变量@@SERVERNAME返回当前服务器的名称，全局变量@@FETCH_STATUS返回当前读游标记录的状态信息。运算符与表达式T-SQL支持算术运算、位运算、比较运算、逻辑运算和字符串运算等常规运算。常量、变量、字段名或函数通过与运算符的有机结合可以构成各类表达式函数T-SQL提供了大量的函数供用户使用。常用函数名称格式功能系统日期函数GETDATE()返回当前系统日期年函数YEAR(日期表达式)返回年的四位整数。月函数MONTH(日期表达式)返回1～12之间的整数，表示一年的某月日函数DAY(日期表达式)返回值为1～31之间的整数，表示日期中的某一天。取子串函数SUBSTRING(字符串表达式,起始位置,长度)返回字符或表达式的一部分。截取左子串函数LEFT(字符串表达式，数值表达式)从“字符串表达式”左侧第1个字符开始，截取“数值表达式”值个字符。截取右子串函数RIGHT(字符串表达式，数值表达式)从“字符串表达式”的最右端的第1个字符开始，截取“数值表达式”值个字符类型转换函数CAST(表达式AS

数据类型)将某种数据类型的表达式显式转换为另一种数据类型。CAST和CONVERT提供相似的功能。CONVERT(数据类型[(长度)],表达式)赋值语句有SET和SELECT两种赋值语句可以给局部变量赋值。使用SET命令对变量赋值的语句格式是

SET@local_variable=expression使用SELECT命令对变量赋值的语句格式是SELECT@local_variable=expression[,@local_variable=expression…]或SELECT@local_variable=expression[,@local_variable=expression…]FROM<表名>|<视图名>…复合语句BEGIN…END条件语句IFBoolean_expression sql_statement[ELSE sql_statement]循环语句WHILEBoolean_expression sql_statement在循环体中可以使用BREAK语句强制跳出循环（转到循环语句后的语句），可以使用CONTINUE语句强制开始下一次循环（转到循环体的第一条语句）。WAITFOR语句WAITFOR语句通常用于事务控制，例如当已超过指定的时间间隔或到达一天中指定的时间再恢复程序的运行。例1：使程序延迟10秒执行WAITFORDELAY'00:00:10'例2：使程序等到14时20分再开始执行WAITFORTIME'14:20'PRINT语句PRINTmsg_str|@local_variable|string_exprmsg_str是字符串常量，@local_variable可以是任何有效的字符数据类型变量（或者能够隐式转换为字符数据类型的变量），string_expr是字符串表达式。只能输出一个字符串如何输出非字符串？如数值如何输出多个值？RAISERROR语句RAISERROR语句可以根据出错类型输出用户定义的出错信息。RETURN语句RETURN语句为返回语句，它无条件终止当前的查询、存储过程或批处理，返回调用程序。游标游标游标当前行指针游标结果集游标

游标可以看作是临时文件DECLARECURSOR——声明游标（文件）OPEN——打开游标（文件）FETCH——提取游标（文件）CLOSE——关闭游标（文件）DEALLOCATE

——释放游标（文件）声明用来存储数据的变量开始声明游标打开游标提取完毕？提取数据关闭游标释放游标结束否是例1在T-SQL中用游标列出所有仓库记录声明游标

游标可以看作是一个临时存储或临时文件，它的内容就是SELECT语句的查询结果。

基本格式

DECLARE<游标名>CURSORFOR<SELECT-查询>声明用来存储数据的变量开始声明游标打开游标提取完毕？提取数据关闭游标释放游标结束否是例1在T-SQL中用游标列出所有仓库记录DECLAREwh_cursorCURSORFORSELECT仓库号,城市,面积FROM仓储.仓库打开游标该游标必须是用DECLARECURSOR语句已经定义好的。执行该语句意味着执行在DECLARECURSOR语句中定义的SELECT查询，并使游标指针指向查询结果的第一条记录。只能打开已经声明但还没有打开的游标。

基本格式

OPEN<游标名>声明用来存储数据的变量开始声明游标打开游标提取完毕？提取数据关闭游标释放游标结束否是例1在T-SQL中用游标列出所有仓库记录DECLAREwh_cursorCURSORFORSELECT仓库号,城市,面积

FROM仓储.仓库OPENwh_cursor从游标中读记录

这里的游标必须是已经说明并打开了的，INTO后的主变量要与在DECLARECURSOR中SELECT的字段相对应。该语句的功能是取出游标的当前记录并送入主变量，同时使游标指针指向下一条记录。

基本格式

FETCH<游标名>[INTO<主变量1>,<主变量2>…]声明用来存储数据的变量开始声明游标打开游标提取完毕？提取数据关闭游标释放游标结束否是在T-SQL中使用游标使用全局变量@@FETCH_STATUS来判断FETCH语句对游标的操作状态，取值如下：0：FETCH语句成功；-1：FETCH语句失败或行不在结果集中；-2：提取的行不存在。循环语句控制是否继续读取游标WHILE@@FETCH_STATUS=0例1在T-SQL中用游标列出所有仓库记录DECLARE@whnoCHAR(6),@cityCHAR(10),@areaINTDECLAREwh_cursorCURSORFORSELECT仓库号,城市,面积FROM仓储.仓库OPENwh_cursorFETCHFROMwh_cursorINTO@whno,@city,@area例1在T-SQL中用游标列出所有仓库记录DECLARE@whnoCHAR(6),@cityCHAR(10),@areaINTDECLAREwh_cursorCURSORFORSELECT仓库号,城市,面积FROM仓储.仓库OPENwh_cursorFETCHFROMwh_cursorINTO@whno,@city,@areaWHILE@@FETCH_STATUS=0BEGIN FETCHFROMwh_cursorINTO@whno,@city,@areaEND例1在T-SQL中用游标列出所有仓库记录DECLARE@whnoCHAR(6),@cityCHAR(10),@areaINTDECLAREwh_cursorCURSORFORSELECT仓库号,城市,面积FROM仓储.仓库OPENwh_cursorFETCHFROMwh_cursorINTO@whno,@city,@areaWHILE@@FETCH_STATUS=0BEGIN FETCHFROMwh_cursorINTO@whno,@city,@areaEND例1在T-SQL中用游标列出所有仓库记录DECLARE@whnoCHAR(6),@cityCHAR(10),@areaINTDECLAREwh_cursorCURSORFORSELECT仓库号,城市,面积FROM仓储.仓库OPENwh_cursorFETCHFROMwh_cursorINTO@whno,@city,@areaWHILE@@FETCH_STATUS=0BEGIN FETCHFROMwh_cursorINTO@whno,@city,@areaEND例1在T-SQL中用游标列出所有仓库记录DECLARE@whnoCHAR(6),@cityCHAR(10),@areaINTDECLAREwh_cursorCURSORFORSELECT仓库号,城市,面积FROM仓储.仓库OPENwh_cursorFETCHFROMwh_cursorINTO@whno,@city,@areaWHILE@@FETCH_STATUS=0BEGIN FETCHFROMwh_cursorINTO@whno,@city,@areaEND例1在T-SQL中用游标列出所有仓库记录DECLARE@whnoCHAR(6),@cityCHAR(10),@areaINTDECLAREwh_cursorCURSORFORSELECT仓库号,城市,面积FROM仓储.仓库OPENwh_cursorPRINT'--------仓库列表--------'PRINT'仓库号所在城市仓库面积'FETCHFROMwh_cursorINTO@whno,@city,@areaWHILE@@FETCH_STATUS=0BEGIN PRINT@whno+''+@city+STR(@area,4) FETCHFROMwh_cursorINTO@whno,@city,@areaEND关闭游标

在使用CLOSE语句关闭某游标后，系统并没有完全释放游标的资源，并且也没有改变游标的定义，当再次使用OPEN语句时可以重新打开此游标。

基本格式

CLOSE<游标名>声明用来存储数据的变量开始声明游标打开游标提取完毕？提取数据关闭游标释放游标结束否是例1在T-SQL中用游标列出所有仓库记录DECLARE@whnoCHAR(6),@cityCHAR(10),@areaINTDECLAREwh_cursorCURSORFORSELECT仓库号,城市,面积FROM仓储.仓库OPENwh_cursorPRINT'--------仓库列表--------'PRINT'仓库号所在城市仓库面积'FETCHFROMwh_cursorINTO@whno,@city,@areaWHILE@@FETCH_STATUS=0BEGIN PRINT@whno+''+@city+STR(@area,4) FETCHFROMwh_cursorINTO@whno,@city,@areaENDCLOSEwh_cursor释放游标

释放游标就释放了与该游标有关的一切资源，包括游标的声明，以后就不能再使用OPEN语句打开此游标了。

基本格式DEALLOCATE<游标名>声明用来存储数据的变量开始声明游标打开游标提取完毕？提取数据关闭游标释放游标结束否是例1在T-SQL中用游标列出所有仓库记录DECLARE@whnoCHAR(6),@cityCHAR(10),@areaINTDECLAREwh_cursorCURSORFORSELECT仓库号,城市,面积FROM仓储.仓库OPENwh_cursorPRINT'--------仓库列表--------'PRINT'仓库号所在城市仓库面积'FETCHFROMwh_cursorINTO@whno,@city,@areaWHILE@@FETCH_STATUS=0BEGIN PRINT@whno+''+@city+STR(@area,4) FETCHFROMwh_cursorINTO@whno,@city,@areaENDCLOSEwh_cursorDEALLOCATEwh_cursor游标概念归纳与程序设计语言中的文件相对照DECLARECURSOR相当于说明了一个文件OPEN相当于打开文件FETCH相当于读一条记录CLOSE相当于关闭文件DEALLOCATE相当于删除文件。例2：在T-SQL中使用游标列出每个仓库及其职工的工资信息。游标可以嵌套例2：在T-SQL中使用游标列出每个仓库及其职工的信息。游标可以嵌套游标可以嵌套DECLARE@whnoCHAR(6),@cityCHAR(10)DECLARE@enoCHAR(8),@salarynumeric(8,2)DECLAREwh_cursorCURSORFORSELECT仓库号,城市FROM仓储.仓库--说明游标wh_cursorOPENwh_cursor--打开游标wh_cursorFETCHFROMwh_cursorINTO@whno,@city--从游标读数据WHILE@@FETCH_STATUS=0--用@@FETCH_STATUS判断FETCH语句是否成功BEGINPRINT'仓库：'+@whno+'位于'+@city+'有职工‘--*************************************DECLAREemp_cursorCURSORFORSELECT职工号,工资FROM基础.职工WHERE仓库号=@whno--说明嵌套的内层游标emp_cursorOPENemp_cursor--打开游标emp_cursorFETCHFROMemp_cursorINTO@eno,@salary--从emp_cursor游标读数据WHILE@@FETCH_STATUS=0--用@@FETCH_STATUS判断FETCH语句是否成功BEGINPRINT@eno+'的工资是'+str(@salary,8,2)FETCHFROMemp_cursorINTO@eno,@salary--读下一条职工信息ENDCLOSEemp_cursor--关闭内层游标emp_cursorDEALLOCATEemp_cursor--释放内层游标emp_cursor--**************************************FETCHFROMwh_cursorINTO@whno,@city--读下一条仓库信息（外层游标）ENDCLOSEwh_cursor--关闭游标wh_cursorDEALLOCATEwh_cursor--释放游标wh_cursor游标可以嵌套DECLARE@whnoCHAR(6),@cityCHAR(10)DECLARE@enoCHAR(8),@salarynumeric(8,2)DECLAREwh_cursorCURSORFORSELECT仓库号,城市FROM仓储