[Database] 쿼리 명령어

Create

CREATE TABLE `my testTBL` (id INT);
SELECT * FROM `my testTBL`;
# ~ 밑에 있는 백틱 사용
# 열이름 띄어쓰기 할때 사용 // 열이름 띄어쓰기 없는걸 추천
-- DB에서 사용하는 주석
# 사용성을 위해 #을 지원

 

Drop

DROP TABLE `my testTBL`;

 

Use

USE shopdb;
# USE DB이름;

 

Select

SELECT * FROM membertbl;
# SELECT 열이름 FROM 테이블이름;

SELECT memberID, memberName FROM membertbl;
SELECT memberName, memberID FROM membertbl;
# 열이름 적는 순서 = 출력순서

SELECT * FROM membertbl WHERE memberName='지운이';
# WHERE 조건절 // 조건 열 이름 = '조건내용'

SELECT memberName, memberID, memberAddress FROM membertbl WHERE memberName='한주연';

 

Between

USE sqldb;

SELECT * FROM userTBL WHERE height >= 180 AND height <= 182;
SELECT * FROM userTBL WHERE height BETWEEN 180 AND 182;

Or

SELECT * FROM userTBL WHERE addr='경북' OR addr='경남' OR addr='전남';
SELECT * FROM userTBL WHERE addr IN ('경북','경남','전남');

Like

SELECT * FROM userTBL WHERE name LIKE '김%';
# % 글자의 수 제한없이 전부
SELECT * FROM userTBL WHERE name LIKE '_종신';
# _ 글자 수 만큼 언더바로 표현

USE employees;
SELECT * FROM employees WHERE gender = 'M' AND date_format(hire_date, '%Y-%m-%d') > '1990-12-31' AND last_name LIKE 'M%';

Subquery, Order by

SELECT * FROM employees WHERE hire_date IN (SELECT hire_date FROM employees WHERE date_format(hire_date, '%Y-%m-%d') > '1989-12-31') ORDER BY birth_date DESC;

Distinct

USE sqldb;
SELECT addr FROM userTBL ORDER BY addr;
SELECT DISTINCT addr FROM userTBL ORDER BY addr;

# DISTINCT : 중복제거

 

Limit

USE employees;

SELECT emp_no, hire_ate FROM employees ORDER BY hire_date;
SELECT emp_no, hire_ate FROM employees ORDER BY hire_date LIMIT 10;
# LIMIT(출력갯수 조절)

 

USE sqldb;

CREATE TABLE buyTBL2 (SELECT * FROM buyTBL);
SELECT * FROM buyTBL2;
# CREATE로 테이블 복사 : 제약조건, 키 등등의 정보는 가져오지 않음

 

CREATE TABLE buyTBL3 (SELECT userID, prodName, price * amount FROM buyTBL);
SELECT * FROM buyTBL3;
# 원하는 열만 복사 붙여넣기 가능

 

Group by

SELECT userID, amount FROM buyTBL ORDER BY userID;
SELECT userID, SUM(amount) FROM buyTBL GROUP BY userID;
# 집계 함수 사용, group by로 중복값을 하나로 묶을 수 있음

 

집계함수

SELECT AVG(amount) AS '평균 구매 개수' FROM buyTBL;
# AS : Alias -> 별칭

SELECT name, MAX(height), MIN(height) FROM userTBL;
SELECT name, MAX(height), MIN(height) FROM userTBL GROUP BY name;
SELECT name, height FROM userTBL
WHERE height = (SELECT MIN(height) FROM userTBL)
OR height = (SELECT MAX(height) FROM userTBL);

SELECT COUNT(*) FROM userTBL;
SELECT COUNT(mobile1) FROM userTBL;
# null 값은 카운트 안함

 

SELECT employees.emp_no, first_name, last_name, hire_date, salary FROM employees, salaries 
WHERE salary > (SELECT AVG(salary) FROM salaries) LIMIT 100;

 

Having

USE sqldb;

SELECT userID AS "사용자", SUM(price*amount) AS "총구매액"
FROM buyTBL
GROUP BY userID
HAVING SUM(price*amount) > 1000;

# group by와 세트, group 에 대한 조건

 

USE employees;

SELECT AVG(salary) FROM salaries;

SELECT *
FROM salaries
WHERE from_date = '1985-02-07'
GROUP BY emp_no
HAVING MAX(salary) > (SELECT AVG(salary) FROM salaries)
LIMIT 100;

 

Rollup

USE sqldb;

SELECT num, groupName, SUM(price * amount) AS '비용'
FROM buyTBL
GROUP BY groupName, num
WITH ROLLUP;
# ROLLUP : 소합계

 

Insert

CREATE TABLE testTBL1 (id INT, userName CHAR(3), age INT) ;
INSERT INTO testTBL1 VALUES(1, '홍길동', 2500) ;
SELECT * FROM testTBL1

 

INSERT INTO testTBL1(id, userName) VALUES(2, '설현');

# NULL 값이 있을 때 입력 방법 // 데이터 입력할 열 이름을 명시

 

INSERT INTO testTBL1(userName, age, id) VALUES('초아', 30, 3);
# 입력 순서 바꿀 수 있음

 

CREATE TABLE testTBL2
(id INT AUTO_INCREMENT PRIMARY KEY,
 userName CHAR(3), age INT) ;
INSERT INTO testTBL2 VALUES (NULL, '지민', 29);
INSERT INTO testTBL2 VALUES (NULL, '유나', 28);
INSERT INTO testTBL2 VALUES (NULL, '유경', 27);
SELECT * FROM testTBL2;

 

INSERT INTO testTBL2(userName,age) VALUES ('준수', 28), ('동혁', 25), ('영재', 32);

#  한번에 여러개 삽입
SELECT * FROM testTBL2;

Alter

ALTER TABLE testTBL2 AUTO_INCREMENT=100;
# ALTER : 수정, 100을 시작값으로 1씩 증가로 변경
INSERT INTO testTBL2 VALUES (NULL, '찬비', 27);
INSERT INTO testTBL2 VALUES (NULL, '유림', 25);
SELECT * FROM testTBL2;

 

Set

SET @@auto_increment_increment=3;
# DB서버 -> DBMS -> MariaDB 변수 수정
INSERT INTO testTBL2 VALUES (NULL, '지인', 24);
INSERT INTO testTBL2 VALUES (NULL, '인영', 24);
SELECT * FROM testTBL2

 

Update

UPDATE testTBL2 SET age = 26 WHERE userName = '유림';
# UPDATE : 테이블 데이터 수정
SELECT * FROM testTBL2;

 

Drop, Truncate

DROP TABLE testtbl2;
# 테이블 삭제
TRUNCATE TABLE testtbl1;
# 테이블 내용 삭제 // 트랜잭션 x // 복구 안됨

 

시간 함수

SELECT ADDDATE('2023-11-03', INTERVAL 31 DAY), 
 ADDDATE('2023-11-03', INTERVAL 1 MONTH);
 
SELECT CURDATE(), YEAR(CURDATE()), MONTH(CURDATE()), DAYOFMONTH(CURDATE());
SELECT HOUR(CURTIME()), MINUTE(CURTIME()), SECOND(CURTIME());

SELECT DATEDIFF(NOW(), '2022-04-12');

 

순위 함수

SELECT RANK() OVER(ORDER BY height DESC) "키순위", name, addr, height FROM userTBL;

 

피벗 테이블

CREATE TABLE pivotTBL
(uname CHAR(3),
 season CHAR(2),
 amount INT);
  
INSERT INTO pivotTBL VALUES
('김범수','겨울',10),('윤종신','여름',15),('김범수','가을',25),
('김범수','봄',3),('김범수','봄',37),('윤종신','겨울',40),
('김범수','여름',14),('김범수','겨울',22),('윤종신','여름',64);

SELECT * FROM pivotTBL;

SELECT uname, 
 SUM(IF(season='봄', amount, 0)) AS '봄',
 SUM(IF(season='여름', amount, 0)) AS '여름',
 SUM(IF(season='가을', amount, 0)) AS '가을',
 SUM(IF(season='겨울', amount, 0)) AS '겨울',
 SUM(amount) AS '합계'
FROM pivotTBL GROUP BY uname;

 

Inner Join

SELECT * FROM buyTBL
INNER JOIN userTBL
ON buyTBL.userID = userTBL.userID
WHERE buyTBL.userID = 'jyp';
# 두 테이블 모든 열 출력

SELECT buyTBL.userID, name, prodName, addr, CONCAT(mobile1, mobile2) AS '연락처'
FROM buyTBL
INNER JOIN userTBL
ON buyTBL.userID = userTBL.userID;
# 겹치는 열 이름은 테이블명까지 명확하게 적기

 

SELECT buyTBL.userID, name, prodName, addr, CONCAT(mobile1, mobile2) AS '연락처'
FROM buyTBL, userTBL
WHERE buyTBL.userID = userTBL.userID;
# inner join을 쓰지 않아도 됨

 

SELECT B.userID, U.name, B.prodName, U.addr, CONCAT(U.mobile1, U.mobile2) AS '연락처'
FROM buyTBL B
INNER JOIN userTBL U
ON B.userID = U.userID;

 

CREATE TABLE stdTbL
(stdName CHAR(3) PRIMARY KEY,
 addr CHAR(2));
 
INSERT INTO stdTbL VALUES
('김범수','경남'),('성시경','서울'),('조용필','경기'),
('은지원','경북'),('바비킴','서울');

CREATE TABLE clubTbL
(clubName CHAR(2) PRIMARY KEY,
 roomNo CHAR(4));
 
INSERT INTO clubTbL VALUES
('수영','101호'),('바둑','102호'),('축구','103호'),('봉사','104호');

CREATE TABLE stdclubTBL
(member INT PRIMARY KEY,
 stdName CHAR(3),
 clubName CHAR(3),
 FOREIGN KEY(stdName) REFERENCES stdTBL(stdName),
 FOREIGN KEY(clubName) REFERENCES clubTBL(clubName));

INSERT INTO stdclubTBL VALUES
(1,'김범수','바둑'),(2,'김범수','축구'),(3,'조용필','축구'),
(4,'은지원','축구'),(5,'은지원','봉사'),(6,'바비킴','봉사');

SELECT S.stdName, S.addr, C.clubName, C.roomNo
FROM stdTBL S
 INNER JOIN stdclubTBL SC
  ON S.stdName = SC.stdName
 INNER JOIN clubTBL C
  ON SC.clubName = C.clubName
ORDER BY S.stdName;

SELECT * FROM stdTbL;
SELECT * FROM clubTbL;
SELECT * FROM stdclubTBL;

 

Outer Join

SELECT buyTBL.userID, name, prodName, addr, CONCAT(mobile1, mobile2) AS '연락처'
FROM userTBL
LEFT JOIN buyTBL
ON buyTBL.userID = userTBL.userID
ORDER BY userID;

SELECT buyTBL.userID, name, prodName, addr, CONCAT(mobile1, mobile2) AS '연락처'
FROM buyTBL
RIGHT JOIN userTBL
ON buyTBL.userID = userTBL.userID
ORDER BY userID;
# LEFT = RIGHT // Join 순서만 바꾸면

 

View

CREATE VIEW v_emp
AS (SELECT employees.emp_no, employees.first_name, employees.last_name, n_salary.max_salary, employees.hire_date, dept_emp.dept_no
FROM employees
INNER JOIN dept_emp ON employees.emp_no = dept_emp.emp_no
INNER JOIN n_salary ON dept_emp.emp_no = n_salary.emp_no
WHERE dept_emp.dept_no = 'd005');

SELECT * FROM v_emp LIMIT 100;

 

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;

SELECT * FROM indexTBL WHERE first_name = 'Mary';
# 인덱스 없이 조건별 검색 // 0.094초

EXPLAIN SELECT * FROM indexTBL WHERE first_name = 'Mary';
# 쿼리문 실행계획 내용 출력
# type = ALL -> 인덱스 안쓴거 확인

CREATE INDEX idx_indexTBL ON indexTBL (first_name);
# 인덱스 설정 -> 검색 많이 할 열 이름으로 생성
SELECT * FROM indexTBL WHERE first_name = 'Mary';
# 인덱스 설정한 테이블 조건별 검색  // 0초

 

Trigger

INSERT INTO memberTBL VALUES('피겨', '김연아', '경기도 군포시');
SELECT * FROM memberTBL;

CREATE TABLE deletedMemberTBL
( memberID CHAR(8),
  memberName CHAR(5),
  memberAddress CHAR(20),
  deleteDate date
);
#  트리거 룰 설정 전 백업 테이블 생성

DELIMITER //
CREATE TRIGGER trg_deletedMemberTBL # 트리거 이름
AFTER DELETE # 동작 조건, 삭제 발생 후 동작
ON memberTBL # 대상 테이블 이름
FOR EACH ROW # 대상(모든 행) 설정
BEGIN  # 동작 구문
INSERT INTO deletedMemberTBL
VALUES (OLD.memberID, OLD.memberName, OLD.memberAddress, CURDATE());
END //
DELIMITER ;
# 트리거 룰 설정

DELETE FROM memberTBL WHERE memberName = '김연아';
# 트리거 동작

SELECT * FROM memberTBL;
SELECT * FROM deletedMemberTBL;
# 백업 테이블에 데이터 백업 확인

 

Backup

cmd 창에서

backup -> mysqldump -uroot -p database_name > file_name

restore -> mysql -uroot -p database_name < file_name