16장. 테이블의 변경 새로운 행 삽입 테이블에서 테이블로 행을 복사 행 값의 변경 테이블에서 행 삭제

Presentation on theme: "16장. 테이블의 변경 새로운 행 삽입 테이블에서 테이블로 행을 복사 행 값의 변경 테이블에서 행 삭제"— Presentation transcript:

1 16장. 테이블의 변경 새로운 행 삽입 테이블에서 테이블로 행을 복사 행 값의 변경 테이블에서 행 삭제
쉽게 배우는 MySQL 5.x

2 16.1 새로운 행 삽입 -INSERT 명령문은 테이블에 새로운 행을 삽입하기 위해서 사용
-첫 번째 형식은 오직 새로운 행만 삽입 -두 번째 형식은 다른 테이블로부터 필요한 행을 가져와 테이블에 삽입 -꼭 필요한 데이터(NOT NULL로 지정된 값 포함) 만을 선택하여 입력가능 [예제 16-1] 교수코드 “6001”과 교수이름 “대조영”을 추가하라. (단, 데이터는 교수코드와 교수명만 입력한다.) mysql> insert into professor(prof_code, prof_name)     -> values('6001','대조영'); Query OK, 1 row affected (0.06 sec) 위의 예제는 INSERT 명령문에서 지정하지 않는 모든 열(PROF_ENAME, CREATE_DATE)은 NULL 값이 삽입된다. 만약 NULL이 리터럴로 지정되었다면 그 행에서 지정된 열은 NULL 값으로 채워진다. 쉽게 배우는 MySQL 5.x

3 [예제 16-2] 새로운 과목을 추가하라. 단, 데이터는 다음과 같다.
(‘5001', 'JSP프로그래밍', 'JSP Programming', '2006'); mysql> insert into subject values     -> (‘5001','JSP프로그래밍','JSP Programming',2006'); Query OK, 1 row affected (0.00 sec) -SUB_CODE, SUB_NAME 열은 반드시 데이터를 입력 -이유는 2개의 열은 NOT NULL로 지정되어 있기 때문 -NULL 대신에 문자열의 NULL을 나타내는 ‘ ’와 같이 입력하여도 무관 [예제 16-2]를 열의 순서와 상관없이 다음과 같이 입력하여도 동일 mysql> INSERT INTO SUBJECT     -> ( CREATE_YEAR, SUB_CODE, SUB_NAME, SUB_ENAME)     -> VALUES ('2006', ‘5001', 'JSP프로그래밍', 'JSP Programming'); 열의 이름을 지정하지 않았다면 결과는 완전히 다르다. SQL은 값 ‘2006’을 교과목코드로 간주하고, 값 ‘5001’를 교과목명으로 간주 쉽게 배우는 MySQL 5.x

4 16.2 테이블에서 테이블로 행을 복사 -새로운 행을 삽입하는 것이 아니라 각 행은 한 테이블에서 다른 테이블로 이동
16.2 테이블에서 테이블로 행을 복사 -새로운 행을 삽입하는 것이 아니라 각 행은 한 테이블에서 다른 테이블로 이동 -한 테이블에서 다른 테이블로 복사하는 것과 동일 [예제 16-3] 새로운 전라남도 순천시 향우회 테이블을 생성시켜 보자. 향우회테이블은 학생의 학번, 이름, 우편번호, 현주소, 전화번호로 구성되어 있다. (1) 먼저 새로운 테이블을 생성한다. mysql> create table hang(                       -> stu_no char(10) Not null,     -> stu_name char(10) Not null,     -> hyun_post_no varchar(7) Not null,     -> hyun_add varchar(100) Not null,     -> hyun_tel varchar(14),     -> primary key (stu_no)     -> ) type = innoDB; (2) STUDENT 테이블에 등록되어 있는 전라남도 순천시(현주소 우편번호 3자리가 540으로 시작하는)의 학생에 관한 정보를 HANG 테이블로 복사한다. mysql> insert into hang                                        -> (stu_no, stu_name, hyun_post_no, hyun_add, hyun_tel)     -> select stu_no, stu_name, post_no, address, tel     -> from student     -> where substring(post_no, 1, 3) = '540'; Query OK, 5 rows affected (0.03 sec) 쉽게 배우는 MySQL 5.x

5 (3) 다음은 전라남도 순천시 향우회의 내용을 확인하여 보자.
mysql> select * from hang; | stu_no   | stu_name | hyun_post_no | hyun_add                | hyun_tel     | | | 박도준   |       | 16-2번지                | 061) | | | 정인정   |       | 부영7차APT 304동 1210호 | 061) | | | 이순신   |       | 번지              | 061) | | | 김문영   |       | 380-2번지               | 061) | | | 최차영   |       | 금사리 1427             | 061) | 5 rows in set (0.00 sec) 다음의 insert문은 [예제 16-3]의 insert문과 동일한 결과 생성 INSERT INTO  HANG SELECT STU_NO, STU_NAME, POST_NO, ADDRESS, TEL FROM  STUDENT WHERE SUBSTRING(POST_NO, 1, 3) = '540'; INSERT INTO HANG (HYUN_ADD, HYUN_POST_NO, HYUN_TEL, STU_NAME, STU_NO) SELECT ADDRESS, POST_NO, TEL, STU_NAME, STU_NO FROM  STUDENT 쉽게 배우는 MySQL 5.x

6 INSERT 명령문에서 SELECT문 사용시 규칙
- SELECT 명령문은 새로운 행이 복사될 테이블을 참조하지 않는다(이러한 제한이 모든 SQL 제품에서 적용되는 것은 아니다). - 여기서 사용되는 SELECT 명령문은 완전히 동일한 SELECT 명령문이다. 따라서 부속 질의어, JOIN, GROUP BY등의 기능을 그대로 사용 INSERT INTO 절에 있는 열의 수는 SELECT 절에 있는 수식의 수와 동일 INSERT INTO 절에 있는 열의 자료형은 SELECT 절에 있는 수식의 자료형과 일치해야 한다. INSERT   INTO STUDENT SELECT   * FROM    STUDENT; 전라남도 순천시 지역 향우회가 되지 않고, 학적 테이블에 있는 모든 행을 복사하므로 오류 INSERT   INTO HANG FROM    STUDENT WHERE   SUBSTRING(POST_NO, 1, 3) = '540'; 참조할 테이블(STUDENT)의 열의 수와 실제로 복사할 테이블(HANG)의 열의 수가 서로 다르기 때문에 오류 쉽게 배우는 MySQL 5.x

7 [예제 16-4] 교수(PROFESSOR) 테이블에서 PROF_ENAME 열을 삭제하라.
(1) PROFESSOR 테이블과 동일한 열을 가지는 테이블을 생성하는데, 이 때 PROF_ENAME 열을 제외하고 새로운 “DUMMY”테이블을 생성한다. mysql> create table dummy     -> (prof_code char(4) not null,     -> prof_name char(10) not null,     -> create_date date,     -> primary key (prof_code)) type = innoDB; Query OK, 0 rows affected, 1 warning (0.13 sec) (2) PROFESSOR 테이블에서 DUMMY 테이블로 모든 행을 복사한다. mysql> insert into dummy     -> (prof_code, prof_name, create_date)     -> select prof_code, prof_name, create_date     -> from professor; Query OK, 12 rows affected (0.02 sec) Records: 12  Duplicates: 0  Warnings: 0 (3) PROFESSOR 테이블을 삭제하라. mysql> drop table professor; Query OK, 0 rows affected (0.06 sec) 쉽게 배우는 MySQL 5.x

8 (4) DUMMY 테이블의 구조와 동일한 PROFESSOR 테이블을 생성하라.
mysql> create table professor as select * from dummy; Query OK, 12 rows affected (0.08 sec) Records: 12  Duplicates: 0  Warnings: 0 (5) DUMMY 테이블을 삭제하라. mysql> drop table dummy; Query OK, 0 rows affected (0.02 sec) (6) PROFESSOR 테이블을 질의하라. mysql> select * from professor; | prof_code | prof_name | create_date | | 4001      | 정진용    |   | | 4002      | 나인섭    |   | | 4003      | 정창부    |   | | 4004      | 박상철    |   | | 4005      | 정병열    |   | | 4006      | 고진광    |   | | 4007      | 김영식    |   | | 4008      | 최우진    |   | | 4009      | 문창수    |   | | 5010      | 정종필    |   | | 5011      | 최종주    |   | | 6001      | 대조영    | NULL        | 12 rows in set (0.00 sec) 쉽게 배우는 MySQL 5.x

9 16.3 행 값의 변경 UPDATE 명령문은 테이블에 있는 값을 변경하기 위해서 사용
16.3 행 값의 변경 UPDATE 명령문은 테이블에 있는 값을 변경하기 위해서 사용 [예제 16-5] 자바프로그래밍의 생성년도를 2006년으로 갱신하라. mysql> update subject     -> set create_year = '2006'     -> where sub_name = '자바프로그래밍'; Query OK, 1 row affected (0.05 sec) Rows matched: 1  Changed: 1  Warnings: 0 과목명이 자바프로그래밍인 행에 대하여 CREATE_YEAR를 변경전의 값 ‘1999’을 ‘2006’으로 변경 [예제 16-6] 모든 등록금총액을 5% 증가시켜라. mysql> update fee     -> set fee_total = fee_total * 1.05; Query OK, 26 rows affected (0.08 sec) Rows matched: 26  Changed: 26  Warnings: 0 WHERE 절이 생략되었기 때문에 갱신은 테이블에서 관련된 모든 행에 대하여 수행 쉽게 배우는 MySQL 5.x

10 변경된 데이터를 확인하기 위해서 다음과 같은 SELECT문을 사용
mysql> select stu_no, fee_year, fee_term, fee_total     -> from fee; | stu_no   | fee_year | fee_term | fee_total | | | 2000     |        1 |   | | | 2000     |        2 |   | | | 2001     |        1 |   | | | 2001     |        2 |   | | | 2006     |        1 |   | | | 2006     |        2 |   | | | 2007     |        1 |   | | | 2007     |        2 |   | | | 2006     |        1 |   | | | 2006     |        2 |   | | | 2007     |        1 |   | | | 2007     |        2 |   | | | 2007     |        1 |   | | | 2007     |        2 |   | | | 2007     |        1 |   | | | 2007     |        2 |   | | | 2007     |        1 |   | | | 2007     |        2 |   | | | 2007     |        1 |   | | | 2007     |        2 |   | | | 2007     |        1 |   | | | 2007     |        2 |   | | | 2007     |        1 |   | | | 2007     |        2 |   | | | 2008     |        1 |   | | | 2008     |        1 |   | 26 rows in set (0.00 sec) 쉽게 배우는 MySQL 5.x

11 하나의 UPDATE 명령문으로 행에 있는 다수의 열을 갱신할 필요가 있는 경우
[예제16-9]김문영 학생 가족이 서울특별시 영등포구 신길1동 652-9번지 3통 5반으로 이사하였다. 신길1동의 우편번호는 이고, 전화번호 02) 번이다. 데이터를 변경하라. mysql> update student                         -> set address = '652-9번지 3통 5반',     ->     post_no = ' ',     ->     tel = '02) '     -> where stu_name = '김문영'; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0 하나의 UPDATE 명령문으로 행에 있는 다수의 열을 갱신할 필요가 있는 경우 -SET 절의 각 항목 간에는 콤마를 사용하여 다수의 열을 변경 -예제에서 ADDRESS, POST_NO, TEL 열을 변경하기 위하여 콤마를 사용 SQL은 UPDATE 명령문을 어떻게 수행하는가? -각 행에 대하여 SQL은 WHERE 절에 있는 조건이 참인지 조사 -참이면 관련된 행의 복사가 이루어지고, 변경될 각 행에 대하여 수식이 처리 -계산 또는 처리는 복사본의 열에서 수행 -수식의 결과는 원래의 행에 기록되어 실제적인 변경이 이루어짐 -행이 처리된 후에 복사본은 자동적으로 삭제 쉽게 배우는 MySQL 5.x

12 등록 테이블의 FEE_PRICE, FEE_TOTAL, FEE_PAY가 변경되었는지 질의해 보자.
mysql> update fee     -> set fee_price = fee_price ,     ->     fee_total = fee_total ,     ->     fee_pay = fee_pay     -> where stu_no in     -> (select stu_no     -> from circle); Query OK, 18 rows affected (0.08 sec) Rows matched: 18  Changed: 18  Warnings: 0 등록 테이블의 FEE_PRICE, FEE_TOTAL, FEE_PAY가 변경되었는지 질의해 보자. mysql> select stu_no, fee_year, fee_term,     -> fee_price, fee_total, fee_pay     -> from fee 쉽게 배우는 MySQL 5.x

13 +----------+----------+----------+-----------+-----------+---------+
| stu_no   | fee_year | fee_term | fee_price | fee_total | fee_pay | | | 2000     |        1 |   |   | | | | 2000     |        2 |   |   |    5000 | | | 2001     |        1 |   |   |  | | | 2001     |        2 |   |   |  | | | 2006     |        1 |   |   |  | | | 2006     |        2 |   |   |  | | | 2007     |        1 |   |   | | | | 2007     |        2 |   |   |  | | | 2006     |        1 |   |   | | | | 2006     |        2 |   |   |  | | | 2007     |        1 |   |   | | | | 2007     |        2 |   |   | | | | 2007     |        1 |   |   | | | | 2007     |        2 |   |   | | | | 2007     |        1 |   |   | | | | 2007     |        2 |   |   | | | | 2007     |        1 |   |   | | | | 2007     |        2 |   |   | | 18 rows in set (0.00 sec) 쉽게 배우는 MySQL 5.x

14 16.4 테이블에서 행의 삭제 DELETE 명령문은 테이블에서 행을 삭제하기 위해서 사용
16.4 테이블에서 행의 삭제 DELETE 명령문은 테이블에서 행을 삭제하기 위해서 사용 [예제 16-10] 전자상거래 실무 과목을 삭제하라. mysql> delete from subject     -> where sub_name = '전자상거래 실무'; Query OK, 1 row affected (0.03 sec) 만약 WHERE 절이 생략되면 지정된 테이블에 있는 모든 행이 삭제 -DELETE 명령문은 명령문이 수행 후에 테이블에 관한 정보 테이블의 정의, 칼럼 내용(칼럼이름, 데이터 형, 크기, NOT NULL 유무, PRIMARY KEY), 뷰, 인덱스 등은  그대로 남아 있고 테이블에 있는 내용만 삭제 -DROP 명령문은 테이블의 내용을 삭제할 뿐만 아니라 카탈로그 테이블에 있는 테이블의 정의, 뷰, 인덱스도 삭제 -DELETE 명령문에서 WHERE 절에 있는 부속 질의어는 행이 삭제될 테이블을 참조하지 않아야 한다. 따라서 다음 명령문은 잘못된 것이다. DELETE FROM STUDENT WHERE  STU_NO IN   (SELECT STU_NO FROM   STUDENT  WHERE  CLASS = 2); 쉽게 배우는 MySQL 5.x