Some commonly used SQL Commands

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

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'