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;
select from where
This command is used to query a table with condition:
First we create a Sales table:
mysql> create table Sales
(SaleId int not null auto_increment primary key,
CusId int,
Qty int,
Price decimal(10, 2));
Only one note here is that the decimal is the format for a number with the decimal point. 10 is the size of the integral part and 2 is the size of the fractional part. The CusId is the CustomerId of each customer.
Then we insert new row into the Sales table:
mysql> insert into Sales
(CusId, Qty, Price)
values
(1, 5, 5.5), (3, 10, 5.2), (2, 3, 5.9),
(1, 15, 5), (2, 9, 5.4);
Now we can check the Sales table:
mysql> select * from Sales;
We have the output:
+--------+------------+------+-----------+ | SaleId | CustomerId | Qty | UnitPrice | +--------+------------+------+-----------+ | 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 | +--------+------------+------+-----------+
We can query all the Sales items with the totals (qty * unit price) are greater than 30.00
mysql> select *,
(Price * Qty) as "Total"
from Sales
where Price * Qty > 30.00;
Explanation:
– the select *, will return all columns.
– the (UnitPrice * Qty) as “Total” will create a column named “Total” in the result, the value is the product of UnitPrice and Qty.
– the where clause will filter only row that UnitPrice * Qty > 30.
Now run the code:
+--------+-------+------+-------+-------+ | SaleId | CusId | Qty | Price | Total | +--------+-------+------+-------+-------+ | 2 | 3 | 10 | 5.20 | 52.00 | | 4 | 1 | 15 | 5.00 | 75.00 | | 5 | 2 | 9 | 5.40 | 48.60 | +--------+-------+------+-------+-------+
We can add more logic to where clause with and, or keywords. Say we will query all the Sales items with the totals (qty * unit price) are greater than 30.00 and the Qty greater or equal to 10:
mysql> select *,
(Price * Qty) as "Total"
from Sales
where Price * Qty > 30.00 and Qty >= 10
Run the code and of course we have:
+--------+-------+------+-------+-------+ | SaleId | CusId | Qty | Price | Total | +--------+-------+------+-------+-------+ | 2 | 3 | 10 | 5.20 | 52.00 | | 4 | 1 | 15 | 5.00 | 75.00 | +--------+-------+------+-------+-------+
update set
This command is used to update a row:
Back to Customers table:
+------------+-----------+----------+ | CustomerId | FirstName | LastName | +------------+-----------+----------+ | 1 | Tom | Rex | | 2 | Higie | Roniek | | 3 | Ken | Star | +------------+-----------+----------+
Say we want to change the customer with FirstName Higie and LastName Roniek to
FirstName of Luke and LastName of Skywalker:
mysql> update Customers
set
FirstName = 'Luke',
LastName = 'Skywalker'
where FirstName = 'Higie'
and LastName = 'Roniek';
Explanation:
– the update keyword comes first then the name of the table.
– next the set keyword and we set the value of each column that we want to modify.
– the where clause tell MySQL which row to modify.
Now we query the whole table again:
mysql> select * from Customers;
The output:
+------------+-----------+-----------+ | CustomerId | FirstName | LastName | +------------+-----------+-----------+ | 1 | Tom | Rex | | 2 | Luke | Skywalker | | 3 | Ken | Star | +------------+-----------+-----------+
Voila, we have just updated the row.
delete from where and
This command is used to delete a row:
First we insert a dummy row:
mysql> insert into Customers
(FirstName, LastName)
values ('dummy', 'dummy');
Run the code and query select * again:
+------------+-----------+-----------+ | CustomerId | FirstName | LastName | +------------+-----------+-----------+ | 1 | Tom | Rex | | 2 | Luke | Skywalker | | 3 | Ken | Star | | 4 | dummy | dummy | +------------+-----------+-----------+
Now we will delete the row with FirstName dummy:
mysql> delete from Customers
where FirstName = 'dummy'
and LastName = 'dummy';
Now we query all the table again:
mysql> select * from Customers;
And the table is now:
+------------+-----------+-----------+ | CustomerId | FirstName | LastName | +------------+-----------+-----------+ | 1 | Tom | Rex | | 2 | Luke | Skywalker | | 3 | Ken | Star | +------------+-----------+-----------+
Great. So we know basic operations in SQL, that is insertion of a new row, retrieving data from rows, modifying a row, and deletion of a row. They are called CRUD, which is short for Create, Read, Update, Delete.