关系数据库基础第章数据表管理与查询PPT课件.ppt

第3章数据表的管理 2020 3 20 1 主要内容 了解SQLServer2005的数据类型掌握表的创建和表结构的修改方法掌握表中数据的插入 删除和修改操作掌握表中数据的检索方法 2020 3 20 2 三大知识块 2020 3 20 3 3 1表的创建及修改 3 1 1数据类型3 1 2创建表3 1 3修改表结构3 1 4删除表3 1 5临时表 2020 3 20 4 3 1 1数据类型 数据类型决定了数据在计算机中的存储格式 代表不同的信息类型SQLServer2005定义了多种系统数据类型允许用户自定义数据类型 2020 3 20 5 系统提供的数据类型 2020 3 20 6 货币数据类型 货币数据类型专门用于货币数据处理 包括 money 263 263 1 精确到货币单位的10 2个4B整数构成smallmoney 214748 3648 214748 3647 精确到货币单位的10 2个2B整数构成 28 56第1个4字节 第2个4字节第1个2字节 第2个2字节 货币值的整数部分 货币值的小数部分 2020 3 20 7 7 3 1 2创建表 表是数据库中最重要的对象 表的结构决定了数据在数据库中的存储方式两种创建表的方式 1 在SQLServerManagementStudio中创建表2 用T SQL语句创建表 2020 3 20 8 2020 3 20 9 学生表 SQLServerManagementStudio CREATETABLE学生 学号char 10 PRIMARYKEY 姓名varchar 8 NULL 性别char 2 NULL 出生年月datetimeNULL 家庭住址varchar 50 NULL T SQL语句 1 2 取列名 定义数据类型 定义数据类型 创建表 用SQLServerManagementStudio创建表 1 进入SSMS 选择表节点 单击右键 在弹出的快捷菜单中选择 新建表 2 打开表设计器 2020 3 20 10 用SQLServerManagementStudio创建表 3 设置属性和约束 保证数据完整性 2020 3 20 11 4 3 1数据完整性概念 数据完整性是指数据库中的数据在逻辑上的一致性和准确性 在关系数据库系统中 重要的完整性有 实体完整性域完整性参照完整性用户自定义的完整性 4 3SQLSERVER数据完整性 2020 3 20 12 4 3 2域完整性 域完整性又称列完整性 是指给定列的输入有效性 强制域有效性的方法有 限制类型 定义数据类型 取值范围 通过CHECK约束 定义DEFAULT默认值 定义NOTNULL NULL来实现 定义数据类型 设置是否为NULL 2020 3 20 13 用SQLServerManagementStudio创建表 设置默认值 没有输入时系统自动填入值 2020 3 20 14 在这里直接输入需要的值 用SQLServerManagementStudio创建表 设置检查约束 定义列的取值范围右击列 弹出快捷菜单 选择 CHECK约束 2020 3 20 15 点这里 打开CHECK约束对话框 选择 添加 2020 3 20 16 用SQLServerManagementStudio创建表 点这里 输入正确的表达式 2020 3 20 17 用SQLServerManagementStudio创建表 在这里直接输入 或点这个在弹出的对话框中输入 完成后点这里 点关闭完成设置 4 3 3实体完整性 实体完整性又称行完整性 是指将行定义为特定表的惟一实体 要求每一个表中都有一个主键 并且其值不能为空且不允许有重复的值与之对应 实体完整性强制表的标识符列或主键的完整性 通过UNIQUE约束 PRIMARYKEY约束或IDENTITY属性来实现 2020 3 20 18 设置IDENTITY属性 标识规范 只能应用于int型数据 系统自动编号 不能手工输入 2020 3 20 19 用SQLServerManagementStudio创建表 展开 设定标识增量和标识种子 2020 3 20 20 用SQLServerManagementStudio创建表 选择是 设定起始编号 设定编号步长 设定主键约束 每个表必须有主键约束 2020 3 20 21 用SQLServerManagementStudio创建表 单列主键 选中该列点右键 在弹出的快捷菜单中选中设置主键 组合主键 选中几列后点右键 在弹出的快捷菜单中选中设置主键 设置UNIQUE唯一性约束 该列不能输入重复值右击列 弹出快捷菜单 选择 索引 键 2020 3 20 22 用SQLServerManagementStudio创建表 点这里 打开索引 键对话框 选择 添加 2020 3 20 23 用SQLServerManagementStudio创建表 点这里 选择 唯一键 2020 3 20 24 用SQLServerManagementStudio创建表 选择类型 指定使用唯一键的列 2020 3 20 25 用SQLServerManagementStudio创建表 点这里弹出列选择对话框 选择家庭地址 然后点确定 点关闭完成设置 4 3 4参照完整性 参照完整性又称引用完整性 是指主表中的数据与从表中的数据的一致性 在输入或删除其中一个表的记录时 另一个表对应的约束应满足 即参照完整性保持表之间已定义的关系 在SQLServer中 参照完整性基于外键与主键之间或外键与惟一键之间的关系 通过FOREIGNKEY约束 确保键值在所有表中一致 这样的一致性要求不能引用不存在的值 如果键值更改了 那么在整个数据库中 对该键值的所有引用要进行一致的更改 外键约束是保证关系数据库数据完整性的最重要的约束 建立了表与表之间的联系 2020 3 20 26 设置外键约束选中列点右键 在弹出的快捷菜单中选择 关系 2020 3 20 27 用SQLServerManagementStudio创建表 点这里 打开外键关系对话框 选择 添加 2020 3 20 28 用SQLServerManagementStudio创建表 点这里 指定主外键关系的列 2020 3 20 29 用SQLServerManagementStudio创建表 点这里弹出表和列选择对话框 选择主键表和主键列 然后点确定 点关闭完成设置 使用FOREIGNKEY约束需要注意的问题1 一个表最多可以有可以建立一个或者多个FOREIGNKEY约束 最多可以建253个FOREIGNKEY约束 2 创建FOREIGNKEY约束的主表字段可以是主键约束也可以是UNIQUE约束 3 创建FOREIGNKEY约束的表可以和同数据库中其他表间建立约束 也可以和它本身建立FOREIGNKEY约束 例如 总经理也是公司成员中的一员 则可以将总经理字段 zjl 和成员 cy 字段间建立FOREIGNKEY约束 4 建立FOREIGNKEY约束的主表和子表间两个字段的数据类型和数据宽度必须相同 5 创建FOREIGNKEY约束前应该确定那个表是主表 那个是子表 假如A表删了一条记录 表B也要删一条对应的记录 这时候应该在表B上设定关系 A是主表 B是子表 有的称为从表 2020 3 20 30 最后保存表 完成创建 2020 3 20 31 用SQLServerManagementStudio创建表 点击保存 输入表名点击确定 课堂练习 创建表 在这个交互式多媒体中 你可以了解如何使用SQLServerManagementStudio创建表 请注意你需要为表指定一个自动增长的列作为ID 同时另外一列将拥有默认值 2020 3 20 32 语法形式 CREATETABLEtable name column name1data type DEFAULTconstant expression IDENTITY SEED INCREMENT NULL NOTNULL n ON filegroup DEFAULT 表的名字 表中列的名字 列名在表中必须唯一 列的命名必须遵循数据库对象的命名规则 列的数据类型和宽度 指定所定义的列的默认值 默认值由常量表达式确定 定义该列是一个标识列 必须同时定义起始值和增值 标识列的起始值 即插入表的第一行数据的标识列的值 标识列的增值 即插入表的最近一行相对于前一行标识列数据值的增量 指出该列是否允许为空 默认为NULL 指定在哪个文件组上创建表 DEFAULT表示将表存储在默认文件组中 用T SQL语句创建表 2020 3 20 33 用T SQL语句创建表 2020 3 20 34 例3 1创建一个教师表USEEducate 在Educate数据库中创建表GOCREATETABLE教师表 教师编号INTIDENTITY 1 1 NOTNULL 设置标识规范教师姓名CHAR 8 NOTNULL 职称CHAR 6 DEFAULT 讲师 设置默认值约束专业方向CHAR 20 系CHAR 10 联系方式CHAR 30 使用T SQL语句也可以实现数据完整性实体完整性域完整性参照完整性定义数据类型 定义NOTNULL NULL 2020 3 20 35 用T SQL语句创建表 column namedata type 列名 列的数据类型 column namedata typeNOTNULL 定义该列不能为空 可以NULL为默认情况 不需要定义 定义DEFAULT默认值约束例 给学生表的性别列添加默认值约束 2020 3 20 36 用T SQL语句创建表 column namedata typeDEFAULT常量表达式 指定该列的默认值 常量表达式可以是数值 字符串 函数等 CREATETABLE学生 学号char 10 PRIMARYKEY 姓名varchar 8 NULL 性别char 2 NULLDEFAULT 男 出生年月datetimeNULL 家庭住址varchar 50 NULL 定义检查约束方法一 方法二 在定义完所有的列之后 加上 2020 3 20 37 用T SQL语句创建表 column namedata typeCHECK expression 定义要检查的条件 可以是任何规范的表达式 CONSTRAINTconstraint name CHECK expression 约束约束名 省略则系统自动为约束提供一个名字 例 给学生表的性别列添加检查约束 2020 3 20 38 用T SQL语句创建表 CREATETABLE学生 学号char 10 PRIMARYKEY 姓名varchar 8 NULL 性别char 2 NULLDEFAULT 男 CHECK 性别 男 or性别 女 直接在列后面加检查约束出生年月datetimeNULL 家庭住址varchar 50 NULL 或者 CREATETABLE学生 学号char 10 PRIMARYKEY 姓名varchar 8 NULL 性别char 2 NULLDEFAULT 男 出生年月datetimeNULL 家庭住址varchar 50 NULL CONSTRAINTck sexCHECK 性别 男 or性别 女 在定义完所有列后 再加检查约束 约束与约束名可以省略 设置IDENTITY属性例 添加标识列 简单的例子 2020 3 20 39 用T SQL语句创建表 column namedata typeIDENTITY SEED INCREMENT 定义标识列 包括2个参数 起始值 插入表中的第一行数据的值 增量值 隔多少开始编号 只能是整型数据 CREATETABLE学生 学号intidentity 1000 1 标识列的数据类型必须是整形 要指定起始值和增量姓名varchar 8 NULL 定义主键约束单列主键例 定义单列主键 2020 3 20 40 用T SQL语句创建表 column namedata typePRIMARYKEY 设定该列为主键 CREATETABLE学生 学号char 10 PRIMARYKEY 定义学号为主键姓名varchar 8 NULL 性别char 2 NULLDEFAULT 男 出生年月datetimeNULL 家庭住址varchar 50 NULL CONSTRAINTck sexCHECK 性别 男 or性别 女 组合主键 在定义完所有的列之后定义例 定义组合主键 2020 3 20 41 用T SQL语句创建表 CONSTRAINTconstraint name PRIMARYKEY col name n 括号内为多个列用逗号分开 createtablescore Snochar 11 Cnochar 10 Graderealcheck Grade 0andGrade 100 primarykey Sno Cno 学号和课程号共同作为成绩表的主键 定义UNIQUE唯一性约束例 设定学生表的姓名列为唯一键 2020 3 20 42 用T SQL语句创建表 column namedata typeUNIQUE 设定该列为唯一键 CREATETABLE学生 学号char 10 PRIMARYKEY 姓名varchar 8 NULLUNIQUE 唯一键性别char 2 NULLDEFAULT 男 出生年月datetimeNULL 家庭住址varchar 50 NULL CONSTRAINTck sexCHECK 性别 男 or性别 女 定义外键约束 在定义完所有的列之后定义例 在成绩表中添加外键约束 2020 3 20 43 用T SQL语句创建表 CONSTRAINTname FOREIGNKEY col name1 n REFERENCEStable name column name1 n 本表中使用外键约束的列 参照表表名 参照表中的参照列 createtablescore Snochar 11 Cnochar 10 Graderealcheck Grade 0andGrade 100 primarykey Sno Cno foreignkey Sno referencesstudent Sno 学号参照学生表foreignkey Cno referencescourse Cno 课程号参照课程表 3 1 3修改表结构 1 在SQLServerManagementStudio中修改表选中要修改的表 点右键 在弹出的快捷菜单中选择修改 打开表设计器 进行修改 操作方法和创建表的时候一样 2020 3 20 44 用T SQL语句修改表 对列进行修改的语法形式为 ALTERTABLEtable nameADDcolumn namedate type DEFAULTexpression IDENTITY SEED INCREMENT NULL NOTNULL DROPCOLUMNcolumn name ALTERCOLUMNcolumn namenew datetype NULL NOTNULL 2020 3 20 45 添加列 删除列 修改列 用T SQL语句修改表 例3 2修改教师表 2020 3 20 46 1 添加列 电子邮件 数据类型是varchar 50 可以为空USEEducategoALTERTABLE教师表ADD电子邮件varchar 50 ADD后面不能加关键字COLUMN 用T SQL语句修改表 例3 2修改教师表 2020 3 20 47 2 删除职称列ALTERTABLE教师表DROPCOLUMN职称 第一次运行报错 消息5074 级别16 状态1 第1行对象 DF 教师表 职称 634EBE90 依赖于列 职称 消息4922 级别16 状态9 第1行由于一个或多个对象访问此列 ALTERTABLEDROPCOLUMN职称失败 必须先把约束删除掉 修改如下 ALTERTABLE教师表 删除约束 具体内容下面细讲DROPCONSTRAINTDF 教师表 职称 634EBE90goALTERTABLE教师表DROPCOLUMN职称 用T SQL语句修改表 例3 2修改教师表 2020 3 20 48 3 修改列 将教师编号列改为位的字符串ALTERTABLE教师表ALTERCOLUMN教师编号char 6 第一次运行报错 消息2749 级别16 状态3 第1行标识列 教师编号 的数据类型必须是int bigint smallint tinyint或decimal 或者是小数位数为0的numeric数据类型 并且约束为不可为空 必须先把IDENTITY属性删除掉 通过删除该列完成 修改如下 ALTERTABLE教师表DROPCOLUMN教师编号goALTERTABLE教师表ADD教师编号char 6 注意 只能修改列的数据类型 以及列值是否为空 用T SQL语句修改表 使用ALTERTABLE语句创建与删除约束ALTERTABLEtable name WITHCHECK WITHNOCHECK ADD CONSTRAINTconstraint name constraint typeALTERTABLEtable nameDROPCONSTRAINTconstraint name 2020 3 20 49 新加入的约束对表中现有的数据是否进行检查 要创建的约束的类型 用法与在创建表添加约束一致 要删除的约束的名字 使用ALTERTABLE语句创建主键约束ALTERTABLEtable nameADD CONSTRAINTconstraint name PRIMARYKEY col name n 用T SQL语句修改表 4 将教师编号设为主键USEEducategoALTERTABLE教师表ALTERCOLUMN教师编号char 6 notnull 先设定教师编号为非空 因为不能在可为空的列上定义PRIMARYKEY约束goALTERTABLE教师表ADDPRIMARYKEY 教师编号 2020 3 20 50 注意 即使在创建主键约束时带有WITHNOCHECK选项 系统总要对现存数据进行检查 若现有数据在该列上出现重复或空值 SQLServer会提示错误信息 并拒绝执行创建主键约束操作 用T SQL语句修改表 使用ALTERTABLE语句创建唯一性 UNIQUE 约束ALTERTABLEtable nameADD CONSTRAINTconstraint name UNIQUE col name n 2020 3 20 51 5 给教师姓名列设定唯一键USEEducategoALTERTABLE教师表ADDUNIQUE 教师姓名 用T SQL语句修改表 使用ALTERTABLE语句创建CHECK约束ALTERTABLEtable nameADD CONSTRAINTconstraint name CHECK expression 2020 3 20 52 6 给教师表的系列设定只能输入计科系与信管系的检查约束USEEducategoALTERTABLE教师表ADDCHECK 系 计科系 or系 信管系 用T SQL语句修改表 使用ALTERTABLE语句创建默认值约束ALTERTABLEtable nameADD CONSTRAINTconstraint name DEFAULT expression FORcolumn name 2020 3 20 53 7 给教师表的专业方向列设定一个默认值 计算机USEEducategoALTERTABLE教师表ADDdefault 计算机 for专业方向 用T SQL语句修改表 使用ALTERTABLE语句创建外键约束ALTERTABLEtable nameADD CONSTRAINTconstraint name FOREIGNKEY col name1 n REFERENCEStable name column name1 n 2020 3 20 54 8 设定教师表的教师编号列参照本校教师的教师编号列USEEducategoALTERTABLE教师表ADDFOREIGNKEY 教师编号 REFERENCES本校教师 教师编号 注意 当将外键约束添加到一个已有数据的列上时 默认情况下 SQLServer将会自动检查表中已有数据 以确保所有的数据和主键保持一致 用T SQL语句修改表 2020 3 20 55 使用ALTERTABLE语句删除约束ALTERTABLEtable nameDROPCONSTRAINTconstraint name 9 删除教师表的系列上的检查约束USEEducategoALTERTABLE教师表DROPCONSTRAINTCK 教师表 系 6AEFE058 必须给出正确的的约束名字 3 1 4删除表 将表从数据库中删除 不仅删除了表的结构 还包括表中的所有对象 如数据 约束和索引等如果要删除通过FOREIGNKEY和UNIQUE或PRIMARYKEY约束相关联的表 则必须先删除具有FOREIGNKEY约束的表 如果要删除FOREIGNKEY约束中引用的表但不能删除整个外键表 则必须删除FOREIGNKEY约束 2020 3 20 56 3 1 4删除表 1 在SQLServerManagementStudio中删除表 2020 3 20 57 点这里 3 1 4删除表 2 用T SQL语句删除表语法形式 DROPTABLEtable name例3 3删除数据库 教务管理 中的 成绩 表DROPTABLE成绩 2020 3 20 58 3 1 5临时表 临时表存储在系统数据库tempdb中 当不再使用时会自动删除 临时表有两种类型 本地的和全局的 本地临时表的名称以单个数字符号 打头 它们仅对当前的用户连接是可见的 当用户从SQLServer实例断开连接时被删除 全局临时表的名称以两个数字符号 打头 创建后对任何用户都是可见的 当所有引用该表的用户从SQLServer断开连接时被删除 2020 3 20 59 3 2表中数据的修改 3 2 1表中数据的插入3 2 2表中数据的删除3 2 3表中数据的字段值的修改3 2 4数据关系图的使用 2020 3 20 60 3 2 1表中数据的插入 1 用SQLServerManagementStudio输入数据展开数据库 展开表 选择要输入数据的表节点 点右键 弹出快捷菜单 选择 打开表 2020 3 20 61 点这里 直接在这里输入 如果输入内容不符合要求 系统会给出相应的提示 3 2 1表中数据的插入 2 使用T SQL语句插入数据语法形式INSERT INTO table name column name n VALUES expression NULL DEFAULT n 2020 3 20 62 要插入数据的表名 要插入数据的列名 每个列之间用逗号分隔 与列名相对应的字段的值 也可以输入NULL或DEFAULT 每个值用逗号分隔 3 2 1表中数据的插入 2020 3 20 63 例 向外聘教师表中输入一组数据USEEducategoINSERT外聘教师 教师编号 教师姓名 职称 VALUES 300212 王天明 副教授 3 2 1表中数据的插入 在插入数据时 对于允许为空的列可使用NULL插入空值 对于具有默认值的列可使用DEFAULT插入默认值当向表中所有列都插入新数据时 可以省略列名表 但必须保证VALUES后的各数据项位置同表定义时的顺序一致 否则系统会报错 2020 3 20 64 插入时使用NULL或DEFAULT 这里省略了列名 与表定义的顺序一致 3 2 1表中数据的插入 具有IDENTITY属性的列 其值由系统给出 用户不能向表中插入数据 2020 3 20 65 不能给标识列输入数据 系统自动编号 3 2 1表中数据的插入 使用INSERTINTO语句一次只能插入一行数据 若想一次插入多行数据 则需在INSERTINTO语句中加入查询子句SELECT 通过SELECT子句从其它表中选出符合条件的数据 再将其插入到指定的表中 语法形式 INSERT INTO dest table name column name n SELECTcolumn name n FROMsource table name WHEREsearch conditions 功能 先从source table name表中找出符合条件的所有数据 从中选择所需要的列 将其插入到dest table name表中 2020 3 20 66 3 2 1表中数据的插入 2020 3 20 67 注意 要插入数据的表dest table name必须是已经存在的 不能向不存在的表中插入数据 要插入数据的表dest table name中的列和SELECT子句中的列的数量 顺序必须相同 列的数据类型也要相同 3 2 2表中数据的删除 1 用SQLServerManagementStudio删除数据展开数据库 展开表 选择要输入数据的表节点 点右键 弹出快捷菜单 选择 打开表 2020 3 20 68 点这里 点这里 点这里 3 2 2表中数据的删除 1 用SQLServerManagementStudio删除数据删除多行 2020 3 20 69 点这里 点这里 3 2 2表中数据的删除 2 使用T SQL语句删除数据语法形式DELETE FROM table name WHEREsearch conditions 功能 删除表中符合search conditions的数据 缺省WHERE子句时 表示删除表中所有数据 2020 3 20 70 例3 4从学生表中删除学号为的学生USEEducategoDELETEfrom学生WHERE学号 9810100502 3 2 3表中数据的字段值的修改 1 用SQLServerManagementStudio修改数据展开数据库 展开表 选择要输入数据的表节点 点右键 弹出快捷菜单 选择 打开表 2020 3 20 71 鼠标点进单元格即可进行修改 3 2 3表中数据的字段值的修改 2 使用T SQL语句修改数据语法形式UPDATEtable nameSETcolumn name expression n WHEREsearch conditions 2020 3 20 72 要更新数据的表名 要更新数据的列名 更新后的数据值 更新条件 只对表中满足该条件的记录进行更新 3 2 4数据关系图的使用 1使用关系图显示数据库的完整性 2020 3 20 73 3 2 4数据关系图的使用 2通过关系图建立外键关系1 展开要创建外键约束的表所在的数据库 选择 数据库关系图 节点 点右键 在弹出的快捷菜单中选择 新建数据库关系图 命令 2020 3 20 74 点这里 3 2 4数据关系图的使用 2 在弹出的窗口中选择要建立主 外键关系的表 点击 添加 然后关闭该窗口 2020 3 20 75 选择表 点这里 3 2 4数据关系图的使用 3 工作区出现选择的表 2020 3 20 76 3 2 4数据关系图的使用 4 点击成绩表中的主键学号 按住鼠标左键不放拖动到学生表的学号字段后松开左键 系统弹出2个对话框 分别单击 确定 即可建立外键关系 2020 3 20 77 点这里 3 2 4数据关系图的使用 5 采用同样方法创建成绩表和课程表之间的参照关系 得到下面的关系图 单击工具栏的 保存 按钮 给新建的关系图起名字 单击 确定 2020 3 20 78 2020 3 20 79 选择是 Anyquestion 2020 3 20 80 数据查询 第三章第二部分 2020 3 20 81 是数据库中检索信息的唯一方式 SELECT是关键字 2020 3 20 82 2020 3 20 82 3 3表中数据的检索 2020 3 20 83 2020 3 20 83 3 3 1SELECT语句概述 SELECT语句可以完成以下工作投影 用来选择表中的列选择 用来选择表中的行 2020 3 20 84 2020 3 20 84 3 3 1SELECT语句概述 联接 将两个关系拼接成一个关系联接是通过联接条件来控制的 联接条件为公共属性 或者具有相同语义 可比的属性 2020 3 20 85 2020 3 20 85 3 3 1SELECT语句概述 SELECT语句可从数据库中检索信息 形式最简单的SELECT语句必须包含以下内容 一个SELECT子句 该子句指定要显示的列一个FROM子句 该子句指定SELECT子句中的各列所在的表 2020 3 20 86 2020 3 20 86 3 3 1SELECT语句概述 SELECT ALL DISTINCT TOPn PERCENT select list INTOnew table FROMtable name WHEREsearch condition GROUPBYgroup by expression HAVINGsearch condition ORDERBYorder expression ASC DESC 2020 3 20 87 关键字 关键字 2020 3 20 87 3 3 2列的检索 选择列就是垂直地划分数据 限制返回结果中的列 这由SELECT语句中的SELECT子句指定要返回的列来控制SELECTselect listFROMtable name 2020 3 20 88 由select list决定 2020 3 20 88 选择所有列 在SELECT关键字后加上一个星号 可以显示表中数据的所有列 SELECT FROMtable name还可以通过单独列出表中的各个列来显示它们 例3 6查询本校教师信息 2020 3 20 89 SELECT教师编号 教师姓名 系 专业方向 职称From本校教师 SELECT FROM本校教师 2020 3 20 89 选择所有列 2020 3 20 90 2020 3 20 90 选择特定的列 若要选择表中的特定列 只需在SELECT子句中列出每个所需的列名并使用逗号分隔每个名称即可 SELECTcolumn name1 column name2 FROMtable name 2020 3 20 91 注意 在数据查询时 列的显示顺序由SELECT语句的SELECT子句指定 顺序可以和列定义时顺序不同 这并不影响数据在表中的存储顺序 在查找多列内容时 用 将各字段分开 例3 7选择 学生 表中的姓名 联系电话 家庭住址和邮编SELECT姓名 联系电话 家庭住址 邮编FROM学生 2020 3 20 91 使用算术运算符 在SELECT子句中可以使用算术运算符对数值型数据列进行加 减 乘 除 和取模 运算 构造计算列 注意 使用这些运算符并不能在表中创建新列 也不能更改实际的数据值 这些计算的结果仅显示在输出中 例 从Northwind数据库的Products表中查询产品ID ProductID 产品名称 ProductName 产品单价 UnitPrice 产品库存量 UnitsInStock 及产品的总价值 2020 3 20 92 使用算术运算符 2020 3 20 93 2020 3 20 93 用 连接字符串 2020 3 20 94 2020 3 20 94 使用系统函数 getdate 取当前系统时间year 时间 取指定日期的年份 2020 3 20 95 2020 3 20 95 怎么知道有哪些系统函数 查看系统函数 数据库 Educate 可编程性 函数 系统函数 在12个类别中展开其中一类 具体某个函数 参看参数与返回值鼠标指向某函数时可看到说明函数作用的系统提示 2020 3 20 96 2020 3 20 96 使用别名 为了增加查询的可读性 可以给列取别名两种方法 列名AS 列标题 AS关键字可省 列标题 列名 例 19查询学生的姓名 性别和年龄 给通过计算得到的年龄列取一个新列名 年龄 2020 3 20 97 2020 3 20 97 使用别名 2020 3 20 98 2020 3 20 98 增加说明列 为了增加查询结果的可读性 可以在SELECT子句中增加一些说明列 增加的说明文字串需用单引号括起来 2020 3 20 99 2020 3 20 99 3 3 3行的检索 使用WHERE子句的目的是从表中筛选出符合条件的行 其语法形式如下 SELECTcolumn name1 column name2 FROMtable nameWHEREsearch conditionSQLServer支持的查询条件包括比较运算 逻辑运算 模糊匹配 范围 列表 以及是否为空等 2020 3 20 100 定义查询条件 2020 3 20 100 比较搜索条件 使用比较运算符WHERE子句中允许使用的比较运算符包括 2020 3 20 101 2020 3 20 101 使用比较运算符 2020 3 20 102 2020 3 20 102 使用逻辑运算符 在WHERE子句中可以使用逻辑运算符把若干个查询条件连接起来 构成一个复杂的条件进行查询 可以使用的逻辑运算符包括 逻辑与 AND 逻辑或 OR 和逻辑非 NOT 语法形式如下 NOT search condition AND OR NOT search condition 2020 3 20 103 2020 3 20 103 比较搜索条件 2020 3 20 104 2020 3 20 104 比较搜索条件 2020 3 20 105 注意 如果NOT所修饰的查询条件不是单个的 而是复合的 则需要用圆括号 把复合条件括起来 2020 3 20 105 使用查询范围 在WHERE子句中使用BETWEEN关键字可以对表中某一范围内的数据进行查询 系统将逐行检查表中的数据是否在BETWEEN关键字设定的范围内 如果在其设定的范围内 则取出该行 否则不取该行 语法形式如下 column name NOT BETWEEN表达式1AND表达式2等同于使用 和and所限定的范围 2020 3 20 106 2020 3 20 106 使用查询范围 2020 3 20 107 2020 3 20 107 使用查询列表 如果列值的取值范围不是一个连续的区间 而是一些离散的值 就应使用SQLServer提供的另一个关键字IN 语法形式如下 column name NOT IN value1 value2 等同于使用or连接的多个条件 2020 3 20 108 2020 3 20 108 使用查询列表 2020 3 20 109 2020 3 20 109 字符串模糊匹配 前面的查询条件是确定的 但如果条件不确定 必须使用LIKE关键字进行模糊查询 语法形式如下 expression NOT LIKE string 可以使用四种匹配符 2020 3 20 110 匹配字符串 2020 3 20 110 字符串模糊匹配 2020 3 20 111 2020 3 20 111 字符串模糊匹配 2020 3 20 112 2020 3 20 112 字符串模糊匹配 2020 3 20 113 2020 3 20 113 课堂练习 1 小王需要显示从2007年1月1日到2007年12月31日雇佣的所有职员的姓名和雇佣日期 职员信息表tblEmployees包含列Name和列HireDate 下面哪些语句能完成该功能 A SELECTName HireDateFROMtblEmployeesB SELECTName HireDateFROMtblEmployeesWHEREHireDate 2007 01 01 ORHireDate 2007 12 31 C SELECTName HireDateFROMtblEmployeesWHEREHireDateBETWEEN 2006 12 31 AND 2008 01 01 D SELECTName HireDateFROMtblEmployeesWHEREDATEPART yy HireDate 2007 2020 3 20 114 2020 3 20 114 空值的判定 在SQLServer中 用NULL表示空值 它仅仅是一个符号 不等于空格 也不等于0若要在查询中测试空值 可在WHERE子句中使用ISNULL或ISNOTNULL语法形式如下 column nameIS NOT NULL 2020 3 20 115 例 17查询成绩表中无成绩的学号和课程 USEEducategoSELECT学号 课程号FROM成绩WHERE成绩ISNULL 2020 3 20 115 DISTINCT关键字 使用DISTINCT关键字可以从返回的结果数据集中删除重复的行 语法形式为 SELECT ALL DISTINCT column name1 column name2 FROMtable nameWHEREsearch condition 2020 3 20 116 允许重复数据出现 是默认的关键字 从结果集中删除重复的行 注意 DISTINCT关键字的作用范围是整个查询的结果集 而不是单独的一列 如果同时对两列数据进行查询时 使用了DISTINCT关键字 将返回这两列数据的唯一组合 注意 DISTINCT关键字可以返回简单明了的数据 但服务器需要花费额外的时间对查询到的数据进行分类 管理 使查询速度减慢 若不必要 不使用DISTINCT关键字 2020 3 20 116 DISTINCT关键字 2020 3 20 117 2020 3 20 117 TOP关键字 使用TOP关键字可以返回表中前面一定数量的数据 语法形式为 SELECT TOPinteger TOPintegerPERCENT column nameFROMtable name 2020 3 20 118 表示返回表中最前面的几行 用integer表示返回的行数 用百分比表示返回的行数 2020 3 20 118 TOP关键字 2020 3 20 119 2020 3 20 119 3 3 5数据转换 有时在查询中需要将结果以另外一种形式显示出来可以使用CASE子句指定数据的转换 2020 3 20 120 2020 3 20 120 3 3 11排序 在SQLServer中 通过ORDERBY子句 可以将查询结果进行排序显示 其语法形式为 SELECTselect listFROMtable nameWHEREconditionORDERBYcolumn name alias position ASC DESC 2020 3 20 121 列名 别名 位置 按升序排列 可省略 按降序排列 2020 3 20 121 排序 2020 3 20 122 注意 ORDERBY子句默认为升序排列 即默认使用ASC关键字 如果特别要求按降序排列 必须使用DESC关键字 2020 3 20 122 排序 可以使用列所处的位置来指定排序列 2020 3 20 123 第4个列 2020 3 20 123 排序 当ORDERBY子句指定了多个排序列时 系统先按照ORDERBY子句中第一列的顺序排列 当该列出现相同值时 再按照第二列的顺序排列 依次类推 2020 3 20 124 2020 3 20 124 课堂练习 2 在SQLServer2005创建订单表 创建语句如下 CREATETABLE订单表 订单代号intIDENTITY 1 1 PRIMARYKEY 客户代号intnotnull 雇员代号intnotnull 经手该笔业务的雇员的代号订单日期datetimenotnull 销售金额moneynotnull 备注varchar 200 null 小王需要获得订单信息列表 包括雇员代号 销售金额和订单日期 他想按日期从近到早的顺序显示 并且对于每一天的订单 按销售金额从大到小的顺序排序 下面哪条语句能够准确地完成该任务 2020 3 20 125 2020 3 20 125 课堂练习 A SELECT雇员代号 销售金额 订单日期FROM订单表ORDERBY销售金额 订单日期DESCB SELECT雇员代号 销售金额 订单日期FROM订单表ORDERBY订单日期 销售金额DESCC SELECT雇员代号 销售金额 订单日期FROM订单表ORDERBY订单日期DESC 销售金额DESCD SELECT雇员代号 销售金额 订单日期FROM订单表ORDERBY销售金额DESC 订单日期DES 2020 3 20 126 2020 3 20 126 课堂练习 3 下面哪些关键字能影响SELECT语句返回的结果的行数 多选 A TOPnB WHEREC ORDERBYD DISTINCT 2020 3 20 127 2020 3 20 127 3 3 8连接查询 在实际查询中 有时所需要的信息来自于不同的表SQLServer提供了实现多表查询的方法 连接查询 所谓连接查询是将多个表通过某个或某些列为条件进行连接 从中检索出关联数据 两个表在查询中的关联方式 指定每个表中要用于联接的列 具有相同名字的列 指定用于比较各列的值的逻辑运算符 例如 或 2020 3 20 128 2020 3 20 128 3 3 8连接查询 语法形式 SELECTcolumn listFROM table name1 join type JOINtable name2ONconnection condition n WHEREsearch conditionorSELECTcolumn listFROMtable name1 n WHERE search conditionAND ORconnection condition n connection condition 为连接条件 join type 为连接类型 2020 3 20 129 2020 3 20 129 内连接 INNERJOIN 所谓内连接是多个表通过连接条件中共享列的值进行的比较连接 当未指明连接类型是 缺省为内连接 内连接值显示两个表中所有匹配数据的行 2020 3 20 130 2020 3 20 130 内连接 例3 24查询学生姓名 所选课程和各科成绩 表及表结构如下 2020 3 20 131 2020 3 20 131 内连接 2020 3 20 132 2020 3 20 132 内连接 2020 3 20 133 限定列名 注意 当单个查询引用多个表时 所有列都必须明确 在查询所引用的两个或多个表之间 任何重复的列名都必须用表名限定 如学生 学号 表示引用了学生表中的学号列 2020 3 20 133 内连接 为了增加可读性 可以使用表的别名 表的别名的命令语法形式为 FROMtable nametable alias 2020 3 20 134 表的别名 定义别名 用别名代替表名 注意 一旦使用了别名代替某个表名 则在查询时必须用表的别名 不能再用表的原名 2020 3 20 134 外连接 O