On this page
Structured Query Language (SQL)
Structured Query Language (SQL)
July 30, 2021
On this page
Some commonly used SQL Commands
| Operation | Description |
|---|---|
| SELECT | extracts data from a database |
| UPDATE | updates data in a database |
| DELETE | deletes data from a database |
| INSERT INTO | inserts new data into a database |
| CREATE DATABASE | creates a new database |
| ALTER DATABASE | modifies a database |
| CREATE TABLE | creates a new table |
| ALTER TABLE | modifies a table |
| DROP TABLE | deletes a table |
| CREATE INDEX | creates an index (search key) |
| DROP INDEX | deletes an index |
Select Queries
SELECT
id,
title
FROM
`products`
WHERE
`id` = 50
ORDER BY
`id` DESC
LIMIT
1;
SELECT
id,
title
from
`products`
WHERE
`id` = 50
ORDER BY
`id` ASC
LIMIT
1;
SELECT
id,
title
from
`products`
where
`id` = 50
ORDER BY
`id` ASC
LIMIT
0, 20;
SELECT
id,
title
FROM
`products`
where
`id` IN (50, 60, 70)
ORDER BY
`id` ASC
LIMIT
0, 20;
select
id,
title
from
`products`
WHERE
title LIKE 'a%'
--Finds any values that start with "a"
select
id,
title
from
`products`
WHERE
title LIKE '%a' --Finds any values that end with "a"
select
id,
title
from
`products`
WHERE
title LIKE '%or%' --Finds any values that have "or" in any position
select
id,
title
from
`products`
WHERE
title LIKE '_r%' --Finds any values that have "r" in the second position
select
id,
title
from
`products`
WHERE
title LIKE 'a_%' --Finds any values that start with "a" and are at least 2 characters in length
select
id,
title
from
`products`
WHERE
title LIKE 'a__%' --Finds any values that start with "a" and are at least 3 characters in length
select
id,
title
from
`products`
WHERE
title LIKE 'a%o' --Finds any values that start with "a" and ends with "o"
The SQL query below says “return only 10 records, start on record 16 (OFFSET 15)”:
SELECT * FROM `products` LIMIT 10 OFFSET 15You could also use a shorter syntax to achieve the same result:
SELECT * FROM Orders LIMIT 15, 10
Create Table:
CREATE TABLE products( id int NOT NULL AUTO_INCREMENT, title varchar(255), descrition text, created_at datetime, PRIMARY KEY (id) );
CREATE TABLE user_management(
id int NOT NULL AUTO_INCREMENT,
account_id int,
customer_id int,
first_name varchar(255),
last_name varchar(255),
email_address varchar(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);
Delete Queries:
DELETE FROM `products` WHERE id IN (15,16,17,18,19,20,21);
The date range lies in between two dates in MySQL query
SELECT
*
FROM
`table_name`
WHERE
created_at < '2023-02-10 00:00:00'
AND created_at > '2023-02-01 00:00:00'