DBMS
(Database Management System)
MySQL 환경변수 설정하기
echo $SHELL
.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 만들기
Create Schema…
클릭Apply
클릭MySQL Workbench에서 새로운 Table 만들기
Create Table…
클릭Apply
클릭VARCHAR(10)
의 10은 max값을 의미한다.PK
: Primari KeyNN
: Not NullSQL 쿼리문으로 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();
Database 안에서
를 형식 언어를 이용해서 정의한 구조이다.
→ MySQL(MariaDB)에서는 Schema == Database
select * from indexTBL WHERE first_name='Mary';
-- 인덱스 설정
create index idx_indexTBL_firstname ON indexTBL(first_name);
인덱스 설정 전
인덱스 설정 후
View를 사용하는 이유
실제로 프로젝트에서 개발 시에 TABLE을 직접 사용하는 경우는 없다.
-- 함수 생성
DELIMITER // -- 구분자를 ;에서 //로 변경
CREATE PROCEDURE myFUNC()
BEGIN
SELECT * FROM memberTBL WHERE memberName = '아이유';
SELECT * FROM productTBL WHERE productName = '냉장고';
END //
DELIMITER ; // 다시 구분자를 ;로 변경
-- 함수 호출
CALL myFUNC();
table에 trigger를 부착시킨다.
Insert
, Update
, Delete
가 발생ctr + space
)ctr + /
)ctr + b
)-- 사용할 데이터베이스 선택
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;