SQL

Select 컬럼 from 스키마.테이블

where population between 1000000 and 2000000

숫자 연산자

= '! = ' <> > <

• null 값은 =로 인식이 불가하고 is null로 해줘야함
• 예시

select*from world.country
where inDepyear is null;

문자연산자

select count(*)from city where name LIKE'___'
select count(*)from city where name LIKE'%m'

• %는 앞의 문자가 제한없이 _는 한 개

서브쿼리

select *
from city
where population>(select population from city where name ='Seoul');

select *
 from city
 where population > ANY ( select *
 from city
 where CountryCode ='Kor');

• ANY 저 서브 쿼리 값의 제일 작은 값보다 커야함

select name
from city
where Population =(select avg(population)
from city);

• foreign key

• 타입도 동일해야한다

 

• distinct 중복제외

select distinct CountryCode
from city;
select count(countrycode)/count(distinct countrycode)
from city;

• count 내부에 작성을 해야 중복이 안된다.

 

• group by

select countrycode, count(name),sum(population)
from city
group by countrycode;
select countrycode, count(name),sum(population)
from city
group by countrycode;

• 보여줄 것들을 select로 선정 group by는 기준이 될 것 선정
• group by는 where절 못씀 대신 having 절을 쓴다

 

• having 절

select countrycode as '국가코드',
	count(name) as '등록도시수',
	sum(population) as '총인구수',
	avg(population) as '평균인구수',
	district
from city
group by CountryCode
Having Sum(population)>1000000
order by count(name) DESC,sum(population);

• 무조건 group by 절 뒤에 나와야한다.

 

• order by

select countrycode as '국가코드',
count(name) as '등록도시수',
sum(population) as '총인구수',
avg(population) as '평균인구수'
from city
group by CountryCode
order by count(name),sum(popualation);
-- DESC(내림차순)

• 정렬 기준

 

• 실행하는 명령어?

• count(*)
  sum()
  avg()
  min()
  max()
  AS

우선 이미지를 보고 하면 이해가 쉽다.

• Join

select*from sales
	INNER JOIN member
on sales.mem_id = member.id;

select sales.*,member.name,member.addr from sales
inner join member
on sales.mem_id = member.id;

• inner join 연결(교집합)

select  distinct actor.first_name
from film_actor
inner join actor
on film_actor.actor_id=actor.actor_id;

select distinct film.title
from film_actor
inner join film
on film_actor.film_id=film.film_id;

select film.title,actor.first_name
from film_actor
inner join film
on film_actor.film_id=film.film_id
inner join actor
on film_actor.actor_id=actor.actor_id;

• outer join

SELECT * FROM hellodb.sales;

select member.*, sales.*
from member

left outer join sales
on member.id = sales.mem_id;

select film.*
from  film_actor
right outer join film
on film.film_id=film_actor.film_id
where film_actor.actor_id is null;