Some commonly used SQL Commands

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 15

You 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);

SQL: Joins (Inner, Left, Right and Full Joins)

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Types of the JOINs in SQL:

• (INNER) JOIN: Returns records that have matching values in both tables
• LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
• RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
• FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

Example:

SELECT
  Orders.OrderID,
  Customers.CustomerName,
  Orders.OrderDate
FROM
  Orders
  INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

EXPLAIN table_name;

ALTER TABLE
  table_name
ADD
  column_name varchar(255);

ALTER TABLE
    user_management
ADD
    COLUMN flag INT DEFAULT 0,
ADD
    COLUMN id_created_by int(11);

Insert into table

INSERT INTO
table_name (title, type)
VALUES
('Test', 'Type 1');

INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas.

Example:

INSERT INTO tbl_name
    (a,b,c)
VALUES
    (1,2,3),
    (4,5,6),
    (7,8,9);

SQL Command Line

mysql -u DB_USER_NAME -p YOUR_PASSWORD -h YOUR_HOST_NAME DATABASE_NAME

Insert data using a CSV file

LOAD DATA LOCAL INFILE '/var/www/html/csv_file/your_csv_file.csv' IGNORE
INTO TABLE my_table
fields terminated BY ','
lines terminated BY '\n'
IGNORE 1 ROWS
(@col1, @col2, @col3,@col4,,@col5)
SET col1= @col1, col2= @col2, col3= @col3, col4= @col4, col5= @col5

Let’s break down the code step by step:

1. LOAD DATA LOCAL INFILE '/var/www/html/csv_file/your_csv_file.csv':

   This part of the code specifies the path to the CSV file you want to load data from. In this example, the file path is ‘/var/www/html/csv_file/your_csv_file.csv’. The LOCAL keyword indicates that the file is located on the local file system of the MySQL client.

2. INTO TABLE my_table:

   This part of the code specifies the target database table where you want to insert the data. Replace my_table with the name of your actual MySQL table.

3. FIELDS TERMINATED BY ',':

   This part of the code specifies that the fields in the CSV file are separated by a comma (,). This delimiter is used to split each line of the CSV file into individual columns.

4. LINES TERMINATED BY '\n':

   This part of the code specifies that each line in the CSV file is terminated by a newline character (\n). This indicates the end of a row in the CSV file.

5. IGNORE 1 ROWS:

   This optional part of the code tells MySQL to ignore the first row of the CSV file. This is often used when the first row contains headers and you don’t want to insert it as data.

6. (@col1, @col2, @col3, @col4, @col5):

   This part of the code defines user variables @col1, @col2, @col3, @col4, and @col5. These variables correspond to the columns in your MySQL table.

7. SET col1 = @col1, col2 = @col2, col3 = @col3, col4 = @col4, col5 = @col5:

   This part of the code sets the values of the table columns (col1, col2, col3, col4, col5) to the values stored in the user variables (@col1, @col2, @col3, @col4, @col5) for each row of data in the CSV file. This effectively maps the columns in the CSV file to the corresponding columns in the database table.

Update data based on another table

UPDATE table_1 
INNER JOIN table_2 
ON table_1.internal_id = table_2.internal_id 
SET table_1.city = table_2.city, 
table_1.state = table_2.state
WHERE table_2.internal_id = table_2.internal_id

Connect your database using the command:

mysql -h host_name -u db_user_name -p db_name

Database backup using the command:

mysqldump -h host_name -u db_user_name -p db_name table_name > /folder/table_name.sql

Finding duplicate values in a SQL table

SELECT
    customer_email, customer_mobile, COUNT(*)
FROM
    wp_cf7_customer_data
GROUP BY
    customer_email, customer_mobile
HAVING 
    COUNT(*) > 1

SQL: Indexing in Databases

Indexing is a way to optimize the performance of a database by minimizing the number of disk accesses required when a query is processed. It is a data structure technique that is used to quickly locate and access the data in a database.

Indexes are created using a few database columns.  

What Does Indexing Do?

Indexing is the way to get an unordered table into an order that will maximize the query’s efficiency while searching. For example, the table below represents a table in a fictional data source, that is completely unordered. https://prnt.sc/u1DA-SN2Q_Pp  If we were to run the following query:

SELECT
  company_id,
  units,
  unit_cost
FROM
  index_test
WHERE
  company_id = 18

The database would have to search through all 17 rows in the order they appear in the table, from top to bottom, one at a time. So to search for all of the potential instances of the company_id number 18, the database must look through the entire table for all appearances of 18 in the company_id column. With an index on the company_id column, the table would, essentially, “look” like this: https://prnt.sc/_Waf_AkDID0y Now, the database can search for company_id number 18 and return all the requested columns for that row then move on to the next row. If the next row’s comapny_id number is also 18 then it will return the all the columns requested in the query. If the next row’s company_id is 20, the query knows to stop searching and the query will finish.

SQL: Stored Procedure

A stored procedure is a collection of SQL statements that are stored in the database. A stored procedure can contain business logic, which is one of the key aspects that distinguish stored procedures from views. A stored procedure can accept parameters, and you can set variables, write IF statements, etc within a stored procedure.

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it. You can also pass parameters to a stored procedure so that the stored procedure can act based on the parameter value(s) that is passed.

Syntax

CREATE PROCEDURE sp_name(p_1 INT)
BEGIN
...code goes here...
END;

Example

Calling a Stored Procedure

We can use CALL keyword to invoke stored procedures.

CALL spGetPosts()

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'