정보처리기사 : SQL 작성 (1) 완전정복!!

SQL 

• 데이터를 처리하기 위해 쓰는 질의 언어

 

SQL의 특징

• 구문이 영어 문장과 유사하여 배우기 쉽고 사용하기 편리하다.
• 데이터 연산이 절차적이지 않고 집합 단위로 처리된다.
• 표준 SQL 문법이 있어서 다양한 DBMS(예: Oracle, MSSQL, MySQL 등)에 구애받지 않고 사용할 수 있다.
• SQL은 대소문자를 구별하지 않는다.
• 한 줄 주석은 --로 시작하고, 여러 줄 주석은 /* */로 묶는다.

 

SQL 문법의 종류

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

• CREATE: 테이블 생성

CREATE TABLE 회원 (
    USER_NO INT(11) NOT NULL AUTO_INCREMENT,
    NAME VARCHAR(50) NOT NULL,
    AGE TINYINT(4) DEFAULT '0',
    PRIMARY KEY (USER_NO)
);

• ALTER: 테이블 구조 변경

-- 회원 테이블에 주소 속성 추가
ALTER TABLE 회원 ADD ADDR VARCHAR(200);

• DROP: 테이블 삭제

-- 회원 테이블 삭제
DROP TABLE 회원;

• RENAME: 테이블 이름 변경

-- 회원 테이블 이름을 사용자로 변경
RENAME TABLE 회원 TO 사용자;

• TRUNCATE: 테이블 데이터 초기화

-- 회원 테이블 데이터 삭제
TRUNCATE TABLE 회원;

---

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

• SELECT: 데이터 조회

-- 부서별 인원수와 나이 합산 조회
SELECT 부서, COUNT(*) AS 인원수, SUM(나이) AS 나이
FROM 사원정보
GROUP BY 부서;

• INSERT: 데이터 삽입

-- 회원 데이터 추가
INSERT INTO 회원 (NAME, AGE)
VALUES ('이흥직', '42');

• UPDATE: 데이터 수정

-- 회원 나이를 43으로 수정
UPDATE 회원
SET AGE = 43
WHERE NAME = '이흥직';

• DELETE: 데이터 삭제

-- 회원 데이터 삭제
DELETE FROM 회원
WHERE NAME = '이흥직';

---

③ 데이터 제어어 (DCL, Data Control Language)

• GRANT: 권한 부여

-- 사용자에게 SELECT 권한 부여
GRANT SELECT ON 회원 TO '사용자이름';

• REVOKE: 권한 회수

-- 사용자로부터 SELECT 권한 회수
REVOKE SELECT ON 회원 FROM '사용자이름';

---

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

• COMMIT: 트랜잭션 결과 확정

-- 변경 사항 확정
COMMIT;

• ROLLBACK: 트랜잭션 결과 취소

-- 마지막 변경 사항 취소
ROLLBACK;

• SAVEPOINT: 트랜잭션 중간 저장점 설정

-- 저장점 설정
SAVEPOINT 저장점1;

-- 저장점으로 롤백
ROLLBACK TO 저장점1;

---

 

DDL의 조작 방법

① CREATE

• 데이터베이스 객체(테이블, 인덱스, 뷰 등)를 생성하는 명령어
• 데이터베이스 생성 
• CREATE DATABASE db;
• 테이블 생성

  CREATE TABLE 회원 (
      USER_NO INT(11) NOT NULL AUTO_INCREMENT,
      NAME VARCHAR(50) NOT NULL,
      AGE TINYINT(4) DEFAULT '0',
      PRIMARY KEY (USER_NO)
  );
  

 

 

DDL (Data Definition Language) 조작 방법

① CREATE

• INDEX 생성

  // 회원 테이블에 search_name 이름의 인덱스 생성
  CREATE INDEX search_name
  ON 회원 (name);
  

•  VIEW 생성

  // 회원 테이블에서 이름과 나이만 가지고 VIEW 생성
  CREATE VEIW y_user
  AS
   [쿼리]
   ;

 

② ALTER

• 속성 추가

  // 회원 테이블에 ADDR 속성 추가
  ALTER TABLE 회원 ADD ADDR VARCHAR(200);
  

• 속성 변경

  // 회원 테이블에 AGE 속성 INT로 변경
  ALTER TABLE 회원 MODIFY AGE INT(11);
  

• 속성 삭제

  // 회원 테이블에 AGE 속성 삭제
  ALTER TABLE 회원 DROP COLUMN AGE;
  

• INDEX 변경

  // 회원명 INDEX를 성명으로 변경
  ALTER INDEX 회원명 RENAME TO 성명;
  
  // INDEX의 속도가 저하되거나 깨졌을 경우 INDEX 재구성
  ALTER INDEX 회원명 REBUILD;
  
  // INDEX 비활성화
  ALTER INDEX 회원명 UNUSABLE;
  

③ DROP

• 데이터베이스 객체를 삭제하는 명령어

  // 회원 테이블 삭제
  DROP TABLE 회원;
  

④ TRUNCATE

• 테이블의 모든 데이터를 삭제하는 명령어로, 구조는 남기고 데이터만 비운다.[초기화]

  // 회원 테이블 내용 초기화
  TRUNCATE [TABLE] 회원;

---

제약 조건 적용

제약 조건 설명

PRIMARY KEY	- 하나의 테이블에서 유일하게 데이터를 식별할 수 있는 기본키를 정의- 기본으로 NOT NULL, UNIQUE 제약 포함
FOREIGN KEY	- 다른 테이블의 기본키를 참조하는 외래키를 정의 - 참조 무결성 위배 상황 발생 시 처리 방법으로 옵션 지정 가능 - 예) FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE;

 

 

 

제약 조건 적용

제약 조건 설명

UNIQUE	- 테이블 내에서 각 행의 해당 컬럼 값이 유일해야 함을 정의 - 예) USER_ID VARCHAR(10) UNIQUE NOT NULL
NOT NULL	- 해당 컬럼이 NULL 값을 허용하지 않도록 정의- 예) USER_ID VARCHAR(10) NOT NULL
CHECK	- 개발자가 정의하는 특정 조건을 만족해야 하는 컬럼에 대한 제약 설정 - 예) CONSTRAINT user_jumin CHECK(LENGTH(jumin)=13)

 

---

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

1. 데이터 조작어의 개념

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

2. 조작 방법

① INSERT

• 테이블에 데이터를 추가하는 명령어
• 컬럼 순서와 값의 순서가 일치해야 해당 컬럼에 데이터가 추가된다.
• 예: 
• // 회원 데이터 추가 INSERT INTO 회원 (NAME, AGE) VALUES ('이흥직', '42'); // 여러 행을 검색하여 추가 INSERT INTO 회원 SELECT NAME, AGE FROM 회원2;

② SELECT

• 하나 이상의 테이블에서 데이터를 조회하는 명령어

사용 키워드

• WHERE: 조건에 맞는 데이터를 지정
• GROUP BY: 특정 속성을 그룹으로 만들어서 집계 함수를 사용
• HAVING: GROUP BY 절에 정의된 조건
• ORDER BY: 반환되는 열의 순서를 지정

사용 예

• 모든 직원 정보 조회

  SELECT * FROM 사원정보;
  

• 개발팀 직원 정보 조회 (사번, 이름, 직책만)

  SELECT 사번, 이름, 직책 FROM 사원정보
  WHERE 부서 = '개발팀';
  

• 부서별 인원수와 나이 합산

  SELECT 부서, COUNT(*) AS 인원수, SUM(나이) AS 나이
  FROM 사원정보
  GROUP BY 부서;