SQL

Cheatsheet: MySQL

Create Table:

CREATE TABLE products(
  id int NOT NULL AUTO_INCREMENT,
  title varchar(255),
  descrition text,
  created_at datetime,
  PRIMARY KEY (id)
);

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

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

Insert into table

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

SQL CMD Line

mysql -u root -p your_password -h your_host_name database_name

 

Leave a Reply

Your email address will not be published. Required fields are marked *