생각하는 개발자 Dave

서브쿼리

<사용법>
SELECT 컬럼명1, 컬럼명2, ... FROM 테이블명 WHERE 조건컬럼명
IN (SELECT 조건컬럼명 FROM 서브테이블명 WHERE 조건식)

이름이 Chirst Demos인 사람의 급여 정보

SELECT emp_no, salary, from_date, to_date
FROM salaries
WHERE emp_no IN (SELECT emp_no
        FROM employees
        WHERE first_name = 'Demos'
AND last_name = 'Christ');

2개 이상의 테이블을 JOIN

SELECT *
FROM employees AS e
JOIN salaries AS s
JOIN titles AS t
ON e.emp_no = s.emp_no
AND e.emp_no = t.emp_no;

JOIN

관련 있는 데이터를 가지고 있는 둘 이상의 테이블을 결합하기 위해 사용
 INNER JOIN : 일반적인 JOIN, INNER 는 생략 가능, 두 테이블에서 동일하게 가지고 있는 데이터들을 모아서 하나의 테이블인 것처럼 출력(교집합)
LEFT OUTTER JOIN : OUTTER는 생략 가능, 두 테이블 중 왼쪽에 있는 테이블의 모든 내용을 출력하고, 오른쪽에 있는 테이블의 내용 중 왼쪽 테이블에 해당하는 내용을 출력
RIGHT OUTTER JOIN : OUTTER는 생략 가능, 두 테이블 중 오른쪽에 있는 테이블의 모든 내용을 출력하고, 왼쪽에 있는 테이블의 내용 중 오른쪽 테이블에 해당하는 내용을 출력
CROSS JOIN : 양쪽 테이블의 모든 내용을 출력
SELF JOIN : 왼쪽 테이블 및 오른쪽 테이블을 하나의 테이블로 구성하여 결과를 출력하는 방식

 <사용법>
 SELECT 테이블명1.컬럼명1, 테이블명1.컬럼명2, ...,
테이블명2.컬럼명1, 테이블명2.컬럼명2, ...
FROM 테이블명1

JOIN 테이블명2
ON 테이블명1.동일한데이터컬럼 = 테이블명2.동일한데이터컬럼
AND 조건식

JOIN으로 두 테이블의 모든 컬럼 출력

SELECT * FROM employees
JOIN salaries
ON employees.emp_no = salaries.emp_no;

두 테이블에서 필요한 컬럼만 출력

SELECT employees.emp_no, employees.birth_date,
employees.first_name, employees.last_name, employees.gender, 
employees.hire_date, salaries.salary, salaries.from_date, salaries.to_date
FROM employees
JOIN salaries
ON employees.emp_no = salaries.emp_no;

AS 명령어로 별칭을 사용한 JOIN

SELECT e.emp_no, e.birth_date, e.first_name, e.last_name, 
e.gender, e.hire_date, s.salary, s.from_date, s.to_date
FROM employees AS e
JOIN salaries AS s
ON e.emp_no = s.emp_no
AND e.emp_no = 10009;

문제 1) 사원 테이블(employees)에서 이름(first_name)중 시작 글자가 ma로 시작하는 사람 중 여성인 사람을 모두 검색하세요

SELECT * FROM employees WHERE gender = 'F' AND first_name LIKE 'ma%';

문제 2) 사원 테이블에서 성(last_name) 중 끝 글자가 do로 끝나고 글자가 수가 6자인 사람 중 남성을 모두 검색하세요

SELECT * FROM employees WHERE gender = 'M' AND last_name LIKE '____do';

문제 3) 사원 테이블에서 이름이 'mary', 'maria', 'jouko' 이며, 성별이 여성이면서, 사번이 30000번 보다 빠른 사람을 모두 검색하세요 (IN 사용)

SELECT * FROM employees
WHERE emp_no < 30000 AND gender = 'F' AND first_name IN ('mary', 'maria', 'jouko');

문제 4) 사원 테이블에서 이름 중 시작 글자가 ma인 사람과 jo이며 성별이 여성이 사람을 모두 검색하세요 (OR 사용)

SELECT * FROM employees
WHERE emp_no < 30000 AND gender = 'F' AND (first_name LIKE 'ma___' OR first_name LIKE 'jo%');

문제 5) 생일이 '1962-12-01' ~ 1962-12-15'인 사람 중 남자인 사람만 모두 검색하세요

SELECT * FROM employees WHERE gender = 'M' AND birth_date BETWEEN '1962-12-01' AND '1962-12-15';

문제 6) 입사일이 '1997-10-16' ~ '1997-10-30'인 사람 중 남자이며, 성의 글자수가 5자 이상인 사람만 모두 검색하세요 (BETWEEN 방식, 비교 연산자 방식 둘다 생성)

SELECT * FROM employees 
WHERE gender = 'M' AND hire_date 
BETWEEN '1997-10-16' AND '1997-10-30' AND last_name LIKE '%_____%';


SELECT * FROM employees 
WHERE gender = 'M' AND last_name LIKE '%_____%' 
AND hire_date >= '1997-10-16' AND hire_date <= '1997-10-30';

 BETWEEN

지정한 범위 내의 데이터를 출력할 때 사용, 시작값과 종료값 포함, WHERE 절에서 사용, 범위 값은 숫자, 문자, 날짜로 사용 가능

<사용법 >

WHERE 컬럼명 BETWEEN 시작값 AND 종료값
S

ELECT * FROM employees WHERE hire_date BETWEEN '1999-10-01' AND '1999-10-10';
SELECT * FROM employees WHERE hire_date BETWEEN '1999-10-01' AND '1999-10-10' ORDER BY hire_date ASC;

비교연산자와 AND 연산자를 사용하여 BETWEEN과 동일한 결과를 얻을 수 있음

SELECT * FROM employees 
WHERE hire_date >= '1999-10-01' AND hire_date <= '1999-10-10' ORDER BY hire_date ASC;

문자열로 범위 설정

SELECT * FROM employees WHERE first_name BETWEEN 'maria' AND 'mariz' ORDER BY first_name ASC;

AS  

테이블 및 컬럼명에 별칭을 부여하는 명령어, 테이블과 SELECT에서 사용, 해당 쿼리문에서만 효과가 있음, 컬럼명의 길이가 길어 수정하고자 할 경우, 화면에 출력하고자 하는 컬럼명을 변경할 경우, JOIN 시 테이블명을 변경하고자 할 경우
 <사용법>

SELECT 컬럼명1 AS '별칭1', 컬럼명2 AS '별칭2', ... FROM 테이블명
SELECT 별칭테이블명.컬럼명1, 별칭테이블명.컬럼명2, ... FROM 테이블명 AS '별칭' 
SELECT emp_no AS '사원번호', birth_date AS '생년월일', first_name AS '이름', last_name AS '성', gender AS '성별', hire_date AS '입사일' FROM employees AS a; 

SELECT a.emp_no, a.birth_date, a.first_name, a.last_name, a.gender, a.hire_date FROM employees AS a;

 IN 

검색에 대한 여러개의 데이터를 지정할 수 있는 명령어, 서브쿼리 사용을 위한 명령어, WHERE 절에서 사용, OR 명령어를 사용한 것과 동일한 효과

<사용법>

WHERE 컬럼명 IN (데이터1, 데이터2, ...)
WHERE 컬럼명 IN (SELECT ... )

SELECT * FROM employees WHERE first_name = 'mario';
SELECT * FROM employees WHERE first_name = 'marko';

OR

SELECT * FROM employees WHERE first_name IN ('mario', 'marko');
SELECT * FROM employees WHERE first_name = 'mario' OR first_name = 'marko';

LIKE

SELECT * FROM employees WHERE first_name LIKE 'mar%' OR first_name LIKE '%ko';

WILD CARD

문자열에서 하나 이상의 문자를 대체하는 문자, _, % 가 존재
-- % : 0개 이상의 임의의 문자로 대체됨 
-- _ : 1개의 임의의 문자로 대체됨, 문자의 수가 고정되어 있을 경우 사용

LIKE

검색 패턴을 생성하는 명령어, 와일드카드문자와 함께 사용하여 패턴 생성, WHERE 절에서 사용
<사용법>

WHERE 컬럼명 LIKE '검색패턴'

검색어의 시작부분만 가지고 검색

SELECT * FROM employees WHERE first_name LIKE 'El%';

검색어의 끝부분만 가지고 검색

SELECT * FROM employees WHERE first_name LIKE '%is';

검색어의 중간 부분만 가지고 검색

SELECT * FROM employees WHERE first_name LIKE '%rio%';

검색어의 양끝 부분만 가지고 검색

SELECT * FROM employees WHERE first_name LIKE 'm%o';

검색어의 문자수가 5자인 것을 검색

SELECT * FROM employees WHERE first_name LIKE '_____';

검색어 중 시작 글자가 m이고, 글자 수는 5자인 사람 검색

SELECT * FROM employees WHERE first_name LIKE 'm____';

검색어의 글자수가 최소 5자 이상인 사람 검색

SELECT * FROM employees WHERE first_name LIKE '%_____%';

LIMIT

MySql에서 결과로 출력하는 데이터 수를 제한하는 명령어, WHERE 절에서 사용

<사용법> 

WHERE 조건식 LIMIT 출력수
WHERE LIMIT 출력번호, 출력수

SELECT * FROM employees;
SELECT * FROM employees LIMIT 10;
SELECT * FROM employees LIMIT 10, 10;

MIN() / MAX()

지정한 컬럼의 최소값/최대값을 출력하는 함수, SELECT 절에서 사용

<사용법 >

SELECT MIN(컬럼명) FROM 테이블명

SELECT MIN(birth_date), MAX(hire_date) FROM employees;
SELECT MIN(hire_date), MAX(birth_date) FROM employees;

COUNT() 

지정된 기준과 일치하는 데이터 수를 출력, SELECT 절에서 사용

<사용법> 

SELECT COUNT(컬럼명) FROM 테이블명

SELECT COUNT(emp_no)
FROM employees 
WHERE birth_date < '1953-09-02' AND first_name = 'marko' AND gender = 'F';

AVG()

지정된 컬럼(숫자일 경우에만)의 평균값을 출력, SELECT 절에서 사용

<사용법>

SELECT AVG(컬럼명) FROM 테이블명

SELECT AVG(salary) FROM salaries WHERE emp_no = 10009;

SUM()

지정된 컬럼(숫자일 경우에만)의 총합을 출력, SELECT 절에서 사용

<사용법>

 SELECT SUM(컬럼명) FROM 테이블명

SELECT SUM(salary) AS 'total' FROM salaries WHERE emp_no = 10009;

ORDER BY

지정한 컬럼을 기준으로 데이터를 정렬하여 출력, ASC(오름차순), DESC(내림차순), WHERE 구문 마지막에 입력, 정렬할 컬럼은 여러개를 지정할 수 있음, 3개 이상 사용 시 원하는 형태로 정렬되지 않을 수 있음

<사용법 > 
SELECT 컬럼명1, 컬럼명2, ... FROM 테이블명 WHERE 검색조건 ORDER BY 컬럼명 ASC|DESC

INSERT

데이터 베이스에 데이터를 추가하는 명령어, 데이터 입력 시 컬럼의 순서는 상관없음(사용자가 입력한 컬럼 순서에 맞는 데이터만 입력하면 됨), AI/Default로 설정된 데이터는 입력 시 컬럼을 생략해도 됨, NotNull로 설정된 부분만 정확하게 데이터를 입력하면 됨, Unique 로 설정된 키는 중복되는 데이터를 입력하면 안됨
사용법 :
INSERT INTO 테이블명 (컬럼명1, 컬럼명2, ...) VALUES (데이터1, 데이터2, ...);
INSERT INTO 테이블명 (컬럼명1, 컬럼명2, ...) VALUES (데이터1-1, 데이터1-2, ...), (데이터2-1, 데이터2-2, ...), ...

INSERT INTO member (idx, mem_name, mem_phone, mem_email, mem_addr, mem_gender)
VALUES 
(1, '아이유', '01012345678', 'iu@bitc.co.kr', '부산광역시 부산진구 중앙대로', '여자');
INSERT INTO member(idx, mem_name, mem_phone, mem_email, mem_addr, mem_gender)
VALUES
(2, '유인나', '01098765432', 'inna@bitc.co.kr', '부산진구 중앙대로', '여자'),
(3, '유재석', '01045672158', 'yjs@bitc.co.kr', '부산진구 전포대로', '남자'),
(4, '유아인', '01023657854', 'you@bitc.co.kr', '부산', '남자');

UPDATE

데이터 베이스에 저장되어 있는 데이터를 수정하는 명령어
주의사항 : 조건식을 부분을 생략하게 되면 해당 테이블의 모든 데이터를 수정
<사용법> : UPDATE 테이블명 SET 컬럼명1 = 데이터1, 컬럼명2 = 데이터2, ... WHERE 조건식

UPDATE member SET mem_email = 'ybj@bitc.co.kr', mem_phone = '0109873563' WHERE idx = 5;
UPDATE member SET mem_addr = '부산 어딘가..';

DELETE

데이터 베이스에 저장되어 있는 데이터를 삭제하는 명령어
주의사항 : 조건식을 생략할 경우 해당 테이블의 모든 데이터를 삭제
<사용법>  DELETE FROM 테이블명 WHERE 조건식

DELETE FROM member WHERE idx = 6;
DELETE FROM member;

SELECT * FROM employees;
SELECT * FROM employees ORDER BY first_name ASC;
SELECT * FROM employees ORDER BY first_name DESC, last_name ASC;
SELECT * FROM employees ORDER BY first_name DESC, last_name ASC, hire_date DESC;

SELECT 

데이터를 조회하기 위한 명령어, WHERE 구문을 함께 사용하여 조회 시 필요한 조건을 추가하여 데이터를 조회함
<사용법>
SELECT 컬럼명1, 컬럼명2, ... FROM 테이블명;
SELECT 컬럼명1, 컬럼명2, ... FROM 테이블명 WHERE 조건;

SELECT * FROM employees;
SELECT emp_no, first_name, last_name, gender FROM employees;

SELECT * FROM salaries;
SELECT * FROM titles;

DISTINCT

SELECT 구문의 컬럼명과 함께 사용하는 명령어로 중복되는 데이터를 제거

SELECT DISTINCT title FROM titles;

WHERE 

데이터베이스에서 데이터를 조회할 때 조건을 추가하는 명령어, 조건에 만족하는 데이터만 출력함, SELECT/UPDATE/DELETE 에서 사용됨
<사용법> 
SELECT 컬럼명1, 컬럼명2, ... FROM 테이블명 WHERE 검색조건
UPDATE 테이블명 SET 컬럼명1, 컬럼명2, ... WHERE 검색조건-- DELETE FROM 테이블명 WHERE 검색조건

SELECT * FROM employees WHERE first_name = 'mario';
SELECT * FROM employees WHERE gender = 'F';

AND/OR : WHERE 구문을 사용하여 검색한 조건에 추가 조건을 사용하고자 할 경우 사용, AND는 조건이 추가될 때마다 검색 정확도가 올라감, OR는 조건이 추가될 때마다 검색되는 데이터의 양이 늘어남, WHERE 구문에 추가하여 사용함
SELECT 컬럼명1, 컬럼명2, ... FROM 테이블명 WHERE 조건1 AND 조건2, AND 조건3, ...
SELECT 컬럼명1, 컬럼명2, ... FROM 테이블명 WHERE 조건1 OR 조건2, OR 조건3, ...

SELECT * FROM employees WHERE emp_no < 20000;
SELECT * FROM employees WHERE emp_no < 20000 AND gender = 'M';
SELECT * FROM employees WHERE emp_no < 20000 AND gender = 'M' AND first_name = 'mario';
SELECT * FROM employees WHERE emp_no = 10100;
SELECT * FROM employees WHERE emp_no = 10100 OR emp_no = 10101;
SELECT * FROM employees WHERE emp_no = 10100 OR emp_no = 10101 OR first_name = 'luigi';

NOT

검색 조건에 부합하지 않는 데이터를 출력
<사용법 >
SELECT 컬럼명1, 컬럼명2, ... FROM 테이블명 WHERE NOT 검색조건;

SELECT * FROM employees WHERE gender = 'M';
SELECT * FROM employees WHERE NOT gender = 'M';

DDL

• 데이터 정의어, 데이터 베이스 관리자가 사용, 데이터 베이스를 설계 및 생성하는 언어, create/drop/alter

DML

• 데이터 조작어, 일반 사용자/프로그램 개발자가 사용, 데이터 베이스에 실제 데이터를 추가, 삭제, 수정, 조회하기 위해 사용하는 언어, select, insert, update, delete

DCL

• 데이터 관리어, 데이터 베이스 관리자가 사용, 데이터 베이스 사용 권한 및 사용자 권한 제어하기 위해 사용하는 언어, grant/revoke

•  mysql 접속
  
  mysql -u 계정명 -p   (기본 로그인)
  mysql -u 계정명 -h 서버주소 -p   (다른 서버로 접속)
  mysql -u 계정명 -h 서버주소 -P 포트번호 -p   (다른 서버에 다른 포트로 접속)

• database 목록 확인
  show databases;

• data table 목록 확인
  show tables;

• 지정한 database 사용
  use 데이터베이스명;
  
   localhost : 네트워크 상에서 자기 자신을 뜻하는 DNS주소(ip 주소로 127.0.0.1)
  % : mysql의 계정정보에서 host 부분에 %로 표시되어 있으면 모든 곳에서 접속이 가능
  
  
• 사용자 생성
  create user '계정명';
  create user '계정명'@'접속주소';
  create user '계정명'@'접속주소' identified by '비밀번호';
  
  
•  사용자 삭제
  drop user '계정명'@'접속주소';
  
  
• 데이터 베이스 추가
  create database 데이터베이스명;
  create database 데이터베이스명 default character set utf8;

• 문자셋 : 컴퓨터에서 문자를 표시하기 위한 코드표, 현재는 유니코드인 utf8 방식이 표준으로 사용되고 있으나 예전 버전에는 문자셋이 기본이었기 때문에 utf8로 설정이 필요했음
  
  
• 데이터 베이스 삭제
  drop database 데이터베이스명;
  
  
• 데이터 베이스 사용 권한 확인
  show grants for '계정명'@'접속주소';
  
  
• 데이터 베이스 사용 권한 설정
  grant all privileges on 데이터베이스명.테이블명 to '계정명'@'접속주소';
  
  
• 데이터 베이스 사용 권한 삭제
  revoke all on 데이터베이스명.테이블명 from '계정명'@'접속주소';
  
  
• 명령어 즉시 적용
  flush privileges;
  
  
• 비밀번호 변경
  alter user '계정명'@'접속주소' identified with mysql_native_password by '비밀번호';