https://sir.kr/so_g5tuning/37 에서 mysql sample database를 설치하세요.

SQL Union and Union All

 

select * from mysqlsampledb.customers
where customerNumber between 100 and 120
union all
select * from mysqlsampledb.customers
where customerNumber between 100 and 120;
 

select * from mysqlsampledb.customers
where customerNumber between 100 and 120
union 
select * from mysqlsampledb.customers
where customerNumber between 100 and 120;

https://www.youtube.com/watch?v=Jva68Yh_s1E&list=PLD20298E653A970F8&index=34

SQL Add

+는 숫자 컬럼은 더하기가 됩니다. 

select salary, ifnull(commission,0), salary + ifnull(commission,0) from simpledb.employee;

Date 더하기는? 

select hire_date, hire_date+2 from simpledb.employee;

date_add함수를 사용해야 https://www.w3schools.com/sql/func_mysql_date_add.asp

select hire_date, date_add(hire_date, interval 2 day) from simpledb.employee;

https://www.youtube.com/watch?v=9bmGWrmOHng&list=PLD20298E653A970F8&index=35

DateTime

select cast('20121019' as datetime);
select cast('20121019' as date);
select cast('20121019101010' as datetime);

select now();
select cast(now() as date);
select cast(now() as time);

select convert(hire_date, date) from simpledb.employee;

date_format 함수로 변경 https://www.w3schools.com/sql/func_mysql_date_format.asp

select date_format(hire_date, '%M %d %Y') from simpledb.employee;

https://www.youtube.com/watch?v=l4l3w6z5OOE&list=PLD20298E653A970F8&index=36

SQL Date Comparison

select * from simpledb.employee
where hire_date ='2005-02-22';

데이타 양이 많을 때 Date Compare에서 시간이 많이 걸리는 것 같습니다.

https://stackoverflow.com/questions/2758486/mysql-compare-date-string-with-string-from-datetime-field

https://www.youtube.com/watch?v=A7E3EdZpPOU&list=PLD20298E653A970F8&index=37

SQL Format Function

mysqlsampledb로 사용

select * from mysqlsampledb.orders
where month(orderdate) = 7;

date_format 과 format

select orderdate, date_format(orderdate, '%d') from mysqlsampledb.orders
where month(orderdate) = 7;

https://www.youtube.com/watch?v=rCj-3asMFwE&list=PLD20298E653A970F8&index=39

SQL CTE Statement

http://www.mysqltutorial.org/mysql-cte/

with cteEmployee
  ( employeenumber, firstname, jobtitle)
as
(
  select employeenumber, firstname, jobtitle from mysqlsampledb.employees
)
select * from cteEmployee;

https://www.youtube.com/watch?v=5KGjqnMss7g&list=PLD20298E653A970F8&index=42

SQL Copy Table

SQL Table이 자동으로 만들어 지지는 않고.

create table ...  like ... 로 만들수가 있습니닫.

create table simpledb.employee_test like simpledb.employee;

select into는 없는 것 같고, insert into로.. (into는 옵션..)
insert into simpledb.employee_test select * from simpledb.employee;
https://www.youtube.com/watch?v=nEndOUQFaOI&list=PLD20298E653A970F8&index=43