언제쯤 검색없이 코딩을 짤까요.

Q.

파일명: a.txt

내용

이름,국어,영어,수학
aaa,43,54,65
bbb,7,67,87
ccc,54,65,76

a.txt 파일을 오픈하여 성적추출하기.

import numpy as np

#파일 내용을 한 줄 씩 읽음

f = open('./stu_data.txt', 'r')
datas = []
while True:
    s = f.readline()    # s: '1, aaa, 43, 65, 78 \n'
    if s == '':
        break
    s = s.split('\n')[0]   #엔터를 잘라냄 -> s: '1, aaa, 43, 465, 78'
    stu = s.split(',')    # stu: ['1', 'aaa', '43', '65', '78']
    for i in range(0, 2):
        stu.append(0)
    datas.append(stu)
f.close()

datas

여기에서 s.split('\n')[0]는 \n을 기준으로 두 조각(예를들어, aaa,43,54,65\n 이 있다면 \n을 기준으로 앞과 뒤를 나누게 되어 두조각이 됌)에서 0번째 방에 해당하는 앞 조각만 사용하겠다는 뜻이다.

# datas리스트를 numpy 배열로 변환
arr = np.array(datas)
arr

# 각 학생의 이름만 추출
names = arr[:,0]
names

# 각 학생의 점수 추출
score = arr[:, 1:]
score

#점수 배열 요소 타입을 float으로 변경
x = arr[:, 1:]
score = x.astype(np.float32
score

#모든 줄의 총점, 평균 계산
'''
for i in score:
    k = i[1:4]      # k: [국,영,수]
    sum1 = k.sum()
    i[4]=sum1
    i[5]=sum1/3
'''

score[:,4] = score[:,1:4].sum(axis=1)   #axis=1 : 가로줄
score[:,5] = score[:,4]/3

score
print('각 학생의 총점,평균')
for i in range(0, 5):
    print(names[i], end='\t')
    print(score[i])

#학생들의 국어총점, 영어총점, 수학총점
'''
kor = score[:,1].sum(axis=0)
eng = score[:.2].sum(axis=0)
math = score[:,3].sum(axis=0)
'''
sums = score.sum(axis=0)    #axis=0 : 세로줄

print('국어총점:', sums[1])
print('영어총점:', sums[2])
print('수학총점:', sums[3])
print('전체총점:', sums[4])
print('전체평균합:', sums[5])

주제 정하기

  수업시간에서 예제로 다루었던 수강,수업관리 프로그램은 접근성이 높아서 쇼핑몰 등 구매 프로그램을 하려했으나 1인당 1테이블(우리팀은 총 5분)을 전담하여 만들어야 하기에 영화예약프로그램을 하기로 결정.

 DAO와 SERVICE를 정하기에 앞서 기능을 먼저 정했다.

• 회원가입(개인정보 확인, 수정, 삭제 등. 로그인, 로그아웃도 포함)

• 영화(영화 정보: 영화명, 개봉일, 감독, 배우)

• 상영관(상영관 정보: 상영관명, 상영시간)    

• 자리(상영관마다 10개의 자리보유(좌석마다 금액의 차등을 줌), 자리선점의 유무)

• 예약(영화선택, 시간선택(또는 상영관선택), 자리선택, 결제)

테이블 만들기(ER-Diagram)

 테이블은 인원에 맞춰 5개가 만들어졌다. ER다디어그램에서 보다시피 예약(reserve)테이블이 모든 테이블과 조인이 되어있는 상태... 그나마 조인이 적은 영화(movie) 테이블을 맡게 되었으나 상영관(theater)와 조인이 되어있는 상황이라 영화를 수정하고 삭제하는 것이 다소 염두해야할 부분이었다.

클래스 다이어그램

기능 명세화

1. 메뉴

  메뉴를 짜면서 기능을 명세화 시키는 것이 훨씬 빠르고 수월했다. (아주 효율적인 방법이라고 생각한다)

 기본 메뉴는 위와 같은 구성으로 짜기로 결정하였다.

2. Member  

  가입정보 : 아이디, pwd, 이름, 핸드폰번호, 포인트(예매 후에 포인트가 쌓이는 방식)

이번 팀플젝에서 가장 포인트로 생각했던 것은 일반 사용자와 관리자 모두 사용할 수 있는 프로그램을 만들고 싶었다. 그래서 Member테이블의 service를 사용자와 관리자모드 두개로 나누어 만들었다.

가입 후 사용자가 로그인을 하면 아래와 같은 메뉴들을 선택할수 있도록 구현했고,

관리자 메뉴를 따로 만들어서 

회원관리가 가능하도록 구현했다. 당연히 관리자메뉴는 관리자 아이디로만 로그인해서 접근할 수 있게!

하지만 회원과 관리자가 이용하는 Dao는 겹치는 메소드가 많았기 때문에 공통으로 사용했다.

 3. Movie

 영화 테이블은 관리자모드로만 접근이 가능하게 했다. (위의 관리자 메뉴를 참고)

영화테이블은 이런식으로 영화명, 개봉일, 감독, 배우의 정보를 담고 있어야하고 상영관과 조인을 하기 위해 코드를 auto increment로 자동으로 배정되게 해서 외래키로 사용하게 했다.
추후에 구현을 돌려보며 오류를 잡아낼 때 참조값 무결성 문제가 발생해 테이블을 다시 갈아엎었다는 전설이...

on delete cascade로 해결!

영화메뉴는 이렇게 구성이 되었고, 영화검색, 수정,삭제에서 영화명에 찾고자 하는 단어가 들어가있는 영화를 모두 출력하게끔 코딩했다. 나중에 수정과 삭제를 위해 영화명을 검색했을 때 찾은 단어가 들어가있는 모든 영화들의 정보가 바뀌게 되는 것을 방지하기 위함이었다.

Dao에서 select 메소드를 이렇게 짜서

검색해서 수정,삭제할 영화의 코드를 선택하여 안전하게 정보변경이 되게끔 코딩했다.

우선 단어를 검색하여 모두다 출력하게 한 것과 if문으로 오류를 줄인 것이 뿌듯하다. (물론 조원의 도움이 있었다. len(movie)로 검색한 영화가 참인 상태로 if를 짠 것이 새로운 방법의 제시라 신세계였다)

영화를 등록하고 검색하면 '해리포터' 단어가 들어간 영화가 출력된다.

수정도 이런식으로~

6번의 영화자동등록은 크롤링으로 박스오피스 5위까지 해당하는 영화를 자동으로 영화테이블에 추가하는 건데 조원이 짠 코드이고 내가 완벽하게 공부하지 않은 부분이라서 우선 추후에 크롤링 공부 후 포스팅하겠다.

4. Theater

상영관도 역시 관리자모드로만 접근이 가능.

영화테이블처럼 코드를 auto increment로 자동배정하게 테이블을 만들었고 영화코드와 서로 참조되어있는 상태다.

상영관 메뉴는 위와 같이 구성되어있고

시어터는 movie_code를 파라미터로 받아 선택, 수정이 되게 하였다.  

5. Seat 

시트 테이블은 파이썬에서 상영관을 등록하면 그 상영관에 자동으로 좌석 20개가 만들어지고 1번좌석~5좌석은 10,000원, 6번좌석~10좌석은 15,000으로 가격의 차등을 두었다.

예매시 이미 자리가 선점되어있는지 확인 후 좌석을 고르게끔 자리선점 컬럼도 보이게 하였다.

6. Reserve   

  이 프로그램의 핵심 예약 테이블.

영화, 상영관, 자리, 회원정보 모두와 조인이 되어있어 다 코드를 외래키로 참조하고 있다.

무슨_코드가 계속 보이는게 눈이 아프다.

예약을 진행해본 화면이다.

후기

각자 코드를 구현한 뒤 최종적으로 합치는 과정이 가장 떨렸다. 얼레벌레 조장이 되어버려서 추합하여 메뉴를 굴리게 되었는데 그 와중에 잠깐씩 나오던 에러들을 수정하고 다시 진행하는 것이 떨렸지만 짜릿했던 순간이었다. 

참조값 때문에 삭제 에러가 나 테이블도 다시 만들고, 일반 사용자로 로그인해 관리자모드로 접근을 해본다던가 검색명을 엉뚱하게 하던가 최대한 여러 에러들을 염두하여 실행하고 고쳐나갔다. 분명 완벽하다 생각했는데 발표 준비를 하다가 발견하는 에러들도 수두룩했다. 아직 못찾아낸 에러들도 존재하겠지만 뭐... except Exception as e로 대충 흐린눈하기...^^

첫 팀플임에도 완성도가 매우 높았다. 좋은 조원덕에 첫 단추를 잘 꽨느낌이다. 다음 프로젝트도 좋은 퀄이 나오기를!

import  member.memModel as mem

class memMenu:
    def __init__(self):
        self.memService = mem.MemService()

    def memRun(self):
        flag = True
        while flag:
            m = input('1.회원가입 2.로그인 3.내정보확인 4.내정보수정 5.로그아웃 6.탈퇴 7.메뉴나감')
            if m=='1':
                self.memService.join()
            elif m=='2':
                self.memService.login()
            elif m=='3':
                self.memService.printMyInfo()
            elif m=='4':
                self.memService.editMyInfo()
            elif m=='5':
                self.memService.logout()
            elif m=='6':
                self.memService.delMyInfo()
            elif m == '7':
                break

Main

import Menu

def main():
    m = Menu.memMenu()
    m.memRun()

main()

import pymysql

#테이블값 저장
class MemVo:
    def __init__(self, id=None, pwd=None, name=None, email=None):
        self.id = id
        self.pwd = pwd
        self.name = name
        self.email = email

    def __str__(self):
        return 'id:'+self.id+' / pwd:'+self.pwd+' / name:'+self.name+' / email:'+self.email

class MemDao:#member 테이블과 관련된 db 작업만 구현
    def __init__(self):
        self.conn = None    #커넥션 객체 담을 멤버 변수

    #db연결함수. db 사용전 로그인하는 작업을 수행
    def connect(self):
        self.conn = pymysql.connect(host='localhost', user='root', password='1234', db='encore', charset='utf8')

    #db 닫는 함수
    def disconnect(self):
        self.conn.close()

    def insert(self, vo):#member 테이블에 한 줄 추가. 회원가입 정보를 저장
        self.connect() #db 연결
        cur = self.conn.cursor()  #사용할 커서 객체 생성
        #insert into member values(vo.id, vo.pwd, vo.name, vo.email)
        sql = "insert into member values(%s, %s, %s, %s)"   #변수가 들어갈 위치에 %s와 같은 포맷문자 지정
        vals = (vo.id, vo.pwd, vo.name, vo.email)
        cur.execute(sql, vals)
        self.conn.commit()
        self.disconnect()

    def select(self, id):
        self.connect()  # db 연결
        cur = self.conn.cursor()  # 사용할 커서 객체 생성
        sql = "select * from member where id=%s"  # 변수가 들어갈 위치에 %s와 같은 포맷문자 지정
        vals = (id,)
        cur.execute(sql, vals) #쿼리 실행. 검색 결과가 cur에 저장
        row = cur.fetchone()    #cur 객체에서 검색된 한 줄 fetch. 검색 결과 없으면 None반환
        self.disconnect()   #db 닫음
        if row!=None:   #검색된 결과가 있으면
            vo = MemVo(row[0], row[1], row[2], row[3])#id, pwd, name, email
            return vo

    def update(self, id, new_pwd):
        self.connect()  # db 연결
        cur = self.conn.cursor()  # 사용할 커서 객체 생성
        sql = "update member set pwd=%s where id=%s"  # 변수가 들어갈 위치에 %s와 같은 포맷문자 지정
        vals = (new_pwd, id)
        cur.execute(sql, vals)
        self.conn.commit()
        self.disconnect()

    def delete(self, id):
        self.connect()  # db 연결
        cur = self.conn.cursor()  # 사용할 커서 객체 생성
        sql = "delete from member where id=%s"  # 변수가 들어갈 위치에 %s와 같은 포맷문자 지정
        vals = (id,)
        cur.execute(sql, vals)
        self.conn.commit()
        self.disconnect()

class MemService:#제공할 기능 구현

    '''
    로그인 상태 유지 => 로그인함수에서 처리한 아이디를 어딘가 보관 =>
    로그인함수(): id, pwd를 입력받아서 db에 일치하는가 확인. 로그인 성공.
    기억상실처리 => MemService.login_id에 로그인 성공한 id를 저장. 로그아웃할때까지 유지
    MemService.login_id:None => 로그아웃 상태
    MemService.login_id:'aaa' => 'aaa'로 로그인 상태
    내정보확인 메뉴선택:
    '''

    login_id = None #로그인한 사람의 id보관. None이면 로그인 안된 상태

    def __init__(self):
        self.dao = MemDao() #대부분 기능이 db작업 필요. db작업 전담하는 dao객체를 멤버변수로 생성

    def join(self): #회원가입 기능
        print('회원가입')
        id = input('id:')
        pwd = input('pwd:')
        name = input('name:')
        email = input('email:')
        try:
            vo = MemVo(id, pwd, name, email)
            self.dao.insert(vo)#db에 id, pwd, name, email 저장
        except Exception as e:
            print(e)
        else:
            print('회원가입완료')

    def login(self):#로그인 기능
        print('로그인')
        if MemService.login_id != None: #로그인 상태
            print('이미 로그인 중')
            return
        id = input('id:')
        pwd = input('pwd:')
        vo = self.dao.select(id) #입력한 id로 검색
        if vo == None: #검색 결과 없음
            print('없는 아이디')
        else:
            if pwd == vo.pwd: #입력한 pwd와 vo에 담긴 pwd가 일치. 로그인 성공
                print('로그인 성공')
                MemService.login_id = id #로그인상태 유지
            else:
                print('패스워드 불일치')

    def logout(self):
        print('로그아웃')
        if MemService.login_id == None:#로그인 상태 확인
            print('로그인 먼저 해라')
            return
        MemService.login_id = None #로그아웃 상태

    def printMyInfo(self):#내정보확인기능
        print('내정보확인')
        if MemService.login_id == None:#로그인 상태 확인
            print('로그인 먼저 해라')
            return
        vo = self.dao.select(MemService.login_id)#로그인한 사람의 id를 select(id)에 담아 호출하면 로그인한 id정보 검색
        print(vo)

    def editMyInfo(self):#MemService.login_id로 새 pwd입력받아서 수정
        print('내정보수정')
        if MemService.login_id == None:#로그인 상태 확인
            print('로그인 먼저 해라')
            return
        new_pwd = input('새 pwd:')
        self.dao.update(MemService.login_id, new_pwd)

    def delMyInfo(self):#MemService.login_id로 삭제
        print('탈퇴')
        if MemService.login_id == None:
            print('로그인 먼저 해라')
            return

        self.dao.delete(MemService.login_id)#db에서 id가 동일한 행 삭제
        MemService.login_id = None  #로그아웃 처리

import pymysql

def select():
    conn, cur = None, None
    conn = pymysql.connect(host='127.0.0.1', user = 'root', password='1234',db = 'encore', charset='utf8')
    cur = conn.cursor()
    sql = 'select * from departments'
    cur.execute(sql)
    for row in cur:   #커서 객체에서 검색결과 한 줄씩 fetch
        print('dept_id:',row[0],'/ dept_name:', row[1], ' / manager_id:', row[2], ' / location_id:', row[3])

    conn.close()

def insert():
    conn, cur = None, None
    conn = pymysql.connect(host='127.0.0.1', user='root', password='1234', db='encore', charset='utf8')
    cur = conn.cursor()
    sql = 'insert into departments values(280, "dept test1", null, 1700)'
    cur.execute(sql)
    conn.commit()
    conn.close()

def update():
    conn, cur = None, None
    conn = pymysql.connect(host='127.0.0.1', user='root', password='1234', db='encore', charset='utf8')
    cur = conn.cursor()
    sql = 'update departments set department_name="가나다라", manager_id=100 where department_id=280'
    cur.execute(sql)
    conn.commit()
    conn.close()

def selectById(id):
    conn, cur = None, None
    conn = pymysql.connect(host='127.0.0.1', user='root', password='1234', db='encore', charset='utf8')
    cur = conn.cursor()
    sql = 'select * from departments where department_id='+str(id)
    cur.execute(sql)
    row = cur.fetchone()   #한줄 fetch
    if row == None:
        print('없는 부서 번호')
    else:
        print('dept_id:',row[0],'/ dept_name:', row[1], ' / manager_id:', row[2], ' / location_id:', row[3])
    conn.commit()
    conn.close()

def delete():
    conn, cur = None, None
    conn = pymysql.connect(host='127.0.0.1', user='root', password='1234', db='encore', charset='utf8')
    cur = conn.cursor()
    sql = 'delete from departments where department_id=280'
    cur.execute(sql)
    conn.commit()
    conn.close()

def main():
    insert()
    select()
    selectById(280)
    update()
    selectById(290)
    
main()

함수

물론 Mysql에서 프로시저만 사용하는 것은 아니다. 함수를 따로 정의해서 사용해도 된다.

SET GLOBAL log_bin_trust_function_creators = 1;

우선 위의 코드를 실행시키고 다음과 같이 함수를 만들어주면 된다.

CREATE DEFINER=`root`@`localhost` FUNCTION `f1`(n1 varchar(20), n2 varchar(20)) 
RETURNS varchar(50) CHARSET utf8
BEGIN
	declare name varchar(50);
    set name = concat_ws(' ', n1, n2);
RETURN name;
END

하지만 mysql에서는 자체 내장된 함수만을 사용하는 것을 선호하기 때문에 프로시저를 이용하는 것을 추천한다.

트리거(Trigger)

insert, update, delete 동작이 실행될떄마다 이 동작 전이나 후에 실행할 코드를 등록하는 방법

create trigger 트리거이름
after/before insert/update/delete
on 테이블이름
[for each row] 

행의 삽입, 수정 후에 트리거가 실행되는 것이 좋다.

delimiter $$
create trigger insert_emp_trig
after insert
on emp1
for each row
begin
set @msg = concat(new.name, '님 새로 추가됨');    #new.name/old.name : 트리거에서 사용하는 값
end$$
delimiter ;
-> emp1에 insert할떄마다 실행

insert into emp1 values(300,'aaa',10000,80);
select @msg;

delimiter $$
create trigger emp1_trig
after insert
on emp1
for each row
begin
insert into emp1_backup(id, cmd, new_sal) values(new.emp_id, 'insert', new.sal);
end$$
delimiter ;

insert into emp1 values(301,'bbb',15000,80);
select @msg;

delimiter $$
create trigger emp1_trig2
after update
on emp1
for each row
begin
insert into emp1_backup(id, cmd, old_sal, new_sal) 
    values(old.emp_id, 'update', old.sal, new.sal);
end$$
delimiter ;

update emp1 set sal=20000 where emp_id=300;

삭제 전에 트리거가 실행되는 것이 좋다.

delimiter $$
create trigger emp1_trig3
before delete
on emp1
for each row
begin
insert into emp1_backup(id, cmd, old_sal, new_sal) 
    values(old.emp_id, 'delete', old.sal);
end$$
delimiter ;

delete from emp1 where emp_id>=145 and emp_id<150;

프로시저(Proceduer)

프로시저는 간단하게 파이썬에서 함수라고 생각하면 된다.

함수 호출
call 함수명()

CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`(enum int)
BEGIN
	declare ename varchar(20); #변수선언
    select last_name into ename
    from employees where employee_id = enum;
    select ename;
END

같은 아웃풋을 보이는 프로시저들을 다양한 방법으로 표현해보자.

(if/case/loop/repeat/while)

• IF

CREATE DEFINER=`root`@`localhost` PROCEDURE `p5`(enum int)
BEGIN
	declare dept_id int default 0;
	declare dept_name varchar(20);
	select department_id into dept_id from employees where employee_id=enum;
    if dept_id = 10 then set dept_name = '개발1팀';
	elseif dept_id = 20 then set dept_name = '개발2팀';
    elseif dept_id = 30 then set dept_name = '개발3팀';
    elseif dept_id = 40 then set dept_name = '개발4팀';
    else set dept_name = '디자인팀';
    end if;
    select dept_name;
END

• case

CREATE DEFINER=`root`@`localhost` PROCEDURE `p6`(enum int)
BEGIN
	declare dept_id int default 0;
	declare dept_name varchar(20);
	select department_id into dept_id from employees where employee_id=enum;
    case 
		when dept_id = 10 then set dept_name = '개발1팀';
		when dept_id = 20 then set dept_name = '개발2팀';
		when dept_id = 30 then set dept_name = '개발3팀';
		when dept_id = 40 then set dept_name = '개발4팀';
		else set dept_name = '디자인팀';
    end case;
    select dept_name;
END

• Loop

CREATE DEFINER=`root`@`localhost` PROCEDURE `p7`(x int)
BEGIN
	declare y int default 1;
    l1:loop
		select y;
        set y=y+1;
        if y>x then leave l1;   #y가 x보다 크면 루프를 나가라
        end if;
	end loop;
END

CREATE DEFINER=`root`@`localhost` PROCEDURE `p8`(x int)
BEGIN
	declare y int default 1;
    l1:loop
		select y;
        set y=y+1;
        if y <=x then iterate l1;   #반복할 조건
        end if;
	end loop;
END

• 반복문 Repeat

CREATE DEFINER=`root`@`localhost` PROCEDURE `p9`(x int)
BEGIN
	declare y int default 1;
    repeat         #반복문
		select y;
        set y=y+1;
        until y > x end repeat;   #조건 만족시 반복끝
END

• while

CREATE DEFINER=`root`@`localhost` PROCEDURE `p10`(x int)
BEGIN
	declare y int default 1;
    while y <= x do        #반복문
		select y;
        set y=y+1;
	end while;
END

커서(Cusor)

CREATE DEFINER=`root`@`localhost` PROCEDURE `cusor_test`()
BEGIN
	declare enum int;
    declare ename varchar(20);
    declare sal int;
    
    #cursor: 여러줄, 여러컬럼으로 구성된 검색결과. 
    declare done tinyint default 0;
    declare c1 cursor for select employee_id, last_name, salary from employees;
    declare continue handler for not found set done=1;  #반복조건으로 사용
    #cursor에서 한줄씩 fetch. 언제까지? not found(커서에서 더 읽을줄이 없음)까지.
    #open -> fetch(한줄씩 읽음) : 반복 -> close
    
    open c1;
    l1: loop
		fetch from c1 into enum, ename, sal;  #커서 c1에서 한 줄씩 읽음
        if done then leave l1;  #not found이면 루프 빠져나감
        end if;
        select enum, ename, sal;   #변수값 출력
	end loop;
    

END

뷰(View)

가상의 테이블  -> 
1. 복잡한 쿼리를 가상 테이블인 뷰로 만들면 사용이 간단해 진다.
2. 보안(사용자의 레벨에 따라 접근 레벨을 분류할 수 있음)
3. 데이터의 독립성을 유지

create [or replace] view 뷰이름
as 
서브쿼리

create view view_80
as
select employee_id as emp_id, last_name as name, salary
from employees
where department_id = 80;

인덱스(Index)

빠른 검색을 제공하기 위해 지정된 컬럼값을 완전 B트리로 구성
제약조건 primary key, unique을 만들면 자동 생성됨
인덱스 설정할 컬럼: 수정, 삭제가 잘 일어나지 않고 테이블에 많은 수의 행이 있지만 검색 where 잘해서 사용하는 컬럼으로 이 컬럼으로 검색된 행이 몇 줄 안되는 컬럼.

create index 인덱스명 on 테이블명(컬럼명);

Q. 게시판(num, writher, w_date, title, content)과 회원가입 (id, pwd, name, email) 테이블 만들고 서로 참조시키기.

1. 회원가입 테이블 만들기

create table member(
id varchar(20) primary key,  #줄 대푯값
pwd varchar(20) not null,   #null 허용안함
name varchar(20) not null,
email varchar(20) unique   #중복허용안함
);

  1-1. 3명 회원가입

insert into member values('aaa', '111', 'namea', 'aaa@mail.con');
insert into member values('bbb', '222', 'nameb','bbb@mail.con');
insert into member values('ccc', '333', 'namec','ccc@mail.con');

2. 게시판(num, writher, w_date, title, content) 생성

create table board(
num int auto_increment primary key,
writer varchar(20), 
w_date datetime, 
title varchar(50),
content varchar(500), 
constraint foreign key(writer) references member(id)
);

insert into board(writer, w_date, title, content) 
values('aaa', sysdate(), 'title1', 'content1'); 

insert into board(writer, w_date, title, content) 
values('abc', sysdate(), 'title1', 'content');

위의 코드는 에러발생! member(id)에 abc가 없기때문. (근데 번호 생성은 했음.)

insert into board(writer, w_date, title, content) 
values('bbb', sysdate(), 'title2', 'content2'); 

delete from board where writer = 'bbb';
delete from member where id = 'bbb';

-> 삭제시 참조하는 행도 삭제하고 진행해야하기 때문에 create table할 때 on delete cascade를 추가해주면 문제가 없다.

자, drop하고 다시 시작하자.

drop table board;

create table board(
num int auto_increment primary key,
writer varchar(20), 
w_date datetime, 
title varchar(50),
content varchar(500), 
constraint foreign key(writer) references member(id) on delete cascade
);

insert into board(writer, w_date, title, content) 
	values('aaa', sysdate(), 'title1', 'content1');

그럼 이제 id='aaa' 를 삭제하자.

delete from member where id = 'aaa';

만약, on delete cascade 대신 on delete set null을 추가하면 부모 테이블의 행 삭제시 이 줄을 참조하는 모든 줄의 값을 null로 셋팅이 된다.

테이블(Table)

• 테이블 생성
  

  create table 이름(
  컬럼명 타입(크기) [제약조건],
  컬럼명 타입(크기) [제약조건],
  컬럼명 타입(크기) [제약조건]
  );​

• 타입
  정수 : int(interger)
  실수 : float
  문자 : char(크기) -고정크기 / varchar(크기)-가변크기
  대용량 텍스트: longtext(4GB)
  날짜: date(년-월-일) / datetime(년-월-일 시:분:초)
  

  create table test2(
  num int auto_increment primary key,
  name varchar(20) not null,
  addr varchar(50) default '서울',
  w_date datetime default now(),
  msg varchar(200)
  );​

• 테이블 삽입
  

  insert into test2(name, msg) values('aaa', 'hello');
  insert into test2(name, addr, msg) values('bbb', '안양', '안녕하세요');​

• 테이블 수정

     테이블 구조 변경. 컬럼추가, 컬럼삭제, 컬럼의 타입이나 크기를 변경(값이 없을떄)

     -1. 컬럼추가

alter table 테이블명
add (컬럼명 타입(크기))​

alter table test2
add (pwd varchar(10)); ​

    -2. 컬럼변경(타입이나 크기 변경)

alter table 테이블명
modify (컬럼명 새타입(새크기));

alter table test2
modify pwd varchar(20);   #크기를 크게 바꾸는 건 가능하지만 작게는 힘듬.

    -3. 컬럼 삭제

alter table 테이블명
drop column 컬럼명;

alter table test2
drop column pwd;

     -4. 컬럼명 변경

alter table 테이블명
change 원컬럼명 새컬럼명 타입;

alter table test2
change msg hello_msg varchar(200);

   -5. 컬럼 삭제 (한번에 컬럼 하나만 삭제가능)

alter table test2
drop column col1;

    delete VS truncate

select * from new_test2;
delete from new_test2;   #delete는 rollback이 되지만
rollback;

truncate table new_test2;  #전체행 삭제. rollback 안됨.

• 테이블 이름 변경

alter table test2
rename new_test2;