데이터 분석: 시각화(키바나)
ix 데이터 시각호
xiii 베타리더 리뷰
DB:
MySQL: 중소형(프렌차이즈)Open source -> Oracle 합병 -> MariaDB(MySQL개발자들이 또 만든 오픈진영)
OracleDB: 대형(국가사이트, 은행사이트)
PostgreSQL: 오픈소스 Free BSD(MySQL과 유사)
Sqlite: Mobile DB(핸드폰 주소록), IOT 온도센서 등
MongoDB
NoSQL
DB, DBMS(DataBaseManagementSystem), RDBMS(Relational DBMS)관계형 데이터베이스 관리 시스템
DB: 여러 개의 표 모음
SQL문은 ;으로 끝남
Structuraled Query Language
show databases;
create database my_data_1;
use my_data_1;
exit
mariadb -u root -p:재시작
mariadb -u root -p1234
show tables;
create table test_table(
col_a integer,
col_b varchar(40)
);
desc test_table: describe
drop table test_table; : 테이블 삭제
create database book;
create table book(
bookid integer primary key,
bookname varchar(40),
publisher varchar(40),
price integer
);
create table customer(
custid integer primary key,
name varchar(40),
address varchar(50),
phone varchar(40)
);
foreign key (custid) references
customer(custid),
foreign key (bookid) references
book(bookid)
);
키 : 후보키(custid, name, phone)
기본키(행을 대표)
대체키(조합키)
select * from orders;
delete from orders;
insert into book values(1, '축구의 역사', '굿스포츠', 7000);
insert into book values(2, '축구 아는 여자', '나무수', 13000);
insert into book values(3, '축구의 이해', '대한미디어', 22000);
insert into book values(4, '골프 바이블', '대한미디어', 35000);
insert into book values(5, '피겨 교본', '굿스포츠', 8000);
insert into book values(6, '역도 단체별 기술', '굿스포츠', 6000);
insert into book values(7, '야구의 추억', '이상미디어', 20000);
insert into book values(8, '야구를 부탁해', '이상미디어', 13000);
insert into book values(9, '올림픽이야기', '삼성당', 7500);
insert into book values(10, 'Olympic Champions', 'Pearson', 13000);
*Data Manipulation Language(데이터 조작)
select; data 조회
insert; data 삽입
delete; data 삭제
update; data 일부 수정
select from [테이블 명];
select orderdate, saleprice from orders;
select distinct [] from book; :중복 제외
select from book where publisher='nnnn';
select * from book where price<='10000'
select from book where price <20000 and price >=10000;
select from book where price between 10000 and 20000; :이상 이하
select from book where publisher='굿스포츠' or publisher = '대한미디어';
select from book where publisher in ('굿스포츠', '대한미디어');
select from book where publisher not in ('굿스포츠', '대한미디어');
select from book where bookname like'%구%';
select from customer where phone is NULL ;null엔 is 사용
select from book order by price; :price순서대로 정렬
select * from book order by price DESC; :반대로 정렬
select sum(price) from book; :총합
select avg(price) from book; :평균(avg)
select count(price) from book;
sum, avg, count, max, min
select count(distinct publisher) from book;
select count(distinct publisher) as 총거래처수 from book;
select sum(saleprice) as 총판매액
-> from orders
-> where custid = 2;
+-----------+
| 총판매액 |
+-----------+
| 15000 |
+-----------+
select sum(saleprice), avg(saleprice), min(saleprice),max(saleprice) from orders
-> ;
+----------------+----------------+----------------+----------------+
| sum(saleprice) | avg(saleprice) | min(saleprice) | max(saleprice) |
+----------------+----------------+----------------+----------------+
| 118000 | 11800.0000 | 6000 | 21000 |
+----------------+----------------+----------------+----------------+
select
sum(saleprice) as 총판매액,
avg(saleprice) as 평균판매액,
min(saleprice) as 최저가,
max(saleprice) as 최고가
from orders;
+----------+------------+--------+--------+
| 총판매액 | 평균판매액 | 최저가 | 최고가 |
+----------+------------+--------+--------+
| 118000 | 11800.0000 | 6000 | 21000 |
+----------+------------+--------+--------+
select
custid as 고객번호,
sum(saleprice) as 총판매액,
avg(saleprice) as 평균판매액,
min(saleprice) as 최저가,
max(saleprice) as 최고가
from orders
group by custid;
select custid as 고객번호,
count(*) as 총수량
from orders
where saleprice>=8000
group by custid;
select custid as 고객번호,
count() as 총수량
from orders
where saleprice>=8000
group by custid
having count() >=2;
cross join
select * from customer, orders;
select * from customer, orders
where customer.custid = orders.custid;
select * from customer, book;
select name, saleprice from customer, orders
where customer.custid = orders.custid
select customer.name, orders.saleprice
from customer, orders
where customer.custid = orders.custid;
select customer.name, orders.saleprice
from customer left outer join orders
on customer.custid = orders.custid;
select name from customer
where address like '영국%' or address like '미국%'
union *
select bookname from book
where publisher = 'Pearson';
select name from customer
union
select name from customer
where custid in (1, 2, 3, 4);
select name from customer
union all :중복 포함
select name from customer
where custid in (1, 2, 3, 4);
부속질의(sub query)
select bookname from book
where bookid = (select bookid from orders
where saleprice =(select max(saleprice) from orders));
select name from customer
where custid in (select custid from orders);
select name from customer
where custid in (select custid from orders
where bookid in (select bookid from book
where publisher = '대한미디어'));
select name from customer
where address like '대한민국%' and custid not in (select custid from orders);
- select sum(saleprice) from orders
where custid in (select custid from customer
where name = '박지성'); - select count(custid) from orders
where custid in (select custid from customer
where name = '박지성'); - select count(distinct publisher) from book
where bookid in (select bookid from orders
where custid in (select custid from customer
where name = '박지성')); - select bookname, price, price-saleprice from book, orders
where book.bookid = orders.bookid
and book.bookid in (select bookid from orders
where custid in (select custid from customer
where name = '박지성'));
select name from customer
select sum(saleprice) from orders
group by custid;
select name, sum(saleprice) from customer left outer join orders
on customer.custid = orders.custid
group by orders.custid;
left outer join - on : 값이 없는것도 포함
group by : 항목마다 개별로 적용
select max(price-saleprice) from book, orders
where book.bookid = orders.bookid;
select * from book, orders
where book.bookid = orders.bookid
and price-saleprice=(select max(price-saleprice) from book, orders
where book.bookid = orders.bookid);
DML
insert
delete
select
update
insert into customer values(6, '서호준', NULL, "010-1234-1234");
insert into customer(custid, name) values(6, '서호준')
!= insert into customer(name) values('서호준')
delete from customer
where custid in (6, 7)l
update customer
set name = "장미란"
where custid = 3;
DDL(Data Definition Language)
create
create table test1(
bookid integer primary key,
bookname varchar(40) not null unique,
publisher varchar(40),
price integer
);
unique :고유의 값으로 중복 불가능
not null :빈칸 있으면 안됌
insert into book(bookid, publisher) values(1, '굿스포츠');
insert into test1 values(1, '축구의 역사', '굿스포츠', 7000);
insert into test1 values(2, '축구의 역사', '배드스포츠', 8000);
create table test2(
bookid integer primary key,
price integer check(price >=0)
);
insert into test2 value(1, 7000)
insert into test2 value(2, -7000)
create table test3(
bookid integer primary key,
price integer default 100000
);
alter table test3 add bookname varchar(20);
alter table test3 modify bookname varchar(40);
alter table test3 drop bookname ;
alter table test3 modify price integer not null;
alter table test3 modify price integer not null default 100000 ;
drop table ~;
view : 편리하게 재사용이 가능
: 보안성
: 저장 공간에 유리(계산 부담)
: 보고용
select* from customer
where address like "대한민국%";
create view view_korean_customer
as select* from customer
where address like "대한민국%";
select * from view_korean_customer;
update Customer
set name = "정미란"
where custid = 3;
update book set price = 7200 where bookit = 1;
analyze table book;
commit; :
rollback; :
mysql백업(내보내기) 및 복원(임포트)
cd C:\Program Files\MariaDB 10.7\bin
mysqldump -u root -p my_data_1 < my_data_1_back.sql
