10장. MySQL 데이터베이스 사용하기(p. 303)
1. SQL이란?
- SQL : Structured Query LAnguage의 약자.
- 관계형 데이터베이스 관리 시스템 (RDBMS, Relational Database MAnagement System)을 다루는 표준언어
- SQL은 데이터베이스에 데이터를 저장하고 불러오는데 사용한다.
- MySQL, Oracle, PostreSQl, Sybase, 마이크로소프트 SQL Server등에 SQL이 쓰인다
- MySQL 온라인 메뉴얼 : http://dev.mysql.com/doc/refman/5.1/en/compatibility.html
- DDL(Data Definition Languages) : 데이터베이스 정의할때 쓰임
DML(Data Manipulation Languages) : 데이터베이스에서 쿼리를 실행시킬때 쓰임
- SQL에서 DML은 데이터베이스에 실제 데이터를 저장하고 불러오는 부분이기 떄문에 DDL보다 자주 사용
2. 데이터베이스에 데이터 삽입하기
- SQL에서 INSERT라는 구문이 데이터 삽입을 맡는다.
- RDBMS에서 데이터는 테이블 형식으로 줄단위로 이루어짐.
각 줄은 실제 객체나 관계를 설명하며 열의 값은 실제 객체에 대한 정보를 저장함.
- INSERT 구문의 형식 :
INSERT [INTO] table [(colums1, colums2, colums3,...)] VALUES (value1, value2, value3, ...0;
ex.) customers라는 테이블에 데이터를 삽입하고 싶다면,
insert into cutomers values (NULL, 'seheekim', 'seoul', 'republic of korea');
- 위와 같이, table자리에 원하는 테이블 실제 이름을 넣고 value자리에 특정 값을 넣는다.
- 이 값들이 문자열이면 작은따옴표(')나 큰따옴표(")를 붙인다. (숫자나 날짜에는 필요없음)
- 데이터들은 테이블에 순서대로 첫번쨰 열에서부터 저장됨. 따라서 어떤 특정한 열에만 데이터를 삽입하고 싶거나,
다른 순서로 삽입할때는 구문에 열의 이름을 명시해줌.
ex) insert into cutomers (name, city) values ('sally', 'hanra apartment');
또는, insert into customers set name='sally', address='hanra apartment' city='suwon';
- 여기서 위의 구문을 삽입할 때, id값이 없다. 전 포스팅 보면 데이터베이스를 설정할 때
id는 이 테이블의 기본 키였기때문에 id 열을 auto_increment라고 해주었다면(대부분 id 열은 auto_increment해준다)
삽입하지 않아도 자동으로 숫자를 증가시켜 입력하게 된다.
- INSERT문은 여러가지로 변형 가능
: 다음과 같은 명령어를 INSERT뒤에 붙일수 있다
LOW_PRIORITY? 테이블에서 데이터가 읽혀지지 않았을 때에 시스템이 기다렸다가 뒤에 삽입해도 된다는 뜻
DELAYED? 삽입한 데이터를 버퍼링하라는 뜻. 만약 서버가 바쁘다면 INSERT문이 완료되는 것을 기다리지 않고 게속 쿼리를 진행
IGNORE? 고유키를 가지는 다른 레코드와 중첩되는 레코드를 삽입하려고 할 때 그냥 무시하고 삽입하지 않는다는 뜻
ON DUPLICATE KEY UPDATE expressionㅇ? 중첩되는 값이ㅔ 있을 떄 평범한 UPDATE문과 같은 역할을 한다.
* 참고사항
> mysql -h host -u bookorama -p books < /path/to/book_insert.sql
하면 /path/to에 있는 book_insert.sql 파일을 데이터벵스에 입력가능
3. 데이터베이스에서 데이터 불러오기
- 기본적인 SELECT 구문은 다음과 같음
SELECT [options] items
[INTO file_details]
FROM tables
[WHERE conditions]
[GROUP BY group_type]
[HAVING where_difinition]
[ORDER BY order_type]
[LIMIT limit_criteria]
[PROCEDURE proc_name(arguments)]
[lock_option]
;
- 아무런 옵션 없이 테이블에서 특정 데이터를 읽어오는 쿼리 (이런 아이템은 테이블의 한 열이다)
ex. cutomers 테이블에서, name 열과 city열에 들어있는 데이터들을 불러옴
select name, city from customers;
- select 키워드 옆에 필요한 열의 이름을 나열하는데, '*'을 쓰면 '모든'이라는 뜻로 테이블의 모든 열을 가져옴
ex. order_item 테이브르이 모든 열을 가져옴
select * from order_items;
3-1. 특정 조건을 만족시키는 데이터들만 불러오기
- 테이블안에서 어떤 조건을 만족시키는 데이터만 불러올 때 : WHERE을 사용함
ex. select * from orders where customerid = 3;
- where 구문에서 자주 사용되는 비교연산자
where customerid = 3
where amount > 60.00
where amount <60.00
where amount <= 60.00
where quantity !=0;
where address is not null //이 필드에 값이 들어있는지 확인
where address in null //이 필드에 값이 들어있지 않은지 확인
where between 0 and 60.00 //이 값이 최소값 이상 최대값 이하인지 확인
where city in ("Carlton","Moe") //이 값이 특정 집합 안에 있는지 확인
where city not in ("Carlton", "Moe") //이 값이 집합 안에 없는지 확인
where name like ("Fred%") //값이 특정 패턴에 맞는지 확인
where name not like ("Fred%") //값이 특정 패턴에 맞지 않는지 확인
- like는 간단한 sql 패턴 일치를 사용함.
여기에서 '%'는 몇개의 문자든 모두 대응되며 (ex. Fred%는 Fred A도 되고 Fred Apple, Fred RedApple도 됨)
'_'는 하나의 문자에 대응 됨
- REGEXP는 정규 표현식의 매치에서 쓰인다. MYSQL은 POSIX 정규표현식을 사용.
REGEXP 대신에 RLIKE를 사용할 수 있는데, 같은 뜻임.
- AND나 OR를 사용하여 간단한 연산자들과 패턴 매칭을 가지고 복잡한 조건을 만들 수있음
ex. select * from orders where customerid = 3 or customerid = 4;
3-2. 여러개의 테이블에서 데이터 불러오기
- 조인(join) : 두개 이상의 테이블에 들어있는 데이터 사이에 연관성이 있을 때,
그리고 연관성을 통해서 무언가를 얻어내고자할 때 조인을 사용.
1) 간단한 두개의 테이블에서의 조인
ex. select order.orderid, order.amount, orders.date
from customers, orders
where customers.name = 'Julie Smith'
and customers.customerid = orders.customerid;
- 특정 테이블의 특정 열을 가리킬 때는 '.'을 사용한다.
이는 customers.customerid는 customers 테이블의 customerid 열을 말한다.
- 똑같은 열의 이름이 여러 테이블에서 사용되는 경우 '.'를 사용한다.
- '데이터베이스.테이블.열'로 사용할 수도 있다.
2) 두개 이상의 테이블 조인
ex. select customers.name
from customers, orders, order_items, books
where customers.customerid = orders.customerid
and orders.orderid = order_items.orderid
and order_items.isbn = books.isbn
and books.title like '%Java%';
- 네 가지 테이블을 사용해 데이터 추출
- equi-join이고 세가지 조인 조건을 사용하였다.
- 한 쌍의 테이블을 조인시킬 때 각각 하나의 조인 조건이 필요하므로 조인 조건의 개수는 테이블 개수보다 하나 적은 수가 된다.
3) 일치하지 않는 행 찾기
- LEFT JOIN은 MYSQ에서 주로 사용되는 조인 중 하나
- 두 테이블의 행들을 명시된 조인 조건에 따라 매치시키는데 한쪽에 매치되는 데이터가 없을 떄에는 대신에 NULL을 넣어서 추가
ex. select customer.customerid, customers.name, orders.orderid
from cutomers left join orders
on customers.cutomerid = orders.customerid;
- 예제에서 보는 바와 같이 LEFT JOIN이 사용되었고, 다른 조인 구문들과는 조금 다르게 'ON'이 사용됨
4) 별칭 : 테이블에 다른 이름 붙이기
- 테이블에 잠시 다른 이름을 붙여 사용하면 편한데 이러한 다른 이름을 별칭(alias)이라고 함
- 쿼리의 시작 부분에 선언해서, 끝날 때 까지 사용가능하며 길고 복잡한 이름을 가진 테이블들을 사용한
쿼리문을 만들어야 할 때 별칭을 사용해서 간단히 사용하자
- 사용방법 : 원래의 테이블 이름 as 별칭으로 쓸 이름
4. 특정 순서로 데이터 불러오기
- 데이터베이스에서 불러온 데이터를 특정한 순서로 출력해보고 싶다면 select문에서 ORDER BY를 사용
ex. select name, address from customers order by name;
- 기본적으로 오름차순(a부터z)으로 정렬된다 .ASC는 오름차순(asscend)라는 뜻.
ex. select name, address from customers order by name asc;
- 내림차순(DESC, descend)로 정렬시킬 수도 있다
5. 데이터 통계값 구하기
- 쿼리의 결과에 몇개의 데이터가 들어있는지, 결과 데이터의 평균값이 얼마인지 알고싶을때 쓰는 함수가 몇가지 있음
- 다음은 mysql의 통계 함수
이름 |
설명 |
AVG(column) |
특정 열(column)에 있는 값들의 평균 |
COUNT(item) |
특정 열이 주어졌을 때 NULL이 아닌 항목의 개수를 알려준다. |
MIN(column) |
특정 열의 최소값 |
MAX(column) |
특정 열의 최대값 |
STD(column) |
특정 열의 표준편차 |
STDDEV(column) |
STD(column)과 같다. |
SUM(column) |
특정 열의 값들의 합 |
6. 어떤 행을 리턴할지 선택하기
- LIMIT는 결과에서 어떤 행을 리턴할지를 지정
- LIMIT에는 두개의 숫자가 필요함. 첫번째 수자는 시작부분을 나타내고 두번째 숫자는 리턴할 행의 개수를 지정
ex. select name from custmers limit 2,3;
- 위의 예시를 해석하면 'cusmtoer에서 name을 select하라. 그 결과중에 행 번호가 2부터 3개, 즉 2,3,4행을 리턴하라'라는 뜻.
7. 하위 쿼리 사용하기
- 하위쿼리란 ? 다른 쿼리의 내부에 속하는 쿼리
- 임시 테이블과 조인을 잘 사용하면 하위 쿼리의 기능 대부분을 구현할 수 있지만 하위 쿼리를 사용하는 편이 읽거나 쓰기 더 쉬움
7-1) 기본 하위 쿼리
- 하위 커리는 대부분 한 쿼리의 결과를 다른 쿼리와 비교할 때 사용
ex. select customerid, amount from orders where amount = (select max(amount) from
- 위의 예시는 주문들 중 가장 큰 주문의 양(amount, 열)이 얼마나 되는 알고 싶을때의 쿼리문
- 조인을 사용하면 같은 결과를 낼 수 있다
ex. select customerid, amount from orders order by amount desc limit 1;
7-2) 하위 쿼리와 연산자
- 특별 하위 쿼리 연산자에는 다섯 가지가 있음.
- 네개는 일반적인 하위 쿼리에서 사용하고 하나(EXISTS)는 관련된 하위 쿼리에서만 사용
- 다음은 일반적인 하위 쿼리 연산자
이름 |
문법 예제 |
설명 |
ANY |
SELECT c1 FROM t1 WHERE c1> ANY(SELECT c1 FROM t2); |
하위 쿼리에 있는 줄과 비교가 참인 것이 있으면 ture 리턴 |
IN |
SELECT c1 FROM t1 WHERE c1> IN(SELECT c1 FROM t2); |
=ANY와 동일 |
SOME |
SELECT c1 FROM t1 WHERE c1> SOME(SELECT c1 FROM t2); |
ANY의 별칭, 사람이 보기에는 이쪽이 더 합리적. |
ALL |
SELECT c1 FROM t1 WHERE c1> ALL(SELECT c1 FROM t2); |
하위 쿼리에서 반환한 모든 줄과의 비교가 참일 경우 ture 반환 |
7-3) 상위 하위 쿼리
- 상관된 하위 쿼리에서는 상위 쿼리에서의 아이템을 내부 쿼리에 사용할 수 있다.
ex. select isbn, title form bobks where not exists (select * from order_item where order_items.isbn=books.isbn);
- 이 쿼리는 상관 하위 쿼리의 사용과 마지막으로 남은 특별한 하위쿼리의 연산지인 EXISTS의 사용을 설명함
- EXISTS 연산자는 만약 하위 쿼리에 일치하는 줄이 있으면 ture를 반환.
7-4) 줄 하위 쿼리
- 모든 하위쿼리들은 단 하나의 값만을 반환하며 대체로 ture나 false를 반환.
- 하지만 줄 하위 쿼리는 전체 줄을 반환하여 상위 쿼리와 줄 전체를 비교하므로 다른 테이블에 똑같은 줄이
있는지 비겨할 수 있다.
ex. select c1, c2, c3 from t1 where(c1,c2,c3) in (select c1,c2,c3 from t2);
7-5) 하위 커리를 임시 테이블로 사용하기
- 하위 쿼리문을 FORM절에서 쓸수 있다
- 하위 쿼리의 결과를 효과적으로 사용할 수 있는 임시 테이블처럼 여기게 된다
ex. select * from (select customerid, name from customer where city='box hill') as
box_hill_cumoters;
8. 데이터베이스에 저장된 데이터 변경
- UPDATE 구문의 형식은 다음과 같다.
UPDATE [LOW_PRIORITY] [IGNORE] tablename
SET colum1=expression1, colum2=expression2, ...
[WHERE condition]
[ORDER BY order_criteria]
[LIMIT number]
9. 테이블이 만들어진 후에 테이블의 구조 변경하기
- ALTER TABLE 구문은 테이블의 구조를 변경하는 역할을 한다.
- 기본 형식 : ALTER TALBE [IGONORE] tablename alteration [, alteration...]
- IGNORE 절이 명시되어 있고 변경에 의해 기본키가 중첩될 경우, 첫번째만이 변경된 테이블에 들어가고 나머지
중첩된 데이터들은 삭제됨. 만약 IGONORE절이 없다면(기본값) 변경 쿼리가 실패하고 테이블을 원래로 돌린다.
- 다음은 'alteration'부분에 들어갈수 있는 몇가지 명령어에 대해 나열하였다.
(p. 322)
10. 데이터베이스에 저장된 데이터 삭제하기
- DELET [LOW_PRIORITY] [QUICK] [IGNORE] FROM table
[WHERE condition]
[ORDER BY order_cols]
[LIMIT number]
- 테이블 안의 모든 데이터 삭제 : delete from table;
- where 구문으로 조건 명시하여 삭제 : delete from tablename where id = 5;
- LIMIT는 삭제 가능한 행의 수를 'number'에 적혀있는 개수 이하로 제한해주는 역할을 함
- ORDER BY는 LIMIT와 같이 사용
11. 테이블 삭제하기
- 테이블 전체가 필요없게 되었을 경우 사용
- DROP TABLE table;
12. 데이터베이스 전체 삭제하기
- DROP DATABASE로 구데이터베이스 전체 삭제
- DROP DATABASE database;
