OSX에서 MySQL 설치하기, RMySQL 패키지 사용하기, 간단한 SQL 문법

2016/06/29

MySQL 설치하기

다음 명령어를 터미널에서 입력합니다.

$ brew install mysql

다음과 같은 결과가 나오게 됩니다. mysql_secure_installation, mysql -uroot, mysql.server start의 세 명령어가 눈에 들어오는군요.

==> Downloading https://homebrew.bintray.com/bottles/mysql-5.7.13.el_capitan.bot
######################################################################## 100.0%
==> Pouring mysql-5.7.13.el_capitan.bottle.tar.gz
==> /usr/local/Cellar/mysql/5.7.13/bin/mysqld --initialize-insecure --user=Sungp
==> Caveats
We've installed your MySQL database without a root password. To secure it run:
    mysql_secure_installation

To connect run:
    mysql -uroot

To have launchd start mysql now and restart at login:
  brew services start mysql
Or, if you don't want/need a background service you can just run:
  mysql.server start
==> Summary
🍺  /usr/local/Cellar/mysql/5.7.13: 13,344 files, 445.0M

서버를 시작하게 됩니다. 성공!

$ mysql.server start
Starting MySQL
... SUCCESS!
$ mysql -uroot

uroot권한으로 접속하여 mysql창에서 DB를 생성하고 아이디와 비밀번호를 생성합니다.

mysql> create database CJDate7e;
mysql> grant all privileges on CJDate7e.* to shan@localhost identified by 'asan1234';
mysql> \q

이제 Terminal 창에서 shan으로 DB에 접속합니다.

$ mysql -u shan -p
mysql> use CJDate7e;

자, 이제 모든 준비가 끝났습니다. 이제 r에서 접속해보겠습니다.

library(RMySQL)
MyDB = dbConnect(RMySQL::MySQL(), dbname = "CJDate7e", user="shan", password="asan1234")

다시 MySQL로 돌아와서 Table을 채워 넣겠습니다. (C.J. Date Intro to DB Systems, 7e)

DROP TABLE S;
DROP TABLE P;
DROP TABLE J;
DROP TABLE SPJ;

CREATE TABLE S
(SNO VARCHAR(5) PRIMARY KEY,
SNAME VARCHAR(25) NOT NULL, STATUS INTEGER, CITY VARCHAR(20) NOT NULL);

CREATE TABLE P
(PNO VARCHAR(6) PRIMARY KEY, PNAME VARCHAR(25) NOT NULL, COLOR CHAR(10) NOT NULL, WEIGHT REAL NOT NULL, CITY VARCHAR(20) NOT NULL);

CREATE TABLE J (JNO VARCHAR(6) PRIMARY KEY, JNAME VARCHAR(25) NOT NULL, CITY VARCHAR(20) NOT NULL);

CREATE TABLE SPJ
(SNO VARCHAR(5) NOT NULL, PNO VARCHAR(6) NOT NULL, JNO VARCHAR(6) NOT NULL, QTY INTEGER NOT NULL,
CONSTRAINT SPJ01 PRIMARY KEY (SNO, PNO, JNO),
CONSTRAINT SPJ02 FOREIGN KEY (SNO) REFERENCES S(SNO),
CONSTRAINT SPJ03 FOREIGN KEY (PNO) REFERENCES P(PNO),
CONSTRAINT SPJ04 FOREIGN KEY (JNO) REFERENCES J(JNO));

DELETE FROM S;
DELETE FROM P;
DELETE FROM J;
DELETE FROM SPJ;

INSERT INTO S (SNO, SNAME, STATUS, CITY) VALUES ('S1', 'Smith', 20, 'London');
INSERT INTO S (SNO, SNAME, STATUS, CITY) VALUES ('S2', 'Jones', 10, 'Paris');
INSERT INTO S (SNO, SNAME, STATUS, CITY) VALUES ('S3', 'Blake', 30, 'Paris');
INSERT INTO S (SNO, SNAME, STATUS, CITY) VALUES ('S4', 'Clark', 20, 'London');
INSERT INTO S (SNO, SNAME, STATUS, CITY) VALUES ('S5', 'Adams', 30, 'Athens');

INSERT INTO P (PNO, PNAME, COLOR, WEIGHT, CITY) VALUES ('P1', 'Nut', 'Red', 12.0, 'London');
INSERT INTO P (PNO, PNAME, COLOR, WEIGHT, CITY) VALUES ('P2', 'Bolt', 'Green', 17.0, 'Paris');
INSERT INTO P (PNO, PNAME, COLOR, WEIGHT, CITY) VALUES ('P3', 'Screw', 'Blue', 17.0, 'Rome');
INSERT INTO P (PNO, PNAME, COLOR, WEIGHT, CITY) VALUES ('P4', 'Screw', 'Red', 14.0, 'London');
INSERT INTO P (PNO, PNAME, COLOR, WEIGHT, CITY) VALUES ('P5', 'Cam', 'Blue', 12.0, 'Paris');
INSERT INTO P (PNO, PNAME, COLOR, WEIGHT, CITY) VALUES ('P6', 'Cog', 'Red', 19.0, 'London');

INSERT INTO J (JNO, JNAME, CITY) VALUES ('J1', 'Sorter', 'Paris');
INSERT INTO J (JNO, JNAME, CITY) VALUES ('J2', 'Display', 'Rome');
INSERT INTO J (JNO, JNAME, CITY) VALUES ('J3', 'OCR', 'Athens');
INSERT INTO J (JNO, JNAME, CITY) VALUES ('J4', 'Console', 'Athens');
INSERT INTO J (JNO, JNAME, CITY) VALUES ('J5', 'RAID', 'London');
INSERT INTO J (JNO, JNAME, CITY) VALUES ('J6', 'EDS', 'Oslo');
INSERT INTO J (JNO, JNAME, CITY) VALUES ('J7', 'Tape', 'London');

INSERT INTO SPJ (SNO, PNO, JNO, QTY) VALUES ('S1', 'P1', 'J1', 200);
INSERT INTO SPJ (SNO, PNO, JNO, QTY) VALUES ('S1', 'P1', 'J4', 700);
INSERT INTO SPJ (SNO, PNO, JNO, QTY) VALUES ('S2', 'P3', 'J1', 400);
INSERT INTO SPJ (SNO, PNO, JNO, QTY) VALUES ('S2', 'P3', 'J2', 200);
INSERT INTO SPJ (SNO, PNO, JNO, QTY) VALUES ('S2', 'P3', 'J3', 200);
INSERT INTO SPJ (SNO, PNO, JNO, QTY) VALUES ('S2', 'P3', 'J4', 500);
INSERT INTO SPJ (SNO, PNO, JNO, QTY) VALUES ('S2', 'P3', 'J5', 600);
INSERT INTO SPJ (SNO, PNO, JNO, QTY) VALUES ('S2', 'P3', 'J6', 400);
INSERT INTO SPJ (SNO, PNO, JNO, QTY) VALUES ('S2', 'P3', 'J7', 800);
INSERT INTO SPJ (SNO, PNO, JNO, QTY) VALUES ('S2', 'P5', 'J2', 100);
INSERT INTO SPJ (SNO, PNO, JNO, QTY) VALUES ('S3', 'P3', 'J1', 200);
INSERT INTO SPJ (SNO, PNO, JNO, QTY) VALUES ('S3', 'P4', 'J2', 500);
INSERT INTO SPJ (SNO, PNO, JNO, QTY) VALUES ('S4', 'P6', 'J3', 300);
INSERT INTO SPJ (SNO, PNO, JNO, QTY) VALUES ('S4', 'P6', 'J7', 300);
INSERT INTO SPJ (SNO, PNO, JNO, QTY) VALUES ('S5', 'P2', 'J2', 200);
INSERT INTO SPJ (SNO, PNO, JNO, QTY) VALUES ('S5', 'P2', 'J4', 100);
INSERT INTO SPJ (SNO, PNO, JNO, QTY) VALUES ('S5', 'P5', 'J5', 500);
INSERT INTO SPJ (SNO, PNO, JNO, QTY) VALUES ('S5', 'P5', 'J7', 100);
INSERT INTO SPJ (SNO, PNO, JNO, QTY) VALUES ('S5', 'P6', 'J2', 200);
INSERT INTO SPJ (SNO, PNO, JNO, QTY) VALUES ('S5', 'P1', 'J4', 100);
INSERT INTO SPJ (SNO, PNO, JNO, QTY) VALUES ('S5', 'P3', 'J4', 200);
INSERT INTO SPJ (SNO, PNO, JNO, QTY) VALUES ('S5', 'P4', 'J4', 800);
INSERT INTO SPJ (SNO, PNO, JNO, QTY) VALUES ('S5', 'P5', 'J4', 400);
INSERT INTO SPJ (SNO, PNO, JNO, QTY) VALUES ('S5', 'P6', 'J4', 500);

R에서 SQL query를 입력하여 그 값을 받아보겠습니다.

dbGetQuery(MyDB,"SELECT DISTINCT JNO FROM SPJ WHERE NOT SPJ.SNO = 'S2'")

이제 DB를 닫고 마무리 합니다.

dbDisconnect(MyDB)

데이터를 어떻게 저장할지 정의하기

데이터를 조작하기

트랜잭션

간단한 예제

CREATE DATABASE mydb; -- mydb 데이터베이스 생성
USE mydb; -- 기본 데이터베이스로 mydb를 지정
CREATE TABLE mytable ( id INT PRIMARY KEY, name VARCHAR(20) ); -- mytable 테이블 생성
INSERT INTO mytable VALUES ( 1, 'Will' ); -- 데이터 입력
INSERT INTO mytable VALUES ( 2, 'Marry' );
INSERT INTO mytable VALUES ( 3, 'Dean' );
SELECT id, name FROM mytable WHERE id = 1;
UPDATE mytable SET name = 'Willy' WHERE id = 1;
SELECT id, name FROM mytable;
DELETE FROM mytable WHERE id = 1;
SELECT id, name FROM mytable;
DROP DATABASE mydb;
SELECT count(1) from mytable; gives the number of records in the table

Reference