Skip to content

Module 3 : MYSQL

Jump to bottom
Rishabh Malviya edited this page Mar 14, 2023 · 9 revisions

MYSQL

MySQL is one most popular Open Source SQL database management systems developed, distributed, and supported by Oracle Corporation.

A database is a structured collection of data. To add, access, and process data stored in a computer database, we need a database management system such as MySQL Server. Since computers are very good at handling large amounts of data, database management systems play a central role in computing, as standalone utilities, or as parts of other applications.

Features of MYSQL

MYSQL provides various features that make it an ideal choice for managing data. Some of these features include:

  • Easy to Use: MYSQL is user-friendly and easy to use, even for non-technical users.
  • Scalability: MYSQL can handle large volumes of data, making it scalable for small and large businesses.
  • Security: MYSQL provides various security features like encryption and password protection to secure data.
  • High Availability: MYSQL ensures high availability by providing features like replication and clustering.
  • Customizability: MYSQL is highly customizable, allowing users to tailor the database to their specific needs.

MYSQL uses SQL (Structured Query Language) to manage and operate the database.

SQL is used to do all kinds of operations on a database like create, insert, update, delete, and drop databases.

MYSQL Data Types

Data types are used to represent the nature of the data that can be stored in the database table. For example, in a particular column of a table,

Datatypes are mainly clasfiied into tree categories for every database

  • Numeric
  • String
  • Date and Time

Numberic data types are used to store numeric values. The following are the most commonly used numeric data types in MYSQL:

  • INT - This data type is used to store integer values. The integer values can be positive or negative. The maximum value that can be stored in an INT data type is 2147483647. The minimum value that can be stored in an INT data type is -2147483648.

  • FLOAT - This data type is used to store floating point values. The maximum value that can be stored in a FLOAT data type is 3.402823466E+38. The minimum value that can be stored in a FLOAT data type is -3.402823466E+38.

  • DOUBLE - This data type is used to store floating point values. The maximum value that can be stored in a DOUBLE data type is 1.7976931348623157E+308. The minimum value that can be stored in a DOUBLE data type is -1.7976931348623157E+308.

  • DECIMAL - An unpacked floating-point number that cannot be unsigned. In unpacked decimals, each decimal corresponds to one byte. Defining the display length and the number of decimals is required. Numeric is a synonym for decimal.

  • BIT - This data type is used to store binary values. The maximum value that can be stored in a BIT data type is 1. The minimum value that can be stored in a BIT data type is 0.

  • TINYINT - This data type is used to store integer values. The integer values can be positive or negative. The maximum value that can be stored in a TINYINT data type is 127. The minimum value that can be stored in a TINYINT data type is -128.

  • SMALLINT - This data type is used to store integer values. The integer values can be positive or negative. The maximum value that can be stored in a SMALLINT data type is 32767. The minimum value that can be stored in a SMALLINT data type is -32768.

  • MEDIUMINT - This data type is used to store integer values. The integer values can be positive or negative. The maximum value that can be stored in a MEDIUMINT data type is 8388607. The minimum value that can be stored in a MEDIUMINT data type is -8388608.

  • BIGINT - This data type is used to store integer values. The integer values can be positive or negative. The maximum value that can be stored in a BIGINT data type is 9223372036854775807. The minimum value that can be stored in a BIGINT data type is -9223372036854775808.

  • BOOLEAN - This data type is used to store boolean values. The maximum value that can be stored in a BOOLEAN data type is 1. The minimum value that can be stored in a BOOLEAN data type is 0.

String data types are used to store string values. The following are the most commonly used string data types in MYSQL:

  • CHAR - This data type is used to store a fixed-length string (can contain letters, numbers, and special characters). The maximum length of a CHAR value is 255 characters.

  • VARCHAR - This data type is used to store a variable-length string (can contain letters, numbers, and special characters). The maximum length of a VARCHAR value is 65535 characters.

  • BINARY - This data type is used to store a fixed-length string. The maximum length of a BINARY value is 255 characters.

  • VARBINARY - This data type is used to store a variable-length string. The maximum length of a VARBINARY value is 65535 characters.

  • TINYBLOB - This data type is used to store a string with a maximum length of 255 characters.

  • TINYTEXT - This data type is used to store a string with a maximum length of 255 characters.

  • BLOB - This data type is used to store a string with a maximum length of 65535 characters.

  • TEXT - This data type is used to store a string with a maximum length of 65535 characters.

  • MEDIUMBLOB - This data type is used to store a string with a maximum length of 16777215 characters.

  • MEDIUMTEXT - This data type is used to store a string with a maximum length of 16777215 characters.

  • LONGBLOB - This data type is used to store a string with a maximum length of 4294967295 characters.

  • LONGTEXT - This data type is used to store a string with a maximum length of 4294967295 characters.

  • ENUM - This data type is used to store a list of permitted values. An ENUM can have a maximum of 65535 distinct values.

  • SET - This data type is used to store a set of permitted values. A SET can have a maximum of 64 distinct members.

Date and time data types are used to store date and time values. The following are the most commonly used date and time data types in MYSQL:

  • DATE - This data type is used to store a date (year, month, and day). The supported range is '1000-01-01' to '9999-12-31'.

  • DATETIME - This data type is used to store a date and time combination (year, month, day, hour, minute, and second). The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

  • TIMESTAMP - This data type is used to store the number of seconds between the timestamp '1970-01-01 00:00:00' UTC and the time specified in the value. The supported range is '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

  • TIME - This data type is used to store a time (hour, minute, and second). The supported range is '-838:59:59' to '838:59:59'.

  • YEAR - This data type is used to store a year (year, month, and day). The supported range is '1901' to '2155' or '0000'.

  • DECIMAL -

SQL (Structured Query Language)

Structured Query Language (SQL) is a popular programming language used for managing relational databases. Testing SQL code is essential to ensure that the code functions as expected and produces accurate results.

Types of SQL

  1. Data Definition Language (DDL)
  • DDL changes the structure of the table like creating a table, deleting a table, altering a table, etc.
  • All the command of DDL are auto-committed that means it permanently save all the changes in the database.

Commands that come under DDL:

  • CREATE
  • ALTER
  • DROP
  • TRUNCATE
  1. Data Manipulation Language (DML)
  • DML commands are used to modify the database. It is responsible for all form of changes in the database.
  • The command of DML is not auto-committed which means it can't permanently save all the changes in the database. They can be rollback.

Commands come under DML:

  • INSERT

  • UPDATE

  • DELETE

  1. Data Control Language (DCL)

    DCL commands are used to grant and take back authority from any database user.

    Here are some commands that come under DCL:

    • Grant

    • Revoke

MYSQL Database

A database is a structured collection of data that is organized and stored in a specific format that allows for efficient retrieval and management of the data.

A MySQL database consists of one or more tables, each of which contains a set of related data organized into rows and columns. Each column in a table represents a specific data element, and each row represents a specific record or instance of that data element.

Create Database

Create database command is used to create a new database in MYSQL.

CREATE DATABASE [IF NOT EXISTS] <databasename>;
CREATE DATABASE hotwax;

Use Database

The use database command is used to select a database to use for creating table inside it.

USE <databasenmae>;
USE hotwax;

Show Database

In SQL, the SHOW DATABASES command is used to list all the databases that exist on a server. This command is particularly useful when you want to know what databases are available to you, or if you want to verify that a database you created actually exists.

SHOW DATABASES;
SHOW DATABASES LIKE <pattern>;
SHOW DATABASES LIKE 'hotwax';

MYSQL Drop Database

The MySQL DROP DATABASE statement is used to delete a database, including all tables and data contained within it. The syntax for using the DROP DATABASE statement is as follows:

DROP DATABASE [IF NOT EXISTS] <databasename>;
DROP DATABASE hotwax;

The statement must be executed by a user with appropriate privileges to delete the database. Once the statement is executed, the database and all its contents are permanently deleted, and cannot be recovered.

MYSQL Tables

Tables are a fundamental component of MySQL databases. A table comprises rows and columns, with each column representing a specific type of data, and each row representing a record or instance of that data. MySQL tables can be created, modified, and deleted using SQL queries.

Create Table

To create a table in MySQL, the CREATE TABLE statement is used. This statement specifies the columns and data types for the table, as well as any constraints or indexes that should be applied.

Example of CREATE TABLE:

CREATE TABLE emp (
  id INT NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL,
  PRIMARY KEY (id)
);

Alter Table

In MySQL, the ALTER TABLE statement is used to modify an existing table. It can be used to add or remove columns, change column data types, add or remove indexes, and more.

Example of Alter Table (Adding):

ALTER TABLE employees
ADD COLUMN phone_number VARCHAR(20) AFTER email;

Modify

ALTER TABLE employees
MODIFY COLUMN hire_date DATETIME;

Rename

ALTER TABLE employees
RENAME TO employees_backup;

Drop

ALTER TABLE employees
DROP COLUMN phone_number;

Overall, ALTER TABLE is a powerful statement that allows developers to modify existing tables in MySQL using SQL queries.

Show Tables

MYSQL provides a SHOW TABLES command to list all the tables in a database. This command is particularly useful when you want to know what tables are available to you, or if you want to verify that a table you created actually exists.

SHOW TABLES;
SHOW TABLES LIKE <pattern>;
SHOW TABLES LIKE 'emp';

Descibe Table

Describe table command is used to get the structure of a table.

DESCRIBE <tablename>;
DESCRIBE emp;

Drop Table

The DROP TABLE statement is used to delete a table from a MySQL database. The syntax for using the DROP TABLE statement is as follows:

DROP TABLE [IF EXISTS] <tablename>;
DROP TABLE emp;

The statement must be executed by a user with appropriate privileges to delete the table. Once the statement is executed, the table and all its contents are permanently deleted, and cannot be recovered.

MYSQL Temporary Tables

MYSQL provides a temporary table feature that allows you to create a temporary table that is only accessible to the current session. This is useful for storing intermediate results from a query, or for performing complex operations on a table without affecting the original table.

Create Temporary Table

To create a temporary table in MySQL, the CREATE TEMPORARY TABLE statement is used. This statement specifies the columns and data types for the table, as well as any constraints or indexes that should be applied.

CREATE TEMPORARY TABLE temp_table (
  id INT NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL,
  PRIMARY KEY (id)
);

Drop Temporary Table

To drop a temporary table in MySQL, the DROP TEMPORARY TABLE statement is used. This statement specifies the name of the table to be dropped.

DROP TEMPORARY TABLE temp_table;

MYSQL SHOW COLUMNS

The SHOW COLUMNS command is used to list all the columns in a table. This command is particularly useful when you want to know what columns are available to you, or if you want to verify that a column you created actually exists.

SHOW COLUMNS FROM <tablename>;
SHOW COLUMNS FROM emp;
SELECT INFORMATION_SCHEMA.COLUMNS FROM emp;

Table Queries

Insert Query

The INSERT statement is used to insert a new record into a table. The syntax for using the INSERT statement is as follows:

INSERT INTO <tablename> (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Example:

INSERT INTO emp (first_name, last_name, email)
VALUES ('John', 'Doe', 'johndoe@gmail.com');

Update Query

The UPDATE statement is used to modify the existing records in a table. The syntax for using the UPDATE statement is as follows:

UPDATE <tablename>
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example:

UPDATE emp
SET email = 'hello@gmail.com;'
WHERE id = 1;

Delete Query

The DELETE statement is used to delete records from a table. The syntax for using the DELETE statement is as follows:

DELETE FROM <tablename>
WHERE condition;

Example:

DELETE FROM emp
WHERE id = 1;

Select Clauses

Where Clause

WHERE is used to filter records. The WHERE clause is used to extract only those records that fulfill a specified condition.

SELECT column1, column2, column3, ...
FROM <tablename>
WHERE condition;

Example:

SELECT first_name, last_name, email
FROM emp
WHERE id = 1;

Order By Clause

ORDER BY is used to sort the result-set in ascending or descending order. The ORDER BY clause is used to sort the data in ascending or descending order based on one or more columns.

SELECT column1, column2, column3, ...
FROM <tablename>
ORDER BY column1, column2, column3, ... ASC|DESC;

Example:

SELECT first_name, last_name, email
FROM emp
ORDER BY first_name ASC;

Group By Clause

GROUP BY is used to group the result-set by one or more columns. The GROUP BY clause is used in conjunction with the SELECT statement to arrange identical data into groups.

SELECT column1, column2, column3, ...
FROM <tablename>
GROUP BY column1, column2, column3, ...;

Example:

SELECT first_name, last_name, email
FROM emp
GROUP BY first_name;

Having Clause

HAVING is used to filter groups. The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

SELECT column1, column2, column3, ...
FROM <tablename>
GROUP BY column1, column2, column3, ...
HAVING condition;

Example:

SELECT first_name, last_name, email
FROM emp
GROUP BY first_name

Limit Clause

LIMIT is used to specify the maximum number of records to be returned from a query. The LIMIT clause is used to specify the number of records to return.

SELECT column1, column2, column3, ...
FROM <tablename>
LIMIT number;

Example:

SELECT first_name, last_name, email
FROM emp
LIMIT 5;

Like Clause

LIKE is used to search for a specific pattern in a column. The LIKE operator is used in a WHERE clause to search for a specific pattern in a column.

SELECT column1, column2, column3, ...
FROM <tablename>
WHERE column1 LIKE pattern;

Example:

SELECT first_name, last_name, email
FROM emp
WHERE first_name LIKE 'J%';

MYSQL Aggregate Functions

Aggregate functions are used to perform calculations on a set of values. The following table lists the aggregate functions available in MySQL.

Function Description
AVG() Returns the average value
COUNT() Returns the number of rows
MAX() Returns the largest value
MIN() Returns the smallest value
SUM() Returns the sum 
SELECT AVG(id) FROM emp;
SELECT COUNT(id) FROM emp;
SELECT MAX(id) FROM emp;
SELECT MIN(id) FROM emp;
SELECT SUM(id) FROM emp;

MYSQL Views

MYSQL Views are virtual tables that are created from a query. They are used to simplify complex queries, and to provide a layer of abstraction between the database and the application. They are similar to stored procedures, but they are read-only and are not executed until they are explicitly referenced. Views are created using the CREATE VIEW statement, and dropped using the DROP VIEW statement. Example:

CREATE VIEW emp_view AS
SELECT id, first_name, last_name, email
FROM emp;

MYSQL Indexes

Indexes are used to improve the performance of queries in MySQL. They are created using the CREATE INDEX statement, and dropped using the DROP INDEX statement. Example:

CREATE INDEX idx_emp_email ON emp (email);

MYSQL Triggers

Triggers are special stored procedures that are automatically executed when a specific event occurs in MySQL. They are used to enforce data integrity, and to automatically update data in related tables. Triggers are created using the CREATE TRIGGER statement, and dropped using the DROP TRIGGER statement. Example:

CREATE TRIGGER emp_email_insert
BEFORE INSERT ON emp
FOR EACH ROW
SET NEW.email = CONCAT(NEW.first_name, '.', NEW.last_name, '@example.com');

MYSQL Transactions

Transactions are used to ensure that a set of SQL queries are executed as a single unit. If any of the queries fail, all of the queries are rolled back, and the database is left in the same state as before the transaction was started. Transactions are started using the START TRANSACTION statement, and committed using the COMMIT statement. They can also be rolled back using the ROLLBACK statement. Example:

START TRANSACTION;
UPDATE emp SET first_name = 'John' WHERE id = 1;
UPDATE emp SET first_name = 'Jane' WHERE id = 2;
COMMIT;

MYSQL Constraints

Constraints are used to specify rules for the data in a table. They are used to ensure the accuracy and consistency of the data in the database. Constraints are created using the ALTER TABLE statement. Example:

ALTER TABLE emp
ADD CONSTRAINT emp_email_unique UNIQUE (email);

MYSQL Joins

Joins are used to combine rows from two or more tables, based on a related column between them.

Types of Joins:

  • Inner Join
  • Left Join
  • Right Join
  • Full Join
  • Cross Join Joins

Inner Join

An inner join returns rows that have matching values in both tables.

SELECT column1, column2, ...
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Example:

SELECT emp.id, emp.first_name, emp.last_name, emp.email, dept.name
FROM emp
INNER JOIN dept ON emp.dept_id = dept.id;

Left Join

A left join returns all rows from the left table, even if there are no matches in the right table.

SELECT column1, column2, ...
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Example:

SELECT emp.id, emp.first_name, emp.last_name, emp.email, dept.name
FROM emp
LEFT JOIN dept ON emp.dept_id = dept.id;

Right Join

A right join returns all rows from the right table, even if there are no matches in the left table.

SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Example:

SELECT emp.id, emp.first_name, emp.last_name, emp.email, dept.name
FROM emp
RIGHT JOIN dept ON emp.dept_id = dept.id;

Full Join

A full join returns all rows when there is a match in either left or right table.

SELECT column1, column2, ...
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name
UNION 
SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Example:

SELECT emp.id, emp.first_name, emp.last_name, emp.email, dept.name  FROM emp
LEFT JOIN dept ON emp.dept_id = dept.id
UNION
SELECT emp.id, emp.first_name, emp.last_name, emp.email, dept.name  FROM emp
RIGHT JOIN dept ON emp.dept_id = dept.id;

Cross Join

A cross join returns the Cartesian product of the sets of records from the two or more joined tables.

SELECT column1, column2, ...
FROM table1
CROSS JOIN table2;

Example:

SELECT emp.id, emp.first_name, emp.last_name, emp.email, dept.name
FROM emp
CROSS JOIN dept;

MYSQL Subqueries

Subqueries are used to return data from a query that is nested inside another query. They are used to return data from multiple tables, or to return data from a query that is more complex than a single SELECT statement. Example:

SELECT id, first_name, last_name, email
FROM emp
WHERE dept_id IN (
  SELECT id
  FROM dept
  WHERE name = 'Sales'
);

MYSQL Unions

Unions are used to combine the results of two or more SELECT statements into a single result set. The result set consists of the columns from all the SELECT statements, and the rows from all the SELECT statements. Example:

SELECT id, first_name, last_name, email
FROM emp
WHERE dept_id = 1
UNION
SELECT id, first_name, last_name, email
FROM emp
WHERE dept_id = 2;

Keys in MySQL

Keys are used to uniquely identify each row in a table. They are used to enforce data integrity, and to improve the performance of queries. Keys are created using the ALTER TABLE statement. Example:

ALTER TABLE emp
ADD PRIMARY KEY (id);

Primary Key

A primary key is a column or a combination of columns that uniquely identifies each row in a table. It cannot contain NULL values, and each table can have only one primary key.

ALTER TABLE emp
ADD PRIMARY KEY (id);

Foreign Key

A foreign key is a column or a combination of columns that uniquely identifies a row in another table. It can contain NULL values, and it can be used to create relationships between tables.

ALTER TABLE emp
ADD FOREIGN KEY (dept_id) REFERENCES dept(id);

Unique Key

A unique key is a column or a combination of columns that uniquely identifies each row in a table. It can contain NULL values, and it can be used to enforce uniqueness for a column or a combination of columns.

ALTER TABLE emp
ADD UNIQUE (email);

Composite Key

A composite key is a combination of two or more columns that can be used to uniquely identify each row in a table. It can contain NULL values, and it can be used to create primary keys and unique keys.

ALTER TABLE emp
ADD PRIMARY KEY (id, dept_id);

Alternate Key

An alternate key is a column or a combination of columns that can be used to uniquely identify each row in a table. It can contain NULL values, and it can be used to create primary keys and unique keys.

Candidate Key

A candidate key is a column or a combination of columns that can be used to uniquely identify each row in a table. It can contain NULL values, and it can be used to create primary keys and unique keys.

Artificial Key

An artificial key is a column or a combination of columns that can be used to uniquely identify each row in a table. It can contain NULL values, and it can be used to create primary keys and unique keys.

Normalization in MySQL

Normalization is a process of organizing data in a database to minimize redundancy and dependency. It is used to reduce data anomalies, and to improve the performance of queries.

First Normal Form (1NF)

  • In the first normal form, the data is stored in a table with each column containing atomic values.
  • There should be no repeating groups of columns.

Second Normal Form (2NF)

  • In the second normal form, the data is stored in a table with each column containing atomic values. There should be no repeating groups of columns.
  • The table should be in the first normal form.

Third Normal Form (3NF)

  • In the third normal form, the data is stored in a table with each column containing atomic values. There should be no repeating groups of columns.
  • The table should be in the second normal form.

Boyce-Codd Normal Form (BCNF)

  • A table is in BCNF if it is in 3NF and for every functional dependency A -> B, A is a super key.
  • This eliminates certain types of data anomalies that can occur in 3NF tables where a non-key attribute determines another non-key attribute.

Clone this wiki locally