SQL

1.SQL 언어

1) 데이터 정의 언어 (DDL: Data Definition Language)

• CREATE, ALTER, DROP 등의 명령어

2) 데이터 조작 언어 (DML: Data Manipulation Language)

• INSERT, UPDATE, DELETE, SELECT 등의 명령어

3) 데이터 제어 언어 (DCL: Data Control Language)

• GRANT, REVOKE, COMMIT, ROLLBACK 등의 명령어

2. SQL 문법

1) database 관리

# 현재 database 목록 확인
SHOW DATABASES;

# Database 이름을 지정하여 생성
CREATE DATABASE dbname;

# 해당 데이터베이스로 이동 (사용)
USE dbname;

# Database 삭제
DROP DATABASE dbname;

2) 유저관리

# 사용자 정보는 mysql 에서 관리하므로 일단 mysql 데이터베이스로 이동후 조회
USE mysql;
SELECT host, user FROM user;

# 현재 PC 에서만 접속 가능한 사용자를 비밀번호와 함께 생성
CREATE USER 'username'@'localhost' identified by 'password';

# 외부에서 접속 가능한 사용자를 비밀번호와 함께 생성
CREATE USER 'username'@'%' identified by 'password';

# 접근 범위에 따라 같은 이름의 사용자여도 별도로 삭제
DROP USER 'username'@'localhost'
DROP USER 'username'@'%'

3) 유저 권한 관리

# 사용자에게 부여된 모든 권한 목록을 확인
SHOW GRANTS FOR 'username'@'localhost';

# 사용자에게 특정 데이터베이스의 모든 권한을 부여
GRANT ALL ON dbname.* to 'username'@'localhost';

# 사용자에게 특정 데이터베이스의 모든 권한을 삭제
REVOKE ALL ON dbname.* from 'username'@'localhost';

4) 수정내용이 적용이 되지 않은 경우 새로고침

FLUSH PRIVILEGES;

5) TABLE 관리

# table 생성
CREATE TABLE tablename
(
columnname datatype.
columnname datatype.
...
)

# Table 목록 확인
SHOW TABLES;

# Table 정보 확인
DESC tablename;

# Table 이름 변경
ALTER TABLE tablename RENAME new_tablename;

# Table Column 추가
ALTER TABLE tablename ADD COLUMN columnname datatype;

# Table Column 변경
#datatype
ALTER TABLE tablename MODIFY COLUMN columnname datatype;
 
#columnname
ALTER TABLE tablename CHANGE COLUMN old_columnname new_columnname new_datatype;

# Table Column 삭제
ALTER TABLE tablename DROP COLUMN columnname;

# Table 삭제
DROP TABLE tablename;

# UPDATE 문법 
UPDATE tablename SET column1 = value1, column2 = value2, ㆍㆍㆍ
WHERE condition;

# DELETE 문법
DELETE FROM tablename WHERE condition;

6) ORDER BY 문법

• ASC(Ascending): 오름차순으로 정렬
• DESC(Descending): 내림차순으로 정렬

7) Comparison Operators(비교연산자)

A = B   : A 와 B 가 같은
A > B   : A 가 B 보다 큰 (초과)
A < B   : A 가 B 보다 작은 (미만)
A >= B : A 가 B 보다 크거나 같은 (이상)
A <= B : A 가 B 보다 작거나 같은 (이하)
A <> B : A 가 B 보다 크거나 작은 (같지않은)
A != B  : A 와 B 같지 않은

8) Logical Operators (논리 연산자)

AND : 조건을 모두 만족하는 경우 TRUE
OR : 하나의 조건이라도 만족하는 경우 TRUE
NOT : 조건을 만족하지 않는 경우 TRUE
BETWEEN : 조건값이 범위 사이에 있으면 TRUE
IN : 조건값이 목록에 있으면 TRUE
LIKE : 조건값이 패턴에 맞으면 TRUE

9) UNION
여러 개의 SQL문을 합쳐서 하나의 SQL 문으로 만들어주는 방법 (※ 칼럼의 개수가 같아야함)

- UNION : 중복된 값을 제거하여 알려준다.
SELECT * FROM table1
UNION 
SELECT * FROM table2;

- UNION ALL : 중복된 값도 모두 보여준다. 
SELECT * FROM table1
UNION ALL
SELECT * FROM table2;

10) JOIN

- INNER JOIN : 두 개의 테이블에서 공통된 요소들을 통해 결합하는 조인방식

SELECT column1, column1,ㆍㆍㆍ FROM table1
INNER JOIN table2
ON table1.column = table2.column
WHERE condition;

- LEFT JOIN : 두개의 테이블에서 공통영역을 포함해 왼쪽 테이블의 다른 데이터를 포함하는 조인방식

SELECT column1, column1,ㆍㆍㆍ FROM table1
LEFT JOIN table2
ON table1.column = table2.column
WHERE condition;

- RIGHT JOIN : 두개의 테이블에서 공통영역을 포함해 오른쪽 테이블의 다른 데이터를 포함하는 조인방식

SELECT column1, column1,ㆍㆍㆍ FROM table1
RIGHT JOIN table2
ON table1.column = table2.column
WHERE condition;

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

SELECT column1, column1,ㆍㆍㆍ FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column
WHERE condition;

# MySQL 에서는 FULL JOIN 을 지원하지 않으므로 다음의 쿼리로 같은 결과를 만들 수 있다
SELECT column1, column1,ㆍㆍㆍ FROM table1
LEFT JOIN table2
ON table1.column = table2.column
UNION
SELECT column1, column1,ㆍㆍㆍ FROM table1
RIGHT JOIN table2
ON table1.column = table2.column
WHERE condition;

- SELF JOIN 
SELECT column1, column2,ㆍㆍㆍ
FROM table1, table2,ㆍㆍㆍ
WHERE condition;

11) CONCAT

- CONCAT : 여러 문자열을 하나로 합치거나 연결
SELECT CONCAT ('string', 'string',ㆍㆍㆍ)

12) ALIAS

- ALIAS : 칼럼이나 테이블 이름에 별칭 생성
#COLUMN  
SELECT column as alias
FROM tablename;
#TABLE
SELECT column1, column2,ㆍㆍㆍ
FROM tablename as alias;

13) DISTINCT

- DISTINCT : 검색한 결과의 중복 제거
SELECT DISTINCT column1, column2,ㆍㆍㆍ
FROM tablename

14) LIMIT

- LIMIT : 검색결과를 정렬된 순으로 주어진 숫자만큼만 조회
SELECT column1, column2,ㆍㆍㆍ
FROM tablename
WHERE condition
LIMIIT number;

15) Python에서 MYSQL 접속하기

mydb = mysql.connector.connet(
	host = "<hostname>",
	user = "<username>",
	password = "<password>"
)

mycursor = mydb.cursor()
mycursor.execute(<query>)
읽어올 데이터 양이 많은 경우 (buffered=True) 추가

16) PRIMARY KEY, FOREIGN KEY 설정

CREATE TABLE tablename
	(
	column1 datatype,
	column2 datatype,
	column3 datatype,
	...
	CONSTRAINT constraint_name
	PRIMARY KEY (column1, column2,...),
	CONSTRAINT constraint_name
	FOREIGN KEY (column3, column4,...) REPERENCES REF_tablename(REF_column)
	)

17) Aggregate Funtions (집계함수)

count : 총 갯수를 계산해주는 함수
sum : 합계를 계산해 주는 함수
avg : 평균을 계산해주는 함수
min : 가장 작은 값을 찾아주는 함수
max : 가장 큰 값을 찾아주는 함수
first : 첫번째 결과값을 리턴하는 함수
last : 마지막 결과값을 리턴하는 함수

18) Scalar Funtions

ucase : 영문자를 대문자로 변환하는 함수
lcase : 영문자를 소문자로 변환하는 함수
mid : 문자열 부분을 반환하는 함수
length : 문자열의 길이를 반환하는 함수
round : 지정한 자리에서 숫자를 반올림하는 함수
now : 현재 날씨 및 시간을 반환하는 함수
format : 숫자를 천단위 콤마가 있는 형식으로 반환하는 함수