Gang Min

꾸준히 기록하는 블로그.

SQL 정리 - (3)

데이터 조작과 테이블 관리

  • 데이터 조작
    • create table
    • insert
    • 작은 따옴표 넣기
    • 테이블 복사
    • update
      • join
    • delete
    • on conflict
    • upsert
    • export
    • import
  • 테이블 관리
    • 데이터 타입 boolean
      • 참과 거짓의 값을 저장한다.
    • 데이터 타입 character
      • char 고정형 길이의 문자열을 저장한다.만약 CHAR(10)인데 ‘ABCDE’만 입력할 경우 실제로는 ‘ABCDE’ 뒤로 공백을 붙여 저장한다.
      • VARCHAR 가변형 길이의 문자열을 저장한다. 만약 VARCHAR(10)인데 ‘abcde’만 입력할 경우 실제로 ‘abcde’만 저장한다. 공백을 붙이지 않는다.
      • text 대용량의 문자 데이터를 저장한다.
    • 데이터 타입 numeric
      • int 정수형 데이터를 저장한다. 크기는 4byte이다.
      • smallint 정수형 데이터를 저장한다. 크기는 2byte이다.
      • float 부동 소수정의 데이터를 저장 크기는 8byte이다.
      • numeric numeric(15,2)와 같이 전체 크기와 소수점의 자리를 지정할 수 있다.
    • 테이블 생성시 칼럼의 제약조건
      • not null 해당 제약 조건이 있는 컬럼은 NULL이 저장될수 없다
      • UNIQUE 해당 제약 조건이 있는 컬럼의 값은 테이블 내에서 유일해야 한다.(null 가능)
      • PRIMARY 해당 제약 조건이 있는 컬럼의 값은 테이블내에서 유일해야 하고 반드시 NOT NULL이어야 한다 .
      • CHECK 해당 제약 조건이 있는 컬럼은 지정하는 조건에 맞는 값이 들어가야 한다.
      • REFERENCES 해당 제약 조건이 있는 컬럼의 값은 참조하는 테이블의 특정 컬럼에 값이 존재해야 한다.
    • ctas(if not exists)
    • check
    • rename
    • add column
    • drop column
    • type 변경
    • rename
    • 테이블 제거
    • 임시 테이블
    • TRUNCATE

CREATE TABLE LINK (

ID SERIAL PRIMARY KEY

, URL VARCHAR (255) NOT NULL

, NAME VARCHAR (255) NOT NULL

, DESCRIPTION VARCHAR (255)

, REL VARCHAR (50) );

INSERT

INTO LINK (URL, NAME)

VALUES (‘http://naver.com’,’Naver’) ;

COMMIT; #데이터를 insert해주면 commit을 해줘야함

  • 작은 따옴표 넣기(‘’‘(넣고싶은거)’’’)

INSERT INTO LINK (URL, NAME)

VALUES (‘'’http://naver.com’’’,’'’Naver’’’) ;

COMMIT;

  • 테이블 껍데기 복사

CREATE TABLE LINK_TMP AS

SELECT * FROM LINK WHERE 0=1;

  • 껍데기에 데이터 넣기

INSERT INTO LINK_TMP

SELECT *

FROM LINK ;

COMMiT;

  • update

ALTER TABLE LINK ADD COLUMN LAST_UPDATE DATE;

ALTER TABLE LINK ALTER COLUMN LAST_UPDATE SET DEFAULT CURRENT_DATE;

SELECT * FROM LINK;

  • default값으로 바꿔주기
    • where절 없으면 전체컬럼 선택

UPDATE LINK

SET LAST_UPDATE = DEFAULT

WHERE LAST_UPDATE IS NULL;

COMMiT;

UPDATE PRODUCT A

SET NET_PRICE = A.PRICE - (A.PRICE * B.DISCOUNT)

FROM PRODUCT_SEGMENT B

WHERE A.SEGMENT_ID = B.ID;

SELECT * FROM PRODUCT;

COMMIT;

DELETE

FROM LINK_TMP A

USING LINK B

WHERE A.ID = B.ID ;

COMMIT;

SELECT * FROM CUSTOMERS;

INSERT INTO CUSTOMERS (NAME, EMAIL)

VALUES ( ‘Microsoft’, ‘hotline@microsoft.com’ )

ON CONFLICT (NAME) #conflict가 있어도 그냥 넘어가라

DO NOTHING;

INSERT INTO CUSTOMERS (NAME, EMAIL)

VALUES ( ‘Microsoft’, ‘hotline@microsoft.com’

)

ON CONFLICT (NAME)

DO UPDATE

SET EMAIL = EXCLUDED.EMAIL || ‘;’ || CUSTOMERS.EMAIL; #충돌하면 뒤에 이메일 살려줘. ||는 연결할 때 사용 #excluded는 위에서 insert 시도한 email값

COMMIT;

자료 export

COPY CATEGORY(CATEGORY_ID, NAME, LAST_UPDATE)

TO ‘C:\temp\DB_CATEGORY.csv’

DELIMITER ‘,’

CSV HEADER;

자료 import

COPY CATEGORY_IMPORT(CATEGORY_ID, “NAME”, LAST_UPDATE)

FROM ‘C:\temp\DB_CATEGORY.csv’

DELIMITER ‘,’

CSV HEADER ;

조건에 맞는 테이블 생성

CREATE TABLE ACTION_FILM AS

SELECT A.FILM_ID , A.TITLE , A.RELEASE_YEAR , A.LENGTH , A.RATING

FROM FILM A, FILM_CATEGORY B

WHERE A.FILM_ID = B.FILM_ID

AND B.CATEGORY_ID = 1 ;

없으면 만들어라(있으면 에러 발생하지 않고 안만듬)

CREATE TABLE IF NOT EXISTS ACTION_FILM AS

SELECT A.FILM_ID , A.TITLE , A.RELEASE_YEAR , A.LENGTH , A.RATING

FROM FILM A, FILM_CATEGORY B

WHERE A.FILM_ID = B.FILM_ID

AND B.CATEGORY_ID = 1 ;

ALTER TABLE LINKS ADD COLUMN ACTIVE BOOLEAN;

ALTER TABLE LINKS DROP COLUMN ACTIVE;

ALTER TABLE LINKS RENAME COLUMN TITLE TO LINK_TITLE;

ALTER TABLE LINKS ADD COLUMN TARGET VARCHAR(10);

ALTER TABLE LINKS ALTER COLUMN TARGET SET DEFAULT ‘_blank’;

ALTER TABLE LINKS ADD CHECK (TARGET IN (‘_self’, ‘_blank’, ‘_parent’, ‘_top’));

ALTER TABLE VENDORS RENAME TO SUPPLIERS; #table

ALTER TABLE TB_CUST

ADD COLUMN CONTACT_NM VARCHAR NOT NULL;

#이미 값이 있는 행에 새로운 컬럼을 추가할 때는 not null로 추가할 수가 없다. 무조건 null값이 들어감.

ALTER TABLE TB_CUST

ADD COLUMN CONTACT_NM VARCHAR NULL; #null값으로 먼저 넣어준다.

UPDATE TB_CUST

SET CONTACT_NM = ‘홍길동’

WHERE CUST_ID = 1; #값 입력

COMMIT;

SELECT * FROM TB_CUST;

ALTER TABLE TB_CUST #다시 설정을 not null로 바꿔줌

ALTER COLUMN CONTACT_NM SET NOT NULL;

ALTER TABLE BOOKS DROP COLUMN CATEGORY_ID;#다른 엔터티에서 category_id를 참조하고 있지 않으면 삭제 가능, 즉 자기가 다른 엔터티를 참조하는건 삭제가 가능하다.

ALTER TABLE BOOKS DROP COLUMN PUBLISHER_ID; #book_info라는 데에서 publisher_id를 참조하고 있어서 삭제 불가능

ALTER TABLE BOOKS DROP COLUMN PUBLISHER_ID CASCADE; #이러면 삭제 가능 book_info도 사라짐

타입변경

ALTER TABLE ASSETS ALTER COLUMN NAME TYPE VARCHAR(50);

#타입 안바뀔 때

ALTER TABLE ASSETS

ALTER COLUMN ASSET_NO TYPE INT USING ASSET_NO::INTEGER;

ALTER TABLE CUSTOMER_GROUPS

RENAME COLUMN NAME TO GROUP_NAME; #rename을 사용하면 fk도 변경되서 다 반영된다. 참조를 하는데 문제가 생기지 않는다.

DROP TABLE PAGE; #부모 테이블은 못날린다. cascade를 사용해서 부모를 날리면 fk관계(참조 무결성 제약조건)이 사라짐.

임시테이블 : 세션이 끊어지면 날라간다.

CREATE TEMP TABLE TB_CUST_TEMP_TEST(CUST_ID INT);

TRUNCATE : 대용량의 테이블을 빠르게 지우는 방법

TRUNCATE TABLE BIG_TABLE;

ROLLBACK; 복구가능 #POSTGRE SQL에서만 가능

  • References

    • 패스트캠퍼스 모두를 위한 SQL강의

Comments

You May Also Enjoy