목차
DBMS
DCL(GRANT / REVOKE )
DDL(CREATE / ALTER / DROP / RENAME / TRUNCATE)
DML(INSERT, SELECT, UPDATE, DELETE)
TCL(COMMIT, ROLLBACK)
🧩DataBase
- 데이터를 영구적으로 저장 할 수 있는 저장소 이다.
- Data Base를 이용하면 서로 다른 시스템 간에 데이터를 공유 할 수 있다.
- 데이터베이스는 여러 테이블을 저장하는 장소 개념
🧩DBMS (Data Base Management System)
- Data Base 를 관리하는 시스템을 DBMS라고 한다.
- 많은 종류의 DBMS 들이 존재한다.
- Data Base Model 은 Relational 과 NO-SQL 로 크게 구분 된다.
- Model 은 관계형 데이터 베이스(Relational Data Base) 이다.
🧩DCL (Data Control Language) : 데이터 제어어
- 데이터 사용권한이나 유저를 다루는 언어
- 데이터베이스에 접근하고 객체들을 사용하도록 권한을 주고 회수하는 명령어들
- 데이터의 보안, 무결성, 회복 정의에 사용유저생성
- Root 는 모든 권한을 가지고 있다.
- Root 를 통해 사용자 의 명령어 실행 및 접근 권한을 조정 할 수 있다.
- GRANT / REVOKE / COMMIT / ROLLBACK
유저생성 : 접근 IP 대역을 설정
CREATE USER [유저이름]@[접근가능한 IP주소] IDENTIFIED BY [비밀번호]-- web_user는 모든 ip에서 접근 가능하고, 비밀번호는 pass로 만들어라
CREATE user 'web_user'@'%'IDENTIFIED by'pass';
-- dba_user 는 192.168 로 시작하는 ip에서 접근 가능하고, 비밀번호는 pass로 만들어라.
-- 특정 IP 대역만 접근 가능하게 할 경우
CREATE user 'dba_user'@'192.168.%' IDENTIFIED by 'pass';
-- user002 는 192.168.38.70 ip 에서만 접근 가능하고, 비밀번호는 pass3 로 만들어라.
CREATE user 'user002'@'192.168.38.70'IDENTIFIED by'pass3';
+) 그외 유저삭제, 확인, 변경
/*비밀번호 변경*/
-- SET PASSWORD FOR [사용자] = PASSWORD[패스워드];
-- 'user002'@'192.168.38.70'의 비밀번호를 pass 로 바꿔보자
set password for 'user002'@'192.168.38.70' = password('pass');
/*user의 모든 테이블 보기*/
-- 유저 권한은 user 확인에서 가능하다.
-- 유저생성확인
SELECT * from mysql.user;
/*user 삭제*/
-- DROP USER[유저이름]@[ip]
drop user'dba_user'@'192.168.%';
drop user'user002'@'192.168.38.70';
GRANT : 사용자에게 작업에 대한 권한을 부여
GRANT[권한종류]ON[데이터베이스테이블]TO[유저명]-- 데이터를 INSERT(추가), UPDATE(수정), SELECT(조회), DELETE(삭제)
-- ALL PRIVILEGES : GRANT OPTION 을 제외한 모든 권한 부여
-- GRANT OPTION : 권한을 주는 권한
-- dba_user 에게 모든 데이터베이스의 모든 테이블의 데이터를 볼 수 있는 권한을 준다.
grant select on *.* to 'dba_user'@'192.168.%';
-- user002 에게 모든 데이터베이스의 모든 테이블에 데이터를 보고, 추가하고, 수정하고, 삭제할 수 있는 권한을 준다.
grant select,insert,update,delete on *.* to'user002'@'192.168.38.70';
-- web_user 에게 모든 데이터베이스에 모든 테이블의 데이터를 뭐든지 할 수 있는(권한 주는거 제외) 권한을 준다.
-- IP가 %인 경우는 생략 가능
grant all privileges on *.* to 'web_user'@'%';
REVOKE :사용자에게 작업에 대한 권한을 회수, 박탈
REVOKE [권한] ON [데이터베이스,테이블] FROM [유저명]-- 'user002'@'192.168.38.70'에게 insert,update,delete 권한을 회수하기
REVOKE insert,update,delete on *.* FROM 'user002'@'192.168.38.70';
🧩DDL (Data Define Language) : 데이터정의어
- 데이터베이스 구조 정의, 테이블 등을 생성하는 언어(명령어)
- 데이터베이스의 전체 골격 구성하는 역할
- 데이터베이스의 구조를 정의, 제거, 변경
- 외부스키마 정의하거나 데이터베이스의 논리적, 물리적 구조 정의하고 제약조건 규정
- CREATE / ALTER / DROP / RENAME / TRUNCATE
CREATE : 데이터베이스, 테이블 생성
1) 데이터 베이스 생성
CREATE DATABASE [데이터 베이스 이름]CREATE database mydb;
-- 데이터 베이스 목록 보기
show databases;
-- 데이터 베이스 사용
-- USE [데이터베이스 이름]
use mydb;
2) 테이블 생성
CREATE TABLE [테이블]([컬럼명][데이터타입](크기),)/* 데이터타입 종류 (자주 쓰는 일부)
* 1) 문자 타입
* 고정형 - 한번정해진 크기가 변하지 않는 것 (2글자로 선언 했으나 1글자로 오면 남은 한자리는 공백 처리 됨)
* char(바이트수)
* 가변형 - 정해진 크기보다 적게 들어오면 알아서 줄여준다.
* varchar(바이트수)
* text : 65,535 byte
* longtext : 4,294,967,295 byte
*
* 2) 숫자 타입 - int, long, float, double
*
* 3) boolean
*
* 4) 날짜 타입
* DATE : 0000-00-00
* DATETIME : 0000-00-00 00:00:00
* TIMESTAMP: DATETIME 과 같지만 time Zone에 따라 시간이 변경 된다.
* */
-- test_table 테이블 생성
CREATE TABLE test_table (
user_name VARCHAR(50),
age INT(3),
mobile VARCHAR(13),
reg_date DATE
);
-- employees 테이블 생성
CREATE table employees(
emp_no int(3),
first_name varchar(8),
last_name varchar(2),
email varchar(50),
mobile varchar(13),
salary int(8),
reg_date date
);
++) 혹시나 DATE쪽에서 오류나게되면... (버전낮아서 오류났음)
-- sql버전 낮아서 날짜 트리거
-- test_table
CREATE TRIGGER test_table_OnInsert BEFORE INSERT ON test_table FOR EACH ROW SET NEW.reg_date = CURRENT_DATE;
-- employees
CREATE TRIGGER employees_OnInsert BEFORE INSERT ON employees FOR EACH ROW SET NEW.reg_date = CURRENT_DATE;-- test_table 확인
desc test_table;
-- employees 확인
desc employees;
-- 테이블 확인
show tables;
ALTER : 기존테이블의 컬럼 추가, 삭제, 데이터타입 변경 (*구조변경시 기존데이터에 영향을 줄 수 있다.)
1) 컬럼 이름 변경
ALTER TABLE[테이블명] RENAME COLUMN [변경전이름] TO[변경후이름]-- CREATE로 생성한 녀석은 DROP로 삭제하고 ALTER로 수정한다.
-- 테이블 수정(컬럼에 대한 수정)
ALTER TABLE employees RENAME COLUMN last_name TO family_name;
++) 혹시나 오류나게되면... (낮은버전에서 오류날경우)
-- 버전낮아서 이름변경 이걸로해야함...
ALTER TABLE employees CHANGE last_name family_name VARCHAR(2);
2) 컬럼 추가
ALTER TABLE [테이블명] ADD ([컬럼명][데이터타입](크기))ALTER TABLE employees ADD (depart_no varchar(10));
ALTER TABLE employees ADD (commision varchar(10)); -- commision 문자열 10바이트
ALTER TABLE employees ADD (etc varchar(100)); -- etc 문자열 100 바이트
3)컬럼 속성 변경
ALTER TABLE [테이블명] MODIFY COLUMN [컬럼명][데이터타입](크기)ALTER TABLE employees MODIFY COLUMN commision float(4,2);
-- float(전체자릿수, 소숫점자릿수) -> 00.00
4)컬럼 삭제
ALTER TABLE [테이블명] DROP COLUMN [컬럼명]-- etc 삭제해보기
ALTER TABLE employees DROP COLUMN etc;
DROP : 테이블을 삭제, 데이터베이스를 삭제하는 명령어
1) 데이터베이스 삭제 : 데이터베이스는 함부로 삭제 XX!
DROP DATABASE [데이터 베이스 이름]DROP DATABASE mydb;
2) 테이블 삭제
DROP TABLE [테이블]-- employees 테이블 삭제
drop table employees;
-- test_table 테이블 삭제
drop table test_table;
🧩DML (Data Manipulation Language) : 데이터 제어어
- 데이터를 다루는 언어
- 정의된 데이터베이스에 입력된 레코드를 조회, 수정, 삭제하는 등의 역할을 한다.
- INSERT, SELECT, UPDATE, DELETE, UPSERT
- key 의 중복 여부에 따라 INSERT 또는 UPDATE 할 수 있는 UPSERT 문
INSERT : 데이테베이스에 레코드 삽입
INSERT INTO [테이블명](컬럼명,...)VALUES(컬럼명에 넣을값,...)-- 붙여쓰기..
INSERT INTO employees(emp_no,first_name,family_name,email,mobile,salary,depart_no,commision)
VALUES(111,'지훈','김','zer0box@naver.com','01012341234',99999999,'dev01',90);
-- 알아보기쉽게 띄어쓰기
insert INTO employees (
emp_no,
first_name,
family_name,
email,
mobile,
salary
) VALUES (
112,
'태근',
'김',
'email@naver.com',
'01020343054',
99999999
);
DELETE : 데이터 삭제
DELETE FROM [테이블명]-- employees 테이블 데이터 삭제
delete from employees;
-- commision 이 null 인 값을 지워보자
delete from employees where commision is null;
UPDATE : 데이터 수정
UPDATE [테이블명] SET [컬럼] = [값] WHERE [조건]-- depart_no가 dev005인 row에 대해서 commision을 30으로 변경
UPDATE employees SET commision = 30 where depart_no = 'dev005';
SELECT : 데이터조회
1) 특정 컬럼 조회
SELECT [조회할 컬럼] FROM [테이블명]-- employees 테이블 데이터 전체조회
SELECT * FROM employees;
-- employees 테이블에서 first_name, family_name, mobile 조회
SELECT first_name,family_name,mobile from employees;
-- 산술 표현
-- 숫자로 된 컬럼에 대해서는 계산해서 보여 줄 수 있다.(실제로 계산되는건 아님)
SELECT first_name, family_name, salary/10000 as 급여 FROM employees;
-- 컬럼명을 임시로 변경해서 보여 줄 수도 있다. (as 사용)
-- 보여주는 순서 변경 가능
SELECT family_name, first_name, salary/10000 as 급여 FROM employees;
-- 문자열을 합칠 수도 있다.
SELECT concat(family_name, first_name) as 이름,
concat(TRUNCATE(salary/10000,0),'만원') as 급여
FROM employees;
-- 이 모든건 실제 데이터를 가공해서 보여줄 뿐이지 조작 되진 않는다.
2) 특정 조건의 데이터를 조회
SELECT [조회할 컬럼] FROM [테이블] WHERE [조건]-- employees 테이블에서 family_name이 김씨인 사람의 데이터 조회
SELECT * FROM employees WHERE family_name ='김';
-- employees 테이블에서 급여가 300만원 이상인 사람의 first_name,salary 조회
SELECT first_name,salary FROM employees WHERE salary > 3000000;
-- family_name = '김' && salary > 2000000
-- family_name = '김' and salary > 2000000
-- salary >= 1000000 && salary <= 3000000
SELECT concat(family_name,first_name),salary FROM employees where salary >= 1000000 and salary <= 3000000;
-- || or
-- family_name = '김' || salary = 2000000
SELECT concat(family_name,first_name),salary FROM employees WHERE family_name = '김' or salary = 2000000;
-- BETWEEN AND (특정값 사이를 구할 때 사용)
-- AND 조건을 이용 해서 salary 가 50만원 보다 크거나 같고, 400만원 보다 작거나 같은 사람의 first_name, family_name,salary를 구하시오.
-- 부등호가 특수문자로 인식되는 경우가 있어서 피하고자 할 떄 종종 사용된다.
SELECT first_name,family_name,salary FROM employees WHERE salary BETWEEN 500000 AND 4000000;
3) 중복 제거
SELECT DISTINCT [출력할 컬럼] FROM [테이블]SELECT DISTINCT family_name,salary FROM employees WHERE salary = 2000000;
-- distinct는 하나의 컬럼만 사용 할 때 가장 정확한 효과가 나타난다.
SELECT DISTINCT family_name,first_name FROM employees WHERE salary = 2000000;
4) IN : OR과 같다.
-- family_name이 '김' 또는 '이' 또는 '박' 인 사람의 모든 정보를 가져와라
SELECT * FROM employees WHERE family_name = '김' or family_name = '이' or family_name = '박';
-- OR로 비교하는 컬럼이 모두 같은 경우 사용한다.(속도도 훨씬 빠르다!)
SELECT * FROM employees WHERE family_name IN ('김', '이', '박');
5) IS NULL || IS NOT NULL
-- 사용전.. null값이 없어서 만들어줌
-- commision이 10인 녀석을 찾아 null로 만들어라.
UPDATE employees SET commision = NULL WHERE commision = 10;
-- 사용해보기
SELECT * FROM employees where commision is null;
SELECT * FROM employees where commision is not null;
6) LIKE : 일부 비슷한 문자열 검색, 검색기능시 많이 사용 / 남발할 경우 속도저하
WHERE [컬럼명] LIKE '%[문자열]%'-- ze% : ze로 시작하지만 뒤에는 아무거나 와도된다. -> ze로 시작하는....
SELECT * FROM employees WHERE email LIKE 'ze%';
-- %com : 앞에 아무거나 와도 되지만 뒤는 com 으로 끝나야 한다. -> com으로 끝나는...
SELECT * FROM employees WHERE email LIKE '%com';
-- %se% : se 앞뒤로 아무거나 와도 된다. -> se를 포함하는...
SELECT * FROM employees WHERE email LIKE '%se%';
-- %s%e% : s 와 e 사이에 아무거나 와도 된다. -> s 또는 e를 포함하는...
SELECT * FROM employees WHERE email LIKE '%s%e%';
7) ORDER BY (정렬) : 특정컬럼을 기준으로 정렬 / 오름차순 : ASC(△), 내림차순:DESC(▽)
SELECT * FROM [테이블명] ORDER BY [컬럼명] [ASC|DESC]SELECT * FROM employees ORDER BY salary DESC;
SELECT * FROM employees ORDER BY family_name ASC;
SELECT * FROM employees ORDER BY family_name; -- ASC : 생략이 가능하다.
-- 김씨중에서 급여가 높은 순으로 정렬하시오.
SELECT * FROM employees WHERE family_name = '김' ORDER BY salary DESC;
-- 연봉이 높은 순으로 정렬 하세요.
SELECT family_name, first_name, salary*12 as annual_salary FROM employees ORDER BY salary DESC;
-- 다중정렬 (1차정렬 후 동률의 데이터에서 2차정렬을 진행)
SELECT * FROM employees ORDER BY family_name, salary DESC;
8) GROUP BY : 데이터를 특정컬럼을 기준으로 묶어서 가져오는 경우 사용 (통계)
- 조회되는 컬럼들은 묶어주는 기준 컬럼이거나 집계되는 컬럼이어야한다.
SELECT [컬럼1], [컬럼2]... FROM [테이블이름] GROUP BY [묶어줄 컬럼]-- employees 테이블에서 부서(depart_no)별로 급여(salary) 합산 요청
SELECT depart_no, sum(salary) FROM employees GROUP BY depart_no;
-- 그룹의 기준이 되는 컬럼과, 집계 컬럼 외에 다른 컬럼이 들어간다면?
-- 나오긴하지만 그 의미를 알 수 없다 -> 각 그룹의 가장 첫값을 보여준다.
-- 다른 DB에서는 이 경우 에러를 발생 시킬수도 있다.
SELECT depart_no, sum(salary),family_name FROM employees GROUP BY depart_no;
-- 부서별 급여 평균 - AVG(column)
SELECT depart_no, TRUNCATE(AVG(salary),0) as sal_avg FROM employees GROUP BY depart_no;
8-1) HAVING :GROUP BY에서 WHERE처럼 조건을 주고 싶을 때 사용
- HAVING 은 별칭을 사용하면 안된다. (total 대신 sum(salary) 써야 한다. ) => 마리아에서는 허용 해주지만 몇몇 디비에서는 에러가 발생한다.
-- 급여 합계가 500만원 이상인 부서들을 합계 급여가 높은 순으로 정렬 하세요.
SELECT depart_no, sum(salary) as total FROM employees GROUP BY depart_no HAVING sum(salary) > 5000000 ORDER BY total DESC;
🧩 TCL (Transaction Control Language) : 트랜잭션별로 제어하는 명령어
- data Base 에서 사용되는 쪼갤 수 없는 업무처리의 단위이다.
- All or Nothing, 하나의 작업 내부의 과정이 잘못되면 전부 취소 되어야 한다.
- commit 과 rollback은 데이터(DML)에 한정된다.
- DCL, DDL 은 되돌릴 수 없다.
COMMIT : 트랜잭션의 작업을 저장 / 현재 상태를 저장
COMMIT;-- 1) AUTO COMMIT 여부
SELECT @@AUTOCOMMIT; -- 1: 설정, 0: 미설정
-- 설정 | 미설정
SET AUTOCOMMIT = 1; -- 오토커밋 설정 ON
SET AUTOCOMMIT = 0; -- 오토커밋 설정 OFF
ROLLBACK : 트랜잭션의 작업을 취소, 원상태로 복구 / commit하지 않은 내용에 대해서 되돌릴 수 있다.
ROLLBACK;
