Mysql
1.데이터베이스(DB)
데이터베이스(DB : Database)는 데이터를 체계화해 통합,관리하는 데이터 집합체를 의미한다. 즉, 효율적인 사용을 위해 처리된 데이터를 데이터베이스라고 생각할 수 있다. 이런 데이터베이스를 관리하는 프로그램을 데이터베이스 관리 시스템(DBMS : Database Management System)이라고 한다. 이런 DBMS 종류 중 지배적인 형태가 관계형 데이터베이스 관리 시스템(RDBMS : Relational Database Management System)이다. RDBMS의 가장 큰 특징은 테이블이라는 구조를 이용해 데이터를 저장한다는 점이다. 이런 RDBMS의 종류로는 Oracle, MySQL, MS-SQL, Maria DB 등이 있다.
2.SQL
Structured Query Language의 약어로 RDBMS를 이용하기 위해 제작된 언어이다. 다음과 같이 3가지로 분류할 수 있다.
- DDL : Data Definition Language
-> 골격 조작 용도
- create
- alter
- drop
- truncate - DML : Data Manipulation Language
-> 내용 조작 용도
- select
- insert
- update
- delete - DCL : Data Control Language
-> 권한 조작, 저장 조작 용도
- grant
- revoke
- commit
- rollback
2.1 데이터 정의 언어(DDL)
테이블이나 인덱스같은 전체 구조를 조작하는 언어로, 기본적으로 바로 commit(저장)이 되는 성질이 있어서 rollback기능이 적용되지 않는다.
- create : 데이터베이스 개체(테이블,인덱스,제약조건 등) 생성
create table 테이블명(
필드명 필트타입 제약조건
);
create table 테이블명 as select 복제할다른테이블컬럼명 from 다른테이블명 where 조건;
-- 다른테이블에서 조건에 맞는 복제할다른테이블컬럼의 레코드를 복제한다
-- 여기서 조건에 false값을 넣어주면 다른테이블의 구조만 복제된다.- drop : 데이터베이스 개체 삭제
- 추가 제어
- if exists : 기본적으로 drop은 삭제하려는 개체가 없으면 오류가 생긴다. 이를 방지하기 위해 개체가 존재하면 삭제하고 없으면 넘어가라는 명령어 if exists를 사용할 수 있다.
drop table if exists 테이블명;- alter : 데이터베이스 개체 수정
- 추가 제어
- add : 새로운 컬럼이나 제한 추가
- modify : 기존에 존재하던 컬럼이나 제한 수정
- drop : 기존에 존재하던 컬림이나 제한 삭제
- change : 컬럼명/컬럼타입 변경하기
alter table 테이블명 add 새로운컬럼명 새로운컬럼타입 새로운제한조건;
alter table 테이블명 modify 기존컬럼명 새로운컬럼타입 constraint 제약조건명 새로운제약조건;
alter table 테이블명 drop 기존컬럼명;
alter table 테이블명 change 기존컬럼명 변경컬럼명 변경컬럼타입;
- truncate : 테이블 구조는 유지하고 데이터만 삭제(delete와 달리 rollback 못 씀)
truncate table 테이블명;2.2 데이터 제어 언어(DML)
데이터베이스에 들어가는 데이터 자체를 관리하는 언어로, 별도의 auto-commit설정이 되어있지 않는한 바로 commit되지는 않아서 commit전으로 돌아가는 rollback기능을 사용해 되돌릴 수 있는 작업들이다.
- select : 데이터 검색
※ 정확하게 표현하면 DML이 아니라 DQL(Data Querry Language)로 분류된다!
select 필드명 as 표실할필드명 from 테이블명 where 조건 order by 컬럼명 asc(오름차순,내림차순:asc,desc);
-- 여러 컬럼을 각각 다르게 order by 하기(앞에 있는 조건이 우선됨)
select * from 테이블명 order by 컬럼명1 asc, 컬럼명2 desc;
select distinct 필드명 from 테이블명;
-- 필드에서 중복된 값은 제거하고 검색
select 필드명 from 테이블명 where 검색할필드명 between 검색시작범위 and 검색끝범위;
select ifnull(필드명, 값이 null일 경우 대체값) from 테이블명;
select 필드명 from 테이블명 where 비교할필드명 in(비교값1, 비교값2, ...);
-- 비교할필드명에 해당하는 값이 비교값1, 비교값2,... 에 포함되면 해당 레코드의 필드명 값 검색
select
case
when 조건1 then 작업1
when 조건2 then 작업2
else 작업3
end as 필드명별칭
from 테이블명;
-- 필드명별칭으로 표시하고 검색내용으로는 조건1을 만족하면 작업1실행, 조건2만족하면 작업2실행, 아무것도 만족하지 않으면 작업3실행하여 검색완료 - insert : 데이터 삽입
insert into 테이블명 (값넣어줄필드들) values (값넣어줄필드에들어갈값);
-- 값넣어줄필드들을 아예 생략하면 모든 필드에 해당하는 값들을 다 넣어줘야한다.
-- 값넣어줄필드에 선택받지 못한 필드들은 그냥 default값으로 넣어진다.
insert into 테이블명 values (),();
-- 이런식으로 여러 레코드를 한번에 넣어줄 수 있다.- update : 데이터 수정
update 테이블명 set 필드값수정;
update 테이블명 set 필드값수정 where 조건;- delete : 데이터 삭제
delete from 테이블명 where 조건;
-- alter drop과 다른점 : alter drop은 특정 필드를 선택 그 필드 자체를 삭제함. delete는 where 조건을 통해조건을 만족하는 레코드들(행)만 삭제한다.2.3 데이터 조작 언어(DCL)
권한 조작, 작업 저장 조작
- grant : 권한부여
grant all privilege on 데이터베이스명.* to '계정명'@'localhost';
-- 해당 데이터베이스에 관한 localhost권한을 해당 계정에게 부여한다.- revoke :
-
commit : 데이터베이스 속 영구메모리에 저장
-
rollback : 데이터베이스 속 임시메모리에 있는 내용 없에고 영구메모리에 있는 내용 불러옴
3.테이블(table)
RDBMS의 가장 큰 특징으로 키(key)와 값(value)의 관계를 나타낸 구조다.
- 열(column) : 필드(field)나 속성(attribute)이라고도 불리며 테이블에서 세로 한줄을 의미한다.
- 행(row) : 튜플(tuple)이나 레코드(record)라고도 불리며 테이블에서 가로 한줄을 의미한다.
- 키(key) : 기본 키(primary key)라고도 불리며 테이블에서 행의 식별자로 이용되는 열을 의미한다.
- 값(value) : 테이블에서 각각의 행과 열에 입력된 데이터를 의미한다.
3.1 키(key)
키는 테이블에서 레코드들(행들)을 서로 구분할 수 있게 하는 하나 이상의 속성들(열들)의 집합을 의미한다. 쉬운 예로 주민등록번호같은 경우 개개인이 고유한 값을 가지고 있어 주민등록번호가 개개인을 구분할 수 있게하는 키(key)라고 할 수 있다.
-
후보 키(candidate key) : 기본 키가 될 수 있는 후보
- 유일성 : 레코드(행)값들이 중복되지 않아야 함
(이름은 사람을 구분하는 데 유일성을 만족하지 않음 --> 동명이인 때문에) - 최소성 : 최소한의 키들만 포함해야 함
(주민등록번호는 사람을 구분하는 데 유일성을 만족하고, 운전면허등록번호도 사람을 구분하는 데 유일성을 만족한다. 이때 각각의 속성을 따로 키로 사용하면 최소성을 만족하지만, 이 둘을 합쳐 (주민등록번호,운전면허등록번호)를 하나의 키로 사용하게 되면 최소성을 만족하지 못하는 키가 된다.)
- 유일성 : 레코드(행)값들이 중복되지 않아야 함
-
기본 키(primary key) : 후보 키들 중 선택된 단 하나의 키
-
not null : null값을 가지지 못함
-
선언방법(6가지)
-- 첫번째방법 create table 테이블명( 필드명 필드타입 primary key ); -- 두번째방법 create table 테이블명( 필드명 필드타입, constraint 제약조건명 primary key (필드명) ); -- 세번째방법(새 필드 추가하면서 해당 필드를 기본 키로) alter table 테이블명 add 필드명 필드타입 primary key; -- 네번째방법(새 필드 추가하면서 해당 필드를 기본 키로) alter table 테이블명 add constraint 제약조건명 primary key (필드명) -- 다섯번째방법(기존 필드 기본 키로 수정) alter table 테이블명 modify column 필드명 필드타입 primary key -- 여섯번째방법(기존 필드 기본 키로 수정) alter table 테이블명 modify column constraint 제약조건명 primary key (필드명)
-
-
대체 키(alternate key) : 후보 키들 중 선택된 기본 키를 제외한 나머지 후보 키들
-
슈퍼 키(super key) : 유일성은 만족하지만 최소성은 만족하지 않는 키들
- 유일성 : 레코드(행)값들이 중복되지 않아야 함
(이름은 사람을 구분하는 데 유일성을 만족하지 않음 --> 동명이인 때문에) - 최소성 : 최소한의 키들만 포함해야 함
(주민등록번호는 사람을 구분하는 데 유일성을 만족하고, 운전면허등록번호도 사람을 구분하는 데 유일성을 만족한다. 이때 각각의 속성을 따로 키로 사용하면 최소성을 만족하지만, 이 둘을 합쳐 (주민등록번호,운전면허등록번호)를 하나의 키로 사용하게 되면 최소성을 만족하지 못하는 키가 된다.)
- 유일성 : 레코드(행)값들이 중복되지 않아야 함
-
외래 키(foreign key) : 하나의 테이블에서 참조하는 다른 테이블의 기본 키
- 주민등록번호를 기본 키로 사용하는 테이블을 다른 테이블에서 참조하여 주민등록번호를 가져다 쓸 경우 이 속성을 외래 키라고 한다. 이때 참조 무결성을 지켜야 하므로 주민등록번호를 기본 키로 사용하던 테이블에 없던 주민등록번호 값을 참조할 순 없다. 오직 기본 키로 사용하던 테이블에 존재하는 값만을 참조할 수 있다.
4.제약조건(Constraint)
제약조건을 거는 방법을 여러가지 있다. 위에서 설명된 alter를 이용해 제약조건을 추가하거나 기존 제약조건을 변경할 수도 있다. 아니면 create를 이용해 테이블을 생성할 때 제약조건을 걸어줄 수도 있다.
create table 테이블명(
필드명 필드타입,사이즈 제약조건
);
create table 테이블명(
필드명 필드타입,사이즈,
constraint 제약조건명 제약조건 (필드명)
);
-- 이미 테이블이 만들어졌을 때
alter table 테이블명 add constraint 제약조건명 제약조건 (필드명);
alter table 테이블명 modify 필드명 필드타입,사이즈 제약조건;제약조건 종류들
-
not null : null값 저장 불가능
말 그대로 null값이 허용되지 않는다는 뜻이지, 생략이 불가하다는 뜻과 100% 같은 말은 아니다. 만약 not null로 제한조건이 걸려있는 컬럼에 해당하는 값을 생략하고 값을 insert한다고 했을 때 무조건 문제가 생기지 않는다. 생략하고 넣는다면 이것이 바로 null아닌가? 라고 생각할 수 있지만 더 정확히 말하자면 해당 값을 생략을 하고 insert를 할 경우 해당 칼럼에 설정된 default값이 넣어지게 되고 보통 default값이 null값으로 설정되어있어서 이런 오해를 할 수 있는 것이다. 예를 들어 제한조건은 not null이지만 default값이 777로 설정되어 있는 int타입 컬럼name이 있다고 가정하면, name에 대한 값을 생략하고 값을 insert하면 컬럼name에는 default값 777이 입력되고 not null을 만족하게 된다. -
unique : 고유한 값만 저장 가능, null은 허용
-
primary key : 'unique + not null' 고유한 값만 저장 가능하며 null값 저장 불가능
-
foreign key : 타 테이블 필드를 참조해옴
-- 다른 제약조건들과 달리 두 개의 요소가 필요해 문법도 살짝 다름
create table 테이블명(
필드명 필드타입,사이즈,
foreign key (필드명) references 타테이블명(타테이블필드명)
);
-- 이미 테이블이 만들어졌을 때
alter table 테이블명 add constraint 제약조건명 foreign key (필드명) references 타테이블명(타테이블필드명);- check : 조건식을 이용해 데이터의 유효 유무를 검증함
create table 테이블명 (
필드명 필드타입,사이즈,
check (조건문)
);- default : 필드의 default값을 지정해줌
create table 테이블명 (
필드명 필드타입,사이즈 default 지정할디폴트값
);5.조인(join)
두 개 이상의 테이블을 겹쳐서 서로의 교집합, 합집합 부분 등을 사용하는 방법이다. 각각의 조인을 설명하기 위해 예시 테이블을 2개 만들어본다.
<A테이블> (A테이블의 번호는 B테이블의 번호를 참조한 외래 키다.)
번호 이름 재능
1 홍길동 춤
2 이장군 노래
3 김미소 null
<B테이블>
번호 지역
1 한국
2 미국
3 중국
4 null - Equi Join : '='연산자를 이용해 테이블에서 같은 조건을 가지고 있는 값을 출력하기 위해 사용한다.
select * from A as a,B as b where a.번호=b.번호;
-->
-- (번호,이름,춤,번호,지역 순으로)
-- 1, 홍길동, 춤, 1, 한국
-- 2, 이장군, 노래, 2, 미국
-- 3, 김미소, null, 3, 중국
-- 출력됨- Non-Equi Join : '='연산자가 아닌 beteween and, >,<,>=,<= 등의 연산자를 사용해 해당 조건을 만족하는 값을 출력하기 위해 사용한다.
select * from A where A.번호 between 1 and 2;
-- A에서 번호가 1과 2사이에 있는
-- (번호,이름,춤,번호,지역 순으로)
-- 1, 홍길동, 춤, 1, 한국
-- 2, 이장군, 노래, 2, 미국
-- 출력됨- Self Join : 자신의 테이블을 참조해야 할 경우가 있다. 예를 들어 한 테이블에 모든 사원의 번호가 있고 각 사원의 번호가 있는 행에는 그들의 상사번호가 있다고 가정해본다. 이때 사원의 상사가 누구인지 알고 싶다면 상사번호를 알아내어 이 번호를 다시 테이블에서 사원번호로 검색해야 한다. 이번 경우에는 위에서 예시로 만든 A,B테이블 대신 다른 테이블을 예시로 들겠다.
ex)
<employee테이블>
번호 이름 상사번호
1 김철수 2
2 이영미 3
3 박희민 4
4 최지영 null - 이중 호출 : 간단하게 두 번 호출하여 문제를 해결할 수 있다.
select e1.이름 부하이름, e2.이름 상사이름
from employee e1,employee e2
where e1.상사번호=e2.번호;
-- (부하이름 상사이름)
-- 김철수 이영미
-- 이영미 박희민
-- 박희민 최지영
-- 이렇게 3개의 데이터 출력된다.
- Sub Querry : 쿼리문을 중첩시키는 방법이다. 이런 서브 쿼리문으로 원하는 값을 특정해서 뽑아낼 수 있다.
select 이름
from employee
where 번호=(select 상사번호 from employee where 번호=1);
-- 서브 쿼리문부터 실행이 된다. 즉, 번호가 1인 레코드에서 상사번호를 검색해온다. 이는 번호:1,이름:김철수,상사번호:2인 레코드에서 상사번호 2를 검색해온다는 뜻이고 이 값이 where절로 대입되어 번호가 2인 레코드에서 이름을 검색하게 된다. 즉, 번호:2,이름:이영미,상사번호:3인 레코드에서 이름인 이영미를 뽑아오게 된다.
-- 출력: 이영미- Inner Join ... on A.key=B.key : 가장 기본적인 조인으로 두 테이블의 교집합 부분을 이용한다고 생각하면 된다.
select * from A inner join B on A.번호=B.번호;
-- A테이블의 번호와 B테이블의 번호가 같으면 선택해라.
-- (번호,이름,춤,번호,지역 순으로)
-- 1, 홍길동, 춤, 1, 한국
-- 2, 이장군, 노래, 2, 미국
-- 3, 김미소, null, 3, 중국
-- 이렇게 3개의 데이터가 출력된다.- (Left Join / Right Join) ... on A.key=B.key : left와 right 조인은 방향만 다르고 작업을 동일하다. left의 경우 left join 기준 왼쪽, right의 경우 right join 기준 오른쪽의 테이블을 기준테이블로 삼는다. 기준테이블이란 on에서 제시된 조건을 충족하지 않더라도 값을 출력하는 테이블을 의미한다. 예시를 통해 알아보자.
select * from A right join B on A.번호=B.번호;
-- B가 기준테이블로 on에 제시된 조건을 충족하지 않더라도 값을 출력할 것임. 먼저 on 조건을 충족하는 값들은
-- (번호,이름,춤,번호,지역 순으로)
-- 1, 홍길동, 춤, 1, 한국
-- 2, 이장군, 노래, 2, 미국
-- 3, 김미소, null, 3, 중국
-- 이렇게 3개의 데이터가 있다. 하지만 B테이블에서 번호4에 대한 데이터가 있기 때문에 아무리 A테이블에 번호4와 매치되는 데이터가 없다고 해도 right join에 대한 기준을 B테이블로 잡았으므로 번호4에 대한 데이터도 출력된다.
-- (번호,이름,춤,번호,지역 순으로)
-- 1, 홍길동, 춤, 1, 한국
-- 2, 이장군, 노래, 2, 미국
-- 3, 김미소, null, 3, 중국
-- null,null,null,4,null
-- 이렇게 4개의 데이터가 출력된다.- (Left Join / Right Join) ... on A.key=B.key where (A/B).key is null : 위 경우와 비슷하지만 뒤에 where절을 이용해 기준테이블과 다른테이블이 null일 경우를 제외시켜 자신만 가지고 있는 값을 출력한다. 쉽게 말하면 다른 테이블과 겹친 부분을 빼고 출력하겠다는 의미다.
select * from A right join B on A.번호=B.번호 where A.번호 is null;
-- 위에 있는 예시의 출력값에서 where절만 적용해 주면 된다.
-- (번호,이름,춤,번호,지역 순으로)
-- 1, 홍길동, 춤, 1, 한국
-- 2, 이장군, 노래, 2, 미국
-- 3, 김미소, null, 3, 중국
-- null,null,null,4,null
-- 이렇게 4개가 출력된 상황에서 where A.번호 is null을 만족하는 경우는
-- null,null,null,4,null
-- 이렇게 데이터 하나만 출력된다.-
Left/Right Outer Join ... on A.key=B.key : outer는 사실 생략이 가능하다. 즉 생략을 하고보면 위에서 설명한 예시와 다를 것이 없다. full outer의 경우는 A와 B의 합집합을 의미하지만 mysql에서는 지원되지 않아서 outer 기능이 크게 쓰이지 않는다.
-
Left/Right Outer Join ... on A.key=B.key where A.key is null or B.key is null : A와 B테이블에서 on조건을 만족하는 부분만 뺀 나머지 데이터 검색
6.그룹함수
그룹함수란 다수의 행데이터를 한번에 처리하는 함수를 의미한다. null값은 자동 제거해주며 기능한다.
- select count(테이블속성) from 테이블명;
: 테이블 속에서 테이블속성 개수를 세고 출력한다.
이를 이용해서 종류를 세주고 싶으면 distinct와 섞어서 'select count(dsitinct 테이블속성) from 테이블명'이런식으로 표현해줄 수 있다. - select sum(테이블속성) from 테이블명;
: 테이블 속에서 테이블속성을 모두 더해서 출력한다. - select avg(테이블속성) from 테이블명;
: 테이블 속에서 테이블속성의 평균을 구해서 출력한다. - group by 테이블속성;
: 테이블속성을 기준으로 그룹화 - having 조건문;
: 그룹함수 사용 시 조건문 - where과 비슷. 다른점은 where은 조건문에 그룹함수(sum, count 등등)를 사용하지 못한다. 그래서 이런 그룹함수를 넣어서 조건문을 작성하고 싶을 때 having을 사용하느 것이다.
