SQL 1

Feb 19, 2024

>show databases; : db목록을 보여줌  

>use sample; : sample db를 선택함  

>DESC sample21;  : sample21의 테이블구조가 나옴(sql명령x) 
            -> 테이블구조참조(describe)

테이블구조
Field : 열이름
Type : 열의 자료형
-INTEGER 정수(소수점x)
-CHAR 최대가 정해진 문자열로, 남는 공간은 공백으로 채움
-VARCHAR 최대가 정해진 문자열이나, 실제 문자열길이만큼만 저장
-DATE 연월일
-TIME 시분초
Null : Null값 허용여부
Key : 해당열이 Key로 지정되어있는지.
Default : 생략시 기본값.(NULL)

>SELECT * FROM a; : a테이블의 *(모든)열.  
>SELECT name FROM a : a테이블의 이름 열  
>SELECT 열1, 열2 ... FROM 테이블명 : 결과는 지정한 순서대로 표시

SECLECT구 -> FROM구 -> WHERE구 (WHERE구는 생략시 모든 행)
ex)
SELECT * FROM a; : 테이블a의 모든 열에서 모든 행 추출
SELECT * FROM a WHERE no=2; : 테이블a의 no열이값 2인 ‘행’의 모든열 추출
SELECT name FROM sample21 WHERE no=2; : 테이블sample21의 no열값이 2인 ‘행’의 name열만 추출
SELECT no FROM sample21 WHERE name=‘홍길동’; : 테이블sample21의 name열 값이 “홍길동"인 행의 no열만 추출.

[조건연산자]

= : ==
<> : !=
>
<
>=
<=

t = 1;
t = ‘문자열’;
t = ‘2024-01-01’;
t = ‘23:29:00’;

[NULL값 검색]

=NULL이 아닌, IS NULL로 검색

>SELECT * FROM sample21 WHERE birthday IS NULL;

[패턴매칭 검색] LIKE

간단한 패턴매칭이라면 LIKE
복잡한 매턴매칭이라면 정규표현식(Regular Expression)사용

> SELECT * FROM sample25 WHERE text1 LIKE 'ㅡㅡ';  

> '%test' : test앞에만 공백을 포함한 문자가 있는 것  
> '%test%' : test 앞 뒤로 공백을 포함한 문자가 있는 것  
> 'test%' : test 뒤에만 공백을 포함한 문자가 있는것  

> '%'를 찾고자 할 경우 : \%를 사용 ex. '%\%'   '\%%'  ...  

> '를 찾고자 할 경우 : '''';  ( '  ''   ') ex. it's => 'it''s';

[정렬] ORDER BY

클라이언트로 행 순서를 바꾸어 반환하는 것뿐, 원본 데이터의 행 순서를 변경하지는 않는다.
=> SELECT는 참조만 할 뿐 변경하지 않음

> SELECT * FROM oo ORDER BY age(열이름);

검색결과의 행 순서를 정렬해서 반환함
ORDER BY생략시 DB에 저장된 순서로 반환

ORDER BY (열이름) DESC; : 내림차순(descending)
ORDER BY (열이름) ASC; : 오름차순(ascending)(생략가능, 다만 dbms마다 다를 수 있으니 주의)

a열이 같은경우 b열순으로 정렬하고 싶음
=> ORDER BY a, b; : a오름차순 + b오름차순

NULL 값이 저장된 열의 정렬
=> 가장 크거나 가장 작은 값으로 취급함(제품마다 다름)
MySQL의 경우 가장 작은 값으로 취급.

[결과 행 제한] LIMIT구

MySQL, PostgreSQL 문법임

> SELECT * FROM table LIMIT 3; : 최대 3개행 출력.

OFFSET

> 1페이지 : LIMIT 5 OFFSET 0; => 0번부터 5개  
> 2페이지 : LIMIT 5 OFFSET 5 => 5번부터 5개  
(배열인덱스처럼 0부터 시작) 즉, (시작행 -1)로 표기하기

[서버 내부처리 순서]

WHERE -> SELECT -> ORDER BY

[SELECT구에서 연산]

> SELECT *, price*quantity FROM table; : table을 출력하고,  
 price열과 quantity열을 곱한 열을   price*quantity라는 이름의 열로 옆에 출력  

열명을 재지정하고싶은 경우, price*quantity AS newName 으로 재지정 가능  
(AS생략가능)  
> SELECT *, price*quantity AS amount FROM table;

[WHERE구에서 연산]

-price*quantity >= 1000 인 행을 출력하고자 할 때  
> SELECT *, price*quantity AS amount FROM table WHERE amount >= 1000; ERROR발생

=> 서버 내부처리 순서때문임 : 표준은 아니나 대부분의 데이터베이스는 WHERE구 -> SELECT구 순서로 처리함
그렇기에 WHERE amount에서 amount를 찾을 수 없음

> SELECT *, price*quantity AS amount FROM table WHERE price*quantity >= 1000;

[ORDER BY구에서 연산]

ORDER BY구는 SELECT구 보다 나중에 처리되므로 별명(alias)를 사용할 수 있음

> SELECT *, price*quantity AS amount FROM table ORDER BY amount DESC;

[NULL값의 연산]

NULL과 연산시 결과는 모두 NULL이 됨
ex) NULL+1 => NULL, 1/NULL => NULL division by zero발생X

[함수]

MOD(a,b) : a%b
ROUND(a) : a 소수점 첫째자리 기준 반올림
ROUND(a,b) : a 소수점 b+1자리에서 반올림, b가 음수일경우 10단위 반올림도 가능
TRUNCATE() : 버림

[문자열 연산]

-문자열 결합

MySQL : CONCAT(a,b)
SQL Server : +
Oracle, DB2, PostgreSQL : ||

price=10 : int
quantity=10 : int
unit=개 : varchar

> SELECT CONCAT(quantity,unit) FROM table;   => 10개
> SELECT CONCAT(price,quantity) FROM table; => 1010;

-문자열 추출

ex) date를 YYYYMMDD와 같이 문자열로 저장하는 경우 해당 데이터에서 년월일을 따로 추출하고싶을 때 사용
SUBSTRING(‘string’,i,l) : string의 i번째부터 길이 l만큼 추출

mysql> SELECT SUBSTRING('12345',1,3);
+------------------------+
| SUBSTRING('12345',1,3) |
+------------------------+
| 123                    |
+------------------------+

-앞뒤(중간x) 스페이스 제거

ex)자료형이 CHAR인 경우 남은 공간은 공백으로 채워지는데 이를 제거하는데 유용

TRIM(‘string’)
TRIM(‘ABC ‘) => ‘ABC’
TRIM(’ A BC’) => ‘A BC’
TRIM(’ ABC ‘) => ‘ABC’

-문자열 길이

CHARACTER_LENGTH() (CHAR_LENGTH())

OCTET_LENGTH() : 문자열의 길이를 바이트 단위로 반환
!주의할점!
인코드방식, ‘문자세트’에 따라 한 문자당 바이트 수가 달라질 수 있음
아래 두 인코드방식에서 ASCII문자는 1바이트로 같지만, 한글은
EUC-KR의 경우 1문자당 2바이트
UTF-8의 경우 1문자당 3바이트를 차지
이런 이유로 문자단위가 아닌 바이트단위로 조작할 경우 주의가 필요함.

[날짜 연산]

날짜, 시간 저장은 제품에 따라 다름
날짜+시간 자료형, DATE TIME DATETIME과 같이 세분화해 지원하기도 함

날짜나 시간 데이터는 사칙연산이 가능
그 결과값으로 동일유형의 데이터를 반환하기도 하며, 기간형(interval) 데이터를 반환하기도 함
ex) 10일간, 10분 ..

-시스템 날짜 : 하드웨어 상 시계로부터 실시간으로 얻을 수 있는 일시적 데이터

SQL표준함수 CURRENT_TIMESTAMP(파라미터x)

mysql> SELECT CURRENT_TIMESTAMP;
+---------------------+
| CURRENT_TIMESTAMP   |
+---------------------+
| 2024-02-24 23:17:37 |
+---------------------+

-날짜 덧셈

mysql> SELECT CURRENT_TIMESTAMP + INTERVAL 1 DAY; : 현재 + 1일후

-날짜형 간의 뺄셈 : 두 날짜 사이의 차이
mysql> SELECT DATEDIFF('2024-01-01','2024-02-24');
+-------------------------------------+
| DATEDIFF('2024-01-01','2024-02-24') |
+-------------------------------------+
|                                 -54 |
+-------------------------------------+
ORACLE의 경우 '2024-01-01' - '2024-02-24'

[CASE문으로 데이터 변환]

간단한 처리의 경우 사용자 정의 함수를 작성하지 않고 CASE문으로 처리
a열이 NULL인 경우 0으로 처리하기

mysql> SELECT CASE WHEN a IS NULL THEN 0 ELSE a END AS 'a(null=0)' FROM sample37;

-COALESCE( … ) 함수
주어진 파라미터들 중 가장 앞에 있는 NULL이 아닌 값을 반환
mysql> SELECT COALESCE(a,0) FROM sample37; : a가 NULL인 경우 0이 반환됨

-검색 CASE : CASE WHEN 조건식 THEN 식

mysql> SELECT a "코드",
    -> CASE
    ->  WHEN a=1 THEN "남자"
    ->  WHEN a=2 THEN "여자"
    ->  ELSE "미지정"
    -> END AS "성별" FROM sample37;
=> if-else와 비슷!

-단순 CASE : CASE 식1 WHEN 식2 THEN 식3

mysql> SELECT a AS "코드",
    -> CASE a
    -> WHEN 1 THEN "남자"
    -> WHEN 2 THEN "여자"
    -> ELSE "미지정"
    -> END AS "성별" FROM sample37;
=> switch와 비슷!
+------+--------+
| 코드 | 성별   |
+------+--------+
|    1 | 남자   |
|    2 | 여자   |
| NULL | 미지정 |
+------+--------+

-SELECT구 외 어디에나 CASE문 사용가능
-ELSE 생략시 ELSE NULL

-단순 CASE에서 식에 NULL이 들어갈 경우 비교할 수 없음( WHEN NULL THEN ..)
=> 검색 CASE문으로 WHEN a IS NULL THEN .. 을 사용하자