2022-02-18(금) 14주차 5일

Jeongyun Heo·2022년 2월 18일
DDLDMLDQLsql
0

ec2 mariadb install 검색

ec2 yum install mariadb 검색

https://trend21c.tistory.com/2163

ctl = ctrl

sudo ← 관리자 권한

[Y/n]
Y가 대문자로 되어 있음
암묵적인 관례, 약속
입력 안 하고 엔터를 치면 제시된 값 중에서 대문자로 된 글자가 입력된 걸로 간주된다.

/git/eomcs-docs/dbms/mariadb-settings.txt

🔹 MySQL 사용자 추가
CREATE USER '사용자아이디'@'원격호스트주소' IDENTIFIED BY '암호';
CREATE USER 'study'@'localhost' IDENTIFIED BY '암호';

localhost 라고 했기 때문에 study 라는 사용자는 오직 로컬에서만 접속 가능
원격에서는 불가능

데이터베이스를 설치한 다음에 제일 먼저 해야 될 건
개발 테스트 하는 동안 사용할 아이디를 준비한다

하나의 DB를 여러 프로그램이 공유해서 사용함
아이디를 한 개 쓰지 않고 인사팀에서 사용할 아이디를 따로 만들고 재무팀에서 사용할 아이디를 따로 만들고 물류팀에서 사용할 아이디를 따로 만든다.
아이디마다 각각 사용권한을 제어·통제한다.

🔹 MySQL 사용자 목록 조회
SELECT 컬럼명1, 컬럼명2, ... FROM 데이터베이스명.테이블명;
SELECT user, host FROM mysql.user;

🔹 MySQL 사용자 삭제
DROP USER 'study'@'localhost';

study 사용자가 사용할 데이터베이스를 생성하자

🔹 MySQL 데이터베이스 생성
CREATE DATABASE 데이터베이스명 CHARACTER SET utf8 COLLATE utf8_general_ci;

데이터베이스에 utf8로 집어 넣겠다

🔹 데이터베이스 목록 조회
show databases;

🔹 MySQL 데이터베이스 삭제
DROP DATABASE 데이터베이스명;

이 데이터베이스는 오직 누가 쓸 수 있다고 설정을 해야 됨
사용 권한 설정

🔹 MySQL 사용자에게 데이터베이스 사용 권한 부여
GRANT ALL ON 데이터베이스명.* TO '사용자아이디'@'서버주소';

localhost로 접속한 study 사용자에게 studydb 데이터베이스 모든 권한을 부여하겠다

이번에는 study 사용자로 접속

mysql -u study -p

study 사용자가 사용할 수 있는 데이터베이스 목록을 확인해보자
show databases;

ServerApp에 직접 요청 못 함
ClientApp으로 요청해야 됨
ClientApp이 서버에 전달

mysqld.exe (서버)
mysql.exe (클라이언트 프로그램)

지금은 로컬에서 해서 그렇지
로컬이 아니라 다른 사람 컴퓨터 서버에 요청을 보낼 수도 있음
mysql -h 아이피주소 -u 아이디 -p

리눅스 서버에 접속해보자

포트 번호 3306 열어줘야 됨

원래는 DBMS 방화벽 안 연다
DBMS를 외부에서 직접 접근 못 하게 함
보안때문에 항상 리눅스 서버에 가서 거기서 DBMS에 접근하도록 한다
원격 서버에 먼저 접속해서
리눅스 서버에서 DBMS에 접속해야 됨

create user 'study'@'localhost' identified by '암호';

create database studydb character set utf8 collate utf8_general_ci;

권한 설정 해주기
GRANT ALL ON studydb.* TO 'study'@'localhost';
로컬에서 접속한 study 사용자에 대해 studydb 안에 있는 모든 항목에 대해서 모든 권한을 부여하겠다

mysql에서 나오기

이번에는 study 사용자로 들어가기
mysql -u study -p

show databases;

원격에서 접속하는 따로 등록해줘야 됨

내 컴퓨터 터미널에서 원격에 있는 리눅스 서버에 접속해보기

mysql -h 아이피주소 -u study -p
ERROR 1130 (HY0000) 에러 뜸
Host is not allowes to connect to this MariaDB server

아까 study 사용자 등록할 때 localhost에서 접속하는 경우에만 허용한 상태
그래서 원격에서 접속이 안 됨

서버에 등록한 사용자가 study는 맞지만
오직 로컬에서 접속하는 study 이다.
원격에서 접속하는 study는 따로 추가해야 한다.

루트 사용자로 접속하기
mysql -u root -p

study 사용자를 하나 더 추가할 거임
루트 사용자만이 다른 사용자를 등록할 수 있다

create user 'study'@'%' identified by '암호';
% ← 원격에 있는 어떤 IP Adderess라도 가능하다는 거
실무에서는 % 안 적음
그 IP를 가진 컴퓨터인 경우에만 접속 가능하도록 제한을 걸어 놓는다

study 라는 사용자가 이미 있는데요?
그 사용자는 로컬에서 접속하는 사용자고
얘는 원격에서 접속하는 사용자

같은 이름으로 만들 수 있다

아까 등록한 study는 로컬에서 접속하는 study
지금 등록하는 study는 원격에서 접속하는 study

IP Address에 따라서 같은 이름을 여러 개 만들 수 있다.

원격에서 접속하는 study 사용자에도 studydb의 사용 권한을 줘야 됨

GRANT ALL ON studydb.* TO 'study'@'%';
원격에서 접속하는 study 사용자에게 studydb를 다룰 수 있는 모든 권한을 주겠다

원격에 다시 접속해보기

접속됨

-h 옵션이 없네? localhost에 접속하라는 건가 보다

호스트 주소를 안 주면 기본이 로컬

mysql client documentation 검색

--host=host_name
-h host_name

--port=port_num
-P port_num

mysql -h 3.33.222.222 -P 3336 -u study -p

포트 번호 별도로 지정할 수 있다

포트 번호 생략하면 기본이 3306

호스트 주소를 안 주면 기본이 로컬

루트 사용자로 로그인 해서 사용자 생성하고 데이터베이스 만들고 권한 주는 연습하기

연습 할 때는 로컬에서 연습하겠음

MariaDB의 클라이언트와 서버

09-JDBC프로그래밍 / 3 페이지

옛날에는 파일에 데이터를 저장

예전에는 App. → 파일에 데이터를 저장
파일에 저장
우리만의 데이터베이스를 만듦

서버 개발자가 제공해주는 ScoreTableProxy 클래스를 사용하면 간단하게 서버에 접속해서 원하는 작업을 할 수 있더라

DBMS도 똑같다
DBMS에서 자바 개발자를 위해서 라이브러리(JDBC API)를 제공
JDBC API 라이브러리에 있는 메서드를 호출하면
직접 DBMS 서버에 접속할 필요가 없다
대신 접속해서 요청을 하고 응답 결과를 받아서 리턴해준다

DBMS와 명령문

09-JDBC프로그래밍 / 4 페이지

Oracle Client -------명령문------> Oracle DBMS
MS-SQL Client -------명령문------> MS-SQL DBMS
MySQL Client -------명령문------> MySQL DBMS

✓ DBMS마다 명령 문법이 다르다면 사용하기 매우 불편할 것이다.
⇒ 그래서 표준 명령 문법을 제정
⇒ SQL (Structured Query Language)
    구조적인 질의를 하는 언어

SQL

09-JDBC프로그래밍 / 5 페이지

🔹 SQL (Structured Query Language)
DBMS에 상관없이 명령문을 작성할 때 사용하는 표준 문법

하나의 SQL문을 가지고 요청할 수 있다.

      -------요청------> Oracle
SQL문 -------요청------> MS-SQL
      -------요청------> MySQL

SQL 현실

09-JDBC프로그래밍 / 6 페이지

DBMS마다 고유의 문법이 존재한다.

Oracle : 표준 SQL + α 문법
MS-SQL : 표준 SQL + β 문법
MariaDB : 표준 SQL + γ 문법

⇒ DBMS에 따라 표준 SQL 문법의 지원 범위가 다르다.

MS-SQL에서 Oracle 문법을 사용하려면 표준 문법은 그대로 가져오고 ⍺ 부분을 β로 변경해야 한다.

데이터베이스 특정 문법을 MariaDB에 맞춰서 변경해야 한다.

Oracle에서는 되는 표준 문법이 Oracle에서는 안 되는 경우가 있다.

MariaDB에서 지원하지 않는 표준 문법은 따로 MariaDB에 맞게끔 변경해야 한다.

SQL이 표준이라고 하는데

왜 DBMS마다 전용 문법이 있는가?
DBMS마다 특별히 제공하는 고유 기능이 있다. (경쟁력)
그 기능을 제어하는 문법이다.

엑셀에서도 SQL문을 쓸 수 있다... 표준 SQL의 절반 정도...

SQL 문법

09-JDBC프로그래밍 / 7 페이지

SQL 명령의 종류

SQL 명령은 크게 다음과 같이 나눌 수 있다.

MariaDB에서는 DQL를 합쳐서 DML이라고 부른다.
Oracle은 DML과 DQL을 달리 한다.

🔹 DDL (Data Definition Language)
데이터를 클래스처럼 정의하는 Language

테이블 생성/변경/삭제
뷰 생성/변경/삭제
함수 생성/변경/삭제

CREATE table ...
ALTER table ...
DROP table ...

🔹 DML (Data Manipulation Language)
데이터를 다루는 Language

데이터를 입력/변경/삭제

INSERT ...
UPDATE ...
DELETE ...

🔹 DQL (Data Query Language)
데이터를 질의하는 Language

데이터 조회

SELECT ...

/Users/nana/git/eomcs-docs/sql/Exam01.sql

  • 트리거(trigger=listener)
    • 특정 조건에서 자동으로 호출되는 함수
    • 특정 조건? SQL 실행 전/후 등
    • OOP 디자인 패턴에서 옵저버에 해당한다.

varchar = variable character
최소 0자 ~ 최대 50자

not null = 이름을 반드시 입력하라 (필수 입력)

create table studydb.test01 (
    name varchar(50) not null,
    kor int not null,
    eng int not null,
    math int not null,
    sum int not null,
    aver float not null
  );

use studydb;

show tables;

drop table test01;

sheet가 테이블

field = column

row = entity = tuple

row = record = entity = object
column = field = attribute

테이블 생성, 조회, 변경, 삭제

먼저 테이블을 만드는 방법을 배워보다

int는 기본이 11자리

/Users/nana/git/eomcs-docs/sql/Exam01.sql

안 적으면 null 허용

// 안 적으면 null 허용
create table test1 (
    no int,
    name varchar(20)
  );

select * from test1;

insert into test1(no, name) values(null, 'bbb');

필수 입력값으로 만들고 싶습니다.
not null 

> create table test1(
    no int not null,
    name varchar(20) default 'noname',
    age int default 20
  );
MariaDB [studydb]> insert into test1(no, name) values(1, 'aaa');

MariaDB [studydb]> insert into test1(no, name) values(null, 'bbb'); /* 실행 오류 */

MariaDB [studydb]> insert into test1(no, name) values(null, 'bbb');
ERROR 1048 (23000): Column 'no' cannot be null

데이터 무결성(data integrity)
흠이 없는 상태
완전한 상태

필수 입력 항목이 비어 있으면 결함이 발생한 거
절대 null이 될 수 없다고 정의
DBMS의 역할 : 결함 방지

입력 값을 생략하면 해당 컬럼에 지정된 기본값이 대신 입력된다.

MariaDB [studydb]> insert into test1(name, age) values('aaa', 30);
ERROR 1364 (HY000): Field 'no' doesn't have a default value

tinyint(1바이트), smallint(2바이트), mediumint(3바이트), bigint(8바이트)

https://mariadb.com/kb/en/data-types/

DBMS마다 약간씩 차이가 있어서 문서 봐야 됨

ZEROFILL

SQL문에도 주석이 있다.

numeric = decimal

> insert into test1(c1) values(3.14); /* 소수점 이하 반올림하고 짜름 */

c3 numeric(6,2)
전체 6자리 중에서 소수점 이하 2자리니까 정수 4자리

MariaDB [studydb]> insert into test1(c4) values(12345678.90);

반올림 돼서 12345679 저장됨

MariaDB [studydb]> insert into test1(c1) values(2111111111);
Query OK, 1 row affected (0.001 sec)

MariaDB [studydb]> insert into test1(c1) values(21111111111);
ERROR 1264 (22003): Out of range value for column 'c1' at row 1

numeric은 자릿수만 10자리면 OK

MariaDB [studydb]> insert into test1(c4) values(9999999999);
Query OK, 1 row affected (0.013 sec)
profile
Jeongyun Heo
이전 포스트

2022-02-17(목) 14주차 4일

다음 포스트

2022-02-21(월)

0개의 댓글