So far we’ve been hitting what is arguably the more mundane aspects of dealing with Database Administration. This would be the DDL or data definition language portion of working with MySQL. It is by using DDL that we are able to build and modify your databases and tables. Of course this is needed stuff, we need a database and some tables before we can start inserting, reading, updating, and deleting some information. Once we do have our database and tables in place however, we are ready to start applying some DML or data manipulation language commands to work with the actual data. Let’s get cooking!
Inserting Data using INSERT
Until now, we’ve not made note of the fact that commands in MySQL are usually all uppercase. I can’t stand using the shift key, or the caps lock key, so guess what? That’s right, when I work with MySQL it is in ALL LOWERCASE BABY :-). Ok, small tangent aside, we’re going to start by looking at the insert command, as it is with this command that we can begin putting information into our database. The basic syntax usually follows this format:
insert into table (column1, column2, column3…) values (value1, value2, value3…)
You can also use the insert command following this format:
insert into table set column1=’value1′, column2=’value2′, column3=’value3
Let’s revisit the friends table we had already created earlier.
mysql> show create table friendsG
*************************** 1. row ***************************
Table: friends
Create Table: CREATE TABLE `friends` (
`first_name` varchar(40) NOT NULL DEFAULT '',
`last_name` varchar(40) NOT NULL DEFAULT '',
`cell_phone` varchar(20) NOT NULL DEFAULT '',
`birthday` date NOT NULL DEFAULT '0000-00-00',
PRIMARY KEY (`first_name`,`last_name`,`birthday`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql>
Now we can use the insert command to put some friends into our friends table.
mysql> insert into friends (cell_phone, first_name, birthday, last_name) values
-> ('423-1423', 'Jim', '1985-07-24', 'Jones');
Query OK, 1 row affected (0.02 sec)
mysql>
Ok, do you notice anything a little strange here? That’s right, we are not required to provide the columns in the same order with which they were created. MySQL is very flexible in this sense and as long as you have a one to one mapping between the columns and values in your insert statement, it will just work. Lets add another imaginary friend using the alternative syntax.
mysql> insert into friends set first_name='Emmet', last_name='Brickowski', birthday='2013-07-15',
-> cell_phone='987-1244';
Query OK, 1 row affected (0.02 sec)
mysql> select * from friends;
+------------+------------+------------+------------+
| first_name | last_name | cell_phone | birthday |
+------------+------------+------------+------------+
| Emmet | Brickowski | 987-1244 | 2013-07-15 |
| Jim | Jones | 423-1423 | 1985-07-24 |
+------------+------------+------------+------------+
2 rows in set (0.00 sec)
mysql>
mysql>
Fantastic! So happy to see that both Jim Jones and Emmet Brickowski are our friends. Lest you are unaware, Emmet is the special, which prophecy has foretold will be the one to stop President Business and his use of The Kragle on humanity. There are a couple of things to remember when using insert. When inserting dates into the database, make sure they are quoted, otherwise the MySQL engine will perform arithmetic on the value you give if in the YYYY-MM-DD format. The same goes for entering phone numbers. Also notice that the birthdate field has a default value in the table definition. This means that when we insert a record, we can actually leave off that field if we like.
Batch Inserts with MySQL
We only have two friends, what a bummer. Let’s add some more to our circle, and we’ll do it using batch inserts which are great fun.
mysql> insert into friends (first_name, last_name, cell_phone)
-> values
-> ('Rick', 'James', '564-6398'), ('Dave', 'Chappelle', '687-3471');
Query OK, 2 rows affected (0.07 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>
Notice that we can group multiple collections of values to enter into our fields by enclosing them within parenthesis. Each group will be separated by a comma as well. So in the example above, we were able to add both Rick and Dave as our new friends with one insert statement. Pretty cool.
Inserts using only values
Another neat feature of the insert command in MySQL is that you don’t even have to specify the fields you want to insert data into if you don’t want to. Just provide the values, and make sure that the mapping is correct. So for example, our table has the fields in the order of first_name, last_name, cell_phone, and birthday. If we want to insert only using values, we better be sure we provide the values in that order, because if we don’t we will get an error, or worse if the data type matches what can be stored in that field, it will simply insert that incorrect data and give you no indication that there was a problem. Let’s test this out now.
mysql> insert into friends values ('Sofia','Vergara','487-4612','1972-07-10');
Query OK, 1 row affected (0.00 sec)
mysql> select * from friends;
+------------+------------+------------+------------+
| first_name | last_name | cell_phone | birthday |
+------------+------------+------------+------------+
| Dave | Chappelle | 687-3471 | 0000-00-00 |
| Emmet | Brickowski | 987-1244 | 2013-07-15 |
| Jim | Jones | 423-1423 | 1985-07-24 |
| Rick | James | 564-6398 | 0000-00-00 |
| Sofia | Vergara | 487-4612 | 1972-07-10 |
+------------+------------+------------+------------+
5 rows in set (0.00 sec)
mysql>
Fantastic! Look at our collection of friends now. I really do like that Sofia.
Using the UPDATE command in MySQL
So we have a database full of friends, yet there are a few things to consider. Dave and Rick never provided their birthday, so we’ll need to fix that. Well, their data is already in the database, so what do we do? We update their information using the update command. Here is how to do it for Rick, we’ll do it for Dave as well.
mysql> update friends set birthday='1948-02-01' where first_name='Rick'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0
As we can see the typical update command follows the format of:
update table set column1=value1, column2=value2 where
The update command is very powerful. You have the possibility of changing hundreds or even thousands of rows that maybe you didn’t intend to. As an example if you did something like the following query,
mysql> update friends cell_phone='555-5555';
it would update the cell_phone field, but it would update ALL of the cell_phone fields. Now every single one of your friends has the same cell phone number, and we know this is not the case. This is a silly example, however updating something of more importance is no laughing matter. In addition, there is no undo command, so once you make your mistake, you are stuck with it. If you are unsure, you can do a select statement first, to see how many rows the query will match. select and update use the same clauses so we can do this. If we turn the prior statement into a select, it would look like this:
mysql> select cell_phone from friends; +------------+ | cell_phone | +------------+ | 687-3471 | | 987-1244 | | 423-1423 | | 564-6398 | | 487-4612 | +------------+ 5 rows in set (0.00 sec) mysql>
So by using this technique, we can see that 5 rows are going to be updated had we used an update instead of a select. Maybe we only wanted to update Dave’s cell phone number. In that case, we can first do a select to determine how we will then write the update. Let’s check this out.
mysql> select cell_phone from friends where first_name='Dave'; +------------+ | cell_phone | +------------+ | 687-3471 | +------------+ 1 row in set (0.06 sec) mysql> update friends set cell_phone='977-5555' where first_name='Dave'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select cell_phone from friends where first_name='Dave'; +------------+ | cell_phone | +------------+ | 977-5555 | +------------+ 1 row in set (0.00 sec) mysql>
In the example above, we now can see that only the one row was update which is what we wanted. By first writing the select statement, it helped us figure out how to put together our update statement to get the result we want. Perfect.
Using DELETE in MySQL
Ahh yes, the good old fashioned delete command. Just in case you thought you couldn’t do enough damage with the update command, we give you the equally powerful and dangerous delete command. Yes, you should practice using this command in a sandbox type environment before you take it to your production setup. Most times, you’re going to want to make sure that a where clause is included in your delete statement. This way you can narrow down the operation to only a limited amount of data. In the generic sense, it would look like this format:
delete from table where expression
With the delete command, you do not specify any columns or fields. That is because with delete, you are deleting whole rows at a time. If your goal was to simply clear out one value within a row, you could use an update statement instead. Let’s remove a record from the database now to see how it works.
mysql> delete from friends where last_name='James'; Query OK, 1 row affected (0.04 sec) mysql> select * from friends; +------------+------------+------------+------------+ | first_name | last_name | cell_phone | birthday | +------------+------------+------------+------------+ | Dave | Chappelle | 977-5555 | 1973-08-24 | | Emmet | Brickowski | 987-1244 | 2013-07-15 | | Jim | Jones | 423-1423 | 1985-07-24 | | Sofia | Vergara | 487-4612 | 1972-07-10 | +------------+------------+------------+------------+ 4 rows in set (0.00 sec) mysql>
Much like we can convert an update statement into a select statement so that we can safely see what will be affected in the database, we can do the same with the delete statement so that we don’t go deleting all kinds of rows we did not intend to. Since the delete statement affects all fields, we can simply translate that to an asterisk when constructing the corresponding select statement. For example:
mysql> select * from friends where last_name='Jones'; +------------+-----------+------------+------------+ | first_name | last_name | cell_phone | birthday | +------------+-----------+------------+------------+ | Jim | Jones | 423-1423 | 1985-07-24 | +------------+-----------+------------+------------+ 1 row in set (0.00 sec)
Using a where clause is a good idea in this case. One other thing to know is that if you try to delete a row that is already gone from the database, that is perfectly fine. MySQL will simply give you a query ok, with 0 rows affected.
What if you want to delete all rows from a table, how can we do that? Well, it turns out there are a couple of ways to do this. One of them doesn’t even use the delete command, however it is a great way to go about completing this task. If we wanted to get rid of all our friends, we could do either of the following:
delete from friends;
or
truncate table friends;
It turns out the truncate method is the preferred method. When would you do this? Consider if you had a table in your database that continually tracks some type of history, which builds up over time, yet has largely inconsequential data. Every so often, it may make sense to clear out that table to reduce load on the server. That is an ideal application for the truncate command.
SELECT in MySQL
We saved the best for last, the select command. Retrieving data from a database is arguably the most important and powerful aspect of working with databases. We’ve been using select all along during our examples. If you’ve been using MySQL at all, the select command really just becomes second nature to you since you use it so much. The really basic format of the select command is just like this:
select from table where expression
There are many more options you can find in the official docs, so be sure to check those out once your MySQL skills advance. You’ll find yourself visiting those docs frequently, in addition to copious amounts of Google Searches, and frequent visits to Stack Overflow.
The best way to get up to speed with select is to simply start hammering out some commands at your console. We can select individual columns, a combination of columns, or all columns. Here are some example commands and their output:
mysql> select first_name from friends; +------------+ | first_name | +------------+ | Dave | | Emmet | | Jim | | Sofia | +------------+ 4 rows in set (0.00 sec) mysql> select last_name from friends; +------------+ | last_name | +------------+ | Chappelle | | Brickowski | | Jones | | Vergara | +------------+ 4 rows in set (0.00 sec) mysql> select cell_phone from friends; +------------+ | cell_phone | +------------+ | 977-5555 | | 987-1244 | | 423-1423 | | 487-4612 | +------------+ 4 rows in set (0.00 sec) mysql> select cell_phone, last_name from friends; +------------+------------+ | cell_phone | last_name | +------------+------------+ | 977-5555 | Chappelle | | 987-1244 | Brickowski | | 423-1423 | Jones | | 487-4612 | Vergara | +------------+------------+ 4 rows in set (0.00 sec) mysql> select * from friends; +------------+------------+------------+------------+ | first_name | last_name | cell_phone | birthday | +------------+------------+------------+------------+ | Dave | Chappelle | 977-5555 | 1973-08-24 | | Emmet | Brickowski | 987-1244 | 2013-07-15 | | Jim | Jones | 423-1423 | 1985-07-24 | | Sofia | Vergara | 487-4612 | 1972-07-10 | +------------+------------+------------+------------+ 4 rows in set (0.00 sec)
As you get better and better at the overall syntax, you can start adding in more clauses and conditions. There might be a need for distinct data, table joins, group by commands, order by statements, limits, sort orders, and so on. Their implementation becomes clear once you find a use case for them.
The DML Conclusion
Data Manipulation Language really is the bread and butter of your data driven applications. It is by using DML that we can collect and control all kinds of data using our MySQL database. At the end of the day, it really boils down to only four commands, insert, select, update, and delete. These commands of course are used in combination with all kinds of clauses to refine how they operate on the data. Even though there are many graphical user interface tools available to work with MySQL, practicing these queries by hand at the console really is the best way to make this stuff stick. This makes using those graphical tools much more intuitive. It certainly is easier to go from using the console to a graphical tool than it is to just barely get by using the graphical application, then have to make things work at the standard MySQL console.
Nest.js Tutorial A Progressive Node.js Framework
Twitter Bootstrap Navigation Elements
The Database First Approach of Yii
Superfood Diet For Hackers
Setting Up Vagrant On Ubuntu
How To Compare Two Arrays of Data and Calculate Position Differences
Dynamic DNS: An Overview
What Are the Machine Learning Algorithms