项目6 在教学评测系统数据库表中操作数据

1、项目6 在教学评测系统数据库中操作数据6.1 用户需求与分析 教学测评系统中，经常需要向系统中新增学生，这就需要用到Insert语句来完成。同时，也经常需要对数据进行汇总，例如，统计某一个教师一学期其所上班级学生对其评分的总分、平均分等，这就需要用到对单表、多表查询的知识。6.2相关知识-插入数据 1.插入单个元组。 插入单个元组的INSERT语句的格式为： INSERT INTO (，) VALUES(，) 其功能是将新元组插入指定表中。其中新记录属性列1的值为常量1，属性列2的值为常量2，。如果某些属性列在INTO子句中没有出现，则新记录在这些列上将取空值。 应当了解关于默认值和空值优先的

2、常规INSERT行为。如果遗漏了列表和数值表中的一列，那么当默认值存在时，该列会使用默认值。如果默认值不存在，SQL Server会尝试补上一个空值。如果列声明了NOT NULL，尝试空值会导致错误。在数值表中，如果明确指定了NULL，即使默认值存在，列仍会设置为NULL（假设它允许NULL）。当在一个允许NULL且没有声明默认值的列中使用DEFAULT占位符时，NULL会被插入到该列中。如果在一个声明NOT NULL且没有默认值的列中指定NULL或DEFAULT，或者完全省略了该值，将导致错误。6.2相关知识插入数据 2.插入子查询结果子查询不仅可以嵌套在SELECT语句中，用以构造父查询的

3、条件，也可以嵌套在INSERT语句中，用以生成要插入的数据。插入子查询结果的INSERT语句的格式为INSERT INTO (，) 子查询其功能是以批量插入。通过SELECT语句生成结果集，由INSERTSELECT语句把这些行添加到表中。通过INSERTSELECT语句把其他数据源的行添加到现有的表中。使用INSERTSELECT语句可比使用单行的INSERT语句效率要高得多6.2相关知识修改数据 1.更新指定元组更新数据的UPDATE语句的格式为：UPDATE table_name SET column_name = expression | DEFAULT | NULL WHERE 6.

4、2相关知识修改数据 2.更新所有元组可以不使用where子句指定元组，这时更新所有的元组6.2相关知识删除数据用户经常需要删除数据。如学生毕业了，需要将学生信息，学生课程信息等数据从数据库中删除。这就需要用到Delete语句。DELETE语句可以通过使用事务从表或视图中删除一行或多行。通过筛选目标表或者使用子查询指定SQL Server要删除的行。Delete语句的语法结构如下：DELETE FROM table_name WHERE 其中，table_name为要删除数据的表名称，search_condition为条件。6.2相关知识- SELECT语句建立数据库的目的是为了查询数据，因此，

5、可以说数据库查询是数据库的核心操作。在数据库中，数据查询是通过SELECT语句来完成的。SELECT语句的功能是从数据库中按用户要求检索数据，并将查询结果以表格的形式返回，其一般格式如下：SELECT ALL|DISTINCT ，FROM ，WHERE GROUP BYHAVINGORDER BYASC|DESC;6.2相关知识单表查询 选择表中的全部列或部分列，这类运算又称为投影。其变化方式主要表现在SELECT子句的上。语法格式如下：SELECT := *| table_name | view_name .*| column_name | expression ,.n 参数说明select

6、_list：是所要查询的表的列的集合，多个列之间用逗号分开。*：通配符，返回所有对象的所有列。table_name | view_name.*：限制通配符*的作用范围。凡是带*的项，均返回其中所有的列。column_name：指定返回的列名。Expression：表达式可以为列名、常量、函数或它们的组合。6.2相关知识单表查询 显示全部列将表中的所有属性列都选出来，可以有两种方法。一种方法就是在SELECT关键字后面列出所有列名。如果列的显示顺序与其在基表中的顺序相同，也可以简单的将指定为*。6.2相关知识单表查询 显示经过计算的列SELECT子句的不仅可以是表中的属性列，也可以是有关系表达式

7、，即可以将查询出来的属性列经过一定的计算后列出结果。SELECT StudentNO ,StudentName ,SubString(StudentNO,1,4) FROM TTS.dbo.TTS_Student6.2相关知识单表查询 显示字面值字面值包括字母、数字或在结果集中作为特殊值的符号。在选择列表中使用字面值，使结果集更具可读性。SELECT StudentNO As 学号 ,StudentName 姓名 ,SubString(StudentNO,1,4) As 年级 FROM TTS.dbo.TTS_Student6.2相关知识单表查询 使用比较运算符过滤数据使用比较运算符可以让表中

8、值与指定值或表达式作比较，也可以使用比较运算符来做条件检查。比较运算符用来比较兼容数据类型的列或变量。操作符操作符说明说明操作符操作符说明说明=等于=大于或等于大于或等于 大于=小于或等于小于或等于 小于小于 不等于不等于6.2相关知识单表查询 使用逻辑运算符过滤数据使用逻辑运算符AND、OR和NOT来连接一系列表达式并且简化处查询处理。查询结果将根据表达式的分组情况和选择条件的顺序的不同而有所不同。使用逻辑运算符要遵循以下规则：1.使用AND运算符选择满足所有选择条件的行。2.使用OR运算符选择满足任意选择条件的行。3.使用NOT运算符否定NOT后面的表达式。4.当有两个或更多的表达式作为选

9、择条件时，可以使用括号。使用括号可以： （1）将表达式分组 （2）改变求值的次序 （3）增加表达式的可读性5.当在一条语句中使用多个逻辑运算符时，需要考虑以下的事项： （1）SQL Server 2000将首先求NOT运算符的值，然后是AND运算符，最后求OR运算符的值。 （2）当一个表达式中的所有运算符具有相同的优先级时，则按照从左到右的顺序依次求值。6.2相关知识单表查询 使用字符串比较符过滤数据 使用通配符结合LIKE查询条件，通过进行字符串比较来查询符合条件的行。使用它Like关键字可以实现模糊插叙，例如，要查询姓名为“王”开头的学生或姓名中含有“王”的学生信息。使用Like关键字遵循

10、以下规则：1.模式字符串中的所有字符都有意义，包括开头与结尾的空格。2.LIKE只适用数据类型为char、nachar、varchar、nvarchar、binary、varbinary、smalldatetime或datetime的数据，以及特定条件下数据类型为text、next和image的数据。6.2相关知识单表查询 根据值的范围过滤在WHERE子句中，使用BETWEENAND和NOT BERWEENAND选择条件选择属性值在（或不在）指定范围内的行。其中BETWEEN后是范围的下限（即低值），AND后面是上限（即高值）。例如查询学号介于“20050001”和“20051000”之间的学

11、生信息。使用BETWEENAND或NOT BETWEENAND应该遵循以下规则：1.在结果集中，SQL Server包括边界值。2.为了简化语法，使用BETWEEN选择条件而不使用由AND运算符连接的两个比较运算符所组成的表达式（=x AND x AND y）。3.使用NOT BETWEENAND选择条件来选择指定范围外的行。注意，使用NOT条件会降低数据选择的速度。6.2相关知识单表查询 根据值的列表过滤在WHERE子句中使用IN选择条件选择与指定值列表相匹配的行。使用IN选择条件遵循以下规则：1.不管是使用IN选择条件还是使用由OR运算符连接的一系列比较表达式，SQL Server都用相同

12、的方式处理，返回完全相同的结果集。2.在选择条件中不要包含空值。3.使用NOT IN选择条件查询不在指定值列表中的行。注意使用NOT会降低数据选择的速度。6.2相关知识单表查询 涉及未知值的查询如果在数据输入过程中没有给某个列输入值并且没有给该列定义默认值，那么该列就存在一个空值。空值不等同于数值0或空字符。使用IS NULL查询条件来查询那些指定列中遗漏信息的行。使用它NULL应遵循以下规则：1.空值与任何值比较都会失败，因为空值不等于任何值。2.在CREATE TABLE语句中定义列是否允许有空值。3.使用IS NOT NULL查询条件来查询在指定列上具有已知值的行。6.2相关知识单表查询

13、 消除重复行两个本来并不完全相同的行，投影到指定的某些列上后，可能变成完全相同的行了。如果希望一个列表没有重复值，则可以使用DISTINCT子句来消除结果集中的重复行。语法格式为：SELECT DISTINCT 使用Distinct关键字遵循宜家规则：1.在结果集中，除非已经指定了DISTINCT子句，否则将返回所有符合SELECT语句中指定的查询条件的行。2.选择列表中所有值的组合决定其惟一性。3.查询包含任何惟一值组合的行，并且将这些行返回到结果集中。4.DISTINCT子句以随机的次序排列结果集，除非使用ORDER BY子句。5.如果指定DISTINCT子句，ORDER BY指定的列必须

14、出现在SELECT语句的选择列表中。6.对于DISTINCT关键字来说，各空值将被认为是相互重复的内容。当SELECT语句中包括DISTINCT时，不论遇到多少个空值，在结果中只返回一个NULL。DISTINCT关键字有一个与其对应的关键字ALL，无论选择列表中的值的组合惟一与否，它让SQL Server返回所有的行。由于ALL关键字是SELECT语句的默认行为，所以在一般情况下不使用ALL，但可以使用它来使查询语法更容易理解。6.2相关知识单表查询 对查询结果排序如果没有指定查询结果的显示顺序，SQL Server 2008将按其最方便的顺序（通常是行在表中的先后顺序）输出查询结果。用户也可

15、以用ORDER BY子句指定按照一个或多个属性列的升序或降序重新排列查询结果。语法格式如下： ORDER BY order_by_expression ASC | DESC ,.n 。6.2相关知识使用聚合函数 COUNT()函数COUNT()函数用于统计查询结果集中记录的个数。使用COUNT（）函数应该遵循以下规则：1.DISTINCT：指定COUNT返回惟一非空值的数量。2.expression：一个表达式，其类型是除uniqueidentifier、text、image或ntext之外的任何类型。不允许使用聚合函数和子查询。3.*：指定应该计算所有行以返回表中行的总数。COUNT(*)不

16、需要任何参数，而且不能与DISTINCT一起使用。COUNT(*)不需要expression参数，因为根据定义，该函数不使用有关任何特定列的信息。COUNT(*)返回指定表中行的数量而不消除副本。它对每行分别进行计数，包括含有空值的行。4.COUNT(*) 返回组中项目的数量，这些项目包括 NULL 值和副本。COUNT(ALL expression) 对组中的每一行都计算 expression 并返回非空值的数量。COUNT(DISTINCT expression) 对组中的每一行都计算 expression 并返回惟一非空值的数量。6.2相关知识使用聚合函数 MAX()函数MAX函数用于返

17、回指定列中的最大值。使用MAX（）函数遵循以下规则：1.DISTINCT：指定每个惟一值都被考虑。DISTINCT对于MAX无意义，使用它仅仅是为了符合SQL-92兼容性。2.expression：常量、列名、函数以及算术运算符、按位运算符和字符串运算符的任意组合。MAX可用于数字列、字符列和datetime列，但不能用于bit列。不允许使用聚合函数和子查询。6.2相关知识使用聚合函数 MIN()函数MIN（）函数用于返回指定列中的最小值。6.2相关知识使用聚合函数 SUM()函数SUM函数返回表达式中所有值的和，或只返回DISTINCT值。SUM只能用于数字列。空值将被忽略。使用SUM函数，

18、有两种形式，SUM（expression）或，SUM（DISTINCT expression），参数说明如下：DISTINCT：指定SUM返回惟一值的和。expression：是常量、列或函数，或者是算术、按位与字符串等运算符的任意组合。expression是精确数字或近似数字数据类型分类（bit数据类型除外）的表达式。不允许使用聚合函数和子查询。6.2相关知识使用聚合函数 AVG()函数AVG（）函数返回组中值的平均值，该组中的NULL值在计算过程中将被忽略。使用AVG函数遵循以下规则：1.DISTINCT：指定AVG操作只使用每个值的惟一实例，而不管该值出现了多少次。2.expressio

19、n：精确数字或近似数字数据类型类别的表达式（bit数据类型除外）。不允许使用聚合函数和子查询。6.2相关知识分组查询 分组查询在某些列或表达式中使用GROUP BY子句，把表分成组，并对组进行汇总。使用GROUP BY遵循以下规则：1.SQL Server为每一个定义的组生成一个列值。2.SQL Server只是为每一个指定的组返回单一的行，并不返回详细信息。3.所有在GROUP BY子句中指定的列必须包含在选择列表中。4.如果包含WHERE子句，则SQL Server只对满足WHERE子句条件的行进行分组。5.GROUP BY列的大小、聚合的列和包含在查询中的聚合的值限制了列项的数目。这个限

20、制来源与中间工作表每行最多只能有8060字节的限制，中间工作表用来存放中间查询结果。6.因为空值是作为一个组处理的，所以不要在包含多个空值的列中使用GROUP BY子句。7.使用ALL关键字与GROUP BY子句显示在聚合列中含有空值的所有行，而忽略这些记录是否满足WHERE子句。6.2相关知识分组查询 筛选分组结果对列或表达式使用HAVING子句为结果集中的组设置条件。HAVING子句为GROUP BY子句设置条件的方式与WHERE子句为SELECT语句设置条件的方式大致相同。使用HAVING子句应遵循以下规则：1.HAVING子句只有与GROUP BY子句连用才能对分组进行约束。只使用HA

21、VING子句而不使用GROUP BY子句是没有意义的。2.可以引用任何出现在选择列表中的列。3.不要与HAVING子句一起使用ALL关键字，因为HAVING子句会忽略ALL关键字而返回只满足自己条件的分组。6.2相关知识连接查询 等值与非等值连接查询但用户的一个查询请求涉及到数据库的多个表时，必须按照一定的条件把这些表连接在一起，以便能够共同提供用户需要的信息。用来连接两个表的条件成为连接条件或连接谓词，其一般格式为：. . 其中比较运算符主要有：=、=、=、!=。此外，连接谓词还可以使用下面形式：. BETWEEN . AND .但连接运算符为时，成为等值连接。使用其他运算符成为非等值连接。

22、连接谓词中的列名称为连接字段。连接条件中的各连接字段类型必须时可比的，但不必是相同的。例如，可以都是字符型，或都是日期型；也可以一个是整型，另一个是实型，整型和实型都是数值型，因此是可比的。但若一个是字符型，另一个是日期型就不允许了，因为它们是不可比的类型。6.2相关知识连接查询 内连接查询内连接通过比较两个表共同拥有的列的值，把两个表连接起来。SQL Server 2008将只返回满足条件的行。使用内连接可以得到两个单独的表中的信息，并把这些信息组合到一个结果集中。使用内连接查询遵循以下规则：1.内连接是SQL Server 2008默认的连接方式。可以把INNER JOIN子句简写成JOI

23、N。2.在SELECT语句选择列表中包含合法列名，以指定要在结果集中显示的列名。3.使用WHERE子句以限制结果集要返回的行。4.在连接的条件中不要使用空值，因为空值和其他值都不相等。6.2相关知识连接查询 外连接查询若要创建一个查询，以返回一个或多个表中的所有行（无论在另一个表中是否含有相匹配行），则需要使用外连接。外连接包括左外连接和右外连接。左外连接或右外连接可以将两个表中返回符合连接条件的行组合在一起，同时也将左边或右边的表中不符合连接条件的行组合在一起。在结果集中，不满足连接条件的行将显示空值。使用外连接遵循以下规则：1.左外连接可以显示表达式左边的那个表中的所有行。如果反置FROM子句中两个表的顺序，则生成的结果集同使用右外连接的结果集相同。2.右外连接可以显示表达式右边的那个表中的所有行。如果反置FROM子句中两个表的顺序，则生成的结果集同使用左外连接的结果集相同。3.可以把LEFT OUTER JOIN（左外连接）或RIGHT OUTER JOIN（右外连接）简写成LEFT JOIN或RIGHT JOIN。6.2相关知识连接查询 交叉连接查询交叉连接将从被连接的表中返回所有可能的行的组合。使用交叉连接时不要求连接的表一定拥有相同的列。尽管在一个规范化的数据库中很少使用交叉连接，但可以用它为数据库生成测试数据，或为核对表及业务模板生成所有可能组合的清单。6