(MariaDB)데이터베이스 명령어 실습 - DMS, DDL, DCL까지
[카카오 클라우드 스쿨] DB의 다양한 조회, 활용을 돕는 SQL문
MariaDB (5) - 전부 다 배우지는 못하겠지만 데이터베이스의 다양한 명령어, 쿼리문을 배워 보자
- DML: 데이터 조작 (SELECT, DELETE, UPDATE, INSERT)
- DDL: 데이터베이스 개체 (pk view등 뷰, 인덱스 와 같은 개체)를 생성, 삭제, 수정 (CREATE, DROP, ALTER)
- DCL: 사용자에게 권한 부여 (GRANT, REVOKE, DENY)
- ex) GRANT ALL PRIVILEGES ON . TO ‘root’@localhost’
[본문] SQL문 실습을 통해 배워 보자
1 . 테이블 생성하기
- userTbl 생성
-- 데이터베이스 및 테이블 생성
MariaDB [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 );
MariaDB [labDB]> desc userTbl;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| userid | char(8) | NO | PRI | NULL | |
| name | varchar(10) | NO | | NULL | |
| birthyear | int(11) | NO | | NULL | |
| addr | char(3) | NO | | NULL | |
| mobile1 | char(3) | YES | | NULL | |
| mobile2 | char(8) | YES | | NULL | |
| height | smallint(6) | YES | | NULL | |
| mdate | date | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
8 rows in set (0.024 sec)
- buyTbl 생성
- 구매항목 ID는 가용자가 입력하지 않고 자동으로 부여되게끔 함
- auto increment 를 지정 - PK 지정 필요
MariaDB [labDB]> create table buyTbl
-> (num INT AUTO_INCREMENT PRIMARY KEY,
-> userid CHAR(8) NOT NULL,
-> prodname CHAR(8) NOT NULL,
-> groupname CHAR(4),
-> price INT NOT NULL,
-> amount SMALLINT NOT NULL, /*SMALLINT: 메모리를 절약하기 위해 사용함*/
-> FOREIGN KEY (userid) REFERENCES userTbl(userid)
-> );
MariaDB [labDB]> desc buyTbl;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| num | int(11) | NO | PRI | NULL | auto_increment |
| userid | char(8) | NO | MUL | NULL | |
| prodname | char(8) | NO | | NULL | |
| groupname | char(4) | YES | | NULL | |
| price | int(11) | NO | | NULL | |
| amount | smallint(6) | NO | | NULL | |
+-----------+-------------+------+-----+---------+----------------+
6 rows in set (0.003 sec)
2 . 데이터 삽입하기
- userTbl 데이터 입력하기
MariaDB [labDB]> INSERT INTO userTbl VALUES ('user1', '홍길동', 1996, '서울', '010', '11111111', 180, CURDATE());
Query OK, 1 row affected (0.001 sec)
MariaDB [labDB]> INSERT INTO userTbl VALUES ('user2', '김철수', 1996, '부산', '010', '22222222', 180, CURDATE());
Query OK, 1 row affected (0.001 sec)
MariaDB [labDB]> INSERT INTO userTbl VALUES ('user3', '고영희', 1996, '제주', NULL, NULL, NULL, CURDATE());
Query OK, 1 row affected (0.001 sec)
MariaDB [labDB]> select * from userTbl;
+--------+-----------+-----------+--------+---------+----------+--------+------------+
| userid | name | birthyear | addr | mobile1 | mobile2 | height | mdate |
+--------+-----------+-----------+--------+---------+----------+--------+------------+
| user1 | 홍길동 | 1996 | 서울 | 010 | 11111111 | 180 | 2022-07-18 |
| user2 | 김철수 | 1996 | 부산 | 010 | 22222222 | 180 | 2022-07-18 |
| user3 | 고영희 | 1996 | 제주 | NULL | NULL | NULL | 2022-07-18 |
+--------+-----------+-----------+--------+---------+----------+--------+------------+
3 rows in set (0.001 sec)
- buyTbl 데이터 입력하기
MariaDB [labDB]> INSERT INTO buyTbl VALUES (NULL, 'user1', '청바지', '의류', 30, 1);
Query OK, 1 row affected (0.002 sec)
MariaDB [labDB]> INSERT INTO buyTbl VALUES (NULL, 'user2', '선풍기', '전자', 120, 1);
Query OK, 1 row affected (0.002 sec)
MariaDB [labDB]> INSERT INTO buyTbl VALUES (NULL, 'user3', '노트북', '전자', 900, 2);
Query OK, 1 row affected (0.001 sec)
MariaDB [labDB]> SELECT * FROM buyTbl;
+-----+--------+-----------+-----------+-------+--------+
| num | userid | prodname | groupname | price | amount |
+-----+--------+-----------+-----------+-------+--------+
| 1 | user1 | 청바지 | 의류 | 30 | 1 |
| 2 | user2 | 선풍기 | 전자 | 120 | 1 |
| 3 | user3 | 노트북 | 전자 | 900 | 2 |
+-----+--------+-----------+-----------+-------+--------+
3 rows in set (0.000 sec)
3 . FK CASCADE 설정하기
- 자동으로 생성된 FK의 키 이름 확인하기
- information_schema 테이블 하단의, table_constraints에 저장되어 있음
- 따라서 다음으로 확인 가능
- FK의 키 이름은 buyTbl_ibfk_1 이다
MariaDB [labDB]> select * from information_schema.table_constraints where table_name='buyTbl';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| def | sqlDB | PRIMARY | sqlDB | buyTbl | PRIMARY KEY |
| def | sqlDB | buyTbl_ibfk_1 | sqlDB | buyTbl | FOREIGN KEY |
| def | labDB | PRIMARY | labDB | buyTbl | PRIMARY KEY |
| def | labDB | buyTbl_ibfk_1 | labDB | buyTbl | FOREIGN KEY |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
4 rows in set (0.004 sec)
- 이제 FK에 대한 CASCADE 설정하기
- CASCADE: FK의 원본이 변경 시, FK도 변경될 수 있도록 설정하기
- ON UPDATE CASCADE;
- ON DELETE CASCADE;
새로운 FK의 이름은 FK_testdb로 지정하자
-- FK 제거
MariaDB [labDB]> ALTER TABLE buyTbl
-> DROP FOREIGN KEY buyTbl_ibfk_1;
Query OK, 0 rows affected (0.019 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- CASCADE가 적용된 FK 새로 설정
MariaDB [labDB]> ALTER TABLE buyTbl
-> ADD CONSTRAINT FK_testdb
-> FOREIGN KEY (userid)
-> REFERENCES userTbl (userid)
-> ON UPDATE CASCADE
-> ON DELETE CASCADE;
Query OK, 3 rows affected (0.016 sec)
Records: 3 Duplicates: 0 Warnings: 0
4 . AUTO_INCREMENT
- 행이 추가될수록 값을 자동으로 증가해 준다
- ALTER TABLE testTbl AUTO_INCREMENT=100;
5 . UPDATE
MariaDB [labDB]> UPDATE userTbl SET addr = '대구' WHERE userid='user1';
Query OK, 1 row affected (0.002 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [labDB]> UPDATE buyTbl SET price=price*2 WHERE groupname='전자';
Query OK, 2 rows affected (0.001 sec)
Rows matched: 2 Changed: 2 Warnings: 0
MariaDB [labDB]> UPDATE userTbl SET userid='user02' WHERE userid='user2';
Query OK, 1 row affected (0.002 sec)
Rows matched: 1 Changed: 1 Warnings: 0
- userid의 경우 CASCADE가 적용되어 있기 때문에 buyTbl에도 적용이 된다
MariaDB [labDB]> select * from userTbl;
+--------+-----------+-----------+--------+---------+----------+--------+------------+
| userid | name | birthyear | addr | mobile1 | mobile2 | height | mdate |
+--------+-----------+-----------+--------+---------+----------+--------+------------+
| user02 | 김철수 | 1996 | 부산 | 010 | 22222222 | 180 | 2022-07-18 |
| user1 | 홍길동 | 1996 | 대구 | 010 | 11111111 | 180 | 2022-07-18 |
| user3 | 고영희 | 1996 | 제주 | NULL | NULL | NULL | 2022-07-18 |
+--------+-----------+-----------+--------+---------+----------+--------+------------+
3 rows in set (0.000 sec)
MariaDB [labDB]> select * from buyTbl;
+-----+--------+-----------+-----------+-------+--------+
| num | userid | prodname | groupname | price | amount |
+-----+--------+-----------+-----------+-------+--------+
| 1 | user1 | 청바지 | 의류 | 30 | 1 |
| 2 | user02 | 선풍기 | 전자 | 240 | 1 |
| 3 | user3 | 노트북 | 전자 | 1800 | 2 |
+-----+--------+-----------+-----------+-------+--------+
3 rows in set (0.000 sec)
6 . DELETE
MariaDB [labDB]> DELETE FROM buyTbl WHERE num=2;
Query OK, 1 row affected (0.002 sec)
MariaDB [labDB]> select * from buyTbl;
+-----+--------+-----------+-----------+-------+--------+
| num | userid | prodname | groupname | price | amount |
+-----+--------+-----------+-----------+-------+--------+
| 1 | user1 | 청바지 | 의류 | 30 | 1 |
| 3 | user3 | 노트북 | 전자 | 1800 | 2 |
+-----+--------+-----------+-----------+-------+--------+
2 rows in set (0.000 sec)
7 . JOIN
- (복습) JOIN의 종류
- INNER JOIN: 두 테이블에 모두 있는 정보만 결합하여 출력
- OUTER JOIN: 두 테이블에 있는 데이터 + 한쪽에만 있는 데이터
- SELF JOIN: 자체 조인은 테이블을 자체에 연결한다
7-1 . INNER JOIN
- SELECT [보고 싶은 열 이름] FROM [첫번째 테이블(왼쪽에 옴)] INNER JOIN [두 번째 테이블] ON [조인될 조건] WHERE[조건절]
MariaDB [sqlDB]> SELECT * FROM buyTbl
-> INNER JOIN userTbl
-> ON buyTbl.userID=userTbl.userID
-> WHERE buyTbl.userID = 'KBS';
+-----+--------+-----------+-----------+-------+--------+--------+-----------+-----------+--------+---------+----------+--------+------------+
| num | userID | prodName | groupName | price | amount | userID | name | birthYear | addr | mobile1 | mobile2 | height | mDate |
+-----+--------+-----------+-----------+-------+--------+--------+-----------+-----------+--------+---------+----------+--------+------------+
| 1 | KBS | 운동화 | NULL | 30 | 2 | KBS | 김범수 | 1979 | 경남 | 011 | 22222222 | 173 | 2012-04-04 |
| 2 | KBS | 노트북 | 전자 | 1000 | 1 | KBS | 김범수 | 1979 | 경남 | 011 | 22222222 | 173 | 2012-04-04 |
| 5 | KBS | 청바지 | 의류 | 50 | 3 | KBS | 김범수 | 1979 | 경남 | 011 | 22222222 | 173 | 2012-04-04 |
+-----+--------+-----------+-----------+-------+--------+--------+-----------+-----------+--------+---------+----------+--------+------------+
3 rows in set (0.001 sec)
- join에 CONCAT 사용 가능
MariaDB [sqlDB]> SELECT U.userID, name, addr, CONCAT(mobile1, mobile2), prodname, amount
-> FROM buyTbl B
-> INNER JOIN userTbl U
-> on B.userID=U.userID;
+--------+-----------+--------+--------------------------+-----------+--------+
| userID | name | addr | CONCAT(mobile1, mobile2) | prodname | amount |
+--------+-----------+--------+--------------------------+-----------+--------+
| KBS | 김범수 | 경남 | 01122222222 | 운동화 | 2 |
| KBS | 김범수 | 경남 | 01122222222 | 노트북 | 1 |
| JYP | 조용필 | 경기 | 01144444444 | 모니터 | 1 |
| BBK | 바비킴 | 서울 | 01000000000 | 모니터 | 5 |
| KBS | 김범수 | 경남 | 01122222222 | 청바지 | 3 |
| BBK | 바비킴 | 서울 | 01000000000 | 메모리 | 10 |
| SSK | 성시경 | 서울 | NULL | 책 | 5 |
| EJW | 은지원 | 경북 | 01188888888 | 책 | 2 |
| EJW | 은지원 | 경북 | 01188888888 | 청바지 | 1 |
| BBK | 바비킴 | 서울 | 01000000000 | 운동화 | 2 |
| EJW | 은지원 | 경북 | 01188888888 | 책 | 1 |
| BBK | 바비킴 | 서울 | 01000000000 | 운동화 | 2 |
+--------+-----------+--------+--------------------------+-----------+--------+
12 rows in set (0.001 sec)
- 결과에 중복 제거 DISTINCT
MariaDB [sqlDB]> SELECT DISTINCT B.userID, addr, CONCAT(mobile1, mobile2)
-> FROM buyTbl B
-> INNER JOIN userTbl U
-> on B.userID=U.userID;
+--------+--------+--------------------------+
| userID | addr | CONCAT(mobile1, mobile2) |
+--------+--------+--------------------------+
| BBK | 서울 | 01000000000 |
| EJW | 경북 | 01188888888 |
| JYP | 경기 | 01144444444 |
| KBS | 경남 | 01122222222 |
| SSK | 서울 | NULL |
+--------+--------+--------------------------+
5 rows in set (0.001 sec)
- 퀴즈 (VIEW)
- 두 테이블을을 inner join하여 구매자의 이름, 주소, 휴대폰번호, 제품명, 수량 만을 포함하는 View(v_tracking)를 생성하라
- 또한, 이 테이블에 접속할 수 있는 알바생의 계정인 alba1을 생성하고, alba1은 해당 뷰에서 select만 가능하다
- 완성 후 alba1으로 로그인하여 해당 테이블을 조회해 본다
MariaDB [sqlDB]> CREATE VIEW v_tracking
-> AS
-> SELECT name, addr, CONCAT(mobile1, mobile2), prodname, amount
-> FROM buyTbl B INNER JOIN userTbl U
-> on B.userID=U.userID;
Query OK, 0 rows affected (0.003 sec)
MariaDB [sqlDB]> CREATE USER alba1 IDENTIFIED BY '1234';
Query OK, 0 rows affected (0.001 sec)
-- 권한 부여
MariaDB [sqlDB]> GRANT SELECT ON sqlDB.v_tracking TO 'alba1'@'localhost' IDENTIFIED BY 'alba1';
MariaDB [sqlDB]> GRANT SELECT ON sqlDB.v_tracking TO 'alba1'@'%' IDENTIFIED BY 'alba1';
MariaDB [sqlDB]> FLUSH PRIVILEGES;
- 권한 부여?
- – grant create session, create table, create sequence, create view to [계정 이름];
- – GRANT 행동 ON 테이블명 TO 사용자명;
7-2 . OUTER JOIN
- SELECT [보고 싶은 열] FROM [첫 번째 테이블] [LEFT/RIGHT/FULL] OUTER JOIN [두 번째 테이블]
- LEFT: 오른쪽에 데이터가 없어도 왼쪽 테이블의 모든 것을 출력함
- RIGHT: 왼쪽에 데이터가 없어도 오른 테이블의 모든 것을 출력함
- FULL: 데이터가 둘다 없어도 (양쪽 모두에 일치하지 않아도) 전부 출력함
MariaDB [sqlDB]> SELECT U.userID, NAME, prodname, addr
-> FROM userTbl U
-> LEFT OUTER JOIN buyTbl B
-> ON U.userID = B.userID
-> ORDER BY U.userID;
+--------+-----------+-----------+--------+
| userID | NAME | prodname | addr |
+--------+-----------+-----------+--------+
| BBK | 바비킴 | 모니터 | 서울 |
| BBK | 바비킴 | 메모리 | 서울 |
| BBK | 바비킴 | 운동화 | 서울 |
| BBK | 바비킴 | 운동화 | 서울 |
| EJW | 은지원 | 책 | 경북 |
| EJW | 은지원 | 청바지 | 경북 |
| EJW | 은지원 | 책 | 경북 |
| JKW | 조관우 | NULL | 경기 |
| JYP | 조용필 | 모니터 | 경기 |
| KBS | 김범수 | 운동화 | 경남 |
| KBS | 김범수 | 노트북 | 경남 |
| KBS | 김범수 | 청바지 | 경남 |
| KKH | 김경호 | NULL | 전남 |
| LJB | 임재범 | NULL | 서울 |
| LSG | 이승기 | NULL | 서울 |
| SSK | 성시경 | 책 | 서울 |
| user1 | gildong | NULL | NY |
| YJS | 윤종신 | NULL | 경남 |
+--------+-----------+-----------+--------+
18 rows in set (0.001 sec)
MariaDB [sqlDB]> SELECT U.userID, NAME, prodname, addr
-> FROM userTbl U
-> LEFT OUTER JOIN buyTbl B
-> ON U.userID = B.userID
-> WHERE prodname IS NULL
-> ORDER BY U.userID;
+--------+-----------+----------+--------+
| userID | NAME | prodname | addr |
+--------+-----------+----------+--------+
| JKW | 조관우 | NULL | 경기 |
| KKH | 김경호 | NULL | 전남 |
| LJB | 임재범 | NULL | 서울 |
| LSG | 이승기 | NULL | 서울 |
| user1 | gildong | NULL | NY |
| YJS | 윤종신 | NULL | 경남 |
+--------+-----------+----------+--------+
6 rows in set (0.001 sec)
7-3 . CROSS JOIN
- 한쪽 테이블의 모든 행들과 다른 쪽 테이블의 모든 행을 조인시키는 기능
- 10행 * 12행 = 총 120행이 됨
7-4 . SELF JOIN
- 자기 자신과 자신을 조인한다
- 다음과 같은 조직도 등에서 사용할 수 있다
+-----------+-----------+----------+
| emp | manager | empTel |
+-----------+-----------+----------+
| 나사장 | NULL | 0000 |
| 김재무 | 나사장 | 2222 |
| 이부장 | 김재무 | 2222-2 |
| 우대리 | 이부장 | 2222-2-1 |
| 지사원 | 이부장 | 2222-2-2 |
+-----------+-----------+----------+
8 . UNION
- 두 쿼리의 결과를 행으로 합친다
- SELECT [문장] [UNION, UNION ALL, NOT IN, IN] SELECT [문장2]
9 . STORED PROCEDURE
- 반복문은 물론 IF문도 사용 가능하다
- 분기, 흐름 제어 등 프로그래밍 언어에서 사용하는 논리를 사용 가능함
- 기본적인 형태는 다음과 같다
DELIMETER //
CREATE PROCEDURE [스토어드 프로시져 이름]
BEGIN
[SQL 프로시져 코딩]
(변수 선언, SQL문 등)
END //
DELIMETER;
CALL [스토어드 프로시져 이름]
- 실습 ```sql DROP PROCEDURE IF EXISTS ifproc;
DELIMITER // CREATE PROCEDURE ifproc() BEGIN DECLARE var1 INT; SET var1 = 100;
IF var1%2 = 0 THEN
SELECT '값은 짝수이다';
ELSE
SELECT '값은 홀수이다';
END IF;
END//
DELIMITER ; CALL ifproc(); +———————+ | 값은 짝수이다 | +———————+ | 값은 짝수이다 | +———————+ 1 row in set (0.001 sec)
Query OK, 0 rows affected (0.001 sec)
<br>
<hr>
**10 . 날짜 관련 함수**
* ADDDATE 사용
```sql
MariaDB [(none)]> SELECT ADDDATE('2022-01-01', INTERVAL 31 DAY), ADDDATE('2022-01-01', INTERVAL 1 MONTH);
+----------------------------------------+-----------------------------------------+
| ADDDATE('2022-01-01', INTERVAL 31 DAY) | ADDDATE('2022-01-01', INTERVAL 1 MONTH) |
+----------------------------------------+-----------------------------------------+
| 2022-02-01 | 2022-02-01 |
+----------------------------------------+-----------------------------------------+
1 row in set (0.001 sec)
- 날짜 차이 계산 가능
MariaDB [(none)]> SELECT DATEDIFF('2002-12-31', '2021-01-01');
+--------------------------------------+
| DATEDIFF('2002-12-31', '2021-01-01') |
+--------------------------------------+
| -6576 |
+--------------------------------------+
1 row in set (0.001 sec)
select datediff(curdate(), '2021-01-01');
+-----------------------------------+
| datediff(curdate(), '2021-01-01') |
+-----------------------------------+
| 563 |
+-----------------------------------+
- 년으로 계산 가능
- 일수를 365로 나누어 5가 넘는다 = 입사한지 5년이 넘었다
select datediff(curdate(), '2021-01-01')/365;
+---------------------------------------+
| datediff(curdate(), '2021-01-01')/365 |
+---------------------------------------+
| 1.5425 |
+---------------------------------------+
- QUIZ
- 실습했던 userTbl에서 각 사용자들의 생년을 확인하여 올해에 짝수라면 생년이 짝수인 사람들의 정보출력시 마지막 열에 ‘올해’
- 만약 생년이 홀수라면 ‘내년’이 출력되도록 해 보시오
- 이름 주소 생년 건강검진대상
- 홍길동 서울 1998 올해
- 김미나 울산 1992 내년
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();
+--------+-----------+--------+-----------+--------------+
| 연번 | NAME | addr | birthYear | 건강검진 |
+--------+-----------+--------+-----------+--------------+
| 1 | 조용필 | 경기 | 1950 | 올해 |
| 2 | 임재범 | 서울 | 1963 | 내년 |
| 3 | 조관우 | 경기 | 1965 | 내년 |
| 4 | 윤종신 | 경남 | 1969 | 내년 |
| 5 | 김경호 | 전남 | 1971 | 내년 |
| 6 | 은지원 | 경북 | 1972 | 올해 |
| 7 | 바비킴 | 서울 | 1973 | 내년 |
| 8 | 김범수 | 경남 | 1979 | 내년 |
| 9 | 성시경 | 서울 | 1979 | 내년 |
| 10 | 이승기 | 서울 | 1987 | 내년 |
| 11 | gildong | NY | 1996 | 올해 |
+--------+-----------+--------+-----------+--------------+
11 . OVER
- 합계나 평균등의 집계함수의 결과를 group by 없이 출력하고 싶을때 사용할 수 있다.
- 바로 위의 퀴즈에서 사용하였으니 참고바람
12 . 테이블 삭제
- DROP TABLE [테이블 명];
13 . 테이블 수정
- ALTER TABLE [테이블명] ..
- 엄청 많지만 그중 몇 가지만 추리면
- addr 열 바로 다음에 열 추가
MariaDB [sqlDB]> ALTER TABLE userTbl
-> ADD sns VARCHAR(30) AFTER addr;
- 열 통째로 제거
MariaDB [sqlDB]> ALTER TABLE userTbl DROP COLUMN sns;
- 열 이름, 데이터 타입 변경
MariaDB [sqlDB]> ALTER TABLE userTbl
-> CHANGE COLUMN name uname VARCHAR(20) NOT NULL;
- QUIZ
- 사용자 테이블에 추가로 point 열을 추가하고, 3~4명에 대하여 점수를 입력해 주세요
- id uname … point
- user1 김철수 … 80
- user2 박명수 … 90
- 또한 사용자의 ID 와 mobile2를 변수에 담고, 여기에 매치하는 학생의 점수를 출력하되, 아래와 같이 출력되도록 해 보세요
- 만약 해당 사용자가 없다면, ‘사용자 없음’을 출력하시오
- 이름 점수 합격여부
- 박명수 90 합격
- 사용자 테이블에 추가로 point 열을 추가하고, 3~4명에 대하여 점수를 입력해 주세요
MariaDB [sqlDB]> ALTER TABLE userTbl ADD score INT;
MariaDB [sqlDB]> UPDATE userTbl SET score = 80 WHERE userID = 'BBK';
MariaDB [sqlDB]> UPDATE userTbl SET score = 90 WHERE userID = 'EJW';
MariaDB [sqlDB]> UPDATE userTbl SET score = 45 WHERE userID = 'JKW';
-- 내가 시도하려던 것 (잘 안됨)
DROP PROCEDURE IF EXISTS ifproc;
DELIMITER //
CREATE PROCEDURE ifproc()
BEGIN
DECLARE userid VARCHAR(5);
DECLARE mobile VARCHAR(10);
SET userid = 'BBK';
SET mobile = '00000000';
SELECT userID, mobile2,
IF score>70 THEN "pass"
ELSE "nope"
END IF AS "result"
FROM userTbl
WHERE userID = userid;
END //
DELIMITER ;
CALL ifproc();
--샘플 답안
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='JKW';
SET phone = '99999999';
SELECT point INTO result FROM userTbl WHERE userID=id AND mobile2=phone;
if result >= 60 THEN
SET pass = 'PASSWD';
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();
14 . WHILE문
- WHILE [부울 식] DO [SQL 명령문들] END WHILE;
- 인스턴스를 만들어라
(인스턴스를 만드는 작업과 동시에 SQL문 수행)
WHILE TRUE
do
인스턴스가 만들어져 있는가?
아니다
.출력
만들어져 있다
break;
sleep 3;
done
=> ....OK!
- Quiz. 1~100 사이의 숫자 중 5배수의 총 합은?
DROP PROCEDURE IF EXISTS ifproc;
DELIMITER //
CREATE PROCEDURE ifproc()
BEGIN
DECLARE numm INT;
DECLARE summ INT;
SET numm = 1;
SET summ = 0;
WHILE (numm < 101) DO
IF (numm % 5 = 0) THEN
SET summ = summ + numm;
END IF;
SET numm = numm + 1;
END WHILE;
SELECT summ;
END //
DELIMITER ;
CALL ifproc();
--결과는
1050
[DB 마무리] 엔지니어가 반드시 기억해야 할 DB 지식
- 데이터베이스 생성하기
- 테이블 생성하기
- INSERT 해서 데이터 입력하기
- SELECT 해서 조건절과 함께 원하는 데이터 출력하기
- PK, FK 지정하기
- INNER JOIN 수행하기
- VIEW 를 간단히 생성하기
TRIGGER 간단히 생성하기
- DATA REPLICATION
- GALERA 클러스터링
- 여러 대의 데이터베이스를 클러스터로 묶어 한 대의 DB 서버처럼 동작시키는 기술
- 여러 대의 데이터베이스를 클러스터로 묶어 한 대의 DB 서버처럼 동작시키는 기술
- 프로메테우스+GRAFANA
- 모니터링
- Dashboard (웹 콘솔) 을 통해 웹서버 인스턴스, DB 등의 정보를 관리해야 함
- 이러한 웹 콘솔은 DB로 만들어 둔다
- 인스턴스 삭제 시 DB에서도 삭제되게끔 하는 작업을 만들어야 할 것임!