[SQL] MySQL 실습(feat. MySQL WorkBench)

이전 내용

[SQL] MySQL 설치하기

---

MySQL 실습해보기
(feat. employees 데이터베이스)

 

MySQL에서는 employes DB를 사용하기 위한 쿼리문을 입력했다면, WorkBench에서는 사용하고자 하는 DB를 더블클릭해주기만 하면 된다. (사용 중인 DB일 경우 Bold 처리 된다.)

 

- SELECT 문: 데이터 조회

show tables;
select * from employees;
SELECT first_name, gender from employees;

 

◆ alias (as, 별칭)의 경우 'as' 를 붙여도 되고, 생략해도 되고, ' ' 까지 생략해도 된다.

주석의 경우, 한 줄일 때는 --, 여러줄 일때는 자바와 같이 /* ~ */

/* employees 테이블에서 이름, 성별, 입사일을 가져오되,
칼럼명도 이름, 성별, 입사일로 출력 */
SELECT first_name as '이름', gender '성별', hire_date 입사일 FROM employees;

 

- CREATE: 생성 (데이터베이스, 테이블 등)

DROP database if exists sqldb; -- 만약 sqldb 있으면 삭제
CREATE database sqldb;

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, -- 지역(두 글자, ex: 경기, 서울, 강남)
mobile1 CHAR(3), -- 휴대전화 국번(010 등, 생략 가능)
mobile2 CHAR(8), -- 휴대전화 나머지 번호 (하이픈 제외, 생략 가능)
height SMALLINT, -- 키 (생략 가능)
mDate DATE -- 회원가입일 (생략 가능)
);


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

SELECT * FROM buytbl;

 

 

- INSTER INTO: 테이블에 데이터 넣기

새로 만든 테이블 useretbl과 buytbl에 새로운 데이터를 넣는다.

INSERT INTO usertbl VALUES('LSG', '이승기', '1987', '서울', '010', '12341234', '184', '2020-08-08');
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, 32, 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, WHERE: 특정 조건으로 조회하기

SELECT 문에 WHERE 구문을 통해 특정 조건의 데이터를 조회할 수 있다.

• WHERE 구문 사용 시, 비교연산자 등을 이용할 수 있다.
• O%: % 는 모든 문자를 포함. (ex. 김%: 김으로 시작하는 모든 데이터, %종신: 종신으로 끝나는 모든 데이터)
• LIKE:  LIKE 구문은 텍스트 데이터의 패턴 검색에 사용되는 연산자
• _O: _는 한 글자 (ex. LIKE _종신: 종신으로 끝나고, 앞글자가 1자리인 모든 데이터)

/* 1970년 이후 출생, 신장 180 이상인 사람의 ID와 이름 조회 */
SELECT userID ID, name 이름 
	FROM usertbl 
	WHERE birthYear >= 1970 AND height >= 180;
    
-- 위의 조건을 BETWEEN 이용하여 사용
SELECT userID ID, name 이름 
	FROM usertbl
    WHERE birthYear BETWEEN 1970 AND 2005
		AND height BETWEEN 180 AND 190;
        
/* 주소가 '경남', '전남', '경북' 인 이름, 주소 조회
    WHERE addr = '경남' OR addr = '전남' OR addr = '경북'*/
SELECT name 이름, addr 주소
	FROM usertbl
    WHERE addr IN ('경남', '전남', '경북');

-- 이름이 김 자로 시작하는 회원 명단 출력
SELECT * FROM usertbl
	WHERE name like '김%';
    
-- 이름이 '종신'으로 끝나는 회원 명단 출력
SELECT * FROM usertbl
	WHERE name like '%종신';
    
-- 이름이 '종신'으로 끝나고 앞글자가 1자리인 회원 명단 출력
SELECT * FROM usertbl
	WHERE name like '_종신';
    
-- 이름이 '신'으로 끝나고 앞글자가 2자리인 회원 명단 출력
SELECT * FROM usertbl
	WHERE name like '__신';

• 결과가 여러개 일 경우, AVG, MIN, MAX 함수를 사용하여 해당되는 조건의 평균, 최솟값 또는 최댓값으로 기준을 설정할 수 있다.

-- 전체 사용자의 총 구매액 합계, 평균
SELECT SUM(price * amount) '총 구매액', AVG(price * amount) '평균 구매액' -- 같은 select 문에서는 alias 명 사용 불가
	FROM buytbl;
    
-- 사용자 중 가장 키가 작은 사람의 이름과 키 출력
SELECT name '이름', MIN(height) '키' FROM usertbl; -- 에러

SELECT name '이름', height '키' FROM usertbl
	WHERE height = (SELECT MIN(height) FROM usertbl);

-- 가장 큰 사람
SELECT name '이름', height '키' FROM usertbl
	WHERE height = (SELECT MAX(height) FROM usertbl);

 

◆ 서브쿼리: 하나의 SQL 문 안에 포함되어 있는 또 다른 SQL 문

[서브쿼리 관련 글]

 

• in, any, all 등의 함수를 사용하여 다수의 값과 비교하는 형식으로 사용할 수 있다.
• in: 비교값 중 하나라도 같은 경우 True
• any: 다수의 비교값 중 한개라도 만족하면 True
• all: 모든 조건에 만족할 경우 True

-- 서브쿼리
-- 회원 중에 '김경호'보다 키가 작은 회원의 이름과 키를 출력
SELECT name 이름, height 키 FROM usertbl
	WHERE height < 
    (SELECT height FROM usertbl WHERE name = '김경호');

-- 조건이 2개 이상일 경우    
-- 회원 중에 주소가 경남인 사람의 키보다 큰 회원 명단 출력
SELECT * FROM usertbl WHERE height >
	any (SELECT height FROM usertbl WHERE addr = '경남'); -- 둘 중 하나라도 만족 (or)
    
SELECT * FROM usertbl WHERE height >
	all (SELECT height FROM usertbl WHERE addr = '경남'); -- 둘 다 만족 (and)
    
SELECT * FROM usertbl WHERE height
	in (SELECT height FROM usertbl WHERE addr = '경남'); -- 회원 중 경남인 사람의 키와 똑같은 회원만 추출

SELECT * FROM usertbl WHERE height >
	any (SELECT AVG(height) FROM usertbl WHERE addr = '경남'); -- 경남인 회원의 키 평균보다 큰 회원 추출
    
SELECT * FROM usertbl WHERE height >
	any (SELECT MAX(height) FROM usertbl WHERE addr = '경남'); -- 경남인 회원의 키의 최댓값 보다 큰 회원 추출
    
SELECT * FROM usertbl WHERE height >
	any (SELECT MIN(height) FROM usertbl WHERE addr = '경남'); -- 경남인 회원의 키의 최솟값 보다 큰 회원 추출

 

◆ ORDER BY: 정렬 (ASC: 오름차순 - 생략가능, DESC: 내림차순)

-- ORDER BY
-- 회원 테이블에서 가입일 순으로 이름과 가입일 출력
SELECT name 이름, mDate 가입일 FROM usertbl ORDER BY mDate ASC; -- 오름차순 (ASC 생략 가능)
SELECT name 이름, mDate 가입일 FROM usertbl ORDER BY mDate DESC; -- 내림차순

-- 회원 테이블에서 이름, 키, 가입일을 키는 내림차순, 가입일은 오름차순으로 출력
SELECT name 이름, height 키, mDate 가입일 FROM usertbl ORDER BY height DESC, mDate; -- 동일한 값일 경우 앞에 있는 조건이 우선됨

• limit n: n개 만큼 정렬
• offest: 제한적으로 데이터 조회 (limit 10 offset 5 : 6번째에서 15번째까지의 데이터를 제한적으로 조회)

-- limit n: n개 만큼 정렬
SELECT emp_no 사원번호, hire_date 입사일자
	FROM employees
    ORDER BY hire_date
    limit 10;

-- offset: 5, limit: 10
SELECT emp_no 사원번호, hire_date 입사일자
	FROM employees
    ORDER BY hire_date
    limit 5, 10;
    
    
SELECT emp_no 사원번호, hire_date 입사일자
	FROM employees
    ORDER BY hire_date
    limit 10 offset 5;

 

◆ 데이터 복사하기

• CREATE 를 SELECT와 같이 이용하면 데이터 복사 가능

 -- SELECT 문 이용하여 테이블 복사하기
use sqldb;
CREATE TABLE buytbl2 (
	SELECT * FROM buytbl);
    
SELECT * FROM buytbl2;

 

◆ 데이터 상세 정보 보기: DESC

DESC 명령어를 사용하여 상세 정보를 확인할 수 있다.

DESC buytbl;
DESC buytbl2;

▶ 테이블을 복사할 때 데이터는 복사되나, PK나 FK 같은 설정은 복사되지 않는다.

 

◆ GROUP BY

데이터들을 작은 그룹으로 분류하여 소그룹에 대한 항목별로 통계 정보 요청

• HAVING: 그룹절에 사용되는 조건문(GROUP BY 용 WHERE 구문)
• ROLLUP: 소그룹 간의 합계 계산 (GROUP BY로 묶은 각각의 소그룹 합계와 전체 합계 계산)

-- Group BY
SELECT userID, SUM(amount) FROM buytbl GROUP BY userID;
SELECT userID '사용자 아이디', SUM(amount) '구매 수량 합계' FROM buytbl GROUP BY userID;
    
-- Grop BY 조건: Having > 사용자별 총구매금액이 1000이상인 사용자 id, 금액 출력
SELECT userID, SUM(price * amount) '총 구매금액'
	FROM buytbl
	GROUP BY userid
    HAVING SUM(price * amount) >= 1000;
    
-- Grop BY 조건: Having > 사용자별 총구매금액이 1000이상인 사용자 id, 금액 오름차순 출력
SELECT userID, SUM(price * amount) '총구매금액'
	FROM buytbl
	GROUP BY userid
    HAVING SUM(price * amount) >= 1000
    ORDER BY SUM(price * amount);
    
-- GROUP BY에는 alias 사용 가능    
SELECT userID, SUM(price * amount) '총구매금액'
	FROM buytbl
	GROUP BY userid
    HAVING 총구매금액 >= 1000
    ORDER BY 총구매금액;    

-- rollup: 부분합, 총합
SELECT num, groupName, SUM(price * amount) as '비용'
	FROM buytbl
    GROUP BY groupName, num
    WITH ROLLUP;

 

◆ 기타

• DDL: 데이터 정의문
• CRUD: CREATE(생성), READ(조회), UPDATE(수정), DELETE(삭제) 
• UPDATE의 경우, SET 뒤에는 수정할 값 입력

-- DDL에 속하는 create
CREATE TABLE testtbl1 (id INT, username CHAR(3), age INT);
INSERT INTO testtbl1(id, username) values (1, '홍길동');

-- INSERT에서 모든 칼럼에 데이터 입력 시 칼럼명 생략
INSERT INTO testtbl1 values(2, '김자바', 25); -- 값이 없을 땐 , 라도 써야 함 

-- Auto_Increment의 경우 NULL 값으로 주면 DB엔진에서 자동으로 번호 할당

-- 테이블 생성 시, 데이터를 복사하고 구조 따로 생성 가능
CREATE TABLE testtbl2 (id INT, fname VARCHAR(50), lname VARCHAR(50));
INSERT INTO testtbl2
	SELECT emp_no, first_name, last_name
		FROM employees.employees; -- 다른 DB의 TABLE로부터 불러옴
        
CREATE TABLE testtbl3
	(SELECT emp_no, first_name, last_name
		FROM employees.employees); -- INSERT INTO 없이 바로 생성 가능: 단 가져오는 DB의 TABLE명과 동일한 칼럼 사용
        
-- 입력된 칼럼의 데이터를 변경하는 UPDATE
SELECT lname, fname FROM testtbl2 WHERE fname = 'kyoichi';

UPDATE testtbl2 
	SET lname = '없음'
    WHERE fname = 'Kyoichi';

 

◆ DELETE, DROP, TRUNCATE 차이

• DELETE와 TRUNCATE는 구조를 남기고, DROP은 구조를 남기지 않는다.
• DELETE 보다는 TRUNCATE의 삭제 속도가 더 빠르다.
• DELETE 는 특정 데이터만 삭제할 수 있으나, TRUNCATE는 일괄 삭제한다.

-- DELETE: WHERE 구문 생략 시 전부 삭제
DELETE FROM testtbl2 WHERE fname = 'Kyoichi';

-- DELETE, DROP, TRUNCATE 차이
CREATE TABLE bigtbl1 (SELECT * FROM employees.employees);
CREATE TABLE bigtbl2 (SELECT * FROM employees.employees);
CREATE TABLE bigtbl3 (SELECT * FROM employees.employees);

DELETE FROM bigtbl1; -- 2.109 sec 구조는 남아 있음
DROP TABLE bigtbl2; -- 0.016 sec
TRUNCATE TABLE bigtbl3; -- 0.032 sec 구조는 남아 있음

SELECT * FROM bigtbl1;
SELECT * FROM bigtbl2; -- Error: doesn't exist
SELECT * FROM bigtbl3;

 

★ 1175 에러 발생 시, SAFE MODE를 OFF 하면 되는데

• 방법1: WorkBench 'Edit' 메뉴 - Preferences - SQL Editor - Safe Updates 체크 해제
• 방법2: SAFE MODE를 off 해주는 명령어(하단) 입력

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

SET SQL_SAFE_UPDATES = 0; -- safe mode off

 

방법1

▶ 필자의 경우, 체크가 해제되어 있음에도 에러가 발생하여 쿼리문을 입력해 줬더니 바로 해결되었다.

---

다음 내용