마리아 디비 CLI 환경에서 사용 가능케하는 명령어
setx path "C:\Program Files\MariaDB 10.3\bin;%path%" /m
해당 path로 이동 후 shift + 마우스 오른쪽 =>powershell열기
mysql -h 192.168.0.85 -u director -p
h : 서버
u : 유저명
p : 비번 지정해서 디비접속
(hedisql에서 접속 계정의 호스트 권한이 있어야 가능)
테이블 검색
SELECT from membertbl;
SELECT memberName, memberAddress FROM memberTbl;
SELECT FROM membertbl WHERE memberName = '지운이';
테이블 생성 검색 삭제
create table my testTBL(id int);
select * from my testTBL;
drop table my testTBL;
인덱스 생성
CREATE TABLE indexTBL (first_name varchar(14), last_name varchar(16) , hire_date date);
INSERT INTO indexTBL
SELECT first_name, last_name, hire_date
FROM employees.employees
LIMIT 500;
인덱스 조회
SELECT FROM indexTBL;
SELECT FROM indexTBL WHERE first_name = 'Mary';
실행계획 물어보기
EXPLAIN SELECT * FROM indexTBL WHERE first_name = 'Mary';
인덱스 생성, 삭제
CREATE INDEX idx_indexTBL_firstname ON indexTBL(first_name);
DROP INDEX idx_indexTBL_firstname ON indexTBL;
shopdb테이블 사용
USE shopdb;
뷰 생성
CREATE VIEW uv_memberTBL
AS
SELECT memberName, memberAddress FROM memberTBL;
테이블 조회
SELECT FROM uv_memberTBL;
SELECT FROM memberTBL;
SELECT FROM memberTBL WHERE memberName = '당탕이';
SELECT FROM productTBL WHERE productName = '냉장고';
stored procedure 생성 후 호출 후 삭제
DELIMITER //
CREATE PROCEDURE myProc()
BEGIN
SELECT FROM memberTBL WHERE memberName = '당탕이';
SELECT FROM productTBL WHERE productName = '냉장고';
END //
DELIMITER;
CALL myProc();
DROP PROCEDURE myProc;
데이터 삽입
INSERT INTO membertestshopdbTBL VALUES ('Figure', '연아', '경기도 군포시 당정동');
데이터 수정
UPDATE memberTBL SET memberAddress = '서울 강남구 역삼동' WHERE memberName = '연아';
데이터 삭제
DELETE FROM memberTBL WHERE memberName = '연아';
테이블 조회
SELECT * FROM membertbl;
데이터 삽입
INSERT INTO memberTBL VALUES ('Dang', '당탕이', '경기 부천시 중동');
백업 데이터 생성 및 트리거 생성 조회 삭제 후 복원
트리거 사용전 마리아 디비 사용 원리
CREATE TABLE deletedMemberTBL ( -- 백업 테이블
memberID char(8) ,
memberName char(5) ,
memberAddress char(20),
deletedDate date -- 삭제한 날짜
);
DELIMITER // -- 구분자
CREATE TRIGGER trg_deletedMemberTBL -- 트리거 이름
AFTER DELETE -- 삭제 후에 작동하게 지정
ON memberTBL -- 트리거를 부착할 테이블
FOR EACH ROW -- 각 행마다 적용시킴
BEGIN
-- OLD 테이블의 내용을 백업테이블에 삽입
INSERT INTO deletedMemberTBL
VALUES (OLD.memberID, OLD.memberName, OLD.memberAddress, CURDATE() );
END //
DELIMITER;
SHOW TRIGGERS;
SELECT FROM memberTBL;
SELECT from deletedmembertbl;
DELETE FROM memberTBL WHERE memberName = '당탕이';
SELECT * FROM deletedMemberTBL;
INSERT INTO memberTBL -- 삭제된 데이터 복원
SELECT memberID, memberName, memberAddress
FROM deletedmembertbl;
테이블 shopdb 사용 후 테이블 조회
USE shopdb;
SELECT * FROM productTBL;
- 모든 권한
CREATE USER director@'%' IDENTIFIED BY 'director';
GRANT ALL ON . TO director@'%' WITH GRANT OPTION;
- director 권한 테스트
mysql -h 192.168.0.163 -u director -p
CREATE DATABASE sampleDB;
SHOW DATABASES;
DROP DATABASE sampleDB;
- 읽기 권한
CREATE USER ceo@'%' IDENTIFIED BY 'ceo';
GRANT SELECT ON . TO ceo@'%';
- ceo 권한 테스트
mysql -h 192.168.0.163 -u ceo -p
USE shopdb;
SELECT * FROM membertbl;
DELETE FROM memberTBL WHERE memberID = 'Dang';
- shopDB
읽기(SELECT),
쓰기(INSERT, UPDATE, DELETE) 권한,
employees 읽기 권한
[스토어 프로시저 생성(CREATE ROUTINE), 수정(ALTER ROUTINE)]
CREATE USER staff@'%' IDENTIFIED BY 'staff';
GRANT SELECT, ALTER ROUTINE, CREATE ROUTINE, INSERT, UPDATE, DELETE ON shopDB. TO staff@'%';
GRANT SELECT ON employees. TO staff@'%';
- staff 권한 테스트
mysql -h 192.168.0.163 -u staff -p
USE shopdb;
SELECT FROM memberTBL;
DELETE FROM memberTBL WHERE memberID = 'Sang';
SELECT FROM memberTBL;
DROP TABLE memberTBL;
USE employees;
SELECT * FROM employees;
- 사용자 삭제
USE mysql;
DROP USER staff;
- 사용자 목록
SELECT USER, HOST FROM USER;
- 사용자 정보 변경
ALTER USER 'director'@'%' IDENTIFIED BY 'kosa0220';
- 사용자 권한 보기
SHOW GRANTS FOR root@'localhost';
SHOW GRANTS FOR director@'%';
SHOW GRANTS FOR ceo@'%';
- 테이블 생성
CREATE TABLE buyTbl
(
num INT AUTO_INCREMENT NOT NULL PRIMARY KEY, -- 순번(PK)
userID CHAR(8) NOT NULL, -- 아이디(FK)
prodName CHAR(6) NOT NULL, -- 물품명
groupName CHAR(4), -- 분류
price INT NOT NULL, -- 단가
amount SMALLINT NOT NULL, -- 수량
FOREIGN KEY (userID) REFERENCES userTbl(userID) -- 외래 키 지정
);
외래 키 (Foreign Key) #
한 Table과 참조되는 다른 Table 간의 연결되는 Primary Key Column을 Foreign Key라 합니다.
Foreign Key는 다른 Primary Key를 참조하는 속성 또는 속성들의 집합을 의미한다.
Foreign Key는 참조관계의 기본 키와 같은 속성을 가진다.
- 테이블 확인
DESCRIBE buyTbl; -- 또는 DESC buyTbl;
- 데이터 입력
INSERT INTO buyTbl VALUES(NULL, 'KBS', '운동화', NULL , 30, 2);
INSERT INTO buyTbl VALUES(NULL, 'KBS', '노트북', '전자', 1000, 1);
INSERT INTO buyTbl VALUES(NULL, 'JYP', '모니터', '전자', 200, 1);
INSERT INTO buyTbl VALUES(NULL, 'BBK', '모니터', '전자', 200, 5);
INSERT INTO buyTbl VALUES(NULL, 'KBS', '청바지', '의류', 50, 3);
INSERT INTO buyTbl VALUES(NULL, 'BBK', '메모리', '전자', 80, 10);
INSERT INTO buyTbl VALUES(NULL, 'SSK', '책' , '서적', 15, 5);
INSERT INTO buyTbl VALUES(NULL, 'EJW', '책' , '서적', 15, 2);
INSERT INTO buyTbl VALUES(NULL, 'EJW', '청바지', '의류', 50, 1);
INSERT INTO buyTbl VALUES(NULL, 'BBK', '운동화', NULL , 30, 2);
INSERT INTO buyTbl VALUES(NULL, 'EJW', '책' , '서적', 15, 1);
INSERT INTO buyTbl VALUES(NULL, 'BBK', '운동화', NULL , 30, 2);
- 데이터 확인
SELECT * FROM buyTbl;
- sqlDB 내보내기
mysqldump -u root -p sqlDB > sqlDB.sql
- sqlDB 가져오기
mysql -h 192.168.0.163 -u director -p
DROP DATABASE sqldb;
CREATE DATABASE sqlDB;
USE sqlDB;
source sqlDB.sql
show tables;
