MySQL Queries and some Operators

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

In a first way, both the column names and values are inserted.

INSERT INTO table_name(column1,column2,column3,...)
VALUES (value1,value2,value3,...);

Example :

INSERT INTO t1 (FirstName, LastName, Email)
VALUES ('Hannah', 'Baker', '[email protected]');

Output :

If the values are in the same order as column names than you do not have to specify column names.

Syntax :

INSERT INTO table_name 
VALUES (value1,value2,value3,...);

Example :

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 database, use 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 fulfil the condition.

Syntax :

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example :

SELECT*FROM t1
WHERE LastName='Geller';

Output :

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 :

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 :

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 :

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 :

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 :

Leave a Reply

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