서누레코즈

풀면서 개인적으로 골때렸던 문제를 여기서 복습차원에서 한번 다시 풀어보겠다 .

나한테 골때렸던거라 님들한텐 안골때릴수도 ㅎ

날짜문제)

사원테이블에서 사원의 입사후 다음주 일요일의 날짜를 구하시오

사원테이블의 원본이 없으므로 여기서는 문제를 풀때 임의의 날짜를 정해서 풀어보겠다.

-- 임의의 사원 정보 : 
-- 이름 : 이선우
-- 입사일 : 2015-05-06

select '이선우','2015-05-06' 입사일,
       date_add('2015-05-06',
       interval 7 - dayofweek('2015-05-06')+ 1 day);
       
 -- 풀이
 
 일단 이선우의 입사일은 2015-05-06 이다. 이 날짜를 기준으로 다음주 일요일이 언제인지 구하는 것인데. 
 5월 6일의 요일을 date_add를 통해 토요일로 만들어야한다. 토요일로 만드는 이유는 초기화의 개념으로 받아들
 이면 편한다. 날짜의 기준을 잡을때 어떤 요일이던 토요일로 만들어주고 거기서 원하는 요일의 코드를 dayofweek
 함수로 반환해서 초기화한 토요일에 더 해주면 자연스레 다음주의 원하는 요일을 구할 수 있다. 
 
 date_add함수를 이용하여 기준날짜로부터 몇일을 추가하는지 interval을 이용해서 일주일인 7일을 기준으로 잡고
 일요일로 만들기 위해 dayofweek함수를 이용하여 현재요일의 코드를 반환받아 7에서 빼주면 토요일까지 남은 날짜가 
 나올 것이고 남은 날짜를 기준날짜에 더해주면 자연스레 토요일이 된다. 
 
 토요일이 되고나서 dayofweek 함수로 요일들을 뽑아내면 1: 일요일 2: 월요일 3: 화요일 ...
 이렇게 나오는데 원하는 요일인 일요일인 +1을 더해주면 다음주 일요일의 날짜가 출력되는걸 확인할 수 있다.

풀면서 골때렸던 문제가 뭐가 더 있나 훑어봤는데 2일넘게 다 반복적으로 훑어봤던거라 뭔가 다 익숙한 느낌이 난다. 

그래서 이문제만 올리겠다 ..컹스컹스방구

1. 현재날짜와 시간 : now(), current_timestamp, sysdate()

select now(),current_timestamp, sysdate();

2. 현재 날짜 : curdate() , current_date()

   현재 시간 : curtime(),current_time

select curdate(),current_date;

3. 날짜계산 :   adddate(날짜,날짜간격)
                   subdate(날짜,날짜간격)
                   date_add(날짜, 날짜간격)
                   date_sub(날자, 날짜간격)
                  간격 식별문자열 : second, minute,hour,day,month,year

select adddate('2020-12-10',interval 15 day), -- 날짜 더하기
       adddate('2020-12-10',interval 1 year),
       subdate('2020-12-10',interval 1 year), -- 날짜 빼기
       subdate('2020-12-10',interval 1 year);

4. 날짜 / 시간 추출 : year, month, day, week, hour, minute, second

select year(now()) 년, month(now()) 월,day(now()) 일 ,week(now()) '2020~현재/주',
       hour(curtime()) 시 ,minute(curtime()) 분,second(now()) 초;

+ dayofyear(올해중 몇일째 ) , dayofmonth(이달의 며칠), dayofweek(요일), monthname(달), dayname(요일)

select dayofyear('2020-12-10') 올해중몇일째,
       dayofmonth('2020-12-10') 며칠,
       dayofweek('2020-12-10') 요일, -- 1: sunday 2:monday ~~~~
       monthname('2020-12-10') 월이름,
       dayname('2020-12-10') 요일이름;

5. 날짜계산 : datediff(날짜1, 날짜2) 

두 날짜 사이의 일수를 반환

-- 날짜계산 : datediff(날짜1,날짜2)
-- 오늘기준 크리스마스가 몇일 남았는지 계산
select datediff('2020-12-25','2020-12-10');

6. 날짜 / 시간 계산 : timestampdiff(유형, 날짜1, 날짜2)

select timestampdiff(day,'2020-12-10','2020-12-25') '남은 날',
       timestampdiff(hour,'2020-12-10','2020-12-25')'남은 시간',
       timestampdiff(minute,'2020-12-10','2020-12-25')'남은 분';

+ 달의 마지막일 출력 : last_day(날짜)

select last_day('2020-12-10'); -- 2020-12-31 출력 확인

7. 날짜변환함수 : str_to_date(문자열, 형식문자열)
   형식문자열 : %H/%h(24시간,12시간간, %i(분) , %s(초)   -시간관련
                    %Y/y(4자리연도,마지막2자리연도), %m(월순서), %d(일), %W(요일), %M(월이름)  -날짜관련

select str_to_date('2020-12-10','%Y-%m-%d'),
       str_to_date('12-10-20','%m-%d-%Y');

8. 날짜변환함수2 : date_format()

SELECT DATE_FORMAT('2017-06-15', '%M %d %Y'),
       date_format('2020-12-10','%W %M'),
       date_format('2020-12-10','%a %b %j'),
       date_format('2020-12-10 12:14:12','%p %h:%i:%s');

9. 숫자문자변환 : cast(대상 as 자료형)
   자료형 : char, date/time, decimal, float
   묵시적(자동) 형변환, 명시적(수동) 형변환

select substr(123456789,5,1);
-- 숫자형 데이터가 자동으로 문자형으로 변환

select 1234567890, cast(1234567890 as char); -- 숫자는 오른쪽정렬 문자형은 왼쪽으로 정렬
    -- cast를 char형을 바꿔서 왼쪽정렬해서 출력되는 것을 확인할 수 있다~

select 10/3, 10/'3';
    -- 첫번째 값은 float형으로 소수점자리가 4자리까지나오고
    -- 두번재 값은 문자가 실수(float)로 자동형변환 되어 더블형으로 소수점자리가 많아지네요잉~
select 10/cast( '3' as signed ),10/cast('3' as float);
select 10/cast('3' as integer), 10/cast('3' as float );

select cast(124035 as time);
select cast(20201210 as date);
    -- 시간, 날짜도 변환가능 (단, 정수형태로 작성해야 함)

mariaDB 내장함수 :

1. 사용자의 편의르 위해 미리 작성해둔 함수 

2. 숫자/문자/날짜/시간/변환/집계/분석 함수등이 제공

숫자함수 :

1. 절댓값 계산 : abs(값);

select abs(-4.5),abs(4.5);  -- 4.5 / 4.5 출력확인

2. 반올림. : round(값, 자리수);

select round(4.1), round(4.5);              -- 4.1 / 4.5 출력확인
select round(123.456,1), round(123.456,2);  -- 123.5 / 123.46 출력확인

3. 무조건 내림 : floor(값);

select round(4.1), round(4.5);    -- 4 / 4 출력확인
select round(-4.1), round(-4.5);  -- -5 / -5 출력확인

-- 음수에서 floor 하면 더 작아져야함으로 -5가된다. 

4. 무조건 올림 : ceil(값);

select ceil(4.1), ceil(4.5); -- 5 / 5
select ceil(-4.1), ceil(-4.5); -- 4 / 4

-- 위와 마찬가지로 음수에서는 값이 커지면 절댓값 자체는 낮아져야한다. 

5. 나머지 연산 : mod(분자,분모);

select mod(10,5),mod(10,3); -- 0 / 1 출력확인

6. 난수 생성 : random (0~1사이 실수값이 출력이된다);

select rand(), rand()*10, rand()*100;

 ex) n~m 사이 임의의 정수 생성

-- n~m 사이 임의의 정수 생성
-- n + rand()*(m-n+1)  4~14 사이의 임의의 정수 생성

select floor(4+rand()*(14-4+1));

문자함수:

1. 문자의 ASCII 코드 출력 : ascii(문자);

select ascii('a'), ascii('A'),ascii('0'); -- 97 / 65 / 48 출력확인

2. ASCII 코드의 문자 출력 : char(숫자);

select char(97),char(65),char(48); -- a / A / 0 출력확인

3. 문자열 길이 : length(문자열);

   영문자 : 1byte

   유니코드 : 3byte 한글은 유니코드 3byte먹는걸 확인할 수 있다.

select length('Hello, World!!');  -- 14 출력확인
select length('가나다라마바');      -- 18 출력확인

4. 문자열 연결 : concat(문자열1,문자열2, ... );

select concat('Hello',',World','!!'); -- Hello,World!! 출력확인

5. 문자열 추출 : left(문자열, 길이); , right(문자열, 길이);          left는 왼쪽에서 부터 right는 오른쪽에서부터 

                    mid(문자열, 시작, 길이); , substring(문자열, 시작, 길이); or substr(문자열, 시작, 길이)

+ substring_index 함수

select left('Hello,World',5); -- Hello 출력확인
select right('Hello,World',5);-- World 출력확인
select mid('Hello,World',1,5);-- Hello 출력확인


SELECT SUBSTR('foobarbar' FROM 4); -- barbar 출력확인
SELECT SUBSTR('Quadratically',5,6); -- ratica 출력확인
SELECT SUBSTR('Sakila', -3); -- ila 출력확인
SELECT SUBSTRING('Sakila', -5, 3); -- aki 출력확인
SELECT SUBSTRING('Sakila' FROM -4 FOR 2); -- ki 출력확인
SELECT SUBSTRING('Quadratically',5); -- ratically 출력확인

select substring_index('Hello_World_!!','_',1);
-- 첫번째 ' _ '로 분리한 문자열을 출력

select substring_index('Hello_World_!!','_',2);
-- 두번째 ' _ '로 분리한 문자열을 출력

6. 대소문자 변환: upper() or ucase() , lower() or lcase()

select upper('abc'), lower('ABC'); -- ABC , abc 출력확인
select ucase('abc'), lcase('ABC'); -- ABC , abc 출력확인

7. 공백처리 : trim() , ltrim() , rtrim()

select trim(' abc '),              -- 앞뒤의 공백을 전부 제거
       rtrim('abc           '),    -- 뒤 공백 전부 제거
       ltrim('           abc');    -- 앞 공백 전부 제거

8. 문자열 채움 : pad(문자열, 총길이 , 채움문자)

왼쪽에 채우려면 lpad를 오른쪽을 채울려면 rpad를

select lpad('1234567890',10,'_'),  
       lpad('12345',10,'_'),
       rpad('12345',10,'_'),
       rpad('가나다',10,'_');

9. 문자열 바꾸기 : replace(문자열, 찾을문자, 바꿀문자)

select replace('Hello,World','World','mariadb'),
       replace('Hello,World','World',''); -- 공백으로 지울 수도 있다

10. 문자열 뒤집기 : reverse()

select reverse('12345'), reverse('가나다라마');

11. 문자열 위치 찾기 : instr(문자열 , 찾을위치)

select instr('Hello,World','W'),
       instr('Hello,World','H'),
       instr('Hello,World','!');

12. Null 처리 함수 : coalesce(문자열, null일때 대체 값) or ifnull(문자열, null일때 대체값)

select Null, coalesce(Null,'널임'),
             coalesce('Hello','널임'),
             IFNULL(Null,'널임'),
             IFNULL('Hello','널임');

+ 조건 판단 :  if (조건식, 참일때값, 거짓일때 값)

  다중 조건 : case when 조건식 then 처리값 or else 처리값 or 처리값 end

SELECT case when (5>3) then '겁나커요이잉~' else '겁나작아요이잉~' end,
       case when (3>3) then '겁나커요이잉~' else '겁나작아요이잉~' end;

깃허브를 알게된뒤 부터 블로그에 올리지 않고 바로 깃허브에 학원에서 올린 코드들을 보며 복습해왔는데 

확실히 사람이 편해지면 게을러진다. 나만 그런걸수도 있지만 컹스컹스

그전에 올린 글을 보니 DAY1 통합개발환경 구축하는 이클립스를 설치했었는데 친구가 블로그에 복습하면서 하는게 좋다고 해서 나도 한번 다시 블로그에 수업에 했던 내용을 적어 복습할겸 적어본다. 

깃허브로 코드들만 보니 뭔가 복습이 제대로 안되고 있다고 느끼기도 해서  컹스컹스방구

일단 오늘 MySQL에서 뷰와 내장함수를 배웠다.  

뷰( View) :

1. 다른 테이블을 기반으로 만들어진 가상 테이블

2. 데이터를 실제로 저장X , 논리적 존재하는 테이블 but 일반테이블과 동일한 방법으로 사용

3. 뷰를 통해 기본테이블( 뷰를 만드는 물리적 테이블)의 내용을 쉽게 조회 가능하나 기본테이블의 

데이터를 변경하는 것은 제한적이다. 

4. order by 정렬 불가능

뷰( View )의 목적 :

1. 특정필드에만 접근 허용( 보안의 목적 )

2. 데이터 조작의 간소화 ( 조인/검색 )

뷰( View ) 생성 코드 :

-- 뷰 생성
create view [뷰이름] as select문

-- 뷰의 이름을 생성후 as 로 뷰에 추가할 테이블을 select 문으로 설정해준다.

-- 뷰를 변경할수도 있다.

alter view [뷰이름] as select문

뷰( View ) 를 이용한 데이터 삽입, 수정,삭제 :

1. 뷰는 원본테이블의 분신이다.

2. 삽입, 수정, 삭제 작업은 원본테이블을 대상으로 진행

   단, 수정,삭제는 제한적으로 수행이 될 수 있다. 

3. 수정,삭제가 제한적으로 수행이 되는 테이블의 예로 지금 까지 내가 확인한 바로는

테이블에서 컬럼을 뽑아와 뷰를 만든 뷰에서 

수정을 하려고 할 때 안뽑아온 나머지 컬럼에 not null 이 적용 되어있으면 수정 불가능하다. 

문제) 원본테이블의 부재의 이유는 깃허브에 있다.. 아쉬운 마음에 사진을 첨부해본다... 

-- 제품번호가 p08, 재고량이 1000, 제조업체가 신선식품인 새로운 제품의 정보를
-- 제품1 뷰에 삽입해보자. 그런 다음 제품1 뷰에 있는 모든 내용을 검색해보자


create view 제품1 as
    select pdNum ,qunatity,company from productTest;

insert into 제품1 values ('p08',1000,'신선식품');

select * from 제품1;

문제2) 뷰를 만들때 여러 테이블을 조인하여 만들 수도 있다.. 

조인하여 만들면 편하게 데이터를 조회할 수 있는데 

아래의 문제가 그렇다. 

주문 테이블은 상품테이블과 고객테이블에 외래키를 두고 있다. 

그 외래키로 묶인걸 조인해서 뷰를 만들어 놓으면 각각의 테이블의 데이터 값들을 넘나들며 조회할 수 있는데 

예를들어 고객테이블의 csid 가 'banana' 인 고객의 이름은 '김선우'이다. 단순하게 김선우의 주문 제품이 무엇인지

알아보려면 뷰를 만들기 이전에는 셀랙문에 서브쿼리를 이용하든 조인을 이용해서 제품테이블과 주문테이블을 연결 해주어야 하지만 전부 묶어서 뷰를 만들어 놓으면 셀렉문을 쓸때 from에 뷰를 걸어놓고 where 절에 김선우 라는 조건을 걸어만 주면 된다. 앙 기모륑~

-- 주문, 상품, 고객테이블을 조인하고
-- 판매데이터라는 뷰를 만드세요
-- 또한, banana 고객이 주문한 상품이름을 조회하세요
create view 판매데이터 as
select * from orderTest ot join productTest pt using(pdNum)
                           join customerTest ct using(csid);

select pdName from 판매데이터 where csid='banana';

만들어진 뷰 테이블