[MariaDB, 카카오 클라우드 스쿨 4주차 -1] SQL 작성 규칙

6. SQL 작성 규칙

- SQL의 예약어는 대소문자 구분을 하지 않습니다.

- 테이블 이름이나 칼럼 이름은 대소문자를 구분하는 데이터베이스도 있고 구분하지 않는 데이터베이스도 있음

• MariaDB나 MySQL은 구분합니다

- 값을 작성할 때는 대소문자 구분을 하는데 MariaDB는 대소문자 구분을 하지 않는 경우도 있습니다.

- 숫자 데이터는 따옴표를 하지 않고 작은따옴표를 해서 표현하는데 MariaDB나 MySQL은 큰 따옴표도 허용합니다.

- 명령문의 마지막은 ; 인데 접속 도구에서는 해도 되고 하지 않아도 되지만 절차적 프로그래밍을 할 때는 명확하게 해주어야 하며 프로그래밍 언어에서 SQL을 사용할 때는 ;을 하면 안 됩니다.

---

7. 데이터베이스 관련 명령어

1) 데이터베이스 생성

create database 데이터베이스이름; # 이미 존재하는 이름이면 에러

-> 일반적으로 프로젝트를 진행할 때마다 데이터베이스를 생성

 

2) 데이터베이스 확인

show databases;

 

3) 데이터베이스 사용- 항상 MySQL이나 Maria DB에서는 SQL을 사용하기 전에 데이터베이스 사용 설정을 먼저

use 데이터베이스이름;

 

4) 데이터베이스 삭제

drop database 데이터베이스이름;

 

5) 데이터베이스에 존재하는 테이블 확인

show tables;

 

6) 데이터베이스 생성 및 사용 실습

-- 데이터베이스 목록 보기
show databases;

-- 데이터베이스 만들기
create database woo;

-- 데이터베이스 사용
use woo;

---

8. 샘플 데이터 생성

 ppt 참고

---

9. SQL 분류 

1) DDL(구조에 관련된 명령어로 일반적으로 DBA의 명령어)- 취소 안됨

- CREATE: 구조 생성

- ALTER: 구조 변경

- DROP: 구조 삭제

 

- TRUNCATE: 테이블 내의 데이터 삭제

 

- RENAME: 구조 이름 변경

 

2) DQL - 검색 관련 명령어

- SELECT

 

3) DML - 데이터 관련 명령어- 취소 가능

- INSERT

- UPDATE

- DELETE

 

4) TCL- 트랜잭션 관련 명령어- 취소 불가능

- COMMIT: 현재까지 작업 내용을 원본에 반영

- ROLLBACK: 작업 내용을 취소

- SAVEPOINT: 취소할 지점을 만드는 명령어

 

5) DCL- 제어 명령- 취소 불가능, 운영자의 언어

- GRANT: 권한 부어

- REVPKE: 권한 회수

 

6) 개발자에게 중요도

DQL -> DML -> TCL -> DDL -> DCL

---

10. SELECT

- 데이터 조회 명령어로 원본에 아무런 영향을 주지 않음

- 원본에서 데이터를 복제해서 리턴합니다

 

1) 샘플 데이터 구조

           

EMP 테이블
	EMPNP: 사원번호 정수 4자리이고 기본키
    ENAME: 사원이름으로 문자
    JOB: 직무로 문자
    MGR: 관지의 사원번호
    HIREDATE: 입사일로 날짜 형식
    SAL: 급여로 실수 7자리 소수 2자리
    COMM: 상여금으로 실수 7자리 소주 2자리
    DEPT: 부서 번호로 정수 2자리이고 DEPT 테이블의 EPTNO를 참조
    
DEPT 테이블
	DEPTNO: 부서번호로 정수 2자리이고 기본키
    DNAME: 부서이름으로 문자
    LOC: 위치로 문자
    
SALGRADE 테이블
	GRADE 테이블: ghqhddmfh tntwkdlrh rlqhszl
    LOSAL: 호봉의 최저 급여로 숫자
    HISAL: 호봉의 최고 급여로 숫자
    
TCITY 테이블
	NAME: 도시이름으로 문자열이고 기본키
    AREA: 면적으로 정수
    POPU: 인구수로 정수
    METRO: 대도시 여부로 문자
    REGION: 지역으로 문자
    
 TSTAFF 테이블
 	NAME: 직원으로 문자이고 기본키
    DEPART: 부서이름으로 문자열
    GENDER: 성별로 문자열
    JOINDATE: 입사일로 문자열
    GRADE: 직무로 문자열
    SALARY: 급여로 정수
    SCORE: 고과 점수로 실수

 

2) SELECT 용어

 

- Selection: 테이블의 행을 선택할 때 사용하는 것

- Projection: 테이블의 열을 선택할 때 사용하는 것

- Join: 공유 테이블 양쪽의 열에 대해서 링크를 생성해서 다른 테이블의 데이터를 가져와서 합치는 것

 

 

 

3) MariaDB에서의 SELECT 구조

 

5- SELECT              데이터를 열 단위로 조회하기 위한 열 이름이나 계산식을 나열

1- FROM                  데이터를 조회할 테이블을 나열

2- [WHERE              데이터를 행 단위로 분할하기 위한 조건]

3- [GROUP BY         데이터를 그룹화시키기 위한 열 이름이나 계산식을 나열]

4- [HAVING              데이터를 행 단위로 분할하기 위한 조건]

6- [ORDER BY         데이터를 정렬하기 위한 열 이름이나 계산식 또는 SELECT 절의 번호와 정렬 방법]

7- [LIMIT                   데이터의 위치와 개수를 지정해서 가져오기 위한 절로 표준은 아님]

 

-> FROM을 수행하게 되면 원본 데이터베이스에서 테이블 단위로 복제를 해와서 작업을 수행 - 여기서 다른 이름을 명명하는 것은 별명이 아니고 이름을 바꾸는 것입니다.

 

 

 

4) SELECT 구문의 가장 기본적인 구조

 

-> 테이블의 모든 데이터 조회: 칼럼의 순서는 테이블을 만들 때 작성한 순서대로 리턴

직접 테이블을 생성한 경우가 아니라면 *은 사용하지 않는 것이 좋습니다.

SELECT * FROM 테이블이름

 

tCity 테이블의 모든 데이터를 조회

SELECT * FROM tCity;

 

-> 특정 칼럼만 추출

SELECT 절에 필요한 칼럼만 나열 FROM 테이블이름;

 

tCity 테이블에서 name와 popu 칼럼을 조회

SELECT name, popu FROM tCity;

 

tStaff 테이블에서 name, depart, grade 칼럼을 조회

SELECT name, depart, grade FROM tStaff;

 

 

5) SELECT 절에서의 별명

 

-> SELECT 절에서는 칼럼에 별명을 부여할 수 있습니다.

-> 하나의 공백을 두고 별명을 설정하면 되는데 이때 공백 자리에 AS를 추가해도 됩니다.

-> 별명에 공백이나 특수문자 또는 대문자가 있으면 " "로 묶어야 합니다. 

-> SELECT 절의 별명은 ORDER BY에서 사용 가능하고 프로그래밍 언어에서도 별명을 가지고 데이터를 가지고 옵니다.

-> 계산식이나 그룹 함수의 결과를 조회하거나 할 때는 별명을 부여하는 것이 좋습니다.

 

SELECT name as 이름 FROM 

 

 

 6) 계산식 출력

 

-> FROM 절을 제외한 모든 곳에서 계산식 사용이 가능

-> 계산식은 가상의 칼럼이고 FROM은 실제 테이블을 가져오는 것이므로 FROM 절에는 계산식을 사용할 수 없음

-> tCity 테이블에서 name과 popu에 10000을 곱한 결과를 조회

SELECT 60 * 60 *24

 

7) concat 함수

 

-> 2개 이상의 문자열을 합쳐주는 함수

-> 2개 이상의 칼럼이나 연산식을 하나로 합쳐서 출력하기 위해서 사용

-> MyBatis와 같은 SQL Mapper Framework에서 like를 사용하기 위해서는 알아두어야 합니다.

 

 

8) DISTINCT

 

-> SELECT 절의 맨 앞에 한 번만 기재해서 칼럼의 중복된 값을 제거하는 역할

-> 칼럼 이름이 하나이면 그 칼럼의 값이 중복된 것만 제거하고 칼럼이 2개 이상이면 모든 값이 일치하는 경우에 제외

 

tCity에서 region을 조회

SELECT region FROM tCity;

 

tCity 테이블에서 region 중복을 제외하고 조회

SELECT DISTINCT region FROM tCity;

 

tCity 테이블에서 region과 name 모두가 중복된 경우만 제외

SELECT DISTINCT region, name FROM tCity;

 

----여기 메모장 ..봐야해 놓쳤어

 

=> SELECT 구문의 결과가 2개 이상의 행이 될 것 같은 경우에는 ORDER BY를 이용해서 정렬을 해주는 것이 좋습니다.

 

 

 

9) WHERE

 

- 테이블의 데이터를 행 단위로 분할하기 위한 조건을 설정하는 절

- SELECT, UPDATE, DELETE 구문과 함께 사용

 

- 비교 연산자

• =
• >
• <
• >=, NOT 칼럼이름 <
• <=, NOT 칼럼이름 >
• <>, !=, ^=, NOT 칼럼이름 =

tCity 테이블에서 name이 서울인 데이터의 모든 칼럼을 조회

SELECT * FROM tCity WHERE name='서울';

tCity 테이블에서 metro가 y인 데이터의 모든 칼럼을 조회

SELECT * FROM tCity WHERE metro='y';

 

Maria DB의 경우 대소문자를 구별하지 않는 경우가 있기 때문에 유의해야 합니다.

 

대소문자 구별하기 - 조회를 할 때 칼럼 이름을 BINARY로 묶어주거나 칼럼을 만들 때 자료형 뒤에 BINARY를 추가해주어야 합니다.

SELECT * FROM tCity WHERE BINARY(metro)='Y';

 

tCity 테이블에서 popu의 값이 100 이상인 데이터의 모든 칼럼을 조회

SELECT * FROM tCity WHERE popu >= 100;

 

크다 작다 그러나 같다 작거나 같다 조건이 있는 경우 테스트를 할 때 경곗값과 경곗값 양쪽의 데이터를 반드시 테스트하세요- Boundary Value Analysys(경곗값 분석 기법)

 

- NULL(아직 알려지지 않은 값으로 표현) 비교

• NULL은 일반 연산자로 비교 안됨
• IS NULL과 IS NOT NULL로 비교
• 데이터베이스에서 NULL을 저장하는 방법은 공간에 NULL을 대입하는 개념이 아니고  NULL을 저장할 수 있는 칼럼에는 데이터를 저장할 수 있는 공간에 하나의 공간을 추가해서 그 공간에 NULL 여부를 표시하기 때문

- 논리 연산자 AND와 OR을 제공

• AND는 두 개의 조건이 모두 일치하는 경우만 조회하는데 앞의 조건이 일치하지 않으면 뒤의 조건은 확인하지 않음
• OR는 두 개의 조건 중 하나의 조건만 일치해도 조회되는데 앞의 조건이 일치하면 뒤의 조건은 확인하지 않음
• AND가 OR보다 우선순위가 높습니다.

 

 

- NOT

 

 

-LIKE

• 부분 일치하는 데이터를 조회
• _: 하나의 문자와 매칭
• %: 글자 수 상관없음
• [ ]: 문자를 나열하면 문자 중 하나와 일치
• [^]: 문자를 나열하면 문자에 포함되지 않는 
• 와이들 카드 문자를 검색하고자 하는 경우는 ESCAPE 이용

tCity 테이블에서 name에 천이 포함된 데이터를 조회
SELECT * FROM tCity WHERE name LIKE '%천%';

tCity 테이블에서 name에 천이 포함되지 않은 데이터를 조회
SELECT * FROM tCity WHERE name NOT LIKE '%천%';

tCity 테이블에서 name에 천으로 끝나는 데이터를 조회
SELECT * FROM tCity WHERE name  LIKE '%천';  

tCity 테이블에서 name에 천으로 시작하는 데이터를 조회
SELECT * FROM tCity WHERE name  LIKE '천%';  

EMP 테이블에서 ENAME이 N으로 끝나는 6자의 이름을 가진 데이터를 조회(_ 5개)
SELECT * FROM EMP WHERE ENAME LIKE '_____N';

SALE에 30%가 포함된 데이터 조회
WHERE SALE LIKE '%30#%%' ESCAPE '#'

 

 

- BETWEEN ~ AND

• BETWEEN A AND B 형태로 작성하는 A부터 B까지의 데이터 조회
• 숫자, 날짜 문자열 모두 사용 가능
• 단수 AND로도 가능

tCity 테이블에서 popu가 50~100 사이인 데이터 조회

SELECT * FROM tCity WHERE popu BETWEEN 50 AND 100;

 

• 문자의 크기 비교는 맨 앞 글자부터 순서대로 하나씩 비교

SELECT * FROM tCity WHERE name BETWEEN '가' AND '사';

SELECT * FROM tCity WHERE name >= '차' AND name <'카';


//  2015년 1월 1일부터 2018년 12월 31일 사이인 데이터를 조회
SELECT * FROM tStaff WHERE joindate BETWEEN '20150101' AND '20181231';

 

- IN 연산자

IN ( 값을 나열): 나열된 값에 포함되는 경우 조회

 

SELECT * FROM tCity WHERE region ='경상' OR region='전라';

SELECT * FROM tCity WHERE region IN('경상', '전라');  

 

12) LIMIT

- 행의 개수 제한에 사용 - TOP N

• LIMIT [ 건너뛸 행의 개수], 조회할 개수

 

- 최근에는 LIMIT 개수 OFFSET 건너뛸 행의 개수

- ORDER BY와 같이 사용되는 경우가 많음

// tCity 테이블에서 popu가 큰 4개의 데이터 조회
SELECT * FROM tCity tc ORDER BY popu DESC LIMIT 4;

// tCity 테이블에서 popu가 큰 5번째  데이터부터 2개 조회
SELECT * FROM tCity tc ORDER BY popu DESC LIMIT 4,2;

SELECT * FROM tCity ORDER BY popu DESC OFFSET 4 ROWS FETCH NEXT 2 ROWS ONLY;

---

10. Scala Function

1) Function

- 데이터베이스에서 함수는 반드시 리턴을 해야 합니다.