23.03.16MySQL연산자 이용하기

목차

1. MySQL연산자 이용하기

• 연산자
  • 산술연산자
  • 비교연산자
  • 대입연산자
  • 논리연산자
  • 기타연산자
• 연산자 이용해보기
  • 데이터 검색
  • 데이터 별명사용
  • 모든 칼럼 가져오기
  • null과 ' ' 의차이
  • 조건절
  • is 기타연산자
  • between 기타연산자
  • like 기타연산자
  • 정렬
  • 조건절  + 연산자
  • Limit (-MySQL전용)
  • 정렬 + Limit
• 집계함수
  • count
  • sum
  • ave
  • min
  • max
• 그룹
  • 그룹 + 조건절  + 그룹조건  + 정렬

---

SQL 연산자 

1. 산술연산자 

• +,   -,   *,   /,    mod( % (가능은 하지만 잘쓰지 않음)),   div(몫 을 구하는 연산)

2. 비교연산자

• =(같다,조건절에 들어가면 비교연산자.),     <,     >,     <=,     >=,     <>(다르다)

3. 대입연산자  (=)

• 대입연사자(=) 비교연산자의 (=)와 다르다. 

                                             대입연산자           비교연산자
         update member set point = 100 where gender = '여자'

 

4. 논리연산자

• and,   or,   not,   xor(피연산자가 서로 다르면 True 을 반환하고, 서로 같으면 False 을 반환함.)

5. 기타연산자

• is : 양쪽이 모두 같으면 true 또는 다르면 false
• between A and B : 값이 A보다는 크거나 같고, B보다는 작거나 같으면 turn 아니면 false
• in : 매개변수로 전달된 리스트에 값이 존재 하면 true 아니면 false
             userid in ('apple', 'banana') true냐? false냐?
• like : 패턴으로 문자열로 검색하여 값이 존재 하면 true 아니면 false

---

1.연산자 이용해보기

• kdt 데이터베이스와 member 테이블은 https://sangdal91.tistory.com/26 여기서 한걸 그대로 사용하겠습니다. 

   1-1.데이터 검색

• 문법 :  select 필드명1, 필드명2 .... from 테이블명;

 use kdt;  # MySQL을 사용하기 위해 kdt라는 DB 선택하기. 
 select userid, username, gender from member;
 select username, gender, userid from member; -- 필드의 순서는 바껴도 전혀 상관 없다.

1-2.데이터 별명 사용

•  문법 : select 필드명 as 별명 from 테이블명;

select 100 + 50 as '덧셈'; -- ''를 사용하는 이유는 단지 띄어쓰기 있을 수 있어서 사용한다.
select 100 + 50 as '덧셈 연산'; -- 띄어쓰기가 있으면 무조건 해줘야한다. 실무에서는 칼럼에 띄어쓰기를 잘 넣지않는다. 
select 100 + 50 '덧셈';  -- as 생략 가능하다. 
select 100 + 50 as 덧셈; -- '' 생략 가능하다.

select userid as 아이디, username as 이름, hp as 핸드폰번호 from member;

1-3.모든 칼럼 가져오기

• 문법 : select * from 테이블명;

 

1-4.null과 ' ' 의차이

• null 의 값은 데이터가 없음을 뜻합니다. (insert 되지 않음)
• ' ' (싱글쿼터 두개)는 데이터가 삽입 되어 있는것을 뜻합니다. 
• 제일 큰 차이로 null의 값은 연산할 수 없고, ' ' 는 연산할 수 있습니다.  ([예시] 맨마지막)

• select null; # 데이터가 없음, insert가 되지 않은 것

select null; # 데이터가 없음, insert가 되지 않은 것

• select '';   # 해당 셀에 '' 데이터가 삽입된 것

select '';   # 해당 셀에 '' 데이터가 삽입된 것

• select 100 + null;  # 결과값 : null, 연산할 수 없음

select 100 + null;  # 결과값 : null, 연산할 수 없음

• select 100 + ''; # 결과값 : 100, 연산이 가능하다.

select 100 + ''; # 결과값 : 100, 연산이 가능하다.

 

1-5.조건절

• 문법 : select 필드명1,필드명2,...... from 테이블명 where 조건절

select userid, username, hp, email from member where userid='apple'; 
select userid, username from member where gender='남자';
select userid, username, point from member where point >= 300;

• 로그인 프로그램 쿼리

/*로그인 프로그램 쿼리*/
#데이터가 나오면 로그인 성공 , 데이가 나오지 않으면 로그인 실패 
select userid, username, hp, email from member where userid='apple' and userpw = '1234';
select userid, username, hp, email from member where userid='apple' and userpw = '1111';

1-6.is 기타연산자

• 주소에 null값이 들어가는 사람들의 정보 찾기
  

select userid, username, hp from member where address1 = 'null' ; -- 잘못된 방법 !
select userid, username, hp from member where address1 = null ; -- 잘못된 방법 !
select userid, username, hp from member where address1 is null ; -- 옳은 방법 ! -- is: 양쪽 모두 참인 경우 
select userid, username, hp from member where address1 is not null ; -- 옳은 방법 !

 

1-7.between 기타연산자

select userid, username, point from member where point between 300 and 600; -- 300 <= porint <=600
select userid, username, point from member where point >= 300 and point <= 600; -- 위와 같은 쿼리

 

1-8.like 기타연산자

select userid, username from member where userid like 'a%'; -- a로 시작하는 문자열 찾아주세요
select userid, username from member where userid like '%a'; -- a로 끝나는 문자열
select userid, username from member where userid like '%a%'; -- a가 들어가는 문자열
select userid, username from member where userid like '%app%'; -- app가 들어가는 문자열 
select userid, username from member where userid like 'app__'; -- app으로 시작하는 총 5글자인 문자열, _(언더바)의 갯수: 글자수

 

1-9.정렬

• 문법 :  select 필드명1, 필드명2, .... from 테이블명 order by 정렬할 필드명 [asc, desc] 
• 기본값: asc(오름차순)  생략 가능

select userid, username, point from member order by userid asc; -- 아이디 오름차순 정리
select userid, username, point from member order by userid desc; -- 아이디 내림차순
select userid, username, point from member order by userid;

• 1-9.예문) 포인트를 기준으로  내림차순 정렬을 하고 , 같은 포인트인 경우 아이디로 내림차순 정렬 

select userid, username, point from member order by point desc, userid desc ;

1-10.조건절  + 연산자

• 규칙 - select 필드명1,필드명2 ..... from 테이블명 where 조건절 order by 정렬할 필드명 [asc,desc];
•  조건절과 정렬이 순서가 바뀌면 안돼!

• 1-10.예문) 성별이 여성인 회원을 point가 많은순으로 정렬 (단, point가 같을경우 가입한 순서대로 정렬)

select userid, username, gender, point, regdate from member where gender = '여자' order by point desc, regdate asc;

1-11.Limit (-MySQL전용)

• limit 는 -- MySQL전용
• 문법 : select 필드명1,필드명2,..... from 테이블명 limit 가져올 행의 갯수
• 문법 : select 필드명1,필드명2,..... from 테이블명 limit 시작행, 가져올 행의 갯수
  • 시작행을 index[  ]라고 생각하면 쉽다. 

select userid, username, gender from member limit 3;
select userid, username, gender from member limit 3, 2;

1-12.정렬 + Limit

• 문법 : select 필드명1,필드명2,..... from 테이블명 order by 정렬할 필드명 [asc,desc] limit 가져올 행의 갯수;
  • 포인트가 3등까지 뽑기

select userid, username, point from member order by point desc limit 3;

---

2.집계함수

• 내가 원하는 행을 묶어서 계산(연산) 해주는 함수

2-1. count

• 행의 갯수를 세는 함수, null을 제외하고 카운트 하기 때문에 null포함된 행은 사용하면 값이 다르게 나올수 있다

. 

• 2-1.예문) "전체인원을 알고 싶다 !" 
• primary key가 적용되어 필드를 적용하는게 좋다 ! primary key는 null값이 존재 할 수 없기 때문에

select count(userid) from member;
select count(userid) as 전체인원 from member;

• 2-1.예문) 우편번호를 입력한 인원을 알고 싶다 ! : null이 있으면 우편번호를 입력 하지 않았기 때문에

select count(zipcode) as 우편번호 from member;

2-2. sum 

• 행의 값을 더함 

select sum(point) as 포인트합 from member;

• 에러코드 

userid 한명당 point의 값이 1개뿐이 없기 때문에 말이 안되서 에러 발생

select userid, sum(point) as 포인트합 from member;

#Error Code: 1140. In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'kdt.member.userid'; this is incompatible with sql_mode=only_full_group_by

 

2-3. ave

• 행 값의 평균을 구함

select avg(point) as 평균 from member;

2-4. min

• 행의 최소값을 구함

select min(point) as 최소값 from member;

2-5. max

• 행의 최대값을 구함

select max(point) as 최대값 from member;

---

3.그룹

• 일반 필드는 들어올수 없음
• 문법 : select 그룹을 맺은 칼럼 또는 집계함수 from 테이블명 group by 그룹을 맺을 필드명;

select gender from member group by gender; 
select gender, sum(point) as 합계 from member group by gender;
select gender, avg(point) as 평균 from member group by gender;
select gender, count(point) as 인원수 from member group by gender;

3-1.그룹 + 조건절 + 그룹조건 + 정렬 

• 문법 : select 그룹을 맺은 칼럼 또는 집계함수 from 테이블명 where 조건절 group by 그룹을 맺을 필드명 having 조건절 order by 정렬할 필드명 [asc,desc];
• 한줄로 이어서 써야합니다. 

select gender from member group by gender having gender = '여자';

• 3-1예문) point가 0이 아닌 회원들 중에서 남/여로 그룹을 나눠서 포인트의 평균을 구하고 평균point가 100이상인성별을 검색 하여 point로 내림차순 정리한다. 

select gender, avg(point) as avg from member where point > 0 group by gender having avg >= 100 order by avg desc;         -- as avg 별명으로 진행
select gender, avg(point) from member where point > 0 group by gender having avg(point) >= 100 order by avg(point) desc;  -- 별명 없이 그냥 진행

---