[SQL] SQL문

옛날 프로젝트 했던걸 기반으로 예시 SQL문 짱박아놓기

 

//선배가 준 예시본
--관리자 추가
INSERT INTO MANAGER VALUES('manager', '000');

--엘레베이터 정보 추가
INSERT INTO ELEVATOR_INFO VALUES('00A', 800, 6);
INSERT INTO ELEVATOR_INFO VALUES('00B', 900, 8);
INSERT INTO ELEVATOR_INFO VALUES('00C', 1000, 7);

--엘레베이터 추가
INSERT INTO ELEVATOR VALUES('00a', '00A', '000');
INSERT INTO ELEVATOR VALUES('00b', '00B', '001');
INSERT INTO ELEVATOR VALUES('00c', '00C', '002');

--이력 추가
INSERT INTO HISTORY VALUES('00_0a', '00a', SYSDATE);
INSERT INTO HISTORY VALUES('00_0b', '00b', SYSDATE);
INSERT INTO HISTORY VALUES('00_0c', '00c', SYSDATE);

--점검 이력 추가
INSERT INTO CHECK_HISTORY VALUES('00_0a', '이상 없음');
INSERT INTO CHECK_HISTORY VALUES('00_0c', '이상 없음');

--고장 이력 추가
INSERT INTO FAILURE_HISTORY VALUES('00_0a', '하중 센서 이상');
INSERT INTO FAILURE_HISTORY VALUES('00_0b', '속도 이상');
INSERT INTO FAILURE_HISTORY VALUES('00_0c', '문 이상');

--수리 이력 추가
INSERT INTO REPAIR_HISTORY VALUES('00_0a', '하중 센서 수리');
INSERT INTO REPAIR_HISTORY VALUES('00_0b', '속도 센서 교체');
INSERT INTO REPAIR_HISTORY VALUES('00_0c', '문 수리');

------------------------------------------------------------------------------
알아간것들

// INSERT INTO 클래스명 VALUES ('자료','자료');

//컬럼 삭제
ALTER TABLE APT  //ALTER TABLE 테이블 이름
DROP COLUMN APT_floor;  // DROP COLUMN 컬럼이름;

//컬럼 타입 변경
ALTER TABLE APT  //ALTER TABLE 테이블이름
MODIFY(APT_floor INTEGER); //MODIFY(컬럼이름 타입);

//컬럼추가
ALTER TABLE HISTORY //ALTER TABLE 테이블이름
ADD(APT_floor INTEGER); //ADD(컬럼이름 타입);

//날짜(DATE) 추가
//TO_DATE(숫자 , YYYYMMDD)
//만약 안에 하이픈이라던가 띄어쓰기 있으면 문장형으로 숫자가 아닌 '숫자-숫자'등으로 쓴다
TO_DATE(20140101,'YYYYMMDD')
TO_DATE('2014-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')

//DATE 가져올때

SELET TO_CHAR (HISTORY, 'YYYY-MM-DD HH24:MI:SS)

//SQL 문 Join

select 열 목록 (칼럼) from 첫 번째 테이블 INNER JOIN 두 번째 테이블 ON 조인될 조건 where 검색 조건

select history_date,check_contents from history h inner join check_history c on h.HISTORY_CODE=c.HISTORY_CODE where
elevator_code = 'E1_01'

//원하는 데이터 수정할때 (update)

update 테이블 명 set 새 데이터명 = ' ' where 바꿀 데이터명 = ' '

update telephone_dic set phone = '010-1534-5668' where phone = '010-1234-1234'

//원하는 데이터 삭제할때 (delete)

delete from 테이블명 where 지우고자하는데이터명 = ' '

delete from telephone_dic where phone = '"+textBox1.Text+"'
---------------------------------------------------------------------------------
데이터 저장용(관리자는 없음)

--APT

INSERT INTO APT VALUES('A1',1);
INSERT INTO APT VALUES('A2',2);

--HOUSEHOLD

INSERT INTO HOUSEHOLD VALUES('F1_101',101,'A1');
INSERT INTO HOUSEHOLD VALUES('F1_102',102,'A1');
INSERT INTO HOUSEHOLD VALUES('F1_103',103,'A1');
INSERT INTO HOUSEHOLD VALUES('F1_104',104,'A1');
INSERT INTO HOUSEHOLD VALUES('F1_105',105,'A1');
INSERT INTO HOUSEHOLD VALUES('F1_106',106,'A1');
INSERT INTO HOUSEHOLD VALUES('F1_107',107,'A1');
INSERT INTO HOUSEHOLD VALUES('F1_108',108,'A1');
INSERT INTO HOUSEHOLD VALUES('F2_101',101,'A2');
INSERT INTO HOUSEHOLD VALUES('F2_102',102,'A2');
INSERT INTO HOUSEHOLD VALUES('F2_103',103,'A2');
INSERT INTO HOUSEHOLD VALUES('F2_104',104,'A2');
INSERT INTO HOUSEHOLD VALUES('F2_105',105,'A2');
INSERT INTO HOUSEHOLD VALUES('F2_106',106,'A2');
INSERT INTO HOUSEHOLD VALUES('F2_107',107,'A2');
INSERT INTO HOUSEHOLD VALUES('F2_108',108,'A2');

INSERT INTO HOUSEHOLD VALUES('F1_201',201,'A1');
INSERT INTO HOUSEHOLD VALUES('F1_202',202,'A1');
INSERT INTO HOUSEHOLD VALUES('F1_203',203,'A1');
INSERT INTO HOUSEHOLD VALUES('F1_204',204,'A1');
INSERT INTO HOUSEHOLD VALUES('F1_205',205,'A1');
INSERT INTO HOUSEHOLD VALUES('F1_206',206,'A1');
INSERT INTO HOUSEHOLD VALUES('F1_207',207,'A1');
INSERT INTO HOUSEHOLD VALUES('F1_208',208,'A1');

 

--Telephone_Dic

INSERT INTO TELEPHONE_DIC VALUES('010-0001-0101','F1_101');
INSERT INTO TELEPHONE_DIC VALUES('010-0001-0102','F1_102');
INSERT INTO TELEPHONE_DIC VALUES('010-0001-0103','F1_103');
INSERT INTO TELEPHONE_DIC VALUES('010-0001-0104','F1_104');
INSERT INTO TELEPHONE_DIC VALUES('010-0001-0105','F1_105');
INSERT INTO TELEPHONE_DIC VALUES('010-0001-0106','F1_106');
INSERT INTO TELEPHONE_DIC VALUES('010-0001-0107','F1_107');
INSERT INTO TELEPHONE_DIC VALUES('010-0001-0108','F1_108');
INSERT INTO TELEPHONE_DIC VALUES('010-0002-0101','F2_101');
INSERT INTO TELEPHONE_DIC VALUES('010-0002-0102','F2_102');
INSERT INTO TELEPHONE_DIC VALUES('010-0002-0103','F2_103');
INSERT INTO TELEPHONE_DIC VALUES('010-0002-0104','F2_104');
INSERT INTO TELEPHONE_DIC VALUES('010-0002-0105','F2_105');
INSERT INTO TELEPHONE_DIC VALUES('010-0002-0106','F2_106');
INSERT INTO TELEPHONE_DIC VALUES('010-0002-0107','F2_107');
INSERT INTO TELEPHONE_DIC VALUES('010-0002-0108','F2_108');

--ELEVATOR_INFO

INSERT INTO ELEVATOR_INFO VALUES('1',1010,10);
INSERT INTO ELEVATOR_INFO VALUES('2',1020,20);
INSERT INTO ELEVATOR_INFO VALUES('3',1030,30);
INSERT INTO ELEVATOR_INFO VALUES('4',1040,40);

--Elevator

INSERT INTO ELEVATOR VALUES('E1_01','1','A1');
INSERT INTO ELEVATOR VALUES('E1_02','2','A1');
INSERT INTO ELEVATOR VALUES('E1_03','3','A1');
INSERT INTO ELEVATOR VALUES('E1_04','4','A1');
INSERT INTO ELEVATOR VALUES('E2_01','1','A2');
INSERT INTO ELEVATOR VALUES('E2_02','2','A2');
INSERT INTO ELEVATOR VALUES('E2_03','3','A2');
INSERT INTO ELEVATOR VALUES('E2_04','4','A2');

--HISTORY

INSERT INTO HISTORY VALUES('H001','E1_01',TO_DATE('2014-11-20 09:52:17','YYYY-MM-DD HH24:MI:SS'),2);

--CHECK_HISTORY

INSERT INTO CHECK_HISTORY VALUES('H001','점검 완료');

--REPAIR_HISTORY

INSERT INTO REPAIR_HISTORY VALUES('H001','수리하였음');

--FAILURE_HISTORY

INSERT INTO FAILURE_HISTORY VALUES('H001','고장났음');

--CALL_HISTORY

INSERT INTO CALL_HISTORY VALUES('H001',TO_DATE('2014-11-20 10:06:26','YYYY-MM-DD HH24:MI:SS'),TO_DATE('2014-11-20 10:07:06','YYYY-MM-DD HH24:MI:SS'));