第4章SQL语言基础.ppt

第4章SQL语言基础 本章学习导航 数据库安全管理 本章学习要点 1 SQL查询的基本语法 2 简单查询的形式和实现 3 连接查询的形式和实现 4 子查询的实现和应用 5 联合查询及其应用 6 数据定义语句 7 数据操纵语句 8 数据控制语句计划 6 4课时 Oracle的常用账户 用户管理命令1 更改用户登录命令格式 conn用户名 密码 ASSYSDBA 注意 如果连接的是超级管理员 SYS 必须写上ASSYSDBA2 用户加锁和解锁加锁 SQL alteruser用户名accountlock 解锁 SQL alteruser用户名accountunlock SQL PLUS的操作命令 3 创建用户用具有创建新用户权限的用户登录到数据库 才能进行新用户的创建 例如 sys system 以及被授予该权限的其他用户 命令格式 SQL createuser用户名identifiedby密码 SQL PLUS的操作命令 4 更改用户密码命令格式 SQL alteruser用户名identifiedby密码 注意 如果忘记所有用户的密码 可启动SQL PlUS 输入以下命令 conn assysdbaSQL alteruser用户名identifiedby密码 SQL PLUS的操作命令 SQL PLUS的其他操作命令 1 查看当前连接用户SQL showuser 2 查看当前用户拥有的表对象 查看数据字典user tables SQL SELECTtable nameFROMuser tables 3 查看表或视图各个列的名称以及属性describe命令 可缩写为desc命令格式 SQL descobject name 其他常用命令 4 继续使用上次命令命令格式 SQL 5 清空屏幕命令格式 SQL clscr 6 命令用于执行脚本文件 命令格式 SQL 文件名 注意 需写文件路径 sql文件的后缀可以不写 Oracle常用数据类型 4 1示例数据库 Oracle数据库中有个默认用户SCOTT 就是BruceScott 而SCOTT用户的默认密码tiger 是当时Scott养的一只猫的名字 scott sql脚本文件位于 ORACLE HOME RDBMS ADMIN下在SQL PLUS下 以scott用户连接到Oracle数据库 运行该脚本 则可建立scott示例数据库 什么是 方案 方案的概念 方案 就是一系列数据库对象的集合 是数据库中存储数据的一个逻辑表示或描述 在Oracle11g数据库中 每个用户都拥有自己的方案 创建了一个用户 就创建了一个同名的方案 方案与数据库用户是对应的 用户在数据库中创建了一个方案对象后 这个方案对象默认地属于这个用户的方案 scott方案中的表 雇员表 emp scott数据库中的表 部门表 dept scott数据库中的表 奖金表 bonus scott数据库中的表 工资等级表 salgrade 4 2SQL语言 结构化查询语言 是一种数据库查询和程序设计语言 用于存取数据以及查询 更新和管理关系数据库系统 分为3类 1 数据定义语言 DDL 用来定义和管理数据库中的对象 如表 视图 存储过程 触发器 由CREATE ALTER DROP命令构成 SQL语言 2 数据操纵语言 DML 针对数据库中存储的数据进行相关操作 主要包括 增加 insert 删除 delete 更新 update 查询 select 在实际工作中增加 删除和更新所占使用DML比例为20 而查询所占比例为80 SQL语言 3 数据控制语言 DCL 用来管理用户对指定数据库对象的使用权限 常用操作有分配 grant 和回收 revoke 4 3查询的基本语法 查询基本语法 数据查询是一种从数据库中检索符合搜索条件的记录生成数据记录集合 并把它们存入数据记录集对象中的操作 4 3 1简单查询 选择列 1 选择所有列2 选择指定列3 使用别名4 计算列 例1 1 查询雇员的所有信息 例1 2 人力资源管理人员在查询雇员信息时只需要了解所有雇员的编号 姓名和工作职位以及雇佣日期 例1 3 查询雇员的姓名 职位 雇佣日期和月薪 用中文显示这些字段名 SELECTenameAS 姓名 jobAS 职位 hiredateAS 工作日期 salAS 工资 FROMemp 例1 4 查看工资等级表salgrade中同一等级最高工资和最低工资的差距 列的标题使用形象的别名 SELECT表达式AS列别名FROM数据源 使用列的别名 4 3 1简单查询 4 3 1简单查询 条件查询 使用where子句过滤行 使用WHERE子句来指定想要查询的行 WHERE后的条件可以是以下情况 关系表达式逻辑表达式空值用SQL操作符构造的条件 4 3 1简单查询 比较运算符 比较运算符 ANY在WHERE子句中可以使用ANY操作符将一个值与列表中的任何值作比较 ANY之前可以添加 或 例如 customer id ANY 2 3 4 表示只要行的customer id大于 中的任何一个都可以 比较运算符 ALL在WHERE子句中可以使用ALL操作符将一个值与列表中的所有值作比较 ALL之前可以添加 或 例如 customer id ALL 2 3 4 表示行的customer id大于 中的所有值时 是满足要求的 4 3 1简单查询 条件查询示例 例1 5 需要了解雇员中编号为 7521 的雇员的所有信息 例1 6 需要职位为 CLERK 或 ANALYST 的雇员的编号 姓名和雇佣日期 SQL操作符 4 3 1简单查询 注意 表中SQL运算符可以与NOT运算符取反处理 例如 NOTLIKE NOTBETWEEN和ISNOTNULL等 模式匹配的字符 匹配指定位置的一个字符 匹配从指定位置开始的任意个字符 例如 a 表示第一个字符为任意 第二个字符为a 的字符串 LIKE运算符 4 3 1简单查询 条件查询示例 例1 7 需要查询雇员名称中以S开头的雇员的编号 姓名 职位和月薪信息 例1 8 需要查询编号在 7369 7521 7789 中的雇员的信息 例1 9 需要查询没有获得奖金的雇员的信息 例1 10 需要查询月薪在1500 2000之间的雇员的信息 4 3 1简单查询 使用逻辑运算符可以将简单的条件组合起来 4 3 1简单查询 条件查询示例 例1 11 需要查询月薪在 1500 2000 的雇员的信息 例1 12 需要查询职位为 CLERK 或 SALESMAN 的雇员的信息 例1 13 需要查询月薪低于1000或高于2000的雇员的信息 4 3 1简单查询 选择行 去掉重复记录 在输出查询数据记录集时 如果需要去掉重复的记录 则可以使用DISTINCT来实现 例1 14 在雇员表中查询雇员来自哪些部门 输出部门编号 例1 15 在雇员表中查询有哪些是属于管理者 输出他们的员工编号 4 3 1简单查询 选择行 查询结果排序 如果需要定制查询数据记录集的输出顺序 则可以使用ORDERBY子句来实现 ORDERBY子句能够按照指定的一个或多个列 表达式 的升序或降序来重新排列查询数据记录集的输出顺序 例1 16 需要查询雇员的信息 并按奖金由低到高的顺序进行输出 例1 17 需要查询雇员的奖金信息 并按奖金由低到高 月薪由高到低的顺序进行输出 4 3 1简单查询 关于rowid和rownum rowid表中的每一行的唯一的行标识符 Oracle数据库内部使用行标识符来存储行的物理位置 它是一个18位的数字 使用base 64编码 rownum是一个伪列 它返回每一行在结果集中的行号 例如 查询返回的第一行的rownum是1 第二行的rownum是2 例1 18 查询雇员表中雇员的详细信息 输出包含rowid和rownum 4 3 1简单查询 查询结果分组 聚合函数 通过使用GROUPBY子句来实现 如果需要在分组的基础上进行组的过滤 则可以结合GROUPBY子句再使用HAVING子句来实现 对于需要对查询数据集进行汇总以生成统计报表的情况 则需要使用COMPUTE子句 4 3 1简单查询 选择行 查询结果分组 使用聚合函数 例1 19 查询每一个部门的雇员人数 例1 20 查询同一工作职位的雇员的平均奖金和平均月薪 例1 21 查询雇员中获得的最高奖金和最低奖金数 例1 22 查询工作职位为 CLERK 或 SALESMAN 的雇员的平均奖金和平均月薪 4 3 2连接查询 连接查询 实际上 数据库实例中的各个表之间可能存在某些内在关联 通过这些关联 可以为应用程序提供一些涉及多个表的复杂信息 如主表和外表之间就存在主键和外键的关联 将存在关联的多个表连接起来进行查询 称为连接查询根据连接类型不同 可分为 内连接和外连接 内连接是使用比较运算符作为连接条件的连接方式 内连接作为一种典型的默认连接方式 关键字INNER默认提供 使用内连接方式时 只有那些满足连接条件的数据记录被显示 不满足连接条件的数据记录将不被显示 根据连接条件中的关系运算符是否使用 内连接可以分为等连接和不等连接 使用较少 连接查询 内连接 4 3 2连接查询 等连接的语法示例 SELECT表1 字段1 表1 字段2 表2 字段1FROM表1INNERJOIN表2ON表1 字段 表2 字段 WHERE ORDERBY SQL 92标准下的内连接 4 3 2连接查询 4 3 2连接查询 内连接 等连接 例2 1 需要查询每个员工的员工号 姓名和所在部门名称及地址 语句1 SQL 86标准 SELECTE empno E ename D dname D locFROMempE deptDWHEREE deptno E deptno 4 3 2连接查询 内连接 等连接 例2 1 需要查询每个员工的员工号 姓名和所在部门名称及地址 SQL 92标准下的写法 SELECTE empno E ename D dname D locFROMempEINNERJOINdeptDONE deptno D deptno 使用USING子句简化 等连接且列名相同 SELECTE ename E job E sal SG gradeFROMempEINNERJOINsalgradeSGUSING deptno 连接的条件是除了 之外的操作符 包括 LIKE IN BETWEEN 不等连接示例 查询员工的姓名 职位 工资及工资级别 内连接 不等连接 4 3 2连接查询 SQL 92 SELECTE ename E job E sal SG gradeFROMempEINNERJOINsalgradeSGONE salBETWEENSG losalANDsg hisal 4 3 2连接查询 自连接 若用于连接的两个表或视图来源于同一个表或视图 这样的内连接也被称之为自连接 为了分别表示一个表与其自身 需要引入表的别名 例2 3 需要查询每个员工和其管理者的姓名 SELECTE1 ename员工 E2 ename管理者FROMempE1JOINempE2ONE2 mgr E1 empno SELECTE1 ename员工 E2 ename管理者FROMempE1 empE2WHEREE2 mgr E1 empno 4 3 2连接查询 外连接查询 即使连接中的列包含一个空值 外连接也会返回一行 在连接条件中可以使用外连接操作符来执行一个外连接 左外连接右外连接 4 3 2连接查询 外连接查询 左外连接 查询数据记录集包含来自一个表的所有数据记录和另一个表中的匹配数据记录的连接称为左外连接 对于左外连接 第一个表中的所有数据记录将被显示 第二个表 匹配表 如果找不到相匹配的数据记录 相应的列将显示为空值 NULL 否则显示匹配数据记录 例 查询每名员工的上级管理者 SELECTE1 ename员工 E2 ename管理者FROMempE1LEFTJOINempE2ONE2 mgr E1 empno 4 3 2连接查询 外连接查询 右外连接 查询数据记录集包含来自第二个表的所有数据记录和第一个表中的匹配数据记录的连接称为右外连接 对于右外连接 第二个表中的所有数据记录将被显示 第一个表 匹配表 如果找不到相匹配的数据记录 相应的列将显示为空值 NULL 否则显示匹配数据记录 例 查询员工的部门信息 将暂时没有分配部门的员工信息也显示出来 SELECTE empno员工编号 E ename姓名 D dname部门FROMempERIGHTJOINdeptDONE deptno D deptno 4 3 4子查询 子查询概念 将一个查询语句嵌套在另一个查询语句中的查询称为嵌套查询或子查询 子查询语句一般嵌入在另一个查询语句的WHERE子句或HAVING子句中 另外 子查询语句也可以嵌入在一个数据记录更新或删除语句中 例如 SELECTempno enameFROMempWHEREsal SELECTAVG hisal FROMsalgrade 例如 UPDATEempSETsal SELECTAVG hisal FROMsalgrade WHEREempno 7521 例如 DELETEFROMempWHEREsal SELECTAVG hisal FROMsalgrade 4 3 4子查询 在WHERE子句中使用子查询 语法格式为 WHERE表达式操作符 子查询 说明 此处的操作符可以是 SELECTAVG sal FROMemp 4 3 4子查询 IN子查询 对列表操作的子查询通过IN关键字实现父查询和子查询之间的连接 判断指定列的值是否出现在子查询的查询数据记录集中 使用IN的子查询语句返回的查询数据记录集一般由单列多行值组成 这也是子查询中最常用的一种形式 对于使用IN的子查询的连接条件 其语法格式为 WHERE表达式 NOT IN 子查询 4 3 4子查询 IN子查询 例4 2 假如学生选课系统有表student sno sname sex dept course cno cname grade selCourse sno cno score 查询参加 或未参加 选修的学生的学号 姓名 4 3 4子查询 ANY或ALL子查询 例4 3 需要了解比职位为 CLERK 的员工获得奖金数高的员工信息 查询结果按奖金数降序排列 语句 SELECT FROMempWHEREcomm ALL SELECTcommFROMempWHEREjob CLERK ORDERBYcommDESC 4 3 4子查询 EXISTS子查询 EXISTS操作符用于检查子查询返回行的存在性 它并不关心子查询返回多少行 只关心子查询是否返回任何行 例4 4 查询职位是 CLERK 且有佣金的员工记录 例如 SELECTempno enameFROMempWHEREjob CLERK ANDcommISNOTNULL 例如 SELECTempno enameFROMempWHEREEXISTS SELECT FROMempWHEREjob CLERK ANDcommISNOTNULL 4 3 5集合查询 UNION联合查询 使用UNION操作符可将两个表中的结果组合起来 不包括重复行 UNIONALL将两个表中的结果组合起来 包括重复行 例 4 5 查询emp表及bonus表中员工的姓名 职位 月薪和佣金 SELECTename job sal commFROMempUNIONSELECTename job sal commFROMbonus 4 3 5集合查询 UNION联合查询 例 4 6 查询emp表中员工姓名中包含S字符和T字符的员工信息 SELECTempno enameFROMempWHEREenameLIKE S UNIONSELECTempno enameFROMempWHEREenameLIKE T 4 3 5集合查询 INTERSECT查询 INTERSECT为交运算 可取到两个表中的共同记录 示例 SELECT FROMempWHEREenameLIKE S INTERSECTSELECT FROMempWHEREenamelike SM 4 3 5集合查询 MINUS查询 MINUS为差运算 可取到第一个表中不同于第二个表的数据行 例如 SELECTempno enameFROMempWHEREsal 2500MINUSSELECTempno enameFROMempWHEREsal 1000ANDsal 2600 根据sql查询功能 结合Oracle的示例数据库 尝试各种查询操作 并编写对应的查询语句 任务 4 4数据定义 定义数据对象 包括 表 视图和索引 在Oracle数据库中 还有其他数据对象 如 触发器 游标 过程 程序包等 SQL的数据定义语句 4 4 1创建表 创建表的简化SQL语法 CREATETABLE CONSTRAINTcnstraint defDEFAULTdefault exp CONSTRAINTconstraint defDEFAULTdefault exp Oracle常用数据类型 LOB数据类型 用于大型的 未被结构化的数据 如二进制文件 图片文件等 LOB数据类型又分为BLOB CLOB和BFILE三种 BLOB类型 用于存储二进制对象 如图像 音频 视频 CLOB类型 用于存储字符格式的大型对象 Oracle将数据转换成Unicode格式 BFILE类型 将二进制文件作为操作系统文件存储在数据库外部 BFILE类型的字段仅保存二进制文件的指针 约束及其含义 在scott示例数据库中创建一个经理表manager 结构如下 创建表示例 CREATETABLEmanager mgr idNUMBER 6 NOTNULLUNIQUE nameVARCHAR2 10 emailVARCHAR2 20 phoneVARCHAR2 20 salaryNUMBER 8 workdateDATE 4 4 2修改表 修改表可以执行以下任务 添加 修改或删除列 添加或删除约束 启用或禁用约束 添加标题文字 ALTERTABLE ADD 完整性约束 DROP MODIFY 4 4 2修改表 修改列的长度修改数字列的精度修改列的数据类型ALTERTABLE表名MODIFY列名类型 长度 修改列的默认值ALTERTABLE表名MODIFY列名DEFAULT默认值 添加非空约束 MODIFY列名CONSTRAIT约束名NOTNULL 添加约束 ADDCONSTRAINT 删除约束 DROPCONSTRAINT 添加标题文字 修改列 4 4 2修改表 例1 向manager表中增加性别列sex 数据类型为CHAR 长度为2 例2 将manager表中mgr id字段改为8位 例3 为manager表中的mgr id创建主键约束 例3 删除manager表中mgr id字段的UNIQUE约束 例4 删除manager表中的phone字段 添加标题文字 4 4 3删除表 添加标题文字 语法 DROPTABLE 用户方案 4 4数据定义 案例 假设某商店销售书籍 DVD CD 影像之类的商品 此商店数据库将存放客户 员工 产品和销售的有关信息 现要创建一个store数据库 经过前期分析和设计 计划建立以下数据表 结构定义 store模式下的表 customers 存储顾客的详细信息 数据定义语句 创建customers表的脚本CREATETABLEcustomers customer idINTEGERCONSTRAINTcustomers pkPRIMARYKEY first nameVARCHAR2 10 NOTNULL last nameVARCHAR2 10 NOTNULL dobDATE phoneVARCHAR2 12 store模式下的表 product types存储销售的产品类型 创建product types表的脚本CREATETABLEproduct types product type idINTEGERCONSTRAINTproduct types pkPRIMARYKEY nameVARCHAR2 10 NOTNULL store模式下的表 products 存储销售的产品的信息 创建products表的脚本CREATETABLEproducts product idINTEGERCONSTRAINTproducts pkPRIMARYKEY product type idINTEGERCONSTRAINTproducts fk product typesREFERENCESproduct types product type id nameVARCHAR2 30 NOTNULL descriptionVARCHAR2 50 priceNUMBER 5 2 store模式下的表 purchases 存储顾客的购买记录信息 创建purchases表的脚本CREATETABLEpurchases product idINTEGERCONSTRAINTpurchases fk productsREFERENCESproducts product id customer idINTEGERCONSTRAINTpurchases fk customersREFERENCEScustomers customer id quantityINTEGERNOTNULL CONSTRAINTpurchases pkPRIMARYKEY product id customer id store模式下的表 employees 存储商店中员工的信息 创建employees表的脚本CREATETABLEemployees employee idINTEGERCONSTRAINTemployees pkPRIMARYKEY manager idINTEGER first nameVARCHAR2 10 NOTNULL last nameVARCHAR2 10 NOTNULL titleVARCHAR2