LEARN TO CODE MySQL

The DELETE statement is used to delete existing records in a table.

Commands	Syntax	Example
DELETE (The DELETE statement is used to delete existing records in a table.)	DELETE FROM table_name WHERE condition;	DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';

Some Important MYSQL commands

Commands	Syntax	Example
NULL Values (A field with a NULL value is a field with no value.)	SELECT column_names FROM table_name WHERE column_name IS NULL;	SELECT CustomerName, ContactName, Address FROM Customers WHERE Address IS NULL;
SELECT TOP (The SELECT TOP clause is used to specify the number of records to return.)	SELECT TOP number|percent column_name(s) FROM table_name WHERE condition;	SELECT TOP 50 PERCENT * FROM Customers;
MIN() (The MIN() function returns the smallest value of the selected column.)	SELECT MIN(column_name) FROM table_name WHERE condition;	SELECT MIN(Price) AS LargestPrice FROM Products;
MAX() (The MAX() function returns the largest value of the selected column.)	SELECT MAX(column_name) FROM table_name WHERE condition;	SELECT MAX(Price) AS LargestPrice FROM Products;
COUNT() (The COUNT() function returns the number of rows that matches a specified criterion.)	SELECT COUNT(column_name) FROM table_name WHERE condition;	SELECT COUNT(ProductID) FROM Products;
AVG() (The AVG() function returns the average value of a numeric column. )	SELECT AVG(column_name) FROM table_name WHERE condition;	SELECT AVG(Price) FROM Products;
SUM() (The SUM() function returns the total sum of a numeric column. )	SELECT SUM(column_name) FROM table_name WHERE condition;	SELECT SUM(Quantity) FROM OrderDetails;
LIKE Operator (The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.)	SELECT column1, column2, ... FROM table_name WHERE columnN LIKE pattern;	SELECT * FROM Customers WHERE CustomerName LIKE 'a%';
IN (The IN operator allows you to specify multiple values in a WHERE clause.)	SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...);	SELECT * FROM Customers WHERE Country IN ('Germany', 'France', 'UK');
BETWEEN (The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.)	SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;	SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;
Aliases (An alias is created with the AS keyword.)MYSQL aliases are used to give a table, or a column in a table, a temporary name.	SELECT column_name AS alias_name FROM table_name;	SELECT CustomerID AS ID, CustomerName AS Customer FROM Customers;
INNER JOIN (The INNER JOIN keyword selects records that have matching values in both tables.)	SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;	SELECT Orders.OrderID, Customers.CustomerName FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
LEFT JOIN (The LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2). The result is 0 records from the right side, if there is no match.)	SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;	SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY Customers.CustomerName;
RIGHT JOIN (The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records from the left table (table1). The result is 0 records from the left side, if there is no match.)	SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;	SELECT Orders.OrderID, Employees.LastName, Employees.FirstName FROM Orders RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID ORDER BY Orders.OrderID;
FULL OUTER JOIN (The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or right (table2) table records.)	SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name WHERE condition;	SELECT Customers.CustomerName, Orders.OrderID FROM Customers FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName;
Self Join (A self join is a regular join, but the table is joined with itself.)	SELECT column_name(s) FROM table1 T1, table1 T2 WHERE condition;	SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City FROM Customers A, Customers B WHERE A.CustomerID <> B.CustomerID AND A.City = B.City ORDER BY A.City;
UNION Operator (The UNION operator is used to combine the result-set of two or more SELECT statements.)	SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;	SELECT City FROM Customers UNION SELECT City FROM Suppliers ORDER BY City;
GROUP BY (The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country".)	SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s);	SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country;
HAVING Clause (The HAVING clause was added to MYSQL because the WHERE keyword cannot be used with aggregate functions.)	SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);	SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5;
EXISTS Operator (The EXISTS operator is used to test for the existence of any record in a subquery.)	SELECT column_name(s) FROM table_name WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);	SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);
ANY Operators (ANY means that the condition will be true if the operation is true for any of the values in the range.)	SELECT column_name(s) FROM table_name WHERE column_name operator ANY (SELECT column_name FROM table_name WHERE condition);	SELECT ProductName FROM Products WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);
ALL Operators (ALL means that the condition will be true only if the operation is true for all values in the range. )	SELECT column_name(s) FROM table_name WHERE column_name operator ALL (SELECT column_name FROM table_name WHERE condition);	SELECT ProductName FROM Products WHERE ProductID = ALL (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);
SELECT INTO (The SELECT INTO statement copies data from one table into a new table.)	SELECT * INTO newtable [IN externaldb] FROM oldtable WHERE condition;	SELECT * INTO CustomersBackup2017 FROM Customers;
INSERT INTO SELECT (The INSERT INTO SELECT statement copies data from one table and inserts it into another table.)	INSERT INTO table2 SELECT * FROM table1 WHERE condition;	INSERT INTO Customers (CustomerName, City, Country) SELECT SupplierName, City, Country FROM Suppliers;
CASE Statement (The CASE statement goes through conditions and returns a value when the first condition is met (like an if-then-else statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.)	CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN conditionN THEN resultN ELSE result END;	SELECT OrderID, Quantity, CASE WHEN Quantity > 30 THEN 'The quantity is greater than 30' WHEN Quantity = 30 THEN 'The quantity is 30' ELSE 'The quantity is under 30' END AS QuantityText FROM OrderDetails;
Stored Procedures (A stored procedure is a prepared MYSQL code that you can save, so the code can be reused over and over again.)	CREATE PROCEDURE procedure_name AS MYSQL_statement GO;	CREATE PROCEDURE SelectAllCustomers AS SELECT * FROM Customers GO; EXEC procedure_name;

1. CREATE DATABASE

The CREATE DATABASE statement is used to create a new MYSQL database.
CREATE DATABASE databasename;

2. DROP DATABASE

The DROP DATABASE statement is used to drop an existing MYSQL database.
DROP DATABASE databasename;

3. BACKUP DATABASE

The BACKUP DATABASE statement is used in MYSQL Server to create a full back up of an existing MYSQL database.
BACKUP DATABASE databasename TO DISK = 'filepath';

4. CREATE TABLE

The CREATE TABLE statement is used to create a new table in a database.
CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, .... );

5. DROP TABLE

The DROP TABLE statement is used to drop an existing table in a database.
DROP TABLE table_name;

6.TRUNCATE TABLE

The TRUNCATE TABLE statement is used to delete the data inside a table, but not the table itself.
TRUNCATE TABLE table_name;

7. ALTER TABLE

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
ALTER TABLE table_name ADD column_name datatype;

8. MYSQL Constraints

MYSQL constraints are used to specify rules for data in a table.
Constraints can be specified when the table is created with the CREATE TABLE statement, or after the table is created with the ALTER TABLE statement.
CREATE TABLE table_name ( column1 datatype constraint, column2 datatype constraint, column3 datatype constraint, .... );

The following constraints are commonly used in MYSQL:

• NOT NULL - Ensures that a column cannot have a NULL value
• UNIQUE - Ensures that all values in a column are different
• PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
• FOREIGN KEY - Prevents actions that would destroy links between tables
• CHECK - Ensures that the values in a column satisfies a specific condition
• DEFAULT - Sets a default value for a column if no value is specified
• CREATE INDEX - Used to create and retrieve data from the database very quickly

9. NOT NULL

By default, a column can hold NULL values, The NOT NULL constraint enforces a column to NOT accept NULL values.
CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255) NOT NULL, Age int );

10. UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column are different.
CREATE TABLE Persons ( ID int NOT NULL UNIQUE, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int );

11. PRIMARY KEY

The PRIMARY KEY constraint uniquely identifies each record in a table.
CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, PRIMARY KEY (ID) );

12. FOREIGN KEY

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.
CREATE TABLE Orders ( OrderID int NOT NULL PRIMARY KEY, OrderNumber int NOT NULL, PersonID int FOREIGN KEY REFERENCES Persons(PersonID) );

13. CHECK Constraint

The CHECK constraint is used to limit the value range that can be placed in a column.
CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int CHECK (Age>=18) );

14. DEFAULT Constraint

The DEFAULT constraint is used to set a default value for a column.
Syntax: CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, City varchar(255) DEFAULT 'Sandnes');

15. CREATE INDEX Statement

The CREATE INDEX statement is used to create indexes in tables.
Syntax: CREATE INDEX index_name ON table_name (column1, column2, ...);

16. AUTO INCREMENT Field

Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.
Syntax: CREATE TABLE Persons ( Personid int NOT NULL AUTO_INCREMENT, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, PRIMARY KEY (Personid) );

17. Views

In MYSQL, a view is a virtual table based on the result-set of an MYSQL statement.
Syntax: CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;