[필기정리]Day54-2 - 데이터베이스(MySQL) 이론 및 실습문제

# 학사("haksa") 데이터베이스 생성 작업

1. MySQL 접속하기

mysql -u root -p

1234 // 비밀번호

 

2. MySQL 새로운 데이터베이스 생성

create database haksa;

 

3. 생성된 Database 확인

show databases;

 

4. 생성된 Database 사용하기 위해 데이터베이스 변경

use haksa;

 

# 인사테이블("insa") 생성 및 데이터 입력

1. insa 테이블 만들기

create table insa(
bunho int auto_increment,
name char(8) not null,
e_name char(10) not null,
town char(6) not null,
primary key(bunho)
);

 

2. 데이터 삽입

insert into insa values(1, '홍길동', 'Hong', '순천');
insert into insa values(2, '제갈공명', 'Je', '여수');
insert into insa values(3, '순자', 'Soon', '부산');
insert into insa values(4, '이순신', 'Lee', '여수');
insert into insa(name, e_name, town) values('배트맨', 'batman', '고담');

 

# Commit / Rollback 작업

- Commit : 변경된 데이터를 데이터베이스에 적용시킨다.

- Rollback : 변경된 데이터를 취소시킨다. 직전에 Commit이 수행된 시점까지 취소시킨다.

 

1. "INSA" 테이블 질의

select * from insa;

 

2. 주의사항으로 MySQL은 명령어를 실행하면 자동(Default)으로 Commit를 하게 되어 있다.

   우선 AutoCommit를 하지 않도록 한다.

set autocommit = 0;

 

3. "insa"테이블의 내용 변경 : 번호 4번 도시(TOWN)을 한산도로 변경

update insa
set town = '한산도'
where bunho = 4;

 

4. 변경된 "insa" 테이블 질의

select * from insa;

 

5. 변경된 데이터 복구작업 : Rollback

rollback;

 

6. "insa" 테이블 내용 변경 도시(town)가 '여수'인 데이터를 '대구'로 변경

update insa
set town = '대구'
where town = '여수';

 

7. "insa" 테이블 내용 데이터베이스에 저장 : Commit

commit;

 

8. 변경된 데이터 복구작업 : Rollback(복구가 되지 않음)

rollback;

 

9. "insa" 테이블 질의

select * from insa;

 

# Savepoint/Truncate 작업

Savepoint는 변경된 지점(저장점)의 위치를 저장한다.

Savepoint로 저장점을 저장하고,

INSERT, DELETE, UPDATE작업을 수행 후 Rollback to 저장점을 수행하면

그 위치까지 다시 복구시킬수 있다.

 

1. "INSA" 테이블 변경 작업 : 번호 2의 도시(TOWN)을 "여수"로 변경

update insa
set town = '여수'
where bunho = 2;

 

2. Savepoint "aa" 지정

savepoint aa;

 

3. 번호 3번 행 삭제 : DELETE 작업

delete from insa
where bunho = 3;

 

4. "insa" 테이블 질의

select * from insa;

 

5. "INSA" 테이블 Savepoint "aa" 까지 복구

rollback to aa;

 

6. "INSA" 테이블 질의

select * from insa;

 

7. Truncate 작업 : "insa" 테이블의 삭제 처리(모든 행이 삭제 처리됨)

truncate table insa;

 

8. Truncate 작업 후 "insa" 테이블 복구(복구가 되지 않음)

rollback;

// Tip : truncate vs delete 차이점

- truncate :  auto commit

- delete : commit 따로 해줘야 함 

∴ 이미 truncate로 auto commit 되었기 때문에 테이블이 복구되지 않는다

 

9. "insa" 테이블 질의

select * from insa;

 

※ Truncate 작업의 "insa" 테이블 삭제 처리 시 복구가 되지 않는다.

 

# SQL 데이터형(Data Type)

1. 숫자 데이터형

① 정수 데이터형(INT) - INT

② 실수 데이터형(FLOAT) - FLOAT(N,M)

 

2. 문자 데이터형(CHAR, VARCHAR, BLOB)

① char 데이터형 - CHAR(n) ex) 이름, 여권번호, 주민번호 등

    1바이트에서 255바이트까지의 고정 길이 문자열을 저장하고,

    정의된 저장공간보다 입력 데이터가 짧으면 나머지 공간은 공백(SPACE)으로 채워진다.

    정의된 길이보다 입력 데이터가 길면 길이에 맞게 잘린 데이터가 입력된다.

    그러므로 테이블 생성 시 저장할 데이터의 최대크기로 정의해야만 데이터의 손실을 막을 수 있다.

 

② VARCHAR 데이터형 - VARCHAR(n) ex) 제품명, e-mail, 주소 등

    CHAR 데이터형과 유사하나 정의된 저장공간보다 긴 문자열이 입력되면

    CHAR 데이터형에서는 에러를 발생시키지 않고 초과되는 데이터를 잘라서 입력하지만

    VARCHAR에서는 에러 값을 리턴 한다.

   최대로 정의할 수 있는 데이터의 길이는 255바이트까지 저장할 수 있고,

   메모 등의 다양한 길이의 데이터에 적절하고,

   가변적인 길이이의 문자열을 저장하기 때문에, 문자열을 저장하기 위하여 선호되는 데이터형이다.

   하지만 자료 구조의 원리로 볼 때는 CHAR가 VARCHAR보다 검색 속도가 훨씬 빠르다.

 

③ BLOB, TEXT 데이터형 

    BLOB와 TEXT는 65,535 이상의 거대한 텍스트 데이터를 저장할 때 사용하면 된다.

    BLOB는 검색 시 대소문자를 구분하고,  ex) 이미지 등

    TEXT는 대소문자의 구분이 없이 검색할 수 있다. ex) 게시판 본문, 논문 등

 

3. 날짜 데이터형 

   MySQL은 날짜 및 시간 데이터를 저장하기 위해서 Date 데이터형을 제공한다.

   사용자들은 SYSDATE이라는 함수를 사용해서 현재 OS의 날짜를 조회할 수 있다.

select now();

 

4. 바이너리(binary) 데이터형

   MySQL은 음성, 화상(이미지), 동영상과 같은 데이터를 저장하기 위해서

   바이너리 데이터형으로 RAW와 LONG RAW 데이터형을 사용하고

   제약점으로는 내장함수를 사용할 수 없다. ex) if, substr

 

① RAW 데이터형

    이진형 데이터를 255바이트까지 수용할 수 있으나

    저장 공간의 제한점 때문에 많이 사용하지 않는다.

 

② LONG RAW 데이터형

    이진형 데이터를 2GB까지 수용할 수 있다.

 

③ BLOB 데이터형

   이진형 데이터를 4GB가지 수용할 수 있다.

 

# 데이터베이스 및 사용자 계정 생성

1. MySQL 접속하기

mysql -u root -p

1234 // 비밀번호

 

2. MySQL 새로운 데이터 베이스 생성

create database haksa_database;

 

3. 생성된 Database 확인

show databases;

 

4. 사용자 생성 및 권한 부여 (haksa_admin에 비밀번호 1234로 모든 권한)

CREATE USER 'haksa_admin'@'%' IDENTIFIED BY '1234';
GRANT ALL PRIVILEGES ON haksa_database.* TO 'haksa_admin'@'%' WITH GRANT OPTION;

# 구(MySQL 5.6 이하)
grant all privileges on haksa_database. * to haksa_admin@localhost identified by '1234' with grant option;

- grant : 권한을 부여하는 명령어

 

# 위에서 생성한 haksa_admin으로 haksa_database에 접속하자.

1. MySQL 접속하기

mysql -u haksa_admin -p haksa_database

1234 // 비밀번호

 

# 테이블 생성

   테이블 생성 방법은

   MySQL을 이용한 SQL 명령어(Command)방식과 MySQL Query Browser 방식이 있다.

 

MySQL에서 파일을 실행시키는 명령은 아래와 같다.

\. [path]\[filename]

 

- 학사관리 테이블 생성(SQL 명령어 사용)

1. MySQL 접속하기

mysql -u haksa_admin -p haksa_database

 

2. table.sql 파일을 이용한 데이터 삽입

SQL 파일을 c:\sql 이라는 폴더로 복사한다.

\. c:\sql\table.sql

 

# 테이블 데이터 삽입

1. MySQL 접속하기

mysql -u haksa_admin -p haksa_database

 

2. data.sql 파일을 이용한 data 삽입

\. c:\sql\data.sql

 

# 추가 예제

1. STUDENT 테이블로부터 성별이 남자인 각 학생의 학번, 이름, 영문이름, 학년, 주민등록번호를

   영문이름 순서로 출력하라.

select stu_no, stu_name, stu_ename, grade, id_num from student where substring(id_num, 8, 1)=1 order by stu_ename;

 

2. 학년이 1학년이고 성별이 남자인 각 학생의 학번과 이름을 출력하는데,

   출력 순서는 학번 내림차순이다.

select stu_no, stu_name from student where grade = 1 and substring(id_num,8,1)=1 order by stu_no desc;

 

 

3. 교과목 테이블에 관한 모든 정보를 출력하라

select * from subject;

 

4. 교과목 중 운영체제의 생성년도를 2006년으로 변경하라.

update subject
set create_year = '2006'
where sub_name = '운영체제';

 

5. 교과목 테이블에서 교과목코드, 교과목명, 교과목 영문이름, 생성년도를 출력하라.

select sub_code, sub_name, sub_ename, create_year from subject;

 

// 삭제처리(DELETE) 작업은 테이블의 내용을 제거할 때 행(ROW) 단위로 이루어진다.

   만약에 "SAMPLE"이라는 테이블의 모든 행을 삭제한다면 다음과 같다.

DELETE FROM SAMPLE;

 

// 앞의 예제는 "SAMPLE" 테이블의 모든 행을 삭제처리 하나 테이블 자체가 없어진 것은 아니다.

   테이블의 명세표, 인덱스, 부여된 권한 등 환경테이블 정보 자체를 완전히 없애버릴 때는

   DROP 명령어를 다음과 같이 사용한다.

DROP TABLE SAMPLE;

 

6. 과목명(SUB_NAME)이 UML인 과목을 삭제하라.

delete from subject where sub_name = 'UML';
select * from subject;

 

7. 교과목 중 운영체제의 생성년도를 2002년으로 변경하라.

update subject set create_year = '2002' where sub_name = '운영체제';

 

8. 교과목 테이블에 교과목코드(4007), 교과목명(UML), 교과목영문이름(Unified Modeling Language),

   생성년도(2005)인 새로운 행을 삽입하라.

insert into subject values('4007', 'UML', 'Unified Modeling Language', '2005');

 

# 뷰(Views) : select한 데이터를 가상의 테이블형으로 만들어주는 것

  뷰는 다음과 같은 상황에서 주로 사용된다.

 - 반복되는 명령문이나 루틴(routine)을 간단히 사용하고자 할 때,

 - 테이블의 출력 방법을 재구성하고자 할 때,

 - 여러 단계에서 select 명령문이 사용될 때,

 - 데이터를 보호하고자 할 때

   ㄴ ex) 주민번호 등을 민감한 데이터를 제외한 이름, 주문번호 등 대표적인 데이터를 보여주고 싶은 경우

 

9. 학적 테이블의 학번, 이름, 출생년도, 나이를 출력하라.

select stu_no, stu_name, birth_year "출생년도", year(now()) - birth_year+1 "나이" from student;

 

10. 학적 테이블의 학번, 이름, 나이로 구성된 AGES 뷰 테이블을 생성하라.

create view ages(stu_no, stu_name, age) as select stu_no, stu_name, year(now())-birth_year+1 from student;

 

# 보안 설정

- root 사용자의 데이터 보안

1. root 패스워드 설정하기

use mysql;

# 구( MySQL 5.6 이하)
update user set password=password('12345') where user='root';

flush privileges;

 

// MySQL flush privileges 명령어

   INSERT나 UPDATE, DELETE문을 이용해서

   MySQL의 사용자를 추가,삭제하거나, 사용자 권한 등을 변경하였을 때,

   MySQL에 변경사항을 적용하기 위해서 사용하는 명령어가 flush privileges이다.

 

   이 flush privileges은 grant 테이블을 reload 함으로 변경사항을 바로 적용해주는 명령어인데,

   INSERT, UPDATE와 같은 SQL문이 아닌 grant 명령어를 사용해서 사용자를 추가하거나 권한 등을 변경하였다면

   굳이 실행할 필요가 없다.

 

// 추가설명 - user 테이블의 데이터를 update, insert와 같은 SQL문을 사용했을 시

                  테이블에 직접 반영이 되지 않기 때문에 flush privileges를 통해 직접 반영 시켜줘야 한다.

 

2. 슈퍼 유저[root] 패스워드 변경

# mysql 8.0
ALTER user 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '변경할 비밀번호';

alter user 'root'@'localhost' identified with mysql_native_password by '12345';

// Tip - alter : 테이블의 구조를 변경할 때 쓰는 명령어

 

# 구(MySQL 5.6 이하)

- set password 사용하기

  set password 문을 사용하여, root의 패스워드를 12345로 변경하는 예제이다.

  flush privileges; 명령을 사용하지 않아도 바로 적용이 된다.

set password for root@localhost = password('12345');

// Tip - set : 데이터베이스 내 환경설정을 변경하는 명령어 

 

- update문으로 user 테이블 수정하기

  update문을 사용하여 mysql 시스템 데이터베이스 안의 user 테이블을 수정하는 것으로,

  flush privileges; 명령을 주어야 적용이 된다.

update user set password=password('12345') where user='root';
flush privileges;

 

- password() 함수

select password('12345');

 

# 사용자 생성 및 권한 부여

  MySQL에서 사용자를 생성하는 방법은 command 방법과 GUI 방법(MySQL Administrator)이 있다.

 

- command 방법

1. create 문으로 user 생성하기

create user 사용자명 identified by '비밀번호';

 

① create 문을 사용하여 새로운 사용자 "choi"를 암호(password) "choi123"으로 생성해 보자.

create user choi identified by 'choi123';

 

② create 문을 사용하여 새로운 사용자 "lee@localhost"를 암호(password) "lee123" 으로 생성해 보자.

create user lee@localhost identified by 'lee123';

 

③ "user" 테이블에서 새로이 생성된 사용자를 확인하기 위해 호스트명, 사용자, 비밀번호를 출력해 보자.

# MySQL 5.7 이상
select host, user, authentication_string from user;

# MySQL 5.6 이하
select host, user, password from user;

 

검색된 "user" 테이블을 확인 해 보면 사용자 "choi"와 "lee"는 host 이름이 서로 다른 것을 확인할 수 있다.

사용자 "choi"의 경우는 host명이 "%"이므로 localhost 아닌 원격에서 접속이 가능하고

사용자 "lee"는 host명이 localhost로 접속이 가능하다.

 

즉, 사용자 'choi'는 원격에서 네트워크를 이용하여 데이터베이스를 이용할 수 있으며

사용자 'lee'는 로컬 컴퓨터에서만 데이터베이스를 사용할 수 있다.

 

2. 사용자 권한 부여

grant all privileges on 데이터베이스명.* to 사용자명;

grant 부여할 권한 SQL 명령문 on 데이터베이스명. * to 사용자명;

 

①

grant select, insert, update, delete on haksa. * to lee@localhost;

①은 사용자 'lee'에게 'haksa' 데이터베이스를 select, insert, update, delete 할 수 있는 권한을 부여하였고, 

 

② 

grant all privileges on haksa. * to choi;

②은 사용자 'choi'에게 'haksa' 데이터베이스를 관리할 수 있는 모든 권한을 부여한 경우이며,

 

③ 

grant all privileges on *.* to lee@localhost;

③은 사용자 'lee'는 모든 데이터베이스를 모든 권한을 가지고 관리할 수 있도록 권한을 부여하였으므로

DBA의 권한을 부여한 것과 같다.

 

3. 사용자 생성 및 권한부여를 동시에 처리(MySQL 5.6 이하 참고)

(

- 일반형식         

grant priv_type [(column_list)][, priv_type[(column_list)]...]
on tbl_name |*|*.*| db_name.* to user_name [identified by 'password']
[, user_name [identified by 'password']...] [with grant option]

 

- 형식

①

grant all privileges on DB명. * to DB계정명@localhost identified by '비밀번호' with grant option;

①은 localhost에서 'DB계정명' 이라는 사용자를 등록한 경우이고, 

 

②

grant all privileges on DB명. * to DB계정명 identified by '비밀번호' with grant option;

 ②는 localhost 아닌 원격에서 접속시 호스트 부분으로 해준 경우이다.

 

ex) 

grant all privileges on haksa. * to kim@localhost identified by 'kim123' with grant option;

grant all privileges on *. * to han identified by 'han123' with grant option;

 

- "user" 테이블에서 새로이 생성된 사용자를 확인하기 위해 호스트명, 사용자, 비밀번호를 출력해 보자.

# MySQL 5.7 이상
select host, user, authentication_string from user;

# MySQL 5.6 이하
select host, user, password from user;

// Tip -  authentication_string : 보안화된 문자열 ex) 비밀번호

 

4. 사용자 권한 회수

- 일반 형식

revoke priv_type [(column_list)][,priv_type[(column_list)]...]
on tbl_name |*|*.*|db_name. * from user_name [, user_name ...]

// Tip : grant vs revoke 차이점

- grant : 권한을 주는 명령어

- revoke : 권한을 뺏는 명령어

 

- 형식

revoke SQL 명령문 on DB명. * from '해당유저이름';

               

 ex)

① 사용자 "choi"을 "haksa" 데이터베이스에서 select 할 수 있는 권한을 회수해보자.

revoke select on haksa. * from choi@'%';

② 사용자 "lee"을 "haksa" 데이터베이스에서 select, update할 수 있는 권한을 회수해보자.

revoke select, update on haksa. * from lee@'localhost';
flush privileges;

 

+ 사용자 "choi"와 "lee"의 권한이 회수되었는지 확인해 보자.

select host, db, user, select_priv, update_priv from db;

 

5. 사용자 삭제

①

drop user '해당유저이름';

①은 "user" 테이블과 "db" 테이블에서 완전히 해당유저를 삭제하는 경우이고,

 

② 

delete from user where user='해당유저이름';

②는 "user" 테이블에서 사용자를 삭제하는 경우이고,

 

③ 

delete from db where user='해당유저이름';

③은 "db" 테이블에서 해당유저에게 부여된 데이터베이스의 권한을 삭제한 경우이다.

 

- 생성된 사용자가 데이터베이스를 권한을 알 수 있는 "db" 테이블의 정보를 알아보자.

desc db;

// Tip - desc : 해당 테이블의 개괄적인 정보를 알려주는 명령어 (description) 

 

- 새로이 생성된 사용자의 데이터베이스 권한을 확인하기 위해

  "db" 테이블에서 호스트명, DB명, 사용자, select 권한만을 출력해보자.

select host, db, user, select_priv from db;

 

- 사용자 "kim"과 "lee"를 삭제처리 해보자.

drop user kim@localhost;

delete from user where user='lee';

delete from db where user='lee';

 

// Tip : ZEROFILL 속성 

         숫자 컬럼에 INT를 선언할 경우 자동적으로 INT(11)이 입력된다. 
         INT형 옆의 11이란 숫자는 입력된 값의 자릿수를 의미한다. 
         해당 값은 ZEROFILL (이하 ZF) 속성이 지정됐을 경우에만 영향을 받는다. 
         즉, ZF 속성을 사용하지 않는다면 의미가 없다. 

        그렇다면 ZF의 기능은 무엇일까? 
        입력된 값의 자릿수를 일관되게 맞추려는 목적으로 사용된다. 
        INT(5) 약 해당 컬럼에 입력된 값의 자릿수가 
        지정된 자릿수보다 작다면 좌측부터 0으로 채워진다.
        만약 지정한 자릿수보다 크다면 아무런 영향을 받지 않는다. 

 

// Tip : dev.mysql.com/doc/refman/8.0/en/

MySQL :: MySQL 8.0 Reference Manual

 

book4.sql

0.00MB

data.sql

0.01MB

diplomas.sql

0.00MB

table.sql

0.00MB

webdb.sql

0.00MB