《数据表的管理31表的创建及修改.ppt》由会员分享，可在线阅读，更多相关《数据表的管理31表的创建及修改.ppt（36页珍藏版）》请在三一办公上搜索。
1、第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中创建表，另一
2、种方式是通过执行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
3、-2 要修改教师表的教师编号为6位字符，删除职称列：ALTER TABLE 教师表ALTER COLUMN 教师编号 char（6）GOALTER TABLE 教师表DROP COLUMN 职称GO,3.1.4 删除表,将表从数据库中删除，不仅删除了表的结构，还包括表中的所有对象，该表的结构定义、数据、全文索引、约束和索引都从数据库中永久删除。如果要删除通过 FOREIGN KEY 和 UNIQUE 或 PRIMARY KEY 约束相关联的表，则必须先删除具有 FOREIGN KEY 约束的表。如果要删除 FOREIGN KEY 约束中引用的表但不能删除整个外键表，则必须删除 FOREIGN
4、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 实例断开连接时被删除。全局临时表的名称以两个数字符号“#”打头，创建后对任何用户都是可见的，当所有引用
5、该表的用户从 SQL Server 断开连接时被删除。,3.2表中数据的修改3.2.1 表中数据的插入INSERT 语句可向表中添加一个或多个新行。其语法格式如下：INSERT INTO table_or_view(column_list)VALUES(data_values,n)使用INSERTSELECT语句还可以将一个表中的数据插入到另一个表中，例如：INSERT 课程 SELECT*FROM 基础课程表,3.2.2 表中数据的删除,在对象资源管理器中通过菜单命令删除表；使用删除语句DELETE也可实现对记录的删除。语法格式：DELETE table_or_view FROM table
6、_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 课程号199,3.2.4 数据关系图的使用,数据库关系图设计器,3.3.1 SELECT语句概述数据的查询是通过SELECT语句完成的。的主
7、要子句可归纳如下：SELECT select_listINTO new_table_name FROM table_list WHERE search_conditions GROUP BY group_by_list HAVING search_conditions ORDER BY order_list ASC|DESC,3.3 表中数据的检索,3.3.2 列的检索选择列是限定返回某些列组成结果集。1选择所有列选择所有的列，既可以在查询中逐一地列示出所有的列名，也可以使用符号“*”。例 3-6 查询本校教师信息SELECT*FROM 本校教师2选择指定的列若要选择表中的特定列，应在选择列表
8、中明确地列出每一列，在列与列之间用逗号隔开。例：SELECT 教师编号，教师姓名，系，专业方向，职称FROM 本校教师,行的检索可以将通过限定条件返回结果的行组成结果集。1使用DISTINCT消除重复行使用DISTINCT消除重复行的语法格式如下：DISTINCT column_name,column_nameDISTINCT关键字对其后面所指定的列消除重复行。一个SELECT语句中只能有一个DISTINCT，而且必须放在所有的列名之前。,3.3.3 行的检索,2使用 TOP 子句限制结果集中返回的行数TOP 子句指定返回的行数，语法格式为：TOP(expression)PERCENT WIT
9、H TIES 其中，expression 是指定返回行数的数值表达式，如果指定了 PERCENT，则是指返回的结果集行的百分比（由 expression 指定）。,3.3.3 行的检索,3使用WHERE子句限定返回行的条件在SELECT语句中使用WHERE子句指定查询的条件，使系统将符合条件的行作为结果。WHERE子句的语法格式：WHERE AND AND|OR 1）比较搜索条件：在WHERE子句中对表达式进行比较，可使用比较运算符和逻辑运算符。,3.3.3 行的检索,3使用WHERE子句限定返回行的条件WHERE子句的语法格式：WHERE AND AND|OR 2）范围搜索条件：范围搜索返回
10、介于两个指定值之间的所有值。在SELECT语句中用BETWEEN 关键字指定要搜索的包括范围。例 3-13 查询成绩在60分到70分之间的学号、课程和成绩：use 教务管理SELECT 学号,课程号,成绩FROM 成绩WHERE 成绩 BETWEEN 60 AND 70,3.3.3 行的检索,3使用WHERE子句限定返回行的条件WHERE子句的语法格式：WHERE AND AND|OR 3）列表搜索条件：在WHERE子句中使用关键字IN或OR运算符指定可选的取值。例 3-14 查询成绩表中“高等数学”和“大学语文”课程的成绩（课程号分别为101和102）。use 教务管理SELECT 学号,课
11、程号,成绩FROM 成绩WHERE 课程号=101 OR 课程号=102或use 教务管理SELECT 学号,课程号,成绩FROM 成绩WHERE 课程号 IN(101,102),3.3.3 行的检索,3使用WHERE子句限定返回行的条件WHERE子句的语法格式：WHERE AND AND|OR 4）搜索条件中的模式匹配：使用LIKE关键字来限定模式匹配查询。LIKE子句中可使用的通配符,3.3.3 行的检索,3使用WHERE子句限定返回行的条件WHERE子句的语法格式：WHERE AND AND|OR 5）NULL值的处理：空值表示值未知。空值不同于空白或零值。没有两个相等的空值。若要在查询
12、中测试空值，可在 WHERE 子句中使用 IS NULL 或 IS NOT NULL。例 3-17 查询成绩表中无成绩的学号和课程。USE 教务管理SELECT 学号,课程号FROM 成绩WHERE 成绩 IS NULLGO,3.3.3 行的检索,3.3.4 数据算术运算在对表进行查询时，有时需要对所查询的某些列使用表达式进行计算，SELECT语句支持表达式的使用。通过在带有算术运算符、函数、转换或嵌套查询的选择列表中使用数值列或数值常量，可以对数据进行计算和运算。算术运算符允许对数值数据进行加、减、乘、除运算。查询语句中支持的算术运算符包括：+，-，*，/，%（模运算）。例：以计算结果作为派
13、生列输出,3.3.5 数据转换有时在查询中需要将结果以另外一种形式显示出来，例如成绩的显示，有时需要分数有时需要等级，如“优秀”、“及格”等。可以使用CASE子句指定数据的转换。,例 3-19 查询学生成绩，以“优秀”、“良好”、“合格”和“不及格”的等级显示结果。USE 教务管理SELECT 学号,课程号,成绩,等级=CASE WHEN 成绩=85 THEN 优秀 WHEN 成绩=70 AND 成绩=60 AND 成绩70 THEN 合格 WHEN 成绩60 THEN 不及格ENDFROM 成绩GO,3.3.6 函数的应用SELECT查询也可以放在用户自定义的函数中，通过调用函数执行数据库查
14、询。例 3-20 定义一个函数来计算全体学生某门课程的平均分数。通过调用这个函数来查询“101”、“201”和“301”号的课程的平均分。CREATE FUNCTION averagescore(course char(3)RETURNS INTASBEGINDECLARE avg_score INT SELECT avg_score=(SELECT AVG(成绩)FROM 成绩 WHERE 课程号=course)RETURN avg_scoreENDGO例：调用函数：USE 教务管理SELECT dbo.averagescore(101),3.3.7 数据汇总在对表数据进行查询时，经常需要对
15、结果进行汇总计算。可以使用聚集函数对数据进行计算。例 3-20 统计选修“301”号课程的总分，平均分和最高分。USE 教务管理SELECT SUM(成绩)AS 总成绩,AVG(成绩)AS 平均成绩,MAX(成绩)AS 最高成绩FROM 成绩WHERE 课程号=301GO,常用聚集函数,3.3.7 数据汇总除使用聚集函数进行汇总外，还可以使用COMPUTE关键字对结果进行汇总计算。COMPUTE可以生成合计作为附加的汇总列，出现在结果集的最后。例 3-21 查询家住江苏省的学生，输出学号、姓名和家庭住址，并统计学生人数。USE 教务管理SELECT 学号,姓名,家庭住址FROM 学生WHERE
16、 家庭住址 LIKE 江苏%COMPUTE COUNT(学号)GO,3.3.8 连接查询在实际查询中，有时所需要的信息来自于不同的表，需要在查询中将这些表进行连接方能得到完整的信息。连接条件可通过以下方式定义两个表在查询中的关联方式：指定每个表中要用于联接的列。典型的联接条件在一个表中指定一个外键，而在另一个表中指定与其关联的键。指定用于比较各列的值的逻辑运算符（例如=或）。,3.3.8 连接查询1内部连接内部联接是使用比较运算符比较要联接列中的值的联接。使用FROMWHERE子句能够将表进行连接，语法格式为：SELECT column_name,coluumn_name,/*指定输出的列*/
17、FROM table_name,table_name,/*指定连接的表*/WHERE condition/*指定连接条件或选择条件*/例 3-22 查询学生姓名、所选课程和各科成绩。表及表结构如下：学生(学号,姓名,性别,身份证号,出生年月,联系电话,民族,家庭住址,邮编)课程(课程号,课程名,学时,学分)成绩(学号,课程号,成绩),3.3.8 连接查询2外部连接外部连接会返回 FROM 子句中提到的至少一个表或视图中的所有行，只要这些行符合任何 WHERE 或 HAVING 搜索条件。Microsoft SQL Server 2005 对 FROM 子句中指定的外部连接使用下列关键字：LEF
18、T OUTER JOIN 或 LEFT JOINRIGHT OUTER JOIN 或 RIGHT JOINFULL OUTER JOIN 或 FULL JOIN其中LEFT表示左外连接，RIGHT表示右外连接，FULL表示完全外连接。,3.3.8 连接查询2外部连接例：左外连接、右外连接、完全外连接。,左外连接查询,右外连接查询,完全外连接,3.3.8 连接查询3交叉连接在FROM子句中关键字CROSS表示交叉连接，又称为自然连接，即生成一个笛卡尔积。没有 WHERE 子句的交叉连接将产生连接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。,交叉连接查询,3.3
19、.9 联合查询UNION操作符将两个或两个以上的查询结果全并为一个结果集。UNION语句的语法格式为：|()UNION ALL|()UNION ALL|()n其中，指明了查询的详细说明或查询表达式。UNION为合并操作符。ALL表示合并所有数据行到结果集中，包括值重复的数据行。如果不指定此选项，则重复的数据行只显示一行。,3.3.9 联合查询例 3-26查询所有的教师姓名，包括本校教师和外聘教师。表及表结构为：本校教师(教师编号,教师姓名,职称,专业方向,系)外聘教师(教师编号,教师姓名,职称,专业方向,工作单位,联系方式)查询语句为：,3.3.10 嵌套查询嵌套查询也称“子查询”。外部的SE
20、LECT语句称为外围查询（也称为父查询），内部的SELECT语句称为子查询。子查询的结果将作为外围查询的参数使用。1无关子查询无关子查询，子查询在外围查询之前进行，然后返回结果集供外围查询使用。对应于SQL语句来说，如果被嵌套的查询中不包含对于外围查询的引用，则成为无关子查询。最常用的无关子查询方式是IN或NOT IN子句，其语法格式如下：SELECT select_listFROM table_nameWHERE condition NOT IN(SELECT select_listFROM table_nameWHERE condition),3.3.10 嵌套查询2相关子查询相关子查询是
21、指在执行子查询时需要使用的到外围查询的数据。这时，外围查询首先选择数据提供给子查询，然后子查询再对数据进行比较，执行结束后再将它的查询结果返回给外围查询中。相关子查询使用的关键字通常包括：EXISTS，AND，SOME，ANY、ALL等。EXISTS一般直接写在WHERE关键字后面，不写列名、常量或表达式，此时子查询的SELECT列表一般由“*”组成。关键字EXISTS在相关子查询中使用，使用时，对外表中的每一行子查询都要运行一遍，该行的值也要在子查询的WHERE子句中被使用，这样，通过EXISTS子句就能将外层表中的各行数据依次与子查询处理的内层表中的数据进行存在性比较，得到所需要的结果。,3.3.11 排序要求记录按某种顺序输出，因此需要对表中的行进行排序。使用ORDER BY子句对查询结果进行排序，其语法格式为：SELECT select_listFROM table_nameWHERE conditionORDER BY column_name|alias|position ASC|DESC排序可以是升序的(ASC)，也可以是降序的(DESC)。如果未指定是升序还是降序，就默认为 ASC。例 3-32 查询选修了“301”号课程的学生，并按分数从高到低的顺序输出学生的学号和成绩。,
