If you type in a new size and check the resample box, Photoshop will resample the image to that size and resolution. In other words, it will add or remove pixels (resampling) from the image so that there are 300 pixels per row of pixels per inch at the size you specify.
If you type in a new size and do not check the resample box, Photoshop will change the output size without any resampling. In other words, no pixels will be added or removed to create the new output size.
For example, let’s say you have a 10x10-inch image that is 100ppi. If you reduce that image to 5x5 inches without resampling, you will be crowding the same number of pixels into a smaller space, which in this case, means you will end up with 200 pixels in an inch-long row of pixels. In other words, reducing the image dimensions by half, doubles the resolution because the same number of pixels are crowded into a smaller output size.
Upsampling adds pixels through interpolation — a process where algorithms make guesses as to what extra pixels should be added. For most practical purposes, it sort of amounts to averaging things upwards Downsampling removes pixels through interpolation.
It rarely does any good to upsample an image for the reasons PrintDriver mentioned. Photoshop can add more pixels, but it can’t do magic by adding detail to a photo that isn’t there. There are applications and plugins with algorithms that make interpolation guesses as to additional detail when upsampling, like finding a sharp edge and maintaining that sharp edge, but in general, these upsampling applications don’t produce great results.
Downsampling is mainly useful for reducing the resolution of images that would otherwise be larger than necessary. For example, if you have a 10x10-inch image that is 300ppi and reduce that image to 5x5 inches without resampling, the resolution of that image will increase to 600ppi, which, for a photo, is usually higher than needed. Checking the resample box and specifying that you want it resampled to 300ppi will remedy this problem by reducing the number of pixels.