[0904]MySQL

** MySQL

1. 사용 가능한 데이터베이스 확인

show databases;

2. 데이터베이스 사용

use 데이터베이스이름;

3. 데이터베이스 생성

create database 데이터베이스이름;

4. 데이터베이스 삭제

drop database 데이터베이스이름;

5. 테이블 생성 : 

create table 테이블이름( 컬럼이름 자료형 제약조건 ... 테이블 제약조건)옵션;

6. 테이블 구조 확인 : 

desc 테이블이름;

7. 테이블 데이터 확인 : 

select* from 테이블이름;

8. 테이블의 컬럼 추가 : 

alter table 테이블명 add 새로운 컬럼명 컬럼타입 [first 또는 after 컬럼명];

9. 테이블 컬럼을 삭제 :

alter table 테이블이름 drop 삭제할 컬럼이름;

10. 테이블의 컬럼이름 변경 :

alter table 테이블이름 change 이전 컬럼명 새로운 컬럼명 컬럼 타입;

11. 테이블의 컬럼 자료형 변경 :

alter table 테이블명 modify 컬럼이름 새로운 자료형;

- 동일한 형식의 자료형으로만 변경이 가능하며 자릿수를 늘리는 것은 관계없지만 줄일 때 데이터가 변경될 수 있습니다.(되돌릴 수 없어서 정말 조심해야 한다)

- ddl은 한번 수행되면 되돌릴 수 없다. alter, create, drop

12. 테이블을 삭제 :

drop table 테이블이름;

13. 데이터 삽입

- auto_increment로 설정된 컬럼은 데이터를 삽입하지 않아도 일련번호 형태로 자동으로 삽입됩니다.

insert into 테이블이름(컬럼이름 나열) values(값을 나열); 또는 insert into 테이블이름 values(값을 나열[각주:1]);

- date는 문자열 형태로 바로 대입이 가능합니다.

'yyyy-mm-dd'

14. 데이터 수정

update 테이블이름 set 수정할 내용 [where 조건];

- where 절이 없으면 테이블의 데이터 전체를 수정합니다.

- 수정할 내용이 여러 개 이면 ,를 이용해서 나열하면 됩니다.

15. 데이터 삭제

delete from 테이블이름 [where 조건];

- 조건이 없으면 테이블의 데이터 전부 삭제

16. 트랜잭션 제어어(TCL)

- 현재까지 작업 내용을 원본에 반영

commit;

- 현재까지 작업 내용을 취소

rollback;

- 접속 프로그램을 정상적으로 종료한 경우, DDL을 수행한 경우(create, alter, drop), DCL(grant, revoke : 주는것, 회수하는것)의 정상 수행

auto commit;

- 접속 프로그램이 비정상적으로 종료된 경우

auto rollback;

- MySQL은 default로 테이블을 만들면 Engine이 MyISAM으로 설정되는 경우가 있어서 commit 과 rollback이 동작을 안 할 수 있습니다.

- 트랜잭션을 사용할 것 같으면 테이블을 생성할 때 engine 옵션을 innodb로 설정해야 합니다.

---

** 데이터 조회

1. 테이블의 모든 컬럼을 조회 :

select * from 테이블이름;

- 조회 구문에서는 select 와 from절은 생략이 안됩니다.

- 테이블 이름에 해당하는 테이블에서 모든 컬럼의 값을 가져옵니다.

2. 특정 컬럼만 추출해서 조회

select 컬럼이름 나열 from 테이블이름;

3. 컬럼의 데이터 중복을 제거

distinct

- distinct는 select 절에서 한번만 사용 가능합니다.

- distinct 뒤에 2개 이상의 컬럼 이름이 나열되면 2개 이상의 컬럼의 값이 모두 일치하는 경우만 제외됩니다.

4. order by

- 데이터를 정렬하기 위한 절

- select 구문의 가장 마지막에 수행

- 데이터베이스는 컬럼의 순서나 행의 순서가 없습니다.

∵데이터베이스는 해싱을 이용해서 데이터를 저장하기 때문에 순서와는 무관하게 저장됩니다.

∴ 2개 이상의 행을 조회하는 경우는 적절한 조건으로 데이터를 정렬해서 조회하는 것이 좋습니다.

order by 정렬할 컬럼이름이나 수식 [asc | desc], 정렬할 컬럼이름이나 수식...

- asc는 오름차순 정렬이고 desc는 내림차순 정렬인데 생략하면 asc이다.

5. where

- from 다음에 수행합니다.

1) 연산자

>, >=, <, <=, =, !=(<>) and, or, not in(데이터 나열)

- in은 데이터 중 하나와 일치하면 됩니다.

between A and B

- A 와 B 사이. A 와 B 포함. B 가 A 보다 크거나 같아야 합니다.

- in 과 between 에도 not 을 사용할 수 있습니다.

2) 와일드 카드 문자

_

- 한글자와 매핑

%

- 글자 수 와 상관없이 매핑

like

- 와일드카드 문자는 like 와 함께 사용

6. group by

- 그룹화 하기 위한 컬럼이나 연산식을 기술하는 절

- where 뒤에 기술하고 where 다음으로 수행됩니다.

- 작성순서

select

→

from

→

where

→

group by

→

having

→

order by

- 실행순서

from

→

where

→

group by

→

having

→

select

→

order by

- group by를 사용하게 되면 select 절에서 group by 한 항목과 그룹함수^[각주:2]만 조회할 수 있습니다.

7. having

- 그룹화 한 이후의 조건을 설정하기 위한 절

- 조건은 where 절에 기술하지만 where는 group by 가 수행되기 전에 실행되기 때문에 그룹화 한 이후의 조건을 설정할 수 없습니다.

8. limit

- 마지막에 기술하는데 정수 2개 또는 1개를 기술할 수 있습니다.

- 1개를 기술하면 이 정수 번째 이후의 데이터를 전부 가져옵니다.

- 0부터 시작합니다.

- 2개를 기술하면 앞 번호는 시작하는 인덱스가 되고 뒤 숫자는 데이터 개수입니다.

- 데이터베이스의 저장은 해싱으로 이루어지므로 그냥 order by로 정렬 후에 가져오는 경우가 대부분이다. 만약 정렬하지 않고 limit를 사용한다면 랜덤하게 가져오는것과 다를 바가 없다.

---

** 그룹함수

- 그룹함수는 select 절과 having 절과 order by 절에서 사용할 수 있습니다.

count(컬럼이름 또는 *)

- 데이터의 개수를 정수로 리턴합니다.

- 컬럼 이름을 기재하면 컬럼의 값이 null 인 데이터는 제외하고 개수를 셉니다.

- *을 사용하면 행의 개수를 리턴합니다.

※ 데이터베이스를 자바와 연동해서 출력할 때 출력만 하는 것은 중간에 삽입, 삭제 하는 일이 없다고 보므로 메모리 낭비를 줄일려면 배열을 사용하는 것이 좋다.

sum(컬럼이름), avg(컬럼이름), max(컬럼이름), min(컬럼이름)

- 문자열은 최대값과 최소값을 구할 수는 있지만 합계와 평균은 못 구합니다.

※ 게시판의 글번호를 시퀀스나 auto_increment로 하게 될 경우, static 변수와 같아서 글을 쓰고 삭제하면 중간 번호가 붕 뜨는 경우가 생긴다. 이게 싫으면 max 함수를 이용해서 제일 큰 번호(마지막 글번호)를 찾아서 +1 을 하면 된다.

---

** 주요 함수

coalesce(컬럼이름이나 연산식, null일 때 대체값)

- Oracle에는 null 인 경우 대체 값을 설정할 수 있는 nvl 이라는 함수가 있습니다.

MySQL 에는 coalesce()함수가 있습니다.

- ifnull 이라는 함수도 동일한 기능을 수행하는데 mssql에는 이 함수가 없습니다.

- avg 나 sum은 null 인 경우 연산에서 배제됩니다.

money 컬럼

10

null

30

------------

avg(money) => 20

avg(coalesce(money,0)) => 13.33333...

---

** join

https://blog.naver.com/knock_otd999/221352809487

** 서브쿼리

https://blog.naver.com/knock_otd999/221352886296

** 자바연동/spring연동

https://blog.naver.com/knock_otd999/221352894500

https://blog.naver.com/knock_otd999/221402517198

1. 모든 컬럼의 값을 순서대로 나열해야 한다. [본문으로]
2. 아래 목차 참고 [본문으로]