001_SQL 입문[MySQL]

LBR56·2023년 2월 6일

CRUD HAVING LIMIT ORDER BY WHERE as distinct group by install mysql sql 개발자 빅데이터 집계함수 천재교육 천재교육IT교육센터 코딩

개요

발전 이유와 형태

과거 회사 정보를 수기로 작성 가능함
현재 하루에 몇 만개의 데이터가 나옴(수기 X)
데이터 베이스는 관계형 데이터 베이스(Relational DataBase Management : RDBM)이기 때문에
- 집합을 알고 있으면 도움이 됨

SQL(Structured Query Language)은 무엇인가?

구조화 질문 언어
- 데이터 베이스는 질의하는 방식
- 질의할 때, 구조가 필요함

SQL의 종류

다양한 종류의 SQL이 존재함(목표는 CRUD를 쉽게 하는 것)

Orcale

오랜 역사 & 부동에 1위
설치 방법과 질의가 어려움
- 기업용 tool 설치시 하루 이상 걸림

MySQL

Orcale에서 일부 개발자가 나와서 만든 오픈소스 디비(무료!)
- 결국 큰 형태는 Orcale과 비슷
  - 기능적으로 Orcale이 많음
- 결국 Orcale에서 인수함

MariaDB

인수 후 MySQL에서 일부 개발자가 나와서 만든 오픈소스 디비(무료!)

MS SQL == SQL Server

마이크로소프트가 개발한 SQL
- windows에서만 작동
  - 대부분 회사에서 서버 컴퓨터를 리눅스(무료)로 돌림

Postgresql

신흥 주자
다양한 데이터 셋 지원
- 속도가 약간 느림

CRUD

Create(생성)
- INSERT를 통해 데이터 생성
Read(읽기)
- SELECT를 통해 데이터 읽기
Update(수정)
- UPDATE를 통해 데이터 수정
Delete(삭제)
- DELECT를 통해 데이터 삭제

ERD(Entity Relationship Diagram)

테이블간의 관계를 설명해주는 다이어그램
쉽게 작성할 수 있는 사이트
- ERDCloud
- dbdiagram.io

MySQL 설치

web에서 설치 파일 설정

MySQL Downloads에서
Community(GPL) 설치
Windows 설치 클릭

설치 주의 사항(대부분 넘겨도 무방)

커스텀 설정
- Server, WorkBench(GUI tool), Shell, Samples and Examples 설치
인증만 밑으로 변경하고 종료
- 새로운 버전이 python에서 오류를 일으킴

설치 유무 확인

mysql 서버의 위치를 환경변수에 추가함
- C:\Program Files\MySQL\MySQL Server 8.0\bin 기본 설치 위치는 다음과 같음
cmd 창에 mysql을 쳤을 때 밑에 결과가 나와야 함
- ERROR 1045 (28000): Access denied for user 'ODBC'@'localhost' (using password: NO)

명령어 해보기

로그인하여 접속

# mysql에 root 유저를 비밀번호로 들어가겠다!
mysql -u root -p

데이터 베이스 확인, 이동, 생성 및 삭제

SHOW DATABASES;

# 데이터 베이스가 있어야 함 
USE sakila;

# CREATE DATABASE 테이블 이름;
CREATE DATABASE testdb;

# CREATE DATABASE 테이블 이름;
DROP DATABASE testdb;

테이블 확인 및 검색

SHOW TABLE;

# SELECT * FROM 테이블 이름 LIMIT 갯수;
# 전체를 가져와라, 필름에 있는, 10개만
SELECT
	* 
FROM 
	film 
LIMIT 
	10;

# 전체 갯수를 가져와라 필름에 있는
SELECT 
	COUNT(1) 
FROM 
	film;

유저 생성, 삭제, 권한부여

# 유저 생성
# % == 외부 접속 가능
# CREATE user '유저 이름'@'접속 위치' IDENTIFIED BY '비밀번호';
CREATE user 'testUser'@'%' IDENTIFIED BY '0000';

# 유저 권한 부여
GRANT ALL PRIVILEGES ON testdb.* TO testUser@'%';

# 유저 권한 부여와 유저 생성을 동시에
GRANT ALL PRIVILEGES ON testdb.* TO testUser@'%' IDENTIFIED BY '0000';

# 유저 삭제
DELETE 
FROM 
	user 
WHERE 
	user='testUser';

테이블 생성

# 테이블 이름 설정
CREATE TABLE member(
	# 큰 숫자, 자동 증가, null 허용 안함, 기본키
	id BIGINT AUTO_INCREMENT NOT NULL PRIMARY KEY,
	
	# 글자 10개
	member_id VARCHAR(10),
	name VARCHAR(10),
	address VARCHAR(10),
	phone_number VARCHAR(20),

	# 날짜 정보
	create_date DATETIME,
	update_date DATETIME
);

# 잘만들어 졌늕 ㅣ확인
DESC member

다양한 데이터 타입 제공

정수
- INT
- BIGINT
  - BIGINT 는 큰 수 == id
소수
- DOUBLE
문자
- VARCHAR
날짜
- DATETIME

데이터 CRUD

CREATE

# 생성
INSERT INTO member(
) VALUES (
	'member1', 
	'member',
	'seoul',
	'010-xxxx-xxxx',
	NOW(),
	NOW()
);

# address 없음
INSERT INTO member(
	member_id,
	name,
	phone_number,
	create_date,
	update_date
) VALUES (
	'member2', 
	'member',
	'010-xxxx-xxxx',
	NOW(),
	NOW()
);

# id만 
INSERT INTO member(
	member_id,
	create_date,
	update_date
) VALUES (
	'member3',
	NOW(),
	NOW()
);

# 복수개 생성 가능
INSERT INTO testdb.member
( member_id, name, address, phone_number, create_date, update_date)
VALUES
('member1', 'A', 'Seoul', '010-1111-1111', NOW(), NOW()),
('member2', 'B', 'Seoul', '010-1111-2222', NOW(), NOW()),
('member3', 'C', 'Daegu', '010-1111-3333', NOW(), NOW()),
('member4', 'D', 'Daegu', '010-1111-4444', NOW(), NOW()),
('member5', 'D', 'Daegeon', '010-1111-5555', NOW(), NOW()),
('member6', 'E', 'Daegeon', '010-2222-1111', NOW(), NOW()),
('member7', 'F', 'Daegeon', '010-2222-1111', NOW(), NOW()),
('member8', 'F', 'Seoul', '010-2222-2222', NOW(), NOW());

READ


# 읽기
# SELECT 컬럼 FROM 테이블 WHERE 조건;
SELECT 
	* 
FROM 
	product 
WHERE 
	qty > 10;

UPDATE

# 수정
# id가 '2'인 row의 address 속성을 부산으로 변경
# UPDATE 테이블 SET 컬럼1 = 값1, 컬럼2 = 값2,  WHERE 조건; 
UPDATE
	member 
SET 
	address = 'busan' 
WHERE 
	id = '2';
# 다양한 연산이 가능함
# 동일 테이블 타 컬럼 사용 가능
# 타 테이블 타 컬럼 사용 가능(잘 묶여 있어야 함)
UPDATE 
	product 
SET 
	qty - 5 / price 
WHERE 
	name = 'apple';

DELETE

# 삭제
# DELETE FROM 테이블 WHERE 조건;
# 위 기능을 극도로 꺼려짐(사용하지 말자!)
# is_use 또는 is_delete columns을 추가해서 삭제함
DELETE
FROM 
	member
WHERE 
	id = 3;

다양한 기능 제공

NULL, NOT NULL
UNIQUE
DEFAULT
AUTO_INCREMENT
PRIMARY KEY
- 고유키
- row를 대표함
- 비어있으면 안됨, NOT NULL
- 겹치면 안됨, UNIQUE
FOREIGN KEY
- 참조키, 외래키
- 참조 위치에 존재해야 함

PK 이면서 FK인 경우?
가능?

WHERE (조건)

=, ≥, ≤
BETWEEN A AND B
- A ≤ x AND x ≤ B
IN(a, b)
- x가 a거나 b이다.
- 또는 SELECT 문으로 잡을 수 있음)
  - 성능 하락
LIKE ‘%c%’
- % 는 어떤 글자든 상관 X
- c가 포함된 문장을 찾음(오래걸림)
  - 검색에 사용
AND, OR

ORDER BY(순서)

ORDER BY [컬럼][ASC | DESC]
- abc 순 or 오름차순

# ORDER BY [컬럼] [ASC | DESC]
SELECT 
	* 
FROM 
	product 
ORDER BY 
	price;

LIMIT

LIMIT [start][count]
- 출력 갯수 조절

SELECT 
	* 
FROM 
	product 
LIMIT 
	3;

DISTINCT

SELECT DISTINCT [칼럼] FROM [테이블]

SELECT 
DISTINCT 
	price 
FROM 
	product;

집계함수

SUM, AVG, MAX, MIN, COUNT


SELECT 
	SUM(qty), AVG(qty), MAX(qty), MIN(qty), COUNT(qty) 
FROM 
	product;

# 집계함수를 사용했다면
SELECT 
	# 이름, 합, 평균, 최대, 최소, 갯수, 가격
	name, SUM(qty) AS 'sumQty', AVG(qty), MAX(qty), MIN(qty), COUNT(qty), price
FROM 
	product
WHERE
	price > 1000
GROUP BY
	name, price # select 값을 가져옴
HAVING
	sum(qty) > 10; # 그룹 값에 조건 걸기

GROUP BY

값을 묶을 기준으로 감

HAVING

묶은 값에 조건을 넣음

AS(alias)

이름을 변경함

개발자의 약속

대소문자
- 테이블명, 컬럼명 제외 → 반드시 대문자
의미 단위로 Enter
별(*)은 피하자
큰따옴표(”) 사용 불가

과제

CREATE

product 만들기
- id(식별자, 자동 증가), 이름, 갯수, 단가, 생성 날짜, 수정 날짜

# 테이블 이름 설정
CREATE TABLE product(
	id BIGINT AUTO_INCREMENT NOT NULL PRIMARY KEY,
	
	name VARCHAR(10),
	qty INT ,
	price INT ,

	create_date DATETIME,
	update_date DATETIME
);

INSERT INTO testdb.product
(name, qty, price, create_date, update_date)
VALUES 
('carrot', 10, 1000, NOW(), NOW()),
('apple', 100, 500, NOW(), NOW()),
('pear', 30, 800, NOW(), NOW()),
('orange', 50, 800, NOW(), NOW()),
('honey', 10, 3000, NOW(), NOW()),
('cabage', 15, 3000, NOW(), NOW()),
('pine', 20, 5000, NOW(), NOW()),
('mellon', 10, 10000, NOW(), NOW());

SELECT

주소가 대구인 member 테이블의 모든 정보를 가져온다

SELECT 
	id, member_id, name, address, phone_number, create_date, update_date 
FROM 
	member 
WHERE 
	address = 'Daegu';

핸드폰 번호가 010-1111-1111 인사람의 이름만 가져옴

SELECT 
	name 
FROM 
	member 
WHERE 
	phone_number= '010-1111-1111';

주소가 대전인 사람의 member_id와 핸드폰 번호를 가져온다

SELECT 
	member_id, phone_number 
FROM 
	member 
WHERE 
	address = 'Daegeon';

product에서 carrot의 가격을 가져온다

SELECT 
	price 
FROM 
	product 
WHERE
	name = 'carrot';

product에서 3000원이 넘는 가격을 가진 상품의 모든 정보를 가져온다.

SELECT 
	id, name, qty, price, create_date, update_date 
FROM 
	product 
WHERE 
	price > 3000;

D로 시작하는 address를 가진 member 테이블의 이름 검색

SELECT
	name 
FROM 
	member 
WHERE 
	address LIKE 'D%';

휴대폰 번호가 ‘1111’로 끝나는 member 테이블의 membe_id와 address를 가져온다.

SELECT
	member_id, address
FROM
	member
WHERE
	phone_number LIKE '%1111';

qty가 20보다 크고 가격이 2000원 이상인 product 테이블의 name을 가져온다.

SELECT
	name
FROM
	product
WHERE
	qty > 20
	AND price >= 2000;

Seoul에 살거나 phone_number가 ‘2222’이 들어가는 member 이름을 가져온다

SELECT
	name
FROM
	member
WHERE
	address = 'Seoul' OR 
	phone_number LIKE '%2222%';

UPDATE & DELETE

product 테이블에 있는 모든 ROW의 양을 5개씩 늘려라

UPDATE 
	product
SET
	qty = qty + 5;

product 테이블에 있는 ORANGE의 가격을 200원 인상

UPDATE 
	product
SET
	price = price + 200
WHERE 
	name = 'orange';

member8 이라는 merber_id를 가진 member의 주소를 Busan으로 변경

UPDATE 
	member
SET
	address = 'Busan'
WHERE 
	member_id= 'member8';

product 테이블에서 name이 cabage인 상품을 제거

DELETE 
FROM 
	product 
WHERE 
	name= 'cabage';

ORDER BY & LIMIT & DISTINCT

member 테이블에서 address의 역순으로 모든 데이터 조회

SELECT 
	* 
FROM 
	member 
ORDER BY 
	address 
DESC;

qty가 20이상인 product를 price의 역순으로 3개만

SELECT 
	* 
FROM 
	product 
WHERE 
	qty >= 20 
ORDER BY 
	price 
DESC 
LIMIT 
	3;

member 테이블에서 중복을 제거한 address

SELECT 
DISTINCT 
	address 
FROM 
	member;

address가 Daegeon 인 member를 name 순서대로 정렬

SELECT 
	* 
FROM 
	member 
WHERE 
	address = 'Daegeon' 
ORDER BY 
	name;

집계함수

product 테이블에 있는 모든 가격의 총합(sumPrice)

SELECT 
	SUM(price) AS sumPrice 
FROM 
	product;

product 테이블에 있는 과일 갯수의 평균치

SELECT 
	AVG(qty) AS avgQty 
FROM 
	product;

qty에 대응되도록 price의 갯수

SELECT 
	qty, COUNT(price) AS countPrice 
FROM 
	product 
GROUP BY 
	qty;

회고

ERDcloud를 통해 타인이 작성한 다양한 ERD를 볼 수 있게 되어 좋았음
User를 생성하고 다양한 테이블에 권한을 주는 개념을 알고 있었으나, 코드로 처음 보게 되어 신기함
아직까지 PK와 FK가 동시에 존재할 수 있다는 개념이 이해가 되지 않음
다양한 과제를 하면서, 놓친 부분을 확인함
- 프로그래머스를 활용해서 추가로 공부할 부분을 찾아내야 할 것

Ref

LBR56

DA DE DS

이전 포스트

006_파이썬 프로그래밍 입문 강의[python programming]

다음 포스트

001_SQL 입문[MySQL]

개요

발전 이유와 형태

SQL(Structured Query Language)은 무엇인가?

SQL의 종류

Orcale

MySQL

MariaDB

MS SQL == SQL Server

Postgresql

CRUD

ERD(Entity Relationship Diagram)

MySQL 설치

web에서 설치 파일 설정

설치 주의 사항(대부분 넘겨도 무방)

설치 유무 확인

명령어 해보기

로그인하여 접속

데이터 베이스 확인, 이동, 생성 및 삭제

테이블 확인 및 검색

유저 생성, 삭제, 권한부여

테이블 생성

다양한 데이터 타입 제공

데이터 CRUD

CREATE

READ

UPDATE

DELETE

다양한 기능 제공

WHERE (조건)

ORDER BY(순서)

LIMIT

DISTINCT

집계함수

SUM, AVG, MAX, MIN, COUNT

GROUP BY

HAVING

AS(alias)

개발자의 약속

과제

CREATE

SELECT

UPDATE & DELETE

ORDER BY & LIMIT & DISTINCT

집계함수

회고

Ref

006_파이썬 프로그래밍 입문 강의[python programming]

002_SQL 입문[MySQL]

0개의 댓글