1 / 140

Oracle 10g( 二 )

Oracle 10g( 二 ). SQL 基础. 课程介绍. 本课程主要讲授 Oracle 10g SQL 基础 适用于 数据库管理员 (DBA) 面向的学员是 对 SQL 有基本了解的应用开发工程师 全部课程时间大约是 270 分钟. 关于示例. 示例原脚本位于 $oracle/rdbms/admin/scott.sql 经修改后见 附件一 . 示例中的数据库结构 : dept( 部门表 ): emp( 员工表 ,fk:dept.deptno): salgrade( 薪资等级表 ):. 概 要. 对 SQL 语句做全面的介绍

guy-glover
Download Presentation

Oracle 10g( 二 )

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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;

More Related