➢ What is a database?
A database is an organized collection of structured information, or data, typically stored
electronically in a computer system. A database is usually controlled by a database management
system (DBMS). Together, the data and the DBMS, along with the applications that are
associated with them, are referred to as a database system, often shortened to just database.
Types of Databases:
1. Relational Databases: Relational databases became dominant in the 1980s. Items in a
relational database are organized as a set of tables with columns and rows. Relational
database technology provides the most efficient and flexible way to access structured
information. RDBMS. Example- MySQL, Oracle, PostgreSQL.
2. Non-Relational Databases: These are also called NoSQL Databases. A NoSQL, or
nonrelational database, allows unstructured and semi structured data to be stored and
manipulated (in contrast to a relational database, which defines how all data inserted into
the database must be composed). NoSQL databases grew popular as web applications
became more common and more complex. Example- MongoDB.
➢ What is MySQL?
The SQL part of “MySQL” stands for “Structured Query Language”. SQL is the most common
standardized language used to access databases.
It is used to perform CRUD operations, i.e. Create, Read, Update, Delete.
a. MySQL is a database management system.
b. MySQL databases are relational.
c. MySQL software is Open Source.
d. The MySQL Database Server is very fast, reliable, scalable, and easy to use.
e. MySQL Server works in client/server or embedded systems.
f. Database may consist of one or more tables which are interrelated.
g. The columns in the table tells the design of the table and the rows in the table talk about the
individual data about an entry. The columns are also referred to as schemas in MySQL.
➢ Installation of MySQL:
# yum list available | grep mysql-server
# yum install -y @mysql
Enable the MySQL Deamon.
# systemctl enable --now mysqld.service
Secure the MySQL installation.
# mysql_secure_installation
Accessing MySQL:
➢ Making databases:
mysql> CREATE DATABASE test_db;
mysql> SHOW DATABASES;
To Remove Databases:
mysql> DROP DATABASE demo;
Using a database to make changes or create table in a DB.
mysql> USE test_db;
Creating a table
Making entries to a table
Primary key would be different for all entries.
To view table:
➢ SQL Datatypes:
Each column in a database table is required to have a name and a data type.
An SQL developer must decide what type of data that will be stored inside each column when
creating a table. The data type is a guideline for SQL to understand what type of data is expected
inside of each column, and it also identifies how SQL will interact with the stored data.
DATATYPE DESCRIPTION USAGE
A FIXED length string (can contain letters, numbers, and special
characters). The size parameter specifies the column length in characters
CHAR - can be from 0 to 255. Default is 1 CHAR(50)
A VARIABLE length string (can contain letters, numbers, and special
characters). The size parameter specifies the maximum string length in
VARCHAR characters - can be from 0 to 65535 VARCHAR(50)
BLOB For BLOBs (Binary Large Objects). Holds up to 65,535 bytes of data BLOB(1000)
INT A small integer. Signed range is from -32768 to 32767. INT
TINYINT A medium integer. Signed range is from -2147483648 to 2147483647.
A large integer. Signed range is from -9223372036854775808 to
BIGINT 9223372036854775807.
BIT can store x-bit values. X can range from 1 to 64 BIT(2)
FLOAT Decimal number with precision to 23 digits FLOAT
DOUBLE Decimal number with precision from 24 to 53 digits
BOOLEAN Boolean values 0 or 1 BOOLEAN
DATE date in format YYYY-MM-DD ranging from 1000-01-01 to 9999-12-31 DATE
YEAR year in 4 digits ranging from 1901 to 2155 YEAR
Signed and Unsigned Datatypes:
TINYINT UNSIGNED (0 to 255) – To increase our range we can use this when we know the values
can’t be negative.
TINYINT (-128 TO 127)
➢ Types of SQL Commands:
1. DDL (Data Definition Language): create, alter, rename, truncate & drop.
2. DQL (Data Query Language): select
3. DML (Data Manipulation Language): insert, update and delete.
4. DCL (Data Control Language): grant & revoke permissions to users.
5. TCL (Transaction Control Language): start transaction, commit and rollback.
➢ MySQL Queries:
1. CREATE DATABASE IF NOT EXISTS
2. DROP DATABASE IF EXISTS
3. SHOW DATABASES
4. SHOW TABLES
➢ Table Related Queries:
1. CREATE TABLE
mysql> CREATE table_name (
column_name1 datatype constraint,
column_name2 datatype constraint,
);
2. DROP TABLE
3. SELECT * : To print all data of a table.
4. INSERT INTO
➢ Keys:
1. Primary Key: It is a column (or set of columns) in a table that uniquely identifies each row.
There is only 1 PK & it shouldn’t be null.
2. Foreign Key: A Foreign Key is a column or set of columns in a table that refers to the primary
key of another table. FKs can have duplicate & null values.
➢ Constraints:
SQL Constraints are used to specify rules for data in a table.
1. NOT NULL
Columns can’t have a null value
Col1 INT NOT NULL
2. UNIQUE
All values in column are different.
Col2 INT UNIQUE
3. PRIMARY KEY
Makes a column unique & not null but used only for one
Id INT PRIMARY KEY
When using 2 columns as primary key, one entry can be duplicate but both can’t be
duplicate at once.
4. Default
Sets the default value of a column when not provided.
It is important to use the format to use default value. We have to tell that we are not writing
the value that we want to take default.
5. Check
It can limit the values allowed in a column.
➢ Select Command:
Syntax: SELECT col1,col2 FROM table_name;
# SELECT DISTINCT ip FROM devices;
(values won’t repeat)
➢ Where Clause:
To define some condition.
SELECT col1, col2 FROM table_name WHERE conditions;
WHERE + AND
Using Operators with WHERE:
1. Arithmetic Operators: addition '+' ,subtraction '-' ,multiplication '*' ,division '/' and modulus
'%'
2. Comparison Operators: equal to '=' ,not equal to '!=' , '>' , '>=' , '<' ; '<='
3. Logical Operators: AND, OR, NOT, IN, BETWEEN, ALL, LIKE, ANY
IN is used to provide a range of values. And NOT IN just reverse the condition.
4. Bitwise Operators: Bitwise AND '&' , Bitwise OR '|'
➢ Limit Clause:
# SELECT * FROM table_name limit 5;
➢ Order By Clause:
# SELECT * FROM table_name ORDER BY col4 ASC;
ASC , DESC
➢ Aggregate Functions:
Aggregate functions perform a calculation on a set of values, and return a single value.
COUNT( )
MAX( )
MIN( )
SUM( )
AVG( )
# SELECT max(marks) FROM students;
➢ Group By Clause:
Group the rows that have the same values into summary rows.
It collects data from multiple records and groups the result by one or more column.
Generally, we use group by with some aggregation function.
➢ Having Clause:
Similar to where i.e, applies some conditions on rows.
Used when we want to apply some conditions after grouping.
➢ General Order of writing SQL Query:
SELECT columns
FROM table_name
WHERE conditions
GROUP BY columns
HAVING conditions
ORDER BY columns DESC;
➢ Table Related Queries:
1. Update:
# UPDATE table_name
SET col1 = val1, col2 = val2
WHERE condition;
To Turn the safe mode off:
SET SQL_SAFE_UPDATES = 0;
2. Delete:
To delete existing rows.
# DELETE FROM table_name WHERE condition;
➢ Foreign Keys
Cascading for FKs:
1. On Delete Cascade: When we create a foreign key using this option, it deletes the
referencing rows in the child table when the referenced row is deleted in the parent table
which has a primary key.
2. On Update Cascade: When we create a foreign key using UPDATE CASCADE the referencing
rows are updated in the child table when the referenced row is updated in the parent table
which has a primary key.
CREATE TABLE student (
id INT PRIMARY KEY,
course-id INT,
FOREIGN KEY (course_id) REFERENCES course(id)
ON UPDATE CASCADE
ON DELETE CASCADE
);
➢ Table Related Queries:
1. ALTER – To change the schema of the table i.e. columns, datatype, constraints etc.
a. ALTER TABLE table_name
ADD COLUMN column_name datatype constraint
b. ALTER TABLE table_name
DROP COLUMN column_name;
c. ALTER TABLE table_name
RENAME TO new_table_name;
d. ALTER TABLE table_name
CHANGE COLUMN old_name new_name datatype constraint;
e. ALTER TABLE table_name
MODIFY col_name new_datatype new_constraint;
2. TRUNCATE
To delete table’s data
TRUNCATE TABLE table_name;
➢ Joins in SQL:
Join is used to combine rows from two or more tables, based on a related column between
them. We need some common column between tables.
1. Inner Join: Returns records that have matching values in both tables.
SELECT columns
FROM table1
INNER JOIN table2
ON table1.col_name = table2.col_name;
As is used to make alias of the table names, useful when table names are long.
2. Left Join: Returns all records from the left table, and the matched records from both tables.
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.col_name table2.col_name;
3. Right Join: Returns all records from the right table, and the matched records from both
tables.
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.col_name table2.col_name;
4. Full Join: Returns all records when there is a match in either left or right table.
SELECT columns FROM table1
LEFT JOIN table2
ON table1.col_name table2.col_name;
UNION
SELECT columns FROM table1
RIGHT JOIN table2
ON table1.col_name table2.col_name;
5. Left Exclusive Join:
SELECT columns FROM table1
LEFT JOIN table2
ON table1.col_name table2.col_name
WHERE table2.col_name IS NULL;
6. Right Exclusive Join:
SELECT columns FROM table1
RIGHT JOIN table2
ON table1.col_name table2.col_name
WHERE table1.col_name IS NULL;
7. Self-Join: It is a regular join but the table is joined with itself.
SELECT columns FROM table_name as a
JOIN table_name as b
ON a.col_name = b.col_name ;
➢ Union: It is used to combine the result set of two or more SELECT statements. Gives UNIQUE
record.
To use it:
a. Every SELECT should have same number of columns.
b. Columns must have similar data types.
c. Columns in every SELECT should be in same order.
SELECT columns FROM table1
SELECT columns FROM table2
➢ SQL Sub Queries:
A subquery or inner query or a nested query is a query within another SQL query.
It involves 2 SELECT statements.
SELECT columns
FROM table_name
WHERE col_name operator
(subquery);
➢ MySQL Views:
A view is a virtual table based on the result-set of an SQL statement.
CREATE VIEW view_name AS
SELECT columns FROM table_name;
SELECT * FROM view_name;
DROP VIEW view_name;
