0부터 시작하는 Maria DB 공부 - SQL 기본 3

1. SQL의 권한과 종류

P. 205

• GRANT ALL PRIVILEGES ON . TO 'root'@'localhost' : root는 모든 데이터베이스 ( . ) 와 모든 테이블 ( . ) 에 대해 모든 권한 ( ALL PRIVILEGES )를 갖는다

  • 앞 이 데이터 베이스, 뒤 가 테이블을 나타낸다
  • 'root'는 계정, @ 뒤에는 접속할 주소다. 모든 주소를 허용할려면 '%' 로 하면 된다

• SQL의 구분

  • DML : 데이터 자체를 조작을 위한 퀴리 ( SELECT/INSERT/UPDATE/DELETE )
  • DDL : 데이터 베이스 개체를 생성/삭제/수정하는 쿼리 ( 데이터베이스나 테이블을 조작한다. ex. CREATE/DROP/ALTER )
  • DCL : 사용자에게 권한을 부여하고, 뺏는 쿼리 ( GRANT/REVOKE/DENY )

2. DB & TABLE 생성

p. 308

• labDB가 존재한다면, 해당 데이터 베이스를 삭제시키는 DDL 구문을 실행시키고, labDB를 생성시킨다
  
• 다음과 같이 TABLE을 생성해준다. 데이터 타입은 p.224를 참조하자

  • SMALLINT를 통해 INT가 사용하는 4 바이트 보다 사용하는 데이터 공간을 줄여준다

• desc로 TABLE의 구조를 확인하자
  
• 이제 물품 구매 테이블을 생성한다. 구매 테이블은 유저 테이블의 ID를 FK로 받으며, 구매 번호는 AUTO INCREMENT로 설정해야한다. 이 AUTO INCREMENT로 지정한 값은 반드시 PK 혹은 UNIQUE로 지정해야한다

  • 이러한 번호를 생성할 때 길게 만드는 것이 구분하기 수월하다
  • FK는 부모 테이블 참조하는 데이터 타입 및 설정이 반드시 같아야한다
  • 이로서 usertbl과 buytbl은 1:N ( 0 이상 ) 의 관계를 형성한다
  • buytbl에서는 usertbl에 없는 user에 대한 data를 데이터 무결성으로 인해 입력할 수 없다

• ALTER TABLE ~ AUTO_INCREMENT = N; 을 통해 N 부터 숫자가 증가하게 할 수 있다 ( p.208 )

  • SET @@auto_increment_increment=N;을 통해 숫자가 N만큼 증가하게 할 수 있다

• Heidi SQL에서 FK를 확인해보자
  
• information_schema에는 DB에 대한 정보가 담겨있다. 이 중 테이블 제약 조건을 출력하는데, buytbl에 대한 정보를 출력한다. 이로써 PK와 FK에 대한 정보를 확인 가능하다

3. 데이터 입력

• 데이터를 입력한다. 가입 날짜는 CURDATE()를 통해 현재 날짜를 입력한다. mdate 타입을 date로 했기에 날짜 데이터가 들어갈 수 있다
  
• num은 AUTO_INCREMENT로 설정했기에 값을 따로 입력하면 안된다. 따라서 NULL로 입력하여 공간을 비워둔다. 그러면 자동으로 값이 입력된다
  
• 잘 입력됬다

4. Foreign Key 삭제 및 생성

P. 321

• 무결성을 위해서 CASCADE를 통해 이미 입력된 data에 대해 usertbl에서 user의 id가 바뀌면, buytbl에서의 id도 바뀌게 해야한다. 기존 FK를 새 FK로 대체하자

  • ON UPDATE CASCADE : 변경시 같이 변경
  • ON DELETE CASCADE : 삭제시 같이 삭제
  • FK 이름은 테이블 제약 조건 정보에 표시된 이름이다. 따라서 buytbl_ibfk_1을 삭제해준다. 그 후, 새 제약조건을 추가해서 FK의 이름을 설정하고, FK에 대한 설정을 해준 뒤, 마지막에 데이터 변경에 대한 CASCADE 설정을 해준다

• 잘 적용되었다

  • 테이블에 대한 변경이므로 DDL의 ALTER를 사용한다

5. 데이터 수정

P. 210

• UPDATE ~ SET ~ 을 통해 DATA 수정이 가능하다
  
• 다음과 같이 GROUPNAME이 전자인 제품들의 가격을 1.5배 증가도 가능하다
  
• userid도 수정해보자. userid를 변경했기에 buytbl에서 user2에 대해 DATA가 user02로 자동 수정되야 한다
  
• 잘 수정됬다
  
• 삭제도 해보자

6. INNER JOIN

p. 264

• JOIN 의 종류

  • INNER JOIN : 두 테이블에 모두 있는 정보만 결합하여 출력
  • OUTER JOIN : 두 테이블에 있는 데이터 + 한 쪽에만 있는 데이터

• INNER JOIN 형식

  SELECT 열 이름
  FROM 첫 번째 테이블
  INNER JOIN 두 번째 테이블
  ON 조인될 조건 ;

  • 다음과 같이 사용한다
    

• INNER JOIN 테이블에서는 구매한 이력이 있는 USER의 정보를 출력한다. 따라서 구매한 이력이 없는 USER는 출력되지 않는다

• 이렇게 실행하면, userID가 양 테이블에 있기에 이를 명시하지 않아서 구분할 수가 없으므로 오류가 발생한다. 따라서 앞에 어느 테이블인지 명시해야 한다
  

• 이렇게 양 테이블에 존재하는 DATA에 대해서는 테이블을 명시해서 테이블 명. 열 이름으로 작성해야 한다. 한 테이블에만 존재하는 DATA에 대해서는 어느 테이블인지 구분이 가능하므로 열 이름만 작성해도 된다. 왠만해서는 테이블 이름을 작성해서 어느 테이블인지 명시해주자

• CONCAT은 안에 들어있는 DATA를 한 줄로 합쳐서 출력해준다
  

• 다음과 같이 테이블 이름을 변경해서 사용할 경우, 모든 테이블 명시에 대해 변경된 이름을 사용해야 한다. 만약 userTbl을 U로 변경했는데, userTbl.~을 사용할 경우 오류가 난다. 이는 이름을 변경해서 사용한다고 명시했기에 본래 테이블 이름을 인지 못하기 때문이다

• QUIZ. 두 테이블을 INNER JOIN 하여 구매자의 이름, 주소, 휴대폰 번호, 제품명, 수량만을 포함하는 VIEW를 v_tracking 이라는 이름으로 생성하라. 또한 이 테이블에 접속할 수 있는 알바생의 계정인 alba 1을 생성하고, alba 1은 해당 뷰에서 select 만 가능하게 하라

  

  • VIEW를 생성해준다
    
  • alba1에 대해 view에서 select 만을 허용해준다. 이로써 해당 계정으로 접속했을때 buytbl에 접근하면, 위와 같이 error가 난다
  • 이는 해당 계정에 대한 기능 권한과 접속 권한을 부여하는 것이다. 접속 권한을 부여했기에 이는 계정을 생성하는 것이기도 하다

    • MYSQL에서는 CREATE USER를 통해 USER를 생성하고, GRANT로 권한을 부여해야 하지만, MariaDB에서는 GRANT로 USER 생성과 동시에 권한 부여가 가능하다

  • FLUSH privileges는 GRANT 테이블을 RELOAD 하여 SQL 환경 설정에 대한 변경사항을 적용시켜주는 명령어지만, 안 써도 적용이 된다. 허나, 혹시나 자동으로 적용이 안될 경우도 있기에 GRANT 테이블에 대해 내용 변경시 꼭 써주도록 하자

    • MySQL의 캐시를 지우고 새로운 설정을 적용하기 위해 사용하는 명령어로, 이 명령어를 사용하려는 사용자는 reload 권한을 가지고 있어야 한다

  

  • view의 select만 허용했으므로, view를 select 하는 것은 잘 실행된다

• DISTINCT를 통해 중복 DATA를 합쳐서, 다음과 같이 구매한 기록이 한 번이라도 있는 user를 출력할 수 있다

• 서버의 자원 사용량을 DB 에 기록해서, 새 작업이 들어왔을 때, DB를 참조하여 사용량이 가장 적은 서버에 자동으로 MIGRATION 해줄 수 있다

7. OUTER JOIN

p. 274

• 조인의 조건에 만족되지 않는 행까지 출력하는 조인 방법이다. 즉, 한 쪽에만 있는 Data도 출력해준다

• OUTER JOIN 형식

  SELECT 열 이름
  FROM 첫 번째 테이블
  <LEFT / RIGHT / FULL > OUTER JOIN 두 번째 테이블

  • LEFT로 설정하면 첫 번째 테이블의 모든 DATA를 출력. 즉, 한 쪽을 기준으로 조건과 일치하지 않는 것을 모두 출력
  • RIGHT로 설정하면 두 번째 테이블의 모든 DATA를 출력. 즉, 한 쪽을 기준으로 조건과 일치하지 않는 것을 모두 출력
  • FULL로 설정하면 두 테이블의 모든 DATA를 출력. 즉, 양 쪽 모두에 조건이 일치하지 않는 것을 모두 출력
    

  ON 조인 조건 ;

  • 이렇게 작성한다

• 구매 기록이 없는 USER의 정보도 출력해야 되기에 LEFT OUTER JOIN을 해보자
  

• 위와 같이 구매 기록이 없는 USER는 prodname이 NULL로 출력된다
  

• RIGHT OUTER JOIN으로 동일한 결과를 출력하려면, 테이블의 순서를 바꾸면 된다
  

• 구매 기록이 없는 USER만 출력하려면 IS NULL을 사용하여 WHERE 문을 추가해주면 된다

8. 그 외의 JOIN 들

• CROSS JOIN

p. 278

• 한 쪽 테이블의 모든 행들과 다른 쪽 테이블의 모든 행을 조인하는 것으로 카티션 곱이다
• ON 구분 사용이 불가하며, 주로, 테스트로 사용할 많은 용량의 데이터를 생성할 때 사용한다
  SELECT 열 이름
  FROM 우측 테이블
  CROSS JOIN 좌측 테이블
• 이 형식으로 사용하거나
  SELECT 열 이름
  FROM 우측 테이블 , 좌측 테이블
• 이런 형식으로 사용한다

• SELF JOIN

p. 279

• 테이블 자기 자신과 자기 자신이 JOIN 하는 것. 주로, 조직도와 관련된 테이블에 사용한다
  
• 조직도를 위한 테이블을 생성해준다
  
• 필요한 DATA를 입력하고, SELF JOIN을 해준다
• 왼쪽, 오른쪽 테이블을 한 테이블로 작성하되, 이름을 변경하여 사용하여 한 테이블을 자체 JOIN 할 수 있다. MANAGER와 EMP가 동일한 것을 INNER JOIN하여 부하 직원과 직속 상관에 대한 정보를 출력한다. 이처럼, 조직도는 SELF JOIN을 주로 사용한다