MySQL DELETE From Table

Initial situation in our table: 

mysql> SELECT * FROM friends;
+----+-----------+----------+-----------+
| id | Name      | Lastname | Telephone |
+----+-----------+----------+-----------+
|  1 | John      | Smith    |    111111 |
|  2 | Anastasia | TheGreat |    111111 |
|  3 | Samantha  | Fox      |    111111 |
| 10 | Larry     | TheFirst |    111111 |
| 11 | Bill      | Gatezzz  |    111111 |
| 12 | Bill      | Gatezzz  |    111111 |
| 13 | John      | Smith    |    111111 |
| 14 | Anastasia | Some     |    111111 |
+----+-----------+----------+-----------+
8 rows in set (0.00 sec)

mysql>

Delete example targeting one column with specific id:

mysql> DELETE FROM friends WHERE (id=1);
Query OK, 1 row affected (0.10 sec)

mysql>

The above MySQL query deletes the row from the friends table where the id is equal to 1.

The DELETE statement is used to delete one or more rows from a table.

In this case, the WHERE clause is used to specify the condition that the row with id=1 should be deleted. Once the query is executed, the row is removed from the table, and the query returns a message indicating the number of affected rows, which in this case is 1. 

mysql> DELETE FROM friends WHERE (Name='Anastasia');
Query OK, 2 rows affected (0.22 sec)

mysql>

This command will delete all rows from the table "friends" where the Name column has a value of "Anastasia". 

Situation after delete command:

mysql> SELECT * FROM friends;
+----+----------+----------+-----------+
| id | Name     | Lastname | Telephone |
+----+----------+----------+-----------+
|  3 | Samantha | Fox      |    111111 |
| 10 | Larry    | TheFirst |    111111 |
| 11 | Bill     | Gatezzz  |    111111 |
| 12 | Bill     | Gatezzz  |    111111 |
| 13 | John     | Smith    |    111111 |
+----+----------+----------+-----------+
5 rows in set (0.00 sec)

mysql>

We can use OR with DELETE:

mysql> DELETE FROM friends WHERE (id=3 OR id=13);
Query OK, 2 rows affected (0.12 sec)

mysql>

This command is deleting rows from the friends table where the id column equals either 3 or 13.

The syntax of the command is as follows:

• DELETE FROM friends: This specifies the table to delete rows from.
• WHERE (id=3 OR id=13): This specifies the condition that must be met in order for rows to be deleted. In this case, rows will only be deleted if their id value is either 3 or 13. 

mysql> SELECT * FROM friends;
+----+-------+----------+-----------+
| id | Name  | Lastname | Telephone |
+----+-------+----------+-----------+
| 10 | Larry | TheFirst |    111111 |
| 11 | Bill  | Gatezzz  |    111111 |
| 12 | Bill  | Gatezzz  |    111111 |
+----+-------+----------+-----------+
3 rows in set (0.00 sec)

mysql>

mysql> DELETE FROM friends WHERE Telephone=111111;
Query OK, 3 rows affected (0.28 sec)

mysql>

This command deletes all rows from the "friends" table where the "Telephone" column has a value of 111111. The output message shows that three rows have been affected, indicating that three rows have been deleted from the table. 

mysql> SELECT * FROM friends;
Empty set (0.00 sec)

mysql>

The output shows that the SELECT query returned an empty set, indicating that there are no more rows in the friends table after deleting the rows where the Telephone value is 111111. 

mysql> DESCRIBE friends;
+-----------+----------+------+-----+---------+----------------+
| Field     | Type     | Null | Key | Default | Extra          |
+-----------+----------+------+-----+---------+----------------+
| id        | int      | NO   | PRI | NULL    | auto_increment |
| Name      | char(30) | YES  |     | NULL    |                |
| Lastname  | char(30) | YES  |     | NULL    |                |
| Telephone | int      | YES  |     | NULL    |                |
+-----------+----------+------+-----+---------+----------------+
4 rows in set (0.05 sec)

mysql>

MySQL - UPDATE Data in Table

The UPDATE statement is used to modify existing data in a table. 

mysql> SELECT * FROM friends;
+----+-----------+----------+-----------+
| id | Name      | Lastname | Telephone |
+----+-----------+----------+-----------+
|  1 | John      | Smith    |    111333 |
|  2 | Anastasia | TheGreat |    555777 |
|  3 | Samantha  | Fox      |    456456 |
| 10 | Larry     | TheFirst |    555888 |
| 11 | Bill      | Gates    |    444888 |
| 12 | Bill      | Gates    |    444888 |
| 13 | John      | Smith    |    111333 |
| 14 | Anastasia | Some     |    555444 |
+----+-----------+----------+-----------+
8 rows in set (0.00 sec)

mysql>

This query updates the Telephone field of the row in the friends table where id is equal to 1: 

mysql> UPDATE friends SET Telephone=000000
    -> WHERE (id=1);
Query OK, 1 row affected (0.11 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

The new value for the Telephone field is set to 000000.

The query returns a message indicating that one row was affected by the update, indicating that the update was successful. 

mysql> SELECT * FROM friends;
+----+-----------+----------+-----------+
| id | Name      | Lastname | Telephone |
+----+-----------+----------+-----------+
|  1 | John      | Smith    |         0 |
|  2 | Anastasia | TheGreat |    555777 |
|  3 | Samantha  | Fox      |    456456 |
| 10 | Larry     | TheFirst |    555888 |
| 11 | Bill      | Gates    |    444888 |
| 12 | Bill      | Gates    |    444888 |
| 13 | John      | Smith    |    111333 |
| 14 | Anastasia | Some     |    555444 |
+----+-----------+----------+-----------+
8 rows in set (0.00 sec)

mysql>

This MySQL query updates the "Telephone" field of the row with "id=1" in the "friends" table to 999999: 

mysql> UPDATE friends SET Telephone=999999
    -> WHERE (id=1);
Query OK, 1 row affected (0.09 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

The UPDATE statement is used to modify or change existing records in a table.

In this case, the SET clause is used to specify the column to be updated, which is the "Telephone" field. The WHERE clause is used to identify the specific row to be updated, which is the row with "id=1".

When the query is executed, it updates the row with the specified ID and sets the "Telephone" field to the new value of 999999. The output indicates that one row was matched and changed, with no warnings. 

mysql> SELECT * FROM friends;
+----+-----------+----------+-----------+
| id | Name      | Lastname | Telephone |
+----+-----------+----------+-----------+
|  1 | John      | Smith    |    999999 |
|  2 | Anastasia | TheGreat |    555777 |
|  3 | Samantha  | Fox      |    456456 |
| 10 | Larry     | TheFirst |    555888 |
| 11 | Bill      | Gates    |    444888 |
| 12 | Bill      | Gates    |    444888 |
| 13 | John      | Smith    |    111333 |
| 14 | Anastasia | Some     |    555444 |
+----+-----------+----------+-----------+
8 rows in set (0.00 sec)

mysql>

mysql> UPDATE friends SET Lastname='Gatezzz'
    -> WHERE (Name='Bill' AND Lastname='Gates');
Query OK, 2 rows affected (0.10 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql>

mysql> SELECT * FROM friends;
+----+-----------+----------+-----------+
| id | Name      | Lastname | Telephone |
+----+-----------+----------+-----------+
|  1 | John      | Smith    |    999999 |
|  2 | Anastasia | TheGreat |    555777 |
|  3 | Samantha  | Fox      |    456456 |
| 10 | Larry     | TheFirst |    555888 |
| 11 | Bill      | Gatezzz  |    444888 |
| 12 | Bill      | Gatezzz  |    444888 |
| 13 | John      | Smith    |    111333 |
| 14 | Anastasia | Some     |    555444 |
+----+-----------+----------+-----------+
8 rows in set (0.00 sec)

mysql>

mysql> UPDATE friends SET Telephone=111111;
Query OK, 8 rows affected (0.11 sec)
Rows matched: 8  Changed: 8  Warnings: 0

mysql>

mysql> SELECT * FROM friends;
+----+-----------+----------+-----------+
| id | Name      | Lastname | Telephone |
+----+-----------+----------+-----------+
|  1 | John      | Smith    |    111111 |
|  2 | Anastasia | TheGreat |    111111 |
|  3 | Samantha  | Fox      |    111111 |
| 10 | Larry     | TheFirst |    111111 |
| 11 | Bill      | Gatezzz  |    111111 |
| 12 | Bill      | Gatezzz  |    111111 |
| 13 | John      | Smith    |    111111 |
| 14 | Anastasia | Some     |    111111 |
+----+-----------+----------+-----------+
8 rows in set (0.00 sec)

mysql>

MySQL ORDER BY - ASC & DESC

The ORDER BY clause is used to sort the query results in either ascending (ASC) or descending (DESC) order.

The ASC keyword is used to sort the result set in ascending order, which is the default behavior if you don't specify any order.

The DESC keyword, on the other hand, is used to sort the result set in descending order.

Content of our table:

mysql> SELECT * FROM friends;
+----+-----------+----------+-----------+
| id | Name      | Lastname | Telephone |
+----+-----------+----------+-----------+
|  1 | John      | Smith    |    111333 |
|  2 | Anastasia | TheGreat |    555777 |
|  3 | Samantha  | Fox      |    456456 |
| 10 | Larry     | TheFirst |    555888 |
| 11 | Bill      | Gates    |    444888 |
| 12 | Bill      | Gates    |    444888 |
| 13 | John      | Smith    |    111333 |
| 14 | Anastasia | Some     |    555444 |
+----+-----------+----------+-----------+
8 rows in set (0.00 sec)

mysql>

Usage of ORDER BY targeting column id: 

mysql> SELECT * FROM friends ORDER BY id DESC;
+----+-----------+----------+-----------+
| id | Name      | Lastname | Telephone |
+----+-----------+----------+-----------+
| 14 | Anastasia | Some     |    555444 |
| 13 | John      | Smith    |    111333 |
| 12 | Bill      | Gates    |    444888 |
| 11 | Bill      | Gates    |    444888 |
| 10 | Larry     | TheFirst |    555888 |
|  3 | Samantha  | Fox      |    456456 |
|  2 | Anastasia | TheGreat |    555777 |
|  1 | John      | Smith    |    111333 |
+----+-----------+----------+-----------+
8 rows in set (0.02 sec)

mysql>

Targeting column Name ascending: 

mysql> SELECT * FROM friends ORDER BY Name ASC;
+----+-----------+----------+-----------+
| id | Name      | Lastname | Telephone |
+----+-----------+----------+-----------+
|  2 | Anastasia | TheGreat |    555777 |
| 14 | Anastasia | Some     |    555444 |
| 11 | Bill      | Gates    |    444888 |
| 12 | Bill      | Gates    |    444888 |
|  1 | John      | Smith    |    111333 |
| 13 | John      | Smith    |    111333 |
| 10 | Larry     | TheFirst |    555888 |
|  3 | Samantha  | Fox      |    456456 |
+----+-----------+----------+-----------+
8 rows in set (0.01 sec)

mysql>

mysql> SELECT * FROM friends ORDER BY Name DESC;
+----+-----------+----------+-----------+
| id | Name      | Lastname | Telephone |
+----+-----------+----------+-----------+
|  3 | Samantha  | Fox      |    456456 |
| 10 | Larry     | TheFirst |    555888 |
|  1 | John      | Smith    |    111333 |
| 13 | John      | Smith    |    111333 |
| 11 | Bill      | Gates    |    444888 |
| 12 | Bill      | Gates    |    444888 |
|  2 | Anastasia | TheGreat |    555777 |
| 14 | Anastasia | Some     |    555444 |
+----+-----------+----------+-----------+
8 rows in set (0.00 sec)

mysql>

This MySQL query selects all columns from the "friends" table and orders the results in descending order by the "Name" column. The resulting table will contain all rows from the "friends" table, sorted in reverse alphabetical order by the values in the "Name" column.

The DESC keyword is used to specify that the order should be descending.

If you used ASC instead, the results would be sorted in ascending order. 

mysql> SELECt * FROM friends ORDER BY Lastname ASC;
+----+-----------+----------+-----------+
| id | Name      | Lastname | Telephone |
+----+-----------+----------+-----------+
|  3 | Samantha  | Fox      |    456456 |
| 11 | Bill      | Gates    |    444888 |
| 12 | Bill      | Gates    |    444888 |
|  1 | John      | Smith    |    111333 |
| 13 | John      | Smith    |    111333 |
| 14 | Anastasia | Some     |    555444 |
| 10 | Larry     | TheFirst |    555888 |
|  2 | Anastasia | TheGreat |    555777 |
+----+-----------+----------+-----------+
8 rows in set (0.00 sec)

mysql>

More experiments: 

mysql> SELECT id, Name, Telephone FROM friends ORDER BY Lastname ASC;
+----+-----------+-----------+
| id | Name      | Telephone |
+----+-----------+-----------+
|  3 | Samantha  |    456456 |
| 11 | Bill      |    444888 |
| 12 | Bill      |    444888 |
|  1 | John      |    111333 |
| 13 | John      |    111333 |
| 14 | Anastasia |    555444 |
| 10 | Larry     |    555888 |
|  2 | Anastasia |    555777 |
+----+-----------+-----------+
8 rows in set (0.00 sec)

mysql>

mysql> SELECT Lastname, id, Name, Telephone FROM friends ORDER BY Lastname ASC;
+----------+----+-----------+-----------+
| Lastname | id | Name      | Telephone |
+----------+----+-----------+-----------+
| Fox      |  3 | Samantha  |    456456 |
| Gates    | 11 | Bill      |    444888 |
| Gates    | 12 | Bill      |    444888 |
| Smith    |  1 | John      |    111333 |
| Smith    | 13 | John      |    111333 |
| Some     | 14 | Anastasia |    555444 |
| TheFirst | 10 | Larry     |    555888 |
| TheGreat |  2 | Anastasia |    555777 |
+----------+----+-----------+-----------+
8 rows in set (0.00 sec)

mysql>

mysql> SELECT Lastname, id, Name, Telephone FROM friends ORDER BY Lastname DESC;
+----------+----+-----------+-----------+
| Lastname | id | Name      | Telephone |
+----------+----+-----------+-----------+
| TheGreat |  2 | Anastasia |    555777 |
| TheFirst | 10 | Larry     |    555888 |
| Some     | 14 | Anastasia |    555444 |
| Smith    |  1 | John      |    111333 |
| Smith    | 13 | John      |    111333 |
| Gates    | 11 | Bill      |    444888 |
| Gates    | 12 | Bill      |    444888 |
| Fox      |  3 | Samantha  |    456456 |
+----------+----+-----------+-----------+
8 rows in set (0.00 sec)

mysql>

MySQL NOT Condition

MySQL NOT condition is a logical operator that reverses the boolean result of a condition.

It can be used to exclude rows from a query result that meet a certain condition.  

Generic select result:

mysql> SELECT * FROM friends;
+----+-----------+----------+-----------+
| id | Name      | Lastname | Telephone |
+----+-----------+----------+-----------+
|  1 | John      | Smith    |    111333 |
|  2 | Anastasia | TheGreat |    555777 |
|  3 | Samantha  | Fox      |    456456 |
| 10 | Larry     | TheFirst |    555888 |
| 11 | Bill      | Gates    |    444888 |
| 12 | Bill      | Gates    |    444888 |
| 13 | John      | Smith    |    111333 |
| 14 | Anastasia | Some     |    555444 |
+----+-----------+----------+-----------+
8 rows in set (0.00 sec)

mysql>

Usage of NOT condition: 

mysql> SELECT * FROM friends WHERE NOT Telephone=111333;
+----+-----------+----------+-----------+
| id | Name      | Lastname | Telephone |
+----+-----------+----------+-----------+
|  2 | Anastasia | TheGreat |    555777 |
|  3 | Samantha  | Fox      |    456456 |
| 10 | Larry     | TheFirst |    555888 |
| 11 | Bill      | Gates    |    444888 |
| 12 | Bill      | Gates    |    444888 |
| 14 | Anastasia | Some     |    555444 |
+----+-----------+----------+-----------+
6 rows in set (0.00 sec)

mysql>

The NOT condition is used to exclude the rows where the Telephone value is equal to 111333.

The query returns all rows where the Telephone value is not equal to 111333.

mysql> SELECT * FROM friends WHERE NOT Name='Anastasia' AND NOT Lastname='Gates';
+----+----------+----------+-----------+
| id | Name     | Lastname | Telephone |
+----+----------+----------+-----------+
|  1 | John     | Smith    |    111333 |
|  3 | Samantha | Fox      |    456456 |
| 10 | Larry    | TheFirst |    555888 |
| 13 | John     | Smith    |    111333 |
+----+----------+----------+-----------+
4 rows in set (0.00 sec)

mysql>

This is a MySQL query that selects all rows from the "friends" table where the "Name" column is not equal to 'Anastasia' AND the "Lastname" column is not equal to 'Gates'.

The query uses the NOT operator to negate the condition of the WHERE clause. The first condition is "NOT Name='Anastasia'", which will select all rows where the value of the "Name" column is not equal to 'Anastasia'. The second condition is "NOT Lastname='Gates'", which will select all rows where the value of the "Lastname" column is not equal to 'Gates'.

The AND operator is used to combine these two conditions, so only rows that meet both conditions will be selected. T

Therefore, this query will select all rows from the "friends" table where the "Name" is not 'Anastasia' AND the "Lastname" is not 'Gates'. 

mysql> SELECT id, Name, Telephone FROM friends WHERE NOT Lastname='Fox';
+----+-----------+-----------+
| id | Name      | Telephone |
+----+-----------+-----------+
|  1 | John      |    111333 |
|  2 | Anastasia |    555777 |
| 10 | Larry     |    555888 |
| 11 | Bill      |    444888 |
| 12 | Bill      |    444888 |
| 13 | John      |    111333 |
| 14 | Anastasia |    555444 |
+----+-----------+-----------+
7 rows in set (0.00 sec)

mysql>

This SELECT statement will retrieve data from the "friends" table that matches the following conditions:

• The "Lastname" column does not contain the value "Fox".
• For the resulting rows, only the "id", "Name", and "Telephone" columns will be displayed.

The "NOT" keyword is used to negate the condition. So, this statement will return all rows where the "Lastname" is not equal to "Fox".

The result will include the id, name, and telephone number of all the friends whose last name is not "Fox". 

mysql> SELECT * FROM friends WHERE NOT Name='Anastasia' OR NOT Lastname='Gates';
+----+-----------+----------+-----------+
| id | Name      | Lastname | Telephone |
+----+-----------+----------+-----------+
|  1 | John      | Smith    |    111333 |
|  2 | Anastasia | TheGreat |    555777 |
|  3 | Samantha  | Fox      |    456456 |
| 10 | Larry     | TheFirst |    555888 |
| 11 | Bill      | Gates    |    444888 |
| 12 | Bill      | Gates    |    444888 |
| 13 | John      | Smith    |    111333 |
| 14 | Anastasia | Some     |    555444 |
+----+-----------+----------+-----------+
8 rows in set (0.00 sec)

mysql>

The query "SELECT * FROM friends WHERE NOT Name='Anastasia' OR NOT Lastname='Gates'" will retrieve all rows from the "friends" table where either the "Name" is not "Anastasia" OR the "Lastname" is not "Gates".

This means that the query will return all rows where either the "Name" is not "Anastasia" (even if the "Lastname" is "Gates") OR the "Lastname" is not "Gates" (even if the "Name" is "Anastasia").

MySQL AND & OR Conditions

AND and OR are logical operators used to create complex conditions for filtering data in a SELECT statement.

AND operator is used to retrieve records that meet all specified conditions.

For example, if we want to retrieve records where both the Name and the Lastname match the specified criteria, we would use the AND operator. The syntax would be: 

SELECT * FROM table_name WHERE condition1 AND condition2;

On the other hand, OR operator is used to retrieve records that meet at least one of the specified conditions.

For example, if we want to retrieve records where either the Name or the Lastname match the specified criteria, we would use the OR operator. The syntax would be:

SELECT * FROM table_name WHERE condition1 OR condition2;

Examples

mysql> SELECT * FROM friends;
+----+-----------+----------+-----------+
| id | Name      | Lastname | Telephone |
+----+-----------+----------+-----------+
|  1 | John      | Smith    |    111333 |
|  2 | Anastasia | TheGreat |    555777 |
|  3 | Samantha  | Fox      |    456456 |
| 10 | Larry     | TheFirst |    555888 |
| 11 | Bill      | Gates    |    444888 |
| 12 | Bill      | Gates    |    444888 |
| 13 | John      | Smith    |    111333 |
| 14 | Anastasia | Some     |    555444 |
+----+-----------+----------+-----------+
8 rows in set (0.00 sec)

mysql>

In MySQL, single quotes are used to enclose string values. This is necessary to distinguish string values from other types of values, such as numeric values or column names. 

mysql> SELECT * FROM friends WHERE Name='Larry' AND Lastname='TheFirst';
+----+-------+----------+-----------+
| id | Name  | Lastname | Telephone |
+----+-------+----------+-----------+
| 10 | Larry | TheFirst |    555888 |
+----+-------+----------+-----------+
1 row in set (0.00 sec)

mysql>

The output of the above query shows all the columns of the row that matches the conditions specified in the WHERE clause, i.e., the name is "Larry" and the last name is "TheFirst".

There is only one row that meets these conditions, so the output contains only that row.

mysql> SELECT * FROM friends WHERE Name='Larry' AND Name='John';
Empty set (0.00 sec)

mysql>

The output of the above query is an empty set, as there is no row in the table where the name is both "Larry" and "John" at the same time.

The condition Name='Larry' and Name='John' are contradictory, and no row can satisfy both conditions simultaneously. 

mysql> SELECT * FROM friends WHERE id=12 AND Telephone=444888;
+----+------+----------+-----------+
| id | Name | Lastname | Telephone |
+----+------+----------+-----------+
| 12 | Bill | Gates    |    444888 |
+----+------+----------+-----------+
1 row in set (0.00 sec)

mysql>

You should not use single quotes around integer values in MySQL tables. This is because MySQL will treat the value as a string, rather than a numeric value, and may cause unexpected behavior in queries or calculations.

In general, you should use quotes only around string values and omit them for numeric values and other data types such as dates, times, and booleans. 

mysql> SELECT Name FROM friends WHERE id=13 AND Lastname='Smith';
+------+
| Name |
+------+
| John |
+------+
1 row in set (0.00 sec)

mysql>

We will experiment with more requests. Select all: 

mysql> SELECT * FROM friends;
+----+-----------+----------+-----------+
| id | Name      | Lastname | Telephone |
+----+-----------+----------+-----------+
|  1 | John      | Smith    |    111333 |
|  2 | Anastasia | TheGreat |    555777 |
|  3 | Samantha  | Fox      |    456456 |
| 10 | Larry     | TheFirst |    555888 |
| 11 | Bill      | Gates    |    444888 |
| 12 | Bill      | Gates    |    444888 |
| 13 | John      | Smith    |    111333 |
| 14 | Anastasia | Some     |    555444 |
+----+-----------+----------+-----------+
8 rows in set (0.00 sec)

mysql>

Where and or combinations: 

mysql> SELECT * FROM friends WHERE Name='John' OR Telephone=555444;
+----+-----------+----------+-----------+
| id | Name      | Lastname | Telephone |
+----+-----------+----------+-----------+
|  1 | John      | Smith    |    111333 |
| 13 | John      | Smith    |    111333 |
| 14 | Anastasia | Some     |    555444 |
+----+-----------+----------+-----------+
3 rows in set (0.00 sec)

mysql>

mysql> SELECT * FROM friends WHERE id=3 OR Lastname='Some';
+----+-----------+----------+-----------+
| id | Name      | Lastname | Telephone |
+----+-----------+----------+-----------+
|  3 | Samantha  | Fox      |    456456 |
| 14 | Anastasia | Some     |    555444 |
+----+-----------+----------+-----------+
2 rows in set (0.00 sec)

mysql>

MySQL WHERE Clause

Situation in our database at the moment:

mysql> SELECT * FROM friends;
+----+-----------+----------+-----------+
| id | Name      | Lastname | Telephone |
+----+-----------+----------+-----------+
|  1 | John      | Smith    |    111333 |
|  2 | Anastasia | TheGreat |    555777 |
|  3 | Samantha  | Fox      |    456456 |
| 10 | Larry     | TheFirst |    555888 |
| 11 | Bill      | Gates    |    444888 |
| 12 | Bill      | Gates    |    444888 |
| 13 | John      | Smith    |    111333 |
+----+-----------+----------+-----------+
7 rows in set (0.00 sec)

mysql>

This is a sample MySQL query that selects all columns and all rows from the "friends" table where the value in the "Name" column is equal to "Anastasia".

The WHERE clause is used to filter the results and return only those rows where the specified condition is true. 

mysql> SELECT * FROM friends WHERE Name='Anastasia';
+----+-----------+----------+-----------+
| id | Name      | Lastname | Telephone |
+----+-----------+----------+-----------+
|  2 | Anastasia | TheGreat |    555777 |
+----+-----------+----------+-----------+
1 row in set (0.00 sec)

mysql>

The output shows one row of data that meets the specified condition, with values in the "id", "Name", "Lastname", and "Telephone" columns. The "id" column displays the unique identifier of the record, while the "Name", "Lastname", and "Telephone" columns display the name and phone number of the individual whose name is "Anastasia".

It's worth noting that the value in the "Name" column is case-sensitive, so "Anastasia" and "anastasia" would be considered different values by the query.

If there were multiple rows in the "friends" table with the same name, only the rows where the name is "Anastasia" would be returned by this query.

mysql> INSERT INTO friends (id, Name, Lastname, Telephone)
    -> VALUES (NULL, 'Anastasia', 'Some', 555444);
Query OK, 1 row affected (0.38 sec)

mysql>

This is a sample MySQL query that inserts a new record into the "friends" table.

The VALUES keyword is used to specify the values to be inserted into each column of the table. The NULL value in the "id" column indicates that the database should generate a unique identifier for the new record.

The values 'Anastasia', 'Some', and 555444 are inserted into the "Name", "Lastname", and "Telephone" columns, respectively. The output shows that one row was affected by the query, indicating that the record was successfully inserted into the table.

After executing this query, the "friends" table would contain an additional row with a unique identifier, the name "Anastasia", the last name "Some", and a phone number of 555444. 

mysql> SELECT id, Lastname, Telephone FROM friends WHERE Name='Bill';
+----+----------+-----------+
| id | Lastname | Telephone |
+----+----------+-----------+
| 11 | Gates    |    444888 |
| 12 | Gates    |    444888 |
+----+----------+-----------+
2 rows in set (0.00 sec)

mysql>

This is a sample MySQL query that selects the "id", "Lastname", and "Telephone" columns from the "friends" table where the value in the "Name" column is equal to "Bill". 

The output shows two rows of data that meet the specified condition, with values in the "id", "Lastname", and "Telephone" columns. Both rows have a value of "Bill" in the "Name" column, but different values in the "id", "Lastname", and "Telephone" columns.

It's worth noting that the value in the "Name" column is case-sensitive, so "Bill" and "bill" would be considered different values by the query. If there were multiple rows in the "friends" table with the same name, all the rows where the name is "Bill" would be returned by this query. 

mysql> SELECT id, Lastname, Telephone FROM friends WHERE Name='Larry';
+----+----------+-----------+
| id | Lastname | Telephone |
+----+----------+-----------+
| 10 | TheFirst |    555888 |
+----+----------+-----------+
1 row in set (0.00 sec)

mysql>

This is a sample MySQL query that selects the "id", "Lastname", and "Telephone" columns from the "friends" table where the value in the "Name" column is equal to "Larry". The WHERE clause is used to filter the results and return only those rows where the specified condition is true.

The output shows one row of data that meets the specified condition, with values in the "id", "Lastname", and "Telephone" columns. The row has a value of "Larry" in the "Name" column, and values of "TheFirst" and "555888" in the "Lastname" and "Telephone" columns, respectively.

mysql> SELECT Lastname, Telephone, Name, id FROM friends WHERE Name='Larry';
+----------+-----------+-------+----+
| Lastname | Telephone | Name  | id |
+----------+-----------+-------+----+
| TheFirst |    555888 | Larry | 10 |
+----------+-----------+-------+----+
1 row in set (0.00 sec)

mysql>

The order of the columns in the SELECT clause determines the order of the columns in the result set.

In this case, the columns are returned in the order "Lastname", "Telephone", "Name", and "id".

MySQL SELECT DISTINCT

Situation in the table at the moment: 

mysql> SELECT * FROM friends;
+----+-----------+----------+-----------+
| id | Name      | Lastname | Telephone |
+----+-----------+----------+-----------+
|  1 | John      | Smith    |    111333 |
|  2 | Anastasia | TheGreat |    555777 |
|  3 | Samantha  | Fox      |    456456 |
| 10 | Larry     | TheFirst |    555888 |
| 11 | Bill      | Gates    |    444888 |
+----+-----------+----------+-----------+
5 rows in set (0.00 sec)

mysql>

These are two sample MySQL queries that insert data into the "friends" table: 

mysql> INSERT INTO friends (id, Name, Lastname, Telephone)
    -> VALUES (NULL, 'Bill', 'Gates', 444888);
Query OK, 1 row affected (0.16 sec)

mysql> INSERT INTO friends (id, Name, Lastname, Telephone)
    -> VALUES (NULL, 'John', 'Smith', 111333);
Query OK, 1 row affected (0.11 sec)

mysql>

The first query inserts a new record with four values into the "friends" table.

The "INSERT INTO" statement is used to add data to a table in a MySQL database. The column names are listed in parentheses after the table name, followed by the keyword "VALUES", and then the values to be inserted in parentheses. In this case, a new record is being added to the table with "NULL" as the value for the "id" column (which should auto-increment).

The second query also inserts a new record into the "friends" table using the same syntax as the first query, but with different values.

Select report:

mysql> SELECT * FROM friends;
+----+-----------+----------+-----------+
| id | Name      | Lastname | Telephone |
+----+-----------+----------+-----------+
|  1 | John      | Smith    |    111333 |
|  2 | Anastasia | TheGreat |    555777 |
|  3 | Samantha  | Fox      |    456456 |
| 10 | Larry     | TheFirst |    555888 |
| 11 | Bill      | Gates    |    444888 |
| 12 | Bill      | Gates    |    444888 |
| 13 | John      | Smith    |    111333 |
+----+-----------+----------+-----------+
7 rows in set (0.00 sec)

mysql>

Usage of "SELECT DISTINCT", targeting specific columns: 

mysql> SELECT DISTINCT Name, Lastname, Telephone FROM friends;
+-----------+----------+-----------+
| Name      | Lastname | Telephone |
+-----------+----------+-----------+
| John      | Smith    |    111333 |
| Anastasia | TheGreat |    555777 |
| Samantha  | Fox      |    456456 |
| Larry     | TheFirst |    555888 |
| Bill      | Gates    |    444888 |
+-----------+----------+-----------+
5 rows in set (0.00 sec)

mysql>

The DISTINCT keyword is used to remove duplicate values from a result set. It is often used with the SELECT statement to select unique or distinct values from one or more columns of a table.

When the DISTINCT keyword is included in a SELECT statement, MySQL evaluates the values in the specified columns and returns only one instance of each unique value. In other words, if there are multiple rows in a table with the same value in the column(s) specified in the SELECT statement, only one of those rows will be included in the result set.

For example, the query "SELECT DISTINCT column1, column2 FROM table" will return a result set with unique combinations of values from the "column1" and "column2" columns of the "table". The DISTINCT keyword can be used with one or more columns, depending on the specific use case.

It's important to note that the DISTINCT keyword considers the entire value in a column when determining uniqueness. So, for example, if a column contains values with leading or trailing whitespace, those values will be considered distinct even if they appear identical when displayed.

The DISTINCT keyword is a useful tool for filtering and analyzing data in a MySQL database, and can be used to remove redundancy from result sets to help streamline analysis and reporting.

MySQL Custom Reports

The SELECT statement retrieves all the rows from the friends table, which has four columns: id, Name, Lastname, and Telephone. The result shows five rows, each representing a person and their corresponding information in the table. 

mysql> SELECT * FROM friends;
+----+-----------+----------+-----------+
| id | Name      | Lastname | Telephone |
+----+-----------+----------+-----------+
|  1 | John      | Smith    |    111333 |
|  2 | Anastasia | TheGreat |    555777 |
|  3 | Samantha  | Fox      |    456456 |
| 10 | Larry     | TheFirst |    555888 |
| 11 | Bill      | Gates    |    444888 |
+----+-----------+----------+-----------+
5 rows in set (0.00 sec)

mysql>

The id column uniquely identifies each person, and it was set to automatically increment by 1 for each new row inserted. The Name column stores the first name of each person, the Lastname column stores their last name, and the Telephone column stores their phone number.

The rows are ordered by the value in the id column in ascending order. 

mysql> SELECT Name from friends;
+-----------+
| Name      |
+-----------+
| John      |
| Anastasia |
| Samantha  |
| Larry     |
| Bill      |
+-----------+
5 rows in set (0.00 sec)

mysql>

This SELECT statement retrieves data from the friends table but only for the Name column, so only the values of the Name column are displayed in the result.

The result shows five rows, each row contains the Name value of a friend from the table. 

mysql> SELECT name from friends;
+-----------+
| name      |
+-----------+
| John      |
| Anastasia |
| Samantha  |
| Larry     |
| Bill      |
+-----------+
5 rows in set (0.00 sec)

mysql>

In MySQL, column names are case insensitive by default, so "name" and "Name" are considered the same column in the "friends" table. When you ran the second SELECT statement using "name" in lowercase, it returned the same result as the first SELECT statement that used "Name" with an uppercase N. 

mysql> SELECT NAME FROM FRIENDS;
+-----------+
| NAME      |
+-----------+
| John      |
| Anastasia |
| Samantha  |
| Larry     |
| Bill      |
+-----------+
5 rows in set (0.00 sec)

mysql>

By default, table and column names are case-insensitive.

This means that whether you use upper-case or lower-case letters to refer to table or column names in your queries, MySQL will treat them the same. In the example you provided, the first query used the lower-case "Name" to select data from the "friends" table, while the second query used the upper-case "NAME".

Both queries returned the same result because MySQL is not case-sensitive in this regard. 

mysql> SELECT Name, Telephone FROM friends;
+-----------+-----------+
| Name      | Telephone |
+-----------+-----------+
| John      |    111333 |
| Anastasia |    555777 |
| Samantha  |    456456 |
| Larry     |    555888 |
| Bill      |    444888 |
+-----------+-----------+
5 rows in set (0.00 sec)

mysql>

This query selects two columns, Name and Telephone, from the friends table. The result will be a table with all rows in the friends table, but only with these two columns, where the column "Name" shows the name of the person and the column "Telephone" shows their phone number. 

mysql> SELECT id, Name, Telephone FROM friends;
+----+-----------+-----------+
| id | Name      | Telephone |
+----+-----------+-----------+
|  1 | John      |    111333 |
|  2 | Anastasia |    555777 |
|  3 | Samantha  |    456456 |
| 10 | Larry     |    555888 |
| 11 | Bill      |    444888 |
+----+-----------+-----------+
5 rows in set (0.00 sec)

mysql>

This is a sample MySQL query that selects three columns, "id", "Name", and "Telephone" from the "friends" table. The results are displayed in a tabular format with one row for each record in the table that meets the query criteria. In this case, there are five rows displayed.

The "SELECT" statement is used to retrieve data from one or more tables in a MySQL database. The "FROM" clause specifies the table or tables from which the data should be retrieved. In this case, the "friends" table is being queried.

MySQL INSERT Alternatives

This MySQL report is showing the result of the SELECT query executed on the "friends" table. It is displaying a table with four columns: id, Name, Lastname, and Telephone.

The table has two rows, where each row represents a record in the "friends" table. 

mysql> SELECT * FROM friends;
+----+-----------+----------+-----------+
| id | Name      | Lastname | Telephone |
+----+-----------+----------+-----------+
|  1 | John      | Smith    |    111333 |
|  2 | Anastasia | TheGreat |    555777 |
+----+-----------+----------+-----------+
2 rows in set (0.00 sec)

mysql>

For example, the first row shows that the person with id 1 is named "John", has a last name of "Smith", and has a telephone number of 111333.

Similarly, the second row shows that the person with id 2 is named "Anastasia", has a last name of "TheGreat", and has a telephone number of 555777. 

This INSERT statement adds a new row to the friends table with the specified values for the id, Name, Lastname, and Telephone columns. The id column is set to NULL, which means that MySQL automatically assigns a new value to it using the AUTO_INCREMENT feature.

The Name, Lastname, and Telephone columns are set to 'Samantha', 'Fox', and 456456, respectively.

mysql> INSERT INTO friends (id, Name, Lastname, Telephone)
    -> VALUES (NULL, 'Samantha', 'Fox', 456456);
Query OK, 1 row affected (0.09 sec)

mysql>

After running the INSERT statement, MySQL returns a message saying that one row was affected. This means that one new row was added to the friends table. 

mysql> SELECT * FROM friends;
+----+-----------+----------+-----------+
| id | Name      | Lastname | Telephone |
+----+-----------+----------+-----------+
|  1 | John      | Smith    |    111333 |
|  2 | Anastasia | TheGreat |    555777 |
|  3 | Samantha  | Fox      |    456456 |
+----+-----------+----------+-----------+
3 rows in set (0.00 sec)

mysql>

The id column was set to NULL, which means that MySQL automatically assigned a new value to it using the AUTO_INCREMENT feature.

When we ran the SELECT statement, all three rows were displayed in the result, including the newly added row for Samantha Fox. 

mysql> INSERT INTO friends (id, Name, Lastname, Telephone)
    -> VALUES (10, 'Larry', 'TheFirst', 555888);
Query OK, 1 row affected (0.17 sec)

mysql>

The above MySQL code is inserting a new row into the "friends" table.

The "id" column is set to 10 explicitly in the INSERT statement, along with values for the "Name", "Lastname", and "Telephone" columns. The query returns "Query OK, 1 row affected", indicating that one row was successfully inserted into the table. 

mysql> SELECT * FROM friends;
+----+-----------+----------+-----------+
| id | Name      | Lastname | Telephone |
+----+-----------+----------+-----------+
|  1 | John      | Smith    |    111333 |
|  2 | Anastasia | TheGreat |    555777 |
|  3 | Samantha  | Fox      |    456456 |
| 10 | Larry     | TheFirst |    555888 |
+----+-----------+----------+-----------+
4 rows in set (0.00 sec)

mysql>

We can see that a new row was added to the friends table using the INSERT statement.

In the first example, we used NULL for the id column, so MySQL assigned a new value to it using the AUTO_INCREMENT feature.

In the second example, we specified a value of 10 for the id column, which was inserted into the table along with the other values.

When we ran the SELECT statement after each INSERT statement, we could see that the table contained all the rows that were added. The number of rows in the result set is displayed at the bottom of the output, which is 3 in the first example and 4 in the second example. 

mysql> INSERT INTO friends (id, Name, Lastname, Telephone)
    -> VALUES (NULL, 'Bill', 'Gates', 444888);
Query OK, 1 row affected (0.08 sec)

mysql>

This SQL statement is inserting a new row into the friends table. The id column is set to NULL, which means that MySQL will automatically assign a new value to it using the AUTO_INCREMENT feature. The Name, Lastname, and Telephone columns are set to the specified values.

The INSERT statement is successful, and MySQL returns a message saying that one row was affected. 

mysql> SELECT * FROM friends;
+----+-----------+----------+-----------+
| id | Name      | Lastname | Telephone |
+----+-----------+----------+-----------+
|  1 | John      | Smith    |    111333 |
|  2 | Anastasia | TheGreat |    555777 |
|  3 | Samantha  | Fox      |    456456 |
| 10 | Larry     | TheFirst |    555888 |
| 11 | Bill      | Gates    |    444888 |
+----+-----------+----------+-----------+
5 rows in set (0.00 sec)

mysql>

Note: It's generally recommended to avoid inserting explicit values into an auto-increment column.

Instead, simply omit the id column from the INSERT statement, and MySQL will assign a new value to it automatically.

MySQL INSERT Values Into Table

Our MySQL code is inserting a new row into the friends table.

The INSERT INTO statement is used to insert data into a table, and the friends (id, Name, Lastname, Telephone) specifies the table and columns where the data will be inserted.

The values to be inserted are specified in the VALUES clause, which is VALUES (NULL, 'John', 'Smith', 111333) in this case. 

mysql> INSERT INTO friends (id, Name, Lastname, Telephone)
    -> VALUES (NULL, 'John', 'Smith', 111333);
Query OK, 1 row affected (0.27 sec)

mysql>

We are inserting a row with the following values:

• id: NULL (the id column is an auto-increment column, so MySQL will generate a unique value for this column)
• Name: 'John'
• Lastname: 'Smith'
• Telephone: 111333

The output Query OK, 1 row affected (0.27 sec) indicates that the query was executed successfully and one row was affected (i.e., inserted) in the friends table. 

mysql> SELECT * FROM friends;
+----+------+----------+-----------+
| id | Name | Lastname | Telephone |
+----+------+----------+-----------+
|  1 | John | Smith    |    111333 |
+----+------+----------+-----------+
1 row in set (0.02 sec)

mysql>

This code is performing a SELECT query on the "friends" table in the "test" database.

The asterisk (*) after the SELECT keyword indicates that all columns in the table should be returned in the result set.

The query returns one row of data that was inserted earlier using an INSERT statement. The row contains the values for the "id", "Name", "Lastname", and "Telephone" columns of the table. 

mysql> INSERT INTO friends (id, Name, Lastname, Telephone)
    -> VALUES (NULL, 'Anastasia', 'TheGreat', 555777);
Query OK, 1 row affected (0.14 sec)

mysql>

This MySQL code inserts a new row of data into the "friends" table.

The data includes the columns "id", "Name", "Lastname", and "Telephone". Since "id" is defined as an AUTO_INCREMENT field, the value for "id" is set to NULL, which will trigger MySQL to automatically assign the next available ID number.

The values for the "Name", "Lastname", and "Telephone" columns are specified in the VALUES clause of the INSERT statement.

The inserted data is: id=2, Name='Anastasia', Lastname='TheGreat', Telephone=555777. The query returns a message indicating that one row was affected. 

mysql> SELECT * FROM friends;
+----+-----------+----------+-----------+
| id | Name      | Lastname | Telephone |
+----+-----------+----------+-----------+
|  1 | John      | Smith    |    111333 |
|  2 | Anastasia | TheGreat |    555777 |
+----+-----------+----------+-----------+
2 rows in set (0.00 sec)

mysql>

As we can see, the INSERT statement added a new row to the friends table with the specified values for the id, Name, Lastname, and Telephone columns.

The id column was set to NULL, which means that MySQL automatically assigned a new value to it using the AUTO_INCREMENT feature.

When we ran the SELECT statement, both rows were displayed in the result.

MySQL Data Types in Tables

The following data types are used in the MySQL code:

• int: This data type stands for integer and is used for the id and Telephone columns. It is used to store whole numbers, both positive and negative.

• char: This data type is used for the Name and Lastname columns. It is used to store strings with a fixed length, which is specified in parentheses. In this case, both Name and Lastname can store up to 30 characters.

• PRI: This is a keyword used in the Key column for the id column, which indicates that it is a primary key.

• NULL: This keyword is used in the Null column to indicate whether a column can contain null values, which means it can have no value assigned to it.

• auto_increment: This is used in the Extra column for the id column and specifies that the value for this column should be automatically generated and incremented for each new row added to the table.

mysql> DESCRIBE friends;
+-----------+----------+------+-----+---------+----------------+
| Field     | Type     | Null | Key | Default | Extra          |
+-----------+----------+------+-----+---------+----------------+
| id        | int      | NO   | PRI | NULL    | auto_increment |
| Name      | char(30) | YES  |     | NULL    |                |
| Lastname  | char(30) | YES  |     | NULL    |                |
| Telephone | int      | YES  |     | NULL    |                |
+-----------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql>

 

Common Datatypes used in MySQL

Some common datatypes used in MySQL are:

• INT: used to store integers.
• VARCHAR: used to store variable-length strings of characters.
• CHAR: used to store fixed-length strings of characters.
• TEXT: used to store large amounts of text.
• DATE: used to store dates in the format YYYY-MM-DD.
• DATETIME: used to store dates and times in the format YYYY-MM-DD HH:MM:SS.
• FLOAT: used to store floating-point numbers.
• BOOLEAN: used to store boolean values (true or false).

 

Why we use parentheses while working with char and varchar

Parentheses are used with CHAR and VARCHAR data types to specify the maximum number of characters that can be stored in a column. For example, if you define a column as VARCHAR(50), it means that this column can store up to 50 characters.

The reason for using parentheses is to help optimize storage space.

By specifying the maximum length of the column, MySQL can allocate just enough storage space to hold the data, rather than using a fixed amount of space for each column. This can help reduce the amount of disk space required and improve performance.

However, it is important to note that using excessively large values for the maximum length of a column can negatively impact performance, as it requires more storage space and can result in slower queries. Therefore, it is recommended to use reasonable and realistic maximum lengths for columns based on the actual data being stored.

 

BLOB in MySQL

BLOB (Binary Large Object) is a data type in MySQL that can be used to store large binary data, such as images, audio files, or videos, in the database.

BLOB can store up to 65,535 bytes of data, or even more if the database is configured to support larger BLOBs. BLOB data type can be useful when you need to store binary data directly in the database instead of on the file system.

BLOB in MySQL, or links to files ?

It is generally advised to store the links to files in the database rather than storing the files themselves using the BLOB data type.

This is because storing large files directly in the database can cause performance issues, and it can be more difficult to manage the files.

Storing links to files allows the files to be stored on a separate file server or in cloud storage, which can improve performance and scalability. Additionally, storing links to files allows the files to be easily updated or replaced without modifying the database.

 

Recommended datatype in MySQL while working with Financial Data

When working with financial data, it is recommended to use the DECIMAL data type in MySQL.

This is because the DECIMAL data type is precise and allows for exact decimal calculations, making it suitable for storing monetary values.

ther numeric data types like FLOAT or DOUBLE may not be as precise and can result in rounding errors when used for financial calculations.

MySQL NULL and NOT NULL

NULL is a special value that represents missing or unknown data. It is different from an empty string or a value of zero.

When a column is defined as allowing NULL values, it means that the column can contain NULL values in addition to regular values of the specified data type.

When a column is defined as NOT NULL, it means that the column cannot contain NULL values and must have a regular value of the specified data type. 

mysql> DESCRIBE friends;
+-----------+----------+------+-----+---------+----------------+
| Field     | Type     | Null | Key | Default | Extra          |
+-----------+----------+------+-----+---------+----------------+
| id        | int      | NO   | PRI | NULL    | auto_increment |
| Name      | char(30) | YES  |     | NULL    |                |
| Lastname  | char(30) | YES  |     | NULL    |                |
| Telephone | int      | YES  |     | NULL    |                |
+-----------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql>

NOT NULL is a constraint that can be applied to a column when creating a table.

It ensures that the column cannot contain NULL values, meaning that every row in the table must have a value for that column.

If a value is not specified for a NOT NULL column during an INSERT operation, the operation will fail with an error. This constraint is commonly used to enforce data integrity and to prevent unexpected behavior when working with the database.

 

What is Primary Key in MySQL

A primary key is a column or set of columns that uniquely identify each row in a table. A primary key constraint ensures that the values in the primary key columns are unique and not null, meaning they cannot be empty.

By defining a primary key for a table, you can ensure data integrity and enable efficient data retrieval operations, as primary keys are typically used for indexing and searching.

In addition, primary keys can be referenced by foreign keys in other tables, enabling the creation of relationships between tables.

 

What are Default Values in MySQL

Default values are used to specify a value that is automatically assigned to a column if no value is provided during insertion. The default value is defined when a table is created, and can be specified for each column individually.

For example, if you have a "users" table with a "created_at" column that has a default value of the current timestamp, then if you insert a row without providing a value for "created_at", MySQL will automatically assign the current timestamp to that column.

Here's an example of creating a table with a default value for a column:

CREATE TABLE example (
   id INT NOT NULL PRIMARY KEY,
   name VARCHAR(50) DEFAULT 'John Doe',
   age INT DEFAULT 18
);

In this example, the "name" column has a default value of "John Doe" and the "age" column has a default value of 18. If you insert a row without providing a value for either of these columns, the default values will be used.

MySQL CREATE TABLE

This code creates a new database named "test" in MySQL: 

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.13 sec)

mysql>

The statement is executed successfully and the query result shows "Query OK, 1 row affected", indicating that one database was created. 

mysql> USE test;
Database changed
mysql>

This code uses the MySQL command USE to switch to the test database that was just created with the CREATE DATABASE command.

The USE command is used to select the database that you want to work with in MySQL.

In this case, the test database is now the active database and any subsequent queries or commands will be executed within this database. 

mysql> CREATE TABLE friends (
    -> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> Name CHAR(30),
    -> Lastname CHAR(30),
    -> Telephone INT);
Query OK, 0 rows affected (1.02 sec)

mysql>

This code creates a new table named "friends" in the currently selected database ("test" in this case). The table has four columns:

• "id": an integer column that serves as the primary key for the table. It is set to not allow NULL values, and is set to auto-increment, meaning that a new value will be automatically generated for each new row inserted into the table.
• "Name": a character column that can store up to 30 characters.
• "Lastname": another character column that can store up to 30 characters.
• "Telephone": an integer column that can store phone numbers.

The query returns "Query OK, 0 rows affected" indicating that the table was successfully created. 

mysql> SHOW TABLES FROM test;
+----------------+
| Tables_in_test |
+----------------+
| friends        |
+----------------+
1 row in set (0.00 sec)

mysql>

This code is displaying the list of tables in the "test" database using the SHOW TABLES statement.

It then returns a table with a single row containing the name of the only table in the "test" database, which is "friends". 

mysql> DESCRIBE friends;
+-----------+----------+------+-----+---------+----------------+
| Field     | Type     | Null | Key | Default | Extra          |
+-----------+----------+------+-----+---------+----------------+
| id        | int      | NO   | PRI | NULL    | auto_increment |
| Name      | char(30) | YES  |     | NULL    |                |
| Lastname  | char(30) | YES  |     | NULL    |                |
| Telephone | int      | YES  |     | NULL    |                |
+-----------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql>

The DESCRIBE statement is used to display the structure of the "friends" table, which shows the names, data types, and other details about each of the table's columns.

MySQL CREATE and DROP Database

The statement starts with the keyword "CREATE" followed by "DATABASE", indicating that a new database will be created. "friends" is the name of the new database being created:

mysql> CREATE DATABASE friends;
Query OK, 1 row affected (0.11 sec)

mysql>

Once the statement is executed, MySQL responds with a message that indicates the query was successful, with "Query OK" and the number of rows affected (in this case, 1).

The time taken to execute the statement is also displayed in seconds (in this case, 0.11 seconds).

In summary, the code creates a new MySQL database called "friends".

mysql> CREATE DATABASE stamps;
Query OK, 1 row affected (0.12 sec)

mysql>

This is a MySQL command to create a new database named "stamps". The syntax for creating a new database in MySQL is:

CREATE DATABASE database_name;

Here, "stamps" is the name of the new database to be created.

The output "Query OK, 1 row affected (0.12 sec)" indicates that the execution of the CREATE DATABASE command was successful and it created the new database named "stamps". 

mysql> USE friends;
Database changed
mysql>

This command is used to select a specific database to work with in MySQL. In this case, the command is "USE friends;", which is selecting the "friends" database.

After executing this command, any subsequent SQL queries will be performed on the "friends" database until a different database is specified with another USE command or the connection is closed.

The output of the command is "Database changed", which indicates that the switch to the "friends" database was successful.

mysql> SHOW TABLES FROM friends;
Empty set (0.00 sec)

mysql>

The SHOW TABLES command is used to display the list of tables in a specific database.

In this case, we switched to the friends database using the USE command, and then ran the SHOW TABLES command to check if there were any tables in the friends database. The output shows that there were no tables in the friends database yet, hence the "Empty set" message. 

mysql> DROP DATABASE friends;
Query OK, 0 rows affected (0.13 sec)

mysql>

This command will drop (delete) the "friends" database from the MySQL server. It is accomplished by executing the DROP DATABASE statement, followed by the name of the database to be dropped.

In this case, the "friends" database has been dropped successfully, as indicated by the "Query OK" message with 0 rows affected. 

mysql> DROP DATABASE stamps;
Query OK, 0 rows affected (0.13 sec)

mysql>

The command "DROP DATABASE" is used to delete a database and all its related objects permanently. In this case, the "stamps" database was created earlier using the command "CREATE DATABASE".

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>

Return to situation before we created custom databases.

MySQL SHOW, USE, DESCRIBE

The command "SHOW DATABASES" is used to list all the databases that are available in the MySQL server: 

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>

In this case, the output shows four databases: information_schema, mysql, performance_schema, and sys. These are system databases that are created automatically when MySQL is installed.

The information_schema database contains information about the MySQL server, such as database metadata, table metadata, and user privileges.

The mysql database contains user account information and other system-level data.

The performance_schema database contains performance-related data, such as statistics on queries and locks.

The sys database is used by MySQL Workbench to provide additional functionality for database administration. 

mysql> SHOW DATABASES \g
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>

This is the same MySQL command as before, which shows the list of databases in the current MySQL instance. The only difference is that the "\g" character sequence is used at the end of the command instead of a semicolon (;).

The "\g" sequence can be used to terminate a command and send it to the server for execution. It is an alternative to using a semicolon as a command terminator. When the "\g" sequence is used, the output of the command is displayed immediately after the command is executed, rather than waiting for the server to return control.

In this case, the output is the same as before, showing the four system databases that are available in the MySQL server. 

mysql> USE mysql;
Database changed
mysql>

This is the output of a MySQL command that changes the current database context to the "mysql" database. The "USE" command is used to select a database in MySQL.

The command "USE mysql" is executed, which selects the "mysql" database. The output shows that the database has been changed successfully.

Once a database is selected using the "USE" command, all subsequent commands that operate on tables or data in the database will be executed in the context of that database, unless a different database is selected using the "USE" command again.

mysql> SHOW TABLES FROM mysql;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| component                 |
| db                        |
| default_roles             |
| engine_cost               |
| func                      |
| general_log               |
| global_grants             |
| gtid_executed             |
| ...                       |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
33 rows in set (0.08 sec)

mysql>

The "SHOW TABLES" command is used to display a list of all the tables in a particular database.

The command "SHOW TABLES FROM mysql" is executed, which lists all the tables that exist in the "mysql" database. The output shows a list of table names, each on a separate line, that includes system tables such as "user", "db", "tables_priv", "columns_priv", and others.

These tables store information about user accounts, permissions, databases, and other metadata that are used by the MySQL server to manage and secure the system. 

mysql> DESCRIBE user;
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                    | Type                              | Null | Key | Default               | Extra |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                     | char(255)                         | NO   | PRI |                       |       |
| User                     | char(32)                          | NO   | PRI |                       |       |
| Select_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Update_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Delete_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Create_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| ...                      | ...                               | ...  |     | ....                  |       |
| password_lifetime        | smallint unsigned                 | YES  |     | NULL                  |       |
| account_locked           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_role_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_role_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Password_reuse_history   | smallint unsigned                 | YES  |     | NULL                  |       |
| Password_reuse_time      | smallint unsigned                 | YES  |     | NULL                  |       |
| Password_require_current | enum('N','Y')                     | YES  |     | NULL                  |       |
| User_attributes          | json                              | YES  |     | NULL                  |       |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
51 rows in set (0.10 sec)

mysql>

The DESCRIBE statement is used to display the structure or schema of a table in a MySQL database. It shows the column names, data types, and any constraints on the columns such as primary key, foreign key, and nullability.

In our example, the DESCRIBE statement is used to display the structure of the user table in the mysql database. It shows that the table has 51 columns, including columns for the username, host, and various privileges. It also shows additional columns for password policies and user attributes.