MY SQL
Database:
A database is an application that stores the organized collection of records.
It can be accessed and manage by the user very easily. It allows us to organize
data into tables, rows, columns, and indexes to find the relevant information very
quickly.
Each database contains distinct API for performing database operations such as
creating, managing, accessing, and searching the data it stores.
Many databases are available like MySQL, Sybase, Oracle, MongoDB, PostgreSQL,
SQL Server, etc.
What is MySQL?
MySQL is currently the most popular database management system software
used for managing the relational database.
It is open-source database software, which is supported by Oracle Company. It is
fast, scalable, and easy to use database management system in comparison with
Microsoft SQL Server and Oracle Database.
It allows us to implement database operations on tables, rows, columns, and
indexes.
It defines the database relationship in the form of tables (collection of rows and
columns), also known as relations.
It provides the Referential Integrity between rows or columns of various tables.
It allows us to updates the table indexes automatically.
It uses many SQL queries and combines useful information from multiple tables
for the end-users
Entity: The smallest table that contains meaningful set of data
Field: A column in a table containing specific information about every record in a
table
Relation: Allows us to retrieve data effeciently
RDBMS: Relational Database Management System Ex Relational Tables
Table: Entity, Database object
Rows: Horizontal entity, Also called entity instances
Column: Vertical entity
Database Management: Database Design+Creation+Manupulation
SQL: Standard Query Language is a programing language ,you need to execute
commands to create and Manupulate Databases
Types Of programing:
Procedural(Imperative) ---> C, Java (Step by step)
Object-Oriented
Declarative
Functional
SQL is mainly regarded as Declarative programing language(Non-Procedural)
that means while coding you are not interested in how you want the job done but
focus in what result you want to obtain
Main Components of SQL Syntax:
Data Definition Language(DDL)
Data Manipulation Language(DML)
Data Control Language (DCL)
Transaction control Langauage(TCL)
SQL's Syntax: Comprises several types of statements that allow you to perform
various commands & operations
Key Words: Reserve words, Objects and data bases cannot have names that
coincide with SQL keywords
DDL: A set of statements that allow the user to modify data structures and
objects, such as tables
Create
Alter
Drop
Truncate
Rename
DML: Statements allows us to manupulate the data in the tables
Database Manipulation: Allows you to use your Dataset to extract business
insights (Performance & Efficiency)
Select-- Select statement used to retrieve data from database objects like tables
Select ... From
Insert... Into...Values
Update... Set... Where
Delete.. From...Where
DCL: Mainly Grant & Revoke statements, Allows us to manage the rights that
users have in a Database, Grant--- allowing & Revoke--- Exactly Opposite to Grant
Database Administrators: People who have complete rights to a database, can
grant access users and can revoke it
TCL: Not every change you make to a Database is saved automatically
Commit--- Save the changes permanently
Rollback--- Allows you to undo changes you have made, A step back
DDL - Creation of Data
DML - Manipulation of Data
DCL - Assignment & Removal of permissions to use Data
TCL - Saving & Restoring changes to a Database
Relational Schemas:
Primary Key:
A column(or a set of columns) whoose value exists and is unique for every record
in a table is called primary key
Each table can have one and only one primary key
In one table you cannot have multiple primary keys
Generally it is coded on the upper part of all fields in a table and always
underlined
Foreign Key:
The foreign key is used to link one or more than one table together. It is also
known as the referencing key.
Identifies the relationship between the tables
Should mentioned FK with the brackets next to the column name
Points to the column of another table and thus links the two tables
Foreign Key in SQL is defined through a foreign key Constraint
Parent Table - Referenced table
Child table - Referencing table
Data Types:
We must always specify the type of data that will be inserted in each column of
the table
Different Data types represent different types of information that can be
contained in a specific column
Lenght: A measure used to indicate how many symbols a certain string has
Size: Indicates the memory space used by a Data type (bytes)
Storage: The physical space in the computer drive's memory where the data type
being saved or stored
String: The text format in SQL is called string
Digits, symbols or blank spaces can also be used in the string format
No Mathematical operations can be executed, they will convey only text
information
Ex: Character CHAR(5) Fixed Max Size 255(byte)
Variable Character VARCHAR(5) Not fixed MaxSize 65,535byt
Enumerate ENUM('M', 'F') only M/F are allowed
DATE
DATETIME
TIMESTAMP
String Datatype:
The string data type is used to hold plain text and binary data, for example, files,
images, etc. MySQL can perform searching and comparison of string value based
on the pattern matching such as LIKE operator, Regular Expressions, etc
Date & Time Datatype:
This data type is used to represent temporal values such as date, time, datetime,
timestamp, and year. Each temporal type contains values, including zero. When
we insert the invalid value, MySQL cannot represent it, and then zero value is
used
ENUM Datatype:
It is short for enumeration, which means that each column may have one of the
specified possible values. It uses numeric indexes (1, 2, 3…) to represent string
values
Numeric Data Types:
Integer, Fixed point, Floating point
Integer: Whole numbers with no decimal points Ex: 3,7,-5,333
Integers INT - Allows us to insert integers into table
Fixed Point: Decimal/Numeric - DECIMAL(7,0)/DECIMAL(7), DECIMAL(5,3)
Floating Point: Float,Double - FLOAT(5,3)
INTEGER
DECIMAL
NUMERIC
FLOAT
DOUBLE
Constraints:
The constraint in MySQL is used to specify the rule that allows or restricts what
values/data will be stored in the table.
They provide a suitable method to ensure data accuracy and integrity inside the
table. It also helps to limit the type of data that will be inserted inside the table.
If any interruption occurs between the constraint and data action, the action is
failed
Constraints are specific rules, limits that we define in our table
The role of constraints is to outline the existing relationships between different
tables in our Database
Types of MySQL Constraints
Column Level Constraints: These constraints are applied only to the single column
that limits the type of particular column data.
Table Level Constraints: These constraints are applied to the entire table that
limits the type of data for the whole table
The following are the most common constraints used in the MySQL:
NOT NULL
CHECK
DEFAULT
PRIMARY KEY
AUTO_INCREMENT
UNIQUE
INDEX
ENUM
FOREIGN KEY
NOT NULL Constraint
This constraint specifies that the column cannot have NULL or empty values. The
below statement creates a table with NOT NULL constraint
UNIQUE Constraint
This constraint ensures that all values inserted into the column will be unique. It
means a column cannot stores duplicate values. MySQL allows us to use more
than one column with UNIQUE constraint in a table. The below statement creates
a table with a UNIQUE constraint:
CHECK Constraint
It controls the value in a particular column. It ensures that the inserted value in a
column must be satisfied with the given condition. In other words, it determines
whether the value associated with the column is valid or not with the given
condition
DEFAULT Constraint
This constraint is used to set the default value for the particular column where we
have not specified any value. It means the column must contain a value, including
NULL
PRIMARY KEY Constraint
This constraint is used to identify each record in a table uniquely. If the column
contains primary key constraints, then it cannot be null or empty. A table may
have duplicate columns, but it can contain only one primary key. It always
contains unique value into a column
Auto Increment: This constraint automatically generates a unique number
whenever we insert a new record into the table. Generally, we use this constraint
for the primary key field in a table
ENUM Constraint
The ENUM data type in MySQL is a string object. It allows us to limit the value
chosen from a list of permitted values in the column specification at the time of
table creation. It is short for enumeration, which means that each column may
have one of the specified possible values. It uses numeric indexes (1, 2, 3…) to
represent string values.
INDEX Constraint
This constraint allows us to create and retrieve values from the table very quickly
and easily. An index can be created using one or more than one column. It assigns
a ROWID for each row in that way they were inserted into the table.
Foreign Key Constraint
This constraint is used to link two tables together. It is also known as the
referencing key. A foreign key column matches the primary key field of another
table. It means a foreign key field in one table refers to the primary key field of
another table.
On Delete Cascade: If a Specific value from parent table's Primary key has been
deleted, allthe records from child table referring to this value will be removed as
well
WHERE Clause:
MySQL WHERE Clause is used with SELECT, INSERT, UPDATE and DELETE clause to
filter the results. It specifies a specific position where you have to do the
operation
SELECT *
FROM officers
WHERE Condition;
DISTINCT Clause:
MySQL DISTINCT clause is used to remove duplicate records from the table and
fetch only the unique records. The DISTINCT clause is only used with the SELECT
statement
SELECT DISTINCT expressions
FROM tables
[WHERE conditions]
ORDER BY:
The MYSQL ORDER BY Clause is used to sort the records in ascending or
descending order
SELECT expressions
FROM tables
[WHERE conditions]
ORDER BY expression [ ASC | DESC ]
GROUP BY:
The MYSQL GROUP BY 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.
You can also use some aggregate functions like COUNT, SUM, MIN, MAX, AVG etc.
on the grouped column.
SELECT expression1, expression2, ... expression_n,
aggregate_function (expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n;
HAVING Clause:
MySQL HAVING Clause is used with GROUP BY clause. It always returns the rows
where condition is TRUE.
SELECT expression1, expression2, ... expression_n,
aggregate_function (expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n
HAVING condition;
Parameters
{ aggregate_function: It specifies any one of the aggregate function such as
SUM, COUNT, MIN, MAX, or AVG.
expression1, expression2, ... expression_n: It specifies the expressions that are
not encapsulated within an aggregate function and must be included in the
GROUP BY clause.
WHERE conditions: It is optional. It specifies the conditions for the records to be
selected }
MY SQL JOINS:
MySQL JOINS are used with SELECT statement. It is used to retrieve data from
multiple tables. It is performed whenever you need to fetch records from two or
more tables.
There are three types of MySQL joins:
1 MySQL INNER JOIN (or sometimes called simple join)
2 MySQL LEFT OUTER JOIN (or sometimes called LEFT JOIN)
3 MySQL RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)
MySQL Inner JOIN (Simple Join)
The MySQL INNER JOIN is used to return all rows from multiple tables where the
join condition is satisfied. It is the most common type of join.
SELECT columns
FROM table1
INNER JOIN table2
ON [Link] = [Link];
MySQL LEFT JOIN:
The Left Join clause returns all the rows from the left table and matched records
from the right table or returns Null if no matching record found.
This Join can also be called a Left Outer Join clause. So, Outer is the optional
keyword to use with Left Join
SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON [Link] = [Link];
MySQL RIGHT JOIN:
The Right Join is used to joins two or more tables and returns all rows from the
right-hand table, and only those results from the other table that fulfilled the join
condition. If it finds unmatched records from the left side table, it returns Null
value. It is similar to the Left Join, except it gives the reverse result of the join
tables. It is also known as Right Outer Join. So, Outer is the optional clause used
with the Right Join
SELECT column_list
FROM Table1
RIGHT [OUTER] JOIN Table2
ON [Link] = [Link];
MySQL CROSS JOIN:
MySQL CROSS JOIN is used to combine all possibilities of the two or more tables
and returns the result that contains every row from all contributing tables.
The CROSS JOIN is also known as CARTESIAN JOIN, which provides the Cartesian
product of all associated tables.
The Cartesian product can be explained as all rows present in the first table
multiplied by all rows present in the second table.
It is similar to the Inner Join, where the join condition is not available with this
clause
SELECT column-lists
FROM table1
CROSS JOIN table2;