SQL语言及T-SQL的应用.ppt

第三章SQL语言及T SQL的应用 1 本章学习重点之一 1 关系数据库的标准语言SQL基本知识 2 SQL的数据定义语句 数据操作语句 数据控制语句 3 视图 索引的概念与操作 内容概要 之一 3 1关系数据库的标准语言SQL3 2数据定义3 3数据查询语言 3 1关系数据库标准语言SQL3 1 1SQL的发展史 1974年IBM圣约瑟实验室 SanJoseResearchLaboratory 的Boyce和Chamberlin为关系数据库管理系统System R设计的一种查询语言 当时称为SEQUEL语言 StructuredEnglishQueryLanguage 后简称为SQL 1981年IBM推出关系数据库系统SQL DS后 SQL得到了广泛应用 1986年美国国家标准协会 AmericanNationalStandardInstitute简称ANSI 公布了第一个SQL标准 SQL86 1987年 国际标准化组织 InternationalOrganizationforStandardization简称ISO 通过SQL86标准 1989年 ISO制定SQL89标准 SQL89标准在SQL86基础上增补了完整性描述 1990年 我国制定等同SQL89的国家标准 1992年 ISO制定SQL92标准 即SQL2 1999年 ANSI制定SQL3标准 注 Server2000中使用Transact SQL语言 3 1 2SQL的特点 SQL StructuredQueryLanguage 的特点 综合统一a SQL集各种数据语言的功能于一体 语言风格统一 b 实体和实体之间的联系均用关系表示 2 高度非过程化只要提出 做什么 有利于各种数据库之间交换数据 有利于程序的移植 有利于实现程序和数据间的独立性 有利于实施标准化 3 面向集合的操作方式操作数是集合 操作结果也是集合 4 以同一种语法结构提供两种使用方法自含式语言 联机交互的使用方式 嵌入式语言 嵌入到高级语言中 5 语言简洁 易学易用 3 1 3SQL的分类1 数据定义语言 DDL DataDefinitionLanguage 创建 修改或删除数据库中各种对象 包括表 视图 索引等 2 查询语言 QL QueryLanguage 按照指定的组合 条件表达式或排序检索 3 数据操纵语言 DML DataManipulationLanguage 插入 删除 修改等操作 4 数据控制语言 DCL DataControlLanguage 授予或收回访问数据库的某种特权 控制数据操纵事务的发生时间及效果 对数据库进行监视 注意 在书写各种SQL命令时 命令中所涉及的标点符号 如括号 逗号 分号 圆点 英文句号 等都应是英文半角 如果写成中文全角符号 则会在执行命令时出错 3 2数据定义命令数据定义命令用于建立数据库和建立 修改 删除基本表 3 2 1建立数据库ANSI标准SQL建立数据库 SCHEMA 模式 的命令是 CREATESCHEMAAUTHORIZATION 例如 创建者是duck 则上面命令写作 CREATESCHEMAAUTHORIZATIONduck 注 RDBMS更常用下面命令格式 CREATEDATABASE 例如 建立学费管理数据库的命令CREATEDATABASExfgl Server 语言 SQLSERVER中建立数据库补充 SQLServer2000中常用的数据类型 Server中的数据类型 3 2 2建立数据表 标准 建立基本表的命令格式为 CREATETABLE 所要定义的基本表的名字 组成该表的各个属性 列 涉及相应属性列的完整性约束条件 如 NOTNULL 指该列的值不能为空值 UNIQUE 指该列的值是唯一的 涉及一个或多个属性列的完整性约束条件注 完整性约束被存入系统的数据字典中 创建表语句中常用完整性约束 主码约束 PRIMARYKEY 唯一性约束 UNIQUE 非空值约束 NOTNULL 参照完整性约束 FOREIGNKEYREFERENCES引用表名 引用列 检查约束 CHECK 检查表达式 默认值约束 DEFAULT默认值 例1 建立一个 学生 表Student 它由学号Sno 姓名Sname 性别Ssex 年龄Sage 所在系Sdept五个属性组成 其中学号不能为空 值是唯一的 并且姓名取值也唯一 非空约束 唯一性约束实例 CREATETABLEStudent SnoCHAR 8 NOTNULLUNIQUE SnameVARCHAR 10 UNIQUE SsexCHAR 2 Sagetinyint SdeptCHAR 2 Notnull unique组合等价于主键约束 例2 主键约束 默认约束 CREATETABLEstudent SnoCHAR 8 PRIMARYKEY SnameVARCHAR 10 UNIQUE SsexCHAR 2 default 男 SageTINYINT SdeptCHAR 2 列级约束 PRIMARYKEY与UNIQUE的区别 主键具有以下特征 每个表只能定义一个主键 主键值不能为空 主键列可以由一个或多个列组成 主键值不重复 惟一键通常限定的是没有被选为主键的关键字 它 列或多列值的不重复 惟一性与主键的区别如下 每个表可以有多个惟一键 惟一键的列值可以为空 但只能有一个空 例3 建立一个 学生选课 表SC 它由学号Sno 课程号Cno 修课成绩Grade组成 其中 Sno Cno 为主码 CHECK约束实例 CREATETABLESC SnoCHAR 8 CnoCHAR 3 Gradeintcheck grade 0andgrade 100 Primarykey Sno Cno 表级约束 例4 参照完整性约束与check约束 CREATETABLESC SnoCHAR 8 FOREIGNKEYREFERENCESstudent sno CnoCHAR 3 FOREIGNKEYREFERENCEScourse cno Gradeintcheck grade 0andgrade 100 primarykey Sno Cno 3 2 3修改基本表 ALTERTABLE ADD 完整性约束 DROP MODIFY 要修改的基本表 ADD子句 增加新列和新的完整性约束条件 DROP子句 删除指定的完整性约束条件 MODIFY子句 用于修改列名和数据类型注 标准SQL无删除列的语句 1 添加新列命令 ALTERTABLEADD列名数据类型 例 向Student表增加 入学时间 列 其数据类型为日期型 ALTERTABLEStudentADDScomeDATETIME 不论基本表中原来是否已有数据 新增加的列一律为空值 2 删除指定的完整性约束条件的命令 ALTERTABLEDROP完整性约束名 例 删除学生表中姓名列必须取唯一值的约束 ALTERTABLEStudentDROPUNIQUE Sname 注 SQL中删除约束命令 ALTERTABLEDROPCONSTRAINT约束名 例7 将Student表中的删除主键约束删除 ALTERTABLESTUDENTDROPCONSTRAINTPK student 21B6055D注 在创建时若未给所设置的约束取名 系统将自动取名字 查看约束信息 可以用存储过程 sp help数据表名 可以在sysobjects中查到 如 IFEXISTS SELECT FROMsysobjectsWHERENAME PK student 21B6055D 修改列命令 ALTERTABLEMODIFY列名数据类型 例 将年龄的数据类型改为占2字节整数 ALTERTABLEStudentMODIFYSageSMALLINT 注 修改原有的列定义有可能会破坏已有数据 T SQL ALTERTABLEStudentaltercolumnSageSMALLINT SQL中删除列命令 ALTERTABLEDROPCOLUMN列名 例 将Student表中新增加的 入学时间 列删除 ALTERTABLEStudentDROPCOLUMNScome 注 在删除列时 必须先删除该列的索引和约束后 才能删除该列 back 3 2 4数据表的删除 DROPTABLE 基本表删除数据 表上的索引都删除表上的视图往往仍然保留 但无法引用 删除基本表时 系统会从数据字典中删去有关该基本表及其索引的描述 例10 删除Student表DROPTABLEStudent 须使用的基本操纵 查询语句 1 查看表数据 1 使用企业管理器 2 SQL语句格式简单格式 Select 属性列组 from数据表名2 向数据表中插入数据 使用企业管理插入数据 2 SQL语句格式INSERTINTO VALUES 例1 向学生基本情况表 student 中插入一条记录 学号 95020 姓名 陈林 性别 女 所在系 MA 年龄 19岁 INSERTINTOstudentVALUES 95020 陈林 女 19 MA 3 删除数据 1 使用企业管理器 2 SQL删除语句 DELETEFROM WHERE 例1 删除学号为95010的学生记录 DELETEFROMstudentWHEREsno 95010 4 修改数据 1 使用企业管理器 2 修改数据SQL语句UPDATESET WHERE 例1 将课程表 course 中所有课程的学时 credit 减少5学时 UPDATEcourseSETcredit credit 5 SQLSERVER中建立数据表 数据完整性约束补充之一 企业管理器创建数据表 数据完整性约束方法 演示 创建数据表 数据完整性约束 语句 用select into 以复制创建数据表格式 Select 属性列组 into新数据表名from数据表名如复制表结构 Select intostufromstudentwhere2 1 SQLSERVER课后阅读 如复制表的结构和内容 Select intostufromstudent思考 如何实现不同数据库间数据表的复制 如何实现不同数据服务器间数据表的复制 提示 服务器实例名 数据库名 拥有者 数据表名 创建数据表时自定义约束名例 主键约束createtablecourse cnochar 3 constraintpk courseprimarykey cnamevarchar 10 credit hourtinyint 例 check约束如 要求课程表中学分 credit hour 限制在 范围内 createtablecourse cnochar 3 constraintpk courseprimarykey cnamevarchar 10 credit hourtinyintconstraintch coursecheck credit hour 0andcredit hour 5 例 外键约束CREATETABLESC SnoCHAR 5 CONSTRAITFK SNOFOREIGNKEYREFERENCESstudent sno CnoCHAR 3 CONSTRAITFK CNOFOREIGNKEYREFERENCEScourse cno Gradeintcheck grade 0andgrade 100 Primarykey Sno Cno 表是包含数据库中所有数据的数据库对象 用来存储各种各样的信息 在SQLServer2000中 一个数据库中最多可以创建200万个表 用户创建数据库表时 最多可以定义1024列 在同一数据库的不同表中 可以有相同的字段 但在同一个表中不允许有相同的字段 小知识 3 对计算列使用表达式例 下例显示如何使用表达式 low high 2 计算myavg计算列 假设low 最低分 high 最高分 myavg 平均分 CREATETABLEmytable lowint highint myavgAS low high 2 例 对计算列使用系统函数下例在myuser name列中使用系统函数USER NAME函数 CREATETABLEmylogintable date indatetime user idint myuser nameASUSER NAME 思考 可以用自定义函数吗 可以 4 创建临时表 1 局部临时表例 Createtable productTemp productIDintprimarykey productnamechar 10 2 全局临时表例 Createtable tempWork pkint collint 5 在列中使用uniqueidentifier数据类型下例创建含有uniqueidentifier列的表 该表使用PRIMARYKEY约束以确保用户不会在表中插入重复的值 并在DEFAULT约束中使用NEWID 函数为新行提供值 CREATETABLEGlobally Unique Data guiduniqueidentifierCONSTRAINTGuid DefaultDEFAULTNEWID Employee Namevarchar 60 CONSTRAINTGuid PKPRIMARYKEY Guid 6 修改数据库添加一个或多个属性列 ALTERTABLE表名ADD列名数据类型 长度 null notnull 实例 修改数据库abab中表a 添加两个属性列cc dd 使cc为整型 且不为空 取默认值为5 dd类型为字符型 最多占20字节 useababaltertableaaddccintnotnulldefault5 ddchar 20 7 修改字段的属性ALTERTABLE表名ALTERCOLUMN列名数据类型 长度 约束 注 将一个原来允许为空的列改为不允许为空时 必须满足列中没有存放空值的记录 以及在该列上没有创建索引 8 删除字段 列 T SQL ALTERTABLE表名DROPCOLUMN列名实例 例1 例2 删除表a中的cc属性列 altertableadropcolumncc注 须先删除属性列上的约束 再删除该列 查询数据表信息 sp helpa 9 修改表名 调用存储过程 Sp rename原表名 新表名实例 将学生信息库student中 studentx 表更名为 student Usestudentsp rename studentx student 注 修改会影响调用该表的存储过程 触发器 视图等 使它们无效 待续 3 2 索引的建立与删除 索引是对数据库表中一个或多个列的值进行排序的结构 1 建立索引语句CREATE UNIQUE CLUSTERED INDEX索引名ON基本表名 列名 次序 列名 次序 说明 索引可以建立在一列和多列之上 索引顺序可以是ASC 升序 或DESC 降序 缺省值是升序 UNIQUE表示每一个索引值对应唯一的数据记录 CLUSTERED表示要建立的索引是聚簇索引 a 唯一值索引 对于已含重复值的属性列不能建UNIQUE索引 对某个列建立UNIQUE索引后 插入新记录时DBMS会自动检查新记录在该列上是否取了重复值 这相当于增加了一个UNIQUE约束 例1 为学生 课程数据库中的Student Course SC三个表建立索引 其中Student表按学号升序建索引 Course表按课程号升序建唯一索引 SC表按学号升序和课程号降序建唯一索引 CREATEINDEXStusnoONStudent Sno CREATEUNIQUEINDEXCoucnoONCourse CnoASC CREATEUNIQUEINDEXSCnoONSC Sno CnoDESC b 聚簇索引 聚簇索引是指索引项顺序与表中记录的物理顺序一致 在一个基本表上最多只能建立一个聚簇索引 聚簇索引用途 对于某些类型的查询 可以提高查询效率 聚簇索引的适用范围 很少对基表进行增删操作 很少对其中的变长列进行修改操作 该列为最常查询列 例3 在Student表的Sname 姓名 列上建立一个聚簇索引 而且Student表中的记录将按照Sname值的升序存放 CREATECLUSTEREDINDEXStusnameONStudent Sname 例4 在学生基本情况表jbqk之上建立一个关于学生表的索引文件 索引文件名为 学生索引 索引建立在学号之上 按学号降序排序 Createindex学生索引onjbqk snodesc 2 删除索引语句DROPINDEX 注意 该命令不能删除由CREATETABLE或者ALTERTABLE命令创建的主键和唯一性约束索引 也不能删除系统表中的索引 删除索引时 系统会从数据字典中删去有关该索引的描述 例12 删除例5创建的索引 学生索引 程序如下 DROPINDEXJBQK 学生索引 例13 删除Student表的Stusname索引 DROPINDEXStudent Stusname 返回 3 3数据查询语言 3 3 1查询语句格式 SQL语言的查询语句一般格式是 SELECT ALL DISTINCT FROM WHERE GROUPBY HAVING ORDERBY ASC DESC 下面对该命令进行一些说明 SELECT子句 指定显示的属性列 选出元组中的属性值形成结果表 FROM子句 指定查询对象 基本表或视图 WHERE子句 指定查询条件 筛选出满足条件的元组 GROUPBY子句 对查询结果按指定列 列1 的按值分组 该属性列值相等的元组为一个组 通常会在每组中作用集函数 HAVING短语 筛选出分组中满足指定条件的元组 和GROUPBY子句一起用 ORDERBY子句 对查询结果表按指定列 列2 值的升序或降序排序 注 目标列表达式几种表示 列表达式可以是 列名1 列名2 的形式 如果FROM子句指定了多个表 则列名应是 表名 列名 的形式 也可以是全部列 指定为 列表达式可以使用函数形成表达式 DISTINCT参数 表示在结果集中 查询出的内容相同的记录只留下一条 示例数据库 学生 课程数据库学生表 Student Sno Sname Ssex birthday Sdept 课程表 Course Cno Cname Cpno Ccredit 学生选课表 SC Sno Cno Grade back 3 3 2单表查询单表查询是指仅涉及一个表的查询 一 选择表中的若干列 1 查询指定列 例1 查询全体学生的学号与姓名 性别 SELECTSno Sname SsexFROMStudent 例2 查询全体学生的学号 姓名 所在系 SELECTSno Sname SdeptFROMStudent 2 查询全部列 例3 查询全体学生的详细记录 SELECTSno Sname Ssex birthday SdeptFROMStudent 或SELECT FROMStudent 3 查询经过计算的值SELECT子句的不仅可以是表中的属性列 也可以是表达式 如算术表达式 字符串常量 函数 列别名等 例4 查全体学生的姓名及其出生年份 SELECTSname year birthday FROMStudent 输出结果 Sname2004 Sage 李勇1976刘晨1977王名1978张立1978 思考 如何通过出生年月信息求出年龄 例5 查询全体学生的姓名 出生年份和所有系 要求用小写字母表示所有系名 SELECTSname YearofBirth year birthday ISLOWER Sdept FROMStudent 输出结果 Sname YearofBirth year birthday ISLOWER Sdept 李勇YearofBirth 1980cs刘晨YearofBirth 1981is王名YearofBirth 1982ma张立YearofBirth 1981is 例 使用列别名改变查询结果的列标题 SELECTSnameNAME YearofBirth BIRTH BirthYear year birthday ISLOWER Sdept asDEPARTMENTFROMStudent 输出结果 NAMEBIRTHBirthYearDEPARTMENT 李勇YearofBirth 1980cs刘晨YearofBirth 1981is王名YearofBirth 1982ma张立YearofBirth 1981is 用 空格或AS 二 选择表中的若干元组 消除取值重复的行在SELECT子句中使用DISTINCT短语 假设SC表中有下列数据SnoCnoGrade 9500119295001285950013889500229095002380 例7 查询选修了课程的学生学号 1 SELECTSnoFROMSC 或 默认ALL SELECTALLSnoFROMSC 结果 Sno 9500195001950019500295002 2 SELECTDISTINCTSnoFROMSC 结果 Sno 9500195002 注意 DISTINCT短语的作用范围是所有目标列 查询满足条件的元组WHERE子句常用的查询条件 1 比较大小 在WHERE子句的中使用比较运算符 30 或SelectSname SageFromStudentWhereSage 30 2 确定范围 使用谓词 BETWEEN AND NOTBETWEEN AND 例1 查询年龄在20 29岁 包括20岁和29岁 之间的学生的姓名 年龄和所在系SELECTSname year getdate year birthday SdeptFROMStudentWHEREyear getdate year birthday BETWEEN20AND29 例1 查询年龄不在20 29岁之间的学生姓名 年龄和系别 SELECTSname year getdate year birthday SdeptFROMStudentWHEREyear getdate year birthday NOTBETWEEN20AND29 3 确定集合 使用谓词IN NOTIN 用逗号分隔的一组取值 例1 查询信息系 IS 数学系 MA 和计算机科学系 CS 学生的学号 姓名和性别 SELECTSno Sname SsexFROMStudentWHERESdeptIN IS MA CS 例1 查询既不是信息系 数学系 也不是计算机科学系的学生的学号 姓名和性别 SELECTSno Sname SsexFROMStudentWHERESdeptNOTIN IS MA CS 其它实例 查询考试成绩不及格学生的学号 查询学分在2 4之间的课程名称和学分 case 4 字符串匹配 NOT LIKE ESCAPE 指定匹配模板匹配模板 固定字符串或含通配符的字符串当匹配模板为固定字符串时 可以用 运算符取代LIKE谓词用 或运算符取代NOTLIKE谓词 通配符 百分号 代表任意长度 长度可以为0 的字符串 例 a b表示以a开头 以b结尾的任意长度的字符串 如acb addgb ab等都满足该匹配串 下横线 代表任意单个字符 例 a b表示以a开头 以b结尾的长度为3的任意字符串 如acb afb等都满足该匹配串 case ESCAPE短语 当查询的字符串本身就含有 或 时 需用ESCAPE 短语对通配符进行转义 LIKE DB Design ESCAPE ESCAPE 短语表示 为换码字符 这样匹配串中紧跟在 后面的字符 不再具有通配符的含义 转义为普通的 字符 例题 1 匹配模板为固定字符串 例1 查询学号为95001的学生的详细情况 SELECT FROMStudentWHERESnoLIKE 95001 等价于 SELECT FROMStudentWHERESno 95001 case 2 匹配模板为含通配符的字符串 例1 查询所有姓刘学生的姓名 学号和性别 SELECTSname Sno SsexFROMStudentWHERESnameLIKE 刘 例1 查询姓 欧阳 且全名为三个汉字的学生的姓名和性别 SELECTSname SsexFROMStudentWHERESnameLIKE 欧阳 case 例18 查询名字中第2个字为 阳 字的学生的学号 姓名 SELECTSno SnameFROMStudentWHERESnameLIKE 阳 例19 查询所有不姓刘的学生学号 姓名 性别 SELECTSno Sname SsexFROMStudentWHERESnameNOTLIKE 刘 case 3 使用换码字符将通配符转义为普通字符 例20 查询DB sql课程的课程号和学分 SELECTCno CcreditFROMCourseWHERECnameLIKE DB sql ESCAPE 例21 查询以 DB 开头 且倒数第3个字符为i的课程的详细情况 SELECT FROMCourseWHERECnameLIKE DB i ESCAPE case 5 涉及空值的查询 使用谓词ISNULL或ISNOTNULL ISNULL 不能用 NULL 代替 例22 某些学生选修课程后没有参加考试 所以有选课记录 但没有考试成绩 查询缺少成绩的学生的学号和相应的课程号 该列允许为空 不是零值 即无成绩 SELECTSno CnoFROMSCWHEREGradeISNULL case case 例22 查所有有成绩的学生学号和课程号 SELECTSno CnoFROMSCWHEREGradeISNOTNULL 6 多重条件查询 用逻辑运算符AND和OR来联结多个查询条件AND的优先级高于OR可以用括号改变优先级可用来实现多种其他谓词 NOT IN NOT BETWEEN AND case 例24 查询计算机系年龄在20岁以下的学生姓名 SELECTSnameFROMStudentWHERESdept CS ANDyear getdate year birthday 20 原例12 查询信息系 IS 数学系 MA 和计算机科学系 CS 学生的姓名和性别 SELECTSname SsexFROMStudentWHERESdeptIN IS MA CS 可改写为 SELECTSname SsexFROMStudentWHERESdept IS ORSdept MA ORSdept CS case 思考 原例10 查询年龄在20 29岁 包括20岁和29岁 之间的学生的姓名 系别和年龄 case 7 T SQL Top子句 选择 Topn子句 在查询结果中输出前面的n条记录 Topnpercent子句 则在查询结果中输出前面占结果记录总数的n 条记录 注 top子句不能和Distinct关键字同时使用 1 显示student表中 最前面的两条记录 selecttop2 fromstudent2 显示student表中 占总数10 条记录 selecttop10percent fromstudent 三 对查询结果排序 使用ORDERBY子句可以按一个或多个属性列排序升序 ASC 降序 DESC 缺省值为升序当排序列含空值时ASC 排序列为空值的元组最后显示DESC 排序列为空值的元组最先显示 例2 查询选修了3号课程的学生的学号及其成绩 查询结果按分数降序排列 SELECTSno GradeFROMSCWHERECno 3 ORDERBYGradeDESC case SQLserver中执行后 SnoGrade 9500792950038295010829500975950146195002559501095024 例26 查询全体学生情况 查询结果按所在系的系号升序排列 同一系中的学生按姓名降序排列 SELECT FROMStudentORDERBYSdept SnameDESC case 查询中集函数 又称为聚合函数 的使用说明 DISTINCT 取消列中的重复值ALL 为默认状态 表示不取消重复值 例27 查询学生总人数 SELECTCOUNT FROMStudent 例28 查询选修了课程的学生人数 SELECTCOUNT DISTINCTSno FROMSC 注 用DISTINCT以避免重复计算学生人数 例29 计算1号课程的学生平均成绩 SELECTAVG Grade FROMSCWHERECno 1 例30 查询选修1号课程的学生最高分数 SELECTMAX Grade FROMSCWHERCno 1 四 对查询结果分组 使用GROUPBY子句分组细化集函数的作用对象