실습 예제입니다.
파일을 참고해 학습해주세요.
CREATE DATABASE jsp_00_03_market_mysql;
USE jsp_00_03_market_mysql;
CREATE TABLE customer(
id VARCHAR(50),
pw VARCHAR(16),
name VARCHAR(10)
);
CREATE TABLE item(
item_number INT(50),
item_category VARCHAR(50),
item_name VARCHAR(16),
item_price INT(50),
item_stock INT(50), -- 재고 수량
item_image VARCHAR(10),
item_info VARCHAR(100),
discount_rate INT(50)
);
CREATE TABLE cart(
cart_number INT(50),
buyer VARCHAR(50),
item_name VARCHAR(16),
buy_price INT(50),
buy_count INT(50),
item_image VARCHAR(50)
);
INSERT INTO customer (id, pw, name) VALUES('qwer', '1234', '컬리마');
INSERT INTO customer VALUES('abcd', '1234', '김리컬');
INSERT INTO item VALUES(1,'채소', '대파',1200,3,'1.jpg', '대파입니다', 10);
INSERT INTO item VALUES(2,'채소', '상추',2000,4,'2.jpg', '상추입니다', 0);
INSERT INTO item VALUES(3,'채소', '당근',2500,8,'3.jpg', '당근입니다', 0);
INSERT INTO item VALUES(4,'채소', '깻잎',1200,0,'4.jpg', '깻잎입니다', 0);
INSERT INTO item VALUES(5,'채소', '애호박',1000,5,'5.jpg', '애호박입니다', 0);
INSERT INTO item VALUES(6,'채소', '버섯',1500,4,'6.jpg', '버섯입니다', 0);
INSERT INTO item VALUES(7,'채소', '단호박',3000,7,'7.jpg', '단호박입니다', 0);
INSERT INTO item VALUES(8,'채소', '파프리카',3000,6,'8.jpg', '파프리카입니다', 0);
INSERT INTO item VALUES(9,'채소', '아스파라거스',3000,10,'9.jpg', '아스파라거스입니다',0);
INSERT INTO item VALUES(10,'채소', '양파',2000,11,'10.jpg', '양파입니다', 0);
INSERT INTO item VALUES(11,'해산물', '자반고등어',5000,20,'11.jpg', '고등어입니다', 4);
INSERT INTO item VALUES(12,'해산물', '연어',14000,40,'12.jpg', '노르웨이산 연어입니다', 0);
INSERT INTO item VALUES(13,'해산물', '오징어',5000,10,'13.jpg', '오징어입니다', 0);
INSERT INTO item VALUES(14,'해산물', '바지락',7000,0,'14.jpg', '바지락입니다', 0);
INSERT INTO item VALUES(15,'해산물', '새우',10000,1,'15.jpg', '새우입니다', 0);
INSERT INTO item VALUES(16,'해산물', '전복',20000,5,'16.jpg', '전복입니다', 0);
INSERT INTO item VALUES(17,'육류', '막창',20000,5,'17.jpg', '막창입니다', 0);
INSERT INTO item VALUES(18,'육류', '안심스테이크',18000,0,'18.jpg', '안심스테이크입니다',0);
INSERT INTO item VALUES(19,'육류', '닭', 12000,4,'19.jpg', '닭입니다', 0);
INSERT INTO item VALUES(20,'육류', '닭다리',10000,7,'20.jpg', '닭다리입니다', 8);
INSERT INTO item VALUES(21,'육류', '폭립',20000,10,'21.jpg', '폭립니다', 0);
INSERT INTO item VALUES(22,'육류', '양념갈비',25000,20,'22.jpg', '양념갈비입니다', 0);
INSERT INTO item VALUES(23,'육류', '등갈비구이',20000,0,'23.jpg', '등갈비구이입니다', 5);
INSERT INTO item VALUES(24,'육류', '치즈돈가스',13000,1,'24.jpg', '치즈돈가스입니다', 50);
INSERT INTO item VALUES(25,'육류', '닭가슴살',5000,4,'25.jpg', '닭가슴살입니다', 0);
INSERT INTO item VALUES(26,'육류', '차돌박이',20000,3,'26.jpg', '차돌박이입니다', 10);
INSERT INTO item VALUES(27,'전자제품', '그라인더',28000,3,'27.jpg', '그라인더입니다', 0);
INSERT INTO item VALUES(28,'전자제품', '스팀다리미',30000,3,'28.jpg', '스팀다리미입니다', 0);
INSERT INTO item VALUES(29,'전자제품', '헤어드라이기',25000,3,'29.jpg', '헤어드라이기입니다', 0);
INSERT INTO item VALUES(30,'전자제품', '체중계',19000,3,'30.jpg', '체중계입니다', 0);
INSERT INTO item VALUES(31,'전자제품', '전자체중계',22000,3,'31.jpg', '전자체중계입니다', 0);
INSERT INTO item VALUES(32,'전자제품', '미니선풍기',16000,0,'32.jpg', '미니선풍기입니다', 0);
INSERT INTO item VALUES(33,'전자제품', '청소기',40000,3,'33.jpg', '청소기입니다', 0);
INSERT INTO item VALUES(34,'전자제품', '카메라',50000,3,'34.jpg', '카메라입니다', 0);
INSERT INTO item VALUES(35,'전자제품', '믹서기',3800,3,'35.jpg', '믹서기입니다', 7);
SELECT * FROM customer;
SELECT * FROM item;
JavaScript
복사
SELECT * FROM customer WHERE id='qwer';
SELECT COUNT(*) FROM customer WHERE id='qwer';
SELECT COUNT(*) FROM customer WHERE id='qwer' AND pw='1234';
SELECT * FROM item ORDER BY item_number;
SELECT * FROM item WHERE item_number=1;
SELECT * FROM cart WHERE buyer='qwer';
SELECT buy_count FROM cart WHERE buyer='qwer' AND item_name=(SELECT item_name FROM item WHERE item_number=2);
UPDATE cart SET buy_count=buy_count + 1 WHERE buyer='qwer' AND item_name=(SELECT item_name FROM item WHERE item_number=2);
DELETE FROM cart WHERE cart_number='2';
JavaScript
복사