第2章_SQL语言基础(理论).ppt

第二章SQL语言基础 内容回顾 Oracle进程分为用户进程 服务器进程和后台进程监听程序是客户端与Oracle服务器之间通信的中介Oracle内存可分为系统全局区 SGA 和程序全局区 PGA 系统全局区中包括数据缓冲区 字典缓冲区 重做日志缓冲区 SQL共享池Oracle数据库的物理文件包括数据文件 重做日志文件 控制文件Oracle实例是后台进程和内存结构的集合 为应用程序提供对数据库中数据的管理和维护功能Oracle服务器一般指数据库各软件部件 如SQL Plus Oracle实例及Oracle数据库3个主要部分SYS用户是Oracle中的超级用户 SYSTEM用户是Oracle中默认的管理员 它拥有DBA权限 SCOTT是Oracle数据库的一个示范账号Oracle包含3个用于网络连接配置的文件 分别是监听程序的配置文件 listener ora 本地网格服务名的配置文件 tnsnames ora 命名方法配置文件 sqlnet ora Oracle管理工具主要包括SQL Plus SQLDeveloper和OracleEnterpriseManager 本章内容 SQL概述使用DDL语句使用DML语句使用SELECT语句使用DCL数据控制语言使用TC事务控制语言使用基本函数 本章目标 掌握Oracle数据类型掌握Oracle子查询的使用掌握OracleDCL数据控制语言的使用熟练掌握Oracle常用函数及其用法 1 SQL语言概述 1 1SQL语言分类数据定义 DataDefinitionLanguage DDL 用于创建 修改和删除数据库对象 如CREATETABLE ALTERTABLE DROPTABLE等 DDL语句会自动提交事务数据操纵语言 DataManipulationLanguage DML 用于操纵数据库 包括INSERT UPDATE DELETE SELECT等数据控制语言 DataControlLanguage DCL 用于执行授予权限和撤销权限的操作 包括GRANT 授予权限 REVOKE 撤销权限 两条命令 DCL语句会自动提交事务事务控制语言 TransactionalControlLanguage TCL 用于维护数据的一致性 包括COMMIT 提交事务 ROLLBACK 回滚事务 和SAVEPOINT 设置保存点 3条语句 SQL关键字不区分大小写 既可以使用大写格式 又可使用小写格式 或者大小写混用对象名与列名不区分大小写字符值与日期值区分大小写在SQL Plus中 每条SQL语句要以分号结束 如果语句比较长 可以将语句文本分布到多行上 最后以分号结束 1 2SQL语句编写规则 2 使用DDL语句 2 1Oracle11g常用的数据类型字符类型CHAR类型CHAR表示固定长度字符串 长度不够的用空格补充 最多可以存储2000字节CHAR类型区分中英文 中文在CHAR中占两个字节 而英文只占一个字节VARCHAR2类型VARCHAR2表示可变长度字符串 最多可以存储4000字节在定义该数据类型时 应该指定其大小 与CHAR类型相比 使用VARCHAR2可以节省磁盘空间数值类型NUMBER类型可以存储正数 负数 零 定点数和精度为38位的浮点数NUMBER M N 其中 M表示精度 代表数字的总位数 N表示小数点右边数字的位数日期类型DATE类型DATE数据类型用于存储表中的日期和时间数据 取值范围是公元前4712年1月1日至公元9999年12月31日DATE类型的长度是7 7个字节分别表示世纪 年 月 日 时 分和秒TIMESTAMP类型TIMESTAMP数据类型用于存储日期的年 月 日以及时间的小时 分和秒值其中 秒值精确到小数点后6位 该数据类型同时包含时区信息 大对象类型CLOB大字符串对象类型CLOB CharacterLargeObject 数据类型用于存储可变长度的字符数据 最多可存储4GB数据该数据类型用于存储VARCHAR2类型不能存储的长文本信息BLOB大二进制类型BLOB BinaryLargeObject 数据类型用于存储较大的二进制对象 如图形 视频剪辑和声音剪辑等 最多可以存储4GB数据 2 2CREATETABLE命令 CREATETABLE用于创建表 在创建表时 经常会创建该表的主键 外键 唯一约束 Check约束等 创建约束 外键信息 2 3ALTERTABLE命令 向已经创建的表中增加一个新列altertabletableNameaddcolumnNamedataType修改表中指定列的数据类型和类型长度altertabletableNamemodifycolumncolumnNamedataType删除表中指定的列altertabletableNamedropcolumnName 2 4TRUNCATETABLE命令 RUNCATETABLE命令用于删除表的所有内容 并释放表使用的存储空间 但不删除表结构语法truncatetabletableNameTRUNCATETABLE命令可以快速删除表的记录并释放空间 不使用事务处理 速度快且效率高 但无法回滚事务DELETE命令可以在执行删除之后通过ROLLBACK撤销删除 2 5DROPTABLE命令 DROPTABLE命令用于从数据库中删除表及全部数据 3 使用DML语句 3 1INSERT命令 插入普通数据 插入日期值 3 2UPDATE命令 当日期列的数据为条件时 日期数据类型必须遵守默认日期格式 如果希望使用习惯的日期格式 则可以使用TO DATE函数进行格式化 3 3DELETE命令 DELETE命令用于删除表的内容 使用默认日期格式 使用TO DATE函数 4 使用SELECT语句 4 1示例数据库概述安装Oracle11g创建数据库的环节中 如果在 指定数据库配置详细资料 窗口的 示例方案 选项卡中选择了 创建带样本方案的数据库 复选框 就会安装新的用户示例数据库Oracle的示例方案都基于一个虚拟的通过各种渠道销售物质的公司其中 HR方案是基本的关系数据库方案 用于介绍最简单和最基本的话题该方案中包含7张表 雇员 employees 部门 departments 地点 locations 国家 countries 地区 regions 岗位 jobs 和工作履历 job history 4 2HR示例方案简介 默认情况下 所有示例方案的账户都已被锁定 它们的密码是过期的 不能连接到数据库解除HR用户的锁定状态 解锁 设置新密码 4 3HR示例方案中的表结构 4 4子查询 单行子查询 查询公司中工资最高的雇员信息 多行子查询 查询工资高于部门20中所有员工的雇员信息 多列子查询 查询各部门中工资最低的雇员信息 相关子查询 查询负责管理其他雇员的管理员信息 4 5在DDL DML语句中使用子查询 在CREATETABLE语句中使用子查询 在INSERT语句中使用子查询 在DELETE语句中使用子查询 在UPDATE语句中使用子查询 5 使用DCL数据控制语言 5 1GRANT如果用户在自己的模式中创建了一张表 则该用户即为此表的所有者 拥有对这张表的所有操作权限除非该用户希望与其他用户共享这张表 否则无须为其他用户赋予该表的任何操作权限可以通过GRANT命令为其他用户授予该表的某些操作权限 这样的权限被称为对象权限语法GRANTprivilegesONobject nameTOuserName 1 以SCOTT用户登录 2 访问HR用户的emp对象 出错 3 以HR用户登录 授予SCOTT用户关于表emp的SELECT权限 4 再次以SCOTT用户登录 并查询HR用户的emp表 1 授予SCOTT用户关于表emp的更新权限 2 以SCOTT用户更新HR方案的emp对象 3 更新其他列 提示权限不足 如何为某个用户授予某张表中部分列的SELECT对象权限 为了授予列级的SELECT权限 应该创建一个包含所需列的视图 然后授予视图的SELECT权限 1 创建视图 2 授予视图SELECT权限 3 查询视图 4 访问emp表中的其他列 提示权限不足 5 2REVOKE REVOKE命令用于撤销已经授予的用户对象权限语法REVOKEprivilegesONobject nameFROMuserName 1 授予SCOTT用户DELETE权限 2 以SCOTT用户登录 删除HR方案对象emp表 3 以HR用户登录 撤销SCOTT用户的DELETE权限 事务开始连接到数据库 并开始执行一条DML UPDATE INSERT DELETE 语句前一个事务结束或者执行一条会自动提交事务的语句事务结束执行一条COMMIT 事务提交 或ROLBACK 事务回滚 语句执行一条会自动提交事务的语句执行一条DML语句却失败了自动提交事务执行一条DDL语句 如CREATETABLE ALTERTABLE等 执行一条DCL语句 如GRANT REVOKE等 断开与数据库的连接 例如 在SQL PLUS中执行一条DISCONNECT命令 或在同一个SQL PLUS中执行一条CONNECT命令启动一个会话 或者执行EXIT命令退出SQL PLUS等 6 使用TCL事务控制语言 6 1事务的开始与结束 6 2使用COMMIT命令提交事务 会话A向emp表中插入一条记录 在提交之前 另一个会话B无法查看到新的记录 只有在会话A提交事务之后 会话B才可以查看新增加的记录 这实际上就是事务的隔离性 会话A向emp表中插入一条记录 并未提交 会话B查看emp表中的记录 会话A提交事务 会话B再次查看emp表中的数据 6 3使用ROLLBACK回滚事务 1 执行DML语句 2 回滚事务 3 再次查询 6 4回滚部分事务 1 执行DML操作 2 设置保存点 3 回滚保存点 4 提交事务 DELETE语句被提交 INSERT语句被撤销了 7 使用基本函数 dual表dual表是Oracle数据库中的一个虚表 它有一行一列 所有者是SYS用户 但可以被数据库中的所有用户使用不能向该表插入数据 但可以使用该表来选择系统变量 或者求一个表达式的值单行函数日期函数ADD MONTHS函数 用于返回指定的日期加上指定的月数后的日期值MONTHS BETWEEN函数 用于返回两个日期之间的月份数LAST DAY函数 用于返回指定日期对应月份的最后一天NEXT DAY函数 用于返回指定下一个星期几的日期EXTRACT函数 用于提取日期中的特定部分 教员演示各函数的具体使用格式 使用基本函数 字符函数 使用基本函数 数学函数 使用基本函数 转换函数TO CHAR 用于将日期或数字以指定的格式转换为VARCHAR2数据类型的值语法TO CHAR d n fmt d表示日期 n表示数字 fmt是指定日期或数字的格式 使用基本函数 转换函数TO DATE 将字符类型转换为日期数据类型语法TO DATE char fmt fmt指定需要转换的日期格式 使用基本函数 转换函数TO NUMBER 将包含数字的字符串转换为NUMBER数据类型 从而可以对该数据类型执行算术运算 语法TO NUMBER char 通常不需要进行转换 因为Oracle可以对数字字符串进行隐式转换 使用基本函数 其他函数NVL 用于将空值替换为指定值语法NVL expr1 expr2 如果expr1不是NULL 则返回expr1 否则返回expr2向HR方案中的employees表中添加一个关于奖金的字段bonus 该字段充许为空 现要求计算雇员的实发工资 实发工资 基本工资 奖金 如果bonus为空 则将其值赋为零 使用基本函数 其他函数NVL2 与NVL类似 只是参数有所不同语法NVL2 expr1 expr2 expr3 如果expr1不是NULL 则返回expr2的值 否则返回expr3的值 7 使用基本函数 其他函数DECODE DECODE函数相当于一条件语句 IF 它将输入数值与函数中的参数列表相比较 根据输入值返回一个对应值语法DECODE input value value result value result default result input value表示试图处理的数值 DECODE函数将该数值与一系列的序偶相比较 决定最后的返回结果value表示一组成序偶的数值 如果输入数值与之匹配成功 相应的结果将被返回result表示一组成序偶的结果值default result表示未能与任何一序偶匹配成功时函数返回的默认值为employees表中的雇员按表需求