Beginner Series: MySQL Tutorial
For this section, we’re going to assume that you’ve already installed the necessary components and that you have a working database. If that assumption is wrong, you may refer to our previous section on getting started to learn how to do this.
Creating Your First Table
Now that you have created your first database, it’s time to populate it with some data! At the moment it’s empty so it’s of no use to you or anyone else in this state.
Assuming you’re coming from our previous tutorial on building your first database we’re going to use the same parameters.
We are going to create a table called “names” that includes the following content: key, first name, last name.
To call upon our database, you must enter the following into the command prompt (if you haven’t already open the Command Line Client from the MySQL program group):
USE test_db;
Next, you’ll need to specify the data parameters for the content you wish to enter into your database. You do this with the following code:
CREATE TABLE names (id INT NOT NULL AUTO_INCREMENT, first VARCHAR(50), last VARCHAR(50), PRIMARY KEY(id));
We are not going to cover in detail the syntax used to deal with the table. If you would like to better understand the commands and tags we used in the last line of code, refer to the MySQL documentation.
To ensure the table was created properly – and that our last line of code was correct – we can use the “describe” command to see the structure of the table.
Enter the following into the prompt and press enter:
describe names;
It should return a representation of your table with the fields, and related info presented in table form. If that is what you see, you may continue.
The next step is to actually add some data to our table for the database to store. We are going to leave this step up to you entirely. You may enter as many values as you like, using custom data.
You can do all that with the following line of code:
INSERT INTO names (first, last) VALUES ('John', 'Doe');
Pay attention to how you use the quotation marks to signify the beginning and end of each value. If you make a mistake here the prompt will return an error.
Once you are satisfied with the amount of information you have entered, you may display the table in it’s entirety using the following code:
SELECT * FROM names;
If all goes well, you should see a formatted table containing all the information you entered.
Congrats! You have created your first table and entered your first set of data! Go ahead and pop the champagne, but don’t get too buzzed yet. We have more work to do!
Create a User Account
After you have your database and the data has been populated, you’ll want to create a user access account so you can connect and reference the data stored within. You can do this with the “root” database account, but it is considered bad practice to do this. You’re better off – and safer – creating a separate user account for this.
To create a user account with access to the database you created in the steps above, use the following line of code:
GRANT ALL PRIVILEGES on test_db.* to yourname@'localhost' identified by 'helloworld';
After you have entered this code into the prompt, you should be able to login to your database. Please note that the username is “yourname” and the password is “helloworld”.
For the final part of our tutorial, we’ll take a look at additional Resources you can use to learn more about MySQL and PHP.
Disclosure of Material Connection: Some of the links in the post above are “affiliate links.” This means if you click on the link and purchase the item, I will receive an affiliate commission. Regardless, I only recommend products or services I use personally and believe will add value to my readers.
