데이터 분석 부트 캠프/학습 일지

[패스트캠퍼스] BDA 부트캠프 학습일지_7주차

이-다은 2023. 8. 4. 08:58
7주차 (0731-0804)
패스트캠퍼스 데이터 분석 부트캠프

 

 

 SQL 온라인 강의 : 가장 쉽게 시작하는 데이터 분석, SQL 유치원 올인원 패키지 Online.  

Day 10. 조건에 조건 더하기

서브쿼리

  • 하나의 쿼리 내 포함된 또 하나의 쿼리를 의미.
    • SELECT, FROM, WHERE, HAVING, ORDER BY 절에 사용 가능
    • INSERT, UPDATE, DELETE 문에도 사용 가능
  • 반드시 괄호 안에 있어야 함
  •  ; (세미 콜론)을 붙이지 않아도 됨

Select 절 내의 서브 쿼리

SELECT [칼럼명] ,
           ( SELECT [칼럼명]  FROM [테이블] WHERE [조건식] )
FROM [테이블]
WHERE [조건식];

* SELECT절의 서브 쿼리는 반드시 결과값이 하나의 값 이어야 함.


ex) 

 

From절 내의 서브 쿼리

--- 실무 쿼리에서 가장 많이 쓰였음

SELECT [칼럼명] 
FROM ( SELECT [칼럼명]  FROM [테이블] WHERE [조건식] ) AS [별명] - 반드시 별명으로 가져와야 함
WHERE [조건식];

ex) 

 

Where 절 내의 서브 쿼리

SELECT [칼럼명] 
FROM [테이블]
WHERE [컬럼명] [연산자] ( SELECT [칼럼명]  FROM [테이블] WHERE [조건식] );
( 연산자: = / != / > / < /,,, 
[컬럼명] IN ( 서브쿼리 )            --- 메인 쿼리의 컬럼이 서브 쿼리의 결과값 내에 있다
[컬럼명] < ALL ( 서브쿼리 )      --- 메인 쿼리의 컬럼이 모든 서브 쿼리의 결과값보다 작다
[컬럼명] < ANY ( 서브쿼리 )     --- 메인 쿼리의 컬럼이 서브 쿼리의 결과값보다 하나라도 작다
EXISTS ( 서브쿼리 )                --- 서브 쿼리의 결과값이 존재한다
NOT EXISTS ( 서브쿼리 )        --- 서브 쿼리의 결과값이 존재하지 않는다  )

ex) 

실습

 

Day 11. 알아두면 좋은 고급 기

데이터 삭제, 수정하기

where 문 사용하기 

 

제약 조건

  • 데이터를 입력할 때 실행되는 데이터 입력 규칙
  • 테이블을 만들거나 변경하면서 설정한다.
    • CREATE TABLE 및 ALTER TABLE 구문

ex)

 

권한과 DCL

 

  • 사용자 확인하기
    • #MySQL 기본 데이터베이스인 mysql 데이터베이스 선택하기
    • USE mysql;
    • #사용자 목록 조회하기
    • SELECT user, host FROM user;
  • 사용자 생성, 삭제하기
    • #사용자 생성하기
    • CREATE USER [사용자 이름]@[ip주소];
    • #비밀번호와 함께 사용자 생성하기
    • CREATE USER [사용자 이름]@[ip주소] IDENTIFIED BY ‘[사용자 비밀번호]’;
    • #사용자 삭제하기
    • DROP USER [사용자 이름]
  • 권한 부여하기
    • #권한 부여하기
    • GRANT [권한] ON [데이터베이스 이름].[테이블 이름] TO [사용자 이름]@[ip주소];
    • #권한 확인하기
    • SHOW GRANTS FOR [사용자 이름]@[ip주소];
    • #권한 삭제하기
    • REVOKE [권한] ON [데이터베이스 이름].[테이블 이름] FROM [사용자 이름]@[ip주소];
    • #권한 적용하기
    • FLUSH PRIVILEGES;
    • ※  ip주소가 %면, 해당 아이디를 가지는 모든 ip에서의 접근을 허용한다는 의미
    • 권한 : ALL PRIVILEGES(모든 권한), SELECT, INSERT 

 

트랜잭션과 TCL

1. 트래잭션, 2. 세이브포인트

 

  • 트랙잭션
    • #트랜잭션 시작하기
    • START TRANSACTION;
    • #트랜잭션 확정하기
    • COMMIT;
    • #트랜잭션 이전으로 돌아가기
    • ROLLBACK;
  • 세이브포인트
    • #세이브포인트 만들기
    • SAVEPOINT [세이브포인트 이름];
    • #세이브포인트로 돌아가기
    • ROLLBACK TO [세이브포인트 이름];

 SQL 온라인 강의 : 초격차 패키지 : 한 번에 끝내는 데이터 사이언스 

Ch 5. SQL 기초

테이블 생성하기 ( CREATE TABLE )

1. 코드 작성 -> 2. 테이블 간 칼럼 관계

 

테이블 / 컬럼 변경하기 ( ALTER TABLE )

#1. 칼럼 추가하기
ALTER TABLE TB_LINK ADD COLUMN ACTIVE_YN CHAR(1);

#2. 칼럼 지우기
ALTER TABLE TB_LINK DROP COLUMN ACTIVE_YN;

#3. 칼럼명 변경하기
ALTER TABLE TB_LINK RENAME COLUMN TITLE TO LINK_TITLE;

#4. 칼럼 내 디폴트값 추가로 지정하기
ALTER TABLE TB_LINK ALTER COLUMN TARGET SET DEFAULT '_BLANK';

#5. 칼럼 내 제약조건 추가로 지정하기
ALTER TABLE TB_LINK ADD CHECK (TARGET IN ('_SELF', '_BLANK', '_PARENT', '_TOP'));
ALTER TABLE TB_LINK ADD CONSTRAINT UNIQUE_URL UNIQUE (URL); -- 제약조건명 추가
ALTER TABLE TB_USER ADD PRIMARY KEY (USER_NO);

#6. 컬럼 타입 변경하기
1: ALTER TABLE TB_ASSET ALTER COLUMN ASSET_NM TYPE VARCHAR;
2: ALTER TABLE TB_ASSET
       ALTER COLUMN LOC TYPE VARCHAR ,
       ALTER COLUMN DESCRIPTION TYPE VARCHAR ;

* INT로 변경하고 싶을 경우 TYPE INT를 쓰면 에러 발생 ( ∴ USING 절 내용 추가 )
→ ALTER TABLE TB_ASSET ALTER COLUMN ASSET_ID TYPE INT USING ASSET_ID::INTEGER;

 

테이블 제거하기 ( TRUNCATE / DROP TABLE )

#1. 값만 지우기
TRUNCATE TABLE TB_INVOICE;
TRUNCATE TABLE TB_INVOICE RESTART IDENTITY → 아예 처음부터 (SERIAL 값도 초기화)

#2. 테이블 싹 다 지우기
DROP TABLE TB_INVOICE;
DROP TABLE IF EXISTS TB_INVOICE; ( 에러 방지 )

 

테이블 복사하기 ( COPY TABLE )

CREATE TABLE TB_CONTACT_BAK AS TABLE TB_CONTACT; 
→ TB_CONTACT : 복사하려는 기존 테이블, TB_CONTACT_BAK : 복사한 신규 테이블
* 테이블 구조 및 데이터는 복제되지만, 제약 조건은 복제 (반영) 안 됨.

ALTER TABLE TB_CONTACT_BAK ADD PRIMARY KEY(CONTACT_NO);
ALTER TABLE TB_CONTACT_BAK ADD UNIQUE(EMAIL_ADRES);
ALTER TABLE TB_CONTACT_BAK ALTER COLUMN FIRST_NM SET NOT NULL;
ALTER TABLE TB_CONTACT_BAK ALTER COLUMN LAST_NM SET NOT NULL;
ALTER TABLE TB_CONTACT_BAK ALTER COLUMN EMAIL_ADRES SET NOT NULL;
∴ 위와 같이 제약 조건 따로 추가해야 됨.

기본키 ( PK, Primary Key )

테이블 내에서 고유하게 행을 식별하는 데 사용되는 컬럼 또는 컬럼들의 조합

#1-1. 테이블 생성하며 기본키 설정하기
CREATE TABLE TB_USER
(USER_NO INT,
PRIMARY KEY (USER_NO) );
→ USER_NO 컬럼으로 이루어진 인덱스가 자동 생성됨.

#1-2. 테이블 생성하며 기본키 설정하기
CONSTRAINT PK_TB_USER PRIMARY KEY (USER_NO) 
→ 기본키의 제약조건 명을 지정할 수 O

#2. 테이블 생성, 데이터 입력 이후 기본키(serial 타입) 추가하기
ALTER TABLE TB_VENDOR ADD COLUMN VENDOR_ID SERIAL PRIMARY KEY;
→ ADD COLUMN 이후에 [컬럼명][타입][제약조건_primary key] 작성

#3. 기본키 제거하기
ALTER TABLE TB_VENDOR DROP CONSTRAINT TB_VENDOR_PKEY;

1. 제약조건명 지정 X  →  2. 제약조건명 지정 O

 

외래키 ( FK, Foreign Key )

(부모)테이블의 기본키를 참조하는 (자식)테이블의 컬럼 또는 컬럼들의 조합

 

# 부모 테이블 생성 → PK: CUST_NO
CREATE TABLE TB_CUST
( CUST_NO INT ,
CUST_NM VARCHAR(255) NOT NULL ,
PRIMARY KEY(CUST_NO) );

#1. 자식 테이블 생성 및 외래키 지정
CREATE TABLE TB_CONTACT
( CUST_NO INT , 
CONSTRAINT FK_CUST_NO_TB_CUST FOREIGN KEY(CUST_NO) REFERENCES TB_CUST(CUST_NO));

#2. 자식 테이블 생성, 데이터 입력 이후 외래키 추가하기
CREATE TABLE TB_CONTACT
( CUST_NO INT )
↓
ALTER TABLE TB_CONTACT ADD CONSTRAINT FK_CUST_NO_TB_CUST FOREIGN KEY(CUST_NO) REFERENCES TB_CUST(CUST_NO) ON DELETE NO ACTION / SET NULL / CASCADE ;

 

외래키 지정 옵션별 에러 차이

1. (자식)테이블의 외래키에 데이터가 입력된 경우:

DELETE FROM TB_CUST WHERE CUST_NO = 1;

로 cust_no가 1인 행을 지우려고 할 때,

→ (부모)테이블의 기본키에 해당 데이터를 지우지 못 함 (에러 발생)

 

2. SET NULL 옵션 지정: 자식을 가진 부모행을 지울 경우, 자식행의 값을 NULL로 세팅

CONSTRAINT FK_CUST_NO_TB_CUST FOREIGN KEY(CUST_NO) REFERENCES TB_CUST(CUST_NO) ON DELETE SET NULL;

 

3. CASCADE 옵션 지정: 자식을 가진 부모행을 지울 경우, 자식행 또한 같이 삭제해버림.

CONSTRAINT FK_CUST_NO_TB_CUST FOREIGN KEY(CUST_NO) REFERENCES TB_CUST(CUST_NO) ON DELETE CASCADE;

제약조건

체크 제약조건

컬럼값에 대한 제약을 거는 것임. (잘못된 데이터가 입력되는 것을 방지하는 목적)

→ [칼럼명] CHECK ([제약 조건])

#1. 테이블 생성 및 제약 조건 지정
CREATE TABLE TB_EMP
( BIRTH_DE DATE CHECK (BIRTH_DE > '1900-01-01'),
JOIN_DE DATE CHECK (JOIN_DE > BIRTH_DE),
SAL_AMT numeric CHECK(SAL_AMT > 0) );

#2. 테이블 생성, 이후 제약 조건 추가하기
ALTER TABLE TB_EMP ADD CONSTRAINT TB_EMP_SAL_AMT_CHECK CHECK ( SAL_AMT > 0);

 

* 조건에서 벗어나 위반 시 에러 발생함.

 

 

유니크 제약조건

컬럼 또는 컬럼 조합에 대한 인덱스가 자동으로 생성됨.

→ [칼럼명] UNIQUE

#1. 테이블 생성 및 제약 조건 지정
CREATE TABLE TB_PERSON
( EMAIL_ADRES VARCHAR(150) UNIQUE );

CREATE TABLE TB_PERSON
( UNIQUE (FIRST_NM, LAST_NM, EMAIL_ADRES) ); 
→ 세 컬럼의 조합이 유니크해야 됨. ∴   A,B,123 - A,C,123 (O) // A,B,123 - A,B,123 (X)
 
#2. 테이블 생성, 이후 제약 조건 추가하기
유니크 인덱스 생성하기
CREATE UNIQUE INDEX IDX_TB_PERSON_01 ON TB_PERSON (FIRST_NM, LAST_NM, EMAIL_ADRES);
↓
유니크 제약조건 걸어주기
ALTER TABLE TB_PERSON ADD CONSTRAINT CONSTRAINT_TB_PERSON_01 UNIQUE USING INDEX IDX_TB_PERSON_01;

 

* 동일 데이터값을 넣어 위반 시 에러 발생함.

 

NOT NULL 제약조건

컬럼값에 NULL이 들어오는 것을 차단

 

NULL인지 아닌지 비교하는 방법

SELECT * FROM TB_PERSON WHERE EMAIL_ADRES IS NULL ;
SELECT * FROM TB_PERSON WHERE EMAIL_ADRES IS NOT NULL ;

 

제약조건 걸어주기

→ [칼럼명] NOT NULL

#1. 테이블 생성 및 제약 조건 지정
CREATE TABLE TB_PERSON
( LAST_NM VARCHAR (50) NULL,
EMAIL_ADRES VARCHAR(150) NOT NULL );

#2. 테이블 생성, 이후 제약 조건 추가하기 (컬럼을 SET을 변경하는 방식)
ALTER TABLE TB_PERSON ALTER COLUMN LAST_NM SET NOT NULL;

 

* NULL값을 넣어 위반 시 에러 발생함.

 

 

VIEW 

#1. 만들기
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

#2. 수정하기
CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

#3. 삭제하기
DROP VIEW view_name;