실습페이지
24_07_12 오늘의 실습내용
regnator
2024. 7. 12. 17:13
728x90
반응형
MySQL 더미데이터 생성
-- --------------------------------------------------------
-- 호스트: 127.0.0.1
-- 서버 버전: 8.0.37 - MySQL Community Server - GPL
-- 서버 OS: Win64
-- HeidiSQL 버전: 12.7.0.6850
-- --------------------------------------------------------
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
-- ksmartdb 데이터베이스 구조 내보내기
-- DROP DATABASE IF EXISTS `ksmartdb`;
-- CREATE DATABASE IF NOT EXISTS `ksmartdb` /*!40100 DEFAULT CHARACTER SET utf8mb3 */ /*!80016 DEFAULT ENCRYPTION='N' */;
-- USE `ksmartdb`;
-- 테이블 ksmartdb.comm_group_code 구조 내보내기
DROP TABLE IF EXISTS `comm_group_code`;
CREATE TABLE IF NOT EXISTS `comm_group_code` (
`comm_group_cd` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '공통그룹코드',
`comm_group_nm` varchar(30) NOT NULL COMMENT '공통그룹코드명',
`comm_group_eng_nm` varchar(30) NOT NULL COMMENT '공통그룹코드영문명',
`comm_group_reg_date` timestamp NOT NULL COMMENT '공통그룹코드등록일자',
PRIMARY KEY (`comm_group_cd`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='공통그룹코드';
-- 테이블 데이터 ksmartdb.comm_group_code:~2 rows (대략적) 내보내기
DELETE FROM `comm_group_code`;
INSERT IGNORE INTO `comm_group_code`
(`comm_group_cd`, `comm_group_nm`, `comm_group_eng_nm`, `comm_group_reg_date`)
VALUES
('comm_group_1', '회원등급', 'MEMBER GRADE', '2024-07-11 05:59:32'),
('comm_group_2', '결재상태', 'PAYMENT STATUS', '2024-07-11 06:04:28');
-- 테이블 ksmartdb.comm_code 구조 내보내기
DROP TABLE IF EXISTS `comm_code`;
CREATE TABLE IF NOT EXISTS `comm_code` (
`comm_group_cd` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '공통그룹코드',
`comm_cd` varchar(20) NOT NULL COMMENT '공통코드',
`comm_nm` varchar(30) NOT NULL COMMENT '공통코드명',
`comm_eng_nm` varchar(30) NOT NULL COMMENT '공통코드영문명',
`comm_cd_use_yn` varchar(5) NOT NULL COMMENT '공통코드사용유무',
`comm_cd_reg_date` date NOT NULL COMMENT '공통그룹코드등록일자',
PRIMARY KEY (`comm_group_cd`,`comm_cd`),
UNIQUE KEY `comm_cd_IDX` (`comm_cd`) USING BTREE,
CONSTRAINT `comm_code_comm_group_code_FK` FOREIGN KEY (`comm_group_cd`) REFERENCES `comm_group_code` (`comm_group_cd`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='공통코드';
-- 테이블 데이터 ksmartdb.comm_code:~3 rows (대략적) 내보내기
DELETE FROM `comm_code`;
INSERT IGNORE INTO `comm_code` (`comm_group_cd`, `comm_cd`, `comm_nm`, `comm_eng_nm`, `comm_cd_use_yn`, `comm_cd_reg_date`) VALUES
('comm_group_1', 'mbr_grd_1', '관리자', 'ADMIN', 'y', '2024-07-11'),
('comm_group_1', 'mbr_grd_2', '판매자', 'SELLER', 'y', '2024-07-11'),
('comm_group_1', 'mbr_grd_3', '구매자', 'CUSTOMER', 'y', '2024-07-11');
-- 테이블 ksmartdb.members 구조 내보내기
DROP TABLE IF EXISTS `members`;
CREATE TABLE IF NOT EXISTS `members` (
`mbr_id` varchar(20) NOT NULL COMMENT '회원ID',
`mbr_pw` varchar(20) NOT NULL COMMENT '회원PASSWORD',
`mbr_name` varchar(50) NOT NULL COMMENT '회원이름',
`mbr_grd` varchar(20) NOT NULL COMMENT '회원등급',
`mbr_addr` varchar(50) DEFAULT NULL COMMENT '회원주소',
`mbr_daddr` varchar(50) DEFAULT NULL COMMENT '회원상세주소',
`mbr_zip` int NOT NULL COMMENT '회원주소우편번호',
`mbr_telno` varchar(50) DEFAULT NULL COMMENT '회원연락처',
`mbr_email` varchar(255) DEFAULT NULL COMMENT '회원이메일주소',
`mbr_reg_date` date NOT NULL COMMENT '회원등록일자',
PRIMARY KEY (`mbr_id`),
KEY `members_comm_code_FK` (`mbr_grd`),
CONSTRAINT `members_comm_code_FK` FOREIGN KEY (`mbr_grd`) REFERENCES `comm_code` (`comm_cd`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='회원';
-- 테이블 데이터 ksmartdb.members:~5 rows (대략적) 내보내기
DELETE FROM `members`;
INSERT IGNORE INTO `members`
(`mbr_id`, `mbr_pw`, `mbr_name`, `mbr_grd`, `mbr_addr`, `mbr_daddr`, `mbr_zip`, `mbr_telno`, `mbr_email`, `mbr_reg_date`)
VALUES
('ksmartid1', 'ksmartpw1', '홍1', 'mbr_grd_1', '전북특별자치도 전주시 덕진구 기린대로 499', '3층 한국스마트정보', 54888, '063-717-1008', 'ksmartid1@ksmart.or.kr', '2024-07-11'),
('ksmartid2', 'ksmartpw2', '홍2', 'mbr_grd_2', '전북특별자치도 전주시 덕진구 백제대로 477', '헬스프라자', 54894, '010-0002-0002', 'ksmartid2@ksmart.or.kr', '2024-07-11'),
('ksmartid3', 'ksmartpw3', '홍3', 'mbr_grd_3', '전북특별자치도 전주시 덕진구 백제대로 514', '3번지', 54888, '010-0003-0003', 'ksmartid3@ksmart.or.kr', '2024-07-11'),
('ksmartid4', 'ksmartpw4', '홍4', 'mbr_grd_2', '전북특별자치도 전주시 완산구 백제대로 10', '롯데백화점(평화점)', 54106, '010-0004-0004', 'ksmartid4@ksmart.or.kr', '2024-07-11'),
('ksmartid5', 'ksmartpw5', '홍5', 'mbr_grd_3', '전북특별자치도 전주시 덕진구 기린대로 499', '3층 궁전요리학원', 54888, '010-0005-0005', 'ksmartid5@ksmart.or.kr', '2024-07-11'),
('ksmartid6', 'ksmartpw6', '홍6', 'mbr_grd_3', '전북특별자치도 전주시 완산구 백제대로 13', '우성아파트', 55092, '010-0006-0006', 'ksmartid6@ksmart.or.kr', '2024-07-11'),
('ksmartid7', 'ksmartpw7', '홍7', 'mbr_grd_2', '전북특별자치도 전주시 완산구 간납대 1길', '14번지', 55035, '010-0007-0007', 'ksmartid7@ksmart.or.kr', '2024-07-11'),
('ksmartid8', 'ksmartpw8', '홍8', 'mbr_grd_2', '전북특별자치도 전주시 완산구 객원길 3', '3번지', 55115, '010-0008-0008', 'ksmartid8@ksmart.or.kr', '2024-07-11'),
('ksmartid9', 'ksmartpw9', '홍9', 'mbr_grd_3', '전북특별자치도 전주시 덕진구 기린대로 499', '3층 한국스마트정보', 54888, '010-0009-0009', 'ksmartid9@ksmart.or.kr', '2024-07-11'),
('ksmartid10', 'ksmartpw10', '홍10', 'mbr_grd_3', '전북특별자치도 전주시 덕진구 거북바우로 10', '도래미아파트', 54925, '010-0010-0010', 'ksmartid10@ksmart.or.kr', '2024-07-11');
-- 테이블 ksmartdb.members_login_log 구조 내보내기
DROP TABLE IF EXISTS `members_login_log`;
CREATE TABLE IF NOT EXISTS `members_login_log` (
`login_no` int NOT NULL AUTO_INCREMENT COMMENT '로그인번호',
`login_id` varchar(20) NOT NULL COMMENT '로그인ID',
`login_ip` varchar(15) NOT NULL COMMENT '로그인IP',
`login_date` timestamp NOT NULL COMMENT '로그인일시',
PRIMARY KEY (`login_no`),
KEY `members_login_log_members_FK` (`login_id`),
CONSTRAINT `members_login_log_members_FK` FOREIGN KEY (`login_id`) REFERENCES `members` (`mbr_id`)
) ENGINE=InnoDB AUTO_INCREMENT=128 DEFAULT CHARSET=utf8mb3 COMMENT='로그인로그';
-- 테이블 데이터 ksmartdb.members_login_log:~127 rows (대략적) 내보내기
DELETE FROM `members_login_log`;
INSERT IGNORE INTO `members_login_log`
(`login_no`, `login_id`, `login_ip`, `login_date`)
VALUES
(1, 'ksmartid1', '192.168.1.1', '2023-12-31 23:00:00'),
(2, 'ksmartid2', '192.168.1.2', '2024-01-01 00:00:00'),
(3, 'ksmartid3', '192.168.1.3', '2024-01-01 00:00:00'),
(4, 'ksmartid4', '192.168.1.4', '2024-01-01 02:00:00'),
(5, 'ksmartid5', '192.168.1.5', '2024-01-01 06:00:00'),
(6, 'ksmartid6', '192.168.1.6', '2024-01-01 04:00:00'),
(7, 'ksmartid7', '192.168.1.7', '2024-01-01 05:00:00'),
(8, 'ksmartid8', '192.168.1.8', '2024-01-01 06:00:00'),
(9, 'ksmartid9', '192.168.1.9', '2024-01-01 07:00:00'),
(10, 'ksmartid10', '192.168.1.10', '2024-01-01 08:00:00'),
(11, 'ksmartid1', '192.168.1.1', '2024-01-01 23:00:00'),
(12, 'ksmartid2', '192.168.1.2', '2024-01-02 00:00:00'),
(13, 'ksmartid3', '192.168.1.3', '2024-01-02 00:05:00'),
(14, 'ksmartid5', '192.168.1.5', '2024-01-02 00:07:00'),
(15, 'ksmartid6', '192.168.1.6', '2024-01-02 04:00:00'),
(16, 'ksmartid7', '192.168.1.7', '2024-01-02 05:00:00'),
(17, 'ksmartid8', '192.168.1.8', '2024-01-02 06:00:00'),
(18, 'ksmartid1', '192.168.1.1', '2024-01-02 23:00:00'),
(19, 'ksmartid2', '192.168.1.2', '2024-01-03 00:00:00'),
(20, 'ksmartid4', '192.168.1.4', '2024-01-03 01:00:00'),
(21, 'ksmartid3', '192.168.1.3', '2024-01-03 02:00:00'),
(22, 'ksmartid9', '192.168.1.9', '2024-01-03 04:00:00'),
(23, 'ksmartid8', '192.168.1.8', '2024-01-03 06:00:00'),
(24, 'ksmartid10', '192.168.1.10', '2024-01-03 08:00:00'),
(25, 'ksmartid3', '192.168.1.3', '2024-01-03 22:00:00'),
(26, 'ksmartid1', '192.168.1.1', '2024-01-04 00:00:00'),
(27, 'ksmartid5', '192.168.1.5', '2024-01-04 00:07:00'),
(28, 'ksmartid6', '192.168.1.6', '2024-01-04 04:00:00'),
(29, 'ksmartid7', '192.168.1.7', '2024-01-04 05:00:00'),
(30, 'ksmartid8', '192.168.1.8', '2024-01-04 06:00:00'),
(31, 'ksmartid10', '192.168.1.10', '2024-01-04 08:00:00'),
(32, 'ksmartid5', '192.168.1.5', '2024-01-04 23:00:00'),
(33, 'ksmartid1', '192.168.1.1', '2024-01-05 00:00:00'),
(34, 'ksmartid2', '192.168.1.2', '2024-01-05 00:05:00'),
(35, 'ksmartid5', '192.168.1.5', '2024-01-05 00:07:00'),
(36, 'ksmartid6', '192.168.1.6', '2024-01-05 04:00:00'),
(37, 'ksmartid7', '192.168.1.7', '2024-01-05 05:00:00'),
(38, 'ksmartid8', '192.168.1.8', '2024-01-05 06:00:00'),
(39, 'ksmartid9', '192.168.1.9', '2024-01-05 10:00:00'),
(40, 'ksmartid3', '192.168.1.3', '2024-01-05 12:00:00'),
(41, 'ksmartid1', '192.168.1.1', '2024-01-05 23:00:00'),
(42, 'ksmartid2', '192.168.1.2', '2024-01-06 00:00:00'),
(43, 'ksmartid3', '192.168.1.3', '2024-01-06 01:00:00'),
(44, 'ksmartid4', '192.168.1.4', '2024-01-06 02:00:00'),
(45, 'ksmartid5', '192.168.1.5', '2024-01-06 03:00:00'),
(46, 'ksmartid6', '192.168.1.6', '2024-01-06 04:00:00'),
(47, 'ksmartid8', '192.168.1.8', '2024-01-06 06:00:00'),
(48, 'ksmartid10', '192.168.1.10', '2024-01-06 08:00:00'),
(49, 'ksmartid1', '192.168.1.1', '2024-01-06 23:00:00'),
(50, 'ksmartid3', '192.168.1.3', '2024-01-07 01:00:00'),
(51, 'ksmartid4', '192.168.1.4', '2024-01-07 02:00:00'),
(52, 'ksmartid6', '192.168.1.6', '2024-01-07 04:00:00'),
(53, 'ksmartid7', '192.168.1.7', '2024-01-07 05:00:00'),
(54, 'ksmartid8', '192.168.1.8', '2024-01-07 06:00:00'),
(55, 'ksmartid9', '192.168.1.9', '2024-01-07 07:00:00'),
(56, 'ksmartid10', '192.168.1.10', '2024-01-07 08:00:00'),
(57, 'ksmartid1', '192.168.1.1', '2024-01-07 23:00:00'),
(58, 'ksmartid3', '192.168.1.3', '2024-01-08 01:00:00'),
(59, 'ksmartid4', '192.168.1.4', '2024-01-08 02:00:00'),
(60, 'ksmartid6', '192.168.1.6', '2024-01-08 04:00:00'),
(61, 'ksmartid8', '192.168.1.8', '2024-01-08 06:00:00'),
(62, 'ksmartid9', '192.168.1.9', '2024-01-08 07:00:00'),
(63, 'ksmartid10', '192.168.1.10', '2024-01-08 08:00:00'),
(64, 'ksmartid1', '192.168.1.1', '2024-01-08 23:00:00'),
(65, 'ksmartid2', '192.168.1.2', '2024-01-09 00:00:00'),
(66, 'ksmartid4', '192.168.1.4', '2024-01-09 02:00:00'),
(67, 'ksmartid6', '192.168.1.6', '2024-01-09 04:00:00'),
(68, 'ksmartid7', '192.168.1.7', '2024-01-09 05:00:00'),
(69, 'ksmartid9', '192.168.1.9', '2024-01-09 07:00:00'),
(70, 'ksmartid1', '192.168.1.1', '2024-01-09 23:00:00'),
(71, 'ksmartid2', '192.168.1.2', '2024-01-10 00:00:00'),
(72, 'ksmartid3', '192.168.1.3', '2024-01-10 01:00:00'),
(73, 'ksmartid4', '192.168.1.4', '2024-01-10 02:00:00'),
(74, 'ksmartid5', '192.168.1.5', '2024-01-10 03:00:00'),
(75, 'ksmartid6', '192.168.1.6', '2024-01-10 04:00:00'),
(76, 'ksmartid7', '192.168.1.7', '2024-01-10 05:00:00'),
(77, 'ksmartid8', '192.168.1.8', '2024-01-10 06:00:00'),
(78, 'ksmartid10', '192.168.1.10', '2024-01-10 08:00:00'),
(79, 'ksmartid1', '192.168.1.1', '2024-01-10 23:00:00'),
(80, 'ksmartid2', '192.168.1.2', '2024-01-11 00:00:00'),
(81, 'ksmartid4', '192.168.1.4', '2024-01-11 02:00:00'),
(82, 'ksmartid5', '192.168.1.5', '2024-01-11 03:00:00'),
(83, 'ksmartid6', '192.168.1.6', '2024-01-11 04:00:00'),
(84, 'ksmartid7', '192.168.1.7', '2024-01-11 05:00:00'),
(85, 'ksmartid8', '192.168.1.8', '2024-01-11 06:00:00'),
(86, 'ksmartid1', '192.168.1.1', '2024-01-11 23:00:00'),
(87, 'ksmartid2', '192.168.1.2', '2024-01-12 00:00:00'),
(88, 'ksmartid3', '192.168.1.3', '2024-01-12 01:00:00'),
(89, 'ksmartid4', '192.168.1.4', '2024-01-12 02:00:00'),
(90, 'ksmartid5', '192.168.1.5', '2024-01-12 03:00:00'),
(91, 'ksmartid6', '192.168.1.6', '2024-01-12 04:00:00'),
(92, 'ksmartid7', '192.168.1.7', '2024-01-12 05:00:00'),
(93, 'ksmartid8', '192.168.1.8', '2024-01-12 06:00:00'),
(94, 'ksmartid9', '192.168.1.9', '2024-01-12 07:00:00'),
(95, 'ksmartid10', '192.168.1.10', '2024-01-12 08:00:00'),
(96, 'ksmartid1', '192.168.1.1', '2024-01-12 23:00:00'),
(97, 'ksmartid2', '192.168.1.2', '2024-01-13 00:00:00'),
(98, 'ksmartid3', '192.168.1.3', '2024-01-13 01:00:00'),
(99, 'ksmartid4', '192.168.1.4', '2024-01-13 02:00:00'),
(100, 'ksmartid5', '192.168.1.5', '2024-01-13 03:00:00'),
(101, 'ksmartid6', '192.168.1.6', '2024-01-13 04:00:00'),
(102, 'ksmartid7', '192.168.1.7', '2024-01-13 05:00:00'),
(103, 'ksmartid8', '192.168.1.8', '2024-01-13 06:00:00'),
(104, 'ksmartid1', '192.168.1.1', '2024-01-13 23:00:00'),
(105, 'ksmartid2', '192.168.1.2', '2024-01-14 00:00:00'),
(106, 'ksmartid3', '192.168.1.3', '2024-01-14 01:00:00'),
(107, 'ksmartid4', '192.168.1.4', '2024-01-14 02:00:00'),
(108, 'ksmartid5', '192.168.1.5', '2024-01-14 03:00:00'),
(109, 'ksmartid6', '192.168.1.6', '2024-01-14 04:00:00'),
(110, 'ksmartid7', '192.168.1.7', '2024-01-14 05:00:00'),
(111, 'ksmartid9', '192.168.1.9', '2024-01-14 07:00:00'),
(112, 'ksmartid1', '192.168.1.1', '2024-01-14 23:00:00'),
(113, 'ksmartid2', '192.168.1.2', '2024-01-15 00:00:00'),
(114, 'ksmartid3', '192.168.1.3', '2024-01-15 01:00:00'),
(115, 'ksmartid4', '192.168.1.4', '2024-01-15 02:00:00'),
(116, 'ksmartid5', '192.168.1.5', '2024-01-15 03:00:00'),
(117, 'ksmartid6', '192.168.1.6', '2024-01-15 04:00:00'),
(118, 'ksmartid7', '192.168.1.7', '2024-01-15 05:00:00'),
(119, 'ksmartid8', '192.168.1.8', '2024-01-15 06:00:00'),
(120, 'ksmartid9', '192.168.1.9', '2024-01-15 07:00:00'),
(121, 'ksmartid1', '192.168.1.1', '2024-01-15 23:00:00'),
(122, 'ksmartid2', '192.168.1.2', '2024-01-16 00:00:00'),
(123, 'ksmartid3', '192.168.1.3', '2024-01-16 01:00:00'),
(124, 'ksmartid5', '192.168.1.5', '2024-01-16 03:00:00'),
(125, 'ksmartid6', '192.168.1.6', '2024-01-16 04:00:00'),
(126, 'ksmartid8', '192.168.1.8', '2024-01-16 06:00:00'),
(127, 'ksmartid9', '192.168.1.9', '2024-01-16 07:00:00');
-- 테이블 ksmartdb.vendors 구조 내보내기
DROP TABLE IF EXISTS `vendors`;
CREATE TABLE IF NOT EXISTS `vendors` (
`vend_cd` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '판매처코드',
`vend_mbr_id` varchar(20) NOT NULL COMMENT '판매처 아이디',
`vend_brno` varchar(20) DEFAULT NULL COMMENT '판매처사업자등록번호',
`vend_name` varchar(50) NOT NULL COMMENT '판매처명',
`vend_addr` varchar(50) DEFAULT NULL COMMENT '판매처주소',
`vend_daddr` varchar(50) DEFAULT NULL COMMENT '판매처상세주소',
`vend_zip` int NOT NULL COMMENT '판매처우편번호',
`vend_telno` varchar(20) DEFAULT NULL COMMENT '판매처연락처',
PRIMARY KEY (`vend_cd`),
KEY `vendors_members_FK` (`vend_mbr_id`),
CONSTRAINT `vendors_members_FK` FOREIGN KEY (`vend_mbr_id`) REFERENCES `members` (`mbr_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='판매처';
-- 테이블 데이터 ksmartdb.vendors:~0 rows (대략적) 내보내기
DELETE FROM `vendors`;
INSERT IGNORE INTO `vendors`
(`vend_cd`, `vend_mbr_id`, `vend_brno`, `vend_name`, `vend_addr`, `vend_daddr`, `vend_zip`, `vend_telno`)
VALUES
('vend_1', 'ksmartid2', '001-01-00001', '판매처1', '판매처 주소1', '판매처 상세 주소1', 00012, '010-0012-0012'),
('vend_2', 'ksmartid4', '002-02-00002', '판매처2', '판매처 주소2', '판매처 상세 주소2', 00024, '010-0024-0024'),
('vend_3', 'ksmartid7', '003-03-00003', '판매처3', '판매처 주소3', '판매처 상세 주소3', 00037, '010-0037-0037'),
('vend_4', 'ksmartid8', '004-04-00004', '판매처4', '판매처 주소4', '판매처 상세 주소4', 00048, '010-0048-0048');
-- 테이블 ksmartdb.products 구조 내보내기
DROP TABLE IF EXISTS `products`;
CREATE TABLE IF NOT EXISTS `products` (
`prod_cd` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '제품코드',
`vend_cd` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '제품판매처코드',
`prod_name` varchar(50) NOT NULL COMMENT '제품명',
`prod_untprc` int NOT NULL COMMENT '제품가격',
`prod_reg_date` date NOT NULL COMMENT '제품등록일자',
PRIMARY KEY (`prod_cd`),
KEY `products_vendors_FK` (`vend_cd`),
CONSTRAINT `products_vendors_FK` FOREIGN KEY (`vend_cd`) REFERENCES `vendors` (`vend_cd`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='상품';
-- 테이블 데이터 ksmartdb.products:~0 rows (대략적) 내보내기
DELETE FROM `products`;
INSERT IGNORE INTO `products`
(`prod_cd`, `vend_cd`, `prod_name`, `prod_untprc`, `prod_reg_date`)
VALUES
('prod_1', 'vend_1', '모니터', 100000, '2023-12-21'),
('prod_10', 'vend_2', '프린터', 130000, '2023-12-22'),
('prod_11', 'vend_2', 'RAM', 600000, '2023-12-22'),
('prod_12', 'vend_2', '그래픽카드', 50000, '2023-12-22'),
('prod_13', 'vend_2', '키보드', 30000, '2023-12-22'),
('prod_14', 'vend_2', '마우스', 170000, '2023-12-22'),
('prod_15', 'vend_2', 'SSD', 8000, '2023-12-22'),
('prod_16', 'vend_2', 'USB', 600000, '2023-12-22'),
('prod_17', 'vend_2', 'CPU', 20000, '2023-12-22'),
('prod_18', 'vend_2', '스피커', 200000, '2023-12-22'),
('prod_19', 'vend_1', '스캐너', 50000, '2023-12-23'),
('prod_2', 'vend_1', '모니터', 100000, '2023-12-21'),
('prod_20', 'vend_1', '프린터', 80000, '2023-12-23'),
('prod_21', 'vend_1', 'RAM', 770000, '2023-12-23'),
('prod_22', 'vend_1', '그래픽카드', 130000, '2023-12-23'),
('prod_23', 'vend_1', '키보드', 70000, '2023-12-23'),
('prod_24', 'vend_1', '마우스', 800000, '2023-12-23'),
('prod_25', 'vend_1', 'SSD', 100000, '2023-12-23'),
('prod_26', 'vend_1', 'USB', 700000, '2023-12-23'),
('prod_27', 'vend_1', 'CPU', 40000, '2023-12-23'),
('prod_28', 'vend_2', '스피커', 50000, '2023-12-23'),
('prod_29', 'vend_3', '스캐너', 30000, '2023-12-24'),
('prod_3', 'vend_1', '키보드', 50000, '2023-12-21'),
('prod_30', 'vend_1', '프린터', 90000, '2023-12-25'),
('prod_31', 'vend_3', 'RAM', 440000, '2023-12-25'),
('prod_32', 'vend_3', '그래픽카드', 60000, '2023-12-25'),
('prod_33', 'vend_3', '키보드', 70000, '2023-12-25'),
('prod_34', 'vend_3', '그래픽카드', 135000, '2023-12-25'),
('prod_35', 'vend_3', '키보드', 120000, '2023-12-25'),
('prod_36', 'vend_3', '그래픽카드', 90000, '2023-12-25'),
('prod_37', 'vend_3', '마우스', 10000, '2023-12-25'),
('prod_38', 'vend_3', '키보드', 6000, '2023-12-25'),
('prod_39', 'vend_3', 'USB', 55000, '2023-12-25'),
('prod_4', 'vend_1', '마우스', 200000, '2023-12-21'),
('prod_40', 'vend_3', '키보드', 13000, '2023-12-25'),
('prod_41', 'vend_3', 'USB', 160000, '2023-12-25'),
('prod_42', 'vend_2', '키보드', 190000, '2023-12-26'),
('prod_43', 'vend_2', '스캐너', 10000, '2023-12-26'),
('prod_44', 'vend_2', 'USB', 13000, '2023-12-26'),
('prod_45', 'vend_2', '키보드', 123000, '2023-12-26'),
('prod_46', 'vend_2', 'USB', 19000, '2023-12-26'),
('prod_47', 'vend_2', '키보드', 148000, '2023-12-26'),
('prod_48', 'vend_2', 'USB', 340000, '2023-12-26'),
('prod_49', 'vend_2', '키보드', 410000, '2023-12-26'),
('prod_5', 'vend_1', 'SSD', 5000, '2023-12-21'),
('prod_50', 'vend_3', '키보드', 200000, '2023-12-27'),
('prod_51', 'vend_3', 'CPU', 60000, '2023-12-27'),
('prod_52', 'vend_3', '키보드', 800000, '2023-12-27'),
('prod_53', 'vend_2', 'SSD', 100000, '2023-12-27'),
('prod_54', 'vend_1', '프린터', 125000, '2023-12-27'),
('prod_55', 'vend_1', 'SSD', 34000, '2023-12-27'),
('prod_56', 'vend_1', '키보드', 28000, '2023-12-30'),
('prod_57', 'vend_1', '마우스', 100000, '2023-12-30'),
('prod_58', 'vend_2', 'SSD', 300000, '2023-12-30'),
('prod_59', 'vend_2', '프린터', 299900, '2023-12-30'),
('prod_6', 'vend_1', 'USB', 400000, '2023-12-21'),
('prod_7', 'vend_1', 'CPU', 500000, '2023-12-21'),
('prod_8', 'vend_2', '스피커', 100000, '2023-12-22'),
('prod_9', 'vend_2', '스캐너', 80000, '2023-12-22');
-- 테이블 ksmartdb.orders 구조 내보내기
DROP TABLE IF EXISTS `orders`;
CREATE TABLE IF NOT EXISTS `orders` (
`order_no` int NOT NULL AUTO_INCREMENT COMMENT '주문번호',
`cust_id` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '주문고객ID',
`order_date` datetime NOT NULL COMMENT '주문날짜',
PRIMARY KEY (`order_no`),
KEY `orders_members_FK` (`cust_id`),
CONSTRAINT `orders_members_FK` FOREIGN KEY (`cust_id`) REFERENCES `members` (`mbr_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb3 COMMENT='주문';
-- 테이블 데이터 ksmartdb.orders:~9 rows (대략적) 내보내기
DELETE FROM `orders`;
INSERT IGNORE INTO `orders`
(`order_no`, `cust_id`, `order_date`)
VALUES
(1, 'ksmartid3', '2024-01-01 09:26:07'),
(2, 'ksmartid5', '2024-01-01 16:01:00'),
(3, 'ksmartid5', '2024-01-02 10:20:03'),
(4, 'ksmartid3', '2024-01-03 12:28:01'),
(5, 'ksmartid9', '2024-01-03 14:26:50'),
(6, 'ksmartid3', '2024-01-04 08:50:07'),
(7, 'ksmartid5', '2024-01-05 09:40:07'),
(8, 'ksmartid9', '2024-01-05 20:30:09'),
(9, 'ksmartid3', '2024-01-05 22:19:20');
-- 테이블 ksmartdb.orderitems 구조 내보내기
DROP TABLE IF EXISTS `orderitems`;
CREATE TABLE IF NOT EXISTS `orderitems` (
`order_no` int NOT NULL COMMENT '주문번호',
`order_item` varchar(20) NOT NULL COMMENT '주문항목번호',
`order_prod_cd` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '제품ID',
`order_cnt` int NOT NULL COMMENT '제품수량',
PRIMARY KEY (`order_no`,`order_item`),
KEY `orderitems_products_FK` (`order_prod_cd`),
CONSTRAINT `orderitems_orders_FK` FOREIGN KEY (`order_no`) REFERENCES `orders` (`order_no`),
CONSTRAINT `orderitems_products_FK` FOREIGN KEY (`order_prod_cd`) REFERENCES `products` (`prod_cd`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='주문상세';
-- 테이블 데이터 ksmartdb.orderitems:~9 rows (대략적) 내보내기
DELETE FROM `orderitems`;
INSERT IGNORE INTO `orderitems`
(order_no, order_item, order_prod_cd, order_cnt)
VALUES
(1, 'item_2401011','prod_2', 4),
(1, 'item_2401012','prod_4', 1),
(1, 'item_2401013','prod_6', 2),
(1, 'item_2401014','prod_9', 5),
(1, 'item_2401015','prod_10', 10),
(2, 'item_2401011','prod_1', 3),
(3, 'item_2401021','prod_15', 1),
(3, 'item_2401022','prod_17', 6),
(4, 'item_2401031','prod_20', 7),
(4, 'item_2401032','prod_26', 2),
(4, 'item_2401033','prod_30', 3),
(5, 'item_2401031','prod_40', 11),
(6, 'item_2401042','prod_2', 12),
(7, 'item_2401051','prod_6', 3),
(7, 'item_2401052','prod_7', 5),
(7, 'item_2401053','prod_13', 1),
(7, 'item_2401054','prod_37', 1),
(8, 'item_2401051','prod_21', 4),
(8, 'item_2401052','prod_26', 15),
(8, 'item_2401053','prod_33', 7),
(9, 'item_2401051','prod_40', 10),
(9, 'item_2401052','prod_50', 8);
/*!40103 SET TIME_ZONE=IFNULL(@OLD_TIME_ZONE, 'system') */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;복붙후 전체실행
728x90
반응형