Let's consider a basis on which we might judge what it means for a test to "work".
In simple terms (in the form most commonly conceived at least, more or less a Neyman-Pearson style test), hypothesis tests consist of
A null hypothesis
An alternative hypothesis
A test statistic which is calculated from sample data which should have different behavior when the null is false than when it is true (or the test is not helpful).
A rejection rule (from which a significance level could be obtained) or a significance level (from which a rejection rule is obtained)
A rejection rule says under what circumstances (what values of the test statistic) the null would be rejected. The significance level is the probability of rejecting the null when it's true (or the highest such probability if the null is not simple). Either will tell you the other.
See here for a definition of some of the terms.
From these, we can compute the power of the test (the probability of rejecting the null when it is false) under any specific set of circumstances. When a variety of situations are considered (e.g. you mention testing significance of correlation; you might look at what happens as the correlation changes at some fixed sample size, or what happens as the sample size changes at some fixed correlation), you can obtain a power curve.
Examples of power functions can be seen here:
a. power curves for several tests, including a couple of power comparisons of different tests
Here's one example from there:
This compares two tests of normality (with unspecified mean and variance) against a sequence of alternatives of increasingly skew gamma distributions. We can see that at the same significance level (5% in this case), the Shapiro Wilk has better power than the Lilliefors for this set of alternatives (at the sample size this was calculated at, I think it was for n=30 -- though the general pattern of behavior is similar at larger and smaller typical sample sizes).
b. binomial test
c. effect of changing significance level on power
[Sometimes such an examination of power behavior can reveal serious problems with tests people do actually use. For example, see this blog post which shows serious problems with the RESET test.]
If you consider several things changing at once, you may get a power surface, but they're harder to compare visually.
Clearly you want to have power as high as possible and you want the significance level as low as possible, but at a fixed sample size they move up or down together, so there's a tradeoff in choosing the significance level -- the lower you make it (and so the lower the type I error rate), the lower the power (the higher the type II error rate).
So for a given significance level we can compare the power of two tests. It's quite common (but sometimes misleading) to ignore small samples and simply compute the asymptotic relative efficiency of tests - in effect, compare the ratio of their power vanishingly close to the null as the sample size goes off to infinity. There are theorems that help come up with tests with the highest asymptotic relative efficiency (and so will come to have the best power as samples become sufficiently large); these are sometimes (if potentially misleadingly) just called "efficient tests". Fortunately those tests usually have good properties in small samples as well.
In some cases you can prove that particular tests are the uniformly most powerful.
An example of this is the widespread use of likelihood ratio tests (for which Wilks' theorem may be used), and so also Wald tests and score tests, which are asymptotically equivalent. However, unless you can work out the small sample distribution of the test statistic (or some function of it), the significance level you choose will generally only be approximately correct in small samples.
One characteristic that is often important is robustness to assumptions. For example a t-test (the usual equal variances version) to compare means is robust to moderate non-normality (both in terms of significance level being close to the chosen one, and in terms of maintaining reasonable power, especially if the tails are not too heavy), but less robust to changes in variance unless the sample sizes are equal (or very close to it) . An F-test for variances is not at all robust to non-normality.
So there are several senses in which tests may be considered to 'work', including:
do they have the actual significance level you chose?
do they have good power against alternatives you care about?
are they able to deal with some degree of failure of assumptions?
Numerous texts discuss these and other properties of tests, such as
consistency (as sample sizes increase, does the test eventually always reject false nulls?); almost every test in wide use is consistent.
sufficiency (does the test statistic contain all the sample information relevant to what you're testing? This is mostly of practical interest only in so far as it relates to efficiency)
bias (does the test have its lowest rejection rate somewhere other than at the null? This is often of direct fairly practical import, since it represents behavior that is somewhere worse than just rejecting the null at random with probabilty $\alpha$ -- but people often tolerate somewhat biased tests in practice)
For the most common tests, many of these things are well understood; as an example, a chi-squared test of multinomial goodness of fit is known not to be unbiased in general but is asymptotically efficient. Most goodness of fit tests have bias issues (e.g. try the Anderson-Darling test with uniform null against a "hill shaped" symmetric beta alternative)
When calculation of power, significance level or robustness is not analytically tractable, simulation methods are often used. [You don't even need to be able to derive the distribution of the test statistic to invent new tests; simulations/resampling methods can be used]
Even on a cheap laptop hundreds of thousands of tests might be simulated in a few moments, allowing accurate calculation of (say) rejection rates under a variety of situations in a reasonably short period of time.
To return to the matter of a text that covers this sort of detail for a hundred tests, I don't think any text does so (one might, but I have never heard of one). Once the principles are understood, there's probably little need for an encyclopedia of power for 90 tests you never use, 5 you use once in a blue moon and 5 you use fairly often.
[For the little bit of actual hypothesis testing I do, the behavior of the t-test and F tests in regression and the similar asymptotic tests in a GLM probably cover most cases, along with the occasional chi-square -- but usually my interest is on effect sizes and prediction intervals rather than testing]
