SUMMARY OF DATABASE LANGUAGE

 

DDL (DATA DEFINITION LANGUAGE)

 

• CREATE
  • TABLE [table_name]([column_name][data_type],...); // 테이블 생성
    
    
  • INDEX [index_name]
    ON [table_name]([column_name], ...); // 인덱스 생성
    
    
  • VIEW[view_name]
    AS [view_Query]; // 뷰 생성
    
    
  • DATABASE // 데이터베이스 생성
  • SEQUENCE // 시퀀스 생성
  • FUNCTION // 함수 생성
  • PROCEDURE // 프로시저 생성 
    
    
• ALTER
  • TABLE[table_name] 
    ADD [column_name][data_type]; // 새로운 컬럼 추가
    
    
  • TABLE[table_name]
    DROP COLUMN [column_name]; // 컬럼 삭제
    
    
  • TABLE[table_name]
    MODIFY [column_name][data_type]; // 컬럼 데이터 타입 변경
    
    
  • TABLE[table_name]
    RENAME COLUMN [old_column_name] TO [new_column_name]; // 컬럼 이름 변경
    
    
  • INDEX... // 인덱스 수정
  • VIEW... // 뷰 수정
  • DATABASE... // 데이터 베이스 수정
    
    
• DROP
  • TABLE [table_name]; // 테이블 삭제
  • VIEW [view_name]; // 뷰 삭제
  • INDEX [index_name]; // 인덱스 삭제
  • DATABASE [database_name]; // 데이터베이스 삭제
    
    
• TRUNCATE
  • TABLE [table_name];

 

* Types of Constraints

1. NOT NULL: 해당 열에 NULL 값을 허용하지 않습니다.(PK)
2. UNIQUE: 해당 열에 중복된 값을 허용하지 않습니다.(Email)
3. PRIMARY KEY: 해당 열을 기준으로 레코드를 식별할 수 있도록 설정하며,
   NOT NULL과 UNIQUE 제약 조건을 포함합니다.
4. FOREIGN KEY: 해당 열이 다른 테이블의 기본 키와 관계를 맺도록 설정합니다.
5. CHECK: 해당 열의 데이터 값이 지정한 조건식을 만족해야 합니다.
6. DEFAULT: 해당 열에 값이 지정되지 않았을 때 기본 값을 설정합니다.

---

DML (DATABASE MANIPULATION LANGUAGE)

 

• INSERT
  • INTO [table_name] ([col1, col2, col3])
    VALUES ([data1, data2, data3]);
    
    
• SELECT
  • [column1, column2, ...]
    FROM [table_name];
    
    
  • [column1, column2, ...]
    FROM [table_name]
    WHERE [condition_column] = [data];
    
    
  • DISTINCT [column1, column2]
    FROM [table_name];
    
    
  • *
    FROM [table_name]
    WHERE [column1] = [data] AND [column2] = [data];
    WHERE [column1] = [data] OR [column2] = [data];
    WHERE NOT [column1] = [data];
    
    
  • *
    FROM [table_name]
    WHERE [column1] BETWEEN [value1] AND [value2];
    
    
  • *
    FROM [table_name]
    WHERE column_name IN (value1, value2, value3);
    
    
  • *
    FROM [table_name]
    WHERE [column1] LIKE 'abc%'; // abc로 시작하는 글자가 있는 ROW 출력
    
    
  • *
    FROM [table_name]
    WHERE[column1] LIKE '_bc'; // bc로 끝나는 글자가 있는 ROW 출력
    
    
  • [column1, column2, ...]
    FROM [table_name]
    WHERE [condition_column] = [data]
    ORDER BY column2 (ASC, DESC);
    
    
  • [column1], [aggregate_function(column2)] AS [alias_column2]
    FROM [table_name]
    GROUP BY [column1];
    
    
    • * aggregate_function:
      COUNT(column), SUM(column), AVG(column), MAX(column), MIN(column), STDDEV(column)
      
      
  • [column1], [aggregate_function(column2)] AS [alias_column2]
    FROM [table_name]
    GROUP BY [column1]
    HAVING [aggregate_function(column2) >= [data]; // 집계함수, 별칭, 비교연산자
    
    
    • * comparison_operator:
      =, <=, <>, !=, >= ..
      
      
  • [column1], [aggregate_function(column2)] AS [alias_column2]
    FROM [table_name]
    GROUP BY [column1]
    HAVING [aggregate_function(column2) >= [data]
    ORDER BY [column2] (ASC, DESC);
    ORDER BY [column1] ASC, [column2] DESC; // 집계함수, 별칭, 정렬(오름차순, 내림차순)
    
    
  • SELECT [column1], [column2]
    FROM [table_name1]
    UNION
    UNION ALL
    INTERSECT
    EXCEPT
    SELECT [column1], [column2]
    FROM [table_name2]; // 집합 연산자 (Set Operator)
    
    
  • [table_name].[column1], [ref_table_name].[column2], [table_name].[column3]
    FROM [table_name]
    INNER JOIN [ref_table_name]
    ON [table_name].[column4] = [ref_table_name].[column4]; // Inner Join
    
    
  • [table_name].[column1], [ref_table_name].[column2], [table_name].[column3]
    FROM [table_name]
    LEFT OUTER JOIN [ref_table_name]
    ON [table_name].[column4] = [ref_table_name].[column4]; // Left Outer Join
    
    
  • *
    FROM [table_name]
    CROSS JOIN [table_name2]; // Cross Join
    
    
  • SELECT [column1], [column2], [column3],
    (SELECT AGG([column3]) FROM [table_name]) AS [agg_column3]
    FROM [table_name]
    ORDER BY [column1], [column2]; // 스칼라 서브쿼리(프레임) (SELECT 문 안)
    
    SELECT first_name, last_name, salary,
    (SELECT AVG(salary) FROM employees) AS avg_salary
    FROM employees
    ORDER BY last_name, first_name; // 스칼라 서브쿼리(응용) (SELECT 문 안)
    
    
  • SELECT employees.first_name, employees.last_name, employees.department_id
    FROM (SELECT department_id, MAX(salary) AS max_salary
                 FROM employees
                 GROUP BY department_id)
    AS dept_max_salary
    JOIN employees
    ON employees.department_id = dept_max_salary.department_id
    AND employees.salary = dept_max_salary.max_salary
    ORDER BY employees.department_id; // 인라인뷰 서브쿼리(Inline View)
    
    
  • SELECT [column1], [column2], [column3]
    FROM [table_name]
    WHERE [column3] > (SELECT AGG([column3]) FROM [table_name])
    ORDER BY [column2], [column1]; // 중첩 서브쿼리(프레임) (WHERE 문 안)
    
    SELECT first_name, last_name, salary
    FROM employees
    WHERE salary > (SELECT AVG(salary) FROM employees)
    ORDER BY last_name, first_name; // 중첩 서브쿼리(응용) (WHERE 문 안)
    
    
  • SELECT department_name, job_title, SUM(salary) AS total_salary
    FROM employees
    JOIN jobs ON employees.job_id = jobs.job_id
    JOIN departments ON employees.department_id = departments.department_id
    GROUP BY ROLLUP(department_name, job_title); // ROLLUP 함수 예시
    
    
  • SELECT department_name, job_title, SUM(salary) AS total_salary
    FROM employees
    JOIN jobs ON employees.job_id = jobs.job_id
    JOIN departments ON employees.department_id = departments.department_id
    GROUP BY CUBE(department_name, job_title); // CUBE 함수 예시
    
• UPDATE
  • [table_name]
    SET [field] = [data]
    WHERE [condition_column] = [data];
• DELETE
  • FROM [table_name]
    WHERE [condition_column] = [data];