This article will teach some basic MySQL queries, which are important to master MySQL database. Also, we will learn some supporting operators for MySQL queries.
Index
MySQL Queries
MySQL provides some basic queries like CREATE, INSERT, SELECT, UPDATE, and DELETE to execute CRUD operations in the database. Here, we will learn each query with a brief explanation.
SQL CREATE TABLE Statement
This Statement is used to create a new table in the database.
Syntax
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
...
);Example
Put this code into your SQL statement and click on the Go button to create a table.
CREATE TABLE t1 (
FirstName varchar(255),
LastName varchar(255),
Email varchar(255)
);Output
SQL INSERT INTO Statement
The INSERT INTO Statement has two possible ways to write the query.
Syntax
Firstly, both the column names and values are inserted.
INSERT INTO table_name(column1,column2,column3,...)
VALUES (value1,value2,value3,...);Example 1
INSERT INTO t1 (FirstName, LastName, Email)
VALUES ('Hannah', 'Baker', '[email protected]');Output
If the values are in the same order as column names, you do not have to specify column names.
Syntax
INSERT INTO table_name
VALUES (value1,value2,value3,...);Example 2
INSERT INTO t1
VALUES ('Clay','Jenson','[email protected]');Output
SQL UPDATE Statement
This statement is used to change the existing records in the table.
Syntax
UPDATE table_name
SET colum1=value1, column2=value2,column3=value3,...
WHERE condition;Example
UPDATE t1
SET Email = '[email protected]'
WHERE id = 0;Output
SQL DELETE Statement
This statement is used to delete the existing records in the table.
Syntax
DELETE FROM table_name WHERE condition;Example
DELETE FROM t1 WHERE FirstName='Hannah';Output
SQL SELECT Statement
This statement allows for selecting data from the table.
Syntax
SELECT column1, column2, ...
FROM table_name;Example
SELECT 'Ross','Geller','[email protected]'
FROM `t1`;Output
If you want to select all the columns available in the database, use the following syntax:
SELECT * FROM table_name;Example
SELECT * FROM t1;Output
SQL WHERE Clause
The WHERE clause is used to filter records.
The WHERE clause is used to filter data with specific conditions.
It returns only those records that fulfill the condition.
Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition;Example
SELECT*FROM t1
WHERE LastName='Geller';Output
MySQL Operators
As mentioned above, MySQL queries help us to execute CRUD operations on databases. Besides, MySQL operators play a major role in SQL queries.
In short, MySQL operators work as filters in queries to do operations on our desired data. Here, we will learn some popular SQL operators.
SQL LIKE Operator
This operator is used with the WHERE in SQL to filter for a specified pattern in a field.
There are two wildcards used with the LIKE operator:
- % (Percentage) – It represents zero, one, or multiple frames.
- _ (Underscore) – It represents a single character.
Syntax
SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern;Some examples showing different LIKE operators with ‘%’ and ‘_’ wildcards:
| LIKE Operator | Description |
|---|---|
| WHERE FirstName LIKE ‘a%’ | Finds any values that start with “a” |
| WHERE FirstName LIKE ‘%a’ | Finds any values that end with “a” |
| WHERE FirstName LIKE ‘%or%’ | Finds any values that have “or” in any position |
| WHERE FirstName LIKE ‘_o%’ | Finds any values that have “o” in the second position |
| WHERE FirstName LIKE ‘a_%_%’ | Finds any values that start with “a” and are at least 3 characters in length |
| WHERE FirstName LIKE ‘p%o’ | Finds any values that start with “p” and ends with “o” |
Example
SELECT*FROM t1 WHERE FirstName LIKE 'J%';Output
SQL IN Operator
This operator allows us to specify multiple values in WHERE clause.
Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);Example 1
SELECT * FROM t1
WHERE LastName IN ('Buffay', 'Bing');Output
The following SQL statement selects all LastName fields that are NOT in “Buffay,” “Bing” category:
Example 2
SELECT * FROM t1
WHERE LastName NOT IN ('Buffay', 'Bing');Output
Or
SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);Example 3
SELECT * FROM t1 WHERE Email IN (SELECT Email FROM t1);Output
SQL BETWEEN Operator
This operator selects values within a given range. It can be numbers, text, or dates.
Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;Example
SELECT * FROM t1 WHERE Email BETWEEN '[email protected]' AND '[email protected]';Output
Read Also: [SOLUTION] Warning: Mysqli_num_rows() Expects Parameter 1 to Be Mysqli_result
Conclusion
I hope now you have a complete understanding of MySQL queries and operators. MySQL database is quite popular among all other databases. Also, learning MySQL has many benefits regarding DBA jobs. So, keep exploring MySQL.
Enjoy Coding 🙂
