[Python] SQL

• SQL (Structured Query Language)
  • 관계형 데이터베이스 관리시스템의 데이터 관리를 위해 특수 목적으로 설계된 프로그래밍 언어
  • 데이터베이스 스키마 생성 및 수정
  • 자료의 검색 및 관리
  • 데이터베이스 객체 접근 조정 관리
• SQL 분류
  • DDL (Data Definition Language) - 데이터 정의 언어
    • 관계형 데이터베이스 구조(테이블,스키마)를 정의하기 위한 명령어
    • CREATE, DROP, ALTER
  • DML (Data Manipulation Language) - 데이터 조작 언어
    • 데이터를 저장, 조회, 수정, 삭제, 등을 하기 위한 명령어
    • INSERT, SELECT, UPDATE, DELETE
  • DCL (Data Control Language) - 데이터 제어 언어
    • 데이터베이스 사용자의 권한 제어를 위해 사용하는 명령어
    • GRANT, REVOKE, COMMIT, ROLLBACK

 

• db.sqlite3 활용

sqlite3 db.sqlite3

 

• 테이블 생성 및 삭제

# SELECT 문은 특정 테이블의 레코드(행) 정보를 반환!
SELECT * FROM examples; # ; 필수

# 터미널 view 변경
.headers on # 속성 출력
.mode column # 줄 표시

# 데이터베이스에서 테이블 생성
CREATE TABLE classmates (
id INTEGER PRIMARY KEY,
name TEXT,
age INT,
address TEXT
);

# 데이터베이스에서 테이블 제거
DROP TABLE classmates;

 

• CREATE

# INSERT는 특정 테이블에 레코드(행)를 삽입(생성)!
INSERT INTO classmates (name,age) VALUES ('홍길동', 23);
# 모든 데이터가 있는 경우 column을 명시하지 않아도 됨
INSERT INTO classmates VALUES ('홍길동', 23, '서울'); 

# id -> rowid
# SQLite는 따로 PRIMARY KEY 속성의 컬럼을 작성하지 않으면 
# 값이 자동으로 증가하는 PK 옵션을 가진 rowid 컬럼을 정의

# 꼭 필요한 정보라면 공백 X -> CREATE단계에서 NOT NULL설정하기

 

• READ

# SELECT : 테이블에서 데이터를 조회
SELECT rowid, name FROM classmates;
# SELECT DISTINCT : 조회 결과에서 중복 행을 제거, DISTINCT 절은 SELECT 키워드 바로 뒤에 작성!
SELECT DISTINCT age FROM classmates;

# LIMIT : 쿼리에서 반환되는 행 수를 제한, 특정 행부터 시작해서 조회하기 위해 OFFSET 키워드(0부터 시작)와 함께 사용하기도 함
SELECT rowid,name FROM classmates LIMIT 1;
SELECT rowid,name FROM classmates LIMIT 1 OFFSET 2; # 3번째부터 1개 조회

# WHERE : 쿼리에서 반환된 행에 대한 특정 검색 조건을 지정
SELECT rowid, name FROM classmates WHERE address='서울';

 

• DELETE

# DELETE : 테이블에서 행을 제거
DELETE FROM classmates WHERE rowid=5;

# SQLite는 기본적으로 id 재사용
# 재사용하지 않기 위해 테이블 생성 단계에서 AUTOINCREMENT 적기

 

• UPDATE

# UPDATE : SET clause에서 테이블의 각 열에 대해 새로운 값을 설정
UPDATE classmates SET name='홍길동', address='제주도' WHERE rowid=5;

 

 

• Aggregate function : 집계 함수
  • 각 집합에 대한 계산을 수행하고 단일 값을 반환
  • 여러 행으로부터 하나의 결괏값을 반환하는 함수
  • SELECT 구문에서만 사용
  • COUNT, AVG, MAX, MIN, SUM

# 테이블의 총 개수
SELECT COUNT(*) FROM users;

# 30살 이상인 사람들의 평균 나이
SELECT AVG(age) FROM users WHERE age>=30;

# balance가 가장 높은 사람과 그 액수 조회
SELECT firse_name, MAX(balance) FROM users;

 

• LIKE operator
  • 패턴 일치를 기반으로 데이터 조회
  • % : 0개 이상의 문자
  • _ : 임의의 단일 문자
  • wildcard character : 구체적인 이름 대신에 여러 파일을 동시에 지정할 목적으로 사용하는 특수 기호

# 나이가 20대인 사람만 조회
SELECT * FROM users WHERE age LIKE '2_';

# 지역 번호가 02인 사람만 조회
SELECT * FROM users WHERE phone LIKE '02-%';

# 이름이 '준'으로 끝나는 사람만 조회
SELECT * FROM users WHERE first_name LIKE '%준';

# 중간 번호가 5114인 사람만 조회
SELECT * FROM users WHERE phone LIKE '%-5114-%';

 

• ORDER BY
  • 조회 결과 집합을 정렬
  • SELECT문에 추가
  • ASC - 오름차순(default)
  • DESC - 내림차순

# 나이 순으로 오름차순 정렬하여 상위 10개만 조회
SELECT * FROM user ORDER BY age ASC LIMIT 10;

# 나이 순, 성 순으로 오름차순 정렬하여 10개만 조회
SELECT * FROM user ORDER BY age, last_name ASC LIMIT 10;

# 계좌 잔액 순으로 내림차순 정렬하여 해당 유저의 성과 이름을 10개만 조회
SELECT last_name, first_name FROM users ORDER BY balance DESC LIMIT 10;

 

• GROUP BY
  • 행 집합에서 요약 행 집합을 만듦
  • SELECT 문의 optional 절
  • 선택된 행 그룹을 하나 이상의 열 값으로 요약 행으로 만듦
  • 문장에 WHERE 절이 포함된 경우 반드시 WHERE 절 뒤에 작성

# users에서 각 성씨가 몇명씩 있는지 조회
SELECT last_name, COUNT(*) FROM users GROUP BY last_name;
# AS를 활용해서 컬럼 명을 바꿧 조회 가능
SELECT last_name, COUNT(*) AS name_count FROM users GROUP BY last_name;

 

• ALTER
  • table 이름 변경
  • 테이블에 새로운 column 추가
  • column 이름 수정

# 테이블 이름을 users에서 news로
ALTER TABLE users RENAME TO news;

# 새로운 컬럼 추가
ALTER TABLE news ADD COLUMN created_at TEXT NOT NULL;
# error -> 기존 레코드들에는 새로 추가할 필드에 대한 정보가 없다
# 해결법
# 1.
# NOT NULL 설정 없이 추가
ALTER TABLE news ADD COLUMN created_at TEXT ;
# 2. 
# 기본 값 설정 
ALTER TABLE news ADD COLUMN subtitle TEXT NOT NULL DEFAULT '소제목';