单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,第,3,章 数据表的管理,3.1,表的创建及修改,3.3,表中数据的检索,3.2,表中数据的修改,主要内容,了解,SQL Server 2005,的数据类型,掌握表的创建和表结构的修改方法,掌握表中数据的插入、删除和修改操作,掌握表中数据的检索方法,3.1,表的创建及修改,3.1.1,数据类型,数据类型是数据库的重要特性之一，,SQL Server,提供了多种系统数据类型，也可以由用户自定义数据类型。,逻辑类型,整数类型,浮点类型,字符类型,二进制类型,时间数据类型,货币数据类型,3.1.2,创建表,在,SQL Server 2005,中提供两种创建表的方式：一种是在,SQL Server Management Studio,中创建表，另一种方式是通过执行,T-SQL,语句创建表。,1,在,SQL Server Management Studio,中创建表,2,用,T-SQL,语句创建表,例,3-1,创建一个教师表,CREAT TABLE,教师表,(,教师编号,INT IDENTITY(1,1)NOT NULL,/*,设置标识规范,*,/,教师姓名,CHAR(8)NOT NULL,职称,CHAR(6)DEFAULT,讲师,/*,设置默认值约束,*,/,专业方向,CHAR(20),系,CHAR(10),联系方式,CHAR(30),GO,3.1.3,修改表结构,1,在,SQL Server Management Studio,中修改表,2,用,T-SQL,语句修改表,:,ALTER TABLE,例,3-2,要修改教师表的教师编号为,6,位字符，删除职称列：,ALTER TABLE,教师表,ALTER COLUMN,教师编号,char,（,6,）,GO,ALTER TABLE,教师表,DROP COLUMN,职称,GO,3.1.4,删除表,将表从数据库中删除，不仅删除了表的结构，还包括表中的所有对象，该表的结构定义、数据、全文索引、约束和索引都从数据库中永久删除。,如果要删除通过,FOREIGN KEY,和,UNIQUE,或,PRIMARY KEY,约束相关联的表，则必须先删除具有,FOREIGN KEY,约束的表。如果要删除,FOREIGN KEY,约束中引用的表但不能删除整个外键表，则必须删除,FOREIGN KEY,约束。,3.1.4,删除表,1,在,SQL Server Management Studio,中删除表,2,用,T-SQL,语句删除表,删除表的,T-SQL,语句是：,DROP TABLE table_name,例,3-3,删除数据库“教务管理”中的“成绩”表,DROP TABLE,成绩,3.1.5,临时表,临时表存储在系统数据库,tempdb,中，当不再使用时会自动删除。,临时表有两种类型：本地的和全局的。,本地临时表的名称以单个数字符号“,#,”,打头，它们仅对当前的用户连接是可见的，当用户从,SQL Server,实例断开连接时被删除。,全局临时表的名称以两个数字符号“,#,”,打头，创建后对任何用户都是可见的，当所有引用该表的用户从,SQL Server,断开连接时被删除。,3.2,表中数据的修改,3.2.1,表中数据的插入,INSERT,语句可向表中添加一个或多个新行。其语法格式如下：,INSERT INTO table_or_view(column_list)VALUES(data_values,n),使用,INSERT,SELECT,语句还可以将一个表中的数据插入到另一个表中，例如：,INSERT,课程,SELECT*FROM,基础课程表,3.2.2,表中数据的删除,在对象资源管理器中通过菜单命令删除表；,使用删除语句,DELETE,也可实现对记录的删除。,语法格式：,DELETE,table_or_view,FROM,table_sources,WHERE,search_condition,3.2.3,表中数据的字段值的修改,UPDATE,语句可以更改表或视图中单行、行组或所有行的数据值。其语法格式如下：,UPDATE table_name SET column_name=expression|DEFAULT|NULL,n,FROM tablelist WHERE condition,例,3-5,设置课程号小于“,199,”的课学分为,4,分,UPDATE,课程,SET,学分,=4 WHERE,课程号,=85 THEN,优秀,WHEN,成绩,=70 AND,成绩,=60 AND,成绩,70 THEN,合格,WHEN,成绩,60 THEN,不及格,END,FROM,成绩,GO,3.3.6,函数的应用,SELECT,查询也可以放在用户自定义的函数中，通过调用函数执行数据库查询。,例,3-20,定义一个函数来计算全体学生某门课程的平均分数。通过调用这个函数来查询“,101,”、“,201,”和“,301,”号的课程的平均分。,CREATE FUNCTION averagescore(course char(3),RETURNS INT,AS,BEGIN,DECLARE avg_score INT,SELECT avg_score=,(SELECT AVG(,成绩,),FROM,成绩,WHERE,课程号,=course),RETURN avg_score,END,GO,例：调用函数：,USE,教务管理,SELECT dbo.averagescore(101),3.3.7,数据汇总,在对表数据进行查询时，经常需要对结果进行汇总计算。可以使用聚集函数对数据进行计算。,例,3-20,统计选修“,301,”号课程的总分，平均分和最高分。,USE,教务管理,SELECT SUM(,成绩,)AS,总成绩,AVG(,成绩,)AS,平均成绩,MAX(,成绩,)AS,最高成绩,FROM,成绩,WHERE,课程号,=301,GO,函数名,说明,AVG,求平均值,COUNT,（,xolumn_name,）,统计列中数据项数,COUNT,（,*,）,统计行数,MAX,求最大值,MIN,求最小值,SUM,求和,STDEV,求标准偏差,VAR,求方差,常用聚集函数,3.3.7,数据汇总,除使用聚集函数进行汇总外，还可以使用,COMPUTE,关键字对结果进行汇总计算。,COMPUTE,可以生成合计作为附加的汇总列，出现在结果集的最后。,例,3-21,查询家住江苏省的学生，输出学号、姓名和家庭住址，并统计学生人数。,USE,教务管理,SELECT,学号,姓名,家庭住址,FROM,学生,WHERE,家庭住址,LIKE,江苏,%,COMPUTE COUNT(,学号,),GO,3.3.8,连接查询,在实际查询中，有时所需要的信息来自于不同的表，需要在查询中将这些表进行连接方能得到完整的信息。,连接条件可通过以下方式定义两个表在查询中的关联方式：,指定每个表中要用于联接的列。典型的联接条件在一个表中指定一个外键，而在另一个表中指定与其关联的键。,指定用于比较各列的值的逻辑运算符（例如,=,或,）。,3.3.8,连接查询,1,内部连接,内部联接是使用比较运算符比较要联接列中的值的联接。使用,FROMWHERE,子句能够将表进行连接，语法格式为：,SELECT column_name,coluumn_name,/*,指定输出的列,*,/,FROM table_name,table_name,/*,指定连接的表,*,/,WHERE condition,/*,指定连接条件或选择条件,*,/,例,3-22,查询学生姓名、所选课程和各科成绩。,表及表结构如下：,学生,(,学号,姓名,性别,身份证号,出生年月,联系电话,民族,家庭住址,邮编,),课程,(,课程号,课程名,学时,学分,),成绩,(,学号,课程号,成绩,),3.3.8,连接查询,2,外部连接,外部连接会返回,FROM,子句中提到的至少一个表或视图中的所有行，只要这些行符合任何,WHERE,或,HAVING,搜索条件。,Microsoft SQL Server 2005,对,FROM,子句中指定的外部连接使用下列关键字：,LEFT OUTER JOIN,或,LEFT JOIN,RIGHT OUTER JOIN,或,RIGHT JOIN,FULL OUTER JOIN,或,FULL JOIN,其中,LEFT,表示左外连接，,RIGHT,表示右外连接，,FULL,表示完全外连接。,3.3.8,连接查询,2,外部连接,例：左外连接、右外连接、完全外连接。,左外连接查询,右外连接查询,完全外连接,3.3.8,连接查询,3,交叉连接,在,FROM,子句中关键字,CROSS,表示交叉连接，又称为自然连接，即生成一个笛卡尔积。没有,WHERE,子句的交叉连接将产生连接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。,交叉连接查询,3.3.9,联合查询,UNION,操作符将两个或两个以上的查询结果全并为一个结果集。,UNION,语句的语法格式为：,|(),UNION ALL,|(),UNION ALL|()n,其中，,指明了查询的详细说明或查询表达式。,UNION,为合并操作符。,ALL,表示合并所有数据行到结果集中，包括值重复的数据行。如果不指定此选项，则重复的数据行只显示一行。,3.3.9,联合查询,例,3-26,查询所有的教师姓名，包括本校教师和外聘教师。,表及表结构为：,本校教师,(,教师编号,教师姓名,职称,专业方向,系,),外聘教师,(,教师编号,教师姓名,职称,专业方向,工作单位,联系方式,),查询语句为：,3.3.10,嵌套查询,嵌套查询也称“子查询”。外部的,SELECT,语句称为外围查询（也称为父查询），内部的,SELECT,语句称为子查询。子查询的结果将作为外围查询的参数使用。,1,无关子查询,无关子查询，子查询在外围查询之前进行，然后返回结果集供外围查询使用。对应于,SQL,语句来说，如果被嵌套的查询中不包含对于外围查询的引用，则成为无关子查询。最常用的无关子查询方式是,IN,或,NOT IN,子句，其语法格式如下：,SELECT select_list,FROM table_name,WHERE condition NOT IN,(SELECT select_list,FROM table_name,WHERE condition),3.3.10,嵌套查询,2,相关子查询,相关子查询是指在执行子查询时需要使用的到外围查询的数据。这时，外围查询首先选择数据提供给子查询，然后子查询再对数据进行比较，执行结束后再将它的查询结果返回给外围查询中。,相关子查询使用的关键字通常包括：,EXISTS,，,AND,，,SOME,，,ANY,、,ALL,等。,EXISTS,一般直接写在,WHERE,关键字后面，不写列名、常量或表达式，此时子查询的,SELECT,列表一般由“,*,”组成。,关键字,EXISTS,在相关子查询中使用，使用时，对外表中的每一行子查询都要运行一遍，该行的值也要在子查询的,WHERE,子句中被使用，这样，通过,EXISTS,子句就能将外层表中的各行数据依次与子查询处理的内层表中的数据进行存在性比较，得到所需要的结果。,3.3.11,排序,要求记录按某种顺序输出，因此需要对表中的行进行排序。,使用,ORDER BY,子句对查询结果进行排序，其语法格式为：,SELECT select_list,FROM table_name,WHERE condition,ORDER BY column_name|alias|position ASC|DESC,排序可以是升序的,(ASC),，也可以是降序的,(DESC),。如果未指定是升序还是降序，就默认为,ASC,。,例,3-3