2022.08.28/DB 정리/DDL

Jimin·2022년 8월 29일
MariaDBdbsql비트자바
0

비트캠프

목록 보기
31/60
post-thumbnail
  • SQL 사용법
  • DBMS 사용법
    • 사용자 등록, 데이터베이스 생성, 권한 설정

mariaDB 설치(mac)

[mac]
mariadb 설치
> brew install mariadb

mariadb 설치 후 root 암호 변경
> sudo mysql_secure_installation

mariadb 실행
> brew services start mariadb
> brew services stop mariadb
또는
> mysql.server start
> mysql.server stop

파일관리자 등장

1. 각 APP이 파일 관리

APP끼리 데이터 공유가 힘들다.

2. APP끼리 파일 공유

다른 APP의 데이터를 덮어쓸 수 있다.
접근 권한 제어가 필요하다.

3. 파일 관리자 도입

APP 개발을 할 때마다 파일 관리 APP도 개발해야한다.

  • 중복 개발
  • 네트워크 프로그래밍
  • 접근 권한 제어

사용자 등록, Database 생성, 권한 설정

0. 원격 MySQL 접속

mysql -h 서버주소 -u root -p
Enter password: 암호입력

1. MySQL 사용자 등록

CREATE USER '사용자아이디'@'원격호스트주소' IDENTIFIED BY '암호';
  • 로컬에서만 접속할 수 있는 사용자를 만들기:
create user 'study'@'localhost' IDENTIFIED BY '1111';

이 경우 study 사용자는 오직 로컬(서버를 실행하는 컴퓨터)에서만 접속 가능한다.
다른 컴퓨터에서 실행하는 MySQL 서버에 접속할 수 없다는 것을 의미한다.

  • 원격에서만 접속할 수 있는 사용자를 만들기:
CREATE USER 'study'@'%' IDENTIFIED BY '1111';

이 경우 study 사용자는 원격에서만 접속 가능하다.

2. MySQL 데이터베이스 생성

mariadb에서는 default 키워드를 사용하지 않는다.

CREATE DATABASE 데이터베이스명 CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE studydb CHARACTER SET utf8 COLLATE utf8_general_ci;

3. 사용자가 접근할 수 있는 데이터베이스 권한 부여

GRANT ALL ON 데이터베이스명.* TO '사용자아이디'@'서버주소';
GRANT ALL ON studydb.* TO 'study'@'localhost';

사용자 교체

quit or exit  -- (프로그램 종료 후)
mysql -u study -p   -- (다시 실행)

MySQL 데이터베이스 삭제

DROP DATABASE 데이터베이명;
DROP DATABASE studydb;

데이터베이스 목록 조회

show databases;

데이터베이스의 전체 테이블 목록 조회

show tables;

table, row, column

  • readme 참고

SQL

1. DDL (Data Definition Language)

2. DQL (Data Query Language)

query: 조회

3. DML (Data Manipulation Language)

Manipulation:

SQL: DDL

DB 객체(테이블, 뷰, 함수, 트리거 등)를 생성, 변경, 삭제하는 SQL 명령

  • 데이터베이스(database) = 스키마(schema)
  • 테이블(table)
  • 뷰(view)
  • 트리거(trigger=listener)
    • 특정 조건에서 자동으로 호출되는 함수
    • 특정 조건? SQL 실행 전/후 등
    • OOP 디자인 패턴에서 옵저버에 해당한다.
  • 함수(function)
  • 프로시저(procedure)
  • 인덱스(index)

1. 테이블 정의

저장할 데이터구조 정의

create table 테이블명 (
  컬럼명 타입 NULL여부 옵션,
  컬럼명 타입 NULL여부 옵션,
  .
  .
  .
  컬럼명 타입 NULL여부 옵션
  );
create table 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 사용할 데이터베이스명;
use studydb;

2. 테이블에 들어있는 데이터 조회

insert into test1 (no, name) values(1, '홍길동');

3. 테이블에 들어 있는 데이터 조회

select no, name from test1;

나열된 순서대로 데이터 추출

테이블 정보 보기

show tables; -- 전체 테이블 정보
describe 테이블명; 
desc 테이블명;

테이블 삭제하기

drop table 테이블명;

테이블 컬럼 옵션

null 허용

데이터를 입력하지 않아도 된다.

create table test1 (
    no int,
    name varchar(20)
  );

not null

데이터를 입력하지 않으면 입력/변경 거절!

create table test1(
    no int not null,
    name varchar(20)
  );

기본값 지정하기

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

create table test1(
    no int not null,
    name varchar(20) default 'noname',
    age int default 20
  );

컬럼에 default 옵션이 있는 경우,

  • 컬럼 값을 생략하면 default 옵션으로 지정한 값이 사용된다.
  • 컬럼 값을 null로 지정하면 기본 값이 사용되지 않는다.

컬럼 타입

int

  • 4바이트 크기의 정수 값 저장
  • 기타 tinyint(1바이트), smallint(2바이트), mediumint(3바이트), bigint(8바이트)

float

  • 부동소수점 저장

numeric = decimal

  • 전체 자릿수와 소수점 이하의 자릿수를 정밀하게 지정할 수 있다.
  • numeric(n,e) : 전체 n 자릿수 중에서 소수점은 e 자릿수다.
    • 예) numeric(10,2) : 12345678.12
  • numeric : numeric(10, 0) 과 같다.
create table test1(
  c1 int,
  c2 float,
  c3 numeric(6,2), /* 소수점 자릿수를 지정하면 부동소수점으로 사용 */
  c4 numeric -- decimal 과 같다
  );

char(n)

  • 최대 n개의 문자를 저장.
  • 0 <= n <= 255
  • 고정 크기를 갖는다.
  • 한 문자를 저장하더라도 n자를 저장할 크기를 사용한다.
  • 메모리 크기가 고정되어서 검색할 때 빠르다.

varchar(n)

  • 최대 n개의 문자를 저장.
  • 0 ~ 65535 바이트 크기를 갖는다.
  • n 값은 문자집합에 따라 최대 값이 다르다.
  • 한 문자에 1바이트를 사용하는 ISO-8859-n 문자집한인 경우 최대 65535 이다.
  • 그러나 UTF-8로 지정된 경우는, n은 최대 21844까지 지정할 수 있다.
  • 가변 크기를 갖는다.
  • 한 문자를 저장하면 한 문자 만큼 크기의 메모리를 차지한다.
  • 메모리 크기가 가변적이라서 데이터 위치를 찾을 때 시간이 오래 걸린다.
    그래서 검색할 때 위치를 계산해야 하기 때문에 검색 시 느리다.

DBMS 중에는 고정 크기인 컬럼의 값을 비교할 때 빈자리까지 검사하는 경우도 있다.
즉 c1='abc'에서는 데이터를 찾지 못하고, c1='abc '여야만 데이터를 찾는 경우가 있다.
그러나 mysql은 고정크기 컬럼이더라도 빈자리를 무시하고 데이터를 찾는다.

text(65535), mediumtext(약 1.6MB), longtext(약 2GB)

  • 긴 텍스트를 저장할 때 사용하는 컬럼 타입이다.
  • 오라클의 경우 long 타입과 CLOB(character large object) 타입이 있다.

date

  • 날짜 정보를 저장할 때 사용한다.
  • 년,월,일 정보를 저장한다.
  • 오라클의 경우 날짜 뿐만 아니라 시간 정보도 저장한다.

time

  • 시간 정보를 저장할 때 사용한다.
  • 시, 분, 초 정보를 저장한다.

datetime

  • 날짜와 시간 정보를 함께 저장할 때 사용한다.

boolean

  • 보통 true, false를 의미하는 값을 저장할 때는 정수 1 또는 0으로 표현한다.
  • 또는 문자로 Y 또는 N으로 표현하기도 한다.
  • 실제 컬럼을 생성할 때 tinyint(1) 로 설정한다.

artificial key (인공키)

  • Primary key로 사용하기에 적절한 컬럼을 찾을 수 없다면,
    • 예) 게시글 : 제목, 내용, 작성자, 등록일, 조회수
  • 이런 경우에 key로 사용할 컬럼을 추가한다.
  • 보통 일련번호를 저장할 정수 타입의 컬럼을 추가한다.
    • 예) 게시글 : 게시글 번호
  • 대부분의 SNS 서비스들은 일련의 번호를 primary key 사용한다.
  • 왜?
    • 회원 탈퇴의 경우,
      • 회원 탈퇴할 때 아이디도 제거한다.
      • 아이디를 지우면 그 아이디와 연결된 게시글을 지워야 한다.
      • 그런데 회원 아이디 대신 일련 번호를 사용하면,
      • 그 회원이 쓴 게시글은 일련번호와 묶인다.
      • 따라서 아이디가 삭제되더라도 해당 글은 계속 유효하게 처리할 수 있다.
    • 이메일 변경,
      • primary key 값은 다른 데이터에서 사용하기 때문에,
        • 예) 게시글을 저장할 때 회원 이메일을 저장한다고 가정하자.
      • pk 값을 변경하면 그 값을 사용한 모든 데이터에 영향을 끼친다.
      • 그래서 PK 값을 다른 데이터에서 사용한 경우,
        DBMS는 PK 값을 변경하지 못하도록 통제한다.
      • 이렇게 변경될 수 있는 값인 경우, PK로 사용하지 말라.
      • 대신 회원 번호와 같은 임의의 키(인공 키)를 만들어 사용하는 것이 좋다.

primary key

  • 테이블의 데이터를 구분할 때 사용하는 컬럼들이다.
  • 줄여서 PK라고 표시한다.
  • PK 컬럼을 지정하지 않으면 데이터가 중복될 수 있다.
create table test1(
  name varchar(20) primary key,
  kor int,
  eng int,
  math int
  );
  • 한 개 이상의 컬럼을 PK로 지정하기
create table test1(
  name varchar(20) primary key,
  age int primary key,
  kor int,
  eng int,
  math int
  ); /* 실행 오류 */
  • 두 개 이상의 컬럼을 묶어서 PK로 선언하고 싶다면
    각 컬럼에 대해서 개별적으로 PK를 지정해서는 안된다.
  • 여러 개의 컬럼을 묶어서 PK로 지정하려면 별도의 문법을 사용해야 한다.
constraint 제약조건이름 primary key (컬럼명, 컬럼명, ...)
  • 제약조건이름은 생략 가능.
  • 제약조건이름을 지정하지 않으면 이름이 자동으로 부여된다.
    ⇒ 그래서 나중에 제약조건을 찾기 힘들다.
create table test1(
  name varchar(20),
  age int,
  kor int,
  eng int,
  math int,
  constraint test1_pk primary key(name, age)
);
  • 여러 개의 컬럼을 묶어서 PK로 사용하면 데이터를 다루기가 불편하다.
    즉, 데이터를 찾을 때 마다 name과 age 값을 지정해야 한다.
  • 그래서 실무에서는 이런 경우 '학번'처럼 임의의 값을 저장하는 컬럼을 만들어 PK로 사용한다. (인공 키의 예!)
create table test1(
  no int primary key, /* 학번 */
  name varchar(20),
  age int,
  kor int,
  eng int,
  math int
  );
  • 번호는 중복되지 않았지만, name과 age값이 중복되는 경우를 막을 수 없다
insert into test1(no,name,age,kor,eng,math) values(1,'a',10,90,90,90);
insert into test1(no,name,age,kor,eng,math) values(2,'a',11,91,91,91);
insert into test1(no,name,age,kor,eng,math) values(3,'b',11,81,81,81);
insert into test1(no,name,age,kor,eng,math) values(4,'c',20,81,81,81);
 
insert into test1(no,name,age,kor,eng,math) values(5,'c',20,81,81,81);
  • 위와 같은 경우를 대비해 준비된 문법이 unique이다.
  • PK는 아니지만 PK처럼 중복되어서는 안되는 컬럼을 지정할 때 사용한다.
  • 그래서 PK를 대신해서 사용할 수 있는 key라고 해서 "대안키(alternate key)"라고 부른다.
  • 즉 대안키는 DBMS에서 unique 컬럼으로 지정한다.
create table test1(
  no int primary key,
  name varchar(20),
  age int,
  kor int,
  eng int,
  math int,
  constraint test1_uk unique (name, age)
  );
  • 다음과 같이 제약 조건을 모든 컬럼 선언 뒤에 놓을 수 있다.
create table test1(
  no int,
  name varchar(20),
  age int,
  kor int,
  eng int,
  math int,
  constraint primary key(no),
  constraint test1_uk unique (name, age)
  );
  • 다음과 같이 테이블 정의 다음에 제약 조건을 둘 수 있다.
create table test1(
  no int,
  name varchar(20),
  age int,
  kor int,
  eng int,
  math int
  );

alter table test1
    add constraint test1_pk primary key(no),
    add constraint test1_uk unique (name, age);
profile
Jimin
https://github.com/Dingadung
이전 포스트

2022.08.26/자바 정리/스레드/DBMS

다음 포스트

2022.08.30/DB 정리/DDL/DQL

0개의 댓글