创建表单:
--datetime 和 timestamp 区别 范围不同且timestamp带有时区
create table demo(id int,name varchar(255),createdate date);
1:转换
insert into demo values(convert('1',signed),convert(123,char),convert('2018-12-01',date));
insert into demo values(cast('2' as signed),cast(now() as char),cast('2018-12-02' as date));
--强大到可以自己转换
insert into demo values("1",now(),'2017-01-01')
2:行转列、列转行
行转列:select concat_ws('|',id,name,createdate) cols from demo;(第一个为分隔符)
列转行:
select group_concat( distinct name order by id desc separator '|') name from demo;(将name转为行用|分割去重并排序)
select group_concat(name) from demo;(默认以逗号)
3:case
select name,
case name when 'tom' then 'tom is shagua'
when 'rose' then 'rose lovely'
else '不知道是什么鬼'
end newName
from demo;
4:内连接外链接
内连接:
普通内连接:select * from table1 inner join table2 on table1.id = tabl2.id;
隐式内连接:select * from table1 a,table2 b where a.id = b.id;
外链接:
左外链接:select * from table1 left join table2 on table1.id=table2.id 左面全显示
右外连接:select * from table1 right join table2 on table1.id =table2.id 右边全显示
5:分页查询
select * from table limit M,N;M表示从第M+1行开始查询N行(查询前n行m可以不写)
select * from demo limit 0,2;
6:滤空函数
select name, if(name='tom','tom is shazi','who are you') newName from demo;
select name, ifnull(name,'I am null') newName from demo;(注意空字符串不能识别只能是null)
7:oracle中的个别查询简介
7.1:case 和decode
SQL> select ename,job,sal 涨前,
2 case job when 'PRESIDENT' then sal+1000
3 when 'MANAGER' then sal+800
4 else sal+400
5 end 涨后
6 from emp;
SQL> select ename,job,sal 涨前,
2 decode(job,'PRESIDENT',sal+1000,
3 'MANAGER',sal+800,
4 sal+400) 涨后
5 from emp;
7.2:滤空
select * from nvl2(a,b,c) from table;如果a不为空则为b否则为c
7.3:分页
select * from (
select rownum r,e1* from (select * from emp order by sal) e1
where rownum <=8
) where r >=5;