[정보처리기사실기] 기본 SQL 문법

2과목. 데이터베이스 구축

📒 10. 기본 SQL 작성

---

1. SQL(Structured Query Language)

(1) SQL의 개념

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

(2) SQL 문법의 종류

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

 

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

(1) 데이터 정의어의 개념

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

(2) CREATE 

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

-- db 데이터베이스 생성
CREATE DATABASE db;

-- 회원 테이블 생성
CREATE TABLE 회원 (
    USER_NO INT(11) NOT NULL AUTO_INCREMENT,
    NAME VARCHAR(50) NOT NULL,
    PRIMARY KEY (USER_NO)
);

-- INDEX 생성
CREATE INDEX search_name
ON 회원 (name);

-- VIEW 생성
CREATE VIEW v_user
AS
	SELECT name FROM 회원
;

(3) ALTER

• 기존에 생성된 데이터베이스 객체의 구조를 변경하는 명령어

-- 속성 추가
ALTER TABLE 회원 ADD ADDR VARCHAR(200) null;

-- 속성 변경
ALTER TABLE 회원 MODIFY AGE INT(11);

-- 속성 삭제
ALTER TABLE 회원 DROP COLUMN AGE;

-- INDEX 변경⭐
-- 1. 회원명 INDEX를 성명으로 변경
ALTER INDEX 회원명 RENAME TO 성명

-- 2. INDEX의 속도가 저하되거나 깨졌을 경우 INDEX 재구성
ALTER INDEX 회원명 REBUILD

-- 3. INDEX 비활성화
ALTER INDEX 회원명 UNUSABLE

(4) DROP

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

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

(5) TRUNCATE

• 테이블의 모든 데이터를 삭제하는 명령어로, 구조는 남기고 데이터만 삭제

-- 내용 삭제
TRUNCATE[TABLE] 회원;

 

 

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

(1) 데이터 조작어의 개념

• 데이터 조회, 추가, 삭제, 수정을 위한 언어

(2) INSERT

• 테이블에 데이터를 추가하는 명령어로, 컬럼 순서와 값의 순서가 일치해야 해당 컬럼에 데이터가 추가됨

-- 회원 데이터 추가
INSERT INTO 회원 (NAME, AGE) VALUES('peazh', 20);

-- 여러 행을 검색하여 추가
INSERT INTO 회원 SELECT NAME, AGE FROM 회원2

(3)  SELECT

• 하나 이상의 테이블에서 데이터를 조회하는 명령어
• 사용 키워드
  - WHERE : 조건에 맞는 데이터 지정
  - GROUP BY : 특정 속성을 그룹으로 만들어서 집계 함수 사용
  - HAVING : GROUP BY 절에 정의된 조건
  - ORDER BY : 반환되는 행의 순서 지정 (DESC:내림차순, ASC:오름차순)

-- 기본 구조
SELECT 속성 FROM 테이블명;

-- 개발팀 직원 정보 조회(사번, 이름, 직책만)
SELECT 사번, 이름, 직책 FROM 사원정보 
WHERE 부서='개발팀';

-- 개발팀이나 디자인팀 조회
SELECT * FROM 사원정보 
WHERE 부서='개발팀' OR 부서='디자인팀';

-- 개발팀이나 디자인팀이면서 이씨만 조회
SELECT * FROM 사원정보 
WHERE (부서='개발팀' OR 부서='디자인팀') AND 이름 LIKE '이%';

-- 개발팀이나 디자인팀이면서 이씨로 시작하고 이름이 두 글자만 조회
SELECT * FROM 사원정보 
WHERE (부서='개발팀' OR 부서='디자인팀') AND 이름 LIKE '이_';

-- 개발팀 중 입사일이 NULL이 아닌 직원 조회
SELECT * FROM 사원정보 
WHERE 부서='개발팀' AND 입사일 IS NOT NULL;

-- 입사일이 2010년 이후 직원 조회
SELECT * FROM 사원정보 
WHERE 입사일 >= '2010';

-- 입사일이 2010년부터 2020년까지 출력
SELECT * FROM 사원정보 
WHERE 입사일 >= '2010' AND 입사일 <= '2020';
SELECT * FROM 사원정보 
WHERE 입사일 BETWEEN 2010 AND 2020;

-- 사원 정보에서 부서만 중복 없이 출력
SELECT DISTINCT 부서 FROM 사원정보;

-- 부서별 인원수 출력
SELECT 부서, COUNT(*) AS 인원수 FROM 사원정보 GROUP BY 부서;

-- 부서별 인원수 중 2명 이상만 출력
SELECT 부서, COUNT(*) AS 인원수 FROM 사원정보 GROUP BY 부서 HAVING COUNT(*)>=2;

-- 모든 직원 정보 조회 나이별 내림차순
SELECT * FROM 사원정보 ORDER BY 나이 DESC;

(4)  UPDATE

• 기존 데이터를 갱신하는 명령어로, WHERE 절을 생략했을 경우 모든 행 갱신

-- 기본 구조
UPDATE 테이블명 SET 바꿀 속성;

-- 개발팀의 부서명을 개발지원팀으로 변경
UPDATE 사원정보
SET 부서 = '개발지원팀'
WHERE 부서 = '개발팀';

(5)  DELETE

• 테이블에서 데이터를 삭제하는 명령어로, WHERE 절을 생략했을 경우 모든 행이 삭제

-- 개발팀 직원만 삭제
DELETE FROM 사원정보
WHERE 부서 = '개발팀';

-- 디자인팀 배움 삭제
DELETE FROM 사원정보
WHERE 부서 = '디자인팀' AND 이름 = '배움';

 

 

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

(1) 데이터 제어어의 개념

• 데이터베이스에 접근하거나 객체에 대한 권한을 주고 빼앗는 역할을 하는 언어

(2) GRANT

• 데이터베이스 사용자에게 권한을 부여하는 명령
• WITH GRANT OPTION : 지정된 권한을 다른 유저에게 부여할 수 있도록 함

-- 기본 구조
GRANT [권한] ON [객체명] TO [사용자 계정] [WITH GRANT OPTION];

-- PEAZH 사용자에게 사원정보 SELECT 권한 부여
GRANT SELECT ON 사원정보 TO PEAZH;

(3)  REVOKE

• 데이터베이스 사용자로부터 권한을 회수하는 명령
• CASCADE : 사용자가 부여한 모든 사용자 권한을 같이 회수

-- 기본 구조
REVOKE [권한] ON [객체명] FROM [사용자 계정] [CASCADE];

-- PEAZH 사용자에게 사원정보 SELECT 권한 회수
REVOKE SELECT ON 사원정보 FROM PEAZH CASCADE;

 

 

5. TCL( Transaction Control Language) - 트랜젝션 제어어

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

• 트랜잭션을 제어하는 데 사용되는 명령어들로, 작업 단위를 안전하게 관리하기 위해 사용

(2) COMMIT

• 트랜잭션이 정상적으로 처리된 경우, 그 결과를 데이터베이스에 반영하는 명령어
• 한번 COMMIT을 수행하면, 이전 상태로 돌아갈 수 없음

(3)  ROLLBACK

• 트랜잭션 처리 중 오류가 발생했거나 사용자가 명시적으로 취소할 경우, 변경사항을 되돌리고 데이터를 이전 상태로 복구하는 명령어

(4)  SAVEPOINT

• 트랜잭션 중 특정 지점에 대해 저장점을 생성하는 명령어

 

 

참고자료

흥달쌤,  '흥!나게 합격 달성! 2024 정보처리기사 실기 클래스'

1억뷰 N잡