3年前oracle命令行笔记 到现在还常用。

数据库|mysql教程

3年前,oracle,命令行,笔记,现在,常用,col,列名

数据库-mysql教程

h网站自动采集源码,ubuntu ssl.h,谷歌搜索爬虫推荐,vc php,seo密码pdflzw

col 列名 for(mat) 格式 ：可对列进行格式化（a100 是针对varchar 的格式，显示100个字符，可以a60,a80），例如：col file_name format a100;col 列名：显示当前列的格式clear column:清除所有列的格式//主機名HOST HOSTNAME;//幫助HELP INDEX;//顯示當前環

仿hao123源码 带后台,vscode配置pyqt,诺基亚ubuntu,指定tomcat java,sqlite打不开了,手机端tab切换插件,字节跳动前端用啥框架,爬虫浏览但不爬取,php 匹配图片,孝感seo技术,c 模拟微信登录网站,网页客户端制作软件,系统界面展示模板lzw

微商实用工具源码,ubuntu tty7,爬虫request汉字乱码,php面试常见,seo链接内容lzw

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 SYSDBASELECT * FROM V$VERSION;//查看全局服務名SELECT GLOBAL_NAME FROM GLOBAL_NAME;//数据库名SELECT NAME FROM V$DATABASE;SHOW PARAMETER DB//实例名SELECT INSTANCE_NAME FROM V$INSTANCESHOW 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 | lsnrctlstart listener关闭监听器 lsnrctl stop listener | 显示监听器状态 lsnrctlstatus listener//查看更详细监听器services//测试客户端与服务器连接tnsping myorcl//登陆1 sqlplus2 system@myorcladmin//检查网络故障ping 127.0.0.1//sql;结束/ 新一行结束beginend / 新行执行 在sqlplus命令中执行操作系统命令host dir d:\关闭plusexit or quitor ctrl+z//连接数据库CONNECT system/admin@myorcl//退出数据库DISCONNECT//导入sql文件@F:\banksystem.sql//配置tomcat 环境变量catalina_home=H:\Tomcat 6.0tomcat_home=H:\Tomcat 6.0classpath=%tomcat_home%\lib\servlet-api.jar;tomcat_home%\lib\jsp-api.jar//创建表空间CREATE TABLESPACE userdbDATATILE F:\oracl tool\ //切换用户sqlplussystemadminconn scott/admin --切换show user;//断开当前数据库disc scott;//修改用户密码passw//运行sql脚本start e:\a.sql 或者 @ e:\a.sql//编辑sqledit 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_USERSWHERE 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_SPACEGROUP BY TABLESPACE_NAME;//創建臨時表空間CREATE TEMPORARY TABLESPACE USER_TEMPTEMPFILE F:\DATA_TEMP.DBFSIZE 50M AUTOEXTEND ONNEXT 50M MAXSIZE 10280MEXTENT MANAGEMENT LOCAL; //創建表空間CREATE TABLESPACE USER_DATADATAFILE F:\USER_DATA.DBFSIZE 50MAUTOEXTEND ONNEXT 50M MAXSIZE 10280MEXTENT MANAGEMENT LOCAL; //創建用戶并指定表空間CREATE USER MYORCL IDENTIFIED BY ADMINDEFAULT TABLESPACE USER_DATATEMPORARY TABLESPACE USER_TEMP;//CONN SYSTEM/ADMIN//GRANT CONNECT,RESOURCE TO MYORCLoracle调整表空间文件大小1、调整表空间文件大小 alter database datafile /u01/app/oracle/oradata/rmanrep/users01.dbf resize 15m;2、调整表空间文件自动扩展 alter database datafile /u01/app/oracle/oradata/rmanrep/users01.dbfautoextend onnext 20m maxsize 1g;3、新增磁盘 alter database datafile /u01/app/oracle/oradata/rmanrep/users01.dbf autoextend on next 20mmaxsize 1g; alter tablespace userdb add datafile /u03/oradata/users02.dbf size 50m autoextend on next 10m maxsize 200m5、修改表空間名 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、只复制表结构的sqlcreate table b as select * from a where 112、即复制表结构又复制表中数据的sqlcreate table b as select * from a3、复制表的制定字段的sqlcreate table b as select row_id,name,age from a where 11//前提是row_id,name,age都是a表的列4、复制表的指定字段及这些指定字段的数据的sqlcreate 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(-01-01,yyyy-mm-dd hh24:mi:ss) ,30.0);INSERT INTO PERSON (NAME,AGE,BRITHDAY,MONEY) VALUES( 张三,22, to_DATE(&#【本文来自鸿网互联 ()】39;-01-01,yyyy-mm-dd hh24:mi:ss) ,30.0);//对PERSON 的ID进行自增//創建序列CREATE SEQUENCE SEQ_IDSTART WITH 1 --啟始值MAXVAULE 99999999999999 --最大值 99999999999999 MINVAULE 1 --最小值1 NOCYCLE--序列到達最大值以後不再循環CACHE 100; --緩存100個序列值CREATE SEQUENCE SEQ_IDINCREMENT BY 1START 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)BEGINSELECT SEQ_ID_PERSON.NEXTVAL INTO : NEW.ID FROM DUALEND;//創建觸發器CREATE OR REPLACE TRIGGER TRIGGER_PERSON BEFORE INSERT ON PERSON --插入前觸發 FOR EACH ROW --對每行觸發 自動增加 BEGIN SELECT SEQ_ID.NEXTVAL INTO : NEW.ID FROM DUAL; --序列到下個值END;//刪除TRIGGERDROP TRIGGER TRIGGER_PERSON;//禁用和启用触发器alter trigger disable;alter trigger 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( able_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;9iALTER 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.ZHS16GBKAMERICAN_AMERICA.UTF8 英文值AMERICAN_AMERICA;AMERICAN_AMERICA.WE8ISO8859P1SIMPLIFIED 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);// PROCEDURECREATE PROCEDURE PROL(ID NUMBER) AS V NUMBER; BEGIN INSERT INTO CT VALUES(V,HANGSAN,1,1); COMMIT; END; ///execute執行 //EXECUTE PROL(2)//塊執行DECLAREBEGINPROL(2);END //常量定義constantID CONSTANT NUMBER :=30;//無參存儲過程CREATE OR REPLACE PROCEDURE PRO_PERSON(PARAMENT1 VARCHAR2,PARAMENT2 NUMBER,PARAMENT3 DATE,PARAMENT4 FLOAT) ASBEGININSERT INTO PERSON(NAME,AGE,BRITHDAY,MONEY) VALUES(PARAMENT1,PARAMENT2,PARAMENT3,PARAMENT4);END;//返回一個參數CREATE OR REPLACE PROCEDURE PRO_PERSON_RETURE(PARAMENT1 VARCHAR2,PARAMENT2 OUT NUMBER) ASBEGINSELECT 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 ASTYPE 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;selectcreate 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_bodyfrom 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这些数据库对象的定义都可以用类似的方法得到。//TRANSACTIONSAVEPOINT ASELECT * FROM PERSON;UPDATE PERSON SET AGE=30 WHERE NAME=HANGSAN;SELECT * FROM PERSON;ROLLBACK TO ASELECT * FROM PERSON;//TRANSACTIONS NAMESET TRANSACTION NAME INSERT INTO TABLE TEST_TRANSACTION//INSERT INTO DATABASEINSERT INTO TABLE TEST_TRANSACTION VALUES(KKKK,20,TO_DATE(-10-10,YYYY-MM-DD HH24:MI:SS),20.8);// SELECT TRANSACTIONSELECT NAME FROM V$TRANSACTION;//COMMIT;SELECT NAME FROM V$TRANSACTION;//事务一致性(TRANSACTION-LEVEL READ CONSISTENCY)//会话1 窗口1//READ ONLYSET TRANSACTION READ ONLY;//2SELECT * FROM PERSON;//会话2 窗口2INSERT INTO TABLE TEST_TRANSACTION VALUES(KKKK,20,TO_DATE(-10-10,YYYY-MM-DD HH24:MI:SS),20.8);COMMIT;//会话1 窗口1SELECT * FROM PERSON;//COMMIT;SELECT * FROM PERSON;//INTEGRITY CONSTRAINTS 完整性约束//CREATE TABLECREATE TABLE INTEGRITY_TEST(ID INT,NAME VARCHAR2(10));//ALTER COLUMN OF TABLE FOR PRIMARY KEY ALTER TABLE INTEGRITY_TEST ADD PRIMARY KEY(ID,NAME);//DELETEALTER TABLE INTEGRITY_TEST DROP PRIMARY KEY CASCADE;//ALTER COLUMN OF TABLE FOR PRIMARY KEY AND CONSTRAINT NAMEALTER TABLE INTEGRITY_TEST ADD CONSTRAINT PK1 PRIMARY KEY(ID,NAME);//DELETE ALTERT TABLE INTEGRITY_TEST DROP PK1;//CREATE UNIQUE CONSTRAINTDROP TABLE INTEGRITY_TEST; //1 CREATE TABLE INTEGRITY_TEST(ID INT UNIQUE,NAME VARCHAR2(10));//2ALTER TABLE INTEGRITY_TEST ADD UNIQUE(ID,NAME);//DELETEALTER TABLE INTEGRITY_TEST DROP UNIQUE(ID,NAEM);//3ALTER TABLE INTEGRITY_TEST ADD CONSTRAINT PK3 UNIQUE(ID,NAME);//DELETEALTER TABLE INTEGRITY_TEST DROP CONSTRAINT PK3 CASCADE;//CREATE CHECK CONSTRAINTSDROP TABLE INTEGRITY_TEST;//CREATE CHECK CONSTRAINT OF TABLECREATE 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 COLLEGECREATE TABLE COLLEGE(CID INT,CNAME VARCHAR2(10),CADDRESS VARCHAR2(100),CONSTRAINT PK2 PRIMARY KEY(CID));//CONSTRAINTS FOREIGN KEYALTER TABLE STUDENT ADDCONSTRAINT FK1FOREIGN KEY(CID)REFERENCES COLLEGE(CID);//OR CONSTRAINT FOREIGN KEYALTER TABLE STUDENT ADDCONSTRAINT FK1FOREIGN KEY(CID)REFERENCES COLLEGE(CID) ON DELETE CASCADE;//DELETE CONSTRAINTS FOREIGN KEY//IF TABLE IS COLLEGES 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;