230316_웹 DB 연동 위한 DB 준비
<수업 순서>
1. RDBMS에 테이블 생성
2. DB에 넣을 표 설계하기(ERD, 테이블 정의서 참고/PPT에 ERD 간단히 그려보기)
3. JOIN 종류 및 설명
4. SUB QUERY 예제
5. 가상환경에 오라클 연동/웹 연동
1. RDBMS에 테이블 생성
| DB명 | 오라클 | |
| RDMS명 | SQLDeveloper | |
| 명령 | 명령어 | |
| 1 | 테이블 생성 | CREATE TABLE TestTable (ID VARCHAR2(15) NOT NULL, PW VARCHAR2(20) NOT NULL, NAME VARCHAR2(20) NULL, CONSTRAINT PK_ID PRIMARY KEY(ID)); |
| 2 | 데이터 삽입 | INSERT INTO TESTTABLE(ID, PW, NAME) VALUES('a01', 'pw01', '홍길동'); |
*not null인 데이터는 무조건 제시해야하고 null은 상관없음 ex) id, pw는 무조건, name은 없어도 됨 |
||
| 3 | 데이터 조회 | SELECT * FROM TESTTABLE WHERE ID='a01'; |
*SQLDeveloper의 TABLE '형상관리'일 뿐. 오라클(Data Base)에서 data가 들어가있다는 것을 보여줄 뿐이다. |
||
| 1) 데이터 정렬 | ||
SELECT * FROM TESTTABLE ORDER BY DESC; |
||
| 4 | 데이터 추가/변경 | UPDATE TESTTABLE SET NAME='하승권' WHERE ID='b02'; |
*무조건 where 절 써줘야함. 실무에서 큰일나기 때문 |
||
| 5 | 데이터 삭제 | DELETE FROM TESTTABLE WHERE ID='a01'; |
*무조건 where 절 |
||
| 6 | 데이터 수정/삭제 취소 | ROLLBACK; |
-컴퓨터의 휴지통에서 복원하는 개념 -COMMIT; 후부터 ROLLBACK; 전까지 변경한 것은 취소 -캐시 메모리에 올라간 데이터 취소 |
||
| 7 | 데이터 변경 영구반영 | COMMIT; |
-COMMIT; 명령 이전에, 데이터의 입력/수정/삭제(변경)을 메모리에 영구 반영 -캐시 메모리에서 실제 메모리로 이동시킴 |
||
*웹에서는 OUTOCOMMIT이므로 PC에서 수행 후 웹으로 올리는 순서 |
||
| 8 | 테이블 컬럼 추가 | ALTER TABLE TESTTABLE ADD(LIKE1 VARCHAR2(50) NULL); |
| 9 | 컬럼 이름 변경 | ALTER TABLE TESTTABLE RENAME COLUMN LIKE1 TO LIKEWISE |
| 10 | 테이블 삭제 | DROP TABLE TESTTABLE; |
2. DB에 넣을 표 설계하기(ERD, 테이블 정의서 참고/PPT에 ERD 간단히 그려보기)
표(TABLE) 설계 |
||
| 회원정보 | MEMBER | 아이디(=회원 코드)(PK), 패스워드, 이름 , 전화번호, 주소, 기타 |
| 상품정보 | ITEMS | 상품 코드(PK), 상품명, 색상, 상품금액, /총액(수량*상품금액)/, 상품분류 코드(FK), 거래처 코드(FK) * 코드성 데이터 : P10100001, P10100002 |
| 상품분류 정보 | ITEMS_GROUP |
상품분류 코드(PK), 상품분류명 * 코드성 데이터 : P101 |
| 거래처 정보 | BUYER |
거래처 코드(PK), 거래처명, 거래처주소, 거래처 번호 |
| 장바구니 정보 | CART | 장바구니 코드(PK), 상품 코드(FK), 회원코드(FK), 날짜, 수량, 색상 *장바구니: 소멸하는 TABLE |
| 주문 정보 | ORDER | 주문 코드(PK), 상품 코드(FK), 회원코드(FK), 날짜, 수량, 색상 |
| 비고 | 최상위 테이블(제일 부모) - PK만 있고 FK 없어도 되는 * FK는 부모를 찾아가는 아이 > 회원 정보, 상품분류 정보, 거래처 정보 - PK, FK 쓰는 이유 : 데이터 관리를 위해(데이터의 무결성: 중복 제외) |
|
*테이블 생성 시 부모 테이블부터 만들어야함. 순서 잘 맞춰야지 안 그러면 참조됐을 때 부모 정보 못 지울 수 있음 *ERD, 테이블 정의서 꼭 이해할 수 있어야 테이블 관리 가능 이런 게 없는 회사는 주의(체계 x or 너무 바쁨) |
||
3. JOIN 종류 및 설명
| 1 | INNER JOIN |
| - PK와 FK가 같다는 조건(PK=FK) | |
-(일반 방식) FROM 회원정보, 주문정보 WHERE 회원정보.아이디=주문정보.주문코드 *WHERE 옆에 관계 조건식 |
|
-(국제 표준방식) FROM 회원정보 INNER JOIN 주문정보 ON 회원정보.아이디=주문정보.주문코드 *ON 옆에 관계 조건식 |
|
| 2 | OUTER JOIN(LEFT, RIGHT, FULL) |
-LEFT OUTER JOIN 만족하면서 어느 한 쪽 전체(NULL 허용) -왼쪽 테이블 전체, 오른쪽 테이블은 같은 조건이 있으면 조회, 없으면 NULL -같은 조건 : PK=FK *관계 조건을 제외한 일반 조건이 없는 경우에는 일반 방식 가능 |
|
-(일반 방식) SELECT 회원정보.아이디, SUM(주문정보.수량) FROM 회원정보, 주문정보 WHERE 회원정보.아이디=주문정보.주문코드 (+) *LEFT, RIGHT 의미가 없음(WHERE 절에 왼/오를 기준 테이블로 명시하면 될 뿐) |
|
-(국제 표준방식) FROM 회원정보 LEFT OUTER JOIN 주문정보 ON 회원정보.아이디=주문정보.주문코드 |
|
| *일반 조건이 있는 경우, 무조건 국제 표준 방식 사용해야 함 | |
-(일반 방식) (X) SELECT 회원정보.아이디, SUM(주문정보.수량) FROM 회원정보, 주문정보 WHERE 회원정보.아이디=주문정보.주문코드 (+) AND 회원정보.주소 LIKE '%종로구%' **위에서 순차적으로 **WHERE에서 거르고 나면 AND 절 내용이 걸러져서 안나옴(OUTER JOIN은 없어도 가져오는 개념이라 위배) |
|
-(국제 표준방식) (O) FROM 회원정보 LEFT OUTER JOIN 주문정보 ON 회원정보.아이디=주문정보.아이디 AND 회원정보.주소 LIKE '%종로구%' **SELECT에서 FROM, ON 절 갔다가 JOIN 절 마지막에 간다. **모든 조건은 FROM 절 안에다 다 넣어버려야 함. (WHERE절 안 돼!) |
|
| 3 | SELF JOIN |
-자기 자신 테이블 2개로 사용 -특수한 조건을 뽑아내는 경우 씀. 거의 안 씀 |
|
SELEC A.id, B.name FROM 회원정보 A, 회원정보 B WHERE A.id=B.id AND B.name='홍길동' |
|
| 4 | CROSS JOIN |
-조건 없음 -근데 안 씀. |
|
(일반) SELECT * FROM 회원정보, 주문정보 |
|
(국제표준) SELECT * FROM 회원정보 CROSS JOIN 주문정보 |
|
| 5 | NATURAL JOIN |
-테이블 2개의 컬럼명이 같은 경우 -PK, FK 관계가 없어도 컬럼명 같으면 씀 ex. where A.id=B.id -거의 안 씀 |
|
| 6 | 6. 값에 의한 JOIN |
| -값이 같은 경우 -거의 안 씀. |
|
SELECT * FROM 상품분류 정보, 상품 정보 WHERE 상품분류 코드=SUBSTR(상품코드,0,4) |
|
| 비고 | *관계 조건식 개수 =테이블 개수-1 |
4. SUB QUERY 예제
--[서브쿼리]
--1.회원 아이디가 'b01'인 회원의 마일리지값보다 큰 회원정보 조회
--조회컬럼 : 아이디, 이름, 마일리지
--이름을 기준으로 오름차순
SELECT MEM_ID, MEM_NAME, MEM_MILEAGE
FROM MEMBER
WHERE MEM_MILEAGE>=
(SELECT MEM_MILEAGE
FROM MEMBER
WHERE MEM_ID='b001')
ORDER BY MEM_NAME ASC;
* 컬럼명 별칭
ex. SELECT COLUMN1 AS C1
-WHERE 절 안에 적용 안됨
-ORDER BY 절 안에는 됨
-컴파일 순서때문!
: WHERE 절 끝난 다음에 SELECT 절 실행되기 때문에
즉 테이블(FROM) 확인=>조건절(WHERE)부터 탐색=>뭐 조회할지(SELECT)=>정렬(ORDER)
(컴파일 순서 : FROM => WHERE => SELECT => ORDER BY )
--2.장바구니에 한 번도 뭔갈 담지 않은 회원의 정보 조회
--한번도 주문한 적 없는 회원과 동일
--조회컬럼: 회원아이디, 회원이름,
--정렬 : 이름 기준
SELECT MEM_ID, MEM_NAME
FROM MEMBER
WHERE MEM_ID NOT IN
(SELECT CART_MEMBER
FROM CART)
ORDER BY MEM_NAME ASC;
--3. 상품정보 조회하기
--조회컬럼 : 상품코드, 상품명, 상품분류명
--단, 조인(JOIN) 사용하지 않기, 서브쿼리는 가능
(1. WHERE절 부속질의)
(+ IN 서브쿼리 : IN/NOT IN)
SELECT P.PROD_ID, P.PROD_NAME, L.LPROD_NM
FROM PROD P, LPROD L
WHERE PROD_LGU IN
(SELECT LPROD_GU
FROM LPROD);
(2. SELECT 절 부속질의)
SELECT PROD_ID, PROD_NAME,
(SELECT LPROD_NM
FROM LPROD
WHERE LPROD_GU=PROD_LGU) AS L_NM
FROM PROD;
(+)추가 설명
-EXISTS 서브쿼리
SELECT *
FROM MEMBER
WHERE NOT EXISTS
(SELECT *
FROM CART
WHERE CART_MEMBER=MEM_ID);
'*': 다중 컬럼
--4.회원 아이디가 'b01'인 회원의 마일리지값보다 큰 회원정보 이용해서
--장바구니 정보 확인하기
--조회컬럼 : 아이디, 이름, 마일리지
--이름을 기준으로 오름차순
SELECT *
FROM CART
WHERE CART_MEMBER IN
(SELECT MEM_ID
FROM MEMBER
WHERE MEM_MILEAGE>=
(SELECT MEM_MILEAGE
FROM MEMBER
WHERE MEM_ID='b001'));
--5.회원들이 주문한 전체 수량을 확인하려고 한다.
--회원 전체에 대해 수량 확인, 주문 내역 없으면 0으로
--조회 컬럼: 회원 아이디, 회원이름, 주문수량의 합
(1. LEFT OUTER JOIN)
SELECT MEM_ID, MEM_NAME, NVL(SUM(CART_QUTY),0) AS SUMQTY
FROM MEMBER
LEFT OUTER JOIN CART
ON(MEM_ID=CART_MEMBER)
GROUP BY MEM_ID, MEM_NAME;
(2. LEFT OUTER JOIN2)
SELECT NVL(SUM(CART_QUTY),0) AS SUMQTY
FROM MEMBER
LEFT OUTER JOIN CART
ON(MEM_ID=CART_MEMBER);
*NVL: NULL이면 ~로 바꿔라
*GROUP BY 역할: GROUP 함수 제외하고 다 써준다.(GROUP 함수만 있으면 GROUP BY 무필요)
GROUP 함수(집계 함수): SUM, AVG, MAX, MIN, COUNT
5. 가상환경에 오라클 연동/웹 연동
오라클 설치 in django in 가상환경 |
>pip install cx_Oracle |
가상환경 커널 열어서 oracle_test 파일 생성 |
import cx_Oracle dns=cx_Oracle.makedsn('localhost', 1521, 'xe') |
conn=cx_Oracle.connect('GWANGJU_B', 'DBDB', dns) cursor=conn.cursor() |
sql="SELECT * FROM CART" cursor.execute(sql) |
cursor.fetchall() |
* dsn : domain name system,
클라우드적 개념으론, web 브라우저 상에서 입력하는 url에 대한 IP주소를 찾아서 알려 주는 서비스
요컨대, 연결지어줄 위치에 해당하는 이름정도인 듯
oracle에서 가져오고 싶은 DB에 커서를 대면 쉽게 알 수 있음
* The makedsn() function
accepts
the database hostname, the port number, and the service name.