MYSQL
같은 것과 같지 않은 것
select * from orders
where course_title != '앱개발 종합반'
and payment_method = 'kakaopay';
between, ><
select * from point_users
where point > 20000;
select * from orders
where created_at between '2020-07-13' and '2020-07-15';
in
select * from checkins
where week in (1, 3);
Like
select * from users
where email like '%daum.net';
r을 두번째에 두는 email들
where email like '_r%';
최소한 세글자인 a로 시작하는 email
where email like 'a__%';
Limit
select * from orders
where payment_method = 'kakaopay'
limit 5;
데이터 갯수 제한하기
Distinct
select distinct(payment_method) from orders;
중복 데이터는 제외하고 가져오기
COUNT
SELECT COUNT(*) from orders o
WHERE payment_method = 'kakaopay';
Group by
Select name, count(*) from users
group by name;
SELECT week, count(*) from checkins
group by week;
min, max, avg, sum 가능
SELECT week, min(likes) from checkins
group by week ;
select week, round(avg(likes), 2) 로 소숫점 둘째자리까지 컷트 가능
order by
SELECT name, count(*) from users
group by name
order by count(*) DESC;
기본 ASC
as(별칭)
as cnt
join
left join은 왼쪽에다가 붙이기
inner join은 null은 제외하고 교집합만 붙이기
복합
SELECT c.title, c2.week, COUNT(*) as cnt FROM courses c
inner join checkins c2 on c.course_id = c2.course_id
GROUP BY c2.week, c.title
order by c.title, c2.week
UNION ALL
(
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
)
스프링 JPA H2 등등...
JPA에 객체 등록
@NoArgsConstructor // 기본생성자를 대신 생성해줍니다.
@Entity // 테이블임을 나타냅니다.
public class Course {
@Id // ID 값, Primary Key로 사용하겠다는 뜻입니다.
@GeneratedValue(strategy = GenerationType.AUTO) // 자동 증가 명령입니다.
private Long id;
@Column(nullable = false) // 컬럼 값이고 반드시 값이 존재해야 함을 나타냅니다.
private String title;
@Column(nullable = false)
private String tutor;
public String getTitle() {
return this.title;
}
public String getTutor() {
return this.tutor;
}
public Course(String title, String tutor) {
this.title = title;
this.tutor = tutor;
}
}public interface CourseRepository extends JpaRepository<Course, Long> {
}// Week02Application.java 의 main 함수 아래에 붙여주세요.
@Bean
public CommandLineRunner demo(CourseRepository repository) {
return (args) -> {
};
}