OK, I think I at least partially figured this out.
Short answer: don't use ImportValue in a nested stack, because you can't, because you don't know the sub-stack's name.
Long answer: Apparently it comes down to information sharing. I don't know why I couldn't find this yesterday, but this doc says "if you want to isolate information sharing to within a nested stack group, we suggest that you use nested stacks [and GetAtt]. To share information with other stacks (not just within the group of nested stacks), export values [and ImportValue]." But it's not actually just about whether you want to share information, it's about what information you have available. Although it's not specified in Specifying Stack Name and Parameters, the CLI documentation explains that "The name must be unique in the region in which you are creating the stack.". To use ImportValue, you need to know the name of the stack from which you're importing, but when you create a nested stack, the sub-stacks are given randomly generated suffixes, to satisfy this constraint, so you don't actually know the name.
So, if you have
infrastructure-parent:
-> vpc
-> security-groups
-> bastion
-> NAT
-> ALB
database-parent:
-> RDS
-> phpMyAdmin
For bastion to use something from security-groups, you use GetAtt. But for RDS to use something from vpc, you use Export/ImportValue, both because you can't use GetAtt, and because you don't know the name of the generated vpc stack (ergo, you create a vpcStackName parameter in RDS (and database-parent) to allow passing the name).
I guess the tricky part comes (and this is where my original confusion was generated) when you try to then nest multiple levels. So, if you take the above, and try to do
environment-parent:
-> infrastructure-parent
-> database-parent
the environment-parent needs to know the stack name that got generated for vpc, so it can pass it as a parameter to database-parent (which then passes it to RDS). Thus, the RDS and database-parent templates don't need to change, but the vpc template needs to Output the AWS::StackName pseudo parameter. infrastructure-parent can then get this using GetAtt, and then Output it again so that it's available for GetAtt in environment-parent, which can then pass it as a parameter to database-parent.