數據庫-Oracle

鏈接： oracle常見面試題_ocrale面試題-CSDN博客

鏈接：Oracle 面試題匯總_oracle面試-CSDN博客

〇、Oracle用戶管理

1、創建用戶
概述：在oracle中要創建一個新的用戶使用create user語句，一般是具有dba(數據庫管理員)的權限才能使用。
create user 用戶名 identified by 密碼;
注意：oracle有個毛病，密碼必須以字母開頭，如果以數字開頭，它不會創建用戶
eg、create user xiaoming identified by oracle;

2、給用戶修改密碼
概述：如果給自己修改密碼可以直接使用
SQL> password 用戶名或passw
如果給別人修改密碼則需要具有dba的權限，或是擁有alter user的系統權限
SQL> alter user 用戶名 identified by 新密碼

3、刪除用戶
概述：一般以dba的身份去刪除某個用戶，如果用其它用戶去刪除用戶則需要具有drop user的權限。
比如drop user 用戶名 【cascade】
注意：在刪除用戶時，如果要刪除的用戶，已經創建了表，那么就需要在刪除的時候帶一個參數cascade，即把該用戶及表一同刪除;

4、權限
權限分為系統權限和對象權限。
何為系統權限？
用戶對數據庫的相關權限，connect、resource、dba等系統權限，如建庫、建表、建索引、建存儲過程、登陸數據庫、修改密碼等。
何為對象權限？
用戶對其他用戶的數據對象操作的權限，insert、delete、update、select、all等對象權限，數據對象有很多，比如表，索引，視圖，觸發器、存儲過程、包等。
執行SELECT * FROM Dba_Object_Size;語句可得到oracle數據庫對象。

5、角色
角色分為預定義角色和自定義角色。

?

6、用戶管理的綜合案例
概述：創建的新用戶是沒有任何權限的，甚至連登陸的數據庫的權限都沒有，需要為其指定相應的權限。給一個用戶賦權限使用命令grant，回收權限使用命令revoke。
為了講清楚用戶的管理，這里我給大家舉一個案例。
SQL> conn xiaoming/oracle
ERROR:
ORA-01045: user XIAOMING lacks CREATE SESSION privilege; logon denied
警告: 您不再連接到 ORACLE。
SQL> show user
USER 為 “”
SQL> conn system/oracle
已連接。
SQL> grant connect to xiaoming;
授權成功。
SQL> conn xiaoming/oracle
已連接。

注意：grant connect to xiaoming;在這里，準確的講，connect不是權限，而是角色。

現在說下對象權限，現在要做這么件事情：
* 希望xiaoming用戶可以去查詢emp表
* 希望xiaoming用戶可以去查詢scott的emp表
grant select on scott.emp to xiaoming
* 希望xiaoming用戶可以去修改scott的emp表
grant update on scott.emp to xiaoming
* 希望xiaoming 用戶可以去修改/刪除，查詢，添加scott的emp表
grant all on scott.emp to xiaoming
* scott希望收回xiaoming對emp表的查詢權限
revoke select on scott.emp from xiaoming

一、Oracle數據庫操作

1、創建數據庫

create database databasename

2、刪除數據庫

drop database dbname

3、備份數據庫

• 完全備份

  exp demo/demo@orcl buffer=1024 file=d：\back.dmp full=y

  demo：用戶名、密碼

  buffer: 緩存大小

  file: 具體的備份文件地址

  full: 是否導出全部文件

  ignore: 忽略錯誤，如果表已經存在，則也是覆蓋

• 將數據庫中system用戶與sys用戶的表導出

  exp demo/demo@orcl file=d:\backup\1.dmp owner=(system,sys)

• 導出指定的表

  exp demo/demo@orcl file=d:\backup2.dmp tables=(teachers,students)

• 按過濾條件，導出

  exp demo/demo@orcl file=d：\back.dmp tables=（table1） query=" where filed1 like ‘fg%’"

  導出時可以進行壓縮；命令后面 加上 compress=y ；如果需要日志，后面： log=d:\log.txt

• 備份遠程服務器的數據庫

  exp 用戶名/密碼@遠程的IP:端口/實例 file=存放的位置:\文件名稱.dmp full=y

4、數據庫還原

打開cmd直接執行如下命令，不用再登陸sqlplus。

• 完整還原

  imp demo/demo@orcl file=d:\back.dmp full=y ignore=y log=D:\implog.txt

  指定log很重要，便于分析錯誤進行補救。

• 導入指定表

  imp demo/demo@orcl file=d:\backup2.dmp tables=(teachers,students)

• 還原到遠程服務器

  imp 用戶名/密碼@遠程的IP:端口/實例 file=存放的位置:\文件名稱.dmp full=y

二、Oracle表操作

1、創建表

• 學生表

create table student (Id number(4) primary key,xh number(4) , --學號xm varchar2(20), --姓名sex char(2), --性別birthday date, --出生日期sal number(7,2) --獎學金
);

• 班級表

create table class(classid number(2),cname varchar2(40)
);

–修改表
–添加一個字段
alter table student add (classid number(2));
–修改一個字段的長度
alter table student modify (xm varchar2(30));
–修改字段的類型或是名字（不能有數據） 不建議做
alter table student modify (xm char(30));
–刪除一個字段 不建議做(刪了之后，順序就變了。加就沒問題，應該是加在后面)
alter table student drop column sal;
–修改表的名字 很少有這種需求
rename student to stu;

–創建自增列
create sequence student

increment by 1 – 每次加幾個

start with 1 – 從1開始計數

minvalue 1 --最小值為1

NOMAXvalue – 不設置最大值 (maxvalue 99999999 等同于maxvalue 99999999 )

cache 10; --設置緩存cache個序列，如果系統down掉了或者其它情況將會導致序列不連續，

nocache; --一直累加 不循環

一旦定義了S_Test，你就可以用currval，nextval
currval=返回 sequence的當前值
nextval=增加sequence的值，然后返回 sequence 值
比如：
S_Test.CURRVAL
S_Test.NEXTVAL

insert into student(id,xh, xm, sex) values (student.Nextval,‘a003’, ‘john’, ‘女’);

create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],…)

根據已有的表創建新表：

A：select * into table_new from table_old (使用舊表創建新表)

B：create table tab_new as select col1,col2… from tab_old definition only<僅適用于Oracle>

2、刪除表

drop table tabname

3、重命名表

說明：alter table 表名 rename to 新表名

? eg：alter table tablename rename to newtablename

4、增加字段

說明：alter table 表名 add (字段名 字段類型 默認值 是否為空);

? 例：alter table tablename add (ID int);

? eg：alter table tablename add (ID varchar2(30) default ‘空’ not null);

5、修改字段

說明：alter table 表名 modify (字段名 字段類型 默認值 是否為空);

? eg：alter table tablename modify (ID number(4));

6、重名字段

說明：alter table 表名 rename column 列名 to 新列名 （其中：column是關鍵字）

? eg：alter table tablename rename column ID to newID;

7、刪除字段

說明：alter table 表名 drop column 字段名;

? eg：alter table tablename drop column ID;

8、添加主鍵

alter table tabname add primary key(col)

9、刪除主鍵

alter table tabname drop primary key(col)

10、創建索引

create [unique] index idxname on tabname(col….)

11、刪除索引

drop index idxname

注：索引是不可更改的，想更改必須刪除重新建。

12、創建視圖

create view viewname as select statement

13、刪除視圖

drop view viewname

三、Oracle操作數據

1、數據查詢

select <列名> from <表名> [where <查詢條件表達試>] [order by <排序的列名>[asc或desc]]

2、插入數據

insert into 表名 values(所有列的值);

insert into test values(1,‘zhangsan’,20);

insert into 表名(列) values(對應的值);

insert into test(id,name) values(2,‘lisi’);

3、更新數據

update 表 set 列=新的值 [where 條件] -->更新滿足條件的記錄

update test set name=‘zhangsan2’ where name=‘zhangsan’

update 表 set 列=新的值 -->更新所有的數據

update test set age =20;

4、刪除數據–delete與truncate 區別

• delete from 表名 where 條件 -->刪除滿足條件的記錄

  delete from test where id = 1;

  delete from test -->刪除所有

  commit; -->提交數據

  rollback; -->回滾數據

  delete方式可以恢復刪除的數據，但是提交了，就沒辦法了 delete刪除的時候，會記錄日志 -->刪除會很慢很慢

• truncate table 表名

  刪除所有數據，不會影響表結構，不會記錄日志，數據不能恢復 -->刪除很快

• drop table 表名

  刪除所有數據，包括表結構一并刪除，不會記錄日志，數據不能恢復–>刪除很快

5、數據復制

• 表數據復制

  insert into table1 (select * from table2);

• 復制表結構

  create table table1 select * from table2 where 1>1;

• 復制表結構和數據

  create table table1 select * from table2;

• 復制指定字段

  create table table1 as select id, name from table2 where 1>1;

6、數據庫復制命令

一．入門部分1. 創建表空間
create tablespace schooltbs datafile ‘D:\oracle\datasource\schooltbs.dbf’ size 10M autoextend on;2. 刪除表空間
drop tablespace schooltbs[including contents and datafiles];3. 查詢表空間基本信息
select *||tablespace_name from DBA_TABLESPACES;4. 創建用戶
create user  lihua
identified by lihua
default tablespace schooltbs
temporary tablespace temp;5. 更改用戶
alter user lihua 
identified by 123
default tablespace users;6. 鎖定用戶
alter user lihua account lock|unlock;7. 刪除用戶
drop user lihua cascade;--刪除用戶模式8. oracle數據庫中的角色
connect,dba,select_catalog_role,delete_catalog_role,execute_catalog_role,exp_full_database,imp_full_database,resource9. 授予連接服務器的角色
grant connect to lihua;10. 授予使用表空間的角色
grant resource to lihua with grant option;--該用戶也有授權的權限11. 授予操作表的權限
grant select,insert on user_tbl to scott;--當前用戶
grant delete,update on lihua.user_tbl to scott;--系統管理員二．SQL查詢和SQL函數1.SQl支持的命令：
數據定義語言（DDL）:create,alter,drop
數據操縱語言（DML）:insert,delete,update,select
數據控制語言（DCL）:grant,revoke
事務控制語言（TCL）:commit,savepoint,rollback2.Oracle數據類型
字符，數值，日期，RAW，LOB
字符型
char:1-2000字節的定長字符
varchar2:1-4000字節的變長字符
long:2GB的變長字符注意：一個表中最多可有一列為long型Long列不能定義唯一約束或主鍵約束long列上不能創建索引過程或存儲過程不能接受long類型的參數。數值型
number:最高精度38位
日期時間型
date:精確到ss
timestamp:秒值精確到小數點后6位函數
sysdate,systimestamp返回系統當前日期，時間和時區。
更改時間的顯示
alter session set nls_date_language=’american’;
alter session set nls_date_format=’yyyy-mm-dd’;Oracle中的偽列
像一個表列，但沒有存儲在表中偽列可以查詢，但不能插入、更新和修改它們的值
常用的偽列：rowid和rownum
rowid:表中行的存儲地址，可唯一標示數據庫中的某一行，可以使用該列快速定位表中的行。
rownum:查詢返回結果集中的行的序號，可以使用它來限制查詢返回的行數。3.數據定義語言用于操作表的命令
create table
alter table
truncate table
drop table修改表的命令
alter table stu_table rename to stu_tbl;--修改表名
alter table stu_tbl rename column stu_sex to sex;--修改列名
alter table stu_tbl add (stu_age number);--添加新列
alter table stu_tbl drop(sex);--刪除列
alter table stu_tbl modify(stu_sex varchar2(2));--更改列的數據類型
alter table stu_tbl add constraint pk_stu_tbl primary key(id);--添加約束4.數據操縱語言select,update,delete,insert
利用現有的表創建表
create table stu_tbl_log as select id,stu_name,stu_age from stu_tbl;--
選擇無重復的行select distinct stu_name from stu_tbl;-- 
插入來自其他表中的記錄
insert into stu_tbl_log select id,stu_name,stu_age from stu_tbl;5.數據控制語言
grant,revoke
6.事務控制語言
commit,savepoint,rollback
7.SQL操作符
算術操作符:L+-*/
比較操作符:L=,!=,<>,>,<,>=,<=,between-and,in,like,is null等
邏輯操作符:Land,or,not
集合操作符:Lunion,union all,intersect,minus
連接操作符:L||
示例中stu_tbl_log中的數據如下：ID STU_NAME                STU_AGE---------- -------------------- ----------1000 李華                          201001 accp                         201003 nimda                         3
stu_tbl中的數據如下：ID STU_NAME             ST    STU_AGE---------- -------------------- -- ----------1000 李華                  男         201001 accp                 男         201002 admin                男         30
示例：
select (3+2)/2 from dual;--算術操作符，結果：2.5
select * from stu_tbl where stu_age>=20;--比較操作符
select * from stu_tbl where stu_name like '%a%';--比較操作符：like
select * from stu_tbl where stu_name like 'a___';--比較操作符：like
select * from stu_tbl where stu_age in(20,30);--比較操作符：in
select * from stu_tbl where stu_age between 20 and 30;--比較操作符：between
select stu_name from stu_tbl union all
select stu_name from stu_tbl_log;--集合操作符：union all，測試結果具體如下：
STU_NAME-----------李華accpadmin李華accpnimda已選擇6行。
select stu_name from stu_tbl union 
select stu_name from stu_tbl_log;--集合操作符：union，測試結果具體如下：
STU_NAME---------accpadminnimda李華
select stu_name from stu_tbl intersect 
select stu_name from stu_tbl_log;--集合操作符：intersect，測試結具體如下：
STU_NAME----------accp李華
select stu_name from stu_tbl minus 
select stu_name from stu_tbl_log;--集合操作符：minus，測試結果如下：
STU_NAME----------Admin
從中可以看出：
minus是獲取第一張表獨有的數據
intersect是獲取兩張表中都有的數據
union是整合兩張表的數據，都有的只顯示一次
union all是純粹的兩張表數據整合
select id,stu_name||' '||stu_sex as name_sex,stu_age 
from stu_tbl;--連接操作符||，測試結果具體如下：ID NAME_SEX                   STU_AGE---------- ----------------------- ----------1000 李華    男                         201001 accp   男                         201002 admin  男                         308.SQL函數
單行函數：從表中查詢的每一行只返回一個值，可出現在select子句，where子句中日期函數數字函數字符函數轉換函數：ToChar(),ToDate(),ToNumber()其他函數:Nvl(exp1,exp2):表達式一為null時，返回表達式二Nvl2(exp1,exp2,exp3):表達式一為null時返回表達式三，否則返回表達式二Nullif(exp1,exp2):兩表達式相等時，返回null，否則返回表達式一
分組函數：基于一組行來返回Avg,Min,Max,Sum,CountGroup by,having
分析函數Row_number,rank,dense_rank
示例：
select u.user_name,sum(oi.order_num*oi.order_price) as total,row_number() over (order by sum(oi.order_num*oi.order_price) desc) as sort from order_item_tbloi,user_tbl  u,order_tbl  o where oi.order_id = o.id and o.user_id = u.id group by u.user_name;三．鎖和數據庫對象1.鎖：數據庫用來控制共享資源并發訪問的機制。
鎖的類型：行級鎖，表級鎖
行級鎖：對正在被修改的行進行鎖定。行級鎖也被稱之為排他鎖。
在使用下列語句時，Oracle會自動應用行級鎖：
insert,update,delete,select…… for update
select……for update允許用戶一次鎖定多條記錄進行更新。
使用commit or rollback釋放鎖。
表級鎖：
lock table user_tbl in mode mode;
表級鎖類型：
行共享 row share
行排他 row exclusive
共享 share
共享行排他 share row exclusive
排他 exclusive
死鎖：兩個或兩個以上的事務相互等待對方釋放資源，從而形成死鎖
2.數據庫對象
oracle數據庫對象又稱模式對象
數據庫對象是邏輯結構的集合，最基本的數據庫對象是表
數據庫對象：表，序列，視圖，索引序列
用于生成唯一，連續序號的對象。
創建語法：
create sequence user_id_seq
start with 1000
increment by 1
maxvalue 2000
minvalue 1000
nocycle
cache 1000;--指定內存中預先分配的序號
訪問序列：
select user_id_seq.currval from dual;
select user_id-seq.nextval from dual;更改刪除序列：
alter sequence user_id_seq maxvalue 10000;--不能修改其start with 值
drop sequence user_id_seq;
在Hibernate中訪問序列：<generator class="sequence"><param name="sequence">      
user_id_seq</param></generator>視圖
以經過定制的方式顯示來自一個或多個表的數據
創建視圖：
create or replace view 
user_tbl_view (vid,vname,vage)
as select id,user_name,age from user_tbl
[with check option]|[with read only];
創建帶有錯誤的視圖：
create force view user_tbl_force_view as
select * from user_table;--此時user_table可以不存在
創建外聯接視圖：
create view user_stu_view as
select u.id,u.user_name,u.password,s.ddress
from user_tbl u,stu_tbl s
where u.s_id(+)=s.id;--哪一方帶有(+)，哪一方就是次要的
刪除視圖：
drop user_stu_view;索引
用于提高SQL語句執行的性能
索引類型：
唯一索引，位圖索引，組合索引，基于函數的索引，反向鍵索引創建標準索引：
create index user_id_index on user_tbl(id) tablespace schooltbs;
重建索引：
alter index user_id_index rebuild;
刪除索引：
drop index user_id_index;創建唯一索引：
create unique index user_id_index on user_tbl(id);
創建組合索引：
create index name_pass_index on user_tbl(user_name,password);
創建反向鍵索引：
create index user_id_index on user_tbl(id) reverse;