SQL 문법 정리

데이터 베이스는 MYSQL WorchBench에 기본적으로 있는 world데이터 베이스를 사용했다

SELECT

-- 데이터 베이스 선택
USE worldcity;

-- 데이터 테이블 보기 
SHOW TABLES;

-- city, country, countrylan... 라는 테이블에 어떤 column이 있고 타입등의 정보를 확인가능
describe city;

desc country;

desc countrylanguage;

-- select 보고싶은 정보를 가져오는것

-- city table의 전체 row(행)을 가져온다
select * from city;

-- 전체 행이 아닌 도시 이름들만 가져온다
-- , 로 여러개 선택가능
select name, population from city;

-- where // select에서 조건을 주어 가져올때 
-- population이 1000000이상인 도시를 보여줘 
select name, population 
from city
where population > 1000000;

-- 여러 조건을 붙일 수도 있다

select name, population 
from city
where population > 1000000 and population < 2000000;

SELECT name, population 
FROM city
WHERE population > 1000000 OR population < 2000000;

-- 한국에있는 도시들 보기
DESC city; -- 먼저 도시들을 확인하고 Country코드라는 것이 있다는 것을 발견

-- Country코드라는 'KOR' 한국도시 확인
SELECT *
FROM city
WHERE CountryCode = 'KOR';

-- 한국의 도시 중 인구수가 특정조건인 도시
SELECT *
FROM city
WHERE CountryCode = 'KOR'
AND Population > 1000000;

-- between 특정 숫자 2개의 사이에 있는 데이터를 가져온다
SELECT *
FROM city
WHERE Population BETWEEN 7000000 AND 8000000;

-- Discrete 특정 도시 정보만 가져오기
SELECT *
FROM city
WHERE NAME IN('Seoul','New York','Tokyo');


-- LAB 나라 코드로 여러개 도시 정보들 가져오기
SELECT *
FROM city
WHERE CountryCode IN('KOR','USA','JPN');

-- LIKE 문자열을 검색할때 쓰인다
-- _ 언더바를 뒤에 붙여주면 앞에 글자와 매치되는 것을 가져온다
-- 한글자와 매치
SELECT *
FROM city
WHERE CountryCode LIKE 'KO_';

-- 문자뒤에 무엇이든 (%)허용
-- Tel로 시작하는 도시이름을 다가져온다
SELECT *
FROM city
WHERE Name LIKE 'Tel %';


-- 서브쿼리는 결과가 여러개면 에러!
-- sub Query 쿼리문 안에 또 쿼리문이 있다
-- 컨트리 코드가 기억이안나..
-- 그런데 seoul 이라는 도시이름은 알아!
SELECT *
FROM city
WHERE CountryCode = (   SELECT CountryCode 
						FROM city
						WHERE Name = 'seoul');
                        
-- ANY 서브쿼리의 여러개의 결과 중 한 가지만 만족해도 가능
-- ANY 와 SOME은 같다
-- 뉴욕이란 District를 가지고 있고 Population이 있다면 가져와라
SELECT *
FROM city
WHERE Population =  ANY (   SELECT Population 
							FROM city
							WHERE District = 'New York');
                            
-- ALL 서브쿼리의 여러개의 결과를 모두 만족 시켜야한다
-- New York의 인구수 보다 많은 도시들을 보여줘라
SELECT * FROM city
WHERE Population > ALL ( SELECT Population
						 FROM city
						 WHERE	District = 'New York');

-- ORDER BY DESC 오름차순
-- ASC 내림차순

SELECT * FROM city
ORDER BY CountryCode ASC, Population DESC;

-- LAB 인구수로 내림차순하여 한국의 도시보기

SELECT * FROM city
WHERE CountryCode = 'KOR'
ORDER BY Population DESC;

-- 국가 면적 크기로 내림차순하여 나라보기

DESC Country; -- 데이터 컬럼 확인

SELECT * FROM Country
ORDER BY SurfaceArea DESC;

-- Distinct 중복된것은 제외 -- 중복된 나라코드 제외
SELECT DISTINCT CountryCode
FROM city; 

-- LIMIT 출력개수 제한 상위 N개만 출력

SELECT * FROM city
ORDER BY Population DESC
LIMIT 10 ;

-- GROUP BY 그룹으로 묶어주는 역할
-- 집계 함수를 함께 사용한다
-- AVG()평균 , MIN()최소, MAX()최대, COUNT()행의 개수, COUNT() 중복 제외된 행의 개수
-- STDEV() 표준편차, VARIANCE() 분산,

-- CountryCode를 묶어 인구가 가장큰 것만 출력해라
-- as 를 쓰면 가져오는 컬럼명을 변경할 수 있다
SELECT CountryCode, avg(Population) as 'Average'
FROM city
GROUP BY CountryCode; 

-- 도시의 개수 가져오기 
SELECT COUNT(*)
FROM city; 

-- 도시들의 평균인구수
SELECT AVG(Population)
FROM city;

--  HAVING // WHERE 과 비슷한 개념으로 조건 제한
-- 집계함수에 대해서 조건 제한하는 편리한 개념
-- 반드시 GROUP BY 절 뒤에 나와야함
-- GROUP BY를 써 집계함수와 함께 조건을 주고 싶을때 사용하는 듯 하다

SELECT CountryCode, MAX(Population)
FROM city
GROUP BY CountryCode
HAVING MAX(Population) > 8000000;

-- ROLLUP
-- 중간합계 총합이 필요할 경우 사용
-- CountryCode와 Name을 가져오고 각 국가코드 인구를 더한 값을 출력

SELECT CountryCode, Name, SUM(Population)
FROM city
GROUP BY CountryCode, Name WITH ROLLUP
                        

JOIN
-- JOIN 데이터베이스 내의 여러 테이블에서 가져온 레코드를 조합하여 하나의 테이블이나
-- 결과집합으로 표현

-- JOIN 데이터베이스 내의 여러 테이블에서 가져온 레코드를 조합하여 하나의 테이블이나 
-- 결과집합으로 표현

SELECT * FROM country;
SELECT * FROM city;

-- JOIN조건 도시중에 국가 코드가 같은 것을 출력

SELECT *
FROM city
JOIN country ON city.CountryCode = country.Code
JOIN countrylanguage ON city.CountryCode = countrylanguage.countryCode;

-- sql 내장함수 몇개를 알아보자

SELECT LENGTH('ASDSADDD'); -- 문자열 길이를 반환
SELECT CONCAT('MY','LOVE'); -- 문자열을 합쳐준다 
SELECT LOCATE('ABC','ABDADSBFHADABC'); -- 해당문자열 위치를 반환 0번째가 아닌 1번째 부터 샌다

-- LEFT 왼쪽부터 지정된 숫자 만큼 출력
-- RIGHT 오른쪽부터
-- LOWER 소문자로 변환
-- UPPER 대문자로 변환
SELECT 
LEFT('ASDASDASDASD',5);
SELECT 
RIGHT('ASDASDASDASD',5);

-- replace
-- MS SQL을 MY SQL로 변환
SELECT 
REPLACE('MS SQL','MS','MY');