3/16(목) IT K-DT(11일차) / 2.데이터연산및검색

 

 

---

2. 데이터 연산 및 검색

 

2-1. SQL 연산

 

* 산술 연산자
  +, -, *, /, mod(SQL의 공식적인 '나머지' 연산자), div(SQL의 공식적인 '몫' 연산자)

* 비교 연산자
  =, <, >, >=, <=, <>(다르다)

* 대입 연산자
  =

* 논리 연산자
  and, or, not   

* 기타 연산자
  is: 양쪽이 모두 같으면 true, 아니면 false.
  between A and B: 값이 A보다 크거나 같고 B보다 작거나 같으면 true, 아니면 false.
  in: 매개변수로 전달된 리스트에 값이 존재하면 true, 아니면 false.
      예) userid in ('apple', 'banana')
  like: 패턴으로 문자열을 검색하여 값이 존재하면 true, 아니면 false.

 

2-2. 데이터 검색

 

기본적인 형태

SELECT 필드명1, 필드명2 ... FROM 테이블명;

select 100; select 100+50;

위와 같이 ' from 테이블명 ' 이 없어도 됨.

 

별명의 설정

SELECT 필드명 AS 별명 FROM 테이블명;

select 100+50 as ' 덧셈 '; #  별명에 '  ' (홑따옴표) 를 사용하는 이유는 띄어쓰기가 있을 수 있기 때문. select 100+50 as ' 덧셈 연산 '; select 100+50 as 덧셈;

예) member 테이블의 필드값 userid를 아이디, username을 이름, hp를 휴대폰번호로 바꾸어 데이터 출력.

SELECT userid AS 아이디, username AS 이름, hp AS 휴대폰번호 FROM member;

 

모든 컬럼 가져오기

SELECT * FROM member;

실무에서는 모든 데이터의 load를 추천하지 않음. → 연산량이 매우 많아지므로 속도가 느려지기 때문.

 

null과 ' '

select null;          # 아예 insert가 되지 않은 상황. 데이터가 없음. select 100 + null; # 결과:null. 연산 자체가 안됨. select ' ';              # 해당 셀에 ''데이터가 삽입되었다는 의미. 데이터가 있음. select 100 + ' ';     # 결과:100.

 조건절

SELECT 필드명1, 필드명2 ... FROM 테이블명 WHERE 조건절

예) userid가 'kim'인 조건의 userid, username, hp, email의 데이터를 출력.

SELECT userid, username, hp, email FROM member WHERE userid='kim';

예) gender가 '남자인' 조건의 userid, username의 데이터를 출력.

SELECT userid, username FROM member WHERE gender='남자';

조건의 필드값이 select 내부에 없더라도 출력 가능.

 

is null / is not null
예) 필드값 'address1'가 공백인 조건을 찾고 싶은 경우

SELECT userid, username, hp FROM member WHERE address1 is null; # 정확한 방법. SELECT userid, username, hp FROM member WHERE address1 is not null; # null이 아닌 경우를 찾고 싶은 경우. SELECT userid, username, hp FROM member WHERE address1 = 'null'; # 잘못된 방법1. SELECT userid, username, hp FROM member WHERE address1 = null; # 잘못된 방법2.

between

SELECT 필드명1, 필드명2 ... FROM 테이블명 WHERE 조건필드명 BETWEEN A값 AND B값;

예) member 테이블에서 point가 300이상 600이하인 userid와 username을 출력.

SELECT userid, username, point FROM member WHERE point BETWEEN 300 AND 600; SELECT userid, username, point FROM member WHERE point >= 300 AND point <= 600;

 

like

SELECT userid, username FROM member WHERE userid like ' k % '  ;      # k로 시작하는 문자열 SELECT userid, username FROM member WHERE userid like ' % g ' ;       # g로 끝나는 문자열 SELECT userid, username FROM member WHERE userid like ' % i % ' ;    # i가 포함되는 문자열 SELECT userid, username FROM member WHERE userid like ' % a r % ' ; # ar가 포함되는 문자열 SELECT userid, username FROM member WHERE userid like ' jeo _ _ ' ;   # jeo으로 시작하는 5글자인 문자열

정렬

SELECT 필드명1, 필드명2 ... FROM 테이블명 ORDER BY 정렬할필드명 [ASC, DESC]

예) member 테이블에서 userid를 오름차순으로하여 userid, username, point를 출력

SELECT userid, username, point FROM member ORDER BY userid ASC; SELECT userid, username, point FROM member ORDER BY userid; # 오름차순이 기본값이므로, 'asc'는 생략을 할 수 있음

예) member 테이블에서 userid를 내림차순으로하여 userid, username, point를 출력

SELECT userid, username, point FROM member ORDER BY userid DESC;

예) member 테이블에서 point를 내림차순으로하고, 같은 point인 경우 userid로 내림차순하여 출력

SELECT userid, username, point FROM member ORDER BY point DESC, userid DESC;

조건절 + 정렬

SELECT 필드명1, 필드명2 ... FROM 테이블명 WHERE 조건명 ORDER BY 정렬할필드명 [ASC, DESC]

'조건절' 다음 '정렬'
예) 성별이 여성인 회원을 point가 많은 순으로 정렬 (단, 포인트가 같을경우 먼저 가입한 순으로 정렬)

SELECT * FROM member WHERE gender='여자' ORDER BY point DESC, regdate ASC;

 

limit

SELECT 필드명1, 필드명2 ... FROM 테이블명 LIMIT 가져올 행의 갯수   SELECT 필드명1, 필드명2 ... FROM 테이블명 LIMIT 시작행, 가져올 행의 갯수

예) select * from member;로 출력된 테이블 기준으로 출력.

SELECT userid, username, gender FROM member LIMIT 3;

예) member 테이블에서 필드값 userid, username, gender로 인덱스 3행부터 2개 행의 데이터를 출력.

SELECT userid, username, gender FROM member LIMIT 3, 2;

 

정렬 + limit

SELECT 필드명1, 필드명2 ... FROM 테이블명 ORDER BY 정렬할필드명 [ASC, DESC] LIMIT 가져올 행의 갯수

'정렬' 다음 'limit'.

예) member 테이블에서 필드값 userid, username, point로 point를 내림차순으로 하여 3개의 데이터를 출력.

SELECT userid, username, point FROM member ORDER BY point desc LIMIT 3;

 

2-3. 함수

count: 집계함수. 행의 갯수를 세는 함수.
예) member 테이블에서 userid의 갯수를 집계하여 출력.

SELECT count(userid) FROM member;  SELECT count(userid) AS 전체인원 FROM member; # userid를 '전체인원'으로 이름을 바꿔서 출력.

예) member 테이블에서 zipcode의 갯수를 집계하여 출력.

SELECT  count(zipcode) FROM member; # 0개가 출력. null값은 제외하고 출력이 되기 때문.

null값을 제외하고 출력이 되기 때문에, null이 아닌 값의 갯수를 알 수 있음.

 

 

sum: 행의 값을 더하는 함수.
예) member 테이블에서 point의 합을 집계하여 필드명을 '포인트합'으로 바꿔서 출력.

SELECT  sum(point) AS 포인트합 FROM member;

예) member 테이블에서 zipcode의 갯수를 집계하여 출력.

SELECT  userid, sum(point()) AS  포인트합 FROM member; # 에러발생. 집계함수는 group없이 출력할 수 없음.

 

 

avg: 행 값의 평균을 구하는 함수.
예) member 테이블에서 point의 평균을 집계하여 필드명을 '포인트평균'으로 바꿔서 출력.

SELECT avg(point) AS 포인트평균 FROM member;

 

 

min: 행 값의 최소값을 구하는 함수.
예) member 테이블에서 point의 최소값을 집계하여 필드명을 '최소값'으로 바꿔서 출력.

SELECT min(point) AS 최소값 FROM member;

max: 행 값의 최대값을 구하는 함수.
예) member 테이블에서 point의 최대값을 집계하여 필드명을 '최대값'으로 바꿔서 출력.

SELECT max(point) AS 최대값 FROM member;

 

 

2-4. 그룹

 

그룹

SELECT 그룹을맺은컬럼/집계함수 FROM 테이블명 GROUP BY 그룹을맺을필드명;

예) member 테이블에서 gender를 그룹으로 하여 출력.

SELECT  gender FROM member GROUP BY gender;

예) member 테이블에서 gender를 그룹으로 point를 합하는 집계함수를 적용하여 출력.

SELECT  gender, sum(point) FROM member GROUP BY gender;

예) member 테이블에서 gender를 그룹으로 인원 수를 확인하여 출력 (집계함수 count 사용).

SELECT  gender, count(userid) FROM member GROUP BY gender;

 

그룹 + 그룹조건

SELECT 그룹을맺은컬럼/집계함수 FROM 테이블명 ( GROUP BY 그룹을맺을필드명 HAVING 조건절 ) ;

예) member 테이블에서 gender가 '여자'인 조건으로 gender를 출력.

SELECT  gender FROM member GROUP BY gender HAVING gender = '여자';

조건절 + 그룹 + 그룹조건 + 정렬

SELECT 그룹을맺은컬럼/집계함수 FROM 테이블명 ( WHERE 조건절 GROUP BY 그룹을맺을필드명 HAVING 조건절 ORDER BY 정렬할 필드명 [ASC, DESC] ) ;

예) 포인트가 0이 아닌 회원들 중에서 남, 여로 그룹을 나눠 포인트의 평균을 구하고 평균포인트가 100 이상인 성별을 

     검색하여 포인트로 내림차순 정렬.

SELECT gender, avg(point) as avg FROM member WHERE point<>0 GROUP BY gender HAVING  avg>=100 ORDER BY avg DESC;

avg(point)를 as를 이용해 'avg'로 주어서 작성하는게 편리함.

 

---

TIP

 

* 빅데이터분석기사, 데이터전문가 자격증과 다르게 정보처리기사 자격증은 취득하는 것을 추천.