데이터베이스 실습

1. 데이터베이스 실습 전북대학교 바이오메디컬공학부

2. INDEX • MySQL데이터베이스 생성 • SQL 데이터형(Data Type) • 학사 관리 예제 만들기

3. MySQL데이터베이스 생성

4. 데이터베이스 및 테이블 만들기- 학사(“haksa”) 데이터베이스 생성 작업 ①MySQL접속 - Windows 키 + R키를 클릭하고 아래 명령을 입력 - Enter password : 라는 문구가 나오면 설치 시 입력했던 암호를 입력

5. 데이터베이스 및 테이블 만들기- 학사(“haksa”) 데이터베이스 생성 작업 ②MySQL새로운 데이터베이스 생성 mysql> create database haksa;

6. 데이터베이스 및 테이블 만들기- 학사(“haksa”) 데이터베이스 생성 작업 ③생성된 Database 확인 mysql> show databases;

7. 데이터베이스 및 테이블 만들기- 학사(“haksa”) 데이터베이스 생성 작업 ④생성된 Database 사용하기 위해 데이터베이스 변경 mysql> use haksa;

8. 데이터베이스 및 테이블 만들기- 인사테이블(“insa”) 생성 및 데이터 입력 ⑤테이블 생성 및 데이터 입력 mysql > create table insa( -> number int(1) auto_increment, -> name char(8) not null, -> e_name char(4) not null, -> town char(6) not null, -> primary key(number) -> );

9. 데이터베이스 및 테이블 만들기- 인사테이블(“insa”) 생성 및 데이터 입력 ⑤테이블 생성 및 데이터 입력

10. 데이터베이스 및 테이블 만들기- 인사테이블(“insa”) 생성 및 데이터 입력 ⑤테이블 생성 및 데이터 입력 • 대표적으로 쓰이는 테이블 옵션 • Not null • Auto_increment • Primary key

11. 데이터베이스 및 테이블 만들기- 인사테이블(“insa”) 생성 및 데이터 입력 ⑥테이블 생성 및 데이터 입력 mysql > insert into insa values(‘1’, ‘홍길동’, ‘Hong’, ‘순천’); mysql > insert into insa values(‘2’, ‘제갈공명’, ‘Je’, ‘여수’); mysql > insert into insa values(‘3’, ‘순자’, ‘Soon’, ‘부산’); mysql > insert into insa values(‘4’, ‘이순신’, ‘Lee’, ‘여수’);

12. Commit/Rollback 작업 • Commit • - 변경된 데이터를 데이터베이스에 적용시킴 • Rollback • - 변경된 데이터를 취소시킴 • - 직전에 Commit이 수행된 시점까지 취소시킴

13. Commit/Rollback 작업 ① “INSA” 테이블 질의 mysql > select * from insa;

14. Commit/Rollback 작업 ②AutoCommit를 하지 않도록 설정 - MySQL은 명령어를 실행하면 자동으로 Commit를 하게 되어 있음 mysql > set autocommit = 0;

15. Commit/Rollback 작업 ③ “INSA”테이블의 내용 변경 : 번호 4번 도시(TOWN)을 한산도로 변경 mysql > update insa set town = ‘한산도’ where number = 4;

16. Commit/Rollback 작업 ④변경된 “INSA”테이블 질의 mysql > select * from insa;

17. Commit/Rollback 작업 ⑤변경된 데이터 복구작업 :Rollback mysql > rollback;

18. Commit/Rollback 작업 ⑥ “INSA” 테이블 질의 mysql > select * from insa;

19. Commit/Rollback 작업 ⑦ “INSA” 테이블 내용 변경 : TOWN이 여수인 데이터를 “대구”로 변경 mysql > update insa set town = ‘대구’ where town = ‘여수’;

20. Commit/Rollback 작업 ⑧ “INSA” 테이블 내용 데이터베이스 저장 : Commit mysql > commit;

21. Commit/Rollback 작업 ⑨변경된 데이터 복구작업 : Rollback(복구가 되지 않음) mysql > rollback;

22. Commit/Rollback 작업 ⑩ “INSA” 테이블 질의 mysql > select * from insa;

23. Savepoint/Truncate 작업 • Savepoint • - 변경된 지점(저장점)의 위치를 저장 • - INSERT, DELETE, UPDATE 작업 수행 후 Rollback to 저장점을 수행하면 그 위치까지 • 다시 복구 가능 • Truncate • - 데이터 대량 삭제

24. Savepoint/Truncate 작업 ① “INSA” 테이블 변경 작업 : 번호 2의 도시(TOWN)을 “여수”로 변경 mysql > update insa set town = ‘여수’ where number = 2;

25. Savepoint/Truncate 작업 ② Savepoint “AA” 지정 mysql > savepointaa;

26. Savepoint/Truncate 작업 ③ 번호 3번 행 삭제 : DELETE 작업 mysql > delete from insa where number = 3;

27. Savepoint/Truncate 작업 ④ “INSA” 테이블 질의 mysql > select * from insa;

28. Savepoint/Truncate 작업 ⑤ “INSA” 테이블 Savepoint “AA”까지 복구 mysql > rollback to aa;

29. Savepoint/Truncate 작업 ⑥ “INSA” 테이블 질의 mysql > select * from insa;

30. Savepoint/Truncate 작업 ⑦ “INSA” 테이블의 삭제 처리 mysql > truncate table insa;

31. Savepoint/Truncate 작업 ⑧ Truncate 작업 후 “INSA” 테이블 복구(복구가 되지 않음) mysql > rollback;

32. Savepoint/Truncate 작업 ⑨ “INSA” 테이블 질의 mysql > select * from insa; ⑩ Truncate 작업의 “INSA” 테이블 삭제 처리시 복구가 되지 않음

33. SQL 데이터형(Data Type)

34. SQL 데이터형 • 1) 숫자 데이터형 • - 정수 데이터형(INT) • : int데이터형은0, 음수, 양수를 저장 • - 실수데이터형(FLOAT) • : float 데이터형은 소수점을 포함하여 값을 저장 • 2) 문자 데이터형 • ① CHAR 데이터형 • : 1바이트에서 255바이트까지의 고정 길이 문자열을 저장 • : 정의된 저장 공간보다 입력 데이터가 짧으면 나머지 공간을 공백으로 채워지고, • 정의된 길이보다 길면 길이에 맞게 잘린 데이터가 입력됨 • : 테이블 생성시 저장할 데이터의 최대크기고 정의해야 데이터 손실 예방 가능

35. SQL 데이터형 2) 문자 데이터형 ② VARCHAR 데이터형 : 정의된 저장 공간보다 길면 에러 값을 리턴하게 됨 : 최대 255바이트까지 저장 가능함 메모 등의 다양한 길이의 데이터에 적절함 : 가변적인 길이의 문자열을 저장하기 때문에 문자열 저장 시 선호되는 데이터형 : 자료 구조의 원리로는 CHAR가 VARCHAR보다 검색 속도가 훨씬 빠름 ③ BLOB(Binary Large Object), TEXT 데이터형 : 65,535 이상의 거대한 텍스트 데이터를 저장할 때 사용 : BLOB는 검색 시 대소문자를 구분하고, TEXT는 대소문자의 구분 없이 검색 가능

36. SQL 데이터형 3) 날짜 데이터형 - SYSDATE라는 함수를 사용해서 현재 OS의 날짜를 조회할 수 있음 - select는 산술 계산의 결과나 날짜 등을 볼 수 있음 4) 바이너리(binary) 데이터형 ① RAW 데이터형 : 이진형 데이터를 255바이트까지 수용할 수 있으나 저장 공간의 제한점 때문에 많이 사용하지 않음 ② LONG RAW 데이터형 : 이진형 데이터를 2GB까지 수용 가능 ③ BLOB 데이터형 : 이진형 데이터를 4GB까지 수용 가능

37. NULL 값 • NULL 값 • - “값이 알려져 있지 않다” 또는 “값이 존재하지 않는다”라는 의미 • - NULL은 공백(space)으로 나타냄

38. 학사 관리 예제 만들기

39. 데이터베이스 및 사용자 계정 생성 • 학사 관리 예제 데이터베이스를 위한 데이터베이스와 사용자 계정 생성 • - 데이터베이스 이름 : haksa_database • - 사용자 계정 : haksa_admin • - 비밀번호 : 5678 show variables like ‘char%’; set character set euckr; or show create table 테이블명; alter table 테이블명convert to charseteuckr;

40. 학사 관리 예제 만들기 ①MySQL접속 - Windows 키 + R키를 클릭하고 아래 명령을 입력 - Enter password : 라는 문구가 나오면 설치 시 입력했던 암호를 입력

41. 학사 관리 예제 만들기 ②MySQL새로운 데이터베이스 생성 mysql > create database haksa_database;

42. 학사 관리 예제 만들기 ③생성된 Database 확인 mysql > show databases;

43. 학사 관리 예제 만들기 ④사용자 생성 및 권한 부여 mysql > grant all privileges on haksa_database.* to haksa_admin@localhost identified by ‘5678’ with grant option;

44. 테이블 생성 mysql –u haksa_admin –p haksa_database ① haksa_admin으로 haksa_database에 접속 - Windows 키 + R키를 클릭하고 아래 명령을 입력 - Enter password : 라는 문구가 나오면 설정했던 암호를 입력

45. 테이블 생성 210.117.139.241 ②table.sql 파일을 이용한 TABLE 생성 - 다운로드 받은 SQL파일을 C:\sql 이라는 폴더로 복사 mysql > \. C:\sql\table.sql

46. 테이블 데이터 삽입 ①data.sql 파일을 이용한 DATA 생성 mysql > \. C:\sql\data.sql

47. 질의(Query) 테이블 예제1_STUDENT 테이블로부터 성별이 남자인 각 학생의 학번, 이름, 영문이름, 학년, 주민등록번호를 영문이름 순서로 출력하라. (stu_no, stu_name, stu_ename, grade, id_num) mysql > select stu_no, stu_name, stu_ename, grade, id_num -> from student -> where substring(id_num, 8, 1) = 1 -> order by stu_ename; ※ substr(또는 substring) 함수 - 문자열의 일부를 반환 - select substring(str, pos[, len]); ex) mysql> select substring(‘language’, 5); -> uage mysql> select substring(‘language’, 3, 2); -> ng

48. 질의(Query) 테이블 예제1_STUDENT 테이블로부터 성별이 남자인 각 학생의 학번, 이름, 영문이름, 학년, 주민등록번호를 영문이름 순서로 출력하라. (stu_no, stu_name, stu_ename, grade, id_num)

49. 질의(Query) 테이블 예제2_학년이 1학년이고 성별이 남자인 각 학생의 학번과 이름을 출력하는데, 출력 순서는 학번 내림차순이다. (stu_no, stu_name) mysql > select stu_no, stu_name -> from student -> where grade = 1 -> and substring(id_num, 8, 1) = 1 -> order by stu_nodesc;

50. 질의(Query) 테이블 예제2_학년이 1학년이고 성별이 남자인 각 학생의 학번과 이름을 출력하는데, 출력 순서는 학번 내림차순이다. (stu_no, stu_name)