참조 SQL(Structured Query Language) : 내가 정리한 것 최종 실행파일 : Summber20200614.zip을 압축해지 한 후 a.bat 파일을 더블클릭하면 실행됨 학과테이블(tdept)
|
CREATE TABLE tdept ( dept_no INTEGER, dept_name TEXT, dept_tel TEXT, dept_room TEXT, PRIMARY KEY(dept_no) ); INSERT INTO tdept (dept_no,dept_name,dept_tel,dept_room) VALUES (14,'스마트미디어','2330','공학1관229호'); INSERT INTO tdept (dept_no,dept_name,dept_tel,dept_room) VALUES (19,'일본언어문화','2311','진리관107호'); INSERT INTO tdept (dept_no,dept_name,dept_tel,dept_room) VALUES (23,'게임콘텐츠','3181','예술관209호'); INSERT INTO tdept (dept_no,dept_name,dept_tel,dept_room) VALUES (25,'역사문화','2325','진리관107호'); INSERT INTO tdept (dept_no,dept_name,dept_tel,dept_room) VALUES (62,'사회복지','2426','평화관113호'); INSERT INTO tdept (dept_no,dept_name,dept_tel,dept_room) VALUES (70,'문헌정보','2235','평화관113호'); |
도서테이블(tbook)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
|
CREATE TABLE tbook ( no TEXT, title TEXT, pric INTEGER, publ TEXT, typ TEXT, pos TEXT, PRIMARY KEY(no) ); INSERT INTO tbook (no,title,pric,publ,typ,pos) VALUES ('201900001','자바',20000,'생능출판','기술과학', '3층'); INSERT INTO tbook (no,title,pric,publ,typ,pos) VALUES ('201900002','알고리즘',19000,'이지스퍼블릭','자연과학', '1층'); INSERT INTO tbook (no,title,pric,publ,typ,pos) VALUES ('201900003','데이터베이스',21000,'이한출판사','기술과학', '3층'); INSERT INTO tbook (no,title,pric,publ,typ,pos) VALUES ('201900004','정보검색',15000,'한빛미디어','자연과학', '2층'); INSERT INTO tbook (no,title,pric,publ,typ,pos) VALUES ('201900005','오라클',25000,'생능출판','기술과학', '1층'); INSERT INTO tbook (no,title,pric,publ,typ,pos) VALUES ('201900006','운영체제',20000,'이지스퍼블릭','기술과학', '2층'); INSERT INTO tbook (no,title,pric,publ,typ,pos) VALUES ('202000001','인공지능',30000,'길벗','기술과학','1층'); INSERT INTO tbook (no,title,pric,publ,typ,pos) VALUES ('202000004','정보검색',15000,'한빛미디어','자연과학','1층'); INSERT INTO tbook (no,title,pric,publ,typ,pos) VALUES ('202000007','운영체제',20000,'이지스퍼블릭','기술과학','1층'); INSERT INTO tbook (no,title,pric,publ,typ,pos) VALUES ('202000008','오라클',25000,'생능출판','기술과학','2층'); |
회원테이블(tmember) : 학과를 학과테이블(tdept)와 외래키를 사용해서 연결
|
CREATE TABLE tmember ( no TEXT, name TEXT, dept INTEGER, major TEXT, PRIMARY KEY(no), FOREIGN KEY(dept) REFERENCES tdept(dept_no) ON DELETE SET NULL ON UPDATE CASCADE ); INSERT INTO tmember (no,name,dept,major) VALUES ('201912346','길동이',62,'2'); INSERT INTO tmember (no,name,dept,major) VALUES ('201912345','홍길동',19,'1'); INSERT INTO tmember (no,name,dept,major) VALUES ('10000','홍교수',70,'2'); INSERT INTO tmember (no,name,dept,major) VALUES ('202012345','박길동',14,'3'); |
대출테이블(tlend) : 외래키(foreign key)를 사용해서 생성해야 함
|
CREATE TABLE tlend ( no TEXT, bno TEXT, mno TEXT, cdate NUMERIC DEFAULT CURRENT_TIMESTAMP, days INTEGER DEFAULT 14, PRIMARY KEY(no), FOREIGN KEY(bno) REFERENCES tbook(no) ON DELETE SET NULL ON UPDATE CASCADE, FOREIGN KEY(mno) REFERENCES tmember(no) ON DELETE SET NULL ON UPDATE CASCADE ); |
위와 같이 생성하면 아래와 같이 변경되어 저장됨
|
CREATE TABLE "tlend" ( "no" TEXT, "bno" TEXT, "mno" TEXT, "cdate" NUMERIC DEFAULT CURRENT_TIMESTAMP, "days" INTEGER DEFAULT 14, PRIMARY KEY("no"), FOREIGN KEY("bno") REFERENCES "tbook"("KEYWORDASCOLUMNNAME") ON DELETE SET NULL ON UPDATE CASCADE, FOREIGN KEY("mno") REFERENCES "tmember"("KEYWORDASCOLUMNNAME") ON DELETE SET NULL ON UPDATE CASCADE ); |
회원 뷰(vmember) : 회원정보와… Continue Reading [자바] 도서관리 #6 도서관리 데이터베이스 설계 및 구현(2020년 Sqlite)