[DB] MySQL

DB

0 사용대상

• 디비는 연결과 관리만 하고
• 결국 사용자가 사용하는 것은 테이블임

1 권한

• Connect 권한 :: 접근 connect 권한, 세션권한
• Resource 권한 :: 리소스 생성, 수정, 삭제 할 수 있는 권한
• Admin 권한 :: 관리자 권한

2 설치

• https://dev.mysql.com/downloads/file/?id=474803 밑에 No thanks, just start my download. 클릭하면 다운로드 실행됨

• 설치과정은 아래 자바대장 블로그 참고 https://blog.naver.com/dgkim1007/221230144969

3 환경변수 설정

• 내컴퓨터 > 고급 > 시스템 변수
• MySQL_HOME :: C:\Program Files\MySQL\MySQL Server 5.7\bin
• Path :: %MySQL_HOME%; ~~

4 mysql 실행

• cmd에서 mysql -u root -p 를 실행하면
• 암호 입력하는 프롬프트 나오고 암호 입력하면 들어감.

5 show databases; // 가지고 있는 데이터베이스 전체를 보여준다

6 use mysql; // mysql db를 선택함.

mysql :: db에서 권한 설정 등을 관리하는 db

7 show tables; // DB안에 있는 테이블을 보여줌

8 select host, user from user; // user 테이블에서 host, user 조회

localhost : mysql.session / mysql.sys / root

9 create database aiappdb; // DB 생성

use aiappdb; // aiappdb 선택

10 select database(); // 현재 사용하고 있는 db를 보여줌

11 데이터 타입

• varchar 2 – 오라클만 사용
• varchar – 다른 db에서 사용, 들어온 만큼만 확보함
• char – 정해진 자리를 차지함 (고정길이) –> 검색 속도가 빠름
• tiny
• int (numer) – 오라클에서 number / MySql에서는 int 타입 사용
• glub – 이미지 저장
• date

12 숫자 vs 문자 – 저장하는 방식이 다름

• 숫자는 기억공간에서 뒤로 부터 저장하고
• 문자는 기억공간에서 앞에서부터 저장함 –> 숫자도 연산을 필요하지 않은 경우는 문자로 저장한다.

13 Primary key == 유일키

key :: primary, unique, foriegn (relation 맺을때, 제약을 걸 때),

14 정규화 == 중복 칼럼을 제거함, 최소화

보조 테이블에서 입력할 때 주 테이블에 없는 데이터를 넣으면 안됨. 이때 관계를 맺음으로 제약을 걸어줌 (foreign 키로 제약을 설정함)

견적서 넣을 때 외래키를 넣지만 개발할 때는 외래키를 뺌 (데이터 넣기 어려움) 왠만한 것은 프로그램상 jQuery에서 해결함.

15 E-R Diagram

– 외래키가 설정되어야 연결됨

16 PK

primary key를 가지는 것을 원칙 없는 경우 sequece index로 pk를 줌

PK 키 조건 너무 짧아도 안되고 길어도 안됨 아이디를 만들어 썻는데
최근에는 id를 따로 안쓰고 email로 아이디 처리 함 또 pk키가 가능한 녀석은 후보키라고 함. – unique로 설정가능 ex) 별칭 도 unique로 설정

pk와 unique 차이는 null unique는 null값을 가질 수 있음.

외래키 조건 :: 부모키는 유일한 값을 가지고 있어야 함.

17 member table 생성

create table member(
	email varchar(20) primary key,
	name varchar(20),
	password varchar(100),
	phone varchar(20),
	memlevel int(1),
	photo varchar(100) 
);

18 desc member; // 테이블 구조가 보임 – > select 시 이 순서대로 출력됨

19 select // 테이블 내용이 보임

20 alter table member // 테이블 칼럼 변경 add + drop + change + desc 조회

alter table member add age int(3) default 20;
alter table member drop primary key; //기본키 삭제
alter table member add primary key(email); // 기본키 추가
alter table member change name chname varchar(20); // 칼럼 변경
alter table member add unique key(name); // 유니크 키 선언
alter table member change name name varchar(20) not null; // not null 조건 추가
alter table member drop name;
alter table member add name varchar(20) after email; // 이메일 뒤어 넣어라

21 테이블 로(레코드) 추가 삭제 변경 조회 insert + delete + update + select

insert into member( 칼럼명 ...) valuse ( 값들...); // 필요한 것만 골라서, 갯수가 맞아야 함. -- 많이 사용
insert into member valuses ( 값들...); // 이 경우는 정의된 필드 값을 모두 넣어줘야 함.

insert into member (email,name) values ('aaa@a.com','김한국');
insert into member (email,name,age) values ('abc@a.com','김한국',25);
insert into member values ('bbb@b.com', 'Kar','111','photo00','3');
-- ERROR 1136 (21S01): Column count doesn't match value count at row 1
insert into member values ('bbb@b.com', 'Kar','111','photo00','3','images');
create table cpmember as select * from member; // 테이블 카피, 키는 카피 안됨. 내용과 구조만 파키됨
drop table cpmember;
create table cpmember as select email, name from member; // 테이블 부분 카피
insert into cpmember values ('aaa@a.com','박미국');
alter table cpmember add primary key(email); --> 중복 에러 뜸 
delete from cpmember where email = 'aaa@a.com';
update member set name='박미국', phone = '010-111-2222' where email='bbb@b.com';

22 제약조건 확인

select * from information_schema.table_constraints where table_name = ‘테이블이름’;

23 완전무결성 / 위치투명성

완전무결성 :: 결함이 있으면 db에 들어가지 않음, 조건에 충족해야 insert가능 위치 투명성 :: 어느 위치든지 사용자, pw, 드라이버만 맞으면 연결됨

24 한글 설정

• /etc/mysql/my.cnf 파일 변경하기

현재 Character-Set 확인하기

• show variables like ‘c%’
• status

https://nesoy.github.io/articles/2017-05/mysql-UTF8

25 롤백이 없이 자동 커밋됨

롤백을 사용하기 위해서는 MySQL의 기본 기능인 AUTOCOMMIT 기능을 비활성화해야 함.

select @@autocommit; SET AUTOCOMMIT = 0; // [ 0 = false, 1 = true ]

참고 :: http://blog.naver.com/PostView.nhn?blogId=minki0127&logNo=220783883574&redirect=Dlog&widgetTypeCall=true

26 Result Set (rs) / where 조건절

회사에서 창고에 가서 필요한 물품을 요청하면 관리자가 리스트를 가지고 가서 챙겨 옴 이렇게 챙겨 온 것을 Result Set이라 함 Result Set (Row 단위)–> 자바에서 클래스로 담는다 (rs 닫기 전에) SQL 던진 사람은 Result Set 의 Row 단위가 1개인지 n개인지는 알아야 함. (키 값으로 알 수 있음)

• pk 키로 조건을 던지면 결과가 1개 또는 0개
• pk 가 아닌 조건은 결과가 n개 또는 0개 가능

27 table 실습

create table student (no varchar(6) primary key, name varchar(20));
create table dept (no varchar(4) primary key, name varchar(20));
alter table student add deptno varchar(4);

insert into student values ('210101','김진영','1001');
insert into student values ('210102','나인혁','1002');
insert into student values ('210103','양희원','1003');
insert into student values ('210104','윤지우','1003');
insert into dept values ('1001','컴퓨터공학');
insert into dept values ('1002','정보통신공학');
insert into dept values ('1003','전자회로공학');

28 조인 연습 alias 붙이기

select A.no, A.name, deptno, B.name from student A, dept B where A.deptno = B.no; // 조인할 때 두 관계가 1:N의 방향을 정확히 인지해야 함. // 개발자들은 결과가 무엇인지 이미 예상을 한다. 결과를 모르고 프로그램을 짜면 잘됐는지 안됐는지 모름, – 개발자 본인이 검증해야 한다.

29 참고 : 조인 및 다중 테이블 연산

[MySQL] “DB” 조인(Join)과 다중 테이블 연산

조인과 다중테이블 연산 ​ 1) 다중 테이블이란

다중 테이블이란 데이터베이스에 테이블이 하나 이상 존재하는걸 말한다.

• 다중테이블에서 원하는 데이터들을 모두 얻으려 할 때에는, 조인을 사용하여 테이블을 연결하면 많은 도움이 된다.

2) 카티션 조인

내부 조인의 한 형태, 크로스 조인이라고도 한다.

• 한 테이블의 모든 행과 다른 테이블의 모든행을 짝지어 반환한다.
• 내부 조인은 쿼리의 조건에 따라 일부 결과 행이 제거된 크로스 조인이다.
• 내부조인의 종류로는 동등조인, 비 동등조인, 자연조인이 있다. ​ 3) 동등 조인 같은지를 테스트하는 내부조인 ON 이나 Where절을 이용하여 ‘=’ 연산자를 이용하여 결과 테이블을 도출해 낸다. ​ 4) 비동등 조인 동등조인과 반대로 같지않은 모든 행들을 반환합니다. 표현은 ON, Where 절에 ‘<>’ 를 사용합니다.

5) 자연조인

“두 테이블에 같은 이름의 열”이 있을 때에만 동작합니다. 표현은 Select Table1.컬럼명, Table2.컬럼명 From Table1 “Natural Join” Table2;

서브쿼리 1) 서브쿼리

서브쿼리는 쿼리안의 쿼리이므로 “내부 쿼리“라고도 한다. 바깥쪽 쿼리를 “외부 쿼리“라 합니다.

• 데이터베이스에 하나 이상의 질문을 해야 할때 쿼리안의 쿼리, 서브쿼리를 사용합니다.
• 서브쿼리를 사용하면 데이터의 중복을 피할 수 있고, 쿼리를 좀 더 동적으로 사용 가능합니다.
• 서브쿼리는 비상관 서브쿼리와 상관 서브쿼리가 있습니다.

​ 2) 비상관 서브쿼리

외부쿼리의 어떤 것도 참조하지 않고 단독으로 사용되는 것을 말합니다. 비상관 서브쿼리는 IN, NOT IN을 사용하여 값이 서브쿼리에서 반환된 집합의 원소인지를 확인합니다.

3) 상관 서브쿼리

내부쿼리의 값이 결정되는데 외부쿼리에 의존하는 것을 말한다. EXISTS, NOT EXISTS를 사용합니다.

외부 조인 1) 외부 조인

외부조인은 조인되는 테이블 중 하나의 모든 행을 다중 테이블에서 일치하는 정보와 함께 반환됩니다.

• 내부 조인과 다른 점은 외부조인의 결과가 NULL 값이면 Null을 반환한다는 것입니다.
• 외부조인에는 왼쪽 외부조인과, 오른쪽 외부조인이 있으면, 그 차이는 FROM 뒤에 테이블이 나오는 순서에 따라 달라집니다.

​ 2) 셀프 조인

셀프 조인이란 자기 자신의 테이블을 참조하는 것 마치 같은 정보를 가진 테이블이 2개 있는 것 처럼 쿼리를 내보낼 수 있다.

​ 서브쿼리와 조인의 차이 ​

1. 둘다 바꿔서 사용할 수 있다.
2. 조인은 결과에 여러 테이블의 열이 필요할 때 사용한다.
3. 서브쿼리는 집계 값을 다룰 때 더 유용하게 사용할 수 있다.

[출처] [MySQL] “DB” 조인(Join)과 다중 테이블 연산

작성자 공대생

자바에서는 alter 같은 DDL,DCL 명령어 사용X, 트랜잭션 명령만 실행함 (insert,update,delete, select)

30 사용자 계정 생성, 권한 부여

// 사용자 계정 생성 use mysql; create user ‘scott’@localhost identified by ‘tiger’; select host,user from user;

// 권한 부여 grant all privileges on aiappdb.* to scott@localhost identified by ‘비밀번호’; flush privileges; // 변경된 내용을 메모리에 반영(권한 적용)

// 권한 보기 show grants for userid@localhost;

// 권한 삭제 revoke 권한 on DB명.테이블명 from 사용자ID;

// 사용자 계정 삭제 drop user userid@localhost;

참고 :: https://nickjoit.tistory.com/144

31 사용자 권한 계속

create database testdb; grant all privileges on testdb.* to ‘test’@’%’ identified by ‘1111’ with grant option; // 디비 권한을 주면서 사용자 계정을 만들어 줌. (디비 권한 + 사용자 계정 묶어서 관리함)

% 의미는? host에 ‘200.100.%’ 로 하면 IP주소가 200.100.X.X 로 시작되는 모든 IP에서 원격 접속을 허용한다는 의미 host에 ‘200.100.100.50’ 으로 하면 IP주소가 200.100.100.50 인 곳에서만 원격 접속을 허용한다는 의미 // 접근 허용 사이트를 지정해 주는 것.