Select or List items in your MySQL database
This can be done with the “SELECT” statement.
The most basic query is using “SELECT” to select all items which are stored in a specific table of your database:
SELECT * FROM table;
You just have to change “table” by the name of the table. It is, for instance, useful when you don’t know exactly what you are looking for and shows the different fields which constitute the table. It will return all the entries stored in the table.
SELECT specific_item FROM table;
Change “specific_item” by the appropriate column in the table. For example in this database, if we want to get all the emails from the table “users”:
The command would be:
SELECT email FROM users;
You can also use “LIKE” to select the emails which contain a specific string:
SELECT email FROM users WHERE email LIKE '%apple%';
All the emails which contain “apple” will be listed.
Change/Modify an item in your MySQL database
This is done with the “UPDATE” statement. You have to know the name of the table, the name of the columns, and, eventually, another parameter to indicate the row on which this change has to be applied.
The general query to change a value in your database is:
UPDATE table SET column;
If you add another parameter to specify a specific row:
UPDATE table SET column WHERE specific_parameter = "value";
In the following example we are going to change the email of the user called “ignacio”:
UPDATE users SET email = "email@example.com" WHERE username = "ignacio";
If we haven’t specified the username “ignacio” the query would have been:
UPDATE users SET email = "firstname.lastname@example.org";
This query would have set the value “email@example.com” for all the users as we haven’t specified a specific user, and so all the emails of all our users would have been replaced.
Delete with MySQL
DELETE FROM table;
Like in the previous example you can add an additional condition to specify what you want to delete exactly:
DELETE FROM table WHERE condition;
As a concrete example, here is how to delete all the users who are registered since 2016:
DELETE FROM users WHERE creation >= '2016-01-01';
You can ask yourself why to learn these queries while you can use the interface of phpMyAdmin to make everything you want. Well, knowing these queries may help you when you connect to your MySQL database using SSH as you will see in another course!