第13章SQL语言基础 本章内容 SQL语句分类数据查询 SELECT 数据操纵 INSERT UPDATE DELETE 事务控制SQL函数 本章要求 掌握数据查询的各种应用掌握数据操纵的各种应用掌握事务处理了解SQL函数应用 13 1SQL语言概述 SQL语言介绍SQL语言的分类SQL语言的特点 13 1 1SQL语言介绍 SQL StructuredQueryLanguage 语言是1974年由Boyce和Chamberlin提出的 SQL语言是关系数据库操作的基础语言 将数据查询 数据操纵 数据定义 事务控制 系统控制等功能集于一体 从而使得数据库应用开发人员 数据库管理员等都可以通过SQL语言实现对数据库的访问和操作 13 1 2SQL语言的分类 据定义语言 DataDefinitionLanguage DDL 用于定义 修改 删除数据库对象 包括CREATE ALTER DROP GRANT REVOKE AUDIT和NOAUDIT等 数据操纵语言 DataManipulationLanguage DML 用于改变数据库中的数据 包括数据插入 INSERT 数据修改 UPDATE 和数据删除 DELETE 数据查询语言 DataQueryLanguage DQL 用于数据检索 包括SELECT 事务控制 TransactionControl 用于将一组DML操作组合起来 形成一个事务并进行事务控制 包括事务提交 COMMIT 事务回滚 ROLLBACK 设置保存点 SAVEPOINT 和设置事务状态 SETTRANSACTION 系统控制 SystemControl 用于设置数据库系统参数 包括ALTERSYSTEM 会话控制 SessionControl 用于设置用户会话相关参数 包括ALTERSESSION 13 1 3SQL语言的特点 功能一体化 几乎涵盖了对数据库的所有操作 语言风格统一 高度的非过程化 在使用SQL语言操作数据库时 用户只需要说明 做什么 而不需要说明 怎样做 用户任务的实现对用户而言是透明的 由系统自动完成 这大大减轻了用户的负担 同时降低了对用户的技术要求 面向集合的操作方式 SQL语言采用集合操作方式 不仅查询结果可以是多条记录的集合 而且一次插入 删除 修改操作的对象也可以是多条记录的集合 面向集合的操作方式极大地提高了对数据操作效率 多种使用方式 SQL语句既是自含式语言 又是嵌入式语言 SQL语言可以直接以命令方式与数据库进行交互 也可以嵌入到其他的高级语言中使用 简洁 易学 SQL语言命令数量有限 语法简单 接近于自然语言 英语 因此容易学习和掌握 13 2数据查询 数据查询基础基本查询分组查询连接查询子查询合并查询 13 2 1数据查询基础 基本语法 SELECT ALL DISTINCT column name expression FROMtable1 name table2 name view name WHEREcondition GROUPBYcolumn name1 column name2 HAVINGgroup condition ORDERBYcolumn name2 ASC DESC column name2 13 2 2基本查询 无条件查询有条件查询查询排序查询统计 1 无条件查询 查询所有列SELECT FROMemp 查询指定列SELECTdeptno dnameFROMdept 使用算术表达式SELECTempno sal 0 8FROMemp 使用字符常量SELECTempno Nameis enameFROMemp 使用函数SELECTempno UPPER ename FROMemp 改变列标题SELECTenameemployeename salsalaryFROMemp 使用连接字符串SELECT 员工号 empno 员工名 enameFROMemp 消除重复行SELECTALLdeptnoFROMemp SELECTDISTINCTdeptnoFROMemp 2 有条件查询 查询满足条件的元组可以通过WHERE子句实现 WHERE条件中常用的运算符号 关系运算SELECTempno ename salFROMempWHEREdeptno 10 SELECTempno ename salFROMempWHEREsal 1500确定范围谓词BETWEENAND与NOTBETWEENAND SELECT FROMempWHEREdeptnoBETWEEN10AND20 SELECT FROMempWHEREsalNOTBETWEEN1000AND2000 确定集合谓词IN可以用来查找属性值属于指定集合的元组 SELECTempno ename salFROMempWHEREdeptnoIN 10 30 字符匹配 百分号 代表任意长 长度为0 字符串 下划线 代表任意单个字符 ESCAPE 转义字符SELECT FROMempWHEREenameLIKE S SELECT FROMempWHEREenameLIKE A SELECT FROMempWHEREenameLIKE x ESCAPE x 空值操作涉及空值查询时使用ISNULL或ISNOTNULL 这里的IS不能用 替代 SELECT FROMempWHEREdeptnoISNULL SELECT FROMempWHEREcommISNOTNULL 逻辑操作用逻辑运算符NOT AND和OR来联结多个查询条件 优先级 NOT AND OR 用户可以用括号改变优先级 IN谓词实际上是多个OR运算的缩写 SELECT FROMempWHEREdeptno 10ANDsal 1500 SELECT FROMempWHERE deptno 10ORdeptno 20 ANDsal 1500 注意 使用BETWEEN AND NOTBETWEEN AND IN NOTIN运算符的查询条件都可以转换为NOT AND OR的逻辑运算 例如 下面两个语句是等价的 SELECT FROMempWHEREsal 1000ANDsal 2000 SELECT FROMempWHEREsalBETWEEN1000AND2000 升序 降序排序ASC 升序 缺省 DESC 降序SELECTempno ename salFROMempORDERBYsal SELECTempno ename salFROMempORDERBYsalDESC 多列排序首先按照第一个列或表达式进行排序 当第一个列或表达式的数据相同时 以第二个列或表达式进行排序 以此类推 SELECT FROMempORDERBYdeptno salDESC 3 查询排序 按表达式排序可以按特定的表达式进行排序 SELECTempno ename salFROMempORDERBYsal 12 使用别名排序可以使用目标列或表达式的别名进行排序 SELECTempno sal 12salaryFROMempORDERBYsalary 使用列位置编号排序如果列名或表达式名称很长 那么使用位置排序可以缩短排序语句的长度 SELECTempno sal 12salaryFROMempORDERBY2 4 查询统计 注意除了COUNT 函数外 其他的统计函数都不考虑返回值或表达式为NULL的情况 聚集函数只能出现在目标列表达式 ORDERBY子句 HAVING子句中 不能出现在WHERE子句和GROUPBY子句中 默认对所有的返回行进行统计 包括重复的行 如果要统计不重复的行信息 则可以使用DISTINCT选项 如果对查询结果进行了分组 则聚集函数的作用范围为各个组 否则聚集函数作用于整个查询结果 SELECTcount avg sal max sal min sal FROMempWHEREdeptno 10 SELECTavg comm sum comm FROMemp SELECTcount DISTINCTdeptno FROMemp SELECTvariance sal stddev sal FROMemp 13 2 3分组查询 基本语法单列分组查询多列分组查询使用HAVING子句限制返回组使用ROLLUP和CUBE合并分组查询 SELECTcolumn group function FROMtable WHEREcondition GROUP BYROOLUP CUBE GROUPINGSETS group by expression HAVINGgroup condition ORDERBYcolumn ASC DESC 1 基本语法 注意 GROUPBY子句用于指定分组列或分组表达式 集合函数用于对分组进行统计 如果未对查询分组 则集合函数将作用于整个查询结果 如果对查询结果分组 则集合函数将作用于每一个组 即每一个分组都有一个集合函数 HAVING子句用于限制分组的返回结果 WHERE子句对表中的记录进行过滤 而HAVING子句对分组后形成的组进行过滤 在分组查询中 SELECT子句后面的所有目标列或目标表达式要么是分组列 要么是分组表达式 要么是集合函数 单列分组查询将查询出来的记录按照某一个指定的列进行分组SELECTdeptno count avg sal FROMempGROUPBYdeptno 多列分组查询在GROUPBY子句中指定了两个或多个分组列SELECTdeptno job count avg sal FROMempGROUPBYdeptno job 使用HAVING子句限制返回组可以使用HAVING子句 只有满足条件的组才会返回 SELECTdeptno count avg sal FROMempGROUPBYdeptnoHAVINGavg sal 1500 使用ROLLUP和CUBE如果在GROUPBY子句中使用ROLLUP选项 则还可以生成横向统计和不分组统计 如果在GROUPBY子句中使用CUBE选项 则还可以生成横向统计 纵向统计和不分组统计 SELECTdeptno job avg sal FROMempGROUPBYROLLUP deptno job SELECTdeptno job avg sal FROMempGROUPBYCUBE deptno job 合并分组查询使用GROUPINGSETS可以将几个单独的分组查询合并成一个分组查询SELECTdeptno job avg sal FROMempGROUPBYGROUPINGSETS deptno job 13 2 4连接查询 交叉连接内连接等值连接不等值连接自身连接外连接左外连接右外连接全外连接 1 交叉连接 概念两个或多个表之间的无条件连接 一个表中所有记录分别与其他表中所有记录进行连接 如果进行连接的表中分别有n1 n2 n3 条记录 那么交叉连接的结果集中将有n1 n2 n3 条记录 以下情况可以出现交叉连接连接条件省略连接条件非法一个表的所有行被连接到另一个表的所有行示例SELECTename dnamefromemp dept 2 内连接 执行过程内连接语法等值内连接非等值内连接自身内连接 执行过程首先在表1中找到第一个元组 然后从头开始扫描表2 逐一查找满足连接条件的元组 找到后就将表1中的第1个元组与该元组拼接形成结果表中的一个元组 表2全部找完后 再找表1中的第2个元组 然后再从头扫描表2 逐一查找满足连接条件的元组 找到后就将表1中的第2个元组与该元组拼接形成结果表中的一个元组 重复执行 直到表1中的全部元组都处理完毕为止 内连接语法 标准SQL语句的连接方式SELECTtable1 column talbe2 column FROMtable1 INNER JOINtable2 JOIN ONcondition 内连接语法 Oracle扩展的连接方式SELECTtable1 column talbe2 column