sql

连表查询

例：查询uid=390001693 所在退费流程的审批人aid

退费流程表refund_processes中，uid=390001693，退费流程id=134

审批人表refund_process_approvers中，退费流程process_id=134

refund_processes.id=refund_process_approvers.process_id

select * from refund_processes as a join refund_process_approvers as b on a.id=b.process_id where a.uid=390001693;

select a.id as '退款ID',a.uid as '学生ID',a.status as '状态',a.created_at as '退费创建时间',b.process_id as '流程号',b.aid as '审批人aid',b.created_at as '审批创建时间'
from refund_processes as a join refund_process_approvers as b on a.id=b.process_id where a.id=134;

select a.id as '退款ID',a.uid as '学生ID',a.status as '状态',
case a.status 
when 1 then '已受理'
when 2 then '办理中'
when 3 then '信息审核中'
when 4 then '退费中'
when 5 then '退费完成'
when 6 then '退费取消'
when 7 then '信息审核中'
when 8 then '退费中'
when 9 then '退费中' end as '退费状态',
a.created_at as '退费创建时间',b.process_id as '流程号',b.aid as '审批人',b.created_at as '创建时间'
from refund_processes as a join refund_process_approvers as b on a.id=b.process_id where a.id=134

条件前面是 where,   一般格式为 select 字段1，字段2....  from  表名  where  条件 order by 字段 group by 字段 limit  0,10

多表查询

select a.字段1,a.字段2,b.字段1,b.字段2,c.字段1 from 表1 as a 
join 表2 as b on a.字段1=b.字段2 
join 表3 as c on b.字段1=c.字段2 
where a.字段1=100

查询表结构  

1. 查看表整体结构

   desc refund_processes;

    

2. 查看表的字段名

   --这个会查出数据库所在IP所有表名为'refund_processes'的所有字段
   select column_name from information_schema.columns where table_name='refund_processes';
   
   --查看当前库内表字段和字段信息
   show columns from refund_processes;
   
   --查看数据表的字段名
   select column_name from information_schema.columns
   where table_schema=database() and table_name='refund_processes';
   

    
3. 查看表的字段名，把字段名在一行打印出来

   select group_concat(column_name) 
   from information_schema.columns 
   where table_schema=database() and table_name='refund_processes';