表的管理PPT课件.ppt

1 表的管理 2020 3 19 2 表是数据库中最重要的对象 因此对表的管理是对SQLServer数据库管理的重要内容 本章将介绍对表的管理 包括创建 修改和删除表 表的约束 更新和查询和视图等 2020 3 19 3 1表概述 表的概念表的分类关于临时表在企业管理器中查看数据库中的表 2020 3 19 4 表的概念 表是数据库存储数据的主要对象 SQLServer数据库的表由行和列组成 2020 3 19 5 表的分类 在SQLServer中 表分为永久表和临时表两种 数据通常存储在永久表中 如果用户不手动删除 永久表和其中的数据将永久存在 临时表存储在tempdb数据库中 当不再使用时系统会自动删除临时表 临时表可以分为本地临时表和全局临时表 2020 3 19 6 在企业管理器中查看表 在企业管理器中 展开左侧窗格中的数据库 单击 表 项 可以查看指定数据库中的表 2020 3 19 7 在企业管理器中查看表数据 在企业管理器中 在右侧窗格中 用鼠标右击一个表 在弹出的快捷菜单中选择 打开表 返回所有行 可以查看表中的数据 2020 3 19 8 2表的创建 修改 删除和查看 1 创建表2 修改表3 删除表4 查看表 2020 3 19 9 2 1创建表 使用企业管理器创建表使用CREATETABLE语句创建表 2020 3 19 10 列的常用属性 列名列的数据类型是否允许为空 NULL或NOTNULL 主键 PRIMARYKEY 标识列 IDENTITY 默认值 DEFAULT 2020 3 19 11 1 使用企业管理器创建表 在企业管理器左侧窗格中选择展开数据库 学生管理 右键 表 项 在弹出菜单中选择 新建表 打开表设计器 2020 3 19 12 创建主键 插入列 删除列 右键单击列 在弹出菜单中选择 设置主键 可以将当前列设置为主键列 一个表中只能存在一个主键 2020 3 19 13 查看表属性 右键单击列 在弹出菜单中选择 属性 可以查看当前表的属性 2020 3 19 14 使用企业管理器修改表 在企业管理器中右键单击要修改的表 在弹出菜单中选择 设计表 可以打开表设计器 修改表的结构 2020 3 19 15 修改表名称 在企业管理器中右键单击表 在弹出菜单中选择 重命名 可以使表名表现为编辑状态 修改表名后 单击回车键 将弹出 确认重命名 对话框 单击 是 按钮 可以完成表的重命名 2020 3 19 16 删除表 在企业管理器中右键单击要删除的表 在弹出的快捷菜单中选择 删除表 可以打开 除去对象 对话框 如下图所示 单击 全部除去 按钮可以删除当前表 2020 3 19 17 查看表的属性 在企业管理器中右键单击要查看的表 在弹出菜单中选择 属性 打开 表属性 窗口 2020 3 19 18 查看表的内容 在企业管理器中右键单击要查看的表 在弹出菜单中选择 打开表 返回所有行 可以查看指定表中的所有数据 在弹出菜单中选择 打开表 返回首行 打开 输入行数 对话框 输入要查看的行数 可以查看表中指定数据的记录 2020 3 19 19 5表的更新 包括 数据插入数据删除数据修改 两种更新途径 使用企业管理器 在企业管理器中用鼠标右击要更新的表 在弹出的快捷菜单中选择 打开表 返回所有行 可以更新表中的数据 使用T SQL语句 2020 3 19 20 5 1插入数据 语法 INSERTINTO表名 列名1 列名2 列名n VALUES 值1 值2 值n 2020 3 19 21 例 使用INSERT语句在 院系 表中添加一行数据 院系名称 列的值为 计算机学院 上级编号 列的值为0 INSERTINTO院系 院系名称 上级编号 VALUES 计算机学院 0 执行结果 所影响的行数为1行 2020 3 19 22 2删除数据 DELETEFROM表名WHERE删除条件 2020 3 19 23 例 使用DELETE语句从 学生 表中删除 记录编号 为2的记录 DELETEFROM学生WHERE记录编号 2 2020 3 19 24 例 使用DELETE语句从 学生 表中删除所有 入学成绩 小于30的数据 DELETEFROM学生WHERE入学成绩 30 2020 3 19 25 3修改数据 语法 UPDATE表名SET列名1 值1 列名2 值2 列名n 值n WHERE更新条件 2020 3 19 26 例 使用UPDATE语句将 学生 表的所有记录的入学成绩加10 UPDATE学生SET入学成绩 入学成绩 10 2020 3 19 27 例 使用UPDATE语句将 学生 表中所有班级为 一班 的记录的入学成绩加10 UPDATE学生SET入学成绩 入学成绩 10WHERE班级 一班 2020 3 19 28 6表的查询 在企业管理器中查询数据使用SELECT语句实现查询 2020 3 19 29 6 6 1在企业管理器中查询数据 在企业管理器中 用鼠标右击要查询数据的表 在弹出的快捷菜单中选择 打开表 查询 打开 查询设计器 窗口 在该窗口构建并执行查询 2020 3 19 30 6 6 2SELECT语句 基本语法 SELECT子句 INTO子句 FROM子句 WHERE子句 GROUPBY子句 HAVING子句 ORDERBY子句 除了以上子句 SELECT语句中经常出现的关键字还包括UNION运算符 COMPUTE子句 FOR子句和OPTION子句 2020 3 19 31 6 6 3最基本的SELECT语句 SELECT子句的基本语法如下 SELECT ALL DISTINCT TOPn PERCENT 选择显示的列表 2020 3 19 32 使用DISTINCT DISTINCT的作用是显示惟一行 例 SELECTDISTINCT班级FROM学生 2020 3 19 33 使用TOPn PERCENT 如果只需要显示 学生 表中的前3行记录 可以使用如下语句 SELECTTOP3 FROM学生 2020 3 19 34 改变显示的列标题 使用AS关键字指定列标题 例 SELECTTOP3姓名AS学生姓名FROM学生 2020 3 19 35 6 6 4设置查询条件 WHERE子句用于指定限制返回的行的搜索条件 它的基本语法如下 WHERE条件表达式 2020 3 19 36 1 简单查询条件 例 使用SELECT语句查询 学生 表中所有的男生信息 SELECT FROM学生WHERE性别 1 2020 3 19 37 2 使用LIKE运算符 可以在WHERE子句中使用LIKE运算符和通配符进行模糊查询 用LIKE运算符指定匹配条件的语法如下 NOT LIKE 匹配串 匹配串 可以是以下情况之一 固定字符串 这时可以用 运算符取代LIKE 用 或 运算符取代NOTLIKE 代表任意长度 长度可以为0 的字符串 例如 a b 表示以a开头 以b结尾的任意长度的字符串 如acb addgb ab等都满足该匹配串 2020 3 19 38 2 使用LIKE运算符 下划线 代表任意单个字符 例如 a b表示以a开头 以b结尾的长度为3的任意字符串 如acb afb等都满足该匹配串 指定范围或集合中的任何单个字符 例如 a f 表示在字母a f之间 包括a和f 的任意一个字符 不属于指定范围或集合的任何单个字符 例如 A M 表示不在A M之间的任意一个字符 2020 3 19 39 例 使用SELECT语句查询 学生 表中所有姓名中包含 李 记录 SELECT FROM学生WHERE姓名LIKE 李 2020 3 19 40 3 使用AND和OR运算符 AND 表示两个条件都满足时查询条件才为真OR 表示两个条件中有一个满足时查询条件即为真 2020 3 19 41 示例 例 使用SELECT语句查询 学生 表中所有一班的女生 SELECT FROM学生WHERE班级 一班 AND性别 0 例 使用SELECT语句查询 学生 表中所有一班和二班的学生 SELECT FROM学生WHERE班级 一班 OR班级 二班 2020 3 19 42 4 使用BETWEEN运算符 在WHERE子句中使用BETWEEN运算符可以查询指定范围的记录 例 使用SELECT语句查询 学生 表中所有入学成绩在550 580之间的学生记录 SELECT FROM学生WHERE入学成绩BETWEEN550AND580 2020 3 19 43 6 6 5对查询结果排序 使用ORDERBY子句可以对查询结果进行排序 基本语法 ORDERBY 排序表达式 ASC DESC n 参数说明 排序表达式可以是一个列 也可以是一个表达式 在ORDERBY子句中 可以同时按照多个排序表达式进行排序 排序的优先级从左至右 ASC表示按照递增的顺序排列 ASC为默认值 DESC表示按照递减的顺序排列 2020 3 19 44 例 使用SELECT语句查询表 学生 所有记录 按照入学成绩从大到小排序 SELECT FROM学生ORDERBY入学成绩DESC 2020 3 19 45 6 6 6使用聚合函数 SELECT语句不仅可以显示表或视图中的列 还可以对列应用聚合函数 实现对表中指定数据的统计 如求总和 计数 求平均值等 2020 3 19 46 1 COUNT 函数 COUNT 函数可以用于统计记录个数 例 统计 学生 表的记录个数 SELECTCOUNT AS记录数量FROM学生 2020 3 19 47 2 AVG 函数 AVG 函数可以统计指定表达式的平均值 例6 48 统计 学生 表中所有学生的平均入学成绩 SELECTAVG 入学成绩 AS平均成绩FROM学生 2020 3 19 48 3 SUM 函数 SUM 函数可以统计指定表达式的和 例 统计 学生 表中所有学生的入学成绩之和 SELECTSUM 入学成绩 AS成绩之和FROM学生 聚合函数还包含MAX 和MIN 等 分别用于计算最大值和最小值 其使用方法和以上函数的使用方法类似 2020 3 19 49 聚合函数与WHERE子句结合使用 例 统计 学生 表中所有一班学生的平均入学成绩 SELECTAVG 入学成绩 AS平均成绩FROM学生WHERE班级 一班 2020 3 19 50 6 6 7对查询结果分组 当SELECT子句中包含聚合函数时 可以使用GROUPBY子句对查询结果进行分组统计 计算每组记录的汇总值 GROUPBY子句的基本语法如下 GROUPBY ALL 分组表达式 n 2020 3 19 51 例 在 学生 表中按性别统计所有学生的最高成绩 SELECT性别 MAX 入学成绩 AS最高成绩FROM学生GROUPBY性别 2020 3 19 52 注意 在使用GROUPBY子句时 SELECT子句中每一个非聚合表达式内的所有列都应包含在GROUPBY列表中 否则将会返回错误信息 2020 3 19 53 例 执行下面的语句会因为GROUPBY表达式与SELECT子句不匹配而产生错误 SELECT性别 班级 MAX 入学成绩 AS最高成绩FROM学生GROUPBY性别执行结果如下 服务器 消息8120 级别16 状态1 行1列 学生 班级 在选择列表中无效 因为该列既不包含在聚合函数中 也不包含在GROUPBY子句中 2020 3 19 54 6 6 8指定组或聚合的搜索条件 HAVING子句的功能是指定组或聚合的搜索条件 HAVING通常与GROUPBY子句一起使用 如果不使用GROUPBY子句 HAVING的作用与WHERE子句一样 HAVING与WHERE的区别在于 WHERE子句搜索条件在进行分组操作之前应用 而HAVING搜索条件在进行分组操作之后应用 HAVING语法与WHERE语法类似 但在HAVING子句中可以包含聚合函数 2020 3 19 55 例6 54 统计最高入学成绩超过590的院系及最高入学成绩信息 USE学生管理SELECT所属院系 MAX 入学成绩 AS最高成绩FROM学生GROUPBY所属院系HAVINGMAX 入学成绩 590 2020 3 19 56 例6 55 统计最高入学成绩超过590的院系及最高入学成绩信息 使用WHERE子句包含聚合函数 SELECT所属院系 MAX 入学成绩 AS最高成绩FROM学生WHEREMAX 入学成绩 590GROUPBY所属院系运行结果如下 服务器 消息147 级别15 状态1 行2 聚合不应出现在WHERE子句中 2020 3 19 57 6 6 10连接查询 在很多情况下 需要从多个表中提取数据 组合成一个结果集 如果一个查询需要对多个表进行操作 则将此查询称为连接查询 例如 在 学生 表中 所属院系 列的值是数字类型 不方便用户查看 而该列的值对应于 院系 表中的 记录编号 列 可以使用连接查询 从表 学生 和表 院系 中同时获取数据 从而可以显示学生所在院系的名称 连接查询包括内连接 外连接和交叉连接等 2020 3 19 58 1 内连接 内连接使用比较运算符 最常使用的是等号 即等值连接 根据每个表共有列的值匹配两个表中的行 只有每个表中都存在相匹配列值的记录才出现在结果集中 在内连接中 所有表是平等的 没有主次之分 2020 3 19 59 例6 57 使用内连接从 学生 表和 院系 表中同时获取数据 查询所有学生的姓名及其所在院系的名称 SELECTt1 院系名称 t2 姓名FROM院系t1 学生t2WHEREt1 记录编号 t2 所属院系还可以使用INNERJOIN关键字来定义内部连接 例如 上面的SELECT语句也可以使用下面的语句来代替 SELECTt1 院系名称 t2 姓名FROM院系t1INNERJOIN学生t2ONt1 记录编号 t2 所属院系 2020 3 19 60 2 外连接 与内连接相对 参与外连接的表有主次之分 以主表的每一行数据去匹配从表中的数据列 符合连接条件的数据将直接返回到结果集中 对那些不符合连接条件的列 将被填上NULL值后再返回到结果集中 外连接可以分为左向外连接 右向外连接和完整外部连接3种情况 2020 3 19 61 左向外连接 左向外连接以连接 JOIN 子句左侧的表为主表 主表中所有记录都将出现在结果集中 如果主表中的记录在右表中没有匹配的数据 则结果集中右表的列值为NULL 可以使用 符号定义左连接 2020 3 19 62 例6 58 使用左向外连接从 院系 表和 学生 表中同时获取数据 查询所有学生的姓名和所在院系名称 其中表 院系 为主表 SELECTt1 院系名称 t2 姓名FROM院系t1 学生t2WHEREt1 记录编号 t2 所属院系还可以使用LEFTOUTERJOIN或LEFTJOIN关键字定义左向外连接 例如 上例可以写为 SELECTt1 院系名称 t2 姓名FROM院系t1LEFTJOIN学生t2ONt1 记录编号 t2 所属院系 2020 3 19 63 例6 58使用左向外连接的查询结果 2020 3 19 64 右向外连接 右向外连接以连接 JOIN 子句右侧的表为主表 主表中所有记录都将出现在结果集中 如果主表中的记录在左表中没有匹配的数据 则结果集中左表的列值为NULL 可以使用 符号定义右向外连接 2020 3 19 65 例6 59 使用右向外连接从 院系 表和 学生 表中同时获取数据 查询所有学生的姓名和所在院系名称 其中 院系 表为主表 SELECTt1 院系名称 t2 姓名FROM院系t1 学生t2WHEREt2 所属院系 t1 记录编号还可以使用RIGHTOUTERJOIN或RIGHTJOIN关键字定义右向外连接 例如 上面的语句也可以写成 SELECTt1 院系名称 t2 姓名FROM学生t2RIGHTJOIN院系t1ONt1 记录编号 t2 所属院系 2020 3 19 66 例6 59使用右向外连接的查询结果 2020 3 19 67 完整外部连接 完整外部连接包括连接表中的所有行 无论它们是否匹配 在SQLServer2000中 还可以使用FULLOUTERJOIN或FULLJOIN关键字定义完整外部连接 2020 3 19 68 例6 60 使用完整外部连接从 院系 表和 学生 表中同时获取数据 查询所有学生的姓名和所在院系名称 SELECTt1 院系名称 t2 姓名FROM学生t2FULLJOIN院系t1ONt1 记录编号 t2 所属院系ON子句用于指定两个表之间的连接关系 完整外部连接相当于左向外连接和右向外连接的并集 2020 3 19 69 例6 60完整外部连接执行结果 2020 3 19 70 使用ISNULL和ISNOTNULL 在使用外连接时 结果集中会出现一些值为NULL的记录 在查询条件中使用ISNULL或ISNOTNULL关键字 可以查询某字段值等于或不等于NULL的记录 2020 3 19 71 例6 61 使用外连接查询没有学生的院系 SELECTt1 院系名称 t2 姓名FROM院系t1LEFTJOIN学生t2ONt1 记录编号 t2 所属院系WHEREt2 姓名ISNULL 2020 3 19 72 3 交叉连接 在交叉连接查询中 两个表中的每两行都可能互相组合成为结果集中的一行 交叉连接并不常用 除非需要穷举两个表的所有可能的记录组合 例 使用交叉连接从 院系 表和 学生 表中同时获取数据 查询所有学生的姓名和所在院系名称 SELECTt1 院系名称 t2 姓名FROM学生t2CROSSJOIN院系t1 2020 3 19 73 例6 62交叉连接执行结果 2020 3 19 74 6 6 11子查询 子查询就是在一个SELECT语句中又嵌套了另一个SELECT语句 在WHERE子句和HAVING子句中都可以嵌套SELECT语句 2020 3 19 75 1 使用IN关键字连接子查询 例 查询和小赵在同一个班的所有学生姓名 可以使用以下命令 SELECT姓名FROM学生WHERE班级IN SELECT班级FROM学生WHERE姓名 小赵 2020 3 19 76 2 使用等号连接子查询 例 要显示软件系的所有学生信息 可以使用以下命令 SELECT FROM学生WHERE所属院系 SELECT记录编号FROM院系WHERE院系名称 软件系 2020 3 19 77 3 使用EXISTS关键字连接子查询 例 要显示软件系的所有学生信息 可以使用以下命令 SELECT FROM学生WHEREEXISTS SELECT FROM院系WHERE记录编号 学生 所属院系AND院系名称 软件系 EXISTS关键字用于检测子句的结果集是否为空 2020 3 19 78 6 6 12合并查询 合并查询是将两个或更多查询的结果组合为单个结果集 该结果集包含联合查询中的所有查询的全部行 使用UNION运算符组合两个查询的结果集的两个前提是 所有查询结果中的列数和列的顺序必须相同 对应列的数据类型必须兼容 合并查询经常用来返回明细和统计信息 因为明细和统计信息需要分别统计 所以可以使用合并查询将它们统一到一个结果集中 2020 3 19 79 例 显示所有学