MySQL LIKE - NOT LIKE

LIKE and NOT LIKE are operators used in MySQL for pattern matching in SQL queries.

LIKE is used to match patterns in a given string column.

NOT LIKE is similar to LIKE, but it matches all records that do not match the specified pattern. 

Wildcards are special characters used in MySQL to represent one or more characters in a string. They are used with the LIKE operator to search for patterns in strings. The two most commonly used wildcards in MySQL are the percent sign (%) and the underscore (_).

• % (percent sign) - represents any number of characters, including zero characters.
• _ (underscore) - represents a single character.

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

mysql>

mysql> INSERT INTO friends
    -> VALUES(NULL, 'Ana', 'Smith', 111222);
Query OK, 1 row affected (0.07 sec)

mysql> INSERT INTO friends
    -> VALUES(NULL, 'Ana', 'Gatez', 555666);
Query OK, 1 row affected (0.18 sec)

mysql> INSERT INTO friends
    -> VALUES(NULL, 'John', 'Snow', 666777);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO friends
    -> VALUES(NULL, 'John', 'Smith', 999888);
Query OK, 1 row affected (0.12 sec)

mysql>

This code inserts four new rows into the "friends" table in a MySQL database.

Each row represents a person with their name, last name, and telephone number. The code uses the INSERT INTO command to add a new row into the table with the VALUES keyword to provide the data for each column of the row.

Since the first column of the table is an auto-incremented id, the value NULL is used to let the database generate a unique id for each new row. 

mysql> SELECT * FROM friends;
+----+------+----------+-----------+
| id | Name | Lastname | Telephone |
+----+------+----------+-----------+
| 15 | Ana  | Smith    |    111222 |
| 16 | Ana  | Gatez    |    555666 |
| 17 | John | Snow     |    666777 |
| 18 | John | Smith    |    999888 |
+----+------+----------+-----------+
4 rows in set (0.00 sec)

mysql>

mysql> SELECT * FROM friends WHERE Telephone LIKE "1%";
+----+------+----------+-----------+
| id | Name | Lastname | Telephone |
+----+------+----------+-----------+
| 15 | Ana  | Smith    |    111222 |
+----+------+----------+-----------+
1 row in set (0.03 sec)

mysql>

This code selects all rows from the "friends" table where the "Telephone" column starts with the digit "1".

The wildcard symbol "%" after the "1" means that any sequence of characters can follow the "1" digit. In this case, it only returns one row where the "Telephone" value is 111222 and the person's name is Ana Smith. 

mysql> SELECT * FROM friends WHERE id LIKE "1%";
+----+------+----------+-----------+
| id | Name | Lastname | Telephone |
+----+------+----------+-----------+
| 15 | Ana  | Smith    |    111222 |
| 16 | Ana  | Gatez    |    555666 |
| 17 | John | Snow     |    666777 |
| 18 | John | Smith    |    999888 |
+----+------+----------+-----------+
4 rows in set (0.00 sec)

mysql>

The query selects all rows from the "friends" table where the "id" column starts with the digit "1".  

mysql> SELECT * FROM friends WHERE Lastname LIKE "%z";
+----+------+----------+-----------+
| id | Name | Lastname | Telephone |
+----+------+----------+-----------+
| 16 | Ana  | Gatez    |    555666 |
+----+------+----------+-----------+
1 row in set (0.00 sec)

mysql>

This MySQL query selects all rows from the "friends" table where the "Lastname" column ends with the letter "z". The '%' symbol is used as a wildcard, meaning it matches any sequence of zero or more characters.

Therefore, "%z" matches any string that ends with "z". In this case, only one row with id=16 matches the condition. 

mysql> SELECT * FROM friends WHERE Telephone LIKE "%777";
+----+------+----------+-----------+
| id | Name | Lastname | Telephone |
+----+------+----------+-----------+
| 17 | John | Snow     |    666777 |
+----+------+----------+-----------+
1 row in set (0.00 sec)

mysql>

This query selects all rows from the "friends" table where the "Telephone" column ends with "777". 

mysql> SELECT * FROM friends WHERE Lastname NOT LIKE "Sm%";
+----+------+----------+-----------+
| id | Name | Lastname | Telephone |
+----+------+----------+-----------+
| 16 | Ana  | Gatez    |    555666 |
| 17 | John | Snow     |    666777 |
+----+------+----------+-----------+
2 rows in set (0.00 sec)

mysql>

This SQL command selects all rows from the "friends" table where the value of the "Lastname" column does not start with "Sm". The resulting table contains the ID, Name, Lastname, and Telephone of the two rows where the condition is satisfied. 

mysql> SELECT * FROM friends WHERE Telephone LIKE "%12%";
+----+------+----------+-----------+
| id | Name | Lastname | Telephone |
+----+------+----------+-----------+
| 15 | Ana  | Smith    |    111222 |
+----+------+----------+-----------+
1 row in set (0.00 sec)

mysql>

This command selects all rows from the "friends" table where the "Telephone" column contains the string "12" anywhere in the phone number. In this case, it returns only the row where the telephone number is "111222", which matches the search pattern.