[코리아IT아카데미] SQL 문법 MariaDB (INSERT, 백틱(backtick), INSERT SELECT, CREATE SELECT, DELETE, 삭제안전장치, TRUNCATE, UPDATE, 연습문제)

1. INSERT

 - 기본형식 : INSERT INTO 테이블 (필드목록) VALUES (값목록)

 - 마리아 디비는 INTO 생략을 허용하지만 SQL 표준에는 INTO가 필수

 - INTO 다음에 새 레코드를 삽입할 테이블 이름을 적음, 콤마로 구분 나열, VALUES절의 값 목록 대입 값 나열

 - 자리에 맞춰 VALUES 값을 작성하지 않았을 시, 오류

 - 필드명을 명시했을 시, 자리가 변경되어 VALUES 값 작성 가능

# 서울 레코드 삽입 쿼리문
INSERT INTO tcity (NAME, AREA, metro, region) VALUES ('서울', 605, 974, 'y', '경기');

# 평택 데이터 추가 쿼리문
INSERT INTO tcity VALUES ('평택', 453, 51, 'n', '경기');

 

2. 백틱 

 - 필드명, 테이블명만 백틱(`) 사용 

 - 제외 문자열은 모두 작은따옴표(') 사용

 

3. 연습문제 1

# 1. 도시목록에 용인을 삽입하라. 면적 293에 인구 98만이되, 데이터 임의 값 가능
INSERT INTO tcity VALUES ('용인', 293, 98, 'y', '경기');
# 2. 직원 목록에 자신의 신상을 삽입해보라.
INSERT INTO tstaff VALUES ('진정모', '개발부' , '남', 20240206, '대리', 500, 98.5);

 

4. 확장 INSERT문

 - 초기화 명령어 : TRUNCATE TABLE tCity;

 - 각 행마다 개별적으로 INSERT INTO 명령을 일일이 작성하면 쿼리문이 길어 번잡스러움

# 확장 INSERT문
INSERT INTO tcity (NAME, AREA, popu, metro, region) VALUES
('서울', 605, 974, 'y', '경기'),
('부산', 765, 342, 'y', '경상'),
('오산', 42, 21, 'n', '경기'),
('청주', 940, 83, 'n', '충청'),
('전주', 205, 65, 'n', '전라'),
('순천', 910, 27, 'n', '전라'),
('춘천', 1116, 27, 'n', '강원'),
('홍천', 1819, 7, 'n', '강원');

- 대량의 데이터를 삽입할 때는 더 편하지만, 속도는 개별보다 느림. 표준 SQL은 아니고 오라클에서 지원하지 않음.

# 도시 목록에 이천(461 킬로미터, 21만)과 대구(883 킬로미터, 248만), 영월(1127킬로미터, 4만)을 삽입하라
INSERT INTO tcity (NAME, AREA, popu, metro, region) VALUES
('이천', 461, 21, 'y', '경기'),
('대구', 883, 248, 'y', '경북'),
('영월', 1127, 4, 'y', '경기');

 

5. 연습문제 2

# 고객 테이블에 ('strawberry', '최유경', 30, 'vip', '공무원', 100) 정보 삽입
INSERT INTO `고객` VALUE ('strawberry', '최유경', 30, 'vip', '공무원', 100);
# 고객 테이블에 ('tomaro', '정은심', 36, 'gold', NULL, 4800) 정보 삽입
INSERT INTO `고객`(`고객아이디`, `고객이름`, `나이`, `등급`, `적립금`) VALUES ('tomaro', '정은심', 36, 'gold', 4800);

 

6. INSERT SELECT 

 - INSERT 명령은 한번에 하나의 레코드만 삽입하지만, 다른 테이블 또는 자기 자신에게 이미 저장된 대량 정보를 

   복사 할 때는, 조회한 결과셋을 한꺼번에 삽입할 수 있음

 - 기본형 : INSERT INTO 대상테이블 (필드목록) SELECT 필드목록 FROM 원본테이블

 - INSERT INTO 와 기본형식은 비슷하지만, 필드의 값을 VALUES 절로 지정하지 않고 SELECT 명령으로 다른 테이블을

   읽어온다는 점이 다름. 별도의 문법이라기보다 INSERT INTO에 SELECT 명령이 포함된 형식

INSERT INTO tstaff (NAME, depart, gender, joindate, grade, salary, score) 
SELECT NAME, region, metro, '20210629', '신입', AREA, popu FROM tcity WHERE region = '경기';

 

7. CREATE SELECT

 - INSERT SELECT 와 유사하되 기존 테이블에 레코드를 삽입하는 것이 아니라 새로 테이블을 만들어 결과셋을 삽입

 - 기본형식 : CREATE TABLE 대상테이블 AS SELECT 필드목록 FROM 원본테이블

 - 대상 테이블은 존재하면 안됨. 원본 손상시키지 않고 특수 작업을 할 때 사본이 좋아서 CREATE SELECT 명령 유용

# 경기도 지역의 도시만으로 tSudo 테이블을 생성
CREATE TABLE tSudo AS SELECT NAME, AREA, popu FROM tcity WHERE region = '경기'

# 대량의 변경을 가해야 할 때, 다음 명령으로 복사
# 데이터 백업은 가능하나, 별도 키 설정은 복사 안됨
CREATE TABLE tStaff_backup_240206 AS SELECT * FROM tstaff;

 

8. 연습문제 3 

# 1. 성취도가 80점 이상인 직원만 골라 이름과 월급에 대한 보고서를 별도의 테이블로 작성하라
CREATE TABLE practice01 AS SELECT NAME, salary FROM tstaff WHERE score >= 80;

 

9. DELETE

 - 레코드를 삭제할 때 사용

 - 삭제를 항상 레코드 단위로 수행하므로 필드에 대한 지정은 없음

 - 기본형식 : DELETE FROM 테이블 WHERE 조건; 

# 도시명이 '부산'인 레코드를 삭제
DELETE FROM tcity WHERE NAME = '부산';
# 경기도에 있는 모든 도시를 삭제
DELETE FROM tcity WHERE region = '경기';
# 영업부 직원 모두 해고
DELETE FROM tstaff WHERE depart = '영업부';

 

10. 삭제 안전 장치

 - 삭제 시 문제가 되는 부분 1) 조건절을 빼거나 2) 잘못된 조건을 사용하는 경우

 - 잘못 내린 DELETE 명령은 치명적, 명령 즉시 처리해 버림

 - 실수로 WHERE 절을 생략해버리면 모든 데이터가 삭제가 됨.

 

- (좋은 방법) DELETE 명령을 내리기 전에 먼저 SELECT로 삭제 대상 레코드를 고름

# 먼저 SELECT로 삭제 대상 레코드를 고름
SELECT * FROM tCity WHERE popu > 50;
# 조건이 맞는 지 확인  후 제거
DELETE FROM tCity WHERE popu > 50;

 

11. TRUNCATE

 - WHERE 절이 없는 DELETE 명령은 상당히 위험하지만 테이블을 비울 목적이라면 사용 가능

 - 하지만 하나씩 지우게 되면 서버에도 부하가 생김

 - 이 경우에는 다음 명령이 효율적 > TRUNCATE TABLE 테이블

 - 이 명령은 테이블을 완전히 비움, 물리적인 기억 장소를 깨끗이 비우고 임시 영역에 로그도 남기지 않아 훨씬 빠름

 

12. UPDATE 

 - 레코드의 필드 값을 변경할 때 사용, 대입문으로 테이블의 필드 값을 임의대로 변경.

 - 기본형식 : UPDATE 테이블 SET 필드 = 값 [, 필드 = 값 ] WHERE 조건

 - UPDATE 키워드 다음에 변경 대상 테이블을 지정

 - SET 키워드 다음에 필드에 값을 대입한느 대입문이 오며 콤마로 끊어 복수 개의 필드를 한꺼번에 변경

 - WHERE 절에는 변경 대상 레코드를 지정하는 조건문을 작성

# 순천의 인구를 1000만명으로, 지역을 충청도로 변경
UPDATE tcity SET popu = 1000, region = '충청' WHERE NAME = '순천';

# 만일 실수로 WHERE 절을 뺄 수 있는데, 이 경우 모든 데이터가 변경한느 사고가 발생
UPDATE tcity SET popu = 1000, region = '충청';

# popu의 원래 값을 읽어 그 두배 값을 다시 popu에 대입하는 쿼리문
UPDATE tcity SET popu = popu * 2 WHERE NAME = '홍천';

 

13. 연습문제 4

# 1. 여자 사원급을 모두 대리로 진급시켜라
# SELECT 문으로 데이터 검증 (WHERE 조건문 복수 일 시 AND 사용)
SELECT * from tstaff WHERE grade = '사원' AND gender = '여';
# UPDATE로 데이터 변경
UPDATE tstaff SET grade = '대리' WHERE grade = '사원' AND gender = '여';
# 2. 개발부 직원의 월급을 10%씩 인상하라
UPDATE tstaff SET salary = salary * 1.1 WHERE depart = '개발부';

 

14. 연습문제 5

# 1. 제품 테이블에서 제품번호가 p03인 제품의 제품명을 통큰파이로 수정해보자.
UPDATE `제품` SET `제품명` = '통큰파이' WHERE `제품번호` = 'p03';
# 2.제품테이블에 있는 모든 제품의 단가를 10% 인상해보자.
UPDATE `제품` SET `단가` = `단가` * 1.1;
# 3. 정소화 고객이 주문한 제품의 주문 수량을 5개로 수정해보자.
UPDATE `주문` SET `수량` = 5 WHERE `주문고객` = 'apple';