[SQL] SQL 기초

SQL의 특징#

  • 대소문자를 구분하지 않는다.
  • 관용적으로 키워드는 대문자를 이용한다.
  • …(추가 예정)
-- student table
-- num	name	age	address
-- 100	홍길동	20	서울
-- 101	김길동	30	송파구
-- 102	최길동	40	제주
-- 103	임길동		인천

-- Table 생성

CREATE TABLE student (
    num     VARCHAR(10)  PRIMARY KEY,
    name    VARCHAR(20)  NOT NULL,
    age     INT          ,
    address VARCHAR(30)  NOT NULL    
) DEFAULT CHARACTER SET utf8;   


-- Table 삭제    
DROP TABLE student;

-- Record 입력(1행 데이터를 입력해 보아요!)

INSERT INTO student(num,name,age,address) VALUES('100', '홍길동', 20, '서울');
INSERT INTO student VALUES('101', '김길동', 30, '송파구');
INSERT INTO student VALUES('102', '최길동', 40, '제주');
INSERT INTO student VALUES('103', '임길동', NULL, '인천');  -- 가능
INSERT INTO student(num,name,address) VALUES('103', '임길동', '인천');  -- 가능

-- Record 삭제
DELETE FROM student;
DELETE FROM student WHERE num='102';

-- Record Update
UPDATE student SET name='김연아' WHERE num='100'; 

-- CRUD(Create, Read, Update, Delete )

-- Record Select
SELECT num,name FROM student WHERE num='100';

-- Transaction 처리때문에 이와 같은 과정을 거치는거예요!
COMMIT;

Transaction#

SQL 작업에 대해서 최소 단위이다.

은행의 이체작업을 예시로 설명해보자

A의 통장에서 B의 통장으로 2000원을 이체

  1. A의 통장에 2000원이 있는지를 확인(SELECT)
  2. B의 통장 계좌가 정상 계좌인지 확인(SELECT)
  3. A의 통장 계좌에서 2000원을 차감(UPDATE) 전기가 나갔어요!
  4. B의 통장에 잔액을 2000원 증가(UPDATE)

DBMS의 가장 큰 특지이자 우리가 돈을 주고 사는 이유 중 하나는 Transaction을 걸면(설정하면) DBMS가 ACID 특성을 Transaction에게 부여해준다.

ACID

  • Atomicity (원자성)
  • Consistency (일치성)
  • Isolation (독립성)
  • Durability (영구성)

Transaction을 이용하려면 AutoCommit mode를 False(off)를 한 상태에서 작업을 한 뒤 Commit 해서 적용하거나 Rollback 하여 적용하면 된다.

Python에서 사용해보자#

python 프로그램으로 MySQL DBMS에 접속해서 원하는 DB에서 Data를 가져와보자

  1. 어떤 Module을 사용할지 결정(여기 예시에서는 pymysql을 이용)
  2. 해당 Module을 download 해서 설치
  • pip라는 Module을 통해 설치가 가능하다. 아니면 anaconda 환경에서는 conda를 이용하여 설치가 가능하다.
  1. pymysql 설치
  • conda install pymysql or pip install pymysql
  • jupyter notebook 이나 visual studio 등에서 앞에 !를 붙여서 prompt 명령어를 실행할 수 있다

python에서 사용 예제#

아래 예제에서 사용된 DB는 여러 책의 정보를 가지고 있는 DB이다. 생성된 Table 형태는 다음과 같다.

CREATE TABLE `book` (
  `bisbn` varchar(50) NOT NULL,
  `btitle` varchar(200) DEFAULT NULL,
  `bdate` varchar(20) DEFAULT NULL,
  `bpage` int(11) DEFAULT NULL,
  `bprice` int(11) DEFAULT NULL,
  `bauthor` varchar(100) DEFAULT NULL,
  `btranslator` varchar(100) DEFAULT NULL,
  `bsupplement` varchar(100) DEFAULT NULL,
  `bpublisher` varchar(100) DEFAULT NULL,
  `bimgurl` varchar(100) DEFAULT NULL,
  `bimgbase64` mediumtext,
  PRIMARY KEY (`bisbn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 필요한 module import
import pymysql

# 데이터베이스 connection이 필요!
# 내가 사용하려는 Database에 대한 논리적인 연결 객체가 필요.

# 당연히 접속에 대한 정보를 인자로 넘겨줘야 해요!
conn = pymysql.connect(host='localhost',
                       db='library',
                       user='python',
                       password='python',
                       charset='utf8')

# 접속이 성공하면 instance가 생성되고 그렇지 않으면 None이 들어와요!
print(conn)

# 접속이 성공하면 해당 instance를 이용해서 Query를 실행할 수 있어요!
# cursor 객체를 생성해야 해요!
#cursor = conn.cursor()  # 이렇게 만들면 나중에 결과데이터가 tuple로 나와요!
cursor = conn.cursor(pymysql.cursors.DictCursor)

# SQL구문을 만들어야 해요!
sql = 'SELECT btitle, bauthor, bprice FROM book WHERE bprice > 50000;'

# 생성한 SQL 문장을 cursor를 통해서 실행시켜요!
cursor.execute(sql)

# fetch계열의 함수를 이용해서 결과를 cursor로 부터 가져와요!
result = cursor.fetchall()

# print(result)

for bookDict in result:
    print('제목 : {}, 저자 : {}'.format(bookDict['btitle'],bookDict['bauthor']))


# 키워드를 입력받아서 해당 키워드를 책 제목에 포함하는 책을 검색한 후
# 책의 제목과 저자를 출력하세요!

search_keyword = input("검색키워드 : ")

sql = "SELECT btitle, bauthor FROM book WHERE btitle LIKE '%{}%'".format(search_keyword)

# 와일드카드 문자 %는 0개 이상의 문자를 의미해요!

cursor.execute(sql)

result = cursor.fetchall()

for bookDict in result:
    print('제목 : {}, 저자 : {}'.format(bookDict['btitle'],bookDict['bauthor']))