CS 지식/데이터베이스

[데이터베이스-simple버전] 3. SQL 데이터베이스 언어

cdragon 2023. 11. 7. 21:29
반응형

1. SQL 소개

  • SEQUEL (Structured English QUEry Language)
  • ANSI/ISO 가 표준 standard SQL 를 지정
    • SQL-86 : 1986년도 만들어진 첫번째 표준 SQL, SQL1이라고도 불림
    • SQL-92 : SQL2 로 1992년에 개정된 표준
    • SQL-99, SQL 2011 …
  • SQL 이란?
    • 대부분의 DBMS 는 SQL-92 표준의 대부분을 지원하고 추후의 표준에 지정된 기능을 추가하여 지원하고 고유의 기능도 제공함
    • 최근 SQL-2011 에 추가된 기능 예제
      • Temporal table (system versioned temporal table)
        • 데이터 변경 내용의 전체 기록을 유지해 간편한 지정 시간 분석을 허용하도록 설계된 사용자 테이블의 종류

 

  • SQL 분류
    • DDL (Data Definition Language)
      • 테이블을 생성하고 변경, 제거하는 기능을 제공
    • DML (Data Manipulation Language)
      • 테이블에 새 데이터를 삽입하거나, 테이블에 저장된 데이터를 수정, 삭제하는 기능을 제공
    • SELECT
      • 테이블 데이터를 조회하는 기능을 제공
    • DCL (Data Control Language)
      • 보안을 위해 데이터에 대한 접근 및 사용권한을 조절하는 기능을 제공

2. DDL: 데이터 정의

  • 데이터베이스 구조를 정의하고 변경하는 기능 제공하는 언어
  • Create : 새로운 데이터베이스 오브젝트들을 생성 (schema, table, view 등)
  • Alter : 존재하는 오브젝트의 정의를 변경
  • Drop : 존재하는 오브젝트를 데이터베이스에서 삭제

 

CREATE TABLE syntax

데이터 타입 on SQL

Decimal example

  • price decimal(10, 4)

default clause

  • SQL 분류 := DEFAULT
    • INSERT문에 컬럼에 대한 값이 지정되지 않았을 때 null 대신에 사용
    • credit INT DEFAULT 0
    • contact char(20) DEFAULT ‘james'

 

constraint clause

<integrity constraint> :={ UNIQUE | <primary key> | <reference contraint> | <check constraint>}

  • UNIQUE : 대체 키를 지정하고 유일성을 가지는 컬럼임을 지정
  • PRIMARY KEY : ROW를 식별할 수 있는 기본 키 ( unique + not null )
  • <reference constraint> : 참조하는 테이블을 지정하는 외래키 설정 (다음 장에서 설명)
  • <check constraint> : column 값의 가능 domain 지정 (다음 장에서 설명>

 

CREATE TABLE LIKE

CREATE TABLE <table name> <like_table_clause>

<like_table_clause> := LIKE <table_name> [WITH DATA]

  • Ex) CREATE TABLE customer_backup LIKE customer WITH DATA;

 

ALTER TABLE

DROP TABLE

  • 테이블 데이터 및 catalog 삭제
  • DROP TABLE <table_name>

 

3. SELECT: 데이터 조회

SELECT

  • 테이블에서 원하는 데이터를 검색하는 SQL
  • Syntax
    • SELECT [DISTINCT] {* | <column_name>, ...} FROM ;
  • Example
    • SELECT DISTINCT dept_name from instructor;
      • DISTINCT : 결과 테이블에서 중복된 레코드를 제거하는 키워드
  • SELECT clause 는 산술식 (arithmetic expression) 을 포함 가능
    • +, -, * and /
    • SELECT ID, name, salary/12 from employee;

 

SELECT with WHERE

  • 테이블에서 조건을 맞는 데이터만 검색하기 위해 WHERE clause 이용
  • Syntax
    • SELECT [DISTINCT] {* | <column_name>, ...} FROM <table list> [ WHERE condition ];
    • Condition은 비교 연산자 (=, <>, <, >, <= , =>) 와 논리연산자 (AND, OR, NOT)
  • Example
    • SELECT name from employee WHERE dept_name = ‘SALES’ and salary > 100000;
  • LIKE keyword
    • 문자열 컬럼인 경우 부분적인 매칭으로 조건 검색 가능
    • % : 0개 이상의 문자
    • _ : 1개의 문자
      • SELECT * from employee where name like ‘이%’;
      • SELECT * from employee where name like ‘이정_’;

 

NULL Value

  • null 은 empty 가 아닌 unknown
  • null 값과 산술 연산 및 비교 연산 결과는 null
    • 5 + null returns null
    • null > 5 returns null
    • null = null returns null
  • null 과 논리 연산 결과
    • OR : (null OR true) = true, (null or false) = null, (null or null) = null
    • AND : (true AND null) = null, (false AND null) = false, (null and null) = null
    • NOT : (NOT null) = null
  • null 값을 가진 row을 찾기 위한 조건식 – IS NULL
    • SELECT id, name FROM customer WHERE aga IS NULL;

 

ORDER BY

  • SELECT 의 검색 결과를 정렬하기 위해 ORDER BY 키워드 사용
  • Syntax
    • SELECT [DISTINCT] {* | <column_name>, ...}
      FROM <table list> [ WHERE condition ]
      [ ORDER BY <column_name>, ... [ASC | DESC] ];
    • ASC: 오름 차순 정렬 (default), DESC: 내림 차순 정렬
  • Example
    • SELECT id, name, age, address FROM customer ORDER BY age DESC;
    • SELECT id, name, dept_name, salary FROM employee ORDER BY salary;
    • SELECT id, product, quantity, order_date FROM sales_orders
      ORDER BY order_date ASC, quantity DESC;

 

Aggregation Function

  • 특정 컬럼의 값을 통계적으로 계산한 결과를 보여주는 SQL 함수
    • COUNT : 컬럼 값의 개수
    • MAX : 컬럼 값의 최대값
    • MIN : 컬럼 값의 최소값
    • SUM : 컬럼 값의 합계
    • AVG : 컬럼 값의 평균
  • COUNT, MAX, MIN 은 LOB 타입을 제외한 모든 타입에서 사용 가능
    SUM, AVG 는 숫자 데이터만 가능
    • EX)
    • SELECT sum(salary) FROM employee WHERE dept_name = ‘SALES’;
    • SELECT count(distinct id) FROM sales_orders WHERE product = ‘computer’;

 

Null value in aggregation function

  • SELECT sum (salary) FROM employee;
    • 만약 컬럼 값이 null이면 합을 계산할 때 무시
    • 만약 컬럼 값이 모두 null이면 null이 출력된다
  • MAX, MIN, SUM, AVG 경우 null이 아닌 값으로만 계산
    • 모두 null인 경우 null을 리턴
  • COUNT : null이 아닌 값의 개수를 출력
    • 컬럼이 모두 null 인 경우 0 리턴
  • 테이블에 레코드가 없는 경우
    • sum, avg, min, max : return null
    • count : return 0

 

GROUP BY

  • 테이블에서 특정 컬럼의 값이 같은 rows를 모아 그룹을 만들고, 그룹별로 검색하기 위해 사용되는 keyword
  • 그룹에 대한 조건을 추가하려면 HAVING 키워드를 이용
    • Where 조건 : 레코드를 grouping 하기 전에 조건을 검색
    • Having 조건 : 레코드들을 grouping 후에 그룹에 대한 조건을 검색
  • Syntax
    • SELECT [DISTINCT] {* | <column_name>, ...}
      FROM <table list> [ WHERE condition ]
      [ GROUP BY <column_name_list> [ HAVING condition ] ]
      [ ORDER BY <column_name_list> [ASC | DESC] ]
  • Aggregation function을 제외한 SELECT문의 컬럼은 GROUP BY 리스트에 포함된 컬럼들만 가능
  • SELECT dept_name, ID, avg (salary) from employee group by dept_name;
    • Return error : SELECT list is not in GROUP BY clause and contains nonaggregated column
  • Example
    • SELECT dept_name, avg (salary) FROM employee GROUP BY dept_name;
    • SELECT dept_name, avg (salary) FROM employee GROUP BY dept_name HAVING avg(salary) > 50000000;
    • SELECT company_name, count(product_name) as number_of_product,
      max(price) as highest_price
      FROM product GROUP BY company_name;
    • SELECT company_name, count(product_name) as number_of_product,
      max(price) as highest_price FROM product GROUP BY company_name
      HAVING COUNT(product_name) >= 3;

 

Set

 

 

JOINs

 

 

outer join

 

 

 

4. DML: 데이터 조작

INSERT

  • 테이블에 새로운 row을 삽입하는 SQL
  • Syntax
    • INSERT <table_name> [(<column_name>, ...)] VALUES (value, …)
      • column_name을 적지 않으면 전체 컬럼에 대한 INSERT
  • Example
    • INSERT student values (’20210001’, ‘James’, ‘Computer’, 1);
    • INSERT student (id, name, dept_name, grade) VALUES (‘20210001’, ‘James’, ‘Computer’, 1)
    • INSERT student (id, name, dept_name) VALUES (‘20210001’, ‘James’, ‘Computer’) - set grade value as null
CREATE TABLE student (
    id varchar(8) primary key,
    name varchar(20) not null,
    dept_name varchar(20),
    grade smallint);

 

  • INSERT INTO SELECT
    • 다른 테이블에서 질의 결과를 삽입하는 구문
    • INSERT INTO [(, ...)]
  • Example
    • INSERT INTO student SELECT * from new_students;
CREATE TABLE student (
    id varchar(8) primary key,
    name varchar(20) not null,
    dept_name varchar(20),
    grade smallint);

 

DELETE

  • 테이블에서 쿼리에 해당되는 row을 지우는 SQL Syntax
    • DELETE FROM [WHERE condition]
  • Example
    • DELETE FROM student; -> 모든 rows 삭제
    • DELETE FROM student WHERE dept_name = ‘Computer’;
    • DELETE FROM student WHERE dept_name in (select dept_name from department where building = ‘SEL01’)

 

UPDATE

  • 테이블에 저장된 데이터를 변경하는 SQL Syntax
    • UPDATE SET = value [WHERE condition]
  • Example
    • UPDATE employee set salary = salary * 1.07;
    • UPDATE employee set salary = salary * 1.07 WHERE hire_date < ‘2020.01.01’;
    • UPDATE employee set salary = salary * 1.07 WHERE dept_name in (SELECT * from department WHERE location = ‘SEOUL’
반응형