SQL은 Stuctured Query Language의 약자다.
DB쪽에선 가장 유명한 친구임. 관계형 데이터베이스에서 사용한다.
MySQL, Oracle, SQLite, PostgreSQL등 이 그 종류임.
나 같은 경우 테스트용으로 h2를 많이 쓴다. 워낙 간단해야말이지
SQL 명령어는 쿼리로 이루어지는데, 이 쿼리는 질문이라는 뜻으로도 해석할 수 있다.
DB에게 물어보는거다.
SELECT * FROM member WHERE gender = 'male'; member 테이블에서 gender가 'male'인 애들을 모두 선택하세요. 라는 뜻이다.
명령어
DB 생성, 사용, 조회
CREATE DATABASE 데이터베이스_이름;db를 생성한다. 먼저 생성을 해야 쓰든가 말든가 할거 아닌가?
USE 데이터베이스_이름;생성된 DB를 사용하는 명령어다. 이 명령어 이후에는 DB안 Table을 확인할 수 있다.
show databases;DB목록을 보여준다.
Table 생성, 조회
CREATE TABLE user (
id int PRIMARY KEY AUTO_INCREMENT,
name varchar(20),
email varchar(100)
);테이블 생성시에 row를 이렇게 입력할 수 있다.
cmd sql을 쓸때엔 ";"를 입력하지 않는 이상 계속 줄바꿈만 일어나니까 그냥 치면 됨.
id는 int형 자료를 선언해줬다. int로 해도 앵간하면 다 먹는데, 어차피 메모리 남아 도니까 BIGINT로 후려도 되지 않을까 싶다.
DESCRIBE user;user 테이블의 정보를 확인한다. 이걸 입력하면 표로 보여준다.
INSERT (Create)
DB에 데이터를 넣는 명령어다.
INSERT INTO 테이블이름(필드이름1, 필드이름2...) VALUES (데이터값1, 데이터값2...);필드 이름을 지정하고 순서대로 넣으면 된다.
INSERT INTO 테이블이름 VALUES (데이터값1, 데이터값2...);필드 이름을 생략하면, 그냥 있는 순서대로 다 들어간다. 만약 필드가 3개 있고 2개만 넣고 싶다면, 생략해선 안되겠다. 아님 null값을 박든가
INSERT INTO user(name, email) VALUES ('hello', 'world@mail.com');
name과 email을 넣었다.
SELECT (Read)
데이터를 확인하려면 주구장창 써야하는 명령어다.
SELECT * FROM user;user에 모든 데이터를 불러오는 명령어다. 개인 프로젝트 단위에서는 그다지 많은 DB가 없으니까 상관 없는데, 실무에서 이러면 sql 터질 수도 있지 않을까 싶다. 그럴 경우 limit를 써서 로우 갯수를 한정지어주자.
insert를 하나 더 해서 확인해보면
hello와 howdy가 있다. id는 자동 증가임.
만약 howdy만 뽑고 싶다면
WHERE
SELECT * FROM user WHERE name = 'howdy';
이런식으로 뽑을 수 있다. 만약 email도 필요 없고 name과 id만 보고 싶다면 또 다른 SELECT * 을 고치면 된다.
SELECT id, name FROM user WHERE name = 'howdy';
ORDER BY
정렬할 때 쓸 수 있다. 여기에 옵션을 붙이면 오름차순 내림차순으로 정렬이 가능하다.
id 순으로 잡혀 있음을 확인 할 수 있다. 만약 name으로 사전식 정렬은 원한다면
SELECT * FROM user ORDER BY name;
이런 식으로도 가능하다. 만약 내림차순을 원한다면 DESC를 옆에 붙여주면 된다.
SELECT * FROM user ORDER BY name DESC;
LIMIT
로우가 엄청나게 많다면? * 로 불러오면 박살이 날거다. limit는 갯수 제한을 지정할 수 있다.
SELECT * FROM user LIMIT 3;
총 5개의 로우가 있었는데 limit로 3개만 불러옴.
DISTINCT
중복제거하는 명령어다.
중복을 일단 만들어보자. name이 abc인 애를 한개 더 만들겠음.
3번과 6번의 이름이 같다.
여기서 이름의 종류가 필요하다고 하면, 중복값을 제거해야 하겠지? 그 때 DISTINCT를 쓰면 된다.
SELECT DISTINCT user FROM user; 
여러개를 조합할 수 도 있고, 1개 특성만 DISTINCT를 지정할 수도 있다.
JOIN
join은 여러개의 테이블을 지정한 옵션에 맞춰서 합치는 작업이다.
이게 꽤 중요한데, 이를테면 member 테이블과 게시판 테이블이 있을 때, member의 userId와 게시판의 userID를 비교해서 같다면 권한을 준다. 뭐 이런 세션을 만들어야 한다면 꼭 필요함.
INNER JOIN
기준 테이블과 조인 테이블 모두 데이터가 존재해야 조회된다.
위이이이에 계속 봐온 user를 이용해보자.
새로운 테이블 wallet를 만들어서 id, name, money를 넣어줬음.
hello와 howdy를 집어 넣고 돈을 넣어보자!
자 그럼 INNER JOIN을 이용해서 user와 wallet를 합체해보자. 죄다 합체 할거임.
SELECT * FROM user INNER JOIN wallet;
그냥 무식하게 때려 박아졌다. ㅋㅋㅋㅋㅋㅋㅋ 우리가 원하는 그림은 이게 아니긴 했지. 다시 옵션을 추가해보자.
SELECT * FROM user INNER JOIN wallet ON user.name = wallet.name;
우리가 원하는 방식에 살짝 가까워졌다. id랑 name이 중복이니까 조금 더러워 보인다. select *을 고치자
SELECT user.id, user.name, user.email, wallet.money FROM user INNER JOIN wallet ON user.name = wallet.name;
훨씬 깔끔한 결과가 나왔다.
이 예제에서 중요한건 select로 테이블과 그에 맞는 정확한 컬럼을 선택하고, 어떤 걸 기준으로 join할건지 정하는데에 있다. 조금 복잡해보인다면, as라는 별칭 기능을 사용해도 먹는다.
OUTER JOIN
언듯 보면 Inner join가 비슷해 보일 수 있다. Inner join과 다르게 left right를 지정해서 써야 한다. 이는 기준 테이블을 지정하는 방향이다.
SELECT * FROM user LEFT OUTER JOIN wallet ON user.name = wallet.name;
기준 테이블에 있는 모든 요소를 가져오고, join한 테이블은 다 null처리가 된다. 여기서도 select를 정리해주면 깔끔하게 볼 수 있다.
SELECT a.id, a.name, a.email, b.money FROM user AS a LEFT OUTER JOIN wallet AS b On a.name = b.name;
여기서는 AS를 사용해서 별칭을 부여했다. Right 조인은 left와 반대로 join 테이블의 모든게 조회된다. 기준 테이블이 바뀐다고 보면 좋을듯.
OUTER JOIN은 기준 테이블의 모든 데이터를 누락없이 조회하고 참조 테이블의 값이 있을 경우 해당 값을 사용하기 위해서 많이 쓴다.
그 외에도 CROSS JOIN, FULL OUTER JOIN이 있다. 자주 쓰진 않는듯.
UPDATE (update)
UPDATE 테이블이름
SET 필드이름 = 데이터 값, 필드이름2 = 데이터 값2 ...
WHERE 필드이름 = 데이터값이런 방법으로 레코드 내용을 수정할 수 있다. WHERE은 필수적인게, 이걸 안하면 전체 테이블이 다 수정될 수도 있다.
그러므로 WHERE로 무적권 한정지어서 UPDATE를 해야한다.
UPDATE wallte SET money = 30000 WHERE name = 'hello';
hello의 name의 money가 30000으로 수정됐음을 알 수 있다. where를 안하면 다 바뀌니까 조심요.
DELETE (delete)
DELETE FROM 테이블이름
WHERE 필드이름 = 데이터값;이 친구도 마찬가지로 WHERE가 필수다. 안그러면 테이블 전체 삭제가 되므로 무적권임.
DELETE FROM wallet WHERE name = 'howdy';
howdy를 삭제했다.
데이터 타입
숫자형
unsigned 속성을 부여하면 음수값을 대입하지 못할 뿐 아니라 대입 가능한 숫자의 범위를 늘려줄 수있다.
아무래도 양방향으로 길이가 잡혀 있는데 음수값을 없애버리면 앞통수로 더 늘릴 수 있겠지?
문자형
게시판 글이면 mediumText나 longText가 좋겠지?
트랜잭션과 ACID
A가 B에게 계좌 송금을 2,000원을 했다.
은행 DB에서 A계좌 금액을 -2,000원 하고, B에 +2,000원을 명령했다.
근데 -2,000원이 A계좌에서 빠져나감과 동시에 DB에서 알지못할 에러가 튀어나왔다.
그 덕에 B 계좌엔 +2,000원이 안됐다. (Exception 터짐)다행히 개발자가 트랜잭션을 잘 해놨기 때문에 DB가 Commit 되지 않고 Rollback돼서
A계좌의 -2,000원이 다시 복구 됐다.
상기 상황은 1개의 트랜잭션 안에서 일어난 일을 대충 써둔거다.
과거 선조들은 try, catch문을 이용해서 트랜잭션을 만들었고, Exception이 발생할 경우 catch에서 롤백을 했다.
요샌 어노테이션 하나면 다 해결이 되긴 함.
모든 DB는 기본적으로 AutoCommit 상태다. insert문을 쓰든 update를 하든 우리가 날린 명령이 바로 commit 되므로, rollback을 할 수 없다.
즉 트랜잭션을 연다 라는 뜻은 AutoCommit 기능을 일단 끈다! 라는 소리와 같을 수 있다.
트랜잭션은 4개의 성질을 가지고 있다.
-
Atomicity (원자성)
하나의 트랜잭션에 속해있는 모든 작업이 전부 성공하거나, 전부 실패해야 한다. 만약 트랜잭션 내에서 1개라도 실패할 시 모든 명령이 실패로 바뀌어야 한다.
상기 계좌이체 예시만 보더라도 A 명령은 성공했지만, B가 실패했다. 이 경우 A도 실패처리를 해야 (rollback) DB 오염이 없다. -
Consistency (일관성)
DB 상태는 항상 일관되어야 한다. 트랜잭션 이전과 이후, DB의 상태는 제약이나 규칙이 그대로여야 한다.
당연하게도 A계좌 송금을 했는데, B계좌가 아니라 이름없는 계좌에 만들어져서 들어가면 규칙이 깨진거다.
여기서 중요한건 이름 없는 계좌가 새로 만들어졌다는 거임.
이게 일관성을 해친다. -
Isolation(격리성, 고립성)
모든 트랜잭션은 다른 트랜잭션으로부터 독립되어야 한다. 이를테면 A계좌 송금을 -2,000원을 해야 하는데 C가 그 사이에 +3,000원을 A에게 송금했다손 치자. DB에서 A계좌 돈이 10,000원이 있었는데, 8,000원이 되어야 하는데 다른 트랜잭션 때문에 13,000원이 될 수 있다. 이럴 경우 격리성이 지켜지지 않은거다.
이를 지키기 위해서 보통 하나의 트랜잭션에 A, B의 계좌가 들어갈 경우 그 DB는 잠궈진다. 그리고 그 트랜잭션이 종료된 이후에 C의 트랜잭션이 발동한다. 이를 통해 격리성을 지킬 수 있다. -
Durability(지속성)
트랜잭션이 성공적으로 수행되었다면, 해당 트랜잭션에 대한 로그가 남아야 한다. 만약 오류가 발생하더라도 해당 기록은 영구적으로 있어야 한다. 즉 내역이 남아있어야 한다는 뜻이다.
