SQL Query문 정리

• SELECT - 검색

SELECT 컬럼명 FROM 테이블명 WHERE 조건절;

select * from city
select * from city where Population > 50000

• INSERT INTO (레코드 추가)

INSERT INTO 테이블명(컬럼명) VALUES(값)

insert into cify(ID, Name) values (2, 'name');

• UPDATE - 수정

UPDATE 테이블명 SET 컬렴명 = 값 ... WHERE 조건절;

update city set name = "seoul" where ID = 2;
#id가 2인 레코드를 찾아 city를 seoul로 수정한다.

• DELETE - 삭제

DELETE FROM 테이블명 WHERE 조건절;

delete from city where ID = 100

• DISTINCT - 쿼리결과 중복제거

select distinct CountryCode from city where = CountyCode = 'KOR'
#국가코드가 'KOR'인 도시들의 국가코드를 중복제거해서 표시

selct count(distinct NAME) from ANIMAL_INS where NAME is not NULL;

• 논리연산자(AND, OR, NOT)

select * from city where = CountyCode = 'KOR' and/or Population >= 100000;
select * from city where = CountyCode != 'KOR' and/or Population >= 100000;

• 논리연산자(IN, BETWEEN)

select * from city where CountryCode in ('KOR', 'CHN', 'JPN');
select * from city where CountryCode = 'KOR' and (Population between 10000 and 50000);

• ORDER BY - 결과 정렬

ASC는 기본값이라 따로 표시하지 않아도 된다.

select * from city where CountryCode = 'KOR' order by Population desc/asc;

 

• LIMIT(ROWNUM, TOP) - 결과값 일부 조회

결과 중 상위 몇 개만 보여주는 쿼리

select * from city where CountryCode = 'KOR' order by Population desc limit 10;

• 집합함수(Aggregation Funtion)
  • count() - 레코드이ㅡ 개수를 리턴
  • sum()/avg() - 컬럼값의 합/평균을 리턴
  • min()/max() - 컬럼값의 최소 /최대 값을 리턴

테이블의 전체 레코드를 대상으로 특정컬럼을 적용해서 하나의 값을 리턴하는 함수

select count(*) from city where CountryCode = 'KOR';
select sum(Population) from city where CountryCode = 'KOR';
select avg(Population) from city where CountryCode = 'KOR';
select min(Population) from city where CountryCode = 'KOR';
select max(Population) from city where CountryCode = 'KOR';

 

• 유용한 함수들
  • length() - 레코드의 문자열 컬럼의 글자수 리턴
  • mid() - 문자열의 중간부분 리턴
  • upper()/lower() - 문자열을 대문자/소문자로 리턴
  • round() - 레코드의 숫자 컬럼값을 반올림해서 리턴

 #country 테이블의 국가명의 글자수 표시
select length(Name) from country;

 #country 테이블의 국가명의 앞쪽 세 글자 대문자로 바꿔서 표시
select upper(mid(Name, 1, 3)) from country;

#country 테이블의 기대수명을 소수점 첫째 자리에서 반올림해서 표시
select round(LifeExpentancy, 0) from country;

• JOIN

여러 개의 테이블을 공통 컬럼(보통 외래키)을 기준으로 합치는 기법

조인 시 서로 다른 테이블간의 동일한 컬럼명으로 인한 문제를 피하기 위해서 테이블명.컬럼명과 같은 형태로 사용한다.

• city 테이블과 country 테이블 조인

select * from city join country on city.CountryCode = country.Code;

 

INNER JOIN	조인시 NULL 값을 허용하지 않음(NULL값을 가진 레코드는 조인결과에서 빠짐)
LEFT JOIN	조인시 JOIN의 왼쪽 테이블의 NULL 값을 포함해서 표시
RIGHT JOIN	조인시 JOIN의 오른쪽 테이블의 NULL 값을 포함해서 표시
FULL JOIN	MySQL은 지원하지 않음

 

• ALIAS - 별명

- SELECT 결과를 표시할 때 테이블의 컬럼명이 노출되는 것을 꺼릴 때나 컬럼명이 복잡하거나 직관적이지 않아 다른 이름을 사용하고 싶을 떄 사용한다.

- 주로 조인을 할 경우 컬럼명이 복잡해 지는 것을 막기 위해서 주로 사용한다.

#city 테이블과 country테이블을 조인해서 국가코드가 'KOR'인 나라의 축약표시명(Abbr)과 정식명(FullName)을 표시하시오.
select city.countryCode As Abbr, countryName As FullName from city where city.CountryCode = 'KOR'

• VIEW

SELECT 결과물을 임시테이블의 형태로 저장해 계속 사용하고 싶을 때 사용한다.

사용용도가 끝나면 DROP VIEW 를 사용해서 삭제하면 된다.

#국가코드가 'KOR'인 도시의 국가코드와 국가명을 뷰로 생성하시오
create view sampleView as select city.countryCode, country.Name from city join country on city.CountryCode = country.Code where city.CountryCode = 'KOR';
select * from sampleView;

• SELECT INTO - 쿼리 결과를 새 테이블로 만들어 저장

create table city_new select * from city where CountryCode = 'KOR';

select * from city_new;

• INSERT INTO SELECT

기존에 존재하는 테이블에 쿼리 결과값을 새로운 레코드로 등록한다.

SELECT + INSERT INTO 가 합쳐진 쿼리

#city테이블에서 국가코드가 'KOR'인 도시를 찾아 city_kor 테이블에 넣으세요.
insert into city_kor select * from city where CountryCode = 'KOR';

• CASE... WHEN ... END

SQL의 조건문에 해당

#city 테이블에서 도시명(Name)이 3자가 넘어가는 경우에 앞쪽 세글자를 대문자로 출력하고, 도시의 인구를 같이 표시하세요.
select case 
		when length(Name) > 3 then upper(mid(Name, 1, 3))
		when length(Name) <=3 then upper(Name)
	end, Population from city;

 

• LIKE 검색 - 검색어의 일부만으로 조회

LIKE 검색은 DBMS에 부담이 많으므로 LIKE에 OR와 같은 논리조건자를 중복해서 사용하지 않는 게 좋다.

#K로 시작하는 
select CountryCode from cify where CountryCode like 'K%'; 

#K로 끝나는
select CountryCode from cify where CountryCode like '%K';

#K가 중간에 들어있는
select CountryCode from cify where CountryCode like '%K%';

• NULL값

#NULL값을 가지고 있는 컬럼 검색
select count(*) from country where LifeExpectancy is NULL;

#NULL이 아닌 값을 가지고 있는 컬럼 검색
select count(*) from country where LifeExpectancy is not NULL;

• NULL 함수

숫자컬럼을 연산해야 할 때 NULL을 처리해주는 함수

 

• NULL값이 나오면 다른 값(주로 0)으로 대체해서 계산에 문제없도록 처리한다.
• DBMS마다 제공되는 함수 이름이 다르다.
• IFNULL/COALESCE(MySQL), ISNULL(SQL server), NVL(오라클)
• 숫자연산/집합함수 (ex. sum()) 의 경우에는 처리가 내장되어 있다.

#NULL 값 미반영
select avg(LifeExpectancy) from country;

#NULL 반영
select avg(IFNULL(LifeExpectancy, 0)) from country;

• GROUP BY

집합함수와 같이 사용해서 그룹별 연산을 적용한다.

#city 테이블의 국가코드별 도시숫자를 구하시오.
select CountryCode, count(CountryCode) from city group by CountryCode;

• HAVING

GROUP BY 의 결과물에 다시 조건을 걸고 싶을 때 사용한다.

select CountryCode, count(CountryCode) from city group by CountryCode having count(CountryCode) >= 70;

• UNION / UNION ALL
  • UNION : 두 쿼리의 결과값을 합쳐서 리턴
  • UNION : 두 쿼리의 결과값을 합쳐서 리턴
  • UNION ALL : 중복을 허용하는 UNION

#UNION
select * from city where CountryCode = 'KOR' UNION
select * from city where CountryCode = 'CHN';

#UNION ALL - KOR과 CHN 이 중복되어 표시된다.
select CountryCode from city where CountryCode = 'KOR' UNION ALL
select CountryCode from city where CountryCode = 'CHN';

• INTERSECT - 교집합, 두 쿼리의 결과값 중 공통값을 찾아서 리턴

select Code from country where LifeExpentancy >= 80 INTERSECT
select Code from country where Population >= 1000000;

• MINUS - 차집합, 쿼리 1 결과값에서 쿼리2 결과값을 빼서 리턴

select CountryCode from city when Populataion > 500000 MINUS
select CountryCode from city where CountryCode = 'CHN';

제약조건

• NOT NULL
  • 데이터가 NULL 값을 받아들이지 않음

  create table BusinessCard(Name varchar(255) not null);
  

• UNIQUE
  • 테이블에 동일한 값이 입력되어 있을 경우 걸러짐
• PRIMARY KEY
  • 기본키 제약조건
• FOREIGN KEY
  • 외래키 제약조건
• DEFAULT
  • 컬럼값이 입력되지 않으면 기본값을 입력