14,15일차 - 데이터베이스 DDL, DCL, DML

테이블의 구조

Column : 데이터를 저장하기 위한 필드 또는 속성이라고도 한다

Row :  하나의 테이블에 저장되는 값으로 튜플이라고도 한다

SQL 의 종류

■ DDL (Data Defineition Language) : CREATE, ALTER, DROP, RENAME 

 - 데이터베이스의 구조를 정의하는 언어이다

■ DML (Data Manipulation Language) : INSERT, UPDATE, DELETE, SELECT

 - 테이블에서 데이터를 삽입, 수정, 삭제, 조회한다

■ DCL (Data Control Language) : GRANT, REVOKE 

 - 데이터베이스에서 사용자에게 권한을 부여하거나 회수한다

■ TCL (Transaction Control Language) : COMMIT, ROLLBACK

 - 트랜잭션을 제어하는 명령이다

 

자주 사용하는 데이터 타입

1) 문자 타입:
- 고정형: 한번 정해진 데이터 크기가 변하지 않습니다. 예를 들어, CHAR 타입은 고정된 크기로 문자열을 저장하며, 공백으로 채워집니다. (영어는 1바이트, 한글은 2~3바이트)
- 가변형: 저장된 데이터에 따라 크기가 자동으로 조정됩니다. VARCHAR 타입은 가변적인 크기의 문자열을 저장할 수 있습니다.

2) 숫자 타입:
- 정수형: INT, LONG 등의 정수형 데이터 타입이 있습니다.
- 부동소수점형: FLOAT, DOUBLE 등의 부동소수점형 데이터 타입이 있습니다.

3) 불리언 타입: 
- BOOLEAN 타입은 참(true) 또는 거짓(false) 값을 저장합니다.

4) 날짜 및 시간 타입:
- DATE: 연월일을 나타냅니다. (형식: 'YYYY-MM-DD')
- DATETIME: 연월일과 시분초를 나타냅니다. (형식: 'YYYY-MM-DD HH:MM:SS')
- TIMESTAMP: DATETIME과 비슷하지만, 시간대에 따라 시간이 조정될 수 있습니다.

데이터베이스를 처음 만든다면?

먼저 루트 계정을 그대로 사용하다가 잘못 삭제하면 영영 복구할 수 없어질 수 있고, 모두가 루트 계정을 쓰기에는 보안에도 좋지 않다 그래서 따로 관리할 계정을 만들어서 데이터베이스를 관리하는것이 보편적인 방식이다

/* 1) 계정 생성과 비밀번호 설정 */
create user 'web_user'@'%' identified by 'pass';
-- CREATE USER[사용자의 이름]@[접근할 아이피] IDENTIFIED BY [사용자의 비밀번호]
-- web_user 라는 계정을 만들고 그 계정의 비밀번호는 'pass' 이다
create user 'dba_user'@'192.168.%' identified by 'pass';
select * from mysql.user;
-- * 모든 열을 선택한다는 의미를 가진다
-- % 는 0개 이상의 임의의 문자열을 의미한다
-- 현재 생성된 계정의 목록과 각 계정의 권한을 확인할 수 있다

/* 2) 계정에 권한 부여 및 회수 */
-- GRANT [권한종류] ON [데이터베이스.테이블] TO [유저 이름]
-- REVOKE [권한종류] ON [데이터베이스.테이블] FROM [유저 이름]
-- ALL PRIVILEGES : 권한을 부여하는 권한을 제외한 모든 권한을 부여한다
grant all privileges on *.* to 'web_user'@'%';
select * from mysql.user;
revoke insert, update, delete on *.* from 'user002'@'192.168.38.70';

/* 2-1) 비밀번호 변경 */
-- SET PASSWROD FOR [대상 유저의 이름@아이피] = PASSWORD([변경할 비밀번호])
-- PASSWORD() : 입력된 비밀번호를 암호화시켜서 저장해주는 함수이다
set password for 'user002'@'192.168.38.70' = password('pass');

/* 3) 계정 삭제 */
-- DROP USER [유저 이름]@[접근 가능한 아이피]
drop user 'dba_user'@'192.168.%';
select * from mysql.user;

 

데이터베이스를 관리할 계정인 web_user 를 만들었다면 테이블을 만들 차례이다 테이블을 만들고 여러가지 DML 관련된 SQL 문을 연습해보자 현재 세션에서 사용할 데이터베이스는 항상 지정해주고 테이블을 만들어야 한다

/* 데이터베이스 목록 조회 */
show databases;

/* 1) 데이터베이스를 생성하자 */
-- CREATE DATABASE [데이터베이스의 이름]
create database mydb;
show databases;

/* 1-1) 데이터베이스를 삭제하자 */
-- DROP DATABASE [데이터베이스의 이름]

/* 1-2) 현재 세션에서 사용할 데이터베이스를 선택하자  */
-- USE [데이터베이스의 이름]
use mydb;

/* 2) 테이블 생성 */
-- create table [테이블의 이름](
-- [컬럼의 이름] [데이터 타입](데이터의 크기),
-- .....
-- [컬럼의 이름] [데이터 타입](데이터의 크기)
-- );
-- 자바에서는 int 하면 바이트 크기가 지정되어있지만, 데이터베이스는 크기도 지정해줘야한다!
-- drop table [테이블의 이름];

create table test_table(
	user_name varchar(50),-- 50byte 는 25자를 입력값에 따라 크기를 조정해준다
	age int(3), -- 세자리수를 0~999 사람이 아무리 나이를 먹어도 999를 넘어갈 순 없다
	mobile varchar(11), -- 전화번호 하이픈(-) 뺀 문자열은 11byte이다
	reg_date date default current_date -- 기본값으로 현재 날짜를 입력해준다
);

/* 2-1) 테이블 구조 보기 */
-- 컬럼의 이름과 타입 크기, 기본값 등 테이블과 관련된 정보를 출력해준다
desc test_table;

/* 2-2) 테이블 삭제해보기 */
drop table test_table;
desc test_table;

/* 2-3) 테이블 안 컬럼을 수정하기 */
-- 데이터가 들어가기 전에 미리 수정을 해야한다
/* 
 * 1) 컬럼의 이름을 변경하기
 * 기존 데이터가 있는데 함부로 바꾸면 안된다 기존 데이터가 날라가버린다
 * ALTER TABLE [테이블의 이름] RENAME COLUMN [변경전 이름] TO [변경후 이름] 
*/
alter table employees rename column last_name to family_name;
/*
 * 2) 새로운 컬럼을 추가한다
 * ALTER TABLE [테이블의 이름] ADD (
 * 	[컬럼의 이름] [데이터 타입](크기)
 * );
*/
alter table employees add(depart_no varchar(10));
/*
 * 3) 기존 컬럼의 데이터타입을 변경한다
 * 주의사항 1) 해당 컬럼의 비어 있어야 한다
 * 이유는? 예를 들어서 원래 주민등록번호가 숫자라고 가정한다
 * 999999-231231 이게 갑자기 문자열로 바뀌어버린다
 * 그 안에 있는 값은 숫자에서 문자열로 바뀌는 과정에서 문제가 발생해버린다
 * 그렇기 때문에 데이터타입을 바꿀때에는 테이블에 데이터가 비어있어야한다
 * 자바보다 에러를 상세하게 알려주진 않는다 대충 알려준다
 * ALTER TABLE [테이블의 이름] MODIFY COLUMN [컬럼의 이름] [데이터 타입](크기)
*/
alter table employees modify column commision float(4, 2);
/*
 * 4) 컬럼을 삭제
 * ALTER TABLE [테이블의 이름] DROP COLUMN [컬럼의 이름]
 * etc 를 삭제 해보자
*/
alter table employees drop column etc;
desc employees;

 

 

테이블을 만들고 자바의 클래스를 선언하는 것처럼 안의 컬럼의 데이터 타입과 크기를 지정해줬다 이제는 테이블 안에 데이터를 삽입해보고, 또 삭제하거나 수정해보자 

/* 1) 데이터를 삽입 */
-- INSERT INTO [테이블의 이름](컬럼명,....) VALUES (컬럼명에 넣을 값,...);
insert into employees (emp_no, first_name, family_name, email, mobile, salary, depart_no, commision)
	values(111, '정원', '김', 'comkjo0512@gmail.com', '01038919755', 99999999, 'dev01', 90);

/* 2) 데이터 삭제 */
-- DELETE FROM [테이블의 이름] WHERE [조건]
-- 고객의 정보를 날릴 수 있으니 delete 는 항상 매번 조심하자
delete from employees where commission is null;
-- commission 컬럼에서 null 값을 가지는 행은 모두 지워라

/* 3) 데이터 수정 */
-- UPDATE [테이블의 이름] SET [컬럼] = [값] WHERE [조건]
-- 조건이 없다면 모든 행의 해당 컬럼의 값을 전부 변경해버린다
update employees set commission = 30 where depart_no='dev005';

★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
/* 4) 데이터 조회 */
-- 내가 원하는 데이터를 조회하고 싶다면 아래의 절차를 따라가면 된다!
-- a) 어떤 데이터를 보여주고 싶은가?
-- b) 그 데이터는 어느 테이블에 저장되어 있는가?
-- c) 이 모든건 실제 데이터를 가공해서 보여줄 뿐이지 조작되진 않는다!

-- 1) SELECT 로 데이터 조회하기
-- SELECT [조회하고 싶은 컬럼] FROM [테이블의 이름]
select * from employees;
-- employees 테이블의 모든 열에 붙은 행들이 조회된다

-- 1-1) 실제로 데이터를 변경하지 않고 계산한 결과를 조회한다
select 
	concat(family_name, first_name) as 이름, 
	concat(truncate(salary/10000, 0), ' 만원') as 급여 
from employees;
-- concat(입력 1, 입력 2) : 입력 받은 두개의 문자열 혹은 숫자를 합쳐서 하나의 문자열로 나타낸다
-- 숫자 + 문자 => 가능
-- 숫자 + 숫자 => 불가능
-- 문자 + 문자 => 가능
-- truncate(실수, 소숫점 자릿수) : 실수의 소숫점자릿수를 얼마까지 표시할지 정한다

-- 2) 특정 조건의 데이터를 조회하기
-- SELECT [조회할 컬럼] FROM [테이블의 이름] WHERE [조건]
select * from employees where family_name = '김';
-- BETWEEN AND : 특정 언어에서 부등호를 특수문자로 인식하는 경우에 대체제로 사용한다
select family_name, first_name, salary from employees 
	where 500000 <= salary and salary <= 4000000;
select family_name, first_name, salary from employees 
	where salary between 500000 and 4000000;
    
-- 3) 중복 제거
-- SELECT DISTINCT [중복 검사를 할 컬럼] FROM [테이블의 이름] WHERE [조건]
select distinct family_name from employees where salary = 2000000;
-- 열의 값들 중에서 중복된 값을 제거한 한 결과를 출력한다

-- 4) IN
select * from employees where family_name = '김' or family_name = '이' or family_name = '박';
-- a) or 로 비교하는 컬럼이 모두 같을 경우 사용한다
-- b) 속도도 기존 or 보다 훨씬 빠르다 10만건 이상일때 속도차이가 많이 난다
select * from employees where family_name in ('김', '이', '박');

-- 5) IS NULL || IS NOT NULL
-- 열의 값들 중에서 NULL 값인 행들을 출력한다
select * from employees where commission is null;

-- 6) LIKE
-- 일부 비슷한 문자열을 검색해준다 - 검색 기능을 사용할 때 가장 많이 사용한다
-- WHERE [컬럼명] LIKE '%[문자열]%'
-- % 는 0개 이상의 임의의 문자열을 의미한다

/*
 * ze% : ze 로 시작하지만 뒤에는 아무거나 와도 된다
 * - ze 로 시작하는..
 * %com : 앞에 아무거나 와도 되지만 뒤는 com 으로 끝나야 한다
 * - com 으로 끝나는..
 * %se% : se의 앞 뒤 아무거나 와도 된다
 * - se 를 포함하는..
 * %s%e% : s 의 앞뒤 e 의 앞뒤 아무거나(없거나) 와도 된다
 * - s 또는 e 를 포함하는..
 */
 
 select * from employees where email like 'ze%';
select * from employees where email like '%com';
select * from employees where email like '%se%';
-- 제일 많이 사용한다 단어를 포함하는지 확인할때

b-- 검색엔진에서 제목, 내용 컬럼에서 단어를 포함하는지 결과에 표시해준다
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;

-- 김씨 중에서 급여가 높은 순서대로 정렬하시오.
select * from employees where family_name = '김' order by salary desc;
-- 누군가가 만건의 데이터를 줄태니 정렬하세요~
-- 막 정렬을 한 후 김씨를 뽑으세요~ 
-- 일을 두번하는거지요

-- 김씨를 다 뽑아서 천건 정도 뽑음
-- 정렬하세요~
-- 정렬이 맨 뒤에 작업 하는것이 효율적이다!!

-- 연봉이 높은 순으로 정렬하세요
select emp_no, first_name, family_name, email, salary * 12 as ann_salary from employees order by ann_salary desc;


-- 다중 정렬 (1차 정렬 후 동률의 데이터에서 2차 정렬을 진행한다0
select * from employees order by family_name, salary desc;

-- 8) GROUP BY
-- 데이터를 특정 컬럼을 기준으로 묶어서 가져오는 경우 사용한다
-- 데이터의 통계를 내고 싶을때 자주 사용한다
-- SELECT [컬럼 1], [컬럼 2], ... FROM [테이블의 이름] GROUP BY [묶어줄 컬럼]
-- 조회되는 컬럼들은 묶어주는 기준 컬럼이거나, 집계되는 컬럼이어야 한다
-- 첫번째 order by 해가지고 depart_no 로 
select * from employees order by depart_no;
-- 두번째 order by 하지 않고 where 조건으로 걸어서 1,2,3,4,5 나누어서 계산했습니다
select * from employees where depart_no = 'dev001';
-- 공통점은 depart_no 을 기준으로 연봉을 묶어서 합산했다
-- 묶어줄 하나의 컬럼을 기준을 잡아야하고
-- 연봉 컬럼이 조회되고
-- 묶어진 연봉 행들을 합산하여 내보낸다

-- 부서별로 급여 합산을 통계를 내주세요~
select depart_no, sum(salary) as depart_salary from employees where depart_no = 'dev001';
select depart_no, sum(salary) as depart_salary from employees where depart_no = 'dev002';
select depart_no, sum(salary) as depart_salary from employees where depart_no = 'dev003';
select depart_no, sum(salary) as depart_salary from employees where depart_no = 'dev004';
select depart_no, sum(salary) as depart_salary from employees where depart_no = 'dev005';
select depart_no, salary from employees order by depart_no;
-- 기본적인 예제들은 사용할 수 있지만 깊게 이해해서 사용해야한다
-- 문제점은 너무 비효율적으로 컬럼을 조회한다
-- 그래서 sql 문의 중복을 줄이기 위해 group by 가 등장했다

-- 위 내용을 group by 로 변환해보겠다
select depart_no, sum(salary) from employees group by depart_no;
-- 여기 사업소가 몇개야? 그외의 내용은 불필요하니깐 나중에는 필요한 정보만 조회하자  
-- 뒤로 갈 수록 복잡해진다

-- 그룹의 기준이 되는 컬럼과, 집계 컬럼 외의 다른 컬럼이 들어간다면?
-- 우리가 알고 싶은건 각 팀의 연봉 합산인데 성씨 컬럼이 들어가 뜬금이 없다;;
-- 성씨 컬럼은 각 그룹의 가장 첫 값을 보여준다, (마리아db 에서는 에러를 뱉어버린다!)
-- 다른 DB 에서는 이 경우 에러를 발생 시킬수도 있다
select depart_no, sum(salary), family_name from employees group by depart_no;

-- 부서별 급여 평균을 내보자 avg(column) 컬럼의 평균을 내준다 
select depart_no, 
concat(truncate(avg(salary)/10000, 0), " 만원") as salary_avg
from employees group by depart_no;

-- 팀이 한 30개 쫙 있으니깐 다 보기가 너무 힘들어
-- 부서들의 합계 급여가 천만원이상인 팀들만 알고 싶은데? where
-- group by 하면 where 를 사용할 수 없다

-- 9) HAVING
-- GROUP BY 에서 WHERE 처럼 조건을 주고 싶을때 사용한다
-- 합계 급여가 500만원 이상인 팀을 추출한다
-- HAVING 에서는 별칭을 사용하면 안된다 (total 대신 sum(salary)를 써야한다)
-- 오라클 계열에서는 에러를 뱉어내고, 마리아는 허용한다
-- 가급적이면 별칭을 쓰지말자!
-- 쿼리문을 서버에 던져보고 결과를 확인하는 작업이 꼭 필요하다
select depart_no, sum(salary) as total, family_name 
from employees group by depart_no having total > 5000000;
select depart_no , sum(salary) as total from employees
group by depart_no where total > 5000000;
-- 급여 합계가 500만원 이상인 부서들을 합계 급여가 높은 순으로 정렬하세요
select depart_no, sum(salary) as total 
from employees group by depart_no having total > 5000000 order by total desc;