[ASAC 10일차] SQL 기초

4월 3일의 기록

---

오늘 오전에 실습을 하면서 이것저것 건드려보다가 sequel ace 폰트를 변경 해버렸다.. 바꾼 김에 마음에 드는 폰트로 바꾸고 싶어서 나눔 고딕으로 한번 써봤는데, 오히려 가독성이 더 좋아진 것 같아서 기분이 좋다. 남는 시간에 프로그래머스의 SQL 고득점 KIT 문제를 풀었는데, Group by를 쓰는 부분에서 이해도가 낮은 느낌이라 혼자 복습을 해야겠다고 생각했다.

 

목차

---

1. DB, 테이블, 데이터 생성
2. SQL의 다양한 기능
3. 데이터베이스 모델링
4. SQL 기본 문법

1. DB, 테이블, 데이터 생성

---

1-1) 데이터 베이스 생성

drop database if exists sqldb; -- 만약 기존에 sqldb가 있다면 우선 삭제!!
create database sqldb;
use sqldb;
create table usertbl( 
	userID char(8) NOT NULL PRIMARY KEY, -- id컬럼 :PK
	name varchar(10) NOT NULL,           -- 이름
    birthYear int not null,              -- 출생년도
    addr char(2) not null,               -- 지역(경기, 서울)
    mobile1 char(3),                     -- 휴대폰 앞자리
    mobile2 char(8),                     -- 휴대폰 뒤에..
    height smallint,                     -- 키
    mDate DATE                           -- 회원 가입일..
);

1-2) 테이블 생성

create table buytbl (
	num int auto_increment not null primary key, -- 순번
    userID char(8) not null, -- 아이디 FK : 밑에 어디와 연결하는지 세팅..
    prodName char(6) not null,  -- 상풍명
    groupName char(4),          -- 분류
    price int not null,         -- 가격
    amount smallint not null,   -- 
    foreign key( userID ) references usertbl(userID)
);

1-3) 데이터 입력

INSERT INTO usertbl VALUES('LSG', '이승기', 1987, '서울', '011', '1111111', 182, '2008-8-8');
INSERT INTO usertbl VALUES('KBS', '김범수', 1979, '경남', '011', '2222222', 173, '2012-4-4');
INSERT INTO usertbl VALUES('KKH', '김경호', 1971, '전남', '019', '3333333', 177, '2007-7-7');
INSERT INTO usertbl VALUES('JYP', '조용필', 1950, '경기', '011', '4444444', 166, '2009-4-4');
INSERT INTO usertbl VALUES('SSK', '성시경', 1979, '서울', NULL  , NULL      , 186, '2013-12-12');
INSERT INTO usertbl VALUES('LJB', '임재범', 1963, '서울', '016', '6666666', 182, '2009-9-9');
INSERT INTO usertbl VALUES('YJS', '윤종신', 1969, '경남', NULL  , NULL      , 170, '2005-5-5');
INSERT INTO usertbl VALUES('EJW', '은지원', 1972, '경북', '011', '8888888', 174, '2014-3-3');
INSERT INTO usertbl VALUES('JKW', '조관우', 1965, '경기', '018', '9999999', 172, '2010-10-10');
INSERT INTO usertbl VALUES('BBK', '바비킴', 1973, '서울', '010', '0000000', 176, '2013-5-5');
INSERT INTO buytbl VALUES(NULL, 'KBS', '운동화', NULL   , 30,   2);
INSERT INTO buytbl VALUES(NULL, 'KBS', '노트북', '전자', 1000, 1);
INSERT INTO buytbl VALUES(NULL, 'JYP', '모니터', '전자', 200,  1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '모니터', '전자', 200,  5);
INSERT INTO buytbl VALUES(NULL, 'KBS', '청바지', '의류', 50,   3);
INSERT INTO buytbl VALUES(NULL, 'BBK', '메모리', '전자', 80,  10);
INSERT INTO buytbl VALUES(NULL, 'SSK', '책'    , '서적', 15,   5);
INSERT INTO buytbl VALUES(NULL, 'EJW', '책'    , '서적', 15,   2);
INSERT INTO buytbl VALUES(NULL, 'EJW', '청바지', '의류', 50,   1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '운동화', NULL   , 30,   2);
INSERT INTO buytbl VALUES(NULL, 'EJW', '책'    , '서적', 15,   1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '운동화', NULL   , 30,   2);

2. SQL의 다양한 기능

---

2-1) 뷰 (view)

• 뷰란 가상의 테이블이다.
• 실제 행 데이터를 가지고 있지는 않음
• 실행파일의 바로가기 같은 개념!
• 진짜 테이블의 데이터를 조회하는 것과 동일한 결과를 보내준다. 그러나 특정 정보만을 보여주고 싶거나 조회하게끔 만들고 싶을 때 정보의 접근에 대한 제한을 뒤기 위해 만들어둔 것이다.

#뷰에 접근하게 되면 뷰 생성시에 입력한 select 문만 조회할 수 있다.
create view uv_membertbl as
	select memberName, MemberAddress from membertbl;

2-2) 프로시저 (stored procedure)

---

1. MySQL에서 제공해주는 프로그래밍 기능이다.
2. SQL문을 하나로 묶어서 편리하게 사용할 수 있다.
3. 실무에서는 미리 저장해두고 사용하기 위해 사용하는 기능이다.

# --> 매번 여러 쿼리를 하기가 귀찮다.
# 하나의 스토어드 프로시저
DELIMITER //
create PROCEDURE myProc()
begin
	select * from membertbl where memberName = "당탕이";

	select * from productTBL where `productName` = "냉장고";
end //
DELIMITER ; #delimiter는 ; 공백으로 띄워야 한다.

# 미리 저장한 프로시저 불러오겠다..
call myProc();

2-3) 트리거 (trigger)

---

• 테이블에 부착되어 테이블에서 insert, update, delete 작업이 발생되면 실행되는 코드다.
• 예) 탈퇴회원 관리
  • 회원 테이블에서 특정 유저가 탈퇴하면???
  • 정보는 그냥 사라지게 되므로 어떤 유저가 들어왔다가 나갔는지 로그를 기록할 필요가 있다.
  • 따라서, 해당 회원이 삭제될 때 미리 다른 곳에 삭제될 데이터를 저장해주는 기능으로 만들어 둔다.

#삭제 유저 저장할 테이블 생성하기
create table deletemembertbl (
	memberID char(8),
	memberAddress char(5),
	memberName char(20),
	deletedDate DATE -- 삭제한 날짜에 남기기
);

# --> 특정한 이벤트가 발생할 때마다 데이터가 자동으로 기록을 하도록 설정

DELIMITER // 
create trigger trg_deletedmembertbl -- 트리거 이름 작성
	after delete -- delete 쿼리문이 실행하고 나서,,, (관찰 이벤트)
	on membertbl -- 트리거를 부착할 테이블을 지정...(관찰 대상)
	for each row -- 각 가로줄 마다 적용하겠다!

-- 위 일이 생기면 그때 아래를 실행해라
begin
	-- 할 일 : 기존 테이블의 내역을 deletedmembertbl에 기록
	insert into deletemembertbl 
			value (  old.memberID,
				old.memberName,
				old.memberaddress,
				CURDATE() );
end // 
DELIMITER ;

2-4) 데이터베이스 백업 및 관리

---

데이터 베이스 백업

• 백업용 폴더 작성 :
  • 실제로는 다른 디스크에 이루어져야 의미가 있다!
  • 가능하면 물리적으로 다른 곳으로
• DB 백업
  • DB내의 모든 트리거 및 프로시저까지 백업
  • 백업 폴더에 백업 파일을 저장한다.

2-5) ODBC : Open DataBase Connectivity

---

• ODBC는 응용프로그램에서 데이터 접근을 할 때 어떠한 DBMS(MySQL, MariaDB, Oracle etc)에 의해 관리되고 있는지 의식할 필요가 없이 접근할 수 있는 것이 특징이다.
• 모든 DB에 다 접근할 수 있는 API라고 생각하자!

3. 데이터베이스 모델링

---

3-1) 프로젝트 (Project)

1. 현실세계의 업무를 컴퓨터 시스템으로 옮겨놓은 일련의 과정
2. 대규모의 프로그램을 작성하기 위한 전체 과정
   1. 집 짓기의 경우 : 초가집 → 목조건물 → 수 십층 이상의 건물
3. 분석과 설계 작업을 등한시 하다 → 소프트웨어 개발 방법론이 대두 되었다

3-2) 워터풀 모델 (Waterfall)

1. 단계별로 소프트웨어 개발이 진행 된다.
2. 장점:
   1. 각 단계가 명확히 구분되어 현재 프로젝트 진행단계가 명확해진다.
3. 단점:
   1. 문제점이 생기거나 변경사항이 있을 떄 앞 단계로 가는것이 어렵다.
   2. 문제점을 업무 분석 단계에서 다시 시작하여 해결해야한다.

현 세계에서 사용되는 작업이나 사물들을 DBMS의 데이터베이스 개체로 옮기기 위한 과정

→ 현실에서 쓰이는 것을 테이블로 변경하기 위한 작업

4. SQL 기본

---

4-1) select

• 원하는 데이터를 가져오는 기본 구문
• 가장 많이 사용한다.

#기본 문법
select 열 이름
from 테이블 이름
where 조건
group by 그룹화
having 그룹화의 조건
order by 정렬 기준

간단 실습

-- 고객중 1970 이후에 태어난 사람 중 키가 182 이상, userID, Name

select userID, Name from usertbl

where  birthYear >= 1970
and height >= 182;

-- 2) 주소지가 서울이면서, 핸드폰 번호의 앞자리 011을 사용하는 사람의 모든 정보조회
select * from usertbl
where addr like "서울%"
and mobile1 like "%011%";

-- 3) 주소지가 서울이면서, mobile1의 번호를 011을 사용하는 사람의 mobile 1, mobile2의 번호를 조회(문자발송)

select userid, name , concat( mobile1,"-",mobile2) as mobile from usertbl
where addr like "서울%"
and mobile1 like "%011%";

-- 예 1) 키가 177 이상인 사람들의 이름과 키를 출력

select * from usertbl
where height > 177;

-- 예 2) 키가 김경호보다 큰 사람

select * from usertbl
where height > (
	select height from usertbl where name like "김경호"  );

-- 예 3) 지역이 경남인 사람의 키보다 큰 사람의 이름과 키의 값을 찾아주셈

select * from usertbl
where height > any (
	select height from usertbl where addr like "%경남%" );

## 위는 제일 작은 170보다 크면 전부 ok

## 아래는 안에 있는 모두보다 커야 ok
select * from usertbl
where height > all (
	select height from usertbl where addr like "%경남%" );
	

# 170 or 173인 친구들만 

select * from usertbl
where height = any (
	select height from usertbl where addr like "%경남%" );

insert, update, delete는 다음 시간 부터 진행.