MySQL 정리

프로젝트를 진행하면서 필요한 내용들을 찾아가면서 공부해보고 있는데, HTML을 어느정도 마무리하고 java script를 공부하기 전에 먼저 이전에 배웠던 mysql의 명령어와 개념을 먼저 정리하고 넘어가고자 한다.

1. 데이터 베이스 생성 및 조회
2. 데이터 베이스 수정 및 삭제
3. 서브 쿼리(중첩 SQL문)
4. 제약 조건
5. JOIN

1. 데이터 베이스 생성 및 조회

• create databse DATABASENAME: 데이터 베이스 생성

• use DATABASENAME: 데이터 베이스로 이동

• create table TABLENAME(columnname type list): table 생성. 소괄호 안에 이름과 자료형을 차례로 쓰고 ','를 이용하여 서로 다른 column을 구분한다.

• insert into TABLENAME Values(): 테이블에 새로운 value를 추가. 소괄호 안에 ','를 이용하여 구분.

• show databases: 데이터 베이스 목록 확인

• select * from TABLENAME: table 내부에 있는 모든(*) 요소를 조회

• select * from DATABASENAME.TABLENAME: 외부 데이터 베이스의 table을 조회

• select columnname from TABLENAME: table 내부의 columnname만 조회

• selct * from TABLENAME where 조건문: 조건문에 해당하는 자료만을 찾아서 조회

• select 에서의 정렬: asc, desc 를 이용하여 오름차순, 내림차순 정렬을 할 수 있다. 예를 들어 select * from univ_student order by gpa asc;

2. 데이터 베이스 수정 및 삭제

• update: 테이블 내부의 데이터를 다룰 때 update를 사용

update student set english = 750 where name = "정채연"// student 테이블의 이름이 정채연인 english 칼럼의 값을 750으로 업데이트 하라

• ALTER: 이미 생성되어 있는 테이블의 column을 편집할 때 사용. 기본적인 형식은 ALTER table TABLENAME 명령어 column COLUMNNAME 속성; 으로 구성되어 있으며 각 명령어마다 약간씩 속성이 다르기도 하다. 또한 ALTER는 column을 수정하는 명령어이기 때문에 column은 생략해도 상관이 없다.

• ADD : 새로운 column을 추가할 때 사용하는 명령어. column의 이름과 Type은 필수 입력 대상이고 추가적으로 NULL값이나 Key, Default, Extra 에 대한 설정도 입력할 수 있다. 위치에 대한 default 값은 맨 뒤에 추가되는 것이며 AFTER, FIRST 등으로 추가되는 위치를 조정해줄 수 있다. 동일한 이름을 가진 column을 추가하는 것은 데이터 타입이 다르더라도 허용하지 않는다.

alter table Person ADD column age int; // Person 테이블에 weight column을 추가한다.
alter table Person ADD column height double AFTER age; // Person 테이블에 age 뒤에 height column을 추가한다. 
alter table Person ADD column no int FIRST; //Person 테이블에 no column을 맨 앞에 추가한다.
alter table person add column weight double not null default 60; // Person 테이블에 null 값을 허용하지 않는 default가 60인 double 타입의 weight column을 추가한다.

• MODIFY: column의 속성을 변경할 때 사용하는 명령어.

alter table Person MODIFY column weight int; // Person의 weight column을 double에서 int로 변경한다.
alter table Person MODIFY column height double default 170.0 //Person의 height column을 double, default 170.0으로 설정한다.  처음에는 double을 쓰지않고 alter table person modify column height default 170.0 과 같이 입력해봤는데 error가 뜨길래 double을 넣어서 해봤더니 되더라. 이를 통해서 알수있었던 사실은 alter는 어떤 명령어든 column의 이름과 속성을 입력해줘야한다는 것을 알 수 있었다. 

• CHANGE: column의 이름 또는 속성을 변경할 때 사용하는 명령어. change 도 type을 써줘야 한다.

alter table Person CHANGE height height1 int; // Person의 height column의 이름을 height1으로, 속성을 int로 변경한다. 또한 NULL값이나 Default값에 대한 설정을 따로 해주지 않았기 때문에 이전에 있던 속성값은 사라지게 된다.
alter table Person CHANGE height1 height double NOT NULL default 170.0; //이와 같이 null 값을 허용하지 않거나 default를 설정하는 것도 가능하다.

• DROP: column을 삭제할 때 사용하는 옵션.

alter table person drop column weight; //삭제함

• DELETE와 DROP: delete는 row를 삭제할때, DROP은 table을 삭제할 때 사용할 수 있다.

delete from student where name='송중기'; //송중기라는 이름을 가진 사람을 student 테이블에서 삭제
drop TABLENAME; //tablename에 해당하는 테이블을 삭제

3. 서브 쿼리(중첩 SQL문)

• 내가 정보를 잘 알고 있지 못할 때 이용할 수 있는 방법
  ex) A와 입사동기인 사람을 찾고 싶은데 A의 입사일자를 모름
  -> select * from Person where join_date = (select join_date from Person where name = ‘A’);

4. 제약 조건

• 데이터의 무결성을 지키기 위해 제한된 조건을 의미한다. 중복을 허용하지 않거나, NULL 값을 허용하지 않거나 등의 데이터를 입력받는 것에 있어 실행되는 검사 규칙을 의미한다.

• 제약조건의 종류
  PRIMARY KEY : 중복 X, NULL X.
  UNIQUE KEY : 중복 x, NULL o,
  FOREIGN KEY : 중복 o, 타 자료를 참조(references)하여 사용하는 key. 예를 들어 학생정보 테이블에 수강 정보가 같이 들어있다면 여러 과목을 수강할 때 학생정보를 기본키로 사용할 수 없다(학생 정보가 중복으로 들어가기 때문). 이럴 때는 수강 정보 테이블을 따로 두어 중복을 허용할 수 있다.)
  NOT NULL : Null을 허용하지 않음
  Default
  CHECK

• PRIMARY KEY(기본키)
  중복과 Null값을 허용하지 않는 제약조건이다. 회원가입 아이디, 닉네임, 주민등록번호, 학번 등 다양한 곳에 이용된다.
  다음과 같은 방법을 통해서 학번을 PRIMARY KEY 로 가지는 Student 테이블을 만들 수 있다.

create table Student(
ID int PRIMARY KEY,
Name varchar(10),
Major varchar(20),
Grade int);

중복을 허용하지 않기 때문에 만약 이미 존재하는 ID를 가진 자료를 입력하면 오류가 발생할 것이다.

Insert into Student(ID, Name, Major, Grade) Values(20180001, ‘Tom’, ‘Mathematics’, 3);
Insert into Student(ID, Name, Major, Grade) Values(20180001, ‘Jerry’, ‘English’, 3);
-> ERROR 1062 (23000): Duplicate entry '20180001' for key 'student.PRIMARY' //중복 비허용 에러

• FOREIGN KEY(외래키)
  말 그대로 다른 테이블의 기본키를 참조하는 열을 말하며, 외부의 자료를 가져온다고 하여 외래키, 참조키라고 이야기한다.
  원본파일에 대한 보호, 정보에 대한 관계 파악에 대한 편리성으로 사용하나 실제로 forenign key를 사용함에 있어서 자료 편집등에 불편함이 있어 잘 사용하지 않는다.
  수강 정보, 주문 정보 등 한 사람이 여러개의 정보를 가지고 있을 경우에는 학번이나 ID는 중복을 할 수 없으니 처리할 수 없는 경우가 생긴다. 이럴 경우 학생정보테이블에서 수강정보테이블을 새로 만들어 관리하거나, 회원 정보테이블에서 주문정보테이블을 만들어 관리하는 등의 관리가 필요한데, 이럴 때 외래키를 사용하기로 한다.
  외래키를 사용하는 테이블의 생성은 다음과 같다.

create table univ_camp(
CampName varchar(20),
CampID int,
FOREIGN KEY(CampID) references Student(ID),
Name varchar(20),
Major varchar(20),
Grade int);

외래키의 입력은 다음과 같이 사용할 수 있다.

Insert into univ_camp(CampName varchar(20), CampID, Name, Major, Grade) Values(‘IT취업 캠프’, 2018001, ‘Tom’, ‘Mathematics’, 3);

다만 외래키는 참조하는 대상인 기본키에 있는 대상만을 입력할 수 있다. 예를 들어

Insert into univ_camp(CampName, CampID, Name, Major, Grade) 
Values('IT취업 캠프', 20180010, 'Tom', 'Mathematics', 3);

이런 입력을 했다면 에러가 발생한다.
그 이유는 20180010이라는 학번은 Student의 ID에 존재하지 않는 학번이기 때문이다.

또한 중복을 허용하기 때문에 같은 자료에 대한 정보를 여러번 입력할 수도 있다.

5. JOIN

• 두개이상의 테이블이나 데이터베이스를 연결하여 데이터를 검색하는 방법.

• 조인의 종류
  내부조인(Inner join) : 둘 다 포함되는 내용만 나옴
  왼쪽 조인(Left Outer Join) : 왼쪽 테이블을 기준으로 왼쪽에 데이터가 있는데 오른쪽에 데이터가 없으면 NULL이 나온다.
  오른쪽 조인(Right Outer Join) : 오른쪽 테이블을 기준으로 오른쪽에 데이터가 있는데 왼쪽에 데이터가 없으면 NULL이 나온다.
  풀 조인(Full Join): 왼쪽과 오른쪽 둘다 나온다.

• 조인의 사용법

create table student1(name varchar(10), age int, height double);
Insert into student1(name, age, height) values('Jane', 20, 160.8);
Insert into student1(name, age, height) values('Tom', 23, 178.3);
Insert into student1(name, age, height) values('Julia', 21, 167.5);
Insert into student1(name, age, height) values('Hans', 21, 180.2);


create table patient(name varchar(10), age int, address varchar(50));
Insert into patient(name, age, address) values('Jane', 20, "대구광역시 동구");
Insert into patient(name, age, address) values('Tom', 23, "서울특별시 용산구");
Insert into patient(name, age, address) values('Tony', 22, "대전광역시 중구");
Insert into patient(name, age, address) values('Evan', 21, "부산광역시 해운대구");


select student1.name, student1.age, patient.address from student left outer join patient on student1.name = patient.name;
//아래 그림 1번

select student1.name, student1.age, patient.address from student1 right outer join patient on student1.name = patient.name;
//아래 그림 2번

select student1.name, student1.age, patient.address from student1, patient where student1.name = patient.name;
select student1.name, student1.age, patient.address from student1 inner join patient on student1.name = patient.name;