My Sql - D B M S

• Theory
• DDL
• DRL
• DML
• JOIN
• SET
• SUBQUERIES

Theory

• MySQL is open-source RDBMS, and it uses SQL for all CRUD operations
• MySQL Query are not Case Sensitive
• Install MySQL (Community server & Workbench)
  • https://dev.mysql.com/downloads/windows/installer/8.0.html > Download & Install
  • Select Default Options > Give Password
  • Add User > Write username and password > next
• Ways to write commands
  • MySQL CLI Commands
  • MySQL Workbench
• Connect to a Server
  • Click Database > Connect to Database
  • Click Store in Vault > Enter your Password > Click Ok
• MySQL VIEWS
  • A view is a database object that has no values, Its contents are based on the base table
    • It contains rows and columns similar to the real table
  • In MySQL, the View is a virtual table created by a query by joining one or more tables
    • It is operated similarly to the base table but does not contain any data of its own
  • Views are definitions built on top of other tables (or views)
    • If any changes occur in the underlying table, the same changes reflected in the View also
  • CREATE VIEW view_name AS SELECT columns FROM tables [WHERE conditions];
  • ALTER VIEW view_name AS SELECT columns FROM table WHERE conditions;
  • DROP VIEW IF EXISTS view_name;
  • View using Join clause

      CREATE VIEW Trainer AS SELECT c.course_name, c.trainer, t.email
      FROM courses c, contact t WHERE c.id = t.id;
    

• Comments
  • -- statement & # statement
• DUAL Tables
  • Dual tables are dummy tables created by MySQL, help users to do certain obvious actions without referring to user defined tables
  • SELECT 55 + 11;
  • SELECT now();
  • SELECT curdate();
  • SELECT ucase();
• Keywords
  • DESC tableName => Describes the table
  • UPPER(colName) => Turns into uppercase
  • LTRIM(colName) => Trims white spaces from left
  • RTRIM(colName) => Trims white spaces from right
  • LENGTH(colName) => Returns length of the string
  • INSTR(colName, 'C') => Insert a character in the string at given position
  • substring(colName, N, M) => Returns substring
  • REPLACE(colName, 'C1', 'C2') => Replaces from string
  • CONCAT(colName1, " ", colName2) => Joins strings
  • LIMIT
    • SELECT * FROM tableName LIMIT 5 => Limits result by n numbers
    • LIMIT n1 n2 => Limits result by n2 numbers after an offset (skip) of n1 numbers

DDL

• MANAGING DB (Creation of DB)
  • CREATE DATABASE IF NOT EXISTS dbName; => Creates Database
  • USE dbName; => Selects database, Switch database
  • DROP DATABASE IF EXISTS dbName; => Dropping database
  • SHOW DATABASES; => List all the DBs in the server
  • SHOW TABLES; => List tables in the selected DB
  • Create

      CREATE TABLE tableName1
      (
        colName1 dataType(n),
        colName2 dataType(n),
        PRIMARY KEY(colName)
      )
    

  • Copy table2 into table1, Some attributes are ignored while copying

      CREATE TABLE tableName1 AS
      SELECT * FROM tableName2
    

• Primary Key
  • PK is not null, unique and only one per table
• Foreign Key
  • FK refers to PK of other table
  • Each relation can having any number of FK

      CREATE TABLE ORDER (
      id INT PRIMARY KEY,
      delivery_date DATE,
      order_placed_date DATE,
      cust_id INT,
      FOREIGN KEY (cust_id) REFERENCES customer(id)
      );
    

• UNIQUE
  • Can be null, table can have multiple unique attributes
  • CREATE TABLE customer (email VARCHAR(1024) UNIQUE);
• CHECK
  • CREATE TABLE customer (CONSTRAINT age_check CHECK (age > 12));
• DEFAULT
  • Set default value of the column
  • CREATE TABLE account (saving-rate DOUBLE NOT NULL DEFAULT 4.25);
  • An attribute can be PK and FK both in a table
• ALTER OPERATIONS
  • Changes schema
  • ADD
    • Add new column
    • ALTER TABLE tableName ADD newColName datatype ADD newColName2 datatype;
    • ALTER TABLE customer ADD age INT NOT NULL;
  • MODIFY
    • Change datatype of an attribute
    • ALTER TABLE table-name MODIFY col-name col-datatype;
    • VARCHAR TO CHAR
      • ALTER TABLE customer MODIFY name CHAR(1024);
  • CHANGE COLUMN
    • Rename column name
    • ALTER TABLE tableName CHANGE COLUMN oldColName newColName new-col-datatype;
    • ALTER TABLE customer CHANGE COLUMN name customer-name VARCHAR(1024);
  • DROP COLUMN
    • Drop a column completely
    • ALTER TABLE tableName DROP COLUMN colName;
    • ALTER TABLE customer DROP COLUMN middle-name;
  • RENAME
    • Rename table name itself
    • ALTER TABLE tableName RENAME TO newTableName;
    • ALTER TABLE customer RENAME TO customer-details;

DRL

• SELECT colName1, colName2 FROM tableName;
  • Order of execution from RIGHT to LEFT
  • SELECT * => Select all Columns
  • SELECT colName1, colName2 => Select given Columns in given Order
  • SELECT colName1 + n => Perform Arithematic operation on a Column, Use brackets when taking care of order
  • FROM tableName => Select a Table after selecting a Database
  • FROM databaseName.tableName => Select a Table and Database together
  • Alias in MySQL (AS)
    • Aliases in MySQL is used to give a temporary name to a table or a column in a table for the purpose of a particular query
    • It works as a nickname for expressing the tables or column names
    • It makes the query short and neat
    • FROM tableName aliasName => Give alias name to a table
    • SELECT colName AS alias_name FROM tableName;
    • SELECT colName1, colName2 FROM tableName AS alias_name;
    • SELECT 'string' AS aliasName => Creates a Column of name alias with value equal to given string
• WHERE
  • Reduce rows based on given conditions
  • SELECT * FROM customer WHERE age > 18;
  • WHERE condition => Select Columns if it satisfies the condition, Conditions applied on Columns
  • WHERE condition1 AND condition2 => Apply multiple conditions using Logical operators, Use brackets when taking care of order
  • WHERE colName IN ('value1', 'value2') => Selects rows where values of Column are equal to given values, IN is used for multiple values
  • WHERE colName BETWEEN value1 AND value2 => Values are inclusive
  • WHERE colName LIKE 'value%' => Percent represent any number of character
  • WHERE colName LIKE 'value_' => _ represent single character
  • WHERE colName REGEXP 'value' => Similar to LIKE operator
  • WHERE colName REGEXP '^value' => ^ represent beginning of a string
  • WHERE colName REGEXP 'value$' => $ represent end of a string
  • WHERE colName REGEXP 'value1|value2' => Search for multiple pattern
  • WHERE colName REGEXP '[value1value2]value' => value1 or value2 should be present before value
  • WHERE colName REGEXP '[value1-value2]' => search for range of values
  • BETWEEN
    • SELECT * FROM customer WHERE age BETWEEN 0 AND 100;
      • 0 and 100 are inclusive
  • IN
    • Reduces OR conditions
    • SELECT * FROM officers WHERE officer_name IN ("Lakshay", "Pratap", "Deepika");
  • AND/OR/NOT
    • AND
      • WHERE cond1 AND cond2
    • OR
      • WHERE cond1 OR cond2
    • NOT
      • WHERE colName NOT IN (1,2,3,4);
  • IS NULL
    • SELECT * FROM customer WHERE prime_status is NULL;
  • Pattern Searching / Wildcard
    • "%" => Any number of character from 0 to n, Similar to "*" asterisk in regex
    • "_" => Only one character
    • SELECT * FROM customer WHERE name LIKE "%p_";
• ORDER BY
  • Sorting the data retrieved using WHERE clause
  • DESC = Descending and ASC = Ascending
  • ORDER BY colName DESC;
  • SELECT * FROM customer ORDER BY name DESC;
  • ORDER BY colName => Sort the Data by given Column in ascending order, Column can even be an Alias name/Expression
  • ORDER BY colName1, colName2 => If values matches in column1 then sort it by column2
• DISTINCT
  • Find distinct values in the table
  • SELECT DISTINCT(colName) FROM tableName;
  • GROUP BY can also be used for the same
    • Select colName from table GROUP BY colName;
• GROUP BY
  • This Clause is used to collect data from multiple records and group the result by one or more column
  • It is generally used in a SELECT statement
  • Groups into category based on column given
  • SELECT colName1, colName2, colName3 FROM tableName WHERE condition GROUP BY colName1, colName2, colName3
  • All the column names mentioned after SELECT statement shall be repeated in GROUP BY, in order to successfully execute the query
  • Used with aggregation functions to perform various actions
    • COUNT(), AVG(), SUM(), MIN(), MAX()
  • GROUP BY HAVING
    • Similar to WHERE
    • Select COUNT(cust_id), country from customer GROUP BY country HAVING COUNT(cust_id) > 50;
    • WHERE vs HAVING
      • Both have same function of filtering the row base on certain conditions
      • WHERE clause is used to filter the rows from the table based on specified condition
      • HAVING clause is used to filter the rows from the groups based on the specified condition
      • HAVING is used after GROUP BY while WHERE is used before GROUP BY clause
      • If you are using HAVING, GROUP BY is necessary
      • WHERE can be used with SELECT, UPDATE & DELETE keywords while GROUP BY used with SELECT

DML

• INSERT
  • Insert a row into a Table

      INSERT INTO tableName
      # Give values for each Column based on its attributes defined
      VALUES (value1, value2)
    

  • Mention the Column that you want to insert values for, Can be given in any order

      INSERT INTO tableName (colName1, colName2)
      VALUES (value1, value2)
    

  • DEFAULT gives a already defined default value

      INSERT INTO tableName
      VALUES (DEFAULT, value1, value2, NULL)
    

  • Insert multiple rows

      INSERT INTO tableName
      VALUES (value1, value2),
          (value3, value4)
    

  • Insert hierarchical Rows

      INSERT INTO tableName
      VALUES (LAST_INSERT_ID(), value1, value2)
    

  • Insert selected rows as Subquery statement

      INSERT INTO tableName1
      SELECT *
      FROM tableName2
      WHERE condition
    

• UPDATE
  • UPDATE tableName SET col1 = 1, col2 = ‘abc’ WHERE id = 1;
  • Update multiple rows
    • UPDATE student SET standard = standard + 1;
      • SET SQL_SAFE_UPDATES = 0; => Turn off safe mode
  • Update value of Column which satisfies the condition

      UPDATE tableName
      # Change the value of Column with the value given
      SET colName = value
      # Where clause is optional
      WHERE condition
    

  • Update by giving Subquery in WHERE clause which return id of records which satisfies the condition

      UPDATE tableName
      SET colName = value
      # Use IN instead of equal sign when dealing with multiple values
      WHERE colName = (SELECT * FROM tableName WHERE condition)
    

  • ON UPDATE CASCADE
    • Can be added to the table while creating constraints
    • Suppose there is a situation where we have two tables such that primary key of one table is the foreign key for another table. if we update the primary key of the first table then using the ON UPDATE CASCADE foreign key of the second table automatically get updated
• DELETE
  • DELETE FROM tableName WHERE id = 1;
  • DELETE FROM tableName; => All rows will be deleted
    • Safe mode needs to be turned off
  • DELETE CASCADE => To overcome DELETE constraint of Referential constraints

      CREATE TABLE ORDER (
      order_id int PRIMARY KEY,
      delivery_date DATE,
      cust_id INT,
      FOREIGN KEY(cust_id) REFERENCES customer(id) ON DELETE CASCADE
      );
    

  • ON DELETE NULL

      CREATE TABLE ORDER (
      order_id int PRIMARY KEY,
      delivery_date DATE,
      cust_id INT,
      FOREIGN KEY(cust_id) REFERENCES customer(id) ON DELETE SET NULL
      );
    

• REPLACE
  • Primarily used for already present tuple in a table
  • As UPDATE, using REPLACE with the help of WHERE clause in PK, then that row will be replaced
  • As INSERT, if there is no duplicate data new tuple will be inserted
  • REPLACE INTO student (id, class) VALUES(4, 3);
  • REPLACE INTO table SET col1 = val1, col2 = val2;

JOIN

• FK are used to do reference to other table
• INNER JOIN
  • Returns a resultant table that has matching values from both the tables or all the tables
  • INNER JOIN can also be written as just JOIN
  • SELECT column-list FROM table1 INNER JOIN table2 ON condition1 INNER JOIN table3 ON condition2;
  • Join multiple tables

      FROM tableName1
      JOIN tableName2 ON condition1
      JOIN tableName3 ON condition2
    

  • Join tables with composite primary keys

      FROM tableName1
      JOIN tableName2 USING (colName1, colName2)
    

  • Joins column with same name

      FROM tableName1
      NATURAL JOIN tableName2
    

  • Join tables with composite primary keys => This is a compound Join condition

      FROM database.tableName1
      INNER JOIN tableName2 ON condition1 AND condition2
    

  • Join without using join keywords
    • SELECT * FROM table1, table2 WHERE condition;
    • SELECT artist_name, album_name, year_recorded FROM artist, album WHERE artist.id = album.artist_id;
• OUTER JOIN
  • LEFT JOIN
    • This returns a resulting table that all the data from left table and the matched data from the right table
    • LEFT JOIN can also be written as LEFT OUTER JOIN
    • SELECT columns FROM table LEFT JOIN table2 ON Join_Condition;
  • RIGHT JOIN
    • This returns a resulting table that all the data from right table and the matched data from the left table
    • RIGHT JOIN can also be written as RIGHT OUTER JOIN
    • SELECT columns FROM table RIGHT JOIN table2 ON join_cond;
  • FULL JOIN
    • This returns a resulting table that contains all data when there is a match on left or right table data
    • Emulated in MySQL using LEFT and RIGHT JOIN => LEFT JOIN UNION RIGHT JOIN

        SELECT columns FROM table1 as t1 LEFT JOIN table2 as t2 ON t1.id = t2.id
        UNION
        SELECT columns FROM table1 as t1 RIGHT JOIN table2 as t2 ON t1.id = t2.id;
      

    • statement1 UNION statement2 => Creates union of both values, Number of query returned should be equal
• CROSS JOIN
  • This returns all the cartesian products of the data present in both tables. Hence, all possible variations are reflected in the output
  • Used rarely in practical purpose
  • SELECT column-lists FROM table1 CROSS JOIN table2;
• SELF JOIN
  • It is used to get the output from a particular table when the same table is joined to itself
  • Used very less
  • Emulated using INNER JOIN
  • SELECT columns FROM table as t1 INNER JOIN table as t2 ON t1.id = t2.id;

SET

• Used to combine multiple select statements
• Always gives distinct rows
• 
• UNION
  • Combines two or more SELECT statements
  • SELECT * FROM table1 UNION SELECT * FROM table2;
  • Number of column, order of column must be same for table1 and table2
  • UNION ALL => Can also be used, this will duplicate values as well while UNION gives unique values
• INTERSECT
  • Returns common values of the tables
  • Emulated
  • SELECT DISTINCT column-list FROM table1 INNER JOIN table2 USING(join_cond);
  • SELECT DISTINCT * FROM table1 INNER JOIN table2 ON USING(id);
• MINUS
  • This operator returns the distinct row from the first table that does not occur in the second table
  • Emulated
  • SELECT columnName FROM table1 LEFT JOIN table2 USING(columnName) WHERE table2.columnName IS NULL;
  • SELECT id FROM table1 LEFT JOIN table2 USING(id) WHERE table2.id IS NULL;

SUBQUERIES

• Outer query depends on inner query
• Alternative to joins
• Nested queries

    SELECT columnNames FROM tableName WHERE columnName OPERATOR
    (SELECT columnNames FROM tableName [WHERE]);
  

    SELECT * FROM table1 WHERE col1 IN (SELECT col1 FROM table1);
  

• Sub queries exist mainly in 3 clauses
  • Inside a WHERE clause, Inside a FROM clause, Inside a SELECT clause
• Subquery using FROM clause

    SELECT MAX(rating) FROM (SELECT \* FROM movie WHERE country = ‘India’) as temp;
  

• Subquery using SELECT

    SELECT (SELECT columnNames FROM T_name WHERE condition), columnList FROM T2_name WHERE condition;
  

• Derived Subquery

    SELECT columnLists FROM (SELECT columnLists FROM tableName WHERE [condition]) as new_tableName;
  

• Co-related sub-queries
  • With a normal nested subquery, the inner SELECT query runs first and executes once, returning values to be used by the main query
  • A correlated subquery, however, executes once for each candidate row considered by the outer query
  • In other words, the inner query is driven by the outer query

        SELECT * FROM worker w1 WHERE 4 = (SELECT count(distinct(w2.salary)) FROM worker w2 WHERE w2.salary >= w1.salary);
    

•