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
自连接
--查询公司中员工‘Chen’的manager的信息。
select e1.last_name,e2.last_name,e2.email,e2.salary
from employees e1,employees e2
where e1.manager_id = e2.employee_id and lower(e1.last_name) = ‘chen‘
组函数：（注意：只要不是组函数中的列都应该放在group by中，group by中的顺序不做限定，且其中有的列select中可以没有）
select department_id,job_id,avg(salary)
from employees
group by department_id,job_id
order by department_id asc
有组函数的过滤条件用having而不能用where:
select department_id,avg(salary)
from employees
having avg(salary)>6000
group by department_id
order by department_id asc
查询全公司奖金基数的平均值（注意：avg、count默认是不把值为null的计入，此时要想计入的话必须使用nvl（）函数）：
select avg(nvl(commission_pct,0))
from employees
查询公司在1995-1998年之间，每年雇佣的人数：
select count(*) "total",
count(decode(to_char(hire_date,‘YYYY‘),‘1995‘,1,null)) "1995",
count(decode(to_char(hire_date,‘YYYY‘),‘1996‘,1,null)) "1996",
count(decode(to_char(hire_date,‘YYYY‘),‘1997‘,1,null)) "1997",
count(decode(to_char(hire_date,‘YYYY‘),‘1998‘,1,null)) "1998"
from employees
where to_char(hire_date,‘YYYY‘) in (‘1995‘,‘1996‘,‘1997‘,‘1998‘)
单行子查询：
谁的工资比‘Abel’的高
select last_name,salary
from employees
where salary > (select salary from employees where last_name = ‘Abel‘)
查询员工为Chen的manager的信息
方法一：（使用自连接）
select e2.last_name,e2.manager_id,e2.email,e2.salary
from employees e1,employees e2
where e1.manager_id = e2.employee_id and e1.last_name = ‘Chen‘
方法二：
select employee_id,last_name,email,salary
from employees
where employee_id = (
select manager_id
from employees
where last_name = ‘Chen‘)
返回job_id与141号员工相同，salary比143号员工多的员工姓名、job_id和工资
select employee_id,last_name,email,salary
from employees
where employee_id = (
select manager_id
from employees
where last_name = ‘Chen‘)
返回公司工资最少的员工的last_name,job_id,salary
select last_name,job_id,salary
from employees
where salary = (select min(salary) from employees)
查询最低工资大于50号部门最低工资的部门id和其最低工资
select department_id,min(salary)
from employees
group by department_id
having min(salary)>(select min(salary) from employees where department_id = 50)
多行子查询（in,any,all）:
返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id以及salary
select employee_id,last_name,job_id,salary
from employees
where job_id <>‘IT_PROG‘ and salary < any (select salary from employees where job_id = ‘IT_PROG‘)
order by last_name asc
返回其它部门中比job_id为‘IT_PROG’部门所有（任意）工资低的员工的员工号、姓名、job_id以及salary
select employee_id,last_name,job_id,salary
from employees
where job_id <>‘IT_PROG‘ and salary < any (select salary from employees where job_id = ‘IT_PROG‘)
order by last_name asc

查询平均工资最低的部门信息
select *
from departments
where department_id = (select department_id
from employees
having avg(salary) = (select min(avg(salary))from employees
group by department_id)
group by department_id)
查询平均工资最低的部门信息和该部门的平均工资
select d.* ,(select avg(salary) from employees where department_id = d.department_id)
from departments d
where department_id = (select department_id
from employees
having avg(salary) = (select min(avg(salary))from employees
group by department_id)
group by department_id)
查询平均工资最高的job信息
select *
from jobs
where job_id in (select job_id
from employees
having avg(salary) = (select max(avg(salary))from employees group by job_id)
group by job_id)
查询平均工资高于公司的部门有哪些
select department_id,avg(salary)
from employees
group by department_id
having avg(salary)>(select avg(salary) from employees)
查询公司中所有manager的详细信息
select *
from employees
where employee_id in (select manager_id from employees)
各个部门中，最高工资中最低的那个部门的 最低工资是多少
select min(salary)
from employees
where department_id = (select department_id
from employees
having max(salary)=(select min(max(salary))
from employees
group by department_id)
group by department_id
)
查询平均工资最高的的部门的manager的详细信息：last_name,department_id,email,salary
select last_name,department_id,email,salary
from employees
where employee_id in (select manager_id
from employees
where department_id = (select department_id
from employees
having avg(salary) = (select max(avg(salary))
from employees
group by department_id)
group by department_id))

查询1999年来公司的员工中的最高工资的那个员工的信息
select *
from employees
where salary = (select max(salary)
from employees
where employee_id in (select employee_id
from employees
where to_char(hire_date,‘YYYY‘) = ‘1999‘))
and to_char(hire_date,‘YYYY‘) = ‘1999‘

查看用户定义的表：
select table_name from user_tables;
查询数据库有哪些对象（表、视图、序列、同义词）
select * from user_catalog;
查看用户定义的各种数据库对象
select distinct object_type from user_objects;

创建表的第一种方式：（白手起家）
create table emp1(
id number(10),
name varchar2(20),
salary number(10,2),
hire_date date
)
第二种方式：借助现有的表来创建（数据也会在原有的表中搬过来）
create table emp2
as select employee_id id,last_name name,hire_date,salary
from employees
(where department_id = 80)
(where department_id = 800):不想要任何数据就给它写个没有的部门或者：where 1=2

修改表：
给表追加多一列：
alter table emp1
add (email varchar2(20))
修改表中的列属性：
1、alter table emp1
    modify (id number(15))
2、alter table emp1
　 modify (salary number(20,2) default 2000)
删除一个列：
alter table emp1
drop column email
重命名列：
alter table emp1
rename column salary to sal
删除表：
drop table emp5
清空表：
truncate table emp3;（不可以回滚）
delete from emp2;（可以回滚）
重命名表：
rename emp2 to employees2;

表数据的增、删、改：
向表中增加数据
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只是调用它的表，并没有复制过来）

（未完，待续......）

oracle从入门到精通复习笔记,古老的榕树,5-wow.com