In this post, we’ll be discussing the process of inserting data into MySQL tables using the Feynman technique and the popular 80’s cartoon “The Smurfs”.
This is part of the Feynman Technique/20 hr Method challenge. For more information, click here.
In a previous post, we used Game of Thrones to discuss the basics of creating a table in MySQL. This time, we will be creating and adding data, but this time, we’ll be using The Smurfs.
The Setup:
When creating tables in MySql, you have to first create or choose a database (home) where the table will live and then create the table. So for the sake of simplicity, we are going to say that Brainy Smurf wants to create a list of Smurfs he usually works with. Brainy, because he feels he has to live up to his name, decides to use MySql to create a database. This is what the first steps would look like:
CREATE DATABASE smurfTeam;
USE smurfTeam;The code is self-explanatory – create the database called smurfTeam, and now use smurfTeam. The semicolon is used to execute the code.
Now that the database where the table will live is created, Brainy can now create the table. It would start like this:
CREATE TABLE smurfs(name VARCHAR(20), age INT);This will create our table called “smurfs” with two columns. The first will be called “name” and will allow various characters (varchar) of no more than 20, and the next column will be called “age” and only whole numbers (int = integer) will be allowed in.
But How About Inserting Data?
Brainy decides to add his icon, Papa Smurf to his table. Even though Papa always escapes before Brainy can get a moment with him, he still idolizes him.
INSERT INTO smurfs(name, age)
VALUES('Papa Smurf', 546);The code breaks down into the following:
- Insert into the smurfs table, in the name and then age columns the following…
- The values are named ‘Papa Smurf”, age 546.
Values Must Match The Call Sequence
Whatever order the “insert info” code mentions the entry order of information, the following “values” line must follow. You can switch the orders around of what information goes first, middle, last, etc, however, the values must follow the same order as the insert info. To show you what I mean, Brainy will add his name to the list next but will swap the order.
INSERT INTO smurfs(age, name)
VALUES(150, 'Brainy Smurf');Instead of putting in the name first, we used the age first, and then the name, and followed suit with the values. To check to see if everything went well, Brainy would type in the following:
SELECT * FROM smurfs…and this is what the system would come back with:
+--------------+------+
| name | age |
+--------------+------+
| Papa Smurf | 546 |
| Brainy Smurf | 150 |
+--------------+------+
2 rows in set (0.01 sec)Even though the names and the items were entered in reverse, it still populated in the same way as the original “Create Table” order we established.
For the sake of ease, Brainy decided to finish inserting the data into his MySQL table and so he adds the following Smurfs to his list:
INSERT INTO smurfs(name, age)
VALUES('Clumsy Smurf', 100),
('Jokey Smurf', 100),
('Grouchy Smurf', 100);If Brainy wanted to, he could have kept going. By adding commas to the end of each entry, as seen in each of the ends of these lines, if he added the semicolon, it would close out this group of entries. If he wanted to, just like he did when he added each group, he could just type up the “insert into…” code and following it with the “values(…)” information, but as of right now, his final table looks like this:
+---------------+------+
| name | age |
+---------------+------+
| Papa Smurf | 546 |
| Brainy Smurf | 150 |
| Clumsy Smurf | 100 |
| Jokey Smurf | 100 |
| Grouchy Smurf | 100 |
+---------------+------+
5 rows in set (0.00 sec)…and he’s perfectly happy with that.
Don’t Drop It
If Clumsy decided he wanted to help Brainy out with the table, but Jokey decides to prank Brainy, Jokey could have Clumsy enter the following:
DROP TABLE smurfs;…but knowing Clumsy he accidentally screws things up further by deleting the entire database, with the table inside.
DROP DATABASE smurfTeam;Sorry Brainy.