SQL

SQL 작성형식

1. SQL 문 끝에는 항상 세미콜론을 써줘야합니다.

: SQL 문법 상 세미콜론이 하나의 SQL 문을 종결하는 단위다.

2.SQL 문 안에는 공백이나 개행 등을 자유롭게 넣을 수 있다.

:어떤 방식으로 쓰든, 구분되어야할 키워드들이 최소한 하나 이상의 공백으로 구분되어 있고, 세미콜론으로 마무리되어 있으면 실행에는 문제가 없다.

3.SQL 문의 대소문자 구분 문자

:SQL 문을 보면, MySQL에 기본으로 내장된 키워드들(이런 것들을 보통 ‘예약어’라고 합니다.)은 대문자로 써주고, 나머지 부분은 소문자로 쓴다.

4.데이터베이스 이름과 테이블 이름

: 실무에서는 서로 다른 데이터베이스에, 같은 이름의 테이블이 존재할 수도 있기 때문에 '데이터베이스명'. '테이블명' 형식으로 적는게 좋다.

 --select * from '데이터베이스명'.'테이블명' ;
 select * from employees.departments ;

DDL

DDL(Data Definition Language) - 데이터베이스를 정의하는 언어로서 데이터를 생성, 수정, 삭제하는 등의 데이터의 전체의 골격을 결정하는 역할을 하는 언어다.

주로 데이터 베이스 관리자나 데이터베이스 설계자가 사용하며, CHEMA, DOMAIN, TABLE, VIEW, INDEX를 정의하거나 변경 또는 삭제할 때 사용하는 언어다.

종류	역할
CREATE	데이터베이스, 테이블 등을 생성하는 역할.
ALTER	테이블을 수정하는 역할
DROP	데이터베이스, 테이블을 삭제하는 역할
TRUNCATE	테이블을 초기화 시키는 역할

DML

DML(Data Manipulation Language) - 정의된 데이터베이스에 입력된 레코드를 조회하거나 수정하거나 삭제하는 등의 역할을 하는 언어로 데이터베이스 사용자가 응용 프로그램이나 질의어를 통하여 저장된 데이터를 실질적으로 처리하는데 사용하는 언어다.

종류	역할
SELECT	테이블의 데이터를 조회하는 역할
INSERT	테이블의 데이터를 삽입하는 역할
UPDATE	테이블의 데이터를 수정하는 역할
DELETE	테이블 데이터를 삭제하는 역할

Select

기본적인 데이터를 조회

select *  from employees.employees; -- 전체 데이터를 조회 
select emp_no , birth_date, gender from employees.employees ; 
-- 특정컬럼을 대상으로 조회 

Dummy 테이블 활용

select count() from dual ; 
-- dual 은 더미테이블 
select (1+1) / 2 as val from dual ; 
-- select절에서 다양한 연산처리를 할떄 미리 사용

데이터 정렬 : 오름차순(ASC) 내림차순 (DESC)

select emp_no, birth_date from employees.employees order by birth_date asc ; 
-- 데이터를 조회된 결과를 특정 컬럼을 기준으로 오름차순(ASC) 혹은 내림차순(desc)으로 보여줄지 결정.

select emp_no, birth_date from employees.employees order by emp_no desc ; 
select * from employees.employees order by first_name desc , gender ; 
-- order by는 deafault가 오름차순

중복을 제외하고 보고 싶을 때

select distinct first_name from employees.employees ; 
-- 중복된 값을 제거하고 유일한 값들만 보여줄 때 씀 
select distinct first_name , gender from employees.employees ;

한정된 집합을 조회 (where)

select * from employees.employees where first_name = 'Georgi' ; 
-- 조건에 맞는 한정된 집합만을 조회할 때 쓰는 where
select * from employees.employees where hire_date > '1986-04-30' ; 
-- 1986년 4월 30일 이후의 입사한 사람들을 조회
select*from employees.employees where first_name = 'Georgi' and hire_date > '1986-04-30'; 
-- 입사일이 1986년 4월 30일 이후에 입사한 이름이 Gerogi인 사람 조회 
select  from employees.employees where hire_date between '1986-03-11' and '1986-04-30'; 
-- 1986년 3월11일과 4월30일 사이의 입사한 사람들을 조회

select * from employees.employees where first_name in ('Percy' , 'Weiye'); 
-- 성이 Percy 이거나 Weiye인 사람을 조회

select * from employees.employees where first_name like 'W%'; 
-- 이믈의 첫글자가 W로 시작되는 사람들을 모두 조회

실습하기 앞서 SQL EDITOR 환경설정을 진행할 것이다.

1. AUTO COMMIT 해제

2. 안전모드 해제

EDIT 을 누르고 PREFRENCE 버을 누른다.

SQL Execution (SQL 실행) 에서 New connections use auto commit mode 버튼을 눌러 해제한다.

다음 안전모드를 해제할 것이다.

SQL Editor에 들어가서 Safe Updates (rejects UPDATES and DELETs with restrictions) 버튼을 해제한다.

INSERT , UPDATE , DELETE

1. 테이블 생성

seq : 자동 인덱스 증가,

seq로 컬럼의 이름을 입력하고 데이터 타입은 INT NOT NULL 로 중복이 불가능한 숫자형하고 AUTO_INCREMENT 로 자동 증가를 설정

seq 를 기본키로 지정하여 중복이 불가능하게 지정

CREATE TABLE TEST_DB.customer ( 
seq INT NOT NULL AUTO_INCREMENT, 
ct_id VARCHAR(20) NOT NULL, 
ct_pw VARCHAR(100) NOT NULL, 
address VARCHAR(100) NOT NULL, 
ct_tell VARCHAR(20) NOT NULL, 
REG_DATE DATE NOT NULL, 
EXPIRED_DATE DATE NOT NULL, 
PRIMARY KEY(seq) ) ENGINE= MYISAM CHARSET=utf8 ;
-- show character set ; 캐릭터 셋 종류 확인 

MySQL 스토리지 엔진 종류 및 특징 Databases - Useful GuideUseful Guide

2. 데이터 삽입

INSERT문

INSERT INTO test_db.customer (ct_id, ct_pw, address, ct_tell, REG_DATE, EXPIRED_DATE) 
values ('yunpha123','1234','인천시 부평구 갈산동 무지개아파트 101호','010-1234-5678','20180920','2020-02-18') ;

3. 데이터 확인

select * from test_db.customer ;

UPDATE문

select * from employees.departments; 
insert into employees.departments (dept_no, dept_name) values('d010', 'android developteam') ; 
insert into employees.departments (dept_no, dept_name) values('d011', 'IOS developteam') ; 
insert into employees.departments (dept_no, dept_name) values('d012', 'Infra team') ; 
insert into employees.departments (dept_no, dept_name) values('d013', 'operation team') ;
-- 근데 내가 부서이름을 잘못 넣었어. 
update employees.departments set dept_name = 'andorid team' where dept_no = 'd010'; 
select * from employees.departments where dept_no= 'd010';

select from employees.departments ; 

update employees.departments set dept_name = 'IOS team' 
where dept_name = 'IOS developteam'; 

select  from employees.departments where dept_no = 'd011';
-- 위험 update에 조건절이 없을경우 db에서는 매우 위험한 작업으로 인식함 왜냐하면 데이터 전체를 바꾸게 되므로 
--update employees.departments set dept_no = 'd100' ;

DELETE 문

select  from employees.employees ; 
select  from employees.dept_emp ;
select * from employees.employees a , employees.dept_emp b where a.emp_no = b.emp_no ; 
create table employees.emp_dept as (select a.emp_no , b.dept_no from employees.employees a , employees.dept_emp b where a.emp_no = b.emp_no) ;
select * from employees.emp_dept ;
delete from employees.emp_dept where emp_no = 10001 and dept_no='d005'; 
delete from employees.emp_dept where emp_no = 10002 ;
delete from employees.emp_dept ; -- 데이터만 삭제됨

View

뷰(view)는 관계 데이터베이스의 데이터베이스 언어 SQL에서 하나 이상의 테이블 (또는 다른 뷰)에서 원하는 모든 데이터를 선택하여, 그들을 사용자 정의하여 나타낸 것이다. 관계 데이터베이스의 관계 모델의 관계의 일종인 도출 관계에 해당한다. 여러 테이블(기본 관계) 또는 뷰의 데이터를 연결하여 조합할 수 있다. 보기에 표시되는 데이터의 선택 기준을 지정할 수도 있다[1].

쉽게 말해서 뷰(view)는 데이터베이스에 존재하는 일종의 가상 테이블을 의미한다.

이러한 뷰는 실제 테이블처럼 행과 열을 가지고 있지만, 실제로 데이터를 저장하고 있지는 않고, 다른 테이블이나 다른 뷰에 저장되어 있는 데이터를 보여주는 역할만을 수행합니다.

즉, 뷰를 사용하면 여러 테이블이나 뷰를 하나의 테이블처럼 볼 수 있다[2].

뷰의 특징

장점:

1. 특정 사용자에게 테이블 전체가 아닌 필요한 필드만을 보여준다.

2. 복잡한 쿼리를 단순화해서 사용한다.

3. 쿼리를 재사용할 수 있습니다.

단점:

1. 한 번 정의된 뷰는 변경할 수 없다.

2. 삽입, 삭제, 갱신 작업에 많은 제한 사항을 가진다.

3. 자신만의 인덱스를 가질 수 없다.

문법은 다음과 같다.

 CREATE VIEW 뷰이름 AS ( SELECT 필드이름1, 필드이름2, ... FROM 테이블이름 WHERE 조건)

다음 예제를 따라해보길 권한다.

CREATE VIEW employees.emp_dept22 as (
select a.emp_no , b.dept_no from employees.employees a , employees.dept_emp b 
where a.emp_no = b.emp_no) ; 
select * from employees.emp_dept22 ;

서브쿼리(Sub Query)

서브쿼리란 SQL 문에 포함되어 있는 또 다른 SQL 문을 말한다.

서브쿼리를 사용하는 이유는 여러번의 쿼리를 수행을 통해 얻는 결과를 하나의 중첩된 SQL문장으로 간편하게 결과를 얻을 수 있게 해준다.

서브쿼리는 위치에 따라 명칭이 다르다.

1. FROM절에 사용하는 인라인 뷰(Inline view),

2. SELECT문에 사용하는 스칼라 서브쿼리(Scala Subquery)

3. WHERE절에 사용하는 서브쿼리(Subquery)

3가지 서브쿼리에 대해 간단한 실습을 진행하겠다.

FROM절에 사용하는 인라인 뷰(Inline view)

select emp_no , salary from employees.salaries ; 
select a.*, b.salary from employees.employees a left join 
(select emp_no , salary from employees.salaries) b on a.emp_no = b.emp_no ; 

--테이블 생성 
create table employees.emp_salary as (select a.* , b.salary from employees.employees a 
left join 
(select emp_no , salary from employees.salaries) b on a.emp_no = b.emp_no ) ; 
select * from employees.emp_salary ;

1986년 06 26일에 입사한 회사원 중 연봉이 가장 높은 TOP3를 조회하고 싶을 때 다음과 같다.

select emp_no , first_name, last_name, salary 
from (select * from employees.emp_salary where hire_date ='1986-06-26' ) a 
order by salary limit 3 ;

SELECT문에 사용하는 스칼라 서브쿼리(Scala Subquery)

사원 10010 급여와 테이블 전체 평균 급여를 조회할 때 다음과 같다.

select emp_no , first_name,last_name, salary , 
(select round(avg(salary),-1) from employees.emp_salary ) as '평균급여' 
from employees.emp_salary where emp_no =10010;

Where 절에 사용하는 서브쿼리

'Peac' 성을 가진 사원들보 더 빨리 입사한 사람들을 조회하고 싶을 때 다음과 같다.

select emp_no, first_name , last_name ,hire_date from employees.employees 
where hire_date < (select min(hire_date) from employees.employees where last_name = 'Peac') 
order by hire_date ;

DCL

DCL(Data Control Language) - 데이터베이스에 접근하거나 객체에 권한을 주는등의 역할을 하는 언어로 데이터를 제어하며 데이터의 보안, 무결성, 회복, 병행 수행제어 등을 정의하는데 사용한다.

종류	역할
GRANT	특정 데이터베이스 사용자에게 특정 작업에 대한 수행권한 부
REVOKE	특정 데이터베이스 사용자에게 특정 작업에 대한 수행 권한을 박탈, 회수
COMMIT	트랜잭션의 작업을 취소 및 원래래 복구하는 역할
ROLLBACK	트랜잭션의 작업을 취소 및 원래대로 복구하는 역

select 문 실행순서

SELECT 쿼리문을 실행했을때 FROM - WHERE GROUP BY - HAVING - SELECT - ORDER BY -LIMIT 순서대로 실행이 된다.

1. FROM

2. WHERE

3. GROUP BY

4. HAVING

5. SELECT

6. ORDER BY

7. LIMIT

https://velog.io/@ha0kim/SQL

구체적으로 SQL의 실행순서를 살펴보면 다음과 같다.

1. FROM : DBMS의 각 테이블 중에서 어떤 테이블을 조회할지 먼저 결정한다.

2. WHERE : FROM부분에서 해당되는 테이블에서 조건에 만족하는 row들을 선별한다.

3. GROUP BY : WHERE 조건으로 추출된 데이터를 선택한 칼럼을 기준으로 row들을 그루핑한다. 하나의 그룹은 하나의 row로 표현된다.

4. HAVING : 그루핑 작업 후 생성된 여러 그룹 중 특정 조건(들)을 만족하는 그룹들만 선별한다.

5. SELECT : 여러 조건들을 처리한 후 남은 데이터에서 어떤 컬럼을 출력할지 선택한다.

6. ORDER BY : 행의 순서를 어떻게 보여줄지 정렬한다.

7. LIMIT : 이전 단계까지 조회된 row들 중 일부 row들만 보여준다.

Foreign Key

Foreign Key는 다른 테이블의 특정 row를 식별하는 컬럼으로 우리말로 외래 키라고 부른다. 외래는 참조하는 테이블에서 1개의 키(속성 또는 속성의 집합)에 해당하고, 참조하는 측의 관계 변수는 참조되는 측의 테이블의 키를 가리킨다. 참조하는 테이블의 속성의 행 1개의 값은, 참조되는 측 테이블의 행 값에 대응된다. 이 때문에 참조하는 테이블의 행에는, 참조되는 테이블에 나타나지 않는 값을 포함할 수 없다[3]. 즉 왜리키는 두개의 테이블을 연결해주는 연결 다리 역할을 하며 중복 된 값을 방지해준다[4].

그렇다면 외래키는 왜 필요할까?

예를들어 , 연봉 테이블에서 새로운 데이터를 넣고자 할때 외래키에 해당하는 사원 아이디가 없는 정보면 DBMS는 에러를 발생시켜 해당 데이터 삽입을 막는다. 이 기능은 테이블 내에 저장되어있는 데이터가 항상 참조하는 값이 있다는 것을 보장해주는 역할을 한다[4].

외래키를 사용하는 문법은 다음과 같다.

CREATE TABLE table_name (
   id    INTEGER  PRIMARY KEY,
   col2  CHARACTER VARYING(20),
   col3  INTEGER FOREIGN KEY REFERENCES other_table(column_name),
   ... )

다음은 EMPLOYEES DB에서 salary 테이블을 employees 테이블을 참조하도록 설정하는 실습을 갖겠다.

설정을 마친 후 apply를 누르면 다음과 같이 외래키 문법이 나온다.

해석하면 연봉테이블(salary)의 empno는 사원 테이블(employees)의 emp_no 컬럼을 참조하도록 한다.라고 할 수 있다.

Reference:

[1] https://ko.wikipedia.org/wiki/VIEW_(SQL)

[2] http://tcpschool.com/mysql/mysql_view_createReplace

[3] https://ko.wikipedia.org/wiki/%EC%99%B8%EB%9E%98_%ED%82%A4

[4] https://brunch.co.kr/@dan-kim/26