MySQL

- java와 닷넷에서는 java나 닷넷 프레임워크가 설치되어 있지 않으면 프로그램을 사용할 수 없음.

 

windows에서 c언어로 만들어진 프로그램은 C++ 재배포 패키지가 없으면 설치가 안됨.

 

mysql이 실행된 상태가 아니기 때문에 환경설정에서 서비스를 시작하고 비밀번호를 변경해야함

 

 

데이터베이스 작업

- MySQL은 디비 단위로 작업

1) 만들어진 디비 확인

show databases;

- 처음 만들어진 디비 중에서 world를 제외하고는 시스템 디비

- 시스템 디비는 조회만 하는거고 실제 작업을 하지 않는 것을 권장

 

2) 디비 생성

create database 디비이름;

 

테이블 작업

1. 디비에 존재하는 모든 테이블을 확인

show tables;

 

2. 테이블 구조 확인

desc 테이블이름;

 

3. DDL(Data Definition Language - 데이터베이스 개체의 구조에 관련된 명령어)

1) create: 생성

2) alter: 구조 변경

3) drop: 삭제

4) truncate: 테이블 데이터 삭제

5) rename: 테이블명 변경

-> create, alter, drop만 ddl

 

4. 테이블 생성

Create Table 테이블명(

         컬럼이름 자료형 컬럼제약조건,

         ....

         테이블 제약 조건

)ENGINE=엔진종류 AUTO_INCREMENT=시작값 DEFAULT CHARSET=인코딩 방식;

- ENGINE 종류에 따라 트랜잭션이 동작하지 않을 수 있음

- CHARSET을 설정하지 않으면 한글 입력 안됨

 

5. 자료형

1) 숫자: INT(INTEGER), FLOAT, DOUBLE

2) 문자: CHAR,VARCHAR, TEXT

- CHAR는 크기가 변하지 않는 문자열로 235까지만 설정 가능

- VARCHAR는 크기가 변하는 문자열로 65535까지 설정 가능

- TEXT는 긴 문자열 저장에 사용하는데 LONGTEXT도 있음

3) BOOL: true 또는 false 저장

4) 날짜: DATE(날짜), DATETIME(날짜와 시간), TIMESTAMP(날짜와 자세한 시간), TIME(시간), YEAR(년도)

             날짜와 시간을 같이 저장할 때는 TIMESTAMP를 많이 사용

 

6. 제약조건

- 테이블에 잘못된 데이터가 저장되지 않도록 하기 위한 유효성 검사 조건

1) not null, unique, pk(테이블에서 한개만 설정 가능, 여러개 만드는것은 가능), fk

 

2) auto_increment: 자동증가하는 값을 만들 때 사용하고 숫자 자료형에만 사용이 가능하고

                               pk나 unique와 함께 사용

설정방법

create table 테이블명(

           컬럼이름 자료형 [constraint 제약조건이름] 제약조건,

           [constraint 제약조건명] 제약조건(컬럼이름 나열)

)

- not null은 컬럼 제약 조건으로 설정

- 외래키 옵션은 on delete cascade, on delete set null

 

7. ENGINE

1) MyISAM(Indexed Sequential Access Media)

- 데이터를 빠르게 조회하기 위한 목적으로 사용하는 엔진

- 트랜잭션 처리가 아됨

 

2) InnoDB

- 트랜잭션 처리를 위한 엔진

- 삽입 삭제가 빈번히 발생하는 경우에는 InnoDB 조회가 많은 경우에는 MyISAM을 사용

 

8. AUTO_INCREMENT

- 일련번호의 개념으로 테이블을 만들 때 초기값 설정이 가능

- 초기값을 변경하려면 ALTER TABLE 테이블명 AUTO_INCREMENT = 시작값

 

9. 기존 테이블에 대한 컬럼 작업

1) 컬럼이 추가되어야 하는 경우

alter table 테이블이름 add 컬럼이름 자료형[first 또는 alter 다른 컬럼이름];

 

2) 컬럼을 삭제

alter table 테이블명 drop 컬럼이름;

 

3) 컬럼을 변경

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

 

--자료형 변경

alter table 테이블명 modify 컬럼이름 자료형

 

4) 제약조건 추가

alter table 테이블명 add [constraint 제약조건이름] 제약조건(컬럼이름);

5) 제약조건 수정

alter table 테이블명 modify 컬럼이름 자료형 제약조건;

 

6) 제약조건 삭제

alter table 테이블명 drop constraint 제약조건이름

 

10. 테이블 삭제

drop table 테이블명

* 다른 테이블에서 이 테이블의 컬럼을 외래키로 참조하는 경우 삭제 안됨 -> 자식 테이블을 삭제하거나 외래키 제약조건을 삭제하고 수행해야함.

 

11. 테이블의 데이터만 삭제

truncate table 테이블명 (rollback 안됨) 

 

12. 테이블 이름 변경

alter table 테이블명 rename 테이블명

 

13. truncate와 rename은 표준 SQL이 아님

 

DML(데이터 조작언어)

- DQL(SELECT)과 DML(INSERT, UPDATE, DELETE)로 구분하기도 함

 

2) 테이블의 일부 컬럼이나 표현식 조회

select 컬럼이름이나 표현식 나열

from 테이블나열

 

3) 컬럼이름에 별명 부여

select 절에서 컬럼이름이나 표현식 뒤에 as 별명

- as 생략 가능

- 별명에 공백이나 대문자 또는 한글이 포함된 경우" "로 감싸서 설정

 

- buytbl테티블에서 userid와 price*amount의 값을 조회

price*amount 금액이라는 열 헤더로 조회

 

4) where 조건절

select 조회할 컬럼이름 또는 표현식 나열

from 테이블이름 나열

where 조건:

-> where는 from 다음에 수행

select 절에서 만든 별명은 where절에서 사용 못함

 

in(값을 나열): 나열된 값 중에서 일치하는 데이터를 조회

 

or는 앞의 조건이 참이면 뒤의 조건을 확인하지 않음

and는 앞의 조건이 거짓이면 뒤의 조건을 확인하지 않음

or를 만들 때는 참일 확률이 높은 조건을 앞에 작성하고

and를 만들 때는 거짓일 확률이 높은 조건을 앞에 작성

 

패턴 일치는 like 이용 %, _ 이용

%: 글자 수 상관 없음

_ : 1글자와 매칭

id나 password같은 경우는 완전 일치하는 데이터를 찾지만 대부분의 경우는 부분일치하는 데이터를 찾음

 

usertbl에서 name이 배로 시작하는 데이터의 name과 birthday 조회

 

select name, birthyear

from usertbl

where name like '배%';

 

5) sub query

- from 절에 사용되면 inline view, where절에 사용되면 sub query라고 함.

다중행 서브쿼리는 단일행 연산자르 ㄹ사용할 수 없어서 = 대신에 in, != 대신 Not in

 

6) Group by 

- 컬럼이나 표현식으로 그룹화 할 때 사용하는 절

- where 다음에 수행

- 이 절이 종료되면 그룹함수를 사용할 수 있음

- 그룹함수는 having과 select 그리고 order by 절에서 이용 가능

- 그룹함수는 sum, avg, max, min, stddev, var_samp, count

- 그룹함수는 null을 제외하고 연산을 수행

- count함수는 컬럼이름을 대입하지 않고 보농 *을 이용

 

7) Having

- 그룹함수를 이용한 조건을 만들때 사용하는 절

- where절의 조건은 having에 기재해도 되지만 효율이 떨어짐

 

8) order by 

 

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

- 컬럼이름이나 표현식 뒤에 asc를 붙이면 오름차순 desc를 붙이면 내림차순

- 디비는 데이터를 인덱싱해서 저장하기 때문에 저장한 순서와 조회하는 순서는 다를 수 있음

- 2개 이상의 데이터를 조회하는 경우에는 원하는 조건으로 정렬해서 조회하는 것을 권장

 

10) select 절에서 컬럼이름 앞에 distinct를 기재하면 중복 제거

- 중복제거는 distinct 대신에 group by를 사용해도 됨.

 

11) 데이터 개수 제한

select 구문의 마지막에 limit 데이터개수 또는 limit 시작위치, 데이터개수를 이용해서 필요한 개수 만큼의

데이터를 가져올 수 있음.

 

null 처리 함수

IFNULL(데이터, 기본값): 데이터가 존재하면 데이터, 없으면 기본값

- 프로그래밍 언어의 기본값과 매핑을 하는 경우에는 이 함수의 사용을 고려

 

NULL -> int: 예외 발생

 

4.날짜 및 시간

1) 현재 날짜 및 시간

current_date(): 현재 날짜

current_time(): 현재 시간

current_timestamp(): 현재 날짜 및 시간

 

2) 특정 날짜 및 시간

- MySQL은 일반적인 형식의 날짜 및 시간 문자열은 날짜 및 시간으로 인지