DATABASE-Sub Query

Oct 27, 2020 in DATABASE, DML

Sub Query

sub query는 query 문 안에 있는 query를 의미합니다.

SELECT절, FROM절, WHERE 등에 사용이 가능합니다.

Example

전체 나라수, 전체 도시수, 전체 언어수를 출력 ( SELECT 절에 사용 )

`SUB QUERY 사용시 JOIN TABLE 크기를 줄일 수 있다`

SELECT
    (SELECT count(name) FROM city) AS total_city,
    (SELECT count(name) FROM country) AS total_country,
    (SELECT count(DISTINCT(Language)) FROM countrylanguage) AS total_language
FROM DUAL

800만 이상되는 도시의 국가코드, 이름, 도시인구수를 출력 ( FROM 절에 사용 )

SUB QUERY를 안 쓸때

city table row : 4079
coutnry table row : 239

JOIN TABLE : 4079 * 239

SELECT *
FROM
    city
JOIN
    (SELECT code, name
    FROM country) AS country
ON city.countrycode = country.code
HAVING city.population > 8000000;

SUB QUERY를 쓸 때

city table row : 10
coutnry table row : 239

JOIN TABLE : 10 * 239

SELECT *
FROM
    (SELECT countrycode, name, population
    FROM city
    WHERE population > 8000000) AS city
JOIN
    (SELECT code, name
    FROM country) AS country
ON city.countrycode = country.code;

800만 이상 도시의 국가코드, 국가이름, 대통령이름을 출력( WHERE 절에 사용 )

SELECT code, name, HeadOfState
FROM country
WHERE code IN (
    SELECT DISTINCT(countrycode) FROM city WHERE population > 8000000
)

Comment and share

DATABASE-JOIN UNION

Oct 26, 2020 in DATABASE, DML

JOIN

JOIN은 여러개의 테이블에서 데이터를 모아서 보여줄 때 사용됩니다.
JOIN에는 INNER JOIN, LEFT JOIN, RIGHT JOIN이 있습니다.

MAKE TEST TABLE & DATA

create table & data

CREATE TABLE user (
    user_id int(11) unsigned NOT NULL AUTO_INCREMENT,
    name varchar(30) DEFAULT NULL,
    PRIMARY KEY (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE addr (
    id int(11) unsigned NOT NULL AUTO_INCREMENT,
    addr varchar(30) DEFAULT NULL,
    user_id int(11) DEFAULT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO user(name)
VALUES ("jin"),
("po"),
("alice"),
("petter");

INSERT INTO addr(addr, user_id)
VALUES ("seoul", 1),
("pusan", 2),
("deajeon", 3),
("deagu", 5),
("seoul", 6);

INNER JOIN

두 테이블 사이에 공통된 값이 없는 row는 출력하지 않는다.

두 테이블을 합쳐 id, name, addr 출력

SELECT id, user.name, addr.addr
FROM user
JOIN addr
ON user.user_id = addr.user_id

world 데이터베이스에서 도시이름과 국가이름을 출력

SELECT city.name AS country_name, country.name AS city_name
FROM city
JOIN country
ON city.CountryCode = country.code

아래와 같이 사용할수도 있다.

1
2
3

SELECT city.name AS country_name, country.name AS city_name
FROM city, country
WHERE city.CountryCode = country.code

LEFT JOIN

왼쪽 테이블을 기준으로 왼쪽 테이블의 모든 데이터가 출력되고 매핑되는 키값이 없으면 NULL로 출력된다.

두 테이블을 합쳐 id, name, addr 출력

SELECT id, user.name, addr.addr
FROM user
LEFT JOIN addr
ON user.user_id = addr.user_id

RIGHT JOIN

오른쪽 테이블을 기준으로 왼쪽 테이블의 모든 데이터가 출력되고 매핑되는 키값이 없으면 NULL로 출력된다.

두 테이블을 합쳐 id, name, addr 출력

SELECT id, user.name, addr.addr
FROM user
RIGHT JOIN addr
ON user.user_id = addr.user_id

JOIN과 DISDINCT의 사용

지역과 대륙별 사용하는 언어 출력

SELECT DISTINCT country.Region, country.continent, countrylanguage.Language
FROM country
JOIN countrylanguage
ON countrylanguage.CountryCode = country.Code

대륙과 지역별 사용하는 언어의 수 출력

SELECT sub1.region, sub1.continent, count(*) as count
FROM (
SELECT DISTINCT country.Region, country.continent, countrylanguage.Language
FROM country
JOIN countrylanguage
ON countrylanguage.CountryCode = country.Code
) AS sub1
GROUP BY sub1.region, sub1.continent

테이블 세개 조인하기

국가별, 도시별, 언어의 사용율

SELECT country.name as country_name, city.name as city_name,
countrylanguage.language, countrylanguage.percentage
FROM country
JOIN city
ON country.code = city.countrycode
JOIN countrylanguage
ON country.code = countrylanguage.countrycode

SELECT country.name as country_name, city.name as city_name,
countrylanguage.language, countrylanguage.percentage
FROM country, city, countrylanguage
WHERE country.code = city.countrycode and country.code = countrylanguage.countrycode

UNION

UNION은 SELECT 문의 결과 데이터를 하나로 합쳐서 출력합니다.

컬럼의 갯수와 타입, 순서가 같아야 합니다.

UNION은 자동으로 distinct를 하여 중복을 제거해 줍니다.

중복제거를 안하고 컬럼 데이터를 합치고 싶으면 UNION ALL을 사용합니다.

또한 UNION을 이용하면 Full Outer Join을 구현할수 있습니다.

UNION
- user 테이블의 name 컬럼과 addr 테이블의 addr 컬럼의 데이터를 하나로 함쳐서 출력
  1
  2
  3
  4
  5
  SELECT name
  FROM user
  UNION
  SELECT addr
  FROM addr
UNION ALL
- 중복데이터를 제거하지 않고 결과 데이터 합쳐서 출력
  1
  2
  3
  4
  5
  SELECT name
  FROM user
  UNION ALL
  SELECT addr
  FROM addr

FULL OUTER JOIN

union을 이용하여 full outer join 구현

SELECT id, user.name, addr.addr
FROM user
LEFT JOIN addr
ON user.user_id = addr.user_id
UNION
SELECT id, user.name, addr.addr
FROM user
RIGHT JOIN addr
ON user.user_id = addr.user_id

Comment and share

DATABASE-DML-INSERT UPDATE DELETE

Oct 26, 2020 in DATABASE, DML

INSERT

테이블 이름 뒤에 오는 컬럼이름은 생략이 가능하며 대신에 VALUES 뒤에 value 값이 순서대로 와야 합니다.
1
2
INSERT INTO <table_name>(<column_name_1>, <column_name_2>, ...)
VALUES(<value_1>, <value_2>, ...)
test 데이터 베이스 선택
1
sql> USE test;

user1 테이블에 user_id, name, email, age, rdate를 입력

INSERT INTO user1(user_id, name, email, age, rdate)
VALUES (1, "jin", "pdj@gmail.com", 30, now()),
(2, "peter", "peter@daum.net", 33, '2017-02-20'),
(3, "alice", "alice@naver.com", 23, '2018-01-05'),
(4, "po", "po@gmail.com", 43, '2002-09-16'),
(5, "andy", "andy@gmail.com", 17, '2016-04-28'),
(6, "jin", "jin1224@gmail.com", 33, '2013-09-02');

city_2 테이블 생성

CREATE TABLE city_2 (
    Name VARCHAR(50),
    CountryCode CHAR(3),
    District VARCHAR(50),
    Population INT
)

select 절에서 나온 결과데이터를 Insert

INSERT INTO city_2
SELECT Name, CountryCode, District, Population
FROM city
WHERE Population > 8000000;

UPDATE SET

업데이트시에는 항상 select-where로 변경할 데이터를 확인하고 update 해줘야 실수를 줄일수 있습니다. 또한 limit도 함께 사용해주면 좋습니다.

1
2
3

UPDATE <table_name>
SET <column_name_1> = <value_1>, <column_name_2> = <value_2>
WHERE <condition>

jin 이름을 가지고 있는 사람의 나이를 20, 이메일을 pdj@daum.net으로 변경
1
2
3
sql> UPDATE user1
SET age=20, email="pdj@daum.net"
WHERE name="jin"

DELETE, DROP, TRUNCATE

조건을 설정하여 데이터를 삭제할수 있습니다.

1 2	DELETE FROM <table_name> WHERE <condition>

2016-01-01 이전 데이터 삭제 (DML)

1 2	sql> DELETE FROM user1 WHERE rdate < "2016-01-01"

테이블 구조를 남기고 모든 데이터를 삭제 (DLL)
1
sql> TRUNCATE FROM user1
테이블 전체를 모두 삭제 (DLL)
1
sql> DROP FROM user1

Comment and share

DATABASE DML SELECT

Oct 20, 2020 in DATABASE, DML

SELECT

정의 : 데이터를 검색할때 사용되는 문법

SELECT FROM

기본 포멧

SELECT <column_name_1>, <column_name_2>, ... FROM <table_name>
전체 컬럼 데이터 조회
1
2
sql> SELECT *
FROM world.country

code, name 세개의 컬럼 데이터 조회
1
sql> SELECT code, name FROM world.country
데이터 베이스 선택 : FROM 절에 world. 을 사용할 필요가 없다.
1
2
sql> USE world;
sql> SELECT * FROM country

alias : 컬럼의 이름을 변경할수 있습니다.

1	sql> SELECT code as country_code, name as country_name FROM country

데이터 베이스, 테이블, 컬럼 리스트 확인

1
2
3

sql> SHOW DATABASES;
sql> SHOW TABLES;
sql> DESC city;

WHERE

특정 조건을 주어 데이터를 검색하는데 사용되는 문법

비교연산 - 인구가 1억이 넘는 국가를 출력
1
SELECT * FROM country WHERE Population >= 100000000

논리 연산 : AND, OR - 인구가 7000만에서 1억인 국가를 출력

1 2	SELECT * FROM country WHERE Population >= 70000000 AND Population <= 100000000

범위 연산 : BETWEEN

인구가 7000만에서 1억인 국가를 출력

1 2	SELECT * FROM country WHERE Population BETWEEN 70000000 AND 100000000

아시아와 아프리카 대륙의 국가 데이터를 출력

1 2	SELECT * FROM country WHERE Continent = "Asia" OR Continent = "Africa"

특정 조건을 포함 : IN, NOT IN
- 아시아와 아프리카 대륙의 국가 데이터를 출력
  1
  2
  SELECT * FROM country
  WHERE Continent IN ("Asia", "Africa")
- 아시아와 아프리카 대륙의 국가가 아닌 데이터를 출력
  1
  2
  SELECT * FROM country
  WHERE Continent NOT IN ("Asia", "Africa")
- 아시아와 아프리카 대륙의 국가가 아닌 데이터를 출력 (논리연산 사용)
  1
  2
  SELECT * FROM country
  WHERE Continent != "Asia" AND Continent != "Africa"
특정 문자열이 포함된 데이터 출력 : LIKE
- 정부형태에 Republic이 포함된 데이터 출력
  1
  2
  SELECT * FROM country
  WHERE GovernmentForm LIKE "%Republic%"

ORDER BY

특정 컬럼의 값으로 데이터 정렬에 사용되는 문법

오름차순 인구순으로 국가의 리스트를 출력 (ASC는 생략이 가능)
1
SELECT * FROM country ORDER BY population ASC
내림차수 인구순으로 국가의 리스트를 출력
1
SELECT * FROM country ORDER BY population DESC
국가 코드를 알파벳 순으로 정렬하고 같은 국가 코드를 가지면 인구순으로 내림차순으로 정렬
1
SELECT * FROM city ORDER BY CountryCode ASC, Population DESC

LIMIT

LIMIT은 조회하는 데이터의 수를 제한할수 있습니다.

인구가 많은 상위 5개 국가 데이터를 출력

1	SELECT * FROM country ORDER BY population DESC LIMIT 5

인구가 많은 상위 6위 ~ 8위의 3개 국가 데이터를 출력
1
SELECT * FROM country ORDER BY population DESC LIMIT 5, 3

GRUOP BY, HAVING

GROUP BY는 여러개의 동일한 데이터를 가지는 특정 컬럼을 합쳐주는 역할을 하는 명령입니다.

SQL에는 아래와 같은 그룹함수가 있습니다.
COUNT, MAX, MIN, AVG, VAR_SAMP, STDDEV

COUNT
- city 테이블의 CountryCode를 묶고 각 코드마다 몇개의 데이터가 있는지 확인
  1
  2
  sql> SELECT CountryCode, COUNT(CountryCode) FROM city
  GROUP BY CountryCode
- countrylanguage 테이블에서 전체 언어가 몇개 있는지 구하시오.
  - DISTINCT 중복을 제거해주는 문법
    1
    2
    sql > SELECT COUNT(DISTINCT(Language)) as language_count
    FROM countrylanguage

MAX

대륙별 인구수와 GNP 최대 값을 조회

        sql> SELECT continent, MAX(Population) as Population, MAX(GNP) as GNP
        FROM country GROUP BY continent
        ```    
- MIN
    - 대륙별 인구수와 GNP 최소 값을 조회 (GNP와 인구수가 0이 아닌 데이터 중에서)
        ``` 
        sql> SELECT continent, MIN(Population) as Population, MIN(GNP) as GNP FROM country
        WHERE GNP != 0 AND Population != 0 GROUP BY continent

SUM

대륙별 총 인구수와 총 GNP

1
2
3

sql> SELECT continent, SUM(Population) as Population, SUM(GNP) as GNP
FROM country
WHERE GNP != 0 AND Population != 0 GROUP BY continent

AVG

대륙별 평균 인구수와 평균 GNP 결과를 인구수로 내림차순 정렬

sql> SELECT continent, AVG(Population) as Population, AVG(GNP) as GNP
FROM country
WHERE GNP != 0 AND Population != 0 GROUP BY continent
ORDER BY Population DESC

HAVING

GROUP BY에서 반환되는 결과에 조건을 줄수 있습니다.

대륙별 전체인구를 구하고 5억이상인 대륙만 조회

1
2
3

sql> SELECT continent, SUM(Population) as Population FROM country
GROUP BY continent
HAVING Population > 500000000

대륙별 평균 인구수, 평균 GNP, 1인당 GNP한 결과를 1인당 GNP가 0.01 이상인 데이터를 조회하고 1인당 GNP를 내림차순으로 정렬

sql> SELECT continent, AVG(Population) as Population, AVG(GNP) as GNP, AVG(GNP) / AVG(Population) * 1000 as AVG
FROM country
WHERE GNP != 0 AND Population != 0 GROUP BY continent
HAVING AVG > 0.01
ORDER BY AVG DESC

WITH ROLLUP

고객과 스탭별 매출과 고객별 매출의 총합을 출력

1 2	sql> SELECT customer_id, staff_id, SUM(amount) as amount FROM payment GROUP BY customer_id, staff_id WITH ROLLUP

Comment and share

DATABASE-Sub Query

Sub Query

Example

SUB QUERY 사용시 JOIN TABLE 크기를 줄일 수 있다

DATABASE-JOIN UNION

JOIN

MAKE TEST TABLE & DATA

INNER JOIN

LEFT JOIN

RIGHT JOIN

JOIN과 DISDINCT의 사용

테이블 세개 조인하기

UNION

DATABASE-DML-INSERT UPDATE DELETE

INSERT

UPDATE SET

DELETE, DROP, TRUNCATE

DATABASE DML SELECT

SELECT

정의 : 데이터를 검색할때 사용되는 문법

SELECT FROM

WHERE

ORDER BY

LIMIT

GRUOP BY, HAVING

Yechan Kim

`SUB QUERY 사용시 JOIN TABLE 크기를 줄일 수 있다`