# DCL ( Data Control Language )
데이터제어어( GRANT, REVOKE )
즉, 권한에 따라데이터들을 제어할 자격을 주는것
# TCL ( Transaction Control Language ) ( DCL에 속하여 DCL로부르기도함 )
트랜잭션 제어어( COMMIT, ROLLBACK, SAVEPOINT )
# DML ( Data Manipulation Language )
데이터조작어( SELECT, INSERT, UPDATE, DELETE )
# DDL (Data Definition Language )
데이터정의( CREATE, ALTER, DROP, RENAME 등등 )
# DDL (Data Definition Language )
데이터정의( CREATE, ALTER, DROP, RENAME 등등 )
데이터베이스객체(테이블, 뷰, 인덱스 등등) 의 구조를 정의
CREATE : 데이터베이스객체 생성
DROP : 객체 삭제
ALTER : 기존에 존재하는 DB객체를 재정의
# 테이블 생성
create table member ( //DDL임... data dictionary에 기록
id varchar2(12)
,name varchar2(30) default 'not' --->값이없으면'not'
,email varchar2(30)
,sal number(15) default 0 --->값이없으면 0
,ssn char(13)
,age number(3) -- 정수자리 3자리
,income number(12,2) -- 실수자리12자리중 2개는 소수자리(정수10)
,regDate date
);
-->varchar2(10): 길이가 10 인 가변길이 문자타입.최대4KB
( varchar2 는 오라클사에서만있음... ANSI표준따르는것도있음....)
-->char(13): 길이가(13)인 고정길이 문자타입 최대2000
-->number(12,2): 38까지 유효한 부동 소수점 숫자타입. 전체12중소숫점2까지가능
-->date:날짜1
------>>>>char(3)->a를 입력하면 "_ _ a"로 저장되고
varchar2(3)->a를 입력하면"a"가 저장됨
# 서브쿼리로 테이블 생성
조회된 데이터까지 삽입
CREATE TABLE HISTORY AS
SELECT ID, LAST_NAME, USERID
FROM S_EMP
WHERE DEPT_ID=41; -- 전체복사하려면 SELECT 에 * 주고 WHERE삭제하면됨
테이블 구조만 복사할경우(데이터포함X)
CREATE TABLE HISTORY AS
SELECT ID, LAST_NAME, USERID -- * 가능
FROM S_EMP
WHERE 1=0;
1=0은 false이므로 레코드 없는 테이블만 만들어짐
# 테이블 삭제
DROP TABLE 테이블명;
제약조건도 전부 삭제하고 싶을때는
DROP TABLE 테이블명 CASCADE CONSTRAINTS
# 테이블 변경( 컬럼 추가, 삭제, 변경 )
컬럼추가
ALTER TABE 테이블명 ADD 컬렴명 속성
ALTER TABLE MEMBER ADD AGE VARCHAR2(30) NOT NULL
컬럼수정(제약조건) ( 제약조건명 없을땐 어케하나??? )
ALTER TABLE 테이블명 MODIFY 컬럼명 CONSTRAINT 제약조건명 NOT NULL;
컬럼수정(컬럼명)
(MSSQL에는 SP_RENAME프로시저가 있고 오라클은 RENAME이라는 키워드를 이용한다 )
ALTER TABLE 테이블명 RENAME COLUMN 기존컬럼명 TO 새로운컬럼명
컬럼수정(데이터타입)
ALTER TABLE 테이블명 MODIFY( 컬럼명 데이터타입 )
ALTER TABLE EMP MODIFY( EMP_CODE VARCHAR2(40) )
컬럼삭제
ALTER TABLE 테이블명 DROP COLUMN 칼럼명
# 제약조건-->데이터의 무결성을 보장하기위해 정의
(테이블에 대한 행추가,갱신,삭제때마다 관련 제약조건을 확인하고 적용한다)
(테이블생성시 함께정의하거나 테이블생성후 따로부가될수있음.)
*제약조건의 종류
NOT NULL : 칼럼이 NULL값을 가질수 없음을 명시(컬럼레벨에서만 가능)
UNIQUE : 테이블의 모든행에서 고유한 값을 가져야만 하는 컬럼
컬럼레벨, 테이블레벨(컬럼이 조합된경우) 둘다 가능
EX)주민번호
PRIMARY KEY : 테이블의 각 행을 유일하게 식별할 수 있는 컬럼
컬럼레벨, 테이블레벨
FOREIGN KEY : 다른테이블의 PK나 UNIQUE컬럼을 참조하는 칼럼을 명시
CHECK : 모든행에 반드시 참이어야 하는 조건을 명시
(조건목록중에 하나의 값이어야함)
DOMAIN제약조건? : 값의범위를 정해줌??
하나의 테이블에는 하나의 PK만 선언가능한데 다른컬럼에도 중복 방지하려면 UNIQUE를사용
단, PK는 NULL값을 허용안하지만 UNIQUE는 중복을 방지하는것이지 NULL을 막지는 않음
PK가 될수 있는 후보를 캔디데이트(아이디,이메일,주민번호) 라고 부르고 그중에서 뽑힌놈이 PK
탈락한넘은 UNIQUE등으로 정함
PK는 한테이블에 하나뿐인데 테이블레벨에서 두컬럼을 PK를 줄 수 있다.
그래서 두개의 조합이 PK가됨
*제약조건 정의레벨
1. 컬럼레벨(컬럼바로뒤에생성)
컬럼정의하면서 그 컬럼에 대한 모든 무결성 제약조건을 함께 정의
( 특히 NOT NULL은 반드시 컬럼레벨에서 지정 )
2. 테이블레벨(컬럼정의후 맨뒤에 생성)
하나 이상의 칼럼에 대한 동일한 제약조건일 경우 사용한다.
( NOT NULL제외한 거의모든 조건도 가능 )
기본형식
1.제약조건명 명시
컬럼정의뒤에 CONSTRAINT 제약조건명 제약조건유형
2.제약조건명 미명시 (CONSTRAINT를 없애고 제약조건유형만 주면됨)
(시스템 내부적으로 SYS_C34 이런식으로 저장됨 )
컬럼정의뒤에 제약조건유형
3. 테이블레벨(4)
CONSTRAINT 제약조건명 제약조건유형(적용할 컬럼명)
EX)
createCREATE
CREATE TABLE MEMBER(
ID VARCHAR2(12)
,NAME VARCHAR2(30)
,EMAIL VARCHAR2(30) CONSTRAINT NN_MEMBER_EMAIL NOT NULL---(1)
,SSN CHAR(13) NOT NULL CONSTRAINT NN_MEMBER_SSN UNIQUE---(2)
,INCOME NUMBER(12,2)
,REGDATE DATE
,GRADE CHAR(1) CHECK ( GRADE IN('A','B','C') )--(3)
,GRADE1 CHAR(1) CONSTRAINTS GRADE_CK CHECK ( GRADE IN('A','B','C') )
,CONSTRAINT PK_MEMBER_ID PRIMARY KEY(ID)---(테이블레벨)
);
(3)번은 이름명시없는 체크제약조건
그밑은 제약조건이름있는 체크 제약조건
두개다 A,B,C중 하나의 값이 INSERT, UPDATE되어야함
*제약조건 추가, 삭제 , 조회
추가
ALTER TABLE MEMBER ADD CONSTRAINT MEMBER_ID_PK PRIMARY KEY(ID);
삭제
ALTER TABLE MEMBER DROP PRIMARY KEY CASCADE;
ALTER TABLE MEMBER DROP CONSTRAINT MEMBER_ID_PK CASCADE;
--- CASCADE는 제약조건도 함께 삭제한다는뜻
조회
SELECT A.UNIQUENESS, B.* FROM ALL_INDEXES A, ALL_IND_COLUMNS B
WHERE A.INDEX_NAME = B.INDEX_NAME
AND A.TABLE_NAME = 테이블명;
#제약조건 비활성화/활성화 : 작업시 제약조건을 없애야 작업이 편할수있는데 그때는 임시로 제약조건을 비활성화
DISABLE...../ ENABLE
#제약조건관련 DICTIONARY
- user_constraints
- user_cons_columns
SELECT * FROM user_constraints
SELECT * FROM user_cons_columns
# DML ( Data Manipulation Language )
데이터조작어( SELECT, INSERT, UPDATE, DELETE )
# 데이터 입력 (INSERT)
모든컬럼에 입력할경우
insert into member values(
'hong' //한글 한글자는 2byte
,'gildong'
,'aa@aa','123456'
,'13','33'
,'2011/02/11'
);
특정 컬럼만 데이터 입력할경우
insert into member(id,name,email,ssn,age) values(
'hong'
,'gildong'
,'aa@aa'
,'123456'
,13
);
# 데이터 변경 (UPDATE)
UPDATE MEMBER SET
NAME = 'ABC'
,TEL = '222'
WHERE ID = 2013
WHERE절생략시 해당 테이블 모든것이 해당값으로 변경!! 유의할것!!
그리고 UPDATE시 제약조건을 갖는 컬럼을 제약조건에 벗어나게 수정할시 에러남!! 당연한가...
# 데이터 삭제
DELTE FROM MEMBER
WHERE ID = 2013
이것역시 WHERE주의할것
# TCL ( Transaction Control Language )
( DCL에 속하여 DCL로부르기도함 )
트랜잭션 제어어( COMMIT, ROLLBACK, SAVEPOINT )
논리적으로 한번에 수행되어야 하는 작업의 묶음
( 한번 실행시 전부 실행시키던지 아니면 전혀 실행하지 않아야함. ALL OR NOTHING )
COMMIT : 저장되지않은 모든 변경사항을 확정(저장)
COMMIT이전의 모든 SAVEPOINT는 삭제됨
ROOLBACK : 저장되지않은 모든 사항을 취소(삭제)
SAVEPOINT : 일시저장??개념
EX)
CREATE TABLE LOG(
NO NUMBER
,CONTENT VARCHAR2(20)
);
INSERT INTO LOG VALUES( 1, '입금');
SAVEPOINT S1;
INSERT INTO LOG VALUES(1, '100');
INSERT INTO LOG VALUES(1, '200');
ROLLBACK TO S1; ---- 롤백시 세이브 포인트명을 지정하면 그앞까지만 롤백됨
ROLLBACK; ---- 롤백만할경우 이전 COMMIT있는곳까지 되돌림
# 추가적으로
*위에만든테이블이 sqlplus툴은 자동적으로 user_objects에 생성됨
*select id,name,price from student 는 사실 data dictionary를 검색함
*varchar2와 char의 차이...
varcha2(8) : 'abcd' 가변길이로서 길이를 하나하나계산해서 읽음(어떻게보면 속도 조금느림)
char(8) : 'abcd' 고정길이로서 디스크암이 딱 8개 읽음(메모리?낭비?)
*순서
1.문법확인
2.타입확인
3.제약조건을 data dictionary에서 확인
*위에 제약조건이름을 줄때 제약조건이름과같은이름으로 인덱스를 물리적으로 만듬...
*오라클은 ROWID, ROWNUM 등의 가짜(pseude) 칼럼을 제공함
1. rowid : 오라클db에 저장되는 모든행은 유일한 rowid값을 가짐
select empno, ename, ROWID , ROWNUM from emp where empno=7369;
rowid라는 고유의값이출력됨
2. rownum : where 조건을 만족하는 행의 순서값(행고유의값이아님...변함)
order by에의해서는 값이 변하지않지만 where조건을만족하는
새로운데이터에대해서 다시 순서가 매겨짐
( 추후에 알게된정보로... order by 에 의해서 값이 변하지 않지만! pk등 인덱스를 타는컬럼은
where절후 바로 rownum이 할당되는것이 아니라 order by 이후 새로 할당됨!!
이말은 index를 설정한 컬럼으로 order by 했을경우 rownum값이 다시 변함!!!)
정리하자면 처리순서
1. From / Where절 처리
2. rownum할당
3. select적용
4. group by 적용
5. order by 적용
(6. order by에 index컬럼이있을경우 다시 rownum할당???)
A)select rownum, name, region_id from s_dept;
=>where region_id=1 추가시
B)select rownum, name from s_dept;
A와B의 rownum이 다름!!!
*인덱스를 이용해야 속도가 빠름...
데이터 수정시 인덱스로 수정해야함....=> 느려짐
인덱스는 순전히 검색속도를 빠르게 하기위해...인덱스 사용시 하드용량이 더차지함.
순전히 정보를 검색,조회하는작업이많고 중요하기때문에 인덱스 필요!
*create unique index idx_emp_ename on emp(ename);
create index idx_emp_ename on emp(ename);
'DB > 오라클' 카테고리의 다른 글
| 계층형 쿼리 (0) | 2019.04.03 |
|---|---|
| 오라클 기초 (0) | 2019.04.03 |
| SQL시스템과 오라클 SQL PLUS (0) | 2019.04.03 |
| 컬럼값에 따라 다른 테이블을 조인 (0) | 2019.04.03 |
| 오라클 서버 동시접속자수 제한을 하는 parallel_max_servers 변수 증가 시키기 (0) | 2019.04.03 |