oracle學習筆記

標籤：des   使用   io   資料   for   ar   art   問題   

描述一個表用 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只是調用它的表，並沒有複製過來）