连表查询
例:查询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
查询表结构
- 查看表整体结构
desc refund_processes;
- 查看表的字段名
--这个会查出数据库所在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';
- 查看表的字段名,把字段名在一行打印出来
select group_concat(column_name) from information_schema.columns where table_schema=database() and table_name='refund_processes';