I believe you are talking about a confidence interval for estimating the mean of a normal distribution $\mathrm{N}(\mu,\sigma^2)$, when the variance is unknown and has to be estimated.

But let us first look at the simpler case where we know the true variance $\sigma^2$. Then, we know that the sample mean $\bar{X}$ is normally distributed with mean $\mu$ and variance $\sigma^2/n$. Hence, $$\frac{\bar{X}-\mu}{\sigma/\sqrt{n}}\sim\mathrm{N}(0,1).$$ That is the reason why you have to look up the quantiles in the table for the standard normal distribution.

The 95-percent confidence interval is supposed to overlap the true mean $\mu$ with probability 0.95, i.e., $$P\left( -z_{0.975} \leq \frac{\overline X-\mu}{\sigma/\sqrt{n}} \leq z_{0.975} \right) =0.95. $$ Rearranging gives you the confidence interval $$\left[ { \overline x-z_{0.975}\frac {\sigma}{\sqrt{n}}; \overline x+z_{0.975}\frac{\sigma}{\sqrt{n}}} \right]$$

Now, in the case where we have to estimate $\sigma^2$ using the sample variance $s^2$, everything works the same, just that $$\frac{\bar{X}-\mu}{s/\sqrt{n}}$$ does not follow a normal distribution, but a t-distribution with $n-1$ degrees of freedom.