Note: This is the part where I might do most of my entries — searching for stuff within the database. Not quite running reports — that’s later.
Select (variable name) from (table name) — returns all records
For example:
Select name, price from products;
mysql> select name, price from products;
ERROR 1046 (3D000): No database selected
Note: In this case I haven’t done a USE yet on southwinds or I didn’t specify the table
This is the return when I haven’t selected the database, but I reference it:
mysql> select name, price from southwind.products;
+———–+——-+
| name | price |
+———–+——-+
| Pen Red | 1.23 |
| Pen Blue | 1.25 |
| Pen Black | 1.25 |
| Pencil 2B | 0.48 |
| Pencil 2H | 0.49 |
+———–+——-+
5 rows in set (0.00 sec)
This is the return after I’ve done a USE (to specify a database), and then a select:
use southwind;
mysql> select name, price from products;
+———–+——-+
| name | price |
+———–+——-+
| Pen Red | 1.23 |
| Pen Blue | 1.25 |
| Pen Black | 1.25 |
| Pencil 2B | 0.48 |
| Pencil 2H | 0.49 |
+———–+——-+
5 rows in set (0.00 sec)
This is the return when I’m selecting all variables for all records
mysql> select * from products;
+———–+————-+———–+———-+——-+
| productID | productCode | name | quantity | price |
+———–+————-+———–+———-+——-+
| 1001 | PEN | Pen Red | 5000 | 1.23 |
| 1002 | PEN | Pen Blue | 8000 | 1.25 |
| 1003 | PEN | Pen Black | 2000 | 1.25 |
| 1004 | PEC | Pencil 2B | 10000 | 0.48 |
| 1005 | PEC | Pencil 2H | 8000 | 0.49 |
+———–+————-+———–+———-+——-+
5 rows in set (0.00 sec)
This is the return when doing some basic math without actual tables
mysql> select 1+1;
+—–+
| 1+1 |
+—–+
| 2 |
+—–+
1 row in set (0.00 sec)
This is the return when asking for current date and time. Hey, that’s right!
mysql> select now();
+———————+
| now() |
+———————+
| 2020-02-05 19:12:28 |
+———————+
1 row in set (0.00 sec)
You can also do this for multiple variables
mysql> select 1+1, NOW();
+—–+———————+
| 1+1 | NOW() |
+—–+———————+
| 2 | 2020-02-05 19:13:45 |
+—–+———————+
1 row in set (0.00 sec)
Comparison Operators
Now we can become more granular with our search requests. I remember in the past learning about boolean variables. There are different kinds of operators based on the type of variable.
For number (eg. INT (Integer), DECIMAL (whole number), FLOAT (fraction of a whole number)) we can use these comparison operators to compare two numbers:
‘=’ (equal to)
‘<>‘ or ‘!=’ (not equal to)
‘>’ (greater than)
‘<' (less than)
'>=’ (greater than or equal to)
‘<=' (less than or equal to)
For example:
price > 1.0 (Price greater than 1.0)
quantity <= 500 (quantity less than or equal to 500)
In practice:
use southwind; (I'm specifying which database to work with)
show tables; (What tables do we have) - products
describe products; (Now we know what the variables and types are)
mysql> select name, price from products where price < 1.0;
+-----------+-------+
| name | price |
+-----------+-------+
| Pencil 2B | 0.48 |
| Pencil 2H | 0.49 |
+-----------+-------+
2 rows in set (0.00 sec)
mysql> select name, quantity from products where quantity <= 2000;
+-----------+----------+
| name | quantity |
+-----------+----------+
| Pen Black | 2000 |
+-----------+----------+
1 row in set (0.00 sec)
Comparing variables of type float
Do not compare (eg '=' or '<>‘) float variables for equality because they are not precise
But, we could compare decimals, because they are precise
Note: Up to now, my database doesn’t have a float variable to test.
We can compare two strings with operators like ‘=’, ‘<>‘, ‘>’, ‘<', '>=’, ‘<='
Reviewing again the fields we have:
mysql> describe products;
+————-+——————+——+—–+———-+—————-+
| Field | Type | Null | Key | Default | Extra |
+————-+——————+——+—–+———-+—————-+
| productID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| productCode | char(3) | NO | | | |
| name | varchar(30) | NO | | | |
| quantity | int(10) unsigned | NO | | 0 | |
| price | decimal(7,2) | NO | | 99999.99 | |
+————-+——————+——+—–+———-+—————-+
5 rows in set (0.00 sec)
Focusing on productcode, which is of type char (alphanumeric characters) with a max field length of 3 characters.
mysql> select name, price from products where productCode = ‘PEN’;
+———–+——-+
| name | price |
+———–+——-+
| Pen Red | 1.23 |
| Pen Blue | 1.25 |
| Pen Black | 1.25 |
+———–+——-+
3 rows in set (0.00 sec)
mysql> select name, price, productCode from products where productCode = ‘PEN’;
+———–+——-+————-+
| name | price | productCode |
+———–+——-+————-+
| Pen Red | 1.23 | PEN |
| Pen Blue | 1.25 | PEN |
| Pen Black | 1.25 | PEN |
+———–+——-+————-+
3 rows in set (0.00 sec)
String Matching