[SQL] DBMS 구조와 테이블 생성

1. 개념

2. 데이터 베이스

3. DATABASE 생성 및 조회

CREATE USER 계정명 @HOST IDENTIFIED BY ‘password'​

client p/g(웹 브라우저 등)<====> server p/g(DB 등)

예 : 웹브라우저client  ->버튼눌러서 정보 요청-> 쿠팡server  
: server에 data요청
my sql - DB server
서비스 요청 : client program

My Sql workbench 

password => save : 로그인 정보 저장

오른쪽 위 토글버튼 : 사이드메뉴(왼,아래,오)를 넣었다 뺐다 뾱뾱
설정바꾸기: edit > preferences > Fonts&colors

개념

데이터베이스

Database Management System (DBMS)

: 데이터베이스 관리 시스템

• 데이터 베이스(지속적으로 유지 , 관리되어야 하는 데이터들의 집합)를 DBMS가 관리
• 데이터 -> 변수 -> FIle=>DB에 저장
• MySQL, Oracle, MS MS-SQL, DB2 등.

관계형 데이터 베이스 (RDB)

행과 열로 이루어진 2차원 표 형식으로 Data를 관리하는 데이터베이스
▪ 데이터를 관계 있는 여러 항목 (열)의 집합으로 표현 -> 이 데이터 집합을 관계라고 함

속성값들 관계 -> 하나의 데이터 
데이터들 간의 관계 -> 테이블 
데이블들 간의 관계 -> 데이터 베이스

▪ 업무적 연관성 있는 테이블간의 관계를 통해 데이터를 관리하는 방식
예)
고객 - 홍길동 abc ... -> 다양한 정보들을 '표'형태로 관리: 행과 열데이터
고객 데이터안의 여러개의 속성값들이 서로 연결됨

• 행 - 1개의 데이터
• 열 - 데이터를 구성하는 속성을 표현(속성의 이름)
  => 행기반 조회 - 데이터 조회, 열기반 조회 - 속성값들을 조회

+ 데이터의 종류

• 정형 데이터 : 형태가 정해진 데이터, 어떤 데이터를 모을지 정해져 있음 ex. 이름, 나이, 지역 속성을 모은다(SQL : 표)
• 비정형 데이터 - 모을 대상이 정해지지 않은 데이터.(이미지, 자연어 ,,,), 딥러닝은 비정형 데이터를 분석하는데 사용
• 데이터의 규모가 커진다 > 여러 종류의 데이터가 필요 -> DB에 저장 -> DBMS가 관리
  : 쇼핑몰 데이터 - 상품데이터, 주문데이터, 고객데이터, 물품관리 데이터, 거래처 데이터
• 데이터 테이블들 간에는 관계성이 생긴다(상품데이터 > 고객데이터를 불러옴)

테이블

• entity : 독립적, 분리되어 관리하길 원하는 데이터, 데이터 하나하나의 항목 예:주문데이터 안에서 고객정보를 관리할 수 있지만, 주문데이터와 고객데이터 분리
• table : entity를 물리적(행렬 2차원 표 형식) 데이터 베이스로 표현
• 열Column : 테이블에 보관되는 Data 를 구성 하는 속성
• 행Row,Record: 하나의 Data

DBMS

1) 클라이언트 프로그램 : 데이터를 요청
2) DBMS :클라이언트 프로그램에서 요청한 내용을 데이터 베이스(= DBMS가 관리하는 저장 장치)에 처리(CRUD)

3) 처리 언어 : SQL

• 클라이언트 프로그램과 서버프로그램은 네트워크(클라이언트가 서버에 작업을 요청 - DBMS가 S/p로 요청을 처리)을 한다.
• DB는 네트워크를 따로 만들어야한다 - 데이터베이스를 각 클라이언트에게 있다면(local) 데이터 공유가 되지 않음 > 데이터를 공유할 수 있는 공간에서 관리해야함 : 네트워크

- 클라이언트와 DBMS 사이의 네트워크를 처리하는 언어
= DB에 데이터 CRUD(질의,등록,수정,삭제)요청 할때 사용하는 언어

DML(Data Manipulation Language)

• 트랜잭션 -> 시작과 끝을 알려주는 것, commit와 rollback의 하나의 단위
• commit, rollback - 문제가 생겨서 처음으로 돌아가는 것

데이터 관리자가 사용하는 것 : DDL,DCL

DDL(Data Definition Language)

• 명령어를 바로 실행, 트랜잭션 없음-> 문제가 생겨도 rollback불가
  CREATE, ALTER, DROP, TRUNCATE 만들고 수정하고 삭제DCL(Data Control Language)
• DB관리자들이 수행, 사용자권한 관리 - data 접근 제어

---

DATABASE 생성 및 조회

생성

create[만들고 싶은 것]
create다음에 user, database등 만들고 싶은 것이 들어옴
CREATE DATABASE 이름

조회

SHOW DATABASES - 데이터베이스의 이름들을 조회/ 시작 전 반드시 사용

사용

USE 이름

• 먼저 show database 해준 후 use 이름 해야 사용기능이 해당 database에서 사용됨

사용자 계정 생성 및 권한 설정

사용자 계정 생성

localhost - Local 접속 계정-> 네트워킹 시 자신의 컴퓨터와 db를 연결
원격접속 계정 : 일반사람들은 원격계정으로 네트워크에 접속할 수 있는 권한만 주고, 관리자는 서버에 직접 접근(localhost)할 수 있게함

로컬접속가능한 계정만들기

CREATE USER 계정명 @HOST IDENTIFIED BY ‘password'

원격 접속계정 : @'%'

사용자 계정삭제

DROP USER 계정명 @host
예)
▪ DROP USER scott@localhost
▪ DROP USER scott@'%';

권한 지정

GRANT 권한 ON (DB.TABLE)권한을 부여받는 테이블 TO 권한을 줄 계정명@HOST

권한 : ALL PRIVILEGES - 모든 권한/. - 모든 테이블

예)
▪ GRANT ALL PRIVILEGES ON . TO scott@localhost
▪ GRANT ALL PRIVILEGES ON . TO scott@'%';

---

테이블 생성 - 데이터 타입

• 고정 길이 문자열 -> 채워지지 않은 나머지 문자를 자동으로 채움.
• 제품 시리얼 번호 10글자로 자리를 잡아둔다
• VARCHAR ->최대 30을 잡아두면 다른 정보가 그때마다 잡힌다.,디폴트는 한글자
• 문자열의 값은 ''작은 따옴표로 표시
• 날짜형, DATE, TIME, DATETIME, TIMESTAMP, YEAR
• NULL :없는 값, 모르는 값의 의미
  
  

---

create table member(
    id varchar(10) primary key,
    password varchar(10) not null,
    name varchar(50) not null,
    point  int  default 1000.
    email varchar(100) unique key,
    gender char(1) check(int)
    join_date timestamp not null  default  current_timestamp
);

테이블 삭제

DROP TABLE 테이블 이름
-> 무를 수 없뜸

INSERT (데이터 삽입)

INSERT INTO 테이블이름 컬럼명 , 컬럼명 [,….]) VALUES 값 1, 값 2 [,....])

• 예) INSERT INTO DEPTARTMENT (DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION) VALUES (100, 기획부 ’, '서울')
  • 문자열의 경우 삽입할 값을 ‘ 로 감싸준다
  • 날짜는 형태에 맞게 문자열로 넣어준다 . ( 날짜 : 나 로 구분 , 시간 로 구분
  • 테이블의 모든 컬럼에 데이터를 넣을 경우 컬럼 항목은 생략할 수 있다
  • 특정 컬럼에 값을 안 넣을 경우 INSERT 할 컬럼 대상에서 제외하거나 null 키워드를 값에 넣는다 .

---

테이블 생성 - 제약조건(Key)

컬럼(열) 이 가질 수 있는 값에 대한 제약 조건 을 지정할 때 사용한다

• 기본구문 – 컬럼설정 시 지정
• 컬럼 설정 다음에 따로 설정
  ▪constraint 제약조건이름 제약조건 (컬럼)
• unique key 과 not null 합친 것(중복 x면서 값을 반드시 가짐) -> primary key(pk)
• 나이라는 컬럼 -> 양수만 있게 해주고 싶다 -> 음수만 넣을려고하면 나이는 0보다 큰값만 가능하다는 제한 조건을 건다

---

sql 체크조건

check(gender in ('m','f')) - 'gender' 컬럼의 값은 'm' 또는 'f'만 가능하도록 제한

---

테이블 생성 -> 컬럼명이 뭔지 표시하면됨, 실제로 테이블에 만들때 영문사용

SQL 실습

 -- 주석. 
 -- SQL문 실행 -> control + enter
/* block 주석 */ -- 코드사이에 주석을 넣고 싶을 떄, 주석 여러 줄
-- 사용자 계정 생성

-- local 접속 계정
create user 'playdata'@localhost identified by '1111';
-- 원격 접속 계정
create user 'playdata'@'%' identified by '1111';
-- 등록된 사용자 계정들을 조회
select user, host from mysql.user;


-- 계정에 권한 부여
grant all privileges on *.* to 'playdata'@localhost;
grant all privileges on *.* to 'playdata'@'%';


-- database 를 생성
create database testdb; -- 이름 testdb 인 database를 생성
show databases;
create database mydb;
drop database mydb;

use testdb;

-- table을 생성 - 회원(member) 테이블을 생성
-- 속성이름 데이터타입 제약조건 
create table member (
    id  varchar(10) primary key, 
    password varchar(10) not null,
    name varchar(50) not null,
    point int default 1000, 
    join_date timestamp not null default current_timestamp
);

-- 테이블을 확인
show tables;

drop table member; -- 삭제하면 되돌릴수없기때문에 데이터가 날아가도 상관없는 경우에 , 맨 처음에 실행


create table member(
    id varchar(10) primary key,
    password varchar(10) not null,
    name varchar(50) not null,
    point  int  default 1000,
    email varchar(100) unique key,
    gender char(1) check(gender in ('m','f')),
    age int check(age>0),
    join_date timestamp not null default current_timestamp -- defalut값 설정
);

-- 테이블을 확인
show tables;
-- 테이블의 속성(컬럼)들을 확인
desc member;
-- member 테이블에 데이터를 insert
insert into member values ('id-111','1111', '홍길동', 3000, 
                            'a@a.com','m',20, '2010/2/3 10:20:30');
-- 일부컬럼의 값을 insert => 생략할 수 있는 컬럼 : nullable, default값이 있는 컬럼 
insert into member (id, password, name) values ('id-222','1212','강감찬');


-- #id column은 primary 제약조건이 있기때문에, 같은 id인 'id-111'로 insert x
/*insert into member values ('id-111','1111', '홍길동', 3000, 
                            'a@a.com','m',20, '2010/2/3 10:20:30');*/

-- 제약조건 테스트
-- email  : Unique key 제약조건.
insert into member(id, password, name, email)
        values('id-333','1111','유관순',null);

-- gender: check ==> 'm','f', age:check 양수
insert into member (id, password, name, gender, age)
    values('id-444','1111','유재석', 'm', 20);

-- 조회
select * from member;
desc member;

[ 추가사항 ]

블록 설정 후 - control +shift +enter => 한번에 실행됨, 블록 미설정 시 처음부터 끝까지 실행됨
commit; - 저장을 하는 역할

기본형태

select 컬럼명
from 테이블
where 제약조건

concat = join(문자열 합치기)
select concat('a','b','c','d');

null의 연산값은 null(없는값, 모르는 값)