Oracle学习笔记

1.创建用户和表空间

1. 以超级管理员的身份登录。
2. 创建表空间。
3. 创建用户。
4. 给用户授权。
5. 使用新用户进行查询测试。

sqlplus;
sys as sysdba;	//输入用户名，以sysdba的身份登录
sys				//输入密码
create tablespace 表空间名 datafile '文件路径\文件名.dbf' size 空间大小;
create user 用户名 identified by 密码 default tablespace 表空间;
grant dba to 用户;	//给用户赋予权限
select * from dual;	//使用新用户进行查询测试
1
2
3
4
5
6
7

2. 表约束

 - 主键约束（PRIMARY KEY）
 - 唯一性约束（UNIQUE）//可以为空，且可以有多个空，因为空和空不相等
 - 非空约束（NOT NULL）
 - 外键约束（FOREIGN KEY）//外键在的表是从表，必须在主表中存在才可以使用
 - 检查约束（CHECK）
1
2
3
4
5

3. 排序

降序desc 升序asc
单字段排序：
	select * from 表 order by 列 排序方式;
多字段排序：
	select * from 表 order by 列1 排序方式, 列2 排序方式;
1
2
3
4
5

4.伪列。

说明：查询不存在的列即伪列，当需要的结果不能直接从表中得到 需要经过计算来展示则可以使用伪列 + 表达式实现。

select ename, 1 from emp;
1

查询姓名、月薪、年薪
select ename, sal, sal * 12 as year from emp order by sal desc;
1
2

1. Null处理。

1. 查询员工月收入：工资 + 提成。
2. 查询员工信息，并按升序排序。
select sal, comm, sal + comm as 月收入 from emp;
此时，当提成为空时，工资无法与提成进行加法计算，得到的结果为空。

解决办法：
通过nvl()处理，nvl(exp1, res)；当表达式不为空时，结果为表达式，表达式结果为空的时候，结果为res。

1. select sal, comm, sal + nvl(comm, 0) as 月收入 from emp;
就可以得到正确的工资情况了。
2. 排序可使用nulls first和nulls last来选择空的信息的位置：
select * from emp order by comm nulls first;

1
2
3
4
5
6
7
8
9
10
11
12
13

2. 字符串拼接。

通过||实现字符串的拼接。

查询所有员工姓名并在后面加一个a。
select ename, ename || 'a' as 别名 from emp;
查询所有员工姓名并在后面加上两次本名。
select ename, ename || ename||ename as 别名 from emp;
查询所有员工姓名并加上他们的奖金。//当字符串拼接遇到空的时候，空会自动变成一个空字符串。
select ename, comm, ename || comm as test from emp;
1
2
3
4
5
6
7
8

5. 虚表

        dual是一个虚表，虚拟表，是用来构成select的语法规则，oracle保证dual里卖弄永远只有一条记录。该表只有一行一列，它和其他的表一样可以执行插入、更新、删除操作，还可以执行drop操作，但是不要去执行drop操作，否则会使系统不能用，起不了数据库。
        dual主要用来选择系统变量或是求一个表达式的值。如果我们不需要从具体的表中来取得表中数据，而是单纯地得到一些我们想要的信息，并通过select完成时，就要借助一个对象，这个对象就是dual。

1. 计算999 * 666；
select 999 * 666 from dual;
2. 获取系统时间。
select sysdate from dual;
1
2
3
4

6. 条件查询。

!=、<>、^=：表示不等。

查询员工部门不为10部门的员工。
select * from emp where deptno != 10;
select * from emp where deptno ^= 10;
select * from emp where deptno <> 10;
1
2
3
4

not :对条件取反。

查询员工部门不为20的信息。
select * from emp where not deptno = 20;
1
2

between and :区间之内。

查询员工部门为10-20的员工信息
select * from emp where deptno between 10 and 20 order by deptno;
1
2

in :几个定值

查询员工部门为10和20的员工信息
select * from emp where deptno in (10, 20) order by deptno;
1
2

nvl( , ): 表达式值不为空则是表达式，表达式为空则返回结果值

查询所有没有奖金的员工信息。（需要将奖金的空值转换为0、把它当成0看）
select * from emp where nvl(comm, 0) <= 0;
1
2

7. 模糊查询

select * from emp where ename like '%a%%' escape('a');
表示该字符后面的第一个%或_表示为自己的含义。

该标识只针对后面一位字符：查询包含"a%"的员工名字。
select * from emp where ename like '%aaa%%' escape('a');
查询员工名称中包含a%并且在后面的某个地方还包含_的员工信息。
select * from emp where ename like '%aaa%%a_%' escape('a');
1
2
3
4
5
6
7

8. 函数介绍。

1. 单行函数：对应在表记录时，一条记录返回一个结果，例如lower(x)，将参数转换为小写。
2. 多行函数：也称组函数、聚合函数（重点）：此类函数可同时对多条记录进行操作，并返回一个结果。例如max(x)求最大值。

常用单行函数：

常用组函数：null不参与运算

9. Having过滤组信息

查询部门员工大于等于4的部门，使用Having过滤掉不符合的分组

select deptno, count(*) from emp group by deptno having count(*) >= 4
1

10. Oracle分页

• 假分页：一次性查询所有信息，并分页显示
  优点：与数据库交互少 缺点：数据多时占用内存多
• 真分页：每次查询相应页数的信息，并显示
  优点：占用内存小 缺点：与数据库交互多

通过rownum实现分页。
由于结果集中默认也存在rownum，所以需要通过伪列显示rownum后进行查询。
一页五条，查询第二页数据。
select * from(select ename, deptno, rownum as rw from emp) where rw between 6 and 10;
1
2
3
4

11. 多表连接查询：92语法

等值链接

查询员工及该员工部门信息
select * from emp e, dept d where e.deptno = d.deptno
1
2

非等值链接

查询员工姓名，工资金额，入职时间，工资等级
select ename, sal, hiredate, grade from emp e, salgrade s where e.sal between s.losal and s.hisal
1
2

12. 外链接

只要在主表中出现的记录，即使另一张表没有合它匹配的也会显示出来。
左外连接：左面的表是主表。
右外连接：右面的表是主表。
看’+',带+的是从表，对立面的表为主表。

查看每一个部门的信息以及员工数。
select d.deptno, d.dname, loc, nvl(cc, 0) from dept d, (select deptno, count(*) as cc from emp group by deptno) e where d.deptno = e.deptno(+)
1
2

13. 自链接

特殊的等值链接，（来自于同一张表）

找出上级的员工姓名

```cpp
select e1.ename, e1.mgr, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno(+)
1
2
3
4

14. 多表连接查询：99语法。

1. 笛卡尔积实现：cross join

select * from dept cross join emp
1

2. 自然链接：等值链接（必须有同名列或存在主外键关系）：natural join

查询所有员工名称、员工编号、所属部门标号、所属部门名称。
会自己寻找等值。
select ename, empno, deptno, dname from emp natural join dept
1
2
3

3. 等值连接：(必须有同名列) :join using

查询所有员工名称、员工编号、所属部门标号、所属部门名称。
select ename, empno, deptno, dname from emp join dept using (deptno)
1
2

4. 可以解决一切链接:join on
   同名列前需要加限定词，e.deptno

查询所有员工名称、员工编号、所属部门编号、所属部门名称。
select ename, empno, e.deptno, dname from emp e join dept d on e.deptno = d.deptno

查询每一个员工的姓名，工资，所属部门编号，工资等级。
select ename, sal, deptno, sg.grade from emp e join salgrade sg on e.sal between sg.losal and sg.hisal

查询30部门的员工姓名、工资、部门编号、工资等级、部门名称。
select ename, sal, d.deptno, sg.grade, d.dname
from emp e
join salgrade sg on e.sal between sg.losal and sg.hisal
join dept d on e.deptno = d.deptno
where d.deptno = 30
1
2
3
4
5
6
7
8
9
10
11
12

5. 外连接：有主从表之分
   left out join on(using) / right out join on(using)

查看员工编号、员工名称、上级编号、上级名称
左外连接：
select e1.empno, e1.ename, e1.mgr, e2.ename
from emp e1
left outer join emp e2 on e1.mgr = e2.empno

右外连接：
select e1.empno, e1.ename, e1.mgr, e2.ename
from emp e1
right outer join emp e2 on e1.mgr = e2.empno
1
2
3
4
5
6
7
8
9
10

15. 集合操作

• Union 、Union All、Intersect、Minus
• Union：并集（去重），对两个结果集进行并集操作，不包括重复行同时进行默认规则的排序。

select 'a', 'b' from dual
union
select 'c', 'd' from dual
union
select 'a', 'b' from dual
1
2
3
4
5

• Union All：全集（不去重），对两个结果集进行并集操作，包括重复行，不进行排序。

select 'a', 'b' from dual
union all
select 'c', 'd' from dual
union all
select 'a', 'b' from dual
1
2
3
4
5

• Intersect：交集（找出重复），对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序。

select 'a', 'b' from dual
intersect
select 'a', 'b' from dual
1
2
3

• Minus：差集（减去重复），对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序。

(select 'a', 'd' from dual
union
select 'a', 'b' from dual
union
select 'a','f' from dual
)

Minus
select 'a', 'b' from dual

1
2
3
4
5
6
7
8
9
10