col 列名 for(mat) 格式 ：可对列进行格式化（a100 是针对varchar 的格式，显示100个字符，可以a60,a80），例如：col file_name format a100;col 列名：显示当前列的格式clear column:清除所有列的格式//主機名HOST HOSTNAME;//幫助HELP INDEX;//顯示當前環
col 列名 for(mat) 格式 ：可对列进行格式化（a100 是针对varchar 的格式，显示100个字符，可以a60,a80）， 例如：col file_name format a100; col 列名：显示当前列的格式 clear column:清除所有列的格式 //主機名 HOST HOSTNAME; //幫助 HELP INDEX; //顯示當前環境變量值 SHOW ALL //顯示當前錯誤 SHOW ERR; //顯示數據庫版本 show rel //查看oracle版本 CONN /AS SYSDBA SELECT * FROM V$VERSION; //查看全局服務名 SELECT GLOBAL_NAME FROM GLOBAL_NAME; //数据库名 SELECT NAME FROM V$DATABASE; SHOW PARAMETER DB //实例名 SELECT INSTANCE_NAME FROM V$INSTANCE SHOW PARAMETER INSTANCE //数据库域名 SELECT VALUE FROM V$PARAMETER WHERE NAME=&＃39;DB_DOMAIN&＃39;; SHOW PARAMETER DOMAIN; //数据库服务名 SELECT VALUE FROM V$PARAMETER WHERE NAME=&＃39;SERVICE_NAME&＃39;; SHOW PARAMETER SERVICE_NAME; 登陆监听器 lsnrctl 启动监听器 lsnrctl start listener | lsnrctl start listener 关闭监听器 lsnrctl stop listener | 显示监听器状态 lsnrctl status listener //查看更详细监听器 services //测试客户端与服务器连接 tnsping myorcl //登陆 1 sqlplus 2 system@myorcl admin //检查网络故障 ping 127.0.0.1 //sql ;结束 / 新一行结束 begin end / 新行执行 在sqlplus命令中执行操作系统命令 host dir d:\ 关闭plus exit or quit or ctrl+z //连接数据库 CONNECT system/admin@myorcl //退出数据库 DISCONNECT //导入sql文件 @F:\banksystem.sql //配置tomcat 环境变量 catalina_home=H:\Tomcat 6.0 tomcat_home=H:\Tomcat 6.0 classpath=%tomcat_home%\lib\servlet-api.jar;tomcat_home%\lib\jsp-api.jar //创建表空间 CREATE TABLESPACE userdb DATATILE &＃39;F:\oracl tool\&＃39; //切换用户 sqlplus system admin conn scott/admin --切换 show user; //断开当前数据库 disc scott; //修改用户密码 passw //运行sql脚本 start e:\a.sql 或者 @ e:\a.sql //编辑sql edit e:\a.sql; //截取屏幕select 数据到指定文件 spool d:\b.sql; select * from emp; spool off; //sql语句的 &＃39;&a&＃39; 变量值替换 select * from emp where ename=&＃39;&a&＃39;; //调整行的顯示 set linesize 90; select * from emp; //行显示 show linesize; //分页 set pagesize 5; select * from emp; //创建数据库用户 CREATE USER nokey IDENTIFIED BY orcl;//orcl是密码 //修改其他数据库用户密码 ALTER USER nokey //删除用户 drop user nokey; 如果nokey 里面存在表，那么需要带一个参数cascade才能删除 //为表空间指定用户 alter user demo default tablespace DEMOTSDATA; //给指定用户授权( 表操作 connect ) grant connect to nokey; //给指定用户授权( 表空间操作 resource ) grant resource to nokey; //操作數據庫 Grant DBA To nokey; //查看表结构 desc test; //赋予对象权限查看表 grant select on emp to nokey; //查看被赋权的表 select * from scott.emp;//soctt 表示角色 //赋予对象权限修改表 grant update on emp to nokey; //把emp访问权限都给test (修改/删除delete \查询 \添加 soctt的emp 表 ) grant all on emp to test //收回nokey对emp表的权限 revoke select on emp from nokey; //权限维护 //nokey 查询scott emp表的权限传递给其他用户 // 查看所有用戶 select username from dba_users; //刪除用戶 drop user myorcl cascade; //刪除表空間及數據庫文件（刪除臨時表空間和數據庫文件也是一樣的） drop tablespace tablespace_name including contents and datafiles; DROP TABLESPACE TABLESPACE_NAME INCLUDING CONTENTS AND DATAFILES; //查看所有表 SELECT TABLE_NAME FROM ALL_TABLES //查看當前用戶所有表 SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER=&＃39;MYORCL&＃39;; //查看前用戶的表空间 SELECT USERNAME,DEFAULT_TABLESPACE DEFSPACE FROM DBA_USERS WHERE USERNAME=&＃39;MYORCL&＃39;; SELECT USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE FROM DBA_USERS WHERE USERNAME=&＃39;MYORCL&＃39;; //查詢所有表空間 select tablespace_name from dba_tablespaces; //查詢表空間分佈的用戶信息 select tablespace_name,owner,sum(bytes) from dba_segments group by tablespace_name,owner; //查詢當前用戶所有表空間 SELECT TABLESPACE_NAME FROM USER_TABLESPACES; //查看表空間大小 SELECT A.NAME,SUM(B.BYTES)/1024/1024 FROM V$TABLESPACE A,V$DATAFILE B WHERE A.TS#=B.TS# GROUP BY A.NAME; //查詢表空間空閒空間 SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME; //創建臨時表空間 CREATE TEMPORARY TABLESPACE USER_TEMP TEMPFILE &＃39;F:\DATA_TEMP.DBF&＃39; SIZE 50M AUTOEXTEND ON NEXT 50M MAXSIZE 10280M EXTENT MANAGEMENT LOCAL; //創建表空間 CREATE TABLESPACE USER_DATA DATAFILE &＃39;F:\USER_DATA.DBF&＃39; SIZE 50M AUTOEXTEND ON NEXT 50M MAXSIZE 10280M EXTENT MANAGEMENT LOCAL; //創建用戶并指定表空間 CREATE USER MYORCL IDENTIFIED BY ADMIN DEFAULT TABLESPACE USER_DATA TEMPORARY TABLESPACE USER_TEMP; //CONN SYSTEM/ADMIN //GRANT CONNECT,RESOURCE TO MYORCL oracle调整表空间文件大小 1、调整表空间文件大小 alter database datafile &＃39;/u01/app/oracle/oradata/rmanrep/users01.dbf&＃39; resize 15m; 2、调整表空间文件自动扩展 alter database datafile &＃39;/u01/app/oracle/oradata/rmanrep/users01.dbf&＃39; autoextend on next 20m maxsize 1g; 3、新增磁盘 alter database datafile &＃39;/u01/app/oracle/oradata/rmanrep/users01.dbf&＃39; autoextend on next 20m maxsize 1g; alter tablespace userdb add datafile &＃39;/u03/oradata/users02.dbf&＃39; size 50m autoextend on next 10m maxsize 200m 5、修改表空間名 alter tablespace userdb rename to userdb2 //增加列 ALTER TABLE emp01 ADD eno NUMBER(4); //修改列定义 ALTER TABLE emp01 MODIFY job VARCHAR2(15) ; //删除列 ALTER TABLE emp01 DROP COLUMN dno; //修改列名 ALTER TABLE emp01 RENAME COLUMN eno TO empno; //修改表名 RENAME emp01 TO employee; //修改默认表空间到指定表空间 ALTER DATABASE DEFAULT TABLESPACE USER_DATA; //增加注释 COMMENT ON TABLE employee IS &＃39;存放雇员信息&＃39;; COMMENT ON TABLE employee.name IS &＃39;描述雇员姓名&＃39;; 1、只复制表结构的sql create table b as select * from a where 1<>1 2、即复制表结构又复制表中数据的sql create table b as select * from a 3、复制表的制定字段的sql create table b as select row_id,name,age from a where 1<>1//前提是row_id,name,age都是a表的列 4、复制表的指定字段及这些指定字段的数据的sql create table b as select row_id,name,age from a 以上语句虽然能够很容易的根据a表结构复制创建b表，但是a表的索引等却复制不了，需要在b中手动建立。 5、insert into 会将查询结果保存到已经存在的表中 insert into t2(column1, column2, ....) select column1, column2, .... from t1 //SQL插入时间date类型 INSERT INTO PERSON (ID,NAME,AGE,BRITHDAY,MONEY) VALUES( 1,&＃39;张三&＃39;,22, to_DATE(&＃39;2011-01-01&＃39;,&＃39;yyyy-mm-dd hh24:mi:ss&＃39;) ,30.0); INSERT INTO PERSON (NAME,AGE,BRITHDAY,MONEY) VALUES( &＃39;张三&＃39;,22, to_DATE(&＃【本文来自鸿网互联 (http://www.68idc.cn)】39;2011-01-01&＃39;,&＃39;yyyy-mm-dd hh24:mi:ss&＃39;) ,30.0); //对PERSON 的ID进行自增 //創建序列 CREATE SEQUENCE SEQ_ID START WITH 1 --啟始值 MAXVAULE 99999999999999 --最大值 99999999999999 MINVAULE 1 --最小值1 NOCYCLE --序列到達最大值以後不再循環 CACHE 100; --緩存100個序列值 CREATE SEQUENCE SEQ_ID INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE; CREATE OR REPLACE TRIGGER TRI_USERBEAN BEFORE INSERT ON USERTEST FOR EACH ROW BEGIN IF(:NEW.ID IS NULL)THEN SELECT SEQ_USERBEAN.NEXTVAL INTO:NEW.ID FROM DUAL; END IF; END; / CREATE OR REPLACE TRIGGER TRI_PERSON BEFORE INSERT ON PERSON FOR EACH ROW WHEN(NEW.ID IS NULL) BEGIN SELECT SEQ_ID_PERSON.NEXTVAL INTO :NEW.ID FROM DUAL END; / CREATE TRIGGER TRI_INS_PERSON BEFORE INSERT ON PERSON FOR EACH ROW WHEN(NEW.ID IS NULL) BEGIN SELECT SEQ_ID_PERSON.NEXTVAL INTO : NEW.ID FROM DUAL END; //創建觸發器 CREATE OR REPLACE TRIGGER TRIGGER_PERSON BEFORE INSERT ON PERSON --插入前觸發 FOR EACH ROW --對每行觸發 自動增加 BEGIN SELECT SEQ_ID.NEXTVAL INTO : NEW.ID FROM DUAL; --序列到下個值 END; //刪除TRIGGER DROP TRIGGER TRIGGER_PERSON; //禁用和启用触发器 alter triggerdisable; alter trigger enable; //查看當前用戶當前表觸發器 SELECT * FROM USER_TRIGGERS WHERE TABLE_OWNER=&＃39;MYORCL&＃39; AND TABLE_NAME =UPPER(&＃39;PERSON&＃39;); select * from user_triggers where table_owner = &＃39;xxx&＃39; and table_name = upper(&＃39;table_name&＃39;); //查看这个用户的所有触发器 select * from dba_triggers where owner=用户名； //查看这个用户中所有的外键约束，table_name显示了建立在哪个表上 select * from dba_constraints T where owner=用户名 AND T.CONSTRAINT_TYPE=&＃39;F&＃39;; //清空緩存 10g以上 alter system flush buffer_cache; 9i ALTER SESSION SET EVENTS &＃39;immediate trace name flush_cache&＃39;; // 將中文報錯環境更改為英文 ALTER SESSION SET NLS_LANGUAGE=&＃39;AMERICAN&＃39; // select * from nls_database_parameters; alter database NLS_CHARACTERSET = ZHS16GBK; // 註冊表 NLS_LANG SIMPLIFIED CHINESE_CHINA.ZHS16GBK 中文值 CHINESE_CHINA.ZHS16GBK AMERICAN_AMERICA.UTF8 英文值 AMERICAN_AMERICA; AMERICAN_AMERICA.WE8ISO8859P1 SIMPLIFIED AMERICAN_AMERICA.UTF8 //提示為英文，編碼為中文 AMERICAN_AMERICA.ZHS16GBK //CT TABLE CREATE TABLE CT( ID NUMBER PRIMARY KEY NOT NULL, NAME VARCHAR(10) NOT NULL, AGE NUMBER NOT NULL, DEP NUMBER NOT NULL); // PROCEDURE CREATE PROCEDURE PROL(ID NUMBER) AS V NUMBER; BEGIN INSERT INTO CT VALUES(V,&＃39;ZHANGSAN&＃39;,1,1); COMMIT; END; / //execute執行 //EXECUTE PROL(2) //塊執行 DECLARE BEGIN PROL(2); END //常量定義constant ID CONSTANT NUMBER :=30; //無參存儲過程 CREATE OR REPLACE PROCEDURE PRO_PERSON(PARAMENT1 VARCHAR2,PARAMENT2 NUMBER,PARAMENT3 DATE,PARAMENT4 FLOAT) AS BEGIN INSERT INTO PERSON(NAME,AGE,BRITHDAY,MONEY) VALUES(PARAMENT1,PARAMENT2,PARAMENT3,PARAMENT4); END; //返回一個參數 CREATE OR REPLACE PROCEDURE PRO_PERSON_RETURE(PARAMENT1 VARCHAR2,PARAMENT2 OUT NUMBER) AS BEGIN SELECT AGE INTO PARAMENT2 FROM PERSON WHERE NAME=PARAMENT1; END; //插入 时取得当前ID //第一种 CREATE OR REPLACE PROCEDURE PRO_LAST_ID_PERSON( PARA5 OUT NUMBER,PARA1 VARCHAR2,PARA2 NUMBER,PARA3 DATE,PARA4 FLOAT) AS BEGIN INSERT INTO PERSON(ID,NAME,AGE,BRITHDAY,MONEY) VALUES( SEQ_ID_PERSON.NEXTVAL,PARA1,PARA2,PARA3,PARA4); SELECT SEQ_ID_PERSON.CURRVAL INTO PARA5 FROM DUAL; END; / //第二种 CREATE OR REPLACE PROCEDURE PRO_LAST_ID_PERSON1( PARA5 OUT NUMBER,PARA1 VARCHAR2,PARA2 NUMBER,PARA3 DATE,PARA4 FLOAT) AS BEGIN INSERT INTO PERSON(NAME,AGE,BRITHDAY,MONEY) VALUES(PARA1,PARA2,PARA3,PARA4); SELECT SEQ_ID_PERSON.CURRVAL INTO PARA5 FROM DUAL; END; / //存儲過程返回列表 //造包 CREATE OR REPLACE PACKAGE PERSON_PACKAGE AS TYPE PERSON_CURSOR IS REF CURSOR; PROCEDURE PRO_PERSON_GET(C_REF OUT PERSON_CURSOR); END; / //存儲過程 CREATE OR REPLACE PACKAGE BODY PERSON_PACKAGE AS PROCEDURE PRO_PERSON_GET(C_REF OUT PERSON_CURSOR) IS BEGIN OPEN C_REF FOR SELECT * FROM PERSON; END PRO_PERSON_GET; END PERSON_PACKAGE; / //查看触发器 set long 50000; set heading off; set pagesize 2000; select &＃39;create or replace trigger "&＃39; || trigger_name || &＃39;"&＃39; || chr(10)|| decode( substr( trigger_type, 1, 1 ), &＃39;A&＃39;, &＃39;AFTER&＃39;, &＃39;B&＃39;, &＃39;BEFORE&＃39;, &＃39;I&＃39;, &＃39;INSTEAD OF&＃39; ) || chr(10) || triggering_event || chr(10) || &＃39;ON "&＃39; || table_owner || &＃39;"."&＃39; || table_name || &＃39;"&＃39; || chr(10) || decode( instr( trigger_type, &＃39;EACH ROW&＃39; ), 0, null, &＃39;FOR EACH ROW&＃39; ) || chr(10) , trigger_body from user_triggers; // 查看函数和过程的状态 select object_name,status from user_objects where object_type=&＃39;FUNCTION&＃39;; select object_name,status from user_objects where object_type=&＃39;PROCEDURE&＃39;; SELECT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE=&＃39;TRIGGER&＃39;; // 查看函数和过程的源代码 select text from all_source where owner=user and name=upper(&＃39;名稱&＃39;); SELECT TEXT FROM ALL_SOURCE WHERE TYPE=&＃39;PACKAGE BODY&＃39; AND OWNER=&＃39;SYSTEM&＃39; ORDER BY NAME,LINE; Procedure,Function, Package, Trigger这些数据库对象的定义都可以用类似的方法得到。 //TRANSACTION SAVEPOINT A SELECT * FROM PERSON; UPDATE PERSON SET AGE=30 WHERE NAME=&＃39;ZHANGSAN&＃39;; SELECT * FROM PERSON; ROLLBACK TO A SELECT * FROM PERSON; //TRANSACTION&＃39;S NAME SET TRANSACTION NAME &＃39;INSERT INTO TABLE TEST_TRANSACTION&＃39; //INSERT INTO DATABASE INSERT INTO TABLE TEST_TRANSACTION VALUES(&＃39;KKKK&＃39;,20,TO_DATE(&＃39;2011-10-10&＃39;,&＃39;YYYY-MM-DD HH24:MI:SS&＃39;),20.8); // SELECT TRANSACTION SELECT NAME FROM V$TRANSACTION; //COMMIT; SELECT NAME FROM V$TRANSACTION; //事务一致性(TRANSACTION-LEVEL READ CONSISTENCY) //会话1 窗口1 //READ ONLY SET TRANSACTION READ ONLY; //2 SELECT * FROM PERSON; //会话2 窗口2 INSERT INTO TABLE TEST_TRANSACTION VALUES(&＃39;KKKK&＃39;,20,TO_DATE(&＃39;2011-10-10&＃39;,&＃39;YYYY-MM-DD HH24:MI:SS&＃39;),20.8); COMMIT; //会话1 窗口1 SELECT * FROM PERSON; //COMMIT; SELECT * FROM PERSON; //INTEGRITY CONSTRAINTS 完整性约束 //CREATE TABLE CREATE TABLE INTEGRITY_TEST( ID INT, NAME VARCHAR2(10)); //ALTER COLUMN OF TABLE FOR PRIMARY KEY ALTER TABLE INTEGRITY_TEST ADD PRIMARY KEY(ID,NAME); //DELETE ALTER TABLE INTEGRITY_TEST DROP PRIMARY KEY CASCADE; //ALTER COLUMN OF TABLE FOR PRIMARY KEY AND CONSTRAINT NAME ALTER TABLE INTEGRITY_TEST ADD CONSTRAINT PK1 PRIMARY KEY(ID,NAME); //DELETE ALTERT TABLE INTEGRITY_TEST DROP PK1; //CREATE UNIQUE CONSTRAINT DROP TABLE INTEGRITY_TEST; //1 CREATE TABLE INTEGRITY_TEST(ID INT UNIQUE,NAME VARCHAR2(10)); //2 ALTER TABLE INTEGRITY_TEST ADD UNIQUE(ID,NAME); //DELETE ALTER TABLE INTEGRITY_TEST DROP UNIQUE(ID,NAEM); //3 ALTER TABLE INTEGRITY_TEST ADD CONSTRAINT PK3 UNIQUE(ID,NAME); //DELETE ALTER TABLE INTEGRITY_TEST DROP CONSTRAINT PK3 CASCADE; //CREATE CHECK CONSTRAINTS DROP TABLE INTEGRITY_TEST; //CREATE CHECK CONSTRAINT OF TABLE CREATE TABLE INTEGRITY_TEST(ID INT, NAME VARCHAR2(10), NUM NUMBER CONSTRAINT C_CHECK CHECK(NUM BETWEEN 10 AND 20)); //ADD CHECK CONSTRAINT OF TABLE ALTER TABLE INTEGRITY_TEST ADD CONSTRAINT C_CHECK CHECK(NUM BETWEEN 1 AND 20); //DELETE ALTER TABLE INTEGRITY_TEST DROP CONSTRAINT C_CHECK; //ALTER COLUMN VALUES IS NULL OR NOT NULL OF TABLE ALTER TABLE INTEGRITY_TEST MODIFY (ID NULL); ALTER TABLE INTEGRITY_TEST MODIFY (ID NOT NULL); //FOREIGN KEY //CREATE STUDENT CREATE TABLE STUDENT( SID INT, CID INT, NAME VARCHAR2(20), CONSTRAINT PK1 PRIMARY KEY(SID)); //CREATE COLLEGE CREATE TABLE COLLEGE( CID INT, CNAME VARCHAR2(10), CADDRESS VARCHAR2(100), CONSTRAINT PK2 PRIMARY KEY(CID)); //CONSTRAINTS FOREIGN KEY ALTER TABLE STUDENT ADD CONSTRAINT FK1 FOREIGN KEY(CID) REFERENCES COLLEGE(CID); //OR CONSTRAINT FOREIGN KEY ALTER TABLE STUDENT ADD CONSTRAINT FK1 FOREIGN KEY(CID) REFERENCES COLLEGE(CID) ON DELETE CASCADE; //DELETE CONSTRAINTS FOREIGN KEY //IF TABLE IS COLLEGE&＃39;S DELETE THEN ALTER TABLE COLLEGE DROP CONSTRAINT FK1 //OR USEING CASCADE CONSTRAINTS TO DELETE DROP TABLE COLLEGE CASCADE CONSTRAINTS; //ALL TABLES AND ALL CONSTRAINTS SELECT TABLE_NAME,CONSTRAINT_NAME FROM USER_CONSTRAINTS; // 查看表约束 SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME=upper(&＃39;department&＃39;) AND OWNER=USER; //查看值 SELECT COLUMN_NAME,POSITION FROM USER_CONS_COLUMNS WHERE CONSTRAINT_NAME=&＃39;SYS_C0011056&＃39;;
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有 