I know it's for site layout customization and things like that, but I'm not sure how it works and how I would apply my own code.

I think this is one of the obscure features most people don't bother touching, so I'm not sure how much good advice you'll be able to find relating to e621 itself.

I found this guide which is specifically for WordPress, but it's pretty much the exact same thing.

just mess around in the element viewer and copy the changes from there into the stylesheet.

right click on the page you want to modify, select "Inspect Element" (or just "Inspect" on Chrome or something similar on other browsers).
a big list of HTML elements will show up at the bottom of your screen, and to the right of that is the CSS rules that effect that element.
near the top left of this new box is the element picker, click this and then mouse over to the thing you want to modify until it seems like what you want to mess with is selected.
on the right you can now modify the values of existing rules or add new rules for the element we have selected.
after you're comfortable with your modifications, copy the section you modified and paste it into the style sheet.
save, then refresh the page or open a new page and your modifications should show up.

so, lets say you wanted the user avatars to be slightly smaller for some reason, the thing you want to paste into the custom CSS would be something like
.comment-post-grid .author-info .avatar .post-thumbnail img { max-height: 96px; max-width: 96px; }

".comment-post-grid .author-info .avatar .post-thumbnail img" is the selector that tells the page on which elements to use the following rule(s), "max-height: 96px" and "max-width: 96px" are the two rules we're modifying, { and } are used to contain the rules, and ;is used to separate the rules from each other or whatever comes next.

It's the answer to a lot of tiny issues you may have. Just look at topic #35671 from the other week.

Here's some of the crap in mine from when I was demonstrating making your own color scheme and I just kept the changes:

body {background:#000000}
#nav menu {background:#333333}
#nav menu.main {background:#444444}
#nav menu.main li.current a {background:#AAAAAA; color:black}
#page {background:#333333}

/* Forum */
.list-of-forum-posts .author-info {background:#222222}
.list-of-forum-posts .content {background:#111111}
.list-of-forum-posts .forum-post {background:#000000}
.list-of-forum-posts blockquote {border-left-color:navy; background:#555}
.list-of-forum-posts pre {border-left-color:green; background:#555}
#new_forum_post {background:#444444}
#new_forum_post textarea {background:#000000; color:#CCCCCC}

footer {background:#333333}

Here's how to make the CSS box bigger in the first place, put some extra space around wiki articles, and fix some issue with word wrapping I had on my profile:

/* Make the custom CSS style box bigger */
#user_custom_style {min-height:400px; width:100%;}

#wiki-page-body {margin: 2em 0 2em 0;}

.about-section p {word-break: break-word}

Here's the beginning of your CSS education:

https://developer.mozilla.org/en-US/docs/Learn/Getting_started_with_the_web/CSS_basics
https://developer.mozilla.org/en-US/docs/Web/CSS/CSS_Selectors