实验三SQL查询与更新实验SQL 3学时Word文档下载推荐.docx
《实验三SQL查询与更新实验SQL 3学时Word文档下载推荐.docx》由会员分享，可在线阅读，更多相关《实验三SQL查询与更新实验SQL 3学时Word文档下载推荐.docx（18页珍藏版）》请在冰豆网上搜索。
说明：
在SELECT语句中SELECT子句与FROM子句是不可少的，其余的是可选的。
2.基本的SELECT语句
SELECT语句的基本形式如下：
SELECT<
FROM<
表名列表>
[WHERE查询条件]
（1）选择表中若干列
【练习3.1】
USEJXGL
GO
SELECT学号,姓名
FROMxs
（2）查询表中全部列（*）
将表中所有属性都选出来，可以有两种方法。
一种方法是在SELECT命令后面列出所有列名。
如果列的显示顺序与其在基表中的顺序相同，也可以简单地将<
简写为“*”。
【练习3.2】
GO
SELECT*FROMkc
3．设置字段别名
T-SQL提供了在SELECT语句中操作别名的方法。
用户可以根据实际需要对查询数据的列标题进行修改，或者为没有标题的列加上临时标题。
其语法格式为：
①列表达式AS别名②列表达式别名③别名=列表达式
【练习3.3】查询JXGL数据库的kc表，列出表中的所有记录，每个记录名称依次为课程编号，课程名称，课程学分及课程时数。
SELECT课程号AS课程编号,课程名课程名称,学分课程学分,学时数AS课程时数
FROMkc
4．查询经过计算的值
SELECT子句的<
不仅可以是表中的属性列，也可以是表达式，包括字符串常量、函数等。
其语法格式为：
计算字段名=表达式
【练习3.4】查询所有学生的学号，姓名及年龄。
SELECT学号,姓名,年龄=DATEDIFF（YY,出生时间,GETDATE（））
5．返回全部记录（ALL）
要返回所有记录可在SELECT后使用ALL，ALL是默认设置，因此也可以省略。
【练习3.5】查询XS表中所有学生专业。
SELECT专业
6．过滤重复记录（DISTINCT）
在练习3.5的执行结果集中显示重复行。
如果让重复行只显示一次，需在SELECT子句中用DISTINCT指定在结果集中只能显示唯一行。
【练习3.6】查询xs表中的学生所在专业有哪些。
（重复专业只显示一次）。
SELECTDISTINCT专业
7．仅返回前面若干条记录
其语法格式如下：
SELECT[TOPn︱TOPnPERCENT]列名l[，．．．n]
FROM表名
其中：
TOPn表示返回最前面的n行，n表示返回的行数；
TOPnPERCENT表示返回的最前面的n％行。
【练习3.7】查询xs表中前5条记录
SELECTTOP5*
【练习3.8】查询XS表中前面10％行记录
SELECTTOP10PERCENT*
8.使用INTO子句
使用INTO子句允许用户定义一个新表，并且把SELECT子句的数据插入到新表中，其语法格式如下：
INTO新表名
WHERE查询条件
使用INTO子句插入数据时，应注意以下几点：
（1）新表不能存在，否则会产生错误信息。
（2）新表中的列和行是基于查询结果集的。
（3）使用该子句必须在目的数据库中具有CREATETABLE权限。
（4）如果新表名称的开头为“#”，则生成的是临时表。
注意：
使用INTO子句，通过在WHERE子句中FALSE条件，可以创建一个和源表结构相同的空表。
【练习3.9】创建一个和xs表结构相同的xs_new表。
SELECT*INTOxs_new
WHERE6>
8
设置“WHERE6>
8”这样一个明显为逻辑否的条件的目的是为了只保留xs表的结构，而不返回任何记录。
【练习3.10】查询所有女生的信息并将结果保存在名为“女生表”的数据表中。
SELECT*INTO女生表
WHERE性别='
女'
【练习3.11】查询所有男生的信息并将结果存入临时表中。
SELECT*
INTO#TEMPDB
男'
要查看临时表的内容可用下面的语句：
SELECT*FROM#TEMPDB
9.使用WHERE子句
SELECT列名1[,…列名n]
WHERE条件表达式
使用WHERE子句可以限制查询的记录范围。
在使用时，WHERE子句必须紧跟在FROM子句后面。
WHERE子句中的条件是—个逻辑表达式。
（1）比较表达式作查询条件
比较表达式是逻辑表达式的一种，使用比较表达式作为查询条件的一般表达形式是：
表达式比较运算符表达式
其中，表达式为常量、变量和列表达式的任意有效组合。
比较运算符包括：
=（等于）、<
（小于）、>
（大于）、<
>
（不等于）、!
（不大于）、!
<
（不小于）、>
=（大于等于）、<
=（小于等于）、!
=（不等于）。
【练习3.12】查询年龄在23岁以下的学生。
SELECT姓名,性别,年龄=DATEDIFF（YEAR,出生时间,GETDATE（））
WHEREDATEDIFF（YEAR,出生时间,GETDATE（））<
=23
（2）逻辑表达式作查询条件
使用逻辑表达式作为查询条件的一般表达形式是：
表达式1AND|OR表达式2，或NOT表达式
【练习3.13】查询年龄为23岁且性别为“女”的学生。
WHEREDATEDIFF（YEAR,出生时间,GETDATE（））=23AND性别='
（3）使用（NOT）BETWEEN…AND关键字
其语法格式为：
表达式[NOT]BETWEEN表达式lAND表达式2
谓词可以用来查找属性值在（或不在）指定范围内的元组，其中BETWEEN后是范围的下限（即低值），AND后是范围的上限（即高值）。
使用BETWEEN限制查询数据范围时同时包括了边界值，而使用NOTBETWEEN进行查询时没有包括边界值。
【练习3.14】查询年龄在20到22之间的女学生的学号，姓名，年龄。
WHEREDATEDIFF（YEAR,出生时间,GETDATE（））BETWEEN20AND22AND性别='
（4）使用IN关键字
同BETWEEN关键字一样，IN的引入也是为了更方便地限制检索数据的范围，灵活使用IN关键字，可以用简洁的语句实现结构复杂的查询。
语法格式为：
表达式[NOT]IN（表达式1,表达式2[,…表达式n]）
如果“表达式”的值是谓词IN后面括号中列出的表达式1，表达式2,…表达式n的值之一，则条件为真。
【练习3.15】查询选修了“a003”和“a008”的学生的学号。
SELECTDISTINCT学号
FROMcj
WHERE课程号IN（'
a003'
'
a008'
）
（5）使用LIKE关键字
语法格式：
表达式[NOT]LIKE<
匹配串>
其含义是查找指定的属性列值与<
相匹配的元组。
可以是一个完整的字符串，也可以含有通配符。
SQLServer提供了以下4种通配符供用户灵活实现复杂的查询条件。
％（百分号）：
表示从0到n个任意字符。
_（下划线）：
表示单个的任意字符。
[]（封闭方括号）：
表示方括号里列出的任意一个字符。
[^]：
任意一个没有在方括号里列出的字符。
需要注意的是，以上所有通配符都只有在LIKE子句中才有意义，否则通配符会被当作普通字符处理。
【练习3.16】查询姓“张”学生的学号及姓名
SELECT学号,姓名
WHERE姓名LIKE'
张%'
注意：
通配符和字符串必须括在单引号中。
要查找通配符本身时，需将它们用方括号括起来。
例如：
LIKE'
[[]'
表示要匹配“[”。
（6）涉及空值的查询
对于空值（NULL）要用IS进行连接，不能用“=”代替。
【练习3.17】查询选修了课程却没有成绩的学生的学号。
SELECT学号
WHERE成绩ISNULL
10.使用ORDERBY子句
对查询的结果进行排序，通过使用ORDERBY子句实现。
语法格式如下：
ORDERBY表达式1[ASC︱DESC][,…n]]
其中，表达式给出排序依据，即按照表达式的值升序（ASC）或降序（DESC）排列查询结果。
在默认的情况下，ORDERBY按升序进行排列，即默认使用的是ASC关键字。
不能按ntext、text或image类型的列排序，因此ntext、text或image类型的列不允许出现在ORDERBY子句中。
【练习3.18】按年龄从小到大的顺序显示女学生的姓名，性别及出生时间。
SELECT姓名,性别,出生时间
ORDERBY出生时间DESC
3.3.2SELECT语句的集合函数
为了进一步方便用户，增强检索功能，SELECT语句中的统计功能可以对查询结果集进行求和、求平均值、求最大最小值等操作。
统计的方法是通过集合函数和GROUPBY子句、COMPUTE子句进行组合来实现。
1.使用集合函数
汇总查询是把存储在数据库中的数据作为一个整体，对查询结果得到的数据集合进行汇总或求平均值等各种运算。
SQLServer提供了一系列统计函数，用于实现汇总查询。
【练习3.19】查询学生总人数
SELECT学生总人数=COUNT（*）
2.使用DISTINCT短语
如果指定DISTINCT短语，则表示在计算时要取消指定列中的重复值。
如果不指定DISTINCT短语或指定ALL短语（ALL为缺省值），则表示不取消重复值。
【练习3.20】查询选修了“a003”课程的学生人数。
SELECT选课人数=COUNT（DISTINCT学号）
WHERE课程号='
3.使用MAX（）函数
【练习3.21】查询选修了“a003”号课程的学生最高分数。
SELECTa003课程最高分=MAX（成绩）
3.3.3使用GROUPBY子句
其语法格式如下。
GROUPBY列名
[HAVING条件表达式]
HAVING条件表达式选项是对生成的组进行筛选。
【练习3.22】在xs表中分专业统计出男生和女生的平均年龄及人数，结果按性别排序。
SELECT专业,性别,
AVG（DATEDIFF（YEAR,出生时间,GETDATE（）））AS平均年龄,
COUNT（*）AS人数
GROUPBY专业,性别
ORDERBY性别
【练习3.23】查询cj表中平均成绩大于80分的学生的学号、平均分，并按分数由高到低排序。
SELECT学号,AVG（成绩）AS平均成绩
GROUPBY学号
HAVINGAVG（成绩）>
=80
ORDERBYAVG（成绩）DESC
WHERE子句是对表中的记录进行筛选，而HAVING子句是对组内的记录进行筛选，在HAVING子句中可以使用集合函数，并且其统计运算的集合是组内的所有列值，而WHERE子句中不能使用集合函数。
3.3.4*使用COMPUTE子句
使用COMPUTEBY子句，它对BY后面给出的列进行分组显示，并计算该列的分组小计。
语法格式如下：
COMPUTE集合函数[BY分类表达式]
（1）COMPUTE或COMPUTEBY子句中的表达式，必须出现在选择列表中，并且必须将其指定为与选择列表中的某个表达式完全一样，不能使用在选择列表中指定的列的别名。
（2）在COMPUTE或COMPUTEBY子句中，不能指定为ntext、text和image数据类型。
（3）如果使用COMPUTEBY，则必须也使用ORDERBY子句。
表达式必须与在ORDERBY后列出的子句相同或是其子集，并且必须按相同的序列。
例如，如果ORDERBY子句是：
ORDERBYa,b,c则COMPUTE子句可以是下面的任意一个（或全部），即：
COMPUTEBYa,b,c
COMPUTEBYa,b
COMPUTEBYa
（4）在SELECTINTO语句中不能使用COMPUTE。
因此，任何由COMPUTE生成的计算结果不出现在用SELECTINTO语句创建的新表内。
【练习3.24】在cj表中求每门课程成绩大于80分的人数，并显示统计的明细。
WHERE成绩>
=80
ORDERBY课程号
COMPUTECOUNT（课程号）BY课程号
3.3.5SELECT语句中的多表连接
在SQLServer中连接查询类型分为交叉连接、内连接、外连接、自连接。
连接查询就是关系运算的连接运算，它是从多个数据源间（FORM）查询满足—定条件的记录。
1.交叉连接
交叉连接也叫非限制连接，它是将两个表不加任何约束地组合起来。
也就是将第一个表的所有行分别与第二个表的每行形成一条新的记录，连接后该结果集的行数等于两个表的行数积，列数等于两个表的列数和。
在数学上，就是两个表的笛卡尔积，在实际应用中一般是没有意义的，但在数据库的数学模式上有重要的作用。
其语法结构如下：
SELECT列名列表FROM表名1CROSSJOIN表名2或者SELECT列名列表FROM表名1,表名2
【练习3.25】在xs、cj表中实现交叉连接。
SELECTxs.学号,姓名,课程号,成绩
FROMxsCROSSJOINcj
2.内连接
内连接也叫自然连接，它是组合两个表的常用方法。
内连接就是只包含满足连接条件的数据行，是将交叉连接结果集按照连接条件进行过滤的结果。
连接条件通常采用“主键=外键”的形式。
内连接有以下两种语法格式。
SELECT列名列表FROM表名1[INNER]JOIN表名2ON表名1.列名=表名2.列名或SELECT列名列表FROM表名1,表名2WHERE表名1.列名=表名2.列名
【练习3.25】查xs、cj表内连接值。
FROMxsINNERJOINcjONxs.学号=cj.学号
3.自连接
连接操作不仅可以在不同的表上进行，也可以在同一张表内进行自身连接，即将同一个表的不同行连接起来。
自连接可以看作一张表的两个副本之间的连接。
表名在FROM子句中出现两次，必须对表指定不同的别名，在SELECT子句中引用的列名也要使用表的别名进行限定，使之在逻辑上成为两张表。
【练习3.25】在
SELECTc2.课程号,c2.成绩
FROMcjasc1,cjasc2wherec1.学号=c2.学号andc1.课程号='
4.合并结果集
合并查询也称联合查询是将两个或两个以上的查询结果合并，形成一个具有综合信息的查询结果。
使用UNION语句可以把两个或两个以上的查询结果集合并为一个结果集。
查询语句
UNION[ALL]
注意:
（1）联合查询是将两个表（结果集）顺序连接。
（2）UNION中的每一个查询所涉及到的列必须具有相同的列数、相同位置的列的数据类型要相同。
若长度不同，以最长的字段作为输出字段的长度。
（3）最后结果集中的列名来自第一个SELECT语句。
（4）最后一个SELECT查询可以带ORDERBY子句，对整个UNION操作
结果集起作用。
且只能用第一个SELECT查询中的字段作排序列。
（5）系统自动删除结果集中重复的记录，除非使用ALL关键字。
3.3.6子查询
在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。
将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为子查询。
子查询总是写在圆括号中，可以用在使用表达式的任何地方。
上层的查询块称为外层查询或父查询，下层查询块称为内查询或子查询。
SQL语言允许多层嵌套查询。
即一个子查询中还可以嵌套其他子查询。
子查询的SELECT语句中不能使用ORDERBY子句，ORDERBY子句只能对最终查询结果排序。
3.3.7嵌套子查询
嵌套子查询的执行不依赖于外部嵌套。
其一般的求解方法是由里向外处理。
即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。
1．比较测试中的子查询
比较测试中的子查询是指父查询与子查询之间用比较运算符进行连接。
但是用户必须确切地知道子查询返回的是一个单值，否则数据库服务器将报错。
返回的单个值被外部查询的比较操作（如：
=、!
=、<
、<
=、>
、>
=）使用，该值可以是子查询中使用集合函数得到的值。
【练习3.26】求选修了数字信号处理课程的学生的学号及姓名。
SELECTxs.学号,姓名,专业
FROMxs,cj
WHERExs.学号=cj.学号ANDcj.课程号=
（SELECT课程号
WHERE课程名='
数字信号处理'
【练习3.27】在xs表中查询和“张永强”在同一专业的所有男同学信息。
AND专业=
（SELECT专业
FROMxs
WHERE姓名='
张永强'
AND姓名<
'
2．集合成员测试中的子查询
集合成员测试中的子查询是指父查询与子查询之间用IN或NOTIN进行连接，判断某个属性列值是否在子查询的结果中，通常子查询的结果是一个集合。
IN表示属于，即外部查询中用于判断的表达式的值与子查询返回的值列表中的一个值相等；
NOTIN表示不属于。
【练习3.28】求选修了学分为4分的课程的学生的学号及姓名。
SELECTDISTINCTxs.学号,姓名,专业
WHERExs.学号=cj.学号ANDcj.课程号IN
（SELECT课程号FROMkcWHERE学分=4）
3．批量比较测试中的子查询
（1）使用ANY关键字的比较测试
通过比较运算符将一个表达式的值或列值与子查询返回的一列值中的每一个进行比较，只要有一次比较的结果为TRUE，则ANY测试返回TRUE。
（2）使用ALL关键字的比较测试
通过比较运算符将一个表达式的值或列值与子查询返回的一列值中的每一个进行比较，只要有一次比较的结果为FALSE，则ALL测试返回FALSE。
ANY和ALL都用于一个值与一组值的比较，以“>
”为例，ANY表示大于一组值中的任意一个，ALL表示大于一组值中的每一个。
比如，>
ANY（1,2,3）表示大于1；
而>
ALL（1,2,3）表示大于3。
【练习3.29】在kc表中查询学分大于最小学分的课程信息。
WHERE学分>
ANY
（SELECT学分FROMkc）
3.3.8*相关子查询
所谓相关子查询，是指在子查询中，子查询的查询条件中引用了外层查询表中的字段值。
相关子查询的结果集取决于
升级会员 