SQL wildcards can substitute for one or more characters when searching for data in a database.

It  uses with the SQL LIKE operator

MySQL Table – users

+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  2 | JS   |
|  3 | HTML |
|  5 | ASP  |
+—-+——+

Example

mysql> select * from users WHERE name LIKE ‘%HP’;
+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
+—-+——+
1 row in set (0.00 sec)

Here % is the wildcard

Other wildcard available

‘ – ‘ – substitute for a single character
[char-list] – substitute for given characters

It searchs for a specified pattern in a column

SQL LIKE Syntax

SELECT * FROM table_name WHERE column_name LIKE pattern

The “%” sign can be used to define wildcards (missing letters in the pattern) both before and after the pattern.

‘%ab ‘ – It will return data that end with the letter ‘ab’
‘ab%’ – It returns the data that starts with ‘ab’
‘ab’ – its similar to =
‘%ab%’ – it returns the data that has the value ‘ab’ in it

MySQL Table – users

+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  2 | JS   |
|  3 | HTML |
|  5 | ASP  |
+—-+——+

Example

mysql> select * from users WHERE name LIKE ‘%HP’;
+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
+—-+——+
1 row in set (0.00 sec)

mysql> select * from users WHERE name LIKE ‘HT%’;
+—-+——+
| id | name |
+—-+——+
|  3 | HTML |
+—-+——+
1 row in set (0.00 sec)

mysql> select * from users WHERE name LIKE ‘HT%’;
+—-+——+
| id | name |
+—-+——+
|  3 | HTML |
+—-+——+
1 row in set (0.00 sec)

mysql> select * from users WHERE name LIKE ‘%p%’;
+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  5 | ASP  |
+—-+——+
2 rows in set (0.00 sec)

We can use this to specify the numbers of record sets to return

SQL LIMIT Syntax

SELECT * FROM table_name LIMIT some_value(should be a number)

MySQL Table – users

+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  2 | JS   |
|  3 | HTML |
|  5 | ASP  |
+—-+——+

Example

mysql> select * from users  LIMIT 2;
+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  2 | JS   |
+—-+——+
2 rows in set (0.00 sec)

It delete rows in a table.

SQL DELETE Syntax

DELETE FROM table_name WHERE some_column=some_value

MySQL Table – users

+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  2 | JS   |
|  3 | HTML |
|  4 | PHP  |
|  5 | ASP  |
+—-+——+

Example

mysql> DELETE FROM users WHERE id = 4;
Query OK, 1 row affected (0.00 sec)

mysql> select * from users  ;
+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  2 | JS   |
|  3 | HTML |
|  5 | ASP  |
+—-+——+
4 rows in set (0.00 sec)

It can be used to modify the data .

SQL UPDATE Syntax

UPDATE table_name SET column1=value WHERE some_column=some_value ;

MySQL Table – users

+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  2 | JS   |
|  3 | HTML |
|  4 | PHP  |
|  5 | AS   |
+—-+——+

Example

mysql> UPDATE users SET name=’ASP’ WHERE id=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from users  ;
+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  2 | JS   |
|  3 | HTML |
|  4 | PHP  |
|  5 | ASP  |
+—-+——+
5 rows in set (0.00 sec)

It insert a new row in a table

SQL INSERT INTO Syntax

It can be used in two form

1) Without mentioning column names . In that case we must enter values for all fields .

INSERT INTO table_name VALUES (value1, value2.)

2) By mentioning column names and values

INSERT INTO table_name (column1, column2) VALUES (value1, value2)

MySQL Table – users

+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  2 | JS   |
|  3 | HTML |
|  4 | PHP  |
+—-+——+

Example

mysql> INSERT INTO users values(5,”AS”);
Query OK, 1 row affected (0.02 sec)

mysql> select * from users  ;
+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  2 | JS   |
|  3 | HTML |
|  4 | PHP  |
|  5 | AS   |
+—-+——+
5 rows in set (0.00 sec)

It sort the result-set by a specified column and by default it sort in ascending order

SQL ORDER BY Syntax

SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DES ;

MySQL Table – users

+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  2 | JS   |
|  3 | HTML |
|  4 | PHP  |
+—-+——+

Example

mysql> select * from users ORDER BY name ;
+—-+——+
| id | name |
+—-+——+
|  3 | HTML |
|  2 | JS   |
|  1 | PHP  |
|  4 | PHP  |
+—-+——+
4 rows in set (0.02 sec)

mysql> select * from users ORDER BY name DESC;
+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  4 | PHP  |
|  2 | JS   |
|  3 | HTML |
+—-+——+
4 rows in set (0.00 sec)

It select the data if one of the conditions given is true

SQL OR Syntax

SELECT column_name(s) FROM table_name WHERE column1 operator value OR  column1 operator value ;

MySQL Table – users

+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  2 | JS   |
|  3 | HTML |
|  4 | PHP  |
+—-+——+

Example

mysql> select * from users where name=’PHP’ OR  id=4;
+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  4 | PHP  |
+—-+——+
2 rows in set (0.00 sec)

It select the data if all the conditions given are true

SQL AND Syntax

SELECT column_name(s) FROM table_name WHERE column1 operator value AND  column1 operator value ;

MySQL Table – users

+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  2 | JS   |
|  3 | HTML |
|  4 | PHP  |
+—-+——+

Example

mysql> select * from users where name=’PHP’ AND id=4;
+—-+——+
| id | name |
+—-+——+
|  4 | PHP  |
+—-+——+
1 row in set (0.00 sec)

It select the data based on the condition given .

SQL WHERE Syntax

SELECT column_name(s) FROM table_name WHERE column_name operator value ;

MySQL Table – users

+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  2 | JS   |
|  3 | HTML |
|  4 | PHP  |
+—-+——+

Example

mysql> select * from users where name=’PHP’;
+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  4 | PHP  |
+—-+——+
2 rows in set (0.00 sec)