OracleAlpha.docx

1、OracleAlphaOracle第一天上午课程时间与内容安排表*第一时间段:课程引言ORCLE-F-001Oracle引言1. Oracle数据库简介2. 两种常用的数据存储形式介绍file和table。3. 什么是SQL语句？SQL语句的分类。4. Table表、DB数据库和RDBMS数据库管理系统的关系。5. ORACLE_SID介绍。6. 通过sqlplus和数据库连接。7. 本次教学过程学员练习所涉及到的表结构的介绍。一、 Oracle数据库简介Oracle厂商介绍、国际地位 Oracle与Sun的关系 Oracle版本介绍（i与g的区别） Oracle数据库分为两部分（服务器端、客

2、户端） Oracle的基本用户、管理员介绍(SCOTT/SYS) 二、两种常用的数据存储形式介绍file和table。 三、什么是SQL语句？SQL语句的分类。1、什么是SQL语句？ 英文全称: Structured Query Language(结构化查询语言) SQL是一种专门用来与数据库通信的语言。2、SQL的分类(按创建操作控制的顺序):数据存到数据是以表的形式需要使用DDL语言。操作数据库中的表需要使用到DML语言。将数据存到数据库中需要使用TCL语言。从数据库中取得数据需要使用DQL语言。(1)DDL(create table建表alter table修改drop table)数据

3、定义语言建表需要知道列名、列名类型、列名长度。(2)DML(insert插入update更新delete删除)数据操作语言(3)TCL(commit提交、rollback回滚)事务控制语言(4)DQL(select)数据查询语言说明:首先讲解数据库的作用，和使用数据库所需要掌握的语言。四、表、DB和RDBMS的关系。(1)表：二维表格（行代表一条记录、列代表一个字段）(2)DB: 是依照某种数据模型组织起来并存放二级存储器 中的数据集合. (3) RDBMS: 它通过数据、关系和对数据的约束三者组成的数据模型来存放和管理数据。五、ORACLE_SID介绍1、ORACLE_SID数据库对应的实例

4、的名字 Linux终端下查看实例名称:echo $ORACLE_SID2、创建一个数据库的同时Oracle为我们创建的三个文件 DataFile数据文件、LogFile日志文件、ControlFile控制文件六、Windows下Oracle登陆的方式sqlplus 用户名/密码(命令行)sqlplusw 用户名/密码(图形界面方式登录)七、讲解我们练习表的结构(1)S_EMP(员工信息表)字段说明:title职位名称(2)S_DEPT(部门信息表)(3)S_REGION(区域表)*第二时间段:编写第一条SELECT语句ORCLE-F-002SELECT语句1、SELECT语句的功能是什么？2、

5、SELECT语句的基本语法？3、SELECT语句在sqlplus中如何执行、如何修改？4、SELECT语句的执行原理一、 SELECT语句的功能1、功能:从一个或多个表中检索一个或多个数据列。2、select的操作方式:投影操作、选择操作、连接操作投影操作是对列的操作(用select 列名实现)选择操作是对行的操作。连接操作是对2张表的操作。二、SELECT语句的基本语法1、语法格式: 2、组成部分 在最简单的SELECT语句中必须包含以下内容 一个SELECT子句，指定被显示的列 一个FROM子句，指定从那张表查询数据 3、SELECT子句后面可以跟哪些内容？*要查询表的所有列名distin

6、ct 字段名 去除该字段的重复值column/expor指定的字段名或者表达式column alias列名 该列名的别名4、演示找出每个员工的姓名和工资 select first_name,salaryfrom s_emp; 要求学员练习列出每个员工的名字和职位？ select first_name,titlefrom s_emp; 四、SELECT语句的处理过程： 数据库用户写完一条sql语句通过用户进程sqlplus将sql送往服务进程Server process创建会话Session，然后按以下流程: 处理一条SELECT语句的过程(以下依次执行)： 分析语句：(1)搜索是否有相同的语句

7、从哪搜索: 从SGA中的共享池中找.(2)检查语法、表名、权限(3)在分析过程中给对象加锁(4)生成执行计划绑定变量给变量赋值执行语句获取数据将数据返回给用户进程*第三时间段: SELECT子句的功能ORCLE-F-003SELECT功能1、SELECT子句实现投影功能。2、数值类型表达式(加减乘除)3、别名的引入及双引号的用法4、SELECT语句后面跟数值表达式的处理流程5、算术表达式中null值的处理。6、字符串拼接、单引号的用法7、distinct的用法一、在number类型上使用算术表达式(加减乘除)演示列出每个员工的名字和年薪select first_name,salary*12 f

8、rom s_emp;二、 字段(列)别名1、别名含义：给列起别名能够改变一个列、表达式的标识适合用于做计算字段：比如:select first_name,salary*12 年薪 from s_emp;在原名和别名之间可以使用as关键字(一般都不写)2、双引号含义：别名中包含空格、特殊字符、或希望大小写敏感，则用双引号将其括起来:比如:select first_name,salary*12 ann sal from s_emp;（空格） select first_name,salary*12 Annsal from s_emp; (特殊字符) 三、算术表达式中null值的处理演示计算所有人的总

9、收入：select first_name,salary*12*(1+commission_pct/100) tol_sal from s_emp;引入以下知识点:1)什么是空值(NULL)空值在输入数据时，该字段没有指定值，并且也没有缺省值：空值不等于0空值不等于空格算术表达式包含空值将导致结果为空在算术表达式中包含空值需要用空值转换函数coalesce处理 2)讲解空值转换函数：什么是函数？ 语法格式: 函数名(参数) 作用: 按函数的功能返回处理后的结果。空值处理之一nvl(p1,p2)的含义解释： 如果p1是空值，则返回p2，反之返回p1本身。演示以上讲解完毕，演示:select fir

10、st_name,salary*12*(1+nvl(commission_pct,0)/100) tol_sal from s_emp;空值处理之二coalesce(p1,p2)的含义解释： 如果p1是空值，则返回p2，反之返回p1本身。演示以上讲解完毕，演示:select first_name,salary*12*(1+coalesce(commission_pct,0)/100) tol_sal from s_emp;3)总结:nvl( )与coalesce( )的区别？nvl( )是Oracle数据库特有的、coalesce( )是更多数据库特有的。四、字符串拼接、单引号的用法 演示请列出

11、所有员工的全名(即first_name+last_name)执行select first_name last_name from s_emp;执行完毕后给学员讲解这条语句并没有得到我们想要的结果，而是把last_name当成了列别名，那么我们该如何处理了？引出教师要讲解的以下知识点： |字段拼接 使用单引号表示字符串 知识点讲解后演示正确写法:select first_name|last_name 全名 from s_emp;五、distinct关键字的用法1、演示列出所有部门名称？ 教学批注这条语句给我们的结果包含部门相同的名称，故引出去除重复值的知识点：知识点开始讲解distinct关键字

12、的作用和使用select distinct name from s_dept；注意:多个字段的时候distinct是如何工作的？是做联合去重，只有所有字段值都唯一时候才有去重效果。如果select后面跟distinct关键字则distinct前面不能在跟任何东西。DISTINCT必须使用列名，不能使用计算或者表达式。select语句后可以包含文字值，字符、表达式、数字字符文字值必须使用单引号括起来。&OracleDay01下午时间授课计划与内容： *第一时间段: WHERE子句的功能ORCLE-F-004WHERE功能1. where 子句的基本结构:条件表达式，实现选择操作2. 增加wher

13、e 子句后select 语句的执行过程3. 数值类型在where 子句中的用法4. 条件表达式中包含算术表达式的执行效率，where 子句中是否可以用别名.5. 字符类型在where 子句中的用法6. sql 比较运算符between and 等价于大于等于并且小于等于以及not between and7. sql 比较运算符in 等价于=any 或= or =及其否定形式not in 等价于all 或 and8. sql 比较运算符like,通配符%、_的含义以及转义的概念及其否定形式not like9. sql 比较运算符is null 及其否定形式is not null，判断是否为空值

14、一、WHERE子句功能、位置、后面可以跟的合法内容功能:用WHERE子句对表里记录进行过滤 位置:WHERE子句紧跟在FROM子句之后 合法内容:WHERE子句后可以跟条件表达式 列名、表达式、常量 比较运算符 文字值 注意:WHERE子句后不能跟别名 演示列出部门编号为42的员工姓名、部门ID、月薪二、增加where 子句后select 语句的执行过程教学批注通过以下练习题讲解加Where子句执行顺序：练习以下题目得出结论 找出年薪大于12000的员工姓名、年薪 写法1: 写法2：结论:where子句优先于select语句执行。where子句后能不跟表达式的就不要跟表达式。三、 where

15、子句中是否可以用别名.不能跟别名的原因-where优先于select被执行说明：授课的时候可以写一个where中带别名的sql进行验证。四、 字符类型在where 子句中的用法where后的字符值要用单引号括起来。字符类型的大小写是敏感的。练习以下题目得出结论 找出Carmen的员工姓名和年薪：写法1：报错原因：where后的字符值没用单引号括起来写法2:未出结果的原因：题目要求查的是Carmen写法3:正确的写法由此得出结论：（1）where子句中的列为字符类型（2）字符串要使用单引号括起来、列名的大小写是敏感的 五、 WHERE+比较运算符的用法1、算术表达式比较运算符作用：比较条件运算符

16、用于一个表达式和一个值或者另一个表 达式的比较。举例:.WHERE hire_date=01-JAN-95 . WHERE salary=6000 . WHERE last_name=Smith2、BETWEENAND与NOT BETWEEN AND（区间）你可以用BETWEEN范围条件显示基于一个值范围的行。你指定的范围包含一个下限和一个上限。上例中的SELECT语句从EMPLOYEES表中返回薪水在$2,500和$3,500之间的那些雇员。BETWEEN条件包括指定的上下限值，必须先指定下限。3、IN表示或的用法(集合中筛选)演示找出31、41、43部门员工名字和工资使用or的写法（老式）

17、：使用in( )的写法结论：in( ) 比 or 写法方便简单。如果IN条件中的成员是字符或日期，它们必须放在单引号中。例如：WHERE last_name IN (Hartstein, Vargas);4、LIKE运算符(一)功能:在字符串比较中，可用LIKE和通配符进行查找通配符的种类 %表示0或者多个字符 _表示任意个字符 演示当有符号和通配符一样的情况要进行转义操作：案例:找出用户表中以S_开头的表:很容易误写的写法：正确的写法：select table_namefrom user_tableswhere table_name like S_% ESCAPE 5、IS NULL 是空判

18、断一个字符是否为空使用is null来判断。NULL不能用等于和不等于跟任何值比较，包括它自身，所有不能使用=、来测试一列是否为空。6、运算符的否定形式逻辑比较运算符 != =SQL比较运算符 NOT BETWEEN AND表示不在某个区间 NET IN 表示不在某个范围IS NOT NULL 表示不为空 结论：如果结果集中包含null值，使用not in一定不会出结果。 举例：找出不包含31、42、43部门的员工信息7、AND的用法教学批注通过以下题目引入where后面跟and的用法 SQL找出31、32部门工资大于1000的员工信息 8、Where后面跟多个条件比较两条SELECT语句的哪

19、个结果集大？ *第二时间段: WHERE子句的功能ORCLE-F-005ORDER BYORDER BY子句的功能1. order by 子句的基本结构及排序操作2. 增加order by 子句后select 语句的执行过程3. order by 子句可以表达的各种形式：一个或多个字段、表达式、别名4. 排序时如何处理null 值一、用OREDER BY子句对查询出来的结果集进行排序ASC-升序、缺省DESC-降序ORDER BY是SELECT语句后最后一个子句。SQL按员工部门编号从小到大排列输出：学员练习：列出员工姓名、工资，按工资从高到低排序输出二、增加order by子句后select

20、语句的执行顺序。先from表名where过滤条件select查询order by排序验证方式：order by + 别名(OK)三、 Order by后面可以跟什么？列名、表达式、别名、位置。四、按多列进行排序:关键点:多列排序以逗号为分隔符,来区别排序分类上面语句说明：先按dept_id升序，再按salary降序排序五、order by后面的列可以不出现在select后面。今日作业：Oracle_经典案例1-11题。 Oracle第二天【Oracle-F-006】单行函数的使用一、 什么是单行函数、多行函数，以及它们的区别？1、关于dual表:专门用于函数测试和运算的虚表。2、单行函数:对单

21、个行进行计算,并且每一行返回一个结果。教学批注：可以分别将select lower(ABC) from s_emp;语句放在s_emp表和dual表中去执行，看看返回的记录数，利用返回的记录数告知学员，单行函数与行记录是一一对应的关系。3、多行函数:这类函数能操作成组的行，以行组为单位返回一个结果.区别:相同的都是处理数据库中的数据，单行函数的结果是一对一的关系，多行函数的结果是多对一的关系。二、单行函数的使用1、出现的位置:select、where、order by（与记录相关的地方）2、面试题:为什么where后面只能跟单行函数：因为where子句过滤的是记录，而单行函数跟记录是有关系的。

22、3、字符型单行函数分类：学员练习:求每个员工的最后2个字符LPAD 用给定的字符左填充字符串到给定的长度。(要处理的字符串,要返回的长度,用什么替换补起的位置) to_number(数值字符串)，将数值类型的字符串转换成数字。 显示数据类型转换: 演示这条语句并解释报错原因：select to_name(ab) from dual; 隐式类型转换:字符转数字三、where 子句中使用单行函数的效率四、字符型单行函数：concat、substr、length、lpad、rpad五、数值型单行函数：round、trunc六、数值和字符类型的隐式和显式转换转换函数：to_char、to_number

23、【Oracle-F-007】多表查询课程引入带领学生做如下查询，引出笛卡尔积的问题，然后讲解笛卡尔积产生的原因以及如何避免：SQLfirst_name,name rom s_emp , s_dept;一、 笛卡尔积1、笛卡尔积产生的原因：一个连接条件被遗漏时一个连接条件不正确时在第一个表中的所有行被连接到第二个表的所有行时2、如何避免笛卡尔积的产生： 为了避免笛卡尔乘积的形成，在WHERE 子句中应当总是包含正确的连接条件. 三、内连接join on 内连接原理(非常重要) T1表和T2表做内连接，连接条件为 on t1.c1 = t2.c2, 假设T1表做驱动表，T2表做匹配表，记录的匹配过

24、程如下： 1、从T1表中读取。 使用内连接做2张表的查询，仅返回匹配行的记录。 select d.id, d.name, r.name r_namefrom s_dept d join s_region ron d.region = r.region内连接的核心：任何一张表在另一张表中一定要有相等的条件，这也叫精确查找。练习:亚洲地区有哪些员工?Carmen在那个区上班？等值连接两张表有描述共同属性的列。常见形式为父表的主键和子表的外键相等。 非等值连接 1、可以用between and 这样的非等值运算符将2张表中的列写成一个表达式。 2、所谓表之间的关系，实际是指表中的行(记录)之间的关系

25、，该关系通过将表中的列写成表达式来体现。 自连接 同一张表的列之间有关系实际反映的同一张表的行(记录)之间有关系。外连接:1、 什么情况下使用外连接？分为左外连接、右外连接规则：左连显示匹配记录不匹配用null代替 右连结果集为匹配左边的记录，不匹配的用null代替2、外连接原理？ t1表和t2表做外连接，连接条件为：from t1 left join t2 on t1.c1=t2.c2 t1要必须做驱动表、t2做匹配表(1)外连接结果集=内连接的结果集+t1表中匹配不上的记录和一条null记录的组合。(2)外连接的核心可以将匹配不上的记录找回来，即一个都不能少。3、如何判断谁是驱动表？ le

26、ft的左边是驱动表 right的右边是驱动表 要将数据全部4、外连接往往与is null一起用？外连接+is null用来解决否定问题。练习:哪些人是员工？select m.first_namefrom s_emp e RIGHT join s_emp mon e.manager_id = m.idwhere e.id is null练习:哪个部门没有Smith这个员工。select d.dnamefrom emp e right join dept don e.deptno = d.deptnoand e.ename=Smithwhere e.empno is null结论：and在外连接之

27、前、where在外连接之后执行。 Oracle第三天【Oracle-F-008】组函数一、 什么是组函数？二、组函数的分类三、使用组函数规则1、DISTINCT使得函数只考虑不重复的值，ALL使的函数考虑每一个 值，包含NULL值，组函数默认是ALL。、用于函数的参数类型可以是.【Oracle-F-009】group by 及having 子句的使用一、 group by子句的基本结构,功能介绍。select max(d.name),max(r.name),avg(e.salary)from s_emp e join s_dept don e.dept_id = d.idjoin s_regi

28、on ron d.region_id = r.idgroup by e.dept_id【SQL】求亚洲地区各部门平均工资【SQL】求各部门不同职位的平均工资select dept_id, title,avg(salary)from s_empgroup by dept_id,title二、有having子句的Select语句的执行过程1.行被分组2.将having子句的条件应用到每个分组上3.只有符合having条件的组被保留，再应用select后面的组函数对每组的数据可进行处理。思考1：having子句后面是否可以跟别名？答:不能，因为having在select之前被执行。思考2：havin

29、g子句后面可以跟那些条件表达式答：组函数、需要group的字段【SQL】求各部门平均工资大于1500 select dept_id, title,avg(salary)from s_empgroup by dept_idhaving avg(salary)1500【SQL】求除32、42部门的外所有部门的平均工资 写法1：select dept_id,avg(salary)from s_emp where dept_id not in(32,42)group by dept_id写法2：select dept_id,avg(salary)from s_emp group by dept_idhaving dept_id not in(32,42)总结：写法2优于写法1三、where子句和having子