oracle常用命令基礎

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ù)時若沒有指定hiredate,salary的話則會取默認值數(shù)據(jù)字典:Dba-所有方案包含的對象信息All-用戶可以訪問的對象信息User-用戶方案的對象信息Select * from user_tables;Select * from all_tables;約束:域完整性約束:not null check實體完整性約束: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;實例(創(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;查看表結構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 -空間的自動增長的值是10Mpermanent online; -永久使用3.創(chuàng)建用戶creat

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

11、用戶授予權限grant connect,resource to shi;-給shi用戶授予所有權限Grant dba to shi;-給shi 用戶授予DBA權限grant create table to shi; -給shi用戶授予創(chuàng)建表的權限7.select table_name from user_tables; 察看當前用戶下的所有表8.select tablespace_name from user_tablespaces; 察看當前用戶下的表空間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 -最小擴展值maxextents unlimited -最大擴展值;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)建級聯(lián)13.把兩個列連接起來select concat(name,id from 表名; -把name和id連接起來14.截取字符串select column(name,'李' from 表名; -把name中的李去掉15.運行事務之前必須寫set serveroutput on; -打開輸入輸出(不寫的話,打印不出信息16.while的應用declare -聲明部分ccc number:=1; -復職a number:=0;begin -事務的開

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

15、39;姓名為:'|name;end;%18.利用%rowtype屬性可以在運行時方便的聲明記錄變量和其他結構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想當于復制一個表19.游標的定義和使用DeclareCursor

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

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

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)建一個數(shù)據(jù)庫:打開Database Configuration Assistant,可以通過視圖創(chuàng)建數(shù)據(jù)庫。創(chuàng)建成功后,系統(tǒng)服務里面會多一個OracleServer 數(shù)據(jù)庫名的服務項。查看所有表空間大小:select * from dba_free_space;問題:user lacks create session privilege logon deniedgrant create session to the_user;刪除用戶:Drop user 用戶名 cascade;導入數(shù)據(jù)庫對象:IMP