使用Transact-SQL语言.ppt

Select语句查询 SELECT字段列表FROM数据源 WHERE条件表达式 GROUPBY分组表达式 HAVING搜索表达式 ORDERBY排序表达式 ASC DESC SELECT语句的语法结构为 SELECT字段列表FROM数据源 WHERE条件表达式 ORDERBY排序表达式 ASC DESC Compute聚合函数by要分组显示的列名 例如 按类别显示课程信息 并计算每类课程的平均报名人数 GROUPBY子句在被定义的数据的基础上建立比较小的组 即按照分组表达式对某一列或多列的数据进行分组 将数据值相同的分为一组 并且对每一个组进行聚合函数计算 换句话说 它产生每一组的总体信息 GROUPBY子句 使用GROUPBY子句时 应该注意以下问题 1 GROUPBY子句子句不能使用聚合函数 2 当select语句中包含有where子句和orderby子句时 groupby子句只能放在这两个子句的后面 3 必须在groupby子句中列出select选择列表中的数据项 当使用groupby子句进行分组时 select语句的列表中所选择的列一定是groupby子句后面的分组依据列或聚合函数 2 1Transact SQL概论2 2查询与统计数据2 3编辑用户表数据 第二章使用Transact SQL语言 Transact SQL语言主要组成部分 数据定义语言 DDL DataDefinitionLanguage 数据操纵语言 DML DataManipularionLanguage 数据控制语言 DCL DataControlLanguage 2 1Transact SQL概论 数据定义语言 DDL 数据定义语言是指用来定义和管理数据库以及数据库中的各种对象的语句 这些语句包括CREATE ALTER和DROP等语句 在SQLServer2000中 数据库对象包括表 视图 触发器 存储过程 规则 缺省 用户自定义的数据类型等 这些对象的创建 修改和删除等都可以通过使用CREATE ALTER DROP等语句来完成 数据操纵语言 DML 数据操纵语言是指用来查询 添加 修改和删除数据库中数据的语句 这些语句包括SELECT INSERT UPDATE DELETE等 数据控制语言 DCL 数据控制语言 DCL 是用来设置或者更改数据库用户或角色权限的语句 这些语句包括GRANT DENY REVOKE等语句 2 2查询与统计数据 SELECT语句用来检索和显示满足特定条件的数据select在查询结果中要显示的列的名字from要显示数据的表的名字where要检索数据的条件选择所有列select 案例2 1在student表中查询学号为 000000001 同学的学号和姓名 使用星号或列名 分析 1 识别要查询的数据库 XL2 识别数据库中所需要的表 student3 识别所需的表中的字段 stuno stuname USEXkGOSELECTStuNo StuNameFROMStudentWHEREStuNo 00000001 GO 案例2 2从课程表 course 中查询课程类别 要求清除值相同的那些行 使用distinct消除重复值 分析 1 识别要查询的数据库 XL2 识别数据库中所需要的表 Course3 识别所需的表中的字段 Kind USEXkGO 消除Kind列值都相同的那些重复行SELECTDISTINCTKindFROMCourseGO 消除Kind列和Credit列值都相同的那些重复行SELECTDISTINCTKind CreditFROMCourseGO 使用topn percent 仅返回前n行 TOP关键字与select语句一起使用的top关键字限制了结果集合中返回的行数 Select topn percent 字段From表名Where条件 orderby字段名 Top12返回结果集的前12行Top15percent返回结果集的前15 案例2 3从学生表sudent中查询所有的信息 要求只显示查询结果的前6行数据 分析 1 识别要查询的数据库 XL2 识别数据库中所需要的表 student3 识别所需的表中的字段 所有列 USEXkGOSELECTTOP6 FROMStudentGO 改变查询结果列的标题 用户定义的列标题可以代替默认的列标题 有三种方法 1 使用等号 将用户定义列标题放在默认列名字之前 2 用户定义列标题放在默认列标题名字之后 3 使用AS关键字 案例2 4查询课程表course中课程编号 课程名称 教师 上课时间 限制选课人数和报名人数 要求结果显示如下 1 识别要查询的数据库 XK2 识别数据库中所需要的表 Course3 识别所需的表中的字段 方法1 在查询窗口中执行如下SQL语句 USEXkGOSELECT 课程编码 CouNo 课程名称 CouName 教师 Teacher 上课时间 SchoolTime 限制选课人数 LimitNum 报名人数 WillNumFROMCourseGO 方法2 在查询窗口中执行如下SQL语句 USEXkGOSELECTCouNo 课程编号 CouName 课程名称 Teacher 教师 SchoolTime 上课时间 LimitNum 限制选课人数 WillNum 报名人数 FROMCourseGO 方法3 在查询窗口中执行如下SQL语句 USEXkGOSELECTCouNoAS 课程编号 CouNameAS 课程名称 TeacherAS 教师 SchoolTimeAS 上课时间 LimitNumAS 限制选课人数 WillNumAS 报名人数 FROMCourseGO 使用where限制查询条件 在where子句中 可以包含比较运算符 逻辑运算符 范围运算符 逻辑运算符OR当任何一个指定查找条件是真时返回结果AND当所有指定的查找条件是真时返回结果NOT否定其后的表达式范围运算符 案例2 5在课程表中查询课程类别为 信息技术 而且学分为2的课程信息 USEXkGOSELECT FROMCourseWHEREKind 信息技术 ANDCredit 2GO 表达式作为查询列 在select子句中的选项列表可以为表达式或指定的列的列表 表达式可以是列名 函数或常数的列表 案例2 6查询课程表中最小的报名人数 最大的报名人数和平均报名人数 分析 可以使用max min avg函数1 识别要查询的数据库 XK2 识别数据库中所需要的表 Course3 识别所需的表中的字段 willnum USEXkGOSELECT 最小的报名人数 MIN WillNum 最大的报名人数 MAX WillNum 平均报名人数 AVG WillNum FROMCourseGO 使用orderby子句重新排序查询结果 ORDERBY子句在select语句中使用orderby子句 以指定顺序检索和显示数据 select字段from指定所在表名orderby指定要排序的列 相关列号或表达式asc desc指定按递增 递减顺序对指定列的值进行排序 案例2 7查询课程信息 报名人数与限选人数之比 要求查询结果按照报名人数升序排列 分析 使用表达式作为查询列1 识别要查询的数据库 XK2 识别数据库中所需要的表 Course3 识别所需的表中的字段 willnum limitnum 1 ORDERBY后面为列名USEXkGOSELECT WillNum LimitNum 报名人数与限选人数之比 FROMCourseORDERBYWillNumGO 2 ORDERBY后面为中文标题USEXkGOSELECT WillNum LimitNum 报名人数与限选人数之比 FROMCourseORDERBY报名人数与限选人数之比GO 使用in子句给出列值范围 案例2 7查询课程编号为 004 007 013 的课程信息 USEXkGOSELECT FROMCourseWHERECouNo 004 ORCouNo 007 ORCouNo 013 GO 使用IN关键字的SQL语句如下 USEXkGOSELECT FROMCourseWHERECouNoIN 004 007 013 GO 1 ORDERBY后面为列名USEXkGOSELECT WillNum LimitNum 报名人数与限选人数之比 FROMCourseORDERBYWillNumGO 2 ORDERBY后面为中文标题USEXkGOSELECT WillNum LimitNum 报名人数与限选人数之比 FROMCourseORDERBY报名人数与限选人数之比GO 使用like实现模糊查询 串运算符like与通配符 案例2 8查询课程名以字母D开始的课程信息 USEXkGOSELECT FROMCourseWHERECouNameLIKE D GO USEXkGOSELECT FROMStudentWHEREStuNameLIKE 宝 GO 案例2 9查询第二个字为 宝 的学生信息 案例2 10查询不姓 刘 的学生信息 使用 USEXkGOSELECT FROMStudentWHEREStuNameLIKE 刘 GO 使用NOTLIKEUSEXkGOSELECT FROMStudentWHEREStuNameNOTLIKE 刘 GO 使用isnull查询指定列表未输入值的数据行 ISNULL和ISNOTNULL关键字null是一个未知值 或者是以给还没有提供数据的值 每当null值与任何其他值进行比较时 使用比较运算符 或对null值进行计算时 其结果总是null 案例2 10查询课程表中教师姓名未定的课程信息 USEXkGOSELECT FROMCourseWHEREteacherisnullGO 查询某一范围内的信息 查询在某一范围内的信息 要使用where子句限制查询条件 该条件通常是一个逻辑表达式 案例2 11查询报名人数少于25人并且多于15人的课程信息 USEXkGOSELECT FROMCourseWHEREwillnum 15andwillnum 25GO 使用compute进行计算 使用带select语句的Compute子句 使用聚合函数生成查询结果中的汇总行 Computeby子句进一步按列汇总结果集 Select字段From表名Where条件Compute聚合函数 聚合函数 案例2 12查询课程表中 信息技术 类课程信息 并计算平均报名人数 USEXkGOSELECT FROMCourseWHEREkind 信息技术 Computeavg willnum GO computeby子句computeby子句对by后面给出的列进行分组显示 并计算该列的分组小计 在使用computeby之前必须先使用orderby对computeby中指定的列进行排序 Select字段From表名Where条件Orderby要分组显示的列名Compute聚合函数by要分组显示的列名 案例2 13按类别显示课程信息 并计算给类课程的平均报名人数 USEXkGOSELECT FROMCourseOrderbykindComputeavg willnum bykindGO 使用groupby分组汇总查询结果 GROUPBY子句 Groupby子句用聚合函数 把结果集汇总成查询中定义的组 HAVING子句进一步对结果集进行限制 以根据条件产生数据 Select字段名From表名Where条件Groupby all 指出要分组的select语句的结果集的列名或表达式 having是产生结果集所依据的条件表达式 案例2 14按课程类别分组统计各类课程的平均报名人数 USEXkGOSELECTkindas 课程类别 avg willnum as 每类平均报名人数 FROMCourseGroupbykindGO 注意 使用groupby子句时 在select子句中出现的列名或者出现在聚合函数中 或者出现在groupby子句的后面 否则 在执行语句时就会出错 Having子句 Having子句用于限定对组或者聚合函数的查询条件 该子句常用在groupby子句之后 在查询结果分组之后对组判断是否满足条件 如果在分组之前判断是否满足查询条件则使用where子句 案例2 15查询平均报名人数大于25人的课程类别和每类的平均报名人数 course表 USEXkGOSELECTkindas 课程类别 avg willnum as 每类平均报名人数 FROMCourseWhereavg willnum 25GroupbykindGO 使用子查询 子查询可被定义作返回单值的select查询 它是查询中包含的另一个查询 可以使用子查询代替表达式 子查询只能返回一列数据 有时只能返回单个值 子查询可被定义作返回单值的select查询 子查询是嵌套在select insert update或delete 子查询必须包括在圆括号内 并且不能用orderby和computeby的子句 SELECT字段名FROM表1名WHERE条件名 SELECT字段名FROM表2名WHERE条件名 案例2 16查询报名人数大于平均报名人数的课程信息 course表 USEXkGOSELECT FROMCourseWHEREWillNum SELECTAVG WillNum FROMCourse GO 带有EXIST子句的子查询在where子句中可以使用exist子句 它用于测试跟随的子查询中行是否存在 当用EXIST子句时 总是返回用TRUE或者FALSE值表示的数据 案例2 17查询已报名选修课程的学生信息 要求显示学号和姓名 student表 stucou表 USEXkGOSELECTStuNo StuNameFROMStudentWHEREEXISTS SELECTStuNoFROMStuCouWHEREStuNo Student StuNO GO 连接Innerjoin内连接Outerjoin外连接Crossjoin交叉连接Selfjoin自连接Fulljoin全连接当两个表已被连接时 他们必须共享一个公共键 这个键定义了表中的行如何彼此对应的 内连接在内连接里 只有那些值满足公共列中连接条件的行被显示 两个表中那些不满足条件的行将不被显示 Select字段From表1名 inner Join表2名On是两个表中公共列中的某行相等连接两个表的行 案例2 20查询学生的信息 显示信息包括学生基本信息和班级名称 Class表 student表 USEXkGOSELECTStudent ClassNameFROMClassJOINStudentONClass ClassNo Student ClassNoGO stucou表 案例2 21查询学生选课信息 要求显示姓名 课程名称 志愿号 按姓名和志愿号排序 student表 course表 USEXkGOSELECTStuName CouName WillOrderFROMStuCouJOINStudentONStuCou StuNo Student StuNoJOINCourseONStuCou CouNo Course CouNoORDERBYStuName WillOrderGO stucou表 案例2 22查询学生报名 计算机应用工程系 开设的选修课程情况 显示信息包括学生姓名 课程名称和授课教师 course表 student表 department表 USEXkGOSELECTStuName CouName TeacherFROMStuCou Student Course DepartmentWHEREStuCou StuNo Student StuNoANDStuCou CouNo Course CouNoANDCourse DepartNo Department DepartNoANDDepartNameLIKE 计算机应用工程系 GO 外连接 如果结果集包含来自一个表的所有行和另一个表中的匹配行 那么这种连接称为外连接 Select字段From表1名 left right outerjoin表2名On表1 ref 表2 ref其中显示第一个表中的所有行使用 left 显示第二个表中的所有行使用 right stucou表 案例2 23使用右外连接查询学生的已报名和未报名的课程信息 课程编号 课程名称 学号 course表 USEXkGOSELECTCourse CouNo CouName StuNoFROMCourseRIGHTJOINStuCouONCourse CouNo StuCou CouNoGO 自连接如果一个表中的一行与同一个表中的其他行进行相关联 那么这种连接被称为自连接 由于比较两次使用了同一个表 要先将一个表定义为两个不同的名字 然后在from子句中使用这两个名字 在where子句中需要写出一个连接条件 注意 在select子句中对列的引用形式为 所定义的表名 列名 案例2 24查询课程类别相同但开课系部不同的课程信息 要求显示课程编号 课程名称 课程类别和系部编号 并按照课程编号升序排序查询结果 course表 USEXkGOSELECTDISTINCTC1 CouNoAS课程编号 C1 CouNameAS课程名称 C1 KindAS 课程类别 C1 DepartNoAS 系部编号 FROMCourseC1 CourseC2WHEREC1 Kind C2 KindANDC1 DepartNoC2 DepartNoORDERBY课程编号GO 交叉连接使用关键字CROSS包含一个以上的表的连接称为交叉连接 这种连接输出叫迪卡尔积 两个表的交叉连接中 第一表中的每一行与第二个表中的每一行进行连接 结果集当中的行数是第一个表中的行数乘以第二个表中的行数 全连接为了包含两个表中都不匹配的那些数据行 可以使用全外连接 完成左外连接和右外连接的操作 包括了左表和右表中所有不满足条件的数据行 2 3编辑用户表数据 Insert语句 必须向数据库添加数据 以维护与机构有关的最新信息和机构处理的事务 Insert into 要插入行的表名 任选参数 Values values list Values 指出在表的列中要插入的数据值values list 是要作为标的行出入的列的值列表 使用INSERT语句 2 3 1向用户表数据数据 案例2 25向department表中输入3行数据 USEXkGOINSERTDepartmentVALUES 11 数学系 INSERTDepartment DepartName DepartNo VALUES 物理系 12 INSERTDepartment DepartNo DepartName VALUES 13 化学系 GOSELECT FROMDepartmentGO 使用insert与Select子句 使用insert语句每一次只能输入一行数据 Insert与select语句配合使用可以将表中的数据行输入到表中 并且可以一次输入多行数据 案例2 25向department表中输入3行数据 USEXkGOCreateTableDepartment1 DepartNonvarchar 2 notnull DepartNamenvarchar 30 notnull GOUSEXkGOINSERTDepartment1SELECT FROMDepartmentGO 案例2 26将department表中的数据行输入到department1中 SELECTINTO语句 一个带有INTO子句的SELECT语句用来把结果集存储到不带有数据定义过程的一个新表中 Select旧表的字段into新表名From旧表名Where是在新表中包含行的条件 案例2 25向department表中输入3行数据 USEXkGOSELECT INTODepartment2FROMDepartmentGO 案例2 27将department表中的数据行输入到department2中 UPDATE语句更新行update语句 如果用户 客户 事务或机构要维护的其他数据的规格说明发生变化 数据库中的数据就需要修改 UPDATEtable nameSETcolumn name value column name value FROMtable name WHEREcondition table name 指定要修改的表名column name 指出所指表中要修改的列Value 指出要更新的表的列应取的值table name 指出update使用的表Condition 指出需要更新的行 2 3 2修改用户表数据 案例2 28需要将 电子出版概论 课程的上课时间修改为 周二晚 course表 USEXkGOUPDATECourseSETSchoolTime 周二晚 WHERECouName 电子出版概论 GO 案例2 29现需要 01数据库 班所有学生的选课密码初始化为 123456 student表 Class表 USEXkGOUPDATEStudentSETPwd 123456 FROMStudent ClassWHEREClassName 01数据库 ANDClass ClassNo Student ClassNoGO Delete语句删除行Delete from table name表名 fromtable s 是用于设定删除条件所需的表名 wherecondition 要删除行的条件 Truncatetable语句删除所有行 Truncatetabletable name是剪切或删除这个表中的所有行的表名 2 3 3删除用户表数据 案例2 30学号为 00000005 的同学因故取消课程编号为 017 的选修课 stucou表 course表 USEXkGODELETEStuCouWHEREStuNo 00000005 ANDCouNo 017 GOUPDATECourseSETWillNum WillNum 1WHERECouNo 017 GO 案例2 31学号为 00000005 的同学因故取消 中餐菜肴制作 stucou表 course表 USEXkGODELETEStuCouFROMStuCou CourseWHEREStuCou StuNo 00000005 ANDCouName 中餐菜肴制作 ANDStuCou CouNo Course CouNoGOUPDATECourseSETWillNum WillNum 1WHERECouName 中餐菜肴制作