[데이터 베이스] SQL 기본 문법 3 (CREATE, ALTER, INSERT, UPDATE, DELETE)

mysql로 배우는 데이터베이스 개론과 실습 책을 보고 정리했습니다.

https://www.hanbit.co.kr/academy/books/book_view.html?p_code=B8548562053 

IT CookBook , MySQL로 배우는 데이터베이스 개론과 실습

 

 

SQL 기본 문법 1, 2에서는 데이터 제어어에 대하여 알아보았다. 

이번 게시글에서는 데이터 정의어와 조작어에 대해 알아본다. 

 

https://p-kyung.tistory.com/58

[데이터 베이스] SQL 기본 문법 1 (SELECT, WHERE, ORDER BY, GROUP BY)

https://p-kyung.tistory.com/59

[데이터 베이스] SQL 기본 문법 2 (JOIN, SUBQUERY)

 

 

 

CREATE 문 : 테이블을 구성하고, 속성과 속성에 관한 제약을 정의하며 기본키 및 외래키를 정의하는 명령이다. 

 

 

CREATE TABLE NewBook (
    book id INTEGER,
    bookname VARCHAR(20),
    publisher VARCHAR(20),
    price INTEGER
);

 

 

문자형 데이터 타입 CHAR vs VARCHAR

CHAR(n)은 n바이트를 가진 문자형 타입이다. 저장되는 문자의 길이가 n보다 작으면 나머지는 공백으로 채워서 n바이트를 만들어 저장한다. VARCHAR(n)은 마찬가지로 n바이트를 가진 문자형 타입이지만 저장되는 문자의 길이만큼만 메모리를 차지하는 가변형이다. 

 

 

위에서 작성한 NewBook에서 기본키를 저장하고 싶으면 이와 같이 한다. 

CREATE TABLE NewBook (
    book id INTEGER,
    bookname VARCHAR(20),
    publisher VARCHAR(20),
    price INTEGER,
    PRIMARY KEY (bookid)
);

 

bookid의 속성이 없어서 두 개의 속성이 기본키가 된다면 다음과 같이 복합키를 지정한다. 

CREATE TABLE NewBook (
    bookname VARCHAR(20),
    publisher VARCHAR(20),
    price INTEGER,
    PRIMARY KEY (bookname, publisher)
);

 

좀 더 복잡한 제약사항을 추가한다면?

 

bookname은 NULL값을 가질 수 없고, publisher는 같은 값이 있으면 안된다. price에 값이 입력되지 않을 경우 기본값 10000을 저장한다. 또 가격은 최소 1000원 이상으로 한다. 

CREATE TABLE NewBook (
    bookname VARCHAR(20) NOT NULL,
    publisher VARCHAR(20) UNIQUE,
    price INTEGER DEFAULT 10000 CHECK(price>=1000),
    PRIMARY KEY (bookname, publisher)
);

 

 

다음과 같은 속성을 가진 NewCustomer 테이블을 생성하시오

custid(고객번호) - INTEGER, 기본키

name(이름) - VARCHAR(40)

address(주소) - VARCHAR(40)

phone(전화번호) - VARCHAR(30)

CREATE TABLE NewCustomer (
    custid INTEGER PRIMARY KEY,
    name VARCHAR(40),
    address VARCHAR(40),
    phone VARCHAR(30)
);

 

 

다음과 같은 속성을 가진 NewOrders 테이블을 생성하시오

orderid(주문번호) - INTEGER, 기본키

custid(고객번호) - INTEGER, NOT NULL 제약조건, 외래키(NewCustomer.custid, 연쇄 삭제)

bookid(도서번호) - INTEGER, NOT NULL 제약조건

saleprice(판매가격) - INTEGER

orderdate(판매일자) - DATE

 

ON DELETE 옵션은 참조되는 테이블의 튜플이 삭제될 때 취할 수 있는 동작을 지정한다. 

이 경우 잠조되는 NewCustomer 테이블의 튜플이 삭제될면 참조하는 NewOrders 테이블의 해당 튜플이 연쇄 삭제된다. 

CREATE TABLE NewOrders (
   orderid INTEGER NOT NULL PRIMARY KEY,
   custid INTEGER NOT NULL,
   bookid INTEGER NOT NULL,
   saleprice INTEGER,
   orderdate DATE,
   FOREIGN KEY(custid) REFERENCES NewCustomer(custid) ON DELETE CASCADE
);

 

 

 

 ALTER 문 : 생성된 테이블의 속성과 속성에 관한 제약을 변경하며 기본키 및 외래키를 변경한다. 

 

 

NewBook 테이블에 VARCHAR(13) 자료형을 가진 isbn 속성을 추가하시오

테이블을 삭제하고 다시 생성해도 되지만 테이블에 저장된 데이터를 그대로 두고 변경할 떄는 ALTER문을 사용한다. 

ALTER TABLE NewBook ADD isbn VARCHAR(13);

 

 

NewBook 테이블의 isbn 속성의 데이터 타입을 INTEGER형으로 변경하시오

ALTER TABLE NewBook MODIFY isbn INTEGER;

 

 

NewBook 테이블의 isbn 속성을 삭제하시오

ALTER TABLE NewBook DROP COLUMN isbn;

 

 

NewBook 테이블의 bookid 속성에 NOT NULL 제약조건을 적용하시오

ALTER TABLE NewBook MODIFT bookid INTEGER NOT NULL;

 

 

NewBook 테이블의 bookid 속성을 기본키로 변경하시오

ALTER TABLE NewBook ADD PRIMARY KEY(bookid);

 

 

 

DROP 문 : 테이블을 삭제하는 명령이다. DROP 문은 테이블의 구조와 데이터를 모두 삭제하므로 사용에 주의해야한다. 

 

 

NewBook 테이블을 삭제하시오

DROP TABLE NewBook;

 

 

NewCustomer 테이블을 삭제하시오 만약 삭제가 거절된다면 원일을 파악하고 관련된 테이블을 같이 삭제하시오(NewOrders 테이블이 NewCustomer를 참조하고 있는 상태이다)

 

NewCustomer 테이블을 삭제하기 위해서는 참조하고 있는 테이블 NewOrders 테이블부터 삭제해야한다. 

DROP TABLE NewOrders;
DROP TABLE NewCustomer;

 

 

 

INSERT 문 : 테이블에 새로운 튜플을 삽입하는 명령이다. 

 

 

Book 테이블에 새로운 도서 '스포츠 의학'을 삽입하시오 스포츠 의학은 한솔의학서적에서 출간했으며 가격은 90,000원 이다. 

속성 이름은 생략이 가능하다. 데이터는 항상 속성의 순서대로 입력하지 않아도 된다. 

INSERT INTO Book(bookid, bookname, publisher, price)
	VALUES (11, `스포츠 의학`, `한솔의학서적`, 90000);
    
INSERT INTO Book
	VALUES (11, `스포츠 의학`, `한솔의학서적`, 90000);

 

 

Book 테이블에 새로운 도서 '스포츠 의학'을 삽입하시오 스포츠 의학은 한솔의학서적에서 출간했으며 가격은 미정이다. 

가격은 NULL로 삽입된다. 

INSERT INTO Book(bookid, bookname, publisher)
	VALUES (11, `스포츠 의학`, `한솔의학서적`);

 

 

수입도서 목록(Imported_Book)을 Book 테이블에 모두 삽입하시오

INSERT INTO Book(bookid, bookname, price, publisher)
    SELECT bookid, bookname, price, publisher
    FROM Imported_Book;

 

 

 

UPDATE 문 : 특정 속성 값을 수정하는 명령이다. 

 

 

Customer 테이블에서 고객번호가 5인 고객의 주소를 '대한민국 부산'으로 변경하시오

SET SQL_SAFE_UPDATES=0;
UPDATE Customer
SET address='대한민국 부산'
WHERE custid=5;

 

 

SQL_SAFE_UPDATES 모드란?

UPDATE나 DELETE 중 Error Code : 1175가 발생할 수 있다. 이 에러는 MySQL UPDATE 및 DELETE 수행 시 실수를 방지할기 위하여 기본키 속성을 사용해서만 가능하도록 한 안전 옵션 때문에 발생하게 된다. 

 

 

UPDATE Customer

SET address='대한민국 부산'

WHERE name='박세리';

이면 에러가 발생할 수 있다. (기본키가 아니기 때문)

 

 

Book 테이블에서 14번 '스포츠 의학'의 출판사를 imported_book 테이블의 21번 책의 출판사와 동일하게 변경하시오

UPDATE Book
SET publisher=(SELECT publisher
                FROM imported_book
                WHERE bookid='21')
WHERE bookid='14';

 UPDATE 문에서 여러 속성 값을 한꺼번에 수정하는 작업은 가능하나 잘못 사용하면 위험하다. 잘못 수정된 데이터는 다시 원래대로 돌릴 수 있으나 경우에 따라 문제를 일으킬 수 있으므로 주의해야한다. 

 

 

 

 

DELETE 문 : 테이블에 있는 기존 튜플을 삭제하는 명령이다.

 

 

 

Book 테이블에서 도서번호가 11인 도서를 삭제하시오

DELETE FROM Book
WHERE bookid='11';

 

 

모든 고객을 삭제하시오

DELETE FROM Customer;

이는 삭제가 되지 않는다. 왜냐하면 Orders 테이블에서 Customer.custid 속성을 외래키로 참조하고 있기 때문이다. 제약이 해제되지 않으면 데이터 삭제가 중지된다.