190829 기본 명령문 예제

use sqlDB;
select * from userTbl;
select userId, Name from userTbl where birthYear >= 1970 And height >=182;
select userID, name from userTbl where birthYear >= 1970 or height >=182;
select Name, height from userTbl where height >=180 and height <=183;
select name, height from usertbl where height between 180 and 183;
select name, addr from usertbl where addr='경남' or addr='전남' or addr='경북';
select name, addr from usertbl where addr in ('경남','전남','경북');
select name, height from usertbl where name LIKE '김%'; -- %는 모든 문자
select name, height from usertbl WHERE name LIKE '_종신'; -- -는 앞글자는 아무거나 맨앞에 _ 쓰지마세요, 종_신 이런건 되는데 _ 성능에 문제생김
select name, height from usertbl where height > 177;
select name, height from usertbl
	WHERE height>(select height from userTbl where Name='김경호'); -- subquery
select name, height from usertbl
	where height >= (select height from userTbl where addr='경남'); -- 결과가 한줄 도 없다; 이유는 비교 기준이 2개이상 일 순 없음
select name, height from usertbl
	where height >= ANY (select height from usertbl where addr='경남'); -- any 그 중 어느것 하나라도, 여러개중 하나라면 랜덤? 기준이 있을까?
select name, height from usertbl
		where height = ANY (select height from usertbl where addr='경남');
select name, height from usertbl
		where height in (select height from usertbl where addr='경남');
select Name, mDate from usertbl order by mDate; -- mDate순으로 정렬하여 name, mDate추출
select Name, mDate from usertbl order by mDate DESC; -- DESC, ASC 정렬할수 있는데 기준이 필요 없으면 자동으로 asc로 정렬한다
select Name, height from usertbl order by height desc, name asc; -- 1기준 키 내림, 2기준 이름 오름
select addr from usertbl;
select addr from usertbl ORDER BY addr;
select DISTINCT addr from usertbl; -- 중복제거, 정렬되지 않음. group by 는 정렬되어 나옴 == java collection set 과 동일(중복허용X) == JS set

select emp_no, hire_date from employees.employees -- employees의 employees를 찾아가기 때문에 이렇게 해도 된다.
	order by hire_date asc;
use employees;
select emp_no, hire_date from employees
	order by hire_date asc;
select emp_no, hire_date from employees
	order by hire_date asc
	limit 0, 5; -- limit 5 offset 0  limit : n번째 row 출력, n번째 행까지 출력

use sqlDB;
create table buytbl2 (select * from buytbl);
select * from buyTbl2;

create table buytbl3 (select userID, prodName from buytbl);
select * from buytbl3;

select userID, sum(amount) from buyTbl group by userID;

select userID as '사용자 아이디', SUM(amount) AS '총 구매개수'
	from buytbl group by userID;

select userID as '사용자아이디' , sum(price*amount) as '총 구매액'
	from buytbl group by userID;
    
select avg(amount) as '평균 구매 개수' from buytbl;

select userID, avg(amount) as '평균 구매개수' from buytbl group by userID;

select name, max(height), min(height) from userTbl; -- 가장 큰 키와 가장 작은 키를 가져옴
select name, max(height), min(height) from usertbl group by name;

select name, height from usertbl where height=(select max(height) from usertbl)  -- 가장 큰 키와 가장 작은 키를 가진 사람을 가져옴
									or height=(select min(height) from usertbl); 
select count(*) from usertbl;
select count(mobile1) as '휴대폰이 있는 사용자' from usertbl;
SELECT userID as '사용자', sum(price * amount) as '총구매액'
	from buytbl
    group by userID;
    
select userID as '사용자', sum(price*amount) as '총구매액' 
	from buytbl 
	where sum(price*amount) > 1000 
	group by userID;
    
select userID as '사용자', sum(price*amount) as '총 구매액'-- having 은 where와 비슷한 개념, 조건을 제한함. 집계함수에 대해 조건을 제한하는것으로 생각
	from buytbl
    group by userID
    having sum(price*amount) > 1000; -- 항상 group by 다음에 있어야 한다.

select userID as '사용자', sum(price*amount) as '총 구매액'
	from buytbl
    group by userID
    having sum(price*amount) > 1000
    ORDER BY sum(price*amount) DESC;
    
select num, groupName, sum(price*amount) as '비용'
	from buytbl
    group by groupName, num
    with ROLLUp; -- GROUP BY에서 선택한 기준에 따라 합계, num과  groupname의 합계, 
    
select groupName, sum(price*amount) as '비용'
	from buytbl
    GROUP BY groupName
    with ROLLUP;
    



create table buytbl4 (select userID, prodname, amount from buytbl);
select * from buytbl4;

use sqldb;
desc buytbl;