ORACEL学习笔记

1.    SQL语言的分类

1.1    数据定义语言(DDL)

数据定义语言DDL( Data Definition Language ) , 是SQL语言集中负责数据结构定义与数据库对象（主要是表）定义的语言 , 主要有create、alter、drop和truncate四种常用语句。
DDL对数据结构起作用。
l  create 数据库对象的创建
l  alter 修改数据库对象
l  drop 删除数据库对象
l  truncate 清空表数据
1.1.1    建数据表（create）

1.1.1.1    数据类型（dataType）

l  数字
 number(n) 数字( 最长n位 )
 number(n , m) 浮点数( 总长n为 , 小数点后m位 )
例：number(7,2) 表示最大数为99999.99。
l  字符串
Ø   char(n) 表示定长字符串( 方便查询 )
最长放入n个字符 , 放入的数据如果不够n个字符则补空格 , 无论如何都占n个字符长度。
Ø   varchar2(n) 表示变长字符串( 节省空间 )
最长放入n个字符 , 放入的数据是几个长度就占多大空间
l  日期
 date 日期
1.1.1.2    约束条件 constraint

l  主键( Primary key, 简称PK )
1) 主键约束( primary key ) = 不能重复 + 不能为null
2) 主键约束可以用两种方式定义：列级约束和表级约束。
l  非空约束( not null , 简称NN )
注意：非空约束只能定义在列级
l  唯一约束( Unique , 简称UK )
可以用两种方式定义：列级约束和表级约束。
l  检查约束( Check , 简称 CK )
l  外键( Foreign key, 简称FK )
1) 之前讲的几个约束条件都是用来约束单个表中的列 , 而外键约束定义在两个表的两个字段上( 或者一个表的两个字段上 ) , 用于保证相关两个字段的关系。
2) 外键可以为空。
建表语句如下
1.1.2    更改表结构（alter）

// emp表增加一个email字段
alter table emp add  email  varchar2(20);
// 更改emp表中email的数据类型
alter table emp modify  email varchar2(30);
// 增加emp表中email的唯一约束
alter table emp add constraint email_uk UNIQUE(email);
// 使emp表中email的唯一约束失效
alter table emp disable email_uk;
// 使emp表中email的唯一约束有效
alter table emp enable email_uk;
// 删除emp表email列的约束
alter table emp drop constraint email_uk;
//  删除emp表中email列
alter table emp drop column email;
// 改变emp表中gender为sex
alter table emp rename column gender to sex;
//查看emp表结构
desc emp;
1.1.3    删除表对象（drop）

drop table table_name purge;  eg: drop table emp purge;
1.1.4    清空表数据(truncate)

Truncate table table_name;    eg: truncate table emp;
1.2    数据操纵语言

数据操纵语言DML( Data Manipulation Language ) , 用户通过它可以实现对数据表的基本操作 , 即对表中数据的增、删、改。 DML对数据起作用。
l  insert 插入操作
l  update 更新操作
l  delete 删除操作
1.2.1    插入数据（insert）

例如：向部门（dept）表插入数据：
insert into dept values(10 , 'developer' , 'beijing') ;
insert into dept values(20 , 'account' , 'shanghai') ;
insert into dept values(30 , 'sales' , 'guangzhou') ;
insert into dept values(40 , 'operations' , 'tianjin') ;
向员工表插入数据（emp）
insert into emp_xxx values( 1001 , '张无忌' , 'Manager' , 10000 , 2000 , '12-MAR-10' , 1005 , 10) ;
insert into emp_xxx values( 1002 , '刘苍松' , 'Analyst' , 8000 , 1000 , '01-APR-11' , 1001, 10) ;
insert into emp_xxx values( 1003 , '李翊' , 'Analyst' , 9000 , 1000 , '11-APR-10' , 1001, 10) ;
insert into emp_xxx values( 1004 , '郭芙蓉' , 'Programmer' , 5000 , null , '01-JAN-11' , 1001 , 10) ;
insert into emp_xxx values( 1005 , '张三丰' , 'President' , 15000 , null , '15-MAY-08' , null , 20) ;
insert into emp_xxx values( 1006 , '燕小六' , 'Manager' , 5000 , 400 , '01-FEB-09' , 1005 , 20) ;
insert into emp_xxx values( 1007 , '陆无双' , 'clerk' , 3000 , 500 , '01-FEB-09' , 1006 , 20) ;
insert into emp_xxx values( 1008 , '黄蓉' , 'Manager' , 5000 , 500 , '1-MAY-09' , 1005 , 30) ;
insert into emp_xxx values( 1009 , '韦小宝' , 'salesman' , 4000 , null , '20-FEB-09' , 1008 , 30) ;
insert into emp_xxx values( 1010 , '郭靖' , 'salesman' , 4500 , 500 , '10-MAY-09' , 1008 , 30) ;
1.2.2    更新数据（update）

语法结构： update 表名 set 列名 = 新的列值 ,
列名 = 新的列值. ….
where 条件;
注意：
注意：更新( update )数据表时 , 注意条件 , 如果不加条件 , 修改的是全部表记录。
【案例】将员工号为1012的员工薪水改为3500 , 职位改为Programmer
update emp_xxx set salary = 3500 , job = 'Programmer'
where empno = 1012 ;
1.2.3    删除数据（delete）

语法结构： delete [from] 表名 where 条件 ;
注意：
l  如果删除语句中不加where条件 , 将删掉表中的全部记录。
l  rollback回退 , commit确认。
l  drop table会删除表结构和数据 ;truncate删除表数据 , 保留表结构。Drop和truncate都不可以      回退。 delete仅删除数据 , 可以回退。
【案例】删除员工号为1012的员工。
delete from emp where empno = 1002 ;
1.3    数据查询语言

数据查询语言DQL( Data Query Language ) , 用户主要通过它实现对数据的查询操作。
 select 查询操作
1.3.1    列的别名

【案例】计算员工的名字、月薪和年薪？
select ename , salary , salary * 12 year_sal from emp ;
其中 year_sal是salary*12的别名
1.3.2    Null

1.3.2.1    Oracle中空值的概念：null

l  任何数据类型都可以取值null
l  空值和任何数据做算数运算, 结果都是null。
l  空值和字符串类型做连接操作 , 结果相当于空值不存在。
l  任何值与null比较都等于false。
1.3.2.2    处理空值的函数nvl

【案例】计算员工的月收入
select ename, salary , bonus , salary + nvl(bonus, 0) month_sal from emp ;
l   nvl(d1 , d2)方法演示：如果d1为null则用d2替代,否则返回d1
l   nvl函数的两个参数可以是数字、字符或日期 , 但两个参数的数据类型必须一致。
²  【案例1】插入一条ID为1011 , 姓名为 '张长海' , 其余字段为null的数据
           insert into emp( empno , ename ) values( 1011 , '张长海' ) ;
           查询emp表 , 如果没有职位 , 显示'no position' , 如果有职位 , 显示员工的职位。
           select ename , nvl(job, 'no position' ) from emp ;
²  【案例2】查询emp表 , 如果没有入职时间 , 显示为2011年10月10日 ,
否则原样显示。
select ename , nvl( hiredate , '10-OCT-11' ) from emp ;
1.3.3    复制表

【案例】复制表emp为emp_zch
create table emp_zch as select * from emp ;
1.3.4    distinct关键字

注意：distinct必须( 只能 )跟在select后边
【案例】员工中有多少种职位？
select distinct job from emp;
【扩展练习】查询每个部门不重复的职位
select distinct deptno, job from emp ;   --distinct指所有列的唯一组合
1.3.5    条件查询（where）

Where字句后不能跟列别名。
【案例】薪水高于10000元的员工数据？
select * from emp where salary > 10000 ;
1.3.6    大小写问题

SQL语句大小写不敏感 , 数据大小写敏感
1.3.7    between …and… 关键字

1) 在区间中：between 低值 and 高值
2) 闭区间：[低值 , 高值]
【案例】薪水大于5000并且小于10000的员工数据？
select * from emp where salary between 5000 and 10000 ;
1.3.8    in( 列表 )

in 列表有null不影响 = any，not in列表有null有影响 ,等于<>all。
【案例】列出职位是Manager或者Analyst的员工
select * from emp_xxx where job in ('Manager' , 'Analyst') ;
等于select * from emp_xxx where job = 'Manager' or job = 'Analyst' ;
1.3.9    模糊匹配 like %

1) “% ”表示0到多个字符 , 跟like配合使用
2) “_”下划线表示一个字符
【案例1】列出职位中第二个字符是a的员工数据？
select * from emp where job like '_a%' ;
【案例2】查询数据库中有多少个名字中以 'S_' 开头的表？
select count(*) from user_tables where table_name like 'S\_%' escape '\' ;
如果要查询的数据中有特殊字符( 比如_或% ), 在做模糊查询时 , 需要加上\符号表示转义 , 并且用escape短语指明转义字符\。
1.3.10    各数据类型函数（单行函数）

1.3.10.1    数值函数

²  Round函数   四舍五入行数
²  Trunc函数    截取函数
1.3.10.2    日期函数

1.3.10.3    字符函数

1.3.11    转换函数（单行函数）

1.3.11.1    to_char(date, ‘fmt’)

把日期数据以指定的格式转换为字符数据。
【案例】把时间数据按指定格式输出
select to_char( sysdate , 'yyyy-mm-dd hh24：mi：ss ' ) from dual ;
1.3.11.2    to_char(number, ‘fmt’)

把数字以指定的格式转换为字符数据。
1.3.11.3    to_date

将字符串数据 按指定格式 转换为日期数据
【案例】按指定时间格式插入数据
1.3.12    分支函数（case和decode）

1.3.12.1    case语句

case语句是数据中的分支语句 , 相当于Java中的switch-case语句。
【案例】根据员工的职位 , 计算加薪后的薪水数据
要求：
1) 如果职位是Analyst：加薪10%
2) 如果职位是Programmer：加薪5%
3) 如果职位是clerk：加薪2%
4) 其他职位：薪水不变
1.3.12.2    decode

decode()函数是Oracle中等价于case when语句的函数 , 作用同case语句相同。
decode函数语法如下：
decode(判断条件 , 匹配1 , 值1 , 匹配2 , 值2 , … , 默认值)
表达的意思是：如果判断条件 = 匹配1 , 则迒回值1 判断条件 = 匹配2 , 则迒回值2
1.3.13    组函数

组函数 count () avg() sum() max() min()。
与单行函数如round()、to_date()、to_char()、coalesce()等不同 , 单行函数是每行数据返回一行结果 , 组函数是多行数据返回一行结果。
当组函数要处理的所有值为null，count函数返回值为0，其他函数返回null。
1.3.14    查询结果排序 order by

排序语句放在查询语句的最后 ;
排序可以用列名 , 列别名 , 表达式 , 函数 , 甚至可以用数字, 表示按第几列排序
【案例1】按入职时间排序 , 入职时间越早排在前面。
select ename, hiredate from emp order by hiredate ;
【案例22】按部门排序 , 同一部门按薪水由高到低排序
select ename , deptno , salary from emp order by deptno , salary desc；
1.3.15    分组查询 group by

group by 列名：表示按指定列分组查询。
若没有group by字句，select后边是组函数，其他都必须是组函数。
若有group by字句，select后边可以跟group by后面跟的表达式以及组函数，其他会报错。
反过来说，select后出现的列 , 凡是没有被组函数包围的列 , 必须出现在group by 短语中
【案例】计算每个部门的 薪水总和 和 平均薪水？
select deptno , sum(salary) sum_s , avg(nvl(salary,0)) avg_s
from emp
group by deptno ;
1.3.16    having子句

having子句用于对分组后的数据进行过滤。
注意区别where是对表中数据的过滤 ;having是对分组得到的结果数据进一步过滤
【案例】平均薪水大于5000元的部门数据 , 没有部门的不算在内？
select deptno , avg(nvl(salary , 0)) avg_s from emp
where deptno is not null
group by deptno
having avg(nvl(salary , 0)) > 5000 ;
1.3.17    非关联子查询

子查询就是在一条sql语句中嵌入select语句。
子查询自动去重。
比较运算符的选择：若子查询的返回结果仅为一个值，可以用单值运算符如 =
                  若子查询的返回结果可能为多值，必须用多值运算符如 in
【案例】最低薪水的是谁？
select ename from emp_xxx
where salary = ( select min(salary) from emp ) ;
² 子查询出现在having短语中
【案例】哪个部门的人数比部门30的人数多？
select deptno , count(*) from emp_xxx
group by deptno
having count(*) >
( select count(*) from emp_xxx where deptno = 30 ) ;
1.3.18    All *

【案例】查询谁的薪水比所有叫张无忌的薪水都高？ --大于最大值
select ename from emp_xxx where salary >
ALL( select salary from emp_xxx where ename = '张无忌' ) ;
1.3.19    Any *

【案例】哪些人的薪水比任何一个叫张无忌的员工工资高？ --大于最小值
select ename from emp_xxx where salary >
ANY( select salary from emp_xxx where ename = '张无忌' ) ;
--只要大于叫“张无忌”的人的薪水最小值就查出来
1.3.20    关联子查询

子查询中不再是独立的Sql语句 , 需要依赖主查询传来的参数 , 这种方式叫关联子查询。
【案例】哪些员工的薪水比本部门的平均薪水低？不再和整个部门的平均薪水比较。
select ename, salary, deptno
from emp a
where salary <
( select avg(nvl(salary,0)) from emp where deptno = a.deptno ) ;
1.3.21    Exists关键字

exists关键字判断子查询有没有数据返回 , 有则为ture , 没有则为false
Exists不关心子查询的结果 , 所以子查询中select后面写什么都可以 本例中我们写常量“1”
【案例】哪些人是其他人的经理？( 查找有下属的员工 )
select ename from emp a
where exists (select 1 from emp where mgr = a.empno) ;
【案例】哪些部门没有员工？
select deptno, dname from dept d
where not exists (select 1 from emp where deptno = d.deptno) ;
1.3.22    集合操作

数据库中的查询语句的结果集( ResultSet )：集合A和集合B
 集合A： {1,2,3,4,5}
 集合B： {1,3,5,7,9}
 A不B的合集： {1,2,3,4,5,7,9}
 A不B的交集： {1,3,5}
 A不B的差集： A-B {2,4}
1.3.22.1    结果集操作

1) 两个结果集必须结构相同
 当列的个数、列的顺序、列的数据类型一致时 , 我们称这两个结果集结构相同
 只有结构相同的结果集才能做集合操作
2) 合集 union 和 union all
 union 和 union all的区别
 union去掉重复记录 , union all不去重
 union排序 , union all不排序
 在满足功能的前提下 , 优选union all
3) 交集 intersect
4) 差集 minus( 两个集合做减法 )
1.3.22.1.1    合集

1) union 去重 , 排序
【案例】合集( union )演示
select ename , salary from emp
where deptno = 10
union
select ename , salary from emp_xxx
where salary > 6000 ;
3) union all 不去重 , 不排序
select ename , salary from emp_xxx
where deptno = 10
union all
select ename , salary from emp_xxx
where salary > 6000 ;
1.3.23    表间关联查询

l  表emp和表dept之间存在的参照关系。
1) emp的所在部门( deptno )参照dept的部门编码( deptno )
2) dept是主表( 父表 ) , emp是从表( 子表 )
l  表emp自身存在一种参照关系
员工的经理( mgr )列参照职员编码( empno )列。
1.3.23.1    内连接

内连接的核心为任何一张表里的记录一定要在另一张表中找到匹配的记录，否则不能出现在结果集中。
l  语法：
l  执行过程
【案例】列出员工的姓名和所在部门的名字和城市
select ename , dname , location from emp e
join dept d
on e.deptno = d.deptno ;
²  子表( emp )中的外键值( deptno )为null的数据不包含在结果集中。
1.3.23.2    自连接（内连接的一种）

【案例】列出员工的姓名和他的上司的姓名
select t1.ename , t2.ename from emp t1
join emp t2
on t1.mgr = t2.empno ;
1.3.23.3    外连接

1) 左外连接语法结构： 表1 left outer join 表2 on 条件
2) 右外连接语法结构： 表1 right outer join 表2 on 条件
3) 外连接的特征：
 如果驱动表在匹配表中找不到匹配记录 , 则匹配一行空行
 外连接的结果集 = 内连接的结果集 + 驱动表在匹配表中匹配不上的记录和空值
 外连接的本质是驱动表中的数据一个都不能少
 left outer join以左边的表为驱动表
 right outer join以右边的表为驱动表
l  左外连接 left outer join
l  右外连接 right outer join
【案例1】列出员工的姓名和他所在部门的名字 , 把没有部门的员工也查出来。
方法1：左连接
select e.empno , ename , d.deptno , d.dname , d.location from emp e
left outer join dept d
on e.deptno = d.deptno ;
方法2：右连接
select e.empno , ename , d.deptno , d.dname , d.location from dept d
right outer join emp e
on e.deptno = d.deptno ;
【案例2】列出员工的姓名和他所在部门的名字 , 把没有员工的部门也查出来
select e.empno , e.ename , d.deptno , d.dname , d.location
from dept d left outer join emp e
on e.deptno = d.deptno;
1.4    事务控制语句

1) 事务是一组DML操作的逻辑单元 , 用来保证数据的一致性。
2) 在一个事务内 , 组成事务的这组DML操作 , 或者一起成功提交 , 或者一起被撤销。
3) 事务控制语言TCL( Transaction Control Language )
 commit 事务提交 将所有的数据改动提交
 rollback 事务回滚 回退到事务之初 , 数据的状态和事务开始之前完全一致
 savepoint 事务保存点( 较不常用 )
1.4.1    事务中的数据状态

1.4.2    如果多个会话操作同一张表的数据

当用户不服务器建立连接成功后 , 服务器端Oracle将与客户端建立一个会话( Session )。 客户端不Oracle的交互都是在这个会话环境中进行的。
【案例】Transaction演示
l   步骤1
开启一个会话A , 创建表并插入1条数据( 注意：不提交 )
( 注意练习时使用同一个用户在两个窗口中登录 , 比如jsd1302 )
l  步骤二
开启第2个会话B , 在会话A进行commit之前 , 会话B只能查看表结构 , 查看不到数据。
l  步骤三
会话A中进行commit操作后 ;会话B中就可以查看数据了。
l  步骤四
会话A进行update操作( 没有commit ) , 会话B看到的仍然是原先的数据。
l  步骤5
会话A提交( commit )后 , 会话B看到被改变的结果
l  步骤6
会话A进行update操作( 没有commit ) , 会话B进行delete操作时被挂起 , 因为试图操作相同的数据。
l  步骤7
会话A提交( commit ) , 会话B结束阻塞状态 , 开始执行。
l  步骤8
会话A更新后进行回滚操作( rollback )。
结论：
1) 事务内部的数据改变只有在自己的会话中能够看到
2) 事务会对操作的数据加锁 , 不允许其它事务操作
3) 如果提交( commit )后 , 数据的改变被确认 , 则
 所有的会话都能看到被改变的结果 ;
 数据上的锁被释放 ;
4) 如果回滚( rollback ) , 则
 数据的改变被取消 ;
 数据上的锁被释放 ;
 临时空间被释放。
1.4.3    Savepoint

设置保存点 , 可以回滚( rollback )到指定的保存点。
【案例】savepoint演示