JAVA 웹개발 과정 국비 20일차 정리

20일차

※ 배우는 과정이므로 정확하지 않을 수 있습니다.
잘못된 점은 알려주시면 수정하도록 하겠습니다!

---

1. SUBQUERY 이어서..

1) InLine View

FROM 절에서 사용하는 Subquery

SELECT emp_name FROM (SELECT emp_id, emp_name, salary FROM employee;)

2) 상관(상호연관) Suquery

메인쿼리에 있는 특정 값을 서브쿼리에 사용

JOIN 없이 dept_title을 출력해보자.

SELECT emp_name, (SELECT dept_title FROM department) FROM employee;

그러면 서브쿼리가 여러개의 값을 리턴한다는 에러가 뜬다.
아래와 같이 dept_code를 내부 쿼리에 가져다 쓰면 된다.

SELECT 
    emp_name, 
    dept_code, 
    (SELECT dept_title FROM department WHERE dept_id=e.dept_code) 부서명
FROM employee e;

3) Ranking 함수

• 순위를 적용하는 함수.
• OVER가 따라온다 (숙어같은 함수)
• OVER 뒤에는 정렬 기준이 온다. OVER(ORDER BY)
• RANK / DENSE_RANK / ROW_NUMBER

① RANK() OVER(ORDER BY)

동일한 값일 때 같은 순위로 적용되나, 다음 순위가 사라진다.
ex) 1등 2등 2등 4등

SELECT emp_name, salary, RANK() OVER(ORDER BY salary DESC) 급여순위
FROM employee;

② DENSE_RANK() OVER(ORDER BY)

동일한 값일 때 같은 순위로 적용되나, 다음 순위가 사라지지 않음.
ex) 1등 2등 2등 3등 * dense : 밀집한, 빽뺵한

SELECT emp_name, salary, DENSE_RANK() OVER(ORDER BY salary DESC) 급여순위
FROM employee;

③ ROW_NUMBER() OVER(ORDER BY)

공동 순위 없이 순서대로.
랭킹이 아니라 순위이다.
게임 만드는 거 아니면 이 기능을 더 많이 사용할 것
ex) 1등 2등 3등 4등

SELECT emp_name, salary, ROW_NUMBER() OVER(ORDER BY salary DESC) 급여순위
FROM employee;

④ 연습 문제

문제를 제대로 이해하지 못해서 정리해둔다.

employee 테이블 내에서 급여순위(내림차순)로 정렬했을 때 5 ~ 10 위까지만 출력해보세요.

-- 내가 푼 코드 (틀린코드)
SELECT salary 
FROM employee
WHERE (SELECT RANK() OVER(ORDER BY salary DESC) 급여순위 
		FROM employee) IN (5,6,7,8,9,10); 

서브쿼리가 급여순위(1, 2, 3, 4, 5, …)를 보여주므로 IN을 써서 맞는 순위만 선택해서 출력하면 될 것 같았는데, 너무 자바스럽게 생각했던 것 같다.
N : N 관계의 조건이 애초에 성립을 할 수 없었다는 것을 간과했다.
서브쿼리가 다중행이므로 다중행은 IN을 써야한다! 라는 생각에 갖혀서 왜 안되는지 이해가 안갔었다.
알고보니 다중행 쿼리문에서 IN을 사용한다는 뜻은 1 : N 관계에서 사용할 수 있다는 뜻이었다.

-- 정답 코드
SELECT salary 
FROM (SELECT RANK() OVER(ORDER BY salary DESC) 급여순위 
		FROM employee)
WHERE 급여순위 BETWEEN 5 AND 10 ;

INLINE 서브쿼리로 급여순위가 출력되게 만들고, WHERE 조건절에서 급여순위를 선택하여 출력해주면 된다.
아래 쿼리와 같이 SELECT문에서 급여순위를 출력하고, WHERE절에서 조건을 붙이는 시도를 많이 하는데, 실행 순서 때문에 에러가 뜨니 주의해야한다.

-- 많이 시도하지만 실행 순서 에러 코드
SELECT RANK() OVER(ORDER BY salary DESC) 급여순위 
FROM employee
WHERE 급여순위 BETWEEN 5 AND 10;

2. DDL/DML

• DDL : CREATE(생성) / ALTER(수정) / DROP(삭제) / RENAME(변경) …
  • 객체를 생성하고 수정하고 삭제하는 목적의 언어
  • 객체의 종류 : table, user, view, sequence, index, trigger, package, procedure, function…
• DML : SELECT(검색) / INSERT(삽입) / UPDATE(수정) / DELETE(삭제) …
  • 데이터를 관리하는 목적의 언어

비상연락망을 생성해보자.

1) DDL - CREATE

객체를 생성한다.
컬럼명 - 자료형 - 제약조건 순으로 컬럼을 생성한다.

CREATE TABLE contact(
	id NUMBER,
	name VARCHAR(20),
	contact VARCHAR(20)
);

2) DDL - DROP

객체를 삭제한다. (수정보다 삭제가 쉬움)

DROP TABLE contact;

3) DML - INSERT INTO 테이블명 VALUES

다시 contact 테이블을 CREATE 한 뒤, 값을 넣어보자.

① 그냥 값을 넣는 방법

모든 컬럼 값을 다 집어넣겠다 라는 전제가 깔려있다.
값을 다 넣지 않으면 not enough values 오류가 뜬다.

INSERT INTO contact VALUES(1001, 'Jack', '01012341234');

② 컬럼명을 열거하고 값을 넣는 방법

모든 컬럼의 값 말고, 몇 개의 값만 넣고 싶을 때 사용한다.

INSERT INTO contact (id, name) VALUES(1002, 'Tom');

4) DDL - CREATE - 제약조건

TABLE을 CREATE할 때, 컬럼의 값에 제약을 두는 조건을 설정할 수 있다.

① NOT NULL

NULL 값이 존재할 수 없음 (비워둘 수 없음)

CREATE TABLE contact(
    id number NOT NULL,
    name VARCHAR(20) NOT NULL,
    contact VARCHAR(20) NOT NULL
);

위와 같은 제약조건을 가진 contact 테이블을 다시 생성한다.

INSERT INTO contact (id, name) VALUES(1002, 'Tom');

NOT NULL 조건을 걸었으므로, 2개의 값만을 넣으려고 하면 cannot insert NULL into 오류가 뜬다.

② PRIMARY KEY (기본키)

중복되지 않는 행을 구분하는 식별자. (기본적으로 NOT NULL)
한 테이블에 1개만 사용가능

비상연락망에서 id 컬럼은, 각각의 행을 구분짓기 위한 주민번호, 학번과 같은 식별 용도의 값이다.
NOT NULL 제약조건만으로는 충분하지 않다. 중복되는 값도 없어야 하기 때문이다.
이런 컬럼에 PRIMARY KEY를 설정해준다.

CREATE TABLE contact(
    id number PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    contact VARCHAR(20) NOT NULL
);

위와 같이 id 컬럼을 PRIMARY KEY로 설정하는 테이블을 만들어준다.

INSERT INTO contact VALUES(1001, 'Tom', '01012341234');
INSERT INTO contact VALUES(1001, 'Jack', '01012341234');

그리고 id에 중복된 값을 넣으려고 하면 unique constraint violated 오류가 나온다.

③ Data Dictionary

사용자의 쿼리 또는 명령문에 의해 생성되는 정보들을 저장하는 오라클 내장테이블

-- user_tables : 사용자의 명령에 의해 만들어진 테이블의 목록을 저장하고 있는 딕셔너리
SELECT table_name FROM user_tables;
-- user_constraints : 사용자가 만든 제약조건들을 저장하고 있는 딕셔너리
SELECT * FROM user_constraints;
SELECT * FROM user_constraints WHERE table_name='CONTACT';

그런데 constraint_name이 SYS_C007076, SYS_C007077 등 보기 어렵게 쓰여있다. 보기 편하게 바꿔보도록 하자.

CREATE TABLE contact(
    id number CONSTRAINT id_pk PRIMARY KEY,
    name VARCHAR(20) CONSTRAINT name_nn NOT NULL,
    contact VARCHAR(20) CONSTRAINT contact_nn NOT NULL
);

위와 같이 써주면, 다시 user_constraints를 조회해보면 id_pk, name_nn과 같이 지정한 이름으로 바뀌어있는 것을 볼 수 있다.
제약 조건은 똑같은 이름으로 여러개가 만들어질 수 없으므로 앞에 테이블 명을 붙여서 구분해주기도 한다.

④ UNIQUE

중복 방지

주 키는 아닌데, 중복은 없애고 싶을 수가 있을 수도 있다.
예를 들면, 비상연락망의 핸드폰번호(contact)는 중복되면 안되지만, 주 키로 사용할 정도는 아니다.
이미 id 컬럼이 PRIMARY KEY이기 때문에, PRIMARY KEY를 붙여줄 수도 없다.
이 때, UNIQUE를 써주면, 중복을 방지하는 조건을 걸 수 있다.
UNIQUE + NOT NULL을 해주면 기능적으로 PRIMARY KEY와 동일하다. 그러나 PRIMARY KEY는 식별자의 상징적 의미가 크므로 두 개는 구분해서 사용해주는 것이 좋다.

CREATE TABLE contact(
    id number CONSTRAINT id_pk PRIMARY KEY,
    name VARCHAR(20) CONSTRAINT name_nn NOT NULL,
    contact VARCHAR(20) CONSTRAINT contact_uk UNIQUE
);

위와 같이 테이블을 생성하고, 아래 값을 넣어주면 unique constraint voidated 에러가 뜬다.

INSERT INTO contact VALUES (1001,'Jack','01011112222');
INSERT INTO contact VALUES (1002,'Tom','01011112222');

⑤ CHECK

컬럼에 들어갈 값을 제한

성별 컬럼을 추가해서, 남 or 여 둘 중에 한 값만 넣게 하고 싶다면, CHECK 조건을 쓸 수 있다.

CREATE TABLE contact(
    id number CONSTRAINT id_pk PRIMARY KEY,
    name VARCHAR(20) CONSTRAINT name_nn NOT NULL,
    contact VARCHAR(20) CONSTRAINT contact_uk UNIQUE,
    gender VARCHAR(10) CHECK (gender IN ('남','여')) NOT NULL
);

테이블을 생성하고, ‘남’ 이나 ‘여’ 값이 아닌 값을 삽입하려고 하면 check constraint violated 에러가 뜬다.

INSERT INTO contact VALUES (1001,'Jack','01012341234','M');

⑥ DEFAULT

자바에서 생성자에 default 값을 넣어줬듯이, 컬럼에도 기본값을 정해줄 수 있다.

CREATE TABLE product (
    pid VARCHAR(20) PRIMARY KEY,
    pname VARCHAR(100) NOT NULL,
    price NUMBER DEFAULT 0 NOT NULL
);

default 값을 넣어주고 싶을 때는, 값 대신 default를 써주면 된다.

INSERT INTO product VALUES('p1001','LG-TV',default);

⑦ FOREIGN KEY (외래키) - REFERENCES

데이터 무결성을 유지하기 위한 제약 조건

• 비정상적인 값을 넣는 것을 막을 수 있다.(개연성 없는 데이터는 저장할 수 없다)
• 하나 이상의 테이블에서 생성되는 키
• Parent ㅡ Child 관계를 가진다.
• 외래키가 생성되면, 테이블을 지우는 것이 어려워지므로 주의하자.

상품 정보를 가지고 있는 product 테이블을 하나 생성하고, 값을 넣어주자.

CREATE TABLE product (
    pid VARCHAR(20) PRIMARY KEY,
    pname VARCHAR(100) NOT NULL,
    price NUMBER DEFAULT 0 NOT NULL
);

INSERT INTO product VALUES('p1001','LG-TV',1000000);
INSERT INTO product VALUES('p1002','ipad',600000);

그리고 구매내역을 관리하는 테이블 purchase_details 도 생성해준다.
어떤 것이 팔렸는지, 언제 팔렸는지의 정보를 담는 pid와 pdate 컬럼도 생성해준다.

CREATE TABLE purchase_details(
    id NUMBER PRIMARY KEY,
    pid VARCHAR(20) NOT NULL,
    pdate DATE DEFAULT SYSDATE NOT NULL
);

그런데, product 테이블에 없는 pid가 purchase_details에 pid에 들어가는 일이 생기면 안된다.
상품 id가 없는데, 그 상품이 팔렸다? 말이 되지 않기 때문이다.
이럴 때 REFERENCES를 써서 FOREIGN KEY 설정을 해주는 것이다.
주의할 점은 데이터 타입이 없이 바로 REFERENCES 테이블명(컬럼명)을 써주어야 한다.

CREATE TABLE purchase_details(
    id NUMBER PRIMARY KEY,
    pid REFERENCES product(pid),
    pdate DATE DEFAULT SYSDATE NOT NULL
);

테이블을 지우고, 위의 테이블을 다시 생성해준 뒤, product 테이블에 없는 pid 값인 ‘p2003’을 넣어보면 integrity constraint violated - parent key not found 에러가 뜨면서 값이 저장되지 않는다. 무결성 제약조건이 위반되었습니다 - product 테이블에서 값을 찾을 수 없습니다 라는 뜻이다.
이로써 외래키를 생성하면 자바의 상속과 비슷하게 parent와 child가 생성되는 것도 알 수 있다.

또한, 주의할 점은 외래키가 생성되면서부터 테이블을 지우는 것이 어려워진다는 점이다.
product 테이블은 삭제할 수 없다.
purchase_details 테이블에서 외래키인 pid가 product 테이블의 pid를 참조하고 있기 때문에unique/primary keys in table referenced by foreign keys 라고 에러가 뜨면서 삭제가 되지 않는다.
그래서 product를 삭제하려면, 즉, parent를 삭제하려면 child를 먼저 삭제한 후에 삭제할 수 있다.
그럼 연결되어있는 pid 값은 지워질까?

DELETE FROM product WHERE pid='p1001';

위와 같이 DELETE문을 써서 지우려고 해봤지만 child record found가 뜨며 지울 수 없다고 한다.

⑧ ON DELETE CASCADE

참조키가 삭제되면, 외래키도 삭제한다.

CREATE TABLE purchase_details(
    id NUMBER PRIMARY KEY,
    pid REFERENCES product(pid) ON DELETE CASCADE,
    pdate DATE DEFAULT SYSDATE NOT NULL
);

purchase_details 테이블의 pid 에 ON DELETE CASCADE 조건을 걸어주면, 위에서 곤란했던 상황이 해결된다. 아까와는 달리 product 테이블의 ‘p1001’의 값이 삭제되는 것을 볼 수 있다.
그리고 purchase_details의 pid값이 ‘p1001’인 ‘LG-TV’ 데이터도 연쇄적으로 사라진 것을 확인할 수 있다.

DELETE FROM product WHERE pid='p1001';

4) DML - DELETE FROM

데이터 삭제

• DELETE는 다 지우는 거니 WHERE절로 꼭 조건을 붙여주자
• DELETE는 *을 찍지 않는다.

DELETE FROM member WHERE id='jackid';

5) DML - UPDATE 테이블명 SET

안의 내용을 수정

INSERT INTO contact VALUES (1001, 'Jack', '01012341234','여');

UPDATE contact SET gender='남' WHERE id=1001;
UPDATE contact SET name='tom', contact='01011112222' where id=1001;

3. 그 외..

1) VARCHAR VS CHAR

	VARCHAR	CHAR
크기	가변적	정해져있음
20바이트에 9바이트를 넣으면?	11바이트 없앰	20바이트 다쓴다
성능적 효율	좋지않다	좋다
특정 컬럼의 문자열 값이 수시로 바뀔 때	수정된 값에 따라 메모리 재할당-> 정리 반복	공간은 정해져있고 값만 왔다갔다함