mysql 行转列；列转行；内连接；外链接；case；转换查询语句

创建表单：

--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;