1. DML
DML은 테이블에 데이터를 입력, 수정, 삭제할 때 사용하는 명령어이다. DML을 영구히 저장하기 위해서는 반드시 COMMIT을 해주어야 한다.
1. INSERT 문
단일행 INSERT 문
단일행 INSERT 문은 VALUES 절을 포함하며, 한번에 한 행만 입력된다.
INSERT IN TO 테이블명 [칼럼명] VALUES (값1, 값2, ...) 이 때 값이 입력되지 않은 곳은 NULL이 입력되며, PK나 NOT NULL의 경우 오류가 발생한다.
만약 순번과 같이 1씩 추가되는 값을 넣고 싶다면 VALUES에 인라인뷰를 사용해 주면 된다.
INSERT IN TO PLAYER (ID, NAME, TEAM)
VALUES ((SELECT TO_CHAR(MAX(TO_NUMBER(PLAYER_ID)) +1)
FROM PLAYER, '홍길동', 'K06') 다중행 INSERT 문
INSERT문에 서브쿼리를 사용하면, 서브쿼리의 결과를 테이블에 입력할 수 있다. 서브쿼리의 결과가 다중행이면 한번에 여러건이 입력된다. 단, INTO절의 컬럼명 개수와 서브쿼리의 컬럼명 개수가 같아야 한다.
INSERT INTO 테이블명 [(칼럼1, 칼럼2, ...)]
서브쿼리;INSERT INTO (TEAM_ID, REGION_NAME, TEAM_NAME, ORIG_YYYY, STADIUM)
SELECT REPLACE(TEAM_ID, 'K', 'A') AS TEAM, REGION_NAME, - K를 A로 바꾼다.
REGION_NAME || '올스타' AS TEAM_NAME, - 뒤에 올스타를 붙인다.
2019 AS ORIG_YYYY, STADIUM_ID - 2019로 채운다.
FROM TEAM
where REGION_NAME IN ('성남', '인천');2. UPDATE 문
데이터를 수정한다.
update 테이블명 set 수정할 칼럼명1 = 수정할 값
칼럼명2 = 수정할 값2
where 수정할 행을 식별하는 조건식update PLAYER set POSITION = "MF"
where POSSION IS NULL
포지션을 MF로 바꾼다. 단 포지션이 NULL인 값UPDATE문의 SET절에 값 대신 서브쿼리를 사용하면, 서브쿼리의 결과로 값이 수정된다.
[예제] 홈팀 ID가 존재하는 경기장의 지역번호와, 전화번호를 홈팀의 지역번호와 전화번호로 수정한다.
UPDATE STADIUM A
SET(A.DDD, A.TEL) = (SELECT X.DDD, X.TEL
FROM TEAM X
WHERE X.TEAM_ID = A.HOMETEAM_ID)
where EXISTS (select 1 ---- TRUE OR FALSE 판단
from TEAM X
where X.TEAM_ID = A.HOMETEAM_ID); _
위 쿼리는 두번의 팀 테이블에 엑세스가 필요하다. 하지만, MERGE문을 쓰면, 한번만 엑세스 가능하다.
MERGE
INTO STADIYM T
USING TEAM S
ON (T,TEAN_ID = S.HOMETEAM_ID)
WHEN MATCHED THEN UPDATE SET T.DDD = S.DDD,
T.TEL = S.TEL3. DELETE 문
데이터를 삭제한다. 다만 삭제된 데이터를 ROLLBACK가능한 DELETE와 삭제된 데이터의 로그조차 완전히 삭제하는 TRUNCATE TABLE을 적절히 구분하여 사용하여야 한다.
DELETE FROM [테이블명]
WHERE 삭제할 행을 식별하는 조건식[예제] 선수 테이블에서 포지션이 DF고 입단년도가 2010년 이전인 선수의 데이터를 삭제한다.
DELETE FROM 선수
WHERE POSITION = "DF"
and JOIN_YYYY < 2010;DELETE문의 WHERE절에서 서브쿼리를 사용하면 ,다른 테이블을 참조해 삭제할 행을 식별할 수 있다.
[예제] 선수테이블에서 창단년도가 1980년 이전인 팀에 소속된 선수의 데이터를 삭제한다.
DELETE FROM 선수 A
WHERE EXISTS (select 1
from team x
where x.team_id = A.team_id
and X.orig_yyyy < 1980);4. MERGE 문
mergr문을 사용하면 입력, 수정하는 작업을 한번에 할 수 있다.
MERGE INTO 수정될 테이블
USING 가져올 테이블
ON (조인조건식)
WHEN MATCHED THEN ---- 조인 성공시 수정할 데이터
UPDATE
SET 수정할 칼럼명 = 수정될 새로운 값 , ------- 수정될 테이블 = 가져온 테이블의 형식을 지닌다
[수정할 칼럼명2 = 수정될 새로운 값2]
WHEN NOT MATCHED THEN --- 조인 실패시 입력할 데이터
INSERT [칼럼1, 칼럼2, ..]
VALUES (값1, 값2, ...)
2. TCL
트랜잭션을 다루는 명령어이다. 예를 들어 은행의 송금시스템에서 송금할 때 다음의 과정을 거친다.
[송금명령]
1. 송금할 금액만큼 계좌의 금액을 뺀다.
2. 빼진 금액만큼 송금받은 계좌의 금액을 더한다.만약 1번에서 2번으로 가는 과정사이에 정전등의 이유로 송금명령이 취소된다면, 자신의 통장금액만 MINUS 되어 증발하는 대참사가 일어날 것이다. 이를 막기위해 1번과 2번이 완전히 실행되거나, 혹은 둘 다 실행에 실패하도록 만들어 준다. 이 같은 논리적 단위를 트랜잭션이라고 한다.
DDL이 실행되면 자동으로 AUTO COMMIT된다.
또한, 데이터베이스를 정상적으로 종료했을때 COMMIT된다.
또한, 이상 종료로 데이터베이스와의 접속이 단절되면 ROLLBACK된다.
SQLSURVER는 DML또한 AUTO COMMIT된다.
트랜잭션의 특성
1. COMMIT
입력, 수정, 삭제에 문제가 없다고 판단되었을 경우 트랜잭션을 완료한다는 뜻이다. COMMIT을 사용 할 수 있다.
COMMIT되기 전의 데이터는 다음의 특징을 가진다.
- 데이터의 변경을 취소해 이전상태로 ROLLBACK가능하다.
- 현재 사용자는 SELECT문장으로 결과를 확인 가능하다.
- 다른 사용자는 현재 사용자가 수행한 명령의 결과를 확인할 수 없다.
- 변경된 행은 LOCK이 적용되어 다른사용자가 수정할 수 없다.
COMMIT되기 후의 데이터는 다음의 특징을 가진다.
- 데이터에 대한 변경사항이 데이터베이스에 반영된다.
- 이전데이터는 영원히 잃게 된다.
- 모든 사용자는 결과를 볼 수 있다.
- 관련된 행에 대한 잠금(LOCK)이 풀리고 다른 사용자들이 행을 조작할 수 있게 된다.
2. ROLLBACK
COMMIT 되기 전의 데이터는 이전의 상태로 되돌릴 수 있다.
SQL SERVER의 ROLLBACK
SQL SERVER는 자동으로 COMMIT이 이루어 지므로 ROLLBACK하려면, 명시적으로 트랜잭션을 선언해야한다.
BEGIN TRAN -트랜잭션 선언
select...
from...
where...
select...
from...
where...
ROLLBACK;3. SAVE POINT
SAVE POINT를 지정하면, ROLLBACK할 때 트랜잭션 시작점까지 전체를 ROLLBACK하는 것이 아니라 SAVE POINT까지 일부만 ROLLBACK할 수 있다.
ORACLE
SAVEPOINT 세이브 포인트 명
ROLLBACK 세이브 포인트 명
SQL SERVER
SAVE TRANSACTION 세이브 포인트 명
ROLLBACK TRANSACTION 세이브 포인트 명만약 SAVEPOINT1, SAVEPOINT2, SAVEPOINT3 이 있고, ROLLBACK SAVEPOINT1을 하였을 경우, SAVEPOINT2, SAVEPOINT3은 증발된다.
3. DDL
DDL은 테이블을 정의하는 명령어이다. 테이블은 행,열 구조로 이루어져 있으며, DDL에서는 입력될 데이터를 정의하고, 정의한 데이터를 어떤 데이터 유형으로 선언할 것인지 결정해야한다.
1. CREATE TABLE
테이블과 칼럼의 정의
모든 데이터를 고유하게 식별할 수 있으면서(고유식별자), 반드시 값이 존재(NOT NULL)하는 키를 PRIMARY KEY(PK)라고 한다. PK는 여러개가 존재할 수 있다. PK를 다른 테이블에서 참조하는 관계를 FOREIGN KEY(FK)라고 한다. 테이블들은 서로 PK와 FK를 사용한 관계를 맺고 있으며, 데이터정합성을 위해서 식별자를 이용한 관계를 가지는 것이다.
테이블을 정의하는 형식은 다음과 같다.
create table 테이블명 (칼럼명1 데이터 유형 [DEFAULT값] [NOTNULL],
(칼럼명2 데이터 유형 [DEFAULT값] [NOTNULL],
(칼럼명3 데이터 유형 [DEFAULT값] [NOTNULL], ... )
숫자는 개수 제한을 지정할 때 사용하며, NOT NULL은 값을 반드시 입력해야만한다. 테이블 생성시에 주의해야할 규칙(시험)
- 가능한 단수형사용
- 다른 테이블과 이름 중복 X
- 한 테이블 내에서 중복된 이름의 칼럼 X
- *데이터 유형은 반드시 지정
- *테이블 명은 반드시 문자로 시작해야한다.
- *DB벤더(회사)별로 사전에 정의한 예약어는 사용불가
- *A-Z, a-z, 0-9, _,$,# 문자만 허용된다.
[예제] 테이블을 생성한다. (ORACLE)
CREATE TABLE TEST(TEAM_ID CHAR(3) NOTNULL, -고정최대문자3개 + NOT NULL
PLAYER_ID CHAR(4) NOTNULL, -고정최대문자4개 + NOT NULL
REGION_NAME VARCHAR2(8) NOT NULL, -가변문자 8개 + NOT NULL
ORIG_YYYY CHAR(4), -고정최대문자4개 + NULL허용
BIRTH_DATE DATE, -시간
HEIGHT NUMBER(3), -숫자형최대문자3개
CONSTRAINT TEST_PK PRIMARY KEY (TEAM_ID), -TEAM_ID에 PK 생성
CONSTRAINT TEST_FK FOREIGN KEY (PLAYER_ID) REFERENCES PLAYER (PLAYER_ID));
-PLAYER테이블의 PLAYER_ID를 TEST테이블에 참조해 TEST의 PLAYER_ID를 FK로 지정제약조건의 종류
[CONSTRAINT 제약조건명] 뒤에 아래와 같이 제약조건을 정의할 수 있다. 
- DEFAULT
NULL이 나올 경우, NULL 대신 미리 DEFAULT로 지정한 문자가 나오게 할 수 있다.
생성된 테이블의 구조 확인
ORACLE : DESCRIBE 테이블명
SQL SERVER : sp_help 'dbo.테이블명'
(oracle)----------------------------------------------
컬럼명 NULL 데이터유형
PLAYER_ID NOT_NULL CHAR(7)
TEAM_ID NOT_NULL CHAR(3)
(SQL SERVER)-------------------------------------------
컬럼명 TYPE LENGTH NULLABLE
PLAYER_ID CHAR 7 NO
TEAM_ID CHAR 3 NO
CREATE문을 이용한 테이블 생성
복사 테이블을 만들 수 있다.
[예제] TEAM테이블을 가져와 복사된 TEAM_TEST 테이블을 생성한다. (ORACLE)
CREATE TABLE TEAM_TEST AS SELECT * FROM TEAM[예제] TEAM테이블을 가져와 복사된 TEAM_TEST 테이블을 생성한다. (SQL SERVER)
SELECT * INTO TEAM_TEST FROM TEAM2. ALTER TABLE
테이블의 구조를 추가,수정,삭제 할 수 있는 명령어이다.
ADD COLUMN
기존 테이블에 필요한 칼럼을 추가하는 명령어이다.
(ORACLE)
ALTER TABLE 테이블명 ADD (추가할 칼럼명1 데이터유형() [DEFAULT값] [NOT NULL],
추가할 칼럼명2 데이터유형() [DEFAULT값] [NOT NULL], ...)
(SQL SERVER)
ALTER TABLE 테이블명 ADD 추가할 칼럼명1 데이터유형() [DEFAULT값] [NOT NULL],
추가할 칼럼명2 데이터유형() [DEFAULT값] [NOT NULL], ... 단 추가된 컬럼은 반드시 테이블의 마지막 컬럼에 추가되며, 위치를 변경할 수 없다.
DROP CLOUMN
기존 테이블에 필요없는 칼럼을 삭제하는 명령어이다.
(ORACLE)
ALTER TABLE 테이블명 DROP (삭제할 칼럼명1, 삭제할 칼럼명2 ....)
(SQL SERVER)
ALTER TABLE 테이블명 DROP COLUMN 삭제할 칼럼명1, 삭제할 칼럼명2 ....MODIFY COLUMN
기존 테이블 컬럼의 데이터 유형, DEFAULT값, NOTNULL제약조건에 대한 변경을 할 수 있다.
(ORACLE)
ALTER TABLE 테이블명 MODIFY (수정할 칼럼명1, 데이터 유형(), [DEFAULT값], NOTNULL,
수정할 칼럼명1, 데이터 유형(), [DEFAULT값], NOTNULL... );
(SQL SERVER) - DEFAULT 불가능
ALTER TABLE 테이블명 ALTER COLUMN 칼럼명, 데이터 유형(), [NOTNULL];MODIFY 해줄 때에는 다음을 고려해야한다.
- 기존에 데이터가 존재한다면, 그 데이터보다 작은 길이로 수정할 수 없다.
- 해당칼럼이 NULL만 가지고 있다면, 데이터 유형변경이 가능하다.
- DEFAULT값을 바꾸면, 변경이후에 발생하는 행삽입에만 영향을 미친다.
- 해당 칼럼에 NULL값이 없을 때에만 NOT NULL제약을 추가할 수 있다.
[예제] MODIFY 예제
(ORACLE)
ALTER TABLE TEAM MODIFY (ORIG_YYYY VARCHAR(8) DEFAULT '20220101' NOT NULL);[예제] ALTER COLUMN 예제
(SQL SERVER)
ALTER TABLE TEAM ALTER COLUMN ORIG_YYYY VARCHAR(8) NOT NULL;
ALTER TABLE TEAM ADD CONSTRAINT ORIG_YYYY DEFAULT '20220101' FOR ORIG_YYYY;RENAME COLUMN
기존에 있던 테이블의 컬럼명을 변경해야 되는 경우에 사용할 수 있다.
(ORACLE)
ALTER TABLE 테이블명 RENAME COLUMN 기존칼럼명 TO 새로운 칼럼명(SQL SERVER)
sp_rename "기존 칼럼명", "새로운칼럼명", "COLUMN(고정)";
[예제] sp_rename 예제
(SQL SERVER)
sp_rename "dbo.PLAYER.PLAYER_ID", "TEAM_ID", "COLUMN";DROP CONSTRAINT
제약조건을 삭제하는 명령어이다.
ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명;ADD CONSTRAINT
기존에 제약조건을 설정하지 않았다면, 제약조건을 추가하는 명령어이다.
ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건 (컬럼명);[예제] ADD_CONSTRAINT 예제
ALTER TABLE TEAM ADD CONSTRAINT 제약1 FOREIGN KEY(TEAM_ID) REFERENCES TEST(TEAM_ID);3. RENAME TABLE
테이블의 이름을 변경하는 명령어이다.
(ORACLE)
RENAME 기존 테이블명 TO 새로운 테이블명;
(SQL SERVER)
sp_rename '기존 테이블명','새로운 테이블명';[예제] sp_rename 예제
sp_rename 'dbo.TEAM', 'TEAM_BACKUP';
다시 바꾸기
sp_rename 'dbo.TEAM_BACKUP', 'TEAM';4. DROP TABLE
테이블을 삭제하는 명령어이다. 이 때 오라클은 참조되어 있던 제약조건에 대해서도 통째로 삭제한다. SQL SERVER는 테이블을 삭제하기전에 FOREING KEY나 참조하는 테이블을 먼저 삭제해야한다.
DROP TABLE 테이블명 [CASECADE CONSTRAINT];CASCADE CONSTRAINT옵션은 해당테이블과 관계 있었던 참조되는 제약조건에 대해서도 삭제한다는 뜻이다.
5. TRUNCATE TABLE
테이블을 삭제할 때, 테이블의 데이터만 전부 삭제하고, 데이터 구조는 전부 남겨둘 때 사용한다.
TRUNCATE TABLE 테이블명;데이터구조 = (칼럼명, 제약조건, NULL유무, default값 등)
단, DELETE와 다르게 TRUNCATE TABLE은 빠르게 삭제되는 장점이 있으나, ROLLBACK 되지 않는다.
4. DCL
유저를 생성하고 권한을 부여하거나, 삭제 할 수 있는 명령어이다.
유저와 권한
ORACLE
ORACLE을 설치하면 DB에 다음과 같은 계정을 부여한다.
SQL SERVER
SQL SERVER는 두가지 방법으로 로그인 할 수 있다.
1. 윈도우에 로그인한 정보를 가지고 SQL SERVER에 접속하는 방식이다.
2. 오라클처럼 ID와 PASSWORD를 사용해 로그인하는 방식이다.
1. 유저 생성과 시스템 권한 부여
DB를 설치했다고 해서 바로 DDL문장을 실행 할 수 있는것이 아니라 권한을 부여해 주어야 한다. 이 때 일일히 권한을 부여해주기 보다는 권한을 한번에 부여하는 ROLE을 더 많이 사용한다.
먼저 새로운 유저를 생성하려면 유저 생성 권한이 있어야 한다. (CREATE USER)
ORACLE
CONN SCOTT/TIGER;
----------------
로그인되었습니다.
CREATE USER 아이디 BY 비밀번호;
-----------------------------------
error: 권한이 불충분합니다. CONN SYSTEM/PASSWORD;
-----------------------
로그인되었습니다.
GRANT CREATE USER SCOTT/TIGER
---------------------------------
권한이 부여되었습니다.
CONN SCOTT/TIGER;
----------------
로그인되었습니다.
CREATE USER 아이디 BY 비밀번호;
-----------------------------------
사용자가 생성되었습니다. SQL SERVER
SQL SERVER 에는 유저를 생성하기전 먼저 로그인을 생성해야 한다. 로그인을 생성할 수 있는 권한을 가진 로그인은 기본적으로 SA이다.
sa로 로그인을 한후 SQL 인증을 사용하는 계정을 생성한다. 로그인후 최초로 접속할 DB를 설정한다.
CREATE LOGIN 아이디 WITH "비밀번호", DEFAULT_DATABASE=AdventureWorks;SQL SERVER에서 유저는 데이터베이스마다 존재한다. 그러므로 유저를 생성하기 위해서는 생성하고자 하는 유저가 속할 DB로 이동한 후 처리해야한다.
자세한 방법은 교재 P453 을 참조한다.
오브젝트에 의한 접근권한 부여
오브젝트 권한은 특정 오브젝트인 테이블, 뷰등에 대한 select, update, insert, delete 같은 작업명령어를 의미한다.
만약 다른 유저가 생성한 테이블을 조회하고 싶다면, 조회권한을 부여받아야 한다.
예를 들어 조회해야하는 사람을A, A가 볼 수 있게 select 권한을 부여해야 하는사람을 B로 지정한다. 그러면 다음과 같은 과정을 거친다.
(ORACLE)
COON B/비번;
INSERT INTO MENU VALUES (1,'화이팅 ㅎㅎ')
commit;
GRANT SELECT ON MENU TO A; ------ GRANT "권한명" ON 테이블명 TO 대상
COON A/비번;
SELECT * from B.MENU; -------------- 성공, B를 붙인 이유는 B가 소유한 MENU테이블 이라는 뜻이다.
(SQL SERVER)
B로 로그인하기
INSERT INTO MENU VALUES (1,'화이팅 ㅎㅎ')
GRANT SELECT ON MENU TO SCOTT;
A로 로그인하기
SELECT * from B.MENU; -------------- 성공, B를 붙인 이유는 B가 소유한 MENU테이블 이라는 뜻이다.
*다른 사람의 테이블에 접속할 때는 from [다른사람.접속할 테이블]의 형식을 사용한다.이 때 select말고 다른 권한은 부여하지 않았기에 update 등의 명령시 ERROR가 발생한다.
2. Role를 이용한 권한부여
유저를 생성하면 기본적으로 많은 권한을 부여해야한다. 이는 매우 번거로운 작업이기에 유저와 권한 사이에서 중개역할을 하는 ROLE를 사용한다.
DBA는 ROLE를 생성 후 ROLE에게 각종 권한을 부여한다. ROLE을 또 다른 ROLE이나 유저에게 부여할 수 있다. 또한 ROLE에 포함된 권한이 필요한 유저에게 해당 ROLE만을 부여함으로 써 빠르고 정확하게 필요한 권한을 부여할 수 있다.
ROLE에는 시스템권한과 오브젝트권한이 있으며, ROLE은 유저에게 직접 부여 될수도 있고, 다른 ROLE에 포함해 유저에게 부여될 수도 있다.
(oracle)
CONN SYSTEM/MANAGER;
REVOKE CREATE SESSION, CREATE TABLE FROM A; - 권한 삭제 (무시해도 된다.)
LOGIN_TABLE이라는 ROLE을 만들고 이 ROLE을 A에게 부여한다.
CONN SYSTEM/MANAGER;
CREATE ROLE LOGIN_TABLE; ------- 이름이 LOGIN_TABLE ROLE인 ROEL 생성
GRANT CREATE SESSION, CREATE TABLE TO LOGIN_TABLE; -- CREATE SESSION, CREATE TABLE 권한을 LOGIN_TABLE에 부여
GRANT LOGIN_TABLE TO A; - A에게 LOGIN_TABLE권한 부여
오라클은 기본적으로 몇가지 ROLE을 제공한다. 그중 가장 많이 사용하는 ROLE은 CONNECT와 RESOURCE다. CONNECT와 RESOURCE에 부여된 권한은 다음과 같다.
CONNECT: CREATE SESSION과 같은 로그인 권한이 포함
RESOURCE: CREATE TABLE과같은 오브젝트 생성 권한
일반적인 유저를 생성할 때 위 두 권한을 가장 많이 부여한다.
유저를 삭제하는 명령어
DROP USER 유저ID [CASCADE]; - CASECADE사용시 해당 유저가 생성한 오브젝트를 먼저 삭제 후, 유저를 삭제한다.
즉, 테이블이나 뷰등이 삭제된다.SQL SERVER의 ROLE
SQL SERVER는 보통 ROLE을 생성해 사용하지 않고, 기본적으로 지원하는 ROEL에 유저가 참여하는 형식으로 사용한다. 인스턴스 수준의 작업을 할 때는 서버수준의 역할명을 사용하며, 그보다 작은 DB수준의 작업을 할때는 DB수준의 역할명을 사용한다.
-
서버수준의 역할명
-
DB수준의 역할명
