Database MySQL

EUNLEE·2022년 7월 19일
dbmysql카카오클라우드스쿨
0

카카오 클라우드 스쿨 1기 개발자 과정

목록 보기
11/14

2022.07.19 화요일

🐬 Database

  • 데이터의 집합
  • 관련있는 대용량의 데이터 집합을 체계적으로 구성해 놓은 것
  • 여러명 사용, 데이터 처리, 여러개의 Database 관리
    • 이것을 위해 software가 필요해요. → DBMS(Database Management System)
      • MySQL
      • MariaDB
      • Oracle
        • 상용시장 점유율 1등
      • DB2
        • 메인 프레임 시장 1등
      • SQL Server
      • Postgres

🐬 DBMS 특징

  1. Integrity(무결성)
    • 데이터의 오류가 있어서는 안돼요.
    • constraints(제약조건)
  2. 데이터의 독립성
    • Database의 크기, 위치 등을 변경해도 기존에 사용하고 있는 softward는 영향을 받지 않아야 해요.
  3. 보안
  4. 중복의 최소화
  5. 안정성(backup/restore)

🐬 DBMS의 종류

  1. 계층형 database
  2. 네트워크 database → 계층형의 문제를 해결했지만 구현이 안된다.
  3. IBM (E.F. codd) → Relational Database(관계형)
  4. OO Database(객체지향)
  5. 객체-관계형 Database → Oracle

🐬 DBMS 구조


🐬 MySQL과 MySQL Workbench

MySQL 환경변수 설정하기

  1. 현재 사용중인 shell 확인하기
echo $SHELL

  1. zsh는 .zshrc 파일에 환경변수를 설정해주면 된다.
 # zshrc 파일 열어서 편집하기
vi ~/.zshrc
# MySQL 환경변수
export MYSQL_PATH=/opt/homebrew/bin/mysql
export PATH=$PATH:$MYSQL_PATH

MySQL 서버 실행하기

# brew에서 mysql 실행하기
brew services start mysql

# 실행 확인
brew services list

MySQL 접속하기(root로 로그인하기)

mysql -u root -p
# 위 명령어 치고 password 입력

sql 파일 import 하기

해당 sql 파일이 있는 폴더로 이동한 뒤, mysql에 접속한다.

source emlpoyees.sql

MySQL의 모든 database 조회하기

show databases;

MySQL Workbench에서 새로운 Schema 만들기

  1. 왼쪽 Scemas 탭에서 우클릭 Create Schema… 클릭
  2. Schema Name 설정 후 오른쪽 아래 Apply 클릭

MySQL Workbench에서 새로운 Table 만들기

  1. Tables에서 우클릭 Create Table… 클릭
  2. Table Name과 Column들을 생성해주고 오른쪽 아래 Apply 클릭

  • DataType에서 VARCHAR(10)의 10은 max값을 의미한다.
  • PK: Primari Key
  • NN: Not Null

SQL 쿼리문으로 TABLE 생성하기

-- TABLE 생성
create table indexTBL (
	first_name varchar(14),
    last_name varchar(16),
    hire_date DATE
);

외부 TABLE 가져오기

INSERT INTO indexTBL
	SELECT first_name, last_name, hire_date
    FROM employees.employees
    LIMIT 500; -- 500개만 가지고 오기
-- 확인하기
SELECT * FROM indexTBL;

first_name이 “Mary”인 데이터 찾기

select * from indexTBL WHERE first_name='Mary';

데이터베이스 선택하기

-- SELECT * FROM database이름.table이름;
SELECT * FROM shopdb.memberTBL;

그런데 database 이름을 매번 쓰는게 너무 귀찮다!
방법1. MySql Workbench에서 database를 더블클릭하면 진해짐
→ 해당 database를 사용하겠다는 의미!
방법2. USE 명령어 사용하기

USE shopdb; -- 사용할 Database 선택하기
SELECT * FROM memberTBL;

인덱스 만들기

CREATE INDEX idx_indexTBL_firstname ON indexTBL(first_name);

VIEW 만들기

create view v_memberTBL
AS SELECT memberName, memberAddress FROM memberTBL;

Stored Procedure 만들고 사용하기

DELIMITER //
CREATE PROCEDURE myFUNC()
BEGIN
	SELECT * FROM memberTBL WHERE memberName = '아이유';
	SELECT * FROM productTBL WHERE productName = '냉장고';

END //

DELIMITER ; 

CALL myFUNC();

🐬 Schema

Database 안에서

  • data의 구조
  • data의 표현 방법, type
  • data의 관계

를 형식 언어를 이용해서 정의한 구조이다.

  • External schema
  • Conceptual schema
    • data의 논리적인 구조
  • Internal schema
    • 물리적인 저장구조(file system)

→ MySQL(MariaDB)에서는 Schema == Database

🐬 Index

  • primary key를 설정하면 해당 column에 index가 설정
  • index를 사용하면 검색 속도가 빨라진다.
select * from indexTBL WHERE first_name='Mary';

-- 인덱스 설정
create index idx_indexTBL_firstname ON indexTBL(first_name);

인덱스 설정 전

인덱스 설정 후

🐬 View

  • 가상의 table
  • 실제로 view는 data를 가지고 있지 않다.

View를 사용하는 이유

  • data를 안전하게 유지하기 위해 사용한다.
    • 직접적인 table을 노출시키는 것은 데이터의 오염이나 유실의 위험이 있다.
  • 보안적인 측면
  • 사용의 편리성

실제로 프로젝트에서 개발 시에 TABLE을 직접 사용하는 경우는 없다.

🐬 Stored Procedure

  • 저장된 함수
  • 함수 interface를 제공
-- 함수 생성
DELIMITER // -- 구분자를 ;에서 //로 변경
CREATE PROCEDURE myFUNC()
BEGIN
	SELECT * FROM memberTBL WHERE memberName = '아이유';
	SELECT * FROM productTBL WHERE productName = '냉장고';

END //

DELIMITER ; // 다시 구분자를 ;로 변경

-- 함수 호출
CALL myFUNC();

🐬 Trigger

table에 trigger를 부착시킨다.

  • Insert, Update, Delete가 발생
  • (ex) 회원 table에서 회원정보를 삭제해야 하는 경우
    • 일반적으로 Flag 처리(update)

🐬 MySQL Workbench의 Utility

  • SQL 구문 자동 생성
  • Query editor 설정
    • 예약어 대문자로 변경
    • 자동완성(ctr + space)
    • 주석쿼리(block 잡고 ctr + /)
    • SQL 구문의 표준형태로 변경(ctr + b)
  • 사용자 생성과 권한

🐬 SQL 구문 실습

  • SELECT
    FROM
    WHERE 조건
    GROUP BY
    HAVING 조건(GROUP에 대한)
    ORDER BY
-- 사용할 데이터베이스 선택
USE employees;

-- 원칙적으로는 table select할 때 schema를 명시해줘야해요!
SELECT * FROM employees.titles;

-- USE를 사용한 경우 schema를 생략할 수 있어요!  
SELECT * FROM titles;

-- 원하는 컬럼만 추출할 수 있어요!
SELECT emp_no, title FROM titles;

-- Table의 명세를 확인하려면 DESC를 이용하세요!
DESC titles;

-- Alias를 이용할 수 있어요! (AS를 이용하면 돼요!)
select first_name AS '이름',
		gender AS '성별',
        hire_Date AS '입사일'
FROM employees;

-- 조건을 설정해서 데이터를 추출하고 싶어요!
DROP DATABASE IF EXISTS sqldb;   -- 만약 sqldb가 존재하면 삭제.

CREATE DATABASE sqldb;  -- 데이터베이스 생성

USE sqldb;  -- sqldb 사용

CREATE TABLE usertbl
( userID    CHAR(8) NOT NULL PRIMARY KEY,  -- 사용자 ID(PK)
  name      VARCHAR(10) NOT NULL, -- 이름
  birthYear INT NOT NULL, -- 출생연도
  addr      CHAR(2) NOT NULL, -- 지역(경기, 서울, 경남, etc)
  mobile1   CHAR(3), -- 휴대폰 국번(010)
  mobile2   CHAR(8), -- 휴대폰 나머지 전화번호(하이픈제외)
  height    SMALLINT, -- 키
  mDate     DATE -- 회원가입일
);

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

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

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

연습문제

USE sqldb;

SELECT * FROM usertbl;
SELECT * FROM buytbl;

-- 이름이 김경호인 사람을 usertbl에서 추출하려고 합니다.
SELECT * FROM usertbl WHERE name='김경호';

-- 1970년 이후에 출생하거나 신장이 182이상인 사람의 아이디와 이름을 조회하세요!
SELECT userID, name 
FROM usertbl 
WHERE birthYear >= 1970 OR height >= 182;

-- 키가 180 ~ 183인 사람의 이름과 키를 조회하세요!
SELECT name, height
FROM usertbl
-- WHERE 180 <= height and height <= 183; 
WHERE height BETWEEN 180 AND 183;

-- 지역이 경북, 전남, 전북인 사람을 조회하세요!
SELECT name, addr
FROM usertbl
-- WHERE addr='경남' OR addr='전남' OR addr='경북';
WHERE addr in ('경남', '전남', '경북');

-- 성이 김씨인 사람들의 이름과 키를 조사하세요!
SELECT name, height
FROM usertbl
WHERE name LIKE '김%'; -- %는 0개 이상(김범수, 김경호, 김치, 김건), _는 1개(김치, 김건)

-- 김경호보다 키가 크거나 같은 사람의 이름과 키를 조회하세요!
-- SELECT name, height
-- FROM usertbl
-- WHERE name='김경호'; -- 177

-- SELECT name, height
-- FROM usertbl
-- WHERE height >= 177;

SELECT name, height
FROM usertbl
WHERE height >= (
	SELECT height
    FROM usertb
    WHERE name='김경호'
);

-- 지역이 '경남'인 사람의 키보다 키가 크거나 같은 사람의 이름과 키를 조회하세요! 
SELECT name, height
FROM usertbl
WHERE height >= ANY(
	SELECT height
    FROM usertbl
    WHERE addr='경남'
);

-- 먼저 가입한 순으로 출력하세요!
-- DESC usertbl; -- 가입날짜: mDate
SELECT name, mDate
FROM usertbl
ORDER BY mDate ASC; -- 오름차순 ASC,내림차순 DESC;

-- usertbl에서 회원들의 거주지역이 어디인지를 출력하세요!
SELECT addr
FROM usertbl;

-- 먼저 가입한 순으로 4명만 출력하세요!
SELECT name, mDate
FROM usertbl
ORDER BY mDate ASC
LIMIT 4;

-- '윤종신','2005-05-05'
-- '김경호','2007-07-07'
-- '이승기','2008-08-08'
-- '조용필','2009-04-04'

SELECT name, mDate
FROM usertbl
ORDER BY mDate ASC
LIMIT 1, 3; -- 1번째부터 3개 추출하기 (첫번째는 0부터 시작)
-- '김경호','2007-07-07'
-- '이승기','2008-08-08'
-- '조용필','2009-04-04'

-- 테이블을 복사하는 전형적인 방법
-- 데이터만 복사되고 PK, FK와 같은 제약조건들은 복사가 안된다.
CREATE TABLE buytbl2(
	SELECT * FROM buytbl
    );

-- 구매테이블에서 사용자가 구매한 물품의 개수를 출력하세요!
SELECT userID, SUM(amount)
FROM buytbl
GROUP BY userID;

-- 구매테이블에서 각 사용자별 구매액의 총합을 출력하세요!
SELECT userID, SUM(amount*price)
FROM buytbl
GROUP BY userID;
profile
EUNLEE
은
이전 포스트

Java Thread 동기화, 입출력

다음 포스트

MySQL Insert, Update, Delete, Join

0개의 댓글