잔재미코딩
블로그 자료모음 기업교육

SQL DML(Data Manipulation Language) 이해 및 실습

이해하기 쉽고, 장황하지 않은 자료를 기반으로 강의를 진행합니다.
잔재미코딩 소식 공유
좀더 제약없이, IT 컨텐츠를 공유하고자, 자체 온라인 사이트와, 다음 두 채널도 오픈하였습니다
응원해주시면, 곧 좋은 컨텐츠를 만들어서 공유하겠습니다
●  잔재미코딩 뉴스레터 오픈 [구독해보기]
●  잔재미코딩 유투브 오픈 [구독해보기]

4. SQL DML(Data Manipulation Language) 이해 및 실습 (focusing on CRUD)

4.1. CRUD [Create(생성), Read(읽기), Update(갱신), Delete(삭제)]

  • 데이터 관리는 결국 데이터 생성, 읽기(검색), 수정(갱신), 삭제 를 한다는 의미

4.1.1 데이터 생성

  • 테이블에 컬럼에 맞추어 데이터를 넣는 작업

  • 기본 문법 (INSERT)

    1. 테이블 전체 컬럼에 대응하는 값을 모두 넣기

      mysql> INSERT INTO 테이블명 VALUES(값1, 값2, ...);

    2. 테이블 특정 컬럼에 대응하는 값만 넣기 (지정되지 않은 컬럼은 디폴트값 또는 NULL값이 들어감)

      mysql> INSERT INTO 테이블명 (col1, col2, ...) VALUES(값1, 값2, ...);

예)
# mysql -u root -p
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| dave               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
mysql> use dave;
mysql> show tables;
+----------------+
| Tables_in_dave |
+----------------+
| mytable        |
+----------------+

mysql> desc mytable;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name       | varchar(20)      | NO   |     | NULL    |                |
| model_num  | varchar(10)      | NO   |     | NULL    |                |
| model_type | varchar(10)      | NO   |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

mysql> INSERT INTO mytable VALUES(1, 'i7', '7700', 'Kaby Lake');

Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM mytable;
+----+------+-----------+------------+
| id | name | model_num | model_type |
+----+------+-----------+------------+
|  1 | i7   | 7700      | Kaby Lake  |
+----+------+-----------+------------+
1 row in set (0.00 sec)

mysql> INSERT INTO mytable (name, model_num, model_type) VALUES('i7', '7700K', 'Kaby Lake');

Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM mytable;
+----+------+-----------+------------+
| id | name | model_num | model_type |
+----+------+-----------+------------+
|  1 | i7   | 7700      | Kaby Lake  |
|  2 | i7   | 7700K     | Kaby Lake  |
+----+------+-----------+------------+
2 rows in set (0.00 sec)
실습 - 데이터 생성(입력), 검색
웹페이지(http://www.enuri.com/list.jsp?cate=070701) 에서 1위 ~ 5위까지 데이터 입력하기

- id는 자동 증가
- name 은 코어 이름
- model_num 은 코어 모델 번호
- model_type 은 코드명 (카비레이크 -> Kaby Lake, 스카이레이크 -> Sky Lake 로 영문으로 작성)

- 예: 코어 i7 7700 카비레이크
-> name 은 i7
-> model_num 은 7700
-> model_type 은 Kaby Lake 
INSERT INTO mytable (name, model_num, model_type) VALUES('i7', '7700', 'Kaby Lake');
INSERT INTO mytable (name, model_num, model_type) VALUES('i7', '7500', 'Kaby Lake');    
INSERT INTO mytable (name, model_num, model_type) VALUES('i7', '7700K', 'Kaby Lake');  
INSERT INTO mytable (name, model_num, model_type) VALUES('i7', 'G4600', 'Kaby Lake');  
INSERT INTO mytable (name, model_num, model_type) VALUES('i7', '7600', 'Kaby Lake');
In [ ]:
본 자료 보다 업데이트된 자료와 함께 온라인 강의로 익히면 체계적으로 이해할 수 있습니다!
퀄러티가 다른 온라인 강의로 차근차근 익혀보세요
SQL/DB(MySQL) 기본부터 파이썬/데이터 분석 활용 강의
SQL/DB(MySQL) 기본부터 파이썬/데이터 분석 활용 강의
SQL 과 데이터베이스, 그리고 파이썬으로 MySQL을 다루는 방법까지 쉽고 견고하게 정리하였습니다
자세히 알아보기 모든 강좌 보기

4.1.2 데이터 읽기(검색)

  • 테이블에 저장된 데이터를 읽는 작업
  • 데이터베이스는 대용량 데이터를 가정하므로, 대용량 데이터 중 특정한 조건에 맞는 데이터를 추출하는 검색 작업이라고 하는 편이 보다 적합함

  • 기본 문법 (SELECT)

    1. 테이블 전체 컬럼의 데이터 모두 읽기

      mysql> SELECT * FROM 테이블명;

    2. 테이블 특정 컬럼의 데이터만 읽기

      mysql> SELECT 컬럼1, 컬럼2, ... FROM 테이블명;

      mysql> SELECT name, model_num FROM mytable;
+------+-----------+
| name | model_num |
+------+-----------+
| i7   | 7700      |
| i7   | 7700K     |
+------+-----------+

    3. 테이블 특정 컬럼의 데이터를 검색하되, 표시할 컬럼명도 다르게 하기

      mysql> SELECT 컬럼1 AS 바꿀컬럼이름, 컬럼2 AS 바꿀컬럼이름 FROM 테이블명;

      예)
mysql> SELECT name AS cpu_name, model_num AS cpu_num FROM mytable;
+----------+---------+
| cpu_name | cpu_num |
+----------+---------+
| i7       | 7700    |
| i7       | 7700K   |
+----------+---------+

    4. 데이터 정렬해서 읽기

      • ORDER BY 정렬할 기준 컬럼명 DESC|ASC
      • DESC는 내림차순 ASC는 오름차순
        mysql> SELECT * FROM 테이블명 ORDER BY 정렬할기준컬럼명 DESC;

        mysql> SELECT 컬럼1, 컬럼2 FROM 테이블명 ORDER BY 정렬할기준컬럼명 ASC;

        예)
mysql> SELECT * FROM mytable ORDER BY id DESC;
+----+------+-----------+------------+
| id | name | model_num | model_type |
+----+------+-----------+------------+
|  2 | i7   | 7700K     | Kaby Lake  |
|  1 | i7   | 7700      | Kaby Lake  |
+----+------+-----------+------------+
2 rows in set (0.01 sec)

        mysql> SELECT * FROM mytable ORDER BY id ASC;
+----+------+-----------+------------+
| id | name | model_num | model_type |
+----+------+-----------+------------+
|  1 | i7   | 7700      | Kaby Lake  |
|  2 | i7   | 7700K     | Kaby Lake  |
+----+------+-----------+------------+
2 rows in set (0.00 sec)

    5. 조건에 맞는 데이터만 검색하기 (비교)

      • WHERE 조건문 으로 조건 검색
      • 예) WHERE 컬럼명 < 값
      • 예) WHERE 컬럼명 > 값
      • 예) WHERE 컬럼명 = 값
        SELECT * FROM 테이블명 WHERE 필드명 = '값'

        예)
mysql> SELECT * FROM mytable WHERE id < 2;
+----+------+-----------+------------+
| id | name | model_num | model_type |
+----+------+-----------+------------+
|  1 | i7   | 7700      | Kaby Lake  |
+----+------+-----------+------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM mytable WHERE id = 1;
+----+------+-----------+------------+
| id | name | model_num | model_type |
+----+------+-----------+------------+
|  1 | i7   | 7700      | Kaby Lake  |
+----+------+-----------+------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM mytable WHERE id > 1;
+----+------+-----------+------------+
| id | name | model_num | model_type |
+----+------+-----------+------------+
|  2 | i7   | 7700K     | Kaby Lake  |
+----+------+-----------+------------+

    6. 조건에 맞는 데이터만 검색하기 (논리 연산자)

      • WHERE 조건문 으로 조건 검색
      • 논리 연산자 활용
      • 예) WHERE 컬럼명 < 값 OR 컬럼명 > 값
      • 예) WHERE 컬럼명 > 값 AND 컬럼명 < 값
        SELECT * FROM 테이블명 WHERE (필드명='값') OR ( 필드명 ='값');

        SELECT * FROM 테이블명 WHERE (필드명='값') AND ( 필드명 ='값');

        예)
mysql> SELECT * FROM mytable WHERE id > 0 OR id < 2;
+----+------+-----------+------------+
| id | name | model_num | model_type |
+----+------+-----------+------------+
|  1 | i7   | 7700      | Kaby Lake  |
|  2 | i7   | 7700K     | Kaby Lake  |
+----+------+-----------+------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM mytable WHERE id = 1 AND name = 'i7';
+----+------+-----------+------------+
| id | name | model_num | model_type |
+----+------+-----------+------------+
|  1 | i7   | 7700      | Kaby Lake  |
+----+------+-----------+------------+
1 row in set (0.00 sec)

    7. 조건에 맞는 데이터만 검색하기 (LIKE 를 활용한 부분 일치)

      • WHERE 조건문 으로 조건 검색
      • LIKE 활용
      • 예) 홍으로 시작되는 값을 모두 찾을 경우
        SELECT * FROM 테이블명 WHERE 필드명 LIKE '홍%';
      • 예) 홍이 들어간 값을 모두 찾을 경우
        SELECT * FROM 테이블명 WHERE 필드명 LIKE '%홍%';
      • 예) 홍으로 시작되고 뒤에 2글자가 붙을 경우
        SELECT * FROM 테이블명 WHERE 필드명 LIKE '홍__';

        예)
mysql> SELECT * FROM mytable WHERE name LIKE 'i%';
+----+------+-----------+------------+
| id | name | model_num | model_type |
+----+------+-----------+------------+
|  1 | i7   | 7700      | Kaby Lake  |
|  2 | i7   | 7700K     | Kaby Lake  |
+----+------+-----------+------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM mytable WHERE name LIKE 'i__';
Empty set (0.00 sec)

    8. 결과중 일부만 데이터 가져오기 (LIMIT 을 활용)

      • LIMIT 활용
      • 예) 결과중 처음부터 10개만 가져오기
        SELECT * FROM 필드명 LIMIT 10;
      • 예) 결과중 100번째부터, 10개만 가져오기
        SELECT * FROM 필드명 LIMIT 100, 10;

        예)
mysql> SELECT * FROM mytable LIMIT 1;
+----+------+-----------+------------+
| id | name | model_num | model_type |
+----+------+-----------+------------+
|  1 | i7   | 7700      | Kaby Lake  |
+----+------+-----------+------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM mytable LIMIT 1, 1;
+----+------+-----------+------------+
| id | name | model_num | model_type |
+----+------+-----------+------------+
|  2 | i7   | 7700K     | Kaby Lake  |
+----+------+-----------+------------+
1 row in set (0.00 sec)

    9. 조건 조합

      • 위에서 나열한 조건을 조합해서 다양한 Query를 작성할 수 있음
      • 조합 순서 SELECT FROM WHERE ORDER BY LIMIT
예)
mysql> SELECT id, name FROM mytable
->  WHERE id < 4 AND name LIKE '%i%'
-> ORDER BY name DESC
-> LIMIT 2;
실습 - 데이터 검색
- 연습문제1: model_num 이 7700 으로 시작하는 로우(Row) 검색하기
- 연습문제2: name 이 i7 인 로우(Row) 검색하기
- 연습문제3: model_type 이 카비레이크 인 로우(Row) 를 1개만 검색하기(LIMIT 사용) 
SELECT * FROM mytable WHERE model_num LIKE '7700%'
SELECT * FROM mytable WHERE name LIKE '%i7%'
SELECT * FROM mytable WHERE model_type LIKE '%카바레이크%' LIMIT 1

4.1.3 데이터 수정

  • 테이블에 저장된 데이터를 수정하는 작업

  • 기본 문법 (UPDATE)

    1. 보통 WHERE 조건문과 함께 쓰여서, 특정한 조건에 맞는 데이터만 수정하는 경우가 많음

      mysql> UPDATE 테이블명 SET 수정하고 싶은 컬럼명 = '수정하고 싶은 값' WHERE 특정 컬럼 = '값';

    2. 다수의 컬럼 값을 수정할 수도 있음

      mysql> UPDATE 테이블명 SET 수정하고 싶은 컬럼명1 = '수정하고 싶은 값', 수정하고 싶은 컬럼명2 = '수정하고 싶은 값', 수정하고 싶은 컬럼명3 = '수정하고 싶은 값' WHERE 특정 컬럼 < '값';

      예)
mysql> SELECT * FROM mytable;
+----+------+-----------+------------+
| id | name | model_num | model_type |
+----+------+-----------+------------+
|  3 | i7   | 7700      | Kaby Lake  |
+----+------+-----------+------------+
mysql> UPDATE mytable SET name = 'i5', model_num = '5500' WHERE id = 3;
mysql> SELECT * FROM mytable;
+----+------+-----------+------------+
| id | name | model_num | model_type |
+----+------+-----------+------------+
|  3 | i5   | 5500      | Kaby Lake  |
+----+------+-----------+------------+

4.1.4 데이터 삭제

  • 테이블에 저장된 데이터를 삭제하는 작업

  • 기본 문법 (DELETE)

    1. 보통 WHERE 조건문과 함께 쓰여서, 특정한 조건에 맞는 데이터만 삭제하는 경우가 많음

      mysql> DELETE FROM 테이블명 WHERE 특정 컬럼 = '값';

    2. 테이블에 저장된 모든 데이터를 삭제할 수도 있음

      mysql> DELETE FROM 테이블명;

      예)
mysql> SELECT * FROM mytable;
+----+------+-----------+------------+
| id | name | model_num | model_type |
+----+------+-----------+------------+
|  3 | i5   | 5500      | Kaby Lake  |
+----+------+-----------+------------+
1 row in set (0.00 sec)
mysql> DELETE FROM mytable WHERE id = 3;
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM mytable;
Empty set (0.00 sec)
본 자료와 같이 IT 기술을 잘 정리하여, 온라인 강의로 제공하고 있습니다
체계적으로 전문가 레벨까지 익힐 수 있도록 온라인 강의 로드맵을 제공합니다
데이터 분석/과학 로드맵
데이터 분석/과학 로드맵
데이터 분석가와 데이터 과학자 직군의 기본기를 차근차근 쌓을 수 있도록 꾸몄습니다
자세히 알아보기 모든 강좌 보기
실습 - 테이블 수정, 데이터 수정, 검색
사전조건: 이미 테이블에는 웹페이지(http://www.enuri.com/list.jsp?cate=070701) 에서 1위 ~ 5위까지 데이터가 입력되어 있어야 함

1. lowest_price(컬럼명) INT UNSIGNED(데이터타입) 으로 컬럼 추가
2. 최종 다음과 같은 모습으로 mytable 구조가 되어야 함
mysql> desc mytable;
+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| id           | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name         | varchar(20)      | NO   |     | NULL    |                |
| model_num    | varchar(10)      | NO   |     | NULL    |                |
| model_type   | varchar(10)      | NO   |     | NULL    |                |
| lowest_price | int(10) unsigned | YES  |     | NULL    |                |
+--------------+------------------+------+-----+---------+----------------+
3. 웹페이지(http://www.enuri.com/list.jsp?cate=070701) 에서 1위 ~ 5위까지 lowest_price 값 수정하기
- lowest_price 값은 정품 최저 가격으로 입력하기
- 예: 222,180원 -> 222180

- 연습문제1: lowest_price 이 300000 이하인 로우(Row) 중에서 name과 model_num만 검색하기
. 이하는 같거나, 작은 값을 의미하고, 조건으로는 <= 와 같이 작성한다.

- 연습문제2: lowest_price 이 400000 이상인 로우(Row) 만 검색하기
. 이상은 같거나, 큰 값을 의미하고, 조건으로는 >= 와 같이 작성한다. 
ALTER TABLE mytable ADD COLUMN lowest_price INT UNSIGNED;

UPDATE mytable SET lowest_price = 347100 WHERE id = 1;
...

SELECT name, model_num FROM mytable WHERE lowest_price < 300000;
합리적인 분들과 확연히 다른 좋은 IT 강의를 만들 수 있는 선순환 시스템을 지향합니다
컨택: dream@fun-coding.org
Copyright 2022 © (주)노이먼스 All rights reserved.