MySQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <center><b><u>MySQL Notes</u></b></center>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>MySQL</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. MySQL is a widely used relational database management system (RDBMS).\n",
    "2. MySQL is free and open-source.\n",
    "3. MySQL is ideal for both small and large applications.\n",
    "4. MySQL is very fast, reliable, scalable, and easy to use\n",
    "5. MySQL is cross-platform\n",
    "6. MySQL is compliant with the ANSI SQL standard\n",
    "7. MySQL was first released in 1995\n",
    "8. MySQL is developed, distributed, and supported by Oracle Corporation\n",
    "9. MySQL is named after co-founder Monty Widenius's daughter: My"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>Uses of MySQL</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. Huge websites like Facebook, Twitter, Airbnb, Booking.com, Uber, GitHub, YouTube, etc.\n",
    "2. Content Management Systems like WordPress, Drupal, Joomla!, Contao, etc.\n",
    "3. A very large number of web developers around the world"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>RDBMS</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. RDBMS stands for Relational Database Management System.\n",
    "2. RDBMS is a program used to maintain a relational database.\n",
    "3. RDBMS is the basis for all modern database systems such as MySQL, Microsoft SQL Server, Oracle, and Microsoft Access.\n",
    "4. RDBMS uses SQL queries to access the data in the database."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>Relational Database</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* A relational database defines database relationships in the form of tables. The tables are related to each other - based on data common to each."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>SQL</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. SQL is the standard language for dealing with Relational Databases.\n",
    "2. SQL is used to insert, search, update, and delete database records.\n",
    "3. SQL keywords are NOT case sensitive i.e. <mark style = 'color:#fff; background-color:#DC143C'>select</mark> is the same as <mark style = 'color:#fff; background-color:#DC143C'>SELECT</mark>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>SQL Commands</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* <u>SELECT</u> -> extracts data from a database\n",
    "* <u>UPDATE</u> -> updates data in a database\n",
    "* <u>DELETE</u> -> deletes data from a database\n",
    "* <u>INSERT INTO</u> -> inserts new data into a database\n",
    "* <u>CREATE DATABASE</u> -> creates a new database\n",
    "* <u>ALTER DATABASE</u> -> modifies a database\n",
    "* <u>CREATE TABLE</u> -> creates a new table\n",
    "* <u>ALTER TABLE</u> -> modifies a table\n",
    "* <u>DROP TABLE</u> -> deletes a table\n",
    "* <u>CREATE INDEX</u> -> creates an index (search key)\n",
    "* <u>DROP INDEX</u> -> deletes an index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>Create and Drop Database</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# CREATE DATABASE: \"To create a new SQL database\"\n",
    "        CREATE DATABASE databasename;\n",
    "\n",
    "# DROP DATABASE: \"To drop an existing SQL database\"\n",
    "        DROP DATABASE databasename;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>Create and Drop Table</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create table Syntax: \"To create a new table in a database\"\n",
    "        CREATE TABLE table_name (\n",
    "        column1 datatype,\n",
    "        column2 datatype,\n",
    "        column3 datatype,\n",
    "        ....\n",
    "        );\n",
    "# To create a new table using an existing table:\n",
    "        CREATE TABLE new_table_name AS\n",
    "        SELECT column1, column2,...\n",
    "        FROM existing_table_name\n",
    "        WHERE ....; // Optional"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Drop table Syntax: \"To drop an existing table in a database\"\n",
    "        DROP TABLE table_name;\n",
    "# To delete the data inside a table, but not the table itself:\n",
    "        TRUNCATE TABLE table_name;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>INSERT INTO</b>\n",
    "* The INSERT INTO statement is used to insert new records in a table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1. When Specify both the column names and the values:\n",
    "        INSERT INTO table_name (column1,column2,column3, ...)\n",
    "        VALUES (value1, value2, value3, ...);\n",
    "# 2. When adding the values into all columns:\n",
    "        INSERT INTO table_name\n",
    "        VALUES (value1, value2, value3, ...);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <center><b><u>Fetch Data From Database</u></b></center>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>SELECT</b>\n",
    "* To select data from a database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1. When select data from any Column\n",
    "        SELECT column1, column2, ...FROM table_name;\n",
    "# 2. When select all the Columns\n",
    "        SELECT * FROM table_name;\n",
    "# 3. To return only distinct (different) values\n",
    "        SELECT DISTINCT columnname FROM table_name\n",
    "# 4. When we want to count the columndata        \n",
    "        SELECT COUNT(DISTINCT columnname) FROM table_name"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>WHERE</b>\n",
    "* used to filter records"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "        SELECT column1, column2, ...\n",
    "        FROM table_name\n",
    "        WHERE condition;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Operators in Where\n",
    "* '=' -> Equal\t\n",
    "* '>' -> Greater than\t\n",
    "* '<' -> Less than\t\n",
    "* '>=' -> Greater than or equal\t\n",
    "* '<=' -> Less than or equal\t\n",
    "* '<>' -> Not equal. Note: In some versions of SQL this operator may be written as '!='\n",
    "* 'BETWEEN' -> Between a certain range\t\n",
    "* 'LIKE' -> Search for a pattern\t\n",
    "* 'IN' -> To specify multiple possible values for a column"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>Where with AND,OR, and NOT operators:</b>\n",
    "# AND Syntax: \"If all the conditions separated by AND are TRUE\"\n",
    "        SELECT column1, column2, ...\n",
    "        FROM table_name\n",
    "        WHERE condition1 AND condition2 AND condition3 ...;\n",
    "# OR Syntax: \"If any of the conditions separated by OR is TRUE\"\n",
    "        SELECT column1, column2, ...\n",
    "        FROM table_name\n",
    "        WHERE condition1 OR condition2 Ocondition3 ...;\n",
    "# NOT Syntax: \"If the condition(s) is NOT TRUE\"\n",
    "        SELECT column1, column2, ...\n",
    "        FROM table_name\n",
    "        WHERE NOT condition;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>ORDER BY</b>\n",
    "* used to sort the result-set in ascending or descending order"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Order By\n",
    "        SELECT column1, column2, ...\n",
    "        FROM table_name\n",
    "        ORDER BY column1, column2, ... ASC|DESC;\n",
    "# Order By several columns\n",
    "        SELECT * FROM table_name\n",
    "        ORDER BY column1 ASC|DESC, column2 ASC|DESC, ...;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>NULL Values</b>\n",
    "* NULL value is a field with no value\n",
    "* A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Is Null\n",
    "        SELECT column_names\n",
    "        FROM table_name\n",
    "        WHERE column_name IS NULL;\n",
    "# Is Not Null\n",
    "        SELECT column_names\n",
    "        FROM table_name\n",
    "        WHERE column_name IS NOT NULL;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>UPDATE</b>\n",
    "* used to modify the existing records in a table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Update record\n",
    "        UPDATE table_name\n",
    "        SET column1 = value1, column2 = value2, ...\n",
    "        WHERE condition;\n",
    "# Update Multiple record\n",
    "        UPDATE table_name\n",
    "        SET columnname = condition\n",
    "        WHERE columnname = condition;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>Limit, Min(), Max(), Count(), Avg(), and Sum()</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Limit: \"To specify the number of records to return\"\n",
    "        SELECT column_name(s)\n",
    "        FROM table_name\n",
    "        WHERE condition\n",
    "        LIMIT number;\n",
    "# Min(): \"It returns the smallest value of the selected column\"\n",
    "        SELECT MIN(column_name)\n",
    "        FROM table_name\n",
    "        WHERE condition;\n",
    "# Max(): \"It returns the largest value of the selected column\"\n",
    "      SELECT MAX(column_name)\n",
    "      FROM table_name\n",
    "      WHERE condition;\n",
    "# Count(): \"It returns the number of rows that matches a specified criterion\"\n",
    "      SELECT COUNT(column_name)\n",
    "      FROM table_name\n",
    "      WHERE condition;\n",
    "# Avg(): \"It returns the average value of a numeric column\"\n",
    "      SELECT AVG(column_name)\n",
    "      FROM table_name\n",
    "      WHERE condition;\n",
    "# Sum(): \"It returns the total sum of a numeric column\"\n",
    "      SELECT SUM(column_name)\n",
    "      FROM table_name\n",
    "      WHERE condition;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>LIKE</b>\n",
    "* used in a WHERE clause to search for a specified pattern in a column\n",
    "\n",
    "There are two wildcards often used in conjunction with the LIKE operator:\n",
    "\n",
    "* The percent sign <mark style = 'color:#fff; background-color:#DC143C'>(%)</mark> represents zero, one, or multiple characters\n",
    "* The underscore sign <mark style = 'color:#fff; background-color:#DC143C'>(_)</mark> represents one, single character\n",
    "\n",
    "<center>\n",
    "<table>\n",
    "\n",
    "<tr style = 'color:#fff; background-color:#DC143C'>\n",
    "<th>LIKE Operator</th>\n",
    "<th>Description</th>\n",
    "</tr>\n",
    "\n",
    "<tr>\n",
    "<td>WHERE CustomerName LIKE 'a%'</td>\n",
    "<td>Finds any values that start with \"a\"</td>\n",
    "</tr>\n",
    "\n",
    "<tr>\n",
    "<td>WHERE CustomerName LIKE '%a'</td>\n",
    "<td>Finds any values that end with \"a\"</td>\n",
    "</tr>\n",
    "\n",
    "<tr>\n",
    "<td>WHERE CustomerName LIKE '%or%'</td>\n",
    "<td>Finds any values that have \"or\" in any position</td>\n",
    "</tr>\n",
    "\n",
    "<tr>\n",
    "<td>WHERE CustomerName LIKE '_r%'</td>\n",
    "<td>Finds any values that have \"r\" in the second position</td>\n",
    "</tr>\n",
    "\n",
    "<tr>\n",
    "<td>WHERE CustomerName LIKE 'a_%'</td>\n",
    "<td>Finds any values that start with \"a\" and are at least 2 characters in length</td>\n",
    "</tr>\n",
    "\n",
    "<tr>\n",
    "<td>WHERE CustomerName LIKE 'a__%'</td>\n",
    "<td>Finds any values that start with \"a\" and are at least 3 characters in length</td>\n",
    "</tr>\n",
    "\n",
    "<tr>\n",
    "<td>WHERE CustomerName LIKE 'a_%_%'</td>\n",
    "<td>Finds any values that starts with \"a\" and are at least 3 characters in length</td>\n",
    "</tr>\n",
    "\n",
    "<tr>\n",
    "<td>WHERE CustomerName LIKE 'a%o'</td>\n",
    "<td>Finds any values that start with \"a\" and ends with \"o\"</td>\n",
    "</tr>\n",
    "\n",
    "</table>\n",
    "</center>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Like\n",
    "        SELECT column1, column2, ...\n",
    "        FROM table_name\n",
    "        WHERE columnN LIKE pattern;\n",
    "# Not Like\n",
    "        SELECT column1, column2, ...\n",
    "        FROM table_name\n",
    "        WHERE columnN NOT LIKE pattern;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>IN</b>\n",
    "* To specify multiple values in a WHERE clause\n",
    "* It's a shorthand for multiple OR conditions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# In:\n",
    "        SELECT column_name(s)\n",
    "        FROM table_name\n",
    "        WHERE column_name IN (value1, value2, ...);\n",
    "# Not In:\n",
    "        SELECT column_name(s)\n",
    "        FROM table_name\n",
    "        WHERE column_name NOT IN (value1, value2, ...);\n",
    "<u>OR</u>\n",
    "# In:\n",
    "        SELECT column_name(s)\n",
    "        FROM table_name\n",
    "        WHERE column_name IN (SELECT STATEMENT);\n",
    "# Not In:\n",
    "        SELECT column_name(s)\n",
    "        FROM table_name\n",
    "        WHERE column_name NOT IN (SELECT STATEMENT);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>BETWEEN</b>\n",
    "* It selects values within a given range\n",
    "* The values can be numbers, text, or dates\n",
    "* begin and end values are included"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Between\n",
    "        SELECT column_name(s)\n",
    "        FROM table_name\n",
    "        WHERE column_name BETWEEN value1 AND value2;\n",
    "# Not Between\n",
    "        SELECT column_name(s)\n",
    "        FROM table_name\n",
    "        WHERE column_name NOT BETWEEN value1 AND value2;\n",
    "# Between with IN\n",
    "        SELECT column_name(s)\n",
    "        FROM table_name\n",
    "        WHERE column_name BETWEEN value1 AND value2 And column_name NOT IN (value3,value4,...);\n",
    "# Between Text\n",
    "        SELECT column_name(s)\n",
    "        FROM table_name\n",
    "        WHERE column_name BETWEEN textvalue1 AND textvalue2;\n",
    "# Not Between Text\n",
    "        SELECT column_name(s)\n",
    "        FROM table_name\n",
    "        WHERE column_name NOT BETWEEN textvalue1 AND textvalue2;\n",
    "# Between Dates\n",
    "        SELECT column_name(s)\n",
    "        FROM table_name\n",
    "        WHERE column_name BETWEEN date1 AND date2;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>Aliases</b>\n",
    "* used to give a table, or a column in a table, a temporary name\n",
    "* used to make column names more readable\n",
    "* only exists for the duration of that query\n",
    "* created with the <mark style = 'color:#fff; background-color:#DC143C'>AS</mark> keyword"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Alisas Column\n",
    "        SELECT column_name AS alias_name\n",
    "        FROM table_name;\n",
    "# Alisas Table\n",
    "        SELECT column_name(s)\n",
    "        FROM table_name AS alias_name;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>Joins</b>\n",
    "* used to combine rows from two or more tables, based on a related column between them\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Types of Joins\n",
    "* <u>INNER JOIN</u>: Returns records that have matching values in both tables\n",
    "* <u>LEFT JOIN</u>: Returns all records from the left table, and the matched records from the right table\n",
    "* <u>RIGHT JOIN</u>: Returns all records from the right table, and the matched records from the left table\n",
    "* <u>CROSS JOIN</u>: Returns all records from both tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Inner Join: \"Selects records that have matching values in both tables\"\n",
    "        SELECT column_name(s)\n",
    "        FROM table1\n",
    "        INNER JOIN table2\n",
    "        ON table1.column_name = table2.column_name;\n",
    "# LEFT JOIN: \"Returns all records from the left table1, and the matching records (if any) from the right table2\"\n",
    "        SELECT column_name(s)\n",
    "        FROM table1\n",
    "        LEFT JOIN table2\n",
    "        ON table1.column_name = table2.column_name;\n",
    "# RIGHT JOIN: \"Returns all records from the right table2, and the matching records (if any) from the left table1\"\n",
    "        SELECT column_name(s)\n",
    "        FROM table1\n",
    "        RIGHT JOIN table2\n",
    "        ON table1.column_name = table2.column_name;\n",
    "# CROSS JOIN: \"Returns all records from both tables\"\n",
    "        SELECT column_name(s)\n",
    "        FROM table1\n",
    "        CROSS JOIN table2;\n",
    "# Self Join: \"Table is joined with itself\"\n",
    "        SELECT column_name(s)\n",
    "        FROM table1 T1, table1 T2\n",
    "        WHERE condition;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>UNION</b>\n",
    "* used to combine the result-set of two or more SELECT statements\n",
    "* Every SELECT statement within UNION must have the same number of columns\n",
    "* The columns must also have similar data types\n",
    "* The columns in every SELECT statement must also be in the same order"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Union:\n",
    "        SELECT column_name(s) FROM table1\n",
    "        UNION\n",
    "        SELECT column_name(s) FROM table2;\n",
    "        ORDER BY columan_name; //Optional\n",
    "# Union All:\n",
    "        SELECT column_name(s) FROM table1\n",
    "        UNION ALL\n",
    "        SELECT column_name(s) FROM table2\n",
    "        ORDER BY columan_name; //Optional\n",
    "# Union With Where:\n",
    "        SELECT column_name(s) FROM table1\n",
    "        WHERE column_name = 'value1'\n",
    "        UNION\n",
    "        SELECT column_name(s) FROM table2\n",
    "        WHERE column_name = 'value1'\n",
    "        ORDER BY columan_name; //Optional\n",
    "# Union All With Where:\n",
    "        SELECT column_name(s) FROM table1\n",
    "        WHERE column_name = 'value1'\n",
    "        UNION All\n",
    "        SELECT column_name(s) FROM table2\n",
    "        WHERE column_name = 'value1'\n",
    "        ORDER BY columan_name; //Optional"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>GROUP BY</b>\n",
    "* The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "        SELECT column_name(s)\n",
    "        FROM table_name\n",
    "        WHERE condition\n",
    "        GROUP BY column_name(s)\n",
    "        ORDER BY column_name(s);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>HAVING</b>\n",
    "* The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "        SELECT column_name(s)\n",
    "        FROM table_name\n",
    "        WHERE condition\n",
    "        GROUP BY column_name(s)\n",
    "        HAVING condition\n",
    "        ORDER BY column_name(s);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>EXISTS</b>\n",
    "* Used to test for the existence of any record in a subquery\n",
    "* Returns TRUE if the subquery returns one or more records"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "        SELECT column_name(s)\n",
    "        FROM table_name\n",
    "        WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>ANY and ALL</b>\n",
    "* The ANY and ALL operators allow you to perform a comparison between a single column value and a range of other values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Any:\n",
    "* Returns a boolean value as a result\n",
    "* ANY means that the condition will be true if the operation is true for any of the values in the range."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "        SELECT column_name(s)\n",
    "        FROM table_name\n",
    "        WHERE column_name operator ANY (SELECT column_name FROM table_name WHERE condition);\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# All:\n",
    "* Returns a boolean value as a result\n",
    "* Returns TRUE if ALL of the subquery values meet the condition"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ALL Syntax With SELECT:\n",
    "        SELECT ALL column_name(s)\n",
    "        FROM table_name\n",
    "        WHERE condition;\n",
    "# ALL Syntax With WHERE or HAVING:\n",
    "        SELECT column_name(s)\n",
    "        FROM table_name\n",
    "        WHERE column_name operator ALL (SELECT column_name FROM table_name WHERE condition);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>CASE</b>\n",
    "* 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.\n",
    "* If there is no ELSE part and no conditions are true, it returns NULL."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "        SELECT column1, colummn2,\n",
    "        CASE\n",
    "            WHEN condition1 THEN result1\n",
    "            WHEN condition2 THEN result2\n",
    "            WHEN conditionN THEN resultN\n",
    "            ELSE result\n",
    "        END;\n",
    "        FROM Database_name;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <center><b><u>MySQL Operators</u></b></center>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Arithmetic Operators\n",
    "* '+' ->\tAdd\t\n",
    "* '-' ->\tSubtract\t\n",
    "* '*' ->\tMultiply\t\n",
    "* '/' ->\tDivide\t\n",
    "* '%' ->\tModulo"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Bitwise Operators\n",
    "* '&'\tBitwise AND\n",
    "* '|'\tBitwise OR\n",
    "* '^'\tBitwise exclusive OR"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Comparison Operators\n",
    "* '=' ->\tEqual to\t\n",
    "* '>' ->\tGreater than\t\n",
    "* '<' ->\tLess than\t\n",
    "* '>=' ->\tGreater than or equal to\t\n",
    "* '<=' ->\tLess than or equal to\t\n",
    "* '<>' ->\tNot equal to"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Compound Operators\n",
    "* '+=' ->\tAdd equals\n",
    "* '-=' ->\tSubtract equals\n",
    "* '*=' ->\tMultiply equals\n",
    "* '/=' ->\tDivide equals\n",
    "* '%=' ->\tModulo equals\n",
    "* '&=' ->\tBitwise AND equals\n",
    "* '^-=' ->\tBitwise exclusive equals\n",
    "* '|*=' ->\tBitwise OR equals"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Logical Operators\n",
    "* 'ALL' ->\tTRUE if all of the subquery values meet the condition\t\n",
    "* 'AND' ->\tTRUE if all the conditions separated by AND is TRUE\t\n",
    "* 'ANY' ->\tTRUE if any of the subquery values meet the condition\t\n",
    "* 'BETWEEN' ->\tTRUE if the operand is within the range of comparisons\t\n",
    "* 'EXISTS' ->\tTRUE if the subquery returns one or more records\t\n",
    "* 'IN' ->\tTRUE if the operand is equal to one of a list of expressions\t\n",
    "* 'LIKE' ->\tTRUE if the operand matches a pattern\t\n",
    "* 'NOT' ->\tDisplays a record if the condition(s) is NOT TRUE\t\n",
    "* 'OR' ->\tTRUE if any of the conditions separated by OR is TRUE\t\n",
    "* 'SOME' ->\tTRUE if any of the subquery values meet the condition"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>ALTER TABLE</b>\n",
    "* used to add, delete, or modify columns in an existing table\n",
    "* And used to add and drop various constraints on an existing table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1. ADD Column: \"To add a column in a table\"\n",
    "        ALTER TABLE table_name\n",
    "        ADD column_name datatype;\n",
    "# 2. DROP COLUMN: \"To delete a column in a table\"\n",
    "        ALTER TABLE table_name\n",
    "        DROP COLUMN column_name;\n",
    "# 3. MODIFY COLUMN: \"To change the data type of a column in a table\"\n",
    "        ALTER TABLE table_name\n",
    "        MODIFY COLUMN column_name datatype;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>Constraints</b>\n",
    "* used to specify rules for data in a table\n",
    "* used to limit the type of data that can go into a table\n",
    "\n",
    "The following constraints are commonly used in SQL:\n",
    "* <u>NOT NULL</u> - Ensures that a column cannot have a NULL value\n",
    "* <u>UNIQUE</u> - Ensures that all values in a column are different\n",
    "* <u>PRIMARY KEY</u> - A combination of a <mark style = 'color:#fff; background-color:#DC143C'>NOT NULL</mark> and <mark style = 'color:#fff; background-color:#DC143C'>UNIQUE</mark>. Uniquely identifies each row in a table\n",
    "* <u>FOREIGN KEY</u> - Prevents actions that would destroy links between tables\n",
    "* <u>CHECK</u> - Ensures that the values in a column satisfies a specific condition\n",
    "* <u>DEFAULT</u> - Sets a default value for a column if no value is specified\n",
    "* <u>CREATE INDEX</u> - Used to create and retrieve data from the database very quickly"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<u>Syntax</u>:\n",
    "\n",
    "        CREATE TABLE table_name (\n",
    "            column1 datatype constraint,\n",
    "            column2 datatype constraint,\n",
    "            column3 datatype constraint,\n",
    "            ....\n",
    "        );"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>NOT NULL</b>\n",
    "# NOT NULL on CREATE TABLE\n",
    "        CREATE TABLE Persons (\n",
    "            ID int NOT NULL,\n",
    "            LastName varchar(255) NOT NULL,\n",
    "            FirstName varchar(255) NOT NULL,\n",
    "            Age int\n",
    "        );\n",
    "# NOT NULL on ALTER TABLE\n",
    "        ALTER TABLE Persons\n",
    "        MODIFY Age int NOT NULL;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>UNIQUE</b>\n",
    "# UNIQUE Constraint on CREATE TABLE\n",
    "        CREATE TABLE Persons (\n",
    "        ID int NOT NULL,\n",
    "        LastName varchar(255) NOT NULL,\n",
    "        FirstName varchar(255),\n",
    "        Age int,\n",
    "        UNIQUE (ID)\n",
    "        );\n",
    "# UNIQUE Constraint on ALTER TABLE\n",
    "        ALTER TABLE Persons\n",
    "        ADD UNIQUE (ID);\n",
    "# DROP a UNIQUE Constraint\n",
    "        ALTER TABLE Persons\n",
    "        DROP INDEX UC_Person;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>PRIMARY KEY</b>\n",
    "# PRIMARY KEY on CREATE TABLE\n",
    "        CREATE TABLE Persons (\n",
    "            ID int NOT NULL,\n",
    "            LastName varchar(255) NOT NULL,\n",
    "            FirstName varchar(255),\n",
    "            Age int,\n",
    "            PRIMARY KEY (ID)\n",
    "        );\n",
    "# PRIMARY KEY on ALTER TABLE\n",
    "        ALTER TABLE Persons\n",
    "        ADD PRIMARY KEY (ID);\n",
    "# DROP a PRIMARY KEY Constraint\n",
    "        ALTER TABLE Persons\n",
    "        DROP PRIMARY KEY;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>FOREIGN KEY</b>\n",
    "# FOREIGN KEY on CREATE TABLE\n",
    "        CREATE TABLE Orders (\n",
    "            OrderID int NOT NULL,\n",
    "            OrderNumber int NOT NULL,\n",
    "            PersonID int,\n",
    "            PRIMARY KEY (OrderID),\n",
    "            FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)\n",
    "        );\n",
    "# FOREIGN KEY on ALTER TABLE\n",
    "        ALTER TABLE Orders\n",
    "        ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);\n",
    "# DROP a FOREIGN KEY Constraint\n",
    "        ALTER TABLE Orders\n",
    "        DROP FOREIGN KEY FK_PersonOrder;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>CHECK</b>\n",
    "# CHECK on CREATE TABLE\n",
    "        CREATE TABLE Persons (\n",
    "            ID int NOT NULL,\n",
    "            LastName varchar(255) NOT NULL,\n",
    "            FirstName varchar(255),\n",
    "            Age int,\n",
    "            CHECK (Age>=18)\n",
    "        );\n",
    "# CHECK on ALTER TABLE\n",
    "        ALTER TABLE Persons\n",
    "        ADD CHECK (Age>=18);\n",
    "# DROP a CHECK Constraint\n",
    "        ALTER TABLE Persons\n",
    "        DROP CHECK CHK_PersonAge;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>DEFAULT</b>\n",
    "# DEFAULT on CREATE TABLE\n",
    "        CREATE TABLE Persons (\n",
    "            ID int NOT NULL,\n",
    "            LastName varchar(255) NOT NULL,\n",
    "            FirstName varchar(255),\n",
    "            Age int,\n",
    "            City varchar(255) DEFAULT 'Sandnes'\n",
    "        );\n",
    "# DEFAULT on ALTER TABLE\n",
    "        ALTER TABLE Persons\n",
    "        ALTER City SET DEFAULT 'Sandnes';\n",
    "# DROP a DEFAULT Constraint\n",
    "        ALTER TABLE Persons\n",
    "        ALTER City DROP DEFAULT;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>CREATE INDEX</b>\n",
    "# CREATE INDEX\n",
    "        CREATE INDEX index_name\n",
    "        ON table_name (column1, column2, ...);\n",
    "# CREATE UNIQUE INDEX\n",
    "        CREATE UNIQUE INDEX index_name\n",
    "        ON table_name (column1, column2, ...);\n",
    "# DROP INDEX\n",
    "        ALTER TABLE table_name\n",
    "        DROP INDEX index_name;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>AUTO INCREMENT</b>\n",
    "* Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.\n",
    "\n",
    "* Often this is the primary key field that we would like to be created automatically every time a new record is inserted."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "        CREATE TABLE Persons (\n",
    "            Personid int NOT NULL AUTO_INCREMENT,\n",
    "            LastName varchar(255) NOT NULL,\n",
    "            FirstName varchar(255),\n",
    "            Age int,\n",
    "            PRIMARY KEY (Personid)\n",
    "        );"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>Working With Dates</b>\n",
    "# MySQL Date Data Types\n",
    "MySQL comes with the following data types for storing a date or a date/time value in the database:\n",
    "\n",
    "* <u>DATE</u> -> format YYYY-MM-DD\n",
    "* <u>DATETIME</u> -> format: YYYY-MM-DD HH:MI:SS\n",
    "* <u>TIMESTAMP</u> -> format: YYYY-MM-DD HH:MI:SS\n",
    "* <u>YEAR</u> -> format YYYY or YY"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "        SELECT * FROM Orders WHERE OrderDate='2008-11-11'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>CREATE VIEW</b>\n",
    "* A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Creating a View\n",
    "        CREATE VIEW view_name AS\n",
    "        SELECT column1, column2, ...\n",
    "        FROM table_name\n",
    "        WHERE condition;\n",
    "# Updating a View\n",
    "        CREATE OR REPLACE VIEW view_name AS\n",
    "        SELECT column1, column2, ...\n",
    "        FROM table_name\n",
    "        WHERE condition;\n",
    "# Dropping a View\n",
    "        DROP VIEW view_name;"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "26f01626838fe02340c49408e611549b375a01d3f259fee8fe5c369c3e4c769b"
  },
  "kernelspec": {
   "display_name": "Python 3.9.6 64-bit",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.6"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}