mariadb로 리눅스의 데이터 다루기 + 아나콘다 설치 후 파이썬과 마리아 디비 연동
> 하둡으로 리눅스 데이터 다루기(SQL) > 스파크로 리눅스 데이터 다루기(SQL) > 몽고디비로 리눅스의 데이터 다루기(NoSQL)
문제131. workbench로 마리아 디비에 접속해서 입사년도, 연도별 토탈월급을 출력하시오.
[root@localhost ~]# systemctl start mariadb #마리아db 시작시키는 코드
[root@localhost ~]# netstat -anp | grep 3306
[root@localhost ~]# mysql -u root -p
MariaDB [(none)]> use orcl;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [orcl]> select * from emp;
MariaDB [orcl]> exit;
Bye접속확인했으니 일단 빠져나오고 리눅스os에서 worckbench로 접속
oracle> select to_char(hiredate,'RRRR'), sum(sal)
from emp
group by to_char(hiredate,'RRRR');
maria > select date_format(hiredate,'%Y'), sum(sal)
from emp
group by date_format(hiredate,'%Y');ㅇMaria db의 주요 날짜 포맷
1.%Y : 연도 4자리
2.%y : 연도 2자리
3.%M : 달(영문)
4. %m : 달(숫자)
5. %W : 요일
문제132. 수요일에 입사한 사원의 이름과 입사한 요일을 출력하시오.
select date_format(hiredate,'%W'), ename
from emp
where date_format(hiredate,'%W') = 'wednesday';
# 마리아db는 대소문자구분x
문제133. 1981년도에 입사한 사원들의 이름과 입사일을 출력하시오.
select ename, hiredate
from emp
where date_format(hiredate,'%Y') = '1981';
ㅇmysql에서 실행계획 확인하는 방법
explain
select ename, hiredate
from emp
where date_format(hiredate,'%Y') = '1981';: 쿼리 앞에 explain 붙여주면 됨. 하지만 오라클처럼 자세한 실행계획을 보여주지 않음.
ㅇworkbench에서 글씨 크기를 키우는 방법?
메뉴 -> edit -> preference -> fonts 수정한 후 workbench 재부팅
oracle > select ename, hiredate
from emp
where to_char(hiredate,'RRRR') = '1981';
oracle > select ename, hiredate
from emp
where hiredate between to_date('1981/01/01', 'RRRR/MM/DD') and to_date('1981/12/31','RRRR/MM/DD') + 1;
maria > select ename, hiredate
from emp
where hiredate between str_to_date('1981/01/01','%Y/%m/%d') and str_to_date('1981/12/31','%Y/%m/%d') + 1;
문제134. 1981년도에 입사한 사원들이고 직업이 salesman인 사원들의 이름과 월급과 입사일을 출력하는데 월급이 높은 사원부터 출력하시오.
select ename, sal
from emp
where hiredate between str_to_date('1981/01/01','%Y/%m/%d')
and date_add(str_to_date('1981/12/31','%Y/%m/%d'), interval 1 day)
and job = 'salesman'
order by 2 desc;
문제135. 위의 SQL을 다시 수행하는데 직업이 salesman을 검색할 때 대소문자를 구분해서 검색되도록 하시오.
select ename, sal
from emp
where hiredate between str_to_date('1981/01/01','%Y/%m/%d')
and date_add(str_to_date('1981/12/31','%Y/%m/%d'), interval 1 day)
and binary job = 'SALESMAN'
order by 2 desc;where절 컬럼 앞에 binary를 붙여주면 대소문자를 구분해서 쓰게 됨.
| Oracle | mySQL |
| nvl | ifnull |
| sysdate | sysdate() |
| months_between | period_add |
| decode | if |
| rollup | with rollup |
| listagg | group_concat |
| to_char | date_format |
| to_date | str_to_date |
문제136. 이름과 커미션을 출력하는데 커미션이 null이면 0으로 출력하시오.
oracle > select ename, nvl(comm,0)
from emp;
maria > select ename, ifnull(comm,0)
from emp;문제137. 부서번호, 부서번호별 토탈월급을 출력하는데 맨 아래에 전체 토탈월급을 출력하시오.
oracle > select deptno, sum(sal)
from emp
group by rollup(deptno);
maria > select deptno, sum(sal)
from emp
group by deptno with rollup;
문제138. 부서번호, 부서번호별로 속한 사원들의 이름을 가로로 출력하시오.
oracle > select deptno, listagg(ename,',') within group (order by ename asc)
from emp
group by deptno;
maria > select deptno, group_concat(ename order by ename separator ',')
from emp
group by deptno;
ㅇmariadb 테이블에 리눅스 csv파일 로드하기
예제1. 마리아 db에서 내가 가지고 있는 테이블의 리스트를 확인하시오.
oracle > select table_name from user_tables;
maria > show tables;
예제2. 마리아 db에서 emp2 테이블 생성하기
oracle > create table emp2
(empno number(10),
ename varchar2(20),
job varchar2(10),
mgr number(10),
hiredate date,
sal number(10),
comm number(10),
deptno number(10) );
maria > create table emp2
(empno int(4),
ename varchar(10),
job varchar(10),
mgr int(4),
hiredate date,
sal int(7),
comm int(7),
deptno int(4) );
예제3. /root 밑에 emp.csv를 올립니다.
[root@localhost ~]# cd /media
[root@localhost media]# cd sf_share
[root@localhost sf_share]# ls -l emp.csv
-rwxrwx---. 1 root vboxsf 679 3월 15 11:22 emp.csv
[root@localhost sf_share]# cp emp.csv /root/emp.csv
예제4. putty에서 /root밑으로 가서 emp.csv를 vi로 여세요.
[root@localhost sf_share]# cd
[root@localhost ~]# vi emp.csv
예제5. 맨 위에 있는 컬럼명 행을 지우고 저장하고 나옵니다.
# dd
esc
# :wq!
예제6. emp.csv를 vi편집기로 열어서 null값에 해당하는 부분을 모두 \N으로 변경합니다.
:%s/,,/,\\N,/g
esc
:wq!
예제7. 변경한 /root 밑에 있는 emp.csv를 마리아 db에 emp2 테이블에 입력하시오.
load data local infile '/root/emp.csv'
replace
into table orcl.emp2
fields terminated by ','
enclosed by '"'
lines terminated by '\n';load data local infile '/root/emp.csv' #로드할 csv 파일명
replace # 기존에 테이블에 데이터가 있다면 지금 로드할 데이터로 대체하겠음
into table orcl.emp2 # orcl 데이터베이스의 emp2테이블에 입력
fields terminated by ',' # 값과 값은 콤마로 구분되어 있음
enclosed by '"' # 데이터 중에 혹시 더블 쿼테이션 마크로 둘러져있는 데이터가 있다면 그것도 입력해라
lines terminated by '\n' #행과 행은 엔터로 구분되어져 있음
ignore 1 lines #첫번째 행은 무시하는 코드 - 첫번째행 지웠다면 이 코드는 안써줘도 됨
(empno, ename, job, mgr, hiredate, sal, comm, deptno);
[root@localhost ~]# mysql -h 192.168.122.1 -u scott -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 19
Server version: 10.4.24-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use orcl;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [orcl]> load data local infile '/root/emp.csv'
-> replace
-> into table orcl.emp2
-> fields terminated by ','
-> enclosed by '"'
-> lines terminated by '\n'
->
-> ;
Query OK, 14 rows affected, 14 warnings (0.013 sec)
Records: 14 Deleted: 0 Skipped: 0 Warnings: 14
MariaDB [orcl]> select * from emp2;
문제139. dept.csv를 리눅스 서버의 /root밑에 올리시오.
[root@localhost ~]# cd /media
[root@localhost media]# ls
sf_share
[root@localhost media]# cd sf_share
[root@localhost sf_share]# ls
data8.tgz dept2.txt emp.txt mushrooms.csv wisc_bc_data.csv
dept.csv emp.csv jobs.txt reviewData2.csv
[root@localhost sf_share]# cp dept.csv /root/dept.csv
[root@localhost sf_share]# cd
[root@localhost ~]# ls -l dept.csv
-rwxr-x---. 1 root root 102 3월 15 12:03 dept.csv
문제140. 아래의 오라클 문법을 보고 maria db에서 dept2테이블을 생성하시오.
oracle > create table dept2
(deptno number(10),
dname varchar2(10),
loc varchar2(10) );
maria > create table dept2
(deptno int(2),
dname varchar(20),
loc varchar(20) );
문제141. putty에서 /root밑에 있는 dept.csv를 열어서 맨 위의 컬럼행을 지우시오.
[root@localhost ~]# vi dept.csvdd -> :wq!
문제142. (점심시간 문제) /root 밑에 있는 dept.csv의 데이터를 마리아 디비에 dept2테이블에 입력하시오.
입력한 이후 select * from dept; 결과를 캡쳐해서 올립니다.
load data local infile '/root/dept.csv'
replace
into table orcl.dept2
fields terminated by ','
enclosed by '"'
lines terminated by '\r\n';
문제143. emp2테이블과 dept2테이블을 서로 조인해서 dallas에서 근무하는 사원들의 이름과 부서위치를 출력하시오.
select e.ename, d.loc
-> from emp2 e inner join dept2 d
-> on (e.deptno = d.deptno)
-> where d.loc = 'dallas';
문제144. 네이버 영화 리뷰 데이터를 마리아 디비에 넣을 수 있도록 테이블을 생성하고 데이터를 로드하시오.
[root@localhost ~]# ls -l *.csv
-rwxr-x---. 1 root root 84 3월 15 12:09 dept.csv
-rw-r--r--. 1 root root 219 3월 10 16:29 deptno_10.csv
-rw-r--r--. 1 root root 364 3월 10 16:30 deptno_20.csv
-rw-r--r--. 1 root root 453 3월 10 16:30 deptno_30.csv
-rwxr-x---. 1 root root 655 3월 15 11:32 emp.csv
-rw-r--r--. 1 root root 432416 3월 11 11:54 moga.csv
-rw-r--r--. 1 root root 432416 3월 11 16:47 moga2.csv
-rwxr-xr-x. 1 root root 1229702 2월 3 16:21 mushrooms.csv
-rwxr-x---. 1 root root 1373817 3월 11 11:21 reviewdata2.csv
-rwxr-x---. 1 root root 1373593 3월 11 14:00 reviewdata3.csv
-rwxrwxrwx. 1 root root 856 1월 24 18:32 skin.csv
-rwxr-xr-x. 1 root root 125093 1월 27 15:06 wisc_bc_data.csv
[root@localhost ~]# head reviewdata2.csvreviewdata2.csv가 /root에 있는 거 확인
마리아db접속 후 테이블 생성 및 임포트 하기
MariaDB [orcl]> create table naver2
-> (cname varchar(100),
-> score int(2),
-> review varchar(1000) );load data local infile '/root/reviewdata2.csv'
replace
into table orcl.naver2
fields terminated by ','
enclosed by '"'
lines terminated by '\r\n'
(cname,score,review);
select * from naver2 limit 5;
ㅇ마리아db 한글 깨지는거 수정방법
#방법1
MariaDB [orcl]> select * from naver2 limit 5; # 위의 5개의 행만 출력하시오 !
마리아 디비에서 한글이 깨져서 나옵니다. 이를 해결하세요
MariaDB [orcl]> truncate table naver2;
##데이터베이스 character set 확인
MariaDB [orcl]> show create database orcl;
##데이터베이스의 character set 변경
MariaDB [orcl]> alter database orcl default character set = utf8;
##테이블 character set 확인
MariaDB [orcl]> show create table naver2;
##테이블의 character set 변경
MariaDB [orcl]> alter table naver2 default character set = utf8;
MariaDB [orcl]> alter table naver2 modify review varchar(4000) character set utf8 collate utf8_general_ci;
MariaDB [orcl]> alter table naver2 modify cname varchar(100) character set utf8 collate utf8_general_ci;
##테이블 character set 확인
MariaDB [orcl]> show create table naver2;
# 데이터를 다시 로드 합니다.
MariaDB [orcl]> load data local infile '/root/reviewdata2.csv'
replace
into table orcl.naver2
fields terminated by ','
enclosed by '"'
lines terminated by '\r\n'
(cname, score, review);
MariaDB [orcl]> select * from naver2 limit 5;#방법2
MariaDB [orcl]> create table naver_test
(cname varchar(100),
score int(2),
review varchar(4000) )
ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;
# 데이터를 다시 로드 합니다.
MariaDB [orcl]> load data local infile '/root/reviewdata2.csv'
replace
into table orcl. naver_test
fields terminated by ','
enclosed by '"'
lines terminated by '\r\n'
(cname, score, review);
MariaDB [orcl]> select * from naver_test limit 5;
문제145. naver2 테이블에서 영화이름을 출력하는데 중복제거해서 출력하시오.
MariaDB [orcl]> select discint cname from naver2;
문제146. 위에서 출력되는 행수가 전체 몇건인지 출력하시오.
MariaDB [orcl]> select count(distinct cname) from naver2;
+-----------------------+
| count(distinct cname) |
+-----------------------+
| 1746 |
+-----------------------+
문제147. 영화 모가디슈의 평점(score), 평점(score)별 건수를 출력하시오.
MariaDB [orcl]> select score, count(score)
-> from naver2
-> where cname = '모가디슈'
-> group by score;
+-------+--------------+
| score | count(score) |
+-------+--------------+
| 1 | 704 |
| 2 | 244 |
| 3 | 8 |
| 4 | 95 |
| 5 | 22 |
| 6 | 100 |
| 7 | 73 |
| 8 | 234 |
| 9 | 215 |
| 10 | 1873 |
+-------+--------------+
10 rows in set (0.005 sec)
문제148. 위의 결과를 다시 출력하는데 건수가 높은것부터 출력하시오.
select score, count(score)
from naver2
where cname = '모가디슈'
group by score
order by 2 desc;
ㅇcent OS 리눅스에서 한영키 설정하는 방법
문제149. 위의 결과를 다시 출력하는데 출력되는 결과중에 위의 3건만 출력하시오.
select score, count(score)
from naver2
where cname = '모가디슈'
group by score
order by 2 desc
limit 3;
문제150. 리뷰에 구교환이라는 단어가 포함되어져 있는 행을 모두 출력하시오.
select *
from naver2
where review like '%구교환%';
문제151. 리뷰에 최고라는 단어가 포함된 영화명을 중복제거해서 출력하시오.
select distinct cname
from naver2
where review like '%최고%';
문제152. 리뷰글에 최고라는 단어가 포함된 영화명과 영화명 건수를 출력하시오.
select distinct cname, count(cname)
from naver2
where review like '%최고%'
group by cname
order by 2 desc;
문제153. 평점 1점이 가장 많은 영화제목은 무엇인지 출력하시오.
select distinct cname, count(cname)
from naver2
where score = 1
group by cname
order by 2 desc
limit 1;
ㅇ내 자리에서 서버실에 있는 workbench 프로그램 실행시키는 방법
1. 모바텀(mobaterm) 프로그램을 설치합니다.
https://mobaxterm.mobatek.net/download-home-edition.html
SSH로 리눅스 서버IP입력해서 접속하면 됨.
서버 IP : 192.168.122.1
유저네임 :
포트번호 :
2. mobaxterm --> settings --> configuration --> x11 --> x11 remote access --> full
3. 도스창을 열고 자신의 아이피 주소를 확인합니다.
172.30.1.38
4. 모바텀 터미널창에서 다음과 같이 수행합니다.
export DISPLAY=172.30.1.38:0.0
mysql-workbench
뜨기는 하는데 접속은 안됨. 내일 아나콘다 설치하고 다시 접속하겠습니다.
문제154. (오늘의 마지막 문제) mariadb에서 사원이름, 월급, 월급에 대한 순위를 출력하는데 순위 1등만 출력하시오.
'Study > class note' 카테고리의 다른 글
| 리눅스 / 아나콘다 설치하기 (0) | 2022.03.16 |
|---|---|
| 리눅스 / 원격제어를 이용해서 리눅스 서버에 접속하는 방법 (0) | 2022.03.16 |
| 리눅스 / maria db (0) | 2022.03.14 |
| 리눅스 / 리눅스 vi편집기 명령어1 (0) | 2022.03.14 |
| 리눅스 / 리눅스 명령어3 (0) | 2022.03.14 |
