정보처리기사[실기 핵심 정리] - 기본 SQL 작성

SQL(Structured Query Language)

1) SQL의 개념

• 데이터베이스 시스템에서 데이터를 처리하기 위해 사용되는 구조적 데이터 질의 언어

 

2) SQL 문법의 종류

• Data Definition Language(DDL) - 데이터 정의어
  • 테이블이나 다양한 객체들을 정의하는 데 사용되는 명령어
  • CREATE, ALTER, DROP, RENAME, TRUNCATE
• Data Manipulation Language(DML) - 데이터 조작어
  • 데이터베이스 내의 데이터를 조작(조회,추가,수정,삭제)하는 데 사용되는 명령어
  • SELECT, INSERT, UPDATE, DELETE
• Data Control Language(DCL) - 데이터 제어어
  • 데이터베이스에 접근하고 사용할 수 있는 권한을 부여하고 회수하는 명령어
  • GRANT,REVOKE
• Transaction Control Language(TCL) - 트랜젝션 제어어
  • 작업을 단위를 묶어서, 그 결과를 단위별로 제어하는 명령어
  • COMMIT, ROLLBACK, SAVEPOINT

Data Definition Language(DDL) - 데이터 정의어

1) 데이터 정의어의 개념

• 데이터가 저장되는 테이블이나 다양한 객체들을 정의하는 데 사용되는 언어
• 데이터를 담는 '그릇'을 정의하는 언어로 데이터베이스의 구조를 만들거나 수정,삭제하는 명령어들을 포함한다.

 

2) 조작 방법

• CREATE
  • 데이터베이스 객체(테이블, 인덱스, 뷰 등)을 생성하는 명령어
  • 데이터 베이스 생성 : CREATE DATABASE db; == db라는 이름의 데이터베이스 생성
  • 테이블 생성 : CREATE TABLE 회원( 조건); == 회원 테이블 생성
  • INDEX 생성 : CREATE INDEX search_name ON 회원 (name); == 회원 테이블에 search_name 이름의 인덱스 생성
  • VIEW 생성 : CREATE VIEW v_user AS SELECT name, age FROM 회원 == 회원 테이블에서 이름과 나이만 가져오는 VIEW 생성
• ALTER
  • 기존에 생성된 데이터베이스 객체의 구조를 변경하는 명령어
    • 회원 테이블에 ADDR 속성 추가
    • ALTER TABLE 회원 ADD ADDR VARCHAR(200) null;
    • 회원 테이블에 AGE 속성 INT로 변경
    • ALTER TABLE 회원 MODIFY AGE INT(11); 
    • 회원 테이블 AGE 속성 삭제
    • ALTER TABLE 회원 DROP COLUMN AGE;
    • 회원명 INDEX를 성명으로 변경
    • ALTER INDEX 회원명 RENAME TO 성명
    • INDEX의 속도가 저하되거나 깨졌을 경우 INDEX 재구성
    • ALTER INDEX 회원명 REBUILD
    • INDEX 비활성화
    • ALTER INDEX 회원명 UNUSABLE
  • DROP
    • 데이터베이스 객체를 삭제하는 명령어
    • 테이블삭제
    • DROP TABLE 회원;
  • TRUNCATE
    • 테이블의 모든 데이터를 삭제하는 명령어, 구조는 남기고 데이터만 지운다.
    • 회원 테이블 내용삭제
    • TRUNCATE [TABLE] 회원;

 

4) 제약 조건 적용

제약 조건	설명
PRIMARY KEY	● 하나의 테이블에서 유일하게 데이터를 식별할 수 있는 기본키를 정의한다. ● 기본으로 NOT NULL, UNIQUE 제약이 포함
FOREIGN KEY	● 다른 테이블의 기본키를 참조하는 외래키를 정의한다. ● 참조 무결성 위배 상황 발생 시 처리 방법으로 옵션 지정 가능
UNIQUE	● 테이블 내에서 각 행의 해당 컬럼 값이 유일해야 함을 정의한다.
NOT NULL	●  해당 컬럼이 NULL값을 허용하지 않도록 정의한다.
CEHCK	● 개발자가 정의하는 특정 조건을 만족해야 하는 컬럼에 제약을 설정한다.

 

DML(Data Manipulation Language) - 데이터 조작어

1) 데이터 조작어의 개념

• 데이터베이스에서 데이터 조회, 추가, 삭제, 수정을 위한 언어이다.
• 관계형 데이터베이스에서 데이터를 검색하거나 업데이트하는 등의 조작을 위해 사용된다.

2) INSERT

• 테이블에 데이터를 추가하는 명령어
• 컬럼 순서와 값의 순서가 일치해야 해당 컬럼에 데이터가 추가된다.
• 숫자에는 홑따옴표를 붙이지 않아도 되지만, 문자는 꼭 붙여줘야 한다.
• 회원 추가
• INSERT INTO 회원 (NAME,AGE) VALUES('이홍직', 42);
• 여러 행을 검색하여 추가
• INSERT INTO 회원 SELECT NAME,AGE FROM 회원2

3) SELECT

• 하나 이상의 테이블에서 데이터를 조회하는 명령어
• 사용 키원드
  • WHERE : 조건에 맞는 데이터를 지정
  • GROUP BY : 특정 속성을 그룹으로 만들어서 집계 함수를 사용
  • HAVING : GROUP BY 절에 정의된 조건
  • ORDER BY : 반환되는 열을 순서를 정의
• 논리연산자
  • NOT, AND, OR

4) UPDATE

• 테이블의 기존 데이터를 갱신하는 명령어
• 셀 단위로 갱신이 가능
• WHERE 절을 생략했을 경우 모든 행이 갱신
• 사용 예
  • 모든 직원의 나이를 1살 증가
  •  UPDATE 사원정보 SET 나이 = 나이 +1;
  • 개발팀의 부서명을 개발 지원팀으로 변경
  • UPDATE 사원정보 SET 부서 = '개발지원팀'  WHERE 부서 = '개발팀';

 

5) DELETE

• 테이블에서 데이터를 삭제하는 명령어
• WHERE절을 생략했을 경우 모든 행이 삭제
• 개발팀 직원만 삭제
• DELETE FORM 사원정보WHERE  부서 = '개발팀';
• 디자인 팀 '이창훈' 만 삭제
• DELETE FROM 사원정보 WHERE 부서 = '디자인 팀' AND 이름 = '이창훈'

DCL(Data Control Language) - 데이터 제어어

1) 데이터 제어어의 개념

• 데이터베이스에 접근하거나 객체에 대한 권한을 주고 빼앗는 역할을 하는 언어이다.
• 사용자에게 권한을 생성하거나 삭제하는 등의 작업을 수행한다.

 

2) GRANT

• 데이터베이스 사용자에게 권한을 부여하는 명령어
• 기본형태 : GRANT[권한] ON [객체명] TO [사용자 계정] [WITH GRANT OPTION];
• WITH GRANT OPTION : 지정된 권한을 다른 유저에게 부여할 수 있도록 함
• HUNGJIL 사용자에게 사원정보 SELECT 권한 부여
• GRANT SELECT ON 사원정보 TO HUNGJIK;
• HUNGJIK 사용자에게 사원정보 SELECT 권한 부여하고 다른 사용자에게 SELECT 권한을 부여할 수 있도록 함
• GRANT SELECT ON 사원정보 TO HUNGJIK WITH GRANT OPTION;

 

3) REVOKE

• 데이터베이스 사용자로부터 권한을 회수하는 명령어
• 기본형태: REVOKE [권한] ON [객체명] FROM [사용자계정][CASCADE]
• CASCADE: 사용자가 부여한 모든 사용자 권한을 같이 회수
• HUNGHIK 사용자에게 사원정보 SELECT 권한 회수
• REVOKE SELECT ON 사원정보 FROM HUNGJIK;

TCL(Transaction Control Language) - 트랜잭션 제어어

1) 트랜잭션 제어어의 개념

• 트랜잭션을 제어하는 데 사용되는 명령어들로, 주로 COMMIT과 ROLLBACK을 의미한다
• 데이터베이스에서의 작업 단위를 안전하게 관리하게 위해 사용