INSERTing and SELECTing Data
How do we add data to a table? With a query! Whenever you want to add a new
row to a table, use the INSERT INTO query:
INSERT INTO pet (name, breed) VALUES ("Chew Barka", "Bichon");
Heck, let’s add another one:
INSERT INTO pet (name, breed) VALUES ("Spark Pug", "Pug");
INSERT INTO means “add a new row to this table”. The syntax is a little
odd, but always the same: INSERT INTO, the table name, a comma list of the
fields you want to fill in, the word VALUES, then a comma list of their values.
SELECTing Data
Now that the data is in there, we just need to figure out how to read it.
Reading data always starts with the same word, which you’ll see more than
anything else: SELECT. Try selecting all of the data from the pet
table:
There they are! And even in a nice table. In this simple form, SELECT shows
every row and every field in a table.
Primary Keys: The very Special id Field
Check out that id column. Our INSERT query sent values for name and
breed, but not id. That’s allowed, and you can even setup a column
to have a default value, just for that situation.
But every table has one special column called the primary key. This column
is usually an integer that auto-increments. If we don’t send a value for
it, MySQL just picks 1, 2, 3, 4 and so on. That’s really handy, because the
primary key of each row needs to be unique in the table.
Let’s add another pet, but leave both the id and breed columns blank:
INSERT INTO pet (name) VALUES ("Pico de Gato");
Use SELECT to see all 3 rows:
The id column just keeps on auto-incrementing, but this breed is empty.
What are you Pico do Gato?
SELECT only some Columns
A SELECT query has a few other tricks too, like being able to return only
specific columns instead of everything. Just change the * to a list of
what you want:
SELECT id, name FROM pet;
SELECT only some Rows with WHERE
One of the most common things to do is filter by some condition. Suppose
we only want to return rows where the id column is greater than 1.
We do that by adding a WHERE clause to the end of the query:
SELECT id, name FROM pet WHERE id > 1;
You can add filters like this on any column and even use some pretty complex
logic. We’ll see more examples later, but since MySQL is so popular, you’ll
find plenty of stuff online to help.
