데이터 베이스 sql (feat. cmd)

데이터베이스 수업 계획

2,3 번째 시간 테이블 만들고 조회
4 번째 mysql과 파이썬 연동해서 시각화 및 데이터 추출
5 번째 nosql 몽고db 맛 보기

sql의 개념

데이터베이스- 데이터를 모아놓은 창고
테이블간 연결관계를 표현하기 위해 데이터베이스 사용
데이터베이스란 표, 집합 형태
s(structure 구조화된)q(query 질문)l(language언어)

어떤 데이터 갖고 싶냐고 질의하고 질문하는 방식
제품이 많고 데이터베이스가 쏟아져 나오지만 데이터베이스가 갖고 있는 언어는 sql임

sql에서 가장 중요한것

Creat(데이터를 만드는 일 INSERT) Read(조회 SELECT) Update(UPDATE) Delete(DELETE)

Oracle, mysql, 마리아db, mssql

마리아DB 오픈소스(무료), 기업규모나 회사가 갖는 자본력에 따라 사용하는 DB가 바뀜
Oracle이란 회사(대기업에서 많이 사용)에서 나와서 만든 것이 mysql, but 오라클이 mysql 인수함
mysql 사람들이 다시 만든 것이 마리아db
오라클이 가장 다루기 어려움.
오라클을 견제하는 회사, 마이크로 소프트 mssql 이 2개가 가장 큼.
mssql은 윈도우에서만 돌아감.
대부분의 회사는 리눅스 사용 리눅스에서는 mssql 안 돌아감.
mysql과 마리아db가 가볍고 쓰기 편함

postgre sql(구글맵의 지리정보를 사용,저장할 수 있음)

my sql 설치 방법

mysql custom 클릭 후
맨위 서버스의 + 눌러서서 서버 8.0 맨위 꺼
어플리케이션스의 workbench 맨위 꺼
shell 맨위꺼
documentation

데이터 베이스 구성방식

server - database- table

sql 사용하기

mysql -u root -p
CREATE database testdb;
show databases;
USE testdb;
CREATE USER 'testUser'@'%' IDENTIFIED BY '1111';
GRANT ALL privileges ON testdb. * TO testUser@'%';

sql 명령어

show databases; # 어떤 데이터 베이스 들이 있는지 보여줘
USE sakila; # Database changed
show tables; # 여러 테이블들을 보여줌
SELECT * FROM film limit 10; # film 테이블 안에 모든 것을 다 가져옴 단 10개만
SELECT COUNT(1) FROM film; # 갯수를 샘

1. 중요 포인트에 집중
   • 데이터 만들고 조회하고 업데이트하고 지우고 등등
2. 어떤게 '덜' 중요한지 파악
   • 데이터 베이스는 CRUD 어떤게 덜 중요한 내용인지 파악해야함.
3. '왜?' 그렇게 되는지
   • 원인을 알고 파악하기 그래야 실력이 빨리 늘어남

DOUBLE (소수)
INT (자리수가 작을 때)
BIGINT (자리수가 클 때)
VARCHAR
DATETIME

테이블 만들기

CREATE TABLE member( #member라는 테이블을 만듬
id BIGINT AUTO_INCREMENT NOT NULL PRIMARY KEY, # AUTO_INCREMENT자동 증가, insert를 하지 않아도 값이 나옴
member_id VARCHAR(10), ## 열 이름과 자료형, 컬럼들 정의
name VARCHAR(10),
address VARCHAR(10),
phone_number VARCHAR(20),
create_date datetime, # 날짜 데이터
update_date datetime
);

테이블 안에 행 추가하기

INSERT INTO member (
member_id,
name,
address,
phone_number,
create_date,
update_date
) VALUES(
'member1',
'Member',
'seoul',
'010-xxxx-xxxx',
NOW(), # 현재 시간을 넣을 때 사용하는 함수
NOW()
);

member 값 확인하기

SELECT * FROM member;

id를 안넣었는데 자동으로 만들어짐

내용 수정하기

UPDATE member SET
address='busan'
WHERE
id=2;

적지 않은 값은 null값으로 나옴

INSERT INTO member (
member_id,
create_date,
update_date
) VALUES(
'member3',
NOW(), # 현재 시간을 넣을 때 사용하는 함수
NOW()
);

특정 행 지우기

DELETE FROM member WHERE id=3;

테이블 product 만들기

CREATE TABLE product(
id BIGINT AUTO_INCREMENT NOT NULL PRIMARY KEY,
name VARCHAR(10),
qty INT(10),
price VARCHAR(20),
create_date datetime,
update_date datetime
);

INSERT INTO product (
name,
qty,
price,
create_date,
update_date
) VALUES(
'test1',
3,
'12,000',
NOW(),
NOW()
);

product 테이블 지우기

DROP TABLE product

테이블 보기

show tables;

1. 테이블 명, 컬럼 명 제외 반드시 대문자를 쓰는 것이 원칙.

   • CREATE TABLE '이름'
   • SELECT * FROM '테이블'

2. 의미 단의로 엔터쳐서 구분하기

   • 한줄로 적어도 입력은 되지만 가독성 불편함.

3. CREATE TABLE '이름'(
   '컬럼명' 데이터형태 [숫자, 글자, 날짜 등] NULL여부 [NULL,NOT NULL] UNIQUE, DEFAULT, PRIMARY KEY);

   NULL 여부란? - 이 값을 무시하고 데이터를 집어 넣어도 되냐 여부
   DEFAULT (0,"","Y",True)
   KEY
   I. PRIMARY KEY (고유키)- 어떤 로우의 대표키
   - NULL이어서는 안된다.
   - 유니크 해야 함.
   II. Foreign Key(외래키) - 다른 테이블의 참조키, 이 테이블과 다른 테이블간 관계를 나타냄
   - NULL이어도 됨
   - 대신 존재해야 함

만든 테이블 확인하기

DESC product

insert 사용하기

INSERT INTO testdb.member
( member_id, name, address, phone_number, create_date, update_date) # 컬럼 1,2,3.. 넣은 순서대로
VALUES # 값 1,2,3... 넣어주기
('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());

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()),
('watermellon', 10, 10000, NOW(), NOW());

SELECT '컬럼(열)' FROM '테이블' WHERE '조건절'

SELECT-세로로 자름, WHERE- 가로로 자름

SELECT id, name, price
FROM product
WHERE qty>=20; # sql에서는 같다 표시할때 = 하나만 입력하면 됨.

실습

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

2. 핸드폰 번호가 '010-1111-1111'인 사람의 이름만 가져온다.
   

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

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

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

sql은 큰 따움표 쓰지 않음, 작은 따움표만 사용

UPDATE

update 테이블 명 SET
컬럼1='값'
컬럼2='값'
WHERE (조건)

UPDATE product SET
qty=25
WHERE name='apple'

UPDATE product SET
qty=price/5
WHERE name='apple'

DELETE

DELETE FROM '테이블명'
WHERE (조건절)

DELETE FROM product
WHERE name='사과'

글자 찾기

'%C' C로 끝나는 글자
'C%' C로 시작하는 글자
'%C%' 글자안에 C가 있으면

1. WHERE (조건)

=, >=,<=
BETWEEN 10 to 20 (A>=10 and A<=20)
IN('carrot','apple') (당근과 사과를 찾음)
LIKE '%C' 이렇게 씀

IN('carrot','apple',pear') == IN(SELECT name FROM product)

2. 중첩 (AND OR)

A AND B -> A와 B를 모두 만족
A OR B -> A와 B중 하나라도 만족

SELECT * FROM product
WHERE qty >10 AND price>1000

실습

1. D로 시작하는 address를 가진 member 테이블의 이름을 검색한다.
   

2. 휴대폰 번호가 '1111'로 끝나는 member 테이블의 member_id, address를 가져온다.
   

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

4. Seoul에 살거나 phone_number가 '2222'이 들어가는 member의 이름을 가져온다.
   

UPDATE/ DELETE 실습

1. product 테이블에 있는 모든 row의 qty를 5개씩 늘려봅시다.
   

2. product 테이블에 있는 ORANGE의 가격을 200원 인상해봅시다.
   

3. member8이라는 member_id를 가진 member의 주소를 Busan으로 변경해봅시다.
   

4. product 테이블에서 name이 cabage인 상품을 제거합니다.
   

ORDER BY = 순서

ORDER BY [컬럼][ASC/DESC] , (a,b,c순서나 0~9 순서로 올라감)
ORDER BY NAME하면 -> apple, carrot, 순으로
ORDER BY PRICE 하면 -> apple , pear, 순으로

ASC-> 순서
DESC-> 역순

ORDER BY qty DESC 하면 -> apple, orange, 순으로

LIMIT

LIMIT 10; : 10개만 나온다. # 한개 걸면 갯수
LIMIT 3.2; : 3번부터 2개 나옴 # 2개 걸면 몇번 부터 몇개

예시

SELECT * #모든 것
FROM product # 프로덕트 테이블에서
WHERE qty>10 # qty가 10보다 큰거
ORDER BY price DESC # 가격 역순정렬
LIMIT 3; # 3개만 가져와

DISTINCT 중복제거

SELECT DISTINCT (name, qty) 둘다 겹치면 삭제
apple 10
apple 20
apple 10
pear 10 인경우 apple 10만 삭제

ORDER BY, LIMIT, DISTINCT 실습

1. member 테이블에서 address의 역순으로 모든 데이터를 조회합니다.
   

2. qty가 20이상인 product를 price의 역순으로 3개만 가져옵니다.
   

3. member 테이블에서 중복을 제거한 address를 가져옵니다.
   

4. address가 'Daegeon'인 member를 name순서대로 정렬합니다.
   

집계함수(SUM, MAX, MIN, COUNT, AVG)

1. SUM 합
   SELECT SUM(qty) FROM product;
2. AVG 평균
   SELECT AVG(qty) FROM product;
3. MAX, MIN 최대 최소 값
   SELECT MAX(qty) FROM product;
4. COUNT 갯수
   SELECT COUNT(1) FROM product; # 전체 행의 갯수
5. GROUP BY
   SELECT name, SUM(qty)
   FROM product
   GROUP BY name
   #각각 같은 애들끼리 qty가 합쳐서 나옴
   #만약 SELECT에서 name과 price를 언급했다면 group by에서도 둘다 내려 와야함

SELECT name, count(1)
FROM product
GROUP BY name
#각각 행이 몇개인지 나옴

SELECT name, MAX(qty) '많은 양'
FROM product
GROUP BY name
#각 제품 이름 별 가장 큰 값이 나옴, 열의 이름이 많은 양으로 나옴

집계합수 실습

1. product 테이블에 있는 모든 가격의 총합을 가져옵니다. 이름은 sumprice로 한다.
   

2. product 테이블에 있는 과일 갯수의 평균치를 구합니다. 이름은 avgQty로 한다.
   

3. qty에 대응되도록 price의 갯수를 가져옵니다. 이름은 qty,countprice로 한다.