천재지변으로 인해 일부 데이터가 유실되었습니다. 입양을 간 기록은 있는데, 보호소에 들어온 기록이 없는 동물의 ID와 이름을 ID 순으로 조회하는 SQL문을 작성해주세요.
JOIN을 얼마나 잘 활용하느냐를 묻는 문제이다.
ANIMAL_INS에는 없고, ANIMAL_OUTS에만 있는 데이터를 찾아달라는 것이다. 따라서, 1번으로 join을 해주는데, inner가 아닌 outer조인을 해줘야 한다. 그럼 어떻게 붙일까. 난 ANIMAL_INS를 Select 문의 from에 썼으므로, right join으로 ANIMAL_OUTS를 붙였다. 그리고 id가 같은녀석들로 붙였으며, 마지막에 where문을 사용하여, ANIMAL_INS.ANIMAL_ID IS NULL 을 조건으로 걸면 outs 테이블에만 있는 녀석들을 찾을 수 있다!
SELECT ao.ANIMAL_ID, ao.NAME FROM ANIMAL_INS ai
right join ANIMAL_OUTS ao
on ao.ANIMAL_ID = ai.ANIMAL_ID
where ai.ANIMAL_ID is null
Returns records that have matching values in both table. 두개의 테이블에 모두 있는 값을 매칭한다!
SELECT Table1.Column1,Table1.Column2,Table2.Column1,....
FROM Table1
INNER JOIN Table2
ON Table1.MatchingColumnName = Table2.MatchingColumnName;
## 예제
위의 테이블에서 Employee Table의 EmpID와 Project를 연결해서 , 해당 인물의 프로젝트를 출력하자
SELECT Employee.EmpID, Employee.EmpFname, Employee.EmpLname, Projects.ProjectID, Projects.ProjectName
FROM Employee
INNER JOIN Projects ON Employee.EmpID=Projects.EmpID;
FULL (OUTER) JOIN
Returns all records when there is a match in either left or right table 양쪽을 서로 붙이는 것으로, 서로 없는것들에 대해 NULL로 채우면서 JOIN하는것.
SELECT Table1.Column1,Table1.Column2,Table2.Column1,....
FROM Table1
FULL JOIN Table2
ON Table1.MatchingColumnName = Table2.MatchingColumnName;
Employee 테이블과 Projects 테이블을 EmpID를 기준으로 FULL JOIN하였다. 그 결과 EMP에 없는 내용이지만, Project에 기록이 있을경우 EmpFname에 Null이 입력되고, 반대의 경우도 똑같이 다 연결되었다
LEFT JOIN
Left join returns all records from the left table (table1), and matched recored from the right table. The recult is NULL from the right side, if there is no match. 왼쪽의 있는 테이블을 기준으로, 오른쪽 테이블을 붙이며, 오른쪽 테이블 중 매칭되지 않는 데이터는 NULL로 매핑된다. 인덱스 개수 : 왼쪽 테이블 행 수 만큼
SELECT Table1.Column1,Table1.Column2,Table2.Column1,....
FROM Table1
LEFT JOIN Table2
ON Table1.MatchingColumnName = Table2.MatchingColumnName;
왼쪽테이블 (Employee)을 기준으로 오른쪽 테이블 (Projects)를 붙였다. 그 결과, 왼쪽의 모든것은 다 따라왔으나, 그 데이터 중 오른쪽에 없을경우 Project테이블 내용이 NULL로 매핑되었다
RIGHT JOIN
Returns all records form the right table, and the matched records from the left table 오른쪽 테이블 (join하는 테이블)을 기준으로 붙임 -> 인덱스 개수 : 오른쪽 테이블 행 수 만큼
Select * from Orders o
RIGHT JOIN Customers c
ON o.CustomerID = c.CustomerID;
SELF JOIN
A Self join is a regular join, but the table is joined with itself. 스스로를 조인하는것으로, 활용하는 방법에 대해 조금 난이도있다고 생각된다. 가장 대표적인 케이스로, 어떠한 인적정보 테이블에서, 자신과 같은 도시에 사는 다른사람 쌍을 구해보는 쿼리를 짠다고 생각하면
SELECT e1.FirstName, e2.FirstName, e1.city
FROM employees e1, employees e2
where e1.EmployeeID <> e2.EmployeeID and e1.City = e2.City
where e1.EmployeeId <> e2.EmployeeId 의 부분은 자기 스스로와 매핑되지 않도록 하기위함이고,
and e1.city=e2.City 이 부분은 city가 같은 다른사람을 조인하기 위한 부분이다.
The SELECT DISTINCT statement is used to return only distinct (different) values.
유일한 값으로 가져온다는 뜻으로, 중복 없이 가져오게된다.
SELECT DISTINCT column1, column2, ...
FROM table_name;
Example
-- Write a statement that will select the City column from the Customers table.
SELECT City FROM Customers;
-- Select all the different values from the Country column in the Customers table.
SELECT DISTINCT Country FROM Customers;
WHERE
필터링 하는 것 . 원하는 조건을 걸 수 있다.
The WHERE clause is used to filter records.
The WHERE clause is used to extract only those records that fulfill a specified condition.
LIKE or NOT LIKE
특정 문자 또는 문자열을 포함하고 있는 값을 검색하고 싶을 때 사용.
% : 0개 이상의 문자열과 대치
_ : 임의의 한 개의 문자와 대치
%,_ 를 검색하고싶을땐? → @% , @_
--Genres에서 name이 B로 시작하는것 가져오기
select * from genres g
where g.Name like 'B%';
AND, OR and NOT Operators
생각대로 and , or , not 연산자를 사용하여 조건을 추가할 수 있다.
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;
SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;
-- Customers 테이블에서 Country는 Germany이고, City는 Berlin이나 Munchen인 records를 골라라
SELECT * FROM Customers
WHERE Country='Germany' AND (City='Berlin' OR City='München');
--Select all records where the City column has the value "Berlin".
SELECT * FROM Customers
WHERE City = Berlin;
--The following SQL statement selects all the customers from the country "Mexico", in the "Customers" table:
SELECT * FROM Customers
WHERE Country='Mexico';
--#Select all records where the City column has the value 'Berlin' and the PostalCode column has the value 12209.
Select * From Customers
Where City = 'Berlin' and PostalCode = 12209
SELECT * FROM Customers
Where
IN, BETWEEN
The IN operator allows you to specify multiple values in a WHERE clause.
The IN operator is a shorthand for multiple OR conditions.
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);
SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);
The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.
The BETWEEN operator is inclusive: begin and end values are included.
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
INSERT
The INSERT INTO statement is used to insert new records in a table.
Set the value of the City columns to 'Oslo', but only the ones where the Country column has the value "Norway".
update Customers
set City = 'Oslo'
where Country = 'Norway';
DELETE
삭제하는것
말그대로 삭제이며, from으로 어디서 삭제할지 명시해줘야한다
DELETE FROM table_name WHERE condition;
MIN(), MAX()
최대 최소 뽑기
The MIN() function returns the smallest value of the selected column.
The MAX() function returns the largest value of the selected column.
SELECT MIN(column_name)
FROM table_name
WHERE condition;
SELECT MAX(column_name)
FROM table_name
WHERE condition;
COUNT(), AVG() and SUM()
개수새기, 평균, 합계 함수
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
SELECT AVG(column_name)
FROM table_name
WHERE condition;
SELECT SUM(column_name)
FROM table_name
WHERE condition;
Database에 연결을 해서 메모리상에 존재하던 데이터를 저장해줘야 다시 켰을때 데이터를 보존할 수 있다. Flask로 만든 웹서버에서 오고가는 데이터 또한 DB에 저장해주지 않으면, Flask를 다시 실행했을 때 데이터가 날아가게되어 처음부터 다시 입력하고 시작하는 등의 불편함이 동반된다. 이런것을 방지하기위해 DB에 연결하는것이고, DB에 연결하는것을 도와주는 도구를 이용해서 Flask에 연결해보자!
Application Factory를 지키면서 DB연결을 하자.
일단 DB에 만들 테이블을 먼저 생성해준다.
# models.usermodel.py
from kokoa import db
class User(db.Model):
__tablename__ = 'user'
id = db.Column(db.Integer, primary_key = True)
username = db.Column(db.String(64), unique=True, nullable=False)
password = db.Column(db.String(200), nullable=False)
def __repr__(self):
return f"User id : {self.id}, User name : {self.username}"
# return f"User id : {self.id}, User name : {self.username}, E-mail : {self.email}"
(DB)모델은 kokoa에서 db를 import한다. kokoa를 실행시키면 init.py가 실행되므로 init.py에 있는 db를 import한다고 생각하면 된다.
이렇게 테이블이 어떤모양을 갖고있을지 만들었으면 이제 처리해보자
일단 먼저 ORM을 적용시켜줘야한다. init.py 에 migrate부분을 추가해주고!
Table에 추가하기
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate
db = SQLAlchemy()
migrate = Migrate()
def create_app(config=None):
app = Flask(__name__)
db.init_app(app)
migrate.init_app(app, db)
from .models import user_model
위와같이 model을 추가해줬으면 이제 shell에서 명령어를 쳐줘야한다.
FLASK_APP=kokoa flask db init
FLASK_APP=kokoa flask db migrate
FLASK_APP=kokoa flask db upgrade
디비를 관리하는 초기파일을 migrations라는 디렉터리에 생성해주고, 이 파일을 알아서 관리해주므로 init을 해준 이후부터 모델에 테이블을 추가하거나 하는등 변화를 줬을땐, migrate와 upgrade만 해주면 된다!
relation table 관리하기!
만약 어떤 테이블이 1:N의 관계를 갖는다고 생각해보자. 지금 여기서는 User테이블이 있는 상태이고, 이 User가 어떤 (1:1)채팅방을 갖고있다고 생각했을때, 한명의 유저는 여러개의 채팅방을 가질 수 있다. 이러한 관계를 만든다고 하면,
class Room(db.Model):
__tablename__ = 'room'
id = db.Column(db.Integer, primary_key = True)
user_id = db.Column(db.Integer(), db.ForeignKey('user.id', ondelete='CASCADE'))
company_id = db.Column(db.Integer(), db.ForeignKey('company.id', ondelete='CASCADE'))
user = db.relationship('User', backref=db.backref('rooms', cascade='all, delete-orphan'))
company = db.relationship('Company', backref=db.backref('rooms', cascade='all, delete-orphan'))
def __repr__(self):
return f"Room id : {self.id}, User - Company : {self.user_id} - {self.company_id}"
위를 보면 id는 기본이고, user_id 는 User테이블에 있는 id를 ForeignKey로 갖는다는것을 볼 수 있다. 여기서! ondelete='CASCADE'는 삭제연동을 의미한다. 이것을 설정해줌으로써 user를 삭제하게되면 자동으로 room까지 삭제되도록 하는것이다. 그리고 밑에 보면 user = db.relationship~부분의 backref를 볼 수 있다. 이는 역참조를 의미하며 user에서 user.rooms를 통해 채팅방을 참조할 수 있다.
일단 여기까지 DB모델을 설정해주는부분을 했으니, DB에 데이터를 추가하는 부분을 작업하자. main_route.py라는 함수를 통해 url틍 통해 들어는것을 처리하며 이때 데이터를 같이 넘기면서 user를 추가하는 기능을 구현했다.
위와같이 Login과 signup을 할 수 있도록 form을 작성해주었다. method는 post로 설정하였다. 이후 받게되는 데이터는 아래와같이 처리되는데,
from flask import Blueprint, render_template, request, flash, session, g, url_for
from kokoa.models.user_model import User, Room
from kokoa import db
bp = Blueprint('main', __name__)
@bp.route('/')
def index():
return render_template('index.html')
@bp.route('/signup/', methods=['GET','POST'])
def signup():
print('signup input : ',request.form)
username=request.form['username']
password=request.form['password']
# form = UserCreateForm()
if request.method=='POST': #form전송이 제대로 되었다면!
#user가 이미 존재하는지 먼저 체크!
user = User.query.filter_by(username=username ).first()
if not user: #유저가 존재하지 않음 -> 생성
user = User(username=username,\
password=generate_password_hash(password))
db.session.add(user)
db.session.commit()
return f'<script> alert("유저 생성 완료. ID : {username}, PASSWORD : {password}"); location.href="/" </script>'
else: #유저가 이미 존재함 -> 뒤로가기
return f'<script> alert("이미 존재하는 user입니다. 다른 이름을 입력하세요"); location.href="/" </script>'
# flash('이미 존재하는 사용자입니다')
# return '이미 존재하는 username입니다.'
return f'<script> alert("오류. 다시 시작하세요"); location.href="/" </script>'
기본화면에서 form 제출을 POST로 받고 그렇게 받은 form에서 username과 password를 받았다. db를 조회해서 user가 존재한다면 뒤로가기처리와 이미 존재한다는 알림을 띄우고, user가 db에 없다면 user생성을 하고 이를 알린다. 위와같이 user table에 데이터를 추가하는것을 만들었다!
서로 다른 테이블을 연결하여, 큼직~하게 테이블 하나 만든다고 생각하면된다. 그럼 여기서 어떻게 테이블을 연결할까? 그 방법에 대하여 Inner , Outer, Self 등이 존재한다.
Different Types of SQL JOINs
Here are the different types of the JOINs in SQL:
(INNER) JOIN: Returns records that have matching values in both tables 둘 다 있는 것만 조인됨
LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table 왼쪽 테이블에 오른쪽 테이블을 붙여서 조인(왼쪽 테이블 행만큼)
RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table 오른쪽테이블에 왼쪽 테이블을 붙여서 조인 (오른쪽 테이블 행만큼)
FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table 양쪽에 서로 다 붙임 → 없는거 Null 쭈르륵 생김
SQL INNER JOIN
The INNER JOIN keyword selects records that have matching values in both tables.
양쪽 테이블 모두 갖고있는 것들만 JOIN 한다
SQL LEFT JOIN Keyword
The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.
왼쪽테이블의 모든 칸에 오른쪽테이블의 JOIN이 실행되며, 오른쪽에 결과값이 없는것은 NULL이 매핑된다 Note: In some databases LEFT JOIN is called LEFT OUTER JOIN.
SQL FULL OUTER JOIN Keyword
The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or right (table2) table records.
양쪽의 모든 테이블이 JOIN되며, 없는것들은 모두 NULL
Note: FULL OUTER JOIN can potentially return very large result-sets!
Tip: FULL OUTER JOIN and FULL JOIN are the same.
SQL Self JOIN
A self JOIN is a regular join, but the table is joined with itself.
스스로 조인하는것으로, 활용하는 법에 대하여 조금 난이도있다고 생각된다.
가장 대표적인 것으로, 어떠한 인적정보 테이블에서 , 자신과 같은 도시에 사는 다른사람 쌍을 구해보는 쿼리를 짠다고 생각하면
select e1.FirstName ,e2.FirstName, e1.city
from employees e1, employees e2
where e1.EmployeeId <> e2.EmployeeId and e1.city=e2.City
where e1.EmployeeId <> e2.EmployeeId 의 부분은 자기 스스로와 매핑되지 않도록 하기위함이고,
and e1.city=e2.City 이 부분은 city가 같은 다른사람을 조인하기 위한 부분이다.
The SELECT DISTINCT statement is used to return only distinct (different) values.
유일한 값으로 가져온다는 뜻으로, 중복 없이 가져오게된다.
SELECT DISTINCT column1, column2, ...
FROM table_name;
Example
-- Write a statement that will select the City column from the Customers table.
SELECT City FROM Customers;
-- Select all the different values from the Country column in the Customers table.
SELECT DISTINCT Country FROM Customers;
WHERE
필터링 하는 것
The WHERE clause is used to filter records.
The WHERE clause is used to extract only those records that fulfill a specified condition.
LIKE or NOT LIKE
특정 문자 또는 문자열을 포함하고 있는 값을 검색하고 싶을 때 사용.
% : 0개 이상의 문자열과 대치
_ : 임의의 한 개의 문자와 대치
%,_ 를 검색하고싶을땐? → @% , @_
--Genres에서 name이 B로 시작하는것 가져오기
select * from genres g
where g.Name like 'B%';
AND, OR and NOT Operators
생각대로 and , or , not 연산자를 사용하여 조건을 추가할 수 있다.
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;
SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;
-- Customers 테이블에서 Country는 Germany이고, City는 Berlin이나 Munchen인 records를 골라라
SELECT * FROM Customers
WHERE Country='Germany' AND (City='Berlin' OR City='München');
--Select all records where the City column has the value "Berlin".
SELECT * FROM Customers
WHERE City = Berlin;
--The following SQL statement selects all the customers from the country "Mexico", in the "Customers" table:
SELECT * FROM Customers
WHERE Country='Mexico';
--#Select all records where the City column has the value 'Berlin' and the PostalCode column has the value 12209.
Select * From Customers
Where City = 'Berlin' and PostalCode = 12209
SELECT * FROM Customers
Where
INSERT
The INSERT INTO statement is used to insert new records in a table.
Set the value of the City columns to 'Oslo', but only the ones where the Country column has the value "Norway".
update Customers
set City = 'Oslo'
where Country = 'Norway';
DELETE
삭제하는것
말그대로 삭제이며, from으로 어디서 삭제할지 명시해줘야한다
DELETE FROM table_name WHERE condition;
MIN(), MAX()
최대 최소 뽑기
The MIN() function returns the smallest value of the selected column.
The MAX() function returns the largest value of the selected column.
SELECT MIN(column_name)
FROM table_name
WHERE condition;
SELECT MAX(column_name)
FROM table_name
WHERE condition;
COUNT(), AVG() and SUM()
개수새기, 평균, 합계 함수
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
SELECT AVG(column_name)
FROM table_name
WHERE condition;
SELECT SUM(column_name)
FROM table_name
WHERE condition;
Wildcard 와일드카드
A wildcard character is used to substitute one or more characters in a string.
Wildcard characters are used with the SQL LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
Symbol
Description
Example
%
Represents zero or more characters
bl% finds bl, black, blue, and blob
_
Represents a single character
h\t finds hot, hat, and hit
[]
Represents any single character within the brackets
h[oa]t finds hot and hat, but not hit
^
Represents any character not in the brackets
h[^oa]t finds hit, but not hot and hat
-
Represents a range of characters
c[a-b]t finds cat and cbt
--Select all records where the first letter of the City starts with anything from an "a" to an "f".
SELECT * FROM Customers
WHERE City LIKE '[a-f]%';
Select all records where the first letter of the City is NOT an "a" or a "c" or an "f".
SELECT * FROM Customers
WHERE City LIKE '[^acf]%';
IN, BETWEEN
The IN operator allows you to specify multiple values in a WHERE clause.
The IN operator is a shorthand for multiple OR conditions.
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);
SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);
The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.
The BETWEEN operator is inclusive: begin and end values are included.
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
