Drupal - Where are the content types stored in the database?

It's configuration. Like any other config entity and simple configuration, it is stored in the {config} table.

But using the entity API is the only supported way to load that information, do not query that table directly.