kakao Cloud school 2기 D+30

LEE EUI JOO·2022년 12월 13일
RDBMSmysql
0

MySQL

목록 보기
1/2
post-thumbnail

1. MySQL 설치

  • 설치 완료
  • 설치 후 vm 하나 생성
    • vm name : mariadb
    • image : centos - minimal
    • 2GB / 2core / 20GB

2. Data Base

<셀리눅스 방화벽 OFF>
systemctl stop firewalld
systemctl disable firewalld

[root@mariadb ~]# sed i s/SELINUX=enforcing/SELINUX=disabled/g /etc/selinux/config

[root@mariadb ~]# wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup

[root@mariadb ~]# vi mariadb_repo_setup 
< /server_ 검색>
********

url_base="dlm.mariadb.com"
url_mariadb_repo="https://${url_base}/repo/mariadb-server"
-> mariadb_server_version=mariadb-10.3  #10.3 으로 수정
-> mariadb_server_version_real=mariadb-10.3 # 10.3 으로 수정
mariadb_maxscale_version=latest
write_to_stdout=0
skip_key_import=0
skip_maxscale=0
skip_server=0
skip_tools=0
skip_verify=0
skip_check_installed=0
skip_eol_check=0
skip_os_eol_check=0
extra_options=""
version_info=""
********

[root@mariadb ~]# chmod +x mariadb_repo_setup
실행권한(x) 부여

[root@mariadb ~]# ./mariadb_repo_setup
repo_setup 실행

[root@mariadb ~]# yum -y install mariadb-server
mariadb 설치

[root@mariadb ~]# systemctl restart mariadb
[root@mariadb ~]# systemctl enable mariadb
mariadb 재시작

[root@mariadb ~]# init 6
[root@mariadb ~]# getenforce
Disabled

  • db 내용중 한글 사용할 수 있도록 설정하기
[root@mariadb ~]# vi /etc/my.cnf.d/mysql-clients.cnf

+ [mysql]
+ [mysqldump] 밑에 추가

default-character-set = utf8 


[root@mariadb ~]# vi /etc/my.cnf.d/server.cnf 
+[mysql] 밑에 추가

character-set-server = utf8
collation-server = utf8_general_ci
init_connect = set names utf8

mariadb 다시 시작

  • mysql_secure_installation
[root@mariadb ~]# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
 SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] Y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] Y
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] n
... skipping.
By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] Y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] Y^HY
Reload privilege tables now? [Y/n] Y
... Success!
Cleaning up...
All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!
[root@mariadb ~]# mysql -u root -p1234
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 10.3.37-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)]> show variables like 'cha%'

  • my_sql 워크 벤치로 이동

  • 오류 발생
    권한을 주지 않았기 때문에 오류가 발생했음
    루트계정에도 모든 대역대에서 접근 가능해야 하고 모든 권한을 줘야한다 또한, 모든 대역대에서 로컬 호스트의 주소는 제외 되기 때문에 로컬 호스트계정도 권한을 부여해주자!

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFIED BY '1234';
Query OK, 0 rows affected (0.001 sec) # 루트 계정 권한 부여

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO root@'localhost' IDENTIFIED BY '1234';
Query OK, 0 rows affected (0.000 sec) # 호스트계정 권한 부여

<권한 업데이트>
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.001 sec)
  • 다시 my_sql 워크벤치로 돌아와 진행

  • my_sql connection 성공

  • 쿼리(query) 날려보자!
    • 한줄 쿼리 날리기 : ctrl + enter
    • 사용자 선택 범위 쿼리 날리기 : ctrl + shift + enter
  • 주석 적용
    • 범위 선택하여 : ctrl + /

  • 쿼리 정상적으로 날려짐

< shop db .txt 파일 >
*****
데이터입력(추가) + 테이블 생성 명령어

CREATE TABLE Book (
 bookid INT(2) PRIMARY KEY,
 bookname VARCHAR(40),
 publisher VARCHAR(40),
 price INT(8)
);

CREATE TABLE Customer (
 custid INT(2) PRIMARY KEY,
 name VARCHAR(40),
 address VARCHAR(50),
 phone VARCHAR(20)
);

CREATE TABLE Orders (
 orderid INT(2) PRIMARY KEY,
 custid INT(2) REFERENCES Customer(custid),
 bookid INT(2) REFERENCES Book(bookid),
 saleprice INT(8) ,
 orderdate DATE
);

-- Book, Customer, 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);
INSERT INTO Customer VALUES (1, '박지성', '영국 맨체스타', '000-5000-0001');
INSERT INTO Customer VALUES (2, '김연아', '대한민국 서울', '000-6000-0001');
INSERT INTO Customer VALUES (3, '장미란', '대한민국 강원도', '000-7000-0001');
INSERT INTO Customer VALUES (4, '추신수', '미국 클리블랜드', '000-8000-0001');
INSERT INTO Customer VALUES (5, '박세리', '대한민국 대전', NULL);

-- 주문(Orders) 테이블의 책값은 할인 판매를 가정함
INSERT INTO Orders VALUES (1, 1, 1, 6000, '2014-07-01');
INSERT INTO Orders VALUES (2, 1, 3, 21000, '2014-07-03');
INSERT INTO Orders VALUES (3, 2, 5, 8000, '2014-07-03');
INSERT INTO Orders VALUES (4, 3, 6, 6000, '2014-07-04');
INSERT INTO Orders VALUES (5, 4, 7, 20000, '2014-07-05');
INSERT INTO Orders VALUES (6, 1, 2, 12000, '2014-07-07');
INSERT INTO Orders VALUES (7, 4, 8, 13000, '2014-07-07');
INSERT INTO Orders VALUES (8, 3, 10, 12000, '2014-07-08');
INSERT INTO Orders VALUES (9, 2, 10, 7000, '2014-07-09');
INSERT INTO Orders VALUES (10, 3, 8, 13000, '2014-07-10'); 
*****

use shopdb;

  • 각각의 테이블 만들기 (Book, Orders, Customer)
  • Book table

  • Customer table

  • Orders table

  • 아직 데이터를 추가(INSERT INTO) 하지 않은 상태
    • 데이터 목록 드래그 후 ctrl + shift + enter

  • 새로운 쿼리(Query) 창 열어서 SQL 문 실행시켜 보기

3. 간단한 SQL문 연습문제

<답안>
1. SELECT bookname FROM Book WHERE bookid = 1;
2. SELECT bookname FROM Book WHERE price >= 20000;
3. SELECT sum(saleprice) AS 총구매액 FROM Orders WHERE custid = 1;
4. SELECT COUNT(*) AS 구매한도서수 FROM Orders WHERE custid = 1;

5. SELECT COUNT(*) AS 총개수 FROM Book;
6. SELECT COUNT(DISTINCT publisher) FROM Book;
7. SELECT name, address FROM Customer;
8. SELECT orderid FROM Orders WHERE orderdate > '2014-07-04'and orderdate < '2014-07-07';
9. SELECT orderid FROM Orders WHERE NOT (orderdate > '2014-07-04'and orderdate < '2014-07-07');
10. SELECT name, address FROM Customer WHERE name LIKE "김%";
11. SELECT name, address FROM Customer WHERE name LIKE "김%아";
12. SELECT name, address FROM Customer WHERE name LIKE "조%비츄";
profile
LEE EUI JOO
무럭무럭 자라볼까
다음 포스트

kakao Cloud school 2기 D+31

0개의 댓글