A database is a structured set of data held in a computer that is accessible in various ways. WordPress uses a database to store all of your content that your create on your website.
Databases are designed to store information so that it is easy to retrieve or manipulate later. Databases are like filing cabinets. You store your data in organized drawers (databases call them tables) and the files (databases call them rows or records) can be retrieved or removed or updated with ease as you need.
There are many different types of databases used throughout different operating systems and software applications. Each type of database is suited for certain things. WordPress uses one of the most popular types of databases called MySQL.
MySQL is a relational database. This means that it is stored in tables and can be linked together to form a “relationship”. Relations help further organize the data in your database. The good thing about WordPress is that you do not need to worry about these technicalities as it is all handled for you.
MySQL is also used by a number of other popular content management systems and many closed source projects. Google, Facebook, Twitter, Flickr, and YouTube all use MySQL in one way or another in their organizations.
One of the other main attractions for why WordPress uses MySQL for its database is because it is free and open source just like WordPress.
WordPress requires MySQL to operate. Every WordPress hosting provider will have this available for you in their hosting packages.
WordPress connects to your database by using a hostname, username and password. You provide this information to your website when you are first setting up WordPress. Once you have provided this information, WordPress will take care of the rest and you do not need to worry about this information after that.
If you are using a managed WordPress hosting provider then you will not need to even worry about finding this information because it is setup for you.
What is my database host
The database host refers to the computer or server that stores your database. You connect to your database host using its hostname address.
In most cases your database will be stored and managed on the same hosting server as where your website is located. If this is the case then you will be able to use localhost as your hostname because in computing, localhost refers to the same location that is being referred from.
When you are setting up wordpress, you can use localhost in the host field and that should connect WordPress to your database.
If localhost does not work then it is likely your host provider is using another address to connect to your database. You can log into your hosting provider to find the server hostname in the databases section. Each hosting provider is different so finding your database details may not be as straightforward as others. If you contact your hosting provider, they will be able to tell you the right details.
What are Database tables
Each database has tables which are like sections or draws in your filing cabinet database system. Each table has columns and the information is stored in rows. It is kind of like a spreadsheet where there are rows of information set across columns with data in each field.
Example: a car rental database may have a table called available_cars. This table may have following columns:
Each car will have its own row with the information filled out across the column.
When WordPress is setup, it will automatically create the necessary tables inside your database to run WordPress. The tables that WordPress creates are: wp_commentmeta, wp_comments, wp_links, wp_options, wp_postmeta, wp_posts, wp_termmeta, wp_terms, wp_term_relationships, wp_term_taxonomy, wp_usermeta, wp_users.
Each table corresponds to a certain part or use of WordPress. They each have unique columns to hold different rows of data. For example the wp_users table has these columns: ID, user_login, user_pass, user_nicename, user_email, user_url, user_registered, user_activation_key, user_status, display_name
What is an SQL query?
An SQL query is the instruction you give to the database to perform a specific task. Common querys include creating records, reading records, updating records or deleting records from the database.
The four common queries are often referred to in programming as CRUD (Create, Read, Update, Delete).
SQL is an abbreviation for Structured Query Language. This language is a language made for managing databases. Each time a dynamic website that uses a database (like WordPress) is accessed a special instruction written in SQL (called a query) is sent to the database to retrieve information to display the page.
SQL queries are sent to the database for any other action you take on the site like deleting or updating a page or logging in and viewing your media.
WordPress is programmed to do all of the database heavy lifting for you as you do not need to touch it to manage your site.
What does an SQL query look like?
A typical MySQL query looks like this:
SELECT * FROM wp_users WHERE ID = 2;
The above SQL query will go to the wp_users table and select everything where the id of the row contains 2. This is a basic query to get the second user but queries can get a lot more complex than that. Queries can even create entirely new tables which is what WordPress does when it first sets up your website.
The queries will change depending on what action is being taken in WordPress. For example if you are deleting then you would begin the query with a DELETE statement.
How to Manage your WordPress Database
Sometimes you may be required to perform certain tasks on your WordPress database. You can create queries in your theme or a plugin and perform them but this requires a lot of knowledge and there are easier ways.
On your hosting server is a tool that connects to your database called phpMyAdmin. phpMyAdmin is a web application designed to make administering your database easier. It makes administering your MySQL database easy because it has a graphical interface allowing you to click buttons rather than writing out all of the queries.
phpMyAdmin is another way to also backup and restore your database though it is even simpler still to backup using a plugin.