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".