Paul Garth's Projects & Actions

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

CREATE DATABASE southwind; /*creates a new database and names it*/
SHOW DATABASES; /*displays all databases*/
USE southwind; /*tells mysql which database you want to work with*/
SELECT DATABASE; /*tells you which database has been selected*/

SHOW TABLES; /*displays any tables that are associated with database*/

mysql> CREATE TABLE products (
-> productID INT UnSIGNED NOT NULL AUTO_INCREMENT,
-> productCode CHAR(3) NOT NULL DEFAULT ”,
-> name VARCHAR(30) NOT NULL DEFAULT ”,
-> quantity INT UNSIGNED NOT NULL DEFAULT 0,
-> price DECIMAL(7,2) NOT NULL DEFAULT 99999.99,
-> PRIMARY KEY (productID)
-> );

SHOW TABLES; /*at this point, with a new database, would show one table*/

DESCRIBE products; /*displays the structure of the table products*/

SHOW CREATE TABLE products \G /*More detailed than DESCRIBE*/

Explanations for structure of each variable:
– productID is INT UNSIGNED – non-negative integers.
– productCode is CHAR(3) – a fixed-length alphanumeric string of 3 characters.
– name is VARCHAR(30) – a variable-length string of up to 30 characters.
Note: We use fixed-length string for productCode, as we assume that the productCode contains exactly 3 characters. On the other hand, we use variable-length string for name, as its length varies – VARCHAR is more efficient than CHAR.
– quantity is also INT UNSIGNED (non-negative integers).
– price is DECIMAL(10,2) – a decimal number with 2 decimal places.
Note: DECIMAL is precise (represented as integer with a fix decimal point). On the other hand, FLOAT and DOUBLE (real numbers) are not precise and are approximated. DECIMAL type is recommended for currency.

The attribute “NOT NULL” specifies that the column cannot contain the NULL value. NULL is a special value indicating “no value”, “unknown value” or “missing value”. In our case, these columns shall have a proper value. We also set the default value of the columns. The column will take on its default value, if no value is specified during the record creation.

We set the column productID as the so-called primary key. Values of the primary-key column must be unique. Every table shall contain a primary key. This ensures that every row can be distinguished from other rows. You can specify a single column or a set of columns (e.g., firstName and lastName) as the primary key. An index is build automatically on the primary-key column to facilitate fast search. Primary key is also used as reference by other tables.

We set the column productID to AUTO_INCREMENT. with default starting value of 1. When you insert a row with NULL (recommended) (or 0, or a missing value) for the AUTO_INCREMENT column, the maximum value of that column plus 1 would be inserted. You can also insert a valid value to an AUTO_INCREMENT column, bypassing the auto-increment.

Now we can add data into the new table called products

This is how to, from terminal, add data into the fields within products
INSERT INTO products VALUES (1001, ‘PEN’, ‘Pen Red’, 5000, 1.23);

In this case, because productID is set for auto_increment, I can enter NULL, and it will automatically pick the next number in sequence). In this case, two more records:
INSERT INTO products VALUES
(NULL, ‘PEN’, ‘Pen Blue’, 8000, 1.25),
(NULL, ‘PEN’, ‘Pen Black’, 2000, 1.25);

In this case, you can specify the fields. Because I left ProductID blank it automatically enters
INSERT INTO products (productCode, name, quantity, price) VALUES
(‘PEC’, ‘Pencil 2B’, 10000, 0.48),
(‘PEC’, ‘Pencil 2H’, 8000, 0.49);

Because I’ve got NULL within the data structure, if a field is entered without info it just defaults without error
INSERT INTO products (productCode, name) VALUES (‘PEC’, ‘Pencil HB’);

This returns an expected error because the first field (productID) cannot be null
mysql> INSERT INTO products values (NULL, NULL, NULL, NULL, NULL);
ERROR 1048 (23000): Column ‘productCode’ is set for NOT NULL

select * from products;
Returns the entries I’ve made within products
Note: I’ve also been playing with myPHPAdmin, and seeing the various changes and reports

delete from products where productID = 1006;
Note: This deletes this record. It also had some missing data.