728x90
์จ๋ผ์ธ ๋ง์ผ TABLE์ ๋ง๋ค์ด๋ณด์ธ์.
์์ ๋ด์ฉ โ
-----------------------------------------------------------------
PRODUCT(์ํ) : ์ํ๋ฒํธ, ์ํ๋ช , ์ํ๊ฐ๊ฒฉ, ์ํ์ค๋ช
CONSUMER(์๋น์) : ์๋น์ ID, ์ด๋ฆ, ๋์ด
CART(์ฅ๋ฐ๊ตฌ๋) : ์ฅ๋ฐ๊ตฌ๋ ๋ฒํธ, ์๋น์ ID, ์ํ๋ฒํธ, ์๋
์ํ ํ
์ด๋ธ์ ์ํ์ ๋ฑ๋กํฉ๋๋ค(๊ฐ์๋ ์ํ๋ ๋๋ก).
์๋น์๋ฅผ ๋ฑ๋กํฉ๋๋ค.
์๋น์๊ฐ ์ผํํ ์ํ์ ์ถ๊ฐํฉ๋๋ค.
์ผํํ ์ํ์ ์ถ๋ ฅํฉ๋๋ค.
-------------------------------------------------------------------
๋์
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
|
--์ํ ํ
์ด๋ธ
CREATE TABLE PRODUCT(
PRODUCTID NUMBER PRIMARY KEY,
NAME VARCHAR(50),
PRICE NUMBER,
PRODUCT_DESC VARCHAR2(500)
);
--์๋น์ ํ
์ด๋ธ
CREATE TABLE CONSUMER(
USERID VARCHAR2(50) CONSTRAINT PK_USER PRIMARY KEY,
NAME VARCHAR2(50) NOT NULL,
AGE NUMBER(3)
);
--์ฅ๋ฐ๊ตฌ๋ ํ
์ด๋ธ
CREATE TABLE CART(
CARTID NUMBER CONSTRAINT PK_CART PRIMARY KEY,
USERID VARCHAR2(50), -- ์ธ๋ํค
PRODUCTID NUMBER, -- ์ธ๋ํค
AMOUNT NUMBER
);
--์ธ๋ํค ์์ฑ
ALTER TABLE CART
ADD
CONSTRAINT FK_PRODUCT_ID FOREIGN KEY(PRODUCTID)
REFERENCES PRODUCT(PRODUCTID);
ALTER TABLE CART
ADD
CONSTRAINT FK_USER_ID FOREIGN KEY(USERID)
REFERENCES CONSUMER(USERID);
--์ํ์ค
CREATE SEQUENCE PRODUCT_SEQ
INCREMENT BY 1
START WITH 1000;
--์ํํ
์ด๋ธ์ ์ํ ๋ฑ๋ก
INSERT INTO PRODUCT(PRODUCTID, NAME, PRICE, PRODUCT_DESC)
VALUES(PRODUCT_SEQ.NEXTVAL,'์์ฐ๊นก', 1500,'๋ง์์ด์');
INSERT INTO PRODUCT(PRODUCTID, NAME, PRICE, PRODUCT_DESC)
VALUES(PRODUCT_SEQ.NEXTVAL,'ํฌํ
์ดํ ์นฉ', 2300,'์ง์');
INSERT INTO PRODUCT(PRODUCTID, NAME, PRICE, PRODUCT_DESC)
VALUES(PRODUCT_SEQ.NEXTVAL,'์ปคํผ', 2500,'๋๋ด์ค์');
--์๋น์๋ฅผ ๋ฑ๋กํฉ๋๋ค
INSERT INTO CONSUMER(USERID, NAME, AGE)
VALUES('HGD','ํ๊ธธ๋',24);
INSERT INTO CONSUMER(USERID, NAME, AGE)
VALUES('SCH','์ฑ์ถํฅ',16);
INSERT INTO CONSUMER(USERID, NAME, AGE)
VALUES('JSD','์ ์๋',28);
-- ์๋น์๊ฐ ์ผํํ ์ํ์ ์ถ๊ฐํฉ๋๋ค.
INSERT INTO CART(CARTID, USERID, PRODUCTID, AMOUNT)
VALUES(1, 'SCH', 1001, 2);
INSERT INTO CART(CARTID, USERID, PRODUCTID, AMOUNT)
VALUES(2, 'SCH', 1000, 1);
INSERT INTO CART(CARTID, USERID, PRODUCTID, AMOUNT)
VALUES(3, 'HGD', 1001, 2);
-- ์ผํํ ์ํ์ ์ถ๋ ฅํฉ๋๋ค.
SELECT *
FROM cart
WHERE userid = 'SCH';
SELECT c.userid, c.productid, p.name, p.price
FROM cart c, product p
WHERE c.productid = p.productid AND c.userid = 'SCH';
--์ฑ์ถํฅ์ด ๋ญ ์ผํํ๋์ง ๋์ต๋๋ค.
|
cs |
'๐ฆ ๋ฐ์ดํฐ ๋ฒ ์ด์ค (DB) ๐ฆ > ๐ พ ์ค๋ผํด DB & SQL' ์นดํ ๊ณ ๋ฆฌ์ ๋ค๋ฅธ ๊ธ
๐ VIEW(๋ทฐ) ๋ฌธ์ - 1 (0) | 2021.02.17 |
---|---|
๐ TABLE(ํ ์ด๋ธ) ๋ฌธ์ - 1 (0) | 2021.02.17 |
โพ๏ธ์ด๋๋ถ(์ผ๊ตฌํ) ํ ์ด๋ธ ์์ (0) | 2021.02.17 |
INDEX (์ธ๋ฑ์ค) (0) | 2021.02.17 |
์ํ์ค (SEQUENCE) (0) | 2021.02.17 |
๋๊ธ