oracle学习笔记

描述一个表用 desc employees
过滤重复的部门 select distinct department_id from employees
别名的三种方式： 1、空格 2、加as 3、" "（多个单词组成的别名必须加空格，要么用下划线分开）
条件匹配日期的： where to_char(date,'yyyy-mm-dd')='1997-06-07'
默认格式： where date = '7-6月-1997'
like: where name like '%\_%' escape '\' (%:0个或者多个字符，_表示任意一个字符，escape表示转义关键字)
order by salary asc(升序) desc（降序）
多层排序： order by salary asc,name asc (在工资相同的情况下按照名字升序排列)
删除表中字段重复的记录：
delete from job_grades j1
where rowid <> (select min(rowid) from job_grades j2 where j1.grade_level = j2.grade_level);
如果删除表中自然顺序的第15行，下面语句可实现。
（rowid是数据库的一个伪列，建立表的时候数据库会自动为每个表建立ROWID列
用来唯一标识一行记录。rowid是存储每条记录的实际物理地址，对记录的访问是基于ROWID。）
delete from tab where rowid=(
select ii from (select ROWNUM nn,ROWID ii from tab WHERE ROWNUM<=15) WHERE nn=15);

等值连接：
select employee_id,e.department_id,department_name,city
from employees e,departments d,locations l
where e.department_id = d.department_id and d.location_id = l.location_id
或者
select employee_id,e.department_id,department_name,city
from employees e
join departments d on e.department_id = d.department_id
join locations l on d.location_id = l.location_id
等值连接另外方式：
select last_name,department_id,department_name
from employees join departments
--using (department_id) （前提是两表的列名以及列的数据类型要一样）

非等值连接
select employee_id,last_name,salary,grade_level
from employees e,job_grades j
where e.salary between j.lowest_sal and j.highest_sal
左外连接（哪边空就把“+”号放在哪个表上）
select e.employee_id,e.last_name,d.department_name
from employees e,departments d
where e.department_id = d.department_id(+)
左外（右外、满）连接
select employee_id,e.department_id,department_name
from employees e
left outer
--right outer
--full
join departments d on e.department_id = d.department_id
自连接

表数据的增、删、改：
向表中增加数据
1、insert into emp1 ‘sysdate’或者
values (1002,'BB',to_date('1998-08-08','yyyy-mm-dd'),20000)
2、insert into emp1
values (1002,'BB',to_date('1998-08-08','yyyy-mm-dd'),null)

3、insert into emp1(employee_id,last_name,hire_date) 注意：这里是只给部分列赋值，有非空约束的必须赋值
values (1004,'DD',to_date('1990-08-08','yyyy-mm-dd'))
4、注意：只想赋值一部分列的话，其它必须是允许放空值的列，这里默认salary是NULL（即有非空约束的必须赋值）
insert into emp1(employee_id,last_name,hire_date)
values (1005,'EE',to_date('1996-08-08','yyyy-mm-dd'))
5、弹窗式的插入数据
insert into emp1(employee_id,last_name,hire_date,salary)
values (&id,'&last_name','&hire_date',&salary)
基于现有表的记录插入数据
insert into emp1(employee_id,last_name,hire_date,salary)
select employee_id,last_name,hire_date,salary
from employees
where department_id = 80

更新数据：
update emp1
set salary = 22000
where employee_id = 179
更新114员工的工作和工资使其与206号员工相同
1、select employee_id,job_id,salary
from employees1
where employee_id in (114,205)
2、update employees1
set job_id = (
select job_id from employees1 where employee_id = 205
),salary = (
select salary from employees1 where employee_id = 205
)
where employee_id = 114

调整与employee_id为200的员工的job_id相同的
员工的department_id为employee_id为100的员工的department_id
update employees1
set department_id = (select department_id from employees1 where employee_id = 100)
where job_id = (select job_id from employees1 where employee_id = 200)
容易出现的数据完整性的错误如：
update employees
set department_id = 55
where department_id = 100; 问题出现在表中55号部门本来就不存在
从employees表中删除departments部门名称中含有Public字符的部门id
delete from employees1
where department_id = (select department_id from departments where department_name like '%Public%')

增：
insert into ...
values(...)

insert into ...
select...from...where...
改：
update ...
set ...
where ...
删：
delete from ...
where ...

事务：
commit;
savepoint A;
rollback to savepoint A;
当用户操作表的时候，还没有commit之前，其它用户是不能够对当前的表进行操作的
更改108号员工的信息：使其工资变为所在部门中的最高工资，job变为公司中平均工资最低的 job
update employees
set salary = (select max(salary)
from employees
where department_id = (
select department_id
from employees
where employee_id = 108)
group by department_id),
job_id = (select job_id
from employees
having avg(salary) = (
select min(avg(salary))
from employees
group by job_id )
group by job_id)
where employee_id = 108
删除108号员工所在部门中工资最低的那个员工
delete from employees
where employee_id = (
select employee_id
from employees
where department_id = (select department_id
from employees
where employee_id = 108)
and salary = (select min(salary)
from employees
where department_id =(select department_id
from employees
where employee_id = 108)
)
)
可以优化成：
delete from employees e
where department_id = (select department_id
from employees
where employee_id = 108)
and salary = (select min(salary)
from employees
where department_id = e.department_id
)
使用约束not null和unique创建表：其中在有unique约束中给它赋值多个null，null之间是不冲突的
create table emp3(
--列级约束：
id number(10) constraint emp3_id_uk unique,
name varchar2(20) constraint emp3_name_nn not null,
email varchar2(20),
salary number(10),
--表级约束：
constraint emp3_email_uk unique(email)
)
主键约束：能够唯一的确定一条记录，同样也分表级约束和列级约束，primary key不仅是not null而且unique
create table emp4(
id number(10) constraint emp4_id_pk primary key,
name varchar2(20) constraint emp4_name_nn not null,
email varchar2(20),
salary number(10),
constraint emp4_email_uk unique(email)
)
或者
create table emp4(
id number(10),
name varchar2(20) constraint emp4_name_nn not null,
email varchar2(20),
salary number(10),
constraint emp4_email_uk unique(email)，
constraint emp4_id_pk primary key（id）
)
外键约束：（注意：在emp6中插入数据的时候，不能够插入departments表中department_id没有的数据记录。另外，外键引用的列起码要有一个唯一的约束）
create table emp6(
id number(10),
name varchar2(20) constraint emp6_name_nn not null,
email varchar2(20),
salary number(10),
department_id number(10),
constraint emp6_email_uk unique (email),
constraint emp6_id_pk primary key(id),
constraint emp6_dept_id_fk foreign key(department_id) references departments(department_id)
)
向表中插入departments表中存在的department_id(主键)数据
insert into emp6
values(1002,'AA',null,10000,20)

on delete set null:(级联置空：子表中相应的列置空)
on delete cascade:(级联删除：当父表中的列被删除时，子表中相对应的列也被删除)
create table emp7(
id number(10),
name varchar2(20) constraint emp7_name_nn not null,
email varchar2(20),
salary number(10),
department_id number(10),
constraint emp7_email_uk unique (email),
constraint emp7_id_pk primary key(id),
constraint emp7_dept_id_fk foreign key(department_id) references departments(department_id) on delete set null
)
check约束：比如约束工资的范围
create table emp8(
id number(10),
name varchar2(20) constraint emp8_name_nn not null,
email varchar2(20),
salary number(10) constraint emp8_salary check(salary>1500 and salary<30000),
department_id number(10),
constraint emp8_email_uk unique (email),
constraint emp8_id_pk primary key(id),
constraint emp8_dept_id_fk foreign key(department_id) references departments(department_id) on delete set null
)

修改约束：
添加not null约束
alter table emp5
modify (salary number(10,2) not null)
删除约束：
alter table emp5
drop constraint emp5_name_nn
添加unique约束
alter table emp5
add constraint emp5_name_uk unique(name)
无效化约束：
alter table emp3
disable constraint emp3_email_uk
激活约束：
alter table emp3
enable constraint emp3_email_uk
查询约束：（注意：其中条件里的表名要大写）
select constraint_name,constraint_type,search_condition
from user_constraints
where table_name = 'EMPLOYEES'
查询定义有约束的列有哪些：
select constraint_name,column_name
from user_cons_columns
where table_name = 'EMPLOYEES'

视图：
它实际上是一个虚表，它是依赖于基表的，当视图中的数据更改时，基表中的相应数据也被更改
为什么要使用视图？
答：1、可以控制数据访问 2、简化查询 3、避免重复访问相同的数据

创建视图：
create view empview
as
select employee_id,last_name,salary
from employees
where department_id = 80
基于多张表来创建视图：
create view empview3
as
select employee_id id,last_name name,salary,e.department_name
from employees e,departments d
where e.department_id = d.department_id
修改视图：create or replace
create or replace view empview2
as
select employee_id id,last_name name,department_name
from employees e,departments d
where e.department_id = d.department_id
屏蔽DML操作：with read only (其他用户只能查看，不能增、删、改)
create or replace view empview2
as
select employee_id id,last_name name,department_name
from employees e,departments d
where e.department_id = d.department_id
with read only

简单视图和复杂视图的区别：简单视图没有分组函数，在复杂视图中若使用了组函数创建的，则对它不能使用DML（增、删、改）的操作，因为有些列在基表中原本是不存在的。
创建一个复杂视图：（注意：基表中不存在的列，创建视图时要给它个别名，如下面的平均工资。）
create or replace view empview3
as
select department_name dept_name,avg(salary) avg_sal
from employees e ,departments d
where e.department_id = d.department_id
group by department_name

rownum是一个伪列，跟id有点相关，且有它自己的一个排序。
比如，你想找到表中工资最高的前10位的员工，以下用rownum作为条件是不行的，因为它有自己默认的排列顺序
select rownum,employee_id,last_name,salary
from employees
where rownum <= 10
order by salary desc
要真想用rownum达到查询最高工资的前10位则要如下这样：（注意：rownum只能使用<或<=，而用=,>,>=都不会返回任何数据）
select rownum,employee_id,last_name,salary
from (select employee_id,last_name,salary
from employees
order by salary desc)
where rownum <=10
那么要查询最高工资排列40-50名的数据要怎么办？如下：（此时最外层的rn已经不是伪列了）
select rn,employee_id,last_name,salary
from (select rownum rn,employee_id,last_name,salary
from (select employee_id,last_name,salary
from employees
order by salary desc))
where rn <40 and rn<=50

序列：主要用来提供主键值
创建序列：
create sequence empseq
increment by 10
start with 10
maxvalue 100
cycle
nocache
序列在数据插入表时在主键位置的作用：
先：
create table emp01
as
select employee_id,last_name,salary
from employees
where 1=2
然后：
insert into emp01
values(empseq.nextval,'BB',3300)
修改序列：（能修改增量、最大值、最小值、是否循环以及是否装入内存，如要更改初始值则要通过删除序列重新创建序列，因为改后可能会与之前的数据发生冲突，因为序列是唯一的）
alter sequence empseq
increment by 1
nocycle
序列一以下情况中会出现裂缝：
1、回滚
2、系统出现异常
3、多个表同时使用同一个序列
查询序列：（如果指定了nocache选项，则last_number返回的是序列中下一个有效值）
select sequence_name,min_value,max_value,increment_by,last_number
from user_sequences
删除序列
drop sequence empseq
索引的作用，能够加速 oracle服务器的查询速度，主键和唯一约束中系统默认为它所约束的列创建所引，也可以为非唯一的列手动创建所引，创建好之后系统会自动调用索引，不用你手动编码调用
创建索引：
create index emp01_id_ix
on emp01(employee_id)
删除索引：
drop index emp01_id_ix
什么时候创建索引？
1、列中数据值分布范围很广
2、列经常在where子句或者连接条件中出现
3、表经常被访问而且数据量很大，访问的数据大概占数据总量的2%到4%
什么时候不要创建索引？
1、表很小
2、表经常更新
3、查询的数据大于2%到4%
4、表不经常作为where子句或者连接条件中出现

同义词：
创建同义词
create synonym e for employees
删除同义词
drop synonym e

创建用户和密码：
create user atguigu01
identified atguigu01;
给用户登录数据库的权限，（create table,create sequence,create view,create procedure）
grant create session
to atguigu01 ;
创建用户表空间
alter user atguigu01 quota unlimited（或者5M）
on users
更改用户自己的密码：
alter user atguigu01
identified by atguigu;

角色：使用角色分配给用户权限会更快，角色有什么权限，用户就有什么权限
1、创建角色
create role my_role
2、为角色赋予权限
grant create session,create table,create view
to my_role
3、将角色赋予用户
grant my_role to atguigu02

对象：
1、给atguigu01分配表employees的查询、修改的权限
grant select,update
on scott.employees
to atguigu01
验证上一步的操作：
update scott.employees
set last_name = 'ABCD'
where employee_id = 206 （此时改的是scott用户中的表，atguigu只是调用它的表，并没有复制过来）