The access keys form a hierarchy: when deciding if you can travel on a way, you start by looking for the most-specific key (in your case, motorcycle=), and work your way up (motor_vehicle=, vehicle=, and finally access=) until you find a match.
If you tag the way as motorcycle=yes and motor_vehicle=no:
A motorcycle will find the motorcycle=yes tag and know immediately that a motorcycle is permitted.
A car will look for motorcar= and not find it, then look for motor_vehicle= and see that motor vehicles are not allowed.
A bicycle will look for bicycle= and not find it, look for vehicle= and not find it, look for access= and not find it, and go with the default access for the type of way.
This is perfectly legal and normal road a sign banning access from all motor vehicles and then clarifying that mopeds and residential access are allowed.
So it would evaluate to motor_vehicle=destinationmoped=yes.
The sign in question is a Vienna Convention one and is a good example, because if mopeds (even what a moped is) are considered included in the motorcycle category is one of the differences between signatory countries.
If we go strictly to Vienna Convention, that sign doesn’t forbid just cars and motorcycles. It’s “No entry for power-driven vehicles”. A “moped” is a “power-driven vehicle”.
OSM hierarchy mismatching actual legal definition (that differ between countries) often cause issues, but in this specific case (“no motor vehicles except motorcycles”) it works fine
and your proposed confusing hierarchy is actually far worse
Whether mopeds are treated as power-driven vehicles in the Vienna Convention depends on national legislation. From the Convention:
“Motor cycle” means any two-wheeled vehicle with or without a side-car, which is equipped with a propelling engine. Contracting Parties may also treat as motor cycles in their domestic legislation three-wheeled vehicles whose unladen mass does not exceed 400 kg (900 lb). The term “motor cycle” does not include mopeds, although Contracting Parties may, provided they make a declaration to this effect in conformity with Article 46, paragraph 2, of this Convention, treat mopeds as motor cycles for the purposes of the Convention;
“Power-driven vehicle” means any self-propelled road vehicle, other than a moped in the territories of Contracting Parties which do not treat mopeds as motor cycles, and other than a rail-borne vehicle;
It is useful to explicitly add tags for moped and mofa because these are included in the OSM definition of motor_vehicle. For example, when tagging this sign in the Netherlands, it gets the additional tags moped=yes (because mopeds are not considered motorcycles in the Netherlands) and mofa=yes.
Not quite sure what your point is? Laws put vehicles into categories and some categories overlap in a way that one includes the other, hence the possibility to exclude some modes of transport from restrictions.
Technically, this forms a partial order, because a vehicle can belong to multiple independent categories. This is CS-nerd territory.
OSM always has to figure out a compromise between global consistency and local deviations.
On the one hand, one could just defer to “local legislation” and define motor_vehicle to be whatever the country in question defines as such; this can include mopeds or not. This hasn’t happened, for various reasons, so OSM uses a single “hierarchy” for all countries with its own definition for what qualifies as what.
Which categories to use and how to define them isn’t a technical question, it’s a matter of consensus.
Since OP didn’t mention any country, the best answer to
What is the best way to say “Legally , no motorized vehicles except motorcycles”?
is “It depends”, since all provided answers are wrong. If you want to add that detail, you are free to do so.
