[SQL 01] Database 개념, MySql DDL 기초

Database 란

여러 사람이 공동으로 공유하여 사용할 목적으로, 통합하여 관리되는 저장된 형태의 데이터의 집합.
자료 항목의 중복을 없애고 자료를 구조화하여 저장함으로써 자료 검색과 갱신등 운영의 효율을 높인 데이터의 집합

 

DBMS (Database Management System) 란

‘사용자’ 와 ‘데이터베이스 (DB)’ 사이에서 사용자의 요구(query) 에 따라 정보를 생성해주고 데이버베이스를 관리해주는 소프트웨어 집합이다.

 

하나의 database 에는 여러 table 들로 구성

 

각 table 은  열(field) 과 행(record) 로 구성

‘학생 테이블’ 의 구조
- ‘열’ 은 column, field, attribute(속성) 로 불리고  테이블의 구조(schema) 를 표현
- ‘행’ 은 row, record, tuple 라 불린다
- 테이블의 구조를 테이블 스키마 (schema) 라고 부른다 

 

테이블에서 ‘키(key)’ 란

테이블에서 특정 레코드를 검색하거나 정렬할수 있는 기준이 되는 필드가 있을수 있는데 이러한 필드를 키(key) 라고 한다. 
데이터베이스에서는 레코드를 구분하기 위해 ‘특별히  선정한 키’ 를
primary key (PK, 기본키)라고 한다.

 

* 테이블 스키마 설계시 primary key 로 설정한 필드에는 중복된 값이 들어올수 없고(unique), 비워둘수도 없다 (not null)

 

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

각각의 테이블 스키마에 있어서 어떤 테이블의 의 필드가 다른 테이블의 필드와 관계성(relation)을 가지는 구조로 만드는 것이 관계형 데이터베이스 (Relational database)라고 한다. 

 

** 참고로 로은쪽과 같이 설계도를 ‘스키마 다이어그램’ 이라고 한다 혹은 ERD (Entity-Ralationship Diagram) 이라고도 한다

 

SQL (Structured Query Language) :  구조화된 질의언어.

관계형데이터베이스 시스템 (RDBMS) 에서 데이터를 다루기 위한 목적으로 설계된 프로그래밍 언어

SQL 문법으로 짜여진 쿼리(Query) 문으로 DBMS 에 실행(execute)하면, DBMS는 그에따른 결과를 보내줍니다.

“쿼리문을 (DB에) 날린다”  라는 식으로 표현하곤 합니다.

일반 ODBC/JDBC 프로그래밍에서 데이터베이스를 공부한다는 것은 쿼리(SQL)을 배우는 것을 말합니다.

 

 

 

- database 대표 SQL 명령어 와 분류 

DML (Data Manipulation Language)	INSERT(레코드 입력), UPDATE(변경), DELETE(삭제), MERGE(합병) ** 테이블 내의 레코드나 컬럼 을 다루는 명령어
DDL (Data Definition Language)	CREATE(생성), ALTER(수정), DROP(삭제), TRUNCATE(잘라내기),
DCL (Data Control Language)	GRANT(권한 주기),  REVOKE(권한 뺏기)
TCL (Transaction Control Language)	COMMIT(확정), ROLLBACK(취소)
DQL (Data Query Language)	SELECT

 

SQL 특징

• SQL 명령문은 대소문자 가리지 않습니다
• 문자열 은 ' ~ ' (홀따옴표)로 감쌈,  문자열 내의 내용은 대소문자 가림
• 여러줄에 걸쳐 명령문 입력 가능
• 명령문이 끝나면 반드시   ;   <- 입력
• COMMIT; 을 실행하기 전까지는 임시저장만 되는 것임,  COMMIT; 을 해야만 데이터베이스의 내용이 업데이트 됨.  (ORACLE 의 경우)
  (MySQL 의 경우 기본적으로 auto-commit 한다)

데이터베이스 접속

> mysql -u [사용자아이디] -p [데이터베이스이름]

 

사용자 확인

현재 접속한 사용자 확인

SELECT user()  

생성된 사용자 정보 확인  : (root) 계정에서 

SELECT user, host FROM mysql.user;

 

CREATE DATABASE  / DROP DATABASE

CREATE DATABASE [데이터베이스명] ;         <- 데이터 베이스 생성

DROP DATABASE [IF EXISTS] [데이터베이스명] ;         <- 데이터 베이스 삭제

 

SHOW DATABASES;

• 현재 데이터 베이스 조회 / 열람
• 기본적으로 설치된 DB
• 샘플 DB

CREATE USER  : 사용자 생성

CREATE USER  ‘[사용자아이디]’  IDENTIFIED BY ‘[암호]’

CREATE USER  ‘[사용자아이디]’@’[호스트]’  IDENTIFIED BY ‘[암호]’

 

ALTER USER ~  : 비밀번호 변경

 ALTER USER myuser IDENTIFIED BY '1111'

 

권한설정

grant all privileges on 디비명.테이블명 to '사용자'@'접속지 주소' (with grant option);

flush privileges;      ← grant 명령 후에 flush 를 해주어야 실제 적용됨.

# '사용자'가 111.222.333.0~255 에서 모든 디비와 테이블에 접속하도록 허용

grant all privileges on *.* to '사용자'@'111.222.333.%';

 

# '사용자'가 111.222.333.444 에서 db1 디비의 모든 테이블에 접속하도록 허용

grant all privileges on db1.* to '사용자'@'111.222.333.444';

 

# '사용자'가  localhost 에서 db1 디비의 table1에 접속하도록 허용

grant all privileges on db1.table1 to '사용자'@'localhost';

 

권한 확인

SHOW GRANTS FOR '사용자'@'호스트';
ex)  SHOW GRANTS FOR 'myuser'@'localhost';

 

권한 삭제

revoke all on DB이름.테이블 FROM '계정아이디'@'접속위치';

 

DROP USER : 사용자 삭제

DROP USER [사용자아이디];

DROP USER [사용자아이디]@[호스트];

EX)

USE mysql;

DELETE FROM user WHERE user =   ‘[사용자아이디]’;

FLUSH PRIVILEGES;

 

접속 전환

> system mysql -u myuser -p

 

USE     

데이터 베이스 선택

 

STATUS ; :

현재 데이터 베이스’ 정보를 보기

 

SHOW TABLES;

현재 데이터베이스 내의 테이블들을 리스트

 

CREATE TABLE 

테이블 생성

 

CREATE TABLE [tablename] (

[column_name1] INT PRIMARY KEY AUTO_INCREMENT,

[column_name2] VARCHAR(15) NOT NULL,

[column_name3] INT

)

 

MySQL 필드의 데이터 타입

 

숫자형 필드  TINYINT, INT, FLOAT...

문자형 필드  CHAR, VARCHAR, TEXT, ENUM...

날짜형 필드  DATE, DATETIME, TIMESTAMP ...

 

* 특히 많이 사용되는 타입

INT : 4bytes 정수를 저장할때 사용

CHAR(n) : 문자의 수가 n 개인 문자열을 저장할때 사용

VARCHAR(n) 문자의 수가 최대 n 개인 문자열을 저장할때 사용

TEXT : 문자의 수가 최대 65535 개인 문자열을 저장할때 사용

 

PRIMARY KEY : 고유한 키값을 같는 필드

AUTO_INCREMENT : 레코드가 생성될때마다 자동증가
NOT NULL : Null 값 허용 안함

 

EX)

ALTER TABLE ~ MODIFY COLUMN

테이블 수정

 

ALTER TABLE ~ ADD COLUMN

테이블 추가

 

ALTER TABLE ~ RENAME

테이블 이름 변경

 

ALTER TABLE ~ DROP

테이블 컬럼 삭제

 

ALTER TABLE ~ MODIFY COLUMN

컬럼 이동 

 

ALTER TABLE ~ CHANGE COLUMN

컬럼 이름변경

 

DROP TABLE 

테이블 삭제

DROP TABLE [IF EXISTS] [테이블 명] ;        

 

DESC 

테이블 열 구조 보기

DESC [테이블 명] ;