2023.03.08 DB

DB MySQL ver

16. 관계연산자(AND, OR, BETWEEN, IN)

비교연산자만으로는 원하는 것을 여러단위로 뽑아낼 수없다.

조회수가 0,1,2 인 게시글 조회
SELECT * FROM NOTICE WHERE HIT = 0 OR HIT = 1 OR HIT = 2; 연속되지않을경우
-> 문제가 있는데 조건이 엄청 많아지면 힘들다.

AND,&& : 교집합 연산자
연속해있는 것이니 이것들을 이용하자.
SELECT * FROM NOTICE WHERE 0 <= HIT AND HIT <= 2;

범위연산은 자주 사용한다. 그래서 더 가볍게 사용할 수 있다.
BETWEEN a AND b : a이상 b이하
SELECT * FROM NOTICE WHERE HIT BETWEEN 0 AND 2;

조회수가 0,2,7인 게시글을 조회하시오.
연속되지 않은 값
SELECT * FROM NOTICE WHERE HIT = 0 OR HIT = 2 OR HIT = 7;
-> 이또한 개수가 많아지면 비효율적이게 된다.

IN(값1, 값2 ...) : IN 안에 있는 값이 있는 경우
SELECT * FROM NOTICE WHERE HIT IN (0,2,7);
연속되지않은 값 또는 또는 또는 이면? 이걸로 사용하자.

이 결과의 반대들을 뽑아내고 싶다?
NOT : 부정연산자
0 2 7이 아닌 게시글 조회
SELECT * FROM NOTICE WHERE HIT NOT IN (0,2,7);

17. 패턴 비교 연산자

LIKE, %, _

% : 애스터리스크 문자역할을 한다.
%에는 아무거나 와도 된다. 검색을 할 수 있게 된다.

회원중에서 '박'씨성
SELECT * FROM NOTICE WHERE NAME = '박%';
그러나 = 연산자를 사용하면안된다. = 를 넣으면 진짜 박%인 문자를 찾는다.
SELECT * FROM NOTICE WHERE NAME LIKE '박%';
그래서 이런거같은 문자를 찾아라 라고 한다. 무조건 패턴을 검색할때는 LIKE를 사용해야한다.

- : 한자리수의 애스터리스크 문자
회원중에 박씨 이고 이름이 외자인 회원을 조회
SELECT * FROM NOTICE WHERE NAME LIKE '박_'; 세글자면 박__
자릿수를 한정하고 싶다면 _을 하나씩 추가하면된다.

패턴연산자 연습문제
1.회원 중에서 박씨 성을 제외한 회원을 조회하시오
SELECT * FROM MEMBER WHERE NAME NOT LIKE '박%';

2.회원 중에서 이름에 '도'자가 들어간 회원을 조회
SELECT * FROM MEMBER WHERE NAME LIKE '%도%';

18. 정규식을 이용한 패턴 비교(REGEX_LIKE)

정규식이란? 사회표준으로 사용하는 텍스트로 찾는 검색패턴 기호이다.
문서내에서 패턴을 찾는것을 유닉스 버전에서 사용하는데 정해놓았다. 그러나 이정규식은 이제 어디에서나 쓰고 여기서도 함수를 제공한다.
https://regexlib.com/CheatSheet.aspx 정규식을 만드는 기호와 사람들이 만들어 놓은 정규표현식을 사용할수도있다.

전화번호 검색하고싶다 %-%-% 이러면 전화번호가 아닌것도 검색이 된다.
%에 문자가 아닌 숫자만 뽑고싶다.
자리수를 3~4

18.1 []

--01[016789] - [0-9]
대괄호안에 쓰면 한글자를 표현한다.

18.2 \d

숫자 넣을 수 있다.

18.3 {}

중괄호는 {반복할 횟수} 숫자 두개이상넣을 수 있다.
숫자하나면 그 만큼 {a,b}면 a또는 b

18.4 시작과 끝

시작할때는 ^ 끝일때는$을 넣어줘야한다.

결과는 다음과 같다.
^01[016-9]-\d{3,4}-\d{4}$

정규표현식은 LIKE대신 REGEXP를 넣으면된다.
SELECT * FROM NOTICE WHERE 컬럼 REGEXP '^표현식$';

오라클은 REGEXP_LIKE()함수에 담아서 사용해야 햇엇다.
SELECT * FROM NOTICE WHERE REGEXP_LIKE (COLUNM, ^표현식$);

^ $ 패턴이 시작해서 패턴이 끝나는 경우 딱 바로 010으로 시작해야 검색이 되는 것이다.
포함된것을 하고싶다면?
SELECT * FROM NOTICE WHERE TITLE REGEXP '01[016-9]-\d{3,4}-\d{4}';
^$을 없애면된다. 전화번호만 가지고 있다면 시작끝을 써줘야하는데 다른게 포함되어 있다면 시작끝을 없애자

박씨 성으로 시작하는 것을 찾고 싶다면
LIKE '박%'; 혹은
REGEXP '^박'; (끝을 지정안함)을 사용하면된다.

19. 문자열 비교를 위한 정규식(^\D\w+@...)

문자열인 이메일을 작성해본다고 생각해보자.
email정규식을 작성해보자
나라마다 다른데 com net org 만있다고 가정해보자
@ .org net com

19.1 \w: 문자

w 길이제한x 하나이상와야한다.
[0-9a-zA-Z]와 일치한다.

19.2 길이제한없이 하나이상

* : 0개이상
+ : 1개이상
? : 0또는 1

19.3 | : 또는

[org net com] 이러면 o r g n e t 각각이게 됨
(org|net|com)
()감싸서 하나의 문자를 고르게 만들 수 있다.

19.4 \D : [^0-9]

그런데 이렇게하면 숫자로 시작하는 도메인이 허용되게된다.
이걸막고자한다면 어떻게 해야할까?
\D를넣으면 [^0-9] 부정연산자가 된다.

완성된 이메일 검사 정규표현식
\D\w@\D\w.(org|net|com)

아직 못다른 부분들은 검색해서 보면서 이해해보는게 좋다.

20. ROWNUM 그리고 행 제한하기

페이징 하고 싶다.
회원목록에서 상위 5명만 조회하시오.
그런데 무엇을 기준으로 뽑을까?
순차적으로 뽑고자하면 정리가 안된거는 뽑을 수 없다.

Oracle에서는 rownum만 입력하면 됐었지만 MySQL은 조금 다르다.

@변수명 := 0;
으로 초기화 후
@변수명 := @변수명 + 1;
이런 식으로 사용하게 된다
초기화 쿼리가 없으면 조회할수록 계속 값 증가한다.

SELECT @ROWNUM:=@ROWNUM+1, A.*
FROM 테이블명 A, (SELECT @ROWNUM:=0) R
;

SET @ROWNUM:=0;
SELECT @ROWNUM:=@ROWNUM+1, A.*
FROM 테이블명 A
;

SELECT @ROWNUM:=@ROWNUM+1, A.*
FROM REQUEST_ACTION_HISTORY A
WHERE (@ROWNUM:=0)=0
;

총정리
-- 첫 사용시에는 초기화 필요
SET @ROWNUM := 0

-- 초기화 쿼리가 없으면 조회할수록 계속 값 증가
SELECT @ROWNUM := @ROWNUM + 1 AS R

-- FROM 에서 초기화
SELECT @ROWNUM := @ROWNUM + 1 AS R
  FROM TB2 A, (SELECT @ROWNUM:=0) R_T

-- WHERE에서 초기화
SELECT @ROWNUM := @ROWNUM + 1 AS R
  FROM T
 WHERE (@ROWNUM:=0) = 0;

-- 역순으로 정렬(서브쿼리만들고 정리)
  SELECT A.R
    FROM (
           SELECT @ROWNUM := @ROWNUM + 1 AS R
             FROM TB2 A, (SELECT @ROWNUM:=0) R_T
         ) A
ORDER BY RN DESC

21. 중복 값 제거하기 DISTINCT

중복된 값을 제거 DISTINCT() or DISTINCT
SELECT AGE FROM MEMBER; 중복되는 나이없이만 뽑고싶다.
SELECT DISTINCT AGE FROM MEMBER;
SELECT DISTINCT(AGE) FROM MEMBER;
함수처럼 사용하거나 그냥 사용할 수 있다.
다른 목록과는 사용할 수 없다. 하나의 목록만 보여주기 때문?

MYSQL은 두개를 필터링하는게 가능하다.
두 개 모두가 같을때의 중복 처리하려면
,로 나열하면된다.
SELECT DISTINCT AGE, NAME FROM MEMBER;

22. 중간요약과 함수 단원 안내

함수는 매우 많고 데이터베이스마다 아마 다르다.
하지만 내장 함수는 살펴보는 것은 필요하다.
교재에 다양한 것이 있다.
함수를 나중에 사용해보고자 하고 온라인에 튜토리얼을 검색해보자.
내장함수 6개 정도 사용해보자.
http://www.tcpschool.com/mysql/mysql_builtInFunction_string

23. 문자열 내장 함수 #1

값의 종류에 따라 함수가 달라진다.
이런 것들로 붙이거나 자르거나등등을 한다.
오라클과는 전혀 달라서 따로 공부해야할 듯하다.

23.1 문자열 추출 함수 SUBSTRING(문자열, 시작위치, 길이)

오라클은 SUBSTR
MySQL은 SUBSTRING이다.

MySQL에는 LEFT와 RIHGT도 있다.
SELECT LEFT('가나다라마바', 3) -- 가나다
RIGHT('가나다라마바', 3); -- 라마바

많이 사용되는 곳 모든 학생의 이름과 출생월만을 조회하시오.
월만 따로 하는 컬럼이 존재하지 않으니 날짜를 자르면된다.
SELECT NAME, SUBSTRING(BIRTHDAY, 6 ,2) FROM MEMBERS;
1998.01.05

자바와는 다르게 INDEX 1번부터임.

회원중에서 전화번호가 011으로 시작하는 회원의 모든정보를 출력하시오
조건을 의미하니 WHERE절에 작성해주면된다.

SELECT * FROM MEMBER WHERE SUBSTRING(PHONE, 1 , 3) = '011';
SELECT * FROM MEMBER WHERE LEFT(PHONE, 1 , 3) = '011';

쿼리를 잘이해햐아한다. 뽑아낼 내용이 무엇인지 조건이 필요한지.
행을 필터링해야하는지 컬럼을 필터링해야하는지.
그러나 함수가 WHERE절에 들어가면 전체 테이블이 1억개면 1억개의 함수가 실행되서 비효율적이게 된다.

연산자가 있다하면 그냥 연산자를 사용하자.
SELECT * FROM MEMBER WHERE PHONE LIKE '011%';
대체할게 있다면 대체하는 것으로 사용하자.

함수 연습 문제
1.회원중에서 생년월이 7,8,9인 회원의 모든 정보를 출력하시오
SELECT * FROM MEMBER WHERE SUBSTRING(BIRTHDAY, 6 , 2) IN ('07', '08', '09');

2.전화번호를 등록하지 않은 회원중에서
생년월일이 789인 회원의 모든정보를 출력
SELECT * FROM MEMBER WHERE PHONE IS NULL AND SUBSTRING(BIRTHDAY, 6 , 2) IN ('07', '08', '09');

23.2 문자열합치기 CONCAT() CONCAT_WS()

SUBSTRING의 반대 문자열 덧셈# 함수
SELECT CONCAT('홍','길동');

CONCAT_WS는 구분자를 이용해 문자를 합친다.
SELECT CONCAT_WS('-','2020', '01', '01'); -> 2020-01-01
CONCAT_WS('구분자', '문자열, '문자열')

23.3 문자열 트림 함수

빈공백을 없애는 함수
SELECT LTRIM(' HELLO '); 왼쪽공백 없애기
SELECT RTRIM(' HELLO '); 우측공백 없애기
SELECT TRIM(' HELLO '); 좌우공백 다 없애기

23.4 문자열 소문자 또는 대문자로 변경하기

SELECT LCASE('aBcDe'), LOWER('aBcDe') -> abcde 다 소문자로 바꿔줌
SELECT UCASE('aBcDe'), UPPER('aBcDe'); -> ABCDE 다 대문자로 바꿔줌

이걸 왜쓰나?
회원의 아이디가 newlec인 회원을 조회하시오 단 소/대문자를 가리지 않음.
SELECT * FROM MEMBER WHERE UPPER(MID) = 'NEWLEC';
컬럼 전체를 대소문자 가리지 않게 만들어준다.

23.5 문자열 대치 함수 REPLACE(문자열, 찾는문자열, 대치할 문자열)

SELECT REPLACE ('It is banana', 'banana', 'apple');

SQL Server 2017 버전부터 TRANSLATE 함수가 추가되었다. 오라클의 것을 가져왔다.
REPLACE 함수를 사용하여 여러 개의 문자를 치환하기 위해서는 REPLACE 함수를 중첩해서 사용해야 하는데, 이런 경우 쿼리문이 복잡해진다.
이것을 해결하기 쉽다.
TRANSLATE("문자열", "바꿀문자", "변환문자")
각각 나눠져셔 각각이 대치 된다.

REPLACE 확인문제
회원의 이름과 주소를 조회하시오.(단 주소는 빈칸없이 출력하시오.)
SELECT NAME REPLACE(ADDRESS, ' ', '') FROM MEMBER;

24. 문자열 내장 함수 #2

데이터는 우리가 사용하는 틀,격자안에 들어가잇다.
격자에 들어갓다는 것은 개념상이지 어떻게 분리해서 저장하는지는 알수 없다.
ROW를 필터링해달라거나 컬럼을 필터링해달라고 할 수 있다.
함수를 사용하면 더 다양하게 뽑을 수 있다.
이름은 몇 자인가? 성은 제외하고 이름만 어떻게 되는가?
이름이 외자인경우 뒤에 자를 붙일 것, 이름에 빈공백은 제거하고 출력할 것. 영문이름인 경우 첫글자는 소문자로
등등 자바같은 프로그램 언어로 조작을 해야한다. 이런것들을 함수로 다양하게 뽑을 수 있다.

함수는 좋은 녀석이다. 함수가 어떤건지 정보를 뽑을때 적절히 사용해야한다.

24.1 문자열 패딩함수 LPAD RPAD

[LPAD, RPAD]('문자열', 몇자까지? ,'채울문자열')
SELECT LPAD('1234', 6, '0'), RPAD('1234', 6, '0');
10개의 너비로 뽑아야하는데 모자라니 0으로 채워준다.
LPAD이니 왼쪽에 채워줌 RPAD 오른쪽으로 채워줌

이렇게 PAD는 너비로 고정을 하고 싶을때 사용한다.

회원의 이름을 조회하는데 이름의 길이가 3자가 안되는 경우 오른쪽을 밑줄 _ 로 채우시오
SELECT RPAD(NAME, 3, '_') FROM MEMBER;

->오라클의 경우에는 두번째 인자가 바이트 수인데 MYSQL은 그냥 문자수이다. 차이점이 있다.

24.2 첫문자를 대문자로 바꿔주는 함수 INITCAP(문자열)

mysql에는 없다!! 사용자 함수를 만들어서 표현해야한다.

오라클 사용방식
SELECT INITCAP('the ..') FROM DUAL;
SELECT INITCAP('the most important thing is ...') FROM DUAL; 과연 긴문자열일때 어디만 대문자가 될까?
-> The Most Important Thing Is 다 된다.
중간에 한글이 들어가도 한글 다음거도 대문자가 된다.

24.3 문자열 검색 함수 INSTR()

문자열 str 에서 substr 이 가리키는 위치를 반환하고,
일치하는 정보가 없다면, 0 을 반환한다.
INSTR(str, substr)

24.4 문자열 길이를 얻는 함수 CHAR_LENGTH(), LENGTH()

CHAR_LENGTH()는 문자열의 길이
LENGTH()는 바이트수를 나타낸다.

함수 중첩 만약에 회원의 전화번호 컬럼 포함된 문자'-'를 없앤 전화번호의 길이를 출력하시오
SELECT CHAR_LENGTH(REPLACE(PHONE,'-','') FROM MEMBER;

24.5 코드값을 변환하는 함수

SELECT ASCII('A');

24.6 코드값으로 문자를 반환하는 함수

SELECT CHAR(65);

25. 숫자 내장 함수(ABS/SIGN/ROUND/CEILING/FLOOR)

25.1 절대값을 구하는 함수 ABS(n)

SELECT ABS(35), ABS(-35);
-> 35 / 35

25.2 음수 양수 를 알려주는 함수 SIGN(n)

SELECT SIGN(35), SIGN(-35), SIGN(0);
-> 1 / -1 / 0

25.3 숫자의 반올림 값으로 알려주는 함수 ROUND(n,1)

SELECT ROUND(34.456789);
-> 34
SELECT ROUND(34.456789 , 2);
-> 34.46

몇째짜리에서 반올림할지 정할 수 있다.
사용할때마다 필요할때마다 검색해서 사용법 알아보자.

25.4 숫자의 나머지 값을 반환하는 함수 MOD(n1, n2)

SELECT FLOOR(17/5) 몫, MOD(17,5) 나머지;
-> 3 / 2

25.5 숫자의 제곱을 구하는 함수와 제곱근을 구하는 함수 POW||POWER(n1, n2) / SQRT(n)

SELECT POW||POEWR ,SQRT(25);
-> 25 / 5

2023.03.08 후기

함수들을 포함하여 사용법을 알아보았다.