《数据表的管理31表的创建及修改》由会员分享，可在线阅读，更多相关《数据表的管理31表的创建及修改（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 St
2、udio中创建表，另一种方式是通过执行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中修改
3、表 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
4、 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 实例断开连接时
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 表中数据的删除,在对象资源管理器中通过菜单命令删除表； 使用删除
6、语句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 课程号199,3.
7、2.4 数据关系图的使用,数据库关系图设计器,3.3.1 SELECT语句概述 数据的查询是通过SELECT语句完成的。的主要子句可归纳如下： SELECT select_list INTO 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选择所有列 选择所有的列，既可以在查询中逐一地列示出所
8、有的列名，也可以使用符号“*”。 例 3-6 查询本校教师信息 SELECT * FROM 本校教师 2选择指定的列 若要选择表中的特定列，应在选择列表中明确地列出每一列，在列与列之间用逗号隔开。 例：SELECT 教师编号，教师姓名，系，专业方向，职称 FROM 本校教师,行的检索可以将通过限定条件返回结果的行组成结果集。 1使用DISTINCT消除重复行 使用DISTINCT消除重复行的语法格式如下： DISTINCT column_name,column_name DISTINCT关键字对其后面所指定的列消除重复行。一个SELECT语句中只能有一个DISTINCT，而且必须放在所有的列名
9、之前。,3.3.3 行的检索,2使用 TOP 子句限制结果集中返回的行数 TOP 子句指定返回的行数，语法格式为： TOP ( expression ) PERCENT WITH TIES 其中，expression 是指定返回行数的数值表达式，如果指定了 PERCENT，则是指返回的结果集行的百分比（由 expression 指定）。,3.3.3 行的检索,3使用WHERE子句限定返回行的条件 在SELECT语句中使用WHERE子句指定查询的条件，使系统将符合条件的行作为结果。 WHERE子句的语法格式： WHERE AND AND |OR 1）比较搜索条件：在WHERE子句中对表达式进行比
10、较，可使用比较运算符和逻辑运算符。,3.3.3 行的检索,3使用WHERE子句限定返回行的条件 WHERE子句的语法格式： WHERE AND AND |OR 2）范围搜索条件：范围搜索返回介于两个指定值之间的所有值。在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）
11、列表搜索条件：在WHERE子句中使用关键字IN或OR运算符指定可选的取值。 例 3-14 查询成绩表中“高等数学”和“大学语文”课程的成绩（课程号分别为101和102）。 use 教务管理 SELECT 学号,课程号,成绩 FROM 成绩 WHERE 课程号=101 OR 课程号=102 或 use 教务管理 SELECT 学号,课程号,成绩 FROM 成绩 WHERE 课程号 IN (101, 102),3.3.3 行的检索,3使用WHERE子句限定返回行的条件 WHERE子句的语法格式： WHERE AND AND |OR 4）搜索条件中的模式匹配：使用LIKE关键字来限定模式匹配查询。
12、LIKE子句中可使用的通配符,3.3.3 行的检索,3使用WHERE子句限定返回行的条件 WHERE子句的语法格式： WHERE AND AND |OR 5）NULL值的处理：空值表示值未知。空值不同于空白或零值。没有两个相等的空值。若要在查询中测试空值，可在 WHERE 子句中使用 IS NULL 或 IS NOT NULL。 例 3-17 查询成绩表中无成绩的学号和课程。 USE 教务管理 SELECT 学号,课程号 FROM 成绩 WHERE 成绩 IS NULL GO,3.3.3 行的检索,3.3.4 数据算术运算 在对表进行查询时，有时需要对所查询的某些列使用表达式进行计算，SELE
13、CT语句支持表达式的使用。 通过在带有算术运算符、函数、转换或嵌套查询的选择列表中使用数值列或数值常量，可以对数据进行计算和运算。算术运算符允许对数值数据进行加、减、乘、除运算。 查询语句中支持的算术运算符包括：+，-，*，/，%（模运算）。 例：以计算结果作为派生列输出,3.3.5 数据转换 有时在查询中需要将结果以另外一种形式显示出来，例如成绩的显示，有时需要分数有时需要等级，如“优秀”、“及格”等。可以使用CASE子句指定数据的转换。,例 3-19 查询学生成绩，以“优秀”、“良好”、“合格”和“不及格”的等级显示结果。 USE 教务管理 SELECT 学号,课程号,成绩,等级= CAS
14、E WHEN 成绩=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 SE
15、LECT 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
16、,常用聚集函数,3.3.7 数据汇总 除使用聚集函数进行汇总外，还可以使用COMPUTE关键字对结果进行汇总计算。 COMPUTE可以生成合计作为附加的汇总列，出现在结果集的最后。 例 3-21 查询家住江苏省的学生，输出学号、姓名和家庭住址，并统计学生人数。 USE 教务管理 SELECT 学号,姓名,家庭住址 FROM 学生 WHERE 家庭住址 LIKE 江苏% COMPUTE COUNT(学号) GO,3.3.8 连接查询 在实际查询中，有时所需要的信息来自于不同的表，需要在查询中将这些表进行连接方能得到完整的信息。 连接条件可通过以下方式定义两个表在查询中的关联方式： 指定每个表中要用于联接的列。典型的联接条件在一个表中指定一个外键，而在另一个表中指定与其关联的键。 指定用于比较各列的
