MySQL Tutorial: Managing MySQL data

woman next to laptop
Thomas Barwick/Getty Images

Once you have created a table, you need to add data into it. If you are using phpMyAdmin, you can manually enter this information. First select people, the name of your table listed on the left-hand side. Then on the right-hand side, select the tab called insert and type in the data as shown. You can view your work by selecting people, and then the browse tab.

Insert Into SQL - Add Data

Angela Bradley

A quicker way is to add in data from the query window (select the SQL icon in phpMyAdmin) or a command line by typing:

 INSERT INTO people VALUES ( "Jim", 45, 1.75, "2006-02-02 15:35:00" ), ( "Peggy", 6, 1.12, "2006-03-02 16:21:00" ) 

This inserts the data directly into the table "people" in the order shown. If you are unsure what order the fields in the database are, you can use this line instead:

 INSERT INTO people (name, date, height, age) VALUES ( "Jim", "2006-02-02 15:35:00", 1.27, 45 ) 

Here we first tell the database the order we are sending the values, and then the actual values.

SQL Update Command - Update Data

Angela Bradley

Often, it is necessary to change the data you have in your database. Let's say that Peggy (from our example) came in for a visit on her 7th birthday and we want to overwrite her old data with her new data. If you are using phpMyAdmin, you can do this by selecting your database on the left (in our case people) and then choosing "Browse" on the right. Next to Peggy's name you will see a pencil icon; this means EDIT. Select the pencil. You can now update her information as shown.

You can also do this through the query window or command line. You have to be very careful when updating records this way and double check your syntax, as it is very easy to inadvertently overwrite several records.

 UPDATE people SET age = 7, date = "2006-06-02 16:21:00", height = 1.22 WHERE name = "Peggy" 

What this does is update the table "people" by setting new values for age, date, and height. The important part of this command is WHERE, which ensures that the information is only updated for Peggy and not for every user in the database.

SQL Select Statement - Searching Data

Angela Bradley

Although in our test database we only have two entries and everything is easy to find, as a database grows, it is useful to be able to quickly search the information. From phpMyAdmin, you can do this by selecting your database and then selecting the search tab. Shown is an example of how to search for all users under 12 years old.

In our example database, this only returned one result—Peggy.

To do this same search from the query window or command line we would type in:

 SELECT * FROM people WHERE age < 12 

What this does is SELECT *(all columns) from the "people" table WHERE the "age" field is a number less than 12.

If we only wanted to see the names of people who are under 12 years old, we could run this instead:

 SELECT name FROM people WHERE age < 12 

This may be more helpful if your database contains a lot of fields that are irrelevant to what you are currently searching for.

SQL Delete Statement - Removing Data

Often, you need to remove old information from your database. You should be very careful when doing this because once it is gone, it's gone. That being said, when you are in phpMyAdmin, you can remove information a number of ways. First, select the database on the left. One way to remove entries is by then choosing the browse tab on the right. Next to each entry, you will see a red X. Selecting the X will remove the entry, or to delete multiple entries, you can check the boxes on the far left and then hit the red X at the bottom of the page.

Another thing you can do is select the search tab. Here you can perform a search. Let's say the doctor in our example database gets a new partner who is a pediatrician. He will no longer be seeing children, so anyone under 12 needs to be removed from the database. You can perform a search for an age less than 12 from this search screen. All the results are now displayed in the browse format where you can delete individual records with the red X, or check multiple records and select the red X at the bottom of the screen.

Removing data by searching from a query window or command line is very easy, but please be careful:

DELETE FROM people WHERE age < 12 

If the table is no longer needed you can remove the entire table by selecting the Drop tab in phpMyAdmin or running this line:

 DROP TABLE people 
Format
mla apa chicago
Your Citation
Bradley, Angela. "MySQL Tutorial: Managing MySQL data." ThoughtCo, Feb. 16, 2021, thoughtco.com/mysql-tutorial-managing-mysql-data-2693880. Bradley, Angela. (2021, February 16). MySQL Tutorial: Managing MySQL data. Retrieved from https://www.thoughtco.com/mysql-tutorial-managing-mysql-data-2693880 Bradley, Angela. "MySQL Tutorial: Managing MySQL data." ThoughtCo. https://www.thoughtco.com/mysql-tutorial-managing-mysql-data-2693880 (accessed March 29, 2024).