24.7.11 (MYSQL 3주차, JAVA)

Mysql Part. 3

## replace ##

select restaurant_name "원래 상점명",

replace(restaurant_name, 'Blue', 'Pink') "바뀐 상점명"

from food_orders

where restaurant_name like '%Blue Ribbon%'

 

select addr, replace(addr, '문곡리', '문가리') ch_addr

from food_orders

where addr like '%문곡리%'

 

## substr(조회 할 컬럼, 시작 위치, 글자 수) ##

SELECT addr "원래 주소",

substr(addr, 1, 2) "시도"

from food_orders fo

where addr like '%서울특별시%'

 

 

## concat 문자 붙이기(컬럼, 한글, 영어, 숫자, 기타 특수문자 ##

select restaurant_name "원래 이름",

addr "원래 주소",

concat(restaurant_name, '-', cuisine_type) "음식타입별 음식점",

concat('[', substring(addr, 1, 2), '] ', restaurant_name) "바뀐 이름"

from food_orders

where addr like '%서울%'

 

## if ##

select addr "원래주소",

if(addr like '%평택군%', replace(addr,'문곡리','문가리'), addr) "바뀐주소"

from food_orders

where addr like '%문곡리%'

 

select substr(if(email like '%gmail%', replace(email, 'gmail', '@gmail'), email), 10) "이메일 도메인",

count(customer_id) "고객 수",

avg(age) "평균 연령"

from customers

group by 1

 

## case문 when A then B ##

select case when cuisine_type = 'korean' then '한식'

when cuisine_type in ('japanese', 'chinese') then '아시아'

else '기타' end "음식타입",

cuisine_type

from food_orders

 

## 조건이 더 이상 없을때 else 생략 가능 ##

select order_id, price, quantity,

case when quantity=1 then price

when quantity>=2 then price/quantity end "음식 단가"

from food_orders

 

 

## use segmantaion ##

select case when (age between 10 and 19) and gender = 'male' then '10대 남성'

when (age between 10 and 19) and gender = 'female' then '10대 여성'

when (age between 20 and 29) and gender = 'male' then '20대 남성'

when (age between 20 and 29) and gender = 'female' then '20대 여성' end "고객 분류",

name, age, gender

from customers

where age between 10 and 29

 

select restaurant_name,

price/quantity "단가",

cuisine_type,

order_id,

case when (price/quantity <5000) and cuisine_type='Korean' then '한식1'

when (price/quantity between 5000 and 15000) and cuisine_type='Korean' then '한식2'

when (price/quantity > 15000) and cuisine_type='Korean' then '한식3'

when (price/quantity <5000) and cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '아시아식1'

when (price/quantity between 5000 and 15000) and cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '아시아식2'

when (price/quantity > 15000) and cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '아시아식3'

when (price/quantity <5000) and cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '기타1'

when (price/quantity between 5000 and 15000) and cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '기타2'

when (price/quantity > 15000) and cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '기타3' end "식당 그룹"

from food_orders

 

 

## 조건문 활용한 수수료 구해보기

select case when delivery_time > 30 then price*0.1*if(addr like '%서울%', 1.1, 1)

when delivery_time > 25 then price*0.05*if(addr like '%서울%', 1.1, 1)

else 0 end "수수료",

restaurant_name, order_id, price,delivery_time, addr

from food_orders

 

select case when day_of_the_week = 'weekday' then 3000*if(quantity>3, 1.2, 1)

when day_of_the_week = 'weekend' then 3500*if(quantity>3, 1.2, 1)

end "배달할증료",

restaurant_name, order_id, day_of_the_week, quantity

from food_orders

 

## 3주차 문제 ##

select order_id, restaurant_name, day_of_the_week, delivery_time,

case when day_of_the_week = 'weekday' and delivery_time >= 25 then 'Late'

when day_of_the_week = 'weekend' and delivery_time >= 30 then 'Late'

else 'On-time' end "지연 여부"

from food_orders

 

JAVA

## 닉네임 ##

import java.util.Random;

public class NicknameCreator {

    public String[] firstList = {"기철초풍", "멋있는", "재미있는"};

    public String[] secondList = {"도전적인", "노란색의", "바보같은"};

    public String[] thirdList = {"돌고래", "개발자", "오랑우탄"};

    public String createRandomNickname() {

        Random random = new Random();

        int first_randomIndex = random.nextInt(firstList.length);

        int second_randomIndex = random.nextInt(secondList.length);

        int third_randomIndex = random.nextInt(thirdList.length);

        String name = firstList[first_randomIndex]+secondList[second_randomIndex] + thirdList[third_randomIndex];

        return name;

    }

    public static void main(String[] args) {

        NicknameCreator randomNicknameCreator = new NicknameCreator();

        String Nickname = randomNicknameCreator.createRandomNickname();

        System.out.println(Nickname);

    }

}

 

## 정적, 클래스 변수 입출력 값 차이

public class staticTest {

    static int stVar; // 정적 변수

    int var; // 클래스 변수

   

    public static void main(String[] args) {

       

        staticTest t1 = new staticTest();

        t1.var = 1;

        t1.stVar = 1;

       

        staticTest t2 = new staticTest();

        t2.var = 2;

        t2.stVar = 2;

       

        System.out.print("var : " + t1.var + ", stVar : " + t1.stVar + "\n");

        System.out.print("var : " + t2.var + ", stVar : " + t2.stVar + "\n");

    }

}

결과값

var : 1, stVar : 2
var : 2, stVar : 2