데이터베이스 20180823 (학원10)

DDL

 -> CREATE, ALTER, DROP

@ ALTER

 -> 테이블에 정의된 내용을 수정할때 사용하는 데이터 정의어

 -> 컬럼의 추가/삭제, 제약조건 추가/삭제, 컬럼의 자료형변경,

DEFAULT변경, 테이블명/컬럼명/제약조건의 이름 변경 등이 가능하다.

DCL (DATA CONTROL LANAGUAGE)

 -> 데이터 제어어

 -> DMBS에 대한 보안, 무결성, 복구 등 DBMS를 제어하기 위한 언어

 -> GRANT(권한할당), REVOKE(권한해제), COMMIT(실행), ROLLBACK(복구)

 -> COMMIT, ROLLBACK은 트랜잭션 관련언어로 TCL로 구분하기도 함

@ GRANT

 -> 사용자와 role에 대한 권한 부여

 -> role은 object부분에서 다룸

GRANT [System_Privilege|role] TO [user/role/PUBLIC] WITH ADMIN OPTION

 - System_Privilege : 부여할 시스템 권한의 이름

 - role : 부여할 데이터 베이스 역할의 이름

 - user/role : 부여할 사용자의 이름과 다른 데이터베이스의 이름

 - PUBLIC : 시스템권한, 또는 데이터베이스 역할을 모든 사용자에게 부여할 수 있음

 - WITH ADMIN OPTION : 권한을 부여받은 사용자도 부여받은 권한을 다른 사용자 또는 역할로 부여가능

# system 계정으로 신규사용자를 생성 -> 신규사용자로 접속시도(실패)

 -> 접속권한이 없어서 실패될것이다.

 -> system계정으로 접속권한을 부여 해줄것이다.

 -> 접속성공이 뜰것이다.

--------------------------------------------------------------

cmd키기 -> system -> 1234 -> show user -> create user test01 identified by test01;

-> conn test01 -> 에러가 뜬다. -> show user -> 아무것도 안뜬다. 관리자로접속하기

-> conn system -> 1234 -> show user -> grant connect to test01; ->

이렇게받은다음 test01접속하기 -> conn test01 -> test01 -> 접속 잘된다. -> show user

---------------------------------------------------------------

test01로 ORACLE SQL DEVELOPER로 접속하기

test01로 가서 select * from kh.COFFEE;

하면 못찾는데 권한이 없어서 그렇다.

WEB로해서 시스템계정으로 들어가기

GRANT SELECT ON kh.COFFEE TO test01; -> 이렇게 써주기

그후 test계정가서 다시 

select * from kh.COFFEE;

치면 된다.

test01로와서 보면

INSERT INTO kh.COFFEE VALUES('콜드브루',10000,'YS');  -> 안된다.

보는 권한만 있기 때문이다.

=================================================================

CRUD

C : 데이터 삽입 -> INSERT

R : 데이터 조회 -> SELECT

U : 데이터 수정 -> UPDATE

D : 데이터 삭제 -> DELETE

CREATE,DROP -> 4가지만 준다고생각하면된다.

-------------------------------------------------------------------

TCL (TRANSACTION CONTROL LANGUAGE)

 -> 트랜잭션 제어 언어

 -> COMMIT, ROLLBACK

@ 트랜잭션 (TRANSACTION)

 -> 한꺼번에 수행되어야 할 최소의 작업단위

 -> 하나의 트랜잭션으로 이루어진 작업은 한꺼번에 완료

 -> 취소의 경우에는 한꺼번에 취소가 되어야 한다.

EX) ATM 업무

1. 카드사용

2. 메뉴선택(인출)

3. 금액선택(100만), 인증

4. 계좌에서 돈이 인출

5. 현금으로인출

6. 완료

@ 트랜잭션 SQL

 -> TCL

COMMIT : 트랜잭션작업 정상적으로 완료되면 변경내용을 영구히 저장

(모든 SAVEPOINT가 삭제)

ROLLBACK : 트랜잭션작업을 모두 취소하고 최근 COMMIT시점으로 이동

SAVEPOINT [SAVEPOINT명] : 현재 트랜잭션 작업시점에 이름을 지정

 -> 하나의 트랜잭션안에서도 구역을 나눔

ROLLBACK TO [SAVEPOINT명] -> SAVEPOINT시점으로 이동

KH로 넘어가기

========================================================================

DATABASE

DDL(데이터정의어) - CREATE, ALTER, DROP

DCL(데이터제어어) - GRANT, REVOKE

TCL(트랜잭션 제어어) - COMMIT, ROLLBACK(SAVEPOINT)

DML(데이터조작어) - INSERT, UPDATE, DELETE

DQL(DATA QUERY LANGUAGE) - SELECT 

DBMS 관리할 언어

 홈페이지 

---------------------------------------------------------------------

# 데이터베이스 구축

- 주제 - 자유

- 컬럼수 - min(10) -> 데이터형 다양하게

- 제약조건 pk,fk,u,nn

- 테이블을 만드는건 kh계정에서 만들기

- 데이터를 insert,delt,up,sel하는건 -> 계정 새로 만들어서 수행

===========================================================================

===========================================================================

--20180823

/*

@ ALTER

 -> 테이블에 정의된 내용을 수정할때 사용하는 데이터 정의어

 -> 컬럼의 추가/삭제, 제약조건 추가/삭제, 컬럼의 자료형변경,

DEFAULT변경, 테이블명/컬럼명/제약조건의 이름 변경 등이 가능하다.

*/

CREATE TABLE MEMBER_TBL(

USER_NO NUMBER PRIMARY KEY,

USER_ID VARCHAR2 (20),

USER_PWD CHAR(20)

);

--GUI로봤던 테이블 정보를 간결하게 볼 수 있다.

DESC MEMBER_TBL;

--USER_NAME 컬럼 추가

ALTER TABLE MEMBER_TBL ADD (USER_NAME VARCHAR2(20));

DESC MEMBER_TBL;

--USER_AGE 컬럼을 추가 DEFAULT가 0인 제약조건

ALTER TABLE MEMBER_TBL ADD (USER_AGE NUMBER DEFAULT 0);

DESC MEMBER_TBL;

--1개는 모든 컬럼 입력, 1

--AGE부분 default로 줘봐라.

INSERT INTO MEMBER_TBL VALUES(1,'jong','1234','김종규','26');

INSERT INTO MEMBER_TBL VALUES(2,'jong1','12345','김종규1',default);

--

DESC MEMBER_TBL;

select * from MEMBER_TBL;

--컬럼명에서 default를바꿔주면된다.

--제약조건추가

/*

제약조건의 이름은 MTBL_ID_UNIQ라고 만들어줬다.

그리고 USER_ID에 UNIQUE에 추가시켜준것이다.

*/

ALTER TABLE MEMBER_TBL

ADD CONSTRAINT MTBL_ID_UNIQ UNIQUE (USER_ID);

DESC MEMBER_TBL;

--제약조건 추가할때 주의해야 될사항은?

--회원가입시 패스워드 NOT NULL을 추가해줄것인데 위에처럼 하면 안된다.

--NOT NULL 제약조건을 추가할때는 ADD->MODIFY를 사용해야함

--USER_PWD에 NOT NULL제약조건 추가

--컬럼명이나오고 제약조건 넣는게 나온다.  -> NULL일때는 추가가아니라 수정하는 개념이다.

ALTER TABLE MEMBER_TBL

MODIFY USER_PWD CONSTRAINT MTBL_PWD_NN NOT NULL;

--이렇게 확인 해볼 수 있다.

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME FROM USER_CONSTRAINTS WHERE

TABLE_NAME='MEMBER_TBL';

-- USER_PWD -> 패스워드 컬럼

-- USER_PWD -> PASSWORD 로 변경 해보겠다.

ALTER TABLE MEMBER_TBL

RENAME COLUMN USER_PWD TO PASSWORD;

DESC MEMBER_TBL;

--컬럼의 데이터 타입 수정, DEFAULT값 수정

--USER_NAME CHAR(10) 변경

--USER_AGE의 기본값 20으로 변경

ALTER TABLE MEMBER_TBL

MODIFY USER_NAME CHAR(30)

MODIFY USER_AGE DEFAULT 30;

--이렇게 한번에 바꿀수도 있다.

/*

*/

DESC MEMBER_TBL;

select * from USER_CONSTRAINTS where TABLE_NAME='MEMBER_TBL';

--컬럼삭제

CREATE TABLE TB1(

PK NUMBER PRIMARY KEY,

FK NUMBER REFERENCES TB1,

COL1 NUMBER,

CHECK (PK > 0 AND COL1 >0) --체크로통해 범위로도 가능하다.

);

DESC TB1;

--PK라는걸 삭제한다.

--PK라는 COLUMN을 삭제해버리겠다.

ALTER TABLE TB1 DROP COLUMN PK;

--당연히 안된다.

--삭제하고 싶으면 뒤에 키워드를 넣어줘야한다.

ALTER TABLE TB1 DROP COLUMN PK CASCADE CONSTRAINT;

DESC TB1;

--이번엔 제약조건을 삭제 해보겠다.

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME FROM USER_CONSTRAINTS WHERE

TABLE_NAME='MEMBER_TBL';

--첫번째는 프라이머리키, UNIQUE, NOT NULL로걸어놓았다.

ALTER TABLE MEMBER_TBL

DROP CONSTRAINT MTBL_ID_UNIQ;

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME FROM USER_CONSTRAINTS WHERE

TABLE_NAME='MEMBER_TBL';

--제약조건 이름변경

ALTER TABLE MEMBER_TBL

RENAME CONSTRAINT SYS_C007083 TO MTBL_UN_PK; 

--제약조건,현재이름,TO,바꿀이름

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME FROM USER_CONSTRAINTS WHERE

TABLE_NAME='MEMBER_TBL';

--테이블 이름 변경(MEMBER_TBL)변경해보기

ALTER TABLE MEMBER_TBL

RENAME TO MTBL;

select * from MTBL;

--테이블 삭제

DROP TABLE MTBL; --삭제하는 것이다. 제약조건 있으면 안되니 무시해줄수 있다.

DROP TABLE MTBL CASCADE CONSTRAINT; --제약조건도 같이해서 삭제해버린다.

--유통만 하는 회사이다.

CREATE TABLE COFFEE(

PRODUCT_NAME VARCHAR2 (20) PRIMARY KEY,

PRICE NUMBER NOT NULL,

COMPANY VARCHAR2 (20) NOT NULL

);

INSERT INTO COFFEE VALUES('아메리카노',1800,'DS');

INSERT INTO COFFEE VALUES('에스프레소',4100,'SB');

INSERT INTO COFFEE VALUES('다방커피',2500,'백다방');

select * from coffee;

COMMIT;

--test01로접속하기

/*

COMMIT : 트랜잭션작업 정상적으로 완료되면 변경내용을 영구히 저장

(모든 SAVEPOINT가 삭제)

ROLLBACK : 트랜잭션작업을 모두 취소하고 최근 COMMIT시점으로 이동

SAVEPOINT [SAVEPOINT명] : 현재 트랜잭션 작업시점에 이름을 지정

 -> 하나의 트랜잭션안에서도 구역을 나눔

ROLLBACK TO [SAVEPOINT명] -> SAVEPOINT시점으로 이동

*/

CREATE TABLE USERTBL(

USERNO NUMBER UNIQUE,

ID VARCHAR(20) PRIMARY KEY,

PASSWORD CHAR(20) NOT NULL

);

--1,test01,pass01/2/3 까지 만들어서 저장하기

INSERT INTO USERTBL VALUES('1','test01','pass01');

INSERT INTO USERTBL VALUES('2','test02','pass02');

INSERT INTO USERTBL VALUES('3','test03','pass03');

select * from USERTBL;

COMMIT;

INSERT INTO USERTBL VALUES(4,'test04','pass04');

select * from USERTBL;

ROLLBACK;

--아까 test04 추가하기 전상태로 간다.

select * from USERTBL;

INSERT INTO USERTBL VALUES(4,'test04','pass04');

INSERT INTO USERTBL VALUES(5,'test05','pass05');

select * from USERTBL;

ROLLBACK;

--45를 넣어도 3번까지로 다시간다.

select * from USERTBL;

--SAVEPOINT가보곘다.

INSERT INTO USERTBL VALUES(4,'test04','pass04');

SAVEPOINT SP1;

INSERT INTO USERTBL VALUES(5,'test05','pass05');

select * from USERTBL;

--SP1로 지정한 시점으로 이동한다.

ROLLBACK TO SP1;

select * from USERTBL;

ROLLBACK;

select * from USERTBL;

--마지막 커밋 시점으로 이동한다.

INSERT INTO USERTBL VALUES(4,'test04','pass04');

SAVEPOINT SP1;

INSERT INTO USERTBL VALUES(5,'test05','pass05');

COMMIT;

ROLLBACK TO SP1;

--SAVEPOINT자체가 없다. COMMIT을 했기 때문이다.

ROLLBACK;

select * from USERTBL;

/*

--kh_web계정으로 만들고

다른 계정에서 읽어오고 수정하고 하는 걸 해볼것이다.

*/

/*

# 데이터베이스 구축

- 주제 - 자유

- 컬럼수 - min(10) -> 데이터형 다양하게

- 제약조건 pk,fk,u,nn

- 테이블을 만드는건 kh계정에서 만들기

- 데이터를 insert,delp,up,sel하는건 -> 계정 새로 만들어서 수행

*/

CREATE TABLE CARD (

NO NUMBER PRIMARY KEY,

CARD_NUM VARCHAR2 (20) UNIQUE,

CARD_PWD CHAR(20) NOT NULL,

CARD_NAME VARCHAR2(20) NOT NULL,

CARD_NICKNAME VARCHAR2(20) default '종규',

CARD_GENDER CHAR(10) CHECK(CARD_GENDER IN('남자','여자')),

CARD_TEL VARCHAR(20) default '010-0000-0000',

CARD_ADDRESS VARCHAR(90),

CARD_COMPANY VARCHAR(20),

CARD_COLOR VARCHAR(10) default '파랑'

);

===================================================================================

test01_web.sql

select * from kh.COFFEE;

INSERT INTO kh.COFFEE VALUES('콜드브루',10000,'YS');

--안되는 이유는 select권한만있고 insert는 없기 때문이다.

INSERT INTO kh.COFFEE VALUES('콜드브루',10000,'YS');

--권한주고나니 잘된다.

select * from kh.COFFEE;

--kh는 resource라는 권한을 줬는데 실제로 업무상에선 안친다.

INSERT INTO kh.COFFEE VALUES('물',20000,'YS');

select * from kh.COFFEE;  --조회는 잘된다.