< (MySQL Tutorial – Part 1) Next | (MySQL Tutorial – Part 3) Next >
MySQL Tutorial : Basics
I am planning to divided this tutorial in three level of difficulty: basic, intermediate and advance.
This second part of my tutorial will explain how to perform the basic use of MySQL statements such as:
- Insert
- Select
- Distinct
- Where
- And & Or
- In
- Between
- Order By
- Update
- Delete
INSERT Statement
In the previous part of this tutorial, we saw how to import data into the table using a file with all the information separated with comas (csv file).
Now, we are interested to insert to our table the information directly using the INSERT INTO statement.
It is possible to write the INSERT INTO statement in two forms.
The simplest way to insert information to a table is by following this syntax:
INSERT INTO table_name VALUES (value1, value2, value3,…);
Example:
mysql> INSERT INTO tbl_products VALUES (0, 'Oscilloscope XX1', 500);
Query OK, 1 row affected (0.00 sec)
Our table now looks like this:
mysql> SELECT * FROM tbl_products;
+----+-------------------------------+-------+
| id | name | price |
+----+-------------------------------+-------+
| 1 | Chain Saw Husq | 600 |
| 2 | Chain Saw Tong | 130 |
| 3 | Multi-Tester Pepe Electronics | 240 |
| 4 | Oscilloscope XX1 | 500 |
+----+-------------------------------+-------+
4 rows in set (0.00 sec)
If you notice in this part of the statement “(0, ‘Oscilloscope XX1’, 500)”, the first value is given for the index. However, the index is auto-incremented for which the value ‘0’ is ignored but required.
However, this way require you to provide a value for each column, else you get this error:
mysql> INSERT INTO tbl_products VALUES ('Oscilloscope XX2', 550);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
This error happens because I forgot to add a value for the column id, even do it is auto-incremented.
The first form doesn’t specify the column names where the data will be inserted, only their values:
The second form specifies both the column names and the values to be inserted:
INSERT INTO table_name (column1, column2, column3,…) VALUES (value1, value2, value3,…)
mysql> INSERT INTO tbl_products (name, price) VALUES ('Oscilloscope XX2', 520);
Query OK, 1 row affected (0.00 sec)
So, now our table have:
mysql> SELECT * FROM tbl_products;
+----+-------------------------------+-------+
| id | name | price |
+----+-------------------------------+-------+
| 1 | Chain Saw Husq | 600 |
| 2 | Chain Saw Tong | 130 |
| 3 | Multi-Tester Pepe Electronics | 240 |
| 4 | Oscilloscope XX1 | 500 |
| 5 | Oscilloscope XX2 | 520 |
+----+-------------------------------+-------+
5 rows in set (0.00 sec)
Be able to specify both the column names and the values to be inserted helps a lot when we are dealing with tables using a huge number of columns in which not every column required a value to be inserted.
SELECT Statement
In order to obtain data from the table, we use the SELECT statement.
We indicate which tables (or all tables), we wish to display. The result is stored in the ‘result-set’ table.
Furthermore, we will see other statements and functions that work together with this SELECT statement. Right now, we are just covering the basics.
In previous examples, we say that we can see the whole table by using the following command line:
SELECT * FROM table_name;
mysql> SELECT * FROM tbl_products;
+----+-------------------------------+-------+
| id | name | price |
+----+-------------------------------+-------+
| 1 | Chain Saw Husq | 600 |
| 2 | Chain Saw Tong | 130 |
| 3 | Multi-Tester Pepe Electronics | 240 |
| 4 | Oscilloscope XX1 | 500 |
| 5 | Oscilloscope XX2 | 520 |
+----+-------------------------------+-------+
5 rows in set (0.00 sec)
Now, lets say we only wish to present a list in which only the products are displayed (with out the id and the price). Then, we can use the following format:
SELECT column_name(s) FROM table_name;
mysql> SELECT name FROM tbl_products;
+-------------------------------+
| name |
+-------------------------------+
| Chain Saw Husq |
| Chain Saw Tong |
| Multi-Tester Pepe Electronics |
| Oscilloscope XX1 |
| Oscilloscope XX2 |
+-------------------------------+
5 rows in set (0.00 sec)
Pretty sweet isn’t? However, we can do even more than this. For example, we can set the result to be displayed in descendent order (Z to A).
ORDER BY Keyword
The ORDER BY keyword allow us to specify a column for which the result-set will be sorted. By default the order of the records is ascending by default.
Notice the different:
This is the default order when we don’t specify in which order, we wish the result to be displayed:
mysql> SELECT name, price FROM tbl_products;
+-------------------------------+-------+
| name | price |
+-------------------------------+-------+
| Chain Saw Husq | 600 |
| Chain Saw Tong | 130 |
| Multi-Tester Pepe Electronics | 240 |
| Oscilloscope XX1 | 500 |
| Oscilloscope XX2 | 520 |
+-------------------------------+-------+
5 rows in set (0.00 sec)
This is the order set in ascending order using the column ‘name’ (used by default):
mysql> SELECT name, price FROM tbl_products ORDER BY name ASC;
+-------------------------------+-------+
| name | price |
+-------------------------------+-------+
| Chain Saw Husq | 600 |
| Chain Saw Tong | 130 |
| Multi-Tester Pepe Electronics | 240 |
| Oscilloscope XX1 | 500 |
| Oscilloscope XX2 | 520 |
+-------------------------------+-------+
5 rows in set (0.00 sec)
This is the order set in descending order using the column ‘name’:
mysql> SELECT name, price FROM tbl_products ORDER BY name DESC;
+-------------------------------+-------+
| name | price |
+-------------------------------+-------+
| Oscilloscope XX2 | 520 |
| Oscilloscope XX1 | 500 |
| Multi-Tester Pepe Electronics | 240 |
| Chain Saw Tong | 130 |
| Chain Saw Husq | 600 |
+-------------------------------+-------+
5 rows in set (0.00 sec)
This is the order set in ascending order using the column ‘price’:
mysql> SELECT name, price FROM tbl_products ORDER BY price ASC;
+-------------------------------+-------+
| name | price |
+-------------------------------+-------+
| Chain Saw Tong | 130 |
| Multi-Tester Pepe Electronics | 240 |
| Oscilloscope XX1 | 500 |
| Oscilloscope XX2 | 520 |
| Chain Saw Husq | 600 |
+-------------------------------+-------+
5 rows in set (0.00 sec)
This is the order set in descending order using the column ‘price’:
mysql> SELECT name, price FROM tbl_products ORDER BY price DESC;
+-------------------------------+-------+
| name | price |
+-------------------------------+-------+
| Chain Saw Husq | 600 |
| Oscilloscope XX2 | 520 |
| Oscilloscope XX1 | 500 |
| Multi-Tester Pepe Electronics | 240 |
| Chain Saw Tong | 130 |
+-------------------------------+-------+
5 rows in set (0.00 sec)
In other words, the ORDER BY keyword affects the columns of the table.
The DISTINCT Statement
In a table, some of the columns may contain duplicate values; however, we can list only the records with different (distinct) values:
SELECT DISTINCT column_name(s) FROM table_name
First, lets create a table which we can work with. Lets say a users table named ‘tbl_users’;
mysql> CREATE TABLE db_examples.tbl_users (
-> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> first_name VARCHAR(25) NOT NULL,
-> middle_name VARCHAR(25),
-> last_name VARCHAR(25) NOT NULL,
-> PRIMARY KEY(id)
-> );
Query OK, 0 rows affected (0.10 sec)
Notice that I am indicating first the database and then the name of the table to create. This prevent any mistakes when manipulating databases. In this case, I make sure that the table is not created in another database by mistake.
Second, lets insert three users:
mysql> INSERT INTO db_examples.tbl_users (first_name, last_name) VALUES ('Martin', 'Pachecho');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO db_examples.tbl_users (first_name, last_name) VALUES ('Matias', 'Delgado');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO db_examples.tbl_users (first_name, last_name) VALUES ('Jose', 'Pachecho');
Query OK, 1 row affected (0.00 sec)
Our new table:
mysql> SELECT * FROM db_examples.tbl_users;
+----+------------+-------------+-----------+
| id | first_name | middle_name | last_name |
+----+------------+-------------+-----------+
| 1 | Martin | NULL | Pachecho |
| 2 | Matias | NULL | Delgado |
| 3 | Jose | NULL | Pachecho |
+----+------------+-------------+-----------+
3 rows in set (0.00 sec)
Lets assume, we wish to show the different last name are there in our table:
mysql> SELECT last_name FROM db_examples.tbl_users;
+-----------+
| last_name |
+-----------+
| Pachecho |
| Delgado |
| Pachecho |
+-----------+
3 rows in set (0.00 sec)
You may notice that we have a problem here, because there are two ‘Pachecho’ and we really want to show all the different last name.
We cannot have these repetition. Therefore, we must use the DISTINCT keyword as show:
mysql> SELECT DISTINCT last_name FROM db_examples.tbl_users;
+-----------+
| last_name |
+-----------+
| Pachecho |
| Delgado |
+-----------+
2 rows in set (0.01 sec)
Now, we have a all the different last names in our table. You can notice that last name ‘Pachecho’ only shows once.
Something important to clarify is that the combination SELECT DISTINCT only work on single columns. If you try to use it on more than one column it doesn’t work:
Incorrect:
mysql> SELECT DISTINCT first_name, last_name FROM db_examples.tbl_users WHERE last_name='Pachecho';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Martin | Pachecho |
| Jose | Pachecho |
+------------+-----------+
2 rows in set (0.00 sec)
Correct:
mysql> SELECT DISTINCT last_name FROM db_examples.tbl_users WHERE last_name='Pachecho';
+-----------+
| last_name |
+-----------+
| Pachecho |
+-----------+
1 row in set (0.00 sec)
Another alternative for DISTINCT without the limitation of applying only to one field, is to use the function MAX
which will be explained in the next tutorial):
Here is a preview:
mysql> SELECT first_name, MAX(last_name) FROM db_examples.tbl_users WHERE first_name='Jose' OR last_name='Pachecho';
+------------+----------------+
| first_name | MAX(last_name) |
+------------+----------------+
| Martin | Pachecho |
+------------+----------------+
1 row in set (0.00 sec)
The WHERE Clause
Lets say that we wish to list all the users which last name are ‘Pachecho’. This is the WHERE clause comes hancriterioncriterioncriteriondy. You can extract only those records that fulfill a specified requirement.
mysql> SELECT first_name, last_name FROM db_examples.tbl_users WHERE last_name='Pachecho';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Martin | Pachecho |
| Jose | Pachecho |
+------------+-----------+
2 rows in set (0.00 sec)
The WHERE Clause & AND Operator
Now, I wish to show those records in which the first name is ‘Jose’ and the last name ‘Pachecho’. Notice that only those records that fulfil both conditions will be displayed:
mysql> SELECT first_name, last_name FROM db_examples.tbl_users WHERE first_name='Jose' AND last_name='Pachecho';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Jose | Pachecho |
+------------+-----------+
1 row in set (0.00 sec)
The WHERE Clause & OR Operator
Now, I wish to show those records in which the first name is ‘Jose’ or the last name ‘Pachecho’. Notice that only those records that fulfil either conditions will be displayed:
mysql> SELECT first_name, last_name FROM db_examples.tbl_users WHERE first_name='Jose' OR last_name='Pachecho';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Martin | Pachecho |
| Jose | Pachecho |
+------------+-----------+
2 rows in set (0.00 sec)
The WHERE Clause & In Operator
Lets assume you wish to show those users with the first name ‘Martin’ or ‘Jose’. For something like this, we could use the OR operator; however, if there would be more names, it could become tedious.
For this reason, you can use the IN operator. The IN operator allows you to specify multiple values inside a column in a WHERE clause.
mysql> SELECT * from tbl_users;
+----+------------+-------------+-----------+
| id | first_name | middle_name | last_name |
+----+------------+-------------+-----------+
| 1 | Martin | Agustin | Pachecho |
| 2 | Matias | NULL | Delgado |
| 3 | Jose | George | Pachecho |
+----+------------+-------------+-----------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM tbl_users WHERE first_name IN ('Martin', 'Jose');
+----+------------+-------------+-----------+
| id | first_name | middle_name | last_name |
+----+------------+-------------+-----------+
| 1 | Martin | Agustin | Pachecho |
| 3 | Jose | George | Pachecho |
+----+------------+-------------+-----------+
2 rows in set (0.00 sec)
The WHERE Clause & Between Operator
The BETWEEN operator allows you to selects a range of data between two values. These values can be numbers, text, and even dates.
mysql> SELECT * FROM tbl_products;
+----+-------------------------------+-------+
| id | name | price |
+----+-------------------------------+-------+
| 1 | Chain Saw Husq | 600 |
| 2 | Chain Saw tong | 130 |
| 3 | Multi-Tester Pepe Electronics | 240 |
| 4 | Oscilloscope XX1 | 500 |
| 5 | Oscilloscope XX2 | 520 |
+----+-------------------------------+-------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM tbl_products WHERE price BETWEEN 200 AND 595;
+----+-------------------------------+-------+
| id | name | price |
+----+-------------------------------+-------+
| 3 | Multi-Tester Pepe Electronics | 240 |
| 4 | Oscilloscope XX1 | 500 |
| 5 | Oscilloscope XX2 | 520 |
+----+-------------------------------+-------+
3 rows in set (0.00 sec)
The UPDATE Statement
Lets refresh what we have so far. This is our table:
mysql> SELECT * FROM tbl_users;
+----+------------+-------------+-----------+
| id | first_name | middle_name | last_name |
+----+------------+-------------+-----------+
| 1 | Martin | NULL | Pachecho |
| 2 | Matias | NULL | Delgado |
| 3 | Jose | NULL | Pachecho |
+----+------------+-------------+-----------+
3 rows in set (0.00 sec)
As you may notice, we are missing the middle name of all of them.
If we wish change Martin Pachecho to Martin Agustin Pachecho for example, we would need to update the record using the UPDATE statement plus the WHERE clause:
mysql> UPDATE db_examples.tbl_users SET middle_name='Agustin' WHERE first_name='Martin' AND last_name='Pachecho';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Leaving us with:
mysql> SELECT * FROM tbl_users;
+----+------------+-------------+-----------+
| id | first_name | middle_name | last_name |
+----+------------+-------------+-----------+
| 1 | Martin | Agustin | Pachecho |
| 2 | Matias | NULL | Delgado |
| 3 | Jose | NULL | Pachecho |
+----+------------+-------------+-----------+
3 rows in set (0.00 sec)
Notice the importance of using the WHERE clause, if you do not include it you could change all the records by mistake:
mysql> UPDATE db_examples.tbl_users SET middle_name='Ezequiel';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> SELECT * FROM tbl_users;
+----+------------+-------------+-----------+
| id | first_name | middle_name | last_name |
+----+------------+-------------+-----------+
| 1 | Martin | Ezequiel | Pachecho |
| 2 | Matias | Ezequiel | Delgado |
| 3 | Jose | Ezequiel | Pachecho |
+----+------------+-------------+-----------+
3 rows in set (0.00 sec)
Lets fix this mess by giving each record a different middle name (leaving Matias without middle name since we are at it):
mysql> UPDATE db_examples.tbl_users SET middle_name='Agustin' WHERE id='1';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE db_examples.tbl_users SET middle_name=NULL WHERE id='2';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE db_examples.tbl_users SET middle_name='George' WHERE id='3';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM tbl_users;
+----+------------+-------------+-----------+
| id | first_name | middle_name | last_name |
+----+------------+-------------+-----------+
| 1 | Martin | Agustin | Pachecho |
| 2 | Matias | NULL | Delgado |
| 3 | Jose | George | Pachecho |
+----+------------+-------------+-----------+
3 rows in set (0.00 sec)
The DELETE Statement
The DELETE statement is used to delete rows in a table.
For this statement, lets create a new table named ‘accounts’ where we will have id, username, password, is_enable:
mysql> CREATE TABLE IF NOT EXISTS `tbl_accounts` (
-> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
-> `username` varchar(25) NOT NULL,
-> `password` varchar(128) NOT NULL,
-> `enabled` tinyint(1) NOT NULL DEFAULT '0',
-> PRIMARY KEY (`id`)
-> );
Query OK, 0 rows affected (0.20 sec)
Notice the NOT NULL DEFAULT ‘0’. This means that we don’t have to worry to indicate a default value when inserting a new record at least that we wish a different value for enabled
Lets add three new accounts to play with:
mysql> INSERT INTO db_examples.tbl_accounts (username, password) VALUES ('root', SHA1('rootPassword'));
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO db_examples.tbl_accounts (username, password) VALUES ('userA', SHA1('userAPassword'));
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO db_examples.tbl_accounts (username, password) VALUES ('userB', SHA1('userBPassword'));
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM db_examples.tbl_accounts;
+----+----------+------------------------------------------+---------+
| id | username | password | enabled |
+----+----------+------------------------------------------+---------+
| 1 | root | 1978e9cbdf719d1817aeb7df32a4602b30af21c7 | 0 |
| 2 | userA | 16b0e6f76e73e140348f989e24aacbb96df5a4bc | 0 |
| 3 | userB | 3968c1d3c537fb5fd069b9475070956611a445ac | 0 |
+----+----------+------------------------------------------+---------+
3 rows in set (0.00 sec)
Notice that we are encrypting the password using SHA1 encryption.
Lets delete the userB because we don’t want it in the system:
mysql> DELETE FROM db_examples.tbl_accounts WHERE id=3;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM db_examples.tbl_accounts;
+----+----------+------------------------------------------+---------+
| id | username | password | enabled |
+----+----------+------------------------------------------+---------+
| 1 | root | 1978e9cbdf719d1817aeb7df32a4602b30af21c7 | 0 |
| 2 | userA | 16b0e6f76e73e140348f989e24aacbb96df5a4bc | 0 |
+----+----------+------------------------------------------+---------+
2 rows in set (0.00 sec)
Now, we are left with only two accounts.
It is important to use the WHERE clause when using the DELETE statement since you run a risk to delete all the records:
mysql> SELECT * FROM db_examples.tbl_accounts;
+----+----------+------------------------------------------+---------+
| id | username | password | enabled |
+----+----------+------------------------------------------+---------+
| 1 | root | 1978e9cbdf719d1817aeb7df32a4602b30af21c7 | 0 |
| 2 | userA | 16b0e6f76e73e140348f989e24aacbb96df5a4bc | 0 |
+----+----------+------------------------------------------+---------+
2 rows in set (0.00 sec)
mysql> DELETE FROM db_examples.tbl_accounts;
Query OK, 2 rows affected (0.00 sec)
mysql> SELECT * FROM db_examples.tbl_accounts;
Empty set (0.00 sec)
Ooops, just recently we empty the whole table, just because we forgot to use the WHERE clause.
What is Next?
In the next tutorial (intermediate level), we are going to see how to use all the different functions that MySQL provide us.
< (MySQL Tutorial – Part 1) Next | (MySQL Tutorial – Part 3) Next >
