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='DB_DOMAIN'; SHOW PARAMETER DOMAIN; //数据库服务名 SELECT VALUE FROM V$PARAMETER WHERE NAME='SERVICE_NAME'; 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 'F:\oracl tool\' //切换用户 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语句的 '&a' 变量值替换 select * from emp where ename='&a'; //调整行的顯示 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='MYORCL'; //查看前用戶的表空间 SELECT USERNAME,DEFAULT_TABLESPACE DEFSPACE FROM DBA_USERS WHERE USERNAME='MYORCL'; SELECT USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE FROM DBA_USERS WHERE USERNAME='MYORCL'; //查詢所有表空間 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 'F:\DATA_TEMP.DBF' SIZE 50M AUTOEXTEND ON NEXT 50M MAXSIZE 10280M EXTENT MANAGEMENT LOCAL; //創建表空間 CREATE TABLESPACE USER_DATA DATAFILE 'F:\USER_DATA.DBF' 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 '/u01/app/oracle/oradata/rmanrep/users01.dbf' resize 15m; 2、调整表空间文件自动扩展 alter database datafile '/u01/app/oracle/oradata/rmanrep/users01.dbf' autoextend on next 20m maxsize 1g; 3、新增磁盘 alter database datafile '/u01/app/oracle/oradata/rmanrep/users01.dbf' autoextend on next 20m maxsize 1g; alter tablespace userdb add datafile '/u03/oradata/users02.dbf' 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 '存放雇员信息'; COMMENT ON TABLE employee.name IS '描述雇员姓名'; 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,'张三',22, to_DATE('2011-01-01','yyyy-mm-dd hh24:mi:ss') ,30.0); INSERT INTO PERSON (NAME,AGE,BRITHDAY,MONEY) VALUES( '张三',22, to_DATE('2011-01-01','yyyy-mm-dd hh24:mi:ss') ,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 trigger <trigger_name> disable; alter trigger <trigger_name> enable; //查看當前用戶當前表觸發器 SELECT * FROM USER_TRIGGERS WHERE TABLE_OWNER='MYORCL' AND TABLE_NAME =UPPER('PERSON'); select * from user_triggers where table_owner = 'xxx' and table_name = upper('table_name'); //查看这个用户的所有触发器 select * from dba_triggers where owner=用户名; //查看这个用户中所有的外键约束,table_name显示了建立在哪个表上 select * from dba_constraints T where owner=用户名 AND T.CONSTRAINT_TYPE='F'; //清空緩存 10g以上 alter system flush buffer_cache; 9i ALTER SESSION SET EVENTS 'immediate trace name flush_cache'; // 將中文報錯環境更改為英文 ALTER SESSION SET NLS_LANGUAGE='AMERICAN' // 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,'ZHANGSAN',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 'create or replace trigger "' || trigger_name || '"' || chr(10)|| decode( substr( trigger_type, 1, 1 ), 'A', 'AFTER', 'B', 'BEFORE', 'I', 'INSTEAD OF' ) || chr(10) || triggering_event || chr(10) || 'ON "' || table_owner || '"."' || table_name || '"' || chr(10) || decode( instr( trigger_type, 'EACH ROW' ), 0, null, 'FOR EACH ROW' ) || chr(10) , trigger_body from user_triggers; // 查看函数和过程的状态 select object_name,status from user_objects where object_type='FUNCTION'; select object_name,status from user_objects where object_type='PROCEDURE'; SELECT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE='TRIGGER'; // 查看函数和过程的源代码 select text from all_source where owner=user and name=upper('名稱'); SELECT TEXT FROM ALL_SOURCE WHERE TYPE='PACKAGE BODY' AND OWNER='SYSTEM' ORDER BY NAME,LINE; Procedure,Function, Package, Trigger这些数据库对象的定义都可以用类似的方法得到。 //TRANSACTION SAVEPOINT A SELECT * FROM PERSON; UPDATE PERSON SET AGE=30 WHERE NAME='ZHANGSAN'; SELECT * FROM PERSON; ROLLBACK TO A SELECT * FROM PERSON; //TRANSACTION'S NAME SET TRANSACTION NAME 'INSERT INTO TABLE TEST_TRANSACTION' //INSERT INTO DATABASE INSERT INTO TABLE TEST_TRANSACTION VALUES('KKKK',20,TO_DATE('2011-10-10','YYYY-MM-DD HH24:MI:SS'),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('KKKK',20,TO_DATE('2011-10-10','YYYY-MM-DD HH24:MI:SS'),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'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('department') AND OWNER=USER; //查看值 SELECT COLUMN_NAME,POSITION FROM USER_CONS_COLUMNS WHERE CONSTRAINT_NAME='SYS_C0011056';