[ZB] SQL - 기초

porii·2024년 9월 6일

ZB sql

[edu] zerobase

목록 보기

1/28

1. Database

Database
: 여러 사람이 공유하여 사용할 목적으로 체계화
→ 통합, 관리하는 데이터 집합체
DBMS | Database Managemnet System
: 사용자 요구에 따라 정보 생성, 데이터베이스 관리해주는 소프트웨어
RDB | Relational Database
: 서로 관계가 있는 데이터 테이블을 모아둔 데이터 저장공간
SQL | Structured Query Language
: DB에서 데이터를 정의, 조작, 제어하기 위해 사용하는 언어
- DDL | Data Definition Language 데이터 정의 언어
  - CREATE, ALTER, DROP
- DML | Data Manipulation language 데이터 조작 언어
  - INSERT, UPDATE, DELETE, SELECT
- DCL | Data Control Language 데이터 제어 언어
  - GRANT, REVOKE, COMMIT, ROLLBACK

새로고침 FLUSH PRIVILEGES;

DB 관리

# DB 확인
   SHOW DATABASES;

# DB 생성
   CREATE DATABASE dbname;

# DB 사용
   USE dbname;

# DB 삭제
   DROP DATABASE dbname;

USER 관리

먼저 DB로 이동
```
USE database명
```

User 조회

# 먼저 DB로 이동
USE database명

#1. User 조회
SELECT host, user FROM user;

User 생성

현재 PC에서만 접속 가능한 사용자

CREATE USER ‘username’@’localhost’ idenified by ‘password’;

# ex.
CREATE USER ‘noma’@’localhost’ identified by ‘1234’;

localhost : 현재 PC 옵션에 따른

외부에서 접속 가능한 사용자
```
CREATE USER ‘username’@’%’ identified by ‘password’;
```
- % : 외부에서 접속 가능 옵션

User 삭제

DROP USER ‘username’@’localhost’;
DROP USER ‘username’@’%’;

USER 권한 관리

권한 목록 확인

SHOW GRANTS FOR ‘username’@’localhost’;

권한 부여 - dbname의 모든 권한을 username에게 부여
```
GRANT ALL ON dbname.* to ‘username’@’localhost’;
```

권한 삭제

REVOKE ALL ON dbname.* from ‘username’@’localhost’;

Table

TABLE 생성

CREATE TABLE tablename
(
columnname datatype,
columnname datatype,
…
)

# ex.
   CREATE TABLE mytable(id int, name varchar(16));

TABLE 조회
```
SHOW TABLES;
```
TABLE 구조(정보) 확인
```
DESC tablename;
```

TABLE 변경

이름 변경

ALTER TABLE tablename
RENAME new_tablename;

# ex.
   ALTER TABLE mytable RENAME person;

컬럼 추가

ALTER TABLE tablename
ADD COLUMN columnname datatype;

# ex. 
   ALTER TABLE person ADD COLUMN agee double;

컬럼 수정 - datatype 변경

ALTER TABLE tablename
MODIFY COLUMN columnname new_datatype;

컬럼 수정 - 컬럼명 + datatype 변경

datatype까지 변경 가능

ALTER TABLE tablename
**CHANGE** COLUMN old_columnname new_columnname new_datatype;

컬럼 삭제

ALTER TABLE tablename
DROP COLUMN columnname;

TABLE 삭제
```
DROP TABLE tablename;
```

2. DATA 조작어

INSERT | 데이터 추가
```
INSERT INTO tablename ( column1, column2, …)
VALUES (value1, value2, …)
```
- 모든 컬럼의 값을 추가할 경우 - 컬럼명 적지 않아도 됨
  입력값 순서와 테이블 컬럼 순서 일치
```
INSERT INTO tablename
```

SELECT | 데이터 조회

SELECT col1, col2, … FROM tablename;

# 테이블 내의 모든 컬럼 데이터 조회
SELECT * FROM tablename;

WHERE | 조건
SELECT, UPDATE, DELETE 등에 사용 가능
```
SELECT col FROM tablename WHERE condition ;
```

UPDATE | 데이터 수정

**UPDATE** tablename
**SET** col1 = val1, col2 = val2, …

WHERE condition;

DELETE | 데이터 삭제
```
DELETE FROM tablename
WHERE condition;
```
ORDER BY | 정렬
-ASC(Ascending) 오름차순
-DESC(DEscending) 내림차순
```
SELECT col1, … FROM tablename
ORDER BY col, col2, … ASC | DESC;
```
-default = ASC
-컬럼별로 각각 정렬기준 주어도 OK

3. 비교연산자

연산자	의미
=	같은
>	큰(초과)
<	작은(미만)
>=	크거나 같은(이상)
<=	작거나 같은(이하)
<>	같지 않은
!=	같지 않은

4. 논리연산자

연산자	의미
AND	조건을 모두 만족하면
OR	하나의 조건이라도 만족하는 경우
NOT	조건을 만족하지 않는 경우
BETWEEN	조건값이 범위 사이에 있으면 (이상,이하)
IN	목록 안에 조건이 존재하는 경우
LIKE	조건값이 패턴에 맞으면

5.

UNION

여러 개의 SQL문을 합쳐 하나의 SQL문으로 만들어 줌
- 컬럼의 개수가 같아야 함
- UNION : 중복된 값 제거
- UNION ALL : 중복된 값 모두 보여줌
```
SELECT column1, column2, ... FROM tableA
UNION | UNION ALL
SELECT column1, column2, ... FROM tableB
```

JOIN

두 개 이상의 테이블을 결합하는 것

INNER JOIN : 두 개의 테이블에서 공통된 요소를 통해 결합하는 방식

SELECT column1, column2, ...
FROM tableA
INNER JOIN tableB
ON tableA.column = tableB.column
WHERE condition;

FULL OUTER JOIN : 두 개의 테이블에서 공통영역을 포함하여 양쪽 테이블의 다른 영역을 모두 포함하는 방식
-MySQL에서는 지원 X

# SQL Server
   SELECT column1, column2, ...
   FROM tableA
   FULL OUTER JOIN tableB
   ON tableA.column = tableB.column
   WHERE condition;
   
# MySQL
   SELECT column1, column2, ...
   FROM tableA
   **LEFT JOIN** tableB ON tableA.column = tableB.column
   **UNION**
   SELECT column1, column2, ...
   FROM tableA
   **RIGHT JOIN** tableB ON tableA.column = tableB.column
   WHERE condition;

LEFT|RIGHT JOIN : 두 개의 테이블에서 공통영역을 포함에 왼쪽(오른쪽) 테이블의 다른 데이터를 포함하는 방식
```
SELECT column1, column2, ...
FROM tableA
**LEFT|RIGHT JOIN tableB
ON tableA.column = tableB.column**
WHERE condition;
```
SELF JOIN
WHERE절에 JOIN 조건 명시 + 이후에 일반 조건
- 동일한 컬럼명이 존재하는 경우에만 테이블명 암시
- BUT 가독성 위해 테이블명.컬럼명
- 유일한 컬럼명일 경우 시스템이 구분 가능

CONCAT
여러 문자열을 하나로 합치거나 연결

SELECT CONCAT('a', ' ', 'b');
-> a b

# a와 b에 컬럼명 넣어도 O
# CONCAT으로 여러 컬럼 묶어서 하나의 ALIAS로 설정 O

ALIAS
컬럼이나 테이블 이름에 별칭 생성
AS 생략 가능

SELECT column **as** alias
FROM tablename;

SELECT column1, column2, ...
FROM tablename **as** alias;

DISTINCT
검색한 결과의 중복 제거
```
SELECT **DISTINCT** column1, column2, ...
FROM tablename;
```
distinct 뒤에 column 여러 개가 나열될 경우
: 모든 컬럼값이 동일해야 중복 처리

LIMIT
검색 결과를 정렬순으로 주어진 숫자(number)만큼만 조회

SELECT column1, column2, ...
FROM tablename
WHERE condition
LIMIT number;

6. Primary Key & Foreign Key

Primary Key

# 설정
CREATE TABLE table명
(
	a int,
	b varchar(16),
	c int
	COSNTRAINT constraint_name PRIMARY KEY (a|a,c)
);

# 삭제
ALTER TABLE table명
DROP PRIMARY KEY;

# 재지정
ALTER TABLE table명
ADD PRIMARY KEY (a);

Foreign Key
테이블과 테이블을 연결
참조되는 테이블의 항목은 그 테이블의 기본키 (or 단일값)

desc 옵션에서 Key = MUL 로 나옴
여러 개 있을 수 있음
Constraint 명 생략 시 — 자동 생성

# 설정
CREATE TABLE tablename
(
	col1 datatype NOT NULL,
	col2 datatype NOT NULL,
	col3 datatype,
	col4 datatype, 
	...
	CONSTRAINT constraint_name PRIMARY KEY (col1, col2)
	CONSTRAINT constraint_name FOREIGN KEY (col3, col4, ...) REFERENCES REF_tablename(REF_column)
);

# constraint_name 생략 시 자동생성된 내용 확인
SHOW CREATE TABLE tablename;

# 삭제
# 삭제 이후 Key = MUL로 남아 있어도 삭제 된 것임 (reference 관계가 깨져있어)
ALTER TABLE tablename
DROP FOREIGN KEY constraint_name;

# 테이블 생성 후 지정
ALTER TABLE
ADD FOREIGN KEY (column) REFERENCES REF_tablename(REF_column);

7. 집계함수 | Aggregate Functions

Function	기능
COUNT	총 갯수를 계산	`SELECT COUNT(column) FROM table WHERE condition;`
SUM	합계를 계산	`SELECT SUM(column) FROM tablename WHERE condition;`
AVG	평균을 계산	`SELECT AVG(column) FROM tablename WHERE condition;`
MIN	가장 작은 값	`SELECT MIN(column) FROM tablename WHERE condition;`
MAX	가장 큰 값	`SELECT MAX(column) FROM tablename WHERE condition;`
FIRST	첫번째 결과값 리턴
LAST	마지막 결과값 리턴

Group By

그룹화하여 데이터 조회
Having

집계함수가 조건에 포함되는 경우

8. Scalar Function

Function	기능
UCASE	영어 대문자로 변환
LCLASE	영어 소문자로 변환
MID	문자열 부분 반환
LENGTH	문자열 길이 반환
ROUND	지정한 자리에서 숫자 반올림 ( 0:소숫점 첫째자리 )
NOW	현재 날짜 및 시간 반환
FORMAT	숫자 천단위 반점 있는 형식으로 반환

MID
SELECT MID(string, start_position, lenth)
string : 원본 문자열
start : 반환 문자열 시작 위치 ( 1부터 시작, 마지막 글자는 -1 )
length : 반환 문자열 길이
ROUND
SELECT ROUND(number, 반올림 위치)
default = 소수점 첫째자리에서 반올림
FORMAT
SELECT FORMAT(number, 반올림 위치)
- 반올림 위치가 숫자보다 클 때는 0으로 채움
- 반환값 = string

9. Sub Query

: 하나의 SQL문 안에 포함되어 있는 또다른 SQL문

메임쿼리가 서브쿼리를 포함
- 서브쿼리 : 메임쿼리의 칼럼 사용 가능
- 메인쿼리 : 서브쿼리의 칼럼 사용 불가능
괄호로 묶어서 사용
단일행|복수행 비교 연산자와 함께 사용 가능
ORDER BY 사용 불가

Scalar Subquery — SELECT절
결과 = 한개의 컬럼과 같다

SELECT col1 (SELECT col2 FROM table2 WHERE condition)
FROM table1 WHERE condition;

Inline View — FROM절
메인쿼리에서는 인라인 뷰에서 조회한 column만 사용 가능
```
SELECT a.col, b.col
FROM table1 a, (SELECT col1, col2 FROM table2) b
WHERE condition;
```

Nested Subquery | 중첩 서브리 — WHERE절

단일행 서브쿼리 | single row
- 하나의 열 검색
- 비교연산자 = =, >, ≥, <, ≤, <>, ≠
- 한 개의 결과값
```
SELECT col
FROM tablename
WHERE col = **(SELECT col FROM tablename WHERE condition)**
ORDER BY col;
```

다중행 서브쿼리 | multiple row

하나 이상의 열을 검색

IN - 서브쿼리 결과값 중 컬럼값이 있는 경우 반환

SELECT col
FROm tablename
WHERE col IN (SELECT col FROM tablename WHERE condition)
ORDER BY col;

EXISTS - 서브쿼리 결과에 값이 있으면 반환

SELECT col
FROm tablename
WHERE EXISTS (SELECT col FROM tablename WHERE condition)
ORDER BY col;

ANY - 서브쿼리 결과 중 최소한 하나라도 만족하면 반환

SELECT col
FROm tablename
WHERE col = ANY (SELECT col FROM tablename WHERE condition)
ORDER BY col;

ALL - 서브쿼리 결과를 컬럼이 모두 만족하면

SELECT col
FROm tablename
WHERE col = ALL (SELECT col FROM tablename WHERE condition)
ORDER BY col;

다중컬럼 서브쿼리 | multiple column

하나 이상의 열을 검색
연관 서브쿼리
서브쿼리 내에 메임쿼리 컬럼이 같이 사용되는 경우

SELECT col
FROM tablename a
WHERE (a.col1, a.col2, ...) IN (SELECT b.col1, b.col2, ...
								FROM tablename b
								WHERE a.col = b.col)
ORDER BY col;

99. AWS RDS | Amazon Relational Database Service

AWS에서 제공하는 관계형데이터베이스서비스 (RDBMS)

99.

SQL 실행
1. mysql -h “앤드포인트” -P 3306 -u admin -p
  
  h for host
  
  P for Port
  
  u for user
  
  p for password
2. mysql -h “앤드포인트” -P 3306 -u admin -p 데이터베이스명
  : 바로 해당 데이터베이스로 ( mysql 시작해서 use 데이터베이스명 한 것 과 같음)
cd 폴더명 폴더 한단계 더 들어가기
code . 해당 폴더에서 vs code 실행

(local) 해당 파일 데이터베이스에 넣기
mysql -u user명 -p **database명 < 파일명(확장자 포함)**
(local) 데이터베이스 내용 파일에 넣기
mysql -u user명 -p **database명 > 파일명(확장자 포함)**
(local) 데이터베이스 백업
mysqldump -u user명 -p database명 > 새 파일명(확장자 포함)
mysqldump -u user명 -p --all-databases > 새 파일명(확장자 포함)
(local) 테이블 백업
mysqldump -u user명 -p database명 table명 > 새 파일명(확장자 포함)
(local) 테이블 스키마 로컬로 넣기 - 데이터 제외. 테이블 생성 쿼리 백업
mysqldump -d -u user명 -p database명 table명 > 새 파일명(확장자 포함)
타 파일 해당 데이터베이스에서 실행
source 파일명
테이블 백업
mysqldump --set-gtid-purged=OFF -h "앤드포인트" -P 3306 -u admin -p 데이터베이스 테이블명 > 백업파일명

porii

다음 포스트