[포스코x코딩온] 스마트 팩토리 과정 6주차 회고 | SQL,JOIN,C++과 MySQL연동하기

Dana·2023년 4월 26일

코딩온-스마트팩토리

목록 보기

11/21

JOIN

두 테이블을 묶어서 하나의 테이블을 만듬

Natural Join
Inner Join
Left Outer Join
Right Outer Join
Full Outer Join

select * 
from authors as a inner join books as b
on a.author_id = b.author_id;

select *
from authors natural join books;

select *
from authors a left join books b on a.author_id = b.author_id;

select *
from authors a right outer join books b on a.author_id = b.author_id;

UNION

두 개 이상의 SELECT문의 결과를 하나의 결과로 결합하는 데 사용
이 때, SELECT문의 열 이름과 열의 수가 동일해야한다
UNION ALL을 사용하면 중복을 허용할 수 있다.

select author_id
from authors 
union all select author_id from books;

실습 - Full Outer Join 구현하기

select *
from authors a left join books b on a.author_id = b.author_id
union
select *
from authors a right join books b on a.author_id = b.author_id;

Foreign Key 연결

DROP TABLE IF EXISTS authors;
CREATE TABLE authors (
	author_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(50)
);

DROP TABLE IF EXISTS books;
CREATE TABLE books (
	book_id INT PRIMARY KEY AUTO_INCREMENT,
	author_id INT NOT NULL,
    title VARCHAR(100) NOT NULL,
    publication_date DATE NOT NULL,
    FOREIGN KEY (author_id) REFERENCES authors(author_id) ON UPDATE CASCADE ON DELETE CASCADE
);

INSERT INTO authors (author_id, first_name, last_name, email) VALUES
(1, 'J.K.','Rowling','jkrowling@gmail.com'),
(2, 'George R.R.', 'Martin','grmartin@yahoo.com'),
(3, 'Stephen','King','sking@hotmail.com'),
(5, '아가사', '크리스티', 'agatha.christie@example.com');

INSERT INTO books (title, author_id, publication_date)
VALUES ('Harry Potter and the Philosopher''s Stone', 1, '1997-06-26'),
       ('A Game of Thrones', 2, '1996-08-06'),
       ('The Shining', 3, '1977-01-28'),
       ('스탠드', 3, '1978-01-01');
       # ('Lucky Day', 4, '2023-03-01') 에러 --> author테이블에 author_id 4가 없으므로

set SQL_SAFE_UPDATES = 0; # 세이프모드 해제
update authors set author_id = 4 where last_name = 'king';

실습 - 테이블 생성하기

DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
	id varchar(50) PRIMARY KEY,
	name varchar(50) NOT NULL,
    email VARCHAR(255) NOT NULL,
    address varchar(255) NOT NULL
);

DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
	id int PRIMARY KEY,
	customer_id varchar(50),
    date date NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON UPDATE CASCADE ON DELETE CASCADE
);

DROP TABLE IF EXISTS order_detail;
CREATE TABLE order_detail (
	order_id int,
	item varchar(50) not null,
    quantity int not null,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON UPDATE CASCADE ON DELETE CASCADE
);

실습 - 테이블에 데이터 삽입하기

insert into customers (id, name, email, address) values
('alice789', 'Alice Johnson','alice.johnson@hatmail.com', '789 Elm St, Nowhere'),
('jane456', 'Jane Doe', 'jane.doe@yahoo.com','456 Oak Ave, Somewhere'),
('john123', 'John Smith','john.smith@gmail.com','123 Main St, Anytown, USA');

insert into orders (id, customer_id, date) values
(1, 'john123', '2022-01-01'),
(2, 'john123', '2022-02-01'),
(3, 'jane456', '2022-03-01');

insert into order_detail (order_id, item, quantity) values
(1, 'pen', 10),
(1, 'toy', 2),
(2, 'clothes', 5),
(3, 'socks', 100);

실습 - DML, DDL 복습

# orders 테이블에 total컬럼 추가하기
alter table orders add total int;

# total을 업데이트하기
update orders set total=10000 where id=1;
update orders set total=15000 where id=2;
update orders set total=20000 where id=3;

# id가 john123인 고객 John Park으로 이름 수정하기
update customers set name='John Park' where id='john123';

실습 - SELECT 복습

#1
select distinct customer_id from orders
where date between '2022-01-01' and '2022-02-01';

#2
select distinct name from customers where id in
(select customer_id from orders where date between '2022-01-01' and '2022-02-01');

#3
select * from orders order by total desc limit 1;

#4
select * from orders where total = (select max(total) from orders);

#5
select * from order_detail A join orders B on A.order_id = B.id;
select B.customer_id as customer, sum(quantity) as quantity 
from order_detail A join orders B on A.order_id = B.id
group by customer_id; 

#6
select customer_id, sum(total) as total_price
from order_detail A join orders B on A.order_id = B.id
group by customer_id
having sum(total) >= 21000;

#7
select A.order_id, A.item, A.quantity, B.date, B.total, B.customer_id, C.name, C.email, C.address
from order_detail A join orders B on A.order_id = B.id
join customers C on B.customer_id = C.id;

#8
select A.order_id, A.item, A.quantity, B.date, B.total, B.customer_id, C.name, C.email, C.address
from order_detail A right join orders B on A.order_id = B.id
right join customers C on B.customer_id = C.id;

데이터 제어어(DCL, Data Control Language)

데이터베이스에 접근해 읽거나 쓰는 것에 대한 권한 부여/박탈

GRANT

특정 데이터베이스 사용자에게 특정 작업에 대한 수행 권한 부여

GRANT permission_type ON db_name.table_name
TO username@host IDENTIFIED BY 'pw' [WITH GRANT OPTION];

REVOKE

특정 데이터베이스 사용자에게 특정 작업에 대한 권한 박탈

REVOKE permission_type ON db_name.table_name FROM 'username'@'host';

C++ MySQL 연결

C++ Connector 설치하기 (8.0.29)
- https://downloads.mysql.com/archives/c-cpp/
vscode의 c_cpp_properties.json 파일의 includePath부분에 아래 코드 추가
```
"/usr/local/mysql-connector-c++-8.0.29/include"
```

환경변수 설정

vi ~/.zshrc
export DYLD_LIBRARY_PATH="/usr/local/mysql-connector-c++-8.0.29/lib64"
source ~/.zshrc

프로젝트 실행

# 빌드
g++ -std=c++11 -I /usr/local/mysql-connector-c++-8.0.29/include -L /usr/local/mysql-connector-c++-8.0.29/lib64 -l mysqlcppconn hello.cpp -o hello
# 실행
./hello

실습 참고 https://learn.microsoft.com/ko-kr/azure/mysql/single-server/connect-cpp#read-data

실습 - CREATE & INSERT

#include <iostream>
#include <string>
#include <mysql/jdbc.h>

using std::cout;
using std::endl;
using std::string;

const string server = "tcp://127.0.0.1:3306"; // 데이터베이스 주소 (3306: MySQL 데이터베이스 기본 포트 번호)
const string username = "root"; // 데이터베이스 사용자
const string password = "1234"; // 데이터베이스 접속 비밀번호

int main()
{
    // MySQL Connector/C++ 초기화
    sql::mysql::MySQL_Driver* driver; // 추후 해제하지 않아도 Connector/C++가 자동으로 해제해 줌
    sql::Connection* con;
    sql::Statement* stmt;
    sql::PreparedStatement* pstmt;

    // MySQL 연결
    try {
        driver = sql::mysql::get_mysql_driver_instance();
        con = driver->connect(server, username, password); // MySQL 데이터베이스 연결 객체
    }
    catch (sql::SQLException& e) {
        cout << "Could not connect to server. Error message: " << e.what() << endl;
        exit(1);
    }

    // 데이터베이스 선택
    con->setSchema("kdt");

    // db 한글 저장을 위한 셋팅 
    stmt = con->createStatement();
    stmt->execute("set names euckr");
    if (stmt) { delete stmt; stmt = nullptr; }

    // stmt 객체를 사용해서 SQL 쿼리를 실행할 수 있다
    stmt = con->createStatement();
    stmt->execute("DROP TABLE IF EXISTS inventory"); // DROP
    cout << "Finished dropping table (if existed)" << endl;

    stmt->execute("CREATE TABLE inventory (id serial PRIMARY KEY, name VARCHAR(50), quantity INTEGER);"); // CREATE
    cout << "Finished creating table" << endl;
    delete stmt;

    // pstmt를 이용하면 입력 값을 나중에 지정할 수 있다.
    pstmt = con->prepareStatement("INSERT INTO inventory(name, quantity) VALUES(?,?)"); // INSERT

    pstmt->setString(1, "banana"); // 첫 번째 컬럼에 'banana' string 삽입
    pstmt->setInt(2, 150); // 두 번째 컬럼에 150 int 삽입
    pstmt->execute(); // 쿼리 실행
    cout << "One row inserted." << endl;

    pstmt->setString(1, "orange");
    pstmt->setInt(2, 154);
    pstmt->execute();
    cout << "One row inserted." << endl;

    pstmt->setString(1, "사과");
    pstmt->setInt(2, 100);
    pstmt->execute();
    cout << "One row inserted." << endl;

    // MySQL Connector/C++ 정리
    delete pstmt;
    delete con;

    return 0;
}

실습 - SELECT

sql::ResultSet *result;

...

//select  
pstmt = con->prepareStatement("SELECT * FROM inventory;");
result = pstmt->executeQuery();

while (result->next())
	printf("Reading from table=(%d, %s, %d)\n", result->getInt(1), result->getString(2).c_str(), result->getInt(3));

실습 - UPDATE

//update
pstmt = con->prepareStatement("UPDATE inventory SET quantity = ? WHERE name = ?");
pstmt->setInt(1, 200);
pstmt->setString(2, "banana");
pstmt->executeQuery();
printf("Row updated\n");

실습 - DELETE

//delete
pstmt = con->prepareStatement("DELETE FROM inventory WHERE name = ?");
pstmt->setString(1, "orange");
result = pstmt->executeQuery();
printf("Row deleted\n");