The previous exploration introduced the basics of the SELECT command. That is where you will spend most of your time, but manipulating data is still an important part of database use and this section will cover the basics of doing just that.
This one is the low hanging fruit of the bunch. It is like SELECT except you use DELETE and don’t need to specify columns.
DELETE FROM bsg_people WHERE lname = 'Adama';This will delete everyone who’s last name is ‘Adama’. There is really not much more to it. We will talk about aggregate functions later but there is an important one to mention here and that is COUNT(*). This will return the number of rows returned from a query. If you are unsure if you are about to delete the number of rows you want to you can run something like:
SELECT COUNT(*) FROM my_table WHERE condition_to_check;If you run this you can see how many rows your condition targets. If you are only expecting to affect 10 or 15 rows and this returns 10,000 you know something might be off about your query, maybe you used an OR where you meant to use an AND. Running a SELECT using the condition before you run the DELETE is often a good idea.
INSERT has a bit more to it than delete does. You need to specify the table and columns to insert into along with the values to insert. The general syntax looks like this
INSERT INTO tbl_name (col_name1, col_name2, ... col_nameN) VALUES (val1, val2, ... valN);If you want to insert multiple rows you separate rows grouped in () with , like so:
INSERT INTO tbl_name (col_name1, col_name2, ... col_nameN) VALUES (val1_1, val1_2, ... val1_N), (val2_1, val2_2, ... val2_N);You can insert as many rows as you want in a single query with this syntax an actual example looks like this:
INSERT INTO bsg_people (fname, lname) VALUES ('Sherman', 'Cottle')A couple special things happen here when the following row is created
| id | fname | lname | homeworld | age |
|---|---|---|---|---|
| 235 | Sherman | Cottle | NULL | NULL |
The first is that an id is automatically assigned. We will talk about this a bit later, but this is a really common practice. The other is that fields that were not specified are left NULL. Some fields are created as NOT NULL which means they must have a value. If that were the case for homeworld or age then this insert query would have been rejected.
Finally we will talk about UPDATE which is the most complex of the data manipulation queries. The general syntax looks like this:
UPDATE table_name SET col_name1 = val1, col_name2 = val2, ... col_nameN = valN WHERE condition;Here we need to specify the name of the table, the columns to update along with the values to update them with and finally a condition that will specify which columns to updated. If you only want to update one row you should select it by using it primary key, this way you can be sure to not accidentally update additional rows.
Much like the DELETE query it can be a good idea to test your condition using a SELECT statement first to make sure that your query is going to update the rows you expect it to.
As a concrete example, lets set Sherman Cottle’s age to 66.
UPDATE bsg_people SET age = 66 WHERE id = 235;Note that I used the id of the row I just created. I could have also used a condition like WHERE lname = 'Cottle' but then I would run the risk of updating other rows if there were other people with the last name of Cottle.
If you can insert more than one row at a time and use subqueries in an insertion you are pretty much good to go here. Make sure you can do that on the bsg_people table and then move on to more interesting endeavors.