oracle学习笔记

oracle基本运算符

• between ....and .... 运算符，取一个区间的值，仅限一个条件中
• oracle基本语法

select  [distinct]{*|column|expression [alisa],...}
from table

select 子句表示所检索的数据列
from子句表示检索的数据来自哪个表
不区分大小写，建议小写，建议分多行书写，可以增强代码的可读性；通常以子句为单位进行分行；
关键字不可缩写、分开以及跨行书写；
选择所有列

select *
from emp;

选择指定的列

select deptno,sal
from emp;

算数运算符主要包含：
（+ - * /）
算数运算符的运用

select enanme,sal,sal*12
from emp

算数优先级

• 乘除优先于加减
• 相同优先权的表达式按照从左至右 的顺序依次计算
• 括弧可以提高优先权，并使表达式的描述更为清晰
  空值NULL
• 空值null指一种无效的、未赋值的或不可用的值
• 空值不同于零或者空格
  连接操作符
• 用于连接列于列、列与字符
• 形式上是以两个竖杠||
• 用于创建字符表达式的结果列

select ename||job as "employess"
from emp

原义字符串

• 原意字符串是包含在select列表中的一个字符、一个数字或者一个日期
• 日期和字符面值必须用单引号引起来
• 每个原义字符串都会在每个数据行输出中出现

select ename ||'is a'|| job
from emp

上面其中的is a就为原义字符串 、
消除重复行

• 在select子句中使用关键字distinct可以消除重复行

select distinct depton
from emp

“*”代表所有的

where运算语句的运算符

• between...and..运算符 取x与y之间的值，仅限一个条件中用
• in 运算符 判断比较的值是否和集合列表中的任何一个值相等
• like 运算符 模糊查找“%”代表一个或者多个，无穷的"_"只代表一个，不常用
• is null 判断要比较的值是否为空值
• and 两个条件都为真 结果才为真
• or 只需一个条件为真，结果就返回真
• not 否定词 代表反义 通常用在“in”“like”“between..and..”这些运算符的前边，在“is null”这个运算符的中间，
• order by 释义为排序运算符 在字符串或者数字后边加asc为升序 这个一般为默认值，加desc为降序
• 空值null 升序在最后 降序在最前
  包含以上所有运算符

select * 
from emp
where 
sal in(1500,3000) and ename like '%M_' and comm is not null 
ORDER BY sal,deptno desc 

分组函数

别名：聚集函数

• 分组函数代码
  min最小值，列个可以是数字、日期或字符串；
  max最大值，列个可以是数字、日期或字符串；
  sum求和，列只可以是数字;
  avg平均值, 列只可以是数字;
  count表中所有记录数，满足非空（null）行的个数
  distinct消除重复记录
  nvl（参数a，参数b）a一般为列名，b为任意值；如果第一个参数列名的值是null，这个函数的返回值就是b，反之则为原值
• 练习

1. 查询部门20的员工，每个月的工资总和及平均工资。
2. 查询工作在CHICAGO的员工人数，最高工资及最低工资。
3. 查询员工表中一共有几种岗位类型。

SELECT
    SUM (sal),
    AVG (sal)
FROM
    EMP
WHERE
    DEPTNO = 20

SELECT
    COUNT (*),
    MAX (sal),
    MIN (sal)
FROM
    emp A
JOIN dept b ON A .deptno = b.deptno
WHERE
    b.loc = 'CHICAGO'

SELECT
    COUNT (DISTINCT(JOB))
FROM
    EMP

• group by 分组语句
  select 语句中出了分组函数那些项其他列的语句只能写group by 后边接的列明，或者用函数调用；
  oracle-sql多表连接

---

多表连接

• 需要多考虑一个条件 三张表 两两有相同的一列

等值连接

• 两张表里有相同列，为条件连接
• 以下为等值连接代码

select *
from emp,dept
where emp.deptno =dept.deptno

非等值连接

• 以下为非等值连接的代码

select a.ename,a.sal,b.GRADE 
from emp a,salgrade b
where a.sal between b.losal and b.hisal

内部连接

• （典型的联接运算，使用像 = 或 <> 之类的比较运算符）。包括相等联接和自然联接。
  内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如，检索 emp和dept表中员工部门编号号相同的所有行

外部连接

• 外联接可以是左向外联接、右向外联接或完整外部联接。
  在 FROM子句中指定外联接时，可以由下列几组关键字中的一组指定：

1）LEFT JOIN或LEFT OUTER JOIN
左向外联接的结果集包括 LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。

2）RIGHT JOIN 或 RIGHT OUTER JOIN
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。
3）FULL JOIN 或 FULL OUTER JOIN
完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

笛卡尔积

• 第一个表中的所有行和第二个所有行中都发生
• 一定不要发生此现象
• 以下为笛卡尔积代码

select *
from emp,dept

交叉联接

• 交叉连接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。
  FROM 子句中的表或视图可通过内联接或完整外部联接按任意顺序指定；但是，用左或右向外联接指定表或视图时，表或视图的顺序很重要。有关使用左或右向外联接排列表的更多信息，请参见使用外联接。
  例子：
  a表

id	name
1	张3
2	李四
3	王五

b表

id	job	parent_id
1	23	1
2	34	2
3	34	3

1） 内连接

select a.*,b.*   
from a inner join b on a.id=b.parent_id 

2）左连接

   select a.*,b.*   
  from a left join b on a.id=b.parent_id    

3） 右连接

select a.*,b.*   
from a right join b on a.id=b.parent_id     

4） 完全连接

  select a.*,b.*   
  from a full join b on a.id=b.parent_id   

• 自身连接
  一张表格中通过有关系的条件进行连接的一种方式
  把同一张表看成两张表来思考

查出员工的姓名及其编号和他直接上级的姓名及员工编号

select e2.ename,e2.empno.e1.ename,e1.empno  
from emp e1
join emp e2 on e1.empno = e2.mgr

a,b两个表那个是员工那个是经理，需要看连接条件

• SQL-1999语法的连接
  join
  on子句
  只起到连接多张表的作用；
  可以提高代码的可读性；

子查询和高级子查询

子查询

• 子查询可以嵌于where、having、from子句中；
• 子查询的类型
  单行子查询
  多行子查询
  多列子查询
• 单行运算符
  =、>、<、>=、<=、<>
• 多行运算符
  in、any、all

子查询结果中有一条是空值，这条空值会导致查询没有记录返回；这是因为所有条件和空值比较的结果都是空值，因此无论什么时候只要空值有可以能成为子查询结果集合中的一部分，就不能使用not in运算符

• rownum
  rownum是一个伪列，伪列是没有存储在表中的特殊列，他可以输出为顺序号，但是没有实际意义，不能作为关系列，但可以为这个表增添查询条件。
  对于rownum只能执行小于、小于等于运算，不能执行大于、大于等于或一个区间运算between 、、、and等
  rownum和order by一起使用时，因为rownum在记录输出时生成，而order by子句在最后只执行，所以当两者一起使用时，需要注意rownum实际是已经被排了序的rownum；
• top - n 查询
  asc查询最小的n条记录
  desc查询最大的n条记录
  列> 查询公司员工工资最低的3个人

select rownum,sal
from (select sal from emp 
        order by  sal   asc)
        where rownum <= 3

分页
按照每页显示五条记录，分别查询第一页，第二页，第三页信息，要求显示员工姓名，入职日期、部门名称。

第一页
SELECT
    A .ename,
    A .hiredate,
    b.dname
FROM
    (
        SELECT
            ROWNUM,
            EMP.*
        FROM
            emp
        WHERE
            ROWNUM < 5
    ) A
JOIN dept b ON A .DEPTNO = b.deptno
第二页
SELECT
    A .ename,
    A .hiredate,
    b.dname
FROM
    (
        SELECT
            ROWNUM rb,
            EMP.*
        FROM
            emp
        WHERE
            ROWNUM <= 10
    ) A
JOIN dept b ON A .DEPTNO = b.deptno
WHERE
    A .rb > 5
    第三页
SELECT
    A .ename,
    A .hiredate,
    b.dname
FROM
    (
        SELECT
            ROWNUM rb,
            EMP.*
        FROM
            emp
        WHERE
            ROWNUM <= 15
    ) A
JOIN dept b ON A .DEPTNO = b.deptno
WHERE
    A .rb > 10

集合运算

是用来把两个或多个查询结果集做并、交、差的集合运算，包含集合运算的查询称为复合查询，
联合 union完全联合union all相交intersect相减minus

• 联合运算
• 完全联合运算
• 相交运算
• 相减运算
  返回有任意查询结果集包含的行，并且去除重复行，并且按照查询结果集的第一序列升序排序。
  使用原则
  多个被联合的查询语句所选择的列数和列的数据类型必须一致，列的名字不必相同。
  如果多个查询结果都有null值，整个结果中只包含一个null值
  每个查询不能包含自己的order by子句，只能在联合之后使用。
  列>查询编号为7839的员工当前工资、岗位及历史工资、岗位？

select sal,job
from emp
where empno = 7839
union
select sal,job
from emp_jobhistory
where empno = 7839

使用union all会比union的速度快，因为省去了去掉重复记录和排序的时间

相关子查询

列>查询比本部门平均薪水高的员工姓名，薪水

嵌套子查询的写法
select empno,ename,sal
from emp e
join (select deptno,avg(sal) avgsal
from emp
group by deptno) d  on e.deptno = d.deptno
where  e.sal > d.avgsal

相关子查询的写法
select empno,ename,sal,deptno
from emp a
where sal > 
(select avg(sal)
from emp
where deptno = a.deptno)

列>查询所有部门名称和人数

select dname,
(select count(empno)
from emp
where deptno = d.deptno)
from dept d

• exists****not exists
• exists判断是否“存在”，
• 子查询中如果有记录找到，子查询语句不会继续执行，返回值为true
• 子查询中如果到表的末尾也没有记录找到，返回值为false
• 子查询并没有确切的记录返回，只是判断记录是是否存在，而且只要找到相关的记录，子查询就不需要在执行，然后在进行下面的操作，这样大大提高了语句的执行效率。
• not exists正好相反，判断子查询是否没有返回值。如果没有返回值，表达式为真，如果找到一条返回值，则为假。
  列>查询那些人是经理

select ename,job,sal,deptno
from emp e
where exists 
(select  1
from emp
where mgr = e.empno)

列>查询那些人不是经理

select ename,job,sal,deptno
from emp e
where not exists 
(select  1
from emp
where mgr = e.empno)

子查询中select 后边可以是任何值，没有确切的返回记录，因为exists只看存不存在返回记录，
数据库操作与事务控制

---

dml数据库操作语言

• 增加行数据：使用insert
• 修改行数据：使用update
• 删除行数据：使用delete
• 合并行数据：使用merge
  创建biaoming这个表，借用emp里的所有列

create table 'biaoming' as
select * from emp
where 1=0

向biaoming表中插入职位为MANAGER的记录

insert into biaoming
select *
from emp
where job = 'MANAGER'

插入多行数据
不必书写values子句
INSERT子句和数据类型必须和子查询中列的数量和类型相匹配中列的数量

• 修改数据
  修改数据使用UPDATE子句完成，语法结构如下：
  把员工编号为7782的部门编号修改为20

UPDATE  emp
SET deptno = 20
WHERE   empno = 7782

WHERE子句用来限定修改哪些行。
SET子句用来限定修改哪些列。
修改数据中可以应用子查询、相关子查询

• 删除数据
  语法结构
  使用 DELETE 语句删除表中满足条件的行记录
  删除职位是CLERK的员工记录

DELETE FROM emp
    WHERE   job = 'CLERK'; 

使用删除数据时一定要看清条件，避免错误删除而发生无法挽回的可怕事件；
删除数据也可以使用子查询、相关子查询；

事务(Transaction)概念

• 事务
  也称工作单元，是由一个或多个SQL语句所组成的操作序列，这些SQL语句作为一个完整的工作单元，要么全部执行成功，要么全部执行失败。在数据库中，通过事务来保证数据的一致性。
• 事务处理语言
  Transaction Process Language ,简称TPL，主要用来对组成事务的DML语句的操作结果进行确认或取消。确认也就是使DML操作生效，使用提交(COMMIT)命令实现；取消也就是使DML操作失效，使用回滚(ROLLBACK)命令实现。
  通过事务的使用，能防止数据库中出现数据不一致现象。如两个银行账户进行转账，涉及到两条更新操作，这两条更新操作只允许全部成功或失败，否则数据会出现不一致的现象。
• 事务组成
  在数据库中，事务由一组相关的DML或SELECT语句，加上一个TPL语句（COMMIT、ROLLBACK）或一个DDL语句(CREATE、ALTER、DROP、TRUNCATE等)或一个DCL（GRANT、REVOKE）
• 事物特性
  • 原子性
    事务就像“原子”一样，不可被分割，组成事务的DML操作语句要么全成功，要么全失败，不可能出现部分成功部分失败的情况
  • 一致性
    一旦事务完成，不管是成功的，还是失败的，整个系统处于数据一致的状态。
  • 隔离性
    一个事务的执行不会被另一个事务所干扰。比如两个人同时从一个账户从取钱，通过事务的隔离性确保账户余额的正确性。
  • 持久性
    也称为永久性，指事务一旦提交，对数据的改变就是永久的，不可以再被回滚
• 事物结束
• 显示结束
  提交(COMMIT)：使用COMMIT命令实现，以成功的方式结束事务，组成事务的DML语句操作全部生效。
  回滚(ROLLBACK)：使用ROLLBACK命令实现，以失败的方式结束事务，组成事务的DML语句操作全部被取消。
  隐式结束
  隐式提交：当下列任意一种情况发生时，会发生隐式提交
  执行一个DDL(CREATE、ALTER、DROP、TRUNCATE、RENAME）语句；
  执行一个DCL(GRANT、REVOKE)语句；
  从SQLPlus正常退出（即使用EXIT或QUIT命令退出）；
  隐式回滚：当下列任意一种情况发生时，会发生隐式回滚
  从SQLPlus中强行退出
  客户端连接到服务器端异常中断
  系统崩溃
• 设置保存点SAVEPOINT
  如果一个事物内，设置了保存点,执行回滚命令直接回滚到保存点而不是起点；

数据定义语言

• 数据定义语言
  (Data Definition Language, DDL) 是SQL语言集中负责数据结构定义与数据库对象定义的语言，由CREATE、ALTER与DROP三个语法所组成。
• 创建表

CREATE TABLE DOSSIER(创建表的表名) (
    ID NUMBER(4), 
   CNAME VARCHAR2(20 ), 
    BIRTHDAY DATE,               
    STATURE    NUMBER(3), 
    WEIGHT NUMBER(5, 2), 
    COUNTRY_CODE CHAR(2 ) DEFAULT ‘01’);（表里面列的名称） 

• 表的数据类型
  字符型()char()xx)、varchar2(xx))、数值型(number(x,y))、日期型(date)(timestamp，此为9i之后新增的可以精确到毫秒)
  字符型，char为固定长度字符型数据，varchar2是可变长度字符数据
  数值型后边不加修饰的，可以是小数，整数，范围在-10的125次方到10的126次方。
  （x）表示整型（x，y）总长度为x，小数位最大为y，整数范围最大为x-y位x的范围从1到38，y的范围从-84到127
• 修改表
  语法

ALTER TABLE table  
ADD (columnname datatype[DEFAULT expr]
          [, columnname datatype]...);