[이것이 MySQL이다] 6장 정리

쿼리

create database sampledb;

use sampledb;

create table sampleTBL (name char(10) not null primary key, age int null);

insert into sampleTBL values("홍길동", 30);
-- insert into sampleTBL(name) values("홍길동"); 불가능
insert into sampleTBL(name) values("김김김");
/*
insert into sampleTBL(age) values(30); 불가능
*/

핵심 포인트

• create 명령어를 통해 데이터베이스를 생성할 수 있다.

• use 명령어를 통해 데이터베이스를 선택할 수 있다.

• primary key로 설정된 속성은 중복된 값을 가질 수 없다.

• insert 명령어를 통해 데이터를 삽입할 수 있다.

• primary key를 입력하지 않고 데이터를 삽입할 수 없다.

• 주석은 —, /* */ 명령어로 작성할 수 있다.

usertbl 테이블

buytbl 테이블

쿼리

use sqldb;	-- 시작하기 전에 어떤 db를 참조할 지 명시

select *	-- price가 300 이상인 모든 칼럼을 출력
from buytbl
where price>=300;

select name, height, addr
from usertbl
-- where birthyear>=1970 and height>=182;
-- where height between 180 and 183;
-- where addr in ('경남', '전남', '경북');	-- 해당 값들을 가지는 주소
-- where addr like '_남'; -- 앞에 한 글자만 오고 남으로 끝나는 주소만
where name like '김%'; -- 김으로 시작하고 몇글자든 뒤에 오는 이름만

핵심 포인트

• select문은 데이터베이스 내의 테이블에서 원하는 정보를 추출하는 명령이다.
• select문은 크게 select, from, where, group by, having, order by 절로 나뉜다.

• select 문을 사용하기 이전에 use 명령어를 통해 현재 사용하는 데이터베이스를 지정하거나 변경할 수 있다.

• select문에는 출력하기를 원하는 칼럼을 작성한다.

• from문에는 데이터를 가져올 테이블을 작성한다.

• where문은 가져올 칼럼의 조건을 지정한다.

쿼리

select name, height, addr	-- 메인쿼리 
from (
	select a.name, a.height, a.addr	-- 서브쿼리
	from usertbl as a 
	where name like '김%'
) as b -- alias(별칭 부여) 
where b.height<=175;

select name, height
from usertbl
where height>(	-- 서브쿼리를 통해 원하는 조건을 입력할 수 있다
	select height
	from usertbl
	where name='김경호'
);

select *
from buytbl
where userid = any (	-- any: 서브쿼리의 여러 개 결과 중 하나라도 만족
	select userid
	from usertbl
	where birthyear > 1975
);

select height
from usertbl
where height > all (	-- all: 서브쿼리의 결과 중 여러 개의 결과를 모두 만족
	select height	-- select max(height)가 더 성능이 좋음
	from usertbl
	where birthyear < 1975
);

select max(height) as '가장 큰 키' -- 위와 같은 결과 출력
from usertbl
where birthyear < 1975;

핵심 포인트

• 서브쿼리란 쿼리문 안에 또 쿼리문이 들어 있는 것이다.

• any는 서브쿼리의 여러 개 결과 중에 하나라도 만족해도 된다.

• all은 서브쿼리의 결과 중 여러 개의 결과를 모두 만족시켜야 한다.

쿼리

select userid, prodname, amount	-- order by 기본값: 오름차순
from buytbl
order by amount desc;

select distinct(userID) 	-- distinct로 중복 없이 출력
from buytbl
where amount>=3;

select *	-- limit: 상위 5개 값만 가져옴
from usertbl
limit 5;

핵심 포인트

• order by를 통해 출력된 결과를 정렬할 수 있으며, 기본값은 오름차순이다.

• distinct()를 통해 중복된 값을 하나만 보여주게 할 수 있다.

• limit를 통해 출력할 결과의 개수를 설정할 수 있다.

쿼리

create table buytbl_backup	-- create table ... select로 테이블 복사 가능
select * from buytbl;

핵심 포인트

• create table … select문으로 원하는 테이블을 복사할 수 있다. 이 경우 제약조건이 복사되지는 않는다.

쿼리

select userid, sum(price*amount) as total -- 집계 함수로 칼럼 생성 가능
from buytbl
group by userid -- group by로 그룹핑
having total>100 -- having으로 집계 함수 조건 설정 가능
order by total desc;

select userid, max(price), min(price), count(userid), avg(price*amount)
from buytbl
group by userid;

select count(distinct(prodname))
from buytbl;

핵심 포인트

• group by 절을 통해 그룹핑을 할 수 있다.

• 집계 함수는 group by 절과 함께 쓰이며 데이터를 그룹화해주는 기능을 한다.
• sum(), avg(), min(), max(), count(), count(distinct), stdev(), var_samp() 등의 집계함수가 있다.

• having절을 통해 집계 함수에 대해서 조건을 제한할 수 있다.

쿼리

select * from usertbl;
update usertbl
set mobile2 = '12341234'
where userid='bbk';	-- where절 없이 update하면 데이터 전부에 덧씌워진다.

delete from usertbl where userid='yjs'; -- where절 없이 delete하면 데이터 전부를 삭제한다.

drop table buytbl_backup; -- drop은 table을 삭제한다.
truncate table usertbl; -- truncate는 트랜젝션 로그를 기록하지 않으므로 속도가 빠르다.

핵심 포인트

• update문을 통해 기존에 입력되어 있는 값을 변경할 수 있다.

• delete 문을 통해 행 단위의 데이터를 삭제할 수 있다.

• update와 delete문을 사용할 때 where문을 작성하지 않으면 데이터 전부에 영향을 끼치므로 조심해야 한다.

• truncate문은 delete문과 비슷하지만, 트랜잭션 로그를 기록하지 않기 때문에 속도가 delete문보다 빠르다.

쿼리

select u.userid, sum(price*amount)
from usertbl as u
join buytbl as b -- join은 연산횟수가 많으므로 잘 써야 한다. 잘못 쓰면 성능이 떨어진다. join문과 서브쿼리는 서로 변환할 수 있는 형태다.
on u.userid = b.userid
where u.height < 175
group by u.userid;

select b.userid, sum(price*amount) -- 키가 175 이하인 사람들이 구매한 총액
from buytbl as b
where b.userid = any (	-- any를 in으로 써도 결과는 같다.
	select u.userid
	from usertbl as u
	where height < 175
)
group by userid;

핵심 포인트

• 두 개 이상의 테이블이 서로 관계되어 있는 상태에서 join을 통해 두 개 이상의 테이블을 서로 묶어서 하나의 결과 집합으로 만들어 낼 수 있다.

• join은 연산 횟수가 많으므로 쿼리의 구조를 잘 짜야 오버헤드를 줄일 수 있다.

• join으로 작성할 수 있는 쿼리문은 대부분 서브쿼리의 형태로도 작성할 수 있으므로, 둘 중 오버헤드가 적게 생기는 경우를 사용한다.

결론

• 해당 코드들을 작성하면서 기본적인 SQL문의 문법과 join을 활용하는 방법을 익힐 수 있었다.