
Some commonly used SQL Commands
SELECT
– extracts data from a databaseUPDATE
– updates data in a databaseDELETE
– deletes data from a databaseINSERT INTO
– inserts new data into a databaseCREATE DATABASE
– creates a new databaseALTER DATABASE
– modifies a databaseCREATE TABLE
– creates a new tableALTER TABLE
– modifies a tableDROP TABLE
– deletes a tableCREATE 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 tablesLEFT (OUTER) JOIN
: Returns all records from the left table, and the matched records from the right tableRIGHT (OUTER) JOIN
: Returns all records from the right table, and the matched records from the left tableFULL (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 useVALUES
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'