1-2. 서버 종료하기 - 강제종료하는 방법 ps -ef | grep 'python app.py' | awk '{print $2}' | xargs kill

2-1. EC2 서버 종료하는 방법 (1년 후 자동결제 방지!) 중지 또는 종료하는 법. 무료 기간(1년) 후 결제가 되기 전에, 이렇게 종료하세요!

3-1. AWS EC2에 접속하기 Window: ssh가 없으므로, git bash라는 프로그램을 이용! ssh -i 받은키페어를끌어다놓기 ubuntu@AWS에적힌내아이피 예)ssh -i /path/my-key-pair.pem ubuntu@13.125.250.20

4-1. EC2 한방에 세팅하기 python3 -> python sudo update-alternatives --install /usr/bin/python python /usr/bin/python3 10

pip3 -> pip sudo apt-get update sudo apt-get install -y python3-pip sudo update-alternatives --install /usr/bin/pip pip /usr/bin/pip3 1

port forwarding sudo iptables -t nat -A PREROUTING -i eth0 -p tcp --dport 80 -j REDIRECT --to-port 5000

• flask 폴더 구조 만들기
• static, templates 폴더 + app.py 만들기! 이젠 너무 익숙하죠?
• templates 폴더 안에 index.html 만들기

패키지 설치하기---- 5개 : flask, pymongo, dnspython, bs4, requests

app.py----- from flask import Flask, render_template, request, jsonify app = Flask(name)

from pymongo import MongoClient client = MongoClient('mongodb+srv://test:sparta@cluster0.yroj1zb.mongodb.net/Cluster0?retryWrites=true&w=majority') db = client.dbsparta

@app.route('/') def home(): return render_template('index.html')

// POST방식코딩 @app.route("/homework", methods=["POST"]) def homework_post(): name_receive = request.form['name_give'] comment_receive = request.form['comment_give']

doc = {
    'name': name_receive,
    'comment': comment_receive
}
db.homework.insert_one(doc)

return jsonify({'msg': 'POST 응원 남기기 완료!'})

//GET방식코딩 @app.route("/homework", methods=["GET"]) def homework_get(): comment_list = list(db.homework.find({}, {'_id': False})) return jsonify({'comments': comment_list})

if name == 'main': app.run('0.0.0.0', port=5000, debug=True)

index.html-----

doc = {'name':'bobby','age':21} db.users.insert_one(doc)

한 개 찾기 - 예시

user = db.users.find_one({'name':'bobby'})

여러개 찾기 - 예시 ( _id 값은 제외하고 출력)

all_users = list(db.users.find({},{'_id':False}))

바꾸기 - 예시

db.users.update_one({'name':'bobby'},{'$set':{'age':19}})

지우기 - 예시

db.users.delete_one({'name':'bobby'})

현재기온 : 00.0도

  $.ajax({
            type: "GET",
            url: "http://spartacodingclub.shop/sparta_api/weather/yongin",
            data: {},
            success: function (response) {
              let temp = response['temp']



            }
        })

11. CSS 꿀팁-폰트넣기

• { font-family: 'Gowun Dodum', sans-serif; }

SELECT o.order_no , o.created_at, SUBSTRING(o.created_at,1,10) as Date FROM orders o ;

SELECT SUBSTRING(o.created_at,1,10) as Date, COUNT(*) FROM orders o group by Date;

SELECT o.order_no , o.created_at, SUBSTRING(o.created_at,12,8) as time FROM orders o ;

SELECT pu.user_id , pu.point , ( CASE when pu.point > 10000 then '1만 이상 ' WHEN pu.point > 5000 then '5천 이상' ELSE '5천 미만' END) as lv FROM point_users pu ;

SELECT a.lv, COUNT(*) as cnt FROM ( SELECT pu.user_id , pu.point , ( CASE when pu.point > 10000 then '1만 이상 ' WHEN pu.point > 5000 then '5천 이상' ELSE '5천 미만' END) as lv FROM point_users pu ) a group by a.lv;

with table1 as ( SELECT pu.user_id , pu.point , ( CASE when pu.point > 10000 then '1만 이상 ' WHEN pu.point > 5000 then '5천 이상' ELSE '5천 미만' END) as lv FROM point_users pu ) SELECT a.lv,COUNT(*) as cnt FROM table1 a group by a.lv;

select co.title, a.course_id, a.cnt_checkins, b.cnt_total, round(a.cnt_checkins/b.cnt_total,3) as ratio from ( select course_id, count(distinct (user_id)) as cnt_checkins from checkins ch group by course_id ) a inner join ( select course_id, count() as cnt_total from orders o group by course_id ) b on a.course_id = b.course_id inner join courses co on a.course_id = co.course_id; --with----------; with table1 as ( select course_id, count(distinct (user_id)) as cnt_checkins from checkins ch group by course_id ), table2 as ( select course_id, count() as cnt_total from orders o group by course_id ) select co.title, a.course_id, a.cnt_checkins, b.cnt_total, round(a.cnt_checkins/b.cnt_total,3) as ratio from table1 a inner join table2 b on a.course_id = b.course_id inner join courses co on a.course_id = co.course_id;

select count(*) from checkins;

SELECT week, count(*) FROM checkins c group by week ;

SELECT * FROM checkins c; WHERE week =1 ;

SELECT week, MIN(likes) FROM checkins c group by week ;

SELECT week, MAX(likes) FROM checkins c group by week ;

SELECT week, AVG(likes) FROM checkins c group by week ;

SELECT week, ROUND(AVG(likes),2) FROM checkins c group by week ;

SELECT week, ROUND(AVG(likes),0) FROM checkins c group by week ;

SELECT week, SUM(likes) FROM checkins c group by week ;

SELECT checkin_id, user_id, week, likes FROM checkins c WHERE week = 3 ;

select name, count(*) from users group by name ;

SELECT payment_method , COUNT(*) FROM orders o group by payment_method;

SELECT payment_method , COUNT() FROM orders o WHERE course_title ='웹개발 종합반' group by payment_method; SELECT payment_method , COUNT() FROM orders o WHERE course_title ='웹개발 종합반' group by payment_method order by COUNT(*) ASC ;

SELECT payment_method , COUNT() FROM orders o WHERE course_title ='웹개발 종합반' group by payment_method order by COUNT() ;

SELECT payment_method , COUNT() FROM orders o WHERE course_title ='웹개발 종합반' group by payment_method order by COUNT() DESC ;

SELECT * FROM users u order by email ;

SELECT * FROM users u order by name DESC ;

SELECT * FROM users u order by created_at ASC ;

SELECT payment_method , COUNT(*) FROM orders o group by payment_method ;

SELECT payment_method , COUNT(*) FROM orders o WHERE course_title ='앱개발 종합반' group by payment_method;

SELECT name , email, COUNT(*) FROM users u WHERE email LIKE '%gmail.com' GROUP BY name;

SELECT course_id , ROUND(AVG(likes),1) FROM checkins c group by course_id ;

show tables ; select * from orders; select order_no, created_at, course_title, email from orders;

SELECT /WHERE 연습;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; SELECT * FROM orders o WHERE payment_method ='kakaopay'; SELECT FROM point_users pu WHERE point >=5000; SELECT * FROM orders o WHERE course_title ='앱개발 종합반' AND payment_method = 'CARD'; SELECT FROM point_users pu WHERE point > 20000; SELECT * FROM users u WHERE name = '황*'; SELECT * FROM orders o WHERE course_title ='웹개발 종합반' AND payment_method ='CARD'; SELECT * FROM orders o WHERE course_title != '앱개발 종합반'; select * from orders where created_at between "2020-07-13" and "2020-07-15"; select * from checkins where week in (1, 3); select * from users where email like '%daum.net'; SELECT * FROM orders o WHERE payment_method != 'CARD'; select * from point_users pu where point between 20000 and 30000; select * from users where email like 's%com'; select * from users where email like 's%com' AND name = '이**'; SELECT * FROM orders o WHERE payment_method = 'kakaopay' LIMIT 5; SELECT DISTINCT (payment_method) FROM orders o ; select COUNT() from users;

count/min/max/avg/sum/round 연습;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; SELECT * FROM checkins c limit 10; SELECT week, count(*) FROM checkins c group by week ; SELECT * FROM checkins c WHERE week =1 ; SELECT week, MIN(likes) FROM checkins c group by week ; SELECT week, MAX(likes) FROM checkins c group by week ; SELECT week, AVG(likes) FROM checkins c group by week ; SELECT week, ROUND(AVG(likes),2) FROM checkins c group by week ; SELECT week, ROUND(AVG(likes),0) FROM checkins c group by week ; SELECT week, SUM(likes) FROM checkins c group by week ; SELECT * FROM checkins c WHERE week = 3 ;

group by / order by 연습;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; SELECT * FROM users u group by name ; SELECT name,COUNT() FROM users u group by name ; SELECT name,COUNT() FROM users u group by name; SELECT payment_method ,COUNT() FROM orders o group by payment_method; SELECT payment_method ,COUNT() FROM orders o WHERE course_title ='웹개발 종합반' group by payment_method; SELECT payment_method ,COUNT() FROM orders o WHERE course_title ='웹개발 종합반' group by payment_method order by COUNT() ASC ; SELECT payment_method ,COUNT() FROM orders o WHERE course_title ='웹개발 종합반' group by payment_method order by COUNT() DESC ; SELECT * FROM users u order by email ; SELECT * FROM users u order by name DESC ; SELECT * FROM users u order by created_at ASC ; SELECT payment_method ,COUNT() FROM orders o group by payment_method ; SELECT payment_method ,COUNT() FROM orders o WHERE course_title ='앱개발 종합반' group by payment_method; SELECT name ,COUNT(*) FROM users u
WHERE email LIKE '%gmail.com' GROUP BY name; SELECT course_id ,ROUND(AVG(likes),1) FROM checkins c group by course_id ;

1) show tables로 어떤 테이블이 있는지 살펴보기 2) 제일 원하는 정보가 있을 것 같은 테이블에 select * from 테이블명 limit 10 쿼리 날려보기 3) 원하는 정보가 없으면 다른 테이블에도 2)를 해보기 4) 테이블을 찾았다! 범주를 나눠서 보고싶은 필드를 찾기 5) 범주별로 통계를 보고싶은 필드를 찾기 6) SQL 쿼리 작성하기!

join 연습;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; SELECT * FROM users u left join point_users pu on u.user_id = pu.user_id ; SELECT * FROM users u inner join point_users pu on u.user_id = pu.user_id ; SELECT * FROM orders o inner join users u on o.user_id = u.user_id ; SELECT * FROM checkins c inner join users u on c.user_id = u.user_id ; SELECT * FROM enrolleds e inner join courses c on e.course_id = c.course_id ; SELECT c.course_id , c2.title , COUNT(c.likes) as cnt FROM checkins c inner join courses c2 on c.course_id = c2.course_id GROUP BY c.course_id ; SELECT pu.user_id ,u.name ,u.email ,pu.point FROM point_users pu inner join users u on pu.user_id= u.user_id ORDER BY pu.point DESC ; SELECT u.name ,COUNT() as cnt FROM orders o inner join users u on o.user_id = u.user_id WHERE o.email LIKE '%naver.com' GROUP by u.name ; SELECT o.payment_method ,ROUND(AVG(pu.point),0) as avg_point FROM point_users pu inner join orders o on pu.user_id =o.user_id GROUP by o.payment_method ; SELECT u.name ,COUNT() as cnt_name FROM enrolleds e inner join users u on e.user_id = u.user_id WHERE e.is_registered =0 GROUP by u.name ORDER by cnt_name DESC ; SELECT c.course_id , c.title ,COUNT() as cnt_notstart FROM courses c inner join enrolleds e on c.course_id =e.course_id WHERE e.is_registered =0 GROUP by c.title ; SELECT co.title ,ch.week,COUNT() as cnt FROM courses co inner join checkins ch on co.course_id = ch.course_id GROUP by co.title ,ch.week ORDER by co.title ,ch.week; SELECT co.title ,ch.week,COUNT() as cnt FROM courses co inner join checkins ch on co.course_id = ch.course_id GROUP by co.title ,ch.week ORDER by co.title ASC ,ch.week DESC ; SELECT co.title , ch.week, COUNT() as cnt FROM courses co inner join checkins ch on co.course_id = ch.course_id INNER JOIN orders o on ch.user_id = o.user_id WHERE o.created_at >= '2020-08-01' GROUP by co.title , ch.week ORDER BY co.title , ch.week; SELECT u.name, COUNT() as cnt FROM users u left join point_users pu on u.user_id = pu.user_id WHERE pu.point_user_id IS NULL GROUP by u.name ; SELECT u.name, COUNT() as cnt FROM users u left join point_users pu on u.user_id = pu.user_id WHERE pu.point_user_id IS NOT NULL GROUP by u.name ; SELECT COUNT(pu.point_user_id) as pnt_user_cnt, COUNT(u.user_id ) as tot_user_cnt, ROUND( COUNT(pu.point_user_id) / COUNT(u.user_id ),2) as ratio FROM users u left join point_users pu on u.user_id = pu.user_id WHERE u.created_at BETWEEN '2020-07-10' AND '2020-07-20'; (select '7월' as month, c1.title, c2.week, count() as cnt from courses c1 inner join checkins c2 on c1.course_id = c2.course_id inner join orders o on c2.user_id = o.user_id where o.created_at < '2020-08-01' group by c1.title, c2.week order by c1.title, c2.week ) union all (select '8월' as month, c1.title, c2.week, count() as cnt from courses c1 inner join checkins c2 on c1.course_id = c2.course_id inner join orders o on c2.user_id = o.user_id where o.created_at >= '2020-08-01' group by c1.title, c2.week order by c1.title, c2.week ); SELECT e.enrolled_id, e.user_id , COUNT() as max_count FROM enrolleds e inner join enrolleds_detail ed on e.enrolled_id = ed.enrolled_id WHERE ed.done = 1 GROUP by e.enrolled_id, e.user_id
ORDER BY COUNT() DESC limit 0, 10;
SELECT e.enrolled_id, e.user_id , COUNT(*) as max_count FROM enrolleds e inner join enrolleds_detail ed on e.enrolled_id = ed.enrolled_id WHERE ed.done = 1 GROUP by e.enrolled_id, e.user_id
ORDER BY max_count DESC ;

subquery 연습;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; SELECT u.user_id , u.name , u.email FROM users u inner join orders o on u.user_id = o.user_id WHERE o.payment_method ='kakaopay' ; SELECT u.user_id , u.name , u.email FROM users u WHERE user_id IN ( select user_id FROM orders o WHERE payment_method = 'kakaopay' ); SELECT c.checkin_id , c.user_id , c.likes , (
SELECT ROUND(AVG(likes ),1) FROM checkins c2 WHERE c2.user_id = c.user_id ) AS avg_likes_user FROM checkins c; SELECT pu.user_id ,pu.point,a.avg_likes FROM point_users pu inner join ( SELECT user_id , ROUND(avg(likes ),1) as avg_likes FROM checkins c group by user_id ) a on pu.user_id= a.user_id; SELECT * FROM point_users pu WHERE point > ( SELECT AVG(point) FROM point_users ); SELECT * FROM point_users pu WHERE point > ( SELECT AVG(point) FROM point_users pu inner join users u on pu.user_id = u.user_id WHERE u.name = '이' );
SELECT * FROM point_users pu WHERE point > ( SELECT AVG(point) FROM point_users pu WHERE user_id IN ( SELECT user_id from users u WHERE u.name = '이' ) ); SELECT * FROM point_users pu WHERE point > ( SELECT AVG(point) FROM point_users pu inner join users u on pu.user_id = u.user_id WHERE u.name = '이' );
SELECT * FROM point_users pu WHERE point > ( SELECT AVG(point) FROM point_users pu WHERE user_id IN ( SELECT user_id from users WHERE name = '이' ) ); SELECT c.checkin_id , c.course_id , c.user_id , c.likes , ( select ROUND(avg(c2.likes),1) FROM checkins c2 WHERE c2.course_id =c.course_id
) AS course_avg FROM checkins c ; SELECT c.checkin_id , c3.title , c.user_id , c.likes , ( select ROUND(avg(c2.likes),1) FROM checkins c2 WHERE c2.course_id =c.course_id
) AS course_avg FROM checkins c inner join courses c3 on c.course_id = c3.course_id ; SELECT c.checkin_id , c.course_id , c.user_id , c.likes , ( select ROUND(avg(c2.likes),1) FROM checkins c2 WHERE c2.course_id =c.course_id
) AS course_avg, c3.* FROM checkins c inner join courses c3 on c.course_id = c3.course_id ; SELECT a.course_id, a.cnt_checkins, b.cnt_total, ROUND((a.cnt_checkins/b.cnt_total),3) as ratio FROM ( SELECT course_id,COUNT(DISTINCT (user_id)) as cnt_checkins FROM checkins ch group by course_id ) a INNER JOIN ( SELECT course_id, COUNT() as cnt_total FROM orders o group by course_id ) b ON a.course_id = b.course_id INNER JOIN courses co on a.course_id = co.course_id; SELECT co.title , a.cnt_checkins, b.cnt_total, ROUND((a.cnt_checkins/b.cnt_total),3) as ratio FROM ( SELECT course_id,COUNT(DISTINCT (user_id)) as cnt_checkins FROM checkins ch group by course_id ) a INNER JOIN ( SELECT course_id, COUNT() as cnt_total FROM orders o group by course_id ) b ON a.course_id = b.course_id INNER JOIN courses co on a.course_id = co.course_id;

with 연습;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; SELECT co.title , a.cnt_checkins, b.cnt_total, ROUND((a.cnt_checkins/b.cnt_total),3) as ratio FROM ( SELECT course_id,COUNT(DISTINCT (user_id)) as cnt_checkins FROM checkins ch group by course_id ) a INNER JOIN ( SELECT course_id, COUNT(*) as cnt_total FROM orders o group by course_id ) b ON a.course_id = b.course_id INNER JOIN courses co on a.course_id = co.course_id;

with table1 as ( SELECT course_id,COUNT(DISTINCT (user_id)) as cnt_checkins FROM checkins ch group by course_id ), table2 as ( SELECT course_id, COUNT(*) as cnt_total FROM orders o group by course_id ) SELECT co.title , a.cnt_checkins, b.cnt_total, ROUND((a.cnt_checkins/b.cnt_total),3) as ratio FROM table1 a INNER JOIN table2 b ON a.course_id = b.course_id INNER JOIN courses co on a.course_id = co.course_id;

문자열;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; SELECT u.user_id ,u.email ,SUBSTRING_INDEX(email, '@',-1) FROM users u ; SELECT o.order_no , o.created_at, SUBSTRING(o.created_at,1,10) as Date FROM orders o ; SELECT SUBSTRING(o.created_at,1,10) as Date,COUNT() FROM orders o group by Date; SELECT o.order_no , o.created_at, SUBSTRING(o.created_at,12,8) FROM orders o ; Case; SELECT pu.user_id , pu.point , ( CASE when pu.point > 10000 then '잘 하고 있어요!' ELSE '조금난 더 파이팅!' END) as msg FROM point_users pu ; SELECT pu.user_id , pu.point , ( CASE when pu.point > 10000 then '1만 이상 ' WHEN pu.point > 5000 then '5천 이상' ELSE '5천 미만' END) as lv FROM point_users pu ;
SELECT a.lv,COUNT() as cnt FROM ( SELECT pu.user_id , pu.point , ( CASE when pu.point > 10000 then '1만 이상 ' WHEN pu.point > 5000 then '5천 이상' ELSE '5천 미만' END) as lv FROM point_users pu
) a group by a.lv; with table1 as ( SELECT pu.user_id , pu.point , ( CASE when pu.point > 10000 then '1만 이상 ' WHEN pu.point > 5000 then '5천 이상' ELSE '5천 미만' END) as lv FROM point_users pu ) SELECT a.lv,COUNT() as cnt FROM table1 a group by a.lv; SELECT u.user_id ,u.email ,SUBSTRING_INDEX(email, '@',-1) FROM users u ; SELECT o.order_no , o.created_at, SUBSTRING(o.created_at,1,10) as Date FROM orders o ; SELECT SUBSTRING(o.created_at,1,10) as Date,COUNT() FROM orders o group by Date; SELECT o.order_no , o.created_at, SUBSTRING(o.created_at,12,8) FROM orders o ; Case; SELECT pu.user_id , pu.point , ( CASE when pu.point > 10000 then '잘 하고 있어요!' ELSE '조금난 더 파이팅!' END) as msg FROM point_users pu ; SELECT pu.user_id , pu.point , ( CASE when pu.point > 10000 then '1만 이상 ' WHEN pu.point > 5000 then '5천 이상' ELSE '5천 미만' END) as lv FROM point_users pu ;
SELECT a.lv,COUNT() as cnt FROM ( SELECT pu.user_id , pu.point , ( CASE when pu.point > 10000 then '1만 이상 ' WHEN pu.point > 5000 then '5천 이상' ELSE '5천 미만' END) as lv FROM point_users pu
) a group by a.lv; with table1 as ( SELECT pu.user_id , pu.point , ( CASE when pu.point > 10000 then '1만 이상 ' WHEN pu.point > 5000 then '5천 이상' ELSE '5천 미만' END) as lv FROM point_users pu ) SELECT a.lv,COUNT() as cnt FROM table1 a group by a.lv;