프로그래머스-mysql

SELECT \*from CAR_RENTAL_COMPANY_CARwhere OPTIONS like '%네비게이션%'order by CAR_ID desc;LIKE특정 문자열이 포함 되어 있는가'%문자열%'ex) "SUB1,문자열,SUB2"'%문자열'ex) "SUB1,SU

SELECT ANIMAL_ID,NAME,SEX_UPON_INTAKE from ANIMAL_INSwhere NAME in ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')ORDER BY ANIMAL_IDin특정 칼럼이 튜

SELECT ANIMAL_ID,NAMEfrom ANIMAL_INSwhere NAME like '%EL%'and ANIMAL_TYPE='DOG'order by NAME연산자관계 연산자논리 연산자기타 연산자

SELECT ANIMAL_ID,NAME,'O' as 중성화 from ANIMAL_INSwhere SEX_UPON_INTAKE like '%Neutered%'or SEX_UPON_INTAKE like '%Spayed%'union SELECT ANIMAL_ID,NAME,'

SELECT ai.ANIMALID,a_i.NAME from ANIMALINS as ai join ANIMALOUTS as ao on ai.ANIMALID=ao.ANIMALID order by TIMESTAMPDIFF(second,ai.DATETIME,ao.DATETIM

select left(PRODUCT_CODE,2) as CATEGORY,COUNT(\*) as PRODUCTSfrom PRODUCT group by CATEGORYgroup by\-> 특정 칼럼을 기준으로 그룹화(select 시에 별칭으로 정해준 칼럼을 써도 된다.)

SELECT ANIMAL_ID,NAME,DATE_FORMAT(DATETIME,"%Y-%m-%d") from ANIMAL_INSDATE_FORMATmysql의 datetime자료형을 원하는 포멧으로 변환해준다.

SELECT HISTORY_ID,CAR_ID,DATE_FORMAT(START_DATE,"%Y-%m-%d") as START_DATE,DATE_FORMAT(END_DATE,"%Y-%m-%d") as END_DATE,'장기 대여' as RENT_TYPEfrom CAR_RE

SELECT ROUND(AVG(DAILY_FEE)) as AVERAGE_FEEfrom CAR_RENTAL_COMPANY_CARwhere CAR_TYPE='SUV'AVG\->평균을 계산ROUND\->명시한 자릿수+1에서 반올림(명시가 안되면 소수점 첫째 자리에서 반올림)

SELECT FLAVOR from FIRST_HALForder by TOTAL_ORDER desc,SHIPMENT_ID ascorder by 다중정렬ex)order by col1 asc,col2 desc;\->col1을 기준으로 오름차순 정렬 후 같은 col1값에 대해

SELECT PT_NAME,PT_NO,GEND_CD,AGE, CASE WHEN TLNO IS NULL THEN 'NONE' ELSE TLNO END as TLNOfrom PATIENTwhere AGE<=12and GEND_

SELECT ANIMALID,NAME,DATEFORMAT(DATETIME,"%Y-%m-%d") from ANIMAL_INS DATEFORMAT_ mysql의 datetime자료형을 원하는 포멧으로 변환해준다.

SELECT COUNT(\*) as USERSfrom USER_INFOwhere AGE between 20 and 29and DATE_FORMAT(JOINED,'%Y')='2021'betweenex)between a and b\->a이상 b이하를 충족시키는 조건

SELECT NAMEfrom ANIMAL_INSwhere DATETIME=(select min(DATETIME) from ANIMAL_INS)sub selectMYSQL은 select에서 모든 데이터를 먼저 로드하기 때문에 일반적인 where DATETIME=min(D

SELECT CAR_ID,ROUND(AVG(DATEDIFF(END_DATE,START_DATE)+1),1) as AVERAGE_DURATIONfrom CAR_RENTAL_COMPANY_RENTAL_HISTORYgroup by CAR_IDhaving AVERAGE_DUR

SELECT CAR_TYPE,count(\*) as CARSfrom CAR_RENTAL_COMPANY_CARwhere FIND_IN_SET('통풍시트', OPTIONS)or FIND_IN_SET('가죽시트', OPTIONS)or FIND_IN_SET('열선시트', OP

SELECT USER_ID,PRODUCT_IDfrom ONLINE_SALEgroup by USER_ID,PRODUCT_IDhaving count(\*)>=2order by USER_ID asc,PRODUCT_ID desc;\_다중 group by텍스트\_여러개의 col

SELECT truncate((PRICE/10000),0)10000 as PRICE_GROUP,COUNT() as PRODUCTSfrom PRODUCTgroup by PRICE_GROUPorder by PRICE_GROUP asc;truncateex)truncate(1

SELECT CONVERT(DATE_FORMAT(DATETIME,'%H'),signed integer) as HOUR,COUNT(\*) AS COUNTfrom ANIMAL_OUTSgroup by HOURhaving HOUR>=9 and HOUR<20order by

select count(distinct NAME)from ANIMAL_INSwhere NAME is not NULLdistinct\->중복되는 데이터를 제거

user라는 테이블에서 데이터를 완전히 삭제한다면 복구할때 다시 삽입해야함(삽입시 인덱싱을 할 수도 있기에 속도저하까지 생길 수 있음)deleted라는 필드를 추가하여 삭제상태를 나타냄\*deleted는 1비트의 이진수로 해놓았는데 1이면 삭제된 상태,0이면 삭제되지