#02230307(데이터베이스)

eeapbh·2023년 3월 7일
데이터 입력데이터 활용데이터베이스데이터베이스 개체 활용데이터베이스 백업 및 관리사용자 관리테이블 생성
0

MZC

목록 보기
9/61

데이터베이스란?

데이터베이스를 간단히 정의하면 대용량의 데이터 집합을 체계적으로 구성해 놓은 것 정도로 말할 수 있다.
또한 데이터베이스는 혼자서 사용하는 목적이 아닌 여러 명의 사용자나 시스템이 서로 공유가 가능해야만 합니다.
그리고 데이터베이스 관리 시스템은 이러한 데이터베이스를 관리해 주는 시스템 또는 소프트웨어를 말한다.
데이터베이스는 데이터의 저장공간 자체를 의미하기도 한다.
특히 MariaDB에서는 데이터베이스를 자료ㄹ가 저장되는 디스크 공간으로 취급한다.

데이터베이스 또는 DBMS 특징

데이터의 무결성

  • 데이터베이스 내의 데이터에는 오류가 있어서는 안된다.
  • 삽입(Insert), 삭제(Delete), 갱신(Update)등의 연산 후에도 데이터베이스에 저장된 데이터가 정해진 제약 조건(Constrain)을 항상 만족해야 한다.

데이터의 독립성

  • 데이터베이스의 크기를 변경하거나 데이터 파일의 저장소를 변경하더라도, 기존에 작성된 응용프로그램은 전혀 영향을 받지 않아야 한다.
  • 데이터베이스를 저장한 디스크가 새것으로 변경되어도 기존에 사용하던 응용프로그램은 아무런 변경없이 계속 사용되어야 한다.

보안

  • 데이터베이스 안의 데이터는 아무나 접근할 수 있는 것이 아니라, 데이터를 소유한 사람이나 데이터의 접근이 허가된 사람만이 데이터에 접근할 수 있어야 한다.
  • 불법적인 데이터의 노출이나 변경, 손실로 부터 보호할 수 있어야 한다.

데이터 중복의 최소화

  • 동일한 데이터가 중복되어 저장되는 것을 방지한다.
  • 데이터베이스에 통합하면 하나의 테이블에 저장하고 이를 공유함으로써 데이터 중복이 최소하된다.

응용프로그램 제작 및 수정 용이

  • 데이터베이스를 이용함으로써 통일된 방식으로 응용프로그램 작성이 가능해지고, 유지보수 또한 쉬워진다.

데이터베이스 용어

  • 데이터 : 하나하나의 단편적인 정보
  • 테이블 : 데이터를 입력하기 위해 표 형태로 표현한 것
  • 데이터베이스 : 테이블이 저장되는 저장소
  • DBMS : 데이터베이스를 관리하는 시스템 또는 소프트웨어를 말한다.
  • 열(Colurmn) : 각 테이블은 열로 구성됨
  • 열 이름 : 각 열을 구분하기 위한 이름, 테이블 내에서 고유해야함
  • 데이터 형식 : 문자 형식, 숫자 형식, 테이블을 생성할 때 지정해야 함
  • 행(Row) : 실질적인 데이터
  • 기본 키 열 : 각 행을 구분하는 유일한 열을 말하며 식별자로 이용됨
  • 외래 키: 두 테이블의 관계를 맺어주는 키

SQL 개요

  • SQL은 구조적 쿼리 언어를 나타낸다.
  • 데이터베이스와 통신하는 데 사용된다.
  • ANSI(America National Standards Institute)에 따르면 관계형 데이터베이스 관리 시스템의 표준 언어이다.
  • SQL문은 데이터베이스에서 데이터 업데이트 또는 데이터베이스에서 데이터 검색과 같은 작업을 수행하는데 사용됨
  • SQL을 사용하는 일반적인 관계형 데이터베이스 관리 시스템은 Oracle, Sybase, Microsoft SQL Server, Access, Ingress 등이다
  • 대부분의 데이터베이스 시스템이 SQL을 사용하지만 대부분은 일반적으로 시스템에서만 사용되는 고유한 추가 독점 확장을 가지고 있다.
  • Select, Insert, Update, Delete, Create 및 Drop과 같은 표준 SQL 명령을 사용하여 데이터베이스로 수행하는데 필요한 거의 모든 작업을 수행할 수 있따.

MariaDB 설치

  • v 10.3.34

    Maria DB, dump data


-> 체크 하면 외부에서 접속하는 기능


-> 호환성을 높이기 위해 UTF8 유니코드 default set

  • 신규 클릭
  • 이름 바꾸고 비번치고 open

이거뜸

CMD

  • 관리자 권한으로 실행
  • C:\Program Files\MariaDB 10.3\bin
    -> mariadb를 위한 실행파일들 경로


-> 어떤 cli에서도 이용할수 있도록 설정

  • employees.zip 잇는 압축풀고 그 폴더가서 쉬프트 + 우클릭

  • powershell 로 키기

  • source employees.sql;

  • show databases;

MariaDB

localhos -> 새로생성 -> 데이터베이스 -> shopdb

memberID -> 기본키로

  1. 데이터 입력

행 삽입

회원 데이터 ( 아이디 / 이름 / 주소 )
Dang / 당탕이 / 경기 부천시 중동
Jee / 지운이 / 서울 은평구 중산동
Han / 한주연 / 인천 남구 주안동
Sang / 상길이 / 경기 성남시 분당구

제품데이터(제품명 / 비용 / 날짜 / 제조사 / 수량)
냉장고 / 5 / 2023-02-01 / 대우 / 22
세탁기 / 20 / 2022-09-01 / LG / 3
컴퓨터 / 10 / 2021-01-01 / 삼성 / 17

  1. 데이터 활용

회원의 모든 정보 조회

select * from memberTbl;

이름과 주소만 조회

select memberName, memberAddress from memberTbl;

지운이 정보만 조회

select * from memberTbl where memberName = '지운이';

테이블 생성 (CREATE 개체종류 개체이름 ~~)

create table `my testTBL`(id int);
select * from `my testTBL`;

테이블 삭제 (DROP 개체종류 개체이름 ~~)

drop table `my testTBL`;
  1. 데이터베이스 개체 활용

인덱스(Index): 대부분의 책의 제일 뒤에 붙어 있는 '찾아보기'와 같은 개념

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);

SELECT * FROM indexTBL WHERE first_name = 'Mary';

EXPLAIN SELECT * FROM indexTBL WHERE first_name = 'Mary';

DROP INDEX idx_indexTBL_firstname ON indexTBL;

뷰(View): 가상 테이블, 진짜 테이블에 링크된 개념, 개인 정보 유출 방지

민감한 정보들 빼고 다룰때

USE shopdb; 내가 이 db에서 작업을 하겟다.

CREATE VIEW uv_memberTBL 
AS
	SELECT memberName, memberAddress FROM memberTBL;

SELECT * FROM uv_memberTBL;

스토어드 프로시저(Stored Procedure): SQL문을 하나로 묶어서 편리하게 사용하는 프로그래밍 기능

SELECT * FROM memberTBL WHERE memberName = '당탕이';
SELECT * FROM productTBL WHERE productName = '냉장고';
DELIMITER //
CREATE PROCEDURE myProc()
BEGIN
	SELECT * FROM memberTBL WHERE memberName = '당탕이';
	SELECT * FROM productTBL WHERE productName = '냉장고';
END // 
DELIMITER;

CALL myProc();
-> 위에 지정한 select문 두개가 실행됨

DROP PROCEDURE myProc;

트리거(Trigger): 테이블에 부착되어서, 테이블에 INSERT(삽입)나 UPDATE(수정) 또는 DELETE(삭제) 작업이 발생되면 실행되는 코드

INSERT INTO memberTBL VALUES ('Figure', '연아', '경기도 군포시 당정동');

SELECT * FROM membertbl;

UPDATE memberTBL SET memberAddress = '서울 강남구 역삼동' WHERE memberName = '연아';

SELECT * FROM membertbl;

DELETE FROM memberTBL WHERE memberName = '연아';

SELECT * FROM membertbl;
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;
SELECT * FROM memberTBL;

DELETE FROM memberTBL WHERE memberName = '당탕이';

SELECT * FROM memberTBL;

SELECT * FROM deletedMemberTBL;
INSERT INTO memberTBL -- 삭제된 데이터 복원
	SELECT memberID, memberName, memberAddress
	FROM deletedMemberTBL;
SELECT * FROM memberTBL;
  1. 데이터베이스 백업 및 관리(DBA: DataBase Administrator)

백업과 복원: 데이터베이스를 다른 매체에 보관과 문제 발생 시 원상태로 돌려놓는 작업

USE shopdb;

SELECT * FROM productTBL;

-- shopdb 선택 > 데이터베이스를 SQL로 내보내기(테이블 구조 및 데이터를 모두 내보내기 위한 설정)

-> 저장후 내보내기 

DELETE FROM productTBL;
-> 안에 데이터들 삭제

SELECT * FROM productTBL;

USE mysql; -- 일단 다른 DB를 선택함

-- 파일 > SQL 파일 불러오기(shopdb.sql) > 쿼리 실행

USE shopdb;

SELECT * FROM productTBL;
  1. 사용자 관리

  • 모든 권한

    	CREATE USER director@'%' IDENTIFIED BY 'director';

    -> 비번 director
    GRANT ALL ON . TO director@'%' WITH GRANT OPTION;


-> vmware로 nat키고 mysql 설치함

NAT

  • director 권한 테스트
    mysql -h 192.168.0.14 -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.14 -u ceo -p
    USE shopdb;
    SELECT * FROM membertbl;
    DELETE FROM memberTBL WHERE memberID = 'Dang';

    -> ceo는 권한없어서 에러남

  • 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@'%';


-> 객체 추가 shopdb

GRANT SELECT ON employees.* TO staff@'%';
-> 객체 추가 employees -> select 체크

  • staff 권한 테스트
    mysql -h 192.168.0.14 -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@'%';

--- SQL 명령어 실습(CLI)
1. 데이터베이스 이름, 데이블 이름, 필드 이름이 정확히 기억나지 않거나, 또는 각 이름의 철자가 확실하지 않을 때 찾아서 조회하는 방법을 실습하자. 지금 조회하고자 하는 내용이 employees 데이터베이스에 있는 employees 테이블의 first_name 및 gender 열이라고 가정한다.

  • 데이터베이스 조회
    SHOW DATABASES;

  • 데이터베이스 지정
    USE employees;

  • 테이블 정보 조회
    SHOW TABLES;
    SHOW TABLE STATUS;

  • employees 테이블의 열이 무엇이 있는 확인(first_name과 gender)
    DESCRIBE employees; -- 또는 DESC employees;

  • 데이터 조회(first_name과 gender 열)
    SELECT first_name, gender FROM employees;

  1. 데이터베이스와 테이블 생성

  • 데이터베이스 생성
    DROP DATABASE IF EXISTS sqlDB;
    CREATE DATABASE sqlDB;
    SHOW DATABASES;

  • 테이블 생성
    USE sqlDB;
    CREATE TABLE userTbl -- 회원 테이블
    (
    userID CHAR(8) NOT NULL PRIMARY KEY, -- 사용자 아이디 (pK)
    name VARCHAR(10) NOT NULL, -- 이름
    birthYear INT NOT NULL, -- 출생년도
    addr CHAR(2) NOT NULL, -- 지역(경기, 서울, 경남 식으로 2글자만 입력)
    mobile1 CHAR(3), -- 휴대폰의 국번 (011, 016, 017, 018, 019, 010등)
    mobile2 CHAR(8), -- 휴대폰의 나머지 번호 (하이픈제외)
    height SMALLINT, -- 키
    mDate DATE -- 회원 가입일
    );

기본 키 (Primary Key) #
Data-Table에 있는 유일하게 구분되는 Data-Key를 기본 키(Primary Key)라 부른다.
Primary Key는 유일한 값이기 때문에 중복된 값을 가질 수 없다.
Primary Key는 공백을 가질 수 없습니다.

  • 테이블 확인(열)
    DESCRIBE userTbl; -- 또는 DESC userTbl;

  • 데이터 입력
    INSERT INTO userTbl VALUES('LSG', '이승기', 1987, '서울', '011', '1111111', 182, '2008-8-8');
    INSERT INTO userTbl VALUES('KBS', '김범수', 1979, '경남', '011', '2222222', 173, '2012-4-4');
    INSERT INTO userTbl VALUES('KKH', '김경호', 1971, '전남', '019', '3333333', 177, '2007-7-7');
    INSERT INTO userTbl VALUES('JYP', '조용필', 1950, '경기', '011', '4444444', 166, '2009-4-4');
    INSERT INTO userTbl VALUES('SSK', '성시경', 1979, '서울', NULL , NULL , 186, '2013-12-12');
    INSERT INTO userTbl VALUES('LJB', '임재범', 1963, '서울', '016', '6666666', 182, '2009-9-9');
    INSERT INTO userTbl VALUES('YJS', '윤종신', 1969, '경남', NULL , NULL , 170, '2005-5-5');
    INSERT INTO userTbl VALUES('EJW', '은지원', 1972, '경북', '011', '8888888', 174, '2014-3-3');
    INSERT INTO userTbl VALUES('JKW', '조관우', 1965, '경기', '018', '9999999', 172, '2010-10-10');
    INSERT INTO userTbl VALUES('BBK', '바비킴', 1973, '서울', '010', '0000000', 176, '2013-5-5');

  • 데이터 확인
    SELECT * FROM userTbl;

  • 테이블 생성
    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
    mysqldump -h 192.168.0.14 -u director -p sqldb > sqldb.sql

  • sqlDB 가져오기
    mysql -h 192.168.0.14 -u director -p
    DROP DATABASE sqldb;
    CREATE DATABASE sqlDB; -- 빈거 하나만들고
    USE sqlDB;
    source sqldb.sql
    show tables;

  1. 특정 조건의 데이터만 조회

  • 기본적인 WHERE절(SELECT 열(필드)이름 FROM 테이블이름 WHERE 조건식;)
    USE sqlDB;
    SELECT FROM userTbl;
    SELECT     FROM userTbl WHERE name = '김경호';

  • 관계 연산자의 사용
    SELECT userID, Name FROM userTbl WHERE birthYear >= 1970 AND height >= 182;
    SELECT userID, Name FROM userTbl WHERE birthYear >= 1970 OR height >= 182;

  • BETWEEN ... AND와 IN() 그리고 LIKE
    SELECT Name, height FROM userTbl WHERE height >= 180 AND height <= 183;
    SELECT Name, height FROM userTbl WHERE height BETWEEN 180 AND 183;
    SELECT Name, addr FROM userTbl WHERE addr='경남' OR addr='전남' OR addr='경북';
    SELECT Name, addr FROM userTbl WHERE addr IN ('경남','전남','경북');
    SELECT Name, height FROM userTbl WHERE name LIKE '김%';
    SELECT Name, height FROM userTbl WHERE name LIKE '_종신';

profile
eeapbh
.
이전 포스트

#20230306(NAT Gateway VMware, 서브넷이해)

다음 포스트

#20230308(데이터베이스, php, NMS)

0개의 댓글