Union, GROUP BY / HAVING, Join, GRANT

• Union : 두개의 select문의 대한 결과를 합쳐서 출력하는 명령문 <<조건>>

1. 두 select문의 column의 개수가 같아야 한다.
2. 두 select문의 서로 대응되는 column의 데이터 타입이 같아야 한다.
   • 2번 이거 왜 에러가 안나냐?!??
   • 실행시 두 select 검색에 결과를 합쳐서 반환.... Syntax(문법) -UNION(distinct기능이 동작됨) **SELECT column_name(s) FROM table1_name UNION SELECT column_name(s) FROM table2_name;** 예제) select ID from Persons UNION select PersonID from Orders; -UNION ALL(distinct동작X) **SELECT column_name(s) FROM table1_name UNION ALL SELECT column_name(s) FROM table2_name;** 예제) select ID from Persons UNION ALL select PersonID from Orders; 실습) Person 테이블에 있는 20대 사용자의 ID와 나이, Orders의 PersonID와 주문번호를 같이 출력하세요. select id, age from persons where age >= 20 and age <= 29 union select personId, ordernumber from orders;

• GROUP BY / HAVING(group by의 조건문, 단독으로 사용 X)
  • GROUP BY는 출력된 레코드를 그룹으로 묶고 각 그룹에 대한 요약값 계산시 사용함. HAVING은 GROUP BY에서만 사용하는 조건식을 사용할 경우에 쓰는 구문
  • Syntax(문법) : SELECT 집단함수(column_name) FROM table_name GROUP BY coloum_name HAVING condition;
  • 예제1) Persons테이블에 있는 도시별 사는 사람들의 숫자를 구하는 예제
    • select count(city), city from persons group by city;
  • 예제2) Persons테이블에 있는 도시별 사는 사람들의 숫자를 구하는 예제를 도시별 사람 숫자를 기준으로 정렬
    • select count(city), city from persons group by city order by count(city) desc;
  • 실습1) Persons테이블에 나이가 20이상인 사람들을 나이로 그룹화하고 그룹별로 인원수 조회 내림차순 정렬을 하세요.
    • select age, count(age) as aCount from persons where age >= 20 group by age order by count(age) desc;
      • desc : 내림 차순
• HAVING 조건문 : GROUP BY 구문 내용에 따른 조건을 부여할 때 사용하는 구문
  • 예제3) Persons테이블의 내용을 도시를 기준으로 그룹화하고, 인원을 구하되 3인이상의 결과를 출력해주세요
    • select city, count(city) from persons group by city having count(city)>3;
  • 실습2) 나이가 20이상인 사람들이 사는 지역 도시 별 인원을 구하고, 숫자가 2이상 5미만인 경우 출력하세요
    • select city, count(city) as cCount from persons where age >= 20 group by city having cCount >=2 and cCount <5;
• Join
  • 조인을 사용하는 이유, 데이터를 여러 테이블로 나누면(정규화) 저장 공간을 보다 효율적으로 사용할 수 있고, 조작이 간편하며, 확장하기도 편하다.
  • 하지만 데이터가 여러 테이블로 저장되어 있으면 하나의 SELECT문으로 이 데이터를 어떻게 얻을 것인지가 문제가 된다. 이때에 사용하는 것이 바로 JOIN이다.
  • 즉, 여러 테이블로 나눠져 있는 데이터를 조합하여 출력하는 것이 바로 JOIN이다.
  1. INNER JOIN
     • 두 테이블을 대상으로 동일성 테스트를 하여 그 결과를 기준으로 조인(교집합)
     • 예) SELECT FirstName,Age,City,OrderNumber,OrderDate FROM Persons INNER JOIN Orders on Persons.ID=Orders.PersonID;
       • on 이후로는 조건을 적어줘야 한다. # foreign key의 형식을 탄다.
  2. OUTER JOIN
     • 한 테이블의 행을 다른 테이블의 행과 연결하는 방식
     • LEFT OUTER JOIN : 조인되는 두 테이블 중 왼쪽 테이블의 모든 행이 결과 관계없이 출력하고, 오른쪽 테이블의 결과를 추가로 출력하는 형식
       • 예) SELECT FirstName,Age,City,OrderNumber,OrderDate FROM Persons LEFT OUTER JOIN Orders on Persons.ID=Orders.PersonID;
         • persons를 우선 처리 → 거기 맞춰 출력된다.
     • RIGHT OUTER JOIN : 조인되는 두 테이블 중 오른쪽 테이블의 모든 행이 연결 여부와 관계없이 모두 출력, 왼쪽 테이블의 결과를 추가로 출력하는 형식
       • 예) SELECT FirstName,Age,City,OrderNumber,OrderDate FROM Persons RIGHT OUTER JOIN Orders on Persons.ID=Orders.PersonID;

---

• GRANT(권한 부여) 사용자 권한 부여.
  • Syntax(문법-형식) : GRANT all privileges on db_name.table_name to userid@host;
    • all : 허용할 권한을 부여하는 곳
    • db_name.table_name : 데이터베이스와 권한을 부여할 테이블 명을 지정
      • ex) testDB.Persons
    • userid@host : userid는 사용자를 host는 DB에 접속할 접속장비를 의미함.
      • create user testuser1; # testuser1@% > 계정@장비(host)
      • create user testuser1@localhost; # testuser1@localhost, localhost로만 접근이 가능하다.
        • localhost : 현재 접속 장비
        • 위의 두 계정은 다른 계정이다.
    • password : 각 계정에 사용할 패스워드를 입력
      • alter user testuser1 identified by 'testuser1'; # password 수정 alter user testuser1@localhost identified by 'testuser1';
      • create user testuser2 identified by'testuser2'; # 계정이 생성되자마자 바로 password 추가까지 진행
      • drop user testuser2; # 유저 삭제
  • 예1) 로컬에서 모든 DB 및 테이블에 접근 권한 설정 : GRANT all privileges on . to testuser1
    • GRANT all privileges on . to root@localhost ; -> DBMS 내에 있는 모든 DB와 테이블에 로컬 장비에서 root는 모두 허용.(패스워드는 P@ssw0rd)
  • 예2) 모든 DB 및 테이블에 대해 사용자에게 권한을 주고 리모트에서 접속 가능하게 설정 GRANT all privileges on . to userid@'%';

    • GRANT all privileges on . to test@'%' ; -> DBMS 내에 있는 모든 DB와 테이블에 대해서 로컬 및 리모트(원격)으로 test유저의 모두 허용 (패스워드는 P@ssw0rd)

  • 예3) 'testDB'에 대한 DB접근 및 Persons테이블에 접근 권한 설정 GRANT all privileges on testDB.Persons to userid@'%'';
    • GRANT all privileges on testDB.Persons to test@localhost identified;
  • 권한 설정 적용 : flush privileges;
  • 권한 삭제 : REVOKE all on db_name.table from userid@host ;
  • 권한 조회 : show grants for userid@host ;

정리

select id from persons union select PersonID from orders;
-- disctinct 가 동작하고 있다 (중복을 제거하고 있다)

select id, age from persons union select PersonID, ordernumber from orders;
select PersonID, ordernumber from orders union
select id, firstname from persons ;

select id from persons union all select PersonID from orders;
-- 중복을 제거하지 않을 경우

#Person 테이블에 있는 20대 사용자의 ID와 나이, Orders의 PersonID와 주문번호를 같이 출력하세요.

select id, age from persons where age >= 20 and age <= 29
union select personId, ordernumber from orders;

select count(city), city from persons
group by city;

# 예제2) Persons테이블에 있는 도시별 사는 사람들의 숫자를 
# 구하는 예제를 도시별 사람 숫자를 기준으로 정렬
select count(city), city from persons group by city order by count(city) desc;

select age, count(age) as aCount from persons where age >= 20 
group by age order by count(age) desc;

select city, count(city) from persons group by city having count(city)>3;

select city, count(city) as cCount from persons where age >= 20 
group by city having cCount >=2 and cCount <5;

SELECT FirstName,Age,City,OrderNumber,OrderDate FROM Persons 
INNER JOIN Orders on Persons.ID=Orders.PersonID;
# foreign key

SELECT FirstName,Age,City,OrderNumber,OrderDate
FROM Persons LEFT OUTER JOIN Orders on Persons.ID=Orders.PersonID;

SELECT FirstName,Age,City,OrderNumber,OrderDate
FROM Persons RIGHT OUTER JOIN Orders on Persons.ID=Orders.PersonID;

create user testuser1; # testuser1@% > 계정@장비(host)
create user testuser1@localhost; # testuser1@localhost, localhost로만 접근이 가능하다.

alter user testuser1 identified by 'testuser1'; # password 수정
alter user testuser1@localhost identified by 'testuser1';

create user testuser2 identified by'testuser2';

drop user testuser2;

GRANT all privileges on *.* to testuser1;
show grants for testuser1; # 무엇을 할 수 있는지에 대한 이야기를 해준다.

revoke all on *.* from testuser1; # from 빨간 줄은 신경쓰지 않아도 된다.

grant select, update, delete on testdb.* to testuser1@localhost;
show grants for testuser1@localhost; 

mysql 유저 생성

• 사용자 계정과 관련된 정보는 mysql 데이터베이스 내에 user 테이블에 저장됨.
• 사용자 생성 -단순 사용자 생성

create user [userid]; => mysql 사용자 생성...

• 생성시 사용자 패스워드 설정 (localhost접속 계정)
• create user 'userid'@'localhost' ;
• (원격 접속 계정) : create user 'userid'@'%' identified by 'password';
• select host,user,password from mysql.user;