こんにちは!
初めてのブログ
私は初めてSQLを勉強して、一週間の勉強の総括をしました.
ありがとう!

Day 1->基本構文

データの問合せ

区切り構文例1例2表ビューshowshow表クエリーデータselect*表名select列1から、列2表名検索条件whereselect*表名no=2から指定

役に立つ文法

データの一部のみインポート:Limit

select * from 테이블명 
where 필드명 = "Data" limit 5;

冗長データをインポートしない:Distinting

select distinct(필드명) from 테이블명;

数の数:Count

select count(*) from 테이블명

データをDay 2->GROUP BYにグループ化

select 필드명, count(*) from 테이블명
group by 필드명;
-> 위 쿼리가 실행되는 순서: from → group by → select

役に立つ文法

最大値を求めます:min

select 필드명, min(최솟값을 알고 싶은 필드명) from 테이블명
group by 필드명;

最大値を求めます:max

select 필드명, max(최대값을 알고 싶은 필드명) from 테이블명
group by 필드명;

平均値を求めます:avg

select 필드명, avg(평균값을 알고 싶은 필드명) from 테이블명
group by 필드명;

和を求める:和

select 필드명, sum(평균값을 알고 싶은 필드명) from 테이블명
group by 필드명;

昇順で並べ替え:Order by

select 필드명 from 테이블명 where 조건식 order by 열명; //기본이 오름차순
select 필드명 from 테이블명 where 조건식 order by 열명 ASC;

順序付き降順:Order by

select 필드명 from 테이블명 where 조건식 order by 열명 DESC;

接続Day 3->複数のテーブル

Joinタイプ：Left Join、Inner Join

Inner Join
簡単に言えば、交わればいいと思います.条件テーブルとJoinテーブルの重複値を表示します.

select * from 테이블명 A
inner join 테이블명 B
on A.기준키 = B.기준키;

Left Join
条件テーブルの値+テーブルと条件テーブルの重複値を表示します.

select * from 테이블명 A
left join 테이블명 B
on A.기준키 = B.기준키;

役に立つ文法

成果物のマージ:Union all

(
	select '7월' as month, c.title, c2.week, count(*) as cnt from checkins c2
	inner join courses c on c2.course_id = c.course_id
	inner join orders o on o.user_id = c2.user_id
	where o.created_at < '2020-08-01'
	group by c2.course_id, c2.week
  order by c2.course_id, c2.week
)
union all
(
	select '8월' as month, c.title, c2.week, count(*) as cnt from checkins c2
	inner join courses c on c2.course_id = c.course_id
	inner join orders o on o.user_id = c2.user_id
	where o.created_at > '2020-08-01'
	group by c2.course_id, c2.week
  order by c2.course_id, c2.week
)

Day 4 -> Subquery

Subqueryとは?
1つのクエリ文に含まれる別のクエリ文.

select u.user_id, u.name, u.email from users u
where u.user_id in (
	select user_id from orders
	where payment_method = 'kakaopay'
)

一般的なSubqueryタイプ
Subqueryはwhere、select、fromセクションで非常に役立ちます
WhereのSubqueryに入る

select * from users u
where u.user_id in (select o.user_id from orders o 
		    where o.payment_method = 'kakaopay');

SelectのSubqueryに入る

select c.checkin_id, c.user_id, c.likes, 
	(select avg(likes) from checkins c2
	 where c2.user_id = c.user_id) as avg_like_user
from checkins c;

FromのSubqueryに入る

select pu.user_id, a.avg_like, pu.point from point_users pu
inner join (
	select user_id, round(avg(likes),1) as avg_like from checkins
	group by user_id
) a on pu.user_id = a.user_id

役に立つ文法

SubQuery:Withの名前と使用

WITH Table1 AS
(
 SELECT 'WITH절' AS STR1
 FROM DUAL
)

SELECT * FROM Table1