DBMS 공부 2 - SQL의 이해와 종류 (DML)

저T아카데미 데이터베이스 기초 강의를 요약한 내용며 
MySQL 샘플데이터인  'World'를 사용하였습다.

 

SQL의 이해 

• SQL( Structured Query Language)
• 데이터 베이스에 있는 필요한 정보를 사용할 수 있도록 도와주는 언어 
• 사용방법이나 문법이 다른 언어(JAVA ,C , C# 등) 보다 단순하다 
• 하나를 배우면 모든 DBMS에서 사용이 가능하다 .
• 인터프리터 언어이다 
• 대소문자 구별을 하지 않는다 (데이터 내용은 구별한다.) 

---

SQL의 종류 

1. DML (Data Manipulatuon Langauge)

• 테이블의 데이터를 조작하는 기능 
• 테이블의 레코드를 CRUD(Create, Retrieve , Update , Delete) 함.

SQL문	내용
INSERT	데이터베이스 객체에 데이터를 입력 한다.
DELETE	데이터베이스 객체에 데이터를 삭제 한다.
UPDATE	데이터베이스 객체 안의 데이터를 수정 한다.
SELECT	데이터베이스 객체 안의 데이터를 조회 한다.

 

 1. distict

중복된 값을 제거해서 표시해준다. 

 

2.논리연산자 (AND, OR,NOT,IN, BETWEEN)

SELECT 문의 조건절에 논리 조건을 적용할수 있게 해준다. 

ex)
국가 코드가 'KOR' 이면서 인구가 100만 이상인 도시를 찾아라 . 
국가 코드가 'KOR' ,'CHN' , 'JPN'인 도시를 찾아라
국가 코드가 'KOR'이 아니면서 인구가 100만 이상인 도시를 찾아라
국가 코드가 'KOR' ,'CHN', 'JPN'인 도시를 찾으시오
국가 코드가  'KOR' 이고 인구가 100만이상 500만 이하인 도시를 찾으시오

select * from city where countryCode = 'KOR' and population > 1000000;
select * from city wehre countryCode = 'KOR' or CountryCode='CHN' or CountryCode = 'JPN'
select * from city where countryCode !=  'KOR' and population> 1000000;
select * from city where countryCode in( 'KOR' , 'CHN' , 'JPN')
select * from city where countryCode = 'KOR' and (population between 1000000 and 5000000);

3.결과 정렬 (ORDER BY)

• select 문의 결과값을 특정한 컬럼을 기준으로 오름차순/내림차순으로 정렬해서 표시
• select * from 테이블명 where 조건절 order by 컬럼명 asc / desc 형식으로 사용한다.
• 기본값은 오름차순 (asc) 이다.

국가코드가 'KOR'인 도시를 찾아 인구수의 역순으로 표시하라. 
city 테이블에서 국가코드와 인구수를 출력하며 , 정렬은 국가코드별로 오름차순,   동일한 코드 안에서는 인구 수의 역순으로 표시하라

select * from city where countyCode = 'KOR' order by population desc;
select CountryCode , Population from city order by CountryCode asc  ,  Population  desc;
(CountryCode 정렬이 우선되고 그 상태에서 Population이 정렬된다.)

4.  LIMIT( ROWNUM ,TOP)

• SQL 쿼리 결과 중 상위 몇 개만 보여주는 쿼리 
• select 컬럼명 1, 컬럼명 2 , .... from 테이븖여 where 조건절 limit 숫자.
• 대표적인 비표준기능(DBMS 종류마다 차이가 있다. oracle - rownum , SQLServer -TOP , MySQL Limit)

국가코드가 'KOR' 인 도시들중 인구수 많은 순서로 상위 10개만 표시하라 

select * from city where countryCode = 'KOR' order by population desc limit 10;

 

5. 집합함수(Aggregation Function)

• 테이블의 전체레코드를 대상으로 특정 컬럼을 적용해서 한 개의 값을 리턴하는 함수  
• count(), avg(), sum(),min(),max(),first(),last() ... 등이 있다 .
• select 집합함수(컬럼명) from 테이블명 where 조건절 ; 의 형태로 사용한다.

city 테이블에서 국가코드가 'kor'인 도시의 수를 표시하시오. 
city 테이블에서 국가코드가 'kor'인 도시들으 ㅣ 인구수 총합 / 평균을 구하시오.
city 테이블에서 국가코드가 'kor'인 도시들의 인구수 중 최대값/최소값을 구하시오.

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

 

6.이외의 유용한 함수 (length() , mid() , upper/lower() , round() )

 

country 테이블의 각 레코드의 Name컬럼의 글자수를 표시하라.
country 테이블의 나라명(Name컬럼)을 앞 세글자만 대문자로 표시하시오 .
country 테이블의 기대수명(LifeExpectancy)을 소수점 첫째자리에서 반올림해서 표시하라

select length(name) ,name  from country;
select upper(mid(name,1,3))  from country;
select round(LifeExpectancy,0) from country;

 

7. JOIN

• 서로 다른 테이블을 공통 컬럼을 기준으로 함치는 테이블단위연산을 말한다. 
• 조인의 결과 테이블은 이전 테이블의 컬럼 수의 합과 같다. 
• select * from 테이블1 join 테이블2 on 테이블1.컬럼명 = 테이블2.컬럼명의 형태로 사용한다. 
• 조인시 서로 다른 테이블에 같은 컬럼명이 존재하면 구분을 위해 테이블명.컬럼명으로 표시해서 사용해야한다.

 

city 테이블과 country테이블을 조인하시오 
국가 코드와 해당 나라의 GNP를 표시하시오

select * from city join country on city.countrycode =country.code;
select countrycode , gnp from city join country on city.countryCode = country.code;

- JOIN의 종류 

INNER JOIN	조인시 NULL값을 혀용하지 않는다  (NULL 값을 가진 레코드는 조인결과에서 빠진다.)
LEFT JOIN	조인시 JOIN의 왼쪽 테이블의 NULL 값을 포함해서 표시
RIGHT JOIN	조인시 JOIN의 오른쪽 테이블의 NULL 값을 포함해서 표시
FULL JOIN	LEFT JOIN 과 RIGHT JOIN 의 합집합을 표시한다. (MySQL은 지원하지 않는다. (union을 사용하여 대체할 수 있다 .))

city 테이블에 국가코드가 없는 도시가 있는지 확인해보자. 
country 테이블에는 존재하지만 도시가 하나도 없는 나라가 있는지 확인하라 . 
위의 경우에서  INNER JOIN / LEFT JOIN / RIGHT JOIN의 차이점을 확인하라.

select * from city where countryCode is NULL;
select * from country left join city on country.code = city.countryCode;
select * from country right join city on country.code = city.countryCode;

 

8. 별칭(ALIAS)

• SQL 쿼리 결과생성시 컬럼명에 대한 별칭을 사용해 표시하는 기능 
• SELECT 테이블1.컬럼명1 AS 별칭1 , 테이븖여2.컬럼명2 AS 별칭 2 FROM ... 형태로 사용
• 조인할 때 많이 사용됨

city 테이블과 country 테이블을 조인해서 국가코드가 'KOR '인 나라의 축약표시형(Abbr)과 정식명 (FullName)을 출력하라.

select city.name as 'city' ,countryCode as 'abbr', country.name as 'fullName' from city join country on city.countryCode = country.code where city.countryCode = 'KOR'

 

9.뷰(VIEW)

• SQL 쿼리의 결과 값을 임시테이블로 저장해서 사용할 수 있다. 
• 사용용도가 끝나면 명시적으로 삭제해야 한다 (DROP VIEW ...)
• CREATE VIEW 뷰명 AS SELECT .... 의 형태로 사용한다. (select 문의 결과값을 view로 생성함.)

국가 코드가 'KOR'인 도시들의 국가코드와 국가명(NAME)을 뷰로 생성하라 
(이때 국가코드를 Abbr , 국가코드를 FullName 으로 표시)

create view sampleView as select city.CountryCode , country.name from city join country on city.countryCode = country.code

 

10. SELECT INTO

• 쿼리결과를 새 테이블로 만든다. 
• mySQL에서는 CREATE TABLE 테이블명 SELECT * FROM 테이블명의 형태로 사용한다. 
• 기존에 존재하지 않는 테이블이 새로 생성된다. (일종의 뷰와 동일한 효과) 

city 테이블의 내용에서 국가코드가 'KOR' 인 도시를 찾아 city_new 테이블에 insert 하라

create table city_new select from city where countryCode = 'kor';

 

11. INSERT INTO SELECT 

• 쿼리결과를 기존의 테이블에 추가한다 (기존 테이블이 존재해야 한다.)
• INSERT INTO 테이블명1 SELECT * FROM 테이블명2 WEHRE 조건절 .. 형태로 사용한다.
• SELECT하는 테이블과 INSERT하는 테이블은 동일한 구조를 가져야 한다.
• 두 개의 별도 쿼리가 하나로 합쳐진다.

city 테이블의 내용에서 국가코드가 'kor' 인 도시를 찾아 city_kor 테이블에 넣으시오.

insert into city_kor select * from city where CountryCode='kor'

 

12.CASE ... WHEN ...END

• SQL의 조건문(if/switch) 에 해당한다.
• 조건값에 따라 구분하여 처리할 수 있다

city 테이블에서 도시명이 10자가 넘어가는 경우에 앞쪽 세 자만 대문자로 출력하고 도시의 인구를 같이 표시하라.

select case
when length(name) > 10 then upper(mid(name,1,3)) 
when length(name) <= 10 then name 
end, Population from city;

13.LIKE

• 정확한 키워드를 모를 경우 일부만으로 검색하는 방법
• 와일드카드(% , _)를 사용하여 패턴매칭을 할 수 있다. (% : 0-n글자 , _: 1글자를 의미한다.)
• LIKE의 검색은 매칭하기 위해 DBMS에 부담이 많으므로 LIKE에 OR와 같은 논리 조건자를 중복해서 사용하지 않는게 좋다 .

city 테이블에서 국가코드가 K로 시작하는 / 끝나는 / 중간에 있는 국가코드를 표시하라 . 
city 테이블에서 국가코드가 K로 시작하는 3글자 국가 코드를 표시하라 .

select countryCode from city where countryCode like 'K%'
select countryCode from city where countryCode like '%K'
select countryCode from city where countryCode like '%K%'
select countryCode from city where countryCode like 'K__'

 

14.NULL값

• NULL이란 해당 컬럼이 값이 없다는 의미 (해당 컬럼이 NULL을 허용하는 경우)
• NULL 값을 가지고 있는 컬럼을 검색하려면 is NULL 사용
• NULL이 아닌 값을 가지고 있는 컬럼을 검색하려면 is NOT NULL 사용

country 테이블에서 기대수명(LifeExpectancy)이 없는 국가개수를 표시하라
country 테이블에서 기대수명(LifeExpectancy)이 있는 국가개수를 표시하라

select count(*) from country where LifeExpectancy is NULL;
select count(*) from country where  LifeExpectancy is NOT NULL;

15. NULL 함수

• 숫자컬럼을 연산해야 할 떄 NULL을 처리해주는 함수
• NULL값이 나오면 다른 값(주로 0)으로 대체해서 계산에 문제없도록 처리
• DBMS 종류마다 함수가 다르다 IF NULL / COALESCE(MySQL) , ISNULL(SQL Server)  ,NVL (오라클)
• 숫자연산 / 집합함수(예 sum())의 경우는 처리가 내장되어 있다. 
• 직접 함수나 쿼리에 넣는 경우는 NULL함수를 사용해야한다.

country 테이블의 기대수명의 평균값을 표시하시오. (NULL 값 미반영/ 반영)

country 테이블의 기대수명값이 들어있는 개수를 표시하시오 (NULL값 미반영/반영);

 

select avg(LifeExpectancy) from country;

select avg(IFNULL(LifeExpectancy,0)) from country;

select count(LifeExpectancy) from country;

select count(IFNULL(LifeExpectancy,0)) from country;

 

16. 서브쿼리(SubQuery)

• 쿼리문 내에 또다른 쿼리문이 잇는 형태를 말한다
• 서브쿼리는 메인쿼리에 포함되는 관계
• ()를 사용해 감싸는 형태로 사용한다.
• order by를 사용하지 못한다.
• SELECT / FROM / WHERE / HAVING / ORDER BY / VALUES(INSERT) / SET(UPDATE)절에서 사용가능하다.

단일행 서브쿼리 

 

- 레코드가 하나인 서브쿼리를 말한다. 

- 일반연산자(= , > , <  등 . .)을 사용한다.

 

국가명이 South korea인 국가의 국가코드를 찾아 이에 해당되는 도시의 수를 표시하라 .
city 테이블에서 국가코드가 'kor'인 도시의 평균 인구 수보다 많은 도시들의 이름을 표시하라

select count(*) from city where countryCode = (select code from country where name = 'south korea')
select name from city where population >( select avg(population) from city where countryCode='KOR') order population desc;

 

다중행 서브쿼리

 

- 결과가 레코드 여러 개인 서브쿼리를 말한다. 

- 다중행 연산자 (IN , ALL , ANY ,EXIST)를 사용하여 처리한다.

 

 

다중행 연산자 

 

ALL

• 여러 개의 레코드의 AND 효과를 낸다.(가장 큰 값보다 큰)
• ex ) Population > ALL(select Population from city);

 

Any

• 여러 개의 레코드의 OR효과(가장 작은 값보다 큰)
• Population > ANY(select Populatiojn)

 

IN/EXISTS

결과값 중에 있는 것 중에서의 의미 

IN은 전체 레코드를 스캔하고 , EXISTS는 존재여부만 확인하고 스캔은 하지 않는다(상대적으로 속도가 빠르다). 

존재하면 TRUE / 존재하지 않으면 FALSE가 된다.

 

국가명이 'south korea' , 'china' , 'japan' 인 국가의 국가코드를 찾아 이에 해당되는 도시의 수를 가각 표시하라.
인구수가 5000000명이 넘어가는 도시의 도시이름 , 국가코드 , 인구 수를 표시하라 .
한국의 모든(ALL) 도시보다 인구 수가 많은 도시를 모두 표시하라.
한국의 어떤(ANY)도시보다 인구 수가 많은 도시를 모두 표시하라.
국가코드가 'KOR' , 'CHN' , 'JPN '인 도시명과 국가코드, 인구수를 표시하라 (EXISTS)

select CountryCode, count(*) from city where countryCode IN(select code from country where name IN('south korea' , 'china'  , 'japan' )) group by countryCode;
select name, countryCode,population from city where population > 5000000
select name from city where population > all(select population from city where countrycode ='kor' );
select name from city where population>any(select population from city where countryCode ='kor');

 

 

멀티컬럼 서브쿼리

 

결과가 컬럼 여러 개인 서브쿼리를 말한다.

 

 

17. 집합연산

 

DBMS에서 각종 집합연산을 지원한다

합집합(union) , 교집합(Intersect), 차집합(minus), ..

MySQL은 교집합 , 차집합은 지원하지 않는다.

 

union - 두 쿼리의 결과값을 합쳐서 리턴한다.

두 쿼리의 결과 형식이 동일해야 함(기본적으로 distinct가 적용된다.)

다른 테이블이라도 결과값의 형태가 일치하면 union을 사용할 수 있다.

 

 

 

---