Table of Contents
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 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 products( id int NOT NULL AUTO_INCREMENT, title varchar(255), descrition text, created_at datetime, PRIMARY KEY (id) );
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
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);
Insert into table
INSERT INTO table_name (title, type) VALUES ('Test', 'Type 1');
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.
CREATE PROCEDURE sp_name(p_1 INT) BEGIN ...code goes here... END;
Calling a Stored Procedure
We can use CALL keyword to invoke stored procedures.
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'