MySQL.txt

MySQL
---------------------------------------------------------
*  Open source Relational SQL Dabase Management Systems. - Relaion means we save data in tables.
* Developed, Marketed and supported by MySQL AB, a Swedish company.
* It is very fast, commonly used with PHP. It supports may OS with many languages like PHP, PERL, Python, C, C++, JAVA
* It supports large databases.

* Three ways of working with PHP and MySQL:

MySQLi (object-oriented)
MySQLi (procedural)
PDO = PHP Data Objects


MySQLi (object-oriented):
------------------------------------------
<?php
$servername = "localhost";
$username = "username";
$password = "password";

// Create connection
$conn = new mysqli($servername, $username, $password);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
echo "Connected successfully";
?>


MySQLi (procedural):
------------------------------------------
<?php
$servername = "localhost";
$username = "username";
$password = "password";

// Create connection
$conn = mysqli_connect($servername, $username, $password);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>

* Some of The Most Important 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

* The SELECT DISTINCT statement is used to return only distinct (different) values.
SELECT COUNT(DISTINCT Country) FROM Customers;

* The WHERE clause is used to filter records.
SELECT * FROM Customers WHERE Country='Mexico';

* The ORDER BY keyword is used to sort the result-set in ascending or descending order.

* A field with a NULL value is a field with no value.
We will have to use the IS NULL and IS NOT NULL operators instead.
SELECT column_names FROM table_name WHERE column_name IS NOT NULL;

* The UPDATE statement is used to modify the existing records in a table.
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

* The DELETE statement is used to delete existing records in a table.
DELETE FROM table_name WHERE condition;

* The MIN() function returns the smallest value of the selected column.
The MAX() function returns the largest value of the selected column.
SELECT MAX(column_name) FROM table_name WHERE condition;

* The COUNT() function returns the number of rows that matches a specified criteria.
SELECT COUNT(column_name) FROM table_name WHERE condition;

* The AVG() function returns the average value of a numeric column.
SELECT AVG(Price) FROM Products;

* The SUM() function returns the total sum of a numeric column.
SELECT SUM(Quantity) FROM OrderDetails;

* The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
There are two wildcards used in conjunction with the LIKE operator:

% - The percent sign represents zero, one, or multiple characters
_ - The underscore represents a single character
WHERE CustomerName LIKE 'a%'	Finds any values that start with "a"
WHERE CustomerName LIKE '%a'	Finds any values that end with "a"
WHERE CustomerName LIKE '%or%'	Finds any values that have "or" in any position
WHERE CustomerName LIKE '_r%'	Finds any values that have "r" in the second position
WHERE CustomerName LIKE 'a_%_%'	Finds any values that start with "a" and are at least 3 characters in length
WHERE ContactName LIKE 'a%o'	Finds any values that start with "a" and ends with "o"

The following SQL statement selects all customers with a City starting with "b", "s", or "p":
SELECT * FROM Customers WHERE City LIKE '[bsp]%';

* The following SQL statement selects all customers with a City starting with "a", "b", or "c":
SELECT * FROM Customers WHERE City LIKE '[a-c]%';

* 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, ...);

* The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.
The BETWEEN operator is inclusive: begin and end values are included. 
SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;

* SQL aliases are used to give a table, or a column in a table, a temporary name.
The following SQL statement creates two aliases, one for the CustomerName column and one for the ContactName column. Note: It requires double quotation marks or square brackets if the alias name contains spaces:
SELECT CustomerName AS Customer, ContactName AS [Contact Person] FROM Customers;

The following SQL statement creates an alias named "Address" that combine four columns (Address, PostalCode, City and Country):
SELECT CustomerName, CONCAT(Address,', ',PostalCode,', ',City,', ',Country) AS Address FROM Customers;

* A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
 Here are the different types of the JOINs in SQL:

(INNER) JOIN: Returns records that have matching values in both tables
LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table
RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table
FULL (OUTER) JOIN: Return all records when there is a match in either left or right table

* The UNION operator is used to combine the result-set of two or more SELECT statements.
Each SELECT statement within UNION must have the same number of columns
The columns must also have similar data types
The columns in each SELECT statement must also be in the same order

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

* The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL:
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

* The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5;

* Single line comments start with --.
Any text between -- and the end of the line will be ignored (will not be executed).
The following example uses a single-line comment as an explanation:

* The following constraints are commonly used in SQL:

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 - Uniquely identifies a row/record in another table
CHECK - Ensures that all values in a column satisfies a specific condition
DEFAULT - Sets a default value for a column when no value is specified
INDEX - Used to create and retrieve data from the database very quickly

* SQL Injection
SQL injection is a code injection technique that might destroy your database.
SQL injection is one of the most common web hacking techniques.
SQL injection is the placement of malicious code in SQL statements, via web page input.

* 









Multiple rows insertion:
-------------------------------------------------------
* https://stackoverflow.com/questions/8899802/how-do-i-do-a-bulk-insert-in-mysql-using-node-js







************************************* Useful Links *********************************************
* https://www.jumpingbean.co.za/blog/mysql-if-exists-update-else-insert
* https://dba.stackexchange.com/questions/99120/if-exists-then-update-else-insert/99126
* 

















For Interview
----------------------
* Diff B/W MySQL and MySQLi
* Prepared statements
* SQL Injection
* Primary Key
* There is a hard limit of 4096 columns per table
* MySQL engines
* Slow query log

Primary key cannot have a NULL value.
Each table can have only one primary key.
Primary key can be related to another tables as a Foreign Key.
We can generate ID automatically with the help of Auto Increment field. Primary key supports Auto Increment value.
We can't delete primary key value from the parent table which is used as a foreign key in child table. To delete, we first need to delete that primary key value from the child table.

* Unique Key

Unique Constraint may have a NULL value.
Each table can have more than one Unique Constraint.
By default, Unique key is a unique non-clustered index.
Unique Constraint can not be related with another table's as a Foreign Key.

* 

* https://www.youtube.com/watch?v=uAWWhEA57bE
* 





manoz@17