SQL Map

haribo·2021년 3월 11일
1

MySQL

목록 보기
1/1

정의

SQL(Structured Query Language) - 구조화된 질의 언어

  • 데이터베이스에 저장된 데이터를 조회, 입력, 수정, 삭제하는 등의 조작이나 테이블을 비롯한 다양한 객체(시퀀스, 인덱스 등)를 생성 및 제어하는 언어
  • DDL : 데이터 정의어 (Data Definition Language), 데이터베이스 관리자나 응용 프로그래머가 데이터베이스의 논리적 구조를 정의하기 위한 언어 (생성 : create / 구조명시 : alter)
  • DML : 데이터 조작어 (Data Manipulation Language), 데이터베이스에 저장된 데이터를 조작하기 위해 사용하는 언어. 검색, 추가, 삭제, 갱신 작업을 수행한다. (select, insert, update, delete)
  • DCL : 데이터 제어어(Data Control Language), 데이터에 대한 접근 권한 부여 등 데이터베이스 시스템 및 트랜젝션을 관리하기 위한 언어

기본 명령어


현재 시스템에서 데이터베이스 목록 보기

show databases;

사용할 데이터베이스 선택하기

use 데이터베이스이름;

현재 데이터베이스에서 테이블 목록 보기

show tables;

특정 테이블의 구조 확인하기

desc 테이블이름;

데이터 조회하기


구문 구성

select [distinct] *[컬럼이름 [[as] `별칭`] from 테이블이름
[where 검색조건표현식]
[order by 컬럼이름 [ASC|DESC]]
[limit 조회시작위치, 조회할 데이터 수]; // 페이징
  • distinct : 중복제거 옵션
  • as (혹은 공백) : 컬럼이름에 별칭 적용
  • where : 검색조건을 위한 식을 표현한다.
  • order by : 조회된 데이터를 정렬한다. (ASC=순차정렬, DESC=역순정렬)
  • limit : 부분조회 기능

where절 표현식의 기본 연산자

비교 연산자

=, !=, <, <=, >, >=

논리 연산자

AND, OR, NOT

SQL연산자


구간검색

컬럼명 between A and B

컬럼에 저장되어 있는 데이터가 A에서 B사이인 데이터를 조회한다.

  • 예제 코드

다중검색

컬럼명 in (A, B, C, D)

컬럼에 저장되어 있는 데이터가 A, B, C, D중 하나의 일치하는 것들을 조회한다.

  • 예제 코드

    mysql> SELECT name, grade, deptno FROM student WHERE deptno IN (102, 201);
    +--------+-------+--------+
    | name   | grade | deptno |
    +--------+-------+--------+
    | 김진영 |     2 |    102 |
    | 오유석 |     4 |    102 |
    | 하나리 |     1 |    102 |
    | 윤진욱 |     3 |    102 |
    | 이동훈 |     1 |    201 |
    | 박동진 |     1 |    201 |
    | 김진경 |     2 |    201 |
    | 조명훈 |     1 |    201 |
    +--------+-------+--------+
    8 rows in set (0.00 sec)
  • OR로도 가능하다.

키워드검색

컬럼명 like '%키워드%'

컬럼에 저장되어 있는 데이터가 키워드를 포함하는 데이터들을 조회한다.

  • 예제코드

Null검색

컬럼명 is [not] null

null을 포함하거나 그렇지 않은 데이터들을 조회한다.

  • 예제코드

함수


저장되어 있는 데이터를 가공하기 위하여 제공되는 기능

문자열 관련 함수


date_add의 단위

  • YEAR
  • MONTH
  • DAY
  • HOUR
  • MINUET
  • SECOND

date_add의 예시

SELECT date_add(now(), INTERVAL 1 YEAR);
SELECT date_add(now(), INTERVAL -3 MONTH);
SELECT date_add(birthdate, INTERVAL 1 YEAR) FROM student;

date_format의 예시

SELECT date_format(now(), '%y/%m/%d/ %H:%i:%s');
SELECT date_format(birthdate, '%y/%m/%d %H:%i:%s') FROM student;

날짜 형식 지정은 시스템의 국가 설정에 따라 기본 출력 형태가 다르기 때문에 국가에 상관없이 고정된 형태를 제공하기 위해서 사용


집계함수

그룹조회

SELECT 컬럼, 집계함수(컬럼), FROM 테이블이름
[WHERE 표현식]
[GROUP BY 컬럼1, 컬럼2 ...]
[HAVING 집계함수(컬럼)에 대한 표현식]
[ORDER BY 컬럼 ...]
  • WHERE절을 사용하여 검색된 결과에 대해서 그룹핑 가능
  • GROUP BY절에 사용된 컬럼은 집계함수 없이 SELECT에 명시 가능 혹은 생략 가능
  • GROUP BY절에 사용되지 않은 컬럼은 반드시 집계함수를 사용해서 지정
  • 집계함수의 결과값에 대한 조건 검색을 할 경우 반드시 HAVING절을 사용해야 한다.

Join


정의

두 개 이상의 테이블을 결합하여 필요한 데이터를 조회하는 기능

ex) 교수이름과 학과이름을 같이 불러오고 싶을때 join을 쓴다.

Join의 종류

  • 카티션 곱(Cartesian product, cross join) - 모든 경우의 수를 불러오므로 실무에서 쓰면 큰일남
  • EQUI JOIN : from 후에 join할 테이블명을 다 쓰는것. WHERE절로 조건을 부여한다. (s.deptno = d.deptno) 추가 검색조건은 AND 연산자를 이용한다.
SELECT table1.column, table2.colunmn
FROM table1, table2
WHERE table1.column = table2.column;
  • INNER JOIN : from 후에 기준이 될 테이블명 하나를 쓰고, join 조건은 ON 뒤에 쓴다. 그 밖의 조건은 WHERE을 사용한다. (EQUI JOIN에서 테이블 이름을 구분하는 콤마를 INNER JOIN이라는 키워드로 변경하고 WHERE은 ON으로 변경한다.) 추가 검색조건은 WHERE연산자를 이용한다.
SELECT table1.column, table2.column
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
  • OUTER JOIN : EQUI JOIN과 INNER JOIN은 표현방법만 다를뿐 모두 교집합을 가져오는 방법이다. OUTER JOIN은 조건에 부합하지 않는 행들 까지도 포함시켜 결합하는 것을 의미한다. FULL OUTER JOIN은 자주 사용하지 않는다. 계속 FULL을 쓸거면 차라리 테이블을 합치는게 더 합리적이다. 기준이 되는 테이블에 JOIN한 칼럼에 값이 없으면 NULL까지 출력된다.

SubQurey


정의

  • SQL안의 SQL
  • 하나의 SQL 명령문의 처리 결과를 다른 SQL 명령문에 전달하기 위해 두 개 이상의 SQL문을 하나의 SQL문으로 연결한 형태 (INNER CLASS 생각난다.)
  • 검색 조건을 다른 결과 집합에서 찾기 위해서 사용한다.
  • 서브쿼리를 포함한 SQL 문을 '메인 쿼리' 라고 한다.
  • 서브쿼리는 SELECT문의 시작과 끝에 () 를 묶어서 메인쿼리와 구분한다.

종류

  • 단일행 서브쿼리 : 단 하나의 검색결과만을 반환하는 형태, 비교연산자가 사용된다.
SELECT name, position FROM professor
WHERE position = ( SELECT position FROM professor WHERE name = '전은지');
// 전은지 교수와 같은 직급의 교수에 대한 이름, 직급을 조회
  • 다중행 서브쿼리 : 하나 이상의 검색결과를 반환하는 형태, IN연산자가 사용된다.
SELECT studno, grade, name FROM student WHERE profno IN (
			SELECT profno FROM professor WHERE sal > 300 ) ;
// 급여를 300만원 초과로 받는 교수에게 지도받는 학생들의 학번, 학년, 이름을 조회

입력/수정/삭제


INSERT

테이블에 데이터를 추가하기 위한 구문

<컬럼값 x>

INSERT INTO <테이블이름> VALUES (1,2, ... , 값n);
  • 숫자형식의 데이터는 홑따옴표를 사용하지 않는다.

  • 그 밖의 형식 (문자열, 날짜) 값은 홑따옴표로 감싼다.

  • 값을 나열할때는 테이블 구조에서 정의하고 있는 컬럼의 순서에 맞게 명시하여야한다.

    (귀찮겠다 실수도 자주 발생할듯)

    → DESC 테이블이름; 의 구문으로 테이블 정의를 확인할 수 있다.

<컬럼값 o>

INSERT INTO <테이블이름> (컬럼1, 컬럼2, ..., 컬럼n) 
VALUES (1,2, ... , 값n);
  • 나열되는 컬럼의 이름이 테이블 구조에서 정의하고 있는 컬럼순서와 일치할 필요는 없다.
  • 테이블 이름뒤에 명시되는 컬럼의 순서나 갯수는 VALUES 뒤에 나열되는 값과 일치하여야한다.

NULL 값의 입력 방법

  • DESC 구문을 사용하여 테이블 구조를 조회했을때, NULL 항목에 YES라고 표시되는 컬럼은 NULL값을 허용한다. NO라고 된 값은 필수값이다.
  • 암묵적인 NULL 데이터 입력법 : 컬럼의 이름과 값을 생략한다.
  • 명시적인 NULL 데이터 입력법 : 컬럼값에 NULL 을 사용한다.

날짜 데이터 입력방법

  • YYYY-MM-DD HH:MI:SS 혹은 YYYY-MM-DD 형식에 따른 날짜 데이터를 입력한다.
  • 자동으로 현재 날짜로 입력하려면 now()를 사용하면 된다.

Primary Key - 중복데이터 방지 방법

  • 데이터가 중복되면 디스크의 낭비다.
  • Primary Key(기본키) : 테이블의 각 행을 고유하게 식별하는 값을 가진 열(또는 열 조합)
  • 기본키는 숫자로 관리된다.
  • 일련번호 계산 하기 귀찮으므로 AUTO_INCREMENT옵션을 사용하여 데이터 저장시 시스템에서 자동으로 일련번호를 생성할 수 있다. (기본키에만 붙일 수 있음), 기존값에서 가장 큰 값보다 1 큰값이 저장된다.
  • 기본키 제약조건 : 중복된 기본키는 선언할 수 없다. 따라서 중복데이터를 방지할 수 있다.

UPDATE & DELETE


UPDATE <테이블이름> SET 컬럼1=1, 컬럼2=2, ..., 컬럼n=값n
[WHERE 검색조건]; // 필수다. 없으면 모든 값에 업데이트가 되어버린다... 
DELETE FROM <테이블이름> [WHERE 검색조건];

함수의 사용


INSERT - 저장할 값을 함수를 사용하여 명시한다.

INSERT INTO <테이블이름> VALUES (함수이름(), 함수이름()...);

UPDATE - 수정될 값에 함수를 적용할 수 있다.

UPDATE <테이블이름> SET 컬럼1= 함수이름(1), 컬럼2=함수이름(2), ..., 
컬럼n=함수이름(값n) [WHERE 함수가 적용된 검색조건]; 

DELETE - 삭제될 대상을 지정하는 WHERE 절에서 사용할 수 있다.

DELETE FROM <테이블이름> [WHERE 함수가 적용된 검색조건];

CREATE


CREATE DATABASE `데이터베이스이름` [default charset `utf8`];
  • DB이름은 백쿼테이션으로 써준다. (8버전에선 안넣어줘도 가능)
  • 동일한 이름의 데이터베이스는 생성할 수 없다.
  • 기본 캐릭터셋은 utf8(한글사용), euckr(csv파일) 등의 값을 지정할 수 있다.
CREATE TABLE `테이블이름` (
	`컬럼이름` 데이터타입 [NOT NULL | NULL] [AUTO_INCREMENT],
	`컬럼이름` 데이터타입 [NOT NULL | NULL] [AUTO_INCREMENT],
	`컬럼이름` 데이터타입 [NOT NULL | NULL] [AUTO_INCREMENT], ...
	[, PRIMARY KEY (`기본키컬럼이름`)] // 컬럼 바로 옆에다가 입력할수도 있다.
) [ENGINE = InnoDB] [DEFAULT CHARSET = `utf8`];
  • 괄호 안에 테이블이 포함할 컬럼의 이름과 데이터 타입 쌍을 콤마로 구분하여 명시한다.
  • InnoDB : Oracle이 MySQL을 인수한 후에 추가한 저장형식. 참조키나 프로시저등의 고급 기능 사용 가능

데이터타입

DROP

DROP DATABASE `데이터베이스이름`;
  • 존재하지 않는 DB이름을 넣으면 에러 발생(당연)
  • 삭제한 데이터베이스는 복구할 수 없다. (서버는 그런거 없다.)

ALTER

테이블 구조를 변경하는 구문이다.

ALTER TABLE `테이블이름` 명령어;

명령어의 종류

  • RENAME : 테이블의 이름을 변경한다.
  • ADD : 컬럼이나 제약조건을 추가한다.
  • CHANGE : 컬럼을 수정한다.
  • DROP : 컬럼이나 제약조건을 삭제한다.

기본키와 ALTER

설정된 기본키 속성을 해제하려면 우선 AUTO_INCREMENT 속성을 제거해야한다.

ALTER TABLE `테이블명` CHANGE `컬럼A` `컬럼A` INT NOT NULL; 
// AUTO_INCREMENT속성 언급을 안함으로써 제거했다. (컬럼에 종속)

DROP PRIMARY KEY를 사용하여 기본키를 해제한다.

ALTER TABLE `테이블명` DROP PRIMARY KEY;
// 기본키는 테이블에 종속되어 있으므로 상위 개념인 DROP을 쓴다.

FOREIGN KEY(참조키 OR 외래키)

  • 테이블 A의 컬럼 A에 저장될 데이터가 반드시 테이블 B에 저장되어 있는 값중의 하나여야 할 경우, "테이블 A는 테이블 B를 참조한다" 라고 하며 컬럼 A를 테이블 B에 대한 참조키라고 한다.
  • 교수 테이블의 학과번호를 생각하면 쉽다. 학과번호는 학과테이블에서 온 것이다. 없는 학과번호를 교수로 만들 수 없다.
  • 테이블 구조에서 MUL로 표시된다.

<설정방법>

CREATE TABLE `테이블이름` (
	`컬럼이름` 데이터타입 [NOT NULL | NULL] [AUTO_INCREMENT],
	`컬럼이름` 데이터타입 [NOT NULL | NULL] [AUTO_INCREMENT],
	`컬럼이름` 데이터타입 [NOT NULL | NULL] [AUTO_INCREMENT], ...
	[, PRIMARY KEY (`기본키컬럼이름`)]
	[, FOREIGN KEY (`참조키컬럼이름`)
		 REFERENCES `대상테이블명` (`대상테이블의 컬럼명`) ]
) [ENGINE = InnoDB] [DEFAULT CHARSET = `utf8`];
  • 다른 테이블의 참조를 받고 있는 데이터(학과번호)는 참조하고 있는 데이터(교수테이블의 레코드)가 삭제되기 전까지는 먼저 삭제될 수 없다.
  • 다른 테이블의 데이터를 참조하는 컬럼은(교수테이블의 학과번호 컬럼)은 해당 컬럼의 데이터(학과 테이블의 학과번호) 중 하나를 저장해야만 한다.

<제한>

  • INSERT : 참조되는 컬럼의 값에 지정되지 않은 값을 INSERT할 수 없다. (없는 학과번호로 교수를 추가할 수 없다.)
  • DELETE : 참조를 받고 있는 데이터(학과번호)는 자신을 참조하는 대상(교수테이블의 참조중인 레코드값)이 삭제되기 전에는 먼저 삭제될 수 없다.

DB 백업 및 복구

  • 백업하기 : mysqldump -u계정아이디 -p 백업할DB이름 > 백업파일경로 (미리 디렉토리 생성 필수)
  • 복구하기 : mysql -u계정아이디 -p 복구할DB이름 < 백업파일경로 (미리 데이터베이스 만들어두기)
profile
그림 그리는 백엔드 개발자

0개의 댓글