MySQL Queries and some Operators

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.

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

MySQL create query statement

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', 'hbaker@gmail.com');

Output

MySQL insert query statement

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','cjenson11@gmail.com');

Output

MySQL insert query syntax

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 = 'hannahbaker13@gmail.com'
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','dinoross@gmail.com'
FROM `t1`;

Output

MySQL select query statement

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

MySQL select query statement

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 select query with where clause

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 OperatorDescription
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

MySQL select query with LIKE operator

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

MySQL select query with IN operator

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

MySQL select query with IN operator

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

MySQL select query with IN operator

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 'dinoross@gmail.com' AND 'hahaha@gmail.com';

Output

MySQL select query with BETWEEN operator

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 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *

Read previous post:
Simple Registration and Login form
Simple Registration and Login form with PHP and MySQL

Here we are discussing how to create simple login form in PHP and MySQL. You have to follow below basic...

Close