MySQL：関数と演算子 #12

#(15)関数と演算子
-- (1)基本演算
select 10+30;
select 10-3;
select 10*3;
select 10/3;
select 10%3;
-- (2)nullを含む演算
select 10+null; #null
select 10-null; #null
select 10*null; #null
select 10/null; #null
select 10%null; #null
-- (3)abs関数
select abs(10); #10
select abs(-10);#10
select abs(0);  #0
-- (4)round関数
select id,name,price*1.08 from products;#小数点込みで出力される
select id,name,round(price*1.08,0) from products;#少数第一位で四捨五入
select round(10.555,0);#11
select round(10.555,1);#10.6
select round(10.555,2);#10.56
-- (5)文字列の連結
select concat(last_name,' ',first_name,'さん') from users;
select concat(last_name,'さん'),email from users where gender=2;
-- (6)日付と時刻
select current_date();#2019-01-03
select current_timestamp();#2019-01-03 21:56:45
select current_date()+3;#20190106
select current_date()-3;#20190100
select current_time()+interval 6 hour;#27:58:11
select current_time()-interval 6 hour;#15:58:45

select * from orders where extract(year_month from order_time)=201701;
select * from orders where extract(year from order_time)=2017;
select * from orders where extract(month from order_time)=1;

#(15)関数と演算子

-- (1)基本演算

select 10+30;

select 10-3;

select 10*3;

select 10/3;

select 10%3;

-- (2)nullを含む演算

select 10+null; #null

select 10-null; #null

select 10*null; #null

select 10/null; #null

select 10%null; #null

-- (3)abs関数

select abs(10); #10

select abs(-10);#10

select abs(0); #0

-- (4)round関数

select id,name,price*1.08 from products;#小数点込みで出力される

select id,name,round(price*1.08,0) from products;#少数第一位で四捨五入

select round(10.555,0);#11

select round(10.555,1);#10.6

select round(10.555,2);#10.56

-- (5)文字列の連結

select concat(last_name,' ',first_name,'さん') from users;

select concat(last_name,'さん'),email from users where gender=2;

-- (6)日付と時刻

select current_date();#2019-01-03

select current_timestamp();#2019-01-03 21:56:45

select current_date()+3;#20190106

select current_date()-3;#20190100

select current_time()+interval 6 hour;#27:58:11

select current_time()-interval 6 hour;#15:58:45

select * from orders where extract(year_month from order_time)=201701;

select * from orders where extract(year from order_time)=2017;

select * from orders where extract(month from order_time)=1;

この記事を書いた人
最新の記事

Keita N

最新記事 by Keita N (全て見る)