3. SQL

SQL Functions

DDL: Data Definition Language

• CREATE
• DROP
• ALTER

DML: Data Manipulation Language

• SELECT
• INSERT
• UPDATE
• DELETE

DCL: Data Control Language

• GRANT
• REVOKE
• COMMIT
• ROLLBACK
• TRIGGER
• ASSERTION

기본적인 SQL SELECT 문법

select ~

from ~

where ~

order by ~ asc;

 

특정 필드만 출력하기

select sid, sname 

from student;

 

테이블의 모든 열 출력하기

select *

from student;

 

DISTINCT를 이용하여 중복 제거하기

select distinct deptno

from student;

 

특정 필드 이름을 변경하여 출력하기

select deptno department

from student;

(deptno를 department로 이름 변경)

 

열 결합하여 출력하기

select sid||sname student

from student;

(학번과 이름을 합치고 열 이름을 student로 출력)

 

정렬 출력하기

select sid,sname,deptno,grade

from student

order by deptno;

(기본설정은 오름차순임)

 

내림차순 출력하기

select sid,sname,deptno,grade

from student

order by deptno desc;

 

정렬 기준 여러개 설정하기

select sid,sname,deptno,grade

from student

order by deptno, grade desc;

(학과 순으로 우선 정렬 후, 학점이 높은 순서로 다음 정렬)

 

검색 조건 부여

=

<> (Not equal, !=도 가능)

>

<

>=

<=

Between A and B

A in (B,C) : A가 (B,C) 집합의 원소인지 검사

IS (NOT) NULL

LIKE

 

NOT

AND
OR

---

LIKE '__abc%'

---

UNION

두개의 질의 결과를 합집합(중복된 데이터는 제거)

 

INTERSECT

두개의 질의 결과를 교집합

 

MINUS

앞의 질의 결과에서 뒤의 질의 결과를 차집합

---

NULL에 대한 연산들

10 + NULL 

10 * NULL 등 산술연산 => NULL

 

10 = NULL

10 > NULL

10 <> NULL 등 숫자와 비교연산 => FALSE

 

---

문자 함수

• LOWER, UPPER
• INITCAP
• LPAD, RPAD
• SUBSTR
• INSTR
• LTRIM, RTRIM
• LENGTH
• REPLACE

1. LOWER, UPPER
  : LOWER 함수는 문자열을 모두 소문자로 변환하고, UPPER 함수는 문자열을 모두 대문자로 변환합니다.

     SELECT LOWER('Hello World') AS lower_case FROM dual;
     -- 결과: hello world

     SELECT UPPER('Hello World') AS upper_case FROM dual;
     -- 결과: HELLO WORLD

2. INITCAP
: INITCAP 함수는 문자열의 첫 글자를 대문자로 변환합니다.

     SELECT INITCAP('hello world') AS init_cap FROM dual;
     -- 결과: Hello World

3. LPAD, RPAD
: LPAD 함수는 문자열의 왼쪽에 지정된 문자열을 추가하여 지정된 길이로 만듭니다. RPAD 함수는 문자열의 오른쪽에 지정된 문자열을 추가하여 지정된 길이로 만듭니다.

     SELECT LPAD('123', 5, '0') AS lpad_result FROM dual;
     -- 결과: 00123

     SELECT RPAD('123', 5, '0') AS rpad_result FROM dual;
     -- 결과: 12300

4. SUBSTR
: SUBSTR 함수는 문자열에서 지정된 위치부터 지정된 개수의 문자를 반환합니다.

     SELECT SUBSTR('Hello World', 7, 5) AS sub_result FROM dual;
     -- 결과: World

5. INSTR
: INSTR 함수는 문자열에서 지정된 부분 문자열의 시작 위치를 반환합니다.

     SELECT INSTR('Hello World', 'lo') AS instr_result FROM dual;
     -- 결과: 4

6. LTRIM, RTRIM
: LTRIM 함수는 문자열의 왼쪽에서 지정된 문자를 제거합니다. RTRIM 함수는 문자열의 오른쪽에서 지정된 문자를 제거합니다.

     SELECT LTRIM('   Hello World   ') AS ltrim_result FROM dual;
     -- 결과: 'Hello World   '

     SELECT RTRIM('   Hello World   ') AS rtrim_result FROM dual;
     -- 결과: '   Hello World'

7. LENGTH
: LENGTH 함수는 문자열의 길이를 반환합니다.

     SELECT LENGTH('Hello World') AS length_result FROM dual;
     -- 결과: 11

8. REPLACE
: REPLACE 함수는 문자열에서 지정된 부분 문자열을 다른 문자열로 대체합니다.

     SELECT REPLACE('Hello World', 'Hello', 'Hi') AS replace_result FROM dual;
     -- 결과: Hi World

 

---

숫자 함수

• ROUND, CEIL, FLOOR
• TRUNC
• POWER
• SQRT
• SIGN
• ABS
• MOD

1. ROUND
: ROUND 함수는 숫자를 반올림합니다.

     SELECT ROUND(15.75) AS rounded_number FROM dual;
     -- 결과: 16

2. CEIL (CEILING)
: CEIL 함수는 숫자를 올림합니다.

     SELECT CEIL(15.25) AS ceiling_number FROM dual;
     -- 결과: 16

3. FLOOR
: FLOOR 함수는 숫자를 내림합니다.

     SELECT FLOOR(15.75) AS floored_number FROM dual;
     -- 결과: 15

4. TRUNC
: TRUNC 함수는 숫자를 특정 자릿수까지 잘라내어 반환합니다.

     SELECT TRUNC(15.753, 1) AS truncated_number FROM dual;
     -- 결과: 15.7

5. POWER
: POWER 함수는 숫자의 거듭제곱 값을 계산합니다.

     SELECT POWER(2, 3) AS powered_number FROM dual;
     -- 결과: 8

6. SQRT
: SQRT 함수는 숫자의 제곱근을 반환합니다.

     SELECT SQRT(25) AS square_root FROM dual;
     -- 결과: 5

7. SIGN
: SIGN 함수는 숫자의 부호를 반환합니다.

     SELECT SIGN(-15) AS sign_result FROM dual;
     -- 결과: -1

8. ABS
: ABS 함수는 숫자의 절댓값을 반환합니다.

     SELECT ABS(-15) AS absolute_value FROM dual;
     -- 결과: 15

9. MOD
: MOD 함수는 나눗셈의 나머지를 반환합니다.

     SELECT MOD(10, 3) AS remainder FROM dual;
     -- 결과: 1

---

날짜 함수

SYSDATE

Date + Number

Date - Date

 

Months_Between(date1,date2)

Add_Months(date,n)

Next_Day(date,char), Last_Day(date)

1. **SYSDATE**:
: SYSDATE 함수는 현재 날짜와 시간을 반환합니다.
     SELECT SYSDATE FROM dual;
     -- 결과: 2023-10-05 14:30:45 (현재 날짜와 시간)


2. **Date + Number**:
 날짜에 숫자를 더하여 새로운 날짜를 계산합니다.

     SELECT SYSDATE + 7 FROM dual;
     -- 결과: 2023-10-12 (현재 날짜에서 7일 후)


3. **Date - Date**:
두 날짜 간의 차이를 계산합니다.

     SELECT TO_DATE('2023-10-12', 'YYYY-MM-DD') - TO_DATE('2023-10-05', 'YYYY-MM-DD') AS date_difference FROM dual;
     -- 결과: 7 (날짜 간의 일 수 차이)


4. **Months_Between(date1, date2)**:
 두 날짜 간의 개월 수를 계산합니다.

     SELECT Months_Between(TO_DATE('2023-11-15', 'YYYY-MM-DD'), TO_DATE('2023-10-05', 'YYYY-MM-DD')) AS months_diff FROM dual;
     -- 결과: 1.352 (날짜 간의 개월 수 차이)

5. **Add_Months(date, n)**:
 날짜에 월을 더하여 새로운 날짜를 계산합니다.

     SELECT Add_Months(TO_DATE('2023-10-05', 'YYYY-MM-DD'), 2) AS new_date FROM dual;
     -- 결과: 2023-12-05 (현재 날짜에서 2개월 후)

6. **Next_Day(date, char), Last_Day(date)**:
   - **Next_Day 설명**: 주어진 날짜 이후로 특정 요일로 가장 가까운 날짜를 반환합니다.
   - **Last_Day 설명**: 주어진 날짜의 해당 월의 마지막 날을 반환합니다.

     SELECT Next_Day(TO_DATE('2023-10-05', 'YYYY-MM-DD'), 'FRIDAY') AS next_friday,
            Last_Day(TO_DATE('2023-10-05', 'YYYY-MM-DD')) AS last_day_of_month
     FROM dual;
     -- 결과: 2023-10-07 (다음 주 금요일), 2023-10-31 (해당 월의 마지막 날)

 

날짜함수 응용

select pid,pname,hiredate, trunc((sysdate-hiredate)/365) 근무연수
from professor
where deptno = 20;

 

기타 함수

TO_CHAR

TO_DATE

DECODE

 

1. **TO_CHAR**:
: TO_CHAR 함수는 날짜나 숫자를 문자열로 변환합니다.

     SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS formatted_date FROM dual;
     -- 결과: '2023-10-05' (현재 날짜를 지정한 형식으로 변환)


2. **TO_DATE**:
TO_DATE 함수는 문자열을 날짜로 변환합니다.

     SELECT TO_DATE('2023-10-05', 'YYYY-MM-DD') AS date_value FROM dual;
     -- 결과: 2023-10-05 (지정한 문자열을 날짜로 변환)


3. **DECODE**:
DECODE 함수는 조건에 따라 다른 값을 반환합니다. 일종의 조건문 역할을 합니다.

DECODE(필드, 조건1, 조건1의 참, 조건2, 조건2의 참, ... , 거짓)

     SELECT DECODE(2, 1, 'One', 2, 'Two', 'Other') AS result FROM dual;
     -- 결과: 'Two' (2에 대응하는 값인 'Two' 반환)

---

집계 함수(Aggregate Functions)

• COUNT
• SUM
• AVG
• MAX
• MIN

 

1. **COUNT**:
 COUNT 함수는 특정 열의 레코드 수를 계산합니다.

     SELECT COUNT(*) AS total_records FROM your_table;
     -- 결과: 전체 레코드 수 반환


2. **SUM**:
 SUM 함수는 특정 열의 숫자 값의 합계를 계산합니다.

     SELECT SUM(salary) AS total_salary FROM employees;
     -- 결과: 급여의 총 합계 반환


3. **AVG**:
AVG 함수는 특정 열의 숫자 값의 평균을 계산합니다.

     SELECT AVG(age) AS average_age FROM customers;
     -- 결과: 고객들의 평균 나이 반환


4. **MAX**:
 MAX 함수는 특정 열에서 가장 큰 값을 찾습니다.

     SELECT MAX(score) AS highest_score FROM students;
     -- 결과: 학생들 중 최고 점수 반환


5. **MIN**:
 MIN 함수는 특정 열에서 가장 작은 값을 찾습니다.

     SELECT MIN(price) AS lowest_price FROM products;
     -- 결과: 상품 중 가장 낮은 가격 반환


Group 적용
GROUP BY 구문을 사용하여 특정 열을 기준으로 그룹화하고, 각 그룹에 대한 집계 함수를 계산할 수 있습니다.

     SELECT department, AVG(salary) AS avg_salary
     FROM employees
     GROUP BY department;
     -- 각 부서별로 평균 급여를 계산

 

• Where절을 사용하는 경우 Group by 절 앞에 선언해야한다.
• Select절에 나올 수 있는 필드는 Group by의 필드와 집계 함수가 적용되는 필드로 국한된다.

HAVING 절

GROUP BY 절과 함께 사용되며, 그룹화된 결과에 대한 조건을 지정할 때 사용됩니다. 즉, HAVING 절은 집계 함수를 사용한 조건을 걸 때 활용됩니다.

일반적으로 WHERE 절은 행 단위로 조건을 걸 때 사용되고, HAVING 절은 그룹 단위로 조건을 걸 때 사용됩니다.

예를 들어, 아래의 쿼리는 부서별 평균 급여가 50000 이상인 부서들을 찾습니다.

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) >= 50000;

위 쿼리에서 먼저 GROUP BY 절로 부서별로 그룹화가 이루어지고, 각 그룹에 대한 평균 급여가 계산됩니다. 그리고 HAVING 절에서 평균 급여가 50000 이상인 부서들만 선택됩니다.

---

2.2 Select Subquery

SELECT 서브쿼리는 하나의 SQL 문 안에서 다른 SQL 문이나 쿼리를 포함하는 것입니다. 이를 통해 서브쿼리의 결과를 기반으로 상위 쿼리를 실행할 수 있습니다.

- SELECT 서브쿼리는 주로 WHERE, FROM, HAVING 등의 절에서 사용됩니다.
- 서브쿼리의 결과는 주 쿼리에서 조건이나 비교에 활용됩니다.
- 서브쿼리는 괄호 `( )` 내부에 작성되며, 결과를 반환하는데 사용됩니다.

SELECT name 
FROM students 
WHERE grade = (
    SELECT MAX(grade) 
    FROM students
);

 

>any

>all

exist 

등 활용.

 

---

2.3. Join

SELECT sid, sname, dname
FROM student, department
WHERE student.deptno = department.deptno;

조인 조건을 WHERE절에 기술.

 

SELECT d.deptno, dname, pname
FROM department d inner join professor p ON d.deptno = p.deptno
WHERE d.deptno <> 20;

 

OUTER JOIN

SELECT d.deptno, dname, pname
FROM department d, professor p
WHERE d.deptno!=20 and d.deptno=p.deptno(+);

SELECT d.deptno, dname, pname
FROM department d {RIGHT|LEFT|FULL}OUTER JOIN professor p ON d.deptno=p.deptno
WHERE d.deptno!=20;

---

2.4 Set Operations

• UNION
• INTERSECT
• MINUS

1. **UNION**
`UNION` 연산자는 두 개의 쿼리 결과를 합쳐서 중복된 행을 제거합니다.

     SELECT column1 FROM table1
     UNION
     SELECT column1 FROM table2;

2. **INTERSECT**
 `INTERSECT` 연산자는 두 개의 쿼리 결과에서 공통된 행만을 선택합니다.

     SELECT column1 FROM table1
     INTERSECT
     SELECT column1 FROM table2;


3. **MINUS (또는 EXCEPT)**
`MINUS` 연산자는 첫 번째 쿼리 결과에서 두 번째 쿼리 결과를 뺍니다. `EXCEPT`는 동일한 연산을 의미합니다.

     SELECT column1 FROM table1
     MINUS
     SELECT column1 FROM table2;

    

---

3. 데이터베이스 수정

• INSERT INTO
• UPDATE
• DELETE FROM

1. INSERT INTO
`INSERT INTO` 명령어는 테이블에 새로운 레코드(행)을 추가합니다.

     INSERT INTO customers (first_name, last_name, email)
     VALUES ('John', 'Doe', 'john.doe@example.com');
     
     INSERT INTO department(deptno,dname)
     VALUES (70,'aaa');
     
     INSERT INTO department
     VALUES (80,'bbb',null,null);

필드명을 미리 주지 않으면 values에 모든 필드에 관해 값을 작성해야한다.

시간과 날짜 정보를 삽입할 경우 TO_DATE()함수를 사용해야한다.

INSERT INTO d10student(stno,name,address,grade)
SELECT sid,sname,addr,grade
FROM student
WHERE deptno = 10;

다른 테이블의 값을 그대로 넣을 수 있다.(d10student테이블은 미리 정의되어있어야함)

 

2. UPDATE - SET
`UPDATE` 명령어는 테이블 내의 기존 레코드를 업데이트합니다.

     UPDATE customers
     SET email = 'johndoe@gmail.com'
     WHERE id = 1;

UPDATE department
SET budget = (SELECT max(budget) FROM department)
WHERE deptno in (SELECT deptno FROM professor WEHRE major = 'Database');

subquery를 활용하는 예제.

3. DELETE FROM
`DELETE FROM` 명령어는 테이블에서 레코드를 삭제합니다.

     DELETE FROM customers
     WHERE id = 1;

이때 WHERE절이 없으면 모든 데이터가 삭제된다.

DELETE FROM department
WHERE deptno NOT IN (SELECT DISTINCT deptno FROM student);

학생이 없는 학과를 삭제하는 예제.

 

---

4. 데이터 정의 기능

• CREATE
• DROP
• ALTER

1. CREATE
`CREATE` 명령어는 데이터베이스 객체를 생성합니다. 이 객체는 테이블, 뷰, 인덱스 등이 될 수 있습니다.

CREATE TABLE table_name
(column_name	type(size) [NULL/NOT NULL],
...)

타입 명은 DBMS마다 상이하다.

     CREATE TABLE customers (
         id INT PRIMARY KEY,
         first_name VARCHAR(50),
         last_name VARCHAR(50),
         email VARCHAR(100)
     );

 

다른 테이블을 이용하여 테이블 생성하고 초기값 넣기

CREATE TABLE DEPT30(id,name,gender,grade)
AS
SELECT sid,sname,gen,grade
FROM student
WHERE deptno=30;

 

인덱스(색인) 생성하기

CREATE [UNIQUE] INDEX index ON table(column [,column]...)

예시

CREATE Unique Index st_idx ON student(sid)
CREATE Index name_idx ON student(sname,gen)

 

Primary key와 unique 애트리뷰트에 대해서는 기본적으로 unique index가 생성된다.

색인 생성시 select 연산이 빨라지지만, 삽입,수정,삭제 연산이 느려지며 색인 저장을 위한 저장공간이 필요하다는 단점이 있다.


2. DROP
`DROP` 명령어는 데이터베이스 객체를 삭제합니다. 이는 테이블, 뷰, 인덱스 등을 포함합니다.

     DROP TABLE customers;


3. ALTER
`ALTER` 명령어는 데이터베이스 객체를 수정합니다. 이는 테이블에 새로운 열을 추가하거나, 기존 열의 속성을 변경할 때 사용됩니다.

     ALTER TABLE customers
     ADD COLUMN phone_number VARCHAR(15);

ALTER는 DBMS마다 다르지만 아래의 경우를 할 수 없을 수 있다.

• 속성 type 변경
• 열(속성) 길이 줄이기
• NULL Column 변경
• 열(속성) 순서 변경
• 열(속성) 삭제

ALTER는 가급적 하지않는것이 좋음.

---

5. 데이터 보안

• GRANT
• REVOKE
• VIEW

1. GRANT
 GRANT 문은 데이터베이스 사용자에게 특정 권한을 부여하는 명령입니다. 이를 통해 특정 사용자가 데이터베이스 객체를 조작하거나 액세스할 수 있는 권한을 설정할 수 있습니다.

GRANT SELECT, INSERT ON employees TO user1;

GRANT UPDATE(Grade, DdeptNO) ON student TO ADAMS;

GRANT INSERT,UPDATE ON student TO ADAMS, JONES;

GRANT ALL ON student TO ADAMS;

GRANT SELECT ON student TO ADAMS WITH GRANT OPTION;
-- ADAMS가 다른사람에게 권한을 줄 수 있음

GRANT SELECT ON student TO PUBLIC;
-- 모든사람에게 open

권한부여 권한까지 줄 수 있음.

2. REVOKE
REVOKE 문은 이전에 GRANT된 권한을 취소하고, 사용자 또는 역할로부터 특정 권한을 회수하는 명령입니다.

     REVOKE INSERT ON employees FROM user1;
     
     REVOKE ALL ON student FROM ADAMS;
     
     REVOKE ALL ON student FROM ADAMS CASCADE;
     --ADAMS가 준 권한들도 다 회수

CASCADE를 주의

3. VIEW
VIEW는 하나 이상의 테이블에서 선택적으로 특정 열을 선택하거나 조작하여 새로운 가상 테이블을 생성하는 논리적인 데이터베이스 객체입니다. VIEW는 실제 데이터를 가지고 있지 않고, 쿼리 실행 시에 동적으로 생성됩니다.

 

VIEW의 장점

1. 접근권한, 보안을 더 세세하게 적용 가능
2. 쿼리를 쉽게 만든다.
3. 논리적인 데이터독립성을 지원한다.

     CREATE VIEW employee_view AS
     SELECT emp_id, emp_name, emp_salary
     FROM employees
     WHERE emp_department = 'IT';

SELECT * FROM user_views

DROP VIEW view-name

 

VIEW에서 제약

 

DELETE:

- Join

- Group By

- DISTINCT

이 적용된 경우

 

UPDATE:

- 속성에 연산이 적용된 경우

 

INSERT:

- view에 NOT NULL columns이 모두 선택되지 않은 경우

- deptno=30 조건이 적용된 view에서 deptno=40인 레코드를 추가하면 추가는 되지만 view에서 해당 레코드를 볼 수 없다.

=> CREATE VIEW 할 때 마지막에 'with check option'을 추가하면 where절을 만족하지 않는 insert와 update를 막을 수 있다.

CREATE VIEW D30STUDENT(ID,NAME,DEPT,GRADE)
AS
SELECT sid,sname,deptno,grade
FROM student
WHERE deptno=30
with check option;

 

보다 강력하게 제한하는

with read only 옵션도 있다.