[DB] 22-07-18 TIL

gununoo·2022년 7월 18일
1

DB

목록 보기
4/8
post-thumbnail
post-custom-banner

DML: 데이터 조작 -> SELECT/INSERT/UPDATE/DELETE
DDL: 데이터베이스 개체를 생성/삭제/수정 -> CREATE/DROP/ALTER
DCL: 사용자에게 권한 부여 -> GRANT/REVOKE/DENY 

GRANT INSERT, SELECT ON testdb.* TO 'user1'@'%' IDENTIFIED BY '1234';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY '1234';

실습

DROP DATABASE IF EXISTS labDB; CREATE DATABASE labDB; 
use labDB;
CREATE TABLE usertbl  
    (userid CHAR(8) PRIMARY KEY, 
    name VARCHAR(10) NOT NULL,  
    birthyear INT NOT NULL, 
    addr CHAR(3) NOT NULL,  
    mobile1 CHAR(3),  
    mobile2 CHAR(8),  
    height SMALLINT,  
    mdate DATE);
desc usertbl;

CREATE TABLE buytbl
    (num INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    userid CHAR(8) NOT NULL,
    prodname CHAR(8) NOT NULL,
    groupname CHAR(4), 
    price INT NOT NULL, 
    amount SMALLINT NOT NULL, 
    FOREIGN KEY (userid) REFERENCES usertbl(userid));
desc buytbl;

FK 변경하기

FK의 키 이름 확인하기

show databases;

use information_scheme;
show tables;

information_schema.table_constraints를 사용할 것임 

use labDB;
select * 
from information_schema.table_constraints 
where table_name='buytbl';

FK 이름: buytbl_ibfk_1

[테이블에 값 추가]

INSERT INTO usertbl VALUES ('user1', '홍길동', 1996, '서울', '010', '11111111', 180, CURDATE());
INSERT INTO usertbl VALUES ('user2', '김철수', 1996, '부산', '010', '22222222', 180, CURDATE());
INSERT INTO usertbl VALUES ('user3', '이영희', 1996, '제주', NULL, NULL, NULL, CURDATE());

select * from usertbl;

INSERT INTO buytbl VALUES (NULL, 'user1', '청바지', '의류', 30, 1); 
INSERT INTO buytbl VALUES (NULL, 'user2', '선풍기', '전자', 120, 1); 
INSERT INTO buytbl VALUES (NULL, 'user1', '노트북', '전자', 900, 2);

select * from buytbl;

FK 변경하기

p.321
기존 FK의 이름: buytbl_ibfk_1

새로운 FK의 이름: FK_testdb

alter table buytbl 
    drop foreign key buytbl_ibfk_1;
alter table buytbl
    add constraint FK_testdb
        foreign key (userid) 
        references usertbl (userid) 
    on delete cascade 
    on update cascade;

Update Set

update usertbl 
set addr='대구'
where userid='user1';

-> user1의 주소가 대구로 변경되었음

p.211 

update buytbl 
set price = price * 1.5
where groupname = '전자';

-> 전자 제품의 가격이 50% 인상되었음

update usertbl 
set userid='user02' 
where userid='user2';


-> user2의 이름이 user02로 바뀌었음
-> update cascade로 인해 usertbl에서 변경한 내용이 buytbl에서도 바뀌었음

Delete From

delete from buytbl
where num=2;
-> 구매 정보 2번이 삭제되었음

Join On

p.264

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

inner join

select [열 이름]
from [첫 번째 테이블]
    inner join [두 번째 테이블]
    on [조인될 조건]
where [조회 조건]
select * 
from buyTbl    
    inner join userTbl 
    on buyTbl.userID = userTbl.userID 
where buyTbl.userID = 'KBS'; 

select userID, name, addr, CONCAT(mobile1, mobile2), prodname, amount 
from buyTbl
    inner join userTbl 
    on buyTbl.userID = userTbl.userID;

-> 오류 발생
-> userID가 buyTbl과 userTbl에 모두 있기 때문에 어느 테이블에서 참조할지 지정해주어야 함 

select buyTbl.userID, name, addr, CONCAT(mobile1, mobile2), prodname, amount 
from buyTbl
    inner join userTbl 
    on buyTbl.userID = userTbl.userID;

Quiz. 두 테이블을 inner join하여 구매자의 이름, 주소, 휴대폰 번호, 제품명, 수량 만을 포함하는 view(v_tracking)를 생성하라.
또한 이 테이블에 접속할 수 있는 알바생의 계정인 alba1을 생성하고 alba1은 해당 뷰에서 select만 가능하다.
완성 후 alba1으로 로그인하여 해당 테이블을 조회해본다.

  1. 두 테이블을 inner join하여 구매자의 이름, 주소, 휴대폰 번호, 제품명, 수량 만을 포함하는 view(v_tracking)를 생성하라. 
create view v_tracking 
as 
    select name, addr, concat(mobile1, mobile2), prodName, amount 
    from buyTbl
        inner join userTbl
        on buyTbl.userID = userTbl.userID;
  1. 또한 이 테이블에 접속할 수 있는 알바생의 계정인 alba1을 생성하고 alba1은 해당 뷰에서 select만 가능하다.
grant select on sqlDB.v_tracking to 'alba1'@'localhost' identified by 'alba1';  
grant select on sqlDB.v_tracking to 'alba1'@'%' identified by 'alba1'; 
flush privileges;
  1. 완성 후 alba1으로 로그인하여 해당 테이블을 조회해본다. 
mysql -u alba1 -palba1

select * from v_tracking;

update v_tracking
set addr='대구'
where name='김범수';

-> ERROR 1142 (42000): UPDATE command denied to user 'alba1'@'localhost' for table 'v_tracking'
-> alba1에게는 select 권한만 있어서 update 불가

Distinct

select distinct buyTbl.userID, addr, concat(mobile1, mobile2) 
from buyTbl  
    inner join userTbl 
    on buyTbl.userID = userTbl.userID;
-> 제품을 구매한 경험이 한번이라도 있는 사람들의 목록

outer join

select [보고 싶은 열] 
from [첫 번째 테이블]
    <Left | Right | FULL> outer join [두 번째 테이블]
    on [조인될 조건]

LEFT: 왼쪽 테이블에 있는 것을 모두 출력해라
FULL: 양쪽 모두에 일치하지 않는 것을 모두 출력해라 

SELECT userTbl.userID, name, prodname, addr 
FROM userTbl 
	LEFT OUTER JOIN buyTbl 
	ON userTbl.userID = buyTbl.userID
ORDER BY userTbl.userID DESC; 
SELECT userTbl.userID, name, prodname, addr 
FROM userTbl 
	LEFT OUTER JOIN buyTbl 
	ON userTbl.userID = buyTbl.userID
    WHERE prodname IS NULL 
ORDER BY userTbl.userID DESC;

-> where prodname is null
-> 한 번도 구매한 적이 없는 사람 출력

self join

CREATE TABLE empTbl (emp CHAR(3), manager CHAR(3), empTel VARCHAR(8)); 

INSERT INTO empTbl VALUES (N'이부장', N'김재무', '2222-2');
INSERT INTO empTbl VALUES (N'우대리', N'이부장', '2222-2-1'); 

SELECT A.emp AS '부하직원', B.emp AS '직속상관', B.empTel AS '직속상관연락처'  
FROM empTbl A
	INNER JOIN empTbl B
	ON A.manager = B.emp 
WHERE A.emp = '우대리';

Procedure

DELIMITER //  
CREATE PROCEDURE 이름() 
BEGIN 
	[SQL문]
END //
DELIMITER ; 

CALL 이름(); 
DROP PROCEDURE IF EXISTS ifproc; 

DELIMITER // 
CREATE PROCEDURE ifproc()
BEGIN 
	DECLARE var1 INT; -- var1 변수 선언 
	SET var1 = 101; 
	
	IF var1 % 2 = 0 THEN
		SELECT '짝수입니다' AS '결과';
	ELSE 
		SELECT '홀수입니다' AS '결과'; 
	END IF;	
END // 

DELIMITER ; 

CALL ifproc();

날짜 및 시간 함수

select adddate('2022-07-18', interval 1 week); 
+----------------------------------------+
| adddate('2022-07-18', interval 1 week) |
+----------------------------------------+
| 2022-07-25                             |
+----------------------------------------+


select adddate('2022-07-18', interval 1 month); 
+-----------------------------------------+
| adddate('2022-07-18', interval 1 month) |
+-----------------------------------------+
| 2022-08-18                              |
+-----------------------------------------+


select adddate(curdate(), interval 1 month); 
+--------------------------------------+
| adddate(curdate(), interval 1 month) |
+--------------------------------------+
| 2022-08-18                           |
+--------------------------------------+


select curtime();
+-----------+
| curtime() |
+-----------+
| 13:59:22  |
+-----------+


select concat(curdate(), ' ', curtime());
+-----------------------------------+
| concat(curdate(), ' ', curtime()) |
+-----------------------------------+
| 2022-07-18 13:59:59               |
+-----------------------------------+


select sysdate(); 
+---------------------+
| sysdate()           |
+---------------------+
| 2022-07-18 14:00:57 |
+---------------------+


select year(curdate()); 
+-----------------+
| year(curdate()) |
+-----------------+
|            2022 |
+-----------------+


select datediff(curdate(), '2021-01-01'); 
+-----------------------------------+
| datediff(curdate(), '2021-01-01') |
+-----------------------------------+
|                               563 |
+-----------------------------------+


select datediff(curdate(), '2021-01-01')/365; 
+---------------------------------------+
| datediff(curdate(), '2021-01-01')/365 |
+---------------------------------------+
|                                1.5425 |
+---------------------------------------+

declare

declare hireDate date; -- 변수 선언 

select hire_date into hireDate 
from employees.employees 
where emp_no = 10001;

quiz. 실습했던 usertbl 에서 각 사용자들의 생년을 확인하여 올해가 짝수라면 생년이 짝수인 사람들의 정보 출력 시 마지막 열이 '건강 검진 대상자', 만약 생년이 홀수라면 '내년 건강 검진'이 출력되도록 하기. 

DROP PROCEDURE IF EXISTS ifproc;
DELIMITER //
CREATE PROCEDURE ifproc()
BEGIN
  DECLARE thisyear INT;
  SET thisyear = YEAR(CURDATE())%2;
  SELECT ROW_NUMBER() OVER(ORDER BY birthYear) "연번", NAME, addr, birthYear,
  	CASE 
  		WHEN (birthYear%2) = thisyear THEN "올해"
  		ELSE "내년"
  	END AS "건강검진"
FROM userTbl;
END //
DELIMITER ;

CALL ifproc();

over -> 합계나 평균 등 집계 함수의 결과를 group by 없이 출력하고 싶을 때 사용할 수 있다.

alter

ALTER TABLE userTbl 
ADD sns VARCHAR(30) AFTER addr; 

ALTER TABLE userTbl 
DROP COLUMN sns; 

ALTER TABLE userTbl 
CHANGE COLUMN name uname VARCHAR(20) NOT NULL;

quiz. 사용자 테이블에 추가로 point 열을 추가하고 3~4명에 대하여 점수를 입력해주세요. 

alter table userTbl 
add point int; 

insert into userTbl values('user2', '김성수', 1997, '서울', '010', '11111111', NULL, NULL, 60);
insert into userTbl values('user3', '이건우', 1998, '서울', '010', '22222222', NULL, NULL, 80);
insert into userTbl values('user4', '김희정', 1997, '서울', '010', '33333333', NULL, NULL, 30);

사용자의 ID와 mobile2를 변수에 담고 여기에 매치하는 학생의 점수를 출력하되 아래와 같이 출력되도록 해보세요.
만약 해당 사용자가 없다면, 사용자 없음 출력. 

drop procedure if exists point;
delimiter //
create procedure point()
begin
DECLARE id CHAR(8);
   DECLARE phone CHAR(8);
DECLARE result INT;
DECLARE pass CHAR(10);

SET id='user3';
SET phone = '22222222';

SELECT point INTO result FROM userTbl WHERE userID=id AND mobile2=phone;

if result >= 60 THEN
SET pass = 'PASSED';
ELSEIF result >= 0 THEN
  SET pass = 'FAILED';
ELSE
SET pass = 'NO RESULT';
end if;

select concat('USER : ' , id), CONCAT('POINT : ', result), CONCAT('RESULT : ', pass);
end //
DELIMITER ;
CALL point();

quiz. 1부터 100 사이의 숫자 중 5배수의 총 합은? 

drop procedure if exists whileProc; 
delimiter // 
CREATE PROCEDURE whileProc() 
begin
   declare i int; 
   declare hap int; 
    
   set i = 1;
   set hap = 0; 

   while (i <= 100) DO
      if i % 5 = 0 then 
         set hap = hap + i;  
      end if; 
      SET i = i + 1;
   end while;
   
   select hap; 

end // 
delimiter ; 
call whileProc();

참고: 이것이 MariaDB다, 우재남, 한빛미디어

profile
gununoo
take a look
이전 포스트

[DB] 22-07-15 TIL

다음 포스트

240125 오라클 교육 day4

post-custom-banner

0개의 댓글