oracle常用命令基礎(chǔ)

1、Oracle常用命令Oracle數(shù)據(jù)類型:Create table test1(name char(10,sex char(1;Insert into test1 values(tomcatt北京,f;Create table test2(name nchar(10,sex nchar(1;Insert into test2 values(to mcatt北京,男;刪除表 drop table 表名;Create table test3(name varchar2(10,sex varchar2(2;Insert into test3 values(tomcatt北京,f;/插入值過大Inse

2、rt into test3 values(tomcat北京,f;Create table test4(name varchar2(10,age number(3,salary number(8,2;Create table test5(name varchar2(10,birth date;Insert into test5 values(Tom,28-2月-08;Insert into test5 values(Allen,sysdate;DDL:創(chuàng)建表create table scott.test6(eid number(10,name varchar2(20,hiredate date

3、default sysdate,salary number(8,2 default 0插入數(shù)據(jù)時(shí)若沒有指定hiredate,salary的話則會(huì)取默認(rèn)值數(shù)據(jù)字典:Dba-所有方案包含的對(duì)象信息All-用戶可以訪問的對(duì)象信息User-用戶方案的對(duì)象信息Select * from user_tables;Select * from all_tables;約束:域完整性約束:not null check實(shí)體完整性約束:unique primary key參照完整性約束:foreign key視圖:Create or replace view v1(eid,name,salary as select

4、empno,ename,sal from emp where deptno = 30;序列:sequenceCreate sequence mysequence1 increment by 1 start with 1 nomaxvalue nocycle;Insert into test values(mysequence1.nextval,tom;Create sequence student_sequence start with 1 increment by 1;Insert into student values(student_sequence.nextval,john;表間數(shù)據(jù)拷

5、貝:Insert into dept1(id,name select deptno,dname from dept;實(shí)例(創(chuàng)建表 ID字段自增:-create table test2(id char(10 primary key not null, name char(10;-create sequence test2_sequence increment by 1 start with 1 nomaxvalue nocycle;-insert into test2 values(test2_sequence.nextval,'john'-insert into test2 v

6、alues(test2_sequence.nextval,'allen'-insert into test2 values(test2_sequence.nextval,'candy'-insert into test2 values(test2_sequence.nextval,'aaaa'-insert into test2 values(test2_sequence.nextval,'bbbbb'-insert into test2 values(test2_sequence.nextval,'cccccc'

7、-insert into test2 values(test2_sequence.nextval,'ddddd'-insert into test2 values(test2_sequence.nextval,'eeeee'-insert into test2 values(test2_sequence.nextval,'ggggg'-insert into test2 values(test2_sequence.nextval,'jowwwwhn'-insert into test2 values(test2_sequence.

8、nextval,'aaaadd'-insert into test2 values(test2_sequence.nextval,'ggghhh'-insert into test2 values(test2_sequence.nextval,'eeettt'-insert into test2 values(test2_sequence.nextval,'wwwttt'select * from test2;查看表結(jié)構(gòu)EDITDATA 表名;修改表字段:Alter table 表名 modify(字段名類型約束;alter ta

9、ble test modify (addd varchar2(10 null;alter table 表名 add(字段名類型約束;alter table test add(age varchar2(5;%51.登陸系統(tǒng)用戶sqlplus 然后輸入系統(tǒng)用戶名和密碼登陸別的用戶conn 用戶名/密碼;2.創(chuàng)建表空間create tablespace 空間名datafile 'c:空間名' size 15M -表空間的存放路徑,初始值為15MautoExtend on next 10M -空間的自動(dòng)增長(zhǎng)的值是10Mpermanent online; -永久使用3.創(chuàng)建用戶creat

10、e user shi -創(chuàng)建用戶名為shiidentified by scj -創(chuàng)建密碼為scjdefault tablespace 表空間名 -默認(rèn)表空間名temporary tablespace temp -臨時(shí)表空間為tempprofile default -受profile文件的限制quota unlimited on 表空間名; -在表空間下面建表不受限制4.創(chuàng)建角色create role 角色名 identified by 密碼;5.給角色授權(quán)grant create session to 角色名;-給角色授予創(chuàng)建會(huì)話的權(quán)限grant 角色名 to 用戶名; -把角色授予用戶6.給

11、用戶授予權(quán)限grant connect,resource to shi;-給shi用戶授予所有權(quán)限Grant dba to shi;-給shi 用戶授予DBA權(quán)限grant create table to shi; -給shi用戶授予創(chuàng)建表的權(quán)限7.select table_name from user_tables; 察看當(dāng)前用戶下的所有表8.select tablespace_name from user_tablespaces; 察看當(dāng)前用戶下的表空間9.select username from dba_users;察看所有用戶名稱命令必須用sys as sysdba登陸10.創(chuàng)建表cr

12、eate table 表名(id int not null,name varchar2(20 not nulltablespace 表空間名 -所屬的表空間storage(initial 64K -表的初始值minextents 1 -最小擴(kuò)展值maxextents unlimited -最大擴(kuò)展值;11.-為usrs表添加主鍵和索引alter table usersadd constraint pk primary key (ID;12.為已經(jīng)創(chuàng)建users表添加外鍵alter table usersadd constraint fk_roleid foreign key (roleidre

13、ferences role(role_id on delete cascad; -下邊寫主表的列on delete cascad是創(chuàng)建級(jí)聯(lián)13.把兩個(gè)列連接起來select concat(name,id from 表名; -把name和id連接起來14.截取字符串select column(name,'李' from 表名; -把name中的李去掉15.運(yùn)行事務(wù)之前必須寫set serveroutput on; -打開輸入輸出(不寫的話,打印不出信息16.while的應(yīng)用declare -聲明部分ccc number:=1; -復(fù)職a number:=0;begin -事務(wù)的開

14、始while ccc<=100 loop -循環(huán)if(ccc mod 3=0 then -條件dbms_output.put_line(ccc|',' -打印顯示a:=a+ccc;end if; -結(jié)束ifccc:=ccc+1;end loop; -結(jié)束循環(huán)dbms_output.put_line(a;end; -結(jié)束事務(wù)/17.select into 的用法 -只能處理一行結(jié)果集declarename varchar(30;beginselect username into namefrom userswhere id=2;dbms_output.put_line(&#

15、39;姓名為:'|name;end;%18.利用%rowtype屬性可以在運(yùn)行時(shí)方便的聲明記錄變量和其他結(jié)構(gòu)Set serveroutput on;Declareutype users%rowtype;BeginSelect * into utype from users where id=20;Dbms_output.put_line('姓名'| utype.username;Dbms_output.put_line('生日'| utype.brithday;end;/ -%rowtype想當(dāng)于復(fù)制一個(gè)表19.游標(biāo)的定義和使用DeclareCursor

16、 ucur is select * from users; -聲明游標(biāo)Us users%rowtype;-定義與游標(biāo)想匹配的變量BeginOpen ucur;-打開游標(biāo)Fetch ucur into us;While ucur %found loop -使用循環(huán)遍歷游標(biāo)的查詢結(jié)果Dbms_output.put_line('姓名:'|us.username|'生日'|us.brithday;Fetch ucur into us;End loop;Close ucur; -關(guān)閉游標(biāo)End;=%found在前一條的fetch語(yǔ)句至少對(duì)應(yīng)數(shù)據(jù)庫(kù)的一行時(shí),%found屬性

17、值為true,否則為false;% notfound 在前一條fetch語(yǔ)句沒有對(duì)應(yīng)的數(shù)據(jù)庫(kù)行時(shí),%notfound屬性值為true,否則為false;%isopen 在游標(biāo)打開時(shí)%isopen屬性值為true;否則為false;%rowcount顯示迄今為止從顯示游標(biāo)中取出的行數(shù)20.刪除drop tablespace 空間名 including contents; -刪除表空間和里面的內(nèi)容drop table 表名 -刪除表drop user 用戶名 -刪除用戶查看有哪些表空間:Select tablespace_name from dba_tablespaces;查看某個(gè)表屬于哪個(gè)表空間

18、:Select tablespace_name from tabs where table_name=表名;在指定的表空間創(chuàng)建表:create table(. tablespace users;查詢前N條數(shù)據(jù):select * from 表名 where rownum<=N;Oracle中創(chuàng)建一個(gè)數(shù)據(jù)庫(kù):打開Database Configuration Assistant,可以通過視圖創(chuàng)建數(shù)據(jù)庫(kù)。創(chuàng)建成功后,系統(tǒng)服務(wù)里面會(huì)多一個(gè)OracleServer 數(shù)據(jù)庫(kù)名的服務(wù)項(xiàng)。查看所有表空間大小:select * from dba_free_space;問題:user lacks create session privilege logon deniedgrant create session to the_user;刪除用戶:Drop user 用戶名 cascade;導(dǎo)入數(shù)據(jù)庫(kù)對(duì)象:IMP