SQL : 02 기본 SQL문 & 03 DDL

_플레이데이터 빅데이터캠프 공부 내용_

MySQL : 02 기본 SQL 

1. SQL (Structured Query Language)

: 데이터베이스에 데이터를 질의, 등록, 수정, 삭제 등을 요청하기 위한 표준언어

 

(1) DML(Data Manipulation Language) 

: 데이터 조작하는데 사용하는 언어 

- Table에 Data삽입(추가), 수정, 삭제, 조회(선택)

- 트랙잭션이 발생하여 SQL문은 임시로 적용되며

   commit을 이용해 완전 적용시키거나

   Rollback(잘못 처리한 데이터처리를 되돌리기)을 이용해 처리를 취소할 수 있다. 

- INSERT, UPDATE, DELETE, SELECT(select는 따로 빼서 dql이라고도 함.)

 

(2) DDL(Data Definition Language) 

: Database 스키마 (객체) (Database, Table, View, Index 등의 object)를 생성, 삭제, 수정 등 관리하는 언어

- DDL은 트랙잭션이 발생하지 않아 명령어를 실행하면 Rollback 시킬 수 없다. (늘 주의.!)

- CREATE, ALTER, DROP, TRUNCATE

 

(3) DCL(Data Control Lanuage)

: 사용자에게 권한을 주거나 권한을 없애는 것과 같은 Data 접근을 제어하기 위한 언어

- 사용자에 따라 조회만, 또는 조회랑 추가만 ..

이렇게 사용자에 따라 어떤 처리를 할 수 있는지에 대해 권한을 줄 수 있다. 

- 권한을 주는 것 : GRANT   권한을 뺏는 것  : REVOKE

 

 

MySQL : 03 DDL

1.  Database 생성 및 조회

- 관리자 계정으로 들어간다. (설치할 때 만든 root 계정)

 

(1) Database 생성

create database 이름;

(2) 조회(db 목록 확인)

show databases;

(3) Database 사용

use 이름;

 

 

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

(1) 사용자 계정 생성

create user 계정명@host identifide by 'password';

- host

     -  localhost  :  local 접속 계정

     -  '%'  :   원격 접속 계정

           (mysql이 설치된 컴퓨터가 아니라 다른 곳에서 네트워크 원격 연결해야 할 때)

  => 둘 다 생성해주기

create user scott@localhost identified by 'tiger';
create user scott@'%' identified by 'tiger';

 

-  패스워드 'password'  홀따음표 안에 넣어야 함. 주의 

-  관리자만 계정을 만들 수 있다. (루트계정에 들어와서)

 

(2) 권한 지정

- 생성된 계정은 아무런 권한이 없다. DCL로 권한을 부여해야 한다.

grant 권한 on db.table to 계정@host;

- 권한 지정 또한 로컬, 원격 둘 다 등록해야 한다.

- 아래는 모든 권한을 다 부여한 관리자 계정을 만드는 명령 

-- 예시
grant all privileges on *.* to scott@localhost; -- 로컬에서 접속할 수 있는 계정
grant all privileges on *.* to scott@'%';  -- 원격에서 접속할 수 있는 계정

 

(3) 사용자 계정 삭제

drop user 계정명@host;

-- 예시
drop user scott@localhost; -- 로컬 접속 계정 권한 허용
drop user scott@'%'; -- 원격 접속 계정 권한 허용

 

(4) 생성된 계정 조회

- 만든 로컬, 원격 계정을 확인할 수 있다.

use mysql;
select host, user from user;

 

 

3. 테이블 생성

- sql은 키워드나 테이블, 컬럼명의 경우 대소문자를 구분하지 않는다. (문자열 값인 경우는 구분)

- 대소문자 상관은 없지만, 관레적으로 우리가 만드는 것은 소문자로 예약어의 경우에는 대문자로 작성한다.

 

3.1. 테이블명, 컬럼명 규칙

- 영문자, 숫자, _ 만 가능하다.

- 첫 글자는 반드시 영문자여야 한다. 

 

3.2. 테이블 생성

create table 테이블 이름(
	컬럼이름 datatype [제약조건],
    [컬럼이름 datatype 제약조건]
    ...
);

CREATE TABLE department(
	id NUMBER(4) PRIMARY KEY,
    passwd VARCHAR(30) NOT NULL,
    name VARCHAR(50) NOT NULL,
    point int,
    join_data DATE NOT NULL
    );

- 들여쓰기도 하든 안 하든 상관없지만 가독성 때문에 해준 것. 

- 항상 쉼표 체크하자.

 

3.3. 데이터베이스에 생성된 테이블들 조회

-- database 안의 table들 확인
show tables;

 

3.4. 데이터 생성 - 데이터 타입

 

(1) 문자열 타입

- 크기에 따라, 고정/가변길이에 따라

- 문자열의 값은 작은 따옴표로 감싼다. 큰 따움표 아님!!

- 길이 생략하면 1 (1글자) 로 설정 된다.

 

[고정길이 문자열] : CHAR(n) 

 : 입력된 데이터의 글자 수가 모자라면 공백으로 채운다.

- char(글자수) 지정한 글자수, 크기가 고정

 

[가변길이 문자열] : VARCHAR(n),   TINYTEXT,   TEXT,    MEDIUMTEXT(n),   LONGTEST(n)

 : 최초 지정된 길이는 최대 크기이며 입력된 데이터의 글자수에 따라 저장 크기가 변경되는 타입.

    - 이를 더 많이 쓴다. VARCHAR(n) 대부분 쓴다.

    - TEXT 타입은 max lenght 를 지정하지 않는다.

 

 

(2) Number 타입

 

- 어떤 데이터를 표현하냐에 따라 범위와 크기를 정한다.

1바이트면 되는데 굳이 큰 크기 데이터타입으로 용량을 차지 할 필요가 없다. 데이터에 따라 적절하게 지정.

 

[정수]

- TINYINT

 

- BOOLEAN   : TRUE 1, FALSE 0   

# TINYINT(1) 형식으로 설정되며 입력시 TRUE, FALSE 키워드로 입력하나 조회시에는 1, 0이 조회된다.

 

- SAMLLINT

 

- INT : -21억~21억 사이

 

- BIGINT : 21억 넘으면 

 

[실수]

- DECIMAL 8byte 고정 소수

# DECIAML(M,N) : M 총자릿수, N 소수 자릿수 -- 자리수를 지정할 수 있다.

=> 계산 시 정확도가 요구되는 실수 데이터에 사용

 

- FLOAT     4byte 부동 소수

 

- DOUBLE  8byte 부동 소수

 

= > 4와 8바이트 크기 차이는 정밀도의 차이

소수부가 무한히 나가거나 할 때는 소수점 자리수를 고려하며 정밀하게 표현해야 한다.

그래서 우리는 보통 double을 쓴다.

 

=> 부동 소수 : 큰값을 표현하기 위해 정확성을 떨어뜨린 것.

- 조회할 때는 보통 double 부동소수 

- 계산할 때는 declmal 고정소수  -> 계산의 정확성을 위해

 

 

(3) 날짜형

- DATE           YYYY-MM-DD

- TIME           hh:mm:ss

- DATETIME    YYYY-MM-DD hh:mm:ss  system time zone에 상관없이 고정

- TIMESTAMP  YYYY-MM-DD hh:mm:ss  system time zone에 따라 입력한 일시 변경

- YEAR           YYYY

 

- DATETIME 과 TIMESTAMP의 차이

타임존 위도경도 나라에 따라 다른다. 

mysql에서 타임존을 바꿀 수 있다. 그거에 따라 timestamp는 바뀐다.

근데 datetime은 그거와 무관하게 고정이다. 보통 이거를 많이 쓴다.

 

 

(4) 결측치

- NULL  없는 값, 모르는 값의 의미

 

 

3.5 데이터 생성 - 제약 조건(Key)

 

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

- 기본 구문

   - 컬럼 설정 시 지정

   - 컬럼 설정 다음에 따로 설정

         -  constraint 제약조건이름 제약조건 (컬럼)

- 종류

1) PRIMARY KEY(PK) 

: 하나의 행 row을 대표하는 컬럼, NOT NULL과 UNIQUE 조건을 모두 만족

- 모든 애들이 null 없이 값을 꼭 가지고, 값의 중복이 없다.

=> 행에 대한 식별자로 쓰인다. 행을 구분할 때! 

 

2) FOREIGN KEY(FK)

: 컬럼과 참조하는 테이블의 컬럼 사이의 연결 관계를 설정.

 

3) UNIQUE KEY(UK)

: 테이블의 모든 행이 다른 값을 가져야 하는 컬럼

- 중복 동일한 값을 가질 수 없다. null은 상관없다. 

 

4) NOT NULL(NN)

: null을 값으로 가질 수 없는 컬럼. 즉 반드시 값을 가져야 하는 열

 

5) CHECK(CK)

: 컬럼에 들어가는 값의 조건을 지정 (대부분 업무 규칙을 설정)

- 몇 글자 이상.. 이렇게 값에 대한 규칙을 정하는 것.

 

6) DEFAULT

: 컬럼의 기본값 설정. NULL 값이 insert 되면 지정한 기본값을 추가한다.

 

7) AUTO_INCREMENT

: 자동 증가 정수 컬럼(MY SQL에서 사용). 정수컬럼으로 값이 입력되면 1씩 증가하는 값을 가진다.

- PK 열이 1씩 증가하는 값이 알아서.

- 위 여섯가지와 달리 이것은 MySQL 문법

 

3.6. 테이블 삭제

 

(1) 구문

DROP TABLE 테이블이름;

- DDL로 삭제하면 되돌릴 수 없다.

drop table member;
drop table if exists member; -- 있어도 삭제. 없어도 에러 안뜨게.

- 관리자만이 가능.

(2) 자식테이블에서 참조되고 있는 부모테이블을 삭제  -- 나중에 더 배움

- 자식테이블들을 먼저 삭제한다.

- Foreign key check 옵션을 끄고 해야 한다.

set foreign_key_checks = 0; --옵션 끄기 (1을 주면 옵션을 킨다.)