SQLite는 독립적인 파일 기반 데이터베이스로, SQLite 명령줄 도구 sqlite3.exe를 통해 데이터베이스 파일(exture3_0_group_a_file.db)을 쉽게 핸들링할 수 있습니다.
다음은 sqlite3.exe를 사용하여 exture3_0_group_a_file.db 파일을 관리하는 방법에 대한 단계별 가이드입니다:

1. sqlite3.exe 실행

  • SQLite 명령줄 도구를 실행합니다.

또는 특정 데이터베이스 파일과 함께 실행합니다:

 

결과: SQLite 명령 프롬프트가 표시됩니다.

2. 데이터베이스 연결

SQLite가 실행된 상태에서 데이터베이스 파일을 연결하려면 다음 명령을 사용합니다:

.open exture3_0_group_a_file.db

성공적으로 연결되면 데이터베이스 파일이 열립니다.

3. 테이블 확인

데이터베이스의 테이블 목록을 확인하려면:

.tables

결과: 데이터베이스에 존재하는 모든 테이블 목록이 출력됩니다.

4. 테이블 구조 확인

특정 테이블의 구조를 확인하려면:

.schema 테이블명

5. 데이터 조회

테이블에서 데이터를 조회하려면 SQL 쿼리를 사용합니다:

SELECT * FROM 테이블명;

6. 데이터 삽입

데이터 삽입도 SQL 명령을 통해 가능합니다:

INSERT INTO 테이블명 (컬럼1, 컬럼2) VALUES ('값1', '값2');

7. 데이터 수정

데이터를 수정하려면:

UPDATE 테이블명 SET 컬럼명 = '새로운값' WHERE 조건;

8. 데이터 삭제

데이터를 삭제하려면:

DELETE FROM 테이블명 WHERE 조건;

9. SQLite 명령 사용

SQLite 도구 자체 명령은 .로 시작합니다:

  • 도움말 확인:
     
    .help
  • 데이터 저장 및 닫기:
     
    .exit

10. 스크립트 실행

SQL 파일을 실행하여 작업하려면:

.read script.sql

예: script.sql 파일에 SQL 명령이 포함되어 있으면 해당 명령이 실행됩니다.

예제

샘플 테이블 생성 및 데이터 관리

  1. 테이블 생성:
  2. sql
    CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER);
  3. 데이터 삽입:
  4. sql
    INSERT INTO users (name, age) VALUES ('Alice', 30);
  5. 데이터 조회:
  6. sql
    SELECT * FROM users;
  7. 데이터베이스 닫기:
  8. sql
    .exit

추가 참고

  • SQLite는 SQL 표준에 가까운 쿼리를 사용하므로, SQL 문법에 익숙하다면 쉽게 다룰 수 있습니다.
  • 데이터베이스 파일(aa.db)은 복사만으로 다른 환경에서도 사용할 수 있습니다.


실제활용예)
C:\Users\B210145_BK\Downloads\StockDesignNodeServer\redis_web_5_6_ticker\multiloginredistickserver>sqlite3
SQLite version 3.47.1 2024-11-25 12:07:48
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open exture3_0_group_a_file.db
sqlite> .tables
EXTUREPLUSFUTUREINVESTORINDEX
EXTUREPLUSFUTUREMASTER
EXTUREPLUSFUTURETRADE
EXTUREPLUSFUTURPRIORITYQUOTE
EXTUREPLUSKOSPIVOLATILITYINTERRUPTIONPERINSTRUMENT
EXTUREPLUSSTOCKFOREIGNERMASTER
EXTUREPLUSSTOCKJISUMASTER
EXTUREPLUSSTOCKMARKETOPERATIONSCHEDULEDISCLOSURE
EXTUREPLUSSTOCKMARKETOPERATIONTS
EXTUREPLUSSTOCKMASTER
EXTUREPLUSSTOCKPROGRAMTRADE
EXTUREPLUSSTOCKQUOTE10
EXTUREPLUSSTOCKSECURITIESTRADE
EXTUREPLUSSTOCKTOTALINVESTORINDEX
EXTUREPLUSSTOCKTRADE
EXTUREPLUSSTOCKUPDOWN
EXTUREPLUSTOTALSTOCKINDEX
EXTURESTOCKTOTALBULKTRADE
SECURITIESTRADE
sqlite> .schema SECURITIESTRADE
CREATE TABLE [SECURITIESTRADE] (
        [DATA_CATEGORY] VARCHAR2(2),
        [INFOMARKETCLASS] VARCHAR2(3),
        [STOCKCODE] VARCHAR2(12),
        [A_DESIGNATED_NUMBER_FOR_AN_ISSUE] VARCHAR2(6),
        [MEMBER_NUMBER_1_FOR_ASK] VARCHAR2(5),
        [ASK_TRADING_VOLUME_1] VARCHAR2(12),
        [ASK_TRADING_VALUE_1] VARCHAR2(22),
        [MEMBER_NUMBER_1_FOR_BID] VARCHAR2(5),
        [BID_TRADING_VOLUME_1] VARCHAR2(12),
        [BID_TRADING_VALUE_1] VARCHAR2(22),
        [MEMBER_NUMBER_2_FOR_ASK] VARCHAR2(5),
        [ASK_TRADING_VOLUME_2] VARCHAR2(12),
        [ASK_TRADING_VALUE_2] VARCHAR2(22),
        [MEMBER_NUMBER_2_FOR_BID] VARCHAR2(5),
        [BID_TRADING_VOLUME_2] VARCHAR2(12),
        [BID_TRADING_VALUE_2] VARCHAR2(22),
        [MEMBER_NUMBER_3_FOR_ASK] VARCHAR2(5),
        [ASK_TRADING_VOLUME_3] VARCHAR2(12),
        [ASK_TRADING_VALUE_3] VARCHAR2(22),
        [MEMBER_NUMBER_3_FOR_BID] VARCHAR2(5),
        [BID_TRADING_VOLUME_3] VARCHAR2(12),
        [BID_TRADING_VALUE_3] VARCHAR2(22),
        [MEMBER_NUMBER_4_FOR_ASK] VARCHAR2(5),
        [ASK_TRADING_VOLUME_4] VARCHAR2(12),
        [ASK_TRADING_VALUE_4] VARCHAR2(22),
        [MEMBER_NUMBER_4_FOR_BID] VARCHAR2(5),
        [BID_TRADING_VOLUME_4] VARCHAR2(12),
        [BID_TRADING_VALUE_4] VARCHAR2(22),
        [MEMBER_NUMBER_5_FOR_ASK] VARCHAR2(5),
        [ASK_TRADING_VOLUME_5] VARCHAR2(12),
        [ASK_TRADING_VALUE_5] VARCHAR2(22),
        [MEMBER_NUMBER_5_FOR_BID] VARCHAR2(5),
        [BID_TRADING_VOLUME_5] VARCHAR2(12),
        [BID_TRADING_VALUE_5] VARCHAR2(22),
        [END_KEYWORD] VARCHAR2(1)
);
sqlite> SELECT * FROM EXTUREPLUSSTOCKTRADE WHERE STOCKCODE LIKE '%005930%';
A3|01S|        |G1|40|KR7005930003|000379|090016340616|2|00000000600|00000065900|0000000014|00000065900|00000066000|00000065800|000000740706|000000048808907100.000|1|000000000000000|00000066000|00000065900|NULL
A3|01S|        |G1|40|KR7005930003|000379|090025123774|2|00000000600|00000065900|0000000002|00000065900|00000066000|00000065800|000000760056|000000050084680100.000|1|000000000000000|00000066000|00000065900|NULL
A3|01S|        |G1|40|KR7005930003|000379|090032461321|2|00000000600|00000065900|0000000300|00000065900|00000066000|00000065800|000000776391|000000051161505100.000|1|000000000000000|00000066000|00000065900|NULL
A3|01S|        |G1|40|KR7005930003|000379|090042478696|2|00000000600|00000065900|0000000500|00000065900|00000066000|00000065800|000000848910|000000055939747800.000|2|000000000000000|00000065900|00000065800|NULL
A3|01S|        |G1|40|KR7005930003|000379|090051008868|2|00000000400|00000065700|0000000010|00000065900|00000066000|00000065700|000000906933|000000059757225500.000|1|000000000000000|00000065800|00000065700|NULL
A3|01S|        |G1|40|KR7005930003|000379|090106260640|2|00000000500|00000065800|0000000840|00000065900|00000066000|00000065700|000000917944|000000060480855400.000|2|000000000000000|00000065800|00000065700|NULL
A3|01S|        |G1|40|KR7005930003|000379|090116920780|2|00000000400|00000065700|0000000001|00000065900|00000066000|00000065700|000001023081|000000067388878900.000|1|000000000000000|00000065800|00000065700|NULL
A3|01S|        |G1|40|KR7005930003|000379|090128579478|2|00000000500|00000065800|0000000070|00000065900|00000066000|00000065700|000001092332|000000071941374800.000|1|000000000000000|00000065900|00000065800|NULL
A3|01S|        |G1|40|KR7005930003|000379|090141812818|2|00000000500|00000065800|0000000005|00000065900|00000066000|00000065700|000001104001|000000072709240100.000|1|000000000000000|00000065900|00000065800|NULL
A3|01S|        |G1|40|KR7005930003|000379|090159102608|2|00000000400|00000065700|0000000001|00000065900|00000066000|00000065700|000001215512|000000080046464600.000|1|000000000000000|00000065800|00000065700|NULL
A3|01S|        |G1|40|KR7005930003|000379|090203148634|2|00000000300|00000065600|0000000001|00000065900|00000066000|00000065600|000001311321|000000086341981200.000|1|000000000000000|00000065700|00000065600|NULL
A3|01S|        |G1|40|KR7005930003|000379|090220102040|2|00000000500|00000065800|0000000030|00000065900|00000066000|00000065600|000001355636|000000089254054400.000|2|000000000000000|00000065800|00000065700|NULL
A3|01S|        |G1|40|KR7005930003|000379|090234155211|2|00000000500|00000065800|0000000303|00000065900|00000066000|00000065600|000001378418|000000090751086200.000|2|000000000000000|00000065800|00000065700|NULL
A3|01S|        |G1|40|KR7005930003|000379|090249967609|2|00000000400|00000065700|0000000002|00000065900|00000066000|00000065600|000001460248|000000096127749300.000|1|000000000000000|00000065800|00000065700|NULL
A3|01S|        |G1|40|KR7005930003|000379|090308108824|2|00000000400|00000065700|0000000002|00000065900|00000066000|00000065600|000001467995|000000096636862100.000|1|000000000000000|00000065800|00000065700|NULL
A3|01S|        |G1|40|KR7005930003|000379|090326000851|2|00000000500|00000065800|0000000001|00000065900|00000066000|00000065600|000001473722|000000097013567900.000|2|000000000000000|00000065800|00000065700|NULL
A3|01S|        |G1|40|KR7005930003|000379|090343621379|2|00000000400|00000065700|0000000029|00000065900|00000066000|00000065600|000001480597|000000097465600800.000|1|000000000000000|00000065800|00000065700|NULL
A3|01S|        |G1|40|KR7005930003|000379|090400381512|2|00000000500|00000065800|0000000391|00000065900|00000066000|00000065600|000001487021|000000097888010900.000|2|000000000000000|00000065800|00000065700|NULL
A3|01S|        |G1|40|KR7005930003|000379|090415057289|2|00000000400|00000065700|0000000101|00000065900|00000066000|00000065600|000001494009|000000098347463700.000|1|000000000000000|00000065800|00000065700|NULL
A3|01S|        |G1|40|KR7005930003|000379|090423000680|2|00000000400|00000065700|0000000005|00000065900|00000066000|00000065600|000001576270|000000103752684500.000|1|000000000000000|00000000000|00000000000|NULL
A3|01S|        |G1|40|KR7005930003|000379|090423001705|2|00000000400|00000065700|0000000001|00000065900|00000066000|00000065600|000001580913|000000104057729600.000|1|000000000000000|00000000000|00000000000|NULL
A3|01S|        |G1|40|KR7005930003|000379|090427244354|2|00000000400|00000065700|0000000500|00000065900|00000066000|00000065600|000001647503|000000108432821900.000|1|000000000000000|00000065800|00000065700|NULL
A3|01S|        |G1|40|KR7005930003|000379|090441886146|2|00000000500|00000065800|0000000001|00000065900|00000066000|00000065600|000001764679|000000116143106600.000|1|000000000000000|00000065900|00000065800|NULL
A3|01S|        |G1|40|KR7005930003|000379|090502252835|2|00000000600|00000065900|0000000001|00000065900|00000066000|00000065600|000001811710|000000119237947600.000|2|000000000000000|00000065900|00000065800|NULL
A3|01S|        |G1|40|KR7005930003|000379|090512180351|2|00000000500|00000065800|0000000034|00000065900|00000066000|00000065600|000001899118|000000124989388400.000|1|000000000000000|00000065900|00000065800|NULL
A3|01S|        |G1|40|KR7005930003|000379|090528606862|2|00000000500|00000065800|0000000080|00000065900|00000066000|00000065600|000001997391|000000131455846300.000|1|000000000000000|00000065900|00000065800|NULL
A3|01S|        |G1|40|KR7005930003|000379|090539160461|2|00000000500|00000065800|0000000003|00000065900|00000066000|00000065600|000002082407|000000137049917400.000|1|000000000000000|00000065900|00000065800|NULL
A3|01S|        |G1|40|KR7005930003|000379|090556611200|2|00000000500|00000065800|0000000005|00000065900|00000066000|00000065600|000002085664|000000137264298800.000|1|000000000000000|00000065900|00000065800|NULL
A3|01S|        |G1|40|KR7005930003|000379|090602188802|2|00000000500|00000065800|0000010414|00000065900|00000066000|00000065600|000002205154|000000145126765700.000|2|000000000000000|00000065800|00000065700|NULL
A3|01S|        |G1|40|KR7005930003|000379|090625239029|2|00000000500|00000065800|0000000140|00000065900|00000066000|00000065600|000002211624|000000145552251700.000|2|000000000000000|00000065800|00000065700|NULL
A3|01S|        |G1|40|KR7005930003|000379|090645718364|2|00000000500|00000065800|0000000014|00000065900|00000066000|00000065600|000002214961|000000145771622700.000|2|000000000000000|00000065800|00000065700|NULL
A3|01S|        |G1|40|KR7005930003|000379|090703209584|2|00000000400|00000065700|0000000027|00000065900|00000066000|00000065600|000002221642|000000146210758000.000|1|000000000000000|00000065800|00000065700|NULL
A3|01S|        |G1|40|KR7005930003|000379|090722715549|2|00000000400|00000065700|0000000010|00000065900|00000066000|00000065600|000002227671|000000146607253900.000|1|000000000000000|00000065800|00000065700|NULL
A3|01S|        |G1|40|KR7005930003|000379|090732035363|2|00000000400|00000065700|0000006159|00000065900|00000066000|00000065600|000002295791|000000151083337900.000|1|000000000000000|00000000000|00000000000|NULL
A3|01S|        |G1|40|KR7005930003|000379|090745655162|2|00000000300|00000065600|0000000011|00000065900|00000066000|00000065600|000002343441|000000154213744500.000|1|000000000000000|00000065700|00000065600|NULL
A3|01S|        |G1|40|KR7005930003|000379|090812088869|2|00000000300|00000065600|0000000005|00000065900|00000066000|00000065600|000002352796|000000154827633700.000|1|000000000000000|00000065700|00000065600|NULL
A3|01S|        |G1|40|KR7005930003|000379|090835813027|2|00000000200|00000065500|0000000038|00000065900|00000066000|00000065500|000002484408|000000163461117500.000|1|000000000000000|00000065600|00000065500|NULL
A3|01S|        |G1|40|KR7005930003|000379|090858671239|2|00000000200|00000065500|0000000010|00000065900|00000066000|00000065400|000002637593|000000173494693400.000|1|000000000000000|00000065600|00000065500|NULL
A3|01S|        |G1|40|KR7005930003|000379|090927535167|2|00000000300|00000065600|0000000006|00000065900|00000066000|00000065400|000002666334|000000175377323300.000|2|000000000000000|00000065600|00000065500|NULL
A3|01S|        |G1|40|KR7005930003|000379|091001251410|2|00000000300|00000065600|0000000100|00000065900|00000066000|00000065400|000002671009|000000175683819400.000|2|000000000000000|00000065600|00000065500|NULL
A3|01S|        |G1|40|KR7005930003|000379|091029485349|2|00000000200|00000065500|0000000005|00000065900|00000066000|00000065400|000002682855|000000176459947500.000|1|000000000000000|00000065600|00000065500|NULL
A3|01S|        |G1|40|KR7005930003|000379|091054365941|2|00000000200|00000065500|0000000005|00000065900|00000066000|00000065400|000002694349|000000177212927500.000|1|000000000000000|00000065600|00000065500|NULL
A3|01S|        |G1|40|KR7005930003|000379|091056697133|2|00000000200|00000065500|0000000002|00000065900|00000066000|00000065400|000002766420|000000181933578000.000|1|000000000000000|00000065600|00000065500|NULL
A3|01S|        |G1|40|KR7005930003|000379|091126171772|2|00000000200|00000065500|0000000002|00000065900|00000066000|00000065400|000002820049|000000185446154600.000|2|000000000000000|00000065500|00000065400|NULL
A3|01S|        |G1|40|KR7005930003|000379|091156752631|2|00000000200|00000065500|0000000030|00000065900|00000066000|00000065400|000002850973|000000187471537300.000|1|000000000000000|00000065600|00000065500|NULL
A3|01S|        |G1|40|KR7005930003|000379|091223205147|2|00000000300|00000065600|0000000049|00000065900|00000066000|00000065400|000002933690|000000192897266600.000|1|000000000000000|00000065700|00000065600|NULL
A3|01S|        |G1|40|KR7005930003|000379|091253459756|2|00000000300|00000065600|0000000003|00000065900|00000066000|00000065400|000002993560|000000196824606100.000|2|000000000000000|00000065600|00000065500|NULL
A3|01S|        |G1|40|KR7005930003|000379|091327722198|2|00000000300|00000065600|0000000015|00000065900|00000066000|00000065400|000003000500|000000197279783800.000|2|000000000000000|00000065600|00000065500|NULL
A3|01S|        |G1|40|KR7005930003|000379|091358826380|2|00000000200|00000065500|0000000001|00000065900|00000066000|00000065400|000003086095|000000202886442000.000|1|000000000000000|00000000000|00000000000|NULL
A3|01S|        |G1|40|KR7005930003|000379|091358827630|2|00000000200|00000065500|0000000005|00000065900|00000066000|00000065400|000003106065|000000204194477000.000|1|000000000000000|00000000000|00000000000|NULL
sqlite>


저작자표시

'java 언어 > Node.Server' 카테고리의 다른 글

유사 종목명에 대한 종목코드 처리(팝업이용)  (0) 2024.12.10
Dynamic HTML(데이타를 출력할때에 동적으로 HTML을 생성한다.)  (0) 2024.12.05
Redis.Tick.Server 1개 + Node 조회 Server(포트별로 다르게)  (0) 2024.12.02
SQLite에 저장되어진, 10단계 호가정보(B601S)의 Top 10을 가져와서 출력한다.  (0) 2024.11.28
웹페이지에서 종목코드 호출시에 HTML전체를 서버에서 완성해서 Response 하는 형태  (0) 2024.11.28

+ Recent posts

티스토리툴바