Oracle 10g( 二 )

1. Oracle 10g(二) SQL基础

2. 课程介绍 • 本课程主要讲授Oracle 10g SQL基础 • 适用于数据库管理员(DBA) • 面向的学员是对SQL有基本了解的应用开发工程师 • 全部课程时间大约是270分钟

3. 关于示例 • 示例原脚本位于$oracle/rdbms/admin/scott.sql • 经修改后见附件一. • 示例中的数据库结构: dept(部门表): emp(员工表,fk:dept.deptno): salgrade(薪资等级表):

4. 概 要 对SQL语句做全面的介绍 详细介绍Oracle中的数据类型 SQL的基本语法 函数的种类和作用 多表的数据查询 数据的修改 数据的删除 事务的作用及事务的种类

5. SQL SQL(Structured Query Language): 结构化查询语言,是一种在关系数据库中定义和操纵数据的标准语言. SQL的标准由ANSI(美国国家标准化研究所)开发并制定,被众多数据库厂商遵守并扩展和完善.

6. SQL 语言概述 • 1970年 IBM的E.F.Codd提出关系模型 • 1974年 Boyce和Chamberlin提出了Structured English Query Language(SEQUEL),简称SQL • 1975-1979年 IBM的San Jose在IBM 370计算机上研制了System R关系型数据库系统，支持SQL • 1977年 ORACLE第一个推出关系型数据库

7. SQL 语言概述 • 第一个SQL标准是1986年10月由ANSI颁布的，它是一个美国标准，即SQL86 • 1989年颁布第二个SQL标准，即SQL89 • 1992年颁布了SQL92,即SQL2 • 1999年颁布了SQL3

8. SQL 分类 SQL可以分为以下几类： • 数据定义语言(DDL) • 数据操纵语言(DML) • 事务控制(TC) • 会话控制 • 系统控制

9. 数据定义语言(DDL) DDL(Data Definition Language): 用于定义,修改,删除数据库模式对象,进行权限管理等. 如：create,alter,drop,rename

10. 数据操纵语言(DML) DML(Data Manipulation Language): 用于查询,生成,修改,删除数据库中的数据. 如: select, update,insert,delete

11. 事务控制(TC) TC(Transaction Control): 用于把一组DML语句组合起来形成一个事务并进行事务控制. 如:commit,rollback

12. 会话控制 会话控制(Session Control): 会话控制,用于控制一个会话(session,指从与数据库连接开始到断开之间的时间过程)的属性.

13. 系统控制 系统控制(System Control): 用于管理数据库的属性.只有一条语句,即alter system.

14. Oracle 10g(二)SQL基础 2.1 查询数据

15. 查询数据 查询数据是数据库的核心操作. 查询数据是关系代数,关系演算在SQL语言中的主要体现. 查询数据时,即可以查询所有的列,也可以查询指定的列,所以必须要清楚表的结构及其所有列的概要.

16. 查询表结构 查询表的结构有两种方式: • describe(简写desc) 这是Oracle对SQL的重要改进之一; • select * from tab; 查看用户下所有的表

17. 取消重复行 关键字 distinct 可以取消重复的行: --- 查询指定列 select deptno, job from emp; --- 过滤重复行 select distinct deptno, job from emp;

18. 特殊数据类型 ROWID 用于记录数据库中记录的唯一行号。 如：select rowid,job from emp;

19. SQL基本语法 • 数字运算符(+、-、*、/) 注意：取模用函数MOD（） 如： -- 底薪加500 select losal+500 from salgrade;

20. SQL基本语法 2、比较运算符(=、>、<、>=、<=、<>、!=、!>、!<) 如： -- 查询薪水小于等于2500的员工 select * from emp where sal <= 2500;

21. SQL基本语法 3、逻辑运算符(or 、and、 not) 如： -- 查询工资大于等于1000小于等于1500得员工 select * from emp t where t.sal >= 1000 and t.sal <= 1500;

22. SQL基本语法 4、其他操作符 IN 和 NOT IN 成员 BETWEEN…AND…范围(在上限和下限之间) IS NULL 和 IS NOT NULL 找出NULL值或非NULL 值 例: select * from emp t where t.deptno in(10, 20); select * from emp t where t.sal between 2000 and 3000; select * from emp t where t.comm isnotnull;

23. SQL基本语法 LIKE 模式匹配(或模糊查询) 在搜索字符串中使用通配符 “%”和”_” ,“%”号代表0个或多个字符,而”_”则表示单个字符.如果搜索字符串中需要查询实际的”%”和”_”,那么就需要在搜索字符串中使用一个转义字符(ESCAPE character),如果查一个叫以”S%”开头的名字: select * from emp t where t.ename like 's\%%' escape '\'

24. SQL基本语法 5、 简单查询 Select [all|distinct]select_column_list] [form {table_name|view_name}] [where clause] [group by clause] [having clause] [order by clause]

25. 简单查询 如： select * from emp;select ename,job from emp;select ename as姓名, job as职务 from emp; select ename 姓名, job 职务 from emp; select ename from emp where ename=‘SMITH’;select * from emp order by empno desc;select ename,count(job) from emp groupby ename;?为什么没有分组 selectcount(job) from emp groupby job;

26. 组处理函数 函数 描述 MAX 返回列或表达式的最大值(数据最大,时间最晚) MIN 返回列或表达式的最小值(数据最小,时间最早) AVG 返回列或表达式的平均值 SUM 返回列或表达式的总和 COUNT 返回非NULL行的行数 VARIANCE 返回列或表达式的方差 STDDEV 返回列或表达式的标准偏差 注：样本中各数据与样本平均数的差的平方和的平均数叫做样本方差；样本方差的算术平方根叫做样本标准差。

27. 组处理函数 组处理函数应注意以下几点: • 组处理函数只能出现在选择列表,ORDER BY子句,HAVING子句中,而不能出现在WHERE子句和GROUP BY子句中 • 除了count(*)之外,其他处理函数都会忽略NULL行 • 如果选择列表同时包含列,表达式和组处理函数,则这些列,表达式都必须出现在GROUP BY字句中. • 在组处理函数中可以指定ALL和DISTINCT选项.其中ALL是默认选项,表示统计所有的行(包括重复的行),而DISTINCT只会统计不同的行

28. 组处理函数 --不包含组的查询 Select empno,sal,comm from emp Where deptno=30; --包含组的查询 Select avg(sal),avg(distinct sal),max(sal),min(sal), sum(sal),count(*),count(sal),count(distinct sal), count(distinct sal),count(comm) From emp where deptno=30;

29. 分组 • 单列分组 --显示每个部门的平均工资和最高工资 Select deptno,avg(sal),max(sal) from emp group by deptno; • 多列分组 --显示每个部门、每种岗位的平均工资和最高工资 Select deptno,job,avg(sal),max(sal) from emp groupby deptno, job;

30. 分组 • 使用ROLLUP和CUBE限定词生成报表 --显示每个部门、每种岗位的平均工资和最高工资及横向统计结果 Select deptno,job,avg(sal),max(sal) from emp groupbyrollup(deptno,job); --显示每个部门、每种岗位的平均工资和最高工资及纵向统计结果 Select deptno, job, avg(sal), max(sal) from emp groupbycube(deptno, job);

31. Cube分组报表格式

32. Having子句 Having 字句用来限制(过滤)分组处理后的结果显示,并且必须跟再Group by字句之后 --显示平均工资高于2000元的部门编号、平均工资和最高工资 select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal)>2000; --注意分组函数不能出现在where子句中 select deptno,avg(sal),max(sal) from emp where avg(sal)>2000 group by deptno;

33. 多数据表查询 • 连接查询 • 合并查询 • 子查询

34. 连接查询 • 笛卡儿连接 • 相等连接 • 不等连接 • 自我连接

35. 笛卡儿连接 • 当从两个或两个以上的表中选择数据，并且在where子句中没有指定连接条件时就是笛卡儿连接 如： Select d.deptno,d.dname,e.ename,e.sal From dept d,emp e; 笛卡儿集合的计算来自于关系代数，其结果是两个结果集的乘积 m*n

36. 相等连接 从笛卡儿集合中过滤相等条件的集合 如： Select d.deptno,d.dname,e.ename,e.sal From dept d,emp e Where d.deptno=e.deptno;

37. 不等连接 从笛卡儿集合中过滤掉相等条件的集合 如： --查找部分编号为30的员工工资等级 Select a.ename, a.sal, b.grade from emp a, salgrade b where a.sal between b.losal and b.hisal and a.deptno = 30

38. 自我连接 • 在一个表中进行的连接,表名在FROM字句中出现2次. 如： --查询雇员对应的管理员的名字 Select e.empno, e.ename, m.ename 管理员 From emp e, emp m Where m.empno = e.mgr;

39. 合并查询 • 对几个连接查询的结果进行合并，我们称为合并查询 其基本语法是： SELECT 语句1 [UNION | UNION ALL | INTERSECT | MINUS] SELECT 语句2

40. 合并查询 • 字段名称以第一个结果集为准 • 排序以第一个结果集的列名或者索引为准

41. union • 获取结果集的并集，并自动去除重复行，以第一列的结果进行排序 如： Select empno,ename,mgr from emp Where deptno=30 Union Select empno,ename,mgr from emp Where job=‘MANAGER’;

42. union all • 获取结果集的并集，不去除重复行，不排序 如： Select empno,ename,mgr from emp Where deptno=30 Union all Select empno,ename,mgr from emp Where job=‘MANAGER’;

43. intersect • 获取结果集的交集，只会显示同时存在于结果集合中的数据，并且以第一列的结果进行排序 如： Select empno,ename,mgr from emp Where deptno=30 intersect Select empno,ename,mgr from emp Where job=‘MANAGER’;

44. minus • 获取结果集的差集，只会显示在第一个结果集中存在，但在第二个结果集中不存在的数据，并且以第一列的结果进行排序 如： Select empno,ename,mgr from emp Where deptno=30 minus Select empno,ename,mgr from emp Where job=‘MANAGER’;

45. 子查询 • 子查询(subquery)是指嵌入在其他SQL语句中的一个查询. • 子查询可以嵌套,嵌套子查询的求解次序是由里向外,即先处理子查询,然后将其查询的结果用于父查询. • 使用嵌套子查询的方法,可以用一系列简单的查询构成复杂的查询,从而明显增强SQL语句的功能,这正是SQL语言结构化(structure)的含义所在.

46. 子查询 • 单行子查询(single-row subqueries) 使用的运算符号(=,>,<,>=,<=,<>) • 多行子查询(multiple-row subqueries) 使用的运算符号(in,not in,exists,not exits,all,any) • 相关子查询(correlated subqueries) 格式 select 列名,(select 语句) from 表名 • 标量子查询(scalar subqueries) 子查询是返回单行单列,格式同上 • 多列子查询(multiple-column subqueries) • 在DDL语句中使用子查询 • 在DML语句中使用子查询

47. 单行子查询 • 子查询只返回一行数据，而父查询可以多行 --显示工资最高的雇员信息 Select ename, deptno, sal from emp Where sal = (selectmax(sal) from emp);

48. 多行子查询 • 子查询返回多行数据。 --显示与部门编号为20的岗位相同的雇员信息 Select ename, deptno, sal, job from emp Where job in (selectdistinct job from emp where deptno = 20); --显示不与部门编号为20的岗位相同的雇员信息 Select ename, deptno, sal, job from emp where job notin (selectdistinct job from emp where deptno = 20);

49. 多行子查询 --显示高于部门编号为20的所有雇员的工资的雇员信息 select ename, deptno, sal, job from emp where sal > all (select sal from emp where deptno = 20); ?思考: 怎么把上句变为单行* --显示高于部门编号为20的任何雇员的工资的雇员信息 select ename, deptno, sal, job from emp where sal > any (select sal from emp where deptno = 20);

50. 相关子查询 • 子查询中引用了父查询中某些表或者列 --显示每个部门的最高工资的雇员信息 select deptno, (selectmax(sal) from emp b where b.deptno = a.deptno) maxsa from emp a orderby deptno; --增加distinct selectdistinct deptno, (selectmax(sal) from emp b where b.deptno = a.deptno) maxsal from emp a orderby deptno;