7/18 학습일지 | SQL 구조화 질의 언어

간만에 새로운 것들을 밀어넣느라 머리 터지는 중... 어제에 이어서 두 번째로 보고 있는 mySQL workbench. 두 번째로 보니까 조금 낫다. 오늘 배운 거 정리하기(내가 이해한 내용을 정리하는 것이라서 잘못 이해한 부분이 있을 수도 있음!)

---

mySQL로 데이터베이스를 만드는 방법에는 크게 2가지가 있는데, 리버스 엔지니어링으로 아래처럼 그림을 그려넣는 방법과, 처음부터 SQL 언어를 사용해서 만드는 방법이 있다.

물리적 모델링으로 실제 DB를 만들 때에는 테이블정의서를 보면서 작업해야 한다. 테이블 정의서가 기본! 
테이블을 만드는 방법은 기본적으로 create tabe ~~~ 이고 괄호 열고 속성들을 써주면 된다. 주석을 달 때에는 -- 를 쓴다. 

create database ai;

-- DDL(데이터 정의어)
create table student(
student_id int not null auto_increment primary key, 
student_name varchar(50), 
student_height decimal(5,2),
department_code int
);

CREATE TABLE department (
 department_code INT PRIMARY KEY AUTO_INCREMENT,
 department_name VARCHAR(50)
 );
 
 alter table student
add CONSTRAINT fk_student_department
foreign key (department_code) REFERENCES department(department_code);
-- ON DELETE CASCADE ON UPDATE CASCADE; <- 일관성이 연계되도록 걸어줌..

이렇게 직접 입력할 때에는, 학생과 학과 테이블을 연결하는 FK키를 직접 입력해줘야 한다. 
create table student(
student_id int not null auto_increment primary key, 
student_name varchar(50), 
student_height decimal(5,2),
department_code int
);

그리고 alter table로 둘을 연결해줘야 하는데, 연결 작업은 모든 테이블 작업이나 연습을 끝내고 마지막에 하는 게 좋다고. 만약 만들어놓은 테이블을 삭제하고 싶다면 drop을 하면 된다! 근데 이때, FK키로 서로 연결된 상태면 학과 테이블을 지우고 싶어도 지워지지가 않는다. 학생 테이블이 학과 테이블을 계속 참조하고 있기 때문에.. 그래서 학생을 테이블에 걸어놓은 제약 조건(참조 걸린 FK키)을 삭제하면 학과랑 학생 모두 자유롭게 삭제가 가능하다. 

만들어놓은 테이블 안에 속성값들을 입력하고 싶을 때는 insert into를 쓰면 된다. 대소문자 상관 없는 듯? 그리고 텍스트는 꼭 ' ' 작은 따옴표 안에 넣어서 써야한다. 

insert into department(department_name)
values ('국어국문과'),('전자정보통신과'),('모바일AI공학');

INSERT INTO student (student_name, student_height, 
department_code) VALUES 
('가학생', 170.5, 1), 
('나학생', 165.2, 1), 
('다학생', 180.2, 1), 
('라학생', 175.8, 2), 
('마학생', 160.7, 2), 
('바학생', 168.3, 3), 
('사학생', 172.1, 4), 
('아학생', 175.3, 4);

정보를 업데이트하고 싶다면, update 어떤테이블 set 어떻게바꿔라 where 조건
학사정보시스템에 차학생이 새로 들어왔다고 치고, 학과는 아직 미정이라 null값을 줬다고 치자. 나중에 학과를 정해서 정보를 업데이트할때에는, update 학생 테이블에서 set 3번학과를입력 where 차학생

INSERT INTO student (student_name, student_height, 
department_code) VALUES
 ('차학생', 170.5, null);

update student set department_code=3 where student_id=9;

그렇게 한 뒤에 select * from student; 를 해서 내용물을 보면 잘 들어가 있다. 

 

이 외에 SQL 구조화 질의 언어를 실습했다(구경했다). 속성 안에서 같은 그룹끼리 묶은 다음에 평균을 구하거나, 개수를 카운트하거나 하는 기능도 가능하다. 또 다른 테이블의 정보를 가져와서 두 개의 테이블을 join 시킨 뒤에 원하는 정보면 나타나게 할 수도 있다. 

---

예시1)
학생 테이블에 학과 테이블 join하기(inner join, left join 등 다양한 join 방법이 있음) 

select * from student s 
inner join department d 
on s.department_code=d.department_code

student s -> student를 s 로 줄여서 씀. *을 달면 전체 테이블 안에서 하는 것이지만, * 자리에 원하는 속성을 넣으면 그 값들만 나온다. 

오른쪽 결과가 아래 코드 실행 후 결과

select s.student_name, d.department_name 
from student s inner join department d 
on s.department_code=d.department_code

여기서 조건을 달아서 수학통계학만 뽑고 싶다면, where로 조건 달기.

select s.student_name, d.department_name from student s 
inner join department d on s.department_code=d.department_code 
where department_name='수학통계학'

* 조건을 주는 방법 중에 범위를 줄 때 이런 문법도 있다. (키가 175~180사이인 학생 구하기)

select count(student_id) as cnt from student where student_height>=175 and student_height<=180 LIMIT 0, 1000

select count(student_id) as cnt from student where student_height between 175 and 180 LIMIT 0, 1000

---

예시2) 학과별 교수의 수 구하기

학과 테이블과 교수 테이블을 inner join을 하고, group by로 학과이름에 따라 그룹을 묶어준다. 그리고 교수코드를 기준으로 숫자를 세는데, 이걸 cnt로 받아서 1명 이상인 학과를 뽑아낸 것. (2명 이상이면, cnt>=2). 여기서 select 뒤에 d.departmenet_name이라고 그룹바이된 이름을 앞에 꼭 써줘야한다. 

select d.department_name,count(p.professor_code) cnt 
from professor p inner join department d on p.department_code=d.department_code 
group by d.department_name having cnt>=1 LIMIT 0, 1000

+ 학과별 학생 중 키의 최대값과 평균값 구하기

select  d.department_name, max(s.student_height), avg(s.student_height) 
from student s inner join department d 
on s.department_code=d.department_code 
group by d.department_name LIMIT 0, 1000

---

타이타닉 자료로 실습하기

강사님께서 만들어주신 타이타닉 데이터베이스를 복붙하고, 그 안의 자료는 파이썬에서 집어넣어 실습을 또 진행했다. 데이터를 직접 하나하나 넣지 않아도 된다니! (당연히 그래야 한다고 생각했지만 그래도 놀랍다!!) 타이타닉 데이터를 테이블 몇개로 나누어서 설계할지 뭐 그런 것부터 직접 해보면 좋을텐데, 매우 압축적인 강의였기 때문에 주어진 자료로 다양한 정보를 파악하는 연습부터 했다.