search

DE32-3nd-team1 / DE32-3nd-team1

0 stars 0 forks source link

DB TABLE 생성 #4

Open sooj1n opened 1 month ago

sooj1n commented 1 month ago

AWS 서버 접속

$ ssh -i samdulko.pem ubuntu@52.78.215.75

docker run

$ sudo docker run -d \
        --name mariadb \
        -e MARIADB_USER=team1 \
        --env MARIADB_PASSWORD=1234 \
        --env MARIADB_DATABASE=team1 \
        --env MARIADB_ROOT_PASSWORD=my-secret-pw \
        -p 53306:3306 \
        mariadb:latest

docker확인

$ sudo docker ps
CONTAINER ID   IMAGE            COMMAND                  CREATED       STATUS       PORTS                                           NAMES
56513afb256c   mariadb:latest   "docker-entrypoint.s…"   2 hours ago   Up 2 hours   0.0.0.0:53306->3306/tcp, [::]:53306->3306/tcp   mariadb

docker 실행

$ sudo docker exec -it mariadb bash
root@56513afb256c:/# mariadb -u team1 -p1234
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 11.5.2-MariaDB-ubu2404 mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| team1              |
+--------------------+
2 rows in set (0.001 sec)

MariaDB [(none)]> use team1;
Database changed

MariaDB [team1]> show tables;
+-----------------+
| Tables_in_team1 |
+-----------------+
| receipt         |
+-----------------+
1 row in set (0.001 sec)

MariaDB [team1]> select * from receipt;
+-----+------------+--------+------------+--------------+---------------------+
| num | date       | name   | amount     | payment      | address
  |
+-----+------------+--------+------------+--------------+---------------------+
|   1 | 2024-10-01 | Laptop | 1500000.00 | 신용카드     | 서울시 서초구       |
+-----+------------+--------+------------+--------------+---------------------+
1 row in set (0.001 sec)
lsiwh37249 commented 1 month ago 
CREATE TABLE receipt (
    id SERIAL PRIMARY KEY,   -- 고유 ID, 자동 증가
    date DATE,               -- 날짜 (선택적)
    nm VARCHAR(255) NOT NULL, -- 상품명
    unit_price INT NOT NULL, -- 단가
    cnt INT NOT NULL,        -- 수량
);
tbongkim03 commented 1 month ago 
CREATE TABLE model (
    num INT AUTO_INCREMENT PRIMARY KEY COMMENT '자동 증가 숫자',
    FOREIGN KEY (goods_id) REFERENCES goods(id) ON DELETE CASCADE ON UPDATE CASCADE COMMENT 'goods 테이블 참조',
    purchase_time VARCHAR(200) NOT NULL COMMENT '구매 일자 및 시간',
    predict_bool BOOLEAN NOT NULL DEFAULT false COMMENT '예측 여부'
);
CREATE TABLE goods (
    id INT PRIMARY KEY COMMENT '중복되지 않는난수',
    name VARCHAR(100) NOT NULL COMMENT '상품명',
    cnt INT NOT NULL COMMENT '수량',
    won INT NOT NULL COMMENT '금액'
);
CREATE TABLE labels (
    num INT AUTO_INCREMENT PRIMARY KEY COMMENT '자동 증가 숫자',
    FOREIGN KEY (goods_id) REFERENCES goods(id) ON DELETE CASCADE ON UPDATE CASCADE COMMENT 'goods 테이블 참조',
    name VARCHAR(100) NOT NULL COMMENT '상품명',
    cnt INT NOT NULL COMMENT '수량',
    won INT NOT NULL COMMENT '금액'
);
tbongkim03 commented 1 month ago

수정된 버전

CREATE TABLE model (
    num INT AUTO_INCREMENT PRIMARY KEY COMMENT '자동 증가 숫자',
    FOREIGN KEY (goods_id) REFERENCES goods(id) ON DELETE CASCADE ON UPDATE CASCADE COMMENT 'goods 테이블 참조',
    purchase_time VARCHAR(200) NOT NULL COMMENT '구매 일자 및 시간',
    predict_bool BOOLEAN NOT NULL DEFAULT false COMMENT '예측 여부',
    total INT NOT NULL COMMENT 'goods 테이블에서 같은 id를 가진 상품들의 won 합계를 저장'
 );
 CREATE TABLE goods (
    id INT PRIMARY KEY COMMENT '중복되지 않는난수',
    name VARCHAR(100) NOT NULL COMMENT '상품명',
    cnt INT NOT NULL COMMENT '수량',
    won INT NOT NULL COMMENT '금액'
 );
 CREATE TABLE labels (
    num INT AUTO_INCREMENT PRIMARY KEY COMMENT '자동 증가 숫자',
    FOREIGN KEY (goods_id) REFERENCES goods(id) ON DELETE CASCADE ON UPDATE CASCADE COMMENT 'goods 테이블 참조',
    name VARCHAR(100) NOT NULL COMMENT '상품명',
    cnt INT NOT NULL COMMENT '수량',
    won INT NOT NULL COMMENT '금액'
 );
tbongkim03 commented 1 month ago

재 수정

CREATE TABLE goods (
    id INT PRIMARY KEY COMMENT '중복되지 않는 난수',
    name VARCHAR(100) NOT NULL COMMENT '상품명',
    cnt INT NOT NULL COMMENT '수량',
    won INT NOT NULL COMMENT '금액'
);

CREATE TABLE model (
    num INT AUTO_INCREMENT PRIMARY KEY COMMENT '자동 증가 숫자',
    goods_id INT NOT NULL COMMENT 'goods 테이블 참조용 외래 키',
    purchase_date VARCHAR(200) NOT NULL COMMENT '구매 일자 및 시간',
    predict_bool BOOLEAN NOT NULL DEFAULT false COMMENT '예측 여부',
    total INT NOT NULL COMMENT 'goods 테이블에서 같은 id를 가진 상품들의 won 합계를 저장',
    FOREIGN KEY (goods_id) REFERENCES goods(id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE labels (
    num INT AUTO_INCREMENT PRIMARY KEY COMMENT '자동 증가 숫자',
    goods_id INT NOT NULL COMMENT 'goods 테이블 참조용 외래 키',
    name VARCHAR(100) NOT NULL COMMENT '상품명',
    cnt INT NOT NULL COMMENT '수량',
    won INT NOT NULL COMMENT '금액',
    FOREIGN KEY (goods_id) REFERENCES goods(id) ON DELETE CASCADE ON UPDATE CASCADE
);
tbongkim03 commented 1 month ago

재재수정

CREATE TABLE goods (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '자동 증가 숫자',
    name VARCHAR(100) NOT NULL COMMENT '상품명',
    cnt INT NOT NULL COMMENT '수량',
    won INT NOT NULL COMMENT '금액'
);

CREATE TABLE model (
    num INT AUTO_INCREMENT PRIMARY KEY COMMENT '자동 증가 숫자',
    goods_id INT NOT NULL COMMENT 'goods 테이블 참조용 외래 키',
    purchase_date VARCHAR(200) NOT NULL COMMENT '구매 일자 및 시간',
    predict_bool BOOLEAN NOT NULL DEFAULT false COMMENT '예측 여부',
    total INT NOT NULL COMMENT 'goods 테이블에서 같은 id를 가진 상품들의 won 합계를 저장',
    img_src VARCHAR(500) NOT NULL COMMENT '이미지 경로',
    FOREIGN KEY (goods_id) REFERENCES goods(id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE labels (
    num INT AUTO_INCREMENT PRIMARY KEY COMMENT '자동 증가 숫자',
    goods_id INT NOT NULL COMMENT 'goods 테이블 참조용 외래 키',
    name VARCHAR(100) NOT NULL COMMENT '상품명',
    cnt INT NOT NULL COMMENT '수량',
    won INT NOT NULL COMMENT '금액',
    FOREIGN KEY (goods_id) REFERENCES goods(id) ON DELETE CASCADE ON UPDATE CASCADE
);
tbongkim03 commented 1 month ago

재재재수정

CREATE TABLE goods (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '자동 증가 숫자',
    model_id INT NOT NULL COMMENT 'model 테이블 참조용 외래 키',
    name VARCHAR(100) NOT NULL COMMENT '상품명',
    cnt INT NOT NULL COMMENT '수량',
    won INT NOT NULL COMMENT '금액',
    FOREIGN KEY (model_id) REFERENCES model(id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE model (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '자동 증가 숫자',
    purchase_date VARCHAR(200) NOT NULL COMMENT '구매 일자 및 시간',
    predict_bool BOOLEAN NOT NULL DEFAULT false COMMENT '예측 여부',
    total INT COMMENT 'goods 테이블에서 같은 id를 가진 상품들의 won 합계를 저장',
    img_src VARCHAR(500) NOT NULL COMMENT '이미지 경로'
);

CREATE TABLE labels (
    num INT AUTO_INCREMENT PRIMARY KEY COMMENT '자동 증가 숫자',
    goods_id INT NOT NULL COMMENT 'goods 테이블 참조용 외래 키',
    name VARCHAR(100) NOT NULL COMMENT '상품명',
    cnt INT NOT NULL COMMENT '수량',
    won INT NOT NULL COMMENT '금액',
    FOREIGN KEY (goods_id) REFERENCES goods(id) ON DELETE CASCADE ON UPDATE CASCADE
);
lsiwh37249 commented 1 month ago

재재재재수정

-- 상품 예측 후 결과, 상품들 
CREATE TABLE goods (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '자동 증가 숫자',
    model_id INT NOT NULL COMMENT 'model 테이블 참조용 외래 키',
    name VARCHAR(100) NOT NULL COMMENT '상품명',
    cnt INT NOT NULL COMMENT '수량',
    won INT NOT NULL COMMENT '금액',
    FOREIGN KEY (model_id) REFERENCES model(id) ON DELETE CASCADE ON UPDATE CASCADE
);
-- 상품 예측 후 결과, 영수증 1개
CREATE TABLE model (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '자동 증가 숫자',
    purchase_date VARCHAR(200) NOT NULL COMMENT '구매 일자 및 시간',
    weekday VARCHAR(50) NOT NULL COMMENT '요일 정보',
    predict_bool BOOLEAN NOT NULL DEFAULT false COMMENT '예측 여부',
    total INT COMMENT 'goods 테이블에서 같은 id를 가진 상품들의 won 합계를 저장',
    img_src VARCHAR(500) NOT NULL COMMENT '이미지 경로'
);
-- 상품 예측 후 관리자 입력한 정보
CREATE TABLE labels (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '자동 증가 숫자',
    name VARCHAR(100) NOT NULL COMMENT '상품명',
    cnt INT NOT NULL COMMENT '수량',
    won INT NOT NULL COMMENT '금액',
    model_id INT NOT NULL COMMENT '상품 ID',  -- 외래키 필드를 추가
    FOREIGN KEY (model_id) REFERENCES model(id) ON DELETE CASCADE ON UPDATE CASCADE
);