1. SQL DDL(Data Definition Language) 이해 및 실습
1.1 데이터베이스
- 데이터베이스 안에는 여러 개의 데이터베이스 이름이 존재한다.
- 각 데이터베이스 이름 안에는 여러 개의 테이블이 존재한다.
1. 데이터베이스 생성 CREATE DATABASE dbname; 2. 데이터베이스 목록 보기 SHOW DATABASES; 3. dbname 데이터베이스 사용 시 USE dbname; 4. dbname 데이터베이스 삭제 DROP DATABASE [IF EXISTS] dbname; IF EXISTS 옵션은 해당 데이타베이스 이름이 없더라도 오류를 발생시키지 말라는 의미
1.2 테이블
1.2.1 테이블 생성
- 기본 문법 (CREATE TABLE 구문)
CREATE TABLE 테이블명 (
컬럼명 데이터형,
컬럼명 데이터형,
.
.
기본키 셋
);
- 테이블을 생성할 데이터베이스를 먼저 사용하겠다고 명령한 후에,
CREATE DATABASE mydb; use mydb;
- 테이블 생성
CREATE TABLE 테이블명 ( 컬럼명 데이터형, 컬러명 데이터형, . . Primary Key 가 될 필드 지정 );
-
숫자 타입의 컬럼 정의 문법
CREATE TABLE mytable ( id INT [UNSIGNED] [NOT NULL] [AUTO_INCREMENT],
id : 컬럼명, 가능한 영어 소문자 중심으로 명명 INT : 컬럼에 대한 데이터 타입 선언 [UNSIGNED] : 옵션 사항 예) TINYINT 로 지정시 -128 ~ +127 TINYINT UNSIGNED 로 지정시 0 ~ 255 [NOT NULL] : NOT NULL 명시하면 데이터 입력시, 해당 컬럼 데이터에 값이 할당되지 않는 경우를 허락하지 않겠다는 의미 [AUTO_INCREMENT] : AUTO_INCREMENT 명시하면, 해당 테이블에 데이터 등록시 해당 컬럼은 자동으로 숫자가 1씩 증가하여 저장됨 해당 컬럼은 양의 정수만 등록할 수 있어야 하고, 테이블 안에서 AUTO_INCREMENT 컬럼은 하나만 지정 가능함 -
문자 타입의 컬럼 정의 문법
CREATE TABLE mytable ( name VARCHAR(50),
name : 컬럼명, 가능한 영어 소문자 중심으로 명명 VARCHAR(n) : 컬럼에 대한 문자형 데이터 타입 선언
-
시간 타입의 컬럼 정의 문법
CREATE TABLE mytable ( ts DATE,
ts : 컬럼명, 가능한 영어 소문자 중심으로 명명 DATE : 컬럼에 대한 시간 타입 선언
-
Primary Key 가 될 필드 지정 문법
CREATE TABLE mytable ( 컬럼명 데이터형, . PRIMARY KEY(컬럼명1, 컬럼명2, ...) );
컬럼명1, 컬럼명2, ... : PRIMARY KEY 로 지정할 컬럼명을 넣음 (한 개 이상을 지정할 수 있음, 보통은 한 개를 지정) PRIMARY KEY 로 지정할 컬럼은 NULL 값을 등록할 수 없어야 하고, 컬럼 안에서 같은 값이 없도록 각 값이 유일해야 함 따라서, 해당 컬럼은 보통 NOT NULL(NULL 값 방지) AUTO_INCREMENT(유일함) 선언이 되어 있는 경우가 많음예) CREATE TABLE mytable ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(50) NOT NULL, modelnumber VARCHAR(15) NOT NULL, series VARCHAR(30) NOT NULL, PRIMARY KEY(id) );
실습 1 - 테이블 생성
- 데이터베이스 만들기: 이름 customer_db
- 테이블 만들기
- 테이블 잘못만들어졌으면 DROP TABLE [IF EXISTS] dbname; 로 테이블 삭제 후 재생성
1.2.2 테이블 조회
-
기본 문법 (SHOW TABLES)
SHOW TABLES; +----------------+ | customer | +----------------+ | mytable | +----------------+ 1 row in set (0.00 sec)
-
기본 문법 (DESC 테이블명)
desc mytable; +-------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(50) | NO | | NULL | | | modelnumber | varchar(15) | NO | | NULL | | | series | varchar(30) | NO | | NULL | | +-------------+------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
1.2.3 테이블 삭제
- 기본 문법 (DROP TABLE 테이블명)
DROP TABLE [IF EXISTS] 테이블명;
IF EXISTS 옵션은 해당 테이블 이름이 없더라도 오류를 발생시키지 말라는 의미
1.2.3 테이블 구조 수정
-
테이블에 새로운 컬럼 추가
문법: ALTER TABLE [테이블명] ADD COLUMN [추가할 컬럼명][추가할 컬럼 데이터형] ALTER TABLE mytable ADD COLUMN model_type varchar(10) NOT NULL;
-
테이블 컬럼 타입 변경
문법: ALTER TABLE [테이블명] MODIFY COLUMN [변경할 컬럼명][변경할 컬럼 타입] ALTER TABLE mytable MODIFY COLUMN name varchar(20) NOT NULL;
-
테이블 컬럼 이름 변경
문법: ALTER TABLE [테이블명] CHANGE COLUMN [기존 컬럼 명][변경할 컬럼 명][변경할 컬럼 타입] ALTER TABLE mytable CHANGE COLUMN modelnumber model_num varchar(10) NOT NULL;
-
테이블 컬럼 삭제
문법: ALTER TABLE [테이블명] DROP COLUMN [삭제할 컬럼 명] ALTER TABLE mytable DROP COLUMN series;
실습 2 - 테이블 생성, 조회
CREATE DATABASE mydb; USE mydb; CREATE TABLE mytable ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(50) NOT NULL, modelnumber VARCHAR(15) NOT NULL, series VARCHAR(30) NOT NULL, PRIMARY KEY(id) ); SHOW TABLES; +----------------+ | customer | +----------------+ | mytable | +----------------+ 1 row in set (0.00 sec) desc mytable; +-------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+------------------+------+-----+---------+----------------+ | id | int unsigned | NO | PRI | NULL | auto_increment | | name | varchar(50) | NO | | NULL | | | modelnumber | varchar(15) | NO | | NULL | | | series | varchar(30) | NO | | NULL | | +-------------+------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
(1) 다음과 같이 보이도록 테이블 컬럼을 수정하시오
desc mytable; +------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------+------+-----+---------+----------------+ | id | int unsigned | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | model_num | varchar(10) | NO | | NULL | | | model_type | varchar(10) | NO | | NULL | | +------------+------------------+------+-----+---------+----------------+
(2) 위 테이블을 삭제한 후, (1)과 같은 컬럼을 가진 테이블을 생성하시오 (테이블명은 model_info 로 하시오)
2. SQL DML(Data Manipulation Language) 이해 및 실습
2.1. CRUD [Create(생성), Read(읽기), Update(갱신), Delete(삭제)]
- 데이터 관리는 결국 데이터 생성, 읽기(검색), 수정(갱신), 삭제 를 한다는 의미
2.1.1 데이터 생성
-
테이블에 컬럼에 맞추어 데이터를 넣는 작업
-
기본 문법 (INSERT)
-
테이블 전체 컬럼에 대응하는 값을 모두 넣기
INSERT INTO 테이블명 VALUES(값1, 값2, ...);
-
테이블 특정 컬럼에 대응하는 값만 넣기 (지정되지 않은 컬럼은 디폴트값 또는 NULL값이 들어감)
INSERT INTO 테이블명 (col1, col2, ...) VALUES(값1, 값2, ...);
-
예) show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mydb | | mysql | | performance_schema | | sys | +--------------------+ use mydb; show tables; +----------------+ | customer | +----------------+ | mytable | +----------------+ desc mytable; +------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------+------+-----+---------+----------------+ | id | int unsigned | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | model_num | varchar(10) | NO | | NULL | | | model_type | varchar(10) | NO | | NULL | | +------------+------------------+------+-----+---------+----------------+
INSERT INTO mytable VALUES(1, 'i7', '10700K', '코멧레이크S');
Query OK, 1 row affected (0.01 sec) SELECT * FROM mytable; +----+------+-----------+-------------+ | id | name | model_num | model_type | +----+------+-----------+-------------+ | 1 | i7 | 10700K | 코멧레이크S | +----+------+-----------+-------------+ 1 row in set (0.00 sec)
INSERT INTO mytable (name, model_num, model_type) VALUES('i7', '11500', '코멧레이크S');
Query OK, 1 row affected (0.01 sec) SELECT * FROM mytable; +----+------+-----------+-------------+ | id | name | model_num | model_type | +----+------+-----------+-------------+ | 1 | i7 | 10700K | 코멧레이크S | | 2 | i7 | 11500 | 코멧레이크S | +----+------+-----------+-------------+ 2 rows in set (0.00 sec)
실습 3 - 데이터 생성(입력), 검색
웹페이지(http://www.enuri.com/search.jsp?keyword=%EC%9D%B8%ED%85%94%20CPU) 에서 1위 ~ 5위까지 데이터 입력하기
- id는 자동 증가
- name 은 코어 이름
- model_num 은 코어 모델 번호
- model_type 은 코드명
- 예: 인텔 코어 i7-10700K 코멧레이크S
-> name 은 i7
-> model_num 은 10700K
-> model_type 은 코멧레이크S
웹페이지(http://www.enuri.com/search.jsp?keyword=%EC%9D%B8%ED%85%94%20CPU) 에서 1위 ~ 5위까지 데이터 입력하기
- id는 자동 증가
- name 은 코어 이름
- model_num 은 코어 모델 번호
- model_type 은 코드명
- 예: 인텔 코어 i7-10700K 코멧레이크S
-> name 은 i7
-> model_num 은 10700K
-> model_type 은 코멧레이크S
2.1.2 데이터 읽기(검색)
-
테이블에 저장된 데이터를 읽는 작업
-
데이터베이스는 대용량 데이터를 가정하므로, 대용량 데이터 중 특정한 조건에 맞는 데이터를 추출하는 검색 작업이라고 하는 편이 보다 적합함
-
기본 문법 (SELECT)
-
테이블 전체 컬럼의 데이터 모두 읽기
SELECT * FROM 테이블명;
-
테이블 특정 컬럼의 데이터만 읽기
SELECT 컬럼1, 컬럼2, ... FROM 테이블명;
SELECT name, model_num FROM mytable; +------+-----------+ | name | model_num | +------+-----------+ | i7 | 10700K | | i7 | 11500 | +------+-----------+
-
테이블 특정 컬럼의 데이터를 검색하되, 표시할 컬럼명도 다르게 하기
SELECT 컬럼1 AS 바꿀컬럼이름, 컬럼2 AS 바꿀컬럼이름 FROM 테이블명;
예) SELECT name AS cpu_name, model_num AS cpu_num FROM mytable; +----------+---------+ | cpu_name | cpu_num | +----------+---------+ | i7 | 10700K | | i7 | 11500 | +----------+---------+
-
데이터 정렬해서 읽기
- ORDER BY 정렬할 기준 컬럼명 DESC|ASC
- DESC는 내림차순 ASC는 오름차순SELECT * FROM 테이블명 ORDER BY 정렬할기준컬럼명 DESC;
SELECT 컬럼1, 컬럼2 FROM 테이블명 ORDER BY 정렬할기준컬럼명 ASC;
예) SELECT * FROM mytable ORDER BY id DESC; +----+------+-----------+-------------+ | id | name | model_num | model_type | +----+------+-----------+-------------+ | 2 | i7 | 10700K | 코멧레이크S | | 1 | i7 | 11500 | 코멧레이크S | +----+------+-----------+------------=+ 2 rows in set (0.01 sec)
SELECT * FROM mytable ORDER BY id ASC; +----+------+-----------+-------------+ | id | name | model_num | model_type | +----+------+-----------+-------------+ | 1 | i7 | 11500 | 코멧레이크S | | 2 | i7 | 10700K | 코멧레이크S | +----+------+-----------+-------------+ 2 rows in set (0.00 sec)
-
조건에 맞는 데이터만 검색하기 (비교)
- WHERE 조건문 으로 조건 검색
- 예) WHERE 컬럼명 < 값
- 예) WHERE 컬럼명 > 값
- 예) WHERE 컬럼명 = 값SELECT * FROM 테이블명 WHERE 필드명 = '값'
예) SELECT * FROM mytable WHERE id < 2; +----+------+-----------+-------------+ | id | name | model_num | model_type | +----+------+-----------+-------------+ | 1 | i7 | 10700K | 코멧레이크S | +----+------+-----------+-------------+ 1 row in set (0.00 sec) SELECT * FROM mytable WHERE id = 1; +----+------+-----------+-------------+ | id | name | model_num | model_type | +----+------+-----------+-------------+ | 1 | i7 | 10700K | 코멧레이크S | +----+------+-----------+-------------+ 1 row in set (0.00 sec) SELECT * FROM mytable WHERE id > 1; +----+------+-----------+-------------+ | id | name | model_num | model_type | +----+------+-----------+-------------+ | 2 | i7 | 11500 | 코멧레이크S | +----+------+-----------+-------------+
-
조건에 맞는 데이터만 검색하기 (논리 연산자)
- WHERE 조건문 으로 조건 검색
- 논리 연산자 활용
- 예) WHERE 컬럼명 < 값 OR 컬럼명 > 값
- 예) WHERE 컬럼명 > 값 AND 컬럼명 < 값SELECT * FROM 테이블명 WHERE (필드명='값') OR ( 필드명 ='값');
SELECT * FROM 테이블명 WHERE (필드명='값') AND ( 필드명 ='값');
예) SELECT * FROM mytable WHERE (id > 0) OR (id < 2); +----+------+-----------+-------------+ | id | name | model_num | model_type | +----+------+-----------+-------------+ | 1 | i7 | 10700K | 코멧레이크S | | 2 | i7 | 11500 | 코멧레이크S | +----+------+-----------+-------------+ 2 rows in set (0.00 sec) SELECT * FROM mytable WHERE (id = 1) AND (name = 'i7'); +----+------+-----------+-------------+ | id | name | model_num | model_type | +----+------+-----------+-------------+ | 1 | i7 | 10700K | 코멧레이크S | +----+------+-----------+-------------+ 1 row in set (0.00 sec)
-
조건에 맞는 데이터만 검색하기 (LIKE 를 활용한 부분 일치)
- WHERE 조건문 으로 조건 검색
- LIKE 활용
- 예) 홍으로 시작되는 값을 모두 찾을 경우SELECT * FROM 테이블명 WHERE 필드명 LIKE '홍%';
- 예) 홍이 들어간 값을 모두 찾을 경우SELECT * FROM 테이블명 WHERE 필드명 LIKE '%홍%';
- 예) 홍으로 시작되고 뒤에 2글자가 붙을 경우SELECT * FROM 테이블명 WHERE 필드명 LIKE '홍__';
예) SELECT * FROM mytable WHERE name LIKE 'i%'; +----+------+-----------+-------------+ | id | name | model_num | model_type | +----+------+-----------+-------------+ | 1 | i7 | 10700K | 코멧레이크S | | 2 | i7 | 11500 | 코멧레이크S | +----+------+-----------+-------------+ 2 rows in set (0.00 sec) SELECT * FROM mytable WHERE name LIKE 'i__'; Empty set (0.00 sec)
-
결과중 일부만 데이터 가져오기 (LIMIT 을 활용)
- LIMIT 활용
- 예) 결과중 처음부터 10개만 가져오기SELECT * FROM 필드명 LIMIT 10;
- 예) 결과중 100번째부터, 10개만 가져오기SELECT * FROM 필드명 LIMIT 100, 10;
예) SELECT * FROM mytable LIMIT 1; +----+------+-----------+-------------+ | id | name | model_num | model_type | +----+------+-----------+-------------+ | 1 | i7 | 10700K | 코멧레이크S | +----+------+-----------+-------------+ 1 row in set (0.00 sec) SELECT * FROM mytable LIMIT 1, 1; +----+------+-----------+-------------+ | id | name | model_num | model_type | +----+------+-----------+-------------+ | 2 | i7 | 11500 | 코멧레이크S | +----+------+-----------+-------------+ 1 row in set (0.00 sec)
-
조건 조합
- 위에서 나열한 조건을 조합해서 다양한 Query를 작성할 수 있음
- 조합 순서 SELECT FROM WHERE ORDER BY LIMIT
-
- 예)SELECT id, name FROM mytable WHERE id < 4 AND name LIKE '%i%' ORDER BY name DESC LIMIT 2;실습 4 - 데이터 검색
- (1): model_num 이 10400 으로 시작하는 로우(Row) 검색하기
- (2): name 이 i7 인 로우(Row) 검색하기
- (3): model_type 이 코멧레이크 인 로우(Row) 를 1개만 검색하기(LIMIT 사용)
2.1.3 데이터 수정
-
테이블에 저장된 데이터를 수정하는 작업
-
기본 문법 (UPDATE)
-
보통 WHERE 조건문과 함께 쓰여서, 특정한 조건에 맞는 데이터만 수정하는 경우가 많음
UPDATE 테이블명 SET 수정하고 싶은 컬럼명 = '수정하고 싶은 값' WHERE 특정 컬럼 = '값';
-
다수의 컬럼 값을 수정할 수도 있음
UPDATE 테이블명 SET 수정하고 싶은 컬럼명1 = '수정하고 싶은 값', 수정하고 싶은 컬럼명2 = '수정하고 싶은 값', 수정하고 싶은 컬럼명3 = '수정하고 싶은 값' WHERE 특정 컬럼 < '값';
예) SELECT * FROM mytable; +----+------+-----------+-------------+ | id | name | model_num | model_type | +----+------+-----------+-------------+ | 3 | i5 | 10400F | 코멧레이크S | +----+------+-----------+-------------+ UPDATE mytable SET name = 'i7', model_num = '10700F' WHERE id = 3; SELECT * FROM mytable; +----+------+-----------+-------------+ | id | name | model_num | model_type | +----+------+-----------+-------------+ | 3 | i7 | 10700F | 코멧레이크S | +----+------+-----------+-------------+
-
2.1.4 데이터 삭제
-
테이블에 저장된 데이터를 삭제하는 작업
-
기본 문법 (DELETE)
-
보통 WHERE 조건문과 함께 쓰여서, 특정한 조건에 맞는 데이터만 삭제하는 경우가 많음
DELETE FROM 테이블명 WHERE 특정 컬럼 = '값';
-
테이블에 저장된 모든 데이터를 삭제할 수도 있음
DELETE FROM 테이블명;
예) SELECT * FROM mytable; +----+------+-----------+-------------+ | id | name | model_num | model_type | +----+------+-----------+-------------+ | 3 | i5 | 10400F | K코멧레이크S| +----+------+-----------+-------------+ 1 row in set (0.00 sec) DELETE FROM mytable WHERE id = 3; Query OK, 1 row affected (0.01 sec) SELECT * FROM mytable; Empty set (0.00 sec)
-
실습 5 - 테이블 수정, 데이터 수정, 검색
사전조건: 이미 테이블에는 웹페이지(http://www.enuri.com/search.jsp?keyword=%EC%9D%B8%ED%85%94%20CPU) 에서 1위 ~ 5위까지 데이터가 입력되어 있어야 함
1. lowest_price(컬럼명) INT UNSIGNED(데이터타입) 으로 컬럼 추가
** 최종 다음과 같은 모습으로 mytable 구조가 되어야 함
mysql> desc mytable; +--------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+------------------+------+-----+---------+----------------+ | id | int 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 | | +--------------+------------------+------+-----+---------+----------------+
- 웹페이지(http://www.enuri.com/search.jsp?keyword=%EC%9D%B8%ED%85%94%20CPU) 에서 1위 ~ 5위까지 lowest_price 값 수정하기
- lowest_price 값은 정품 최저 가격으로 입력하기
- 예: 380,270원 -> 380270
- lowest_price 값은 정품 최저 가격으로 입력하기
- lowest_price 이 300000 이하인 로우(Row) 중에서 name과 model_num만 검색하기
- lowest_price 이 300000 이상인 로우(Row) 만 검색하기
Data Engineer