[TIL] 20.12.28 제약조건, 테이블, 뷰, 파이썬 연동

[20.12.28] 제약조건, 테이블, 뷰, 파이썬 연동

 

지난 20.12.23 TIL의 제약 조건에서 이어서 다시 제약 조건부터다.

 

* 제약 조건(Constraint)

 

1) UNIQUE 제약 조건

> 중복되지 않는 값을 가져야 한다는 조건이다.

> 기본키와 비슷하나 UNIQUE는 NULL을 허용한다.

 중복되지 않아야 하지만 NULL 중복은 상관없다.

CREATE TABLE userTBL(
	userID CHAR(8) PRIMARY KEY,
    name VARCHAR(10) NOT NULL,
    birthYear INT NOT NULL,
    email CHAR(30) NULL UNIQUE
);

> 회원의 이메일은 없을 수 있지만(NULL) 있다면 중복되면 안 된다.(UNIQUE)

 

2) CHECK 제약 조건

> 입력되는 데이터를 검증하는 기능이다.

CREATE TABLE userTBL(
    userID CHAR(8) CONSTRAINT pk1 PRIMARY KEY,
    name VARCHAR(10),
    birthYear INT CHECK (birthYear >= 1900 AND birthYear <= 2021),
    mobile CHAR(3) NULL,
    CONSTRAINT ck_name CHECK (name IS NOT NULL)
);

> CHECK 제약 조건을 통해서 생년을 1900~2021년으로 제한하고 있으며 NULL을 허용하지 않는다.

 

3) DEFAULT 정의

> 값을 입력하지 않았을 시 자동으로 입력되는 값을 정의한다.

# 테이블 생성
CREATE TABLE userTBL(
    userID CHAR(8) PRIMARY KEY,
    name VARCHAR(10),
    birthYear INT NOT NULL, DEFAULT -1,
    addr CHAR(2) NOT NULL DEFAULT '서울',
    mobile CHAR(3) NULL,
    height INT NULL DEFAULT 170
);
# 데이터 삽입
# (1)
INSERT INTO userTBL VALUES('HGD', '홍길동', default, default, '010', default);
# (2)
INSERT INTO userTBL(userID, name) VALUES('KCS', '김철수');
# (3)
INSERT INTO userTBL VALUES('LYH', '이영희', 1990, '대전', '012', 170);

> 1번 예시에서는 default 입력 시에 DEFAULT로 정의된 값이 자동 입력된다.

> 2번 예시에서는 열 이름이 명시되지 않았을 때 DEFAULT 값을 자동 입력한다.

> 3번 예시에서는 값을 직접 명시해주면 DEFAULT 값은 무시된다.

 

4) NULL 값 허용

> 컬럼에 NULL 값을 허용하려면 NULL, 허용하지 않으려면 NOT NULL을 사용한다.

> PRIMARY KEY가 설정된 열은 자동으로 NOT NULL이 된다.

CREATE TABLE ex2(
    col1 CHAR(5),
    col2 VARCHAR(10) NOT NULL,
    col3 INT,
    PRIMARY KEY(col1),
    FOREIGN KEY(col3) REFERENCES ex1(col1)
);

 

 

* 테이블 (Table)

- 테이블 생성에 대해서는 이미 전에 배웠다. 그 외에 내용을 다룬다.

 

1) 테이블 삭제

> 외래키 제약 조건의 '기준' 테이블은 삭제가 불가능하다.

> 삭제하려면 참조하고 있는 외래키 테이블을 먼저 삭제해야 한다.

> 동시에 여러 테이블을 삭제할 수도 있다.

# (1)
DROP TABLE tbl;
# (2)
DROP TABLE tb1, tb2, tb3;

> 1번 예시는 단일 삭제, 2번 예시는 다중 삭제이다.

 

2) 테이블 수정

> ALTER TABLE ~ 을 이용해서 수정한다.

> 테이블 내에 무언가 추가, 삭제, 변경 모두 ALTER TABLE을 이용한다.

 

(1) 열 추가

> 기본적으로는 테이블 맨 뒤에 추가되지만 순서를 지정할 수도 있다.

# (1)
ALTER TABLE usertbl
ADD tmp_col VARCHAR(20)
DEFAULT 'test' -- 디폴트 값
NULL; -- NULL 허용
# (2)
ALTER TABLE usertbl
ADD col0 INT
FIRST;
# (3)
ALTER TABLE usertbl
ADD col1 INT
AFTER col0;

> 1번 예시는 단순히 맨 뒤에 열을 추가한다.

> 2번 예시는 'FIRST'를 이용해서 맨 앞에 열을 추가한다.

> 3번 예시는 'AFTER 열이름'을 이용해 특정 열 다음에 추가한다.

 

(2) 열 삭제

> 만약 제약 조건이 걸린 열을 삭제할 경우 제약 조건을 먼저 삭제 후 열을 삭제한다.

ALTER TABLE usertbl
DROP COLUMN col0;

 

(3) 열 변경

> 열의 이름, 데이터 형식을 변경할 수 있다.

ALTER TABLE usertbl
CHANGE COLUMN col0 new_col0 DATE;

> 열의 이름은 'col0' -> 'new_col0', 데이터 형식은 'DATE' 타입으로 변경했다.

 

(4) 열 제약 조건 추가/삭제

# (1)
ALTER TABLE usertbl
ADD CONSTRAINT const FOREIGN KEY(u_id)
REFERENCES parentTBL pk_id;
# (2)
ALTER TABLE usertbl
ADD CONSTRAINT const PRIMARY KEY(u_id);
# (3)
ALTER TABLE usertbl
MODIFY col1 INT CONSTRAINT const NOT NULL;
# (4)
ALTER TABLE usertbl DROP CONSTRAINT const;
# (5)
ALTER TABLE usertbl DROP PRIMARY KEY;

> 1번 예시는 외래키 제약 조건을 추가하는 예시다.

> 2번 예시는 기본키 제약 조건을 추가하는 예시다.

> 3번 예시는 NOT NULL 제약 조건을 추가하는 예시다.

> 4번 예시는 'const'라는 제약 조건을 삭제하는 예시다.

> 5번 예시는 기본키 제약 조건을 삭제하는 예시다.

> 기본키 제약 조건은 삭제 시 참조하는 다른 테이블(외래키)이 있는지 먼저 확인한다.

 

 

* 뷰(View)

- 일반 사용자의 입장에서 테이블과 동일하게 사용하는 개체다.

- 뷰를 생성한 후엔 테이블처럼 접근이 가능하고 결과도 동일하다.

- 뷰는 실제로 물리적 공간을 차지하지는 않는다.

- 다만 뷰 선언 시 넣어놓은 쿼리문을 저장하고 있으며 뷰 호출 시엔 이 쿼리를 가져오는 것이다.

 

1) 뷰 생성

CREATE VIEW v_usertbl
AS
SELECT userid FROM usertbl;

> 'v_usertbl'이라는 이름의 뷰를 생성하고 이 뷰는 마치 AS 뒤에 오는 쿼리의 실행 결과와 같다.

 

2) 뷰의 장점

> 사용자가 중요한 정보에 바로 접근하지 못하게 함으로써 보안성이 향상된다.

> 긴 쿼리를 뷰로 작성하여 복잡한 쿼리를 단순화할 수 있다.

 

 

* 파이썬, mysql 연동

- 앞서 배운 파이썬과 mysql을 연동해서 사용할 수 있다.

- mysql과의 연동이지만 mariaDB도 동일하게 사용이 가능하다.

- [Python] 파이썬에서 MySQL(MariaDB) 사용하기 참고

 

* 실습문제

- <사원 조회 프로그램> 작성하기

- 출력 형식을 보여주는 문제를 볼 때마다 느끼는 점은 디테일에 대한 불편함이다...

- '입력하세요?' 같은 어투 이상한 문장이라던가

- 메뉴 선택 후에 이름은 (first_name)으로 db 컬럼이 노출됐는데 사번, 생년은 다르다던가(통일성 어디...?)

- 이름, 사번의 검색 결과 출력 형식과 생년 검색 결과 출력 형식이 다르다.

- 다른 건 몰라도 마지막 출력 형식이 서로 다른건 쿼리를 다르게 짰다는 이야기인데

- 굳이... 왜...? 라는 생각이 든다... 하나 생각해볼 만한 가능성은 나머지는 fetchone이라서 결과를 일일이 인덱싱해도 괜찮았는데 생년 조회만 fetchall을 써줘야 해서 일일이 인덱싱 하기 귀찮아서 그냥 놔둔 느낌이다. 근데 이건 인정이지...

- 그래서 매번 풀 때마다 그냥 내 마음이 편안하도록 알아서 수정해서 작성하긴 한다...ㅋㅋ

 

 

# 풀이
import pymysql

# db 연결
def dbconn():
    conn = pymysql.connect (host='127.0.0.1',user ='root',password='비밀번호',db='employees',charset='utf8')
    return conn

# 이름으로 사원 검색
def name_search(conn):
    print('조회할 사원의 이름(first_name)을 입력하세요.')
    first_name = input('> ')
    cur = conn.cursor()
    query = 'select * from employees where first_name = "'+first_name+'"'
    cur.execute(query)
    row = cur.fetchone()
    data1 = row[0]
    data2 = row[1]
    data3 = row[2]
    data4 = row[3] 
    data5 = row[4]
    data6 = row[5]
    print(f'{data1}\t{data2}\t{data3}\t{data4}\t{data5}\t{data6}')
    
# 사번으로 사원 검색
def emp_no_search(conn):
    print('조회할 사번을 입력하세요.')
    emp_no = input('> ')
    cur = conn.cursor()
    query = 'select * from employees where emp_no = "'+emp_no+'"'
    cur.execute(query)
    row = cur.fetchone()
    data1 = row[0]
    data2 = row[1]
    data3 = row[2]
    data4 = row[3] 
    data5 = row[4]
    data6 = row[5]
    print(f'{data1}\t{data2}\t{data3}\t{data4}\t{data5}\t{data6}')

# 생년으로 사원 검색
def birth_search(conn):
    print('조회할 생년을 입력하세요.')
    birth = input('> ')
    cur = conn.cursor()
    query = 'select * from employees where birth_date like "'+birth+'%" limit 10'
    cur.execute(query)
    while 1:
        row = cur.fetchone()
        if row == None:
            break
        data1 = row[0]
        data2 = row[1]
        data3 = row[2]
        data4 = row[3] 
        data5 = row[4]
        data6 = row[5]
        print(f'{data1}\t{data2}\t{data3}\t{data4}\t{data5}\t{data6}')
        
# 프로그램 메뉴 출력
def program(conn):
    while 1:
        print('-'*60)
        print('<사원 조회 프로그램입니다.>')
        print(''' 다음 메뉴 중 하나를 입력하세요.
        1.\t이름으로 검색
        2.\t사번으로 검색
        3.\t생년으로 검색
        4.\t프로그램 종료''')
        num = input('> ')
        if num == '1':
            name_search(conn)
        elif num == '2':
            emp_no_search(conn)
        elif num == '3':
            birth_search(conn)
        elif num == '4':
            print('프로그램을 종료합니다.')
            break
        else:
            print('잘못된 입력입니다. 프로그램을 종료합니다.')
            break

def main():
    conn=dbconn()
    program(conn)
    conn.close()
    
if __name__=="__main__" :
    main()

> 코드 길이가 조금 긴데 사실상 조회 결과를 인덱싱 하느라 길어 보이는 거지, 단순 반복이다.

> db를 만들고 파이썬으로 접근해서 사용하는 게 어려울 줄 알았는데 생각보다 엄청 쉬웠다.

 

 

※ 이 게시물은 '이것이 MySQL이다' 교재와 엔코아 플레이데이터에서 배운 내용을 토대로 작성되었습니다.