Programme/SQL

[SQL] 테이블 생성 (DDL & DML)

프랑스어전공 2023. 11. 23. 17:46

DDL - Data Definition Language

데이터베이스를 정의하는 언어를 말하며 데이터를 생성하거나 수정, 삭제 등 데이터의 전체 골격을 결정하는 역할의 언어를 말한다.

CREATE

CREATE table book_list (

book_no varchar(16) not null,
book_name varchar(50) ,
writer varchar(50) ,
publisher varchar(30) ,
reg_date date,
price int #price는 계산에 사용될 수 있으므로 int

);

데이터가 없을 수 없는 경우 즉 데이터가 없으면 오류가 나는게 맞을때 not null 옵션을 걸어주고 그 외에는 유연하게 조건을 건다. price는 계산에 사용될 수 있으므로 int로 설정을 하였다. 하지만 저렇게만 실행했을 때는 오류가 난다.

데이터베이스를 설정하지 않았기 때문, 아래와 같이 현재 데이터베이스들을 확인하고 연결한다.

show databases; #현재 어떤 DB가 있는지?

use sys;

SELECT * from book_list;  #테이블 출력

CREATE 요약

  1. CREATE 문으로 데이터베이스에 테이블을 생성할 수 있다.
  2. 테이블을 생성할 시에는 테이블명, 컬럼명, 컬럼의 데이터타입, 사이즈, 필수 여부 등에 대한 정의가 되어야 한다.
  3. 테이블명은 숫자로 시작할 수 없고 '_', '$' 외의 특수 기호는 사용할 수 없다.(하지만 실제 사용자 테이블에서 '$'는 잘 사용하지 않음.

ALTER

데이터 자체의 구조또는 속성을 바꾸는 함수이다.

alter 명령어로 할 수 있는 것들은 다음과 같다.

  1. CHANGE: 컬럼명 변경
  2. MODIFY : 컬럼의 데이터 타입 및 사이즈 변경
  3. ADD: 컬럼 추가
  4. DROP : 컬럼 삭제
  5. RENAME: 테이블명 변경
select * from book_list;
select * from book_info;

alter table book_list add column description varchar(1000);
alter table book_list modify column book_name varchar(100); #modify size

desc book_list;

alter table book_list change column description book_desc varchar(1000);
alter table book_list drop column book_desc;
alter table book_list rename book_info;

Truncate & Drop

TRUNCATE는 테이블을 초기화 시킨다. 모든 데이터가 사라지고 ROLLBACK도 되지 않는다. DELETE 보다 대용량 데이터를 삭제하기 용이

DROP은 데이터베이스, 테이블을 삭제하는 역할을 한다.

TRUNCATE table book_list; #모든데이터가 사라짐 rollback도 안됨. delete보다 대용량 데이터를 삭제하기 용이
DROP TABLE BOOK_LIST;
  1. TRUNCATE는 데이터만 삭제하는 명령어이고 DROP은 테이블과 데이터를 모두 삭제하는 명령어이다.
  2. TRUNCATE는 특정 행만 삭제할 수가 없기 때문에 반드시 모든 행을 통째로 삭제하는 경우에만 사용해야 한다.
  3. TRUNCATE, DROP 모두 한번 실행하면 돌이킬 수 없으므로 매우 신중하게 사용해야 한다

 

 

DML - Data Manipulation Language

정의된 데이터베이스에 입력된 레코드를 조회하거나 수정하거나 삭제하는 등의 역할을 하는 언어를 말한다. 쉽게 말하면, 테이블에 있는 행과 열을 조작하는 언어라고 생각하면 된다. 데이터베이스 사용자가 질의어를 통하여 저장된 데이터를 실질적으로 처리하는데 사용하는 언어이다.

INSERT

Insert 에는 두가지 방식이 있다. 모든 컬럼에 대해 작업하는 방식과 원하는 컬럼에 대해서만 작업하는 방식.

 

Type 1

values 뒤에 삽입할 내용들을 입력한다. 주의해야할 점은 values 뒤에 오는 데이터의 순서가 테이블에 존재하는 컬럼의 순서와 정확하게 일치해야 한다는 것.

SELECT  * from book_info;

#type 1

insert into book_info values ('9791191891287','메리골드 마음 세탁소', '윤정은', '북로망스', '20230306',15000);

    #price를 문자형으로 넣어도 삽입됨. 자체에서 따옴표를 제거했을때 숫자형이면 변환시켜서 삽입되게 함.

INSERT INTO BOOK_INFO VALUES ('9791191891287','메리골드 마음 세탁소', '윤정은', '북로망스', '20230306','15000');

Type 2

2번째 방식은 원하는 컬럼에만 데이터를 INSERT 하는 방식이다. 참고로 컬럼 중에서 not null로 설정한 컬럼이 있다면 그 컬럼은 INSERT를 해줘야한다.

#2 type 내가 원하는 컬럼에만 데이터 insert하기  / *not null로 설정한 컬럼은 insert를 해줘야함

insert book_info (book_no, book_name, writer, publisher, price)

values ('9791167749984', '도둑맞은 집중력', '요한하리', '어크로스', 18800);

COMMIT; #모든명령문은 커밋해야함. 안그러면 나만 보임.

ROLLBACK; #잘못 insert 했을 경우

실무에서는 type 2 를 선호한다. 테이블을 생성하고나서 변하지 않는 경우도 있긴하지만 변하는 경우가 더 많기 때문이다. 컬럼이 변할 경우 첫번째 타입은 에러가 발생한다.

UPDATE

값을 변경할 수 있는 함수이다. 조건절이 꼭 필요하다. 누락되지 않도록 주의한다. 조건절 없이 사용해도 되긴하지만 조건절이 없으면 모든 행을 변경시켜 버리므로 주의가 필요하다.

ALTER와는 명백히 다르다. UPDATE는 값을 변경하고 ALTER는 데이터 자체의 속성이나 구조를 변경한다.

SELECT * FROM BOOK_LIST; 
 
UPDATE BOOK_LIST SET REG_DATE = '20230302' WHERE BOOK_NAME = '세이노의 가르침';
COMMIT; 
 #BOOK_NAME이 '세이노의 가르침'이란 값에 대해서 REG_DATE를 '20230302'로 변경한다. 

#조건절이 없는경우
UPDATE BOOK_LIST SET WRITER  = 'SQL';
ROLLBACK;

 #모든 값을 변경시켜 버리므로 실수였다면 바로 rollback시킨다.

DELETE

특정 행또는 컬럼값을 제거할 수 있는 함수이다. DELETE 또한 조건절이 누락되지 않도록 주의한다.

만약 조건절을 입력하지 않고 Commit 까지 해버렸다면?

데이터에는 골든 타임이 있다고 한다. 바로 도움을 청한다! ! ! 혼자 해결하려하지 말자.

SELECT * FROM BOOK_LIST

DELETE FROM BOOK_LIST WHERE publisher='데이원';

#특정컬럼값만 삭제하고 싶을땐
UPDATE book_list SET writer = NULL WHERE book_name = '메리골드 마음 세탁소';

#주의 
DELETE FROM book_list;
commit;