4-2강. SQL 활용-기본 SQL 작성(2)

과목4. 데이터베이스 구축, 2강. SQL 활용-기본 SQL작성(2)

 

[ 목차 ]

1. SQL

2. SQL 특징

3. SQL 종류

4. SQL 문법 전제조건

5. SQL 문법 기본규칙6. 데이터 검색

6. 데이터 검색

7. 관계 대수 종류

8. 와일드카드

9. 두 개 이상의 조건식 연결

10. 수치, 날짜 데이터 검색

11. 특정 칼럼의 값이 해당하는지 검색

12. 산술연산자를 이용한 검색

13. NULL

14. 검색 결과의 정렬

15. INSERT 문

16. 서브쿼리

17. DELETE 문

18. UPDATE 문

19. DCL

20. DCL의 종류

21. 권한 허가

22. 사용자 등급 부여

23. 권한 취소

24. 역할

25. 시스템 권한

26. 객체 권한

27. PUBLIC 키워드

28. 객체 및 시스템 권한 부여/회수 예시

 

1. SQL

1) 관계 대수와 관계 해석을 기반으로 집단함수, 그룹화, 갱신 연산 등을 추가하여 개발한 데이터베이스 질의 언어

2) ANSI(미국 표준 기구)에서 SQL을 관계형 데이터베이스의 표준 질의 언어로 채택

 

2. SQL 특징

1) 비절차적 언어(선언적 언어) : 사용자가 원하는 바만 명시하며 원하는 것을 처리하는 방법은 명시하지 않음

2) 대화식 인터페이스 또는 호스트 언어에 내장된 형태로 사용 가능

  - 대화식 SQL : 사용자가 명령문을 명령 창에 입력하고 실행 결과를 확인

  - 내포된 SQL : 프로그래밍 언어에 SQL 문을 내포해서 응용 프로그램이 실행될 때 SQL문이 작동하는 방식

절차적 언어 : 프로그래밍 언어와 같이 기본적인 절차가 있는 언어

 

3. SQL 종류

1) 사용 목적과 영향에 따라 정의어 DDL, 조작어 DML, 제어어 DCL로 구분

2) 데이터를 검색하는 SELECT문은 질의어DQL로 분류하거나 DML에 포함

3) 상용관계 DBMS마다 지원하는 SQL 기능에 다소 차이가 존재

 

4. SQL 문법 전제조건

1) 자연어에 가까워 배우기 쉬움

2) 데이터를 어떻게 가져올 것인가 대신에 어떤 데이터를 원하는지만 기술해주면 되기 때문에 작업이 단순

3) 명령어나 문법은 표준화되어 있어 대부분 모든 관계형 데이터베이스 제품에서 공통으로 사용 가능

 

5. SQL 문법 기본규칙

1) SQL 명령어와 테이블명, 칼럼명과 같은 객체 이름은 대소문자를 구분하지 않으나 데이터베이스에 저장된 값은 대소문자를 구분

2) SQL 명령문은 세미콜론으로 종료

 

6. 데이터 검색

1) SELECT문 형식 : 관계대수의 셀렉션, 프로젝션, 조인, 카티션 곱 등을 결합한 데이터 조회 명령

2) 모든 데이터를 검색하고자 할 때 칼럼명 대신 * 기입

3) 중복된 데이터를 제거하고자 할 때 DISTINCT 칼럼명을 입력

4) 특정 조건을 만족하는 튜플을 검색하고자 할 대 WHERE 절을 사용

관계대수
1) 절차적 언어 : 원하는 정보를 어떻게 유도하는가를 연산자와 연산규칙을 이용하여 기술
2) 분류 : 순수 관계 연산자, 일반 집합 연산자
3) SQL의 이론적인 기초

관계해석
1) 비절차적 언어 : 원하는 정보가 무엇이라는 것만 정의
2) 분류 : 튜플 관계 해석, 도메인 관계 해석

+
1) 기본적으로 관계 해석과 대수는 관계 데이터베이스를 처리하는 기능과 능력면에서 동등
2) 관계 해석으로 표현한 식은 관계 수로 표현 가능

7. 관계 대수 종류

1) SELECT : 릴레이션에서 주어진 조건을 만족하는 튜플들을 검색하는 것으로 기호는 그리스 문자의 시그마를 이용

2) PROJECT : 릴레이션에서 주어진 조건을 만족하는 속성들을 검색하는 것으로 그시루 문자의 파이를 이용

3) JOIN

4) DEVISION

5) 합집합

6) 차집합

 

8. 와일드카드

1) % : 문자가 없거나 하나 이상의 어떤 문자.

   SELECT CALLUM FROM TABLE WHERE CALLUM LIKE *%이%; ->이가 포함된 데이터 검색

2) _ : 하나의 어떤 문자. 

   SELECT CALLUM FROM TABLE WHERE CALLUM LIKE 이_ _; -> 이로 시작하는 세문자로 이루어진 데이터 검색

 

9. 두 개 이상의 조건식 연결

1) AND : A AND B로 연결한 조건식 A와 B가 모두 참일 경우 연산 결과를 만족, 즉 두 조건 A, B를 만족하는 데이터 검색

2) OR : A OR B로 연결한 조건식 중에서 하나 이상이 참일 경우 연산 결과를 만족, 즉 두 조건 A, B 중에서 하나라도 만족하는 데이터 검색

3) NOT : 주어진 조건을 만족하지 않는 데이터 검색

 

10. 수치, 날짜 데이터 검색

1) BETWEEN A AND B : 특정 칼럼의 값이 A 이상 B 이하에 포함되는 데이터를 검색

2) WHERE CALLUM BETWEEN 200 AND 100;   =   WHERE CALLUM >=100 AND CALLUM <=200;

 

11. 특정 칼럼의 값이 해당하는지 검색

1)  C IN (list) : C 칼럼의 값이 list에 포함된 경우 해당 레코드를 출력

2) 특정 칼럼의 값이 검색하고자 하는 값 리스트에 해당하는지 검사한 후 리스트에 해당하는 데이터를 검색하고자 할 때

  ex. 부서번호가 1번 또는 3번에 해당하는 사원의 모든 정보 검색

       SELECT * FROM EMPLOYEE WHERE NO IN (1, 3); 

                                                   = NO = 1 or NO = 3;

 

12. 산술연산자를 이용한 검색

1) + - * / 를 이용해 검색 결과에 연산을 수행한 결과를 확인 가능

2) 산술연산식을 기술하는 방식과 연산 우선순위는 일반적인 연산자 서술양식과 같음

3) 기존 칼럼에 산술연산자를 이용해 새로운 칼럼을 구했을 때 AS를 이용하여 칼럼명 부여 가능

 

13. NULL

1) 널 값에 산술연산을 적용한 결과도 널

2) COUNT(*)를 제외한 집단 함수들은 NULL값을 무시하고 계산을 수행

3) 칼럼 값이 NULL 값인지 확인하기 위해서는 비교 연산자 =가 아닌 IN NULL 또는 IN NOT NULL 연산을 사용

 

14. 검색 결과의 정렬

1) ORDER BY절을 사용

2) SELECT한 결과는 기본적으로 튜플이 삽입된 순서대로 조회

3) 검색 결과로 출력되는 레코드들을 특정 기준에 맞춰 정렬하고자 할 경우 ORDER BY 절을 사용

4) ORDER BY 절에 정렬 기준이 되는 칼럼과 정령방법(ASC, DESC)을 명시

5) 오름차순은 ASC(기본값), 내림차순은 DESC

 

15. INSERT 문

1) 테이블에 새로운 레코드를 한 번에 하나씩 삽입하고자 하면 기본 INSERT문을 사용

2) INTO 절에 칼럼명을 생략할 경우 테이블 생성 시 정의한 순서대로 값을 입력

3) INSERT INTO 테이블명(칼럼명1, 칼럼명2, ...) VALUES(값1, 값2, ...);

 

16. 서브쿼리

1) 테이블에 새로운 레코드들을 한 번에 여러 개씩 삽입하고자 하면 INSERT문에 서브쿼리를 이용

2) VALUES 절 대신 SELECT절을 기술

3) SELECT 절의 칼럼의 개수와 타입이 INSERT INTO절 칼럼의 개수와 타입과 일치해야 함

4) INSERT INTO 테이블명(칼럼명1, 칼럼명2, ...) SELECT절;

 

17. DELETE 문

1) 한 테이블로부터 한 개 이상의 레코드(튜플)을 삭제할 때 사용

2) where 조건 생략 시 모든 튜플이 삭제

3) DELETE FROM 테이블명 WHERE 조건;

 

18. UPDATE 문

1) 한 테이블에 들어 있는 레코드(튜플)들의 칼럼값을 수정할 때 사용

2) where 조건 생략 시 모든 튜플이 수정

2) UPDATE 테이블명 SET 칼럼명 = 값or식 where 조건

 

19. DCL

1) 데이터베이스의 규정이나 기법을 정의하고 제어하는 언어

2) 사용자 권한을 부여하거나 취소하는 명려어와 트랜잭션을 제어하는 명령어를 포함

3) 트랜잭션 제어 명령을 이용해 데이터베이스의 무결성과 보안, 회복, 동시성 제어를 수행

 

20. DCL의 종류

1) COMMIT : 수행된 결과를 실제 물리적 디스크로 저장

2) ROLLBACK : 명령 수행 실패를 의미하며 수행된 결과를 원복

3) SAVEPOINT : 저장점 지정으로 지정된 저장점부터 현재까지 일부만 롤백 가능

4) GRANT : 데이터베이스 사용자에게 사용 권한 부여

5) REVOKE 데이터베이스 사용자에게 부여된 사용 권한 취소

 

21. 권한 허가

1) 객체의 생성자는 객체에 대한 모든 권한으 보유

2) 생성자는 자신이 소유한 임의의 객체에 대한 특정 권한을 GRANT 문을 사용해 다른 사용자, 역할에게 허가 가능

3) GRANT 권한[칼럼 리스트] ON 객체 TO {사용자|역할|PUBLIC} [WITH GRANT OPTION];

4) 권한 : SELECT, INSERT, DELECT, UPDATE, REFERENCES 권한 나열

5) WITH GRANT OPTION : 부여받은 권한을 다른 사용자에게 허가 가능

6) GRANT OPTION FOR : WITH GRANT OPTION을 취소

   REVOKE GRANT OPTION FOR ALL ON 객체 FROM 사용자

 

22. 사용자 등급 부여

1) GRANT 등급 TO 사용자;

2) 등급 : DBA-데이터베이스 관리자, RESOURCE-데이터베이스 및 테이블 생성 가능자, CONNECT:접속/검색 사용자

 

23. 권한 취소

1) 권한을 취소하면 취소당한 사용자가 CASECADE를 통해서 다른 사용자에게 허가했던 권한들도 연쇄적으로 취소

2) 기본적으로 권한을 허가했던 사람만 그 권한을 취소 가능

3) REVOKE {권한 리스트|ALL} ON 객체 FROM {사용자|역할|PUBLIC} CASECASE;

4) CASECADE를 통해 참조무결성 회피 가능

 

24. 역할

1) 여러 사용자들에 대한 권한 관리를 단순화하기 위해 사용

2) 사용자에게 허가할 수 있는 연관된 권한들의 그룹을 의미

3) 각 사용자는 여러 역할에 속할 수 잇으며 여러 사용자가 같은 역할을 가질 수 있음

4) 같은 권한들의 집합을 여러 사용자에게 허가하는 대신 이 권한들을 역할에게 허가하고 역할을 각 사용자에게 허가하는 것이 편리

5) GRANT CREATE TABLE ALTER TABLE DROP TABLE TO ROLE이름

   GRANT ROLE이름 TO 사용자1;

   GRANT ROLE이름 TO 사용자2;

   REVOKE DROP TABLE FROM ROLE이름;

 

25. 시스템 권한

1) 사용자가 데이터베이스에서 특정 작업을 수행할 수 있도록 하는 권한

 

26. 객체 권한

1) 사용자가 특정 객체에 대해 특정 연산을 수행할 수 있도록 함

2) 객체의 소유자는 객체에 대한 모든 권한을 보유하며 자신의 객체에 대한 권한을 다른 사용자, 역할에 허가 가능

 

27. PUBLIC 키워드

1) PUBLIC 키워드를 사용해 권한을 허가하면 모든 사용자에게 권한을 부여

2) GRANT 권한 ON 객체 TO PUBLIC;

3) 시스템 권한의 허가는 데이터베이스 관리자가 GRANT 문을 사용하여 사용자에게 시스템 권한들을 허가

4) WITH ADMN OPTION을 사용해 시스템 권한을 허가하면 권한을 받은 사용자가 다시 이 권한을 다른 사람에게 허가할 수 있음

5) 시스템 권한을 취소할 때는 연쇄적인 취소가 일어나지 않음

 

28. 객체 및 시스템 권한 부여/회수 예시