sql정리 (mysql기준)

박상은·2021년 8월 30일
sql
0

🗃️ database 🗃️

목록 보기
2/2

1. 용어

1.1 DBMS (DataBase Management System)

데이터 베이스 관리 시스템
데이터를 생성, 읽기, 수정, 삭제하는 행위를 처리하는 시스템

1.2 RDBMS (Relational DataBase Management System)

관계형 데이터 베이스 관리 시스템
2차원 형태의 테이블 구조에 데이터를 저장 ( 행과 열을 이용 )
테이블간의 관계가 존재하고, 그 관계에 따라서 여러 테이블로 분리해서 공간을 절약함

1.3 primary key (기본키)

데이터베이스의 무결성을 확보할 때 사용
예를 들면 이름, 나이만 저장하는 테이블이 있다고 가정할 때,
세상에는 이름과 나이가 같은 사람이 분명히 존재할 수 있고, 이 두사람을 구별하기 위해 존재하는 key값을 가지는 컬럼을
key column즉, primary key라고 하고 이렇게 key값을 주는 것을 데이터베이스의 무결성을 확보했다고 함

간단히 말해서 한 테이블에서 유일한 식별자 역할을 하는 값을 primary key라고 함
현 포스트에서는 _id를 primary key로 사용함

1.4 foregin key (참조키)

다른 테이블의 기본키(primary key)와 연결되는 키를 의미함
유저테이블에 _id가 있고, 게시글테이블에 _id, userId가 있고, post.userId를 통해서 user를 찾는다면
userId를 foregin key라고 함 ( 유저테이블의 기본키와 연결된 키 )

1.5 SQL (Structured Query Language)

구조화된 질의언어 즉, 이미 정해진 특정 구조에 맞춰서 질문을 하는 언어임
집합적 언어 즉, 데이터처리를 한번에 여러개 할 수 있다는 의미

1.6 테이블 (table)

데이터를 저장하는 2차원 배열

1.7 행 (row)

세로

1.8 열 (column)

가로

1.9 DDL (Data Definition Language)

데이터 정의어
테이블 외에 다른 데이터베이스 객체들을 처리하는데 사용하는 SQL을 의미
CREATE, DROP, ALTER 등등

1.10 DML (Data Manipulation Language)

데이터 조작어
테이블내부의 데이터를 처리하는데 사용하는 SQL을 의미
INSERT, UPDATE, DELETE, SELECT 등등

1.11 TCL (Transaction Control Language)

트랜잭션 제어어
트랜잭션을 처리하는 SQL을 의미 ( 데이터 처리 되돌리기 기능 )
COMMIT, ROLLBACK 등등

1.12 DCL (Data Control Language)

데이터 제어어
객체에 대한 권한을 할당하거나 회수하는 SQL을 의미 ( 유저 권한부여 등의 기능 )
GRANT, REVOKE 등등

2. 스키마 조작 ( DDL )

2.1 제약조건 ( Contraints )

제약 조건 이름을 반드시 지정할 필요는 없음
1. PRIMARY KEY
2. FOREIGN KEY
3. UNIQUE
4. NOT NULL
5. CHECK

2.1.1 제약 조건 지정 예시

  • FK 제약 조건 옵션
    1. RESTRICT: 삭제 불가능
    2. NO ACTION: RESTRICT와 동일
    3. CASCADE: 같이 삭제
    4. SET NULL: 해당 키만 삭제하고 외래키 NULL 부여
// 명시적으로 제약 조건 지정 방법
[CONSTRAINT <조건명>] PRIMARY KEY(컬럼명)
[CONSTRAINT <조건명>] FOREIGN KEY(컬럼명) REFERENCES <참조테이블명>(참조컬럼명) [ON DELETE <옵션>] [ON UPDATE <옵션>]
[CONSTRAINT <조건명>] UNIQUE(컬럼명)
[CONSTRAINT <조건명>] NOT NULL(컬럼명)	// 애는 사용할 열(column)에 직접 사용하는 방식만 가능함

CREATE TABLE test1 (
    // 명시적으로 NOT NULL 제약조건 지정
    id number CONSTRAINT test1_id_notnull NOT NULL,
    
    // 명시적으로 primary key설정
    constraint test1_pk primary key(id)
);

CREATE TABLE test2 (
    id number not null,
    test1Id number,
    
    constraint test2_pk primary key(id),
    constraint test2_test1_fk FOREIGN KEY(test1Id) REFERENCES test1(id)
);

2.2 테이블 생성

CREATE TABLE <table-name>(
  <column-name> <data-type> [NOT] NULL [PRIMARY KEY] [AUTO_INCREMENT],
  <column-name> <data-type> [NOT] NULL,
  ...
  [[CONSTRAINT <제약조건명>] PRIMARY KEY(<column-name>, ...)],
  [[CONSTRAINT <제약조건명>] FOREIGN KEY(<column-name>) REFERENCES <참조할테이블명>(<참조할컬럼명>)]
)

CREATE TABLE user(
  _id INT NOT NULL PRIMARY KEY auto_increment,
  nickname VARCAHR(20) NULL,
);

2.3 테이블 변경

ALTER를 이용해서 컬럼 추가, 수정, 삭제 및 제약조건 추가, 삭제 등의 기능을 사용 가능함

2.3.1 컬럼 추가

ALTER TABLE <테이블명> ADD (<컬럼명> <컬럼자료형>);
ALTER TABLE user ADD (email varchar(20));

2.3.2 컬럼 삭제

ALTER TABLE <테이블명> DROP COLUMN <컬럼명>;
ALTER TABLE user DROP COLUMN email;

2.3.3 컬럼명 변경

ALTER TABLE <테이블명> RENAME COLUMN <현재이름> TO <변경할이름>;
ALTER TABLE user RENAME COLUMN name TO nickname;

2.3.4 컬럼 변경

  • 주의사항: 기존의 값에 영향을 미치는 변경은 불가능함
    1. 데이터의 타입 변경시 주의
    2. 컬럼의 크기 축소시 주의
    3. DEFAULT값 설정시 이후 추가 및 수정하는 컬럼에만 적용
    4. NOT NULL 제약 조건 추가시 주의
ALTER TABLE <테이블명> MODIFY (<컬럼명> <변경사항>);
ALTER TABLE user MODIFY (nickname NULL);

2.3.5 제약 조건 추가

ALTER TABLE <테이블명> ADD CONSTRAINT <제약조건명> <제약조건 정의>;
ALTER TABLE user ADD CONSTRAINT user_pk PRIMARY KEY(id);

2.3.6 제약 조건 삭제

ALTER TABLE <테이블명> DROP CONSTRAINT <제약조건명>;
ALTER TABLE user DROP CONSTRAINT user_pk;

3. 데이터 조작 ( DML )

3.1 데이터 추가

INSERT INTO <table-name> [(<cloumn-name>)] VALUES (<column-value>);
INSERT INTO user (_id, nickname) VALUES (1, 'apple');

3.2 데이터 삭제

DELETE [FROM] <table-name> [WHERE <condition>];
DELETE FROM user WHERE _id = 1;

3.3 데이터 조회

SELECT 조회할컬럼 FROM 조회할테이블 [WHERE 조건] [ORDER BY 정렬기준컬럼 정렬방법];
SELECT nickname FROM user ORDER BY nickname asc;

3.4 데이터 수정

UPDATE <table-name> SET 컬럼1 = 수정값1 [, 컬럼2 = 수정값2 ...] [WHERE 조건];
UPDATE user SET nickname = 'change' WHERE _id = 1;

3.5 조건

3.5.1 WHERE

  1. =, !=, >, < >=, <=
  2. AND : 모두 포함
  3. OR : 하나라도 포함
  4. LIKE : 모든것을 의미
  5. IN : 포함하는지
  6. BETWEEN : 사이에 존재하는지

3.5.2 ORDER BY

  • ASC : 오름차순
  • DESC : 내림차순
ORDER BY col1 desc, col2 asc;	// col1기준 내림차순정렬후, 그 결과에 col2기준 오름차순정렬
ORDER BY 1, 2, 3;	// ??

4. 함수

4.1 숫자형 함수

4.1.1 ABS(n)

n의 절대값

4.1.2 CEIL(n)

n과 같거나 제일 큰 정수 반환 (올림)

4.1.3 EXP(n)

e의 n승 반환

4.1.4 FLOOR(n)

n과 같거나 작은 최대 정수 반환 (내림)

4.1.5 LN(n)

n의 자연로그 반환

4.1.6 LOG

4.1.7 MOD(n1, n2)

n1을 n2로 나눈 나머지 반환

4.1.8 POWER(n1, n2)

n1의 n2승 반환

4.1.9 ROUND(n, i)

n을 소수점 i+1번째 자리에서 반올림한 값을 반환 (반올림)
정수자리에서도 반올림 가능하며, 생략시 0임 즉, 소수점첫째자리 반올림

4.1.10 SIGN(n)

4.1.11 SORT(n)

n의 제곱근 반환 (루트)

4.1.12 TRUNCATE(n, i)

n을 소수점 i번째 자리 이후 제거

4.2 문자형 함수

4.2.1 CONCAT(str1, str2, ...)

str? 들을 모두 합쳐서 반환

4.2.2 LOWER(str)

모두 소문자로

4.2.3 UPPER(str)

모두 대문자로

4.2.4 LPAD(str, n, char)

str을 n길이만큼 왼쪽부터 char로 채워서 반환
SELECT LPAD('THE', 5, '*') : **THE
SELECT RPAD('THE', 5, '*') : THE**

4.2.5 SUBSTR(str, n1, n2)

str을 n1째자리부터 n2개 짤라서 반환 ( n2생략시 n1부터 끝까지 자름 )
SELECT SUBSTR('ABCDEFG', 3, 2); : CD
단, n1이 음수면 오른쪽끝에서부터 계산하고, n2가 음수면 NULL을 반환

4.2.6 TRIM(str)

str의 좌우공백제거후 반환
( LTRIM, RTRIM도 존재함 )

4.2.7 ASCII(char)

char의 아스키코드값 반환

4.2.8 CHR(ascii)

ascii의 문자값 반환

4.2.9 INSTR(str1, str2)

str1에서 str2가 시작하는 자리수 반환
없으면 0반환

4.2.10 LENGTH(str)

str의 길이 반환

4.3 날짜형 함수

4.3.1 NOW()

현재 시간 반환 (년-월-일 시:분:초)

4.3.2 LAST_DAY()

현재 날짜의 가장 마지막일 반환 (년-월-일)

4.4 각종 유용한 함수들

4.4.1 COALESCE(expr1, expr2, ...)

expr? 들중에서 가장 먼저 NULL이 아닌 값을 반환

4.4.2 NULLIF(expr1 ,expr2)

두 값이 같으면 null, 아니면 expr1반환

5. GROUP BY

집계될 결과를 그룹으로 묶어서 보여줌

  • HAVING은 GROUP BY의 조건절임
SELECT _id
	,gender
	,name
	,age
    from user
    GROUP BY gender
    HAVING age BETWEEN 20 and 30
    ORDER BY name;

user테이블에 _id, gender, name, age컬럼을 보여주는데 ( 이 보여줄걸 결과값이라고 가정하고 )
결과값을 gender기준으로 묶고, age가 20~30인 유저를 name으로 정렬해서 보여주라는 의미

마무리

계속 추가할 예정

참고한 사이트

더북 누구나 쉽게 sql
누구나 쉽게 sql 사용자료

profile
박상은
...
이전 포스트

🗄️ mysql 🗄️

0개의 댓글