추가 DML
JOIN문, Sub Query문
→ 더 상세한 내용의 SELECT 문을 응용할 수 있음
JOIN 문 : 두 테이블을 묶어서 하나의 테이블을 만듦.
( 왜? 두 테이블을 엮어야 원하는 형태가 나오기도 함. )
SELECT 속성명 FROM 테이블명1, 테이블명2 WHERE 조인조건 AND 검색조건;
SELECT 속성명 FROM 테이블명1 INNER JOIN 테이블명2 ON 조인조건 WHERE 검색조건;
테이블명.속성명 표기 : 특정 테이블의 열을 가르킴
WHERE 절을 이용한 JOIN 문
SELECT * FROM customer, orders WHERE customer.custid = orders.custid;
SELECT * FROM customer INNER JOIN orders ON customer.custid = orders.custid;
SELECT * FROM customer JOIN orders ON customer.custid = orders.custid;
-> INNER 생략가능
SELECT * FROM orders JOIN customer ON customer.custid = orders.custid;
-> 테이블명 순서가 바뀌면, 앞에 테이블 속성부터 차례로 추출
orders, customer 테이블이 연결되어 조회
-> custid라는 속성으로 관계를 맺고 있음
custid 순으로 정렬
SELECT * FROM customer, orders
WHERE customer.custid = orders.custid
ORDER BY customer.custid;
SELECT * FROM customer INNER JOIN orders
ON customer.custid = orders.custid
ORDER BY customer.custid;
custid 속성을 오름차순으로 조회
고객별로 주문한 제품 총 구매액 조회
SELECT custname, SUM(price*amount)
AS 'total_price' FROM customer
JOIN orders ON customer.custid = orders.custid
GROUP BY custname ORDER BY custname;
SELECT custname, SUM(price*amount)
AS 'total_price' FROM customer, orders
WHERE customer.custid = orders.custid
GROUP BY custname ORDER BY custname;
orders, customer 테이블의 관계를 맺고 있음
custname 속성 그룹화하여 custname 속성 오름차순으로
customer 테이블의 custname 속성과 orders 테이블의 SUM(price*amount) 속성만 조회
SUB QUERY 문
:SQL 문 내에 또 다른 SQL 문 작성
SELECT 문의 WHERE 절에 또 다른 테이블 결과를 이용해서 다시 SELECT 문을 괄호로 묶는다.
SELECET ~ FROM ~ WHERE ( SELECT ~ FROM ~ ) ~ ;
STEP 1. 주문 금액이 가장 큰 주문 내역은 무엇인가?
SELECT MAX(price*amount) AS'Max' FROM orders;
STEP 2. 가장 큰 주문 금액이 내역의 주문아이디, 고객아이디, 상품명 조회
SELECT orderid, custid, prodname FROM orders WHERE price*amount = 10000;
STEP 3. 서브 쿼리를 이용해 두 SQL 문을 하나로 합치기
SELECT orderid, custid, prodname FROM orders
WHERE priceamount = ( SELECT MAX(priceamount) AS'Max' FROM orders );
주문 이력이 있는 고객 조회
SELECT custname FROM customer WHERE custid IN ( SELECT custid FROM orders );
데이터 조작어
DCL ( Data Control Language )
: 데이터베이스에 접근 권한 부여 및 박탈
‘codee’라는 새로운 user 생성
CREATE USER '계정명'@'접속경로' IDENTIFIED BY '비밀번호';
CREATE USER 'codee'@'localhost' IDENTIFIED BY '4321’
사용자 삭제
DROP USER '계정명'@'접속경로';
DROP USER 'codee'@'localhost';
FLUSH PRIVILEGES;
-> 새로고침해야 반영됨
참고) MySQL 사용자 비밀번호 변경하고 싶다면?
ALTER USER '계정명'@'접속경로' IDENTIFIED WITH mysql_native_password BY '변경할비밀번호';
ALTER USER 'codee'@'localhost' IDENTIFIED WITH mysql_native_password BY '1234';
FLUSH PRIVILEGES;
-> 새로고침해야 반영됨
GRANT 문
: 특정 데이터베이스 사용자에게 특정 작업에 대한 수행 권한 부여
💡 권한유형을 구별하는 이유?
회사 내의 직급에 따라 접근할 수 있는 권한 유형을 구분함
예) 신입이 모든 권한을 부여받는다? 불가능
예) 퇴직한 사원의 계정에 대한 권한을 삭제하는 등
GRANT 권한유형 ON 데이터베이스명.테이블명 TO ‘계정명’@’접속경로’ INDENTIFIED BY ‘설정비밀번호’ WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON . TO 'codee'@'localhost' WITH GRANT OPTION;
모든 데이터베이스의 모든 테이블에 접근 가능 : .
모든 권한을 부여받음 : GRANT ALL PRIVILEGES
다른 사용자한테 권한을 부여 가능하게 하는 옵션 : WITH GRANT OPTION→ ‘codee’ 라는 사용자는 모든 데이터베이스와 모든 테이블에 접근할 수 있는 모든 권한을 부여받음 그리고 다른 사용자에게 권한을 부여할 수 있는 권한도 부여받음
Git Bash를 통해 새로 생성된 사용자 ’코디’를 통해 로그인가능
권한을 부여할 수 있는 예시
GRANT [권한 유형] ON [데이터베이스이름].[테이블이름]
GRANT ALL PRIVILEGES ON [데이터베이스이름].*
=> 특정 데이터베이스의 모든 테이블에 / 모든 권한 부여GRANT ALL PRIVILEGES ON [데이터베이스이름].[테이블이름]
=> 특정 데이터베이스의 특정 테이블에 / 모든 권한 부여GRANT SELECT ON [데이터베이스이름].[테이블이름]
=> 특정 데이터베이스의 특정 테이블에 / SELECT 권한 부여GRANT SELECT, INSERT ON [데이터베이스이름].[테이블이름]
=> 특정 데이터베이스의 특정 테이블에 / SELECT, INSERT 권한 부여
CREATE USER 'happy'@'localhost' IDENTIFIED BY '1234';
GRANT SELECT ON new_smartfactory.* TO 'happy'@'localhost' WITH GRANT OPTION;
‘happy’ 라는 새로운 사용자를 생성하고
new_smartfactory 데이터베이스의 모든 테이블에 SELECT 문에 대한 권한을 부여함.
SELECT 문은 조회 되지만,
INSERT 문은 권한이 없기 때문에 에러메시지 1142이 뜸
REVOKE 문
: 특정 데이터베이스 사용자에게 특정 작업에 대한 권한 박탈
REVOKE 권한유형 ON 데이터베이스명.테이블명 FROM ‘계정명’@’접속경로’;
REVOKE DELETE ON * *. ** FROM 'codee'@'localhost';
‘codee’ 사용자에게 모든 데이터베이스의 모든 테이블에서의 DELETE문에 관한 권한을 박탈시킴
SELECT, INSERT문은 조회가 가능하나,
DELETE문은 권한을 박탈하였기에 불가능
