[SQL] 기본 SQL문

기본 SQL문

1. 기본 쿼리의 종류

 DDL / DML / DCL

D 는 Definition 정의

M 은 Manipulation 조작

C 은 Control 제어

(1) DDL

DDL 은 테이블이나 유저 같은 것들을 생성하거나 지우거나 하는 구문을 얘기합니다. 객체를 만들거나 수정하거나 없애거나 하는 것인데, 개발 단계에서 사용이 되고, 운영단계 에서는 덜 쓰이게 되는 구문 이라는 얘기입니다. CREATE 와 DROP/ ALTER 와 같은 것들이 있습니다.

데이터를 입력하거나 수정 하는 개념이 아니며, 작업하기 위한 환경을 생성하거나 변경하는 언어라고 생각을 하시면 되겠습니다. 예를 들어, 데이터와 무관하게. 체크를 하기 위한 함수를 생성할 수 있겠습니다.

[CREATE FUNCTION 어쩌고저쩌고~~]

라는 DDL 구문을 날리면, 함수가 생성이 됩니다. 이것은 게시판 따위에서 글을 입력하거나 삭제 하는 그런 것과는 성향이 다르죠. 이해가 되시겠는지요.

(2) DML

DML 은 계속 쓰이게 되는 구문을 얘기합니다. SELECT / INSERT / UPDATE / DELETE 와 같은 것들이 있으며, 각각의 목적은 조회/입력/수정/삭제 와 목적을 띕니다. 게시판에서 글을 적고 저장 을 누르거나, 댓글을 달고 저장을 누르는 순간, 데이터베이스로는 INSERT 의 구문이 날아가는 것입니다.

이러한 종류를 DML 이라고 합니다.

(3) DCL

DCL 은 제어 하는 구문 입니다. 좀더 쉽게 생각알 하시면, 권한을 넣거나 빼거나 와 같은 구문을 얘기합니다.

GRANT / REVOKE

2. create문

(1) Oracle

CREATE TABLE ARTIST (

   ArtistID int  NOT NULL,

   Name  char(25) NOT NULL,

   Nationality varchar(30) NULL,

   Birthdate number(4,0) NULL,

   DeceasedDate number(4,0) NULL,

   CONSTRAINT ArtistPK PRIMARY KEY (ArtistID),

   CONSTRAINT ArtistAK1 UNIQUE (Name),

   CONSTRAINT NationalityValues CHECK (Nationality IN ('Canadian', 'English', 'French', 'German', 'Mexican', 'Russian', 'Spanish', 'US')),

   CONSTRAINT BirthValuesCheck CHECK (Birthdate < DeceasedDate),

   CONSTRAINT ValidBirthYear CHECK ((Birthdate > 1000) and (Birthdate < 2100)),

   CONSTRAINT ValidDeathYear CHECK ((DeceasedDate > 1000) and (DeceasedDate < 2100))

);

Create Sequence ArtistID Increment by 1 start with 1;

※ 시퀀스를 따로 만들어준다.

(2) MySQL

CREATE TABLE ARTIST (

   ArtistID int  AUTO_INCREMENT PRIMARY KEY,

   Name  varchar(25) NOT NULL,

   Nationality varchar(30) NULL,

   Birthdate year(4)  NULL,

   DeceasedDate year(4)  NULL

);

CREATE UNIQUE INDEX ARTIST_Name_IDX ON ARTIST(Name);

※ 제약조건이 안먹힌다.

cf.)

 CREATE TABLE `emp` (

  `emp_no` int(11) unsigned NOT NULL,

  `dept_no` int(11) unsigned NOT NULL,

  `emp_name` varchar(32) NOT NULL,

  PRIMARY KEY  (`emp_no`),

  KEY `dept_no` (`dept_no`),

  CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dept_no`) REFERENCES `dept` (`dept_no`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 

→ emp 테이블에 걸려 있는 제약조건은 1개이며, 이름은 emp_ibfk_1이다.

→ emp의 dept_no는 외래키이며, dept의 dept_no를 참조하고 있다. 별도의 옵션이 없으므로 DELETE시, UPDATE시에 제약(RESTRICT)이 있다.

cp.) 제약조건의 종류

3. 테이블 수정

한번 생성된 테이블은 특별히 사용자가 구조를 변경하기 전까지 생성 당시의 구조를 유지하게 된다.

처음의 테이블 구조를 그대로 유지하는 것이 최선이지만, 업무적인 요구 사항이나 시스템 운영상 테이블을 사용하는 도중에 변경해야할 일들이 발생하기도 한다.

이 경우 주로 컬럼을 추가/삭제 또는 제약조건을 추가/삭제 작업을 하게 된다.

(1)  DROP TABLE (테이블 삭제)

테이블을 잘못 만들었거나 테이블이 더 이상 필요 없을 경우 해당 테이블을 삭제해야 한다.

DROP TABLE 테이블명 [CASCADE CONSTRAINT];

DROP TABLE PLAYER;

- DROP TABLE 명령어를 사용하면 테이블의 모든 데이터 및 구조를 삭제한다.

- CASCADE CONSTRAINT 옵션은 해당 테이블과 관계가 있었던 참조되는 제약조건에 대해서도 삭제한다는 것을 의미한다.

(SQL Server에서는 CASCADE 옵션이 존재하지 않는다. 테이블 삭제 전에 참조하는 FOREIGN KEY 제약 등을 먼저 삭제해야 한다.)

(2) RENAME (테이블명 변경)

RENAME 명령어를 사용하여 테이블의 이름을 변경할 수 있다.

RENAME 변경전 테이블명 TO 변경후 테이블명;

RENAME TEAM_BACKUP TO TEAM;

- TEAM_BACKUP 테이블명을 TEAM 으로 변경한다.

(3) ADD COLUMN (컬럼 추가)

기존 테이블에 필요한 컬럼을 추가하는 명령어

ALTER TABLE 테이블명

ADD 추가할 컬럼명  데이터 유형;

ALTER TABLE PLAYER

ADD (ADDRESS VARCHAR2(80));

- PLAYER 테이블 마지막에 ADDRESS(길이 80) 컬럼을 추가한다.

- 새롭게 추가된 컬럼은 테이블의 마지막 컬럼이 되며 컬럼의 위치를 지정할 수는 없다.

(4) DROP COLUMN (컬럼 삭제)

DROP COLUMN은 테이블에서 필요 없는 컬럼을 삭제할 수 있으며, 데이터가 있거나 없거나 모두 삭제 가능하다.

한 번에 하나의 컬럼만 삭제 가능하며, 컬럼 삭제 후 최소 하나 이상의 컬럼이 테이블에 존재해야 한다.

ALTER TABLE 테이블명

DROP COLUMN 삭제할 컬럼명;

ALTER TABLE PLAYER

DROP COLUMN ADDRESS;

- PLAYER 테이블에 ADDRESS 컬럼을 삭제한다.

- 한번 삭제된 컬럼은 복구가 불가능하다.

(5) MODIFY COLUMN (컬럼 수정)

테이블에 존재하는 컬럼의 데이터 유형, 디폴트(DEFAULT) 값, NOT NULL 제약조건에 대한 변경을 한다.

ALTER TABLE 테이블명

MODIFY (컬럼명1 데이터 유형 [DEFAULT 식] [NOT NULL],

             컬럼명2 데이터 유형 [DEFAULT 식] [NOT NULL]);

                   

ALTER TABLE TEAM_TEMP

MODIFY (ORIG_YYYY VARCHAR2(8) DEFAULT '20020129' NOT NULL);

- TEAM 테이블의 ORIG_YYYY 컬럼의 데이터 유형을 CHAR(4)->VARCHAR2(8)로 변경하고, 향후 입력되는 데이터의 DEFAULT 값으로 '20020129'를 적용하고, 모든 행의 ORIG_YYYY 컬럼에 NULL이 없으므로 제약조건 NULL -> NOT NULL로 변경한다.

 

cf.) MODIFY COLUMN 시 고려사항

- 해당 컬럼의 크기를 늘릴 수는 있지만 줄이지는 못한다. 이는 기존의 데이터가 훼손될 수 있기 때문이다.

- 해당 컬럼이 NULL 값만 가지고 있거나 테이블에 아무 행도 없으면 컬럼의 폭을 줄일 수 있다.

- 해당 컬럼이 NULL 값만을 가지고 있으면 데이터 유형을 변경할 수 있다.

- 해당 컬럼의 DEFAULT 값을 바꾸면 변경 작업 이후 발생하는 행 삽입에만 영향을 미치게 된다.

- 해당 컬럼에 NULL 값이 없을 경우에만 NOT NULL 제약조건을 추가할 수 있다.

(6) RENAME COLUMN (컬럼명 수정)

테이블을 생성하면서 만들어졌던 컬럼명을 변경해야 할 경우에 사용한다.

ALTER TABLE 테이블명

RENAME COLUMN 변경해야할 컬럼명 TO 새로운 컬럼명;

ALTER TABLE PLAYER

RENAME COLUMN PLAYER_ID TO TEAM_ID;

- PLAYER 테이블의 PLAYER_ID 컬럼명을 TEAM_ID 로 변경한다.

- RENAME COLUMN으로 컬럼명을 변경하면, 해당 컬럼과 관계된 제약조건에 대해서도 자동으로 변경된다.

(Oracle 등 일부 DBMS에서만 지원한다.)

(7) ADD CONSTRAINT

테이블 생성 시 제약조건을 적용하지 않았다면, 생성 이후에 필요에 의해서 제약조건을 추가할 수 있다.

ALTER TABLE 테이블명

ADD CONSTRAINT 제약조건명 제약조건 (컬럼명);

ALTER TABLE PLAYER

ADD CONSTRAINT PLAYER_FK

   FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID);

- PLAYER 테이블에 TEAM 테이블과의 외래키 제약조건을 추가한다.

- 제약조건명은 PLAYER_FK로 하고, PLAYER 테이블의 TEAM_ID 컬럼이 TEAM 테이블의 TEAM_ID를 참조하는 조건이다.

- FOREIGN KEY 제약조건을 위와 같이 추가하면 PLAYER 테이블의 TEAM_ID 컬럼이 TEAM 테이블의 TEAM_ID 컬럼을 참조하게 된다.

참조 무결성 옵션에 따라서 만약 TEAM 테이블이나 TEAM 테이블의 데이터를 삭제하려 할 경우 외부(PLAYER 테이블)에서 참조되고 있기 때문에 삭제가 불가능하게 제약을 할 수 있다.

즉 외부키(FK)를 설정함으로써 실수에 의한 테이블 삭제나 필요한 데이터의 의도치 않은 삭제와 같은 불상사를 방지하는 효과를 볼 수 있다.

(8) DROP CONSTRAINT

테이블 생성 시 부여했던 제약조건을 삭제하는 명령어

ALTER TABLE 테이블명

DROP CONSTRAINT 제약조건명;

ALTER TABLE PLAYER

DROP CONSTRAINT PLAYER_FK;

- PLAYER 테이블의 제약조건 PLAYER_FK 를 제거한다.

4. 데이터 조작

(1) insert문

1) 모든 데이터를 입력할 경우

INSERT INTO emp VALUES(7369, 'SMITH', 'CLERK', 7902, TO_DATE('80/12/17'),  800, NULL,  20);

2) 원하는 데이터만 입력할 경우

INSERT INTO dept (deptno, dname) VALUES(10, 'ACCOUNTING' );

cf.) 원하지 않는 데이터는 null값이나 디폴트로 지정한 값이 들어간다.

3) SELECT 문장을 이용한 INSERT

INSERT INTO dept2 SELECT * FROM dept;

(2) update문

UPDATE emp SET deptno = 30 WHERE empno = 7902;

(3) delete 문

1) 사원번호가 7902번인 사원의 데이터를 삭제.      

DELETE FROM emp WHERE empno = 7902 ;

2) 평균급여보다 적게 받는 사원 삭제

DELETE FROM emp WHERE sal < (SELECT AVG(sal) FROM emp);

        

3) 모든 행이 삭제

DELETE FROM emp;