create database inhouse3;
use inhouse3;
-- 테이블삭제
drop table EmployeeRegistration;
-- 거래처정보
CREATE TABLE CustomerInformation(
id INT NOT NULL AUTO_INCREMENT,
BRN VARCHAR(20),
CRN CHAR(20),
bnsiness VARCHAR(20),
address VARCHAR(100),
customer_tell CHAR(20),
salesincharge VARCHAR(30),
primary key(id)
);
-- 정보 데이터 확인하는 곳
select * from CustomerInformation;
-- 거래처정보 데이터
INSERT into CustomerInformation (BRN, CRN, bnsiness, address, customer_tell, salesincharge)
values ('칼포니치킨', '169-44-00749', '주영삼', '서울특별시 금천구 가산동 142-47 지상 1층', '02111111', '치킨');
INSERT into CustomerInformation (BRN, CRN, bnsiness, address, customer_tell, salesincharge)
values('누구나홀딱반한닭', '221-47-12113', '한광일', '서울특별시 금천구 가산동 142-52 지상1층(가산동)', '02111112', '치킨');
INSERT into CustomerInformation (BRN, CRN, bnsiness, address, customer_tell, salesincharge)
values('스타에델치킨본점', '295-38-00348', '송명재', '서울특별시 금천구 가산동 46-6 1층 7,8호(가산동)', '02111113', '치킨');
INSERT into CustomerInformation (BRN, CRN, bnsiness, address, customer_tell, salesincharge)
values('BHC가산디지털점', '489-21-01245', '김희성', '서울특별시 금천구 가산동 371-106 지상2층 213호)', '02111114', '치킨');
INSERT into CustomerInformation (BRN, CRN, bnsiness, address, customer_tell, salesincharge)
values('정석닭강정 구로점', '372-87-02302', '채미애', '서울특별시 구로구 구로동 1280 상가 2동 1층 218호,219호', '02111115', '치킨');
INSERT into CustomerInformation (BRN, CRN, bnsiness, address, customer_tell, salesincharge)
values('치코치코', '133-09-36757', '하덕일', '경기도 광명시 철산동 441', '02111116', '치킨');
INSERT into CustomerInformation (BRN, CRN, bnsiness, address, customer_tell, salesincharge)
values('대한민족', '499-58-00405', '강미정', '서울특별시 구로구 구로동 1124-55 1층(구로동)', '02111117', '족발');
INSERT into CustomerInformation (BRN, CRN, bnsiness, address, customer_tell, salesincharge)
values('광복왕족발', '140-08-08913', '권혁중', '경기도 광명시 철산동 56-15 1층', '02111118', '족발');
INSERT into CustomerInformation (BRN, CRN, bnsiness, address, customer_tell, salesincharge)
values('우정수육', '632-06-02447', '전영호', '서울특별시 구로구 구로동 1125-10 1층(구로동)', '02111119', '족발');
INSERT into CustomerInformation (BRN, CRN, bnsiness, address, customer_tell, salesincharge)
values('조선귀족 구로점', '537-56-00697', '변영익', '서울특별시 구로구 굴동 487-76 1층 102호(구로동)', '02111110', '족발');
SELECT BRN as 사업자명, CRN as 사업자등록번호, bnsiness as 대표성함, address as 주소, customer_tell as 전화번호, salesincharge as 담당영업
FROM CustomerInformation;
SELECT BRN as 사업자명, CRN as 사업자등록번호
FROM CustomerInformation;
-- 임직원등록 (거래처정보)
CREATE TABLE EmployeeRegistration(
id INT NOT NULL AUTO_INCREMENT,
Employee_name VARCHAR(20),
Employee_age INT,
Employee_gender VARCHAR(20),
Employee_Teamaffiliation VARCHAR(30),
Employee_Permissions VARCHAR(20),
primary key(id),
c_id int,
foreign key (`c_id`) references `CustomerInformation`(`id`)
);
-- 정보 데이터 확인하는 곳
select * from EmployeeRegistration;
-- 임직원 데이터
INSERT into EmployeeRegistration (Employee_name, Employee_age, Employee_gender, Employee_Teamaffiliation, Employee_Permissions, c_id)
values('주영삼', 26, '남', 'B2B', 'Y', 1);
INSERT into EmployeeRegistration (Employee_name, Employee_age, Employee_gender, Employee_Teamaffiliation, Employee_Permissions, c_id)
values('한광일', 27, '남', 'B2B', 'Y', 2);
INSERT into EmployeeRegistration (Employee_name, Employee_age, Employee_gender, Employee_Teamaffiliation, Employee_Permissions, c_id)
values('송명재', 28, '남', 'BTS', 'N', 3);
INSERT into EmployeeRegistration (Employee_name, Employee_age, Employee_gender, Employee_Teamaffiliation, Employee_Permissions, c_id)
values('김희성', 30, '남', 'B2B', 'Y', 4);
INSERT into EmployeeRegistration (Employee_name, Employee_age, Employee_gender, Employee_Teamaffiliation, Employee_Permissions, c_id)
values('채미애', 22, '여', 'B2B', 'N', 5);
INSERT into EmployeeRegistration (Employee_name, Employee_age, Employee_gender, Employee_Teamaffiliation, Employee_Permissions, c_id)
values('하덕일', 39, '남', 'Sales', 'N', 6);
INSERT into EmployeeRegistration (Employee_name, Employee_age, Employee_gender, Employee_Teamaffiliation, Employee_Permissions, c_id)
values('강미정', 27, '여', 'Sales', 'Y', 7);
INSERT into EmployeeRegistration (Employee_name, Employee_age, Employee_gender, Employee_Teamaffiliation, Employee_Permissions, c_id)
values('권혁중', 35, '남', 'BTS', 'N', 8);
INSERT into EmployeeRegistration (Employee_name, Employee_age, Employee_gender, Employee_Teamaffiliation, Employee_Permissions, c_id)
values('전영호', 32, '남', 'B2B', 'Y', 9);
INSERT into EmployeeRegistration (Employee_name, Employee_age, Employee_gender, Employee_Teamaffiliation, Employee_Permissions, c_id)
values('변영익', 40, '남', 'Sales', 'Y', 10);
-- 연결 연산자
SELECT Employee_name || '의 나이는' || Employee_age || '입니다' as 나이정보
FROM EmployeeRegistration;
-- 연결 연산자2
select concat(Employee_name, Employee_age) from EmployeeRegistration;
-- 데이터를 정렬해서 출력하기
select Employee_name, Employee_age
FROM EmployeeRegistration
ORDER BY Employee_age asc;
-- 비교연산자 배우기(LIKE)
SELECT Employee_name, Employee_age
FROM EmployeeRegistration
WHERE Employee_name LIKE '하%';
-- 비교연산자 배우기(LIKE)
SELECT Employee_name, Employee_age
FROM EmployeeRegistration
WHERE Employee_name LIKE '%영%';
-- 매출조회 (거래처 정보)
CREATE TABLE Salesinquiry(
num INT NOT NULL AUTO_INCREMENT,
sales_account VARCHAR(30),
sales_date DATE,
sales_depositdate DATE,
quantity INT,
unitprice INT,
finalamount INT,
Tradingstatement VARCHAR(30),
primary key(num),
c_id int,
foreign key(`c_id`) references `CustomerInformation`(`id`)
);
-- 정보 데이터 확인하는 곳
select * from Salesinquiry;
-- 매출조회 데이터
INSERT INTO Salesinquiry (sales_account, sales_date, sales_depositdate, quantity, unitprice, finalamount, Tradingstatement, c_id)
values('칼포니','2023-10-1','2023-10-10', 300, 210, 63000, 20231001, 1);
INSERT INTO Salesinquiry (sales_account, sales_date, sales_depositdate, quantity, unitprice, finalamount, Tradingstatement, c_id)
values('누구나홀딱반한닭','2023-10-5','2023-10-20', 900, 4933, 4439700, 20231005, 2);
INSERT into Salesinquiry (sales_account, sales_date, sales_depositdate, quantity, unitprice, finalamount, Tradingstatement, c_id)
values('스타에델치킨','2023-10-1','2023-10-10', 500, 3959, 1979599, 20231001, 3);
INSERT into Salesinquiry (sales_account, sales_date, sales_depositdate, quantity, unitprice, finalamount, Tradingstatement, c_id)
values('BHC','2023-10-5','2023-10-15', 600, 4933, 3466500, 20231005, 4);
INSERT into Salesinquiry (sales_account, sales_date, sales_depositdate, quantity, unitprice, finalamount, Tradingstatement, c_id)
values('정석닭강정','2023-10-7','2023-10-23', 400, 3959, 1583666, 20231007, 5);
INSERT into Salesinquiry (sales_account, sales_date, sales_depositdate, quantity, unitprice, finalamount, Tradingstatement, c_id)
values('치코치코','2023-10-3','2023-10-30', 500, 3959, 1979500, 20231003, 6);
INSERT into Salesinquiry (sales_account, sales_date, sales_depositdate, quantity, unitprice, finalamount, Tradingstatement, c_id)
values('대한민족','2023-10-9','2023-10-15', 400, 3959, 1583600, 20231009, 7);
INSERT into Salesinquiry (sales_account, sales_date, sales_depositdate, quantity, unitprice, finalamount, Tradingstatement, c_id)
values('광복왕족발','2023-10-4','2023-10-25', 300, 210, 63000, 20231004, 8);
INSERT into Salesinquiry (sales_account, sales_date, sales_depositdate, quantity, unitprice, finalamount, Tradingstatement, c_id)
values('우정수육','2023-10-1','2023-10-19', 500, 4933, 2466500, 20231001, 9);
INSERT into Salesinquiry (sales_account, sales_date, sales_depositdate, quantity, unitprice, finalamount, Tradingstatement, c_id)
values('조선귀족 구로점','2023-10-2','2023-10-20', 800, 4933, 3946400, 20231002, 10);
-- 특정철자잘라내기
SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
SELECT TRIM(TRAILING 'x' FROM 'xxxbarxxx');
-- WHERE절 배우기(숫자 데이터 검색)
SELECT sales_account, sales_date, unitprice
FROM Salesinquiry
WHERE unitprice = 4933;
-- WHERE절로 비교 연산자 사용
SELECT sales_account as 거래처, sales_date as 날짜, unitprice as 단가
FROM Salesinquiry
WHERE unitprice <= 4933;
-- 임직원과 매출조회 새 테이블
create table Es(
e_id int,
s_num int,
day date,
primary key(`e_id`,`s_num`),
foreign key(`e_id`) references `EmployeeRegistration`(`id`),
foreign key(`s_num`) references `Salesinquiry`(`num`)
);
-- 정보 데이터 확인하는 곳
select * from Es;
-- 임직원, 매출조회 데이터 (이테이블 단점은 하나하나 확인하면서 테이블 정상적으로 들어갔는 지 확인불가?)
INSERT INTO Es (e_id, s_num, day)
VALUES (2 , 2, '2023-10-19'),
(3 , 5, '2023-10-18'),
(6 , 1, '2023-10-17'),
(3 , 2, '2023-10-15'),
(6 , 9, '2023-10-10'),
(10 , 3, '2023-10-12'),
(8 , 7, '2023-10-15'),
(9 , 4, '2023-10-19'),
(7 , 8, '2023-10-19'),
(8 , 6, '2023-10-18');
-- 매입발주 (거래처 정보)
CREATE TABLE purchaseorder(
num INT NOT NULL AUTO_INCREMENT,
Order_date DATE,
Order_item VARCHAR(20),
Orderer VARCHAR(30),
Order_quantity INT,
Order_unitprice INT,
Order_finalamount INT,
Order_finalinventory INT,
primary key(num),
c_id int,
foreign key (`c_id`) references `CustomerInformation`(`id`)
);
-- 정보 데이터 확인하는 곳
select * from purchaseorder;
-- 매입발주 데이터
INSERT INTO purchaseorder(Order_date, Order_item, Orderer, Order_quantity, Order_unitprice, Order_finalamount, Order_finalinventory, c_id)
VALUES ('2023-9-1','닭','생생농장',350,4990,1746500,50,1);
INSERT INTO purchaseorder(Order_date, Order_item, Orderer, Order_quantity, Order_unitprice, Order_finalamount, Order_finalinventory, c_id)
VALUES ('2023-9-5','닭','태백산',900,4920,3546900,50,2);
INSERT INTO purchaseorder(Order_date, Order_item, Orderer, Order_quantity, Order_unitprice, Order_finalamount, Order_finalinventory, c_id)
VALUES ('2023-9-1','닭','거봉',600,3950,1948090,50,3);
INSERT INTO purchaseorder(Order_date, Order_item, Orderer, Order_quantity, Order_unitprice, Order_finalamount, Order_finalinventory, c_id)
VALUES ('2023-9-5','닭','생생농장',600,3950,2048540,50,4);
INSERT INTO purchaseorder(Order_date, Order_item, Orderer, Order_quantity, Order_unitprice, Order_finalamount, Order_finalinventory, c_id)
VALUES ('2023-9-7','닭','꽁알꽁알농장',500,4998,2386600,50,5);
INSERT INTO purchaseorder(Order_date, Order_item, Orderer, Order_quantity, Order_unitprice, Order_finalamount, Order_finalinventory, c_id)
VALUES ('2023-9-3','돼지족','피피',600,4960,1767550,50,6);
INSERT INTO purchaseorder(Order_date, Order_item, Orderer, Order_quantity, Order_unitprice, Order_finalamount, Order_finalinventory, c_id)
VALUES ('2023-9-9','돼지족','SBS',900,4980,3546500,50,7);
INSERT INTO purchaseorder(Order_date, Order_item, Orderer, Order_quantity, Order_unitprice, Order_finalamount, Order_finalinventory, c_id)
VALUES ('2023-9-4','돼지족','PIG',200,4500,946500,50,8);
INSERT INTO purchaseorder(Order_date, Order_item, Orderer, Order_quantity, Order_unitprice, Order_finalamount, Order_finalinventory, c_id)
VALUES ('2023-9-1','돼지족','BIGpig',500,3600,1746500,50,9);
INSERT INTO purchaseorder(Order_date, Order_item, Orderer, Order_quantity, Order_unitprice, Order_finalamount, Order_finalinventory, c_id)
VALUES ('2023-9-2','돼지족','HIHI',600,5000,3746500,50,10);
select TRIM(TRAILING '족' FROM Order_item) as "아이템", Order_quantity
FROM purchaseorder
WHERE Order_item in ('돼지족');
-- 동그라미로 출력해보기
SELECT Order_item, Order_finalamount, LPAD('●', round(Order_finalamount/1000000), '●') as 민지1
from purchaseorder;
-- 두번째 자리를 *로 출력
SELECT REPLACE(Order_item, SUBSTR(Order_item,2,1),'*') as "아이템"
FROM purchaseorder
WHERE Order_item in ('돼지족');
-- 특정 철자를 N개 만큼 채우기
SELECT Order_item, LPAD(Order_finalamount,10,'@') AS 민지1, rpad(Order_finalamount,10,'@') AS 민지2
FROM purchaseorder;
-- 문자에서 특정 철자의 위치 출력하기
SELECT INSTR('돼지족','족')
FROM DUAL;
SELECT INSTR('abcdefghkij@naver.com','@')
FROM DUAL;
-- 숫자 0을 *로 출력하기alter
SELECT Order_item, REPLACE(Order_finalamount, 0, '#')
FROM purchaseorder;
-- 문자열의 길이를 출력하기
SELECT Orderer, length(Orderer)
FROM purchaseorder;
-- 문자에서 특정 철자 추출하기
SELECT SUBSTR('김민지',1,2)
FROM DUAL;
-- 소문자로 변환하기
SELECT UCASE(Orderer)
FROM purchaseorder
LIMIT 0, 1000;
-- 소문자로 변환하기
SELECT UPPER(SUBSTR(Orderer,1,1))
FROM purchaseorder;
-- 중복된 데이터를 제거해서 출력하기(distinct)
select distinct Order_date
from purchaseorder;
-- 9월10월날짜에 동일한 값을 총합할 때? 사용하면 좋은듯!
SElECT Order_date, Order_quantity *(600+5000) as 매입금액
FROM purchaseorder;
-- 산술연산자배우기
SELECT Order_item, Order_quantity * 12 as 1년매출, Order_unitprice
FROM purchaseorder
Where Order_quantity*12 >= 6000;
-- 컬럼 + 컬럼 값을 출력
SELECT Order_item, Order_quantity, Order_unitprice, Order_quantity + Order_unitprice
FROM purchaseorder
Where Order_item = '돼지족';
-- 비교 연산자 배우기(in)
SELECT Order_date, Order_item
FROM purchaseorder
WHERE Order_item in('돼지족','닭');
-- 날짜 사이에 판 닭의 매출을 보고싶다 근데 또 매출이 19000이하인 것만 보고싶다.
select Order_date as '날짜', Order_item as '아이템', Order_finalamount as '매출'
from purchaseorder
where Order_date between '2023-9-1' and '2023-9-5' and Order_item in ('닭') and Order_finalamount <= 2000000
ORDER BY 매출 asc;
-- 임직원과 매입발주 새 테이블
create table Ep(
e_id int,
p_num int,
day date,
primary key(`e_id`, `p_num`),
foreign key(`e_id`) references `EmployeeRegistration`(`id`),
foreign key(`p_num`) references `purchaseorder`(`num`)
);
-- 테이블 안에 새로운 컬럼 추가
ALTER TABLE ep
ADD Checklist VARCHAR(20);
ALTER TABLE ep
ADD relay VARCHAR(20);
-- 정보 데이터 확인하는 곳
select * from ep;
-- 임직원과 매입발주 데이터
INSERT INTO Ep(e_id, p_num, day, Checklist, relay)
VALUES (2,2,'2023-10-05','Y','Y');
INSERT INTO Ep(e_id, p_num, day, Checklist, relay)
VALUES (3,3,'2023-10-07','Y','Y');
INSERT INTO Ep(e_id, p_num, day, Checklist, relay)
VALUES (4,4,'2023-10-10','N','Y');
INSERT INTO Ep(e_id, p_num, day, Checklist, relay)
VALUES (5,5,'2023-10-11','N','N');
INSERT INTO Ep(e_id, p_num, day, Checklist, relay)
VALUES (6,6,'2023-10-3','Y','N');
INSERT INTO Ep(e_id, p_num, day, Checklist, relay)
VALUES (7,7,'2023-10-13','Y','Y');
INSERT INTO Ep(e_id, p_num, day, Checklist, relay)
VALUES (8,8,'2023-10-01','Y','N');
INSERT INTO Ep(e_id, p_num, day, Checklist, relay)
VALUES (9,9,'2023-10-15','N','Y');
INSERT INTO Ep(e_id, p_num, day, Checklist, relay)
VALUES (10,10,'2023-10-14','N','Y');
-- 비교 연산자 배우기 (BETWEEN AND)
SELECT e_id, p_num, day
from Ep
WHERE day BETWEEN '2023-10-13' AND '2023-10-14';
-- 비교 연산자 배우기 (NOT BETWEEN AND)
SELECT e_id, p_num, day
from Ep
WHERE day NOT BETWEEN '2023-10-13' AND '2023-10-14';
-- 수금관리(거래처정보, 매입발주, 임직원 정보)
CREATE TABLE Collectionmanagement(
num INT NOT NULL AUTO_INCREMENT,
MGMT_Customerinformation VARCHAR(20),
MGMT_salestuff VARCHAR(20),
MGMT_Expecteddepositdate VARCHAR(20),
MGMT_Actualdeposit DATE,
MGMT_Incorrectdepositmanagement DATE,
MGMT_Salesincharge INT,
primary key(num),
c_id int,
p_num int,
e_id int,
foreign key(`c_id`) references `CustomerInformation`(`id`),
foreign key(`p_num`) references `purchaseorder`(`num`),
foreign key(`e_id`) references `EmployeeRegistration`(`id`)
);
-- 정보 데이터 확인하는 곳
select * from Collectionmanagement;
-- 수금관리 데이터
INSERT INTO Collectionmanagement(MGMT_Customerinformation, MGMT_salestuff, MGMT_Expecteddepositdate, MGMT_Actualdeposit, MGMT_Incorrectdepositmanagement,MGMT_Salesincharge,c_id, p_num,e_id)
VALUES ('신한은행','식품유통','8845550','2023-09-20','2023-09-21',560,2,3,5);
INSERT INTO Collectionmanagement(MGMT_Customerinformation, MGMT_salestuff, MGMT_Expecteddepositdate, MGMT_Actualdeposit, MGMT_Incorrectdepositmanagement,MGMT_Salesincharge,c_id, p_num,e_id)
VALUES ('농협은행','식품유통','95656110','2023-09-24','2023-09-25',412,3,3,9);
INSERT INTO Collectionmanagement(MGMT_Customerinformation, MGMT_salestuff, MGMT_Expecteddepositdate, MGMT_Actualdeposit, MGMT_Incorrectdepositmanagement,MGMT_Salesincharge,c_id, p_num,e_id)
VALUES ('하나은행','식품유통','73445550','2023-09-23','2023-09-24',522,2,9,5);
INSERT INTO Collectionmanagement(MGMT_Customerinformation, MGMT_salestuff, MGMT_Expecteddepositdate, MGMT_Actualdeposit, MGMT_Incorrectdepositmanagement,MGMT_Salesincharge,c_id, p_num,e_id)
VALUES ('카카오뱅크','식품유통','9556284','2023-09-22','2023-09-23',604,10,3,7);
INSERT INTO Collectionmanagement(MGMT_Customerinformation, MGMT_salestuff, MGMT_Expecteddepositdate, MGMT_Actualdeposit, MGMT_Incorrectdepositmanagement,MGMT_Salesincharge,c_id, p_num,e_id)
VALUES ('신한은행','식품유통','7832610','2023-09-26','2023-09-25',755,7,8,6);
INSERT INTO Collectionmanagement(MGMT_Customerinformation, MGMT_salestuff, MGMT_Expecteddepositdate, MGMT_Actualdeposit, MGMT_Incorrectdepositmanagement,MGMT_Salesincharge,c_id, p_num,e_id)
VALUES ('하나은행','식품유통','6767558','2023-09-27','2023-09-26',852,8,6,2);
INSERT INTO Collectionmanagement(MGMT_Customerinformation, MGMT_salestuff, MGMT_Expecteddepositdate, MGMT_Actualdeposit, MGMT_Incorrectdepositmanagement,MGMT_Salesincharge,c_id, p_num,e_id)
VALUES ('국민은행','식품유통','3697865','2023-09-28','2023-09-27',900,5,4,1);
INSERT INTO Collectionmanagement(MGMT_Customerinformation, MGMT_salestuff, MGMT_Expecteddepositdate, MGMT_Actualdeposit, MGMT_Incorrectdepositmanagement,MGMT_Salesincharge,c_id, p_num,e_id)
VALUES ('카카오뱅크','식품유통','8569874','2023-09-29','2023-09-29',542,7,6,6);
INSERT INTO Collectionmanagement(MGMT_Customerinformation, MGMT_salestuff, MGMT_Expecteddepositdate, MGMT_Actualdeposit, MGMT_Incorrectdepositmanagement,MGMT_Salesincharge,c_id, p_num,e_id)
VALUES ('현대카드','식품유통','5869744','2023-09-30','2023-09-30',423,3,4,4);
INSERT INTO Collectionmanagement(MGMT_Customerinformation, MGMT_salestuff, MGMT_Expecteddepositdate, MGMT_Actualdeposit, MGMT_Incorrectdepositmanagement,MGMT_Salesincharge,c_id, p_num,e_id)
VALUES ('삼성카드','식품유통','5869745','2023-09-22','2023-09-22',152,4,3,6);
INSERT INTO Collectionmanagement(MGMT_Customerinformation, MGMT_salestuff, MGMT_Expecteddepositdate, MGMT_Actualdeposit, MGMT_Incorrectdepositmanagement,MGMT_Salesincharge,c_id, p_num,e_id)
VALUES ('신한은행','식품유통','68745789','2023-09-27','2023-09-27',289,5,8,6);
-- WHERE절 문자 검색
SELECT MGMT_Customerinformation, MGMT_salestuff, MGMT_Expecteddepositdate
FROM Collectionmanagement
WHERE MGMT_Customerinformation = '신한은행';
-- WHERE절 날짜 검색
SELECT MGMT_Customerinformation, MGMT_salestuff, MGMT_Expecteddepositdate,MGMT_Actualdeposit
FROM Collectionmanagement
WHERE MGMT_Actualdeposit = '2023-09-22';
-- 비교연산자 배우기 in
select MGMT_Customerinformation as 은행, MGMT_Expecteddepositdate as 입금
FROM Collectionmanagement
WHERE MGMT_Expecteddepositdate between 6767558 AND 73445550 and MGMT_Customerinformation in ('신한은행');
-- 매출조회와 수금관리의 새 테이블
create table SC(
s_num int,
c_num int,
day date,
foreign key(`s_num`) references `Salesinquiry`(`num`),
foreign key(`c_num`) references `Collectionmanagement`(`num`)
);
-- 정보 데이터 확인하는 곳
select * from SC;
-- 매출조회와 수금관리 데이터
INSERT INTO SC(s_num, c_num, day)
VALUES (1,1,'2023-10-01');
INSERT INTO SC(s_num, c_num, day)
VALUES (3,1,'2023-10-04');
INSERT INTO SC(s_num, c_num, day)
VALUES (2,8,'2023-10-06');
INSERT INTO SC(s_num, c_num, day)
VALUES (4,5,'2023-10-08');
INSERT INTO SC(s_num, c_num, day)
VALUES (2,10,'2023-10-02');
INSERT INTO SC(s_num, c_num, day)
VALUES (9,3,'2023-10-04');
INSERT INTO SC(s_num, c_num, day)
VALUES (2,8,'2023-10-07');
INSERT INTO SC(s_num, c_num, day)
VALUES (5,5,'2023-10-08');
INSERT INTO SC(s_num, c_num, day)
VALUES (6,7,'2023-10-09');
INSERT INTO SC(s_num, c_num, day)
VALUES (8,3,'2023-10-10');
-- 정보 데이터 확인하는 곳
select * from EmployeeRegistration;
-- 데이터 삭제하는 곳
DELETE FROM EmployeeRegistration
WHERE id=1;
