Contents
Basic SQL commands
We will study the basic SQL commands to query and manipulate data in tables. From now on, we will work exclusively with the test database. At first we have to open the CMD window (or the terminal in Linux/MacOs) and type:
mysql -u testuser -p
Just enter password and we are good to go. Then in the MySQL command prompt, just type:
mysql> use test;
We have known all basic SQL commands for CRUD. Next, we want to query data from both the tables Customers and Sales, and we have the result with SalesId, customer’s Full Name, Qty, UnitPrice. In SQL, we call that joining.
inner join
The first one we cover here is inner join. The inner join will selects only rows that have matching values in both table:
mysql> select
SaleId as 'id',
concat(Customers.FirstName, ' ',
Customers.LastName) as 'Full Name',
Qty, Price
from Sales
inner join Customers
on Customers.CustomerId = CusId;
Explanation:
– we use select from Sales table as usual, but inner join with Customers table. To refer
to the joining table Customers’s column FirstName, we can use Customers.FirstName
In fact we can leave Customers. out, this is only needed when the two tables have columns with the same names, in that case we have to tell SQL which columns we want to use.
– on keyword has the meaning like the where keywords, with inner join we can use where here. But it is the norm that in joining tables, we use on keyword.
Run the query and we have:
+----+----------------+------+-------+ | id | Full Name | Qty | Price | +----+----------------+------+-------+ | 1 | Tom Rex | 5 | 5.50 | | 2 | Ken Star | 10 | 5.20 | | 3 | Luke Skywalker | 3 | 5.90 | | 4 | Tom Rex | 15 | 5.00 | | 5 | Luke Skywalker | 9 | 5.40 | +----+----------------+------+-------+
left join
In the example about inner join, all the CusId of the Sales table can be found in the Customers table. Now, if there’s a row in the Sales table that has the CusId which is non-existence in the Customers table, the inner join will leave that row out. Say, we add a row to the Sales table with a CusId of 4, which does not exist in the Customers table:
mysql> insert into Sales
(CusId, Qty, Price)
values
(4, 6, 5.5);
Now run the select * from Sales; and we have:
+--------+-------+------+-------+ | SaleId | CusId | Qty | Price | +--------+-------+------+-------+ | 1 | 1 | 5 | 5.50 | | 2 | 3 | 10 | 5.20 | | 3 | 2 | 3 | 5.90 | | 4 | 1 | 15 | 5.00 | | 5 | 2 | 9 | 5.40 | | 6 | 4 | 6 | 5.50 | +--------+-------+------+-------+
Next we run the inner join:
mysql> select
SaleId as 'id',
concat(FirstName, ' ',
LastName) as 'Full Name',
Qty, Price
from Sales
inner join Customers
on Customers.CustomerId = CusId;
We can see that the row with SaleId of 6 has been left out:
+----+----------------+------+-------+ | id | Full Name | Qty | Price | +----+----------------+------+-------+ | 1 | Tom Rex | 5 | 5.50 | | 2 | Ken Star | 10 | 5.20 | | 3 | Luke Skywalker | 3 | 5.90 | | 4 | Tom Rex | 15 | 5.00 | | 5 | Luke Skywalker | 9 | 5.40 | +----+----------------+------+-------+
Now the left join will also join two tables like the inner join, but it will keep the rows of the left table (the Sales table) even if the does not have a CusId in the Customers table. The value for column Full Name will be NULL.
Here is the SQL code for left join, which is almost the same as inner join:
mysql> select
SaleId as 'id',
concat(FirstName, ' ',
LastName) as 'Full Name',
Qty, Price
from Sales
left join Customers
on Customers.CustomerId = CusId;
The result is:
+----+----------------+------+-------+ | id | Full Name | Qty | Price | +----+----------------+------+-------+ | 1 | Tom Rex | 5 | 5.50 | | 4 | Tom Rex | 15 | 5.00 | | 3 | Luke Skywalker | 3 | 5.90 | | 5 | Luke Skywalker | 9 | 5.40 | | 2 | Ken Star | 10 | 5.20 | | 6 | NULL | 6 | 5.50 | +----+----------------+------+-------+
order by
We cannot sort the table rows in SQL databases. But we can sort the result with keywords order by:
mysql> select * from Customers order by FirstName;
The code will return a table with rows sorted by the column FirstName in ascending order:
+------------+-----------+-----------+ | CustomerId | FirstName | LastName | +------------+-----------+-----------+ | 3 | Ken | Star | | 2 | Luke | Skywalker | | 1 | Tom | Rex | +------------+-----------+-----------+
Note that we can sort by descending order with the keyword desc:
mysql> select * from Customers order by CustomerId desc;
Run the line and we have:
+------------+-----------+-----------+ | CustomerId | FirstName | LastName | +------------+-----------+-----------+ | 3 | Ken | Star | | 2 | Luke | Skywalker | | 1 | Tom | Rex | +------------+-----------+-----------+