📖 참고 📖
1️⃣2️⃣3️⃣4️⃣5️⃣6️⃣7️⃣
⬆️⬇️➡️
📖 참고 📖 용어 정리
- 데이터
- 하나하나의 단편적인 정보
- 정보는 있으나 아직 체계화 되지 못한 상태
- 테이블
- 데이터 입력하기 위해, 표 형태로 표현하는 것
- 데이터베이스 (DB)
- 테이블이 저장되는 저장소
- 각 데이터베이스는 서로 다른 고유한 이름을 가지고 있어야 함
- DBMS (DataBase Management System)
- 데이터베이스를 관리하는 시스템 또는 소프트웨어
- 열 (=컬럼, 필드)
- 각 테이블은 열로 구성
- 열 이름
- 각 열을 구분하기 위한 이름
- 열 이름은 각 테이블 내에서는 중복되지 않고, 고유해야 함
- 데이터 형식
- 열의 데이터 형식
- 테이블을 생성할 때 열 이름과 함께 지정해야 함
- 행 (=로우, 레코드)
- 실질적인 데이터
- 기본 키
- 기본 키(주키)는 각 행을 구분하는 유일한 열
- 중복되거나 비어있으면 X
- 각 테이블에는 기본 키 하나만 지정되어 있어야 함
- 외래 키 필드
- 두 테이블의 관계를 맺어주는 키
- SQL
- 구조화된 질의 언어
CREATE DATABASE study default CHARACTER SET UTF8;
# CREATE DATABASE study : study라는 데이터베이스 생성
# default CHARACTER SET UTF8 : 한글을 사용할 수 있는 UTF8로 문자열 저장
GRANT ALL PRIVILEGES ON study.* TO study_user@localhost IDENTIFIED BY 'study';
EXIT;
mysql -u study_user -p
USE study;
# GRANT : 사용자에게 데이터베이스 사용 권한 적용
# ALL PRIVILEGES : 데이터베이스에 대한 모든 권한 (디비 삭제도 가능)
# ON study.* : 권한 대상은 study이며, study.*은 study의 모든 테이블을 의미
# TO study_user@localhost : study_user은 사용 권한을 받는 사용자 (없는 유저라면 새롭게 생성)
# IDENTIFIED BY 'study' : 사용자의 비밀번호를 study로 설정
CREATE TABLE professor
(
_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(32) NOT NULL,
belong VARCHAR(12) DEFAULT 'FOO',
phone VARCHAR(12)
) ENGINE=INNODB;
DESCRIBE professor;
# _id : 이름의 칼럼 추가
# INT : 데이터 타입 설정
# PRIMARY KEY : 기본키로 설정 (하단에 PRIMARY_KEY(_id)로 설정 가능)
# AUTO_INCREMENT : 자동 인덱스 추가
# NOT NULL : 입력할 때 항상 값을 넣어줘야 함
# DEFAULT : 아무런 값을 입력하지 않을 때 자동으로 입력되는 값
# ENGINE=INNODB : mysql의 데이터 저장 구조를 선택
# DESC professor : 테이블 구조 확인
INSERT INTO professor
(name, belong, phone)
VALUES('유재석', 'IDE','01112345678');
INSERT INTO professor
(name, belong, phone)
VALUES('황영조', 'MSE', '01121342443');
INSERT INTO professor
(name, belong, phone)
VALUES('케이멀', 'ESE', '01123424343');
INSERT INTO professor
(_id, name, belong, phone)
VALUES(256, '호날두', 'IME', '01134343222');
INSERT INTO professor
(name, belong, phone)
VALUES( '리오넬', 'IDE', '01123432432');
SELECT _id, belong, phone FROM professor;
SELECT * FROM professor;
# INSERT INTO professor : professor에 레코드 삽입
# (name, belong, phone) : professor가 가지는 컬럼 명시
# VALUES('유재석', 'IDE', '01112345678') : name, belong, phone 순서로 설정
SELECT * FROM student;
SELECT * FROM student ORDER BY _id;
# ORDER BY : 데이터를 특정 컬럼을 기준으로 정렬
SELECT * FROM student WHERE belong = 'IDE';
SELECT * FROM student WHERE _id LIKE '2009%';
SELECT * FROM student WHERE _id NOT LIKE '2009%';
SELECT * FROM student WHERE NOT belong = 'IDE';
SELECT * FROM student WHERE belong <> 'IDE';
# WHERE belong = 'IDE' : belong 조건이 IDE와 일치하는 것 찾음
# WHERE _id LIKE '2009%' : 2009로 시작하는 모든 값 찾음
# <>와 !=는 동일한 기능
DROP TABLE study;
CREATE INDEX name_idx ON student(name); # 인덱스 생성
CREATE TABLE student (
name VARCHAR(20) NOT NULL,
INDEX name_idx(name)
); # 테이블 생성시 인덱스 생성
SHOW INDEX FROM INDEX; # 인덱스 조회
ALTER TABLE student DROP INDEX name_idx; # 인덱스 삭제
CREATE VIEW view_student AS SELECT name, phone FROM student; # 뷰 생성
SELECT * FROM student; # 뷰 조회
DROP VIEW view_student; # 뷰 삭제
개념
함수와 차이점
📋 코드 📋
DELIMITER //
CREATE PROCEDURE myProc(
param_name VARCHAR(20),
param_age INT
)
BEGIN
# 변수 선언
DECLARE param_num INTEGER;
SELECT COUNT(*) INTO param_num FROM student;
INSERT INTO student(name, belong, phone, status) VALUES(param_name, "소속", "01055555555", param_age);
END //
DELIMITER ;
# 프로시저 실행
CALL myProc("김수현", 20);
# DELIMITER : 구분자를 의미 (기존 세미콜론 대신하고, 원래대로 돌려놓기)
# -> CREATE PROCEDURE~END를 하나의 단락으로 묶어주는 효과 (프로시저 작성이 완료되지 않았음에도 실행되는 것 방지)
# 삭제 데이터 보관할 테이블 생성
CREATE TABLE deleteTBL (
memberId CHAR(8),
memberName CHAR(5),
memberPhone CHAR(20),
deleteDate DATE
);
# 트리거 생성
DELIMITER //
CREATE TRIGGER trg_delete
AFTER DELETE
ON student
FOR EACH ROW
BEGIN
-- OLD 테이블의 내용을 백업 테이블에 삽입
INSERT INTO deleteTBL VALUES(OLD._id, OLD.name, OLD.phone, CURDATE());
END //
DELIMITER ;
# AFTER DELETE : 삭제 후에 작동하게 지정
# ON student : 트리거 부착할 테이블
# FOR EACH ROW : 각 행마다 적용시킴
📖 참고 📖
- 데이터베이스 튜닝
- 데이터베이스 성능을 향상시키거나 응답하는 시간을 단축시키는 것
- 쿼리에 대한 응답을 줄이기 위해서 가장 집중적으로 봄
- 전체 테이블 검색
- 인덱스가 없을 경우 처음부터 끝까지 테이블 전체 검색
- Full Table Scan
현재의 데이터베이스를 다른 매체에 보관하는 작업
데이터베이스에 문제가 발생했을 때 다른 매체에 백업된 데이터를 이용해서 원상태로 돌려놓는 작업
-- 회원 테이블 생성
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 -- 회원 가입일
);
-- 회원 구매 테이블 생성
CREATE TABLE buytbl (
num int auto_increment not null primary key,
userID char(8) not null,
prodName char(8) not null,
groupName char(4),
price int not null,
amount smallint not null,
FOREIGN KEY(userID) REFERENCES usertbl(userID)
);
SELECT select_expr
[FROM table_references]
[WHERE where_condition]
[GROUP BY {col_name | expr | position}]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}]
SELECT
[ALL | DISTINCT | DISTINCTROW]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT][SQL_BIG_RESULT][SQL_BUFFER_RESULT]
[SQL_NO_CACHE][SQL_CALC_FOUND_ROWS]
select_expr [, select expr ...]
[FROM table_references]
[PARTITION partition_list]
[WHERE where_condition]
[GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
[HAVING where_condition]
[WINDOW window_name AS (window_spec)
[, window_name AS (window_spec) ...]
[ORDER BY {col_name | expr | position} [ASC|DESC], ... [WITH ROLLUP]]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[INTO OUTFILE 'file_name'
| CHARACTER SET charset_name] export_options
| INTO DUMPFILE 'file_name'
| INTO var_name [, var_name]]
[FOR {UPDATE | SHARE} [OF tbl_name [, tbl_name] ...] [NOWAIT|SKIP LOCKED] | LOCK IN SHARE MODE]]
SELECT 필드 이름 FROM 테이블 이름 WHERE 조건식;
SELECT userID, Name FROM usertbl WHERE birthYear >= 1970 AND height >= 182;
-- BETWEEN ... AND
SELECT name, height FROM usertbl WHERE height BETWEEN 180 AND 183;
-- IN()
SELECT name, addr FROM usertbl WHERE addr IN ('경남', '전남', '경북');
-- LIKE
SELECT name, height FROM uertbl WHERE name LIKE '김%';
SELECT name, height
FROM usertbl
WHERE height > (SELECT height FROM usertbl WHERE name = '김경호');
-- 하위 쿼리가 2 이상의 값을 반환하여 오류 발생
select name, height
from usertbl
where height >= (
select height
from usertbl
where addr='경남'
);
-- ANY (하위 조건 - 키가 170보다 크거나 같은 사람)
select name, height
from usertbl
where height >= ANY (
select height
from usertbl
where addr='경남'
);
-- ALL (상위 조건 - 키가 173보다 크거나 같은 사람)
select name, height
from usertbl
where height >= ALL (
select height
from usertbl
where addr='경남'
);
SELECT name, mDate FROM usertbl ORDER BY mDate DESC;
SELECT DISTINCT addr FROM usertbl;
SELECT emp_no, hire_date FROM employees
ORDER BY hire_date ASC
LIMIT 5;
SELECT 컬럼명
[FROM table_references]
[WHERE where_condition]
[GROUP BY {col_name|expr|position}]
[HAVING where_condition]
[ORDER BY {col_name|expr|position}]
SELECT userID AS '사용자', SUM(price * amount) AS '총구매액'
FROM buytbl
GROUP BY userID
HAVING SUM(price*amount) > 1000;
SELECT num, groupName, SUM(price * amount) AS '비용'
FROM buytbl
GROUP BY groupName, num
WITH ROLLUP;
INSERT [INTO] 테이블[(열1, 열2, ...)] VALUES(값1, 값2, ...)
INSERT IGNORE INTO memberTBL VALUES ('BBK', '비비코', '미국');
INSERT INTO memberTBL VALUES ('BBK', '비비코', '미국')
ON DUPLICATE KEY UPDATE name='비비코', addr='미국';
CREATE TABLE testTBL2 (
id int AUTO_INCREMENT PRIMARY KEY,
userName char(3),
age int);
INSERT INTO testTBL2 VALUES (NULL, '지민', 22);
-- 마지막에 입력된 값 확인
SELECT LAST_INSERT_ID();
-- AUTO_INCREMENT 입력값 변경
ALTER TABLE testTBL2 AUTO_INCREMENT=100;
-- AUTO_INCREMENT 증가값 변경 (서버 변수 변경)
SET @@auto_increment_increment=3;
INSERT INTO 테이블 이름(열 이름1, 열 이름2, ...)
SELECT문;
-- employees 데이터 복사
CREATE TABLE testTBL4 (id int, Fname varchar(50), Lname varchar(50));
INSERT INTO testTBL4
SELECT emp_no, first_name, last_name
FROM employees.employees;
UPDATE 테이블 이름
SET 열1 = 값1, 열2 = 값2, ...
WHERE 조건;
DELETE FROM 테이블 이름 WHERE 조건;
DELETE FROM 테이블명;
DROP TABLE 테이블명;
TRUNCATE TABLE 테이블명;
📖 참고 📖
- USE
- 사용할 데이터베이스 지정
USE 데이터베이스_이름;
- SHOW
- 현재 서버에 존재하는 데이터베이스/테이블 정보 조회
SHOW DATABASES; SHOW TABLE;
- DESCRIBE (DESC)
- 테이블 정보 확인
DESCRIBE 테이블명; DESC 테이블명;
- 테이블 복사
- PK, FK 등 제약조건은 복사되지 않음
CREATE TABLE buytbl2 (SELECT * FROM buytbl);
- 트랜잭션
- 테이블의 데이터를 변경(입력/수정/삭제) 할 때 실제 테이블에 완전히 적용X
- 임시로 적용시키는 것
- 실수가 발생할 경우 취소 가능
📖 참고 📖
1️⃣2️⃣3️⃣4️⃣5️⃣6️⃣7️⃣
⬆️⬇️➡️
📖 참고 📖
1️⃣2️⃣3️⃣4️⃣5️⃣6️⃣7️⃣
⬆️⬇️➡️
📖 참고 📖
1️⃣2️⃣3️⃣4️⃣5️⃣6️⃣7️⃣
⬆️⬇️➡️
📖 참고 📖
1️⃣2️⃣3️⃣4️⃣5️⃣6️⃣7️⃣
⬆️⬇️➡️
📖 참고 📖
1️⃣2️⃣3️⃣4️⃣5️⃣6️⃣7️⃣
⬆️⬇️➡️
📖 참고 📖
1️⃣2️⃣3️⃣4️⃣5️⃣6️⃣7️⃣
⬆️⬇️➡️
📖 참고 📖
1️⃣2️⃣3️⃣4️⃣5️⃣6️⃣7️⃣
⬆️⬇️➡️
📖 참고 📖
1️⃣2️⃣3️⃣4️⃣5️⃣6️⃣7️⃣
⬆️⬇️➡️
📖 참고 📖
1️⃣2️⃣3️⃣4️⃣5️⃣6️⃣7️⃣
⬆️⬇️➡️