1. 서브쿼리와 JOIN 활용
(1) customer_id = 5인 고객의 대여 횟수를 출력.
SELECT customer_id, first_name, last_name,
(SELECT count(rental_id)
FROM sakila.rental
WHERE customer_id = c. customer_id) AS rental_count
FROM sakila.customer c
WHERE customer_id=5;
SELECT
c.customer_id,
c.first_name,
c.last_name,
count(r.rental_id) AS rental_count
FROM sakila.customer c
INNER JOIN sakila.rental r
ON c.customer_id = r.customer_id
WHERE c.customer_id=5;
=> JOIN이 서브쿼리보다 일반적으로 성능이 더 빠름.
(2) 대여 횟수가 10번 이상인 고객 ID, 이름, 성을 출력.
SELECT
customer_id,
first_name,
last_name
FROM sakila.customer
WHERE customer_id IN (
SELECT customer_id
FROM sakila.rental
GROUP BY customer_id
HAVING count(rental_id) >= 10
)
ORDER BY customer_id;
(3) 고객별 대여 횟수를 계산해 평균 대여 횟수를 초과한 고객 출력.
SELECT
c.customer_id,
c.first_name,
c.last_name,
count(r.rental_id) AS rental_count
FROM sakila.customer c
JOIN sakila.rental r ON c.customer_id = r.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
HAVING count(r.rental_id) > (
SELECT AVG(subquery_avg.rental_count)
FROM (
SELECT count(r.rental_id) AS rental_count
FROM sakila.rental r
GROUP BY r.customer_id
) AS subquery_avg
)
ORDER BY rental_count DESC;
SELECT
c.customer_id,
c.first_name,
c.last_name,
(SELECT count(rental_id)
FROM sakila.rental r
WHERE r.customer_id = c.customer_id) AS rental_count
FROM sakila.customer c
WHERE (SELECT count(rental_id)
FROM sakila.rental r
WHERE r.customer_id = c.customer_id) > (
SELECT AVG(rental_count)
FROM (SELECT count(rental_id) AS rental_count
FROM sakila.rental
GROUP BY customer_id
) AS subquery_avg
)
ORDER BY rental_count DESC;
2. 메모리 엔진 테이블 생성 및 해시 인덱스
(1) 메모리 엔진 테이블 생성 및 단일 키 조회
use my_database;
CREATE TABLE hash_example(
id INT NOT NULL,
value VARCHAR(100),
primary key(id)
) ENGINE = MEMORY;
INSERT INTO hash_example (id, value) values
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie'),
(4, 'David'),
(5, 'Eve');
SHOW index FROM hash_example;
SELECT * FROM hash_example WHERE id=3;
(2) 해시 충돌 예제
CREATE table hash_collision_example(
id INT NOT NULL,
value VARCHAR(100),
hash_value INT NOT NULL,
primary key (hash_value)
);
INSERT INTO hash_collision_example (id, value, hash_value)
values
(1, "Alice", MOD(1, 3)),
(2, "Bob", MOD(2, 3)),
(3, "Charlie", MOD(3, 3)),
(4, "David", MOD(4, 3)),
(5, "Eve", MOD(5, 3));
3. 이진 트리 테이블 생성
(1) 이진 트리 테이블 생성 및 데이터 삽입
CREATE table BinaryTree(
id INT AUTO_INCREMENT primary key,
value INT NOT NULL,
left_child INT DEFAULT NULL,
right_child INT DEFAULT NULL
);
INSERT INTO BinaryTree(value) values (10);
INSERT INTO BinaryTree(value) values (5);
UPDATE BinaryTree SET left_child = last_insert_id() WHERE id=1;
INSERT INTO BinaryTree(value) values (15);
UPDATE BinaryTree SET right_child = last_insert_id() WHERE id=1;
SELECT * FROM BinaryTree;
4. B-Tree 테이블 생성
(1) B-Tree 테이블 생성 및 데이터 삽입
- B-Tree 인덱스:
1) 데이터를 정렬된 트리 구조로 저장하여 빠른 검색이 가능하게 함.
2) N개의 자식을 가질 수 있도록 고안됨.
3) 좌우 자식 간의 균형이 맞지 않을 경우 매우 비효율적이라, 항상 균형을 맞춘다는 의미에서 균형트리(balanced Tree)라고 불림.
use my_database;
CREATE table BTreeExample(
id INT auto_increment primary key,
value INT NOT NULL,
INDEX (value)
);
INSERT INTO BTreeExample (value) values (10), (20);
INSERT INTO BTreeExample (value) values (30);
INSERT INTO BTreeExample (value) values (40), (50);
INSERT INTO BTreeExample (value) values (25);
SELECT * FROM BTreeExample ORDER BY value;
SELECT * FROM BTreeExample WHERE value BETWEEN 20 AND 40;
5. B+Tree 테이블 생성
(1) B+Tree 테이블 생성 및 데이터 삽입
- B+Tree 인덱스:
1) B-Tree와 기본 구조는 동일하나, leaf node들간 linked list형태로 연결되어 있음.
use my_database;
CREATE table BPlusTreeExample(
id INT auto_increment primary key,
value INT NOT NULL,
data VARCHAR(100),
INDEX (value)
);
INSERT INTO BPlusTreeExample (value, data) values (10, 'Data A');
INSERT INTO BPlusTreeExample (value, data) values (20, 'Data B');
INSERT INTO BPlusTreeExample (value, data) values (30, 'Data C');
INSERT INTO BPlusTreeExample (value, data) values (40, 'Data D');
INSERT INTO BPlusTreeExample (value, data) values (50, 'Data E');
SELECT * FROM BPlusTreeExample WHERE value=30;
SELECT * FROM BPlusTreeExample WHERE value BETWEEN 20 AND 40;
(2) 인덱스가 없는 경우
use my_database;
DROP TABLE IF EXISTS NoIndexTable;
DROP TABLE IF EXISTS IndexedTable;
CREATE table NoIndexTable(
id INT auto_increment primary key,
value INT NOT NULL,
data VARCHAR(100)
);
CREATE table IndexedTable(
id INT auto_increment primary key,
value INT NOT NULL,
data VARCHAR(100),
Index idx_value(value)
);
INSERT INTO NoIndexTable (value, data)
SELECT 678282, CONCAT('Data_', FLOOR(RAND() * 1000000))
FROM dual
LIMIT 1000000;
INSERT INTO NoIndexTable (value, data)
SELECT FLOOR(RAND() * 1000000), CONCAT('Data_', FLOOR(RAND() * 1000000))
FROM dual
LIMIT 100000;
INSERT INTO IndexedTable (value, data)
SELECT FLOOR(RAND() * 1000000), CONCAT('Data_', FLOOR(RAND() * 1000000))
FROM dual
LIMIT 10000000;
SELECT value, COUNT(*) AS occurrences
FROM NoIndexTable
GROUP BY value
ORDER BY occurrences DESC
LIMIT 10;
SELECT value, COUNT(*) AS occurrences
FROM IndexedTable
GROUP BY value
ORDER BY occurrences DESC
LIMIT 10;
SELECT * FROM NoIndexTable WHERE value = 678282;
SELECT * FROM IndexedTable WHERE value = 678282;
SET profiling = 1;
SELECT * FROM NoIndexTable WHERE value = 678282;
SELECT * FROM IndexedTable WHERE value = 678282;
SHOW PROFILES;
정리
| 카테고리 | 문법 | 설명 |
|---|
| SELECT | SELECT column1, column2 FROM table | 특정 컬럼 데이터를 조회 |
| WHERE | WHERE condition | 조건에 맞는 데이터만 조회 |
| ORDER BY | ORDER BY column [ASC / DESC] | 데이터를 지정된 컬럼 기준으로 정렬 (기본값은 오름차순 ASC, 내림차순은 DESC 사용) |
| GROUP BY | GROUP BY column | 데이터를 그룹화 |
| HAVING | HAVING condition | 그룹화된 데이터 조건 필터링 |
| JOIN | INNER JOIN table ON condition | 두 테이블의 데이터 결합 |
| SUBQUERY | (SELECT column FROM table WHERE condition) | 쿼리 내에서 다른 쿼리를 실행 |
| COUNT | COUNT(column) | 특정 컬럼의 개수를 계산 |
| AVG | AVG(column) | 특정 컬럼 값의 평균 계산 |
| INSERT | INSERT INTO table (column1, column2) VALUES (value1, value2) | 테이블에 데이터 삽입 |
| UPDATE | UPDATE table SET column1 = value1 WHERE condition | 기존 데이터 수정 |
| DELETE | DELETE FROM table WHERE condition | 조건에 맞는 데이터를 삭제 |
| CREATE TABLE | CREATE TABLE table_name (column_name data_type constraints) | 새로운 테이블 생성 |
| PRIMARY KEY | PRIMARY KEY(column) | 테이블의 고유 식별자 설정 |
| FOREIGN KEY | FOREIGN KEY(column) REFERENCES other_table(column) | 다른 테이블과의 참조 관계 설정 |
| INDEX | CREATE INDEX index_name ON table(column) | 특정 컬럼에 인덱스 생성 |
| ENGINE | ENGINE = MEMORY | 테이블 저장 엔진 지정 |
| MOD | MOD(value, divisor) | 나머지 계산 |
| LAST_INSERT_ID | LAST_INSERT_ID() | 마지막으로 삽입된 AUTO_INCREMENT 값 반환 |
파이썬
1. 기본
- 알파벳, 숫자, 언더스코어(_)로 선언 가능
- 대소문자 구분됨
- 특별한 의미가 있는 예약어는 쓰지 않는다.
- 파이썬은 들여쓰기에 민감함(주피터는 알아서 고쳐줌)
(1) 산술 연산자(+, -, *, /, //, %, **), print문
// 실습 코드
1+1
2-3
4*6
3/4
3//4
3%5
3**5
10**2
10*10
a=3
b=5
print(a+b)
print("a+b")
(2) 변수의 주소(id)
// 실습 코드
id(a)
(3) 증감 연산자(+=, -=, *=, /=)
// 실습 코드
a=1
a += 1
print(a)
a -= 1
print(a)
a *= 1
print(a)
a /= 5
print(a)
(4) 데이터 타입(type) 및 형 변환(int, float, str, len)
// 실습 코드
a = 1
b = 1
print(a, b)
type(a)
c = 3.5
d = 4.9
print(c, d)
type(c)
print(a+c, b+d)
d = int(c)
print(d)
type(d)
a = 10
type(a)
float(a)
(4) 문자열 길이(len) 계산 및 문자열 슬라이싱(문자열[시작:끝])
- 문자열의 길이를 계산할 때에는 len함수를 사용하여 아래 예제 코드와 같이 계산한다.
- 문자열에서 특정 문자열만을 가져오고 싶을 때에는 문자열[시작:끝]을 이용하여 아래와 같이 슬라이싱한다.(인덱스는 0부터 시작하며, 끝번호에 해당하는 문자는 읽어오지 않으니 끝번호는 항상 +1 하여 적용해야한다!!)
// 실습 코드
str_5 = '인덱싱을 하기 위한 string입니다.'
len(str_5)
str_5[11:17]
정리
| 주제 | 구문 | 설명 |
|---|
| 산술 연산자 | +, -, *, / | 덧셈, 뺄셈, 곱셈, 나눗셈을 수행합니다. |
| // | 나눗셈의 몫을 반환합니다. |
| % | 나눗셈의 나머지를 반환합니다. |
| ** | 거듭제곱을 계산합니다. |
| 변수와 출력 | print(변수명) | 변수의 값을 출력합니다. |
| print("문자열") | 문자열 그대로를 출력합니다. |
| 변수의 주소 확인 | id(변수명) | 변수의 메모리 주소를 반환합니다. |
| 증감 연산자 | +=, -=, *=, /= | 변수의 값에 대해 덧셈, 뺄셈, 곱셈, 나눗셈 후 결과를 변수에 저장합니다. |
| 데이터 타입 확인 | type(변수명) | 변수의 데이터 타입을 확인합니다. |
| 형 변환 | int(값) | 값을 정수형으로 변환합니다. |
| float(값) | 값을 실수형으로 변환합니다. |
| str(값) | 값을 문자열로 변환합니다. |
| 문자열 길이 | len(문자열) | 문자열의 길이를 반환합니다. |
| 문자열 슬라이싱 | 문자열[시작:끝] | 문자열에서 시작 인덱스부터 끝-1까지의 부분 문자열을 반환합니다. |
