설치 후 프로그램 사용가능
SQL> connect system //이 명령어 입력
Enter password: //패스워드는 입력해도 보이지 않으니 입력후 엔테
Connected.-
비밀번호 입력안될때 conn sys as sysdba 명령어 입력 후 비밀번호 없이 엔터치면 접속가능
- ALTER USER SYS IDENTIFIED BY (패스워드);
- 비밀번호 변경 명령어 임
-
프로그램 실행안될때 CMD창으로 이용법
- cmd창 >sqlplus입력
- Enter user-name : system
- Enter password : (패스워드 입력)
-
기본 테이블 확인 명령어 : SQL> SELECT * FROM TAB;
-
출력결과 (길어서 접음)
TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- AQ$DEF$_AQCALL VIEW AQ$DEF$_AQERROR VIEW AQ$_DEF$_AQCALL_F VIEW TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- AQ$_DEF$_AQERROR_F VIEW AQ$_INTERNET_AGENTS TABLE AQ$_INTERNET_AGENT_PRIVS TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- AQ$_QUEUES TABLE AQ$_QUEUE_TABLES TABLE AQ$_SCHEDULES TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- CATALOG SYNONYM COL SYNONYM DEF$_AQCALL TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- DEF$_AQERROR TABLE DEF$_CALLDEST TABLE DEF$_DEFAULTDEST TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- DEF$_DESTINATION TABLE DEF$_ERROR TABLE DEF$_LOB TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- DEF$_ORIGIN TABLE DEF$_PROPAGATOR TABLE DEF$_PUSHED_TRANSACTIONS TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- HELP TABLE LOGMNRC_DBNAME_UID_MAP TABLE LOGMNRC_GSBA TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- LOGMNRC_GSII TABLE LOGMNRC_GTCS TABLE LOGMNRC_GTLO TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- LOGMNRP_CTAS_PART_MAP TABLE LOGMNRT_MDDL$ TABLE LOGMNR_AGE_SPILL$ TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- LOGMNR_ATTRCOL$ TABLE LOGMNR_ATTRIBUTE$ TABLE LOGMNR_CCOL$ TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- LOGMNR_CDEF$ TABLE LOGMNR_COL$ TABLE LOGMNR_COLTYPE$ TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- LOGMNR_DICTIONARY$ TABLE LOGMNR_DICTSTATE$ TABLE LOGMNR_ENC$ TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- LOGMNR_ERROR$ TABLE LOGMNR_FILTER$ TABLE LOGMNR_GLOBAL$ TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- LOGMNR_GT_TAB_INCLUDE$ TABLE LOGMNR_GT_USER_INCLUDE$ TABLE LOGMNR_GT_XID_INCLUDE$ TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- LOGMNR_ICOL$ TABLE LOGMNR_IND$ TABLE LOGMNR_INDCOMPART$ TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- LOGMNR_INDPART$ TABLE LOGMNR_INDSUBPART$ TABLE LOGMNR_INTEGRATED_SPILL$ TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- LOGMNR_KOPM$ TABLE LOGMNR_LOB$ TABLE LOGMNR_LOBFRAG$ TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- LOGMNR_LOG$ TABLE LOGMNR_LOGMNR_BUILDLOG TABLE LOGMNR_NTAB$ TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- LOGMNR_OBJ$ TABLE LOGMNR_OPQTYPE$ TABLE LOGMNR_PARAMETER$ TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- LOGMNR_PARTOBJ$ TABLE LOGMNR_PROCESSED_LOG$ TABLE LOGMNR_PROPS$ TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- LOGMNR_REFCON$ TABLE LOGMNR_RESTART_CKPT$ TABLE LOGMNR_RESTART_CKPT_TXINFO$ TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- LOGMNR_SEED$ TABLE LOGMNR_SESSION$ TABLE LOGMNR_SESSION_ACTIONS$ TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- LOGMNR_SESSION_EVOLVE$ TABLE LOGMNR_SPILL$ TABLE LOGMNR_SUBCOLTYPE$ TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- LOGMNR_TAB$ TABLE LOGMNR_TABCOMPART$ TABLE LOGMNR_TABPART$ TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- LOGMNR_TABSUBPART$ TABLE LOGMNR_TS$ TABLE LOGMNR_TYPE$ TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- LOGMNR_UID$ TABLE LOGMNR_USER$ TABLE LOGSTDBY$APPLY_MILESTONE TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- LOGSTDBY$APPLY_PROGRESS TABLE LOGSTDBY$EDS_TABLES TABLE LOGSTDBY$EVENTS TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- LOGSTDBY$FLASHBACK_SCN TABLE LOGSTDBY$HISTORY TABLE LOGSTDBY$PARAMETERS TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- LOGSTDBY$PLSQL TABLE LOGSTDBY$SCN TABLE LOGSTDBY$SKIP TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- LOGSTDBY$SKIP_SUPPORT TABLE LOGSTDBY$SKIP_TRANSACTION TABLE MVIEW$_ADV_AJG TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- MVIEW$_ADV_BASETABLE TABLE MVIEW$_ADV_CLIQUE TABLE MVIEW$_ADV_ELIGIBLE TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- MVIEW$_ADV_EXCEPTIONS TABLE MVIEW$_ADV_FILTER TABLE MVIEW$_ADV_FILTERINSTANCE TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- MVIEW$_ADV_FJG TABLE MVIEW$_ADV_GC TABLE MVIEW$_ADV_INFO TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- MVIEW$_ADV_JOURNAL TABLE MVIEW$_ADV_LEVEL TABLE MVIEW$_ADV_LOG TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- MVIEW$_ADV_OUTPUT TABLE MVIEW$_ADV_PARAMETERS TABLE MVIEW$_ADV_PLAN TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- MVIEW$_ADV_PRETTY TABLE MVIEW$_ADV_ROLLUP TABLE MVIEW$_ADV_SQLDEPEND TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- MVIEW$_ADV_TEMP TABLE MVIEW$_ADV_WORKLOAD TABLE MVIEW_EVALUATIONS VIEW TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- MVIEW_EXCEPTIONS VIEW MVIEW_FILTER VIEW MVIEW_FILTERINSTANCE VIEW TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- MVIEW_LOG VIEW MVIEW_RECOMMENDATIONS VIEW MVIEW_WORKLOAD VIEW TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- OL$ TABLE OL$HINTS TABLE OL$NODES TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- PRODUCT_PRIVS VIEW PRODUCT_USER_PROFILE SYNONYM PUBLICSYN SYNONYM TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- REPCAT$_AUDIT_ATTRIBUTE TABLE REPCAT$_AUDIT_COLUMN TABLE REPCAT$_COLUMN_GROUP TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- REPCAT$_CONFLICT TABLE REPCAT$_DDL TABLE REPCAT$_EXCEPTIONS TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- REPCAT$_EXTENSION TABLE REPCAT$_FLAVORS TABLE REPCAT$_FLAVOR_OBJECTS TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- REPCAT$_GENERATED TABLE REPCAT$_GROUPED_COLUMN TABLE REPCAT$_INSTANTIATION_DDL TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- REPCAT$_KEY_COLUMNS TABLE REPCAT$_OBJECT_PARMS TABLE REPCAT$_OBJECT_TYPES TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- REPCAT$_PARAMETER_COLUMN TABLE REPCAT$_PRIORITY TABLE REPCAT$_PRIORITY_GROUP TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- REPCAT$_REFRESH_TEMPLATES TABLE REPCAT$_REPCAT TABLE REPCAT$_REPCATLOG TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- REPCAT$_REPCOLUMN TABLE REPCAT$_REPGROUP_PRIVS TABLE REPCAT$_REPOBJECT TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- REPCAT$_REPPROP TABLE REPCAT$_REPSCHEMA TABLE REPCAT$_RESOLUTION TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- REPCAT$_RESOLUTION_METHOD TABLE REPCAT$_RESOLUTION_STATISTICS TABLE REPCAT$_RESOL_STATS_CONTROL TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- REPCAT$_RUNTIME_PARMS TABLE REPCAT$_SITES_NEW TABLE REPCAT$_SITE_OBJECTS TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- REPCAT$_SNAPGROUP TABLE REPCAT$_TEMPLATE_OBJECTS TABLE REPCAT$_TEMPLATE_PARMS TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- REPCAT$_TEMPLATE_REFGROUPS TABLE REPCAT$_TEMPLATE_SITES TABLE REPCAT$_TEMPLATE_STATUS TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- REPCAT$_TEMPLATE_TARGETS TABLE REPCAT$_TEMPLATE_TYPES TABLE REPCAT$_USER_AUTHORIZATIONS TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- REPCAT$_USER_PARM_VALUES TABLE SQLPLUS_PRODUCT_PROFILE TABLE SYSCATALOG SYNONYM TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- SYSFILES SYNONYM TAB SYNONYM TABQUOTAS SYNONYM 174 rows selected.안에 기본적으로 내장된 표를 확인할수 있다.
SQL Developer
DB창을 텍스트 창이 아니라 그래픽으로 볼수있게 해주는 도구
유저를 추가하자
워크시트에 명령어 입력
CREATE USER (ID) IDENTIFIED BY (PASSWORD); //CTLR + ENTER
GRANT CONNECT TO (ID); //CTLR + ENTER
GRANT RESOURCE TO (ID); //표를 생성하는 권한을 주는 명령어id와 비밀번호를 입력해주고 컨트롤 엔터를 눌러야 실행됨
GRANT ← id에 접속권한을 주는 명령어
-
블록을 한 뒤 실행해주면 블록한 영역 동시 실행
-
전체 명령어 실행
F5 -
주석은 : -- 이다 참고!
-
계정을 저장하고 이용하자!
오른쪽 키를 눌러 접속하고 워크시트를 열면 그 계정을 사용할수 있다
-
같은 아이디를 성생했을때의 오류메세지
CREATE USER KHUSER IDENTIFIED BY KHUSER 오류 보고 - ORA-01920: user name 'KHUSER' conflicts with another user or role name 01920. 00000 - "user name '%s' conflicts with another user or role name" *Cause: There is already a user or role with that name. *Action: Specify a different user name. //이미 있으니 데이터를 삭제 하거나 //아이디를 바꾸라는 뜻
표 만들기
CREATE TABLE 표이름(
LOVE_ID VARCHAR2(20),
LOVE_PWD VARCHAR2(20),
LOVER_AGE NUMBER,
LOVER_DATE DATE
);- 괄호 안에는 변수명(행이름) 자료형 으로 구성
표 삭제
DROP TABLE 표이름;표의 내용 값 추가하기
INSERT INTO 표이름(카테고리1, 카테고리2, 카테고리3, 카테고리4)
VALUES('user01', 'pass01', 33, SYSDATE);
---각 카테고리를 선언하고 아래에 값을 넣는 방법 ---
--카테고리 선언없이 카테고리 순서대로 넣는 방법--
INSERT INTO LOVER VALUES('user02', 'pass02', 22, SYSDATE);
INSERT INTO LOVER VALUES('user03', 'pass03', 44, SYSDATE);표 겁색하기
SELECT LOVER_ID, LOVER_PWD, LOVER_AGE, LOVER_DATE //선택할 카테고리 선언
FROM 표이름;현재 작성한 표
| LOVER_ID | LOVER_PWD | LOVER_AGE | LOVER_DATE |
|---|---|---|---|
| user01 | pass01 | 33 | 22/07/12 |
| user02 | pass02 | 22 | 22/07/12 |
| user03 | pass03 | 44 | 22/07/12 |
작성한 표의 데이터 일부만 삭제하기
DELETE
FROM 표이름
WHERE 카테고리 = 카테고리의 값;
//
DELETE
FROM LOVER
WHERE LOVER_ID = 'user01';삭제된 표
지정한 값이 있는 줄만 삭제됐다
| LOVER_ID | LOVER_PWD | LOVER_AGE | LOVER_DATE |
|---|---|---|---|
| user02 | pass02 | 22 | 22/07/12 |
| user03 | pass03 | 44 | 22/07/12 |
표 수정하기
UPDATE LOVER
set LOVER_ID = 'user00' ---> 바꿀값 지정
WHERE LOVER_ID = 'user01'; -->어디를 바꿀지 지정| LOVER_ID | LOVER_PWD | LOVER_AGE | LOVER_DATE |
|---|---|---|---|
| user02 | pass02 | 22 | 22/07/12 |
| user03 | pass03 | 44 | 22/07/12 |
| user00 | pass01 | 33 | 22/07/12 |
표를 텍스트형식으로 조회
desc 표이름;
----출력창----------
이름 널? 유형
------ -- ------------
DEPTNO NUMBER
DNAME VARCHAR2(14)
LOC VARCHAR2(13)유저 추가 정리
create user abc identified by ab; //1
alter user abc identified by abc; //2
grant connect to abc; //3
grant resource to abc; //4
drop user abc; //5- abc라는 아디디를 생성한다 비밀번호는 ab
- abc라는 아이디의 비밀번호를 abc로 수정한다
- abc가 접속할 권한을 준다
- abc가 리소스를 만들수있는 (표같은것) 권한을 준다
- abc 유저를 삭제한다
- 이 모든건 system관리자 아이디로만 할수있다.
hello world