ABOUT ME

-

Today
-
Yesterday
-
Total
-
  • SQL 기초
    SQL/공부 2019. 7. 25. 17:28

    Database SQL

     

    • RDBMS(관계형 데이터베이스 관리 시스템)

    관계형 모델을 기반으로하는 데이터베이스 관리시스템이다.

     

    • SQLite

    서버가 아닌 응용 프로그램에 넣어 사용하는 비교적 가벼운 데이터베이스이다.

     

    기본 용어 정리

    • 스키마(scheme)

    데이터베이스에서 자료의 구조, 표현방법, 관계 등을 정의한 구조

    데이터베이스의 구조와 제약 조건에 관련한 전반적인 명세를 기술한 것

    column datatype
    id INT
    age INT
    phone TEXT
    • 테이블
    • 열(Column)

    각 열에는 고유한 데이터 형식이 지정된다.

    INTEGER, TEXT, NULL 등

    -> 세로

     

    • 행(row), 레코드

    테이블의 데이터는 행에 저장된다.

    예) user 테이블에 4명의 고객정보가 저장되어 있으며, 행은 4개가 존재한다

     

    • PK(기본키)

    각 행(레코드)의 고유값으로 Primary Key로 불린다.

    반드시 설정하여야하며, 데이터베이스 관리 및 관계 설정 시 주요하게 활용된다.

     

    1. SQL 개념

    SQL

    관계형 데이터베이스 관리시스템(RDBMS)의 데이터를 관리하기 위해 설계된 특수 목적의 프로그래밍 언어이다.

    관계형 데이터베이스 관리 시스템에서 자료의 검색과 관리

    데이터베이스 스키마 생성과 수정, 데이터베이스 객체 접근 조정 관리를 위해 고안되었다.

     

    SQL 종류

    DDL

    • 데이터 정의 언어
    • 개념

    데이터를 정의하기 위한 언어이다.

    관계형 데이터베이스 구조(테이블, 스키마)를 정의하기 위한 명령어이다.

    • 예시

    CREATE, DROP, ALTER

     

    DML

    • 데이터 조작 언어
    • 개념

    데이터를 저장, 수정, 삭제, 조회 등을 하기 위한 언어이다.

    • 예시

    INSERT, UPDATE, DELETE, SELECT

     

    DCL

    • 데이터 제어 언어
    • 개념

    데이터베이스 사용자의 권한 제어를 위해 사용되는 언어이다.

    • 예시

    GRANT, REVOKE, COMMIT, ROLLBACK

     

    2. Hello, DB!

     

    $ sqlite3
    

     

    sqlite> SELECT * FROM students;
    

     

    • 키워드(SELECT문)

    SELECT문은 데이터베이스에서 특정한 테이블을 반환한다.

     

     

    3. DB, Table 생성

    1. database 생성

    $ sqlite> .databases
    

    해당하는 데이터베이스 파일을 만들고 sqlite에서 확인해볼 수 있다.

     

    2. Table 생성

    sqlite> CREATE TABLE classmates (
    id INT PRIMARY KEY,
    name TEXT
    );
    

     

    2-1 Datatype

    SQLite은 동적 데이터 타입으로, 기본적으로 affinity에 맞게 들어간다.

    BOOLEAN은 정수 0, 1 으로 저장된다.

     

    Affinity  
    INTEGER TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, UNSIGNED BIG INT
    TEXT CHARACTER, VARCHAR, TEXT
    REAL REAL, DOUBLE, FLOAT
    NUMERIC NUMERIC, DECIMAL, DATE, DATETIME
    BLOB no datatype specified

     

    테이블 목록 조회

    .tables
    
    .tables
    

     

    특정 테이블 스키마 조회

    .schema table
    
    .schema classmates
    

     

    특정 table 삭제

    DROP TABLE table
    
    DROP TABLE classmates;
    

     

    classmate 테이블 만들기

    column datatype
    id INT
    name TEXT
    age INT
    address TEXT

     

    sqlite> CREATE TABLE classmates (
    id INT PRIMARY KEY,
    name TEXT,
    age INT,
    address TEXT
    );
    

     

    데이터 추가, 읽기, 수정, 삭제

     

    1. data 추가(INSERT)

    특정 table에 새로운 행을 추가하여 데이터를 추가할 수 있습니다.

    INSERT INTO table (column1, column2, ..)
    VALUES (value1, value2, ...)
    

     

    • classmates 테이블에 이름이 홍길동이고 나이가 23인 데이터를 넣어봅시다.
    sqlite> INSERT INTO classmates (name, age)
     ...> VALUES('홍길동', 23);
    

     

    • classmates 테이블에 id가 2이고, 이름이 홍길동이고, 나이가 30이고, 주소가 서울인 데이터를 넣어봅시다.
    sqlite> INSERT INTO classmates VALUES (2,'홍길동',50,'서울')
    

     

    • 모든 열에 데이터를 넣을 때에는 column을 명시할 필요가 없습니다.
    INSERT INTO table VALUES (value1, value2, ...)
    

     

    • 주소가 꼭 필요한 정보라면 공백으로 비워두면 안된다.

     

    • id는 Primary Key 이므로 반드시 필요하며, 값이 저장되면 자동으로 증가할도록 한다(unique)

     

    sqlite> DROP TABLE classmates;
    sqlite> CREATE TABLE classmates (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INT NOT NULL,
    address TEXT NOT NULL
    ); 
    

     

    • TABLE 설정 변경
    sqlite> INSERT INTO classmates (name, age)
     ...> VALUES ('홍길동', 23);
     
    Error: NOT NULL constraint failed: classmates.address
    

     

    2. data 가져오기(SELECT)

     

    SELECT * FROM table
    

     

    • 특정한 table에서 특정 Column만 가져오기
    SELECT column1, column2, FROM table
    

     

    • classmates에서 id, name column 값만 가져온다면
    sqlite> SELECT id, name from classmates;
    

     

    • 몇 개만 가져오기
    SELECT column1, column2, FROM table LIMIT num
    

     

    • classmates에서 id, name column 값 중에 하나만 가져온다면
    sqlite> SELECT id, name FROM classmates LIMIT 1;
    

     

    • 위에서 부터 몇개만 가져오기
    SELECT column 1, column2 FROM table LIMIT num OFFSET num
    

     

    • classmates에서 id, name column 값 중에 세번째에 있는 값 하나만 가져온다면
    SELECT id, name FROM classmates LIMIT 1 OFFSET 2;
    

     

    • 특정한 값만 가져오기
    SELECT column1, column2, FROM table WHERE column=value
    

     

    • 주소가 서울인 사람만 가져온다면
    SELECT id, name FROM classmates WHERE address='서울';
    

     

    3. data 삭제(DELETE)

     

    • 특정 table에 특정한 레코드를 삭제할 수 있습니다.
    DELETE FROM table WHERE condition 
    

     

    • classmates 테이블에 id가 3인 레코드를 삭제해봅시다
    DELETE FROM classmates WHERE id=3;
    

     

    4. data 수정(UPDATE)

     

    • 특정 table에 특정한 레코드를 수정할 수 있습니다.
    UPDATE table SET column1=value1, column2=value2, ... 
    WHERE condition
    

     

    • classmates 테이블에 id가 4인 레코드를 수정해봅시다
    • 이름을 홍길동으로, 주소를 제주도로
    UPDATE classmates SET name='홍길동', address='제주도' WHERE id=4;
    

     

    데이터 추가, 읽기, 수정, 삭제 정리

     

      구문 예시
    C INSERT INSERT INTO classmates (name, age, address) VALUES ('홍길동', '30', '서울')
    R SELECT SELECT * FROM classmates WHERE id=1;
    U UPDATE UPDATE classmates SET name="철수" WHERE id=1;
    D DELETE DELETE FROM classmates WHERE id=1;

     

    5. WHERE, expression

    1. WHERE 심화

    • 특정한 table에서 특정 조건의 Column만 가져오기
    SELECT * FROM table WHERE condition
    

     

    • users에서 age가 30이상인 사람만 가져온다면
    SELECT * FROM users WHERE age>=30;
    

     

    • users에서 age가 30 이상인 사람의 이름만 가져온다면
    sqlite> SELECT first_name FROM users WHERE age>=30;
    

     

    • users에서 age가 30이상이고 성이 김인 사람의 성과 나이만 가져온다면
    sqlite> SELECT age, last_name FROM users WHERE age>=30 and last_name="김"; 
    

     

    2. Expression

     

    • 다음의 표현식은 레코드의 갯수를 반환한다.
    SELECT COUNT(column) FROM table
    

     

    • users의 총 갯수는
    sqlite> SELECT COUNT(*) FROM users;
    

     

    • 다음의 표현식은 기본적으로 숫자(INTEGER)일때만 가능하다.
    • AVG(), SUM(), MIN(), MAX()
    SELECT AVG(column) FROM table
    

     

    • users에서 계좌 잔액(balance)이 가장 높은 사람과 액수는
    sqlite> SELECT first_name, MAX(balance) FROM users;
    

     

    • users에서 30살 이상인 사람의 계좌 평균 잔액은
    sqlite> SELECT AVG(balance) FROM users WHERE age>=30;
    

     

    3. LIKE

     

    • 정확한 값에 대한 비교가 아닌, 패턴을 확인하여 해당하는 값을 반환한다.
    SELECT * FROM table WHERE column LIKE ' '
    
    % 2% 2로 시작하는 값
      %2 2로 끝나는 값
      %2% 2가 들어가는 값
    _ _2% 아무값이나 들어가고 두번째가 2로 시작하는 값
      1___ 1로 시작하고 4자리인 값
      2 _ % _ % 2로 시작하고 적어도 3자리인 값

     

    • users에서 20대인 사람의 테이블은
    sqlite> SELECT * FROM users WHERE age LIKE '2%';
    

     

    6. ORDER

     

    1. 정렬(ORDER)

    SELECT columns FROM table ORDER BY column1, column2 [ASC/DESC]
    

     

    • users에서 나이순으로 오름차순 정렬하여 상위 10개만 뽑아보면
    sqlite> SELECT * FROM users ORDER BY age ASC LIMIT 10;
    

     

    • users에서 나이순, 성 순으로 오름차순 정렬하여 상위 10개만 뽑아보면
    sqlite> SELECT * FROM users ORDER BY age, last_name ASC LIMIT 10;
    

     

    • useres에서 계좌잔액 순으로 내림차순 정렬하여 해당하는 사람이름 10개만 뽑아보면
    sqlite> SELECT first_name, last_name FROM users ORDER BY balance DESC LIMIT 10;
    

     

    댓글

© 2019 All rights reserved.