Raspberry Pi(Web 환경 원격 제어): mysql-basic-sql

MySQL - Basic - SQL

MySQL(MariaDB) - Basic - SQL

Database와 Table 만들고 관리하기

Database 사용을 위한 준비
Database 만들고 관리하기
Table 만들기

Database 예 설치하기
Querying data
Sorting data
Filtering data
Joining tables
Grouping data
Modifying data in MySQL

Database와 Table 만들고 관리하기

이 페이지에서는 Mysql command window(Putty와 같은 터미널 프로그램으로 Raspberry Pi에 접속한 다음 mysql에 연결한 경우 열리는 창으로 Mysql 명령을 실행 할 수 있다.)에서 Mysql 명령(SQL 사용)을 사용하여 Database와 Table 만들고 관리한다.

이 페이지에서 SQL에 대하여 설명하는 이유는 인터넷에서 데이터베이스 서비스를 하는데 필요한 프로그래밍 언어(PHP, Javascript, Python 언어 등)에서 SQL 지식을 필요로 하기 때문이다.

Database 사용을 위한 준비

Database를 사용하기 위하여는 Database server(MySQL 또는 MariaDB) 가 설치되어야 한다. 또한 대부분의 경우 Web server(Apache web server)와 Database 관리를 위한 GUI Tool(phpMyAdmin)를 함께 사용한다.

참고자료: Web server(Apache web server) 설치하기
참고자료: Database 관리와 설정에 phpMyAdmin 사용하기
참고자료: Database server(MariaDB) 설치하기

Database 만들고 관리하기

비 전문가인 경우 phpMyAdmin를 사용하는 것이 현실적으로 편리한 방법이다. 그러나 이 페이지에서는 SQL에 대한 이해를 위하여 콘솔 창(어느 정도 SQL를 이해하면 phpMyAdmin의 SQL 창을 사용하여 실험하는 것이 더 편리함. phpMyAdmin의 SQL 창은 SQL 예을 웹 브라우저에서 복사하여 사용 할 수 있음.)에서 mysql 명령을 사용하여 데이터베이스를 관리하는 예를 설명한다. 이 페이지에서 SQL에 대하여 설명하는 것은 인터넷 환경(웹서버와 프로그래밍 언어를 사용)에서 데이터베이스를 관리 하는 프로그램 작성에 SQL에 대한 이해를 필요로하기 때문이다.

콘솔 창(보통 putty를 사용함)에서 Database 연결하기

아래와 같이 Database 사용자 계정(데이터베이스의 생성과 삭제를 위하여 root 계정으로 로그인 하는 것이 보통임)으로 로그인 한다.

sudo mysql -u <username> -p

Database 만들기

아래와 같은 명령으로 새 Database를 만든다.

CREATE DATABASE [IF NOT EXISTS] database_name;

위에서 [IF NOT EXISTS]은 옵션으로 SQL 문으로 Database를 만들 때 만약 동일한 이름의 Database 가 없는 경우에만 다시 만들라는 조건문 이다.

Database 보기

아래와 같은 명령으로 새로 만든 Database를 확인한다.

SHOW DATABASES;

사용 할 Database 선택하기

아래와 같은 명령으로 사용할 Database를 선택한다.

USE database_name;

Database 제거하기

필요 없게된 Database는 아래와 같은 명령으로 삭제 할 수 있다.

DROP DATABASE [IF EXISTS] database_name;

위에서 [IF EXISTS]은 옵션으로 SQL 문으로 Database를 제거 할 때 만약 동일한 이름의 Database 가 있는 경우에만 제거 하라는 조건문 이다.

Table 만들기

Table을 생성하는 기본문법(Syntax)는 아래와 같다.


CREATE TABLE [IF NOT EXISTS] table_name(
   column_1_definition,
   column_2_definition,
   ...,
   table_constraints
) ENGINE=storage_engine;

Table 생성 명령(CREATE TABLE) 다음에 오는 table_name은 데이터베이스 내에서 중복하여 사용할 수 없다.
[IF NOT EXISTS]은 옵션으로 만약 동일한 이름의 Table이 없는 경우에만 Table를 생성하라는 조건문 이다.
괄호안에는 Table 내의 컬럼(columns) 리스트를 나열한다. 컬럼은 콤마로 분리된다.
마자막 줄에는 데이터베이스 저장 엔진(별도로 지정하지 않으면 InnoDB로 설정됨)을 지정한다.

컬럼(columns)을 생성하는 기본문법(Syntax)는 아래와 같다.

column_name data_type(length) [NOT NULL] [DEFAULT value] [AUTO_INCREMENT] column_constraint;

각 컬럼은 데이터형(Data type)을 지정하여야 한다. 예: VARCHAR, INT 등
NOT NULL 제약 조건은 옵션으로 칼럼에 NULL이 포함되지 않도록한다. NOT NULL 제약 조건 외에도 칼럼에는 CHECK 및 UNIQUE와 같은 추가 제약 조건이있을 수 있다.
DEFAULT는 옵션으로 칼럼의 기본값을 지정한다.
AUTO_INCREMENT는 옵션으로 새 행이 테이블에 삽입 될 때마다 칼럼 값이 자동으로 1 씩 증가 하도록한다. 각 테이블에는 하나의 AUTO_INCREMENT 칼럼 만 있을 수 있다.
칼럼 목록 뒤에 UNIQUE, CHECK, PRIMARY KEY 및 FOREIGN KEY와 같은 테이블 제약 조건을 정의 할 수 있다.

아래 예는 customersTable을 생성하는 예 이다.


DROP TABLE IF EXISTS `customersTable`;
CREATE TABLE `customersTable` (
  `customerNo` INT NOT NULL ,
  `name` VARCHAR(16) NOT NULL ,
  `phone` VARCHAR(16) NOT NULL ,
  `address` VARCHAR(50) NULL ,
  PRIMARY KEY (`customerNo`),
  UNIQUE (`phone`)
  ) ENGINE = InnoDB DEFAULT CHARSET=utf8;

위에서 생성한 Table의 구조는 아래 SQL 문(DESCRIBE)으로 확인 할 수 있다.

DESCRIBE customersTable;

Database 예 설치하기

SQL 문을 설명하기 위한 데이터베이스 예로 choSampleDB1을 사용한다.

choSampleDB1을 설치하기

아래에서 choSampleDB1.zip을 다운로드 하여 압축을 푼다.

choSampleDB1: choSampleDB1.zip

root 권한으로 mysql에 로그인한 다음 아래 명령 예를 참고하여 choSampleDB1을 설치한다.

mysql> source c:\temp\choSampleDB1.sql

Querying data

SELECT 문은 하나 이상의 Table로 부터 데이터를 읽을 수 있게 한다. SELECT 문의 기본형은 아래와 같다.

SELECT select_list

FROM table_name;

위에서 SELECT는 Mysql에서 사용하는 키 워드이다. ";" 문자는 sql 문(Statement)을 구분하는 구분자이다. sql은 각 문 단위로 실행된다.

choSampleDB1 데이터베이스의 customersTable에서 이름과 전화번호를 읽는 SELECT 문의 예는 아래와 같다.

SELECT name, phone

FROM customersTable;

Sorting data

SELECT 문의 출력은 순차적으로 정렬되어 있지 않다. SELECT 문에 ORDER BY 절(clause)을 사용하여 SELECT 문의 출력을 정렬 할 수 있다.

SELECT 문과 ORDER BY 절(clause)을 사용하는 기본형은 아래와 같다.


SELECT 
   select_list
FROM 
   table_name
ORDER BY 
   column1 [ASC|DESC], 
   column2 [ASC|DESC],
   ...;

위에서 ORDER BY 절은 하나 이상의 칼럼을 같는다. ASC(Ascending) 옵션을 데이터를 오름차순으로 정렬하고, DESC(Decending) 옵션은 내림차순으로 정렬하게 한다.

choSampleDB1 데이터베이스의 customersTable에서 이름과 전화번호를 읽는 이름 순으로 정렬하는 예는 아래와 같다.


SELECT
	name, phone
FROM
	customersTable
ORDER BY
	name;

윗 예와 같이 정렬 옵션을 생략한 경우에는 ASC(Ascending) 옵션이 적용된다.

Filtering data

WHERE 절(clause) 검색 조건을 지정한다. WHERE 절을 사용하는 기본형은 아래와 같다.


SELECT 
    select_list
FROM
    table_name
WHERE
    search_condition;

search_condition은 논리 연산자 AND, OR 및 NOT을 하나 이상의 조합으로 사용 할 수 있다.

SELECT 문 외에도 UPDATE 또는 DELETE 문에서 WHERE 절을 사용하여 업데이트하거나 삭제할 행을 지정할 수 있다.

choSampleDB1 데이터베이스의 customersTable에서 지정하는 이름을 검색하여 이름과 전화번호를 읽는 예는 아래와 같다.


SELECT 
    name, phone
FROM
    customersTable
WHERE
    name = '조수돌';

search_condition은 논리 연산자 AND, OR 및 NOT을 하나 이상의 조합으로 사용 할 수 있다.

SELECT 문 외에도 UPDATE 또는 DELETE 문에서 WHERE 절을 사용하여 업데이트하거나 삭제할 행을 지정할 수 있다.

WHERE 절(clause): WHERE 절(clause)을 사용하는 다양한 예를 보여주는 페이지

WHERE 절과 함께 사용하는 다양한 절과 연산자(Operator)의 사용 예를 보여주는 페이지

Joining tables

열 이름이 너무 기술적 인 것으로 쿼리 결과를 이해하기가 어려운 경우, 이해하기 쉬운 열 이름을 사용하기 위해 열 별칭을 사용한다.

Table 열(column) 별칭사용하기

열 별칭을 사용하는 기본형은 아래와 같다.

SELECT

[column_1 | expression] AS descriptive_name

FROM table_name;

descriptive_name에 Spaces 가 포함된 경우 'descriptive_name' 과 같이 표시 한다. AS 는 생략 될 수 있다.

CONCAT_WS() 함수를 사용하는 예

SELECT

CONCAT_WS(', ', name, phone) `Contact Info`

FROM customersTable;

Table 별칭사용하기

Table 이름이 너무 기술적 인 것으로 쿼리 결과를 이해하기가 어려운 경우, 이해하기 쉬운 Table 이름을 사용하기 위해 Table 별칭을 사용한다.

Table 별칭을 사용하는 기본형은 아래와 같다.

table_name AS table_alias

Table 별칭을 사용하는 예

SELECT

c.name, c.phone

FROM customersTable c;

Table 별칭은 아래 예와 같이 INNER JOIN, LEFT JOIN, RIGHT JOIN 절을 함께 사용 할 수 있다.


SELECT
    orderNo,
    (o.quantityOrdered * p.salePrice) total
FROM
    orderdetailsTable o
INNER JOIN productsTable p ON ((o.productCode = 'S1-2') and (p.productCode = 'S1-2'));

아래 예는 여러 개의 Table로 부터 검색 조건과 일치하는 결과를 출력하는 예 이다.


SELECT
    ot.orderNo, c.name, p.productName, dt.quantityOrdered, p.salePrice,
    (dt.quantityOrdered * p.salePrice) total
FROM
    ordersTable ot, customersTable c, productsTable p, orderdetailsTable dt
WHERE
    (ot.orderNo = 2 AND c.customerNo = ot.customerNo) AND (dt.orderNo = 2 AND p.productCode = dt.productCode);

윗 예는 4개의 Table로 부터 주문번호 2의 주문번호(ordersTable), 주문자 이름(customersTable), 제품이름(productsTable), 주문수량(orderdetailsTable), 단가(productsTable), 총액(주문수량 * 단가: productsTable, orderdetailsTable)을 출력하는 쿼리이다.

참고자료: Mysql 별칭을 사용하는 다양한 예를 보여주는 페이지

MySQL join 절(clauses)

관계형 데이터베이스는 외래 키 열이라고 하는 공통 열을 사용하여 서로 연결된 여러 관련 테이블로 구성되기 때문에 각 테이블의 데이터 만으로는 통일된 의미를 갖지 못하는 경우가 대부분이다. 그렇기 때문에 완전한 정보를 표시하기 위하여는 하나 이상의 Table 정보(Column 정보)를 결합하여야 하는 경우가 대부분이다.

MySQL INNER JOIN 절(clause)

INNER JOIN 절은 첫 번째 테이블의 열(Row)과 두 번째 테이블 열(Row)의 결합 조건을 비교하여, 결합 조건이 true인 경우로 두 테이블의 열(Row)에서 필요한 데이터(행(Column) 테이터)을 결합하여 의미 있는 새 열(Row)을 작성한다.

INNER JOIN을 사용하는 기본형은 아래와 같다.


SELECT
    select_list
FROM t1
INNER JOIN t2 ON join_condition1
INNER JOIN t3 ON join_condition2
...;

위에서 FROM 절 옆에 있는 t1 이 Main Table 이다.

FROM 절 아래에 있는 t2, t3, ... Table이 Main Table에 Join 되는 Table 이다.

INNER JOIN 절 키워드 ON 다음에 Join condition을 열거한다.

Main Table(t1)과 Join 되는 Table(t2, t3, ...) 사이에 Join 조건을 만족하는 열이 있는 경우 Main 테이블의 열(Row)과 Join 되는 Table의 열에서 필요한 데이터(행(Column) 테이터)을 결합하여 의미 있는 새 열(Row)을 출력(생성)한다.

아래 예는 orderdetailsTable(Mian Table)과 productsTable(Join 되는 Table) 사이에 제품번호(productCode)가 일치하는 경우 orderdetailsTable에 있는 주문번호(orderNo)와 productsTable에 있는 제품번호(productCode), 상품명(productName)을 결합(Join)하여 출력하는 쿼리 이다.


SELECT
    o.orderNo, p.productCode, p.productName
FROM
    orderdetailsTable o
INNER JOIN productsTable p ON (o.productCode = p.productCode);

결합하는 두 테이블의 결합 조건에 사용하는 행의 이름이 같은 경우 아래와 같이 USING 키 위드를 사용 할 수 있다.


SELECT
    o.orderNo, p.productCode, p.productName
FROM
    orderdetailsTable o
INNER JOIN productsTable p USING (productCode);

3개의 테이블을 결합 하는 예


SELECT
    o.orderNo, ot.orderDate, p.productCode, p.productName
FROM
    orderdetailsTable o
INNER JOIN ordersTable ot USING (orderNo)
INNER JOIN productsTable p USING (productCode)
ORDER BY orderDate DESC;

Main Table(orderdetailsTable)과 Join 되는 Table(ordersTable, productsTable) 사이에 Join 조건을 만족하는 열이 있는 경우 Main 테이블의 열(Row)과 Join 되는 Table의 열에서 필요한 데이터(행(Column) 테이터)을 결합하여 새 열(o.orderNo, ot.orderDate, p.productCode, p.productName)을 만들고 이 열을 주문일(orderDate)에 따라 내림차 순서(Decending: 최근 주문을 먼저 출력함))로 출력한다.

4개의 테이블을 결합 하는 예


SELECT
    o.orderNo, c.name, ot.orderDate, p.productCode, p.productName
FROM
    orderdetailsTable o
INNER JOIN ordersTable ot USING (orderNo)
INNER JOIN productsTable p USING (productCode)
INNER JOIN customersTable c USING (customerNo)
ORDER BY name;

Main Table(orderdetailsTable)과 Join 되는 Table(ordersTable, productsTable, customersTable) 사이에 Join 조건을 만족하는 열이 있는 경우 Main 테이블의 열(Row)과 Join 되는 Table의 열에서 필요한 데이터(행(Column) 테이터)을 결합하여 새 열(o.orderNo, c.name, ot.orderDate, p.productCode, p.productName)을 만들고 이 열을 이름(name)에 따라 오름차 순서(Ascending: 가나다 순서로 출력함))로 출력한다.

INNER JOIN과 다른 연산자(Operators)를 사용하는 예


SELECT
    o.orderNo, p.productCode, p.productName, p.quantityInStock, o.quantityOrdered
FROM
    orderdetailsTable o
INNER JOIN productsTable p ON o.productcode = p.productcode
    AND p.quantityInStock > o.quantityOrdered
WHERE
    p.productcode = 'S2-1';

Main Table(orderdetailsTable)과 Join 되는 Table(productsTable) 사이에 제품번호가 'S-2-1' 이고 Join 조건(제품번호가 같고, 제품 재고가 주문수량 보다 큰 경우)을 만족하는 열이 있는 경우 Main 테이블의 열(Row)과 Join 되는 Table의 열에서 필요한 데이터(행(Column) 테이터)을 결합하여 새 열(o.orderNo, p.productCode, p.productName, p.quantityInStock, o.quantityOrdered)을 만들어 출력한다.

MySQL LEFT JOIN 절(clause)

LEFT JOIN 절을 사용하는 기본형은 아래와 같다.


SELECT 
    select_list
FROM
    t1
LEFT JOIN t2 ON 
    join_condition;

위 기본형에서 t1은 Left table 이라하고, t2 를 Right table 이라고 한다.

왼쪽에 있는 테이블(t1)의 모든 열(Rows)과 오른쪽 테이블(t2)의 열을 Join 조건에 따라 비교하고 조건을 만족하는 열이 오른쪽 테이블에 있는 경우 왼쪽 테이블의 열과 오른쪽 테이블의 열에서 필요한 데이터(행(Column) 테이터)을 결합하여 새 열을 만든다. 만약 Join 조건을 만족하는 열이 없는 경우 왼쪽 테이블의 열 데이터만 포함하고 오른쪽 데이블의 데이터는 NULL로 대치하여 새 열을 만든다.

LEFT JOIN을 사용 하는 예


SELECT
    o.orderNo, c.name, c.phone, o.orderDate
FROM
    customersTable c
LEFT JOIN ordersTable o ON o.customerNo = c.customerNo
ORDER BY orderNo;

customersTable의 모든 주문자(주문자번호(customerNo)로 확인)에 대하여 ordersTable에 일치하는 주문자번호(customerNo)가 있는 경우 주문번호(orderNo), 고객이름(name), 전화번호(phone), 주문날짜(orderDate)을 오름차 순서(Ascending)로 출력한다. 만약 customersTable의 주문자번호(customerNo)와 일치하는 주문자번호(customerNo)가 ordersTable에 없는 경우에는 해당 주문자의 주문번호(orderNo)와 주문날짜(orderDate)에 NULL이 출력된다.

MySQL LEFT JOIN 절(Clause)은 두 Table이 정합되지 않는(Unmatched) 열을 찾는데 이용 할 수 있다. 두 Table이 정합되지 않는(Unmatched) 경우 오른쪽 데이블의 데이터는 NULL로 대치되기 때문에 해당 행의 값이 NULL인 경우 두 Table이 정합되지 않는 것으로 판단할 수 있다.

3개의 테이블의 Join에 LEFT JOIN을 사용 하는 예 1


SELECT
    o.orderNo, c.name, c.phone, ot.orderDate, o.productCode
FROM
    orderdetailsTable o
LEFT JOIN ordersTable ot ON o.orderNo = ot.orderNo
LEFT JOIN customersTable c ON ot.customerNo = c.customerNo
ORDER BY orderNo;

모든 주문상품(orderdetailsTable의 orderNo)에 대하여 orderdetailsTable의 주문번호(orderNo)와 일치하는 주문번호(orderNo)가 ordersTable에 있는 경우 주문번호(orderNo)와 주문날자(orderDate), 제품코드(productCode)를 결합하고, ordersTable과 customersTable에 서로 일치하는 주문자번호(customerNo) 가 있는 경우 주문자 이름(name)과 전화번호(phone)를 결합하여 새 열을 만들고 주문자번호 순서로 정렬하여 출력한다.

MySQL RIGHT JOIN 절(clause)

RIGHT JOIN 절을 사용하는 기본형은 아래와 같다.


SELECT 
    select_list
FROM t1
RIGHT JOIN t2 ON 
    join_condition;

오른쪽에 있는 테이블(t2)의 모든 열(Rows)과 왼쪽 테이블(t1)의 열를 Join 조건에 따라 비교하고 조건을 만족하는 열이 왼쪽 테이블에 있는 경우 오른쪽 테이블의 열과 왼쪽 테이블의 열에서 필요한 데이터(행(Column) 테이터)을 결합하여 새 열을 만든다. 만약 Join 조건을 만족하는 열이 없는 경우 오른쪽 테이블의 열 데이터만 포함하고 왼쪽 데이블의 데이터는 NULL로 대치하여 새 열을 만든다.

Inner Join, Left Join, Right Join을 관계를 보여 주는 SQL join venn diagrams. Blue Lotus 색이 각 Join의 출력이다.

MySQL CROSS JOIN 절(clause)

한 쪽 테이블의 각 행(Row)들과 다른 테이블의 모든 행을 Join 한다.

그결과, CROSS JOIN을 실행한 후의 행의 개 수는 두 테이블의 행의 개 수를 곱한 것과 같다.

CROSS JOIN은 테스트로 사용할 대용량의 테이블을 생성할 경우 등에 사용된다.

MySQL Self Join

테이블 내 서로 다른 열에 있는 데이터가 동일한 값을 갖지만 다른 의미를 가지는 경우, 다른 컬럼에 존재하는 두 데이터를 SELF JOIN 하여 의미 있는 정보를 생성하는 경우에 사용한다.

Grouping data

GROUP BY 절을 사용하여 특정 행을 기준으로 데이터를 그룹화 하기

GROUP BY 절은 특정 행을 기준으로 데이터를 그룹(행의 데이터(값이)가 동일한 경우 하나의 그룹으로 분류됨)으로 나눌 수 있다. 또한 집계함수(COUNT, SUM, AVG, MAX, MIN 등)을 이용하여 각 그룹 별 집계 값을 구 할 수 있다.

GROUP BY 절을 이용하는 예


SELECT 
    productCode, SUM(quantityOrdered)
FROM
    orderdetailsTable
GROUP BY productCode;

주문상품(orderdetailsTable의 productCode) 별로(주문상품에 따라 그룹화) 주문수량(SUM(quantityOrdered))의 합계을 출력한다.

GROUP BY 절과 집계함수(SUM), 연산(*)을 이용하는 예


SELECT
    p.productCode, p.productName, p.salePrice, SUM(o.quantityOrdered), (SUM(o.quantityOrdered) * p.salePrice) Total
FROM
    orderdetailsTable o
LEFT JOIN productsTable p USING (productCode)
GROUP BY productCode;

주문상품(orderdetailsTable의 productCode) 별로(주문상품에 따라 그룹화) 제품번호, 제품이름, 판매가, 주문수량 합계, 각 품목 별 주문금액 합계를 출력한다.

GROUP BY 절과 HAVING 절을 사용하는 예

Having 절은 Group by로 집계된 값 중 where 절 처럼 특정 조건을 만족하는 그룹만 선택하도록 한다.

GROUP BY 절과 HAVING 절, 집계함수(SUM), 연산(*)을 이용하는 예


SELECT
    p.productCode, p.productName, p.salePrice, SUM(o.quantityOrdered), (SUM(o.quantityOrdered) * p.salePrice) Total
FROM
    orderdetailsTable o
LEFT JOIN productsTable p USING (productCode)
GROUP BY productCode
HAVING 
    Total > 20000;

주문상품(orderdetailsTable의 productCode) 별로(주문상품에 따라 그룹화) 20,000원을 초과하는 제품의 제품번호, 제품이름, 판매가, 주문수량 합계, 주문금액 합계를 출력한다.

ROLLUP을 사용하여 GROUPING BY 절에 의하여 그룹된 그룹의 계(소계, 또는 총계)를 생성하기

GROUPING BY 절에 의하여 그룹된 그룹의 계(소계, 또는 총계)를 생성하는데 사용된다. 간단한 문법으로 다양한 계를 출력 할 수 있다. ROLLUP으로 계을 구하기 위하여는 SELECT절에 집계함수가 사용되어야 한다.

GROUP BY 절과 ROLLUP 절을 이용하여 중간합계(판매량, 금액)와 총계를 구하는 예


SELECT
    p.productCode, p.productName, ot.orderDate, p.salePrice, SUM(o.quantityOrdered) quantSubTotal, (SUM(o.quantityOrdered) * p.salePrice) SubTotal
FROM
    orderdetailsTable o
INNER JOIN productsTable p USING (productCode)
INNER JOIN ordersTable ot USING (orderNo)
GROUP BY
    productCode,
    orderDate
WITH ROLLUP;

날자 별(주문날자에 따라 그룹화)로 주문 상품의 소계(주문량 소계:quantSubTotal, 소계:SubTotal)를 구하고 주문상품(orderdetailsTable의 productCode) 별로 중간 합계를 구한 다음 총계를 구하여 출력한다. 이 예의 출력에는 판매 날자와 제품부호 란에 중간 합계와 총계가 NULL로 출력된다.

참고: 위 SQL 문를 phpMyAdmin의 SQL 창에서 실행 하는 경우 "WITH ROLLUP"에 Error 표시가 출력(MariaDB 와 phpMyAdmin의 버전 문제일 수 있음)되는 경우가 있으나 실행에는 문제가 없음.

중간 합계와 총계출력 란을 COALESCE 함수를 사용하여 의미있는 문자열(윗 예에서 NULL로 출력되는 란을 의미있는 문자열로 대치)로 출력하는 예

COALESCE 함수는 인수 리스트 중 첫번째로 Null 이 아닌 값을 Return 한다.


SELECT
    COALESCE(p.productCode, 'All Years', p.productCode) AS productCode,
    p.productName,
    COALESCE(ot.orderDate, 'Total', ot.orderDate) AS orderDate,
    p.salePrice, SUM(o.quantityOrdered) quantSum, (SUM(o.quantityOrdered) * p.salePrice) SubTotal
FROM
    orderdetailsTable o
INNER JOIN productsTable p USING (productCode)
INNER JOIN ordersTable ot USING (orderNo)
GROUP BY
    p.productCode,
    ot.orderDate
WITH ROLLUP;

날자 별(주문날자에 따라 그룹화)로 주문 상품의 소계(주문량 소계:quantSubTotal, 소계:SubTotal)를 구하고 주문상품(orderdetailsTable의 productCode) 별로 중간 합계를 구한 다음 총계를 구하여 출력한다. 이 예의 출력에는 판매 날자와 제품부호 중간 합계와 총계출력 란이 "Total"로 출력되고 제품부호의 마지막 란에 "All Years" 가 출력된다.

Modifying data in MySQL

Table을 변경하는 예(INSERT, UPDATE, DELETE, REPLACE)를 설명하기 위하여 아래와 같이 데이터베이스(testDB)와 Table(ordersTableTest)을 생성하여 이용한다.

테스트에 사용 할 데이터베이스(testDB)와 Table(ordersTableTest)을 생성하는 SQL 예


CREATE DATABASE IF NOT EXISTS `testDB` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `testDB`;

CREATE TABLE IF NOT EXISTS ordersTableTest (
  `orderNo` int(11) AUTO_INCREMENT,
  `orderDate` date NOT NULL,
  `shippedDate` date DEFAULT NULL,
  `status` varchar(16) NOT NULL DEFAULT '준비중',
  `customerNo` int(11) NOT NULL,
  PRIMARY KEY (orderNo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Table에 데이터(열)를 삽입(INSERT)하기

INSERT 문은 Table에 하나 이상의 열을 삽입 하는데 사용한다.

INSERT 문의 기본형은 아래 예와 같다.

INSERT INTO table(c1,c2,...)

VALUES (v1,v2,...);

"INSERT INTO" 다음에 Table 이름이 오고, 계속하여 괄호안에 콤마로 구분한 행 이름을 열기한다.
계속하여 VALUES 다음 괄호안에 콤마로 구분한 데이터 값(Values)을 열기한다.
행 수와 데이터 수는 동일하여야 하고, 행의 위치에 대응하는 데이터가 해당 행에 삽입된다.
여러 열(Rows)을 삽입하여야 하는 경우에는 열 사이를 콤마로 구분한다.
INSERT 문을 사용하는 예

위에서 생성한 Table에 아래와 같은 INSERT 문을 사용하여 레코드를 삽입한다.


INSERT INTO ordersTableTest(orderDate, shippedDate, status, customerNo)
VALUES
  ('2020-05-01', '2020-05-01', '배달완료', 12),
  ('2020-05-05', '2020-05-06', '배달중', 13),
  ('2020-05-06', DEFAULT, DEFAULT, 10);

위 INSERT 문을 실행하고 Table 보기를 실행한 결과

orderNo: orderNo는 AUTO_INCREMENT 한 PRIMARY KEY로 선언되었기 때문에 INSERT 문에서 입력하지 않아도 자동으로 1씩 증가한 값이 삽입된다.
orderDate: 이 값은 YYYY-MM-DD 양식으로 주문시 반듯이 입력되어야 한다.
shippedDate: 이 값은 별도로 입력하지 않으면 NULL 이 삽입된다.
status: 이 값은 별도로 입력하지 않으면 '준비중' 문자열이 삽입된다.
customerNo: customerNo에는 정수값이 반듯이 입력되어야 한다. 만약 이곳에 다른 Table(예: 고객 Table에서 정의되고 고객번호가 부여되는 경우)에서 정의된 값이 반듯이 입력되어야 하는 경우에는 customerNo를 FOREIGN KEY로 설정한다.

SELECT 문의 실행 결과를 INSERT 하기

SELECT 문의 결과를 INSERT 문의 데이터 소스로 사용할 수 있다.

아래 예는 SELECT 문의 결과를 INSERT 문의 데이터 소스로 사용하는 기본형 이다.


INSERT INTO table_name(column_list)
SELECT 
   select_list 
FROM 
   another_table
WHERE
   condition;

윗 예에서 VALUES절 대신 SELECT 문이 사용되었다. SELECT 문은 하나 이상의 테이블에서 데이터를 검색 하여 새 Table에 삽입 할 수 있다.

INSERT IGNORE 문

INSERT 문을 사용하여 테이블에 여러 열을 추가하는 도중에 오류가 발생하면 MySQL은 문을 종료하고 오류를 반환합니다. 결과적으로 테이블에 열이 삽입되지 않는다. 그러나 INSERT IGNORE 문을 사용하면 오류를 발생시키는 유효하지 않은 데이터가 있는 열은 무시되고 유효한 데이터가 있는 열은 테이블에 삽입된다.

아래 예는 INSERT IGNORE 문을 사용하는 기본형 이다.

INSERT IGNORE INTO table(column_list)

VALUES( value_list), ( value_list), ... ;

UPDATE 문을 사용하여 테이블의 기존 데이터를 수정하기

UPDATE 문은 테이블의 기존 데이터를 수정한다. 하나 이상의 열에서 단일 행 또는 여러 행의 값을 UPDATE 문으로 변경할 수 있다.

UPDATE 문을 사용하는 기본형 예


UPDATE [LOW_PRIORITY] [IGNORE] table_name 
SET 
    column_name1 = expr1,
    column_name2 = expr2,
    ...
[WHERE
    condition];

첫줄에서는 먼저 UPDATE 키워드 다음에 데이터를 업데이트하려는 테이블의 이름을 지정한다.
다음, SET절 내에 업데이트 할 행과 새 값을 지정한다. 여러 열의 값을 업데이트 하려면 각 열의 Update 값을 문자열, 식 또는 하위 쿼리(Subquery) 형식으로 사용한다.
마지막으로, WHERE 절의 조건을 사용하여 업데이트 할 열을 지정한다. 여기서 WHERE 절은 선택 사항이다. 만약 WHERE 절을 생략하면 UPDATE 문은 테이블의 모든 열을 업데이트 한다.
위에서 옵션으로 사용하는 LOW_PRIORITY 수정자는 테이블에서 데이터를 읽는 연결이 없을 때까지 업데이트를 지연 시키도록 한다.
IGNORE 수정자는 오류가 발생한 경우에도 UPDATE 문이 열을 계속 갱신하도록 한다. 중복 키 충돌과 같은 오류를 일으키는 열은 업데이트되지 않는다.

UPDATE 문을 사용하는 예


UPDATE ordersTableTest
SET 
    shippedDate = '2020-05-07',
    status = '배달중'
WHERE
    orderNo = 3;

Table 보기로 위 SQL 문의 실행 결과를 확인하면 주문번호 3번의 배송일(shippedDate: '2020-05-07')과 상태(status: '배달중')가 변경된 것을 확인 할 수 있다.

DELETE 문을 사용하여 테이블에서 데이터를 삭제하기

테이블에서 데이터를 삭제하는 경우 DELETE 문을 사용한다.

DELETE 문의 기본형은 아래 예와 같다.

DELETE FROM table_name

WHERE condition;

위에서 WHERE 절은 선택 사항이다. 만약 WHERE 절이 생략되면 Table의 모든 내용이 삭제된다.

DELETE 문을 사용하는 예


DELETE FROM ordersTableTest 
WHERE
    orderNo = 3;

Table 보기로 위 SQL 문의 실행 결과를 확인하면 주문번호 3번의 레코드(열)가 삭제된 것을 확인 할 수 있다.

INNER JOIN 절을 사용하는 DELETE 문을 사용하여 여러개의 Table로 부터 열을 삭제하기

DELETE 문에서 INNER JOIN 절(또는 LEFT JOIN 절)을 사용하여 기준 테이블의 열(row)과 다른 테이블의 일치하는 열을 삭제할 수 있다.

DELETE 문과 INNER JOIN 절을 사용하여 두개 Table의 열을 삭제하는 기본형은 아래 예와 같다.

DELETE Table1, Table2

FROM Table1

INNER JOIN Table2 ON Table1.key = Table2.key

WHERE condition;

DELETE와 FROM 키워드 사이에 테이블 이름 Table1과 Table2이 온다. 만약 Table1 테이블을 생략하면 DELETE 문은 Table2 테이블의 행만 삭제한다. 마찬가지로, Table2 테이블을 생략하면 DELETE 문은 Table1 테이블의 행만 삭제한다.

Table1.key = Table2.key 표현식은 Table1과 Table2 테이블 사이에서 삭제 할 열을 일치시키는 조건(Key)을 지정한다.

WHERE 절의 조건에 따라 Table1, Table2에서 삭제 할 열이 결정된다.

DELETE 문과 INNER JOIN 절을 사용하여 두개의 Table에서 열을 삭제하는 예

choSampleDB1에서 ordersTable과 orderdetailsTable의 내용을 Table 보기 명령으로 확인 한다.
아래 SQL 문을 사용하여 주문번호 3번 레코드를 ordersTable과 orderdetailsTable에서 삭제한다.


DELETE ordersTable, orderdetailsTable 
  FROM ordersTable
  INNER JOIN orderdetailsTable ON ordersTable.orderNo = orderdetailsTable.orderNo
WHERE
    ordersTable.orderNo = 3;

주문번호 3번 레코드의 삭제 여부를 ordersTable과 orderdetailsTable의 Table 보기로 확인 한다.

참고: phpMyAdmin SQL를 이용하는 경우 "INNER JOIN" 키 워드 앞에 Error 가 표시되지만 무시하여도 된다.

DELETE 문과 LEFT JOIN 절을 사용하여 Table에서 열(레코드)을 삭제하는 예

choSampleDB1에서 customersTable과 ordersTable의 내용을 Table 보기로 확인 한다.
아래 SQL 문을 실행한다. 아래 SQL 문은 주문이 없는(ordersTable에 customerNo 가 없는) 고객 명단(레코드)을 customersTable에서 모두 삭제하는 예 이다.

	
DELETE customersTable 
  FROM customersTable
  LEFT JOIN ordersTable ON customersTable.customerNo = ordersTable.customerNo
WHERE
    ordersTable.customerNo IS NULL;

주문이 없는 고객번호 4번 레코드의 삭제 여부를 customersTable에서 확인 한다.

REPLACE 문을 사용하여 테이블에 이미 존재하는 열(row)을 새로운 열로 대치하기

테이블에 이미 존재하는 열(row)을 새로운 열로 대치 한다. 존재하는 열을 확인하기 위해 PRIMARY KEY 또는 UNIQUE KEY 인덱스를 사용한다. 테이블에 이러한 인덱스 중 하나가 없으면 REPLACE는 INSERT 문처럼 동작한다.

참고자료: MySQL REPLACE