[SQL] INSERT , UPDATE , DELETE (작성중)

 

일단 나보려고 만든거라 정리는 나중에../😂

--다중 insert
INSERT INTO departments(DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID)
SELECT 300,'Advertising11',100,1700 FROM DUAL
UNION ALL
SELECT  310,'Sales Support11 ',100,1700 FROM DUAL;

Insert all 
    Into departments (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) values( 280,'Advertising',100,1700)
    Into departments (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) values(  290,'Sales Support ',100,1700)
    select * from dual;

 

위의 방법이 더 성능이 좋다고 한다.

 

SELECT * FROM countries;

INSERT INTO countries
VALUES ('KR', 'South Korea', 3);

INSERT INTO countries (country_id, country_name, region_id)
VALUES ('KP', 'North Korea', 3);

SELECT * FROM departments;

INSERT INTO departments
VALUES (280, 'Testing', 200, 1700);

INSERT INTO departments (department_id, department_name,location_id)
VALUES (290, 'Data Analytics', 1700);

SELECT * FROM jobs;
INSERT INTO jobs
VALUES ('IT DS', 'Data Scientist', 10000, 20000);
INSERT INTO jobs (job_id, job_title, min_salary, max_salary)
VALUES ('IT DA', 'Data Analyst', 10000, 20000);

----업데이트
--UPDATE 테이블
--SET 열=값, 열=값,..
--WHERE 조건식
UPDATE countries
SET country_name = 'Democratic People''s Republic of Korea' --이때  ''을 써주면 '안의 '값이 입력된다.
WHERE country_id = 'KP'; -- where을 써주지않으면 전체가  update 되는점 주의

UPDATE countries
SET country_name = 'Republic of Korea'
WHERE country_id = 'KR';

----삭제
--DELETE (FROM) 테이블
--WHERE 조건식
----조건식 사용 필수, FROM은 생략가능.

DELETE
FROM countries
WHERE country_id = 'KP';


DELETE
FROM departments
WHERE department_id = 280;

DELETE
FROM jobs
WHERE job_id = 'IT DA';

--다중 삭제
DELETE FROM countries 
WHERE COUNTRY_ID IN ('AT','NO','ID','TR');