第五章Transact_SQL语言

2020 4 6 数据库应用技术 SQLServer2005 1 一 教学目标与要求 1 了解SQL语言简介2 熟练掌握简单数据查询 查询指定的列3 掌握查询结果排序4 熟练掌握多表查询5 熟练掌握数据插入和删除6 掌握数据库操作7 逐步掌握Transact SQL程序设计8 难点 多表查询 Transact SQL程序设计二 教学手段 1 PPT理论讲解 2 演示操作 链接播放教学 3 提问互动 引导自学 第五章Transact SQL语言 2020 4 6 数据库应用技术 SQLServer2005 2 回顾SQLServer的验证模式验证模式的设置SQLServer2005的账户的设置用户的创建用户的权限设置SQLServer2005的角色的创建角色的权限设置 2020 4 6 数据库应用技术 SQLServer2005 3 第五章Transact SQL语言 教学目标1 了解SQL语言简介2 熟练掌握简单数据查询 查询指定的列3 掌握查询结果排序4 熟练掌握多表查询5 熟练掌握数据插入和删除6 掌握数据库操作7 逐步掌握Transact SQL程序设计 第五章Transact SQL语言 2020 4 6 数据库应用技术 SQLServer2005 4 第五章Transact SQL语言 5 1SQL语言 SQL的全称为StructuredQueryLanguage 结构化查询语言 它利用一些简单的句子构成基本的语法 来存取数据库的内容 由于SQL简单易学 目前它已经成为关系数据库系统中使用最广泛的语言 2020 4 6 数据库应用技术 SQLServer2005 5 第五章Transact SQL语言 5 1 1SQL语言概述 由于在业界有多种关系型数据库系统 因此各家公司都可能有自己的SQL语法或者可以定义不同的数据类型 SQL语言是应用于数据库的语言 本身是不能独立存在的 它是一种非过程性 non procedural 语言 与一般的高级语言 例如C C Pascal 是大不相同的 一般的高级语言在存取数据库时 需要依照每一行程序的顺序处理许多的动作 但是使用SQL时 只需告诉数据库需要什么数据以及怎么显示即可 具体的内部操作则由数据库系统来完成 问题 1分 基于过程 结构化 面向对象语言举例说明 2020 4 6 数据库应用技术 SQLServer2005 6 第五章Transact SQL语言 5 1 2分类 SQL语言按照用途可以分为如下3类 DDL DataDefinitionLanguage 数据定义语言 DML DataManipulationLanguage 数据处理语言 DCL DataControlLanguage 数据控制语言 2020 4 6 数据库应用技术 SQLServer2005 7 第五章Transact SQL语言 5 2Transact SQL基础 SQLServerManagementStudio是一个图形用户界面 用以交互地设计和测试Transact SQL语句 批处理和脚本 在SQLServerManagementStudio中 用户可输入Transact SQL语句 执行语句并在结果窗口中查看结果 2020 4 6 数据库应用技术 SQLServer2005 8 第五章Transact SQL语言 5 2 1数据查询 SQL语言中最主要 最核心的部分是它的查询功能 查询语言用来对已经存在于数据库中的数据按照特定的组合 条件表达式或者一定次序进行检索 其基本格式是由SELECT子句 FROM子句和WHERE子句组成的SQL查询语句 SELECT 列名表 FROM WHERE 一般语言的语法标注格式 SELECTselect list INTOnew talbe FROMtable source WHEREsearch condition GROUPBYgroup by expression HAVINGsearch condition ORDERBYorder expression ASC DESC 2020 4 6 数据库应用技术 SQLServer2005 9 第五章Transact SQL语言 SQL语言中的语法基本格式 SELECT语句的语法格式 SELECT查询列表 INTO新表 查询的结果集进新表 FROMsource 表列表 号分割 WHERE搜索条件 按条件筛选 GROUPBY分组 分类 表达式 按表达式分类 HAVING按GROUPBY指定分组过滤条件 ORDERBY排序表达式 ASC DESC 升降序 COMPUTE子句 查询结果集后生成一个汇总数据行 2020 4 6 数据库应用技术 SQLServer2005 10 第五章Transact SQL语言 5 2 1数据查询 1 查询表中所有的列使用格式 SELECT FROMtable name2 查询表中指定的列使用格式 SELECTcolumn name n FROMtable name3 使用单引号加入字符串4 使用别名5 查询特定的记录6 对查询结果进行排序 ORDERBYorder expression ASC DESC 7 多表查询图5 9消除重复的行8 消除重复的行 转入应用 2020 4 6 数据库应用技术 SQLServer2005 11 第五章Transact SQL语言 5 2 2数据插入和删除 新增数据使用INSERT语句 其语法如下 INSERT INTO table name column list VALUES data values 5 2 3数据修改 修改数据需要使用UPDATE语句 其语法如下 UPDATEtable nameSETcolumn WHEREcondition 例如 将authors表中名为 王小明 的作者全部改为 王明 SQL语句如下 UPDATEauthorsSETauthor name 王明 WHEREauthor name 王小明 2020 4 6 数据库应用技术 SQLServer2005 12 第五章Transact SQL语言 5 2 4使用函数 SQLServer提供了内置函数 使用这些函数 可以插入当前日期 对文本和图像进行操作等 例如 在orderform表中 提交一笔订单 在插入数据时 即可使用GETDATE 函数来获取当前的日期 为了保持数据完整性 首先在clients表中插入一个客户 然后再在orderform表中提交一笔订单 输入下面的SQL语句 INSERTclientsVALUES 1 刘明耀 北京市海淀区 INSERTorderformVALUES 1 2 50 GETDATE 1 DROPTABLEtable name 2020 4 6 数据库应用技术 SQLServer2005 13 第五章Transact SQL语言 5 2 5使用公式 在列出现的位置上 可以使用公式对查询结果进行计算 例如 要查询所有订单中的书名 数量和总额 此时 就可以使用公式来计算总额 输入的SQL语句如下 SELECTbook book name orderform book number 总额为 book price orderform book number FROMorderform bookWHEREorderform book id book book id 2020 4 6 数据库应用技术 SQLServer2005 14 第五章Transact SQL语言 5 2 6数据库的操作语句 1 创建数据库CREATEDATABASEdatabase name2 修改数据库在建立数据库后 可根据需要修改数据库的设置 修改数据库可以使用ALTERDATABASE语句 3 使用和删除数据库图5 15删除销售数据库要使用和删除数据库 可以使用USE和DROP语句 其语法如下 USEDATABASEdatabase nameDROPDATABASEdatabase name 2020 4 6 数据库应用技术 SQLServer2005 15 第五章Transact SQL语言 5 2 7表的操作语句 1 表的创建CREATETABLE语句用来建立表 其语法如下 CREATETABLEtable name column name1data type NULL NOTNULL PRIMARY UNIQUE FOREIGNKEY column name REFERENCESref table ref column column name2data type 2020 4 6 数据库应用技术 SQLServer2005 16 第五章Transact SQL语言 2 修改表ALTERTABLEtable nameADD column namedata type PRIMARYKEY CONSTRAIN FOREIGNKEY column name REFERENCESref table name ref column name DROP CONSTRAINT constraint name COLUMNcolumn name3 删除关联和表DROPTABLEtable name 2020 4 6 数据库应用技术 SQLServer2005 17 第五章Transact SQL语言 5 3SELECT高级查询 本节主要介绍数据汇总 连接查询 子查询和UNION关键词的使用 2020 4 6 数据库应用技术 SQLServer2005 18 第五章Transact SQL语言 5 3 1数据汇总查询 为决策支持系统生成聚合事务的汇总报表是一项复杂并且相当消耗资源的工作 SQLServer2005提供以下两个灵活且强大的组件 用于生成SQLServer2005AnalysisServices 数据转换服务 DTS MicrosoftSQLServerAnalysisServices数据汇总查询1 聚合函数2 GROUPBY子句3 HAVING子句4 COMPUTE和COMPUTEBY子句5 COMPUTE和GROUPBY的区别 2020 4 6 数据库应用技术 SQLServer2005 19 分类汇总 常用统计 聚合 函数AVG ALL DISTINCT 表达式 返回 全部或去除重复行 表达式的平均值 SUM ALL DISTINCT 表达式 返回 全部或去除重复行 表达式的和 MAX ALL DISTINCT 表达式 返回 全部或去除重复行 表达式的最大值 MIN ALL DISTINCT 表达式 返回 全部或去除重复行 表达式的最小值 COUNT ALL DISTINCT 表达式 返回 全部或去除重复行 表达式的行数 第五章Transact SQL语言 2020 4 6 数据库应用技术 SQLServer2005 20 例6 查询成绩表 学号 姓名 C语言 VB SQLServer 显示各科的平均分 总分 最高分 最低分 参加考试的人数 SelectAVG C语言 asC 平均分 AVG VB asVB 平均分 C语言 VB SQL as个人 总分 MAX C语言 asC 最高分 MIN C语言 asC 最低分 COUNT as考试人数From成绩表 第五章Transact SQL语言 2020 4 6 数据库应用技术 SQLServer2005 21 2 使用GROUPBY子句分组 要求是 不计型号 按品名统计或有几类物品 第五章Transact SQL语言 2020 4 6 数据库应用技术 SQLServer2005 22 usenorthwindselectcount 型号 as数量 品名 avg 定价 as平均价 sum 数量 as总合from销售表where数量 20groupby品名 分类 汇总 即 分类统计 不允许出现不包含统计量的列 2020 4 6 数据库应用技术 SQLServer2005 23 3 使用COMPUTEBY子句汇总 当用户在统计汇总的分组结果集后 再生成汇总行 可以使用COMPUTE和COMPUTEBY子句 例7 第五章Transact SQL语言 2020 4 6 数据库应用技术 SQLServer2005 24 若需要显示汇总结果并且显示明细时要使用COMPUTEBY子句 注意 BY子句按某字段排序 此时 必须结合ORDERBY使用 并且 两个BY后的排序要一致 例8 第五章Transact SQL语言 2020 4 6 数据库应用技术 SQLServer2005 25 第五章Transact SQL语言 5 3 2连接查询 1 内连接内连接是用比较运算符比较要连接列的值的连接 内连接使用INNERJOIN关键词2 外连接SQLServer2005对在FROM子句中指定的外连接使用以下关键字 LEFTOUTERJOIN或LEFTJOIN 左向外连接 RIGHTOUTERJOIN或RIGHTJOIN 右向外连接 FULLOUTERJOIN或FULLJOIN 完整外连接 1 左向外连接它包括第一个命名表 左 表 出现在JOIN子句的最左边 中的所有行 不包括右表中的不匹配行 2 右向外连接它包括第二个命名表 右 表 出现在JOIN子句的最右边 中的所有行 不包括左表中的不匹配行 3 完整外连接若要通过在连接结果中包括不匹配的行保留不匹配信息 可以使用完整外连接 2020 4 6 数据库应用技术 SQLServer2005 26 第五章Transact SQL语言 5 3 2连接查询 3 交叉连接在这类连接的结果集内 两个表中每两个可能成对的行占一行 交叉连接不使用WHERE子句 在数学上 它就是表的笛卡儿积 问题1分表1 m行记录表2 n行记录结果集 2020 4 6 数据库应用技术 SQLServer2005 27 多表的联接 置于表中 select学生 学号 课程 成绩AS补考into 临时 新表 查询结果集的内容 from学生Join 类型 INNERJOIN LEFTOUTERJOIN 课程ON等值或不等值条件select学生 学号 课程 成绩AS补考into学生情况from学生INNERJOIN课程on学生 学号 课程 学号 两表中学号相同的记录 内联接 外联接 交叉联接 第五章Transact SQL语言 2020 4 6 数据库应用技术 SQLServer2005 28 第五章Transact SQL语言 5 3 3子查询 子查询是一个SELECT查询 它返回单个值且嵌套在SELECT INSERT UPDATE DELETE语句或其他子查询中 嵌套在外部SELECT语句中的子查询包括以下组件 包含标准选择列表组件的标准SELECT查询 包含一个或多个表或者视图名的标准FROM子句 可选的WHERE子句 可选的GROUPBY子句 可选的HAVING子句 子查询的SELECT查询总是用圆括号括起来 并且不能包括COMPUTE或FORBROWSE子句 如果同时指定TOP子句 则可能只包括ORDERBY子句 1 子查询规则2 子查询类型3 多层嵌套4 相关子查询 2020 4 6 数据库应用技术 SQLServer2005 29 第五章Transact SQL语言 5 3 4使用UNION运算符组合多个结果 使用UNION运算符可以将两个或多个SELECT语句的结果组合成一个结果集 例如 要查询所有作者和客户的号码和名称 可使用下面的SQL语句 USEbookdbGOSELECTauthor id author nameFROMauthorsUNIONSELECTclient id client nameFROMclientsGO 2020 4 6 数据库应用技术 SQLServer2005 30 第五章Transact SQL语言 执行结果为 author idauthor name 1刘明耀1刘耀儒2科技书店2王晓明3明天书屋3张英魁 2020 4 6 数据库应用技术 SQLServer2005 31 第五章Transact SQL语言 5 3 5在查询的基础上创建新表 使用INTO关键字可以创建新表并将结果行从查询插入新表中 例如 下面的SQL语句将查询得到的书名和作者插入到新建的表author book中 USEbookdbGOSELECTbook book name authors author nameINTOauthor bookFROMauthors bookWHEREauthors author id book author idGO 2020 4 6 数据库应用技术 SQLServer2005 32 小测验 2 在INSERT INTO VALUES格式中 VALUES可以省略Y N 1 CREATE可以创建CREATETABLE CREATEFIELDY N 3 ALTER只能用于修改TABLEY N 4 count x sum y 是一样的 都是求和函数Y N 6 UPDATE SET 格式可以缺省SET 即SET是可选项Y N 7 SELECT语句的语法格式 各个子句的顺序 SELECT FROM WHERE GROUPBY HAVING ORDERBY COMPUTEY N 5 内联接 外联接 交叉联接就是两个表的关系图Y N N Y 第五章Transact SQL语言 N 可对数据库对象 N N必须有 不能省 N 不能对字段操作 N表明了两个表组合匹配的关系 2020 4 6 数据库应用技术 SQLServer2005 33 第五章Transact SQL语言 第二部分 应用 2020 4 6 数据库应用技术 SQLServer2005 34 FROM子句FROM子句的格式 FROM表 视图 JOIN 表AS表的别名ON 连接的条件表达式 其中 AS可为表 或视图 和字段指定别名ON指定基于哪个关键字段建立 连接 FROM引导的子句格式使用说明 查询1个表或视图的全部 查询1个表或视图的全部 使用别名 结果集 select from学生 或课程或学生 v select学生 学号 S V 班级 课程 成绩AS补考from学生 课程 学生 vASS V 第五章Transact SQL语言 2020 4 6 数据库应用技术 SQLServer2005 35 多表的联接 置于表中 select学生 学号 课程 成绩AS补考into 临时 新表 查询结果集的内容 from学生Join 类型 INNERJOIN LEFTOUTERJOIN 课程ON等值或不等值条件select学生 学号 课程 成绩AS补考into学生情况from学生INNERJOIN课程on学生 学号 课程 学号 两表中学号相同的记录 第五章Transact SQL语言 结合运算符显示列 select学生 学号 课程 成绩 0 8AS补考成绩into学生情况from学生INNERJOIN课程on学生 学号 课程 学号 内联接 外联接 交叉联接 2020 4 6 数据库应用技术 SQLServer2005 36 2 SELECT子句格式 SELECT ALL DISTINCT TOPn 表名 视图名 表别名 列名 表达式 标识列 AS 列别名 n 例2 从学生 课程表中查询 搜索 学生表中学号与课程表学号相同 显示所有学生表字段和成绩表的 成绩 打八折后置于 补考成绩 分析 SQL语句select学生 课程 成绩 0 8AS补考成绩from学生 课程where学生 学号 课程 学号 包含 列别名 选择列 计算 等值联接 不包括重复的行 多表联接 第五章Transact SQL语言 2020 4 6 数据库应用技术 SQLServer2005 37 例3 从成绩表中查询 包括 学号 姓名 平时成绩 成绩 前8名学生成绩 期末成绩按70 计算 成绩显示为 笔试成绩 其前显示 折合为 字符串 将 平时成绩 和笔试成绩相加显示为 总成绩 包含 指定行 添加字符串 算术计算 成绩表 查询显示结果集 样式 selectTOP8学号 姓名 平时成绩 折合为 成绩 0 7 as笔试成绩 平时成绩 成绩 0 7as总成绩from成绩 播放 第五章Transact SQL语言 2020 4 6 数据库应用技术 SQLServer2005 38 思考 从成绩表中查询 包括 学号 姓名 平时成绩 成绩 学生成绩 平时成绩按30 期末成绩按70 计算 成绩显示为 笔试成绩 其前显示 折合为 字符串 将 平时成绩 和笔试成绩相加显示为 总成绩 要求 交教务处 交班主任生成表 总评成绩 另 把成绩表改为公司销售报表 其中包含物品折扣信息 生成报表 定价低于60元的产品 降价60 销售报表显示 型号 定价 销售价 数量 销售金额 亏损金额 5分钟交互练习 第五章Transact SQL语言 2020 4 6 数据库应用技术 SQLServer2005 39 useNorthwindgoselect学号 姓名 平时成绩 成绩 0 7 as笔试成绩 平时成绩 0 3 成绩 0 7as总成绩into总平成绩from成绩 第五章Transact SQL语言 2020 4 6 数据库应用技术 SQLServer2005 40 打开数据库Select型号 定价 定价 0 6as销售价 数量 定价 0 6 数量as销售金额 定价 数量 定价 0 6 数量 as亏损金额 第五章Transact SQL语言 2020 4 6 数据库应用技术 SQLServer2005 41 例4 函数的基本用法 包含有 工号 姓名 出生日期 工作时间 工资 的职工表要求 列出工作年限 列出某月出生的人员 分析 当前日期 登记时的工作时间 DATEDIFF date 方式 date1 date2 按date 方式 年 月 日 返回 date2 date1 从 出生日期 提取月份 打开数据库Select姓名 DATEDIFF year 工作时间 getdate as工龄From职工Select姓名 DATENAME month 出生日期 as出生月From职工DATENAME day 出生日期 March July DAY 出生日期 返回整数 3 7 问题1分查找同年 同月 同日生的好友 第五章Transact SQL语言 2020 4 6 数据库应用技术 SQLServer2005 42 思考 列出订单供货日期距今天的天数 设置报警 列出某月的订单 Use数据库IF SelectDATEDIFF day Order time getdate FromOrder 5SelectOrder ID DATEDIFF day Order time getdate as供货时限FromOrderElseBeginprint 你的时间已不多 快点干活吧 End SelectOrder ID DATENAME month Order time asOrder mFromOrderWhereDATENAME month Order time 8 子查询 返回结果集 第五章Transact SQL语言 2020 4 6 数据库应用技术 SQLServer2005 43 在查询中 某列的值会出现重复 或本身就相同 为清除重复的记录使用DISTINCT限定 注意 空值被认为是相同的值 例 SELECTDISTINCT区域FROM学生将显示区域中相同地域的第一个值 重复的不再显示 TOPn PERCENT TOPn指定返回前n行 TOPnPERCENT指定返回前n 行 因包含PERCENT n介于0 100若包含ORDERBY子句 TOPn WITHTIES时 将可能扩大显示范围 即 按某字段排序 n位指正好是连续的相同值则查询结果将包含n 连续的相同值 个数 第五章Transact SQL语言 2020 4 6 数据库应用技术 SQLServer2005 44 3 WHERE子句按限定条件查询 返回满足条件的结果集条件是由满足Transact SQL语言语法规则构成的逻辑表达式及比较和逻辑运算符与子查询连接逻辑表达式例 查询搜索1980年8月01日后出生的学生 WHERE出生日期 01 08 1980 查询搜索成绩90AND书名 我不知道 第五章Transact SQL语言 2020 4 6 数据库应用技术 SQLServer2005 45 关于ALL ANY的使用查询库存量最多的商品USE经销商SELECT商品名称 单价 库存量FROM商品信息表 库存表WHERE库存量 ALL SELECT库存量FROM库存表WHERE商品信息表 商品编号 库存表商品编号 结果集 ALL 1 2 3 大于每个 即大于最大的3 ANY 1 2 3 至少大于一个 即大于最小的1 第五章Transact SQL语言 2020 4 6 数据库应用技术 SQLServer2005 46 关于EXISTS的使用USE图书管理系统IFEXISTS SELECT图书名称FROM图书明细WHERE图书名称 Access2003教程 SELECT FROM图书明细WHERE图书名称 Access2003教程 ELSEBEGINPrint 无此书 END 查询结果集 指定的书在其中 即存在 EXISTS 第五章Transact SQL语言 2020 4 6 数据库应用技术 SQLServer2005 47 4 ORDERBY子句查询结果按某字段排序 例5 查询数据库 经销商 的 进货表 返回 进货编号 商品编号 进货数量 进货时间 按 进货时间 升序 相同时 按 金额 降序 USE经销商SELECT进货编号 商品编号 进货数量 进货时间FROM进货表ORDERBY进货时间ASC 金额DESC 注意 排序时以第一关键字先排 只有相同时才按第二关键字决定相同记录的顺序 第五章Transact SQL语言 返回 2020 4 6 数据库应用技术 SQLServer2005 48 小测验 2 select学生 学号 课程 成绩AS补考into学生情况from学生INNERJOIN课程on学生 学号 课程 学号 1 select学生 学号 S V 班级 课程 成绩AS补考from学生 课程 学生 vASS VY N 3 select学号 姓名 平时成绩 成绩 0 7 as笔试成绩 平时成绩 0 3 成绩 0 7as总成绩into总平成绩from成绩 4 Select姓名 DATEDIFF year 工作时间 getdate as工龄From职工 工作至今的时间 第五章Transact SQL语言 查询结果集存