SQLite 사용법 (Create, Alter, Insert, Update, Select, Delete.. )

파이썬으로 간단히 데이터를 저장하고 조회하기 위하여

무료 소프트웨어인 Sqlite를 사용하기 위하여

자주 사용되는 핵심 명령어와 사용법을 정리해 보았습니다.

 

 

 

DB Browser for SQLite 핵심 기능

 

 

Sqlite3 핵심 기능 사용하기

 

 

1. 데이터베이스 열기 - 작업을 시작하기 위하여 이미 만들어진 데이터베이스를 열기
2. 테이블 생성하기  - 테이블을 화면에서 직접 생성하거나 추가
3. SQL 실행하기 - Sql문으로 직접 데이터를 삽입, 수정, 삭제, 조회 
4. 데이터 보기 - 테이블별로 저장되어 있는 데이터들을 화면에서 직접 조회

 

데이터 삽입 , 수정, 삭제 SQL 예제

 

 

1. 데이터 삽입 - Insert 

 

INSERT INTO Person (ID, Name, Birthday)
VALUES (1, '티스토리', '1994-06-09');

####  모든 컬럼에 순서대로 입력 할때

INSERT INTO Person
VALUES (1, '티스토리', '1994-06-09');

####  여러행 입력할때 : ID 값 없어도 자동증가 옵션설정했으므로 자동 증가함

INSERT INTO Person (Name, Birthday)
VALUES ('티스토리1', '1986-05-21'), ('티스토리2', '1992-11-06');

####  행이 존재하는 경우 수정
INSERT OR REPLACE INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

####  다른 테이블 데이타 읽어와서 데이터 저장시

INSERT INTO 테이블B (컬럼1, 컬럼2, ...)
SELECT 컬럼1, 컬럼2, ...
FROM 테이블A;

 

2. 데이터 삭제 - Delete

DELETE FROM Person;

 

 

3. 데이터 수정  - Update

 

UPDATE Person SET Name = '블로그' WHERE Name = '티스토리';

 

 

4. 데이터 조회  - Select

 

SELECT Name FROM Person ORDER BY Name DESC;

SELECT * FROM Person WHERE Name = '티스토리';

SELECT * FROM Person WHERE Birthday IS NOT NULL ;

SELECT * FROM Person WHERE Birthday LIKE '1986%';

##----------------별   명 -------------------------------

SELECT  Name AS "이름", Birthday AS "생일"  FROM Person;

SELECT  Name  "이름", Birthday  "생일"  FROM Person;


##---- 계산식으로  별도 컬럼명으로  지정해서 조회 ---

SELECT  volume_opencontract,
round(price_close / (price_close * volume_opencontract ), 3) account FROM opt_opencontract_202209
    
    ###  round : 지정된 소숫점 자리로 반올림하는 함수
    
    
##---- CASE문으로  별도 컬럼명으로   지정해서 조회 ---

    SELECT     Name,     date,     MM,
    CASE
        WHEN MM = '01' THEN 'Jan.'
        WHEN MM = '02' THEN 'Feb.'
        WHEN MM = '03' THEN 'Mar.'
        WHEN MM = '04' THEN 'Apr.'
        WHEN MM = '05' THEN 'May.'
        WHEN MM = '06' THEN 'Jun.'
        WHEN MM = '07' THEN 'Jul.'      
        WHEN MM = '08' THEN 'Aug.'
        WHEN MM = '09' THEN 'Sep.'
        WHEN MM = '10' THEN 'Oct.'
        WHEN MM = '11' THEN 'Nov.'
        WHEN MM = '12' THEN 'Dec.'
    END Month
FROM BirthdayView;

##---- Distinct 로 중복제거된 값 조회 ---

select distinct name from BirthdayView;

 

 

 

테이블 생성 , 수정, 삭제 SQL 예제

 

1. 테이블  생성 - Create Table

 

CREATE TABLE "Person" (
    "ID"    INTEGER NOT NULL,
    "Name"  TEXT NOT NULL,
    "Birthday"  TEXT,
    PRIMARY KEY("ID" AUTOINCREMENT)
);

 

2. 테이블  수정 - Alter Table

 

 

 

Alter Table 구조

 

 

ALTER TABLE Person ADD COLUMN New INTEGER;   ### 컬럼 추가

ALTER TABLE Person RENAME COLUMN New TO Height;    ### 컬럼명 변경

 

3. 테이블  삭제 - Drop Table

DROP TABLE Person;

 

 

뷰 View 생성,삭제 SQL 예제 - Create View, Drop View

 

CREATE VIEW BirthdayView
AS
SELECT
    Name,
    Birthday bdate,
    substr(Birthday, 1, 4) YYYY,
    substr(Birthday, 6, 2) MM,
    substr(Birthday, 9, 2) DD
FROM Person;


###  substr('abcdefg', 3, 2);  -- 셋째 자리부터 두 글자 반환 --3번째인수생략시는 끝까지



DROP VIEW BirthdayView;

 

 

자주 사용하는 함수 : 날짜형식 strftime, Current_date

 

 

SELECT strftime('%Y-%m-%d %H:%M:%S', 'now') 현재시간; 

###>>  2023-03-21 05:04:38   세계표준시가 반환됨

SELECT strftime('%Y-%m-%d %H:%M:%S', 'now', 'localtime') 현지시간;

###>>  2023-03-21 14:05:51


SELECT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP;

 

 

자주 사용하는 함수 : count, max, min, avg

 

 

###  Count 구하기

SELECT count(*) FROM Person;    ###  null값도 count됨

SELECT count(Height) FROM Person;    ### null값도 count 제외

###  max, max, sum, avg 구하기

SELECT max(Height) FROM Person;  
SELECT max(Height) FROM Person;
SELECT sum(Height) FROM Person;
SELECT avg(Height) FROM Person;

 

자주 사용하는 함수 : round

 

 

###---  키를 정수로 반올림하여 같은 키를 같는 사람수를 구하는 경우---

SELECT round(Height), count(*)
FROM   Person
GROUP  BY 1;   ###  여기서1은 조회되는 첫번째 컬럼을 의미

###---  Havling 절은 그룹의 조건을 지정시 ----------------------------

SELECT round(Height), count(*)
FROM   Person
GROUP  BY round(Height)
HAVING count(*) > 1;    ### 두 명 이상인 경우, 즉 count(*)가 2 이상인 경우만 조회

 

 

자주 사용하는 식 : 편차 구하기, 분산 구하기, 표준편차 구하기

 

 

###------  편차 ----------편차(deviation)는 관측값에서 평균 또는 중앙값을 뺀 것이다.
SELECT
    Name AS 이름,
    Height AS 키,
    avg(Height) over () AS 평균,
    round(Height - avg(Height) over (), 3) AS 편차
FROM Person



###---- 분산 ------------편차의 제곱값에 대한 평균

SELECT avg(편차*편차) AS 분산
FROM (
    SELECT
        round(avg(Height) over () - height, 3) AS 편차
    FROM Person
)


###---- 표준 편차(standard deviation)는 분산을 제곱근한 것

###----  over ()는 SQLite 3.25 버전 이후에서 작동

 

 

자주 사용하는 식 : Sqlite 버전 확인하기

 

 

select sqlite_version();

 

 

자주 사용하는 식 : Union, Union All

 

 

테이블의 내용이나 이름이 똑같지 않더라도 구조가 같다면 Union으로 데이터를 합칠 수 있습니다

UNION과 비슷하되 중복 레코드를 제거하지 않는 UNION ALL도 있습니다.

 

차이점은 UNION은 질의 결과에서 중복을 제거하기 위해 먼저 정렬을 수행하기 때문에 질의 결과가 정렬된다는 점을 들 수 있습니다.

 

select * from kospi_master 
union all
select * from kosdaq_master

 

 

자주 사용하는 식 : Inner Join

 

select * from kospi_master , code_stock
where stock_code = code_stock

### -------  위와 아래는 동일한 결과 나옴

select * from kospi_master
inner join code_stock on stock_code = code_stock

묵시적 조인과 Inner Join 조회해본결과 Inner Join이 더 빠르게 조회되었네요

 

---

https://datawith.tistory.com/5

Power BI에서 Sqlite3 연결방법 (ODBC DSN설정)

---

다음은 파이썬에서 Sqlite 사용하는 예제들 입니다.

https://datawith.tistory.com/83

파이썬 Python 에서 Sqlite3 Query 사용하기

---