DB

데이터베이스 유형

• 관계형
  행, 열의 집합인 테이블 형식으로 데이터 저장
  MySQL, MSSQL, SQLite, Oracle, PostgreSQL....
• 비 관계형
  Key-Value 형태로 데이터 저장
  MongoDB, CouchDB, Redis...

MySQL

MySQL 설치 명령어

1. mysql 설치
$sudo apt-get install mysql-server

#2. mysql 버전 및 상태 확인
$mysql –version
$systemctl status mysql
$systemctl start mysql

#3. root 권한으로, mysql 실행 (기본 설정 값)
$sudo mysql –uroot

#4. 데이터베이스 출력 명령어 입력
mysql> show databases;

기본 DB

MySQL 기본 데이터베이스

Schema	설명
information_schema	• 모든 데이터베이스에 대한 메타데이터(테이블, 컬럼 등) 제공 • 읽기 전용(Read-Only)으로 수정/삭제 불가
mysql	• 서버 사용자 계정정보, 권한, 시스템 정보 등 저장
sys	• 서버 성능 분석, 문제 진단에 필요한 View, Function 제공 • performance_schema 데이터를 요약해서 제공
performance_schema	• 서버 성능 분석을 위한 실시간 데이터 제공 • 서버 이벤트에 대한 정보 수집

information_schema 구조 및 주요테이블

Information_schema.schemata 정보

모든 데이터베이스 정보

Information_schema.tables 정보

데이터베이스의 모든 테이블 정보

Information_schema.columns 정보

데이터베이스의 모든 컬럼 정보

SQL Language

DDL (Definition)

데이터정의 언어(Data Definition Language)
Database, Table 등을 생성/삭제/변경하기 위한 명령어

CREATE

#1. SQL 접속
$sudo mysql

#2. 사용할 DB 생성
mysql > CREATE DATABASE SECURITY;

#3. 생성된 DB 확인
mysql > show databases;

#4. 사용자 생성 및 DB 권한 부여
mysql > create user 'sjyoo'@'localhost' identified by '1234';
mysql > grant all privileges on security.* to 'sjyoo'@'localhost' identified by '1234';
mysql > flush privileges; (변경된 설정사항 저장)
mysql > exit; 

#5. 생성한 사용자로 SQL 접속 및 DB 확인
$mysql –u [사용자명] –p (Enter password: 출력되면 비밀번호 입력)
mysql > show databases;
mysql > exit; 

#6. 테이블 생성
$mysql –u root –p
mysql > use SECURITY


mysql > CREATE TABLE security_user(
→ id int not null auto_increment primary key,
→ name varchar(30) not null,
→ password varchar(30) not null default ‘123456’
→ );

#7. 생성한 테이블 및 구조 확인
mysql > show tables;
mysql > desc security_user;

DROP

#1. test 테이블 생성
mysql > CREATE TABLE test(
→ id int not null auto_increment primary key,
→ name varchar(100) not null,
→ password varchar(100) not null default ‘123456’
→ );

#2. test 테이블 삭제
mysql > DROP TABLE test;
mysql > DROP TABLE if exists test;

#4. 특정 데이터베이스 삭제, root 권한 필요
mysql > DROP DATABASE [데이터베이스명];

ALTER

#1. 테이블 필드 추가
mysql > ALTER TABLE security_user ADD phone int;

#2. 필드 수정 및 default 설정
mysql > ALTER TABLE security_user MODIFY COLUMN phone int default '0';

#3. 필드 자료형 변경, INT에서 varchar형 변경
mysql > ALTER TABLE security_user MODIFY COLUMN phone varchar(30);

#4. phone 필드 수정, default 값 000-0000-0000 설정
mysql > ALTER TABLE security_user MODIFY COLUMN phone vhachar(30) default 000-0000-0000';

#5. 특정 필드 삭제
mysql > ALTER TABLE security_user DROP phone;

#6. 특정 필드 추가
mysql > ALTER TABLE security_user ADD email varchar(50);

#7. root 사용자 비밀번호 설정
mysql > ALTER USER 'root'@'localhost' identified with 
mysql_native_password by '1234';

#8. 생성한 테이블 구조 확인
mysql > desc security_user;

DML (Manipulation)

데이터 조작 언어 (Data Manipulation Language)
Database에 저장된 데이터를 조회/삭제/수정하기 위한 명령어

INSERT

#1. 정보 추가
mysql > INSERT INTO security_user(name, password, email) VALUES ('guest1', 'guest1', 'guest1@test.com');
#2. 정보 추가 시, VALUES 값만 사용해 추가(컬럼 수 일치 필요)
mysql > INSERT INTO security_user VALUES (1, 'guest', 'guest', 'guest@test.com');

SELECT⭐

SELECT 조회하고 싶은 컬럼명 FROM 데이터베이스명
조회하고 싶은 속성명 지정하여 검색

FROM

검색할 데이터가 들어있는 테이블 명을 기술

#1. 테이블의 모든 컬럼 값 조회
SELECT * FROM security_user;

#2. 테이블의 name 컬럼 값 조회
SELECT name FROM security_user;

#3. 테이블의 name, password 컬럼 값 조회
SELECT name, password FROM security_user;

#4. 테이블에서 중복된 컬럼 값 제거 후, 조회
SELECT DISTINCT name, password FROM security_user;

#10. information_schema에 저장된 데이터베이스 조회
SELECT table_schema FROM information_schema.tables;

#11. 데이터베이스의 모든 테이블 조회
SELECT table_name FROM information_schema.tables;

#12. 데이터베이스에 생성된 모든 테이블의 컬럼 조회
SELECT column_name FROM information_schema.columns;

ORDER BY

속성을 기준으로 데이터 정렬

#5. id 필드 값을 기준으로 정렬
SELECT * FROM security_user ORDER BY id;

#5. 3번째 필드 값을 기준으로 정렬	=> 컬럼 수 추출 가능
SELECT * FROM security_user ORDER BY 3;

#6. 테이블의 특정 컬럼 값 내림차순 정렬 검색 (ACS = 오름차순)
SELECT * FROM security_user ORDER BY id DESC;

WHERE

검색할 조건 기술

#7. name이 guest인 필드만 조회
SELECT * FROM security_user WHERE name=‘guest’;

#8. name이 guest이고, password도 guest인 필드만 조회
SELECT name FROM security_user WHERE name=‘guest’ and password=‘guest’;
SELECT name FROM security_user WHERE name=‘guest’ && password=‘guest’;

#9. name이 guest이거나, guest1인 필드 전부 조회
SELECT * FROM security_user WHERE name=‘guest’ or name=‘guest1’;
SELECT * FROM security_user WHERE name=‘guest’ || name=‘guest1’;

AS

속성이나 연산의 이름을 다른 이름으로 표시

#서브쿼리를 사용해 별칭 설정 후, 테이블 처럼 사용
SELECT name from (select * from security_user)AS a;

LIKE

대표 문자를 이용해 지정된 속성의 값이 문자 패턴과 일치하는 튜플을 검색하기 위해 사용
% : 모든 문자를 대표
_ : 문자 하나를 대표
# : 숫자 하나를 대표

UPDATE

#1. 특정 이메일에 해당되는 값의 name 필드 값을 변경
mysql > UPDATE security_user SET name='guest1' WHERE email='guest2@test.com';
#2. 여러 필드 값을 변경
mysql > UPDATE security_user SET name='guest1', password='guest1' WHERE email='guest2@test.com';

DELETE

#1. 테이블 내, 데이터 전부 삭제
mysql > DELETE FROM security_user;

#2.테이블 내, 특정 데이터만 삭제
mysql > DELETE FROM security_user WHERE id=1;

DCL (Control)

데이터베이스 접근 권한 설정 언어 (Data Control Language)
Database에 저장된 데이터 관리를 위한 제어 명령어
ㄴ데이터 보안성 및 무결성 등

GRANT

#1. ROOT로 로그인 후, 현재 사용자 권한 확인
mysql > SHOW GRANTS;

#2. 사용자 추가
mysql > CREATE USER ‘test’@’localhost’ identified by ‘1324’;

#3. test 사용자에게 security 데이터베이스에 대한 테이블 권한을 SELECT 만 부여
mysql > GRANT SELECT ON security.* TO ‘test’@’localhost’;

#4. 설정된 권한 즉시 반영
mysql > flush PRIVILEGES;

#5. test1 계정으로 SELECT 명령어 실행
mysql > SELECT * FROM security_user;

#6. test1 계정으로 INSERT 명령어 실행 (권한거부 뜨면 정상)
mysql > INSERT INTO security_user (name, password) VALUES
(‘test’,1234);

REVOKE

# test1계정 권한회수
mysql > REVOKE ALL ON security.* FROM ‘test’@’localhost’;

Built-in Funtion

MySQL에서 사용되는 내장함수

기타 및 정보 함수

명령어	설명	사용 예시
VERSION()	MySQL 버전정보 조회	select version()
DATABASE()	현재 작업 중인 DATABASE 조회	select database()
USER()	현재 MySQL 사용자 조회	select user()
HEX()	입력 값을 16진수 값 변환 및 출력(숫자, 문자열)	select hex('a');
ORD()	첫번째 문자열을 ASCII 값(10진수) 반환	select ord('a');
CHAR()	ASCII 코드값에 해당하는 문자 반환	select char(ord('a'));
ASCII()	입력 문자열에 대한 ASCII 코드값 반환(10빈수 값 반환)	select ascii('a');
REVERSE(str)	입력한 문자열을 거꾸로 변환해 출력	select reverse('security');
LENGTH(str)	입력한 문자열의 길이 값 계산 및 조회

문자열 함수

명령어	설명	사용 예시
SUBSTR(str, start, length)	문자열 특정 부분 추출	select substr('security',3,5); > curit
MID(str, start, length)	SUBSTR()함수와 동일한 기능	select mid('security',3,5) > curit
LEFT(str, len)	문자열 왼쪽부터 지정된 수의 문자 추출	select left('security',4) > secu
RIGHT(str, len)	문자열 오른쪽부터 지정된 수의 문자 추출	select right('security',4); > rity
LPAD(str, len, padding_str)	지정 길이>문자열 길이 일경우, 문자열 왼쪽을 특정 문자로 채워 지정된 길이의 문자열 생성	select lpad('security', 10, '^'); > ^^security
RPAD(str, len, padding_str)	지정 길이>문자열 길이 일경우, 문자열 오른쪽을 특정 문자로 채워 지정된 길이의 문자열 생성	select rpad('security', 10, '&'); > security&&

그룹 및 집계 함수

명령어	설명	사용 예시
CONCAT(arg1, arg2, ..)	입력한 문자열을 결합해, 하나의 문자열로 출력하는 함수	select concat('Security', ‘_’, ‘Academy’, ‘_2024’); select concat(name) from security_user;
CONCAT_WS('separator', arg1, arg2, ..)	CONCAT + 구분자를 자동으로 입력하는 함수	select concat_ws(‘-’ , database(), version());
GROUP_CONCAT()	특정 그룹 내 여러 행에 대한 문자열을 결합해 출력	select group_concat(name) from security_user;

조건 함수

명령어	설명	사용 예시
IF(expression, arg1, arg2)	참, 거짓에 대한 결과 값을 반환하는 조건문	select if(5>1, database(), 0);
CASE	조건(참, 거짓)에 따른 결과 값을 반환하는 조건문	select case when 5>1 then “TRUE Value” else “FALSE Value” end;

연산자

명령어	설명	사용 예시
AND	두 조건이 모두 참일경우, 참 값을 반환	select (2>1) and (3>1);
OR	두 조건 중 하나라도 참일경우, 참 값을 반환	select 2>1 or 0>1;
LIMIT		select * from security_user limit 1
UNION	select 쿼리 결과를 합침(column 수를 맞춰야함)	SELECT name from security_user union select email from security_user;
LIKE	입력한 문자 패턴을 매칭한 결과 값을 반환	select name from security_user where name like '%g%';
IN	SELECT문에서 WHERE 값을 지정할 때 사용	select * from security_user where name in('guest');
SEPARATOR	GROUP_CONCAT함수에서 구분자	select group_concat(name, password separator '&') from security_user;
SLEEP	지정한 시간동안 일시 정지 후, 재실행(한 데이터 당 일시정지)	select sleep(3);