SQL基本SELECT查询语句_内外连接.ppt

2020 2 22 MicrosoftSQLServer2005 1 Select查询 2020 2 22 MicrosoftSQLServer2005 2 SELECT语法总结 SELECT 字段名 字段名 FROM表名 WHERE条件表达式 GROUPBY分组的字段名 HAVING条件表达式 ORDERBY排序的字段名 ASC DESC 上述 表示可选项 表示取其中之一 2020 2 22 MicrosoftSQLServer2005 3 简单Select查询 语法 SELECT字段1 字段2 FROM表名SELECT FROM表名SELECT字段1 字段2 FROM表名WHERE查询条件SELECT FROM表名WHERE查询条件 简单的Transact SQL查询只包括选择列表 FROM子句和WHERE子句 它们分别说明所查询列 查询的表或视图 以及搜索条件等 2020 2 22 MicrosoftSQLServer2005 4 简单Select查询 SELECTStuId StuName DeptId SexFROMTb Student SELECT FROMTb Student 2020 2 22 MicrosoftSQLServer2005 5 简单Select查询 SELECTStuId StuName DeptId SexFROMTb StudentWHERESex F SELECT FROMTb TeacherWHERESex F andDeptId 08 2020 2 22 MicrosoftSQLServer2005 6 WHERE子句使用的运算符 2020 2 22 MicrosoftSQLServer2005 7 比较运算符和表达式 SELECT FROMTb StudentWHEREStuName 张三 SELECT FROMTb StudentWHERESex F 2020 2 22 MicrosoftSQLServer2005 8 逻辑运算符和表达式 SELECT FROMTb StudentWHERESex M andSex F SELECT FROMTb StudentWHEREClassId S040201101 andSex F SELECT FROMTb StudentWHERENOTSex M 2020 2 22 MicrosoftSQLServer2005 9 范围搜索运算符和表达式 SELECT FROMTb StudentWHEREAgeBETWEEN20TO22 SELECT FROMTb StudentWHEREAgeNOTBETWEEN20TO22 SELECT FROMTb StudentWHEREAgeIN 20 21 22 相当于SELECT FROMTb StudentWHEREAge 20ORAge 21ORAge 22 2020 2 22 MicrosoftSQLServer2005 10 模式匹配运算符和表达式 SELECT FROMTb StudentWHEREStuNameLIKE 张 SELECT FROMTb StudentWHEREStuNameNOTLIKE 张三 2020 2 22 MicrosoftSQLServer2005 11 未知值条件 NULL表示未知的 不可用或将在以后添加的数据 NULL值与零 零长度的字符串的含义不同 在WHERE子句中 使用ISNULL或ISNOTNULL条件可以查询某一数据值是否为NULL的数据信息 例如 SELECT FROMTb GradeWHERERetestScoreISNOTNULL SELECT FROMTb CourseWHERECourseNameISNULL 2020 2 22 MicrosoftSQLServer2005 12 字符串运算符和表达式 字符串表达式是用字符串连接符 将字符型常量 变量 字符串函数连接组成的式子 字符串表达式的计算结果包括两个字符串内的空格和所有字符 仍然是字符型 如 我爱 我家 表达式的值为 我爱我家 例如 Beijing Huanying Ning 请写出结果 2020 2 22 MicrosoftSQLServer2005 13 运算符的优先级 运算符的优先级决定了表达式的计算和比较操作顺序 运算符的优先级由高到低的顺序为 1 括号 2 正 负或取反运算 3 乘 除 求模运算 4 加 减 字符连接运算 5 比较运算 6 位运算 7 逻辑非运算 NOT 8 逻辑与运算 AND 9 BETWEEN IN LIKE OR等运算 10 赋值运算 2020 2 22 MicrosoftSQLServer2005 14 ORDERBY子句 使用SELECT语句进行数据查询 为了按一定顺序浏览数据 可以使用ORDERBY子句对生成的查询结果进行排序 ORDERBY子句在SELECT语句中的语法格式如下 SELECT字段1 字段2 FROM表名WHERE查询条件ORDERBY排序表达式 ASC DESC 2020 2 22 MicrosoftSQLServer2005 15 ORDERBY子句 SELECTStuId StuName SexFROMTb StudentWHEREDeptId 08 ORDERBYStuName SELECTStuId StuName SexFROMTb StudentWHEREDeptId 08 ORDERBYStuNameDESC 2020 2 22 MicrosoftSQLServer2005 16 ORDERBY子句 SELECTStuId StuName SexFROMTb StudentWHEREDeptId 08 ORDERBYStuName Sex结果如何 2020 2 22 MicrosoftSQLServer2005 17 ORDERBY子句 ORDERBY子句总是位于WHERE子句 如果有的话 后面 可以包含一个或多个列 每个列之间以逗号分隔 这些列可能是表中定义的列 也可能是SELECT子句中定义的计算列 在SQLServer2005中 汉字比其它字符值都要大 而汉字之间的大小比较是取决于其拼音首字母的大小 如果没有特别说明 值是以升序序列进行排序的 当然 你也可以选择使用ASC关键字指定升序排序 如果你希望以降序排列 则在ORDERBY后面使用DESC关键字 2020 2 22 MicrosoftSQLServer2005 18 GROUPBY子句 使用SELECT语句进行数据查询 为了按特定条件分组浏览数据 可以使用GROUPBY子句对某一列数据的值进行分类 形成结果集 GROUPBY子句在SELECT语句中的语法格式如下 SELECT字段1 字段2 FROM表名WHERE查询条件GROUPBY字段1 字段2 WITHROLLUP CUBE ORDERBY排序表达式 ASC DESC CUBE生成的结果集显示了所选列中值的所有组合 包括字段1 字段2 字段1 字段2 的分组统计结果和总的汇总统计的聚合 ROLLUP生成的结果集显示了所选列中值的某一层次结构的聚合 包括字段1 字段1 字段2的分组统计结果和总的汇总统计的聚合 2020 2 22 MicrosoftSQLServer2005 19 聚合函数 聚合函数是SQL语言中一种特殊的函数 比如SUM COUNT MAX MIN AVG等 这些函数和其它函数的根本区别就是它们一般作用在多条记录上 换句话说 上面几个返函数的值建立在对多条记录进行统计的基础上 SUM是求和函数 SELECTClassId SUM Score FROMTb GradeWHEREClassId S0408011 COUNT函数用来计算记录的数量 SelectCOUNT StuID FROMTb GradeWHEREClassId S0408011 MAX MIN AVG函数分别是用来求最大值 最小值和平均值 SELECTMAX Score MIN Score AVG Score FROMTb GradeWHEREClassId S0408011 2020 2 22 MicrosoftSQLServer2005 20 GROUPBY子句 常用统计函数 2020 2 22 MicrosoftSQLServer2005 21 GROUPBY子句 显示每个学生的班级编码和成绩SELECTClassId TotalScoreFROMTb Grade 汇总每个班级的平均成绩SELECTClassId AVG TotalScore FROMTb GradeGROUPBYClassId 2020 2 22 MicrosoftSQLServer2005 22 SELECTClassId ClassName AVG TotalScore FROMTb Grade Tb ClassGROUPBYClassId GROUPBY子句 汇总每个班级 含班级名称 的平均成绩 SELECTClassId ClassName AVG TotalScore FROMTb GradeGROUPBYClassId SELECTTb Grade ClassId ClassName AVG TotalScore FROMTb Grade Tb ClassWHERETb Grade ClassId Tb Class ClassIdGROUPBYTb Grade ClassId 2020 2 22 MicrosoftSQLServer2005 23 GROUPBY子句 显示班级名称的汇总各班平均成绩SELECTTb Grade ClassId ClassName AVG TotalScore ASAvgScoreFROMTb Grade Tb ClassWHERETb Grade ClassId Tb Class ClassIdGROUPBYTb Grade ClassId ClassName 当两个表中都有ClassId字段时 要加上表前缀 说明是那个表的ClassId字段 SELECT列表中出现的字段都必须要在GROUPBY列表中出现 聚合函数字段除外 当要显示相关主键表中的其它字段时 如这儿的ClassName字段 则在WHERE子句中通过主外键关系进行连接 2020 2 22 MicrosoftSQLServer2005 24 GROUPBY子句 查询各个班级的最大成绩和最小成绩 SELECTTb Grade ClassId ClassName MAX TotalScore ASMaxScore MIN TotalScore ASMinScoreFROMTb Grade Tb ClassWHERETb Grade ClassId Tb Class ClassIdGROUPBYTb Grade ClassId ClassName 2020 2 22 MicrosoftSQLServer2005 25 GROUPBY子句 GROUPBY分类汇总 分项统计紧跟在GROUPBY后面的字段就是分组条件 如果在前面选择列表中出现的非聚合字段未出现在GROUPBY后面 SQL语句就会出错 思考题 按照不同班级和性别统计学生平均年龄 2020 2 22 MicrosoftSQLServer2005 26 HAVING子句 HAVING子句相当于一个用于组 分类汇总 的WHERE子句 它指定了组或聚合的查询条件 HAVING子句通常与GROUPBY子句一起使用 HAVING子句的语法为 SELECT字段1 字段2 FROM表名WHERE查询条件GROUPBY字段1 字段2 HAVING组查询条件 2020 2 22 MicrosoftSQLServer2005 27 HAVING子句 显示各班级学生平均总成绩大于73的班级编号 SELECTTb Grade ClassId AVG TotalScore ASAvgScoreFROMTb GradeGROUPBYTb Grade ClassIdHAVINGAVG TotalScore 73 2020 2 22 MicrosoftSQLServer2005 28 HAVING子句 求各个班级的最大成绩和最小成绩 且最低成绩小于50分的 SELECTTb Grade ClassId MAX TotalScore ASMaxScore MIN TotalScore ASMinScoreFROMTb GradeGROUPBYTb Grade ClassIdHAVINGMIN TotalScore 50 2020 2 22 MicrosoftSQLServer2005 29 HAVING子句 HAVING子句用来对分组后的数据进行筛选 它后面会跟一个条件表达式 当在GROUPBY子句中使用HAVING子句时 查询结果中只返回满足HAVING条件的组 2020 2 22 MicrosoftSQLServer2005 30 SELECT语法总结 SELECT 字段名 字段名 FROM表名 WHERE条件表达式 GROUPBY分组的字段名 HAVING条件表达式 ORDERBY排序的字段名 ASC DESC 上述 表示可选项 表示取其中之一 2020 2 22 MicrosoftSQLServer2005 31 计算列 SELECT后面还可以是计算列 在SELECT后面的计算列 只是位于该SELECT语句中 是临时性的 它由一个表达式产生 SELECTStuId StuName Sex FROMTb Student上面语句中的StuName Sex 就构成了一个计算列 它的值由两个字符型值和一对括号连接组成 2020 2 22 MicrosoftSQLServer2005 32 列别名 在上面的结果集中 列名都是英文 这样看上去很不直观 我们希望能够给列名换上诸如 学号 姓名 等这样的名字 只需在SELECT语句中 给列取个别名即可 AS可以省去 SELECTStuIdAS学号 StuName Sex AS姓名性别FROMTb StudentSELECTStuId学号 StuName Sex 姓名性别FROMTb StudentSELECT学号 StuId 姓名性别 StuName Sex FROMTb Student 2020 2 22 MicrosoftSQLServer2005 33 当然 我们也可以给表指定列别名 给表取别名和给列取别名类似 但只可以用 原始表名AS别名 和 原始表名别名 这两种方式 SELECT FROMTb StudentST或者SELECT FROMTb StudentASST 字段别名不能放在WHERE子句中 而表别名可以放在WHERE子句中 表别名 2020 2 22 MicrosoftSQLServer2005 34 多表查询 2020 2 22 MicrosoftSQLServer2005 35 查询全院各系部拥有班级的基本情况 显示系部名称 班级编码和班级名称 多表查询 2020 2 22 MicrosoftSQLServer2005 36 USETeaching SystemGOSELECTd c FROMTb ClassASc Tb DeptASd 多表查询 2020 2 22 MicrosoftSQLServer2005 37 笛卡尔乘积 多表查询 2020 2 22 MicrosoftSQLServer2005 38 多表查询 2020 2 22 MicrosoftSQLServer2005 39 多表查询 2020 2 22 MicrosoftSQLServer2005 40 USETeaching SystemGOSELECTd c FROMTb ClassASc Tb DeptASdWHEREc DeptId d DeptId 多表查询 2020 2 22 MicrosoftSQLServer2005 41 多表查询 SELECTStuId StuName DeptId SpecId ClassId SexFROMTb Student SELECTStuId学号 StuName姓名 DeptId系部 SpecId专业 ClassId班级 Sex性别FROMTb Student 2020 2 22 MicrosoftSQLServer2005 42 两表连接 SELECTStuId学号 StuName姓名 DeptName系部 SpecId专业 ClassId班级 Sex性别FROMTb StudentTS Tb DeptTDWHERETS DeptId TD DeptId 2020 2 22 MicrosoftSQLServer2005 43 多表连接基础 在实际查询应用中 用户所需要的数据并不全部在一个表中 这时就需要多表查询 从多个表中抽取我们所需要的数据信息 多表查询实际上是通过各个表之间的共同列的相关性 主外键关系 来查询数据的 这是关系数据库查询数据的主要特征 多表查询首先要用WHERE子句在表之间建立连接关系 在进行多表查询时 可以遵循以下基本原则 1 SELECT子句列表中 字段前要加上基表名称 2 FROM子句应包括所有使用的基表 3 WHERE子句应在表之间建立连接关系 2020 2 22 MicrosoftSQLServer2005 44 两表连接 SELECTStuId学号 StuName姓名 DeptId系部编码 DeptName系部 SpecId专业 ClassId班级 Sex性别FROMTb StudentTS Tb DeptTDWHERETS DeptId TD DeptId SELECTStuId学号 StuName姓名 TS DeptId系部编码 DeptName系部 SpecId专业 ClassId班级 Sex性别FROMTb StudentTS Tb DeptTDWHERETS DeptId TD DeptId 2020 2 22 MicrosoftSQLServer2005 45 多表连接 SELECTStuId学号 StuName姓名 DeptName系部 SpecName专业 ClassName班级 Sex性别FROMTb StudentTS Tb DeptTD Tb SpecTSP Tb ClassTCWHERETS DeptId TD DeptIdANDTS SpecId TSP SpecIdANDTS ClassId TC ClassId 要求显示学生姓名和所在系名 专业 班级名称 2020 2 22 MicrosoftSQLServer2005 46 各种连接查询 内连接 内连接是一种常用的数据连接查询方式 它使用比较运算符进行多个表之间的比较连接操作 内连接会消除与另一个表的任何不匹配的数据行 外连接 外连接会返回FROM子句中提到的至少一个表的所有符合查询条件的数据行 包括连接中不匹配的数据行 外连接分为左外连接 右外连接和完全连接 交叉连接 当对两个表使用交叉连接查询时 将返回来自这两个表的各行的所有可能的数据行组合 自连接 表自身的链接参见教材p119 2020 2 22 MicrosoftSQLServer2005 47 两个样例表 SELECT FROMTb DeptSELECTClassId ClassName DeptIdFROMTb Class 2020 2 22 MicrosoftSQLServer2005 48 内连接 SELECTDeptName系部名称 ClassId班级编码 ClassName班级名称FROMTb DeptINNERJOINTb ClassONTb Class DeptId Tb Dept DeptId 等同于 SELECTDeptName系部名称 ClassId班级编码 ClassName班级名称FROMTb Class Tb DeptWHERETb Dept DeptId Tb Class DeptId 2020 2 22 MicrosoftSQLServer2005 49 外连接 左外连接 SELECTDeptName系部名称 ClassId班级编码 ClassName班级名称FROMTb DeptLEFTOUTERJOINTb ClassONTb Dept DeptId Tb Class DeptId左边表中列全显示 2020 2 22 MicrosoftSQLServer2005 50 外连接 右外连接 SELECTDeptName系部名称 ClassId班级编码 ClassName班级名称FROMTb DeptRIGHTOUTERJOINTb ClassONTb Dept DeptId Tb Class DeptId右边表中列全显示 2020 2 22 MicrosoftSQLServer2005 51 外连接 完全连接 SELECTDeptName系部名称 ClassId班级编码 ClassName班级名称FROMTb DeptFULLOUTERJOINTb ClassONTb Dept DeptId Tb Class DeptId 2020 2 22 MicrosoftSQLServer2005 52 交叉连接 SELECTDeptName系部名称 ClassId班级编码 ClassName班级名称FROMTb DeptCROSSJOINTb Class 2020 2 22 MicrosoftSQLServer2005 53 交叉连接 不使用WHERE子句时 返回的结果是一个基本表中符合条件的行数乘以另一个基本表中符合条件的行数 使用WHERE子句时 返回的结果是被连接的两个基本表所有行的笛卡尔积中满足WHERE条件的所有行的行数 参见教材p118 2020 2 22 MicrosoftSQLServer2005 54 联合查询 UNION 如果有多个不同的查询结果 但又希望将这些查询结果放在一起显示 组成一组数据 在这种情况下 可以使用UNION子句 使用UNION子句的查询又称为联合查询 它可以将两个和多个查询结果集组合成为单个结果集 语法如下 SELECTselect listFROMtable list WHEREsearch conditions UNION ALL SELECTselect listFROMtable list WHEREsearch conditions 其中 ALL关键字为可选的 如果在UNION子句中使用该关键字 则返回全部数据行 如果不使用该关键字 则返回结果中删除重复数据行 2020 2 22 MicrosoftSQLServer2005 55 联合查询 UNION SELECT FROMTb SpecWHERESpecId 08 UNIONSELECTStuId StuName SexFROMTb StudentWHEREClassId S0408011 2020 2 22 MicrosoftSQLServer2005 56 章节综合练习 每个学年结束 班主任都要对班级学生成绩进行分析 然后初定学年奖学金获得者的人选 让我们运用数据库查询的知识帮助04网络1班的班主任刘老师完成以下两个任务 根据学院奖学金评选规定 初定获奖学金学生名单 每位同学的课程平均成绩必须高于75分 对该班级开设的各门课程分别进行成绩分析 显示课程编号 学生学号和成绩的明细并分别汇总每门课程的平均成绩 2020 2 22 MicrosoftSQLServer2005 57 章节综合练习 2020 2 22 MicrosoftSQLServer2005 58 任务一 USETeaching SystemGOSELECTStuId学号 AVG TotalScore AS平均成绩FROMTb GradeWHEREClassId S0408011 GROUPBYStuIdHAVINGAVG TotalScore 75 2020 2 22 MicrosoftSQLServer2005 59 任务二 USETeaching SystemGOSELECTCourseIdAS课程编号 StuIdAS学号 ROUND TotalScore 2 AS成绩FROMTb GradeWHEREClassId S0408011 ORDERBYCourseIdCOMPUTEAVG ROUND TotalScore 2 BYCourseId 2020 2 22 MicrosoftSQLServer2005 60 C